ASP.NET使用SQL存储过程读取数据库（前台页面通过CHECKBOXLIST遍历获取条件）

原创已于 2022-05-18 12:44:14 修改 · 418 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#sql #asp.net #linq

于 2022-05-18 12:41:27 首次发布

这段代码展示了如何从数据库中执行存储过程，根据用户选择的条件筛选数据，并将结果显示在GridView上，最后将数据导出到Excel。存储过程P_GET_IMICSTOCKBILLENTRY用于按指定日期范围、单据类型、制单人和物料编码进行出入库单据查询。

int i_whlist = chk_whlist.SelectedIndex;
string s_whlist = "";
string _ws = "";
if (i_whlist >= 0)
{
foreach (ListItem li in chk_whlist.Items)
{
s_whlist += li.Selected ? li.Value + "," : "";
}
_ws = s_whlist.Substring(0, s_whlist.Length - 1);
} //以上为构建checkboxlist 配合前台jquery控制选择项
string _txtkey = txt_key.Text.ToString() == "" ? "" : txt_key.Text.ToString();
string str_sql = "P_GET_IMSTOCKICInventory " + "'" + _ws + "'" + "," + "'" + _txtkey + "'";
MydB rb = new MydB();
DataTable dt = new DataTable();
dt = rb.ExecuteQueryERP(str_sql);
this.gv_export.DataSourceID = null;
this.gv_export.DataSource = dt;
this.gv_export.DataBind();
mysubmenu.DGToExcel(this.gv_export, _dt);
this.gv_export.Visible = false;

以下为后台存储过程

USE [AIS20200501]
GO
/****** Object: StoredProcedure [dbo].[P_GET_IMICSTOCKBILLENTRY] Script Date: 2022/5/18 12:42:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:       <LOUISPENG>
-- Create date: <20220510>
-- Description:   <按单据类型和日期查询出入库单据>
-- =============================================
ALTER PROCEDURE [dbo].[P_GET_IMICSTOCKBILLENTRY]
   -- Add the parameters for the stored procedure here
   @STARTDATE DATETIME,--起始日期
   @ENDDATE DATETIME,--结束日期
   @TRANSTYPE VARCHAR(30),--单据类型
   @FNAME VARCHAR(30),--制单人
   @FFNUMBER VARCHAR(50)--物料编码名称规格
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

--出入库单据汇总查询表
   SELECT C.FName 制单人,FBillNo 入库单号,CONVERT(VARCHAR(20),A.FDate,112) 单据日期,
   (CASE WHEN A.FCancellation=0 THEN '未作废' ELSE '已作废' END) 是否作废,E.FName 审核人,
   (CASE WHEN A.FROB=1 THEN '蓝字' ELSE '红字' END) 红蓝字,
   (CASE WHEN A.FStatus=0 THEN '未审核' ELSE '已审核' END) 单据状态,F.FName 单据类型,
   B.FOrderBillNo 订单号,B.FEntryID 分录号,D.FNumber 编码,D.FName 名称,D.FModel 规格,STR(B.FQty) 数量,
   G.FName 调入仓库,H.FName 调出仓库
   FROM ICStockBill A INNER JOIN ICStockBillEntry B ON A.FInterID=B.FInterID
   LEFT JOIN t_User C ON A.FBillerID=C.FUserID
   LEFT JOIN t_User E ON A.FCheckerID=E.FUserID
   LEFT JOIN t_ICItem D ON B.FItemID=D.FItemID
   LEFT JOIN ICTransType F ON A.FTranType=F.FID
   LEFT JOIN t_Stock G ON B.FDCStockID=G.FItemID
   LEFT JOIN t_Stock H ON B.FSCStockID=H.FItemID
   WHERE A.FDate BETWEEN @STARTDATE AND @ENDDATE AND charindex(','+ltrim(F.FID)+',',','+@TRANSTYPE+',')>0--F.FID IN ( @TRANSTYPE )
   AND C.FName LIKE '%'+@FNAME+'%'
   AND (D.FNumber LIKE '%'+@FFNUMBER+'%' OR D.FModel LIKE '%'+@FFNUMBER+'%' OR D.FName LIKE '%'+@FFNUMBER+'%')

END