Sql Bulk Copy/Insert in C#
Since you need to load just
from 10 to 50 urlsthere's obviously no need to use
SqlBulkCopy
- it's for thousands of inserts. Except if you'll be needed to repeat this operation many times.
So, if you have a list of urls, i.e. List, then just loop through all URL from list and insert them to database, e.g.
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();
}
But if you really need to use SqlBulkCopy
you need to convert your objects of class URL
to DataTable
. To do this look at Marc Gravell's answer:
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 maybeExpression
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; }
So you can use one of Marc's solutions to create DataTable
from your List<URL>
. Then you just need to write table to destination table on server:
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 helps.
UPD
- Answer to @pseudonym27 question: "Hello can I use BulkCopy class to append data to existing table in SQL database?"
Of course you can, because BulkCopy works as just insert command, but does it a little bit different, that's all. I'd recommend you to use intermediate tables in case operation has high probability to go wrong (and you want to busy your destination table as little time as possible) or you need to do some data transformations, but only if you feel the need of it.
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!!!
You should use Table valued parameters. if you are using > sql server 2005. You can have an example here