best way to generate mass insert statement in c#?

Question

I am needing to insert a bunch of similar records into a table, and was wondering if there was a nice/easy way to do this.

I know it would be easy to just build a big string, but I was hoping I could find a more graceful way to do this.

Answer 1

If your data is stored in Csv or XML, you can import the records with SQL server (assuming you're using SQL server)

Answer 2

The SqlBulkCopy class has WriteToServer methods which write multiple records.

I created a class which implemented the IDataReader interface, which I can pass as a parameter to a SqlBulkCopy.WriteToServer method (see ADO.NET: Building a Custom Data Provider for Use with the .NET Data Access Framework for an example of how to implement the IDataReader interface).

There might be a simpler way than implementing IDataReader (i.e. by using one of the other SqlBulkCopy.WriteToServer methods).

Answer 3

I think the general rule of thumb is that you do not want to open/close a connection for each insert. As obvious as that sounds, it's worth mentioning that I've witnessed this issue twice, both with SQL server. Inside a loop, the code I fixed called an external class library that opened/closed a connection with each insert. On a busy day, the connection pool filled up and the exceptions went wild. It caused errors in other applications as well, because no connections could be established. The fix, of course, was to open the connection before the loop, call the ExecuteNonQuery() function within the loop (over and over), and close the connection after the loop. Seems trivial, but it truly is a common mistake.

Best regards...

Answer 4

If the source data is in a text file (CSV or some standard you can read), use a StreamReader to read each line, and a function that takes that line and converts it to a single insert statement. Then just let it fly

For example, say you have a file like this:

foo 1
bar 2
baz 3
...


using(StreamReader sr = new StreamReader(File.Open("sourcefile.txt", FileMode.Open)))
{
  using(SqlConnection conn = new SqlConnection(connectionString))
  {
   conn.Open();
  string line ="";
  while((line = sr.ReadLine()) != "")
  {
     string[] parts = line.split(new string[]{" "}, StringSplitOptions.None);
     string cmdTxt = String.Format("INSERT INTO MyTable(name, value) VALUES('{0}','{1}')", parts[0], parts[1]);

      using(SqlCommand cmd = new SqlCommand(cmdTxt, conn))
      {
         cmd.ExecuteNonQuery();
      }
   }
}

}