Driving "ands" using lists

May 23, 2012 at 10:31 PM

I have just started implementing a data access layer using dyansql. My application is entirely configuration driven and has no knowledge of the schemas it is interacting with (beyond the configuration). I have run into a block when I have a dictionary of values:

 

function(string tableName, dictionary<string, object> updateValues, dictionary<string, object> whereValues)

 

DBQuery query = DBQuery.Update(tableName)

.Set(???)     //Need to fill in my key value pairs from columnValues here

.AndSet(???)

.WhereFieldEqual(???)   //Need to fill in my key value pairs from updateValues here

.AndWhereFieldEqual(???)

 

I'm sure there is probably a way to do this and I am just missing something.

 

Thank You,

 

-Matt Hoffmann

May 23, 2012 at 10:48 PM

I managed to figure out a solution that seems to meet my needs. Please advise if there is a better way to do this:

My solution:

 

DBQuery query = DBQuery.Update(tableName);

foreach(KeyValuePair updateValue in updateValues)

{

if(firstValue)

query.Set(updateValue.key, updateValue.value);

else

query.andSet(updateValue.key, updateValue.value);

}

 

And do the same for the where.

It would be nice if I didn't have to separate the "Set" and "AndSet"

 

Thank you,

-Matt Hoffmann

May 24, 2012 at 8:15 AM
Edited May 24, 2012 at 8:16 AM

User the Generics version of key value pair and then the compiler will know which Set() method to call.
And Set() will automatically append if the is an existing assignment but is expecting a clause as the value argument.
We can't do that with Where() because it could be an AndWhere(), or an OrWhere() or any other form of construct.

So....

 

            var qry = DBQuery.Update(tableName);

            //set
            foreach (KeyValuePair<string, object> kvp in assignValues)
            {
                qry.Set(kvp.Key, DBParam.ParamWithValue(kvp.Value));
            }

            //where
            bool first = true;

            foreach (KeyValuePair<string,object> kvp in whereValues)
            {
                if (first)
                    qry.WhereField(kvp.Key, Compare.Equals, DBParam.ParamWithValue(kvp.Value));
                else
                    qry.AndWhere(kvp.Key,Compare.Equals,DBParam.ParamWithValue(kvp.Value));
                first = false;
            }

            //commit
            db.ExecuteNonQuery(qry);

 

...will give you a nice SQL update statement using parameters rather than constant values (faster). If you know the DbType of the value beforehand it will be faster to tell DynaSQL rather than the library trying to dynamically determine the type every time the method gets called.