using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DataPro
{
public class ExecSqlBulkCopy
{
/// <summary>
/// 返回连接字符串
/// </summary>
/// <param name="Section"></param>
/// <param name="Key"></param>
/// <returns></returns>
private string GetClientPort()
{
try
{
ManageIni ini = new ManageIni();
ini.Filename = "SqlBase.ini";
ini.Section = "EXcuetCopyConn";
ini.Key = "ExconnCopyStr";
return ini.ReadIniValue();
}
catch (Exception ex)
{
return "";
}
}
/// <summary>
/// 添加表数据到其它表[两个表的表结构需要相同]
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="tableName">对象表名</param>
/// <returns></returns>
public bool AddDataTable(DataTable dt,string tableName)
{
bool status = false;
string connstring = GetClientPort();
SqlConnection sqlCon = new SqlConnection(connstring);
sqlCon.Open();
SqlTransaction sqlTran = sqlCon.BeginTransaction(); // 开始事务
SqlBulkCopy sqlBC = new SqlBulkCopy(sqlCon, SqlBulkCopyOptions.Default, sqlTran);
sqlBC.DestinationTableName = tableName;
sqlBC.BatchSize = dt.Rows.Count;
try
{
sqlBC.WriteToServer(dt); //此处报错
sqlTran.Commit();
status = true;
}
catch (Exception)
{
sqlTran.Rollback();
status = false;
}
finally
{
sqlBC.Close();
sqlCon.Close();
}
return status;
}
/// <summary>
/// 更新数据到对象表
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="tempTableName">临时表名</param>
/// <param name="orgTableName">对象表名</param>
/// <param name="updatesql">更新语句</param>
/// <returns></returns>
public bool Updatetable(DataTable dt, string tempTableName,string orgTableName,string updatesql)
{
bool status = false;
string connectionString = GetClientPort();
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand("", conn))
{
try
{
conn.Open();
//构建临时表
command.CommandText = " select * into " + tempTableName + " from " + orgTableName + " where 1=2";
int s = command.ExecuteNonQuery();
//插入临时表
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity))
{
bulkCopy.DestinationTableName = tempTableName;
bulkCopy.BatchSize = dt.Rows.Count;
if (dt != null && dt.Rows.Count != 0)
{
bulkCopy.WriteToServer(dt);
bulkCopy.Close();
}
}
//从临时表更新到原表,并删除临时表
command.CommandTimeout = 300;
command.CommandText = updatesql + " DROP TABLE " + tempTableName + ";";
command.ExecuteNonQuery();
status = true;
}
catch (Exception ex)
{
status = false;
}
finally
{
conn.Close();
}
}
return status;
}
}
/// <summary>
/// 添加表数据到其它表[两个表的表结构需要相同]
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="tableName">对象表名</param>
/// <returns></returns>
public bool AddDataTableCount(DataTable dt, string tableName,string connstring)
{
bool status = false;
//string connstring = GetClientPort();
SqlConnection sqlCon = new SqlConnection(connstring);
sqlCon.Open();
SqlTransaction sqlTran = sqlCon.BeginTransaction(); // 开始事务
SqlBulkCopy sqlBC = new SqlBulkCopy(sqlCon, SqlBulkCopyOptions.Default, sqlTran);
sqlBC.DestinationTableName = tableName;
sqlBC.BatchSize = dt.Rows.Count;
try
{
// 列名映射 bulkCopy.ColumnMappings.Add("源数据列", "目标表对应列名称");
//sqlBC.ColumnMappings.Add("a", "uname");
//sqlBC.ColumnMappings.Add("b", "usex");
//sqlBC.ColumnMappings.Add("c", "uage");
//sqlBC.ColumnMappings.Add("d", "utext");
sqlBC.WriteToServer(dt); //此处报错
sqlTran.Commit();
status = true;
}
catch (Exception ex)
{
sqlTran.Rollback();
status = false;
}
finally
{
sqlBC.Close();
sqlCon.Close();
}
return status;
}
/// <summary>
/// 更新数据到对象表
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="tempTableName">临时表名</param>
/// <param name="orgTableName">对象表名</param>
/// <param name="updatesql">更新语句</param>
/// <returns></returns>
public bool Updatetable(DataTable dt, string tempTableName, string orgTableName, string updatesql, string connstr)
{
bool status = false;
string connectionString = connstr;
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand("", conn))
{
try
{
conn.Open();
//构建临时表
command.CommandText = " IF OBJECT_ID('UserInfotemp','U') IS NOT NULL DROP TABLE " + tempTableName
+ " select * into " + tempTableName + " from " + orgTableName + " where 1 = 2 ";
command.ExecuteNonQuery();
//插入临时表
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity))
{
bulkCopy.DestinationTableName = tempTableName;
bulkCopy.BatchSize = dt.Rows.Count;
if (dt != null && dt.Rows.Count != 0)
{
bulkCopy.WriteToServer(dt);
bulkCopy.Close();
}
}
//从临时表更新到原表,并删除临时表
command.CommandTimeout = 300;
command.CommandText = updatesql + " DROP TABLE " + tempTableName + ";";
command.ExecuteNonQuery();
status = true;
}
catch (Exception ex) { status = false; }
finally { conn.Close(); }
}
return status;
}
}
}
}