/// <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> Executes a stored procedure to copy a table from one database.schema to another database.schema on the same server. /// <para>Source and target objects must have been initialized using .SetSource(...) and .SetTarget(...)</para> /// <para>Sets 'copied' is set to the number of records copied during this iteration and</para> /// <para>'targetTotal' to the total count on the target table at the end of this iteration</para> /// <para>Note: This operation may timeout. Call .SQLHandler() before calling this method and check .HasErrors to continue the operation. </para> /// </summary> /// <param name="table"></param> /// <param name="copied"></param> /// <param name="targetTotal"></param> /// <returns></returns> bool CopyServerTables(string table, ref int copied, ref int targetTotal) { VEObjectName veName = new VEObjectName(table); bool okay = SourceDatabase != TargetDatabase || SourceSchema != TargetSchema; if (!okay) { message = string.Format("VEAdmin..CopyServerTables: Source and Target tables must differ - {0}.{1}.{2}", SourceDatabase, SourceSchema, veName.TableName); } else { try { VEDataParameters parameters = CopyTableParameters; if (okay = parameters != null && parameters.Count >= 5) { parameters["@sourceDatabase"].Value = SourceDatabase; parameters["@sourceOwner"].Value = SourceSchema; parameters["@targetDatabase"].Value = TargetDatabase; parameters["@targetOwner"].Value = TargetSchema; parameters["@tableName"].Value = veName.TableName; if (targetCmd.ExecuteStoredProcedure(parameters) < 0) { SQLHandler.Message = (string)parameters["@message"].Value; } copied = (int)parameters["@copied"].Value; targetTotal = (int)parameters["@targetTotal"].Value; } else { message += string.Format("\r\nVEDatabaseAdmin.CopyServerTables: An error occurred retrieving parameters for procedure '{0}' on {1}", CopyTableProcedure, targetCmd.DataControl.Connection); copied = targetTotal = -1; } } catch (Exception e) { // // Don't report timeout exceptions // if (e.Message.ToLower().IndexOf("timeout") < 0) { Error(string.Format("VEDataAdmin.CopyServerTables: {0}Table: Trying to Copy a Table by Stored Procedure - [{1}].{2}\r\n", copyModifier, targetObject.ServerID, table), e); copyLoop = 999; copied = -999; } } } if (!okay) { Error(SQLHandler.Errors); } return(okay); }