Table Selection Parameter

TheSquirrelKing posted on Thursday, January 22, 2009

So I've got this slightly unusual situation where most of my stored procedures require a parameter (@TableName) to determine which of 3 identically-structured tables in our DB to use. The sprocs are setup this way to avoid having to create triplicates for each table. This is especially important for us because the number of tables is very likely to change in the future. So far the sprocs themselves work great (using sp_executesql gave a nice boost to efficiency by allowing execution plan reuse), but I have been running the app with the TableName parameter hard-coded in the DataPortal methods like so:

 Private Overloads Sub DataPortal_Fetch(ByVal criteria As RootCriteria)
  Dim db As Database = DatabaseFactory.CreateDatabase()
  Dim dbCommand As DbCommand = db.GetStoredProcCommand("GetDataPointById")

  db.AddInParameter(dbCommand, "@TableName", DbType.String, "TableNameHere")
  db.AddInParameter(dbCommand, "@Id", DbType.Guid, criteria.Id)

  Using dataReader As SafeDataReader = New SafeDataReader(db.ExecuteReader(dbCommand))
   While dataReader.Read()
    DoFetch(dataReader)
   End While
  End Using
 End Sub

So now I am trying to discover the best way to pass the selected TableName parameter from the user (be it from the GUI or in my unit test config) to the DataPortal methods given that these methods would not have access to application variables or app.config settings that might be on another machine (depending on the tiers that are in use) and that DataPortal_Insert and DataPortal_Update don't take criteria objects.

Does anyone have any ideas or suggestions?

Thanks,
TSK

Curelom replied on Thursday, January 22, 2009

depending on your business logic, you could derive which tablename to use via the properties of the object itself.  If these are not sufficient, you could add another property/variable specifically for this case.  This probably isn't as clean as you would like it, but it should work.

TheSquirrelKing replied on Thursday, January 22, 2009

Yeah, I was thinking that I could pass the parameter in with the criteria class for the Create/Fetch methods, set a private helper variable in the object itself, and then use that in the DP methods, but, as you point out, it's not as clean as I'd like it. That said, barring any other creative solutions, this is what I'm going to run with.

Thanks for the second opinion,
TSK