none
Converting CF module to .Net - comparing tuple values between two tables RRS feed

  • Question


  • I have code in ColdFusion that I need to convert to .Net, but so far efforts have failed. I'm posting the CF code not for others to reproduce but to give me insights on how to do this - or if there is already a built tool that will do this.

    <!---
    This module compares AY Webservices feed to Connect FTP feed to ensure data is consistent from both sources. This page relies on the action variable to either email results or display to the end user.
    
    Sections
    General
        - sets variables, pulls column list and data, then loops column list over query comparing each column in both datasets, if errors are found they are added to data4file array
    Email
        - Wraps up data4file array into file, emails to whoever is on list
    Web
        - presents error data on the web page
    --->
    
    
    <!--- General Section --->
    <cfparam name="action" type="string" default="">
    <cfset session.msg="">
    <cfset session.errorcount=0>
    <cfset session.data4file="">
    <cfset session.filepath="D:\tools\webfiles\">
    <cfset session.filename="Xxx_Compare_#dateformat(now(),'yymmddhhmm')#.csv">
    
    <cfquery name="columns" datasource="jgsmdatasecure">
    SELECT [name] AS [ColumnName], colid
    FROM syscolumns
    WHERE id = (SELECT id FROM sysobjects WHERE [NAME] = 'ADM_WSApplication')
    AND [name] not in ('application_id','Xxx_AYID','Consortium','Xxx_RunDate','Xxx_RunTime','emplidDateChanged','netidDateChanged','DOB','curstat_chng_date','previous_app','GRE_date','TOEFL_Date','Date_Created','date_changed','Date_Last_Used','submit_app','app_date_rec','deposit_fee_received_date','deposit_fee_received_date_order','minority','multiethnic','URM','Ethnicity_Count','Ethnicity_HIS','Ethnicity_NAT','Ethnicity_ASI','Ethnicity_BLA','Ethnicity_NPI','Ethnicity_CAU','JGSMUpdatedDate','TestRecord','BestIndustryId','BestJobFunctionId','JPalsStudentCount','JPalsAlumniCount','user_login','password','fax','international_raw','CreateDate','GRE_Verbal_scale','Reapplicant_essay','preferred_name','apply_source_descript')
    Order By colid
    </cfquery>
    
    
    <!---Loop over column list, query to find columns that do not match. If found, add to filevariable--->
    <cfloop query="columns">
      <cfoutput>
      <cfquery name="sampleerrordata" datasource="jgsmdatasecure">
      Select AA.Xxx_AYId, CASE WHEN WS.#ColumnName# <> AA.#ColumnName# THEN AA.#ColumnName# +','+ WS.#ColumnName# END AS Error
      FROM ADM_WSApplication WS
      INNER JOIN ADM_Application AA ON WS.Xxx_AYId=AA.Xxx_AYId
      WHERE WS.Acad_Year='2012' AND AA.program='MBA' And AA.first_name not like 'test%'
      AND WS.#ColumnName# <> AA.#ColumnName#
      </cfquery>
      </cfoutput>
          <cfif sampleerrordata.recordcount gt 0>
              <cfloop query="sampleerrordata">
                  <cfoutput><cfset session.data4file="#session.data4file##TRIM(Xxx_AYID)#,#columns.ColumnName#,#Error#
                  "></cfoutput>
                  <cfset session.errorcount=#session.errorcount#+1>
              </cfloop>
          </cfif>
    </cfloop>
    
     <cfif session.errorcount gt 1>
    <!---Put into a File and email --->
      <cfif action is "email">
        <!---Process the filevariable: strip extra return, and strip bad characters--->
        <cfset len_data4file="#len(session.data4file)#">
        <cfset session.data4file="#Left(session.data4file, evaluate(len_data4file - 1))#">
        <!--- Write the sheet to file   --->
        <cftry>
            <cffile action="write" file="#session.filepath##session.filename#" output="#session.data4file#" addnewline="no">
              <cfcatch>
                  <cfset session.error=1>
                  <cfset session.msg="#session.msg# csv File Created/populated Failed. Contact the admin<BR>">
              </cfcatch>
          </cftry>
        <!--- Send the email if the SQL Insert and File Creation was successful --->
            <CFMAIL TO="clb39@cornell.edu"
                FROM="gm-admin-apps@cornell.edu"
                SUBJECT="Johnson Student Upload #DateFormat(Now(),"mm/dd/yyyy")#"
                MIMEATTACH="#session.filepath##session.filename#">
            
            The attached file contains #session.errorcount# errors for review.
            </CFMAIL>
      <cfelseif action eq "web">
            <!---output to screen--->
            Xxx_AYID,ColumnName,AYData,ConnectData<br />
            <cfloop query="columns">
                <cfquery name="sampleerrordata" datasource="jgsmdatasecure">
                Select AA.Xxx_AYId, CASE WHEN WS.#ColumnName# <> AA.#ColumnName# THEN AA.#ColumnName#+','+WS.#ColumnName# END AS Error
                FROM ADM_WSApplication WS
                INNER JOIN ADM_Application AA ON WS.Xxx_AYId=AA.Xxx_AYId
                WHERE WS.Acad_Year=(Select Adm_Sys_Value FROM adm_sys_settings where Adm_Sys_Name='Acad_Year') AND AA.program='MBA' And AA.first_name not like 'test%'
                AND WS.#ColumnName# <> AA.#ColumnName#
                </cfquery>
                    <cfif sampleerrordata.recordcount gt 0>
                        <cfloop query="sampleerrordata">
                            <cfoutput>#Xxx_AYID#,#columns.ColumnName#,#Error#<br /></cfoutput>
                        </cfloop>
                    </cfif>
            </cfloop>
      <cfelse>
          None of the criteria were met
      </cfif>
    <cfelse>
    
    </cfif>
    


    Thursday, May 30, 2013 7:38 PM

All replies

  • Hi clb247,

    What have you tried to convert this code to .NET?

    If you got any problems with .NET code, please post the code you have tried here.

    We are not able to help you without the .NET code.

    Sorry for any inconvenience.

    Best regards,


    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, June 3, 2013 5:23 AM
    Moderator