This article features some methods that I have used to load data within a C# application. The methods discussed are an Insert Loop (with bind parameters), the DataAdapter.Update() method, and a custom method of passing the table to a stored procedure. For this article, a simple website demo was setup that accepted a 6-column CSV file upload, and parsed it into a Data Table (int, string, string, string, string, string). Every X records, that Data Table was inserted into a cloud hosted remote database.
For the purpose of comparison, the SQL Server BULK INSERT method was also used (NOTE: The time for the BULK INSERT method below is AFTER moving the files to the database server). The SQL Server portion of this demo was done on an Amazon EC2 instance, so BULK INSERT was an option. It does take a small amount of time to get the file to the remote server, but this time is negligible for the purpose of the comparison. If access and permissions to the database server are not an issue, this certainly highlights that the bulk method(s) are typically much faster than the methods being discussed in this article. If processing very large files; if processing them very often; if access to the database server is an option; if a repeatable process can be written to move the files to the database server; this simple demo illustrates that the bulk methods are much more efficient. However, there are a lot of IF’s in that statement.
For fairly large files, repeatable processes, and simple C# solutions, this comparison also highlights that the custom table to stored procedure method is a pretty decent and cost-effective solution in many scenarios.
The timings from my demonstration, for each of the methods, are shown in the table below:
| Method |
Record Counts |
Estimated Time |
| Insert Loop |
20K Records – 5K Batches |
9 minutes |
| DataAdapter.Update() |
20K Records – 5K Batches |
35 seconds |
| DataAdapter.Update() |
100K Records – 5K Batches |
3 minutes |
| Stored Procedure (Input Table) |
20K Records – 5K Batches |
7 seconds |
| Stored Procedure (Input Table) |
100K Records – 5K Batches |
35 seconds |
| Stored Procedure (Input Table) |
500K Records – 5K Batches |
3 Minutes |
| Stored Procedure (Input Table) |
1 Million Records – 5K Batches |
6 Minutes |
| BULK INSERT |
20K Records |
< 1 Second |
| BULK INSERT |
100K Records |
2 Seconds |
| BULK INSERT |
500K Records |
8 Seconds |
| BULK INSERT |
1 Million Records |
15 Seconds |
Insert Loop: A very common method that I have seen and used is simple insert loops with bind variables. It provides easy control over the data, and easy error handling. It is also very slow if the database is remote. I have seen many developers (including myself) use this method in a local development environment, only to have the process grind to a halt in another environment where the database is remote or cloud based. I have successfully used this method to load hundreds, and even thousands of rows. However, this method does not scale well when dealing with tens of thousands, hundreds of thousands, or millions of rows to a remote database. For small files, and/or local database, this method is easy to implement and provides good control over the data.
string mySql = @"
INSERT INTO DemoFileUpload(RecID, Col1, Col2, Col3, Col4, Col5)
VALUES (@RecID, @Col1, @Col2, @Col3, @Col4, @Col5)
";
using (SqlConnection myConn = new SqlConnection())
using (SqlCommand myCmd = new SqlCommand())
{
myConn.ConnectionString = db.GetDBConn1();
myCmd.CommandText = mySql;
myCmd.CommandType = CommandType.Text;
myCmd.Connection = myConn;
myConn.Open();
// Add the command parameters
myCmd.Parameters.Add(“@RecID”, SqlDbType.Int);
myCmd.Parameters.Add(“@Col1”, SqlDbType.NVarChar, 32);
myCmd.Parameters.Add(“@Col2”, SqlDbType.NVarChar, 32);
myCmd.Parameters.Add(“@Col3”, SqlDbType.NVarChar, 32);
myCmd.Parameters.Add(“@Col4”, SqlDbType.NVarChar, 32);
myCmd.Parameters.Add(“@Col5”, SqlDbType.NVarChar, 32);
foreach (DataRow myRow in p_DT.Rows)
{
// Set the params
myCmd.Parameters[“@RecID”].Value = Convert.ToInt32(myRow[“RecID”]);
myCmd.Parameters[“@Col1”].Value = myRow[“Col1”].ToString();
myCmd.Parameters[“@Col2”].Value = myRow[“Col2”].ToString();
myCmd.Parameters[“@Col3”].Value = myRow[“Col3”].ToString();
myCmd.Parameters[“@Col4”].Value = myRow[“Col4”].ToString();
myCmd.Parameters[“@Col5”].Value = myRow[“Col5”].ToString();
// Execute the insert
myCmd.ExecuteNonQuery();
}
myConn.Close();
}
Data Adapter: DataAdapter.Update() method is a very simple method to transfer data from the data table to the database. However, on more complex scenarios or for larger datasets, I often spend a lot of time researching the various options to tune it correctly so that it works more efficiently. I also typically spend a lot of time reviewing the magic of this method, and prefer to have greater control. While I have had success using this method, as the data grows larger, I find that I must customize the process, where the next methods work for larger and smaller sets. I find the DataAdapter.Update() method greatly out-performs the insert loop, and greatly under-performs the next methods described.
using (SqlConnection myConn = new SqlConnection())
{
myConn.ConnectionString = db.GetDBConn1();
// Initialize with an empty set structure: All inserts
SqlDataAdapter myDA = new SqlDataAdapter(“SELECT * FROM DemoFileUpload WHERE 1=2”, myConn);
SqlCommandBuilder myCB = new SqlCommandBuilder(myDA);
myDA.MissingSchemaAction = MissingSchemaAction.Add;
myDA.UpdateBatchSize = myUpdateBatchSize;
myDA.Update(p_DT);
}
Datatable to Stored Procedure: The methods I have used on the last couple of projects pass larger amounts of data to the database, while giving control over how much data, and logging those actions. I find these methods perform somewhat comparable to the bulk methods for reasonable amounts of data (albeit quite slower), but are easier to integrate into a self-contained application. While these methods certainly aren’t as fast as most of the bulk methods I have used, they are fairly efficient, especially for the simplicity of setup and migration. I also find them simple to understand, setup, implement, and replicate across multiple projects.
First, the input file is parsed, line by line into a data table. This is pretty typical of every method I use. Every X rows [as set by configuration] the data table is sent to the database and processed as a set.
If working with SQL Server, I maintain a user-defined table type in the database with that same structure, and a stored procedure that takes in that table type, and insert/selects from that input table into the database. I then pass the data table from the C# application to the stored procedure to insert the data.
string mySql = “[dbo].[spDemoFileUploadInsert]”;
using (SqlConnection myConn = new SqlConnection())
using (SqlCommand myCmd = new SqlCommand())
{
myConn.ConnectionString = db.GetDBConn1();
myCmd.CommandText = mySql;
myCmd.CommandType = CommandType.StoredProcedure;
myCmd.Connection = myConn;
myConn.Open();
// Add the command parameters
myCmd.Parameters.Add(“@tblInput”, SqlDbType.Structured).Value = p_DT;
myCmd.ExecuteNonQuery();
myConn.Close();
}
If working with Oracle the process is similar. Rather than taking in a table type parameter, I create a stored procedure that accepts a CLOB. In the C# application, I convert the data table to an XML string, and pass that string as a CLOB into the stored procedure. In the stored procedure, I again do a simple insert/select from the input CLOB using Oracle XMLTYPE.
string myXMLRec;
using (StringWriter sw = new StringWriter())
{
p_DT.WriteXml(sw);
myXMLRec = sw.ToString();
}
myConn.ConnectionString = db.GetDBConn1();
myCmd.Connection = myConn;
myCmd.CommandText = “MyPackage.MyProcedure”;
myCmd.CommandType = CommandType.StoredProcedure;
myCmd.Parameters.Add(“p_XmlRec”, OracleDbType.Clob).Value = myXMLRec;
myConn.Open();
myCmd.ExecuteNonQuery();
myConn.Close();
While these methods are slower than the built-in bulk methods, I have found them to be simple, repeatable, and fairly efficient, while supporting the logging and control that is required in most of my projects. They are also easily configurable with respect to the batch size of records. The batch can be easily increased or decreased from an application configuration based on the performance in a specific environment. While these methods have the dependency of the stored procedure, I typically have stored procedures that do the subsequent processing of the staged data in the database. Therefore, these methods integrate nicely with the overall process.
