当批插入时如果不手动设置对应关系,则是按照索引排序来进行字段对应的 即datatable中的第一列对应表中第一列,datatable中的第二列对应表中的第二列,现在把datable处理成和表的字段顺序和列明完全一致的结果,
/// <summary>
/// 给批插入的方法设置datable
/// </summary>
/// <param name="connectString"></param>
/// <param name="tableName"></param>
/// <param name="dt"></param>
/// <returns></returns>
private static DataTable GetBatchInsertDataTable(string connectString, string tableName, DataTable dt)
{
List<SpColumnsEntity> addafter = new List<SpColumnsEntity>();
if (dt == null || dt.Rows == null || dt.Rows.Count <= 0) return dt;
DataColumnCollection colum_dt_collection = dt.Columns;
List<SpColumnsEntity> columns_table = ProList<SpColumnsEntity>(connectString, "sp_columns", new { table_name = tableName });
if (columns_table == null || columns_table.Count == 0)
{
return dt;
}
//查找dt中是缺少列并进行补充
foreach (var colun_table in columns_table)
{
string columname = colun_table.Column_Name.ToLower();
bool ishas = false;
foreach (DataColumn colun_dt in dt.Columns)
{
if (colun_dt.ColumnName.ToLower() == columname)
{
ishas = true;
break;
}
}
if (!ishas)
{
SpColumnsEntity add = new SpColumnsEntity();
add.Column_Name = colun_table.Column_Name;
add.Ordinal_Position = columns_table.IndexOf(colun_table);
addafter.Add(add);
}
}
if (addafter != null && addafter.Count > 0)
{
foreach (var item in addafter)
{
dt.Columns.Add(item.Column_Name);
}
}
//给dt按照数据表的顺序进行排序
foreach (var colun_table in columns_table)
{
string columname = colun_table.Column_Name.ToLower();
foreach (DataColumn colun_dt in dt.Columns)
{
if (colun_dt.ColumnName.ToLower() == columname)
{
dt.Columns[colun_dt.ColumnName].SetOrdinal(colun_table.Ordinal_Position - 1);
break;
}
}
}
//移除DT中多余的列
if (dt.Columns.Count > columns_table.Count)
{
int delcount = dt.Columns.Count - columns_table.Count;
for (int i = 0; i < delcount; i++)
{
dt.Columns.RemoveAt(columns_table.Count);
}
}
return dt;
}
里边还用到一个类
public class SpColumnsEntity
{
/// <summary>
/// 列名
/// </summary>
public string Column_Name { set; get; }
/// <summary>
/// 列对应的位置
/// </summary>
public int Ordinal_Position { set; get; }
}