Bulk Insert Using QueryCommand Object
Do you have a tutorial on using QueryCommand to bulk insert into a database. Here is the code I'm using right now:
QueryCommand cmd = conn.CreateCommand();
try
{
foreach (MyObj obj in list)
{
cmd.Parameters.Clear();
cmd.CommandText = "INSERT INTO " + MY_TABLE + " (name, type) VALUES (?,?)";
cmd.Parameters.Add("@name", OdbcType.VarChar).Value = obj.name != null ? obj.name : DBNull.Value.ToString();
cmd.Parameters.Add("@type", OdbcType.VarChar).Value = obj.type != null ? obj.type.ToString() : DBNull.Value.ToString();
cmd.ExecuteNonQuery();
}
}
Is this the correct way to do it?
Does it split a separate query for each iteration?
+3
source to share
1 answer
you can use SqlBulkCopy
here's a simple SqlBulkCopy code example:
using System.Data.SqlClient;
DataTable table = new DataTable("States");
// construct DataTable
table.Columns.Add(new DataColumn("id_state", typeof(int)));
table.Columns.Add(new DataColumn("state_name", typeof(string)));
// note: if "id_state" is defined as an identity column in your DB,
// row values for that column will be ignored during the bulk copy
table.Rows.Add("1", "Atlanta");
table.Rows.Add("2", "Chicago");
table.Rows.Add("3", "Springfield");
using(SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
{
bulkCopy.BulkCopyTimeout = 600; // in seconds
bulkCopy.DestinationTableName = "state";
bulkCopy.WriteToServer(table);
}
+3
source to share