DAAB and Parameterized SQL

Topics: Data Access Application Block
Apr 7, 2009 at 8:52 PM
Edited Apr 7, 2009 at 9:07 PM
I am having a helluva time trying to get some parameterized SQL to work using the Enterprise Library (4.0) and cannot find any examples of how to do this. I don't have the option of creating sprocs so I have to use parameterized sql in my code. Works fine when not using parameters but I need to guard against sql injection. Here is what i have tried:

 Dim lDatabase As Database = DatabaseFactory.CreateDatabase("ClaimsSystemWarehouse")
 Dim lDBCommand As DbCommand

 Dim lCommand As String = "SELECT * FROM Employees WHERE EmployeeFirstName = @FirstName"

 lDBCommand = lDatabase.GetSqlStringCommand(lCommand)

 With lDBCommand
  .Parameters.Add(New SqlParameter("@FirstName", SqlDbType.VarChar))
 End With
 
 lDatabase.AddInParameter(lDBCommand, "@FirstName", DbType.String, firstName)

 Try
  myDataset = lDatabase.ExecuteDataSet(lDBCommand)
 Catch ex as SQLException
  ...Try another database
 End Try

But it just tells me that there is a syntax error in my SQL as if it is not recognizing that @FirstName is a parameter. Please help!!!

Thanks!

Apr 8, 2009 at 3:15 AM
I'm getting the error that I added the parameter twice which makes sense, I'm not sure though why it's different from yours.  What is your actual database type?  Why are you adding the parameter twice?  lDatabase.AddInParameter should be enough. 


Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com
Apr 10, 2009 at 10:02 PM

Well I got it to work (kind of) by doing this:

Dim lDatabase As Database = DatabaseFactory.CreateDatabase("ClaimsSystemWarehouse")
 Dim lDBCommand As DbCommand

 Dim lCommand As String = "SELECT * FROM Employees WHERE EmployeeFirstName = @FirstName"

 lDBCommand = lDatabase.GetSqlStringCommand(lCommand)

 With lDBCommand
  .Parameters.Add(New SqlParameter("@FirstName", firstName))
 End With

 Try
  myDataset = lDatabase.ExecuteDataSet(lDBCommand)
 Catch ex as SQLException
  ...Try another database
 End Try

However now a query that was instantaneous (before parameterizing) now takes about 7 seconds running against SQL Server 2008. That sucks...I don't get how this could be but it is KILLING my app so much that I just can't use this. Anyone have any clue what I am doing wrong?

Apr 13, 2009 at 6:41 AM
Hi,

I tried your code and it seems that it never takes 7 seconds to run that. Well i tried it against a Sql Server 2005. Anyway, one thing you can try is to do the same thing but using the regular Data Access code (Not using the DAAB) and see the difference of the performance. Also, I've noticed that when an exception occurs you will try to execute the command against another database, Is the 7 seconds that you are saying is a first successful database instance?


Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
Apr 13, 2009 at 4:32 PM
Hey there,

Well that isn't really the exact query, it was just an example. I do have other simple queries that when not parameterized returned the results instanteously and after parameterizing them using DAAB it went from less than a second to over 7 seconds. That happend with all of my queries - they all started taking WAY longer than they did before or should. Maybe i'll give it a try without the DAAB, but that is our standard so I can't put any data-access code out that isn't using it. Maybe it's just a sql server 2008 thing? Oh, and yes it is the first query that is taking such a long time, if that fails it goes against our live "database" which is on a VMS server and is a flat-file, non-indexed accessed through CONNX. When the queries against that database are so much faster than the same data indexed in a SQL Server database you know something is very wrong...
Apr 14, 2009 at 2:47 AM
You should definitely try it out using the regular ADO.NET code so we could narrow the possibilities.  Try also running the query directly in sql management studio.  


Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com