Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   ASP .Net (http://www.velocityreviews.com/forums/f29-asp-net.html)
-   -   Store multiple records at once in SQL database (http://www.velocityreviews.com/forums/t484147-store-multiple-records-at-once-in-sql-database.html)

Pim75 03-14-2007 09:20 AM

Store multiple records at once in SQL database
 
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 03-14-2007 09:34 AM

Re: Store multiple records at once in SQL database
 
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" <p.megens@tiscali.nl> wrote in message
news:1173864022.737751.236230@o5g2000hsb.googlegro ups.com...
> 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!
>




Alexey Smirnov 03-14-2007 09:41 AM

Re: Store multiple records at once in SQL database
 
On Mar 14, 10:20 am, "Pim75" <p.meg...@tiscali.nl> 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()


Pim75 03-14-2007 11:22 AM

Re: Store multiple records at once in SQL database
 
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 03-14-2007 12:28 PM

Re: Store multiple records at once in SQL database
 
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" <p.megens@tiscali.nl> wrote in message
news:1173871325.855813.117790@y80g2000hsf.googlegr oups.com...
> 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 03-14-2007 01:50 PM

Re: Store multiple records at once in SQL database
 
Thanks, works great!


sloan 03-14-2007 04:50 PM

Re: Store multiple records at once in SQL database
 
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" <p.megens@tiscali.nl> wrote in message
news:1173864022.737751.236230@o5g2000hsb.googlegro ups.com...
> 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!
>




Hans Kesting 03-14-2007 04:58 PM

Re: Store multiple records at once in SQL database
 
> 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




All times are GMT. The time now is 08:19 AM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.