Sql Connection Open and close

See more:

C#

SQL

Hi,

I have question regarding sql connection open and close each record (1000 times).

Some one give me an idea which is most efficient way to do

Option 1: Open and close connection for every record

Option 2: keep open the connection for all record insert

public void InsertEmployment(DLAEmployeeExport item, SqlConnection sqlConnection1)
     {
         try
         {
             System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("InsertEmployment", sqlConnection1);
             cmd.CommandType = System.Data.CommandType.StoredProcedure;
             cmd.Parameters.Add("@BusinessTitle", SqlDbType.Char);
             cmd.Parameters["@BusinessTitle"].Direction = ParameterDirection.Input;
             cmd.Parameters["@BusinessTitle"].Value = item.PositionCategory;
             sqlConnection1.Open();
             cmd.ExecuteNonQuery();
         }
         catch (System.Exception se)
         {
             Console.WriteLine(se.Message);
         }
         finally
         {
             /// if the connection is not null
             if (sqlConnection1 != null)
             {
                 ///check if the connection is open, if so then close it
                 if (sqlConnection1.State == ConnectionState.Open)
                     sqlConnection1.Close();
             }
         }
     }