我有一个包含一百万个元素的List<MyObject>。 (它实际上是SubSonic集合，但未从数据库中加载)。
我当前正在使用 SqlBulkCopy ，如下所示:
private string FastInsertCollection(string tableName, DataTable tableData)
{
string sqlConn = ConfigurationManager.ConnectionStrings[SubSonicConfig.DefaultDataProvider.ConnectionStringName].ConnectionString;
using (SqlBulkCopy s = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.TableLock))
{
s.DestinationTableName = tableName;
s.BatchSize = 5000;
s.WriteToServer(tableData);
s.BulkCopyTimeout = SprocTimeout;
s.Close();
}
return sqlConn;
}
我使用SubSonic的MyObjectCollection.ToDataTable()从我的集合中构建DataTable。但是，这会复制内存中的对象并且效率很低。我想使用使用IDataReader而不是DataTable的SqlBulkCopy.WriteToServer方法，这样就不会在内存中复制我的集合。
从列表中获取IDataReader的最简单方法是什么？我想我可以实现一个自定义的数据读取器(例如http://blogs.microsoft.co.il/blogs/aviwortzel/archive/2008/05/06/implementing-sqlbulkcopy-in-linq-to-sql.aspx)，但是在不编写一堆通用代码的情况下，我可以做些更简单的事情。
编辑:
似乎无法轻易地从一组对象生成IDataReader。
即使我希望框架中内置一些内容，也要接受当前的答案。
最佳答案
从this post上的代码获取最新版本
显而易见，没有什么比代码搅动:这是一个非常完整的实现。您可以通过IList IEnumerable，IEnumerable(ergo IQueryable)实例化IDataReader。没有令人信服的理由在读取器上公开通用类型参数，并且通过省略它，我可以允许IEnumerable (匿名类型)。查看测试。
源代码(少了xmldocs)足够短，可以在此处进行一些测试。源代码的其余部分(带有xmldocs)和测试是Salient.Data下的here。
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();
//}
}
}
关于c# - 从类型列表中获取IDataReader，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/2258310/