locked
Need help with query RRS feed

  • Question

  • Hey everyone,

    My small brain is failing me on this one.. :)

    I'm developing a site for a friend who sells wine.

    In my view I generate 3 lists of listboxes:

    Color:

    White

    Red

    Rosé

    Country:

    France

    Spain

    Italy

    Price:

    <10€

    >=10 < 15€

    > 15€

    When a user clicks on for example white I need to retrieve all white wines, that's easy

    But when a user clicks on white, red, italy, france, price < 10, price > 15 I need to retrieve alle the white and red wines from france and italy with a price < 10 and a price > 15

    that's where my problem starts :)

    In my controller i generate a list of all the wines like this:

    i have a list array: List<int[]> allewijnenmetprijslandenkleurlijst = new List<int[]>();

    Wine1[23,9,27,30] so 23 is the wineID, 9 is the price, 27 is the color ID and 30 is the Country ID, all Id's are unique

    then with the postback I get all the Id's back and I handle them like this:

    var testtesttest = bouwidsvoorquery;
                
                for (int p = 0; p < testtesttest.Count(); p++)
                {
                    string subquery = "";
                    if (p > 0) subquery += " &&";
                    if (testtesttest[p] == 28)
                    {
                        subquery += " allewijnenmetprijslandenkleurlijst[1] < 10";
                    }
    
                    if (testtesttest[p] == 29)
                    {
                        subquery += " allewijnenmetprijslandenkleurlijst[1] >= 10 && allewijnenmetprijslandenkleurlijst[1] < 15";
                    }
    
                    if (testtesttest[p] == 30)
                    {
                        subquery += " allewijnenmetprijslandenkleurlijst[1] > 10 && allewijnenmetprijslandenkleurlijst[1] < 15";
                    }
    
                    if (KleurenTermIds.Contains(testtesttest[p]))
                    {
    
                        subquery += " allewijnenmetprijslandenkleurlijst[2] == ";
                        subquery += testtesttest[p].ToString();
                    }
    
                    if (LandenTermIds.Contains(testtesttest[p]))
                    {
    
                        subquery += " allewijnenmetprijslandenkleurlijst[3] == ";
                        subquery += testtesttest[p].ToString();
                    }
    
                    totalequery += subquery;
                }

    Don't mind the query strings, don't know how to write them...

    Now i have to iterate over the allewijnenmetprijslandenkleurlijst to get all the id's back that match the criteria.

    Can someone help me out here?

    Borrie


    Friday, March 21, 2014 11:01 AM

Answers

  • Dan,

    I solved it yesterday by using system.Linq.Dynamic and using a stringbuilder:

    This is the complete code:

    StringBuilder finalquery = new StringBuilder();
    
                for (var i = 0; i < postedTerms.Count; i++)
                {
    
    
    
                    var selectedTerms = new List<TermPart>();
                    var postedTermIds = new string[0];
                    var AllTerms = new List<TermPart>();
    
                    List<string> lijstje = new List<string>(postedTerms[i].PostedTerms.TermIds);
                    lijstje.Sort();
                    lijstje.RemoveAt(0);
                    postedTermIds = lijstje.ToArray();
    
                    if (postedTermIds != null && postedTermIds.Any())
                    {
    
                        if (i > 0) finalquery.Append(" AND ");
                        finalquery.Append("(");
                        checkifallempty = 1;
                        int kleur = 0;
                        int land = 0;
                        int prijs = 0;
    
                        for (var j = 0; j < postedTermIds.Count(); j++)
                        {
    
                            if (KleurenTermIds.Contains(Convert.ToInt32(postedTermIds[j])))
                            {
                                if (kleur > 0) finalquery.Append(" OR ");
                                finalquery.AppendFormat("KleurID == {0}", postedTermIds[j]);
                                kleur++;
                            }
    
                            if (LandenTermIds.Contains(Convert.ToInt32(postedTermIds[j])))
                            {
                                if (land > 0) finalquery.Append(" OR ");
                                finalquery.AppendFormat("LandID == {0}", postedTermIds[j]);
                                land++;
                            }
    
                            if (PrijsTermIds.Contains(Convert.ToInt32(postedTermIds[j])))
                            {
                                if (prijs > 0) finalquery.Append(" OR ");
                                if (postedTermIds[j] == "28")
                                {
                                    finalquery.AppendFormat("prijs < {0}", 10);
                                }
    
                                if (postedTermIds[j] == "29")
                                {
                                    finalquery.Append("(");
                                    finalquery.AppendFormat("prijs >= {0}", 10);
                                    finalquery.Append(" AND ");
                                    finalquery.AppendFormat("prijs < {0}", 15);
                                    finalquery.Append(")");
                                }
    
                                if (postedTermIds[j] == "30")
                                {
                                    finalquery.AppendFormat("prijs > {0}", 15);
                                }
    
                                prijs++;
                            }
    
                        }
                        finalquery.Append(") ");
                    }

    • Marked as answer by Fanny Liu Friday, March 28, 2014 9:21 AM
    Monday, March 24, 2014 7:46 AM

All replies

  • Best thing would be to create a procedure in sql like below and call it from your application

    CREATE PROC RetrievWineDetails
    @ColorList varchar(1000),
    @COuntryList varchar(5000),
    @PriceList varchar(500)
    AS
    SELECT *
    FROM YourTable t
    WHERE (EXISTS(SELECT 1 
    FROm dbo.ParseValues(@ColorList,',')
    WHERE Val = t.ColorField 
    )
    OR @ColorList ='')
    AND (EXISTS(SELECT 1 
    FROm dbo.ParseValues(@CountryList,',')
    WHERE Val = t.COuntryField 
    )
    OR @CountryList ='')
    ...

    see

    http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, March 21, 2014 11:52 AM
  • Tx for the answer, but i don't need to query the database, I already have them in the list: allewijnenmetprijslandenkleurlijst

    for example:

    allewijnenmetprijslandenkleurlijst [0] {Wine_ID, Price, ColorID, CountryID}

    allewijnenmetprijslandenkleurlijst [1] {Wine_ID, Price, ColorID, CountryID}

    So I need to retrieve the Wine_ID that match the criteria... My brain freezes on this :)

    Friday, March 21, 2014 12:31 PM
  • Don't mind the query strings, don't know how to write them...

    Now i have to iterate over the allewijnenmetprijslandenkleurlijst to get all the id's back that match the criteria.

    The best way to pass a list of values from application code is a table-valued parameter.  The app code can pass each list as a parameter of type SqlDbType.Structured, with the list of values being a DataTable, DataReader or IEnumerable<SqlDataRecord>.  See http://www.dbdelta.com/maximizing-performance-with-table-valued-parameters/ for examples of these techniques.

    Also, I suggest you use a parameterized query.  See http://www.dbdelta.com/help-stop-sql-injection-madness/.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Fanny Liu Monday, March 24, 2014 6:48 AM
    Friday, March 21, 2014 12:33 PM
  • Dan,

    I solved it yesterday by using system.Linq.Dynamic and using a stringbuilder:

    This is the complete code:

    StringBuilder finalquery = new StringBuilder();
    
                for (var i = 0; i < postedTerms.Count; i++)
                {
    
    
    
                    var selectedTerms = new List<TermPart>();
                    var postedTermIds = new string[0];
                    var AllTerms = new List<TermPart>();
    
                    List<string> lijstje = new List<string>(postedTerms[i].PostedTerms.TermIds);
                    lijstje.Sort();
                    lijstje.RemoveAt(0);
                    postedTermIds = lijstje.ToArray();
    
                    if (postedTermIds != null && postedTermIds.Any())
                    {
    
                        if (i > 0) finalquery.Append(" AND ");
                        finalquery.Append("(");
                        checkifallempty = 1;
                        int kleur = 0;
                        int land = 0;
                        int prijs = 0;
    
                        for (var j = 0; j < postedTermIds.Count(); j++)
                        {
    
                            if (KleurenTermIds.Contains(Convert.ToInt32(postedTermIds[j])))
                            {
                                if (kleur > 0) finalquery.Append(" OR ");
                                finalquery.AppendFormat("KleurID == {0}", postedTermIds[j]);
                                kleur++;
                            }
    
                            if (LandenTermIds.Contains(Convert.ToInt32(postedTermIds[j])))
                            {
                                if (land > 0) finalquery.Append(" OR ");
                                finalquery.AppendFormat("LandID == {0}", postedTermIds[j]);
                                land++;
                            }
    
                            if (PrijsTermIds.Contains(Convert.ToInt32(postedTermIds[j])))
                            {
                                if (prijs > 0) finalquery.Append(" OR ");
                                if (postedTermIds[j] == "28")
                                {
                                    finalquery.AppendFormat("prijs < {0}", 10);
                                }
    
                                if (postedTermIds[j] == "29")
                                {
                                    finalquery.Append("(");
                                    finalquery.AppendFormat("prijs >= {0}", 10);
                                    finalquery.Append(" AND ");
                                    finalquery.AppendFormat("prijs < {0}", 15);
                                    finalquery.Append(")");
                                }
    
                                if (postedTermIds[j] == "30")
                                {
                                    finalquery.AppendFormat("prijs > {0}", 15);
                                }
    
                                prijs++;
                            }
    
                        }
                        finalquery.Append(") ");
                    }

    • Marked as answer by Fanny Liu Friday, March 28, 2014 9:21 AM
    Monday, March 24, 2014 7:46 AM