[size=x-large]此方法在自己最近写的这个程序中经常用到,感觉非常好用。尤其是将TreeView、Menu等,保存到数据库相对的表中时。将些记录下来,以供大家参考。[/size]
/// DataTable记录集导入到数据库表
/// </summary>
/// <param name="connString">数据库联结串</param>
/// <param name="SouseDataTable">要导入的数据DataTable</param>
/// <param name="DestTableName">目的表名</param>
/// <param name="IsAppendRow">是否追加记录</param>
/// <returns></returns>
public static bool DataTableInputDataBase(string connString, DataTable SouseDataTable, string DestTableName, bool IsAppendRow)
{
if (SouseDataTable.Rows.Count > 0)
{
if (IsAppendRow == false)
{
HR.AuditSystem.DBUtility.DbHelperSQL.ExecuteSql("delete from " + DestTableName);
}
SqlConnection SqlCnn = new SqlConnection(connString);
SqlCnn.Open();
DataTable NewTable = OrdinalTable(connString, SouseDataTable, DestTableName);
SqlBulkCopy Bcp = new SqlBulkCopy(SqlCnn);
Bcp.DestinationTableName = DestTableName;
Bcp.WriteToServer(NewTable);
Bcp.Close();
SqlCnn.Close();
}
SouseDataTable.Reset();
SouseDataTable.Dispose();
return true;
}
/// <summary>
/// 调整表字段顺序
/// </summary>
/// <param name="SqlConnstring">数据库联结串</param>
/// <param name="DTS">源DataTable</param>
/// <param name="DestTableName">目标表名</param>
/// <returns></returns>
private static DataTable OrdinalTable(string SqlConnstring, DataTable DTS, string DestTableName)
{
System.Data.SqlClient.SqlConnection SqldbCnn = new System.Data.SqlClient.SqlConnection(SqlConnstring);
DataTable DTD = new DataTable();
System.Data.SqlClient.SqlDataAdapter SqlDbDA = new System.Data.SqlClient.SqlDataAdapter("select top 1 * from " + DestTableName, SqldbCnn);
SqldbCnn.Open();
SqlDbDA.Fill(DTD);
SqlDbDA.Dispose();
SqldbCnn.Close();
DTD.Clear();
int I = 0;
int J = 0;
string CNS = "";
string CND = "";
int MaxCol = 0;
for (I = 0; I < DTS.Columns.Count; I++)
{
CNS = DTS.Columns[I].ColumnName;
for (J = 0; J <= DTD.Columns.Count - 1; J++)
{
CND = DTD.Columns[J].ColumnName;
if (CND.ToUpper() == CNS.ToUpper())
{
if (MaxCol < J)
{
MaxCol = J;
break;
}
}
}
}
for (J = 0; J <= MaxCol; J++)
{
if (DTS.Columns.IndexOf(DTD.Columns[J].ColumnName) < 0)
{
DTS.Columns.Add(DTD.Columns[J].ColumnName.ToUpper(), DTD.Columns[J].DataType);
}
}
for (I = 0; I <= DTD.Columns.Count - 1; I++)
{
CND = DTD.Columns[I].ColumnName;
for (J = 0; J <= DTS.Columns.Count - 1; J++)
{
CNS = DTS.Columns[J].ColumnName;
if (CNS.ToUpper() == CND.ToUpper())
{
DTS.Columns[J].SetOrdinal(I);
break;
}
}
}
return DTS;
}