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))
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?
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,
Copyright (c) Marimer LLC