How to quickly write a List <Object> to a database?

Question

Please tell me how to make the following code faster (I mean the WriteToBase method).

class MyClass { public int a; public int b; public int c; } void main() { List<MyClass> mc=new List<MyClass>(); mc.Add(new MyClass()); //example mc.Add(new MyClass()); WriteToBase(mc); } void WriteToBase(List<MyClass> mc) { //Create Connection string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)"; SqlCommand cmdIns = new SqlCommand(sqlIns, Connection); for(int i=0;i<mc.Count;i++) { cmdIns.Parameters.Add("@name", mc[i].a); cmdIns.Parameters.Add("@information", mc[i].b); cmdIns.Parameters.Add("@other", mc[i].c); cmdIns.ExecuteNonQuery(); } } } 

Any ideas?

Answer 1

You are currently in the database many times. For all inserts there should be only 1 hit.

Try this code:

 void WriteToBase(List<MyClass> mc) { //Create Connection using (TransactionScope scope = new TransactionScope()) { string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)"; SqlCommand cmdIns = new SqlCommand(sqlIns, Connection); for(int i=0;i<mc.Count;i++) { cmdIns.Parameters.Add("@name", mc[i].a); cmdIns.Parameters.Add("@information", mc[i].b); cmdIns.Parameters.Add("@other", mc[i].c); cmdIns.ExecuteNonQuery(); } scope.Complete(); } } 

Answer 2

 void WriteToBase(List<MyClass> mc) { //Create Connection using (TransactionScope scope = new TransactionScope()) { string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)"; SqlCommand cmdIns = new SqlCommand(sqlIns, Connection); for(int i=0;i<mc.Count;i++) { cmdIns.Parameters.AddWithValue("@name", mc[i].a); cmdIns.Parameters.AddWithValue("@information", mc[i].b); cmdIns.Parameters.AddWithValue("@other", mc[i].c); cmdIns.ExecuteNonQuery(); cmdIns.Parameters.Clear(); } scope.Complete(); } }