Best Practice: Run SQL query. If no results run another.

[reisende76]

Hello all,

I am attempting to migrate some old code we have from VB 6 to C# using Visual Studio 2008 and I am looking for the best practice solution for my quandry. Here's what I've got.

In the VB 6 code we run a query and populate a data array using GetRows. We then check the array using IsArray(arTemp). If that is false we immediately run a second query with additional WHERE clause criteria and redefine the arTemp array. This isn't a problem in VB 6 as it's just an array with no messy open DataSets or anything else to deal with.

Here's an example:

sqlString = "SELECT FirstName, LastName FROM Employees WHERE ExternalID = " & vExternalID
arTemp = getRcds(sqlString)

If IsArray(arTemp)
   'Employee is found
Else
   sqlString = "SELECT FirstName, LastName FROM Employees WHERE Email = '" & vEmail & "'"
   arTemp = getRcds(sqlString)

   If IsArray(arTemp)
      'Employee is found
   Else
      'Employee not found.  Return error.
   End If
End If

I am curious as to how you guys would approach this in C# .Net. Can I do something similar using DataSet or DataReader? I've been cooking up some work arounds but they all seem to be more involved than is probably needed.

Thanks in advance,

Tony

 

[jmeckley]

I would use an ORM framework to manage data access. then I would design my logic around a domain instead of sql statements. Any solid ORM will manage the ADO.Net objects.
I really like Nhibernate and ActiveRecord.

if you do stick with managing your own ADO.Net objects, then use parameterized queries, not injected sql.

I would also recommend researching the session per conversation (session per request) concept and the unit of work pattern. This is how ORM manage persistence.

with web applications it's very simple web request = conversation = unit of work. With rich client apps the conversation is not so clear cut, so you need to determine the boundaries of a unit of work.

again NH and AR make this very easy.

also if you are doing a 1:1 conversion of VB6 to C# then you're waisting your time. You don't gain anything with that type of conversion.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[reisende76]

Thanks for the reply Jason. Your help is greatly appreciated. I'll have to look into what you've suggested.