Build a query from a string with values that varies

Question

I'm building an MVC app and, in this app, I need to get a list of objects based on filters used by the user.

So I might end up having a string like this:

*OBJNAME=[SWORD][OF][THOUSAND][TRUTHS]*OBJTYPE=[ARTEFACT]*OBJNUMBER=[28]*COST=[9]+<*POWER=[3]+>=*RATING=[4]+<*OWNER=[WRIGHT][STAN]*OBJSET=[WORLD OF WARCRAFT]*RARITY=[LEGENDARY]*ADDCOST=[6QGBA]*OBJCOLOR=[ALL]

Every * is a tag I introduce when building the string so that I may identify each attributes separately in the DAL class:

public List<ObjInfo> ListObjWithQryString(string _objQry)
{
    List<ObjInfo> listToReturn = new List<ObjInfo>();

    char[] firstDelimiters = {'*'};

    string[] parsedValues = _objQry.Split(firstDelimiters,
       StringSplitOptions.RemoveEmptyEntries);

    foreach (string parsedValue in parsedValues)
    {
       if (parsedValue.Contains("OBJNAME"))
       {
           // WRITE CODE HERE
       }
    }

    return listToReturn;
}

Now my problem is based on the fact that each parameter may OR may not be there, so the query string will change, and I don't want to have to deal with each 81 possibilities, especially since that may and will change eventually.

Is there any way to dynamically build a query to my database depending on the values obtained so that I may return only the value I am looking for?

* EDIT *

Here are a couple of examples of strings that I may end up having:

*OBJNAME=[SWORD][OF][THOUSAND][TRUTHS]

// Seeking all objects named Sword of a Thousand Truths

*OBJNAME=[SWORD][OF][THOUSAND][TRUTHS*OBJNUMBER=[28]*COST=[9]+<*POWER=[3]+>=*RATING=[4]+<RARITY=[LEGENDARY]*ADDCOST=[6QGBA]*OBJCOLOR=[ALL]

// Seeking all objects named Sword of a Thousand Truths with number 28 with cost lesser than 9 with power greater or equal to 3 with rating lesser than 4 with rarity Legendary with additional cost 6QGBA with all colors

*OBJNAME=[SWORD][OF][THOUSAND][TRUTHS]*OBJTYPE=[ARTEFACT]*OBJNUMBER=[28]*COST=[9]+<*POWER=[3]+>=*RATING=[4]+<*OWNER=[STAN]*OBJSET=[WORLD OF WARCRAFT]

// Seeking all objects named Sword of a Thousand Truths with type Artefact with number 28 with cost lesser than 9 with power greater or equal to 3 with owner Stan with objSet World of Warcraft

*ADDCOST=[6QGBA]*OBJCOLOR=[ALL]

// Seeking all objects with additional cost 6QGBA with all colors

*ADDCOST=[6QGBA]*OBJCOLOR=[BLUE][RED][PURPLE]

// Seeking all objects with additional cost 6QGBA with specific color Blue, Red, Purple

*OBJTYPE=[ARTEFACT]*POWER=[3]+>=

// Seeking all objects with type Artefact and power greater or equal to 3

So, you see that I might end up having different tag to the string. I can paste up additional information if needed.

* ALSO *

Keep in mind that it's how I built my app right now, but I'm open to any suggestion if there's a better way to do things.

EDIT 2

Thanks to @I4V, I have a dictionary that actually groups the values. So:

var dict = Regex.Matches(_cardQry, @"\*(\w+)=([^\*$]+)").Cast<Match>()
                            .ToDictionary(x => x.Groups[1].Value,
                                          x => string.Join(" ", x.Groups[2].Value.Split(new char[] {'[', ']'})));

Will give a dictionary. If I do a foreach through the dictionary with a string having the first parameters passed up there, I end up with this:

KEY / VALUE

OBJNAME= SWORD  OF  THOUSAND TRUTHS  
OBJTYPE= ARTIFACT 
OBJNUMBER= 28 
COST= 9 +>= //(small bug here, there should not be a "=" sign at the end, but it's not major)
POWER= 3 +<=
RATING= 4+<
OWNER= STAN
OBJSET= WORLD OF WARCRAFT
RARITY= LEGENDARY
ADDCOST= 4  W  G  U  G  R 
OBJCOLOR= ALL

and with this line of code:

var whereCondition = "WHERE " + String.Join(" AND ", dict.Select(kv => kv.Key + "='" + kv.Value + "'"));

I end up having a rather use string that looks like this:

WHERE OBJNAME=' SWORD  OF  THOUSAND TRUTHS ' AND OBJTYPE=' ARTIFACT ' AND OBJNUMBER=' 28 ' AND COST=' 9 +<' AND POWER=' 3 +>' AND RATING=' 4 +<' AND OWNER=' STAN ' AND OBJSET='WORLD OF WARCRAFT ' AND RARITY=' LEGENDARY ' AND ADDCOST=' 4  W  G  U  G  R  ' AND OBJCOLOR=' ALL '

Now, the trouble is NOT about building the string, but rather how to use it to make a query call. I am very new to MVC app and, especially, database call. I usually made my database calls like this:

var objQry = from o in m_DB.O
             where o.NAME == _nameProvided
             select o;

How can I use the string to make such a query call?

The real question is, how do you kill that which has no life? Sorry, I had to. Only the South Park geeks will get that. — Chris Dixon
– Chris Dixon, Commented Apr 16, 2013 at 16:00
Yeah, I kinda wanted to plug this so that I may keep people interested (1) and "hide" the true nature of my project (World domination, of course.) :) — hsim
– hsim, Commented Apr 16, 2013 at 16:05
Consider providing couple of small examples of input/output strings - it is not clear what you trying to do. Side note - there is no need for "thank you notes" (especially multiple sets) - comment/upvote answers instead, please avoid partial/misspelled words for variable names - "Qry" is not exactly readable word. — Alexei Levenkov
– Alexei Levenkov, Commented Apr 16, 2013 at 16:06

I4V · Accepted Answer · 2013-04-16 17:57:24Z

1

You can create a dictionary from your input string. Working with it would be simpler.

string input = "*OBJNAME=[SWORD][OF][THOUSAND][TRUTHS]*OBJTYPE=[ARTEFACT]*OBJNUMBER=[28]*COST=[9]+<*POWER=[3]+>=*RATING=[4]+<*OWNER=[WRIGHT][STAN]*OBJSET=[RETURN TO RAVNICA]*RARITY=[LEGENDARY]*ADDCOST=[6QGBA]*OBJCOLOR=[ALL]";

var dict = input.Split(new char[]{'*'},StringSplitOptions.RemoveEmptyEntries)
             .Select(p => p.Split('='))
             .ToDictionary(kv => kv[0], 
                           kv => kv[1].Replace("[", "").Replace("]", " ").Trim());

or using Regex

var dict2 = Regex.Matches(input, @"\*(\w+)=([^\*$]+)").Cast<Match>()
            .ToDictionary(x => x.Groups[1].Value, 
                          x => String.Join(" ",x.Groups[2].Value.Split(new char[]{'[',']'},StringSplitOptions.RemoveEmptyEntries)));

edited Apr 16, 2013 at 17:57

answered Apr 16, 2013 at 16:11

I4V

35.4k6 gold badges72 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

hsim Over a year ago

Sounds cool, but now, with that dictionary in hand, how could I build a dynamic query string that will give me a list of values for each examples that I have put up here?

I4V Over a year ago

@HerveS What do you mean by dynamic query. Can you give an example?

hsim Over a year ago

What I mean is that based on what you give me I will end up having a different dictionary each time the method is hit. So I will need to make 81 or so possible read of the dictionary to tackle all issues. What I would have wanted is kind of something dynamic that would allow me to make a single call to the database instead of many calls.

I4V Over a year ago

@HerveS Sorry, I don't get you. Would you try the answer above and print the result by foreach (var kv in dict) Console.WriteLine(kv.Key + "=" + kv.Value);. After that, we can continue this discussion.

I4V Over a year ago

@HerveS Just a thought (Not a smart way. only an example) var whereCondition = "WHERE " + String.Join(" AND ",dict.Select(kv => kv.Key + "='" + kv.Value + "'"));

|

Collectives™ on Stack Overflow

Build a query from a string with values that varies

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related