/// <summary>
/// 上传File
/// </summary>
protected void UploadFile()
{
HttpFileCollection httpFiles = Request.Files;
string couponid = Request["couponid"];
HttpPostedFile httpFile = httpFiles["uploadFile"];
if (string.IsNullOrEmpty(couponid))
{
Response.Write("<script type='text/javascript' >parent.layer.close(parent.layer.index); parent.layer.alert('参数丢失,请刷新重试!');</script>");
return;
}
if (httpFile != null && httpFile.ContentLength > 0)
{
System.IO.StreamReader sr = new System.IO.StreamReader(httpFile.InputStream);
string strLine = "";
string batchNumber = DateTime.Now.ToString("yyyyMMddHHmmssfff");
int importNum = 0;
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add("CouponID", typeof(int));//为新的Datatable添加一个新的列名
dt.Columns.Add("VoucherCode", typeof(string));//为新的Datatable添加一个新的列名
dt.Columns.Add("BatchNumber", typeof(string));//为新的Datatable添加一个新的列名
while (!string.IsNullOrEmpty((strLine = sr.ReadLine())))
{
System.Data.DataRow row = dt.NewRow(); //创建一个行
row["CouponID"] = couponid.ToInt(); //从总的Datatable中读取行数据赋值给新的Datatable
row["VoucherCode"] = strLine;
row["BatchNumber"] = batchNumber;
dt.Rows.Add(row);//添加次行
}
var importSuccess = MySqlBulkCopy(dt, "Trades_CouponLists_VoucherCode");
// 去重
string sqlStr = @"WITH TEST AS
(
SELECT ROW_NUMBER()
OVER(PARTITION BY CouponID,VoucherCode ORDER BY ID )
AS NUM,* FROM Trades_CouponLists_VoucherCode
)
DELETE FROM TEST
WHERE NUM != 1";
int deleteNum = sqlStr.ExecuteNonQuery();
string str = "";
if (deleteNum > 0)
{
str = "存在【" + deleteNum + "】张重复券码!";
}
Response.Write("<script type='text/javascript' >parent.layer.close(parent.layer.index);parent.layer.alert('" + str + "导入【" + (dt.Rows.Count - deleteNum) + "】张券码!');</script>");
return;
}
else
{
Response.Write("<script type='text/javascript' >parent.layer.close(parent.layer.index);parent.layer.alert('请上传券码券文件!');</script>");
return;
}
}
/// <summary>
/// DataTable批量添加(有事务)
/// </summary>
/// <param name="Table">数据源</param>
/// <param name="DestinationTableName">目标表即需要插入数据的数据表名称如"User_1"</param>
public static bool MySqlBulkCopy(System.Data.DataTable Table, string DestinationTableName)
{
bool Bool = true;
string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SqlConnString"].ConnectionString;
using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(ConnectionString))
{
con.Open();
using (System.Data.SqlClient.SqlTransaction Tran = con.BeginTransaction())//应用事物
{
using (System.Data.SqlClient.SqlBulkCopy Copy = new System.Data.SqlClient.SqlBulkCopy(con, System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity, Tran))
{
Copy.DestinationTableName = DestinationTableName;//指定目标表
System.Data.SqlClient.SqlBulkCopyColumnMapping[] Mapping = new System.Data.SqlClient.SqlBulkCopyColumnMapping[3];
Mapping[0] = new System.Data.SqlClient.SqlBulkCopyColumnMapping("CouponID", "CouponID");
Mapping[1] = new System.Data.SqlClient.SqlBulkCopyColumnMapping("VoucherCode", "VoucherCode");
Mapping[2] = new System.Data.SqlClient.SqlBulkCopyColumnMapping("BatchNumber", "BatchNumber");
if (Mapping != null)
{
//如果有数据
foreach (System.Data.SqlClient.SqlBulkCopyColumnMapping Map in Mapping)
{
Copy.ColumnMappings.Add(Map);
}
}
try
{
Copy.WriteToServer(Table);//批量添加
Tran.Commit();//提交事务
}
catch
{
Tran.Rollback();//回滚事务
Bool = false;
}
}
}
}
return Bool;
}