//This is the core bread and butter for creating a sql request dynamically
internal static void CompleteSQLRequest(this SqlCommand Command, DynamicDBRequest Request, ref SprocSchema Schema)
{
//We have a cached schema to work with
if (Schema != null && (Schema.InputParamsList.Count != 0 || Schema.OutputParamsList.Count != 0))
{
foreach (SprocParam param in Schema.InputParamsList)
{
SqlParameter sqlparam = new SqlParameter()
{
ParameterName = param.ParameterName, Value = Request.GetDBParameterValue(param.ParameterName), Direction = param.Direction, SqlDbType = param.Type
};
if (param.Size != 0)
{
sqlparam.Size = param.Size;
}
Command.Parameters.Add(sqlparam);
}
if (Schema.ReturnValue != null)
{
SqlParameter sqlparam = new SqlParameter()
{
ParameterName = Schema.ReturnValue.ParameterName, Direction = Schema.ReturnValue.Direction, SqlDbType = Schema.ReturnValue.Type
};
if (Schema.ReturnValue.Size != 0)
{
sqlparam.Size = Schema.ReturnValue.Size;
}
Command.Parameters.Add(sqlparam);
}
foreach (SprocParam param in Schema.OutputParamsList)
{
SqlParameter sqlparam = new SqlParameter()
{
ParameterName = param.ParameterName, Size = param.Size, Direction = param.Direction, SqlDbType = param.Type
};
if (param.Size != 0)
{
sqlparam.Size = param.Size;
}
Command.Parameters.Add(sqlparam);
}
}
else //Get the definition and build the cache from the DB
{
Schema = new SprocSchema();
SqlCommandBuilder.DeriveParameters(Command);
for (int i = 0; i < Command.Parameters.Count; i++)
{
SqlParameter p = Command.Parameters[i];
if (p.Direction == System.Data.ParameterDirection.Input)
{
SprocParam inparam = new SprocParam {
ParameterName = p.ParameterName, PropertyName = p.ParameterName.Replace("@", ""), Size = p.Size, Type = p.SqlDbType, Direction = p.Direction
};
object ParamValue = Request.GetDBParameterValue(p.ParameterName);
if (ParamValue != null)
{
p.Value = ParamValue;
}
Schema.InputParamsList.Add(inparam);
}
else
{
if (p.Direction == System.Data.ParameterDirection.InputOutput)
{
p.Direction = System.Data.ParameterDirection.Output;
}
if (p.Direction == ParameterDirection.ReturnValue)
{
Schema.ReturnValue = new SprocParam {
ParameterName = p.ParameterName, PropertyName = p.ParameterName.Replace("@", ""), Size = p.Size, Type = p.SqlDbType, Direction = p.Direction
}
}
;
else
{
Schema.OutputParamsList.Add(new SprocParam {
ParameterName = p.ParameterName, PropertyName = p.ParameterName.Replace("@", ""), Size = p.Size, Type = p.SqlDbType, Direction = p.Direction
});
}
}
}
}
}
