我有一个方法，它接受IEnumerable，并循环遍历IEnumerable，并将每个方法插入数据库的表中。像这样：
public void Write(IEnumerable<foo> fooData)
{
for (var index = 0; index < fooData.Count(); index++)
{
var sql = @"insert into foo (col_id, col_name) values (@col_id, col_name)";
}
}
foo是一个反映数据库中表的类：
public class Foo
{
public int col_id {get;set;}
public string col_name {get;set;}
}
事实证明，如果我的行有成千上万的条目，使用for循环效率不高。当涉及IEnumerable时，复制所有这些数据的更有效方法是什么？
我编写了一个行为类似于Postgres的内置SqlBulkCopy类的类。它包装COPY命令以提供快速上载。IEnumerable的方法如下所示（的用法与此类似DataTable）。
public void WriteToServer<T>(IEnumerable<T> data)
{
try
{
if (DestinationTableName == null || DestinationTableName == "")
{
throw new ArgumentOutOfRangeException("DestinationTableName", "Destination table must be set");
}
PropertyInfo[] properties = typeof(T).GetProperties();
int colCount = properties.Length;
NpgsqlDbType[] types = new NpgsqlDbType[colCount];
int[] lengths = new int[colCount];
string[] fieldNames = new string[colCount];
using (var cmd = new NpgsqlCommand("SELECT * FROM " + DestinationTableName + " LIMIT 1", conn))
{
using (var rdr = cmd.ExecuteReader())
{
if (rdr.FieldCount != colCount)
{
throw new ArgumentOutOfRangeException("dataTable", "Column count in Destination Table does not match column count in source table.");
}
var columns = rdr.GetColumnSchema();
for (int i = 0; i < colCount; i++)
{
types[i] = (NpgsqlDbType)columns[i].NpgsqlDbType;
lengths[i] = columns[i].ColumnSize == null ? 0 : (int)columns[i].ColumnSize;
fieldNames[i] = columns[i].ColumnName;
}
}
}
var sB = new StringBuilder(fieldNames[0]);
for (int p = 1; p < colCount; p++)
{
sB.Append(", " + fieldNames[p]);
}
using (var writer = conn.BeginBinaryImport("COPY " + DestinationTableName + " (" + sB.ToString() + ") FROM STDIN (FORMAT BINARY)"))
{
foreach (var t in data)
{
writer.StartRow();
for (int i = 0; i < colCount; i++)
{
if (properties[i].GetValue(t) == null)
{
writer.WriteNull();
}
else
{
switch (types[i])
{
case NpgsqlDbType.Bigint:
writer.Write((long)properties[i].GetValue(t), types[i]);
break;
case NpgsqlDbType.Bit:
if (lengths[i] > 1)
{
writer.Write((byte[])properties[i].GetValue(t), types[i]);
}
else
{
writer.Write((byte)properties[i].GetValue(t), types[i]);
}
break;
case NpgsqlDbType.Boolean:
writer.Write((bool)properties[i].GetValue(t), types[i]);
break;
case NpgsqlDbType.Bytea:
writer.Write((byte[])properties[i].GetValue(t), types[i]);
break;
case NpgsqlDbType.Char:
if (properties[i].GetType() == typeof(string))
{
writer.Write((string)properties[i].GetValue(t), types[i]);
}
else if (properties[i].GetType() == typeof(Guid))
{
var value = properties[i].GetValue(t).ToString();
writer.Write(value, types[i]);
}
else if (lengths[i] > 1)
{
writer.Write((char[])properties[i].GetValue(t), types[i]);
}
else
{
var s = ((string)properties[i].GetValue(t).ToString()).ToCharArray();
writer.Write(s[0], types[i]);
}
break;
case NpgsqlDbType.Time:
case NpgsqlDbType.Timestamp:
case NpgsqlDbType.TimestampTz:
case NpgsqlDbType.Date:
writer.Write((DateTime)properties[i].GetValue(t), types[i]);
break;
case NpgsqlDbType.Double:
writer.Write((double)properties[i].GetValue(t), types[i]);
break;
case NpgsqlDbType.Integer:
try
{
if (properties[i].GetType() == typeof(int))
{
writer.Write((int)properties[i].GetValue(t), types[i]);
break;
}
else if (properties[i].GetType() == typeof(string))
{
var swap = Convert.ToInt32(properties[i].GetValue(t));
writer.Write((int)swap, types[i]);
break;
}
}
catch (Exception ex)
{
string sh = ex.Message;
}
writer.Write((object)properties[i].GetValue(t), types[i]);
break;
case NpgsqlDbType.Interval:
writer.Write((TimeSpan)properties[i].GetValue(t), types[i]);
break;
case NpgsqlDbType.Numeric:
case NpgsqlDbType.Money:
writer.Write((decimal)properties[i].GetValue(t), types[i]);
break;
case NpgsqlDbType.Real:
writer.Write((Single)properties[i].GetValue(t), types[i]);
break;
case NpgsqlDbType.Smallint:
try
{
if (properties[i].GetType() == typeof(byte))
{
var swap = Convert.ToInt16(properties[i].GetValue(t));
writer.Write((short)swap, types[i]);
break;
}
writer.Write((short)properties[i].GetValue(t), types[i]);
}
catch (Exception ex)
{
string ms = ex.Message;
}
break;
case NpgsqlDbType.Varchar:
case NpgsqlDbType.Text:
writer.Write((string)properties[i].GetValue(t), types[i]);
break;
case NpgsqlDbType.Uuid:
writer.Write((Guid)properties[i].GetValue(t), types[i]);
break;
case NpgsqlDbType.Xml:
writer.Write((string)properties[i].GetValue(t), types[i]);
break;
}
}
}
}
writer.Complete();
}
}
catch (Exception ex)
{
throw new Exception("Error executing NpgSqlBulkCopy.WriteToServer(). See inner exception for details", ex);
}
}
您需要首先设置属性DestinationTableName，并且conn必须是打开的连接。
本质上，该方法用于Reflection获取传递列表类型的属性。显然，要填充的表的数据类型必须匹配！编写器通过遍历列表来构建，然后在末尾进行一个批量插入。我可能没有处理您需要的所有类型，但是应该清楚如何添加它们，缺少它们的地方。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句