Click here to Skip to main content
Click here to Skip to main content

Using Oracle Stored Procedures in SqlDataSource Select Command

, 5 Jan 2009
Rate this:
Please Sign up or sign in to vote.
How to use Oracle (and other non Microsoft) SP in SqlDataSource Select command

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

  • 5th January, 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Petrovik Aleksandar
Software Developer (Senior) Strategic Connections Inc.
Canada Canada
I'm in Toronto now, working for some CRM company....

Comments and Discussions

 
Generalpaging Pinmemberalhambra-eidos14-Apr-09 0:46 
How I use paging in gridview, using procedures with nullables parameters for SQL filters ???
 
Thanks
 
AE

GeneralRe: paging PinmemberPetrovik Aleksandar15-Apr-09 23:03 
Generalcool! PinmemberYang Yu5-Jan-09 17:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 5 Jan 2009
Article Copyright 2009 by Petrovik Aleksandar
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid