python - 参数 1：无法从 'System.Data.SqlClient.SqlDataAdapter' 转换为 'System.Data.DataSet' 并且无法识别列名

Question

我不断收到以下错误消息：

Argument 1: cannot convert from 'System.Data.SqlClient.SqlDataAdapter' to 'System.Data.DataSet' and doesn't recognize the column name

我不太确定我做错了什么。此外，我不断收到一条错误消息，说它无法识别列名（对于 else if 语句）。这是在 Visal Studio 2017 中完成的。任何帮助将不胜感激。

使用 System.Windows.Forms；

使用 System.Data.SqlClient；

命名空间 WindowsFormsApp2

{

public partial class Form1 : Form

{

    public Form1()

    {

        InitializeComponent();

    }

    private void textBox1_TextChanged(object sender, EventArgs e)
    {




        if (comboBox1.Text == "Plan_NAME")
        {
            SqlConnection con = new SqlConnection("Data Source=server2016;Initial Catalog=ProjectRD;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
            SqlDataAdapter sda = new SqlDataAdapter("select concat(dbo.[5500].plan_name, dbo.[5500_SF_NEW].SF_PLAN_NAME) as [Plan_NAME], concat(dbo.[5500].ADMIN_CARE_OF_NAME, dbo.[5500_SF_NEW].SF_ADMIN_CARE_OF_NAME) as [Admin_Name], concat(dbo.[5500].SPONS_DFE_EIN, dbo.[5500_SF_NEW].SF_SPONS_EIN) as [EIN_Number], concat(dbo.[5500].SPONS_DFE_PN, dbo.[5500_SF_NEW].SF_PLAN_NUM) as [Plan_Number], concat(dbo.[5500].TOT_ACT_RTD_SEP_BENEF_CNT, dbo.[5500_SF_NEW].SF_TOT_ACT_PARTCP_EOY_CNT) as [Number_Participants], concat(concat( (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS1), (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS2))  ,concat((dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS1]), (dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS2]) )) as [Address], concat(dbo.[5500].SPONS_DFE_MAIL_US_CITY, (dbo.[5500_SF_NEW].[SF_SPONS_US_CITY] ))as [City], concat(dbo.[5500].SPONS_DFE_MAIL_US_STATE, dbo.[5500_SF_NEW].[SF_SPONS_US_STATE])as [State], concat(dbo.[5500].SPONS_DFE_MAIL_US_ZIP, (dbo.[5500_SF_NEW].[SF_SPONS_US_ZIP]) )as [ZipCode], ((isNull(dbo.SCH_H.TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_ASSETS_EOY_AMT,0))) as [Total_Assets], ((isNull(dbo.SCH_H.NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_NET_ASSETS_EOY_AMT,0)) ) as [Net_Assets], ((isNull(dbo.SCH_H.TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_LIABILITIES_EOY_AMT, 0)) +  (isNull(dbo.[5500_SF_NEW].SF_TOT_LIABILITIES_EOY_AMT,0)) ) as [Total_Liabilities] from dbo.[5500] full join dbo.sch_h on dbo.[5500].ack_id = dbo.sch_h.ack_id full join dbo.SCH_I on dbo.[5500].ACK_ID = dbo.sch_i.ACK_ID full join dbo.[5500_SF_NEW] on dbo.[5500].ACK_ID = dbo.[5500_SF_NEW].ACK_ID where (dbo.[5500_SF_NEW].SF_PLAN_NAME in (Select Sf_Plan_name from Temp_Targets_SF) or dbo.[5500].PLAN_NAME in (Select Plan_Name from Temp_Targets)) and [Plan_NAME] LIKE '%" + textBox1.Text + "%'", con);
            DataTable data = new DataTable();
            sda.Fill(data);
            dataGridView1.DataSource = data;

        }
        else if (comboBox1.Text == "Admin_Name")
        {
            SqlConnection con = new SqlConnection("Data Source=server2016;Initial Catalog=ProjectRD;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
            SqlDataAdapter sda = new SqlDataAdapter("select concat(dbo.[5500].plan_name, dbo.[5500_SF_NEW].SF_PLAN_NAME) as [Plan_NAME], concat(dbo.[5500].ADMIN_CARE_OF_NAME, dbo.[5500_SF_NEW].SF_ADMIN_CARE_OF_NAME) as [Admin_Name], concat(dbo.[5500].SPONS_DFE_EIN, dbo.[5500_SF_NEW].SF_SPONS_EIN) as [EIN_Number], concat(dbo.[5500].SPONS_DFE_PN, dbo.[5500_SF_NEW].SF_PLAN_NUM) as [Plan_Number], concat(dbo.[5500].TOT_ACT_RTD_SEP_BENEF_CNT, dbo.[5500_SF_NEW].SF_TOT_ACT_PARTCP_EOY_CNT) as [Number_Participants], concat(concat( (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS1), (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS2))  ,concat((dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS1]), (dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS2]) )) as [Address], concat(dbo.[5500].SPONS_DFE_MAIL_US_CITY, (dbo.[5500_SF_NEW].[SF_SPONS_US_CITY] ))as [City], concat(dbo.[5500].SPONS_DFE_MAIL_US_STATE, dbo.[5500_SF_NEW].[SF_SPONS_US_STATE])as [State], concat(dbo.[5500].SPONS_DFE_MAIL_US_ZIP, (dbo.[5500_SF_NEW].[SF_SPONS_US_ZIP]) )as [ZipCode], ((isNull(dbo.SCH_H.TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_ASSETS_EOY_AMT,0))) as [Total_Assets], ((isNull(dbo.SCH_H.NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_NET_ASSETS_EOY_AMT,0)) ) as [Net_Assets], ((isNull(dbo.SCH_H.TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_LIABILITIES_EOY_AMT, 0)) +  (isNull(dbo.[5500_SF_NEW].SF_TOT_LIABILITIES_EOY_AMT,0)) ) as [Total_Liabilities] from dbo.[5500] full join dbo.sch_h on dbo.[5500].ack_id = dbo.sch_h.ack_id full join dbo.SCH_I on dbo.[5500].ACK_ID = dbo.sch_i.ACK_ID full join dbo.[5500_SF_NEW] on dbo.[5500].ACK_ID = dbo.[5500_SF_NEW].ACK_ID where (dbo.[5500_SF_NEW].SF_PLAN_NAME in (Select Sf_Plan_name from Temp_Targets_SF) or dbo.[5500].PLAN_NAME in (Select Plan_Name from Temp_Targets))  and [Admin_Name] LIKE '%" + textBox1.Text + "%'", con);
            DataTable data = new DataTable();
            sda.Fill(data);
            dataGridView1.DataSource = data;

        }
        else if (comboBox1.Text == "EIN_Number")
        {
            SqlConnection con = new SqlConnection("Data Source=server2016;Initial Catalog=ProjectRD;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
            SqlDataAdapter sda = new SqlDataAdapter("select concat(dbo.[5500].plan_name, dbo.[5500_SF_NEW].SF_PLAN_NAME) as [Plan_NAME], concat(dbo.[5500].ADMIN_CARE_OF_NAME, dbo.[5500_SF_NEW].SF_ADMIN_CARE_OF_NAME) as [Admin_Name], concat(dbo.[5500].SPONS_DFE_EIN, dbo.[5500_SF_NEW].SF_SPONS_EIN) as [EIN_Number], concat(dbo.[5500].SPONS_DFE_PN, dbo.[5500_SF_NEW].SF_PLAN_NUM) as [Plan_Number], concat(dbo.[5500].TOT_ACT_RTD_SEP_BENEF_CNT, dbo.[5500_SF_NEW].SF_TOT_ACT_PARTCP_EOY_CNT) as [Number_Participants], concat(concat( (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS1), (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS2))  ,concat((dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS1]), (dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS2]) )) as [Address], concat(dbo.[5500].SPONS_DFE_MAIL_US_CITY, (dbo.[5500_SF_NEW].[SF_SPONS_US_CITY] ))as [City], concat(dbo.[5500].SPONS_DFE_MAIL_US_STATE, dbo.[5500_SF_NEW].[SF_SPONS_US_STATE])as [State], concat(dbo.[5500].SPONS_DFE_MAIL_US_ZIP, (dbo.[5500_SF_NEW].[SF_SPONS_US_ZIP]) )as [ZipCode], ((isNull(dbo.SCH_H.TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_ASSETS_EOY_AMT,0))) as [Total_Assets], ((isNull(dbo.SCH_H.NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_NET_ASSETS_EOY_AMT,0)) ) as [Net_Assets], ((isNull(dbo.SCH_H.TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_LIABILITIES_EOY_AMT, 0)) +  (isNull(dbo.[5500_SF_NEW].SF_TOT_LIABILITIES_EOY_AMT,0)) ) as [Total_Liabilities] from dbo.[5500] full join dbo.sch_h on dbo.[5500].ack_id = dbo.sch_h.ack_id full join dbo.SCH_I on dbo.[5500].ACK_ID = dbo.sch_i.ACK_ID full join dbo.[5500_SF_NEW] on dbo.[5500].ACK_ID = dbo.[5500_SF_NEW].ACK_ID where (dbo.[5500_SF_NEW].SF_PLAN_NAME in (Select Sf_Plan_name from Temp_Targets_SF) or dbo.[5500].PLAN_NAME in (Select Plan_Name from Temp_Targets))  and [EIN_Number] LIKE '%" + textBox1.Text + "%'", con);
            DataTable data = new DataTable();
            sda.Fill(data);
            dataGridView1.DataSource = data;

        }
                    else if (comboBox1.Text == "Number_Participants")
        {
            SqlConnection con = new SqlConnection("Data Source=server2016;Initial Catalog=ProjectRD;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
            SqlDataAdapter sda = new SqlDataAdapter("select concat(dbo.[5500].plan_name, dbo.[5500_SF_NEW].SF_PLAN_NAME) as [Plan_NAME], concat(dbo.[5500].ADMIN_CARE_OF_NAME, dbo.[5500_SF_NEW].SF_ADMIN_CARE_OF_NAME) as [Admin_Name], concat(dbo.[5500].SPONS_DFE_EIN, dbo.[5500_SF_NEW].SF_SPONS_EIN) as [EIN_Number], concat(dbo.[5500].SPONS_DFE_PN, dbo.[5500_SF_NEW].SF_PLAN_NUM) as [Plan_Number], concat(dbo.[5500].TOT_ACT_RTD_SEP_BENEF_CNT, dbo.[5500_SF_NEW].SF_TOT_ACT_PARTCP_EOY_CNT) as [Number_Participants], concat(concat( (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS1), (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS2))  ,concat((dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS1]), (dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS2]) )) as [Address], concat(dbo.[5500].SPONS_DFE_MAIL_US_CITY, (dbo.[5500_SF_NEW].[SF_SPONS_US_CITY] ))as [City], concat(dbo.[5500].SPONS_DFE_MAIL_US_STATE, dbo.[5500_SF_NEW].[SF_SPONS_US_STATE])as [State], concat(dbo.[5500].SPONS_DFE_MAIL_US_ZIP, (dbo.[5500_SF_NEW].[SF_SPONS_US_ZIP]) )as [ZipCode], ((isNull(dbo.SCH_H.TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_ASSETS_EOY_AMT,0))) as [Total_Assets], ((isNull(dbo.SCH_H.NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_NET_ASSETS_EOY_AMT,0)) ) as [Net_Assets], ((isNull(dbo.SCH_H.TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_LIABILITIES_EOY_AMT, 0)) +  (isNull(dbo.[5500_SF_NEW].SF_TOT_LIABILITIES_EOY_AMT,0)) ) as [Total_Liabilities] from dbo.[5500] full join dbo.sch_h on dbo.[5500].ack_id = dbo.sch_h.ack_id full join dbo.SCH_I on dbo.[5500].ACK_ID = dbo.sch_i.ACK_ID full join dbo.[5500_SF_NEW] on dbo.[5500].ACK_ID = dbo.[5500_SF_NEW].ACK_ID where (dbo.[5500_SF_NEW].SF_PLAN_NAME in (Select Sf_Plan_name from Temp_Targets_SF) or dbo.[5500].PLAN_NAME in (Select Plan_Name from Temp_Targets))  and ([Number_Participants] between '%" + textBox3.Text + "%' and '%" + textBox2.Text + "%')", con);
            DataTable data = new DataTable();
            sda.Fill(data);
            dataGridView1.DataSource = data;

        }
        else if (comboBox1.Text == "City")
        {
            SqlConnection con = new SqlConnection("Data Source=server2016;Initial Catalog=ProjectRD;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
            SqlDataAdapter sda = new SqlDataAdapter("select concat(dbo.[5500].plan_name, dbo.[5500_SF_NEW].SF_PLAN_NAME) as [Plan_NAME], concat(dbo.[5500].ADMIN_CARE_OF_NAME, dbo.[5500_SF_NEW].SF_ADMIN_CARE_OF_NAME) as [Admin_Name], concat(dbo.[5500].SPONS_DFE_EIN, dbo.[5500_SF_NEW].SF_SPONS_EIN) as [EIN_Number], concat(dbo.[5500].SPONS_DFE_PN, dbo.[5500_SF_NEW].SF_PLAN_NUM) as [Plan_Number], concat(dbo.[5500].TOT_ACT_RTD_SEP_BENEF_CNT, dbo.[5500_SF_NEW].SF_TOT_ACT_PARTCP_EOY_CNT) as [Number_Participants], concat(concat( (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS1), (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS2))  ,concat((dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS1]), (dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS2]) )) as [Address], concat(dbo.[5500].SPONS_DFE_MAIL_US_CITY, (dbo.[5500_SF_NEW].[SF_SPONS_US_CITY] ))as [City], concat(dbo.[5500].SPONS_DFE_MAIL_US_STATE, dbo.[5500_SF_NEW].[SF_SPONS_US_STATE])as [State], concat(dbo.[5500].SPONS_DFE_MAIL_US_ZIP, (dbo.[5500_SF_NEW].[SF_SPONS_US_ZIP]) )as [ZipCode], ((isNull(dbo.SCH_H.TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_ASSETS_EOY_AMT,0))) as [Total_Assets], ((isNull(dbo.SCH_H.NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_NET_ASSETS_EOY_AMT,0)) ) as [Net_Assets], ((isNull(dbo.SCH_H.TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_LIABILITIES_EOY_AMT, 0)) +  (isNull(dbo.[5500_SF_NEW].SF_TOT_LIABILITIES_EOY_AMT,0)) ) as [Total_Liabilities] from dbo.[5500] full join dbo.sch_h on dbo.[5500].ack_id = dbo.sch_h.ack_id full join dbo.SCH_I on dbo.[5500].ACK_ID = dbo.sch_i.ACK_ID full join dbo.[5500_SF_NEW] on dbo.[5500].ACK_ID = dbo.[5500_SF_NEW].ACK_ID where (dbo.[5500_SF_NEW].SF_PLAN_NAME in (Select Sf_Plan_name from Temp_Targets_SF) or dbo.[5500].PLAN_NAME in (Select Plan_Name from Temp_Targets))  and [City] LIKE '%" + textBox1.Text + "%'", con);
            DataTable data = new DataTable();
            sda.Fill(data);
            dataGridView1.DataSource = data;

        }
        else if (comboBox1.Text == "State")
        {
            SqlConnection con = new SqlConnection("Data Source=server2016;Initial Catalog=ProjectRD;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
            SqlDataAdapter sda = new SqlDataAdapter("select concat(dbo.[5500].plan_name, dbo.[5500_SF_NEW].SF_PLAN_NAME) as [Plan_NAME], concat(dbo.[5500].ADMIN_CARE_OF_NAME, dbo.[5500_SF_NEW].SF_ADMIN_CARE_OF_NAME) as [Admin_Name], concat(dbo.[5500].SPONS_DFE_EIN, dbo.[5500_SF_NEW].SF_SPONS_EIN) as [EIN_Number], concat(dbo.[5500].SPONS_DFE_PN, dbo.[5500_SF_NEW].SF_PLAN_NUM) as [Plan_Number], concat(dbo.[5500].TOT_ACT_RTD_SEP_BENEF_CNT, dbo.[5500_SF_NEW].SF_TOT_ACT_PARTCP_EOY_CNT) as [Number_Participants], concat(concat( (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS1), (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS2))  ,concat((dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS1]), (dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS2]) )) as [Address], concat(dbo.[5500].SPONS_DFE_MAIL_US_CITY, (dbo.[5500_SF_NEW].[SF_SPONS_US_CITY] ))as [City], concat(dbo.[5500].SPONS_DFE_MAIL_US_STATE, dbo.[5500_SF_NEW].[SF_SPONS_US_STATE])as [State], concat(dbo.[5500].SPONS_DFE_MAIL_US_ZIP, (dbo.[5500_SF_NEW].[SF_SPONS_US_ZIP]) )as [ZipCode], ((isNull(dbo.SCH_H.TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_ASSETS_EOY_AMT,0))) as [Total_Assets], ((isNull(dbo.SCH_H.NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_NET_ASSETS_EOY_AMT,0)) ) as [Net_Assets], ((isNull(dbo.SCH_H.TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_LIABILITIES_EOY_AMT, 0)) +  (isNull(dbo.[5500_SF_NEW].SF_TOT_LIABILITIES_EOY_AMT,0)) ) as [Total_Liabilities] from dbo.[5500] full join dbo.sch_h on dbo.[5500].ack_id = dbo.sch_h.ack_id full join dbo.SCH_I on dbo.[5500].ACK_ID = dbo.sch_i.ACK_ID full join dbo.[5500_SF_NEW] on dbo.[5500].ACK_ID = dbo.[5500_SF_NEW].ACK_ID where (dbo.[5500_SF_NEW].SF_PLAN_NAME in (Select Sf_Plan_name from Temp_Targets_SF) or dbo.[5500].PLAN_NAME in (Select Plan_Name from Temp_Targets))  and [State] LIKE '%" + textBox1.Text + "%'", con);
            DataTable data = new DataTable();
            sda.Fill(data);
            dataGridView1.DataSource = data;

        }
        else if (comboBox1.Text == "ZipCode")
        {
            SqlConnection con = new SqlConnection("Data Source=server2016;Initial Catalog=ProjectRD;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
            SqlDataAdapter sda = new SqlDataAdapter("select concat(dbo.[5500].plan_name, dbo.[5500_SF_NEW].SF_PLAN_NAME) as [Plan_NAME], concat(dbo.[5500].ADMIN_CARE_OF_NAME, dbo.[5500_SF_NEW].SF_ADMIN_CARE_OF_NAME) as [Admin_Name], concat(dbo.[5500].SPONS_DFE_EIN, dbo.[5500_SF_NEW].SF_SPONS_EIN) as [EIN_Number], concat(dbo.[5500].SPONS_DFE_PN, dbo.[5500_SF_NEW].SF_PLAN_NUM) as [Plan_Number], concat(dbo.[5500].TOT_ACT_RTD_SEP_BENEF_CNT, dbo.[5500_SF_NEW].SF_TOT_ACT_PARTCP_EOY_CNT) as [Number_Participants], concat(concat( (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS1), (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS2))  ,concat((dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS1]), (dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS2]) )) as [Address], concat(dbo.[5500].SPONS_DFE_MAIL_US_CITY, (dbo.[5500_SF_NEW].[SF_SPONS_US_CITY] ))as [City], concat(dbo.[5500].SPONS_DFE_MAIL_US_STATE, dbo.[5500_SF_NEW].[SF_SPONS_US_STATE])as [State], concat(dbo.[5500].SPONS_DFE_MAIL_US_ZIP, (dbo.[5500_SF_NEW].[SF_SPONS_US_ZIP]) )as [ZipCode], ((isNull(dbo.SCH_H.TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_ASSETS_EOY_AMT,0))) as [Total_Assets], ((isNull(dbo.SCH_H.NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_NET_ASSETS_EOY_AMT,0)) ) as [Net_Assets], ((isNull(dbo.SCH_H.TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_LIABILITIES_EOY_AMT, 0)) +  (isNull(dbo.[5500_SF_NEW].SF_TOT_LIABILITIES_EOY_AMT,0)) ) as [Total_Liabilities] from dbo.[5500] full join dbo.sch_h on dbo.[5500].ack_id = dbo.sch_h.ack_id full join dbo.SCH_I on dbo.[5500].ACK_ID = dbo.sch_i.ACK_ID full join dbo.[5500_SF_NEW] on dbo.[5500].ACK_ID = dbo.[5500_SF_NEW].ACK_ID where (dbo.[5500_SF_NEW].SF_PLAN_NAME in (Select Sf_Plan_name from Temp_Targets_SF) or dbo.[5500].PLAN_NAME in (Select Plan_Name from Temp_Targets))  and [ZipCode] LIKE '%" + textBox1.Text + "%'", con);
            DataTable data = new DataTable();
            sda.Fill(data);
            dataGridView1.DataSource = data;

        }
        else if (comboBox1.Text == "Total_Assets")
        {
            SqlConnection con = new SqlConnection("Data Source=server2016;Initial Catalog=ProjectRD;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
            SqlDataAdapter sda = new SqlDataAdapter("select concat(dbo.[5500].plan_name, dbo.[5500_SF_NEW].SF_PLAN_NAME) as [Plan_NAME], concat(dbo.[5500].ADMIN_CARE_OF_NAME, dbo.[5500_SF_NEW].SF_ADMIN_CARE_OF_NAME) as [Admin_Name], concat(dbo.[5500].SPONS_DFE_EIN, dbo.[5500_SF_NEW].SF_SPONS_EIN) as [EIN_Number], concat(dbo.[5500].SPONS_DFE_PN, dbo.[5500_SF_NEW].SF_PLAN_NUM) as [Plan_Number], concat(dbo.[5500].TOT_ACT_RTD_SEP_BENEF_CNT, dbo.[5500_SF_NEW].SF_TOT_ACT_PARTCP_EOY_CNT) as [Number_Participants], concat(concat( (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS1), (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS2))  ,concat((dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS1]), (dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS2]) )) as [Address], concat(dbo.[5500].SPONS_DFE_MAIL_US_CITY, (dbo.[5500_SF_NEW].[SF_SPONS_US_CITY] ))as [City], concat(dbo.[5500].SPONS_DFE_MAIL_US_STATE, dbo.[5500_SF_NEW].[SF_SPONS_US_STATE])as [State], concat(dbo.[5500].SPONS_DFE_MAIL_US_ZIP, (dbo.[5500_SF_NEW].[SF_SPONS_US_ZIP]) )as [ZipCode], ((isNull(dbo.SCH_H.TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_ASSETS_EOY_AMT,0))) as [Total_Assets], ((isNull(dbo.SCH_H.NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_NET_ASSETS_EOY_AMT,0)) ) as [Net_Assets], ((isNull(dbo.SCH_H.TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_LIABILITIES_EOY_AMT, 0)) +  (isNull(dbo.[5500_SF_NEW].SF_TOT_LIABILITIES_EOY_AMT,0)) ) as [Total_Liabilities] from dbo.[5500] full join dbo.sch_h on dbo.[5500].ack_id = dbo.sch_h.ack_id full join dbo.SCH_I on dbo.[5500].ACK_ID = dbo.sch_i.ACK_ID full join dbo.[5500_SF_NEW] on dbo.[5500].ACK_ID = dbo.[5500_SF_NEW].ACK_ID where (dbo.[5500_SF_NEW].SF_PLAN_NAME in (Select Sf_Plan_name from Temp_Targets_SF) or dbo.[5500].PLAN_NAME in (Select Plan_Name from Temp_Targets))  and ([Total_Assets] between '%" + textBox3.Text + "%' and '%" + textBox2.Text + "%')", con);
            DataTable data = new DataTable();
            sda.Fill(data);
            dataGridView1.DataSource = data;

        }
        else if (comboBox1.Text == "Net_Assets")
        {
            SqlConnection con = new SqlConnection("Data Source=server2016;Initial Catalog=ProjectRD;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
            SqlDataAdapter sda = new SqlDataAdapter("select concat(dbo.[5500].plan_name, dbo.[5500_SF_NEW].SF_PLAN_NAME) as [Plan_NAME], concat(dbo.[5500].ADMIN_CARE_OF_NAME, dbo.[5500_SF_NEW].SF_ADMIN_CARE_OF_NAME) as [Admin_Name], concat(dbo.[5500].SPONS_DFE_EIN, dbo.[5500_SF_NEW].SF_SPONS_EIN) as [EIN_Number], concat(dbo.[5500].SPONS_DFE_PN, dbo.[5500_SF_NEW].SF_PLAN_NUM) as [Plan_Number], concat(dbo.[5500].TOT_ACT_RTD_SEP_BENEF_CNT, dbo.[5500_SF_NEW].SF_TOT_ACT_PARTCP_EOY_CNT) as [Number_Participants], concat(concat( (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS1), (dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS2))  ,concat((dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS1]), (dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS2]) )) as [Address], concat(dbo.[5500].SPONS_DFE_MAIL_US_CITY, (dbo.[5500_SF_NEW].[SF_SPONS_US_CITY] ))as [City], concat(dbo.[5500].SPONS_DFE_MAIL_US_STATE, dbo.[5500_SF_NEW].[SF_SPONS_US_STATE])as [State], concat(dbo.[5500].SPONS_DFE_MAIL_US_ZIP, (dbo.[5500_SF_NEW].[SF_SPONS_US_ZIP]) )as [ZipCode], ((isNull(dbo.SCH_H.TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_TOT_ASSETS_EOY_AMT,0))) as [Total_Assets], ((isNull(dbo.SCH_H.NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_NET_ASSETS_EOY_AMT, 0)) + (isNull(dbo.[5500_SF_NEW].SF_NET_ASSETS_EOY_AMT,0)) ) as [Net_Assets], ((isNull(dbo.SCH_H.TOT_LIABILITIES_EOY_AMT, 0)) + (isNull(dbo.SCH_I.SMALL_TOT_LIABILITIES_EOY_AMT, 0)) +  (isNull(dbo.[5500_SF_NEW].SF_TOT_LIABILITIES_EOY_AMT,0)) ) as [Total_Liabilities] from dbo.[5500] full join dbo.sch_h on dbo.[5500].ack_id = dbo.sch_h.ack_id full join dbo.SCH_I on dbo.[5500].ACK_ID = dbo.sch_i.ACK_ID full join dbo.[5500_SF_NEW] on dbo.[5500].ACK_ID = dbo.[5500_SF_NEW].ACK_ID where (dbo.[5500_SF_NEW].SF_PLAN_NAME in (Select Sf_Plan_name from Temp_Targets_SF) or dbo.[5500].PLAN_NAME in (Select Plan_Name from Temp_Targets))  and ([Net_Assets] between '%" + textBox3.Text + "%' and '%" + textBox2.Text + "%')", con);
            DataTable data = new DataTable();
            sda.Fill(data);
            dataGridView1.DataSource = data;


        }
    }
    private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
    {

    }

    private void label1_Click(object sender, EventArgs e)
    {

    }

    private void label4_Click(object sender, EventArgs e)
    {
    }

    private void textBox1_TextChanged_1(object sender, EventArgs e)
    {
    }

    private void textBox2_TextChanged_1(object sender, EventArgs e)
    {
    }

    private void textBox3_TextChanged_1(object sender, EventArgs e)
    {

    }
}

}

标签： pythonsqlvisual-studiosql-server-2008smss

Answer 1

更简洁更简洁的代码可能会得到更快的答案。

我可以看到的一个问题是，对于您基于数字（Number_Participants、Total_Assets、Net_Assets）添加 where 术语的 3 种情况，您包括'%" ... "%'使用BETWEEN. 我在下面重写了您的基本代码以压缩它，应用一些常见的 SQL 样式并希望使其更具可读性，但现在跳过了参数化。

看看能不能解决你的问题。

string selectTerm = @"SELECT CONCAT(dbo.[5500].plan_name, dbo.[5500_SF_NEW].SF_PLAN_NAME) [Plan_NAME]
        , CONCAT(dbo.[5500].ADMIN_CARE_OF_NAME, dbo.[5500_SF_NEW].SF_ADMIN_CARE_OF_NAME) [Admin_Name]
        , CONCAT(dbo.[5500].SPONS_DFE_EIN, dbo.[5500_SF_NEW].SF_SPONS_EIN) [EIN_Number]
        , CONCAT(dbo.[5500].SPONS_DFE_PN, dbo.[5500_SF_NEW].SF_PLAN_NUM) [Plan_Number]
        , CONCAT(dbo.[5500].TOT_ACT_RTD_SEP_BENEF_CNT, dbo.[5500_SF_NEW].SF_TOT_ACT_PARTCP_EOY_CNT) [Number_Participants]
        , CONCAT(dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS1, dbo.[5500].SPONS_DFE_MAIL_US_ADDRESS2, dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS1], dbo.[5500_SF_NEW].[SF_SPONS_US_ADDRESS2]) [Address]
        , CONCAT(dbo.[5500].SPONS_DFE_MAIL_US_CITY, dbo.[5500_SF_NEW].[SF_SPONS_US_CITY]) [City]
        , CONCAT(dbo.[5500].SPONS_DFE_MAIL_US_STATE, dbo.[5500_SF_NEW].[SF_SPONS_US_STATE]) [State]
        , CONCAT(dbo.[5500].SPONS_DFE_MAIL_US_ZIP, dbo.[5500_SF_NEW].[SF_SPONS_US_ZIP]) [ZipCode]
        , (ISNULL(dbo.SCH_H.TOT_ASSETS_EOY_AMT, 0) + ISNULL(dbo.SCH_I.SMALL_TOT_ASSETS_EOY_AMT, 0) + ISNULL(dbo.[5500_SF_NEW].SF_TOT_ASSETS_EOY_AMT,0)) [Total_Assets]
        , (ISNULL(dbo.SCH_H.NET_ASSETS_EOY_AMT, 0) + ISNULL(dbo.SCH_I.SMALL_NET_ASSETS_EOY_AMT, 0) + ISNULL(dbo.[5500_SF_NEW].SF_NET_ASSETS_EOY_AMT,0)) [Net_Assets]
        , (ISNULL(dbo.SCH_H.TOT_LIABILITIES_EOY_AMT, 0) + ISNULL(dbo.SCH_I.SMALL_TOT_LIABILITIES_EOY_AMT, 0) +  ISNULL(dbo.[5500_SF_NEW].SF_TOT_LIABILITIES_EOY_AMT,0)) [Total_Liabilities]
     FROM dbo.[5500] 
        FULL JOIN dbo.sch_h ON dbo.[5500].ack_id = dbo.sch_h.ack_id
        FULL JOIN dbo.SCH_I ON dbo.[5500].ACK_ID = dbo.sch_i.ACK_ID
        FULL JOIN dbo.[5500_SF_NEW] ON dbo.[5500].ACK_ID = dbo.[5500_SF_NEW].ACK_ID
     WHERE dbo.[5500_SF_NEW].SF_PLAN_NAME IN (SELECT Sf_Plan_name FROM Temp_Targets_SF)
        OR dbo.[5500].PLAN_NAME IN (SELECT Plan_Name FROM Temp_Targets)";

string whereTerm;

if (comboBox1.Text == "Plan_NAME") whereTerm = " AND [Plan_NAME] LIKE '%" + textBox1.Text + "%';";
else if (comboBox1.Text == "Admin_Name") whereTerm = " AND [Admin_Name] LIKE '%" + textBox1.Text + "%';";
else if (comboBox1.Text == "EIN_Number") whereTerm = " AND [EIN_Number] LIKE '%" + textBox1.Text + "%';";
else if (comboBox1.Text == "Number_Participants") whereTerm = " AND [Number_Participants] BETWEEN " + textBox3.Text + " AND " + textBox2.Text + ";";
else if (comboBox1.Text == "City") whereTerm = " AND [City] LIKE '%" + textBox1.Text + "%';";
else if (comboBox1.Text == "State") whereTerm = " AND [State] LIKE '%" + textBox1.Text + "%';";
else if (comboBox1.Text == "ZipCode") whereTerm = "AND [ZipCode] LIKE '%" + textBox1.Text + "%';";
else if (comboBox1.Text == "Total_Assets") whereTerm = " AND [Total_Assets] BETWEEN " + textBox3.Text + " AND " + textBox2.Text + ";";
else if (comboBox1.Text == "Net_Assets") whereTerm = " AND [Net_Assets] BETWEEN " + textBox3.Text + " AND " + textBox2.Text + ";";

SqlConnection con = new SqlConnection("Data Source=server2016;Initial Catalog=ProjectRD;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
SqlDataAdapter sda = new SqlDataAdapter(selectTerm + whereTerm, con);
DataTable data = new DataTable();
sda.Fill(data);
dataGridView1.DataSource = data;