110,534
社区成员
发帖
与我相关
我的任务
分享
/// <summary>
/// 检查是否有重复的码
/// </summary>
/// <param name="aadt">准备写入数据库的DataTable</param>
/// <param name="icodesum">码的总个数</param>
/// <param name="ilength">码的长度</param>
/// <param name="scode">码的关键字</param>
/// <returns>经过处理后,无重复码的List链表</returns>
private List<string> CheckWriteData(DataTable aadt,int icodesum,int ilength,string scode)
{
using (SqlConnection conn = new SqlConnection())
{
List<string> value = new List<string>(icodesum);
SqlCommand cmd = new SqlCommand();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["fwwlConnectionString"].ConnectionString;
conn.Open();
try
{
//首先删除临时表的记录
cmd.CommandTimeout = 600;
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Delete TmpCode";
cmd.ExecuteNonQuery();
//把所生的码写入临时表(可能会有复制的码)
using (SqlBulkCopy bcp = new SqlBulkCopy(conn))
{
bcp.BatchSize = aadt.Rows.Count % 100;
bcp.BulkCopyTimeout = 3600;
bcp.DestinationTableName = "TmpCode";
bcp.WriteToServer(aadt);
}
using (DataTable dt = new DataTable ())
{
//把重复的码去掉,并补齐个数
cmd.CommandTimeout = 3600;
cmd.CommandText = "Select DISTINCT code from TmpCode";
SqlDataAdapter sdr = new SqlDataAdapter (cmd);
sdr.Fill(dt);
//检查有多少个重复的码,并用循环补足个数
if (dt.Rows.Count < icodesum)
{
int i = icodesum - dt.Rows.Count;
for (int m = 0; m < i; m++)
{
DataRow dr = dt.NewRow();
dr["code"] = Code(ilength, scode, m).ToString(); //生成新的码
dt.Rows.Add(dr);
}
}
//把DataTable中的数据,存入List中,准备返回
for (int m = 0; m < dt.Rows.Count; m++)
{
value.Add(dt.Rows[m]["code"].ToString());
}
dt.Dispose();
}
return value;
}
catch (Exception ex)
{
FileLog.InsertXml(ex.Message);
return value;
}
}
}
CREATE PROCEDURE [dbo].[CheckDataDistinct]
@tableName nvarchar(30), --表名
@sumCount int, --码的个数
@ilength_fw int, --防伪码的长度
@ilength_wl int, --物流码的长度
@scode nvarchar(10) --码的关键字
AS
declare @strSql nvarchar(300) --存储准备执行的SQL语句
declare @tableCount int
declare @tmpCode_fw nvarchar(30) --临时存放新生成的码
declare @tmpCode_wl nvarchar(30) --临时存放新生成的码
--从大到小处理物流码
Create Table #MyTmpeTable(id int,code_fw nvarchar(30),code_wl nvarchar(30),
flag_wl int, select_Sum_Count int)
Set @strSql = '
Insert Into #MyTmpeTable
Select a.*
from '+ @tableName + ' a where
id = (select min(id) from '+@tableName+' where code_wl = a.code_wl) order by a.id'
exec(@strSql)--去掉物流码的所有重复值
Select @tableCount = Count(id) From #MyTmpeTable --去掉重复值后还剩于的码个数
while @tableCount < @sumCount --补齐码的个数
begin
Set @tmpCode_fw = dbo.Code(@ilength_fw,@scode,@tableCount) --生成新码
Set @tmpCode_wl = dbo.Code(@ilength_wl,@scode,@tableCount) --生成新码
Insert into #MyTmpeTable(id,code_fw,code_wl,flag_wl,select_Sum_Count)
Values(@tableCount,@tmpCode_fw,@tmpCode_wl,0,0)
Set @tableCount = @tableCount+1
end
--从小到大处理防伪码
Create Table #MyTable_fw(id int,code_fw nvarchar(30),code_wl nvarchar(30),
flag_wl int, select_Sum_Count int)
Set @strSql = '
Insert Into #MyTable_fw
Select a.*
from #MyTmpeTable a where
id = (select min(id) from #MyTmpeTable where code_fw = a.code_fw) order by a.id'
exec(@strSql)--去掉防伪码的所有重复值
Select @tableCount = Count(id) From #MyTable_fw --去掉重复值后还剩于的码个数
Set @tableCount = @sumCount - @tableCount
while @tableCount > 0 --补齐码的个数
begin
Set @tmpCode_fw = dbo.Code(@ilength_fw,@scode,@tableCount) --生成新码
Set @tmpCode_wl = dbo.Code(@ilength_wl,@scode,@tableCount) --生成新码
Insert into #MyTable_fw(id,code_fw,code_wl,flag_wl,select_Sum_Count)
Values(@tableCount,@tmpCode_fw,@tmpCode_wl,0,0)
Set @tableCount = @tableCount - 1
end
--把处理后的码,写回原表中
Set @strSql = 'delete '+@tableName
exec(@strSql)
Set @strSql = 'Insert into '+@tableName+' (code_fw,code_wl,flag_wl,select_Sum_Count '+
') Select code_fw,code_wl,flag_wl,select_Sum_Count From #MyTable_fw '
exec(@strSql)
GO
CREATE FUNCTION [dbo].[Code] (@ilength int,@scode nvarchar(30),@inum int)
RETURNS nvarchar(50)
BEGIN
--变量
declare @alength int
declare @strTmp nvarchar(50) --新生成的码,返回用
set @alength = @ilength - 6 --需要补齐的位数
Set @strTmp = @inum --码的关键字
if len(@strTmp) > @alength
begin
Set @strTmp = substring(@strTmp,1,@ilength)
end
else
begin
declare @ailength int
set @ailength = @alength - len(@strTmp) --需要循环处理的次数
declare @j int
set @j = 0
while @j < @ailength
begin
Set @strTmp = '0'+@strTmp
Set @j = @j+1
end
Set @strTmp = @scode + @strTmp
end
RETURN(@strTmp)
END
private List<string> CheckWriteData(DataTable aadt, int icodesum, int ilength, string scode)
{
using (SqlConnection conn = new SqlConnection())
{
List<string> value = new List<string>();
SqlCommand cmd = new SqlCommand();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["fwwlConnectionString"].ConnectionString;
conn.Open();
try
{
//首先删除临时表的记录
cmd.CommandTimeout = 600;
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Delete TmpCode";
cmd.ExecuteNonQuery();
//把所生的码写入临时表(可能会有复制的码)
using (SqlBulkCopy bcp = new SqlBulkCopy(conn))
{
bcp.BatchSize = aadt.Rows.Count % 100;
bcp.BulkCopyTimeout = 3600;
bcp.DestinationTableName = "TmpCode";
bcp.WriteToServer(aadt);
}
using (DataTable dt = new DataTable())
{
//把重复的码去掉,并补齐个数
cmd.CommandTimeout = 3600;
cmd.CommandText = "Select DISTINCT code from TmpCode";
SqlDataAdapter sdr = new SqlDataAdapter(cmd);
sdr.Fill(dt);
//把DataTable中的数据,存入List中,准备返回
for (int m = 0; m < dt.Rows.Count; m++)
{
value.Add(dt.Rows[m]["code"].ToString());
}
if (dt.Rows.Count < icodesum) {
string newcode = Code(ilength, scode, m).ToString();
for (int i = dt.Rows.Count - 1; i < icodesum; i++)
{
value.Add(newcode);
}
}
dt.Dispose();
}
return value;
}
catch (Exception ex)
{
FileLog.InsertXml(ex.Message);
return value;
}
}
}
//using (DataTable dt = new DataTable())
//{
//把重复的码去掉,并补齐个数
cmd.CommandTimeout = 3600;
cmd.CommandText = "Select DISTINCT code from TmpCode";
//SqlDataAdapter sdr = new SqlDataAdapter(cmd);
//sdr.Fill(dt);
SqlDataReader dr = cmd.EndExecuteReader();
while (dr.Read())
{
value.Add(dr["code"].ToString());
}
dr.Close();
//检查有多少个重复的码,并用循环补足个数
if (dt.Rows.Count < icodesum)
{
int i = icodesum - dt.Rows.Count;
for (int m = 0; m < i; m++)
{
//DataRow dr = dt.NewRow();
//dr["code"] = Code(ilength, scode, m).ToString(); //生成新的码
//dt.Rows.Add(dr);
value.Add(Code(ilength, scode, m).ToString());
}
}
//把DataTable中的数据,存入List中,准备返回
//for (int m = 0; m < dt.Rows.Count; m++)
//{
// value.Add(dt.Rows[m]["code"].ToString());
//}
//dt.Dispose();
//}