MS Sql server: How to do multiple rows insert ?

Recently I have faced a problem when insert multiple row insert at single call. After first row insert then problem arise from  second row insert. After goggling I have found a solution : it need to clear parameter after each row insert. I want to share that example with you:

Code :C#
// Arranging Data in an Array.
const int no_of_values = 2;
int[] val1 = new int[no_of_values];
int[] val2 = new int[no_of_values];
val1[0] = val1;
val2[0] = val2;
val1[1] = val11;
val2[1] = val22;
// Do the inserts using Parameterized queries.
Connection.Open();
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "Insert INTO [Table] (col1, col2) Values (...val1, ...val2)";
for (int i = 0; i < no_of_values; i++)
{
command.Parameters.Clear(); //it needs to clear parameter after each row inserted.
command.Parameters.Add("...val1", val1[ i ]);
command.Parameters.Add("...val2", val2[ i ]);
command.ExecuteNonQuery();
}
Connection.Close();

I have fixed this problem for MS SQL Server, it is also same for oracle too.
Hope that it may helpful for developers.

Comments

Anonymous said…
Sorry for my bad english. Thank you so much for your good post. Your post helped me in my college assignment, If you can provide me more details please email me.
Ahsan Murshed said…
Its pleasure for me..

Popular posts from this blog

The model backing the 'MyDBContext' context has changed since the database was created. Either manually delete/update the database, or call Database.SetInitializer with an IDatabaseInitializer instance. For example, the DropCreateDatabaseIfModelChanges strategy will automatically delete and recreate the database, and optionally seed it with new data.

How can I get a value of a property from an anonymous type (C#)

Check/Uncheck all items in a CheckBoxList using Javascript