Click here to Skip to main content
15,568,963 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Im fairly new to and SQLite...I have a subroutine which writes data to a db using data adapters and datasets.

This whole sub below works fine. It just looks very long and time consuming for such a simple task. I might want to do this 1000's of times and each time I have to create a new dataset, dataadapter , fill it and do the updates.

Am I doing too much?
Is there a better way to update a db or is this normal?

Thanks for any advice you can provide

Public Shared Sub SaveSumm(ByVal TestNo As Integer, ByVal Valx As Integer)

    Dim Tab1 As String = "USummary"
    Dim Conn1 As New SQLiteConnection(ConnMySys)

        Dim z As String = Trim(TestNo.ToString)
        Dim strSQL As String = "SELECT * FROM " & Tab1 & " WHERE SumTestNo ='" & z & "'"

        Dim da As New SQLiteDataAdapter(strSQL, Conn1)
        Dim ds1 As New DataSet(Tab1)
        da.FillSchema(ds1, SchemaType.Source, Tab1)
        da.Fill(ds1, Tab1)
        da.AcceptChangesDuringFill = False

        Dim foundRows() As DataRow
        foundRows = ds1.Tables(Tab1).Select("SumTestNo = " & z)
        Dim cb As New SQLiteCommandBuilder(da)  'needed

        If foundRows.Count = 0 Then
            'add new row
            Dim dsNewRow As DataRow
            dsNewRow = ds1.Tables(Tab1).NewRow()
            dsNewRow.SetField("SummTestNo", TestNo)
            dsNewRow.SetField("SummCount1", Valx)
            dsNewRow.SetField("SummTested", True)
            dsNewRow.SetField("SummImpScore", GetTestImpScore(TestNo))
            'update old row even if its already the same
            foundRows(0).SetField("SummTestNo", TestNo)
            foundRows(0).SetField("SummCount1", Valx)
            foundRows(0).SetField("SummTested", True)
            foundRows(0).SetField("SummImpScore", GetTestImpScore(TestNo))
        End If
        da.Update(ds1, Tab1)   'used by both

end sub

I'm not sure what you really want to achieve...

Please, start here:[^]
Then read this article on CP: Using SQLite in your C# Application[^]
More about SQLite you'll find at:[^]
Share this answer
Use INSERT INTO Query to insert single row in datatable insted of DataAdapters

Visit This Link to know more on INSERT INTO Syntax

Click Here
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900