Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
105
WebHierarchicalDataSource and ObjectDataSource based on Stored Procedure
posted

I am trying to do a very simple master detail with stored procedures, but it refuses to work:

I debugged it and the customerid and month parameters from the master line are not passed to the detail stored procedure.

What am I doing wrong?

 

Thanks,

Alex

 

<asp:ObjectDataSource ID="ods_MonthlySummary" runat="server"
    OldValuesParameterFormatString="original_{0}" SelectMethod="GETMONTHLYSUMMARY"
    TypeName="ETBossWeb.PKG_WEB_CUSTOMER">
    <SelectParameters>
        <asp:ControlParameter ControlID="lbCustomers" Name="CUSTOMERID"
            PropertyName="SelectedValue" Type="Decimal" />
        <asp:SessionParameter Name="userName" SessionField="userName" Type="String" />
    </SelectParameters>
</asp:ObjectDataSource>

<asp:ObjectDataSource ID="ods_CallsForMonth" runat="server"
    OldValuesParameterFormatString="original_{0}" SelectMethod="GETCALLSFORMONTH"
    TypeName="ETBossWeb.PKG_WEB_CUSTOMER">
    <SelectParameters>
        <asp:Parameter Name="CUSTOMERID" Type="Decimal" />
        <asp:Parameter Name="MONTH" Type="DateTime" />
        <asp:SessionParameter Name="userName" SessionField="userName" Type="String" />
    </SelectParameters>
</asp:ObjectDataSource>

<ig:WebHierarchicalDataSource ID="whds_MonthlyData" runat="server">
    <DataViews>
        <ig:DataView ID="ods_MonthlySummary_DefaultView" DataMember="DefaultView"
            DataSourceID="ods_MonthlySummary" />
        <ig:DataView ID="ods_CallsForMonth_DefaultView" DataMember="DefaultView"
            DataSourceID="ods_CallsForMonth" />
    </DataViews>
    <DataRelations>
        <ig:DataRelation ChildColumns="CUSTOMERID,MONTH"
            ChildDataViewID="ods_CallsForMonth_DefaultView" ParentColumns="CUSTOMERID,MONTH"
            ParentDataViewID="ods_MonthlySummary_DefaultView" />
    </DataRelations>
</ig:WebHierarchicalDataSource>

 

 

for completion: the stored procedure calls:

using System;
using System.Collections.Generic;
using System.Linq;               
using System.Web;                
using System.ComponentModel;     
using System.Data;               
using Oracle.DataAccess.Types;   
using Oracle.DataAccess.Client;  
using System.Configuration;      
using Oracle.DataAccess;         
namespace ETBossWeb              
{
    [DataObject]
    public class PKG_WEB_CUSTOMER
    {
        private static string oracleSchema = "ESP_CRM";
        private static string oraclePackage = "PKG_WEB_CUSTOMER";

        private static String OracleCall(String oracleProcedure)
        {
            return String.Format("{0}.{1}.{2}", oracleSchema, oraclePackage, oracleProcedure);
        }

        [Serializable]
        public class CALLSFORMONTH
        {
            [DataObjectField(false)]
            public System.Decimal CUSTOMERID { get; set; }
            [DataObjectField(false)]
            public System.DateTime MONTH { get; set; }
            [DataObjectField(false)]
            public System.String ANUMBER { get; set; }
            [DataObjectField(false)]
            public System.String BNUMBER { get; set; }
            [DataObjectField(false)]
            public System.DateTime STARTDATE { get; set; }
            [DataObjectField(false)]
            public System.Decimal DURATION { get; set; }
            [DataObjectField(false)]
            public System.Decimal AMOUNT { get; set; }
            [DataObjectField(false)]
            public System.String SERVICETYPE { get; set; }
            [DataObjectField(false)]
            public System.String CALLDIRECTION { get; set; }
            [DataObjectField(false)]
            public System.Decimal CONTACTCUSTOMERID { get; set; }
            [DataObjectField(false)]
            public System.String CONTACTNAME { get; set; }
        }

        [DataObjectMethod(DataObjectMethodType.Select, true)]
        public static List<CALLSFORMONTH> GETCALLSFORMONTH(
            Decimal CUSTOMERID,
            DateTime MONTH,
            string userName)
        {
            List<CALLSFORMONTH> CALLSFORMONTHList = new List<CALLSFORMONTH>();
            using (OracleConnection conn = OracleConnectionManager.getOracleConnection(userName))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand(OracleCall("GETCALLSFORMONTH"), conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new OracleParameter("CUSTOMERID_IN", OracleDbType.Decimal)).Value = CUSTOMERID;
                cmd.Parameters.Add(new OracleParameter("MONTH_IN", OracleDbType.Date)).Value = MONTH;
                cmd.Parameters.Add(new OracleParameter("cur_IN_OUT", OracleDbType.RefCursor)).Direction = ParameterDirection.Output;
                OracleDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    CALLSFORMONTH o = new CALLSFORMONTH();
                    if (!dr.IsDBNull(0)) { o.CUSTOMERID = dr.GetDecimal(0); }
                    if (!dr.IsDBNull(1)) { o.MONTH = dr.GetDateTime(1); }
                    if (!dr.IsDBNull(2)) { o.ANUMBER = dr.GetString(2); }
                    if (!dr.IsDBNull(3)) { o.BNUMBER = dr.GetString(3); }
                    if (!dr.IsDBNull(4)) { o.STARTDATE = dr.GetDateTime(4); }
                    if (!dr.IsDBNull(5)) { o.DURATION = dr.GetDecimal(5); }
                    if (!dr.IsDBNull(6)) { o.AMOUNT = dr.GetDecimal(6); }
                    if (!dr.IsDBNull(7)) { o.SERVICETYPE = dr.GetString(7); }
                    if (!dr.IsDBNull(8)) { o.CALLDIRECTION = dr.GetString(8); }
                    if (!dr.IsDBNull(9)) { o.CONTACTCUSTOMERID = dr.GetDecimal(9); }
                    if (!dr.IsDBNull(10)) { o.CONTACTNAME = dr.GetString(10); }
                    CALLSFORMONTHList.Add(o);
                }
                conn.Close();
            }
            return CALLSFORMONTHList;
        }

        [Serializable]
        public class MONTHLYSUMMARY
        {
            [DataObjectField(false)]
            public System.Decimal CUSTOMERID { get; set; }
            [DataObjectField(false)]
            public System.DateTime MONTH { get; set; }
            [DataObjectField(false)]
            public System.Decimal CALLS { get; set; }
            [DataObjectField(false)]
            public System.Decimal SMS { get; set; }
            [DataObjectField(false)]
            public System.Decimal OTHER { get; set; }
            [DataObjectField(false)]
            public System.Decimal AMOUNT { get; set; }
            [DataObjectField(false)]
            public System.Decimal TAX { get; set; }
        }

        [DataObjectMethod(DataObjectMethodType.Select, true)]
        public static List<MONTHLYSUMMARY> GETMONTHLYSUMMARY(
            Decimal CUSTOMERID,
            string userName)
        {
            List<MONTHLYSUMMARY> MONTHLYSUMMARYList = new List<MONTHLYSUMMARY>();
            using (OracleConnection conn = OracleConnectionManager.getOracleConnection(userName))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand(OracleCall("GETMONTHLYSUMMARY"), conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new OracleParameter("CUSTOMERID_IN", OracleDbType.Decimal)).Value = CUSTOMERID;
                cmd.Parameters.Add(new OracleParameter("cur_IN_OUT", OracleDbType.RefCursor)).Direction = ParameterDirection.Output;
                OracleDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    MONTHLYSUMMARY o = new MONTHLYSUMMARY();
                    o.CUSTOMERID = dr.GetDecimal(0);
                    o.MONTH = dr.GetDateTime(1);
                    o.CALLS = dr.GetDecimal(2);
                    o.SMS = dr.GetDecimal(3);
                    o.OTHER = dr.GetDecimal(4);
                    o.AMOUNT = dr.GetDecimal(5);
                    o.TAX = dr.GetDecimal(6);
                    MONTHLYSUMMARYList.Add(o);
                }
                conn.Close();
            }
            return MONTHLYSUMMARYList;
        }
    }
}                                

 

  • 3147
    posted

    Hi Alex,

    Looking at your code it's hard to tell what's wrong with it. It doesn't look much like an issue with WebHierarchicaDataSource. Are you able to bind directly the ObjectDataSources to some flat data bound control?

  • 5
    posted

    i have the same problem :(

    where do i go wrong?