CODEWITHSUNDEEP
c#sqlsql-server
Jonathan Kittell
Jonathan Kittell

Reputation: 7503

How do I create a data table and pass in information?

I want to insert more than 1000 rows of data into a SQL table so I need to use SqlBulkCopy. However it appears I need to create a DataTable first. Therefore, how do you add data to the DataTable? I want to add dynamically generated data from a dictionary into the DataTable.

var conn = new SqlConnection(masterData.DictRunData["ConnectionStringLocalDb"]);
const string objectName = "NotAvailable";
var dt = DateTime.Now;
var cmd = new SqlCommand("insert into CorporateDataStructure.dbo.ObjectInventory (location, object_name, object_id, object_xpath, time) values (@location, @object_name, @object_id, @object_xpath, @time)", conn);

foreach (var pair in webidsAndXPaths)
{
    conn.Open();
    cmd.Parameters.Clear();
    cmd.Parameters.Add(new SqlParameter("@object_name", objectName));
    cmd.Parameters.Add(new SqlParameter("@object_id", pair.Key));
    cmd.Parameters.Add(new SqlParameter("@object_xpath", pair.Value));
    cmd.Parameters.Add(new SqlParameter("@time", dt));

    cmd.ExecuteNonQuery();
    conn.Close();
}
return true;

Upvotes: 1

Views: 115

Answers (2)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

using (var bulkCopy = new SqlBulkCopy(conn, <SqlBulkCopyOptions>))
            {
                //dataTable definition
                var table = new System.Data.DataTable();
                table.Columns.Add("object_name", typeof(string));
                table.Columns.Add("object_id", typeof(int));
                table.Columns.Add("object_xpath", typeof(string));
                table.Columns.Add("time", typeof(DateTime));
                //bulkCopy options
                bulkCopy.BatchSize = webidsAndXPaths.Count();
                bulkCopy.DestinationTableName = "CorporateDataStructure.dbo.ObjectInventory";
                //bulkCopy mappings (not mandatory, just to avoid depending on column ordering in datatable)
                //That may avoid "strange" mistakes if you change something to your db or datatable.
                bulkCopy.ColumnMappings.Add("object_name", "object_name");
                bulkCopy.ColumnMappings.Add("object_id", "object_id");
                bulkCopy.ColumnMappings.Add("object_xpath", "object_xpath");
                bulkCopy.ColumnMappings.Add("time", "time");

                //dataTable fedding from dictionary
                foreach (var pair in webidsAndXPaths)
                {
                     table.Rows.Add(objectName, pair.Key, pair.Value, dt);
                }
                //write to db.
                bulkCopy.WriteToServer(table);
            }

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460370

Add the appropriate columns to the DataTable, then loop all dictionary entries and add the rows:

var table = new System.Data.DataTable();
table.Columns.Add("object_name", typeof(string));
table.Columns.Add("object_id", typeof(int));
table.Columns.Add("object_xpath", typeof(string));
table.Columns.Add("time", typeof(DateTime));

foreach (var pair in webidsAndXPaths)
{
    table.Rows.Add(objectName, pair.Key, pair.Value, dt);
}

Instead of table.Rows.Add you could also use this longer but readable approach using SetField:

foreach (var pair in webidsAndXPaths)
{
    DataRow row = table.Rows.Add();
    row.SetField("object_name", objectName);
    row.SetField("object_id", pair.Key);
    row.SetField("object_xpath", pair.Value);
    row.SetField("time", dt);
}

Finally you need to use SqlBulkCopy.WriteToServer:

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
{
    conn.Open();
    bulkCopy.DestinationTableName = "dbo.ObjectInventory";
    bulkCopy.WriteToServer(table);
}

If the order of the columns in the DataTable is the same as in the DB you don't need to map columns.

Upvotes: 3

Related Questions