Created
April 24, 2013 19:57
-
-
Save oshea00/5455075 to your computer and use it in GitHub Desktop.
Use SQLBulkLoad with linq-toSQL in LinqPad
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Add this to LinqPad "C# Program" | |
// Example use: | |
// Insead of this: (create a List<CS_POSITION>) | |
// CS_POSITIONs.InsertOnSubmit(position); | |
// This: | |
// positions.Add(position); | |
// | |
// Then instead of this: | |
// SubmitChanges() | |
// This: | |
// BulkInsertAll<CS_POSITION>(positions) | |
void BulkInsertAll<T>(List<T> entities) | |
{ | |
var connstr = Connection.ConnectionString; | |
var conn = new SqlConnection(Connection.ConnectionString); | |
conn.Open(); | |
Type t = typeof(T); | |
var tableAttribute = (TableAttribute)t.GetCustomAttributes(typeof(TableAttribute),false).Single(); | |
var bulkCopy = new SqlBulkCopy(conn) { DestinationTableName = tableAttribute.Name }; | |
var properties = t.GetMembers().Where(p=>{ | |
var columnAttribute = Attribute.GetCustomAttribute(p,typeof (ColumnAttribute)) as ColumnAttribute; | |
if (columnAttribute != null) return true; | |
return false; | |
}).ToArray(); | |
var table = new DataTable(); | |
foreach (var property in properties) | |
{ | |
Type propertyType = ((FieldInfo)property).FieldType; | |
if (propertyType.IsGenericType && propertyType.GetGenericTypeDefinition() == typeof(Nullable<>)) | |
{ | |
propertyType = Nullable.GetUnderlyingType(propertyType); | |
} | |
table.Columns.Add(new DataColumn(property.Name, propertyType)); | |
} | |
foreach (var entity in entities) | |
{ | |
var cols = properties.Select( property => { | |
var field = (FieldInfo) property; | |
var o = field.GetValue(entity); | |
if (o==null) | |
return DBNull.Value; | |
else | |
return o; | |
}).ToArray(); | |
table.Rows.Add(cols); | |
} | |
bulkCopy.WriteToServer(table); | |
conn.Close(); | |
} | |
Don't know if you're maintaining this at all - but I made a couple updates here.
I found that I needed to cast the existing connection (this.Connection) to a SqlConnection, rather than trying to open a new connection. I also found it easier to put it right next to the bulkCopy.Write...
Finally, I had to set the TimeOut to 0, as I was loading a TON of stuff into my table.
Anyhow, just thought you might like to see the end result.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This is awesome.