Using Oracle Stored Procedures in SqlDataSource Select Command

• Download source - 6.14 KB

Introduction

Using Oracle Stored Procedures in SqlDataSource Select command is not straightforward. When you return recordset from Oracle SP, you have to declare out parameter of sys_refcursor type, which is not possible without handling Selecting event on the data source.

Background

To do that, I'll demonstrate two ways, one with simple code behind handler of the Selecting event, and the other with developing custom Parameter control, and sub classing SqlDataSource control. The latter is more complicated, but it gives you the ability to use stored procedures not only from Oracle, but from all databases which return cursors from their stored procedures.

Using Selecting Event in Code Behind

First, we need to create the simple stored procedure which will be called from our application:

create or replace procedure testCountries(p_rc out sys_refcursor) is
begin
       open p_rc for 
       select * from Countries;
end testCountries;

To access this SP from an ASP.NET page, you could create a flowing page:

<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1">
</asp:GridView>
        
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:oraConnectionString %>" 
            ProviderName="<%$ ConnectionStrings:oraConnectionString.ProviderName %>" 
            SelectCommand="TESTCOUNTRIES" SelectCommandType="StoredProcedure" 
            onselecting="SqlDataSource1_Selecting">
            <SelectParameters>
                <asp:Parameter Name="p_rc" Direction="Output" />
            </SelectParameters>        

Then, in the code behind, just find the parameter in the command object, and assign the appropriate type to it:

protected void SqlDataSource1_Selecting
   (object sender, SqlDataSourceSelectingEventArgs e)
   {
       ((System.Data.OracleClient.OracleParameter)e.Command.Parameters[0]).OracleType =
            System.Data.OracleClient.OracleType.Cursor;
   }

Creating Custom Parameter and SqlDataSource

If you want to do this just with declarative syntax, I came up with the following approach:

First, create a custom Parameter control, which will hold the property name of the command object parameter (OracleType in our case) which sets the parameter type, and the enum value of that property (Cursor in our case):

namespace CustomComponents
{
    public class DBSpecialParameter : Parameter
  {
    /// <summary>
    /// Gets or sets the string that contains the name of the property
    /// of the Parameter object which holds parameter type 
    /// </summary>
    
    public string DBParamTypePropertyName
    {
        get { return ViewState["DBParamTypePropertyName"] != 
            null ? (string)ViewState["DBParamTypePropertyName"] : string.Empty; }
        set { ViewState["DBParamTypePropertyName"] = value; }
    }
   /// <summary>
   /// The enum value  which has to be assigned to the DBParamTypePropertyName
   /// </summary>
    public string DBParamTypeEnumeValue
    {
        get { return ViewState["DBParamTypeEnumeValue"] != 
            null ? (string)ViewState["DBParamTypeEnumeValue"] : string.Empty; }
        set { ViewState["DBParamTypeEnumeValue"] = value; }
    }  
  }
}

Next, we subclass SqlDataSource to handle Selecting event, find appropriate command object Parameter, using reflection, find the property with the name from our DBSpecialParameter.DBParamTypePropertyName, and set its value to DBSpecialParameter.DBParamTypeEnumeValue.

Here is the code which does that:

namespace CustomComponents
{
    /// <summary>
    /// Summary description for SqlDataSourceEx
    /// </summary>
    public class SqlDataSourceEx : System.Web.UI.WebControls.SqlDataSource
    {
        public SqlDataSourceEx()
        {
        }
        /// <summary>
        /// Attach a handler to Selecting event
        /// </summary>
        /// <param name="e"></param>
        protected override void OnInit(EventArgs e)
        {
            base.Selecting += 
               new System.Web.UI.WebControls.SqlDataSourceSelectingEventHandler(
               SqlDS_Selecting);
            base.OnInit(e);
        }
        /// <summary>
        /// Finds DBSpecialParameters in SqlDataSourceEx, find parameter in the
        /// Parameters collection of the command object with the same name, then using
        /// reflection find DBParamTypePropertyName property in the parameter and
        /// assign DBParamTypeEnumeValue to it.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="selectingArgs"></param>
        protected void SqlDS_Selecting(object sender,
            System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs selectingArgs)
        {
            foreach (System.Web.UI.WebControls.Parameter selPar 
					in base.SelectParameters)
            {
                if (selPar is DBSpecialParameter)
                {
                    foreach (
                        System.Data.Common.DbParameter commParam 
					in selectingArgs.Command.Parameters)
                    {
                        if (selPar.Name == commParam.ParameterName)
                        {
                            try
                            {
                                // Cast to DBSpecialParameter
                                DBSpecialParameter dbp = selPar as DBSpecialParameter;
                                if (dbp == null)
                                    throw new ApplicationException(
                                    String.Format(
                                    "DBSpecialParameter error: {0} 
					is not DBSpecialParameter!",
                                    selPar.Name));
                                // Get parameter type object
                                Type t = commParam.GetType();
                                // Get the dbType property
                                PropertyInfo dbTypeProperty = t.GetProperty(
                                    dbp.DBParamTypePropertyName);
                                if (dbTypeProperty == null)
                                    throw new ApplicationException(String.Format(
                                    "DBSpecialParameter error: 
				No property with '{0}' name" +
                                    "exists in '{1}'!",
                                        dbp.DBParamTypePropertyName, t.FullName));
                                // Get type of the property 
                                Type enumType = dbTypeProperty.PropertyType;
                                // Get the cursor type enum value
                                FieldInfo evalue = enumType.GetField(
                                    dbp.DBParamTypeEnumeValue);
                                if (evalue == null)
                                    throw new ApplicationException(String.Format(
                                    "DBSpecialParameter error: 
				No enum value '{0}' exists" +
                                    "in '{1}'!",
                                       dbp.DBParamTypeEnumeValue, enumType.FullName));
                                // Set the dbType property to enum cursor value
                                dbTypeProperty.SetValue(commParam,
                                    evalue.GetValue(enumType), null);
                            }
                            catch
                            {
                                throw;
                            }
                            break;
                        }
                    }
                }
            }
        }
    }
}

This is the declarative code which we could use to get data from the same stored procedure from above:

<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSourceEx1">
</asp:GridView>
<custom:SqlDataSourceEx ID="SqlDataSourceEx1"  runat="server" 
            ConnectionString="<%$ ConnectionStrings:oraConnectionString %>" 
            ProviderName="<%$ ConnectionStrings:oraConnectionString.ProviderName %>" 
            SelectCommand="testCountries" SelectCommandType="StoredProcedure"  >
            <SelectParameters>             <dbspecialparameter name=""p_rc"" dbparamtypepropertyname=""OracleType"" dbparamtypeenumevalue=""Cursor"" direction=""Output"" />
             <custom:DBSpecialParameter Name="p_rc" Direction="Output"
                DBParamTypePropertyName="OracleType" DBParamTypeEnumeValue="Cursor" />
            </SelectParameters>    
</custom:SqlDataSourceEx>

Happy programming!

History

• 5^th January, 2009: Initial post