Sql Bulk Copy/Insert in C#

Question

I am new to JSON and SQLBulkCopy. I have a JSON formatted POST data that I want to Bulk Copy/Insert in Microsoft SQL using C#.

JSON Format:

{
    "URLs": [{
        "url_name": "Google",
        "url_address": "http://www.google.com/"
    },
    {
        "url_name": "Yahoo",
        "url_address": "http://www.yahoo.com/"
    },
    {
        "url_name": "FB",
        "url_address": "http://www.fb.com/"
    },
    {
        "url_name": "MegaSearches",
        "url_address": "http://www.megasearches.com/"
    }]
}

Classes:

public class UrlData
{
    public List<Url> URLs {get;set;}
}

public class Url
{
    public string url_address {get;set;}
    public string url_name {get;set;}
}

How can I do that efficiently?

Solution 1

TL;DR If you have your data already represented as DataTable, you can insert it to the destination table on the server with SqlBulkCopy:

string csDestination = "put here the a connection string to the database";

using (SqlConnection connection = new SqlConnection(csDestination))
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
    connection.Open()
    bulkCopy.DestinationTableName = "TUrls";
    bulkCopy.WriteToServer(dataTableOfUrls);
}

---

If you want to load just "from 10 to 50 urls" there's no need to use SqlBulkCopy - its general purpose to eliminate thousands of separate inserts.

So, inserting without SqlBulkCopy [and without EntityFramework] can be done one by one:

string insertQuery = "insert into TUrls(address, name) values(@address, @name)";
foreach (URL url in listOfUrls)
{
    SqlCommand cmd = new SqlCommand(insertQuery);
    cmd.Parameters.AddWithValue("@name", url.url_name);
    cmd.Parameters.AddWithValue("@address", url.urld_address);

    // Remember to take care of connection! I omit this part for clarity
    cmd.ExecuteNonQuery();
}

To insert data with SqlBulkCopy you need to convert your data (e.g. a list of custom class objects) to DataTable. Below is the quote from Marc Gravell's answer as an example of generic solution for such conversion:

Here's a nice 2013 update using FastMember from NuGet:

IEnumerable<SomeType> data = ...
DataTable table = new DataTable();
using(var reader = ObjectReader.Create(data)) {
    table.Load(reader);
}

---

Yes, this is pretty much the exact opposite of this one; reflection would suffice - or if you need quicker, HyperDescriptor in 2.0, or maybe Expression in 3.5. Actually, HyperDescriptor should be more than adequate.

For example:

// remove "this" if not on C# 3.0 / .NET 3.5
public static DataTable ToDataTable<T>(this IList<T> data)
{
    PropertyDescriptorCollection props =
        TypeDescriptor.GetProperties(typeof(T));
    DataTable table = new DataTable();
    for(int i = 0 ; i < props.Count ; i++)
    {
        PropertyDescriptor prop = props[i];
        table.Columns.Add(prop.Name, prop.PropertyType);
    }
    object[] values = new object[props.Count];
    foreach (T item in data)
    {
        for (int i = 0; i < values.Length; i++)
        {
            values[i] = props[i].GetValue(item);
        }
        table.Rows.Add(values);
    }
    return table;        
}

Now, having your data represented as DataTable, you're ready to write it to the destination table on the server:

string csDestination = "put here the a connection string to the database";

using (SqlConnection connection = new SqlConnection(csDestination))
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
    connection.Open()
    bulkCopy.DestinationTableName = "TUrls";
    bulkCopy.WriteToServer(dataTableOfUrls);
}

Hope it helps.

UPD

1. Answer to @pseudonym27 question: "Hello can I use BulkCopy class to append data to existing table in SQL database?"

Yes, you can - BulkCopy works just as an insert command in a way that it appends data.
Also, consider using an intermediate table in case there's high probability for operation to go wrong (e.g. long insert time and connection issues), and you want to busy/lock the destination table as little time as possible. Another use case for an intermediate table is, of course, the need to do some data transformations before an insert.

Solution 2

Using this below code, you can convert List<YourClassname> to DataTable:-

List<YourClass> objlist = alldata;
string json = Newtonsoft.Json.JsonConvert.SerializeObject(objlist);
DataTable dt = Newtonsoft.Json.JsonConvert.DeserializeObject<DataTable>(json);
SaveDataInTables(dt, "Table_Name_Of_SQL");

Here, I'm assuming that alldata contains list<YourClass> object and you can also do - objlist.Add(objYourClass), then pass sql_TableName and data table in SaveDataInTables method. This method will insert all data in SQL_Table.

public void SaveDataInTables(DataTable dataTable, string tablename)
{
   if (dataTable.Rows.Count > 0)
   {
       using (SqlConnection con = new SqlConnection("Your_ConnectionString"))
       {
           using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
           {
               sqlBulkCopy.DestinationTableName = tablename;
               con.Open();
               sqlBulkCopy.WriteToServer(dataTable);
               con.Close();
            }
        }
    }
}

Hope, these codes help you!!!

Solution 3

You should use Table valued parameters. if you are using > sql server 2005. You can have an example here

Solution 4

If it's only 10-50 urls, being inserted infrequently, you can fire off insert statements. Simple and less hassle and you can use something easy and quick like dapper.

Else if you want the Bulk Copy, you would need to create and fill up an ADO.NET datatable from your JSON first - preferably matching the schema of your destination sql table. It's your choice.

View more solutions