SqlCommand NewSProc(string procName)
{
SqlCommand cmd = new SqlCommand(procName, _dbConn);
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
#if EmulateDeriveParameters //see below for our
//own DeriveParameters
MySqlCmdBuilder.DeriveParameters(cmd);
#else
Open();
SqlCommandBuilder.DeriveParameters(cmd);
//SQL treats OUT params as REF params
//(thus requiring those parameters to be passed in)
//if that's what you really want, remove
//the next three lines
foreach (SqlParameter prm in cmd.Parameters)
{
if (prm.Direction == ParameterDirection.InputOutput)
{
//make param a true OUT param
prm.Direction = ParameterDirection.Output;
}
}
#endif
return(cmd);
}
string qrySProc =
"SELECT parameter_name as name"
+ ", data_type as xtype"
+ ", cast(isnull(character_maximum_length, " +
"numeric_scale) as int) as prec"
+ ", case when parameter_mode like '%out%' " +
"then 1 else 0 end as isoutparam"
+ " FROM INFORMATION_SCHEMA.PARAMETERS"
+ " WHERE specific_name = '" + cmd.CommandText + "'"
+ " ORDER BY ordinal_position";
//query SQL-server for given sproc's parameter info
DataTable dt = new DataTable();
new SqlDataAdapter(qrySProc, cmd.Connection).Fill(dt);
foreach (DataRow dr in dt.Rows)
{
SqlParameter prm = new SqlParameter(
(string)dr[0], //dr["name"]
_sqlTypeMap[(string)dr[1]], //dr["xtype"]
(int)dr[2]); //dr["prec"]