Asp. net calling stored procedures with parameters
- 2021-09-16 06:37:01
- OfStack
1. Detailed explanation of calling stored procedures with parameters in the background
Example:
Note: @ AnalysisDate, @ Process_PTR are stored procedure parameters
IDataParameter[] iDataDi = new SqlParameter[2];
iDataDi[0] = new SqlParameter("@AnalysisDate", showDate);
iDataDi[1] = new SqlParameter("@Process_PTR", ID);
// Gets different times of the date selected by the test item
dtDifferTime = SqlHelper.RunProceduresByParameter("pro_GetDifferenceTimeInfos", iDataDi);
//SqlHelper In RunProceduresByParameter(string storedProcName, IDataParameter[] parameters) Methods:
/// <summary>
/// Executes a stored procedure with parameters and returns DataSet Type
/// </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>
/// Build SqlCommand Object ( Used to return 1 Result sets instead of 1 Integer value )
/// </summary>
/// <param name="connection"> Database connection </param>
/// <param name="storedProcName"> Stored procedure name </param>
/// <param name="parameters"> Stored procedure 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. Stored procedure creation statement
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
-- Get the test item on the same day, date and different time
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