CODEWITHSUNDEEP
c#.netdatabase
Freelancer
Freelancer

Reputation: 9074

c# search functionality query/code

I am developing Windows application.

I that , i am implementing Search functionality.

I have 5 filters [Search categories].

Partycode , branchId , Symbol ,BuySell , TerminalId.

If All textboxes for this are empty then it should show all data.

If Partycode is filled then it should show data for particular party code only.

If party code and branchId text boxes are filled then it should give data for particode with particular terminal Id. ...And Conditions goes on for all permutations and combinations.

I am writting different if else statement. These are becoming many If-Else statement.

Is there particular way to write query for this situation? Or is there any functionality .NET provides to deal with it?

Or i am going in the right direction[With lots of If-Else statements]?

I have Tried>>

 private void btnRefresh_Click_1(object sender, EventArgs e)
        {
            string SQL = "";
            if ((txtSearchPartyCode.Text == "") && (txtSearchBranchId.Text == "") && (txtSearchSymbol.Text == "") && (txtSearchTerminalId.Text == "")&&(cmbBuySell.Text==""))
            {

                SQL = "select Party_Code,TradeNo,Scrip_Code,Inst_Type,Expirydate,Strike_price,Option_type,TerminalId,Branch_Id,Buy_Sell,Trade_Qty,Market_Rate,Sauda_Date,OrderNo from tradeFile";

            }
            else
                if ((txtSearchPartyCode.Text != "") && (txtSearchBranchId.Text == "") && (txtSearchSymbol.Text == "") && (txtSearchTerminalId.Text == "")&&(cmbBuySell.Text==""))    
                {

                        SQL="select Party_Code,TradeNo,Scrip_Code,Inst_Type,Expirydate,Strike_price,Option_type,TerminalId,Branch_Id,Buy_Sell,Trade_Qty,Market_Rate,Sauda_Date,OrderNo from tradeFile where Party_Code='"+txtSearchPartyCode.Text+"'";

                }
                else
                    if ((txtSearchPartyCode.Text == "") && (txtSearchBranchId.Text != "") && (txtSearchSymbol.Text == "") && (txtSearchTerminalId.Text == "")&&(cmbBuySell.Text==""))
                    {
                        SQL="select Party_Code,TradeNo,Scrip_Code,Inst_Type,Expirydate,Strike_price,Option_type,TerminalId,Branch_Id,Buy_Sell,Trade_Qty,Market_Rate,Sauda_Date,OrderNo from tradeFile where Branch_Id='"+txtSearchBranchId.Text+"'";
                    }
            else
                        if ((txtSearchPartyCode.Text == "") && (txtSearchBranchId.Text == "") && (txtSearchSymbol.Text != "") && (txtSearchTerminalId.Text == "")&&(cmbBuySell.Text==""))
                        {
                            SQL="select Party_Code,TradeNo,Scrip_Code,Inst_Type,Expirydate,Strike_price,Option_type,TerminalId,Branch_Id,Buy_Sell,Trade_Qty,Market_Rate,Sauda_Date,OrderNo from tradeFile where Scrip_Code='"+txtSearchSymbol.Text+"'";
                        }
            else
                        if ((txtSearchPartyCode.Text == "") && (txtSearchBranchId.Text == "") && (txtSearchSymbol.Text == "") && (txtSearchTerminalId.Text != "")&&(cmbBuySell.Text==""))
                        {
                            SQL="select Party_Code,TradeNo,Scrip_Code,Inst_Type,Expirydate,Strike_price,Option_type,TerminalId,Branch_Id,Buy_Sell,Trade_Qty,Market_Rate,Sauda_Date,OrderNo from tradeFile where TerminalId='"+txtSearchTerminalId.Text+"'";
                        }
            else
                            if((txtSearchPartyCode.Text == "") && (txtSearchBranchId.Text == "") && (txtSearchSymbol.Text == "") && (txtSearchTerminalId.Text == "")&&(cmbBuySell.Text!=""))
                            {
                                float buy_Sell=0;
                                if(cmbBuySell.Text=="Buy")
                                    buy_Sell=1;
                                else
                                    if(cmbBuySell.Text=="Sell")
                                        buy_Sell=2;
                                SQL="select Party_Code,TradeNo,Scrip_Code,Inst_Type,Expirydate,Strike_price,Option_type,TerminalId,Branch_Id,Buy_Sell,Trade_Qty,Market_Rate,Sauda_Date,OrderNo from tradeFile where Buy_Sell='"+buy_Sell+"'";
                            }

                   try
                   {
                    da = new SqlDataAdapter(SQL, con);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    gvTradeFile.DataSource = ds.Tables[0];
                   }
                   catch(Exception ex)
                   {
                       MessageBox.Show(ex.Message);
                   }
        }

Please pull me out of it.!!!!

Upvotes: 0

Views: 3394

Answers (6)

kaka
kaka

Reputation: 1

Select * from TableName where "+ColumnName+"="'"+txtSearch.Text+"'" // Example

Upvotes: 0

user1475623
user1475623

Reputation:

You might want to try something like this:

private void btnRefresh_Click_1(object sender, EventArgs e)
    {
        string WHERE_STATEMENT = null;
        string SEARCH = null;
        string SQL = null;
        int i = 1;

        switch (i)
        {
            case 1:
                WHERE_STATEMENT = "Party_Code";
                SEARCH = "";
            case 2:
                WHERE_STATEMENT = "Branch_Id";
                SEARCH = "";
            case 3:
                WHERE_STATEMENT = "Scrip_Code";
                SEARCH = "";
            case 4:
                WHERE_STATEMENT = "TerminalId";
                SEARCH = "";
            case 5:
                WHERE_STATEMENT = "BUY_SELL";
                SEARCH = "";
            case 6:
                WHERE_STATEMENT = "";
                SEARCH = "";
        }

        try 
            {           
                SQL = "select Party_Code,TradeNo,Scrip_Code,Inst_Type,Expirydate,Strike_price,Option_type,TerminalId,Branch_Id,Buy_Sell,Trade_Qty,Market_Rate,Sauda_Date,OrderNo from tradeFile where " + WHERE_STATEMENT + " like '%" + SEARCH + "%'";
                da = new SqlDataAdapter(SQL, con);
                DataSet ds = new DataSet();
                da.Fill(ds);
                gvTradeFile.DataSource = ds.Tables[0];
            }
       catch (Exception)
            {
                MessageBox.Show(ex.Message);
                throw;
            }

    }

Upvotes: 0

Drew DeVault
Drew DeVault

Reputation: 957

First, a direct answer to your question:

private void btnRefresh_Click_1(object sender, EventArgs e)
{
    var sb = new StringBuilder("select Party_Code,TradeNo,Scrip_Code,Inst_Type,Expirydate,Strike_price,Option_type,TerminalId,Branch_Id,Buy_Sell,Trade_Qty,Market_Rate,Sauda_Date,OrderNo from tradeFile");
    if (!string.IsNullOrEmpty(txtSearchPartyCode.Text))
        sb.AppendFormat(" where Party_Code='{0}'", txtSearchPartyCode.Text);
    if (!string.IsNullOrEmpty(txtSearchBranchId.Text))
        sb.AppendFormat(" where Branch_Id='{0}'", txtSearchBrandId.Text);
    // ...and so on...
    sb.Append(";");
    try
    {
        da = new SqlDataAdapter(sb.ToString(), con);
        DataSet ds = new DataSet();
        da.Fill(ds);
        gvTradeFile.DataSource = ds.Tables[0];
    }
    catch(Exception ex)
    {
       MessageBox.Show(ex.Message);
    }
}

Change your code to something like this and your life might be a little easier.

Secondly, some advice. Your code makes it apparent that you're a novice, and it might be worth your while to invest some time in reading about better ways to work with databases. There are database wrappers and such you can work with, and it's probably not the best idea to be querying the database directly like this. You also need to sanitize your user input, your code is vulnerable to SQL injection (a good database wrapper will do this for you).

Upvotes: 3

user1475623
user1475623

Reputation:

Make one line of code that calls your sql statement to build then adds the if not blank to add to the sql statement. Partycode , branchId , Symbol ,BuySell , TerminalId. Also change your search function to a like statement, so it's not so binary.

select Party_Code,TradeNo,Scrip_Code,Inst_Type,Expirydate,Strike_price,Option_type,
TerminalId,Branch_Id,Buy_Sell,Trade_Qty,Market_Rate,Sauda_Date,OrderNo 
from tradeFile 
where Party_Code like '&"+txtSearchPartyCode.Text+"&'";**

Upvotes: 1

Siddhartha Gupta
Siddhartha Gupta

Reputation: 507

Create your query dynamically 

Select * from TableName where "+ColumnName+"="'"+txtSearch.Text+"'" // Example

Identify your ColumnName from txtSearch textbox name

hope this helps

Upvotes: 1

TalentTuner
TalentTuner

Reputation: 17556

First of all , try to refactor your code at least for Data Access. Your current class is doing to much.

1- Refactor your data access code into some helper class atleast

2- Try to take out the responsibility of SQL creation into some other helper class.

3- Create a parameter class which will hold all search parameters , pass this parameter object to the SQL Creation class.

4- Read about the Builder Pattern

Upvotes: 1

Related Questions