public bool AddDataTableToDB(DataTable source,string tableName)
{
SqlTransaction tran = null;//声明一个事务对象
try
{
using (SqlConnection conn = new SqlConnection(@"server=.;uid=sa;pwd=123456;database=UH1;"))
{
conn.Open();//打开链接
using (tran = conn.BeginTransaction())
{
using (SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran))
{
copy.DestinationTableName = tableName; //指定服务器上目标表的名称
#region 进行字段映射
if (tableName == "SYS_ClassRoom")
{
copy.ColumnMappings.Add("ClassRoomNo", "ClassRoomNo");
copy.ColumnMappings.Add("ClassRoomName", "ClassRoomName");
copy.ColumnMappings.Add("ClassID", "ClassID");
copy.ColumnMappings.Add("ClassName", "ClassName");
copy.ColumnMappings.Add("UserID", "UserID");
copy.ColumnMappings.Add("LoginName", "LoginName");
copy.ColumnMappings.Add("ClassRoomAdd", "ClassRoomAdd");
copy.ColumnMappings.Add("ClassRoomType", "ClassRoomType");
copy.ColumnMappings.Add("IsAccess", "IsAccess");
copy.ColumnMappings.Add("Capacity", "Capacity");
copy.ColumnMappings.Add("SecurityLeve", "SecurityLeve");
copy.ColumnMappings.Add("XCount", "XCount");
copy.ColumnMappings.Add("ycount", "ycount");
copy.ColumnMappings.Add("CreateStaff", "CreateStaff");
copy.ColumnMappings.Add("CreateDate", "CreateDate");
}
else if (tableName == "")
{
}
#endregion
copy.WriteToServer(source); //执行把DataTable中的数据写入DB
tran.Commit(); //提交事务
return true; //返回True 执行成功!
}
}
}
}
catch (Exception ex)
{
if (null != tran)
tran.Rollback();
return false;//返回False 执行失败!
}
{
SqlTransaction tran = null;//声明一个事务对象
try
{
using (SqlConnection conn = new SqlConnection(@"server=.;uid=sa;pwd=123456;database=UH1;"))
{
conn.Open();//打开链接
using (tran = conn.BeginTransaction())
{
using (SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran))
{
copy.DestinationTableName = tableName; //指定服务器上目标表的名称
#region 进行字段映射
if (tableName == "SYS_ClassRoom")
{
copy.ColumnMappings.Add("ClassRoomNo", "ClassRoomNo");
copy.ColumnMappings.Add("ClassRoomName", "ClassRoomName");
copy.ColumnMappings.Add("ClassID", "ClassID");
copy.ColumnMappings.Add("ClassName", "ClassName");
copy.ColumnMappings.Add("UserID", "UserID");
copy.ColumnMappings.Add("LoginName", "LoginName");
copy.ColumnMappings.Add("ClassRoomAdd", "ClassRoomAdd");
copy.ColumnMappings.Add("ClassRoomType", "ClassRoomType");
copy.ColumnMappings.Add("IsAccess", "IsAccess");
copy.ColumnMappings.Add("Capacity", "Capacity");
copy.ColumnMappings.Add("SecurityLeve", "SecurityLeve");
copy.ColumnMappings.Add("XCount", "XCount");
copy.ColumnMappings.Add("ycount", "ycount");
copy.ColumnMappings.Add("CreateStaff", "CreateStaff");
copy.ColumnMappings.Add("CreateDate", "CreateDate");
}
else if (tableName == "")
{
}
#endregion
copy.WriteToServer(source); //执行把DataTable中的数据写入DB
tran.Commit(); //提交事务
return true; //返回True 执行成功!
}
}
}
}
catch (Exception ex)
{
if (null != tran)
tran.Rollback();
return false;//返回False 执行失败!
}
}
其中#region标注处不能删除,因为当字段中有smalldatetime等属性时会出错。(我就是smalldatetime影响,其他属性会不会影响不知道)