Sql Bulk Copy/Insert in C#

.net c# sql sqlbulkcopy

Question

JSON and SQLBulkCopy are new to me. I want to bulk copy/insert JSON-formatted POST data into Microsoft SQL using C#.

Format JSON:

{
    "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 effectively do that?

1
17
9/17/2013 4:11:48 AM

Accepted Answer

Given that you must only load

from 10 to 50 urls
Obviously, there is no need to employSqlBulkCopy Thousands of inserts are covered by it. Unless you need to do this procedure repeatedly.

Therefore, if you have a list of URLs, such as List, just loop over every URL in the list and enter them into the database, for example.

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);

    // don't forget to take care of connection - I omit this part for clearness
    cmd.ExecuteNonQuery();
}

You must transform your class objects, However, if you absolutely must utilizeSqlBulkCopy.URL to DataTable . Check out Answer from Marc Gravell to accomplish this:

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;        
}

Consequently, you may utilize one of Marc's ideas toDataTable as per yourList<URL> . Then, all that is left to do is write the table to the target server table:

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

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

Hope it's useful.

UPD

  1. In response to @pseudonym27's question: "Hello, can I add data to existing tables in SQL databases using the BulkCopy class?"

You can, of course, as BulkCopy just performs the insert instruction slightly differently. If there is a high likelihood that an operation will go wrong (and you want to occupy your destination table for the shortest amount of time feasible) or if you need to do certain data changes, I would advise you to utilize interim tables, but only if you feel the necessity.

28
5/23/2017 12:25:51 PM

Popular Answer

This code below will allow you to convertList<YourClassname> adding 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");

In this case, I'm presuming that alldata includeslist<YourClass> moreover, you are able to:objlist.Add(objYourClass) then move on.sql_TableName a data table, alsoSaveDataInTables method. This technique will include all data.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 are helpful to you!



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow