Asp .net 调用带参数的存储过程

时间:2022-09-17 21:21:18

1.后台调用带参数的存储过程详解

例:

注明:@AnalysisDate,@Process_PTR为存储过程参数

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
     IDataParameter[] iDataDi = new SqlParameter[2];
         iDataDi[0] = new SqlParameter("@AnalysisDate", showDate);
         iDataDi[1] = new SqlParameter("@Process_PTR", ID);
         //获取检测项所选日期的不同时间
         dtDifferTime = SqlHelper.RunProceduresByParameter("pro_GetDifferenceTimeInfos", iDataDi);
         //SqlHelper中的 RunProceduresByParameter(string storedProcName, IDataParameter[] parameters)方法:
/// <summary>
 /// 执行带参数的存储过程,返回DataSet类型
 /// </summary>
 /// <param name="storedProcName"></param>
 /// <param name="parameters"></param>
 /// <returns></returns>
 public static DataSet RunProceduresByParameter(string storedProcName, IDataParameter[] parameters)
 {
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
     DataSet dataSet = new DataSet();
     connection.Open();
     SqlDataAdapter sqlDA = new SqlDataAdapter();
     sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
     sqlDA.Fill(dataSet);
     connection.Close();
     connection.Dispose();
     return dataSet;
   }
 }
/// <summary>
 /// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值)
 /// </summary>
 /// <param name="connection">数据库连接</param>
 /// <param name="storedProcName">存储过程名</param>
 /// <param name="parameters">存储过程参数</param>
 /// <returns>SqlCommand</returns>
 private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
 {
   SqlCommand command = new SqlCommand(storedProcName, connection);
   command.CommandType = CommandType.StoredProcedure;
   foreach (SqlParameter parameter in parameters)
   {
     command.Parameters.Add(parameter);
   }
   return command;
 }

2.存储过程创建语句

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
USE [RedBSys_DB]
GO
/****** Object: StoredProcedure [dbo].[pro_GetDifferenceTimeInfos]  Script Date: 2017-03-22 16:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 --获取检测项当天日期不同时间
CREATE proc [dbo].[pro_GetDifferenceTimeInfos]
  @AnalysisDate varchar(50),
  @Process_PTR int
AS
 select distinct(AnalysisDate) from Assay_BillMain
 where CONVERT(varchar(100),AnalysisDate, 23)=@AnalysisDate and Process_PTR=@Process_PTR
 order by AnalysisDate ASC
GO

 

以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,同时也希望多多支持服务器之家!

原文链接:http://www.cnblogs.com/Resources-blogs/p/6600668.html