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
- Are there potential queries that would break this?
- Can the code be written more efficiently ?
- 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
- Use regex to find Declare(whitespace)@parameter and put it in the exclude list
- 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
- Iterate the include list and search for the object property with the same name
- Add the property value to the SqlCommand's SqlParameter collection
Thanks in advance for your help
Aucun commentaire:
Enregistrer un commentaire