/// <summary> Returns test results for the existence of stored procedure 'procedure' on 'cmd' connection's database in use.
/// <para> If 'procedure' does not exist, an attempt is made to create it from this connection's database. </para>
/// <para> Error messages are stored in .Message </para>
/// </summary>
/// <param name="cmd"></param>
/// <param name="procedure"></param>
/// <returns></returns>
public bool ProcedureExists(VEDatabase cmd, string procedure)
{
bool exists = cmd.StoredProcedureExists(procedure);
if (!exists)
{
string text = RetrieveProcedureTextString(procedure);
if (text.Length == 0)
{
message = string.Format("VEDataAdmin.ProcedureExists: Unable to create procedure '{0}' from this connection's database [{1}]", procedure, DataControl.Connection);
}
else
{
cmd.Execute(text);
if (!(exists = cmd.StoredProcedureExists(procedure)))
{
message = string.Format("VEDataAdminProcedureExists: An error occurred trying to create procedure '{0}' on {1}", procedure, cmd.DataControl.Connection);
}
}
}
return(exists);
}
public void DropProcedure(VEDatabase cmd, string procedure)
{
if (cmd.StoredProcedureExists(procedure))
{
cmd.DropProcedure(procedure);
}
}
public void DropFunction(VEDatabase cmd, string function)
{
if (cmd.StoredFunctionExists(function))
{
cmd.DropFunction(function);
}
}
/// <summary> Retrieves executable 'CREATE TABLE..' text for specified source and target tables. 'sourceTable' is accessible by 'sourceCmd'
/// </summary>
/// <param name="sourceCmd"></param>
/// <param name="sourceTable"></param>
/// <param name="targetTable"></param>
/// <returns></returns>
public string GetSchemaDefinition(VEDatabase sourceCmd, string sourceTable, string targetTable)
{
string text = "",
alias = Alias,
errorMsg = "VEAdmin: Unable to generate the requisite definition for the following reason - \r\n";
if (!ProcedureExists(sourceCmd, alias + getTableSchema))
{
message = errorMsg + message;
}
else
{
VEDataParameters parameters = Parameters(sourceCmd, alias + getTableDefinition);
if (parameters == null)
{
message = errorMsg + message;
}
else
{
int s1 = sourceTable.IndexOf('.'),
s2 = sourceTable.LastIndexOf('.'),
t1 = targetTable.IndexOf('.'),
t2 = targetTable.LastIndexOf('.');
parameters["@table"].Value = s1 > 0 && s1 < s2?sourceTable.Substring(s2 + 1) : sourceTable.Substring(s1 + 1);
parameters["@sourceOwner"].Value = s1 > 0 && s1 < s2?sourceTable.Substring(s1 + 1, s2 - s1 - 1) : sourceTable.Substring(0, s1);
parameters["@targetOwner"].Value = t1 > 0 && t1 < t2?targetTable.Substring(t1 + 1, t2 - t1 - 1) : targetTable.Substring(0, t1);
if (sourceCmd.ExecuteStoredProcedure(parameters) >= 0)
{
text = (string)parameters["@tableDefinition"].Value;
}
}
}
return(text);
}
/// <summary> Returns DataTable 'veTableDependencies' generated by stored procedure '[.AdminAccount].GetTableDependcies' containing schema table dependencies sequenced by increasing levels of dependency.
/// </summary>
/// <param name="cmd"></param>
/// <param name="schema"></param>
/// <returns></returns>
public DataTable GetTableDependencies(VEDatabase cmd, string schema)
{
DataTable copyTableDependencies = null;
string alias = Alias,
procedure = alias + getTableDependencies;
VEDataParameters parameters = Parameters(cmd, procedure);
if (parameters == null || parameters.Count == 0)
{
message = "VEDataAdmin.GetTableDependencies: Unable to create the dependencies table because of the following error -\r\n" + message + "\r\n";
}
else
{
parameters["@owner"].Value = schema;
if (cmd.ExecuteStoredProcedure(parameters) < 0 || parameters["@table"] == null)
{
message = string.Format("VEDataAdmin.GetTableDependencies: An error occurred executing procedure '{0}'\r\n{1}", procedure, parameters.ToString());
}
else
{
string tablename = (string)parameters["@table"].Value;
if (cmd.Fill(tablename, string.Format("SELECT * FROM {0} ORDER BY Sequence, Name", tablename)) >= 0)
{
copyTableDependencies = cmd[tablename];
}
else
{
message = string.Format("VEDataAdmin.GetTableDependencies: An error occurred retrieving dependencies from '{0}'", tablename);
}
}
}
return(copyTableDependencies);
}
/// <summary> Returns the procedure's parameters after creating the stored procedure on 'cmd' if needed
/// </summary>
/// <param name="cmd"></param>
/// <param name="procedure"></param>
/// <returns></returns>
public VEDataParameters Parameters(VEDatabase cmd, string procedure)
{
return(ProcedureExists(cmd, procedure) ? cmd.RetrieveProcedureParameters(procedure) : null);
}
/// <summary>Sets the target VEDatabase object</summary>
/// <param name="cmd"></param>
public VEDatabase SetTarget(VEDatabase cmd)
{
return(TargetCmd = cmd);
}
/// <summary>Sets a returns the source VEDatabase object 'cmd' </summary>
/// <param name="cmd"></param>
public VEDatabase SetSource(VEDatabase cmd)
{
return(SourceCmd = cmd);
}
/// <summary> Retrieves executable 'CREATE TABLE..' text for the specified 'sourceTable' accessible by 'sourceCmd'
/// </summary>
/// <param name="sourceCmd"></param>
/// <param name="sourceTable"></param>
/// <returns></returns>
public string GetSchemaDefinition(VEDatabase sourceCmd, string sourceTable)
{
return(GetSchemaDefinition(sourceCmd, sourceTable, sourceTable));
}
