vendredi 5 juin 2015

Filling sqlCommand Parameters with an object - Potential Issues

I have written a method does the following.

What it does

  • Parses an SqlCommand object string for parameters in a parameterized SQL query.
  • Excludes any parameters declared and parameters starting with @@.
  • Finds properties in an object using reflection that share the parameter name
  • Uses the object's property value and adds a parameter to the parameter collection

Reflection

This method is designed to be used in a web environment that is highly customizable and get parameters from a variety or deserialized JSON objects. I am very aware that reflection is slow but the idea is only to reflect a single object for a return set and not to be used in data processing loops, the reflection issue is not something I care about.

Questions

Aside from listing downsides of reflection, I would like to know what issues anyone sees with this code that I might not have considered. Please make the assumption that the object has the correct properties. I am having a hard time testing this because I wrote it to parse the SQL that I know. I cannot write tests for SQL I don't know. It seems to pass every test of SQL I have written.

Is there any issue with the code

  1. Are there potential queries that would break this?
  2. Can the code be written more efficiently ?
  3. Are there any other issues that you might see with this idea ?

Code

public static void LoadParametersByObject(SqlCommand command, Object obj)
{
    var DeclareREG = new Regex("(?<=Declare\\s*)@\\w{1,}");// finds all Declare @name
    var ParameterREG = new Regex("(@{1,2}\\w{1,})");//finds all @name and all @@name
    List<String> Exclude = (from Match x in DeclareREG.Matches(command.CommandText) select x.Value.Replace("@", "").ToUpper()).ToList();
    List<String> Include = (from Match x in ParameterREG.Matches(command.CommandText)
                            where !x.Value.StartsWith("@@") && !Exclude.Contains(x.Value.Replace("@", "").ToUpper())
                            select x.Value.Replace("@", "").ToUpper()).Distinct().ToList();
    foreach (PropertyInfo prop in (from x in obj.GetType().GetProperties() where Include.Contains(x.Name.ToUpper()) select x).ToArray())
    {
        command.Parameters.AddWithValue("@" + prop.Name, prop.GetValue(obj));
    }
}

Code Breakdown

  1. Use regex to find Declare(whitespace)@parameter and put it in the exclude list
  2. Use regex to find any parameters starting with @ or @@. throw out the @@ as they are internal SQL objects and check the @ parameters to make sure they are not in the exclude list as they were declared in the text of the query and add the results to the include list
  3. Iterate the include list and search for the object property with the same name
  4. Add the property value to the SqlCommand's SqlParameter collection

Thanks in advance for your help





Aucun commentaire:

Enregistrer un commentaire