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



　　　　　

　　　　

转载于:https://www.cnblogs.com/Resources-blogs/p/6600668.html