原文地址:http://www.csvreader.com/csv_downloads.php 或
使用场景:
项目中通过业务逻辑生产了多个大List<T>对象,需要使用bcp方式保存到数据库,有两种方式:
1.把List<T>转成DataTable,然后保存。确定就是内存占用很多。
2.实现一个IDataReader对象,解决内存增长问题。
通用的转换代码如下:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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 }
调用方法
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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 }
这个转换是按照反射后对象的属性顺序与数据库里的字段顺序对应,工作中可能这两个顺序不一致,所以我定义了另外的一种方式:
基类
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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 }
子类
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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 }
调用
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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 }