List<T> To IDatareader For SqlBulkCopy

原文地址：http://www.csvreader.com/csv_downloads.php 或

　　　　　http://technico.qnownow.com/custom-data-reader-to-bulk-copy-data-from-object-collection-to-sql-server/

使用场景：

　　项目中通过业务逻辑生产了多个大List<T>对象，需要使用bcp方式保存到数据库，有两种方式：

1.把List<T>转成DataTable，然后保存。确定就是内存占用很多。

2.实现一个IDataReader对象，解决内存增长问题。

通用的转换代码如下：

  1 public class GenericListDataReader<T> : IDataReader
  2     {
  3         private IEnumerator<T> list = null;
  4         private List<PropertyInfo> properties = new List<PropertyInfo>();
  5 
  6         public GenericListDataReader(IEnumerable<T> list)
  7         {
  8             this.list = list.GetEnumerator();
  9             foreach (PropertyInfo property in typeof(T).GetProperties(
 10                 BindingFlags.GetProperty |
 11                 BindingFlags.Instance |
 12                 BindingFlags.Public))
 13             {
 14                 if (
 15                     property.PropertyType.IsPrimitive ||
 16                     property.PropertyType == typeof(string) ||
 17                     property.PropertyType == typeof(DateTime)
 18                     )
 19                 {
 20                     properties.Add(property);
 21                 }
 22             }
 23         }
 24 
 25         #region IDataReader Members
 26 
 27         public void Close()
 28         {
 29             list.Dispose();
 30         }
 31 
 32         public int Depth
 33         {
 34             get { throw new NotImplementedException(); }
 35         }
 36 
 37         public DataTable GetSchemaTable()
 38         {
 39             throw new NotImplementedException();
 40         }
 41 
 42         public bool IsClosed
 43         {
 44             get { throw new NotImplementedException(); }
 45         }
 46 
 47         public bool NextResult()
 48         {
 49             throw new NotImplementedException();
 50         }
 51 
 52         public bool Read()
 53         {
 54             return list.MoveNext();
 55         }
 56 
 57         public int RecordsAffected
 58         {
 59             get { throw new NotImplementedException(); }
 60         }
 61 
 62         #endregion
 63 
 64         #region IDisposable Members
 65 
 66         public void Dispose()
 67         {
 68             Close();
 69         }
 70 
 71         #endregion
 72 
 73         #region IDataRecord Members
 74 
 75         public int FieldCount
 76         {
 77             get { return properties.Count; }
 78         }
 79 
 80         public bool GetBoolean(int i)
 81         {
 82             throw new NotImplementedException();
 83         }
 84 
 85         public byte GetByte(int i)
 86         {
 87             throw new NotImplementedException();
 88         }
 89 
 90         public long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length)
 91         {
 92             throw new NotImplementedException();
 93         }
 94 
 95         public char GetChar(int i)
 96         {
 97             throw new NotImplementedException();
 98         }
 99 
100         public long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length)
101         {
102             throw new NotImplementedException();
103         }
104 
105         public IDataReader GetData(int i)
106         {
107             throw new NotImplementedException();
108         }
109 
110         public string GetDataTypeName(int i)
111         {
112             throw new NotImplementedException();
113         }
114 
115         public DateTime GetDateTime(int i)
116         {
117             throw new NotImplementedException();
118         }
119 
120         public decimal GetDecimal(int i)
121         {
122             throw new NotImplementedException();
123         }
124 
125         public double GetDouble(int i)
126         {
127             throw new NotImplementedException();
128         }
129 
130         public Type GetFieldType(int i)
131         {
132             return properties[i].PropertyType;
133         }
134 
135         public float GetFloat(int i)
136         {
137             throw new NotImplementedException();
138         }
139 
140         public Guid GetGuid(int i)
141         {
142             throw new NotImplementedException();
143         }
144 
145         public short GetInt16(int i)
146         {
147             throw new NotImplementedException();
148         }
149 
150         public int GetInt32(int i)
151         {
152             throw new NotImplementedException();
153         }
154 
155         public long GetInt64(int i)
156         {
157             throw new NotImplementedException();
158         }
159 
160         public string GetName(int i)
161         {
162             return properties[i].Name;
163         }
164 
165         public int GetOrdinal(string name)
166         {
167             throw new NotImplementedException();
168         }
169 
170         public string GetString(int i)
171         {
172             throw new NotImplementedException();
173         }
174 
175         public object GetValue(int i)
176         {
177             return properties[i].GetValue(list.Current, null);
178         }
179 
180         public int GetValues(object[] values)
181         {
182             throw new NotImplementedException();
183         }
184 
185         public bool IsDBNull(int i)
186         {
187             throw new NotImplementedException();
188         }
189 
190         public object this[string name]
191         {
192             get { throw new NotImplementedException(); }
193         }
194 
195         public object this[int i]
196         {
197             get { throw new NotImplementedException(); }
198         }
199 
200         #endregion
201     }

View Code

调用方法

 1 using (GenericListDataReader<Candidate> reader = new GenericListDataReader<Candidate>(candidates))
 2 using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.Database))
 3 using (SqlBulkCopy bcp = new SqlBulkCopy(conn))
 4 {
 5     conn.Open();
 6 
 7     bcp.DestinationTableName = "candidates";
 8 
 9     string createTableSql = "";
10 
11     createTableSql += "IF EXISTS(SELECT * FROM sys.tables t WHERE t.name = '" 
12         + bcp.DestinationTableName + "') DROP TABLE " + bcp.DestinationTableName + ";";
13     createTableSql += "CREATE TABLE dbo." + bcp.DestinationTableName + "(";
14 
15     for (int column = 0; column < reader.FieldCount; column++)
16     {
17         if (column > 0)
18         {
19             createTableSql += ",";
20         }
21 
22         createTableSql += "[" + reader.GetName(column) + "]" + " VARCHAR(MAX) NULL";
23     }
24 
25     createTableSql += ");";
26 
27     using (SqlCommand createTable = new SqlCommand(createTableSql, conn))
28     {
29         createTable.ExecuteNonQuery();
30     }
31 
32     bcp.WriteToServer(reader);
33 }

View Code

这个转换是按照反射后对象的属性顺序与数据库里的字段顺序对应，工作中可能这两个顺序不一致，所以我定义了另外的一种方式：

基类

  1   public class ColumnInfo
  2     {
  3         public string ColumnName { get; set; }
  4 
  5         public Type ColumnType { get; set; }
  6 
  7         public Func<object> GetValueFunc { get; set; }
  8 
  9         public ColumnInfo(string name, Type type, Func<object> func)
 10         {
 11             this.ColumnName = name;
 12             this.ColumnType = type;
 13             this.GetValueFunc = func;
 14         }
 15     }
 16 
 17     public abstract class ReportReaderBase<T> : IDataReader
 18     {
 19         public readonly IEnumerator<T> List;
 20         public abstract Dictionary<int, ColumnInfo> ColumnInfoDic { get; }
 21 
 22         protected StrategyReportReaderBase(List<T> list)
 23         {
 24             this.List = list.GetEnumerator();
 25             //if (this.List.Current == null)
 26             //    this.List.MoveNext();
 27         }
 28 
 29 
 30         #region IDataReader Members
 31 
 32         public void Close()
 33         {
 34             this.List.Dispose();
 35         }
 36 
 37         public int Depth
 38         {
 39             get { throw new NotImplementedException(); }
 40         }
 41 
 42         public DataTable GetSchemaTable()
 43         {
 44             throw new NotImplementedException();
 45         }
 46 
 47         public bool IsClosed
 48         {
 49             get { throw new NotImplementedException(); }
 50         }
 51 
 52         public bool NextResult()
 53         {
 54             throw new NotImplementedException();
 55         }
 56 
 57         public bool Read()
 58         {
 59             return this.List.MoveNext();
 60         }
 61 
 62         public int RecordsAffected
 63         {
 64             get { throw new NotImplementedException(); }
 65         }
 66 
 67         #endregion
 68 
 69         #region IDisposable Members
 70 
 71         public void Dispose()
 72         {
 73             Close();
 74         }
 75 
 76         #endregion
 77 
 78         #region IDataRecord Members
 79 
 80         public int FieldCount
 81         {
 82             get { return this.ColumnInfoDic.Count; }
 83         }
 84 
 85         public bool GetBoolean(int i)
 86         {
 87             throw new NotImplementedException();
 88         }
 89 
 90         public byte GetByte(int i)
 91         {
 92             throw new NotImplementedException();
 93         }
 94 
 95         public long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length)
 96         {
 97             throw new NotImplementedException();
 98         }
 99 
100         public char GetChar(int i)
101         {
102             throw new NotImplementedException();
103         }
104 
105         public long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length)
106         {
107             throw new NotImplementedException();
108         }
109 
110         public IDataReader GetData(int i)
111         {
112             throw new NotImplementedException();
113         }
114 
115         public string GetDataTypeName(int i)
116         {
117             throw new NotImplementedException();
118         }
119 
120         public DateTime GetDateTime(int i)
121         {
122             throw new NotImplementedException();
123         }
124 
125         public decimal GetDecimal(int i)
126         {
127             throw new NotImplementedException();
128         }
129 
130         public double GetDouble(int i)
131         {
132             throw new NotImplementedException();
133         }
134 
135         public Type GetFieldType(int i)
136         {
137             return this.ColumnInfoDic[i].ColumnType;
138         }
139 
140         public float GetFloat(int i)
141         {
142             throw new NotImplementedException();
143         }
144 
145         public Guid GetGuid(int i)
146         {
147             throw new NotImplementedException();
148         }
149 
150         public short GetInt16(int i)
151         {
152             throw new NotImplementedException();
153         }
154 
155         public int GetInt32(int i)
156         {
157             throw new NotImplementedException();
158         }
159 
160         public long GetInt64(int i)
161         {
162             throw new NotImplementedException();
163         }
164 
165         public string GetName(int i)
166         {
167             return this.ColumnInfoDic[i].ColumnName;
168         }
169 
170         public int GetOrdinal(string name)
171         {
172             throw new NotImplementedException();
173         }
174 
175         public string GetString(int i)
176         {
177             throw new NotImplementedException();
178         }
179 
180         public object GetValue(int i)
181         {
182             return this.ColumnInfoDic[i].GetValueFunc.Invoke();
183         }
184 
185         public int GetValues(object[] values)
186         {
187             throw new NotImplementedException();
188         }
189 
190         public bool IsDBNull(int i)
191         {
192             throw new NotImplementedException();
193         }
194 
195         public object this[string name]
196         {
197             get { throw new NotImplementedException(); }
198         }
199 
200         public object this[int i]
201         {
202             get { throw new NotImplementedException(); }
203         }
204 
205         #endregion
206     }

View Code

子类

 1    public class CalcBmReader :    ReportReaderBase<RptCalcBMEO>
 2     {
 3 
 4         private readonly Guid _reportID;
 5 
 6         public RptCalcBmReader(List<RptCalcBMEO> list, Guid reportID)
 7             : base(list)
 8         {
 9             this._reportID = reportID;
10         }
11 
12         public override Dictionary<int, ColumnInfo> ColumnInfoDic
13         {
14             get
15             {
16                 return new Dictionary<int, ColumnInfo>
17                 {
18                     {0, new ColumnInfo("RptCalcBMID",typeof(Guid), () => Guid.NewGuid())},
19                     {1,  new ColumnInfo("MFReportID",typeof(string), () => this._reportID)},
20                     {2,  new ColumnInfo("CalcDate",typeof(string),()=>this.List.Current.CalcDate)},
21                     {3,  new ColumnInfo("Type",typeof(int),()=>this.List.Current.Type)},
22                     {4,  new ColumnInfo("ReturnRate",typeof(decimal),()=>this.List.Current.ReturnRate)},
23                     {5,  new ColumnInfo("CumRR",typeof(decimal?), () => this.List.Current.CumRR.HasValue ? this.List.Current.CumRR : null)},
24                 };
25             }
26 
27         }
28     }

View Code

调用

1      using (var rptCalcBmReader = new RptFSubAdjDetailReader(factorAdj.RptFSubAdjDetails, reportID, rptFSubAdjID))
2                                 using (var bcp = db.CreateBcp(SqlBulkCopyOptions.Default))
3                                 {
4                                     bcp.DestinationTableName = _rptFSubAdjDetailTableName;
5                                     bcp.WriteToServer(rptCalcBmReader);
6                                 }

View Code