locked
VS 2019 VB How to save a DataTable into sql Database RRS feed

  • Question

  • Hi team

    I have a datatable and or dataset that have been filled from a csv file which is working fine.

    This I display in a unbound data Grid. So I can see the data.

    I need to insert the data in the datatable or dataset into an Sql Database.

    I can use either the datatable OR dataset which ever is the best. 

    The SQL Column headers match the Column headers in the datatable that was in the csv file.

    I delete the contents of the SQL Database just before inserting and reset the Key back to 1

    Note the Datatable do's not have the Key ID which is in the SQL Database.

    So all I need is a simple way to insert this data into the SQL Database.

    My Code I have tryed:

        Protected Sub UpdateDriversLicence(dt As DataTable)
            Dim connectionString As String = MySQLString
            Dim queryString As String = "SELECT ID, ValidCompetitionLicence,ValidOfficialLicence, LicenceNumber, LastName, FirstName, DateValidated, CompetitionLicenceExpiryDate, OfficialLicenceExpiryDate, CompetitionLicenceSuspended, OfficialLicenceSuspended, LicenceDescription EventID  FROM CAMScsvFileData"

            Using connection As SqlConnection = New SqlConnection(connectionString)
                connection.Open()
                Dim adapter As SqlDataAdapter = New SqlDataAdapter()
                adapter.SelectCommand = New SqlCommand(queryString, connection)
                Dim builder As SqlCommandBuilder = New SqlCommandBuilder(adapter)
                builder.RefreshSchema()
                Try
                    adapter.Fill(dt)
                    adapter.InsertCommand = builder.GetUpdateCommand()
                    '' adapter.UpdateCommand = builder.GetUpdateCommand()
                    adapter.Update(dt)

                Catch ex As Exception
                    MsgBox("The Error : " & ex.ToString)
                End Try

            End Using

        End Sub

    Regards

    Ron B






    Thursday, May 30, 2019 11:54 PM

All replies

  • Hi RonbSA,

    Thank you for posting here.

    Since your issue is more related to VB development, we will move this thread to Visual Basic forum for better support. Thank you for understanding.

    Best Regards,

    Dylan


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, June 3, 2019 2:59 AM
  • Hi,

    fix your code:

      Dim olddt As New DataTable
        Protected Sub UpdateDriversLicence(newdt As DataTable)
            Dim connectionString As String = MySQLString
            Dim queryString As String = "SELECT ID, ValidCompetitionLicence,ValidOfficialLicence, LicenceNumber, LastName, FirstName, DateValidated, CompetitionLicenceExpiryDate, OfficialLicenceExpiryDate, CompetitionLicenceSuspended, OfficialLicenceSuspended, LicenceDescription EventID  FROM CAMScsvFileData"
    
            Using connection As SqlConnection = New SqlConnection(connectionString)
                connection.Open()
                Dim adapter As SqlDataAdapter = New SqlDataAdapter()
                adapter.SelectCommand = New SqlCommand(queryString, connection)
                Dim builder As SqlCommandBuilder = New SqlCommandBuilder(adapter)
                builder.RefreshSchema()
                Try
                    adapter.Fill(olddt)
                    adapter.InsertCommand = builder.GetUpdateCommand()
                    adapter.Update(newdt)
                Catch ex As Exception
                    MsgBox("The Error : " & ex.ToString)
                End Try
    
            End Using
    
        End Sub

    Best Regards,

    Alex



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 3, 2019 5:07 AM
  • Hello,

    If all that is being done are inserts consider using SqlBulkCopy.

    In the following code sample the DataTable columns are in the same position as the table in the database in regards to mapping. If not then the easy thing to do is rearrange the DataColumns in the same ordinal position as the table in the database.

    This is all in a class rather than a form, this is a best practice to separate data operations from being done in a form.

    Calling from your form.

    Dim ops = New NameOfYourClassGoesHere
    ops.UpdateDriversLicence(dt)
    If Not ops.IsSuccessful Then
        MessageBox.Show($"Failed with error {ops.LastException.Message}")
    End If

    Back end code, note DestinationTableName needs to be set, change the connection to your connection.

    Imports System.Data.SqlClient
    
    Public Class NameOfYourClassGoesHere
        Private ConnectionString As String =
                    "Data Source=TODO;Initial Catalog=TODO;Integrated Security=True"
        Public LastException As Exception
        Public HasException As Boolean
        Public ReadOnly Property IsSuccessful() As Boolean
            Get
                Return HasException = False
            End Get
        End Property
    
        Public Sub UpdateDriversLicence(dt As DataTable)
            Try
                Using cn As New SqlConnection(ConnectionString)
                    cn.Open()
                    Using sbc As New SqlBulkCopy(cn)
                        sbc.AutoMapColumns(dt)
                        sbc.DestinationTableName = "TODO"
                        sbc.WriteToServer(dt)
                    End Using
                End Using
            Catch ex As Exception
                HasException = True
                LastException = ex
            End Try
    
        End Sub
    End Class
    Public Module SqlExtensions
        ''' <summary>
        ''' Used to map columns where the ordinal position of columns
        ''' match between the DataTable and the SQL-Server Table.
        ''' </summary>
        ''' <param name="sbc"></param>
        ''' <param name="dt"></param>
        <Runtime.CompilerServices.Extension>
        Public Sub AutoMapColumns(sbc As SqlBulkCopy, dt As DataTable)
            For Each column As DataColumn In dt.Columns
                sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName)
            Next
        End Sub
    End Module


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, June 3, 2019 10:20 AM
  • Alex

    Nice, you saw the problem. However, I think that fillschema would even better fit. Otherwise if the table contains many records, it would be useless retrieved.

    https://docs.microsoft.com/en-us/dotnet/api/system.data.common.dbdataadapter.fillschema?view=netframework-4.8


    Success
    Cor

    Monday, June 3, 2019 10:31 AM