让SQL查询工作在C＃ [英] Getting ugly SQL Query to work in C#
问题描述
我试图从数据库中的几个表中获取一系列值。
我目前有这个代码工作：
SqlDataReader reader = null;
SqlCommand myCommand = new SqlCommand（select * from ods_LN_Master，xavierConnection）;
reader = myCommand.ExecuteReader（）;
while（reader.Read（））
{
Console.WriteLine（reader [LMBKNO]。ToString（））;
}
但是我真正想做的SQL查询看起来像这样： / C>C0AS抵押类型，T1，C1AS所有者占用， T1C2AS县名，T1C3AS区域描述，T1C4AS城市，T1 C3，C6ASCurr评估日期，T1，C7AS抵押1银行，T1，C8AS抵押2银行，T1 AS银行拥有的按揭1，T1，C10AS银行拥有的按揭2，T1，C11AS呼叫报告代码，T1 C15AS信用风险代码，T1，C16AS帐户号码，T1 当前FICO分数，T1，C17AS原FICO分数，T0，C18AS银行拥有的主资产，T0，C19 T0，C20ASCurr Appraisal Amount，T0，C21AS抵押金额1，T0，C22AS抵押金额2，T1可用余额，T1，C19AS过期计数器1，T1，C20AS过期计数器2，T1 C22AS过期计数器4，T1C23AS过期计数器5，T1。C24AS过期计数器6
从b $ b选择CT_Master。辅助类型ASC0，CTPROP。所有者占用ASC1，CTPROP ASC3，CTPROP，城市ASC4，CTPROP，原始评估日期ASC5，CTPROPCurr评估日期ASC6 按揭1银行ASC7，CTPROP按揭2银行ASC8，CTPROP银行拥有按揭1ASC9，CTPROP C10，LN_Master。调用报告代码ASC11，isdate（LN_Master。原始注释日期）= 1的情况下，然后convert（datetime，convert（varchar，LN_Master日期））else NULL end ASC12，LN_Master帐户密钥ASC13，LN_Master帐户编号ASC14，LN_Master信用风险代码 ，SIFINA。10 Char Spare Code 1ASC16，SIFINA。10 Char Spare Code 2ASC17，sum（LNIMPR。Book_Balance - Participation_Lookups主要资产）ASC18，sum（CTPROP，Orig Appraisal Amount）ASC19，sum（CTPROP，Curr Appraisal Amount）ASC20，sum（CTPROP抵押金额1）ASC21，总额（CTPROP抵押金额2）ASC22
来自（（（（（（（
选择ods_LN_Master ods_LN_Master，当前贷款状态代码，ods_LN_Master，帐户密钥，ods_LN_Master，Org SKey，ods_LN_Master，资产状态损坏，ods_LN_Master ，ods_LN_Master，呼叫报告代码，ods_LN_Master，原始注释日期，ods_LN_Master，银行编号，ods_LN_Master，帐号，ods_LN_Master 客户编号，ods_LN_Master，经销商编号，ods_LN_Master，CIF密钥，ods_LN_Master，短名称，ods_LN_Master，转换帐户密钥，ods_LN_Master dba，v_ods_LN_Master，ods_LN_Master，ods_LN_Master，ods_LN_Master，ods_LN_Master
其中ods_LN_Master参与号= 0）LN_MasterINNER JOIN（
从BANK38中选择帐户键，参与主体资产
dbo。v_ods_Fact_AllProds_2008
其中应用程序代码='LN'）Participation_Lookups在LN_Master。帐户密钥=Participation_Lookups帐户密钥）LEFT OUTER JOIN（
选择ods_LNIMPR。 Account KeyASAccount_Key，ods_LNIMPR。Book BalanceASBook_Balance
fromBANK38。dbo。v_ods_LNIMPRods_LNIMPR
其中ods_LNIMPR。 = 0）LN_MPREonLN_MasterAccount Key=LNIMPR。Account_Key）LEFT OUTER JOINBANK38dbofi_Core_OrgOrgonLN_Master =Org。Org Key）LEFT OUTER JOIN（
select *
从BANK38。dbo。v_ods_SICOD1ods_CODE_Master
其中ods_CODE_Master Bank_number=Status_CodeBankid和LN_Master。当前贷款状态代码 =Status_Code用户代码）LEFT OUTER JOIN（
选择V_ODS_SIFINA。10 Char Spare Code 1，V_ODS_SIFINA。10 Char Spare Code 2，V_ODS_SIFINA Account KeyLN_Master上的SIFINA的关键字
dboV_ODS_SIFINAV_ODS_SIFINA
V_ODS_SIFINA =SIFINA。帐户键）LEFT OUTER JOIN（BANK38。dbo。v_ods_CT_MasterCT_MasterINNER JOIN（
选择v_ods_CTACTREL帐号，v_ods_CTACTREL Collateral Key=CTACTRELCTACTRELCTACTRELCTACTRELCTACTRELCTACTRELCTACTRELCTANTREL 帐户号=CTACTREL。帐号）LEFT OUTER JOINBANK38。dbo。v_ods_CTPROPCT_Master上的CTPROP。 Collateral Key=CTPROPCollateral Key
其中情况（LN_Master，资产状态损坏='C'），'H'elseStatus_Code <> （'RE'，'IL'，'CL'）和案例当（LN_Master。受损资产状态='C'），'H'时，'PROC应用程序代码''C'和'LN_Master Status_Code。Code Key 2end<> （'O'）中的所有者占用
由CT_Master分组的Collateral Type（430，431）和CT_Master CTPROP，CTPROP，原始评估日期，CTPROP，CTPROP，CTPROP 按揭贷款2银行，CTPROP，银行拥有的按揭1，CTPROP，银行拥有的按揭2，当is_（LN_Master，原始注释日期）= 1然后转换（datetime，convert（varchar，LN_Master。原始注释日期））否则NULL结束，调用报告代码 LN_Master，帐户密钥，LN_Master，帐号，LN_Master，信用风险代码，SIFINA，10 Char Spare Code 1，SIFINA ）T0，（
选择CT_Master，附属类型ASC0，CTPROP所有者占用ASC1，CTPROP Org。区域描述ASC3，CTPROP城市ASC4，CTPROP原始评估日期ASC5，CTPROP 按揭1银行ASC7，CTPROP，按揭2银行ASC8，CTPROP银行拥有按揭1ASC9，CTPROP。 银行拥有的抵押2ASC10，LN_Master呼叫报告代码ASC11，当isdate（LN_Master。原始注日期）= 1然后convert（datetime，convert（varchar， LN_Master。原始注释日期））else NULL end ASC12，LN_Master帐户密钥ASC13，LN_Master帐号ASC14信用风险代码ASC15，SIFINA。10字符备用代码1ASC16，SIFINA10字符备用代码2ASC17，LN_Master C18，LN_Master，过期计数器1ASC19，LN_Master，过期计数器2 过去到期计数器4ASC22，LN_Master。过期计数器5ASC23，LN_Master过期计数器6ASC24
从（（（（
）选择ods_LN_Master帐户计数，ods_LN_Master，当前贷款状态代码，ods_LN_Master，信用风险代码，ods_LN_Master ods_LN_Master，受损资产状态，ods_LN_Master，过期计数器4，ods_LN_Master，过期计数器5，ods_LN_Master计数器1，ods_LN_Master，过期计数器2，ods_LN_Master，过期计数器3，ods_LN_Master，可用余额，ods_LN_Master，原始注释日期，ods_LN_Master Ords 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_MasterCIF Key，ods_LN_Master。Proc应用程序代码，ods_LN_Master产品代码
从BANK38。dbo。v_ods_LN_Masterods_LN_Master
其中ods_LN_Master。参与号= 0）LN_MasterINNER JOIN（
从BANK38选择帐户键，参与主体资产
dbo。v_ods_Fact_AllProds_2008$ b $ 帐户密钥）LEFT OUTER JOIN（
选择ods_LNIMPR。帐户密钥）b。应用程序代码='LN' dba。v_ods_LNIMPRods_LNIMPR
其中ods_LNIMPR。参与号= 0）B LN_MAST上的LNIMPRAccount Key=LNIMPR。Account_Key）LEFT OUTER JOINBANK38。dbofi_Core_OrgOrgonLN_MasterOrg SKey=Org 。Org Key）LEFT OUTER JOIN（
从BANK38。dbo。v_ods_SICOD1ods_CODE_Master
选择*
其中ods_CODE_Master Bank_number和LN_Master。当前贷款状态代码=Status_Code，Bank_no和ods_CODE_Master代码类型='STAT' 用户代码）LEFT OUTER JOIN（
选择V_ODS_SIFINA。10 Char Spare Code 1，V_ODS_SIFINA。10 Char Spare Code 2，V_ODS_SIFINA $ b从BANK38。dbo。V_ODS_SIFINAV_ODS_SIFINA
其中V_ODS_SIFINAAPPLICATION='LN'）SIFINAonLN_MasterAccount Key=SIFINA 帐户键）LEFT OUTER JOIN（BANK38。dbo。v_ods_CT_MasterCT_MasterINNER JOIN（
selectv_ods_CTACTREL。Account Number，v_ods_CTACTREL CTANTREL，CTACTREL，CTACTREL，CTACTREL，CTACTREL，CTACTREL，CTACTREL 帐号=CTACTREL帐号）LEFT OUTER JOINBANK38dbov_ods_CTPROPCT_Master上的CTPROP =CTPROPCollateral Key
其中情况（当LN_Master，受损资产状态=C时，则H否则Status_Code （'RE'，'IL'，'CL'）和案例当（LN_Master。受损资产状态='C'），'H'时，'PROC应用程序代码''C'和'LN_Master Status_Code。Code Key 2end<> （'O'）中的所有者占用）T1
其中（T1 C0为T0，C0为T1，C0为空，T0或T1，C1为空，T0，C1为空）和（T1，C2=T0，C2或T1 T0，C3为空），（T1，C3=T0，C3或T1 T1，C4=T0，C4或T1，C4为空，T0 C5或T1，C5为空，T0，C5为空）和（T1，C6=T0，C6或T1和T0，C6为空）和（T1，C7=T0，C7或T1，C7为空，T0 （T1，C8=T0，C8或T1，C8为空，T0 C10或T1，C10或T1，C10或T1，C9为空，T0为T0，C10为空）和T1，C11=T0，C11和（T1 ，C13或T1，C13为空，T0为空，C12为空，T0 C15和T1，C16=T0，C15和T1，C14 C17或T1，C17或T1，C16为空，T0，C16为空）是空和T0。C17为空）
我的公司使用它来查看数据库中的事情。我知道它返回我想要的确切数据。我想暂时使用这个SQL，因为我还没有达到我们的数据库模式的速度（和我不会相当一段时间，它的巨大....）
我需要做什么来获得丑陋的SQL查询到我的C＃代码，以便它的行为与我的简单语句一样？
我可以使用Find / Peplace只需将其复制并粘贴到存储过程中，然后使用 ADO.NET
编辑：
using（var conn = new SqlConnection ; connection string>））{
using（var cmd = new SqlCommand（MyStoredProcedureName））{
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader reader = cmd.ExecuteReader ;
while（reader.Read（））
{
//使用读取器读取数据
}
}
}
作为一个FYI，另一种可以不使用存储过程的方法是在项目中创建一个新的文本文件，将SQL复制并粘贴到该文本文件中。将文本文件的构建操作更改为嵌入式资源，然后使用 GetManifestResourceStream
如此：
code> 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 （））
{
//使用读取器读取数据
}
}
}
}
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 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
}
}
}
}
这篇关于让SQL查询工作在C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
