A minute before taking your advise - There are times where I don't take the DataReader path because it holds exclusively on to the connection object. When working on a server application it seems bad manners to do so. What do you think?
Remark - One can argue that since we are filling cache of a closed list (no more 200 stored-procedures) this is a "one time" thing.
Thank you, Gad.
Gad, The code looks good. One comment: It would be good to use DataReader in this case, as you dont want to store the data in the cache.
--
HTH,
Sushil Chordia.
This posting is provided "AS IS" with no warranties, and confers no rights.
1. My suggestion for workaround.
I'm referring to the following code in function DiscoverSpParameterSet() in Microsoft.ApplicationBlocks.Data.SqlHelperParameterCache:
Original:
...
connection.Open();
SqlCommandBuilder.DeriveParameters(cmd);
connection.Close();
if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}
SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(discoveredParameters, 0);
...
New code:
...
try
{
using( DataSet ds = SqlHelper.ExecuteDataset( connection, CommandType.Text, String.Concat( @"exec sp_procedure_params_rowset @procedure_name = N'", spName, "'" ) ) )
{
if( ds != null && ds.Tables != null && ds.Tables.Count > 0 )
{
DataTable spParams = ds.Tables[0];
if( spParams != null && spParams.Rows != null && spParams.Rows.Count > 0 )
{
cmd.Parameters.Clear();
Hashtable paramsOfSp = new Hashtable();
short i = 0;
byte hasDefault = 0;
bool hd = true;
foreach( DataRow dr in spParams.Rows )
{
hasDefault = ( byte )DbNullToDefault( dr["PARAMETER_HASDEFAULT"], ( byte )0 );
if( hasDefault == 0 )
hd = false;
paramsOfSp.Add(DbNullToDefault( dr["ORDINAL_POSITION"], i),
CreateParameterNoValue(
( String )DbNullToDefault( dr["PARAMETER_NAME"], String.Empty ),
( short )DbNullToDefault( dr["DATA_TYPE"], ( short )4 ),
( short )DbNullToDefault( dr["PARAMETER_TYPE"], ( short )1 ),
( bool )DbNullToDefault( dr["IS_NULLABLE"], false ),
( int )DbNullToDefault( dr["CHARACTER_MAXIMUM_LENGTH"], ( int )4 ),
( short )DbNullToDefault( dr["NUMERIC_PRECISION"], ( short )4 ),
( short )DbNullToDefault( dr["NUMERIC_SCALE"], ( short )0 ),
String.Empty,
hd,
DbNullToDefault( dr["PARAMETER_DEFAULT"], null ),
( String )DbNullToDefault( dr["TYPE_NAME"], String.Empty )) );
i++;
}
for( short j = 0; j< i; j++ )
{
cmd.Parameters.Add( paramsOfSp[j] );
}
}
else
return null;
}
else
return null;
}
}
catch( Exception ee )
{
System.Diagnostics.Debug.WriteLine( String.Format("{0}\n{1}", ee.Message, ee.StackTrace ) );
return null;
}
...
This relies on:
public static object DbNullToDefault(object value, object defualtReturn)
{
if((value == null) || DBNull.Value.Equals(value))
return defualtReturn;
return value;
}
Couple of remarks:
1. It may be that it won't compile! Appologies, but this is extracted from code that was modified from Microsoft.ApplicationBlocks.Data.
2. It totally relies on the structure of sp_procedure_params_rowset.
3. Any corrections-suggestions are very welcomed.
2. I opened a bug.
Gad.
