public void CallCustomStoredProcedure()
{
// Step 1: Configure parameters for your stored procedure if the stored procedure accepts parameters
// Each database type has different parameter type.
// Please change them accordingly to fit your application's logic.
// SQL Server:
// Parameter type: System.Data.SqlDbType
// Oracle:
// Parameter type: System.Data.OracleClient.OracleType
// MySql:
// Parameter type: MySql.Data.MySqlClient.MySqlDbType
// BaseClasses.Data.StoredProcedureParameter firstParameter = null;
///For SQL Server: use parameter type System.Data.SqlDbType
// firstParameter = new BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeID", 5, System.Data.SqlDbType.Int, System.Data.ParameterDirection.Input);
///For Oracle: use parameter type System.Data.OracleClient.OracleType
// firstParameter = new BaseClasses.Data.StoredProcedureParameter("pk_EmployeeID", 5, System.Data.OracleClient.OracleType.Int, System.Data.ParameterDirection.Input);
///For MySql: use parameter type MySql.Data.MySqlClient.MySqlDbType
// firstParameter = new BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeID", 5, MySql.Data.MySqlClient.MySqlDbType.Int, System.Data.ParameterDirection.Input, true);
// BaseClasses.Data.StoredProcedureParameter secondParameter = null;
///For SQL Server: use parameter type System.Data.SqlDbType
// secondParameter = new BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeLastName", "Smith", System.Data.SqlDbType.VarChar, System.Data.ParameterDirection.Input);
///For Oracle: use parameter type System.Data.OracleClient.OracleType
// secondParameter = new BaseClasses.Data.StoredProcedureParameter("pk_EmployeeLastName", "Smith", System.Data.OracleClient.OracleType.VarChar, System.Data.ParameterDirection.Input);
///For MySql: use parameter type MySql.Data.MySqlClient.MySqlDbType
// secondParameter = new BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeLastName", "Smith", MySql.Data.MySqlClient.MySqlDbType.VarChar, System.Data.ParameterDirection.Input, true);
/// Note, in case you need to return output value from the Stored procedure you might use the following code:
/// BaseClasses.Data.StoredProcedureParameter thirdParameter = null;
/// string myvar = "";
/// thirdParameter = New BaseClasses.Data.StoredProcedureParameter("@paramName", myvar, System.Data.SqlDbType.VarChar, System.Data.ParameterDirection.Output)
/// NOTE: if this parameter has a string type such as varchar, nvarchar, string, etc. you have to set its size:
/// thirdParameter.Size = 10
// Step 2: Add the configured parameters to an array list.
// BaseClasses.Data.StoredProcedureParameter[] parameterList = new BaseClasses.Data.StoredProcedureParameter[1];
// parameterList[0] = firstParameter;
// parameterList[1] = secondParameter;
// Skip steps 1 and 2 if stored procedure does not accept parameters.
BaseClasses.Data.StoredProcedure myStoredProcedure = null;
// "DatabaseNorthwind1" is a connection string obtained from Web.config
// located in application's root directory.
// Step 3: Connect to the stored procedure using following line if
// the stored procedure does not accept any parameters.
myStoredProcedure = new BaseClasses.Data.StoredProcedure("DatabaseNorthwind1", "Stored_Procedure_Name", null);
// If the stored procedure accepts parameters, use the following
// myStoredProcedure = new BaseClasses.Data.StoredProcedure("DatabaseNorthwind1", "Stored_Procedure_Name", parameterList);
// Step 4: Run the stored procedure.
// RunQuery() will return true if stored procedure successfully executed. Otherwise, it will return false.
// Use RunQuery or RunNonQuery. RunQuery is used when a set of records is being returned. RunNonQuery is used when one or more values are returned through output parameters.
if (myStoredProcedure.RunQuery())
{
/// If you have output parameter, use code like:
/// System.Data.IDataParameter outputParameter;
/// outputParameter = (System.Data.IDataParameter)(myStoredProcedure.OutputParameters[0]);
/// string myText = outputParameter.Value.ToString();
// Result from stored procedure is available
// as a DataSet or as an array of RecordValue objects.
// return (myStoredProcedure.DataSet);
// return (myStoredProcedure.Records);
// If you want to go through the data set and access each row and column,
// see below
// System.Data.DataSet ds = myStoredProcedure.DataSet;
// System.Data.DataTable dt = ds.Tables[0];
// foreach (System.Data.DataRow myRow in dt.Rows)
// {
// String lastName = myRow[lastNameColumnIndex].ToString();
// }
// If you want to go through the record set and access each row and column,
// see below
// ArrayList records = myStoredProcedure.Records;
// foreach (BaseClasses.Data.RecordValue record in records)
// {
// String lastName = record.ColumnValues[lastNameColumnIndex].Value.ToString();
// }
}
else
{
///You can raise an exception in the custom stored procedure and catch the exception and reporting it to the user.
///To raise the exception:
///SET NOCOUNT ON;
///RAISERROR (N'My custom error message goes here', 11, 1)
///IMPORTANT: If you raise an error that has a severity level of 10 or less, it is considered
///a warning, and no exception is raised. The severity of the error must be between 11 and 20
///for an exception to be thrown.
///Once the exception is raised, you can look at:
///myStoredProcedure.ErrorMessage to get the text of the error message and use RegisterJScriptAlert to report this to the user.
}
}
|