Streaming 100 megabyte csv files with WCF

by MikeHogg 21. February 2012 20:35

WCF is very configurable, but that is also the thing that I like least about it.  Often I have a project, and a need, and I’m in WCF documentation, and researching how to do exactly what I want, and I get my config just right, tweaked and tested and it works perfectly, but I never learn a new pattern or algorithm.  They are just config options.  Here is a case where I had to play with the buffer limits to be able to stream very large files, but there are more than a few options related to buffer size and so you have to know exactly which ones work for your case.

Here is my client endpoint.  You will see I am using NetTcp.  I’m in an intranet environment.  I added settings for TransferMode(Stream), ReceiveTimeout(ten minutes), also SendTimeout, as the client also uploaded large files too, and MaxReceivedMessageSize(250MB)

 <system.serviceModel>
        <bindings>
            <netTcpBinding>
                <binding name="NetTcpBindingEndpoint" closeTimeout="00:01:00"
                    openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:10:00"
                    transactionFlow="false" transferMode="StreamedResponse" transactionProtocol="OleTransactions"
                    hostNameComparisonMode="StrongWildcard" listenBacklog="10"
                    maxBufferPoolSize="524288" maxBufferSize="65536" maxConnections="10"
                    maxReceivedMessageSize="250000000">
                    <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
                        maxBytesPerRead="4096" maxNameTableCharCount="16384" />
                    <security mode="Transport">
                        <transport clientCredentialType="Windows" protectionLevel="EncryptAndSign" />
                        <message clientCredentialType="Windows" />
                    </security>
                </binding>
            </netTcpBinding>
            <wsHttpBinding>
                <binding name="WSHttpBinding_AnotherClient
                </binding>
            </wsHttpBinding>
        </bindings>
        <client>  
           <endpoint address="anotherendpoint
            </endpoint>
          <endpoint address="net.tcp://somedevserver:8445/SOME_DATA_SERVICE"
                binding="netTcpBinding" bindingConfiguration="NetTcpBindingEndpoint"
                contract="DataServiceReference.IDataService" name="NetTcpBindingEndpoint">
                <identity>
                    <dns value="localhost" />
                </identity>
            </endpoint>
        </client> 
    </system.serviceModel>

You will see similar options in my service config:

 <system.serviceModel>
        <behaviors>
            <serviceBehaviors>
                <behavior name="WindowsService.DataServiceBehavior">
                    <serviceMetadata httpGetEnabled="false" />
                    <serviceDebug includeExceptionDetailInFaults="false" />
                </behavior>
            </serviceBehaviors>
        </behaviors>
      <bindings>
        <netTcpBinding>
          <binding name="NetTcpBinding" maxReceivedMessageSize="250000000" transferMode="StreamedResponse"
                    receiveTimeout="00:10:00" sendTimeout="00:10:00">
            <security mode="Transport">
              <transport clientCredentialType="Windows" protectionLevel="EncryptAndSign"/>
              <message clientCredentialType="Windows"/>
            </security>
          </binding>
        </netTcpBinding>
      </bindings>
        <services>
            <service behaviorConfiguration="WindowsService.DataServiceBehavior"
                name="SOME_DATA_SERVICE.DataService">
                <endpoint address="" binding="netTcpBinding" bindingConfiguration="NetTcpBinding"
                    name="NetTcpBindingEndpoint" contract="SOME_DATA_SERVICE.IDataService">                  
                    <identity>
                        <dns value="localhost" />
                    </identity>
                </endpoint>
                <endpoint address="mex" binding="mexTcpBinding" bindingConfiguration=""
                    name="MexTcpBindingEndpoint" contract="IMetadataExchange" />
                <host>
                    <baseAddresses>
                        <add baseAddress="net.tcp://localhost:8445/SOME_DATA_SERVICE" />
                    </baseAddresses>
                </host>
              
            </service>
          
        </services>
    </system.serviceModel>
  <!-- added this to see trace file -->
  <!--<system.diagnostics>
    <sources>
      <source name="System.ServiceModel"
              switchValue="Information"
              propagateActivity="true">
        <listeners>
          <add name="ServiceModelTraceListener"
               type="System.Diagnostics.XmlWriterTraceListener, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
               initializeData="wcf-traces.svclog"/>
        </listeners>
      </source>
    </sources>
  </system.diagnostics>-->

 

And the c# code to stream:

        private System.IO.MemoryStream GetCSVStream(System.Data.Common.DbDataReader dr)
        {
            Initialize();
            try
            {
                System.IO.MemoryStream ms = new System.IO.MemoryStream();
                System.IO.StreamWriter sw = new System.IO.StreamWriter(ms);
                System.Data.DataTable header = dr.GetSchemaTable();
                foreach (System.Data.DataRow r in header.Rows)
                {
                    string column = r["ColumnName"].ToString();
                    if (column == "ID") column = "id";
                    sw.Write(column);
                    if (header.Rows.IndexOf(r) < header.Rows.Count - 1) sw.Write("|");
                }
                sw.Write(sw.NewLine); sw.Flush();
                while (dr.Read())
                {
                    sw.Write(dr.GetValue(0).ToString());
                    for( int x = 1; x < dr.FieldCount; x++ )
                    {
                        sw.Write("|");
                        sw.Write(dr.GetValue(x).ToString());
                    }
                    sw.Write(sw.NewLine); sw.Flush();
                } 
                ms.Position = 0;
                return ms;
            }
            catch (Exception ex)
            {
                LogError(ex, APPNAME);
                return null;
            }
        } 
and that’s it!

Tags:

WCF

Miscellaneous code to tweak IE access rights on random servers

by MikeHogg 23. January 2012 21:33

I set up a framework where developers could write data parsers for a variety of sources that could be scheduled, normalized, logged, and archived for various purposes.  There were hundreds of data sources and jobs so a lot of them had enough similarity that a framework with standard libraries made sense.  A large portion of these were web scrapes.  (I used WatIn for those- a great mocking solution for our purposes).  I would run into problems, because our scheduler would run on a server farm of hundreds, with new machines/instances being created at intervals, and so we basically had to write everything into our jobs.  Here are some of the hacks I put into a Web library to get usual https sites working with the web scrapes, where I had a variety of popup problems (I needed to turn off IE’s built in popup blocker), file download problems (add domain to trusted sites)…  They are all probably obsolete with Server 2012 now, but they might come in handy for the next few years still…

        private static bool IsPopupEnabled(string domain)
        {
            string keylocation = @"Software\Microsoft\Internet Explorer\New Windows\Allow";
            Microsoft.Win32.RegistryKey cu = Microsoft.Win32.Registry.CurrentUser;
            Microsoft.Win32.RegistryKey parent = cu.OpenSubKey(keylocation);
            if (parent != null) return parent.GetValue(domain) != null;
            else return false;
        }
        private static bool EnablePopup(string domain)
        {
            string keylocation = @"Software\Microsoft\Internet Explorer\New Windows\Allow";
            Microsoft.Win32.RegistryKey cu = Microsoft.Win32.Registry.CurrentUser;
            Microsoft.Win32.RegistryKey parentkey = cu.CreateSubKey(keylocation);
            parentkey.SetValue(domain, Microsoft.Win32.RegistryValueKind.Binary);
            return IsPopupEnabled(domain);
        }
        private static bool TrustedSiteAddition(string domain)
        {
            const string domainsKeyLocation = @"Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\Domains";
     
            Microsoft.Win32.RegistryKey currentUserKey = Microsoft.Win32.Registry.CurrentUser;
            Microsoft.Win32.RegistryKey parentKey = currentUserKey.OpenSubKey(domainsKeyLocation, true);
            Microsoft.Win32.RegistryKey key = parentKey.CreateSubKey(domain);
            object objSubDomainValue = key.GetValue("http");
            if (objSubDomainValue == null || Convert.ToInt32(objSubDomainValue) != 0x02)
            {
                key.SetValue("http", 0x02, Microsoft.Win32.RegistryValueKind.DWord);
            }
            objSubDomainValue = key.GetValue("https");
            if (objSubDomainValue == null || Convert.ToInt32(objSubDomainValue) != 0x02)
            {
                key.SetValue("https", 0x02, Microsoft.Win32.RegistryValueKind.DWord);
            }
            return IsTrusted(domain);
        }
        private static bool IsTrusted(string domain)
        {
            const string domainsKeyLocation = @"Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\Domains";
            string keyLocation = string.Format(@"{0}\{1}", domainsKeyLocation, domain);
            Microsoft.Win32.RegistryKey currentUserKey = Microsoft.Win32.Registry.CurrentUser;
            Microsoft.Win32.RegistryKey foundRegistryKey = currentUserKey.OpenSubKey(keyLocation, false);
            if (foundRegistryKey != null)
                return true;
            else
                return false;
        }
        private static bool CheckFileDownloadEnabled()
        { 
            string keylocation = @"Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\2";
            Microsoft.Win32.RegistryKey cu = Microsoft.Win32.Registry.CurrentUser;
            Microsoft.Win32.RegistryKey parent = cu.OpenSubKey(keylocation,true);
            if (parent != null && (int)parent.GetValue(@"2200") != 0x00)
            {
                parent.SetValue(@"2200", 0x00);
                return true;
            }
            else if (parent != null && (int)parent.GetValue(@"2200") == 0x00)
            {
                return true;
            }
            else return false;
        }
        public static bool IEFullyEnabled(string domain)
        {
            if (!IsPopupEnabled(domain) && !EnablePopup(domain)) return false;
            if (!IsTrusted(domain) && !TrustedSiteAddition(domain)) return false;
            if (!CheckFileDownloadEnabled()) return false;
            return true;
        }

Tags:

Automation | C#

Oracle aggregation query

by MikeHogg 20. January 2012 14:56

One of the processes I set up required a merging of a few different data sources.  Besides our own data store of user input data, we also had our own data store of operational account data, and went to another system to get account billing data, and yet another system for customer contract data.  This query was an aggregation of several queries that, all together, aggregated the exact data we needed for our data warehouse import.  Some of the neat features of this query are the company name partition with replace to account for manual data entry discrepencies…

  cursor c_contracts(p_start_date date, p_end_date date, p_snapshot_date date) is
  Select seq_some.nextval, c.propid, to_char(p_start_date,'YYYYMM'), 
         PRODUCT_NAME ,
         ACCOUNTS_SOLD ,
          case when bui_account.bdm_id is not null then trim(csh_account.bdm)
               when bui_contract.bdm_id is not null then trim(csh_contract.bdm)
               else 'Unknown' end BDM, 
          trim(c.account_bdm) account_bdm,
          trim(c.contract_bdm) contract_bdm,
          busunit_id, 
          c.COMPANY_NAME, 
          c.maxid  COMPANY_ID,
          TRUNC(CONTRACT_TERM_MONTHS,2),
          c.COUNTER_SIGN_DATE,
          TRUNC(TOTAL_CREDIT_COST,2),
          CUSTOMERS_SOLD,
          c.EDC_ACCTNO, 
          c.EDC_NAME,
          c.EST_POWER_END_DATE,
          c.EST_POWER_FLOW_DATE,
          ISO,
          MW_SOLD,
          TRUNC(c.SALES_AGENT_FEE,2),
          c.SALESFEE_CONAME,
          STATE,
          STATUS, 
          TRUNC(ORIGINATION_GROSSMARGIN,2),
          TRUNC(ORIGINATION_NETMARGIN,2),
          TRUNC(ORIGINATION_VOLUME,2),
          c.ZONE,
          c.PRICE_DATE,
          substr(1000000000 + nvl(trim(regexp_replace(regexp_replace(c.duns_no,'-|/|[a-z|A-Z]|\.|[[:space:]]',''),'([[:digit:]]{9})[[:digit:]]*','\1')),'999999999'),2,9) DUNS_NO,
          c.SIC_CODE,
          c.CCM_ID, 
          somebillingsystem.num_quotes,   
          ar.AR_Bal_31_60,
          ar.AR_Bal_61_90,
          ar.AR_Bal_91Plus, 
          ar.bad_debt_balance,
          TRUNC(FM_NET_MARGIN,2) FLOW_Margin,
          TRUNC(FM_VOLUME,2)  FLOW_Volume,
          s_billing.bill_type,                    ----------  BILL TYPES AND OPTIONS
          s_billing.billing_option,
          trim(Green_Volume) ,
          trim(REC_GREEN_VOLUME),
          FM_Sales_Agent_Fee, 
          c.leg_month,
          c.leg_year,
          c.annual_mwh,
          contract_type, 
          NON_STD_TERMS_FLG,
          TELESALES_FLG,
          somebillingsystem.broker2,
          somebillingsystem.ou_type_cd,
          somebillingsystem.x_sub_type,
          ls.Billing_Weight,
          somebillingsystem.early_renewal,
          c.servzip,
          c.acquisition_type,
          1, p_snapshot_date 
          , c.OFFER_TYPE
        from (  select e.*, max(e.cust_id) over (partition by replace(replace(e.company_name,','),'.')) maxid
                       , NULL OFFER_TYPE
                  from some_UNI_CONTRACT_SOME_EXTRACT e 
                 union 
                select null customers_sold, null accounts_sold, 
                       r.MASTERCONTRACT_OUR_NUM propid, r.CONTRACT_STATUS status,
                       r.PRODUCTNAME product_name, r.BDM account_bdm, 'RESI' company_name, 
                       r.BDM contract_bdm, to_char(r.COUNTER_SIGN_DATE,'dd-MON-yy') COUNTER_SIGN_DATE, 
                       '1' cust_id, to_char(r.EST_POWER_FLOW_DATE,'dd-MON-yy') EST_POWER_FLOW_DATE, to_char(r.EST_POWER_END_DATE,'dd-MON-yy') EST_POWER_END_DATE, 
                       r.LEAD_SEGMENT, null mw_Sold, null product_group, r.BUSUNIT_NAME region,
                       null sales_agent_fee, null salesfee_coname, null sic_code, r.STATE, r.ZONE, null LEG_MONTH, null LEG_YEAR, null fm_net_margin, 
                       null fm_volume, null fm_sales_agent_fee, r.TOTAL_MARGIN origination_netmargin, r.TOTAL_VOLUME origination_volume, 
                       null origination_grossmargin, r.TOTAL_CREDITCOST total_credit_cost, null duns_no, null busunit_id, r.ISO, 
                       null price_date, null ccm_id, null rec_green_volume, null green_volume, r.UDC_NAME edc_name, r.contract_term contract_term_months, 
                       r.EDC_ACCTNO, r.ZIP servzip, r.ANNUAL_MWH, null contract_type, null non_std_terms_flg, null telesales_flg, null acquisition_type, '1'
                       , r.OFFER_TYPE
                from (select distinct MASTERCONTRACT_OUR_NUM, CONTRACT_STATUS, PRODUCTNAME, BDM,  
                            COUNTER_SIGN_DATE, EST_POWER_FLOW_DATE, EST_POWER_END_DATE, LEAD_SEGMENT, 
                            BUSUNIT_NAME, STATE, ZONE, 
                            sum(TOTAL_MARGIN) over (partition by mastercontract_our_num) TOTAL_MARGIN, 
                            sum(TOTAL_VOLUME) over (partition by mastercontract_our_num) TOTAL_VOLUME, 
                            sum(TOTAL_CREDITCOST) over (partition by mastercontract_our_num) TOTAL_CREDITCOST, 
                            ISO, UDC_NAME, contract_term, 
                            EDC_ACCTNO, ZIP, ANNUAL_MWH 
                            , OFFER_TYPE
                       from cer_res.residential) r
             )c
        
        -------------- somebillingsystem 
        
        left join ( 
                   select ag.agree_num, (select brk.name from s_org_ext@sblreadonly brk where brk.row_id = ag.x_broker_2_account_id) broker2, 
                         (select count(q.row_id) from s_doc_quote@sblreadonly q where q.quote_num = qu.quote_num) num_quotes,
                         round(months_between(sysdate,co.cust_since_dt),2) cust_accumulated_term, co.x_sub_type, co.ou_type_cd,
                         case when exists (select ai.row_id from somebillingsystem.s_agree_item@sblreadonly ai 
                                             join somebillingsystem.s_org_ext@sblreadonly sa on ai.x_sa_ou_id = sa.row_id
                                            where ag.row_id = ai.doc_agree_id 
                                              and ai.itm_stat_cd = 'Pending' and sa.cust_stat_cd = 'Enrolled'
                                              and ag.stat_cd = 'Pending' and ag.x_offer_type = 'Renewal' ) then 1 else 0 end Early_Renewal 
                    from S_DOC_AGREE@sblreadonly AG 
                    JOIN S_DOC_QUOTE@sblreadonly QU  ON AG.QUOTE_ID = QU.ROW_ID(+)
                    JOIN S_ORG_EXT@sblreadonly CO ON AG.TARGET_OU_ID = CO.ROW_ID               
        )somebillingsystem on c.propid = somebillingsystem.agree_num 
        
        -------------   BILL OPTIONS
        
        left join ( select ag.agree_num, 
                           min(bp.x_group_bill)  billing_option, -- Multi over Single
                           min(bp.bill_pref_cd) bill_type -- SOME (Charges) over UDC (Rate) 
                      from s_doc_agree@sblreadonly ag 
                      join s_agree_item@sblreadonly ai on ag.row_id = ai.doc_agree_id
                      join s_org_ext@sblreadonly sa on ai.x_sa_ou_id = sa.row_id
                      join s_party_rel@sblreadonly pr on sa.row_id = pr.rel_party_id 
                                                     and pr.rel_type_cd = 'Bill Group' 
                                                     and pr.x_primary_bill_group = 'Y'
                      join s_org_ext@sblreadonly bg on pr.party_id = bg.row_id 
                      join S_INV_PROF@sblreadonly bp on bg.row_id = bp.accnt_id 
                     group by ag.agree_num
           ) s_billing on s_billing.agree_num = c.propid
        
           
        ---------    AR_BALANCE  
        left join (select contract_id, /*ar.edc_acctno, */sum(ar.ar_bal_current) ar_bal_current, sum(ar.ar_bal_1_30) ar_bal_1_30 , 
                          sum(ar.ar_bal_31_60) ar_bal_31_60, sum(ar.ar_bal_61_90) ar_bal_61_90, 
                          sum(ar.ar_bal_91plus) ar_bal_91plus, ar.snapshot_date, round(sum(bad.balance),2) bad_debt_balance
                     from (select x.contract_id, x.edc_acctno, sum(b.ar_bal_current) ar_bal_current, sum(b.ar_bal_1_30) ar_bal_1_30 , 
                                  sum(b.ar_bal_31_60) ar_bal_31_60, sum(b.ar_bal_61_90) ar_bal_61_90, 
                                  sum(b.ar_bal_91plus) ar_bal_91plus, b.snapshot_date
                             from some_uni_cust_arb b
                             join some_uni_edc_x_propid x on b.edc_acctno = x.edc_acctno and x.snapshot_date = b.snapshot_date
                            where b.snapshot_date = (select max(snapshot_date) from some_uni_cust_arb where snapshot_date <= p_end_date)
                              --and x.contractid = '1-220606257'
                            group by x.contract_id, x.edc_acctno, b.snapshot_date
                           ) ar
                      -- BAD DEBT LOGIC : ave of EDCs, summed up at contract level, so three EDCs in a contract have 24 month avgs of 12, 1250, 650 bad debt show at contract 1912
                      join (select b.edc_acctno, avg(b.ar_bal_91plus) balance
                              from (select a.edc_acctno,trunc(a.snapshot_date,'MM'),  max(a.snapshot_date) maxdate 
                                      from some_uni_cust_arb a group by a.edc_acctno, trunc(snapshot_date, 'MM')
                                   ) maxdate
                              join some_uni_cust_arb b on maxdate.edc_acctno = b.edc_acctno and maxdate.maxdate = b.snapshot_date 
                              where b.snapshot_date between add_months(p_start_date,-23) and p_end_date 
                              group by b.edc_acctno  
                           ) bad on ar.edc_acctno = bad.edc_acctno
                      group by ar.contract_id, ar.snapshot_date
        ) ar on ar.contract_id = c.propid        
  
        ---------    WORK QUEUES AND BILLG MODES 
        left join ( 
                     select contractid,
                            -- for breakout analysis- remove grouping if you run this query alone
                            /*min(billgroupoption) over(partition by contractid) billgroupoption, -- M trumps S 
                            max(bgbmf) over(partition by contractid) bgbmf, -- in case of multiple just get the highest
                            max(ssomef) over(partition by contractid) ssomef, 
                            q.uidwqitem, q.openedtime ,q.wqtypecode, q.wqqueuecode, q.weight 
                            ,sum(nvl(weight,0)) over(partition by contractid) sumweightpercontract*/  
                            decode(min(billgroupoption),'M',max(bgbmf), max(ssomef)) + sum(weight) Billing_Weight
                      from (
                             select con.contractid,
                                    h.billgroupoption,  
                                    bg.billingmodeflag bgbmf, 
                                    sa.billingmodeflag ssomef,   
                                    --wq.uidwqitem, wq.openedtime ,wq.wqtypecode, wq.wqqueuecode, 
                                    wq.weight                             
                              from (select contractid, uidaccount 
                                      from (select c.contractid, ci.uidaccount, max(c.contractid) over (partition by ci.uidaccount) maxid
                                              from (select uidaccount, uidcontract from CUSTOMERSYS.lscmcontractitem@ls_link) ci 
                                              join (select contractid, starttime, stoptime, uidcontract, executetime 
                                                      from CUSTOMERSYS.lscmcontract@ls_link) c on ci.uidcontract = c.uidcontract and c.executetime is not null
                                             where c.starttime < p_end_date and c.stoptime > add_months(p_start_date,-2)
                                            ) where contractid = maxid 
                                     ) con   
                              
                              -- SUBSELECT to pick one(can't tell which is correct) when LS has bad dupes
                              left join (select uidaccount, accountid, billingmodeflag   
                                       from (select a.uidaccount, a.accountid, a.udcacctid, a.billingmodeflag,
                                                    a.lstime, max(a.lstime) over (partition by a.udcacctid) maxlstime
                                               from CUSTOMERSYS.ACCOUNT@ls_link a
                                              where a.accounttypecode = 'SERVICE_ACCOUNT' ) 
                                       where maxlstime = lstime
                                     ) sa on sa.uidaccount = con.uidaccount      
                
                             LEFT JOIN (select lci.accountid, lci.wqtypecode, lci.wqqueuecode, q.weight--, lci.uidwqitem, lci.openedtime
                                         from CUSTOMERSYS.LSWQCLOSEDITEM@ls_link  lci                                                       
                                         join some_work_queue q on lci.WQTYPECODE = q.type and lci.WQQUEUECODE = q.code 
                                        where CLOSEDTIME Between add_months(p_start_date,-2) and p_end_date     
                                        ) wq  ON wq.accountid = sa.accountid           
                                                                                      
                              -- SUBSELECT to pick one(can't tell which is correct) when LS has bad overlap
                             left JOIN (select uidaccountfrom, uidaccountto   
                                      from (select l.uidaccountfrom, l.uidaccountto, 
                                                   max(l.uidaccountfrom) over (partition by l.uidaccountto) maxbgacctid
                                             from CUSTOMERSYS.LSACCOUNTREL@ls_link l
                                            where l.starttime < p_end_date and l.stoptime > add_months(p_start_date,-2))
                                      where uidaccountfrom = maxbgacctid
                                    )x ON sa.UIDACCOUNT = x.UIDACCOUNTTO                                                 
                             left join CUSTOMERSYS.ACCOUNT@ls_link bg on bg.UIDACCOUNT = x.UIDACCOUNTFROM 
                                                                and bg.accounttypecode = 'BILLING_GROUP'  
                             left JOIN (select billgroupoption, uidaccount
                                      from (select ah.billgroupoption, ah.uidaccount, 
                                                   ah.starttime, min(ah.starttime) over (partition by ah.uidaccount) maxstart
                                              from CUSTOMERSYS.ACCOUNTHISTORY@ls_link ah  )
                                     where starttime = maxstart
                                   )h ON bg.UIDACCOUNT = h.UIDACCOUNT  
                                   
                            ) q group by contractid  
                 
         ) ls on ls.contractid = c.propid 
                   
        
        -----------   BDM JOIN     
        left join main_rpt.SOME_sales_hierarchy_new csh_account on UPPER(c.account_bdm) = UPPER(csh_account.bdm_crm)
        left join some_uni_bdm bui_account on csh_account.employee_id = bui_account.bdm_id and bui_account.month = to_char(p_start_date,'YYYYMM')
        left join main_rpt.SOME_sales_hierarchy_new csh_contract on UPPER(c.contract_bdm) = UPPER(csh_contract.bdm_crm)
        left join some_uni_bdm bui_contract on csh_contract.employee_id = bui_contract.bdm_id and bui_contract.month = to_char(p_start_date,'YYYYMM')
        
        where trim(c.propid) is not null
        and c.est_power_end_date >= p_start_date 
        and c.counter_sign_date  <= p_end_date;   
the subselects for AR balances, and the Bad Debt aggregations over different partitions… the Decode for deciding which weights to use in calculations… and picking distinct values from an external system that had inherent 1-3% data anomalies.

Tags:

Oracle

Using a Data Sandbox to allow users to push their own data changes to Prod

by MikeHogg 14. November 2011 19:03

Most Pl/Sql code is fairly boring, selecting lots of columns from lots of joins and maybe doing calculations and decodes on the fly, or groupings and other aggregations, and sub queries.  But one project I had was pretty neat.  Users had two lists of Products, and although the names and attributes were not usually the same, they were the same, just called different things by different companies. 

When we started using a new Product, we had to find the two matching ones and map them together.  This involved a process that ultimately led to what we called a data fix.  No code was changed, but data that the business process relied on had to be created or changed in the production database.  To do this required an IT resource to team up with a business resource and first make the changes in a test environment, and then test, and make adjustments, and test again, and then do the same exact cumulative change they did in the test environment, in the production environment. 

This was time intensive, and highly manual.  I wanted to make this quick, and automatic, without any room for manual errors.  So one of the features of the new tool I built was for the users to make these changes themselves, in a test environment, with full auditing and editing capability, and be able to see for themselves the effects their changes had, and when they were ready, push a button to have the exact same results applied to their production environment. This worked wonderfully, and worked with full auditing and history tracking in case any recovery was necessary. Besides some finely tuned controls in the new UI, all it required was an Oracle database link to the test environment.

 

  procedure PushToProd(p_audit_user varchar2,
                       p_id         my_product.id%type,
                       p_note       varchar2) is
  
  begin
  
    insert into my_product_history
      (audit_user,  audit_date,  type,
       id,  name, pf_code,  pf_type,  
       cc_code, exch_id,  gs_name,
       pf_commodity, lot_size,  uom_cd, 
       some_lot_sz, term, opt_style,
       product_type,  calendar,  location,
       description_id, trader_group,  settle_date_rule,
       eot_date_rule,  uses_dst, exchange)
      (select p_audit_user,   sysdate, 'PTP',
              nvl(p.id, pp.id),  nvl(p.name, pp.name),  nvl(p.pf_code, pp.pf_code),  nvl(p.pf_type, pp.pf_type),
              nvl(p.cc_code, pp.cc_code),  nvl(p.exch_id, pp.exch_id),  nvl(p.gs_name, pp.gs_name),
              nvl(p.pf_commodity, pp.pf_commodity),  nvl(p.lot_size, pp.lot_size),  nvl(p.uom_cd, p.uom_cd),
              nvl(p.some_lot_sz, pp.some_lot_sz),  nvl(p.term, pp.term),   nvl(p.opt_style, pp.opt_style),
              nvl(p.product_type, pp.product_type),  nvl(p.calendar, pp.calendar),  nvl(p.location, pp.location),
              nvl(p.description_id, pp.description_id),  nvl(p.trader_group, pp.trader_group),  nvl(p.settle_date_rule, pp.settle_date_rule),
              nvl(p.eot_date_rule, pp.eot_date_rule),  nvl(p.uses_dst, pp.uses_dst), nvl(p.exchange, pp.exchange)
         from my_product p
         full outer join my_product@SOMEW_LINK pp on p.id =
                                                                  pp.id -- handles new 
        where p.id = p_id
           or pp.id = p_id);
  
    pkg_SOME_log.WRITEMESSAGE('pkg_ProductMapper.PushToProd',
                              'DEBUG',
                              'Note:' || p_note || '; ' || SQL%ROWCOUNT ||
                              ' new record(s) inserted by User ' ||
                              p_audit_user || ' in my_product_history');
  
    delete from my_product where id = p_id;
    delete from some_fee where product_id = p_id;
  
    insert into my_product
      (select * from my_product@SOMEW_LINK where id = p_id);
  
    pkg_SOME_log.WRITEMESSAGE('pkg_ProductMapper.PushToProd',
                              'DEBUG',
                              'Note:' || p_note || '; ' || SQL%ROWCOUNT ||
                              ' new record(s) inserted by User ' ||
                              p_audit_user || ' in my_product');
  
    insert into some_fee
      (select *
         from SOME.some_fee@SOMEW_LINK
        where product_id = p_id);
  
    pkg_SOME_log.WRITEMESSAGE('pkg_ProductMapper.PushToProd',
                              'DEBUG',
                              'Note:' || p_note || '; ' || SQL%ROWCOUNT ||
                              ' new record(s) inserted by User ' ||
                              p_audit_user || ' in some_fee');
  
  exception
    when others then
       
      rollback;
      raise;
  end;
  procedure PushMappingToProd(p_audit_user varchar2,
                              p_v_id       my_values.id%type,
                              p_note       varchar2) is
  
  begin
  
    insert into my_values_history
      (audit_user,  audit_date,  type,
       id,   key,  value,
       val_type,   schema,  rule,  exch_id)
      select p_audit_user,  sysdate,  'PTP',
             nvl(v.id, vv.id),  nvl(v.key, vv.key),  nvl(v.value, vv.value),
             nvl(v.val_type, vv.val_type),  nvl(v.schema, vv.schema),
             nvl(v.rule, vv.rule),  nvl(v.exch_id, vv.exch_id)
        from my_values v
        full outer join my_values@SOMEW_LINK vv on v.id =
                                                                vv.id
       where v.id = p_v_id
          or vv.id = p_v_id;
  
    pkg_SOME_log.WRITEMESSAGE('pkg_ProductMapper.PushMappingToProd',
                              'DEBUG',
                              'Note:' || p_note || '; ' || SQL%ROWCOUNT ||
                              ' record(s) inserted by User ' ||
                              p_audit_user || ' in my_values_history');
  
    delete from my_values where id = p_v_id;
  
    insert into my_values
      (select * from my_values@SOMEW_LINK where id = p_v_id);
  
    pkg_SOME_log.WRITEMESSAGE('pkg_ProductMapper.PushMappingToProd',
                              'DEBUG',
                              'Note:' || p_note || '; ' || SQL%ROWCOUNT ||
                              ' new record(s) inserted by User ' ||
                              p_audit_user || ' in my_value');
  
  exception
    when others then
      
      rollback;
      raise;
  end;

 

I also put some auditing features in the new UI.  The system relied on the premise that the two databases to start with exact primary key ids and to keep them in sync between the two systems.  I built several auditing screens, showing even the detail for each user’s changes in each environment, so that more thatn one user could see what other users had done.  Here’s one of the auditing tools that showed the user the differences between some of the aggregations of the two systems.  This is the boring sql I was talking about before.  This also works with that oracle database link.

  procedure ViewLotDiff(p_valuation_date in me_trade.valuation_date%type,
                        cur_out          out cur) is
  
  begin
    -- Added condition v.expiration_date >=  p_valuation_date    
    open cur_out for
      select gg.anothercompany_name,
             max(pf_code),
             case
               when sum(some_w_lots) <>  sum(gs_lots) then
                 case when sum(some_p_lots) <>  sum(gs_lots) then 'BOTH'
                 else 'some_W' end 
               else
                'some_P'
             end as issue,            
             sum(some_w_lots) total_some_w_lots,
             sum(some_p_lots) total_some_p_lots,
             sum(gs_lots) total_gs_lots            
        from (select some_w_lots,
                     some_p_lots,
                     gs_lots,
                     src,
                     pf_code,
                     anothercompany_name 
                from (select round(sum(v.lots),1) some_w_lots,
                             0 some_p_lots,
                             0 gs_lots,
                             'some_W' src,
                             v.PF_CODE,
                             v.anothercompany_NAME 
                        from some_.MAP_A_VW v
                       where 1 = 1
                         and v.VALUATION_DATE = p_valuation_date
                         and (v.term <> 'DAY' or
                             v.contract_month >= p_valuation_date)
                         and ( ( v.pf_commodity in ('WEA','WTI') and nvl(v.expiration_date,to_date('12/31/9999','mm/dd/yyyy')) >= p_valuation_date )
                              or 
                             ( v.pf_commodity not in ('WEA','WTI') and nvl(v.settle_date,to_date('12/31/9999','mm/dd/yyyy')) >= trunc(to_date(p_valuation_date),'MM')))
                       group by v.anothercompany_NAME, v.PF_CODE )
              union (select 0 some_w_lots,
                           round(sum(d.lots),1) some_p_lots,
                           0 gs_lots,
                           'some_P' src,
                           d.pf_code,
                           p.GS_NAME anothercompany_NAME
                      from some_.me_trade_detail@some_P_LINK d
                      join some_.me_trade_header@some_P_LINK h on d.eti = h.eti
                      join some_.me_product@some_P_LINK p on d.pf_code = p.pf_code and d.pf_type = p.pf_type and h.exchange = p.exchange
                     where 1 = 1
                       and d.VALUATION_DATE = p_valuation_date
                       and nvl(d.some__status,'nvl') <> 'ORI'
                       and (h.term <> 'DAY' or
                           d.contract_month >= p_valuation_date)
                       and ( ( p.pf_commodity in ('WEA','WTI') and nvl(d.expiration_date,to_date('12/31/9999','mm/dd/yyyy')) >= p_valuation_date  )
                              or 
                             ( p.pf_commodity not in ('WEA','WTI') and nvl(d.settle_date,to_date('12/31/9999','mm/dd/yyyy')) >= trunc(to_date(p_valuation_date),'MM')))
                     group by p.GS_NAME, d.pf_code )
              union (select 0 some_w_lots,
                           0 some_p_lots,
                           sum(g.net_qty) gs_lots,
                           'GS' src,
                           '' pf_code,
                           g.prod_description anothercompany_name 
                      from gsd_open_positions g
                     where 1 = 1
                       and g.stmt_date = p_valuation_date                       
                       and g.expiry_date >= p_valuation_date
                     group by g.prod_description )              
              ) gg 
       group by anothercompany_name
      having ( (sum(some_w_lots) - sum(some_p_lots) <> 0) or (sum(gs_lots) - sum(some_p_lots) <> 0) or (sum(gs_lots) - sum(some_w_lots) <> 0) )
       order by anothercompany_NAME;
  end;

Using Moq to write Unit tests in VS2008

by MikeHogg 5. October 2011 18:26

I’ve read about Test Driven Development, and after trying it out myself I have noticed a difference in the code that I write, even if I am not using it for a particular project.  My classes have become simpler, my functions more atomic.  I write more for the Open Closed principle, that my classes are closed for modification, but open for extension, and I find my projects are more organized and development seems to be quicker even, as the base classes get written quicker, and then additional features drop right into place as if they were expected, rather than the spaghetti code that I used to eventually end up with.

There is a difference between Unit tests and other kinds of tests.  I have heard other developers say that they can’t test because most of their code is dependent upon some specific data layer, or web UI, and I think they might not know that Integration Testing is different than Unit testing.  This was what I meant about changing my writing style.  TDD prompted me to write smaller more abstract classes, with unit testable atomic functions, and then to write application specific interfaces and more specific inherited classes. 

In some cases, though, your tests cross the line, and you need integration testing, but there are several Mocking libraries out there now that make it easy to do this.  The main ones all appear to be similar.  Here is a simple project where I used Moq to fake my Data Service.  The Project had a few components.  The end user UI was WPF, and was built with MVVM. 

Because these UI related elements are decoupled from the actual UI in MVVM, I can unit test them.  Here are some tests for the MainViewModel, which takes a user in Constructor Dependency Injection.

        [TestMethod()]
        [DeploymentItem("AUM.exe")]
        public void CanRestartServiceTest()
        {
            MainViewModel_Accessor target = new MainViewModel_Accessor(AdminUser); 
            Assert.IsTrue(target.CanRestartService());
            target = new MainViewModel_Accessor(ReadonlyUser);
            Assert.IsFalse(target.CanRestartService());
        }
 
        [TestMethod()]
        [DeploymentItem("AUM.exe")]
        public void CanLoadAllTablesTest()
        {
            MainViewModel_Accessor target = new MainViewModel_Accessor(AdminUser); 
            Assert.IsTrue(target.CanLoadAllTables());
            
            target._backgroundmanager.IsRunning = true;
            Assert.IsFalse (target.CanLoadAllTables());
            target = new MainViewModel_Accessor(ReadonlyUser);
            Assert.IsFalse(target.CanLoadAllTables());
        }
  
          
  
        [TestMethod()]
        [DeploymentItem("AUM.exe")]
        public void StartProgressBarTest()
        {
            MainViewModel_Accessor target = new MainViewModel_Accessor(AdminUser); 
            target.StartProgressBar();
            Assert.IsTrue(target.ProgressVisibility == Visibility.Visible);
            Assert.IsTrue(target.ProgressValue == 0);
        }
 
        
        [TestMethod()]
        [DeploymentItem("AUM.exe")]
        public void BM_TimerFinishedTest()
        {
            MainViewModel_Accessor target = new MainViewModel_Accessor(AdminUser); 
            TimerFinishedEventArgs e = new TimerFinishedEventArgs();
            e.Message = "TickTock";
            target.BM_TimerFinished(new object(), e);
            Assert.IsTrue(target.ProgressVisibility == Visibility.Hidden);
            Assert.IsTrue(target.Dialog.Visibility == Visibility.Visible);
            Assert.IsTrue(target.Dialog.Title.Contains("Exceeded"));
        }
        [TestMethod()]
        [DeploymentItem("AUM.exe")]
        public void HandleErrorTest()
        {
            MainViewModel_Accessor target = new MainViewModel_Accessor(AdminUser); 
            target.HandleError(new Exception("Test Exception"));
            Assert.IsTrue(target.Error.Visibility == Visibility.Visible);
            Assert.IsTrue(target.Error.Message.Contains("Test Exception"));
        }
        

I created all of my authorization features as properties on the VM, and then exposed Enabled in the UI to them, so I can test if the VM is Enabled to Admin users or Readonly users, as I do in CanRestartServiceTest.  I also disable certain controls through this mechanism when running certain background jobs, and CanGetTablesTest tests that.  I have a progressbar control, also with its own VM, and hook into it and expose a property called ProgressVisibility in the Main VM, so the StartProgressBarTest can test that it is working.  This UI runs several Background jobs and I wrote a custom BackgroundJobManager class to manage all of them.  BM_TimerFinishedTest tests against one of the behaviors of the manager as it is implemented in the VM.  And HandleErrorTest tests against the DialogErrorVM I am using in the Main VM.  So with MVVM it is possible to write unit tests of a sort for your UI components.

So, it’s great that I can in some way test the UI plumbing, but most of what this app does is interact with a windows service, a DTS package through a COM wrapper, and a Web service, and MVVM doesn’t help me test these integration layer features.  In the past, since I have written each of these as interfaces, I would have to write test classes for each of these.  These would be just stubs, returning dummy data.  And that is fine, but with a Mocking library, you no longer have to write all those test classes.  You can usually write up your mocked class and dummy results in a line or two.  And there is a load of generic flexibility built into it. 

Here I am setting up a mocked object based on my IDataService interface, with one instantiated method, StageAcorn(Datetime) which I set to take Any DateTime as an argument.  My MVVM method takes an IDataService injected as an argument, and so I can now test my method,  without writing any IDataService test stub.

        [TestMethod()]
        [DeploymentItem("AUM.exe")]
        public void StageTablesTest()
        {
            MainViewModel_Accessor target = new MainViewModel_Accessor(AdminUser); 
            var mock = new Moq.Mock<AUM.DataServiceReference.IDataService>();
            mock.Setup<string>(f => f.StageAcorn(Moq.It.IsAny<DateTime>())).Returns("DTSER is Success");
            target.StageTables(mock.Object);
            
            Assert.IsTrue(target.Dialog.Visibility == Visibility.Visible);
            Assert.IsTrue(target.Dialog.Message.Contains("Success"));
            Assert.IsFalse(target.IsInError);
        }

Here are a couple other similar, super simple tests also using mocked interfaces…

        [TestMethod()]
        [DeploymentItem("AUM.exe")]
        public void GetSavePathTest()
        { 
            var mock = new Moq.Mock<IFileDialogService>();
            mock.Setup(f => f.GetSaveFolder()).Returns("testfolderpath");
            Assert.AreEqual("testfolderpath", MainViewModel_Accessor.GetSavePath(mock.Object));
        } 
        [TestMethod()]
        [DeploymentItem("AUM.exe")]
        public void RestartServiceTest()
        {
            MainViewModel_Accessor target = new MainViewModel_Accessor(AdminUser); 
           
            var mock = new Moq.Mock<AUM.RestarterServiceReference.IRestarterService>();
            mock.Setup(f => f.StopAcornAppService()).Returns(true);
            mock.Setup(f => f.StartAcornAppService()).Returns(true);
            
            target.RestartService(mock.Object);
            Assert.IsTrue(target.ProgressVisibility == Visibility.Visible);
            bool running = true; int testtimeout = 0;
            while (running)
            {
                if (target.ProgressVisibility == Visibility.Hidden) running = false;
                System.Threading.Thread.Sleep(100);
                if (testtimeout++ > 200)
                {
                    Assert.Inconclusive("Test Timeout");
                    running = false;
                }
            }
            Assert.IsFalse(target.IsInError);
        }
 

 

These are very simple examples, and not every method on every class is meant to be unit tested, but they show how easy it is to get started with MS testing projects.

Tags:

C# | Automation | Testing

Creating large tables in Oracle with dynamic partitions to allow for instant reads

by MikeHogg 29. September 2011 14:55

A lot of my work in Oracle was with tables between a million rows and a billion, and so indexing and query performance was always part of writing the system.  In this case I knew my table was going to grow a quarter to a half million rows a day, and so to retain immediate selects, I needed partitions.  While it is easy to build a table with the initial partitions, you have to maintain the scheme with a nightly or a weekly job.  Here is the initial table creation:

create   table SOME_CONTRACT 
(
  ID                             NUMBER not null,
  CONTRACT_ID                    VARCHAR2(150) not null,
  MONTH                          NUMBER not null,
  CUSTOMER_ID                    VARCHAR2(150),
  BUSUNIT_ID                     VARCHAR2(25),
  SEGMENT_ID                     VARCHAR2(25),
  PRODUCT_ID                     VARCHAR2(100),
  <snip some hundred columns>
  ZONE_MARKER                    VARCHAR2(50),
  ACTIVE_FG                      NUMBER,
  SNAPSHOT_DATE                  DATE,
  AUDIT_DATE                     DATE
)
tablespace SOME_TBS01
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  )
partition by range (snapshot_date)(  
partition P20110101 values less than (TO_DATE(' 2011-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))    tablespace CER_ABM_TBS01    pctfree 0    initrans 1    maxtrans 255    storage    (      initial 64K      minextents 1      maxextents unlimited    ),
partition P20110201 values less than (TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))    tablespace CER_ABM_TBS01    pctfree 0    initrans 1    maxtrans 255    storage    (      initial 64K      minextents 1      maxextents unlimited    ),
partition P20110301 values less than (TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))    tablespace CER_ABM_TBS01    pctfree 0    initrans 1    maxtrans 255    storage    (      initial 64K      minextents 1      maxextents unlimited    ),
partition P20110401 values less than (TO_DATE(' 2011-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))    tablespace CER_ABM_TBS01    pctfree 0    initrans 1    maxtrans 255    storage    (      initial 64K      minextents 1      maxextents unlimited    ),
partition P20110501 values less than (TO_DATE(' 2011-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))    tablespace CER_ABM_TBS01    pctfree 0    initrans 1    maxtrans 255    storage    (      initial 64K      minextents 1      maxextents unlimited    ),
partition P20110601 values less than (TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))    tablespace CER_ABM_TBS01    pctfree 0    initrans 1    maxtrans 255    storage    (      initial 64K      minextents 1      maxextents unlimited    ),
partition P20110701 values less than (TO_DATE(' 2011-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))    tablespace CER_ABM_TBS01    pctfree 0    initrans 1    maxtrans 255    storage    (      initial 64K      minextents 1      maxextents unlimited    ),
partition P20110801 values less than (TO_DATE(' 2011-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))    tablespace CER_ABM_TBS01    pctfree 0    initrans 1    maxtrans 255    storage    (      initial 64K      minextents 1      maxextents unlimited    ),
partition P20110901 values less than (TO_DATE(' 2011-09-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))    tablespace CER_ABM_TBS01    pctfree 0    initrans 1    maxtrans 255    storage    (      initial 64K      minextents 1      maxextents unlimited    ),
partition P20110902 values less than (TO_DATE(' 2011-09-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  partition P20110905 values less than (TO_DATE(' 2011-09-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  partition P20110906 values less than (TO_DATE(' 2011-09-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  partition P20110907 values less than (TO_DATE(' 2011-09-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  partition P20110908 values less than (TO_DATE(' 2011-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  partition P20110909 values less than (TO_DATE(' 2011-09-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  partition P20110912 values less than (TO_DATE(' 2011-09-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  partition P20110913 values less than (TO_DATE(' 2011-09-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  partition P20110914 values less than (TO_DATE(' 2011-09-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  partition P20110915 values less than (TO_DATE(' 2011-09-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  partition PMAX values less than (TO_DATE(' 2099-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
)
-- Create/Recreate primary, unique and foreign key constraints 
alter table SOME_CONTRACT
  add constraint PK_SOMECONTRACTS_ID primary key (ID)
  using index 
  tablespace SOME_TBS01
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
 
create unique index UK_SOME_PK_CONTR_ACTIVE on SOME_CONTRACT (DECODE(ACTIVE_FG,1,CONTRACT_ID||TO_CHAR(MONTH)))
  tablespace SOME_TBS01
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

So new rows get inserted into PMAX each day in a daily process.  Partitions in Oracle are like separate tables, and in our case, most of our select queries will always be on snapshot_date, so it is like we are querying a single table.  We can also add indices if we want.  I think in my case here I was using an active flag and so I added a composite index with the PK and the column Active_fg.

To maintain this scheme, you have to periodically add new partitions, and split up PMAX.  I did that with a scheduled job, that ran a Stored Proc each night.

procedure Maintain_Partitions is
  cursor c_nightly_deletes is
            select sdate 
             from (select distinct snapshot_date sdate, count(distinct snapshot_date) over (partition by month) total
                     from SOME_contract_nightly 
                    where month = to_char(  sysdate  ,'YYYYMM')   
                    order by snapshot_date ) 
            where rownum < total - 3;  -- keep three latest in any given month
            
  cursor c_compress_ddl (p_table_name varchar2 ) is
         select 'alter table ' || p_table_name || ' move partition ' || partition_name || ' compress' ddl, partition_name
           from user_tab_partitions
          where table_name = p_table_name
            and partition_name not like '%MAX%'
            and (compression <> 'ENABLED'
                 or -- recompress NIGHTLYs since those older than 3 days are being deleted
                 table_name = 'SOME_CONTRACT_NIGHTLY' and partition_name like 'P' || to_char(sysdate,'YYYYMM') ||'%'
                 );
            
  cursor c_truncate_ddl ( p_table varchar2  ) is
         select 'alter table ' || table_name || ' truncate partition ' || partition_name || ' update global indexes' as ddl, partition_name
           from user_tab_partitions
          where table_name = p_table
            and partition_name not like '%MAX%'
            and to_date( trim( substr(partition_name, 2) ), 'YYYYMMDD' )   <= (add_months(sysdate,-24));
          
begin
    
    for r_nightly_deletes in c_nightly_deletes loop
      delete from SOME_contract_nightly 
        where snapshot_date = r_nightly_deletes.sdate;
        
      Log_This('PKG_SOME_UI.Maintain_Partitions','SOME_CONTRACT_NIGHTLY snapshot_date' ||
        to_char(r_nightly_deletes.sdate,'DD-MON-YY') || ' deleted: ' || sql%rowcount);
    
    end loop;
      
    for each_table in (select distinct table_name from user_tab_partitions where table_name like 'SOME%') loop 
      
      -- create new partitions
      SplitWeekdayPartitions( each_table.TABLE_NAME);
      
      -- compress (today's) data
      for r_compress_ddl in c_compress_ddl ( each_table.table_name ) loop
        execute immediate r_compress_ddl.ddl;
        Log_This('PKG_SOME_UI.Maintain_Partitions',each_table.table_name || ' partition compressed: ' || r_compress_ddl.partition_name);
      end loop;
      
      -- remove any partitions older than two years
      for r_truncate_ddl in c_truncate_ddl ( each_table.table_name  ) loop 
              execute immediate r_truncate_ddl.ddl;
          Log_This('PKG_SOME_UI.Maintain_Partitions', each_table.table_name || ' Partition Truncated: ' || r_truncate_ddl.partition_name);
      end loop; 
       
    end loop;
    
    
    Rebuild_Indices();
    
    -- GATHER STATISTICS FOR NEW PARTITIONS
    for x in (  select t.owner, t.table_name, p.partition_name 
            from all_tables t
            join all_tab_partitions p
              on t.table_name = p.table_name 
           where p.table_name like 'SOME%' 
             and (p.last_analyzed is null 
                  or
                  p.table_name = 'SOME_CONTRACT_NIGHTLY' 
                  and p.partition_name like 'P' || to_char(sysdate,'YYYYMM') ||'%' ) )
    loop
              dbms_stats.gather_table_stats(
                      degree=> dbms_stats.AUTO_DEGREE,
                      ownname => x.owner,
                      tabname => x.table_name,
                      partname => x.partition_name,
                      cascade => dbms_stats.AUTO_CASCADE );
              Log_This('PKG_SOME_UI.Maintain_Partitions','Statistics gathered for table.partition: ' || 
                                    x.table_name || '.' || x.partition_name);
    end loop;  
      
    exception when others then
      Rebuild_Indices(); -- do not leave in unuseable state
      raise; -- notify user to investigate and rerun
end Maintain_Partitions;

 

In this particular case, I was deleting old partitions, and using compression to keep the total database size down.   The SplitWeekdayPartitions created the new ones:

--------- PARTITIONING
PROCEDURE SplitWeekdayPartitions( p_table_name in varchar2 ) AS
  v_latest_date date;
  v_new_date date; 
  v_part_name varchar2(8);
  v_upper_bound varchar2(8);
BEGIN
 
     SELECT to_date(substr(MAX(partition_name) KEEP (DENSE_RANK FIRST ORDER BY partition_position desc ), 2),'YYYYMMDD')
       into v_latest_date 
       FROM (SELECT partition_name, partition_position 
               FROM user_tab_partitions
              WHERE table_name = upper(p_table_name) 
                AND partition_name <> 'PMAX' );
                 
        if v_latest_date < sysdate
        then
            v_new_date := v_latest_date + 1;
            while to_char(v_new_date, 'd') in (1,7) loop -- skip weekend dates
                v_new_date := v_new_date + 1;
            end loop;
 
            v_part_name := to_char(v_new_date,'YYYYMMDD');
            v_upper_bound := to_char(v_new_date + 1,'YYYYMMDD');
            execute immediate 
            'ALTER TABLE ' || p_table_name || ' SPLIT PARTITION PMAX AT (to_date(' || v_upper_bound ||
                ',''YYYYMMDD'')) INTO ' || '( PARTITION P' || v_part_name ||  
                ', PARTITION PMAX ) UPDATE GLOBAL INDEXES';
                
                
            Log_This('PKG_SOME_UI.SplitWeekdayPartitions', 'New partition P' || v_part_name || ' created for ' || p_table_name);
            
            SplitWeekdayPartitions( p_table_name );   -- do we need to catch up to today?
        end if;
END SplitWeekdayPartitions;

It’s interesting that I picked a recursive call here instead of a while loop.

… and in the MaintainPartitions proc, RebuildIndices was necessary afterwards because the alter table commands and truncating didn’t trigger index updates.

procedure Rebuild_Indices is 
begin 
    -- LOCAL INDICES   (not using any now but maybe in the future...)
    for x in ( SELECT UIP.INDEX_NAME, UIP.PARTITION_NAME, UIP.COMPRESSION
            FROM USER_IND_PARTITIONS UIP
            WHERE UIP.STATUS <> 'USABLE' )
    loop
            execute immediate
                          'ALTER INDEX ' || x.INDEX_NAME || ' REBUILD PARTITION ' || x.PARTITION_NAME ;
            Log_This('PKG_SOME_UI.Maintain_Partitions','Partition Index Rebuilt: ' || x.index_name);
    end loop;
        
    -- GLOBAL INDICES     
    for x in ( SELECT INDEX_NAME
                FROM USER_INDEXES
                WHERE table_name like 'SOME%' and STATUS not in ( 'VALID', 'N/A' ) )
    loop
             execute immediate
                        'ALTER INDEX ' || x.INDEX_NAME || ' REBUILD ' ;
             Log_This('PKG_SOME_UI.Maintain_Partitions','Global Index Rebuilt: ' || x.index_name);
    end loop;   
end;    
and that’s it.  Neat feature to plug into your next large table.

An example of one of my favorite projects

by MikeHogg 27. September 2011 15:00

Here’s a small one I like.

One time I inherited a system of sorts that supported a single user, with her third party data warehouse application. We didn’t support the warehouse, but we were supposed to get the data extracts that she imported into the warehouse at monthly intervals. The existing IT process was very manual, and very time intensive. As well as involving data from 4 different sources and the queries or processes to get them, it involved a dozen files per run, sometimes up to three people from different departments, with several runs per month, taking four to eight hours each run, and no history or state tracking except to keep the files in folders forever.

The initial attempt to automate this also left behind a number of files and processes to maintain, and it had been running for over a year with 60 monthly man hours of IT dedicated to it and now several hundred files, folders, and processes in assorted locations.

This is one of my favorite jobs. People put a mess in front of me and I turn it into something easy to use that saves time. One of the things that bugged me about the existing process was that there was no history and it took too long. I expanded our small database to include tables for each of our entities, and started automating the extracts in a nightly process. This had the effect of making the user’s request time drop from several hours for the complicated queries to almost instant since we were now caching the data ourselves, as well as provided an easy way for IT to hook into historic runs.

Another thing that I wanted to change was to streamline the steps. The existing process exported from data sources, inserted into databases, extracted into files, joined with other datasources, imported into databases again. So I built an SSIS package that did the data transformations on our Oracle database and inserted the data directly into the warehouse MSSQL server. This removed the need for the files and a whole staging process, and made the whole process easier to maintain from an IT perspective.

Another thing that I wanted to change was to remove the IT resource component. I don’t believe IT needs to be involved for day to day business operation requests, unless something breaks. So I built a simple WPF click-once intranet application with a handful of features, enabling the user to run the whole extract/import instantly for any date they choose, and even view the data by Excel export if they want. I like that it not only provided so much convenience for the user, but that it dropped the IT cost to maintain from an average of 60 monthly man hours to almost zero.

Mapping patterns in Oracle

by MikeHogg 28. June 2011 19:38

In my research, Mapping patterns came in three forms and they varied by levels of complexity.  The simplest form was a mapping table that most people recognize as a many to many join.  This is easy to use, and easy to understand, but doesn’t allow dynamic mapping.  You have to have both entities primary keys and then you have to do an insert to map the relationship.  This won’t work on a data store that is constantly receiving new data.

The second form allows dynamic mapping based on attributes.  You can set up a process that automatically inserts the rows or assigned the mappings according to each entity’s attribute value.  Then you could set up a process where every new entity whose X = Y could be automatically mapped to entity A.  This requires columns for each attribute though, and didn’t allow multiple entities.

The third form of mapping table, involves dynamic attributes, dynamic tables, and allows the most flexibility, but is the most difficult to implement and use, which you realize just as soon as you start writing your procs against it.

I happened to inherit a system where the original consultant set up the third form, and the following generations of developers built a variety of complicated tools and complex business processes to maintain it.  While it took me a while to understand it, once I did, and realized its strengths, I also realized a simple UI could abstract away the complexity and make it easy to use.

Behind the UI though had to be the data layer, and while the UI was simple looking, the data layer was where all the complexity had to be simplified.  Here is one of the queries I wrote to run a mapping function on the fly, for one particular entity, having at least three but no more than four different mapping attributes.  This is one of the few times I had to use dynamic SQL in Oracle, whose PL/SQL allowed for great flexibility but would not help me in this case:

 

  procedure MapUnmappedparts(p_certain_date my_part.certain_date%type) is 
  
    cursor c_mappings is      
     
    select  v.id, v.value, v.schema, v.exch_id,
            firstkey.attr_name firstname, regexp_substr(v.key, '[^()]+',1,2) firstvalue,
            secondkey.attr_name secondname, regexp_substr(v.key, '[^=()]+',1,4) secondvalue,
            thirdkey.attr_name thirdname, regexp_substr(v.key, '[^=()]+',1,6) thirdvalue,
            fourthkey.attr_name fourthname, regexp_substr(v.key, '[^=()]+',1,8) fourthvalue,
            fifthkey.attr_name fifthname, regexp_substr(v.key, '[^=()]+',1,10) fifthvalue,
            sixthkey.attr_name sixthname, regexp_substr(v.key, '[^=()]+',1,12) sixthvalue,
            seventhkey.attr_name seventhname, regexp_substr(v.key, '[^=()]+',1,14) seventhvalue
    from my_values v 
    join my_attributes firstkey on firstkey.short_name = regexp_substr(v.key, '[^=]+',1,1)
    join my_attributes secondkey on secondkey.short_name = regexp_substr(v.key, '[^=()]+',1,3)
    join my_attributes thirdkey on thirdkey.short_name = regexp_substr(v.key, '[^=()]+',1,5)
    left outer join my_attributes fourthkey on fourthkey.short_name = regexp_substr(v.key, '[^=()]+',1,7)
    left outer join my_attributes fifthkey on fifthkey.short_name = regexp_substr(v.key, '[^=()]+',1,9) 
    left outer join my_attributes sixthkey on sixthkey.short_name = regexp_substr(v.key, '[^=()]+',1,11) 
    left outer join my_attributes seventhkey on seventhkey.short_name = regexp_substr(v.key, '[^=()]+',1,13) 
    join my_schemas s on s.sch_id = v.schema and s.key_order = 1 
    order by s.eval_order, s.sch_id;
       
    v_query varchar2(2000);   
  begin
    --pkg_loadparts.map_pf_codes; 
  
    Pkg_somy__Log.writeMessage(p_module   => 'Pkg_ProductMapper.MapUnmappedparts',
                              p_severity => 'DEBUG',
                              p_message  =>  'Started'); 
                              
    for r_mapping in c_mappings loop
      v_query := 'merge into my_part_Detail d
                  using (select dd.id, h.co, h.d_type, dd.curve, dd.delivery_point, 
                                dd.curve_2, dd.commodity, h.part_start_month, h.part_end_month, 
                                dd.term_of_trading_cd, h.price_src_1, h.price_src_2, h.term,
                                dd.vintage, dd.adj_dm_year, h.product_code, dd.region, dd.technology
                          from my_part_detail dd join my_part_header h on dd.eti = h.eti 
                         where dd.certain_date = :1 
                           and dd.pf_code is null 
                           and nvl(dd.somy__status,''nvl'') <> ''ORI''
                           ) unmapped on (unmapped.id = d.id)
                  when matched then update set d.pf_code = :2
                    where 1=1 ';
      
      v_query := v_query || ' and unmapped.' || r_mapping.firstname || ' = :3 ';
      v_query := v_query || ' and unmapped.' || r_mapping.secondname || ' = :4 ';
      v_query := v_query || ' and unmapped.' || r_mapping.thirdname || ' = :5 ';
      if r_mapping.fourthname is not null 
        then      v_query := v_query || ' and unmapped.' || r_mapping.fourthname || ' = :6 ';  end if;
      if r_mapping.fifthname is not null 
        then      v_query := v_query || ' and unmapped.' || r_mapping.fifthname || ' = :7 ';  end if;
      if r_mapping.sixthname is not null 
        then      v_query := v_query || ' and unmapped.' || r_mapping.sixthname || ' = :8 ';  end if;                   
      if r_mapping.seventhname is not null 
        then      v_query := v_query || ' and unmapped.' || r_mapping.seventhname || ' = :9 ';  end if;      
         
        
      case 
        when r_mapping.seventhname is not null then 
          execute immediate v_query using p_certain_date, r_mapping.value, r_mapping.firstvalue,
                                          r_mapping.secondvalue, r_mapping.thirdvalue, r_mapping.fourthvalue,
                                          r_mapping.fifthvalue, r_mapping.sixthvalue, r_mapping.seventhvalue ;
        when r_mapping.sixthname is not null then 
          execute immediate v_query using p_certain_date, r_mapping.value, r_mapping.firstvalue,
                                          r_mapping.secondvalue, r_mapping.thirdvalue, r_mapping.fourthvalue,
                                          r_mapping.fifthvalue, r_mapping.sixthvalue ;
        when r_mapping.fifthname is not null then 
          execute immediate v_query using p_certain_date, r_mapping.value, r_mapping.firstvalue,
                                          r_mapping.secondvalue, r_mapping.thirdvalue, r_mapping.fourthvalue,
                                          r_mapping.fifthvalue ;                                  
        when r_mapping.fourthname is not null then 
          execute immediate v_query using p_certain_date, r_mapping.value, r_mapping.firstvalue,
                                          r_mapping.secondvalue, r_mapping.thirdvalue, r_mapping.fourthvalue ;
        else
          execute immediate v_query using p_certain_date, r_mapping.value, r_mapping.firstvalue,
                                          r_mapping.secondvalue, r_mapping.thirdvalue ;
      end case;
       
                              
    end loop;

Tags:

Oracle

Repository Patterns without Entity Framework

by MikeHogg 2. March 2011 18:03

At the time EF came out, I was usually interfacing with million row Oracle databases, and EF didn’t connect to Oracle.  It wasn’t until I started working in a different industry later on that I got to enjoy EF.  So for some time my Repositories wrapped a Stored Procedure Data Layer. 

Here’s an example of my Repository usage in WPF using MVVM, LINQ, DI.  This was a financials reporting application and one of the interesting features that I was tasked with in this case was running calculations on the fly based on data from its different sources. 

First a couple simple models: The Group Model and a Details Model, both exposing some basic datasource properties as well as some derived properties and the methods to calculate them, and the Group with a List<> of Details:

 

    public class MatchGroup : IEquatable<MatchGroup>  // IEquatable for LINQ.Distinct()
    {
        public decimal Match_Group_Id { get; set; }
        public string Product { get; set; }
        public DateTime Contract_Month { get; set; }
        public decimal? Total_CER_Lots { get; set; }
        public decimal? Total_GS_Lots { get; set; }
        public decimal? Lot_Size { get; set; }
        public decimal? CER_Price { get; set; }
        public decimal? GS_Price { get; set; }
        
        public decimal? CER_Float { get; set; }
        public decimal? CER_Final_Float { get; set; }
        public decimal? GS_Float { get; set; }  
        ... etc etc
        public List<MatchDetail> Details { get; set; }
        
        public string Single_ETI { get; set; }
        public string Single_Goldman_Id { get; set; }
         
        #region Constructor
        public MatchGroup()
        {
            Details = new List<MatchDetail>();
        }
        #endregion
 
        public void Calculate()
        {
            SetSingleETI();
            CheckDetails();
            CheckGroup();
            SumGroupChecks();
        }
        private void SetSingleETI()
        {
            if (Details.Count() > 1)
            {
                Single_ETI = "+";
            }
            else
            {
                Single_ETI = Details.Select(f => f.ETI).FirstOrDefault();
                Single_Goldman_Id = Details.Select(f => f.Goldman_Id).FirstOrDefault();
            }
        }
        private void CheckDetails()
        {
            foreach (MatchDetail d in Details) d.CheckDetails();
        }
        private void CheckGroup()
        {
            Float_Diff = GS_Price - CER_Float;
            Price_Diff = GS_Price - CER_Price;
            Qty_Diff = Total_GS_Lots - Total_CER_Lots;
        }
        private void SumGroupChecks()
        {
            // most of these can be summed from linq grouping in initial creation? 
            CER_Settled_Total = Details.Sum(d => d.CER_Settled_Total);
            CER_Check = Details.Sum(d => d.CER_Check);
            CER_Diff = Details.Sum(d => d.CER_Diff);
            
        }
        #region IEquatable
        public bool Equals(MatchGroup other)
        {
            return true ? Match_Group_Id == other.Match_Group_Id : false;
        }
        public override int GetHashCode()
        {
            return Match_Group_Id == 0 ? 0 : Match_Group_Id.GetHashCode();
        }
        #endregion      
    }

 

The Details Model was similar.

The ViewModel has some UI specific properties, but mostly just exposes the Model properties directly instantiated through constructor injection, as this is a read-only reporting View.

public class MatchGroupVM : ViewModelBase
    { 
        Models.MatchGroup _matchgroup; 
        public decimal Match_Group_Id { get { return _matchgroup.Match_Group_Id; } }
        public string Product { get { return _matchgroup.Product; } }
        public DateTime Contract_Month { get { return _matchgroup.Contract_Month; } }
        public decimal? Total_CER_Lots { get{ return _matchgroup.Total_CER_Lots; } }
        public decimal? Total_GS_Lots { get{ return _matchgroup.Total_GS_Lots; } }
        <snip>
        public MatchGroupVM(Models.MatchGroup mg){
            _matchgroup = mg;
        }
}

I use a list of these GroupVMs on the main page or tab’s VM, with Repository injection.

public class FloatPricesVM : ViewModelBase
    {
        #region Fields  
        #region Properties
        public string SelectedProductName { get; set; }
        public int SelectedDeliveryMonth { get; set; }
        public int SelectedDeliveryYear { get; set; }
        public List<string> ProductNames { get; set; }
        public List<int> DeliveryMonths { get; set; }
        public List<int> DeliveryYears { get; set; }
        public List<MatchGroupVM> MatchGroups
        {
            get
            {
                return _matchgroups;
            }
            set
            {
                SetProperty(ref _matchgroups, value, "MatchGroups");
            }
        }
        #endregion
        public FloatPricesVM(Models.MatchGroupRepository repo)
        {
            _repository = repo;
            _error = new ErrorVM();
            _dialog = new DialogVMBase();
            ProductNames = _repository.GetProductNames();
            DeliveryMonths = _repository.GetDeliveryMonths();
            DeliveryYears = _repository.GetDeliveryYears();
            SelectedDeliveryMonth = DateTime.Now.Month - 1;
            SelectedDeliveryYear = DateTime.Now.Year;
        }
        #region Commands
        private List<MatchGroupVM> GetMatchGroups(string productname, DateTime deliverymonth)
        {
            List<MatchGroupVM> result = new List<MatchGroupVM>();
            try
            {
                foreach (Models.MatchGroup mg in _repository.GetMatchGroups(productname, deliverymonth))
                {
                    result.Add(new MatchGroupVM(mg));
                } 
            }
            catch (Exception e)
            {
                Error = new ErrorVM(e);
            }
            return result;
        }

The Repository wraps our data access layer, which is Oracle Stored Procs in this case.  It gets injected with a Pricing engine provider.

    public class MatchGroupRepository
    {
        IFloatPriceProvider _fpp;
 
        public MatchGroupRepository(IFloatPriceProvider fpp)
        {
            _fpp = fpp;
        }
        public List<MatchGroup> GetMatchGroups(string productname, DateTime deliverymonth)
        {
            List<MatchGroup> result = new List<MatchGroup>();
            DataTable dt = new DataTable();
            try
            {
                dt = LIB.MyOraDBDAC.GetMatchDetails(productname, deliverymonth);
                var groups = from row in dt.AsEnumerable()
                             group row by new
                             {
                                 Id = (decimal)row.Field<OracleDecimal>("MATCH_GROUP_ID"),
                                 P = (string)row.Field<OracleString>("PRODUCT"),
                                 CM = (DateTime)row.Field<OracleDate>("CONTRACT_MONTH")
                             } into mg
                             orderby mg.Key.CM
                             select new Models.MatchGroup
                             {
                                 Match_Group_Id = mg.Key.Id,
                                 Product = mg.Key.P,
                                 Contract_Month = mg.Key.CM,
                                 Total_CER_Lots = RoundNullableDecimal(mg.Sum(f => OracleDecimalToDotNet(f.Field<OracleDecimal>("CER_LOTS"))), 0),
                                 Total_GS_Lots = RoundNullableDecimal(mg.Sum(f => OracleDecimalToDotNet(f.Field<OracleDecimal>("GS_LOTS"))), 0),
                                 Lot_Size = mg.Max(f => OracleDecimalToDotNet(f.Field<OracleDecimal>("LOT_SIZE"))),
                                 CER_Price = mg.Max(f => OracleDecimalToDotNet(f.Field<OracleDecimal>("CER_PRICE"))),
                                 GS_Price = mg.Max(f => OracleDecimalToDotNet(f.Field<OracleDecimal>("GS_PRICE"))),
                                 //CER_Float = _fpp.GetFloat(productname, mg.Key.CM),
                                 CER_Float = mg.Max(f => OracleDecimalToDotNet(f.Field<OracleDecimal>("CER_FLOAT"))),
                                 //CER_Final_Float = _fpp.GetFloat(productname, mg.Key.CM),
                                
                             };
                foreach (Models.MatchGroup mg in groups)
                {
                    mg.Details = GetDetails(mg, dt);
                    mg.Calculate();
                    result.Add(mg);
                }
            }
            catch (InvalidCastException ex)
            {
                foreach (DataColumn c in dt.Columns)
                {
                    ex.Data.Add(c.ColumnName, c.DataType.Name);
                }
                throw;
            }
            return result;
        }
        private List<Match.Models.MatchDetail> GetDetails(Models.MatchGroup mg, DataTable dt)
        {
            var dets = from row in dt.AsEnumerable()
                       where (decimal)row.Field<OracleDecimal>("MATCH_GROUP_ID") == mg.Match_Group_Id
                       select new Models.MatchDetail
                       {
                           MG = mg,
                           GS_Lots = RoundNullableDecimal(OracleDecimalToDotNet(row.Field<OracleDecimal>("GS_LOTS")), 2),
                           Goldman_Id = OracleStringToDotNet(row.Field<OracleString>("GOLDMAN_ID")),
                           ETI = OracleStringToDotNet(row.Field<OracleString>("ETI")),
                           CER_Lots = RoundNullableDecimal(OracleDecimalToDotNet(row.Field<OracleDecimal>("CER_LOTS")), 2),
                           CER_Settled_Value_TC = OracleDecimalToDotNet(row.Field<OracleDecimal>("CER_SETTLED_VALUE_TC")),
                          
                       };
            return dets.ToList();
        }
        private decimal? RoundNullableDecimal(decimal? d, int decimalplaces)
        {
            return d.HasValue ? (decimal?)Decimal.Round(d.Value, decimalplaces) : null;
        }
        private decimal? OracleDecimalToDotNet(OracleDecimal od)
        {
            OracleDecimal d = OracleDecimal.SetPrecision(od, 28);
            return (d.IsNull) ? null : (decimal?)d;
        }
        private string OracleStringToDotNet(OracleString os)
        {
            return (os.IsNull) ? null : (string)os;
        }
    }

 

There’s some neat stuff in there, catching InvalidCastException and adding info before re-throwing the error, Linq grouping, and here you see the call to the model’s Calculate function we showed in the beginning.

and that’s it.

Tags:

WPF | Linq

Hiding and Expanding Row Details in WPF

by MikeHogg 18. November 2010 19:03

We had a normal datagrid, with lots of data.  Each of the rows was a parent to some list of other objects, and the users wanted to be able to expand a row to see the children detail rows. Nested Datagrids, a popular or usual feature request. The Toolkit DataGrid has a RowDetails object, but it is visible all the time.  Here’s how I set it up to have only one exclusive RowDetails visible at a time in WPF:

ViewModel has a property called IsSelected, and in addition to calling OnPropertyChanged event for itself, it calls OnPropertyChanged for another property, DetailsVisibility.  The Model Binder will do a Get on DetailsVisibility, and so we just need to put the logic there…

    public class MatchGroupVM : ViewModelBase
    { 
        Models.MatchGroup _matchgroup;
        bool _etiselected;
        public decimal Match_Group_Id { get { return _matchgroup.Match_Group_Id; } }
        public string Product { get { return _matchgroup.Product; } }
        public DateTime Contract_Month { get { return _matchgroup.Contract_Month; } }
        public decimal? Lot_Size { get{ return _matchgroup.Lot_Size; } }
        <snip>
        public decimal? Price_Diff { get { return _matchgroup.Price_Diff; } }
        public decimal? Float_Diff { get { return _matchgroup.Float_Diff; } }
        public List<Models.MatchDetail> Details { get{ return _matchgroup.Details; } }
        public string Single_ETI { get{ return _matchgroup.Single_ETI; } }
        public string Single_Goldman_Id { get{ return _matchgroup.Single_Goldman_Id; } }
        public bool ETISelected
        {
            get
            {
                return _etiselected;
            }
            set
            {
                if (_etiselected != value)
                {
                    _etiselected = value;
                    OnPropertyChanged("ETISelected");
                    OnPropertyChanged("DetailsVisibility");
                }
            }
        }
        
        
        public System.Windows.Visibility DetailsVisibility
        {
            get
            {
                return Details.Count > 1 == true && ETISelected == true ? System.Windows.Visibility.Visible : System.Windows.Visibility.Collapsed;
            }
            set { var bindingdummysetter = value; }
        } 
        public MatchGroupVM(Models.MatchGroup mg){
            _matchgroup = mg;
        }
    }

 

Note in this case we are not writing our own Getters and Setters in our VM, but simply exposing the composition model.  That is because this VM is a readonly report.  Similarly, the Model Binder doesn’t ever need to Set DetailsVisibility, but by calling OnPropertyChanged the Getter is called, so this one line handles both Showing a Details grid and Hiding a Details Grid.  A parent VM handles the populating of this VM and its Details.

The View will have the nested grid in the RowDetailsTemplate, just like any other, but it binds its DetailsVisibility to the Model.  Note that the nested DetailsVisibility needs to be set to Collapsed to fix the cascading style of WPF.  And we add some eye candy to a row’s colors when it becomes selected. 

<tk:DataGrid Name="GridFloatPrices" ItemsSource="{Binding MatchGroups}"  Grid.Row="1"
                         RowHeaderWidth="1" IsReadOnly="True" AutoGenerateColumns="False"
                         AlternatingRowBackground="Honeydew" CanUserAddRows="False"   
                         SelectionUnit="Cell" ScrollViewer.CanContentScroll="False"
                          >
                
                <tk:DataGrid.Resources>
                    <Style  TargetType="{x:Type tk:DataGridCell}" >
                        <Style.Triggers>
                            <Trigger Property="IsSelected" Value="True">
                                <Setter Property="Background" Value="LightSkyBlue"/>
                                <Setter Property="Foreground" Value="Black"/>
                                <Setter Property="BorderBrush" Value="LightSkyBlue"/> 
                            </Trigger>
                        </Style.Triggers>
                    </Style> 
                    <Style TargetType="tk:DataGridRow"> 
                        <Setter Property="DetailsVisibility" Value="{Binding DetailsVisibility}" />                          
                    </Style>
                    <Style TargetType="{x:Type tkp:DataGridColumnHeader}">
                        <Setter Property="ContentTemplate">
                            <Setter.Value>
                                <DataTemplate>
                                    <TextBlock TextWrapping="Wrap" Text="{Binding}"></TextBlock>
                                </DataTemplate>
                            </Setter.Value>
                        </Setter>
                    </Style>
                </tk:DataGrid.Resources>    
                 
                <tk:DataGrid.RowDetailsTemplate>
                    <DataTemplate>
                        <Grid Background="LightGray" HorizontalAlignment="Stretch" ScrollViewer.CanContentScroll="False"> 
                        <tk:DataGrid ItemsSource="{Binding Details}" RowBackground="LightYellow"
                                     Margin="749,0,0,0" IsReadOnly="True" CanUserAddRows="False"
                                     HeadersVisibility="None" AutoGenerateColumns="False" ScrollViewer.ScrollChanged="OnMouseWheel"
                                     Width="1450" ScrollViewer.HorizontalScrollBarVisibility="Hidden"
                                     HorizontalAlignment="Left">
                                <tk:DataGrid.Resources>
                                    <Style TargetType="tk:DataGridRow">
                                        <Setter Property="DetailsVisibility" Value="Collapsed"/><!-- to turn off cascading style from master grid -->
                                    </Style> 
                                </tk:DataGrid.Resources>
                                
                                <tk:DataGrid.Columns>
                               <snip>
                                        <tk:DataGridTextColumn Binding="{Binding CER_Check}" Width="100"></tk:DataGridTextColumn>
                                        <tk:DataGridTextColumn Binding="{Binding CER_Diff}" Width="100"></tk:DataGridTextColumn>
                                        
                                        <tk:DataGridTextColumn Binding="{Binding GS_Settled_Value}" Width="100"></tk:DataGridTextColumn>
                                        <tk:DataGridTextColumn Binding="{Binding MV_Diff_Value}" Width="100"></tk:DataGridTextColumn>
                                        <tk:DataGridTextColumn Binding="{Binding GS_Settled_Total}" Width="100"></tk:DataGridTextColumn>
                                        <tk:DataGridTextColumn Binding="{Binding GS_Check}" Width="100"></tk:DataGridTextColumn>
                                        <tk:DataGridTextColumn Binding="{Binding GS_Diff}" Width="100"></tk:DataGridTextColumn>
                                        
                                        <tk:DataGridTextColumn Binding="{Binding Settled_Diff}" Width="100"></tk:DataGridTextColumn>
                                </tk:DataGrid.Columns>
                            </tk:DataGrid>  
                            </Grid>
                    </DataTemplate>
                </tk:DataGrid.RowDetailsTemplate>
 
                <tk:DataGrid.Columns>
                    <tk:DataGridTextColumn Binding="{Binding Product}" Header="Product" Width="100"></tk:DataGridTextColumn>
                    <tk:DataGridTextColumn Binding="{Binding Contract_Month, StringFormat=MMM-yyyy}" Header="Contract Month" Width="100"></tk:DataGridTextColumn>
                    <tk:DataGridTextColumn Binding="{Binding Match_Group_Id}" Header="Match Group Id" Width="100"></tk:DataGridTextColumn>
                    <tk:DataGridTextColumn Binding="{Binding CER_Price}" Header="CER Price" Width="75"></tk:DataGridTextColumn>
                    <tk:DataGridTextColumn Binding="{Binding Lot_Size}" Header="Lot Size" Width="75"></tk:DataGridTextColumn>
                    
                    <tk:DataGridTextColumn Binding="{Binding Single_ETI}" Header="ETI" Width="100">
                        <tk:DataGridTextColumn.CellStyle>
                            <Style TargetType="tk:DataGridCell">
                                <Setter Property="IsSelected" Value="{Binding ETISelected, Mode=OneWayToSource}"/>
                                <Style.Triggers>
                                    <Trigger Property="IsSelected" Value="True">
                                        <Setter Property="Background" Value="LightSkyBlue"/>
                                        <Setter Property="Foreground" Value="Black"/>
                                        <Setter Property="BorderBrush" Value="LightSkyBlue"/>
                                    </Trigger>
                                </Style.Triggers>
                            </Style>
                        </tk:DataGridTextColumn.CellStyle>
                    </tk:DataGridTextColumn>
                    <tk:DataGridTextColumn Binding="{Binding Single_Goldman_Id}" Header="Goldman Id" Width="100"></tk:DataGridTextColumn>                   
                    <tk:DataGridTextColumn Binding="{Binding Total_CER_Lots}" Header="Tot CER Lots" Width="75"></tk:DataGridTextColumn>
                    <tk:DataGridTextColumn Binding="{Binding Total_GS_Lots}" Header="Tot GS Lots" Width="75"></tk:DataGridTextColumn>
                <snip>               
                    <tk:DataGridTextColumn Binding="{Binding Settled_Diff}" Header="Settled Diff" Width="100"></tk:DataGridTextColumn>
                    <tk:DataGridTextColumn Binding="{Binding Qty_Diff}" Header="Qty Diff" Width="100"></tk:DataGridTextColumn>
                    <tk:DataGridTextColumn Binding="{Binding Price_Diff}" Header="Price Diff" Width="100"></tk:DataGridTextColumn>
                    <tk:DataGridTextColumn Binding="{Binding Float_Diff}" Header="Float Diff" Width="100"></tk:DataGridTextColumn>
                </tk:DataGrid.Columns>
            </tk:DataGrid> 
and that’s it.

Tags:

WPF

About Mike Hogg

Mike Hogg is a c# developer in Brooklyn.

More Here

Favorite Books

This book had the most influence on my coding style. It drastically changed the way I write code and turned me on to test driven development even if I don't always use it. It made me write clearer, functional-style code using more principles such as DRY, encapsulation, single responsibility, and more. amazon.com

This book opened my eyes to a methodical and systematic approach to upgrading legacy codebases step by step. Incrementally transforming code blocks into testable code before making improvements. amazon.com

More Here