// known difference: when getting the parameters for a sproc, the
// return value gets marked as a return value but for a sql stmt
// the return value gets marked as an output parameter.
static private OleDbParameter[] DeriveParametersFromStoredProcedure(OleDbConnection connection, OleDbCommand command)
{
OleDbParameter[] plist = new OleDbParameter[0];
int support = 0;
if (connection.SupportSchemaRowset(OleDbSchemaGuid.Procedure_Parameters, out support))
{
Object[] parsed = ADP.ParseProcedureName(command.CommandText); // MDAC 70930
Object[] restrictions = new object[4];
object value;
// Parse returns 4 part array:
// 0) Server
// 1) Catalog
// 2) Schema
// 3) ProcedureName
// Restrictions array which is passed to OleDb API expects:
// 0) Catalog
// 1) Schema
// 2) ProcedureName
// 3) ParameterName (leave null)
// Copy from Parse format to OleDb API format
Array.Copy(parsed, 1, restrictions, 0, 3);
//if (cmdConnection.IsServer_msdaora) {
// restrictions[1] = Convert.ToString(cmdConnection.UserId).ToUpper(CultureInfo.InvariantCulture);
//}
DataTable table = connection.GetSchemaRowset(OleDbSchemaGuid.Procedure_Parameters, restrictions);
if (null != table)
{
DataColumnCollection columns = table.Columns;
DataColumn parameterName = null;
DataColumn parameterDirection = null;
DataColumn dataType = null;
DataColumn maxLen = null;
DataColumn numericPrecision = null;
DataColumn numericScale = null;
DataColumn backendtype = null;
int index = columns.IndexOf(ODB.PARAMETER_NAME);
if (-1 != index)
{
parameterName = columns[index];
}
index = columns.IndexOf(ODB.PARAMETER_TYPE);
if (-1 != index)
{
parameterDirection = columns[index];
}
index = columns.IndexOf(ODB.DATA_TYPE);
if (-1 != index)
{
dataType = columns[index];
}
index = columns.IndexOf(ODB.CHARACTER_MAXIMUM_LENGTH);
if (-1 != index)
{
maxLen = columns[index];
}
index = columns.IndexOf(ODB.NUMERIC_PRECISION);
if (-1 != index)
{
numericPrecision = columns[index];
}
index = columns.IndexOf(ODB.NUMERIC_SCALE);
if (-1 != index)
{
numericScale = columns[index];
}
index = columns.IndexOf(ODB.TYPE_NAME); // MDAC 72315
if (-1 != index)
{
backendtype = columns[index];
}
DataRow[] dataRows = table.Select(null, ODB.ORDINAL_POSITION_ASC, DataViewRowState.CurrentRows); // MDAC 70928
int rowCount = dataRows.Length;
plist = new OleDbParameter[rowCount];
for (index = 0; index < rowCount; ++index)
{
DataRow dataRow = dataRows[index];
OleDbParameter parameter = new OleDbParameter();
if ((null != parameterName) && !dataRow.IsNull(parameterName, DataRowVersion.Default))
{
// $CONSIDER - not trimming the @ from the beginning but to left the designer do that
parameter.ParameterName = Convert.ToString(dataRow[parameterName, DataRowVersion.Default]).TrimStart(new char[] { '@', ' ', ':' });
}
if ((null != parameterDirection) && !dataRow.IsNull(parameterDirection, DataRowVersion.Default))
{
parameter.Direction = ConvertToParameterDirection(Convert.ToInt16(dataRow[parameterDirection, DataRowVersion.Default]));
}
if ((null != dataType) && !dataRow.IsNull(dataType, DataRowVersion.Default))
{
// need to ping FromDBType, otherwise WChar->WChar when the user really wants VarWChar
parameter.OleDbType = NativeDBType.FromDBType(Convert.ToInt16(dataRow[dataType, DataRowVersion.Default]), false, false).enumOleDbType;
}
if ((null != maxLen) && !dataRow.IsNull(maxLen, DataRowVersion.Default))
{
parameter.Size = Convert.ToInt32(dataRow[maxLen, DataRowVersion.Default]);
}
switch (parameter.OleDbType)
{
case OleDbType.Decimal:
case OleDbType.Numeric:
case OleDbType.VarNumeric:
if ((null != numericPrecision) && !dataRow.IsNull(numericPrecision, DataRowVersion.Default))
{
// @devnote: unguarded cast from Int16 to Byte
parameter.Precision = (Byte)Convert.ToInt16(dataRow[numericPrecision, DataRowVersion.Default]);
}
if ((null != numericScale) && !dataRow.IsNull(numericScale, DataRowVersion.Default))
{
// @devnote: unguarded cast from Int16 to Byte
parameter.Scale = (Byte)Convert.ToInt16(dataRow[numericScale, DataRowVersion.Default]);
}
break;
case OleDbType.VarBinary: // MDAC 72315
case OleDbType.VarChar:
case OleDbType.VarWChar:
value = dataRow[backendtype, DataRowVersion.Default];
if (value is string)
{
string backendtypename = ((string)value).ToLower(CultureInfo.InvariantCulture);
switch (backendtypename)
{
case "binary":
parameter.OleDbType = OleDbType.Binary;
break;
//case "varbinary":
// parameter.OleDbType = OleDbType.VarBinary;
// break;
case "image":
parameter.OleDbType = OleDbType.LongVarBinary;
break;
case "char":
parameter.OleDbType = OleDbType.Char;
break;
//case "varchar":
//case "varchar2":
// parameter.OleDbType = OleDbType.VarChar;
// break;
case "text":
parameter.OleDbType = OleDbType.LongVarChar;
break;
case "nchar":
parameter.OleDbType = OleDbType.WChar;
break;
//case "nvarchar":
// parameter.OleDbType = OleDbType.VarWChar;
case "ntext":
parameter.OleDbType = OleDbType.LongVarWChar;
break;
}
}
break;
}
//if (AdapterSwitches.OleDbSql.TraceVerbose) {
// ADP.Trace_Parameter("StoredProcedure", parameter);
//}
plist[index] = parameter;
}
}
if ((0 == plist.Length) && (connection.SupportSchemaRowset(OleDbSchemaGuid.Procedures, out support)))
{
restrictions = new Object[4] {
null, null, command.CommandText, null
};
table = connection.GetSchemaRowset(OleDbSchemaGuid.Procedures, restrictions);
if (0 == table.Rows.Count)
{
throw ADP.NoStoredProcedureExists(command.CommandText);
}
}
}
else if (connection.SupportSchemaRowset(OleDbSchemaGuid.Procedures, out support))
{
Object[] restrictions = new Object[4] {
null, null, command.CommandText, null
};
DataTable table = connection.GetSchemaRowset(OleDbSchemaGuid.Procedures, restrictions);
if (0 == table.Rows.Count)
{
throw ODB.NoStoredProcedureExists(command.CommandText);
}
// we don't ever expect a procedure with 0 parameters, they should have at least a return value
throw ODB.NoProviderSupportForSProcResetParameters(connection.Provider); // MDAC 71968
}
else
{
throw ODB.NoProviderSupportForSProcResetParameters(connection.Provider); // MDAC 70918
}
return(plist);
}
