调用sqlserver中的存储过程

 

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