[RESOLVED] Importing Delimited Text Values into SqlCe from .NET CF20 App

[easymoney]

I have not been able to get the ADS (Access Data Sync tool) to work properly for me. So I am trying to develop a work around.

I am using SqlCe on my handheld, no problems there. The problem is getting the needed data into SqlCe.

I can Import/Export the data to a delimited text file from my desktop app. I can also transfer these text files to the handheld and open them.

I can't use a "SELECT * FROM Data.csv" to load the data in a DataTable because it would require OLEDB and if I had that I would not need load Access Sync tool.

But CF20 does support Imports System.IO namespace and the StreamReader/StreamWriter Classes among some others.

So I could in theory use the StreamReader to read in line by line, loading each field into a DataTable, then save it using SqlCe on my handheld app.

Then I could export the modified data using StreamWriter to export back to desktop file....

All the needed tools are supported directly in CF20 and would no longer need Access or the sync tool.

If so, what are the limits in how many lines CF20 will parse... RAM available of course.

Is this a do-able workaround?
.

[Shaggy Hiker]

You might consider serializing to XML, then passing the XML. I wrote up a few classes so that I could synch a PDA SQLServerCE database to an Access database. You needed both programs running (duh), then pressed a button on the PDA to download whatever. Worked pretty well, and worked REALLY well for small tables and small amounts of data.

I just went searching for my name and XML in the mobile dev forum, and found the class I wrote for transfering data, plus several other posts. I suspect one of them had some sample code for packing data from the DB into XML. However, I am on a very unfamiliar computer, using a very unfamiliar browser, and was totally stumped as to how to post the links to relevant threads on this message. Therefore, I shall have to edit this post to add the links.

Here's a key thread:
http://www.vbforums.com/showthread.p...&highlight=XML

And the other piece, which is an example of creating the XML and extracting it. I have no idea whether this is the best method or not, but it seems reasonable:

http://www.vbforums.com/showthread.p...&highlight=XML

[easymoney]

That is some good advice, but I am looking for a quick dirty solution. Not wise, probably get dogged for saying it like that, but it is the truth. The entire app is being migrated to .Net and Sql. But right now I need a proof of concept, a simple fix to transport the data, etc. It is just a temp fix, it only has to work once or twice, by then it won't matter.

I am going to play with this some more. I will try to parse the file line by line, load to DataTable, etc. Just wonder if there are any other Classes in .NET and CF 20 that may eliminate some string manipulation.

I guess if there are any road blocks, I guess I will find them.... I am good that, hehe

[Shaggy Hiker]

Well, you can certainly load a datatable as you suggested. I do that in....heck, I forget what I did that in. Still, it's possible.

I am not near any of my useful computers (nor within 1000 miles of them), so I can't look it up, but what you showed for a SQL statement looked like a simple SELECT, except from a text file. Does SQLServerCE ONLY allow queries to be run against a SQLServer database?

[petevick]

Hi,
I seem to remember that there is a csv class in the opennetCF

Pete

[IamMacro]

I got interested in your XML reading and writing Shaggy Hiker , im doing something quite similar and i wanna ask if you see any optimizations to be done. This is an example of one Sub.
Im going through the suppliers , one by one from a mysql database to check for new ones hence the "IF tmpreader.read = false" , everything works but still , wondering if you never had a Corrupt database when , Deleteing an entire table and re-inserting stuff.

Private Sub Leverancier()

        xmldocu3.Load("Leverancier.xml")
        Dim tmpreader As SqlCeDataReader = Nothing

        Dim insertcommand As New sqlcecommand
        Dim updatecommand As New SqlCeCommand

        For teller = 0 To xmldocu3.DocumentElement.ChildNodes.Count - 1
            'hulp is MYSQL XML ID
            hulp = xmldocu3.DocumentElement.ChildNodes(teller).ChildNodes(0).InnerText
            Try

                Dim strsql As String = "SELECT * from Leverancier where LeverancierID='" & hulp & "'"
                Dim sqlc As New SqlCeCommand(strsql, sqlconn)
                tmpreader = sqlc.ExecuteReader

                If tmpreader.Read = False Then

                    insertcommand = sqlconn.CreateCommand
                    insertcommand.CommandText = "insert into Leverancier(LeverancierID,Naam) values(?,?)"

                    insertcommand.Parameters.Add(New SqlCeParameter("LeverancierID", SqlDbType.BigInt, 8))
                    insertcommand.Parameters.Add(New SqlCeParameter("Naam", SqlDbType.NVarChar, 100))

                    insertcommand.Parameters("LeverancierID").Value = xmldocu3.DocumentElement.ChildNodes(teller).ChildNodes(0).InnerText
                    insertcommand.Parameters("Naam").Value = xmldocu3.DocumentElement.ChildNodes(teller).ChildNodes(1).InnerText

                    insertcommand.ExecuteNonQuery()

                Else
                    Dim LevID As Integer = tmpreader.Item("LeverancierID")
                    Dim LevNaam As String = tmpreader.Item("Naam")
                    Dim xmlLevID = xmldocu3.DocumentElement.ChildNodes(teller).ChildNodes(0).InnerText
                    Dim xmlLevNaam As String = xmldocu3.DocumentElement.ChildNodes(teller).ChildNodes(1).InnerText

                    If Not LevNaam = xmlLevNaam Then

                        updatecommand = sqlconn.CreateCommand

                        updatecommand.CommandText = "update Leverancier set Naam='" & xmldocu3.DocumentElement.ChildNodes(teller).ChildNodes(1).InnerText & "'where klantID='" & hulp & "'"

                        updatecommand.ExecuteNonQuery()

                        updatecommand.Dispose()

                    End If

                    tmpreader.Close()
                    
                End If

            Catch ex As Exception
                MessageBox.Show("lev" & ex.Message)

            End Try


        Next
        

    End Sub

[Shaggy Hiker]

Sure seems pretty straightforward to me. I'd say that if it works, it's fine.

I never had any issues with the code I had written for XML. There did seem to be an issue with the TCP connection class once the size of the XML reached a certain size, but as long as the whole string was moved ok, the XML parts of the code worked fine.

[easymoney]

XML looks very promising. I would take that route if I were more comfortable with .NET (I know the two are idependent of each other), but right now I am keeping it simple.

I have a method that seems to be working using string manipulation parsing the lines read one by one. I am still playing with it though....

[Shaggy Hiker]

Ultimately, XML is string manipulation, but you don't have to do most of it.

[easymoney]

For the moment I just use the streamreader in CF20 and read data in line by line, and use 2 nested do loop's to break the delimited data down into an array. Then I use a Sql statement to Insert the data into the SqlCe database.

It may not be XML, but it works for now. So I will mark this thread resolved.

Accounts.txt contains some data such as:

000100|101 Any Street|1|1
000200|310 My Street|1|2
etc
etc

Dim sr As StreamReader = File.OpenText("\My Documents\Accounts.txt")

        Dim strLine As String
        Dim strData(0 To 3) As String
        Dim intStart As Integer
        Dim intLength As Integer
        Dim intCount As Integer
        Dim intFieldCount As Integer

        Dim strSql As String

        Dim ssceconn As New SqlCeConnection("Data Source = \My Documents\data.sdf")
        ssceconn.Open()

        Dim sqlInsertRow As SqlCeCommand = ssceconn.CreateCommand()

        Dim table As DataTable = New DataTable

        intCount = 0

        Do
            intFieldCount = 0
            intStart = 1
            strLine = sr.ReadLine()

            Do

                intLength = InStr(intStart, strLine, "|")

                If intLength <> 0 Then
                    strData(intFieldCount) = Mid(strLine, intStart, intLength - intStart)
                Else
                    strData(intFieldCount) = Mid(strLine, intStart, (Len(strLine) - intStart) + 1)
                End If

                intStart = intLength + 1
                intFieldCount = intFieldCount + 1

            Loop Until intLength = 0

            strSql = "INSERT INTO Accounts([Account No], [Address], [Var 1], [Var 2]) VALUES"
            strSql = strSql & " ('" & strData(0) & "', '" & strData(1) & "'," & strData(2) & "," & strData(3) & ");"

            sqlInsertRow.CommandText = strSql
            sqlInsertRow.ExecuteNonQuery()

            intCount = intCount + 1

        Loop Until sr.EndOfStream

        ssceconn.Close()

Thanks.