int CopyServerTables(string sourceTable, string targetTable) { int copyTableCount = 0, copyTableTotal = 0, copied = 0, copyRecordCount = SourceCmd.TableCount(sourceTable); int deleted = targetCmd.ModifyTable("DELETE " + targetTable); for (copyLoop = 0; copyLoop < 100 && !Abort && !SQLHandler.HasErrors && copyTableCount >= 0 && (copied += copyTableCount) < copyRecordCount;) { int targetCount = targetCmd.TableCount(targetTable); Status(string.Format("SP Copying {0,8:n0} Records to {1} {2}", copyRecordCount, targetTable, ++copyLoop > 1 ? string.Format("(Pass {0} - {1:n0} Remaining)", copyLoop, copyRecordCount - targetCount) : "")); if (copyLoop > 1) { copied = targetCount; // A timeout must have occurred. Sync up copied total to actual... } SQLHandler.Clear(); CopyServerTables(targetTable, ref copyTableCount, ref copyTableTotal); if (SQLHandler.HasErrors) { Memo("Error", string.Format("Copy {0}Table: {1, 7} Records to {2} WERE NOT COPIED. See errors below -\r\n\r\n{3}", copyModifier, "ERROR", targetTable, SQLHandler.Errors)); } } Status(""); return(copyTableCount < 0 ? copyTableCount : copyTableTotal); }
int CopyObject(string text, string type) { targetObject.ObjectName = sourceObject.ObjectName; Status(string.Format("Copying {0}: {1}", text, targetObject.SchemaObject)); SQLHandler.Clear(); int size = CopyObject(type); if (SQLHandler.HasErrors) { Memo("Error", string.Format("Copy Schema {0}s: {1, 7} {2} WAS NOT Copied because of the following SQL Errors\r\n\r\n{3}", text, "ERROR", targetObject.ServerIDObjectName, SQLHandler.Errors)); } else { Memo("Memo", string.Format("Copy Schema {0}s: {1, 7:n0} Bytes {2}", text, size, targetObject.ObjectName)); } return(size); }
/// <summary> Retrieves and Executes the script to restore table records /// <para> . Retrieves the script from the current position in the zip file </para> /// <para> . Parses and executes the script, record by record </para> /// <para> . If .restoreIdentity is set, prepends and appends each inserted record text with the necessary SET_IDENTITY_INSERT statements</para> /// Returns the number of records restored /// </summary> /// <returns></returns> int RestoreTableData() { int count = 0, tableCount = 0; string zipSchema = zipFile.Schema + ".", targetSchema = TargetSchema + ".", tablename = TargetSchema + "." + zipFile.Name, process = "Restore User Tables"; long position = zipFile.Position; string[] lines = zipFile.InflateLines(); if (lines == null) { Memo("Error", string.Format("{0}: {1, 7} Unable to restore table data - {2}\r\n{3,23} Unable to retrieve data content\r\n", process, "ERROR", tablename, "")); } else { SQLHandler.Clear(); try { string text, columnNames = lines[0]; // column names in (ColumnName1,...,) format string identityInsertOn = restoreIdentity ? "SET IDENTITY_INSERT " + tablename + " ON " : "", identityInsertOff = restoreIdentity && identityInsertOn.Length > 0 ? "SET IDENTITY_INSERT " + tablename + " OFF " : ""; Status("Restoring Tables: " + tablename); for (int i = 1; i < lines.Length && !Abort;) { Status("Restoring Tables: " + tablename + " - " + (lines.Length - i).ToString()); string cmd = ""; for (; i < lines.Length && cmd.Length < 200000; i++) { if ((text = lines[i]).Length > 0 && text.IndexOf("System.") < 0) { text.Replace(zipSchema, targetSchema); int ifirst = text.IndexOf('\''); // Look for internal single quotes if (ifirst >= 0) { parseQuotes = false; for (int paren = RestoreParse(text); paren > 0 && ++i < lines.Length && !Abort;) { string txt = lines[i]; paren += RestoreParse(txt); text += txt; } } cmd += string.Format("{0} INSERT INTO {1} {2} VALUES {3} {4} \r\n", identityInsertOn, tablename, columnNames, text, identityInsertOff); count++; } } if (cmd.Length > 0) { targetCmd.ModifyTable(cmd); if (cmd.Length < minCommand) { minCommand = cmd.Length; } if (cmd.Length > maxCommand) { maxCommand = cmd.Length; } if (cmd.Length < 1025 && cmd.Length > maxCommand1024) { maxCommand1024 = cmd.Length; } if (cmd.Length > 1024 && cmd.Length < 2049 && cmd.Length > maxCommand2048) { maxCommand2048 = cmd.Length; } Thread.Sleep(25); // Sleeping avoids unexplained errors. Probably not handling asynchronous server executions and repsonses properly } } tableCount = targetCmd.TableCount(tablename); } finally { Status(""); Memo(SQLHandler.HasErrors ? "Error" : "Totals", string.Format("{0}{1}: {2, 7:n0} {3}Records restored to {4}{5}", SQLHandler.HasErrors ? "\r\n" : "", process, tableCount, tableCount != count ? "Of " + count.ToString() + " " : "", tablename, SQLHandler.HasErrors ? " - With Errors\r\n" + SQLHandler.Errors : "")); } } return(count); }
int CopyTables(DataTable dependencies) { Memo("Memo", "Copy Schema Tables: Deleting table content...\r\n"); int count = copyTablesCount = 0; // // Delete table content in reverse order of dependency // for (int i = dependencies.Rows.Count; i-- > 0;) { targetTable.TableName = (string)dependencies.Rows[i]["Name"]; string databaseOwnerTable = targetTable.DatabaseSchemaTable; if (IsValidTablename(targetTable.TableName) && !BypassTables.Contains(targetTable.TableName) && targetCmd.TableExists(databaseOwnerTable)) //.Name)) { Status("Deleting content: " + databaseOwnerTable); targetCmd.Execute("DELETE " + databaseOwnerTable); if (SQLHandler.HasErrors) { Memo("Memo", "Copy Schema Tables: ERROR Deleting " + databaseOwnerTable + "\r\n" + SQLHandler.Errors); copyTablesCount--; } } } if (copyTablesCount < 0) { return(0); } // // Copy tables in ascending order of dependency // for (int i = 0; i < dependencies.Rows.Count && !Abort; i++) { sourceTable.TableName = (string)dependencies.Rows[i]["Name"]; targetTable.TableName = sourceTable.TableName; if (!IsValidTablename(sourceTable.TableName) || BypassTables.Contains(sourceTable.TableName)) // BypassTables used for testing purposes { continue; } SQLHandler.Clear(); do { SQLHandler.TryAgain = false; bool okay = CopyTable(CopySourceTable, CopyTargetTable, "Schema "); if (Abort || SQLHandler.HasErrors) { break; } if (!SQLHandler.TryAgain && okay) { copyTablesCount++; count++; } }while (SQLHandler.TryAgain); } return(count); }
/// <summary>Restores table content for the table specified in restoreObject (.FileName = '[tablename].dat'); /// Restores content found in the data file ([selected path]\Tables\Data\[tablename].dat) and; /// Returns the number of records restored or -1 if a problem was encountered /// </summary> /// <param name="dataPath"></param> /// <returns></returns> int RestoreTableData(string dataPath) { int count = -1; string dataName = restoreObject.TableFileName, tablename = restoreObject.TableName; if (!File.Exists(dataPath)) { Memo("Memo", string.Format("Restore User Tables: {0, 7} Unable to restore table data - {1}" + "\r\n{23, 7} Data content file not found - {3}\r\n", "ERROR", tablename, "", dataPath)); } else { SQLHandler.Clear(); StreamReader dataFile = File.OpenText(dataPath); try { string text, columns = dataFile.ReadLine(); // column types in (DataType1...) format string identityInsertOn = restoreIdentity ? "SET IDENTITY_INSERT " + tablename + " ON " : "", identityInsertOff = restoreIdentity && identityInsertOn.Length > 0 ? "SET IDENTITY_INSERT " + tablename + " OFF " : ""; Status("Restoring " + tablename); for (count = 0; !dataFile.EndOfStream && (text = dataFile.ReadLine()).Length > 0 && !Abort;) { if (text.IndexOf("System.") < 0) { int ifirst = text.IndexOf('\''), ilast = text.LastIndexOf('\''); if (ifirst >= 0) { for (int paren = RestoreParse(text); paren > 0 && !dataFile.EndOfStream && !Abort;) { string txt = dataFile.ReadLine(); paren += RestoreParse(txt); text += txt; } } targetCmd.ModifyTable(string.Format("{0} INSERT INTO {1} {2} VALUES {3} {4}", identityInsertOn, tablename, columns, text, identityInsertOff)); count++; } } int tableCount = targetCmd.TableCount(restoreObject.TableName); Status(""); Memo("Memo", string.Format("{4}Restore User Tables: {0, 7:n0} {1}Records restored to {2}{3}", tableCount, tableCount != count ? "Of " + count.ToString() + " " : "", restoreObject.TableName, SQLHandler.HasErrors ? " - With Errors\r\n" + SQLHandler.Errors : "", SQLHandler.HasErrors ? "\r\n" : "")); } finally { if (dataFile != null) { dataFile.Close(); } } } return(count); }