is it possible to write a method which creates a method?

Question

hey guys,

this might seem like a no brainer but hopefully after i explain my problem you might understand why i am asking this.

is it possible to have a method which creates a method and its arguements?

the problem:

in my current project i have to many times call different sql statements which arent all that different.

for example i have one where i inserts some new rows but only has 2 columns and another which also inserts new rows but has 12 columns.

i have created a class called utils.cs and in there i have sorted many "handy" methods, such as validation methods which check for numeric input to text boxes etc.

so i thought well instead of having sql writing methods everywhere ill make one in there and call it when i need to so i have but it currently looks like this:

public static string getInsertSQL(string tablename, string colOne, string colTwo, string colThree, string colFour, string colFive, string colSix, string colSeven, string colEight, string colNine, string colTen, string colEleven, string colTwelve,bool active, string valueOne, string valueTwo, string valueThree, string valueFour, string valueFive, string valueSix, string valueSeven, string valueEight, string valueNine, string valueTen, string valueEleven)
    {
        string strSQL = "";
        strSQL += "INSERT INTO " + tablename;
        strSQL += "(" + colOne + " " + colTwo + " " + colThree + " " + colFour + " " + colFive + " " + colSix + " " + colSeven + " " + colEight + " " + colNine + " " + colTen + " " + colEleven + " " + colTwelve + " )";
        strSQL += " values ("+active+", " + valueOne + " " + valueTwo + " " + valueThree + " " + valueFour + " " + valueFive + " " + valueSix + " " + valueSeven + " " + valueEight + " " + valueNine + " " + valueTen + " " + valueEleven + " )";

        return strSQL;
    }

as you can see thats quite a mess

so i wondered if it was at all possible to write a method which would take an arguement of how many colums needed to be inserted and then could create a method with that many arguements.

i hope you can see what i am getting at and dont just sound like a plep!

thanks in advance

Answer 1

First of all, never ever build a SQL string by concatenating values together like in your example. You're leaving yourself open to SQL injection attacks.

Instead, build parameterized queries.

You can in fact generate methods at runtime or code generate methods based on some meta information. However, you might want to see if you can build your SQL query by parsing arguments to a single method, e.g. pass in a Dictionary of column names values.

Here's pseudo-code that outlines the process

public void DoQuery(string table, Dictionary<string, object> columns)
{
    StringBuilder query = new StringBuilder();
    query.Append("SELECT ");
    foreach (KeyValuePair<string, object> kvp in columns)
    {
        query.Append(kvp.Key).Append(","); // You need extra logic to not append a trailing comma.  Exercise to reader ;-)
    }

    // Etc.  Look at how to add parameters to your where clause using provided link

}

Answer 2

Further to @adrianbanks, you could use c#'s var to pass in a variable number of arguments to your method and itterate through the collection

Answer 3

If you really want to do this then simply pass in a params KeyValuePair<string,string>[] columns, this will let you pass as many pairs (Name/Value) as you like and within the function you can access them in an array.

That said I'd seriously recommend not constructing your SQL in this manner. It's open to expoitation and there's many perfectly good frameworks out there (Linq, NHibernate, EntityFramework etc), or even if you don't want to go that far at least use parameterized queries.

Edit: Since you said you're not sure what a Dictionary is I figured you might need more detail. Here's an example given that the question is still valid even if it's not desirable for SQL:

public static string getInsertSQL(string tablename, params KeyValuePair<string, string>[] columns)
{
    string strSQL = "INSERT INTO {0} ({1}) VALUES ({2})";
    string fields = String.Empty;
    string values = String.Empty;
    foreach (KeyValuePair<string, string> column in columns)
    {
     if (!String.IsNullOrEmpty(fields)) fields += ", ";
     if (!String.IsNullOrEmpty(values)) values += ", ";
     fields += column.Key;
     values += "\"" + column.Value + "\""; //Highly recommend replacing with parameters, or at least SQL escaping
    }
    return String.Format(strSQL, tablename, fields, values);
}