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 and @ Process_PTR are 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> /// executes a stored procedure with parameters, return 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"> name of the stored procedure </ 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 ONGOSET QUOTED_IDENTIFIER ONGO -- Obtain the Date and time of the detection item CREATE proc [dbo]. [pro_GetDifferenceTimeInfos] @ AnalysisDate varchar (50), @ Process_PTR intAS select distinct (AnalysisDate) from Assay_BillMain where CONVERT (varchar (100), AnalysisDate, 23) = @ AnalysisDate and Process_PTR = @ Process_PTR order by AnalysisDate ASCGO

 

The above is all the content of this article. I hope this article will help you in your study or work. I also hope to provide more support to the customer's home!