Enterprise Library Data Access and Stored Procedure Synonyms
The Enterprise Library Data Access application block offers some time-saving routines for calling stored procedures. However, they don't all work when the stored procedure is a synonym.
For example, once I have a Database object, I can get a DataReader from a stored proc called GetUser by simply calling:
-
Database db = DatabaseFactory.CreateDatabase();
-
IDataReader reader = db.ExecuteReader("GetUser", userId);
However, if the stored procedure is a synonym I get an error:
System.InvalidOperationException : The stored procedure 'GetUser' doesn't exist.
The problem isn't actually the Enterprise Library. The problem is - as far as I can see - a bug in ADO.NET. It is the following call that fails:
-
SqlCommandBuilder.DeriveParameters((SqlCommand)discoveryCommand);
It seems to me that SqlCommandBuilder.DeriveParameters(SqlCommand command) hasn't been tested with the stored-proc-is-a-synonym scenario: the SQL that it uses to retrieve the parameters returns an empty result set.
I'm not too phased by this problem because I don't really like specifying the parameter values by ordinal position, which is what you're doing when you just supply a params object[] of values. So, I actually prefer using the workaround, which is to create the parameters explicitly:
-
Database db = DatabaseFactory.CreateDatabase();
-
(SqlConnection)db.CreateConnection());
-
cmd.CommandType = CommandType.StoredProcedure;
-
db.AddInParameter(cmd, "@UserId", DbType.Int32, userId);
-
IDataReader reader = db.ExecuteReader(cmd);