Introduction
Have you tried using SQL Server 2005 with your .NET 1.1 application? If your application uses the Microsoft Data Access Application Block or calls SqlCommandBuilder.DeriveParameters()
, you have been encountering weird errors.
The problem exists because the SqlCommandBuilder.DeriveParameters()
method does not pass the schema name parameter (@procedure_schema
) to the sp_procedure_params_rowset
. In SQL Server 2000, the schema name is almost always “dbo”
, so it is sufficient to pass in just the stored procedure name.
The same problem can be seen when attempting to access the database using the Visual Studio 2003 server explorer. The server explorer will connect to the database but will not show stored procedure parameters correctly.
The problem is compounded by the fact that SQL Server 2005 supports the new ‘XML’
data type – a type that does not exist in the SqlDbType
enumeration in 1.1.
The Approach
Encode the schema name as part of your store procedure name. I use the simple method of prefixing the SP name with the schema. If the SP name is “OM_Get_Order_XML”
and the schema is “OM_Order”
, the encoded SP name would be “OM_Order.OM_Get_Order_XML”
.
You can use the following code snippet to decode the stored procedure name:
string schemaName = "dbo";
int firstDot = spName.IndexOf('.');
if (firstDot > 0)
{
schemaName = spName.Substring(0, firstDot);
spName = spName.Substring(firstDot+1);
}
Note that the above call falls back to the old “dbo”
schema if the schema name is not specified.
Now that we have both the schema name and the stored procedure name, we can manually determine the stored procedure parameters.
Determining Stored Procedure Parameters
I run my code in a mixed SQL Server 2000 & 2005 environment. I therefore chose to write my code for maximum compatibility. I first try to run the SqlCommandBuilder.DeriveParameters()
method, and if the method fails – try the manual approach.
The code is:
private static SqlParameter[] DiscoverSpParameterSet
(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
SqlCommand cmd = new SqlCommand(spName, connection);
cmd.CommandType = CommandType.StoredProcedure;
string schemaName = "dbo";
int firstDot = spName.IndexOf('.');
if (firstDot > 0)
{
schemaName = spName.Substring(0, firstDot);
spName = spName.Substring(firstDot+1);
}
connection.Open();
try
{
SqlCommandBuilder.DeriveParameters(cmd);
}
catch
{
SqlCommand getParams = new SqlCommand("sp_procedure_params_rowset", connection);
getParams.CommandType = CommandType.StoredProcedure;
getParams.Parameters.Add("@procedure_name", spName);
getParams.Parameters.Add("@procedure_schema", schemaName);
SqlDataReader sdr = getParams.ExecuteReader();
if (sdr.HasRows)
{
using (sdr)
{
int ParamNameCol = sdr.GetOrdinal("PARAMETER_NAME");
int ParamSizeCol = sdr.GetOrdinal("CHARACTER_MAXIMUM_LENGTH");
int ParamTypeCol = sdr.GetOrdinal("TYPE_NAME");
int ParamNullCol = sdr.GetOrdinal("IS_NULLABLE");
int ParamPrecCol = sdr.GetOrdinal("NUMERIC_PRECISION");
int ParamDirCol = sdr.GetOrdinal("PARAMETER_TYPE");
int ParamScaleCol = sdr.GetOrdinal("NUMERIC_SCALE");
while (sdr.Read())
{
string name = sdr.GetString(ParamNameCol);
string datatype = sdr.GetString(ParamTypeCol);
if (0 == String.Compare("xml", datatype, true))
{
datatype = "Text";
}
object parsedType = Enum.Parse(typeof(SqlDbType), datatype, true);
SqlDbType type = (SqlDbType)parsedType;
bool Nullable = sdr.GetBoolean(ParamNullCol);
SqlParameter param = new SqlParameter(name,type);
int dir = sdr.GetInt16(ParamDirCol);
switch (dir)
{
case 1:
param.Direction = ParameterDirection.Input;
break;
case 2:
param.Direction = ParameterDirection.Output;
break;
case 3:
param.Direction = ParameterDirection.InputOutput;
break;
case 4:
param.Direction = ParameterDirection.ReturnValue;
break;
}
param.IsNullable = Nullable;
if (!sdr.IsDBNull(ParamPrecCol))
{
param.Precision = (Byte)sdr.GetInt16(ParamPrecCol);
}
if (!sdr.IsDBNull(ParamSizeCol))
{
param.Size = sdr.GetInt32(ParamSizeCol);
}
if (!sdr.IsDBNull(ParamScaleCol))
{
param.Scale = (Byte)sdr.GetInt16(ParamScaleCol);
}
cmd.Parameters.Add(param);
}
}
}
}
finally
{
connection.Close();
}
if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}
SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(discoveredParameters, 0);
foreach (SqlParameter sqlParam in discoveredParameters)
{
if ((sqlParam.SqlDbType == SqlDbType.VarChar) &&
(sqlParam.Size == Int32.MaxValue))
{
sqlParam.SqlDbType = SqlDbType.Text;
}
}
foreach (SqlParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return discoveredParameters;
}
Note to Data Access Application Block Users
The code above is a complete replacement for the DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
method of the SqlHelperParameterCache
class.
History
- Version 1.0 – released 10-June-2006