How quickly to write List<Object> to database?

Question

Please explain me how to make a WriteToBase() method faster or how I can bulk insert without making calls for each insert.

    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?

Solution

You are currently hitting the database many times. There should be only 1 hit for all the inserts.

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();    
  }
}

Comments

[Bmo]

Just in case anyone is wondering you need to add a reference to System.Transactions, the include a using System.Transactions line at the top...at least I had to.

[Arda Basoglu]

Adding parameters in a loop will throw an error since you are adding those parameters multiple times. Only values must be assigned inside the loop.