C#进行IN条件查询的SQL语句使用SqlParameter的通用方法

 

 

C#进行IN条件查询的SQL语句使用SqlParameter的通用方法

 

//第一步定义参数化字段，有多个值就定义多个参数化字段
string para = "";
                for(int i=0;i< GamDocumentInfoList.Count;i++)
                {
                    para += "@docIDs" + i.ToString()+",";
                }
                para = para.TrimEnd(new char[] { ',' });

//第二步，参数化字段 添加到sql中
                string sql_material = $@"SELECT   GTM.ID, GTM.ID_Task, GTM.ID_Document, GTM.ID_OrderRows, mta.MaterialCode, mta.MaterialName,mta.MaterialGuiGe, GTM.TallyState, 
                                GTM.ID_PlanStore, GTM.ID_PlanStore2, GTM.PlanBatch, GTM.PlanWeight, GTM.PlanUnits, GTM.RealUnits, 
                                GTM.ID_RealStore, GTM.ID_RealStore2, GTM.RealBatch, GTM.MBuckleWeight, GTM.MRealWeight, GTM.CreateTime, 
                                GTM.Note, GTM.IsDelete, GTM.DeleteDate, GTM.DeleteYongHuID, GTM.UpdateYongHuID, GTM.UpdateDate, 
                                odc.OrderBH, store.StoreName AS PlanStoreName, TB_Store_1.StoreName AS PlanStoreName2, 
                                TB_Store_2.StoreName AS RealStoreName, dbo.TB_Store.StoreName AS RealStoreName2,GTM.ComeTareWeight as ComeTareWeight,GTM.ComeGrossWeight as ComeGrossWeight,GTM.ComeRealWeight as ComeRealWeight,shdw.DanWeiMingCheng AS SHDWMC,fhdw.DanWeiMingCheng AS FHDWMC
                                FROM dbo.TBG_GamTaskMaterial AS GTM INNER JOIN
                                dbo.TBD_OrderRows AS odr ON GTM.ID_OrderRows = odr.ID INNER JOIN
                                dbo.TBD_OrderDoc AS odc ON odr.ID_OrderDoc = odc.ID LEFT OUTER JOIN
                                TB_Material AS mta on mta.ID = GTM.ID_Material LEFT OUTER JOIN
                                dbo.TB_Store ON GTM.ID_RealStore2 = dbo.TB_Store.ID LEFT OUTER JOIN
                                dbo.TB_Store AS TB_Store_2 ON GTM.ID_RealStore = TB_Store_2.ID LEFT OUTER JOIN
                                dbo.TB_Store AS TB_Store_1 ON GTM.ID_PlanStore2 = TB_Store_1.ID LEFT OUTER JOIN
                                dbo.TB_Store AS store ON GTM.ID_PlanStore = store.ID  LEFT OUTER JOIN
                                dbo.TBG_GamDocument AS gb ON gb.ID = GTM.ID_Document  LEFT OUTER JOIN
                                dbo.TBS_DanWei AS shdw ON shdw.ID = gb.ID_Receiver LEFT OUTER JOIN
                                dbo.TBS_DanWei AS fhdw ON fhdw.ID = gb.ID_Sender  where  GTM.IsDelete=0 AND GTM.ID_Document IN ( {para} ) order by CreateTime desc";
                
                //(" + string.Join(", ", GamDocumentInfoList.Select(ppp => "'" + ppp.ID + "'").Distinct()) + ")

//第三步，参数化字段和实际值对应起来
                List<string> idList = GamDocumentInfoList.Select(ppp => ppp.ID).Distinct().ToList();
                SqlParameter[] parameters = new SqlParameter[GamDocumentInfoList.Count];
                for (int i = 0; i < GamDocumentInfoList.Count; i++)
                {
                    parameters[i] = new SqlParameter("@docIDs" + i.ToString(), idList[i]);
                }
//第四步，从数据库中查找返回
                ajaxResult.Result3 = tbg_GamTaskMaterialRepository.GetDataTable(sql_material, parameters).ToList<GamMaterialInfo>();

 

 

主要是，对应几个值， 需要创建几个参数化字段

 

##数据库查询SQL展示

exec sp_executesql N'SELECT   GTM.ID, GTM.ID_Task, GTM.ID_Document, GTM.ID_OrderRows, mta.MaterialCode, mta.MaterialName,mta.MaterialGuiGe, GTM.TallyState, 
                                GTM.ID_PlanStore, GTM.ID_PlanStore2, GTM.PlanBatch, GTM.PlanWeight, GTM.PlanUnits, GTM.RealUnits, 
                                GTM.ID_RealStore, GTM.ID_RealStore2, GTM.RealBatch, GTM.MBuckleWeight, GTM.MRealWeight, GTM.CreateTime, 
                                GTM.Note, GTM.IsDelete, GTM.DeleteDate, GTM.DeleteYongHuID, GTM.UpdateYongHuID, GTM.UpdateDate, 
                                odc.OrderBH, store.StoreName AS PlanStoreName, TB_Store_1.StoreName AS PlanStoreName2, 
                                TB_Store_2.StoreName AS RealStoreName, dbo.TB_Store.StoreName AS RealStoreName2,GTM.ComeTareWeight as ComeTareWeight,GTM.ComeGrossWeight as ComeGrossWeight,GTM.ComeRealWeight as ComeRealWeight,shdw.DanWeiMingCheng AS SHDWMC,fhdw.DanWeiMingCheng AS FHDWMC
                                FROM dbo.TBG_GamTaskMaterial AS GTM INNER JOIN
                                dbo.TBD_OrderRows AS odr ON GTM.ID_OrderRows = odr.ID INNER JOIN
                                dbo.TBD_OrderDoc AS odc ON odr.ID_OrderDoc = odc.ID LEFT OUTER JOIN
                                TB_Material AS mta on mta.ID = GTM.ID_Material LEFT OUTER JOIN
                                dbo.TB_Store ON GTM.ID_RealStore2 = dbo.TB_Store.ID LEFT OUTER JOIN
                                dbo.TB_Store AS TB_Store_2 ON GTM.ID_RealStore = TB_Store_2.ID LEFT OUTER JOIN
                                dbo.TB_Store AS TB_Store_1 ON GTM.ID_PlanStore2 = TB_Store_1.ID LEFT OUTER JOIN
                                dbo.TB_Store AS store ON GTM.ID_PlanStore = store.ID  LEFT OUTER JOIN
                                dbo.TBG_GamDocument AS gb ON gb.ID = GTM.ID_Document  LEFT OUTER JOIN
                                dbo.TBS_DanWei AS shdw ON shdw.ID = gb.ID_Receiver LEFT OUTER JOIN
                                dbo.TBS_DanWei AS fhdw ON fhdw.ID = gb.ID_Sender  where  GTM.IsDelete=0 AND GTM.ID_Document IN ( @docIDs0,@docIDs1,@docIDs2,@docIDs3,@docIDs4 ) order by CreateTime desc',N'@docIDs0 nvarchar(36),@docIDs1 nvarchar(36),@docIDs2 nvarchar(36),@docIDs3 nvarchar(36),@docIDs4 nvarchar(36)',@docIDs0=N'baeca256-3e5e-4523-9c1c-6231d291f018',@docIDs1=N'6cbb1f06-4774-4025-8b81-09abf2ebd1e9',@docIDs2=N'a8622567-13dc-4709-801c-e2db68866efd',@docIDs3=N'070d6021-f7ab-412b-b345-7ea0f70cd228',@docIDs4=N'd6f7c9f1-d176-413e-b9f1-642da0d6afc2'