sqlserver调用mysql存储过程_调用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;

selectb.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