Asp. net calls stored procedures with parameters and. net stored procedures

Asp. net calls stored procedures with parameters and. net stored procedures

1. Detailed explanation of the stored procedure with parameters called in the background

Example:

Note:@ AnalysisDate,@ Process_PTRFor stored procedure parameters

IDataParameter [] iDataDi = new SqlParameter [2];
IDataDi [0] = new SqlParameter ("@ AnalysisDate", showDate );
IDataDi [1] = new SqlParameter ("@ Process_PTR", ID );
// Obtain the Time of the selected date of the detection item
DtDifferTime = SqlHelper. RunProceduresByParameter ("pro_GetDifferenceTimeInfos", iDataDi );

// The RunProceduresByParameter (string storedProcName, IDataParameter [] parameters) method in SqlHelper:

/// <Summary>
/// Execute the stored procedure with parameters and return the 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>
/// Construct a SqlCommand object (used to return a result set instead of an integer)
/// </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: 16:34:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- Obtain the detection item's day date and 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