Which is correct : Open one Connection for inserting List of objects or open connection for every single insertion? I have written the following method twice but I don't know which is better from performance perspective, code design and best practice.


  First:


public int Insert()
        {
            int affectedRow = -1;
            using (IfxConnection con = new IfxConnection(ConfigurationManager.ConnectionStrings["sh"].ToString()))
            {

                StringBuilder cmdTxt = new StringBuilder();
                cmdTxt.Append(" INSERT INTO shedule(day,short,name,depcode,studycode,batchnum) VALUES (?,?,?,?,?,?) ");
                using (var myIfxCmd = new IfxCommand(cmdTxt.ToString(), con))
                {
                    myIfxCmd.CommandType = CommandType.Text;
                    myIfxCmd.Parameters.Add("day", IfxType.Char);
                    myIfxCmd.Parameters.Add("short", IfxType.NVarChar);
                    myIfxCmd.Parameters.Add("name", IfxType.NVarChar);
                    myIfxCmd.Parameters.Add("depcode", IfxType.Integer);
                    myIfxCmd.Parameters.Add("studycode", IfxType.Integer);
                    myIfxCmd.Parameters.Add("batchnum", IfxType.Integer);

                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }

                    myIfxCmd.Parameters[0].Value = ((object)this.DayId) ?? DBNull.Value;
                    myIfxCmd.Parameters[1].Value = ((object)this.ShortName) ?? DBNull.Value;
                    myIfxCmd.Parameters[2].Value = ((object)this.Name) ?? DBNull.Value;
                    myIfxCmd.Parameters[3].Value = this.DepCode;
                    myIfxCmd.Parameters[4].Value = this.StudyCode;
                    myIfxCmd.Parameters[5].Value = this.BatchNum;

                    affectedRow = myIfxCmd.ExecuteNonQuery();
                }
            }
            return affectedRow;
        }


THEN :

   foreach (Day a in days)
    {
        affectedRow = a.Insert();
    }





  Second:


public int Insert(List&lt;Day&gt; days)
        {
            int affectedRow = -1;
            using (IfxConnection con = new IfxConnection(ConfigurationManager.ConnectionStrings["sh"].ToString()))
            {

                StringBuilder cmdTxt = new StringBuilder();
                cmdTxt.Append(" INSERT INTO shedule(day,short,name,depcode,studycode,batchnum) VALUES (?,?,?,?,?,?) ");
                using (var myIfxCmd = new IfxCommand(cmdTxt.ToString(), con))
                {
                    myIfxCmd.CommandType = CommandType.Text;
                    myIfxCmd.Parameters.Add("day", IfxType.Char);
                    myIfxCmd.Parameters.Add("short", IfxType.NVarChar);
                    myIfxCmd.Parameters.Add("name", IfxType.NVarChar);
                    myIfxCmd.Parameters.Add("depcode", IfxType.Integer);
                    myIfxCmd.Parameters.Add("studycode", IfxType.Integer);
                    myIfxCmd.Parameters.Add("batchnum", IfxType.Integer);

                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    foreach (Day a in days)
                    {
                        myIfxCmd.Parameters[0].Value = ((object)a.DayId) ?? DBNull.Value;
                        myIfxCmd.Parameters[1].Value = ((object)a.ShortName) ?? DBNull.Value;
                        myIfxCmd.Parameters[2].Value = ((object)a.Name) ?? DBNull.Value;
                        myIfxCmd.Parameters[3].Value = a.DepCode;
                        myIfxCmd.Parameters[4].Value = a.StudyCode;
                        myIfxCmd.Parameters[5].Value = a.BatchNum;

                        affectedRow = myIfxCmd.ExecuteNonQuery();
                    }
                }

            }

            return affectedRow;
        }
In general, the best is to do all your inserts in one connection.

Open/Close a connection implies a penalty. Although it is mitigated by some connection pooling mechanism on the database side (btw what is it ?), it is additional work that is not useful nor required.

The reason is that there is the possibility to use a Transaction to ensure that "all or nothing" is inserted, regarding whether things goes good or bad in your insertion loop, and in most cases a transaction is tied to a connection.

Here is something that includes a transaction. That'the way I am doing it in SQLServer. I assumed that the Informix transaction are correctly implemented using the ADO.NET scheme, and I do not know is this implementation is working.



public int Insert(List&lt;Day&gt; days)
{
    int affectedRow = -1;
    using (IfxConnection con = new IfxConnection(ConfigurationManager.ConnectionStrings["sh"].ToString()))
    {
        con.Open();
        using(var transaction = con.BeginTransaction())
        {
            var cmdTxt = " INSERT INTO shedule(day,short,name,depcode,studycode,batchnum) VALUES (?,?,?,?,?,?) ";
            using (var myIfxCmd = new IfxCommand(cmdTxt, con,transaction ))
            {
                myIfxCmd.CommandType = CommandType.Text;
                myIfxCmd.Parameters.Add("day", IfxType.Char);
                myIfxCmd.Parameters.Add("short", IfxType.NVarChar);
                myIfxCmd.Parameters.Add("name", IfxType.NVarChar);
                myIfxCmd.Parameters.Add("depcode", IfxType.Integer);
                myIfxCmd.Parameters.Add("studycode", IfxType.Integer);
                myIfxCmd.Parameters.Add("batchnum", IfxType.Integer);



                foreach (Day a in days)
                {
                    myIfxCmd.Parameters[0].Value = ((object)a.DayId) ?? DBNull.Value;
                    myIfxCmd.Parameters[1].Value = ((object)a.ShortName) ?? DBNull.Value;
                    myIfxCmd.Parameters[2].Value = ((object)a.Name) ?? DBNull.Value;
                    myIfxCmd.Parameters[3].Value = a.DepCode;
                    myIfxCmd.Parameters[4].Value = a.StudyCode;
                    myIfxCmd.Parameters[5].Value = a.BatchNum;

                    affectedRow = myIfxCmd.ExecuteNonQuery();
                }
            }

            // commit the changes in the database. 
            // if an exception occurs somewhere, then the using blocks
            // makes that the transaction is disposed and rolled back,
            // and nothing will be updated in the database
            transaction.Commit();
        }
    }

    return affectedRow;
}