调用sqlserver中的存储过程

时间:2021-12-25 14:06:58

 

1、存储过程名

string strSQL = "usp_GetUnReturnPassports";

2、创建数据库对象 database

Database db = DatabaseFactory.CreateDatabase(ConfigHelper.CurrentConfig.LocalDBName);

3、创建数据库命令对象 DbCommand

DbCommand command = db.GetStoredProcCommand(strSQL);

4、添加参数

db.AddInParameter(command, "unitCode", DbType.String, unitCode.Substring(0, 3));
   db.AddInParameter(command, "startDate", DbType.DateTime, startDate);
   db.AddInParameter(command, "endDate", DbType.DateTime, endDate);

5、执行命令

DataSet ds = db.ExecuteDataSet(command);

6、读取结果

for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
   {

PassportBR one = new PassportBR();
    one.Id = int.Parse(ds.Tables[0].Rows[i][0].ToString());

。。。。

}

额外知识点补充:

1、上面存储过程的写法

USE [cnpc]
GO
/****** Object: StoredProcedure [dbo].[usp_GetUnReturnPassports] Script Date: 05/23/2018 09:26:46 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: zhengwei 18963948278 -- Create date: 2016-12-28 -- Description: 获取应归还证照 -- ============================================= ALTER PROCEDURE [dbo].[usp_GetUnReturnPassports] ( @unitCode nvarchar(6), @startDate datetime, @endDate datetime ) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; select b.Id,b.PassportNumber,b.Name,b.Xmbm,b.processedUnit,b.BorrowDate,b.ReceiverName,b.ReceiverTel,b.CashPledge, b.MustReturnDate,b.ReturnDate,b.Depositary,b.RWPJLetterNumber,b.GroupName,b.BProcessedBy,b.RProcessedBy from [PassportBR] b left join hzhk k on b.passportNumber=k.hzhhm left join ryk on ryk.xmbm=b.xmbm where k.IsSetDeleted=0 and k.jchqk='Y' and k.xhrq is null and k.zhxrq is null and k.jzhrq>GETDATE() --未标记删除的,借出状态的,未销毁的,未注销的,有效期内的 and b.RprocessedBy is null and b.MustReturnDate>'2016-8-1' --未归还的,应归还日期是2016-8-1之后的 and (b.processedUnit=@unitCode or ryk.gzdw=@unitCode) and b.MustReturnDate between @startDate and @endDate --护照保管单位+工作单位 END