Stored Procedures Customization

Retrieve Multiple Records with Your Own Database Stored Procedures

This example shows how to enforce application-wide sign-in security without configuring sign-in on a page by page basis as is typical with role-based security.
Applies to
BasePage class
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();
	    // }
	    ///You can raise an exception in the custom stored procedure and catch the exception and reporting it to the user.
	    ///To raise the exception:
	    ///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.

Terms of Service Privacy Statement