Store multiple records at once in SQL database

Discussion in 'ASP .Net' started by Pim75, Mar 14, 2007.

  1. Pim75

    Pim75 Guest

    Hello,

    I want to store multiple records at once in a SQL database with a
    For..Next instruction like the sample code below:

    For counter = 0 To 100
    Dim dbInsert As New SqlCommand( _
    "INSERT INTO table (Field1, Field2) VALUES ('" & Field1 & "', '" &
    Field2 & "')", _
    myConnection)

    dbInsert.Connection.Open()
    dbInsert.ExecuteNonQuery()
    dbInsert.Connection.Close()
    Next

    In the code above the query to add the record to the database is
    executed 100 times. I would like to know if there's a more efficient
    way to add 100 records at once to the table?

    Any help is appreciated!
    Pim75, Mar 14, 2007
    #1
    1. Advertising

  2. This is fine except you don' need to reopen connection on every insert. Just
    open it once before the loop and close after.

    --
    Eliyahu Goldin,
    Software Developer & Consultant
    Microsoft MVP [ASP.NET]
    http://msmvps.com/blogs/egoldin
    http://usableasp.net


    "Pim75" <> wrote in message
    news:...
    > Hello,
    >
    > I want to store multiple records at once in a SQL database with a
    > For..Next instruction like the sample code below:
    >
    > For counter = 0 To 100
    > Dim dbInsert As New SqlCommand( _
    > "INSERT INTO table (Field1, Field2) VALUES ('" & Field1 & "', '" &
    > Field2 & "')", _
    > myConnection)
    >
    > dbInsert.Connection.Open()
    > dbInsert.ExecuteNonQuery()
    > dbInsert.Connection.Close()
    > Next
    >
    > In the code above the query to add the record to the database is
    > executed 100 times. I would like to know if there's a more efficient
    > way to add 100 records at once to the table?
    >
    > Any help is appreciated!
    >
    Eliyahu Goldin, Mar 14, 2007
    #2
    1. Advertising

  3. On Mar 14, 10:20 am, "Pim75" <> wrote:
    > Hello,
    >
    > I want to store multiple records at once in a SQL database with a
    > For..Next instruction like the sample code below:
    >
    > For counter = 0 To 100
    > Dim dbInsert As New SqlCommand( _
    > "INSERT INTO table (Field1, Field2) VALUES ('" & Field1 & "', '" &
    > Field2 & "')", _
    > myConnection)
    >
    > dbInsert.Connection.Open()
    > dbInsert.ExecuteNonQuery()
    > dbInsert.Connection.Close()
    > Next
    >
    > In the code above the query to add the record to the database is
    > executed 100 times. I would like to know if there's a more efficient
    > way to add 100 records at once to the table?
    >
    > Any help is appreciated!


    If you are using .NET 2/ADO.NET 2, there is a new feature named
    SqlBulkCopy that lets you to perform copy operation for a large amount
    of data between a source data store and a destination.

    For example, when your destination table is

    CREATE TABLE [dbo].[test] (
    [col1] [nvarchar] NULL ,
    [col2] [nvarchar] NULL ,
    ) ON [PRIMARY]
    GO

    a code for SqlBulkCopy could be following

    Dim dt As DataTable = New DataTable()

    dt.Columns.Add(New DataColumn())
    dt.Columns.Add(New DataColumn())

    Dim row As DataRow = dt.NewRow()

    For counter = 0 To 100
    row(0) = ....
    row(1) = ....
    dt.Rows.Add(row)
    Next

    Dim cn As SqlConnection = New SqlConnection(".......")
    Dim bc As System.Data.SqlClient.SqlBulkCopy = New
    System.Data.SqlClient.SqlBulkCopy(cn, SqlBulkCopyOptions.TableLock,
    Nothing)

    bc.BatchSize = dt.Rows.Count
    cn.Open()
    bc.DestinationTableName = "test"
    bc.WriteToServer(dt)
    cn.Close()
    bc.Close()
    Alexey Smirnov, Mar 14, 2007
    #3
  4. Pim75

    Pim75 Guest

    Hello Eliyahu,

    Thanks for your help.
    As I'm new to asp.net it's not clear to me how I can put the Open()
    and Close() instruction outside the For.. Next commands.

    I can make something like the code below but that won't work because
    the dbInsert is declared after the dbInsert.Open in this case. Can you
    help me to get this work correct?

    dbInsert.Connection.Open()
    For counter = 0 To 100
    Dim dbInsert As New SqlCommand( _
    "INSERT INTO table (Field1, Field2) VALUES ('" & Field1 & "', '"
    &
    Field2 & "')", _
    myConnection)
    dbInsert.ExecuteNonQuery()
    Next
    dbInsert.Connection.Close()
    Pim75, Mar 14, 2007
    #4
  5. You have already a separate connection object myConnection. Just do
    myConnection.Open() and myConnection.Close() instead of
    dbInsert.Connection.Open()/Close().


    --
    Eliyahu Goldin,
    Software Developer & Consultant
    Microsoft MVP [ASP.NET]
    http://msmvps.com/blogs/egoldin
    http://usableasp.net



    "Pim75" <> wrote in message
    news:...
    > Hello Eliyahu,
    >
    > Thanks for your help.
    > As I'm new to asp.net it's not clear to me how I can put the Open()
    > and Close() instruction outside the For.. Next commands.
    >
    > I can make something like the code below but that won't work because
    > the dbInsert is declared after the dbInsert.Open in this case. Can you
    > help me to get this work correct?
    >
    > dbInsert.Connection.Open()
    > For counter = 0 To 100
    > Dim dbInsert As New SqlCommand( _
    > "INSERT INTO table (Field1, Field2) VALUES ('" & Field1 & "', '"
    > &
    > Field2 & "')", _
    > myConnection)
    > dbInsert.ExecuteNonQuery()
    > Next
    > dbInsert.Connection.Close()
    >
    Eliyahu Goldin, Mar 14, 2007
    #5
  6. Pim75

    Pim75 Guest

    Thanks, works great!
    Pim75, Mar 14, 2007
    #6
  7. Pim75

    sloan Guest

    If you're using Sql Server 2000 or beyond, there is a better way.

    See:
    http://support.microsoft.com/kb/315968


    but basically you can:

    Create a strong typed dataset.
    Add rows to a table in the dataset.
    Send the DataSet.GetXml() into the stored procedure.

    The extra bonus. If you have any indices (indexes), they get rebuild AFTER
    all N number of rows are inserted.

    ...




    "Pim75" <> wrote in message
    news:...
    > Hello,
    >
    > I want to store multiple records at once in a SQL database with a
    > For..Next instruction like the sample code below:
    >
    > For counter = 0 To 100
    > Dim dbInsert As New SqlCommand( _
    > "INSERT INTO table (Field1, Field2) VALUES ('" & Field1 & "', '" &
    > Field2 & "')", _
    > myConnection)
    >
    > dbInsert.Connection.Open()
    > dbInsert.ExecuteNonQuery()
    > dbInsert.Connection.Close()
    > Next
    >
    > In the code above the query to add the record to the database is
    > executed 100 times. I would like to know if there's a more efficient
    > way to add 100 records at once to the table?
    >
    > Any help is appreciated!
    >
    sloan, Mar 14, 2007
    #7
  8. Pim75

    Hans Kesting Guest

    > Hello,
    >
    > I want to store multiple records at once in a SQL database with a
    > For..Next instruction like the sample code below:
    >
    > For counter = 0 To 100
    > Dim dbInsert As New SqlCommand( _
    > "INSERT INTO table (Field1, Field2) VALUES ('" & Field1 & "', '" &
    > Field2 & "')", _
    > myConnection)
    > dbInsert.Connection.Open()
    > dbInsert.ExecuteNonQuery()
    > dbInsert.Connection.Close()
    > Next
    > In the code above the query to add the record to the database is
    > executed 100 times. I would like to know if there's a more efficient
    > way to add 100 records at once to the table?
    >
    > Any help is appreciated!
    >


    If you change the command to use parameters (safer!), you only have to change
    the
    values of those parameters. Then you can execute the command again.

    Hans Kestin
    Hans Kesting, Mar 14, 2007
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Chumley the Walrus
    Replies:
    1
    Views:
    377
    Mark Rae
    Aug 12, 2004
  2. Øyvind Isaksen

    List categories AND records at once...

    Øyvind Isaksen, Oct 16, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    361
    Patrick.O.Ige
    Oct 17, 2005
  3. JJ
    Replies:
    9
    Views:
    547
    sloan
    May 7, 2007
  4. A P

    Update multiple records at once

    A P, Jan 10, 2005, in forum: ASP General
    Replies:
    1
    Views:
    102
    Evertjan.
    Jan 10, 2005
  5. Gancy
    Replies:
    4
    Views:
    172
    Rasto Levrinc
    Feb 3, 2005
Loading...

Share This Page