Perform an SQL Server Batch Insert Using .NET

Posted on 2009-12-17
Medium Priority
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

Expert Comment

by:Maheshwar R
ID: 26074403
use transaction in mssql to execute batch querie..

Accepted Solution

DeniseGoodheart earned 0 total points
ID: 26074417
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses
Course of the Month8 days, 19 hours left to enroll

621 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