Getting ugly SQL Query to work in C#

Question

I am attempting to get a series of values from a few tables in a database.

I currently have this code working:

SqlDataReader reader = null;
SqlCommand myCommand = new SqlCommand("select * from ods_LN_Master", xavierConnection);

reader = myCommand.ExecuteReader();
while (reader.Read())
{
   Console.WriteLine(reader["LMBKNO"].ToString());
}

However the SQL query that I really want to do is looks like this:

select "T1"."C0" AS "Collateral Type", "T1"."C1" AS "Owner Occuppied", "T1"."C2" AS "County Name", "T1"."C3" AS "Region Description", "T1"."C4" AS "City", "T1"."C5" AS "Orig Appraisal Date", "T1"."C6" AS "Curr Appraisal Date", "T1"."C7" AS "Mortgage 1 Bank", "T1"."C8" AS "Mortgage 2 Bank", "T1"."C9" AS "Bank Owned Mortgage 1", "T1"."C10" AS "Bank Owned Mortgage 2", "T1"."C11" AS "Call Report Code", "T1"."C12" AS "Original Note Date", "T1"."C13" AS "Account Key", "T1"."C14" AS "Account Number", "T1"."C15" AS "Credit Risk Code", "T1"."C16" AS "Current FICO Score", "T1"."C17" AS "Original FICO Score", "T0"."C18" AS "Bank Owned Principal Assets", "T0"."C19" AS "Orig Appraisal Amount", "T0"."C20" AS "Curr Appraisal Amount", "T0"."C21" AS "Mortgage Amount 1", "T0"."C22" AS "Mortgage Amount 2", "T1"."C18" AS "Available Balance", "T1"."C19" AS "Past Due Counter 1", "T1"."C20" AS "Past Due Counter 2", "T1"."C21" AS "Past Due Counter 3", "T1"."C22" AS "Past Due Counter 4", "T1"."C23" AS "Past Due Counter 5", "T1"."C24" AS "Past Due Counter 6"
 from (
select "CT_Master"."Collateral Type" AS "C0", "CTPROP"."Owner Occuppied" AS "C1", "CTPROP"."County Name" AS "C2", "Org"."Region Description" AS "C3", "CTPROP"."City" AS "C4", "CTPROP"."Orig Appraisal Date" AS "C5", "CTPROP"."Curr Appraisal Date" AS "C6", "CTPROP"."Mortgage 1 Bank" AS "C7", "CTPROP"."Mortgage 2 Bank" AS "C8", "CTPROP"."Bank Owned Mortgage 1" AS "C9", "CTPROP"."Bank Owned Mortgage 2" AS "C10", "LN_Master"."Call Report Code" AS "C11", case  when isdate("LN_Master"."Original Note Date") = 1 then convert(datetime,convert(varchar,"LN_Master"."Original Note Date")) else NULL end  AS "C12", "LN_Master"."Account Key" AS "C13", "LN_Master"."Account Number" AS "C14", "LN_Master"."Credit Risk Code" AS "C15", "SIFINA"."10 Char Spare Code 1" AS "C16", "SIFINA"."10 Char Spare Code 2" AS "C17", sum("LNIMPR"."Book_Balance" - "Participation_Lookups"."Participation Principal Assets") AS "C18", sum("CTPROP"."Orig Appraisal Amount") AS "C19", sum("CTPROP"."Curr Appraisal Amount") AS "C20", sum("CTPROP"."Mortgage Amount 1") AS "C21", sum("CTPROP"."Mortgage Amount 2") AS "C22"
 from (((((((
select "ods_LN_Master"."Account Count",  "ods_LN_Master"."Current Loan Status Code", "ods_LN_Master"."Account Key", "ods_LN_Master"."Org SKey", "ods_LN_Master"."Impaired Asset Status", "ods_LN_Master"."Credit Risk Code", "ods_LN_Master"."Call Report Code",  "ods_LN_Master"."Original Note Date", "ods_LN_Master"."Bank Number", "ods_LN_Master"."Account Number", "ods_LN_Master"."Participation Number", "ods_LN_Master"."Customer Number", "ods_LN_Master"."Dealer Number", "ods_LN_Master"."CIF Key", "ods_LN_Master"."Short Name", "ods_LN_Master"."Converted Account Key", "ods_LN_Master"."Branch Number", "ods_LN_Master"."Proc Application Code",  "ods_LN_Master"."Product Code",  "ods_LN_Master"."Account Application Code"
 from "BANK38"."dbo"."v_ods_LN_Master" "ods_LN_Master"
 where ods_LN_Master."Participation Number" = 0) "LN_Master" INNER JOIN (
select "Account Key", "Participation Principal Assets"
 from "BANK38"."dbo"."v_ods_Fact_AllProds_2008"
 where "Application Code" = 'LN') "Participation_Lookups" on "LN_Master"."Account Key" = "Participation_Lookups"."Account Key") LEFT OUTER JOIN (
select "ods_LNIMPR"."Account Key" AS "Account_Key", "ods_LNIMPR"."Book Balance" AS "Book_Balance"
 from "BANK38"."dbo"."v_ods_LNIMPR" "ods_LNIMPR"
 where ods_LNIMPR."Participation Number" = 0) "LNIMPR" on "LN_Master"."Account Key" = "LNIMPR"."Account_Key") LEFT OUTER JOIN "BANK38"."dbo"."fi_Core_Org" "Org" on "LN_Master"."Org SKey" = "Org"."Org Key") LEFT OUTER JOIN (
select *
 from "BANK38"."dbo"."v_ods_SICOD1" "ods_CODE_Master"
 where "ods_CODE_Master"."Application" = 'LN' and "ods_CODE_Master"."Code Type" = 'STAT') "Status_Code" on "LN_Master"."Bank Number" = "Status_Code"."Bankid" and "LN_Master"."Current Loan Status Code" = "Status_Code"."User Code") LEFT OUTER JOIN (
select "V_ODS_SIFINA"."10 Char Spare Code 1", "V_ODS_SIFINA"."10 Char Spare Code 2", "V_ODS_SIFINA"."Account Key"
 from "BANK38"."dbo"."V_ODS_SIFINA" "V_ODS_SIFINA"
 where "V_ODS_SIFINA"."APPLICATION" = 'LN') "SIFINA" on "LN_Master"."Account Key" = "SIFINA"."Account Key") LEFT OUTER JOIN ("BANK38"."dbo"."v_ods_CT_Master" "CT_Master" INNER JOIN (
select "v_ods_CTACTREL"."Account Number", "v_ods_CTACTREL"."Collateral Key"
 from "BANK38"."dbo"."v_ods_CTACTREL" "v_ods_CTACTREL"
 where v_ods_CTACTREL."Application" = 'LN') "CTACTREL" on "CT_Master"."Collateral Key" = "CTACTREL"."Collateral Key") on "LN_Master"."Account Number" = "CTACTREL"."Account Number") LEFT OUTER JOIN "BANK38"."dbo"."v_ods_CTPROP" "CTPROP" on "CT_Master"."Collateral Key" = "CTPROP"."Collateral Key"
 where case  when ("LN_Master"."Impaired Asset Status" = 'C') then 'H' else "Status_Code"."Code Key 2" end  <> 'C' and "LN_Master"."Proc Application Code" in ('RE', 'IL', 'CL') and case  when ("LN_Master"."Impaired Asset Status" = 'C') then 'H' else "Status_Code"."Code Key 2" end  <> 'H' and "CT_Master"."Collateral Type" in ('430', '431') and "CTPROP"."Owner Occuppied" in ('O')
 group by "CT_Master"."Collateral Type", "CTPROP"."Owner Occuppied", "CTPROP"."County Name", "Org"."Region Description", "CTPROP"."City", "CTPROP"."Orig Appraisal Date", "CTPROP"."Curr Appraisal Date", "CTPROP"."Mortgage 1 Bank", "CTPROP"."Mortgage 2 Bank", "CTPROP"."Bank Owned Mortgage 1", "CTPROP"."Bank Owned Mortgage 2", "LN_Master"."Call Report Code", case  when isdate("LN_Master"."Original Note Date") = 1 then convert(datetime,convert(varchar,"LN_Master"."Original Note Date")) else NULL end , "LN_Master"."Account Key", "LN_Master"."Account Number", "LN_Master"."Credit Risk Code", "SIFINA"."10 Char Spare Code 1", "SIFINA"."10 Char Spare Code 2") "T0", (
select "CT_Master"."Collateral Type" AS "C0", "CTPROP"."Owner Occuppied" AS "C1", "CTPROP"."County Name" AS "C2", "Org"."Region Description" AS "C3", "CTPROP"."City" AS "C4", "CTPROP"."Orig Appraisal Date" AS "C5", "CTPROP"."Curr Appraisal Date" AS "C6", "CTPROP"."Mortgage 1 Bank" AS "C7", "CTPROP"."Mortgage 2 Bank" AS "C8", "CTPROP"."Bank Owned Mortgage 1" AS "C9", "CTPROP"."Bank Owned Mortgage 2" AS "C10", "LN_Master"."Call Report Code" AS "C11", case  when isdate("LN_Master"."Original Note Date") = 1 then convert(datetime,convert(varchar,"LN_Master"."Original Note Date")) else NULL end  AS "C12", "LN_Master"."Account Key" AS "C13", "LN_Master"."Account Number" AS "C14", "LN_Master"."Credit Risk Code" AS "C15", "SIFINA"."10 Char Spare Code 1" AS "C16", "SIFINA"."10 Char Spare Code 2" AS "C17", "LN_Master"."Available Balance" AS "C18", "LN_Master"."Past Due Counter 1" AS "C19", "LN_Master"."Past Due Counter 2" AS "C20", "LN_Master"."Past Due Counter 3" AS "C21", "LN_Master"."Past Due Counter 4" AS "C22", "LN_Master"."Past Due Counter 5" AS "C23", "LN_Master"."Past Due Counter 6" AS "C24"
 from (((((((
select "ods_LN_Master"."Account Count", "ods_LN_Master"."Current Loan Status Code", "ods_LN_Master"."Credit Risk Code", "ods_LN_Master"."Call Report Code",  "ods_LN_Master"."Impaired Asset Status", "ods_LN_Master"."Past Due Counter 4", "ods_LN_Master"."Past Due Counter 5", "ods_LN_Master"."Past Due Counter 6", "ods_LN_Master"."Past Due Counter 1", "ods_LN_Master"."Past Due Counter 2", "ods_LN_Master"."Past Due Counter 3", "ods_LN_Master"."Available Balance",  "ods_LN_Master"."Original Note Date", "ods_LN_Master"."Org SKey", "ods_LN_Master"."Account Key", "ods_LN_Master"."Ext Application Code", "ods_LN_Master"."Bank Number", "ods_LN_Master"."Account Number", "ods_LN_Master"."Participation Number", "ods_LN_Master"."CIF Key",  "ods_LN_Master"."Proc Application Code", "ods_LN_Master"."Product Code"
 from "BANK38"."dbo"."v_ods_LN_Master" "ods_LN_Master"
 where ods_LN_Master."Participation Number" = 0) "LN_Master" INNER JOIN (
select "Account Key", "Participation Principal Assets"
 from "BANK38"."dbo"."v_ods_Fact_AllProds_2008"
 where "Application Code" = 'LN') "Participation_Lookups" on "LN_Master"."Account Key" = "Participation_Lookups"."Account Key") LEFT OUTER JOIN (
select "ods_LNIMPR"."Account Key" AS "Account_Key", "ods_LNIMPR"."Book Balance" AS "Book_Balance"
 from "BANK38"."dbo"."v_ods_LNIMPR" "ods_LNIMPR"
 where ods_LNIMPR."Participation Number" = 0) "LNIMPR" on "LN_Master"."Account Key" = "LNIMPR"."Account_Key") LEFT OUTER JOIN "BANK38"."dbo"."fi_Core_Org" "Org" on "LN_Master"."Org SKey" = "Org"."Org Key") LEFT OUTER JOIN (
select *
 from "BANK38"."dbo"."v_ods_SICOD1" "ods_CODE_Master"
 where "ods_CODE_Master"."Application" = 'LN' and "ods_CODE_Master"."Code Type" = 'STAT') "Status_Code" on "LN_Master"."Bank Number" = "Status_Code"."Bankid" and "LN_Master"."Current Loan Status Code" = "Status_Code"."User Code") LEFT OUTER JOIN (
select "V_ODS_SIFINA"."10 Char Spare Code 1", "V_ODS_SIFINA"."10 Char Spare Code 2", "V_ODS_SIFINA"."Account Key"
 from "BANK38"."dbo"."V_ODS_SIFINA" "V_ODS_SIFINA"
 where "V_ODS_SIFINA"."APPLICATION" = 'LN') "SIFINA" on "LN_Master"."Account Key" = "SIFINA"."Account Key") LEFT OUTER JOIN ("BANK38"."dbo"."v_ods_CT_Master" "CT_Master" INNER JOIN (
select "v_ods_CTACTREL"."Account Number", "v_ods_CTACTREL"."Collateral Key"
 from "BANK38"."dbo"."v_ods_CTACTREL" "v_ods_CTACTREL"
 where v_ods_CTACTREL."Application" = 'LN') "CTACTREL" on "CT_Master"."Collateral Key" = "CTACTREL"."Collateral Key") on "LN_Master"."Account Number" = "CTACTREL"."Account Number") LEFT OUTER JOIN "BANK38"."dbo"."v_ods_CTPROP" "CTPROP" on "CT_Master"."Collateral Key" = "CTPROP"."Collateral Key"
 where case  when ("LN_Master"."Impaired Asset Status" = 'C') then 'H' else "Status_Code"."Code Key 2" end  <> 'C' and "LN_Master"."Proc Application Code" in ('RE', 'IL', 'CL') and case  when ("LN_Master"."Impaired Asset Status" = 'C') then 'H' else "Status_Code"."Code Key 2" end  <> 'H' and "CT_Master"."Collateral Type" in ('430', '431') and "CTPROP"."Owner Occuppied" in ('O')) "T1"
 where ("T1"."C0" = "T0"."C0" or "T1"."C0" is null and "T0"."C0" is null) and ("T1"."C1" = "T0"."C1" or "T1"."C1" is null and "T0"."C1" is null) and ("T1"."C2" = "T0"."C2" or "T1"."C2" is null and "T0"."C2" is null) and ("T1"."C3" = "T0"."C3" or "T1"."C3" is null and "T0"."C3" is null) and ("T1"."C4" = "T0"."C4" or "T1"."C4" is null and "T0"."C4" is null) and ("T1"."C5" = "T0"."C5" or "T1"."C5" is null and "T0"."C5" is null) and ("T1"."C6" = "T0"."C6" or "T1"."C6" is null and "T0"."C6" is null) and ("T1"."C7" = "T0"."C7" or "T1"."C7" is null and "T0"."C7" is null) and ("T1"."C8" = "T0"."C8" or "T1"."C8" is null and "T0"."C8" is null) and ("T1"."C9" = "T0"."C9" or "T1"."C9" is null and "T0"."C9" is null) and ("T1"."C10" = "T0"."C10" or "T1"."C10" is null and "T0"."C10" is null) and "T1"."C11" = "T0"."C11" and ("T1"."C12" = "T0"."C12" or "T1"."C12" is null and "T0"."C12" is null) and ("T1"."C13" = "T0"."C13" or "T1"."C13" is null and "T0"."C13" is null) and "T1"."C14" = "T0"."C14" and "T1"."C15" = "T0"."C15" and ("T1"."C16" = "T0"."C16" or "T1"."C16" is null and "T0"."C16" is null) and ("T1"."C17" = "T0"."C17" or "T1"."C17" is null and "T0"."C17" is null)

It was auto generated from a different program that my company uses to look at things in the database. I know that it returns the exact data I am looking for. I want to use this SQL for the time being because I haven't yet gotten up to speed on our database schema yet( and I wont for quite a while, its huge....")

What do I need to do to get the ugly SQL query into my C# code so that it behaves the same way as my simple statement?

My current problem is with the double quotes. Is there a Find/Peplace I can do to make it work?

I was going to re-edit your sql query, but after so much horizontal scrolling, I gave up. — Brad M, Apr 04 '13 at 19:59
You could replace all the double quotes with `\"` and then remove all the line breaks. But wow. — Martin, Apr 04 '13 at 20:01
I know and this is after I cleaned it for an hour. for some reason the SQL generation of this program throws a lot of unessecary things into the query as well as formats it badly — jth41, Apr 04 '13 at 20:01

Icemanind · Accepted Answer · 2013-04-04T20:32:00.060

I would just copy & paste it into a stored procedure, then call the stored procedure using ADO.NET

EDIT:

using (var conn = new SqlConnection("<connection string>")) {
    using (var cmd = new SqlCommand("MyStoredProcedureName")) {
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            // Use the reader to read data
        }
    }
}

Just as an FYI, another way you can do this without using stored procedures is to create a new text file in your project and copy and paste the SQL into that text file. Change the build action of the text file to Embedded Resource and then pull the SQL from the resource using GetManifestResourceStream

So like this:

string sql = "";
using (StreamReader reader = new StreamReader(Assembly.GetExecutingAssembly().
       GetManifestResourceStream("<AssemblyName>.<NameOfTextFile>")) {
    sql = reader.ReadToEnd();
}

if (!string.IsNullOrEmpty(sql))
{
    using (var conn = new SqlConnection("<connection string>")) {
        using (var cmd = new SqlCommand(sql)) {
            cmd.CommandType = CommandType.Text;
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                // Use the reader to read data
            }
        }
    }
}

You could make a search and replace of all double quotes and replace them with `\"` but I @icemanmind's solution is better. — SolarBear, Apr 04 '13 at 20:03
Wouldn't it make more sense to use a view rather than a sproc since this is just selecting? — Ryan Gates, Apr 04 '13 at 20:20
@RyanGates - I think because of the complexity of the selecting, the grouping and ordering by, I'd put it into a stored procedure. Stored procedures are compiled and depending on how much data he has, I think it would run a lot more efficient then a view. — Icemanind, Apr 04 '13 at 20:25

Getting ugly SQL Query to work in C#

1 Answers1

Linked