Perform an SQL Server Batch Insert Using .NET

Posted on 2009-12-17
Last Modified: 2013-11-26
Good Day:

I am using VS.NET 2005 with VB.NET and SQL Server 2000.  I need to perform an SQL Server batch insert using an array that may contain 200 or more records.  How would I do this using the following code that creates a dataset called dataset1,a table called table1, and an array called mysarray?  The following is my code:

Private Sub GetFiles()
        Dim dir As New IO.DirectoryInfo(Me.txtDirLocation.Text)
        Dim files As IO.FileInfo() = dir.GetFiles("*.pdf")
        Dim fileName As IO.FileInfo
        Dim dataset1 As New DataSet
        Dim table1 As New DataTable


        For Each fileName In files
            Dim myarray() As String = Nothing
            Array.Resize(myarray, 1)
            Dim sPDFName As String = fileName.ToString
                   myarray.SetValue(sPDFName, 0)
            table1.LoadDataRow(myarray, True)
    End Sub

Thank You,

Question by:DeniseGoodheart
    LVL 4

    Expert Comment

    by:Mahesh Reddy
    use transaction in mssql to execute batch querie..

    Accepted Solution

    It needs to be a desk top solution that interfaces with SQL Server.  I found a great example and it answered my question.

    Function AddContact(ByVal FirstName As String, ByVal LastName As String, ByVal ContactRecordType As String) As Boolean
            Dim sql As String
            'Dim cmd As New SqlCommand
            Dim cn As New SqlConnection
            cn = ConnectToDatabase()
            'get the table as an empty dataset
            Dim da As New SqlDataAdapter("SELECT * FROM tblContacts WHERE 1 = 0", cn)
            Dim ds As New DataSet

            da.FillSchema(ds, SchemaType.Source, "tblContacts")
            da.Fill(ds, "tblContacts")

            'TO DO: look at commandbuilder class to see if it
            'can do all this much more easily.
            Dim cmdBuilder As New SqlCommandBuilder(da)

            With ds.Tables("tblContacts")
                'create a new row
                Dim dr As DataRow = .NewRow
                dr("NameLast") = LastName
                dr("NameFirst") = FirstName
                dr("ContactRecordType") = ContactRecordType
                'required fields
                dr("AddDate") = Now()
                dr("AddTime") = Now()
            End With

            da.Update(ds, "tblContacts")

            Return True
        End Function

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
    This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now