/// <summary>
/// Creates a new instance of the StoredProcedureInfo class.
/// This constructor is private; call the static GetInfo() method instead.
/// </summary>
/// <param name="connectionName">Name of the database connection.</param>
/// <param name="schema">Name of the database schema.</param>
/// <param name="procedureName">Name of the stored procedure.</param>
private StoredProcedureInfo(string connectionName, string schema, string procedureName)
{
this.ConnectionName = connectionName;
this.Schema = schema;
this.ProcedureName = procedureName;
// query the database for the procedure's parameters;
// if the procedure doesn't exist, zero parameters will be found, successfully (no error)
var parametersQuery = new StoredProcedureQuery("SelectStoredProcedureParameters", connectionName);
parametersQuery.Parameters.Add("Schema", this.Schema);
parametersQuery.Parameters.Add("ProcedureName", this.ProcedureName);
DataSet results = parametersQuery.ReturnAllTables();
var parameters = results.Tables[0]; // no rows if the procedure has no parameters
var columnNames = results.Tables[1]; // no rows if the procedure has no TVPs; all TVPs' columns otherwise
foreach (DataRow parameter in parameters.Rows)
{
var param = new ParameterInfo
{
Name = parameter["ParameterName"] as string,
TypeString = parameter["DataType"] as string,
UserDefinedTypeName = parameter["UserDefinedTypeName"] as string,
MaxLength = parameter["MaxLength"] as int?
};
// TODO: switch this logic around when we internationalize the db to use nvarchar columns
if (param.TypeString == "varchar")
param.Type = SqlDbType.VarChar;
else if (param.TypeString == "char")
param.Type = SqlDbType.Char;
else if (param.TypeString == "nvarchar")
param.Type = SqlDbType.NVarChar;
else if (param.TypeString == "nchar")
param.Type = SqlDbType.NChar;
else if (param.TypeString == "table type")
{
param.Type = SqlDbType.Structured;
// get the table type's column names
param.ColumnNames = columnNames.Select("ParameterName = '" + param.Name + "'")
.Select(dr => dr["ColumnName"] as string).ToArray();
}
this.Parameters.Add(param);
}
}
/// <summary>
/// Creates a StoredProcedureQuery object for the given procedure and connection,
/// automatically parameters from the Arguments dictionary.
/// </summary>
/// <param name="procedureName">Name of the stored procedure to execute.</param>
/// <param name="connectionName">Name of the connection to use.</param>
protected StoredProcedureQuery CreateStoredProcedureQuery(string procedureName, string connectionName)
{
var query = new StoredProcedureQuery(procedureName, connectionName);
// Initialize query parameters by trying to pull all parameters from the arguments passed
int cacheTimeoutSeconds = Config.GetInt("API.CacheStoredProcedureInfoForSeconds", 0);
var sprocInfo = StoredProcedureInfo.GetInfo(connectionName, procedureName, cacheTimeoutSeconds);
foreach (StoredProcedureInfo.ParameterInfo param in sprocInfo.Parameters)
{
if (!this.Arguments.ContainsKey(param.Name)) continue;
// Get the parameter value
object paramValue = this.Arguments[param.Name];
if (param.Type == SqlDbType.Structured && !string.IsNullOrEmpty(param.UserDefinedTypeName))
{
// It's a table valued parameter (TVPs must be defined as user-defined types);
// create a new DataTable object from the value,
// which is expected to be an array of IDictionary<string, object> objects
var dt = new DataTable(param.Name);
foreach (var columnName in param.ColumnNames)
dt.Columns.Add(columnName);
if (paramValue != null && !(paramValue is string))
{
var isIdTableType = (param.UserDefinedTypeName == "IdTableType");
foreach (var item in paramValue as object[])
{
var dr = dt.NewRow();
if (isIdTableType)
{
// parameter is an IdTableType
dr["Id"] = item;
}
else
{
// parse a normal object
var itemDict = item as IDictionary<string, object>;
foreach (var columnName in param.ColumnNames)
{
if (itemDict.ContainsKey(columnName))
dr[columnName] = itemDict[columnName];
}
}
dt.Rows.Add(dr);
}
}
var sqlParam = new SqlParameter(param.Name, param.Type.Value)
{
Value = dt,
TypeName = param.UserDefinedTypeName
};
query.Parameters.Add(param.Name, sqlParam);
}
else if (param.Type.HasValue && param.MaxLength.HasValue)
{
// It's a type we handle specially, rather than letting ADO infer the SQL type from the .NET object;
// see http://geekswithblogs.net/Rhames/archive/2008/10/29/why-you-should-always-specify-the-sqldbtype-for-an-ado.net.aspx
var sqlParam = new SqlParameter(param.Name, param.Type.Value, param.MaxLength.Value)
{
Value = paramValue ?? DBNull.Value
};
query.Parameters.Add(param.Name, sqlParam);
}
else
{
// It's a type for which we let ADO infer the SQL type.
query.Parameters.Add(param.Name, paramValue);
}
}
return query;
}
