文章/答案/技术大牛

发布

社区首页 >问答首页 >申请查询表格

问申请查询表格
EN

Code Review用户

提问于 2015-03-12 11:51:06

回答 3查看 447关注 0票数 -5

我的申请表上有一张搜索表。当我搜索时，检索和显示数据需要花费太多的时间，所以我需要优化这段代码。

public void search_thread()
{
    Stopwatch sw = new Stopwatch();
    sw.Start();



    string sqlcmd = "select en.eventname,q.Q,q.QLevel,q.QUsed,q.QType,q.QAlt,q.QStatus,q.QTag ,m.MediaName,m.MediaPath,m.MediaTag,m.MediaType,O.*,c.Cat1,c.Cat2,c.CatTags,c.SubCat1,c.SubCat2 from tblQ q Inner join tblMediaType  m ON q.QRefNo =M.QRefNo  Inner Join tblOptions  o On  Q.QRefNo =o.QRefNo  Inner Join tblCategories c On Q.QRefNo = c.QRefNo Inner Join tbleventname en On Q.QRefNo = en.QRefNo";

    int flag_checked = 0;

    if (t_search.Text.Trim() != "")
    {
        sqlcmd = sqlcmd + " where (q.Qalt  like N'%" + t_search.Text.Trim() + "%' or o.oAlt1  like N'%" + t_search.Text.Trim() + "%' or o.oAlt2 like N'%" + t_search.Text.Trim() + "%' or o.oAlt3 like N'%" + t_search.Text.Trim() + "%' or o.oAlt4 like N'%" + t_search.Text.Trim() + "%' or o.oAlt5 like N'%" + t_search.Text.Trim() + "%' or O.oAlt6 = N'%" + t_search.Text.Trim() + "%' or o.oAlt7 like N'%" + t_search.Text.Trim() + "%' or o.oAlt8 like N'%" + t_search.Text.Trim() + "%' or o.oAlt9 like N'%" + t_search.Text.Trim() + "%' or o.oAlt10 like N'%" + t_search.Text.Trim() + "%' or o.oAlt11 like N'%" + t_search.Text.Trim() + "%' or o.oAlt12 like N'%" + t_search.Text.Trim() + "%' or q.Q like '%" + t_search.Text.Trim() + "%' or o.o1 like  '%" + t_search.Text.Trim() + "%' or o.o2 like '%" + t_search.Text.Trim() + "%' or o.o3 like '%" + t_search.Text.Trim() + "%' or o.o4 like '%" + t_search.Text.Trim() + "%' or o.o5 like '%" + t_search.Text.Trim() + "%' or o.o6  like '%" + t_search.Text.Trim() + "%' or o.o7  like '%" + t_search.Text.Trim() + "%' or o.o8  like '%" + t_search.Text.Trim() + "%' or o.o9  like '%" + t_search.Text.Trim() + "%'  or o.o10  like '%" + t_search.Text + "%' or o.o11  like '%" + t_search.Text.Trim() + "%' or o.o12  like '%" + t_search.Text.Trim() + "%' or o.CorrectAns  like '%" + t_search.Text.Trim() + "%' )";
        flag_checked = 1;

        if (r_Excludestack.Checked == true)
        {
            if (con.State != ConnectionState.Open)
            {
                if (con.State != ConnectionState.Open){con.Open();}
            }
            SqlCommand smc = new SqlCommand("select distinct count(qrefno) from tblStackData", con);
            int qrefnocnt = Convert.ToInt32(smc.ExecuteScalar());

            string scmd = "select  qrefno from tblStackData";
            string[] temp_qref_array = new string[qrefnocnt];
            smc = new SqlCommand(scmd, con);
            int i = 0;
            if (dr != null)
            {
                if (dr.IsClosed)
                {
                    dr = smc.ExecuteReader();
                }
                else
                {
                    dr.Close();
                    dr = smc.ExecuteReader();
                }
            }
            else
                dr = smc.ExecuteReader();


            while (dr.Read())
                temp_qref_array[i++] = dr["qrefno"].ToString();
            con.Close();
            scmd = "";
            for (i = 0; i < temp_qref_array.Length - 1; i++)
            {
                scmd = scmd + "'" + temp_qref_array[i] + "',";
            }
            scmd = scmd + "'" + temp_qref_array[i] + "'";

            sqlcmd = sqlcmd + " and Q.qrefNo Not IN (" + scmd + ")";

        }

        if (r_search4mstack.Checked == true)
        {


            if (con.State != ConnectionState.Open) {  if (con.State != ConnectionState.Open){con.Open();}}
            SqlCommand smc = new SqlCommand("select distinct count(qrefno) from tblStackData", con);
            int qrefnocnt = Convert.ToInt32(smc.ExecuteScalar());

            string scmd = "select  qrefno from tblStackData";
            string[] temp_qref_array = new string[qrefnocnt];
            smc = new SqlCommand(scmd, con);
            int i = 0;
            if (dr != null)
            {
                if (dr.IsClosed)
                {
                    dr = smc.ExecuteReader();
                }
                else
                {
                    dr.Close();
                    dr = smc.ExecuteReader();
                }
            }
            else
                dr = smc.ExecuteReader();

            while (dr.Read())
                temp_qref_array[i++] = dr["qrefno"].ToString();
            con.Close();
            scmd = "";
            for (i = 0; i < temp_qref_array.Length - 1; i++)
            {
                scmd = scmd + "'" + temp_qref_array[i] + "',";
            }
            scmd = scmd + "'" + temp_qref_array[i] + "'";



            sqlcmd = sqlcmd + " and Q.qrefNo  IN (" + scmd + ")";
        }

        if (r_searchnotused.Checked == true)
        {
            if (con.State != ConnectionState.Open){ if (con.State != ConnectionState.Open){con.Open();}}
            SqlCommand smc = new SqlCommand("select distinct count(qrefno) from tblQ where Qused Not In ('','null','0')", con);
            int qrefnocnt = Convert.ToInt32(smc.ExecuteScalar());

            string scmd = "select  qrefno from  tblQ where Qused Not In ('','null','0')";
            string[] temp_qref_array = new string[qrefnocnt];
            smc = new SqlCommand(scmd, con);
            int i = 0;
            if (dr != null)
            {
                if (dr.IsClosed)
                {
                    dr = smc.ExecuteReader();
                }
                else
                {
                    dr.Close();
                    dr = smc.ExecuteReader();
                }
            }
            else
                dr = smc.ExecuteReader();

            while (  dr.Read())
                temp_qref_array[i++] = dr["qrefno"].ToString();
            con.Close();
            scmd = "";

            for (i = 0; i < temp_qref_array.Length - 1; i++)
            {
                scmd = scmd + "'" + temp_qref_array[i] + "',";
            }

            scmd = scmd + "'" + temp_qref_array[i] + "'";

            sqlcmd = sqlcmd + " and Q.qrefNo Not IN (" + scmd + ")";
        }

        if (r_stackedbutnotused.Checked == true)
        {
            if (con.State != ConnectionState.Open){if (con.State != ConnectionState.Open){con.Open();}}
            SqlCommand smc = new SqlCommand("select distinct count(qrefno) from tblQ where Qused  In ('','null','0')", con);
            int qrefnocnt = Convert.ToInt32(smc.ExecuteScalar());

            string scmd = "select  qrefno from  tblQ where Qused  In ('','null','0')";
            string[] temp_qref_array = new string[qrefnocnt];
            smc = new SqlCommand(scmd, con);
            int i = 0;
            if (dr != null)
            {
                if (dr.IsClosed)
                {
                    dr = smc.ExecuteReader();
                }
                else
                {
                    dr.Close();
                    dr = smc.ExecuteReader();
                }
            }
            else
                dr = smc.ExecuteReader();

            while (dr.Read())
                temp_qref_array[i++] = dr["qrefno"].ToString();
            dr.Close();
            scmd = "";
            for (i = 0; i < temp_qref_array.Length - 1; i++)
            {
                scmd = scmd + "'" + temp_qref_array[i] + "',";
            }
            scmd = scmd + "'" + temp_qref_array[i] + "'";


            smc = new SqlCommand("select distinct count(qrefno)  from tblStackData where qrefno  In(" + scmd + ")", con);
            qrefnocnt = Convert.ToInt32(smc.ExecuteScalar());


            scmd = "select  qrefno from tblStackData where qrefno  In(" + scmd + ")";
            temp_qref_array = new string[qrefnocnt];
            smc = new SqlCommand(scmd, con);
            i = 0;
            try
            {
                dr = smc.ExecuteReader();
                while (dr.Read())
                    temp_qref_array[i++] = dr["qrefno"].ToString();
                con.Close();
            }
            catch (Exception h)
            {

            }

            scmd = "";
            if (temp_qref_array.Length != 0)
            {
                for (i = 0; i < temp_qref_array.Length - 1; i++)
                {
                    scmd = scmd + "'" + temp_qref_array[i] + "',";
                }
                scmd = scmd + "'" + temp_qref_array[i] + "'";
                sqlcmd = sqlcmd + " and Q.qrefNo  IN (" + scmd + ")";
            }

        }
    }

    if (T_refno.Text.Trim() != "")
    {
        if (flag_checked == 0)
        {
            flag_checked = 1;
            sqlcmd = sqlcmd + " where O.QrefNo like '%" + T_refno.Text.Trim() + "%' ";
        }
        else
        {
            sqlcmd = sqlcmd + " and O.QrefNo like '%" + T_refno.Text.Trim() + "%' ";
        }
    }


 //   string en = null;
  //  label6.Invoke(new Action(() => en = c_eventname.Text));
    if ( c_eventname.Text != null &&  c_eventname.Text.Trim() != "")
    {
        if (flag_checked == 0)
        {
            flag_checked = 1;
            sqlcmd = sqlcmd + " where en.eventname = '" +  c_eventname.Text + "' ";
        }
        else
        {
            sqlcmd = sqlcmd + " and en.eventname = '" +  c_eventname.Text + "' ";
        }
    }

  //  label6.Invoke(new Action(() => en = c_qtype.Text));
    if ( c_qtype.Text != null &&  c_qtype.Text.ToString().Trim() != "")
    {
        if (flag_checked == 0)
        {
            flag_checked = 1;
            sqlcmd = sqlcmd + " where Q.QType = '" +  c_qtype.Text + "' ";
        }
        else
        {
            sqlcmd = sqlcmd + " and Q.QType = '" + c_qtype.Text + "' ";
        }
    }
  //  label6.Invoke(new Action(() => en = c_qtype.Text));
    if ( c_level.Text != null && c_level.Text!= "")
    {
        if (flag_checked == 0)
        {
            flag_checked = 1;
            sqlcmd = sqlcmd + " where Q.QLevel = " + c_level.Text;
        }
        else
        {
            sqlcmd = sqlcmd + " and Q.QLevel = " + c_level.Text;
        }
    }

 //   label6.Invoke(new Action(() => en = c_qtype.Text));
//    Console.WriteLine("Qtype " +  C_category.Text);
    if ( C_category.Text != null &&  C_category.Text != "")
    {
        if (flag_checked == 0)
        {
            flag_checked = 1;
            sqlcmd = sqlcmd + " where  C.Cat1 = '" + C_category.Text + "'";
        }
        else
        {
            sqlcmd = sqlcmd + " and  C.Cat1 = '" +  C_category.Text + "'";
        }

    }
   // label6.Invoke(new Action(() => en =  .Text));
    if ( c_subcategory.Text != null &&  c_subcategory.Text.Trim() != "")
    {
        if (flag_checked == 0)
        {
            flag_checked = 1;
            sqlcmd = sqlcmd + " where  C.subCat1 = '" +  c_subcategory.Text + "'";
        }
        else
        {
            sqlcmd = sqlcmd + " and  C.subCat1 = '" + c_subcategory.Text + "'";
        }
    }


    try
    {
        if (con.State != ConnectionState.Open)
        {
            if (con.State != ConnectionState.Open){con.Open();}
        }
        cmd = new SqlCommand(sqlcmd, con);

        if (dr != null) if (dr.IsClosed) { dr = cmd.ExecuteReader(); } else { dr.Close(); dr = cmd.ExecuteReader(); } else dr = cmd.ExecuteReader();
        dataGridView1.Rows.Clear();

        while (dr.Read())
        {
            try
            {
                int f = 0;
                try
                {
                    dataGridView1.Rows.Add(dr["qrefno"].ToString(), dr["QType"].ToString(), dr["Qused"].ToString(), dr["CorrectAns"].ToString(), dr["Q"].ToString(), dr["o1"].ToString(), dr["o2"].ToString(), dr["o3"].ToString(), dr["o4"].ToString(), dr["o5"].ToString(), dr["o6"].ToString(), dr["o7"].ToString(), dr["o8"].ToString(), dr["o9"].ToString(), dr["o10"].ToString(), dr["o11"].ToString(), dr["o12"].ToString(), dr["MediaName"].ToString(), dr["MediaType"].ToString(), dr["cat1"].ToString());
                   // label6.Invoke(new Action(() => f = dataGridView1.Rows.Add(dr["qrefno"].ToString(), dr["QType"].ToString(), dr["Qused"].ToString(), dr["CorrectAns"].ToString(), dr["Q"].ToString(), dr["o1"].ToString(), dr["o2"].ToString(), dr["o3"].ToString(), dr["o4"].ToString(), dr["o5"].ToString(), dr["o6"].ToString(), dr["o7"].ToString(), dr["o8"].ToString(), dr["o9"].ToString(), dr["o10"].ToString(), dr["o11"].ToString(), dr["o12"].ToString(), dr["MediaName"].ToString(), dr["MediaType"].ToString(), dr["cat1"].ToString())));
                }
                catch ( System.InvalidOperationException exe)
                {
                    break;
                }
                if (dr["QUsed"].ToString() == "" || dr["QUsed"].ToString() == "0")
                {

                    //  dataGridView1.Rows[f].DefaultCellStyle.BackColor = Color.Red;
                }
                else
                {
                    dataGridView1.Rows[f].DefaultCellStyle.BackColor = Color.Red;

                }

            }
            catch(Exception ex)
            {
                dr.Close();

            }


        }
        con.Close();
        l_searchfound.Text = dataGridView1.Rows.Count.ToString();
      //  l_searchfound.Invoke(new Action(() => l_searchfound.Text = dataGridView1.Rows.Count + ""));
        sw.Stop();
        Console.WriteLine("Task countUP2 took: " + sw.Elapsed.ToString());
    }
    catch (Exception e1)
    {
        con.Close();
        dr.Close();

    }
  // b_search.Enabled = true;
}

.net

sql-server

回答 3

Code Review用户

回答已采纳

发布于 2015-03-12 12:08:36

我只想进入你的t_search文本框，就像

a'); DROP TABLE tblStackData; DROP TABLE tblOptions; DROP TABLE tblCategories;

删除数据库中的3个表。

帮你自己一个忙，用参数化查询来避免SQL注入

不应该使用字符串连接，比如

scmd = "";
for (i = 0; i < temp_qref_array.Length - 1; i++)
{
    scmd = scmd + "'" + temp_qref_array[i] + "',";
}
scmd = scmd + "'" + temp_qref_array[i] + "'";

在一个循环中。这就是StringBuilder类的用途。

导致

StringBuilder sb = new StringBuilder(1024);
for (i = 0; i < temp_qref_array.Length - 1; i++)
{
    sb.Append("'").Append(temp_qref_array[i]).Append("',");
}
sb.Append("'").Append(temp_qref_array[i]).Append("'");  

sqlcmd = sqlcmd + " and Q.qrefNo Not IN (" + sb.ToString() + ")";

或者更好地使sqlcmd也成为一个StringBuilder，从而导致

sqlcmd.Append(" and Q.qrefNo Not IN (")
for (i = 0; i < temp_qref_array.Length - 1; i++)
{
    sqlcmd.Append("'").Append(temp_qref_array[i]).Append("',");
}
sqlcmd.Append("'").Append(temp_qref_array[i]).Append("')")

但是我们仍然可以做得更好，通过使用@SimonéForsberg建议的String.Join()

sqlcmd.Append(" and Q.qrefNo Not IN ('")
      .Append(String.Join("','" , temp_qref_array))
      .Append("'");

你经常会听到下面的几句话

if (dr != null)
{
    if (dr.IsClosed)
    {
        dr = smc.ExecuteReader();
    }
    else
    {
        dr.Close();
        dr = smc.ExecuteReader();
    }
}
else
    dr = smc.ExecuteReader();

可以简化为

if (dr != null && !dr.IsClosed)
{
   dr.Close();
}
dr = smc.ExecuteReader();

在这里，您一次又一次地给t_search.Text.Trim()打电话(我不知道多久打一次)。

if (t_search.Text.Trim() != "")
{
    sqlcmd = sqlcmd + " where (q.Qalt  like N'%" + t_search.Text.Trim() + "%' or o.oAlt1  like N'%" + t_search.Text.Trim() + "%' or o.oAlt2 like N'%" + t_search.Text.Trim() + "%' or o.oAlt3 like N'%" + t_search.Text.Trim() + "%' or o.oAlt4 like N'%" + t_search.Text.Trim() + "%' or o.oAlt5 like N'%" + t_search.Text.Trim() + "%' or O.oAlt6 = N'%" + t_search.Text.Trim() + "%' or o.oAlt7 like N'%" + t_search.Text.Trim() + "%' or o.oAlt8 like N'%" + t_search.Text.Trim() + "%' or o.oAlt9 like N'%" + t_search.Text.Trim() + "%' or o.oAlt10 like N'%" + t_search.Text.Trim() + "%' or o.oAlt11 like N'%" + t_search.Text.Trim() + "%' or o.oAlt12 like N'%" + t_search.Text.Trim() + "%' or q.Q like '%" + t_search.Text.Trim() + "%' or o.o1 like  '%" + t_search.Text.Trim() + "%' or o.o2 like '%" + t_search.Text.Trim() + "%' or o.o3 like '%" + t_search.Text.Trim() + "%' or o.o4 like '%" + t_search.Text.Trim() + "%' or o.o5 like '%" + t_search.Text.Trim() + "%' or o.o6  like '%" + t_search.Text.Trim() + "%' or o.o7  like '%" + t_search.Text.Trim() + "%' or o.o8  like '%" + t_search.Text.Trim() + "%' or o.o9  like '%" + t_search.Text.Trim() + "%'  or o.o10  like '%" + t_search.Text + "%' or o.o11  like '%" + t_search.Text.Trim() + "%' or o.o12  like '%" + t_search.Text.Trim() + "%' or o.CorrectAns  like '%" + t_search.Text.Trim() + "%' )";
    flag_checked = 1;

最好将返回的值存储在类似的变量中( sqlcmd现在是一个StringBuilder)。

String searchText = t_search.Text.Trim();  
if (searchText.Length > 0)
{
    sqlcmd.Append(" where (q.Qalt  like N'%")
          .Append(searchText)
          .Append("%' or o.oAlt1  like N'%")
          .Append(searchText)
          .Append("%' or o.oAlt2 like N'%")
          .Append(searchText)
          .Append("%' or o.oAlt3 like N'%")
          .Append(searchText)  

           ... and so on

像if (r_Excludestack.Checked == true)这样的表达式可以简化为if (r_Excludestack.Checked)。

你真的应该把这个神的方法分成多个方法。就像.

if (r_Excludestack.Checked)
{

}

应该提取到我现在命名为GetExcludeStackCondition()的方法。

private string GetExcludeStackCondition()
{
    if (con.State != ConnectionState.Open)
    {
        con.Open();
    }

    SqlCommand smc = new SqlCommand("select distinct count(qrefno) from tblStackData", con);
    int qrefnocnt = Convert.ToInt32(smc.ExecuteScalar());

    smc = new SqlCommand("select qrefno from tblStackData", con);

    DataReader dr = smc.ExecuteReader();

    int i = 0;
    string[] temp_qref_array = new string[qrefnocnt];
    while (dr.Read())
    {
        temp_qref_array[i++] = dr["qrefno"].ToString();
    }
    con.Close();

    StringBuilder scmd = new StringBuilder(1024);
    scmd.Append(" and Q.qrefNo Not IN ('")
        .Append(String.Join("','" , temp_qref_array))
        .Append("'");

    return scmd.ToString();
}

会被称为

if (r_Excludestack.Checked)
{
    sqlcmd.Append(GetExcludeStackCondition());
}

其结果是将前一种方法减少了大量代码，并使其更易读和更易于维护。

票数 5

Code Review用户

发布于 2015-03-12 13:23:40

您可以从一个查询中获取结果，然后将其输入另一个查询：

相反，你可以将它们筑巢：

sqlcmd = sqlcmd + " and Q.qrefNo Not IN (select  qrefno from tblStackData)"

票数 1

Code Review用户

发布于 2015-03-13 12:59:46

谢谢你回答我的问题。因此，根据上面的评论，我更新了我的代码。它提高了一些性能。

    public void search_thread()
    {
        Stopwatch sw = new Stopwatch();
        sw.Start();


        StringBuilder sqlcmd = new StringBuilder();
        sqlcmd.Append("select en.eventname,q.Q,q.QLevel,q.QUsed,q.QType,q.QAlt,q.QStatus,q.QTag ,m.MediaName,m.MediaPath,m.MediaTag,m.MediaType,O.*,c.Cat1,c.Cat2,c.CatTags,c.SubCat1,c.SubCat2 from tblQ q Inner join tblMediaType  m ON q.QRefNo =M.QRefNo  Inner Join tblOptions  o On  Q.QRefNo =o.QRefNo  Inner Join tblCategories c On Q.QRefNo = c.QRefNo Inner Join tbleventname en On Q.QRefNo = en.QRefNo");

        int flag_checked = 0;

        String search_text = t_search.Text.Trim();


        if (search_text.Length > 0)
        {
            sqlcmd.Append(" where (q.Qalt  like N'%")
                  .Append(search_text)
                  .Append("%' or o.oAlt1  like N'%")
                  .Append(search_text)
                  .Append("%' or o.oAlt2 like N'%")
                  .Append(search_text)
                  .Append("%' or o.oAlt3 like N'%")
                  .Append(search_text)
                  .Append("%' or o.oAlt4 like N'%")
                  .Append(search_text)
                  .Append("%' or o.oAlt5 like N'%")
                  .Append(search_text)
                  .Append("%' or o.oAlt6 like N'%")
                  .Append(search_text)
                  .Append("%' or o.oAlt7 like N'%")
                  .Append(search_text)
                  .Append("%' or o.oAlt8 like N'%")
                  .Append(search_text)
                  .Append("%' or o.oAlt9 like N'%")
                  .Append(search_text)
                  .Append("%' or o.oAlt10 like N'%")
                  .Append(search_text)
                  .Append("%' or o.oAlt11 like N'%")
                  .Append(search_text)
                  .Append("%' or o.oAlt12 like N'%")
                  .Append(search_text)
                  .Append("%' or q.Q like '%")
                  .Append(search_text)
                  .Append("%' or o.o1 like '%")
                  .Append(search_text)
                  .Append("%' or o.o2 like '%")
                  .Append(search_text)
                  .Append("%' or o.o3 like '%")
                  .Append(search_text)
                  .Append("%' or o.o4 like '%")
                  .Append(search_text)
                  .Append("%' or o.o5 like '%")
                  .Append(search_text)
                  .Append("%' or o.o6 like '%")
                  .Append(search_text)
                  .Append("%' or o.o7 like '%")
                  .Append(search_text)
                  .Append("%' or o.o8 like '%")
                  .Append(search_text)
                  .Append("%' or o.o9 like '%")
                  .Append(search_text)
                  .Append("%' or o.o10 like '%")
                  .Append(search_text)
                  .Append("%' or o.o11 like '%")
                  .Append(search_text)
                  .Append("%' or o.o12 like '%")
                  .Append(search_text)
                  .Append("%' or o.CorrectAns  like '%")
                  .Append(search_text)
                  .Append("%' )");




            flag_checked = 1;

            if (r_Excludestack.Checked)
            {
                sqlcmd.Append(" and Q.qrefNo Not IN (select  qrefno from tblStackData)");
            }

            if (r_search4mstack.Checked)
            {
                sqlcmd.Append(" and Q.qrefNo  IN (select  qrefno from tblStackData)");
            }

            if (r_searchnotused.Checked)
            {
                sqlcmd.Append(" and Q.qrefNo Not IN (select  qrefno from  tblQ where Qused Not In ('','null','0'))");
            }

            if (r_stackedbutnotused.Checked)
            {
                sqlcmd.Append(" and Q.qrefNo  IN (select qrefno from tblStackData where qrefno In(select  qrefno from  tblQ where Qused  In ('','null','0')))");
            }

        }

        if (search_text.Length > 0)
        {
            if (flag_checked == 0)
            {
                flag_checked = 1;
                sqlcmd.Append(" where O.QrefNo like '%" + T_refno.Text.Trim() + "%' ");
            }
            else
            {
                sqlcmd.Append(" and O.QrefNo like '%" + T_refno.Text.Trim() + "%' ");
            }
        }


        //   string en = null;
        //  label6.Invoke(new Action(() => en = c_eventname.Text));
        if (c_eventname.Text != null && c_eventname.Text.Trim() != "")
        {
            if (flag_checked == 0)
            {
                flag_checked = 1;
                sqlcmd.Append(" where en.eventname = '" + c_eventname.Text + "' ");
            }
            else
            {
                sqlcmd.Append(" and en.eventname = '" + c_eventname.Text + "' ");
            }
        }

        //  label6.Invoke(new Action(() => en = c_qtype.Text));
        if (c_qtype.Text != null && c_qtype.Text.Trim() != "")
        {
            if (flag_checked == 0)
            {
                flag_checked = 1;
                sqlcmd.Append(" where Q.QType = '" + c_qtype.Text + "' ");
            }
            else
            {
                sqlcmd.Append(" and Q.QType = '" + c_qtype.Text + "' ");
            }
        }
        //  label6.Invoke(new Action(() => en = c_qtype.Text));
        if (c_level.Text != null && c_level.Text != "")
        {
            if (flag_checked == 0)
            {
                flag_checked = 1;
                sqlcmd.Append(" where Q.QLevel = " + c_level.Text);
            }
            else
            {
                sqlcmd.Append(" and Q.QLevel = " + c_level.Text);
            }
        }

        //   label6.Invoke(new Action(() => en = c_qtype.Text));
        //    Console.WriteLine("Qtype " +  C_category.Text);
        if (C_category.Text != null && C_category.Text != "")
        {
            if (flag_checked == 0)
            {
                flag_checked = 1;
                sqlcmd.Append(" where  C.Cat1 = '" + C_category.Text + "'");
            }
            else
            {
                sqlcmd.Append(" and  C.Cat1 = '" + C_category.Text + "'");
            }

        }
        // label6.Invoke(new Action(() => en =  .Text));
        if (c_subcategory.Text != null && c_subcategory.Text.Trim() != "")
        {
            if (flag_checked == 0)
            {
                flag_checked = 1;
                sqlcmd.Append(" where  C.subCat1 = '" + c_subcategory.Text + "'");
            }
            else
            {
                sqlcmd.Append(" and  C.subCat1 = '" + c_subcategory.Text + "'");
            }
        }

        try
        {
            if (con.State != ConnectionState.Open)
            {
                con.Open();
            }
            cmd = new SqlCommand(sqlcmd.ToString(), con);

            if (dr != null && !dr.IsClosed)
            {
                dr.Close();
            }
            dr = cmd.ExecuteReader();

            dataGridView1.Rows.Clear();

            while (dr.Read())
            {
                try
                {
                    int f = 0;
                    try
                    {
                        dataGridView1.Rows.Add(dr["qrefno"].ToString(), dr["QType"].ToString(), dr["Qused"].ToString(), dr["CorrectAns"].ToString(), dr["Q"].ToString(), dr["o1"].ToString(), dr["o2"].ToString(), dr["o3"].ToString(), dr["o4"].ToString(), dr["o5"].ToString(), dr["o6"].ToString(), dr["o7"].ToString(), dr["o8"].ToString(), dr["o9"].ToString(), dr["o10"].ToString(), dr["o11"].ToString(), dr["o12"].ToString(), dr["MediaName"].ToString(), dr["MediaType"].ToString(), dr["cat1"].ToString());
                        // label6.Invoke(new Action(() => f = dataGridView1.Rows.Add(dr["qrefno"].ToString(), dr["QType"].ToString(), dr["Qused"].ToString(), dr["CorrectAns"].ToString(), dr["Q"].ToString(), dr["o1"].ToString(), dr["o2"].ToString(), dr["o3"].ToString(), dr["o4"].ToString(), dr["o5"].ToString(), dr["o6"].ToString(), dr["o7"].ToString(), dr["o8"].ToString(), dr["o9"].ToString(), dr["o10"].ToString(), dr["o11"].ToString(), dr["o12"].ToString(), dr["MediaName"].ToString(), dr["MediaType"].ToString(), dr["cat1"].ToString())));
                    }
                    catch (System.InvalidOperationException exe)
                    {
                        break;
                    }
                    if (dr["QUsed"].ToString() == "" || dr["QUsed"].ToString() == "0")
                    {

                        //  dataGridView1.Rows[f].DefaultCellStyle.BackColor = Color.Red;
                    }
                    else
                    {
                        dataGridView1.Rows[f].DefaultCellStyle.BackColor = Color.Red;

                    }

                }
                catch (Exception ex)
                {
                    dr.Close();

                }


            }
            con.Close();
            l_searchfound.Text = dataGridView1.Rows.Count.ToString();
            //  l_searchfound.Invoke(new Action(() => l_searchfound.Text = dataGridView1.Rows.Count + ""));
            sw.Stop();
            Console.WriteLine("Task countUP2 took: " + sw.Elapsed.ToString());
        }
        catch (Exception e1)
        {
            MessageBox.Show(e1.ToString());
            con.Close();
            dr.Close();
        }
        // b_search.Enabled = true;
    }

票数 0

页面原文内容由Code Review提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://codereview.stackexchange.com/questions/83915

复制

相似问题

问申请查询表格
EN

回答 3

Code Review用户

Code Review用户

Code Review用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问申请查询表格EN

回答 3

Code Review用户

Code Review用户

Code Review用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问申请查询表格
EN