Asp.net调用带参数的存储过程的详细分析

Asp.net调用带参数的存储过程的详细分析

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

例:

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

 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.存储过程创建语句

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

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