SqlBulkCopy from a List
Simply create a DataTable from your list of objects and call SqlBulkCopy.WriteToServer, passing the data table.
You might find the following useful:
- Adding columns to a DataTable. Add a column for each property/field you wish to write.
- Adding rows to a DataTable. Add a row for each object in your list.
For maximum performance with SqlBulkCopy, you should set an appropriate BatchSize. 10,000 seems to work well - but profile for your data.
You might also observe better results when using SqlBulkCopyOptions.TableLock.
An interesting and informative analysis of SqlBulkCopy performance can be found here.
Bulk Insert of Generic List C# into SQL Server
You may map your list to a datatable and then use SqlBulkCopy to insert all the rows at once.
Get an IDataReader from a typed List
Get the latest version from the code on this post
Nothing like code churn in plain sight: Here is a pretty complete implementation. You can instantiate an IDataReader over IList IEnumerable, IEnumerable (ergo IQueryable). There is no compelling reason to expose a generic type parameter on the reader and by omitting it, I can allow IEnumerable<'a> (anonymous types). See tests.
The source, less xmldocs, is short enough to include here with a couple tests. The rest of the source, with xmldocs, and tests is here under Salient.Data.
using System;
using System.Linq;
using NUnit.Framework;
namespace Salient.Data.Tests
{
[TestFixture]
public class EnumerableDataReaderEFFixture
{
[Test]
public void TestEnumerableDataReaderWithIQueryableOfAnonymousType()
{
var ctx = new NorthwindEntities();
var q =
ctx.Orders.Where(o => o.Customers.CustomerID == "VINET").Select(
o =>
new
{
o.OrderID,
o.OrderDate,
o.Customers.CustomerID,
Total =
o.Order_Details.Sum(
od => od.Quantity*((float) od.UnitPrice - ((float) od.UnitPrice*od.Discount)))
});
var r = new EnumerableDataReader(q);
while (r.Read())
{
var values = new object[4];
r.GetValues(values);
Console.WriteLine("{0} {1} {2} {3}", values);
}
}
}
}
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using NUnit.Framework;
namespace Salient.Data.Tests
{
public class DataObj
{
public string Name { get; set; }
public int Age { get; set; }
}
[TestFixture]
public class EnumerableDataReaderFixture
{
private static IEnumerable<DataObj> DataSource
{
get
{
return new List<DataObj>
{
new DataObj {Name = "1", Age = 16},
new DataObj {Name = "2", Age = 26},
new DataObj {Name = "3", Age = 36},
new DataObj {Name = "4", Age = 46}
};
}
}
[Test]
public void TestIEnumerableCtor()
{
var r = new EnumerableDataReader(DataSource, typeof(DataObj));
while (r.Read())
{
var values = new object[2];
int count = r.GetValues(values);
Assert.AreEqual(2, count);
values = new object[1];
count = r.GetValues(values);
Assert.AreEqual(1, count);
values = new object[3];
count = r.GetValues(values);
Assert.AreEqual(2, count);
Assert.IsInstanceOf(typeof(string), r.GetValue(0));
Assert.IsInstanceOf(typeof(int), r.GetValue(1));
Console.WriteLine("Name: {0}, Age: {1}", r.GetValue(0), r.GetValue(1));
}
}
[Test]
public void TestIEnumerableOfAnonymousType()
{
// create generic list
Func<Type, IList> toGenericList =
type => (IList)Activator.CreateInstance(typeof(List<>).MakeGenericType(new[] { type }));
// create generic list of anonymous type
IList listOfAnonymousType = toGenericList(new { Name = "1", Age = 16 }.GetType());
listOfAnonymousType.Add(new { Name = "1", Age = 16 });
listOfAnonymousType.Add(new { Name = "2", Age = 26 });
listOfAnonymousType.Add(new { Name = "3", Age = 36 });
listOfAnonymousType.Add(new { Name = "4", Age = 46 });
var r = new EnumerableDataReader(listOfAnonymousType);
while (r.Read())
{
var values = new object[2];
int count = r.GetValues(values);
Assert.AreEqual(2, count);
values = new object[1];
count = r.GetValues(values);
Assert.AreEqual(1, count);
values = new object[3];
count = r.GetValues(values);
Assert.AreEqual(2, count);
Assert.IsInstanceOf(typeof(string), r.GetValue(0));
Assert.IsInstanceOf(typeof(int), r.GetValue(1));
Console.WriteLine("Name: {0}, Age: {1}", r.GetValue(0), r.GetValue(1));
}
}
[Test]
public void TestIEnumerableOfTCtor()
{
var r = new EnumerableDataReader(DataSource);
while (r.Read())
{
var values = new object[2];
int count = r.GetValues(values);
Assert.AreEqual(2, count);
values = new object[1];
count = r.GetValues(values);
Assert.AreEqual(1, count);
values = new object[3];
count = r.GetValues(values);
Assert.AreEqual(2, count);
Assert.IsInstanceOf(typeof(string), r.GetValue(0));
Assert.IsInstanceOf(typeof(int), r.GetValue(1));
Console.WriteLine("Name: {0}, Age: {1}", r.GetValue(0), r.GetValue(1));
}
}
// remaining tests omitted for brevity
}
}
/*!
* Project: Salient.Data
* File : EnumerableDataReader.cs
* http://spikes.codeplex.com
*
* Copyright 2010, Sky Sanders
* Dual licensed under the MIT or GPL Version 2 licenses.
* See LICENSE.TXT
* Date: Sat Mar 28 2010
*/
using System;
using System.Collections;
using System.Collections.Generic;
namespace Salient.Data
{
/// <summary>
/// Creates an IDataReader over an instance of IEnumerable<> or IEnumerable.
/// Anonymous type arguments are acceptable.
/// </summary>
public class EnumerableDataReader : ObjectDataReader
{
private readonly IEnumerator _enumerator;
private readonly Type _type;
private object _current;
/// <summary>
/// Create an IDataReader over an instance of IEnumerable<>.
///
/// Note: anonymous type arguments are acceptable.
///
/// Use other constructor for IEnumerable.
/// </summary>
/// <param name="collection">IEnumerable<>. For IEnumerable use other constructor and specify type.</param>
public EnumerableDataReader(IEnumerable collection)
{
// THANKS DANIEL!
foreach (Type intface in collection.GetType().GetInterfaces())
{
if (intface.IsGenericType && intface.GetGenericTypeDefinition() == typeof (IEnumerable<>))
{
_type = intface.GetGenericArguments()[0];
}
}
if (_type ==null && collection.GetType().IsGenericType)
{
_type = collection.GetType().GetGenericArguments()[0];
}
if (_type == null )
{
throw new ArgumentException(
"collection must be IEnumerable<>. Use other constructor for IEnumerable and specify type");
}
SetFields(_type);
_enumerator = collection.GetEnumerator();
}
/// <summary>
/// Create an IDataReader over an instance of IEnumerable.
/// Use other constructor for IEnumerable<>
/// </summary>
/// <param name="collection"></param>
/// <param name="elementType"></param>
public EnumerableDataReader(IEnumerable collection, Type elementType)
: base(elementType)
{
_type = elementType;
_enumerator = collection.GetEnumerator();
}
/// <summary>
/// Helper method to create generic lists from anonymous type
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public static IList ToGenericList(Type type)
{
return (IList) Activator.CreateInstance(typeof (List<>).MakeGenericType(new[] {type}));
}
/// <summary>
/// Return the value of the specified field.
/// </summary>
/// <returns>
/// The <see cref="T:System.Object"/> which will contain the field value upon return.
/// </returns>
/// <param name="i">The index of the field to find.
/// </param><exception cref="T:System.IndexOutOfRangeException">The index passed was outside the range of 0 through <see cref="P:System.Data.IDataRecord.FieldCount"/>.
/// </exception><filterpriority>2</filterpriority>
public override object GetValue(int i)
{
if (i < 0 || i >= Fields.Count)
{
throw new IndexOutOfRangeException();
}
return Fields[i].Getter(_current);
}
/// <summary>
/// Advances the <see cref="T:System.Data.IDataReader"/> to the next record.
/// </summary>
/// <returns>
/// true if there are more rows; otherwise, false.
/// </returns>
/// <filterpriority>2</filterpriority>
public override bool Read()
{
bool returnValue = _enumerator.MoveNext();
_current = returnValue ? _enumerator.Current : _type.IsValueType ? Activator.CreateInstance(_type) : null;
return returnValue;
}
}
}
// <copyright project="Salient.Data" file="ObjectDataReader.cs" company="Sky Sanders">
// This source is a Public Domain Dedication.
// Please see http://spikes.codeplex.com/ for details.
// Attribution is appreciated
// </copyright>
// <version>1.0</version>
using System;
using System.Collections.Generic;
using System.Data;
using Salient.Reflection;
namespace Salient.Data
{
public abstract class ObjectDataReader : IDataReader
{
protected bool Closed;
protected IList<DynamicProperties.Property> Fields;
protected ObjectDataReader()
{
}
protected ObjectDataReader(Type elementType)
{
SetFields(elementType);
Closed = false;
}
#region IDataReader Members
public abstract object GetValue(int i);
public abstract bool Read();
#endregion
#region Implementation of IDataRecord
public int FieldCount
{
get { return Fields.Count; }
}
public virtual int GetOrdinal(string name)
{
for (int i = 0; i < Fields.Count; i++)
{
if (Fields[i].Info.Name == name)
{
return i;
}
}
throw new IndexOutOfRangeException("name");
}
object IDataRecord.this[int i]
{
get { return GetValue(i); }
}
public virtual bool GetBoolean(int i)
{
return (Boolean) GetValue(i);
}
public virtual byte GetByte(int i)
{
return (Byte) GetValue(i);
}
public virtual char GetChar(int i)
{
return (Char) GetValue(i);
}
public virtual DateTime GetDateTime(int i)
{
return (DateTime) GetValue(i);
}
public virtual decimal GetDecimal(int i)
{
return (Decimal) GetValue(i);
}
public virtual double GetDouble(int i)
{
return (Double) GetValue(i);
}
public virtual Type GetFieldType(int i)
{
return Fields[i].Info.PropertyType;
}
public virtual float GetFloat(int i)
{
return (float) GetValue(i);
}
public virtual Guid GetGuid(int i)
{
return (Guid) GetValue(i);
}
public virtual short GetInt16(int i)
{
return (Int16) GetValue(i);
}
public virtual int GetInt32(int i)
{
return (Int32) GetValue(i);
}
public virtual long GetInt64(int i)
{
return (Int64) GetValue(i);
}
public virtual string GetString(int i)
{
return (string) GetValue(i);
}
public virtual bool IsDBNull(int i)
{
return GetValue(i) == null;
}
object IDataRecord.this[string name]
{
get { return GetValue(GetOrdinal(name)); }
}
public virtual string GetDataTypeName(int i)
{
return GetFieldType(i).Name;
}
public virtual string GetName(int i)
{
if (i < 0 || i >= Fields.Count)
{
throw new IndexOutOfRangeException("name");
}
return Fields[i].Info.Name;
}
public virtual int GetValues(object[] values)
{
int i = 0;
for (; i < Fields.Count; i++)
{
if (values.Length <= i)
{
return i;
}
values[i] = GetValue(i);
}
return i;
}
public virtual IDataReader GetData(int i)
{
// need to think about this one
throw new NotImplementedException();
}
public virtual long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length)
{
// need to keep track of the bytes got for each record - more work than i want to do right now
// http://msdn.microsoft.com/en-us/library/system.data.idatarecord.getbytes.aspx
throw new NotImplementedException();
}
public virtual long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length)
{
// need to keep track of the bytes got for each record - more work than i want to do right now
// http://msdn.microsoft.com/en-us/library/system.data.idatarecord.getchars.aspx
throw new NotImplementedException();
}
#endregion
#region Implementation of IDataReader
public virtual void Close()
{
Closed = true;
}
public virtual DataTable GetSchemaTable()
{
var dt = new DataTable();
foreach (DynamicProperties.Property field in Fields)
{
dt.Columns.Add(new DataColumn(field.Info.Name, field.Info.PropertyType));
}
return dt;
}
public virtual bool NextResult()
{
throw new NotImplementedException();
}
public virtual int Depth
{
get { throw new NotImplementedException(); }
}
public virtual bool IsClosed
{
get { return Closed; }
}
public virtual int RecordsAffected
{
get
{
// assuming select only?
return -1;
}
}
#endregion
#region Implementation of IDisposable
public virtual void Dispose()
{
Fields = null;
}
#endregion
protected void SetFields(Type elementType)
{
Fields = DynamicProperties.CreatePropertyMethods(elementType);
}
}
}
// <copyright project="Salient.Reflection" file="DynamicProperties.cs" company="Sky Sanders">
// This source is a Public Domain Dedication.
// Please see http://spikes.codeplex.com/ for details.
// Attribution is appreciated
// </copyright>
// <version>1.0</version>
using System;
using System.Collections.Generic;
using System.Reflection;
using System.Reflection.Emit;
namespace Salient.Reflection
{
/// <summary>
/// Gets IL setters and getters for a property.
///
/// started with http://jachman.wordpress.com/2006/08/22/2000-faster-using-dynamic-method-calls/
/// </summary>
public static class DynamicProperties
{
#region Delegates
public delegate object GenericGetter(object target);
public delegate void GenericSetter(object target, object value);
#endregion
public static IList<Property> CreatePropertyMethods(Type T)
{
var returnValue = new List<Property>();
foreach (PropertyInfo prop in T.GetProperties())
{
returnValue.Add(new Property(prop));
}
return returnValue;
}
public static IList<Property> CreatePropertyMethods<T>()
{
var returnValue = new List<Property>();
foreach (PropertyInfo prop in typeof (T).GetProperties())
{
returnValue.Add(new Property(prop));
}
return returnValue;
}
/// <summary>
/// Creates a dynamic setter for the property
/// </summary>
/// <param name="propertyInfo"></param>
/// <returns></returns>
public static GenericSetter CreateSetMethod(PropertyInfo propertyInfo)
{
/*
* If there's no setter return null
*/
MethodInfo setMethod = propertyInfo.GetSetMethod();
if (setMethod == null)
return null;
/*
* Create the dynamic method
*/
var arguments = new Type[2];
arguments[0] = arguments[1] = typeof (object);
var setter = new DynamicMethod(
String.Concat("_Set", propertyInfo.Name, "_"),
typeof (void), arguments, propertyInfo.DeclaringType);
ILGenerator generator = setter.GetILGenerator();
generator.Emit(OpCodes.Ldarg_0);
generator.Emit(OpCodes.Castclass, propertyInfo.DeclaringType);
generator.Emit(OpCodes.Ldarg_1);
if (propertyInfo.PropertyType.IsClass)
generator.Emit(OpCodes.Castclass, propertyInfo.PropertyType);
else
generator.Emit(OpCodes.Unbox_Any, propertyInfo.PropertyType);
generator.EmitCall(OpCodes.Callvirt, setMethod, null);
generator.Emit(OpCodes.Ret);
/*
* Create the delegate and return it
*/
return (GenericSetter) setter.CreateDelegate(typeof (GenericSetter));
}
/// <summary>
/// Creates a dynamic getter for the property
/// </summary>
/// <param name="propertyInfo"></param>
/// <returns></returns>
public static GenericGetter CreateGetMethod(PropertyInfo propertyInfo)
{
/*
* If there's no getter return null
*/
MethodInfo getMethod = propertyInfo.GetGetMethod();
if (getMethod == null)
return null;
/*
* Create the dynamic method
*/
var arguments = new Type[1];
arguments[0] = typeof (object);
var getter = new DynamicMethod(
String.Concat("_Get", propertyInfo.Name, "_"),
typeof (object), arguments, propertyInfo.DeclaringType);
ILGenerator generator = getter.GetILGenerator();
generator.DeclareLocal(typeof (object));
generator.Emit(OpCodes.Ldarg_0);
generator.Emit(OpCodes.Castclass, propertyInfo.DeclaringType);
generator.EmitCall(OpCodes.Callvirt, getMethod, null);
if (!propertyInfo.PropertyType.IsClass)
generator.Emit(OpCodes.Box, propertyInfo.PropertyType);
generator.Emit(OpCodes.Ret);
/*
* Create the delegate and return it
*/
return (GenericGetter) getter.CreateDelegate(typeof (GenericGetter));
}
#region Nested type: Property
public class Property
{
public GenericGetter Getter;
public PropertyInfo Info;
public GenericSetter Setter;
public Property(PropertyInfo info)
{
Info = info;
Setter = CreateSetMethod(info);
Getter = CreateGetMethod(info);
}
}
#endregion
///// <summary>
///// An expression based Getter getter found in comments. untested.
///// Q: i don't see a reciprocal setter expression?
///// </summary>
///// <typeparam name="T"></typeparam>
///// <param name="propName"></param>
///// <returns></returns>
//public static Func<T> CreateGetPropValue<T>(string propName)
//{
// var param = Expression.Parameter(typeof(object), "container");
// var func = Expression.Lambda(
// Expression.Convert(Expression.PropertyOrField(Expression.Convert(param, typeof(T)), propName), typeof(object)), param);
// return (Func<T>)func.Compile();
//}
}
}
SqlBulkCopy - The given value of type String from the data source cannot be converted to type money of the specified target column
@Corey - It just simply strips out all invalid characters. However, your comment made me think of the answer.
The problem was that many of the fields in my database are nullable. When using SqlBulkCopy, an empty string is not inserted as a null value. So in the case of my fields that are not varchar (bit, int, decimal, datetime, etc) it was trying to insert an empty string, which obviously is not valid for that data type.
The solution was to modify my loop where I validate the values to this (repeated for each datatype that is not string)
//--- convert decimal values
foreach (DataColumn DecCol in DecimalColumns)
{
if(string.IsNullOrEmpty(dr[DecCol].ToString()))
dr[DecCol] = null; //--- this had to be set to null, not empty
else
dr[DecCol] = Helpers.CleanDecimal(dr[DecCol].ToString());
}
After making the adjustments above, everything inserts without issues.
Bulk Update in C#
What I've done before is perform a bulk insert from the data into a temp table, and then use a command or stored procedure to update the data relating the temp table with the destination table. The temp table is an extra step, but you can have a performance gain with the bulk insert and massive update if the amount of rows is big, compared to updating the data row by row.
Example:
public static void UpdateData<T>(List<T> list,string TableName)
{
DataTable dt = new DataTable("MyTable");
dt = ConvertToDataTable(list);
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SchoolSoulDataEntitiesForReport"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("", conn))
{
try
{
conn.Open();
//Creating temp table on database
command.CommandText = "CREATE TABLE #TmpTable(...)";
command.ExecuteNonQuery();
//Bulk insert into temp table
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
{
bulkcopy.BulkCopyTimeout = 660;
bulkcopy.DestinationTableName = "#TmpTable";
bulkcopy.WriteToServer(dt);
bulkcopy.Close();
}
// Updating destination table, and dropping temp table
command.CommandTimeout = 300;
command.CommandText = "UPDATE T SET ... FROM " + TableName + " T INNER JOIN #TmpTable Temp ON ...; DROP TABLE #TmpTable;";
command.ExecuteNonQuery();
}
catch (Exception ex)
{
// Handle exception properly
}
finally
{
conn.Close();
}
}
}
}
Notice that a single connection is used to perform the whole operation, in order to be able to use the temp table in each step, because the scope of the temp table is per connection.
SqlBulkCopy WriteToServer method not writing any data when using datatable
I've tried your code and it successfully copies tables for me!
In order to get the SqlRowsCopied event to fire, you need to set bcp.NotifyAfter to some > 0 value.
As for why you're not seeing values, I'm not exactly sure. If the DB or tables aren't there, you will get an exception (or, at least, I did). One difference in my code is that I commented out DropAndRecreateDB() and, when I hit that point in the debugger, I ran a drop-create script manually in SQL and verified that the tables were present.
Since your actual copy code works fine for me as you've posted it, I would double check to make sure your connection strings are what you think they are. If you could post that information, it'd be easier to continue tracking down.
Update:
FWIW, here is my drop/create script:
USE [master];
ALTER DATABASE MyTestDB2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE MyTestDB2;
GO
CREATE DATABASE MyTestDB2;
GO
USE [MyTestDB2];
CREATE TABLE [dbo].[tblPetTypes](
[commonname] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED ([commonname])
)
CREATE TABLE [dbo].[tblPeople](
[oid] [int] IDENTITY(1,1) NOT NULL,
[firstname] [nvarchar](30) NOT NULL,
[lastname] [nvarchar](30) NOT NULL,
[phone] [nvarchar](30) NULL,
PRIMARY KEY CLUSTERED ([oid])
)
CREATE TABLE [dbo].[tblPets](
[oid] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NOT NULL,
[pettype] [nvarchar](50) NULL,
[ownerid] [int] NULL,
PRIMARY KEY CLUSTERED ([oid])
) ON [PRIMARY]
...and I copied from MyTestDB to MyTestDB2 on the same server.
Related Topics
What Is the Point of Lookup<Tkey, Telement>
Using ASP.NET Identity Database First Approach
How to Provide Custom Cast Support for My Class
Find Nth Occurrence of a Character in a String
How to Serialize an Exception Object in C#
Obtain Sha-256 String of a String
The Entity Type Applicationuser Is Not Part of the Model for the Current Context
Request Windows Vista Uac Elevation If Path Is Protected
How to Implement Inotifypropertychanged in Xamarin.Forms
Differences Between Iqueryable, List, Ienumerator
Converting from Ienumerable to List
How Does Transactionscope Roll Back Transactions
How to Select an Option from Drop Down Using Selenium Webdriver C#