本文介绍了从类型化列表中获取 IDataReader的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个包含一百万个元素的 List.(它实际上是一个 SubSonic Collection,但它不是从数据库中加载的).

I have a List<MyObject> with a million elements. (It is actually a SubSonic Collection but it is not loaded from the database).

我目前使用 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 方法,这样我就不会在内存中复制我的集合.

I use SubSonic's MyObjectCollection.ToDataTable() to build the DataTable from my collection. However, this duplicates objects in memory and is inefficient. I'd like to use the SqlBulkCopy.WriteToServer method that uses an IDataReader instead of a DataTable so that I don't duplicate my collection in memory.

从我的列表中获取 IDataReader 的最简单方法是什么?我想我可以实现一个自定义数据阅读器(就像这里 http://blogs.microsoft.co.il/blogs/aviwortzel/archive/2008/05/06/implementing-sqlbulkcopy-in-linq-to-sql.aspx) ,但肯定有一些更简单的事情我可以不用编写一堆通用代码.

What's the easiest way to get an IDataReader from my list? I suppose I could implement a custom data reader (like here http://blogs.microsoft.co.il/blogs/aviwortzel/archive/2008/05/06/implementing-sqlbulkcopy-in-linq-to-sql.aspx) , but there must be something simpler I can do without writing a bunch of generic code.

从一组对象中轻松生成 IDataReader 似乎并不容易.即使我希望框架中内置一些东西,也接受当前的答案.

It does not appear that one can easily generate an IDataReader from a collection of objects. Accepting current answer even though I was hoping for something built into the framework.

推荐答案

这篇文章

没有什么比代码改动更明显的了:这是一个非常完整的实现.您可以通过 IList IEnumerable、IEnumerable(ergo IQueryable)实例化 IDataReader.没有令人信服的理由在阅读器上公开泛型类型参数,通过省略它,我可以允许 IEnumerable(匿名类型).查看测试.

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.

源代码,较少的 xmldoc,足够短,可以在此处包含几个测试.其余的带有 xmldocs 和测试的源代码是这里 在 Salient.Data 下.

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&lt;> 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&lt;>.
        ///
        /// Note: anonymous type arguments are acceptable.
        ///
        /// Use other constructor for IEnumerable.
        /// </summary>
        /// <param name="collection">IEnumerable&lt;>. 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&lt;>
        /// </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();
        //}
    }
}

这篇关于从类型化列表中获取 IDataReader的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

05-16 21:29