在实际的开发中，会遇到各种各样的需求，今天遇到一个上传excel 文件，后台代码根据用户上传的excel中比对每一个字段和数据库某些表是否是存在；存在则填写正确 ，不存在用户的excel某个字段填写错误，并把相关填写错的字段提示出来。
首先我们先不细说上传功能如何实现，后期在分享出来（主要是用插件NPOI一个专门读取excel文件的）读取后存入DataTable中
根据上面的需求场景，有一下思路：
- 1把读取后的excel 转到DataTable中
- 2再把DataTable 插入自己构造的临时表中 (关键实现)
- 3把临时表与相关连的表进行关联 注意 left join
- 4关联的数据 IS NULL 的话说名没有关联到，也就是excel填写的数据在相关连的表中不存在
- 5CASE WHEN做嵌套 判断+拼接 错误的字段
其中第二点用DataTable 插入临时表；DataTable数据C# 表形式数据结构 并不等于 SQL数据库的表；需要进行转换。
方案一： 1、循环DataTable 每一列 取出相关的数据
2、把取出的数据进行拼sql
如 有DataTable有两行数据则拼sql出来结果如下:
SELECT 111123456 AS Card_No ,
5665 AS Tran_Code,
556 AS Credit,
562.55 AS Actual_Selling_Price,
4545 AS Sales_Branch_Code,
8979787888 AS Sales_Order_No,
'GGH454' AS Barcode,45 AS Credit_Activity_ID ,2 AS Credit_Expiry_Day_Type ,'2020-01-01' AS Credit_Expiry_Date,2 AS Credit_Expiry_Delay_Days ,N'备注' AS Remark
UNION
SELECT 8989 AS Card_No ,
55565 AS Tran_Code,
666 AS Credit,
989898 AS Actual_Selling_Price,
8555 AS Sales_Branch_Code,
65565 AS Sales_Order_No,
'sadsa4d5746' AS Barcode,45 AS Credit_Activity_ID ,2 AS Credit_Expiry_Day_Type ,'2020-01-01' AS Credit_Expiry_Date,2 AS Credit_Expiry_Delay_Days ,N'备注' AS Remark
第二步把上面的sql变成子查询 在关联相关的表
如：
SELECT T.*,
ELSE '' END + CASE WHEN T.Tran_Code = 47 AND T.Credit_Activity_ID = 0 THEN CONVERT(VARCHAR(10), T.Card_No) + N'积分活动奖励请选填对应积分活动ID,'
ELSE '' END + CASE WHEN T.Tran_Code IN ( 47, 51 ) AND T.Actual_Selling_Price != 0 THEN CONVERT(VARCHAR(10), T.Card_No) + CONVERT(VARCHAR(10), T.Card_No) + N'操作类型(47,51)仅为补录积分,'
ELSE '' END + CASE WHEN BH.[Branch Code] IS NULL THEN CONVERT(VARCHAR(10), T.Sales_Branch_Code) + N'销售分店不存在,'
ELSE ''
END AS Error_Msg
FROM ( --上面的拼sql) AS T
LEFT JOIN B ON B.ID=T.ID
LEFT JOIN C ON C.ID=T.ID
...........
缺点：不能适合多数据操作，拼sql不严谨
方案二：
1、打开一个数据链接
2、构建一个临时表
3、使用 bulkcopy.DestinationTableName = “#tpCRM_Credit_Adjust” //插入的临时表
bulkcopy.BatchSize = 1000 //插入的大小
bulkcopy.WriteToServer(Data) //插入的数据源 也就是DataTable
4、用临时表#tpCRM_Credit_Adjust 关联相关的表
具体实现代码如下：VB代码
Public Function View(connectionString As String, Data As DataTable) As DataTable
//创建一个数据链接 必须在一个链接下执行所有
Dim con As New SqlConnection(connectionString)
con.Open()
Dim ds As New DataSet()
Try
//创建临时表
Dim CommandTable = String.Format(<xml>
CREATE TABLE #tpCRM_Credit_Adjust
(
[Card_No] INT NOT NULL ,
[Tran_Code] smallint NOT NULL ,
[Credit] int NOT NULL ,
[Actual_Selling_Price] money NOT NULL,
[Sales_Branch_Code] int NOT NULL,
[Sales_Order_No] VARCHAR(50) NOT NULL,
[Barcode] VARCHAR(50) NOT NULL,
[Credit_Activity_ID] VARCHAR(50) NOT NULL,
[Credit_Expiry_Day_Type] VARCHAR(50) NOT NULL,
[Credit_Expiry_Date] VARCHAR(50) NOT NULL,
[Credit_Expiry_Delay_Days] VARCHAR(50) NOT NULL,
[Remark] VARCHAR(50) NULL {0}
); </xml>.Value, IIf(Domain() = CRMDomainEnum.HK, ", [Cost_Dept] VARCHAR(50) NULL", String.Empty))
Dim cmdUpdate As New SqlCommand(CommandTable, con)
cmdUpdate.ExecuteNonQuery()
'将Data插入临时表
Using bulkcopy As New SqlClient.SqlBulkCopy(con)
bulkcopy.DestinationTableName = "#tpCRM_Credit_Adjust"
bulkcopy.BatchSize = 1000
bulkcopy.WriteToServer(Data)
End Using
Dim cmdText = String.Format("
SELECT T.*,
CASE WHEN CAM.Card_No IS NULL THEN CONVERT(VARCHAR(10), T.Card_No) + N'无效卡号,'
ELSE '' END + CASE WHEN T.Tran_Code = 47 AND T.Credit_Activity_ID = 0 THEN CONVERT(VARCHAR(10), T.Card_No) + N'积分活动奖励请选填对应积分活动ID,'
ELSE '' END + CASE WHEN T.Tran_Code IN ( 47, 51 ) AND T.Actual_Selling_Price != 0 THEN CONVERT(VARCHAR(10), T.Card_No) + CONVERT(VARCHAR(10), T.Card_No) + N'操作类型(47,51)仅为补录积分,'
ELSE '' END + CASE WHEN BH.[Branch Code] IS NULL THEN CONVERT(VARCHAR(10), T.Sales_Branch_Code) + N'销售分店不存在,'
ELSE '' END + CASE WHEN CH.Sales_Order_No IS NULL THEN CONVERT(VARCHAR(10), T.Sales_Order_No)+ N')销售单号不存在,'
ELSE '' END + CASE WHEN CH.Bar_Code IS NULL THEN CONVERT(VARCHAR(10), T.Barcode)+ N')牌仔号不存在,'
ELSE '' END + CASE WHEN CA.Activity_ID IS NULL AND TC.Sign = 1 AND T.Credit > 0 AND (T.Credit_Expiry_Date = '1900-01-01' OR T.Credit_Expiry_Date<CONVERT(DATE,GETDATE()))AND T.Credit_Expiry_Delay_Days = 0 THEN CONVERT(VARCHAR(10), T.Card_No) + N'积分有效期无效,'
ELSE '' END + CASE WHEN CA.Status_Flag <> 1 OR ( CA.Credit_Expiry_Day_Type = 1 AND CA.Credit_Expiry_Date < GETDATE()) THEN CONVERT(VARCHAR(10), Credit_Activity_ID)+ N')无效/过期的积分活动,'
ELSE '' END + CASE WHEN T.Credit IS NOT NULL AND (T.Credit=0 AND T.Credit_Expiry_Day_Type<>0) OR (T.Credit<>0 AND (T.Credit_Expiry_Day_Type=0 OR T.Credit_Expiry_Day_Type>2)) THEN CONVERT(VARCHAR(10), T.Credit_Expiry_Day_Type)+ N')积分日期类型有误,'
ELSE '' END + CASE WHEN T.Credit_Expiry_Day_Type IS NOT NULL AND T.Credit_Expiry_Day_Type=1 AND T.Credit_Expiry_Date<GETDATE() THEN CONVERT(VARCHAR(10), T.Credit_Expiry_Day_Type)+ N'积分到期时间有误,'
ELSE '' END + CASE WHEN T.Credit_Expiry_Day_Type IS NOT NULL AND T.Credit_Expiry_Day_Type=2 AND T.Credit_Expiry_Delay_Days=0 THEN CONVERT(VARCHAR(10), T.Credit_Expiry_Day_Type)+ N'积分延期日有误,'
ELSE '' END + CASE WHEN T.Remark IS NULL THEN CONVERT(VARCHAR(10), T.Credit_Expiry_Day_Type)+ N'备注不能为空'
ELSE ''
END AS Error_Msg
FROM #tpCRM_Credit_Adjust T
LEFT JOIN tbCRM_Card_Master CAM ON T.Card_No = CAM.Card_No
AND CAM.Status_ID = 0
LEFT JOIN dbo.Branch BH ON BH.[Branch Code] = T.Sales_Branch_Code
LEFT JOIN dbo.tbCRM_Credit_History_Tran_Code TC ON TC.Tran_Code = T.Tran_Code
LEFT JOIN dbo.tbCRM_Credit_Activity CA ON T.Credit_Activity_ID = CA.Activity_ID
LEFT JOIN dbo.tbCRM_Credit_History CH ON CH.Card_No = CAM.Card_No
AND CH.CRM_Domain = CAM.CRM_Domain
AND CH.Customer_ID = CAM.Customer_ID
AND CH.Tran_Code = T.Tran_Code
AND CH.Sales_Order_No = T.Sales_Order_No;")
Dim cmd As New SqlDataAdapter(cmdText, con)
cmd.Fill(ds, "ds")
Catch ex As Exception
con.Close()
Finally
con.Close() '关闭连接
End Try
Return ds.Tables(0)
End Function
C#
public DataTable BatchMergeDrawTagsReview(DataTable data)
{
DataSet ds = new DataSet();
using (var conn = new SqlConnection(DbHelperSql.CrmUpdate))
{
using (var command = new SqlCommand("", conn))
{
try
{
conn.Open();
//数据库并创建一个临时表来保存数据表的数据
command.CommandText = @"CREATE TABLE #tpCustomerTagMerge (
[Customer_Id] INT NOT NULL,
[Tag_Desc] NVARCHAR(100) NOT NULL
);";
command.ExecuteNonQuery();
//使用SqlBulkCopy 加载数据到临时表中
using (var bulkCopy = new SqlBulkCopy(conn))
{
bulkCopy.BulkCopyTimeout = 660;
bulkCopy.DestinationTableName = "#tpCustomerTagMerge";
bulkCopy.WriteToServer(data);
bulkCopy.Close();
}
// 执行Command命令 使用临时表的数据去更新目标表中的数据 然后删除临时表
var strSql =
@"SELECT TA.Customer_Id,
TA.Tag_Desc
FROM #tpCustomerTagMerge AS TA
LEFT JOIN dbo.tbCRM_Card_Master AS CAM WITH (NOLOCK)
ON CAM.Customer_ID = TA.Customer_Id
AND CAM.CRM_Domain = 2
AND CAM.Status_ID = 0
LEFT JOIN dbo.[tbCRM_Customer_Tag] AS CCT
ON CAM.CRM_Domain = CCT.CRM_Domain
AND CAM.Customer_ID = CCT.Customer_ID
AND CCT.Tag_ID IN ( SELECT DISTINCT Tag_ID FROM dbo.tbCRM_Tag WHERE Tag_Type = 3 )
LEFT JOIN dbo.tbCRM_Tag AS TG1
ON TG1.Tag_Desc = TA.Tag_Desc
AND TG1.Tag_Type = 3
LEFT JOIN ( SELECT Customer_Id
FROM #tpCustomerTagMerge
GROUP BY Customer_Id
HAVING COUNT(Tag_Desc) >= 2) AS Many
ON Many.Customer_Id = TA.Customer_Id
ORDER BY ErrorMsg DESC;";
SqlDataAdapter cmd = new SqlDataAdapter(strSql, conn);
cmd.Fill(ds, "ds");
}
catch (Exception e)
{
throw new CrmDbException(e.Message, e.StackTrace, null);
}
finally
{
conn.Close();
}
}
}
return ds.Tables[0];
}
2605
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
