A
Alan Silver
Hello,
I'm a bit surprised at the amount of boilerplate code required to do
standard data access in .NET and was looking for a way to improve
matters. In Classic ASP, I used to have a common function that was
included in all pages that took an SQL query and returned a disconnected
recordset. This meant that data access could be achieved in a single
line. I would like to do something similar in ASP.NET.
I know I could just duplicate the code, but that means passing the
complete SQL query in as a parameter. I have recently read more about
SQL injection, and would like to take steps to avoid this, so *don't*
want to do...
string SQL = "select * from mtTable where empName='";
SQL += txtEmpName.Text + "'";
as this is open to injection. (Obviously I would escape single quotes in
the text box, but it's still not so secure.)
I would like to set up an array of some type that has two members, a
name and a value. This array could be populated with the parameter names
and values for the query. Thus, the query could be...
"select * from myTable where empName=@empName"
and the (in this case only) parameter would be named "empName" and have
a value of (say) "Fred".
So, my problem is, how do I do this? I can't work out how to allow the
value part of the parameter type to be able to store any data type
(which would probably only be one of string, int or double, but you
never know), without falling into problems when trying to pass the value
to the SqlParameter object that is going to expect it to be of the
correct type.
The end result is that I would like to pass this array into a function
that could loop through the members of the array and create a new
SqlParameter for each element, using the name and value as appropriate.
TIA for any help you can give. I hope this was all clear!!
I'm a bit surprised at the amount of boilerplate code required to do
standard data access in .NET and was looking for a way to improve
matters. In Classic ASP, I used to have a common function that was
included in all pages that took an SQL query and returned a disconnected
recordset. This meant that data access could be achieved in a single
line. I would like to do something similar in ASP.NET.
I know I could just duplicate the code, but that means passing the
complete SQL query in as a parameter. I have recently read more about
SQL injection, and would like to take steps to avoid this, so *don't*
want to do...
string SQL = "select * from mtTable where empName='";
SQL += txtEmpName.Text + "'";
as this is open to injection. (Obviously I would escape single quotes in
the text box, but it's still not so secure.)
I would like to set up an array of some type that has two members, a
name and a value. This array could be populated with the parameter names
and values for the query. Thus, the query could be...
"select * from myTable where empName=@empName"
and the (in this case only) parameter would be named "empName" and have
a value of (say) "Fred".
So, my problem is, how do I do this? I can't work out how to allow the
value part of the parameter type to be able to store any data type
(which would probably only be one of string, int or double, but you
never know), without falling into problems when trying to pass the value
to the SqlParameter object that is going to expect it to be of the
correct type.
The end result is that I would like to pass this array into a function
that could loop through the members of the array and create a new
SqlParameter for each element, using the name and value as appropriate.
TIA for any help you can give. I hope this was all clear!!