Asp .net 调用带参数的蕴藏进程

GO

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

                
//SqlHelper中的 RunProceduresByParameter(string storedProcName,
IDataParameter[] parameters)方法:

 –获取检测项当天日期不同时间
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

    /// <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;
            }
        }

**2.存储过程创建语句**

/****** Object:  StoredProcedure
[dbo].[pro_GetDifferenceTimeInfos]    Script Date: 2017-03-22
16:34:13 ******/
SET ANSI_NULLS ON
GO

    

 

SET QUOTED_IDENTIFIER ON
GO

       
IDataParameter[] iDataDi = new SqlParameter[2];
                        iDataDi[0] = new SqlParameter(“@AnalysisDate”,
showDate);
                        iDataDi[1] = new SqlParameter(“@Process_PTR”,
ID);
                        //获取检测项所选日期的不同时间
                        dtDifferTime =
SqlHelper.RunProceduresByParameter(“pro_GetDifferenceTimeInfos”,
iDataDi);

     **

    /// <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;
        }

                
例:

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

**USE [RedBSys_DB]
GO

相关文章