/// <summary>
/// Sets up stored procedures
/// </summary>
/// <param name="ConnectionString">Connection string</param>
/// <param name="Temp">Database object</param>
private static void SetupStoredProcedures(string ConnectionString, Database Temp)
{
string Command = "SELECT sys.procedures.name as NAME,OBJECT_DEFINITION(sys.procedures.object_id) as DEFINITION FROM sys.procedures";
using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
{
try
{
Helper.Open();
Helper.ExecuteReader();
while (Helper.Read())
{
string ProcedureName = (string)Helper.GetParameter("NAME", "");
string Definition = (string)Helper.GetParameter("DEFINITION", "");
Temp.AddStoredProcedure(ProcedureName, Definition);
}
}
catch { }
finally { Helper.Close(); }
}
foreach (StoredProcedure Procedure in Temp.StoredProcedures)
{
Command = "SELECT sys.systypes.name as TYPE,sys.parameters.name as NAME,sys.parameters.max_length as LENGTH,sys.parameters.default_value as [DEFAULT VALUE] FROM sys.procedures INNER JOIN sys.parameters on sys.procedures.object_id=sys.parameters.object_id INNER JOIN sys.systypes on sys.systypes.xusertype=sys.parameters.system_type_id WHERE sys.procedures.name=@ProcedureName AND (sys.systypes.xusertype <> 256)";
using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
{
try
{
Helper.Open();
Helper.AddParameter("@ProcedureName", 128, Procedure.Name);
Helper.ExecuteReader();
while (Helper.Read())
{
string Type = (string)Helper.GetParameter("TYPE", "");
string Name = (string)Helper.GetParameter("NAME", "");
int Length = int.Parse(Helper.GetParameter("LENGTH", 0).ToString());
if (Type == "nvarchar")
{
Length /= 2;
}
string Default = (string)Helper.GetParameter("DEFAULT VALUE", "");
Procedure.AddColumn <string>(Name, Type.TryTo <string, SqlDbType>().ToDbType(), Length, Default);
}
}
catch { }
finally { Helper.Close(); }
}
}
}
