Opening and Closing Conneciton for a loop

Opening and Closing Conneciton for a loop

• ACraigM119

  you shouldnt open and close the connection each time, its expensive and inefficient. Dealing with databases is expensive so you want to make the best use out of it whilst you are there.

  you should open the connection outside the loop and close it after the loop and do your processing in the forloop.

  what exactly is the code you are using Can you post that

  
  
  

• Yash Purohit

  This is the code
  
  DbCommand insertCommand=null;
  
  
  ICollection rowCollection = (ICollection)entity.Rows;
  ICollection columnCollection;
  
  IEnumerator rowEnum = rowCollection.GetEnumerator();
  IEnumerator col_enum ;
  
  DataColumn dc = null;
  
  StringBuilder insertRowSQL = new StringBuilder();
  
  int iRow = 0;
  while (rowEnum.MoveNext())
  {
  insertRowSQL = new StringBuilder();
  insertRowSQL.Append("INSERT INTO " + entityName + " VALUES (");
  
  columnCollection = (ICollection)entity.Columns;
  col_enum = columnCollection.GetEnumerator();
  
  while (col_enum.MoveNext())
  {
  dc = (DataColumn)col_enum.Current;
  if (dc != null)
  {
  insertRowSQL.Append("\""+entity.Rows[iRow][dc.ColumnName].ToString()+"\"");
  insertRowSQL.Append(",");
  }
  }
  
  //iRow++;
  //REMOVE THE LAST "," VALUE
  insertRowSQL.Remove(insertRowSQL.Length - 1, 1);
  insertRowSQL.Append(");");
  
  //INSERTING EACH ROW VALUE
  insertCommand = db.GetSqlStringCommand(insertRowSQL.ToString());
  insertCommand.Connection = db.DbProviderFactory.CreateConnection();
  insertCommand.Connection.ConnectionString = db.ConnectionStringWithoutCredentials;
  insertCommand.Connection.Open();
  insertCommand.ExecuteNonQuery();
  insertCommand.Connection.Close();
  iRow++;
  }
  
  
  

• kristinko

  This code should work:
  
  DBCommand db = null;
  db.Connection = db.DbProviderFactory.CreateConnection();
  db.Connection.ConnectionString = db.ConnectionStringWithoutCredentials;
  db.connection.open();
  
  foreach loop
  {
  DBCommand db = db.GetSqlStringCommand(loop generated string);
  db.Connection = db.DbProviderFactory.CreateConnection();
  db.Connection.ConnectionString = db.ConnectionStringWithoutCredentials;
  db.ExecuteNonQuery();
  
  }
  
  
  except you are creating the connection again in the for loop, but not opening it
  Try
  
  DBCommand db = new DbCommand();
  db.Connection = db.DbProviderFactory.CreateConnection();
  db.Connection.ConnectionString = db.ConnectionStringWithoutCredentials;
  db.Connection.Open();
  
  foreach loop
  {
  DBCommand db = db.GetSqlStringCommand(loop generated string);
  db.ExecuteNonQuery();
  
  }
  
  db.Connection.Close();
  
  
• Opening and Closing Conneciton for a loop