/// <summary> /// The bit that does it all. /// </summary> private void ScriptUmbraco() { if ( this.InvokeRequired ) { MethodInvoker invoker = new MethodInvoker( this.ScriptUmbraco ); this.Invoke( invoker ); return; } // Some starting variables bool scriptDb = this.checkBoxCreate.Checked; bool filePerTable = this.checkBoxSingleFile.Checked; bool setDateFormat = this.checkBoxDateFormat.Checked; string path = this.textBoxOutputDir.Text; string sourceDsn = this.textBoxSource.Text; bool errors = false; // Clear the log this.ClearLog(); // // Delete Step1,Step2,Step3 // try { File.Delete( string.Format( @"{0}\{1}", path, "Step1.sql" ) ); File.Delete( string.Format( @"{0}\{1}", path, "Step2.sql" ) ); File.Delete( string.Format( @"{0}\{1}", path, "Step3.sql" ) ); } catch ( IOException e ) { this.Log( e ); errors = true; } // This holds the entire insert statement StringBuilder insertStmt = new StringBuilder(); this.toolStripStatusLabel1.Text = "Scripting objects"; // // Script the drop/create objects if asked for // if ( scriptDb ) { try { StreamWriter writer = new StreamWriter( string.Format( @"{0}\{1}", path, "Step1.sql" ), false, Encoding.Unicode ); writer.Write(this.GetCreateObjectsSql()); writer.WriteLine( "" ); writer.WriteLine("/* CREATE VIEWS */"); writer.Write(this.GetCreateViewsSql()); writer.Close(); } catch ( IOException e ) { this.Log( e ); errors = true; } } // Return if the cancel button is pushed if ( this._cancel ) return; // Default the dateformat if ( setDateFormat ) insertStmt.AppendLine( string.Format( "SET DATEFORMAT {0}", this.textBoxDateFormat.Text ) ); try { using ( SqlConnection sourceConnection = new SqlConnection( sourceDsn ) ) { // Open the source sourceConnection.Open(); SqlCommand insertCommand = new SqlCommand(); // Loop through all tables we have for ( int i = 0; i < _tableNames.Count; i++ ) { // Return if the cancel button is pushed if ( this._cancel ) return; // // Set up the statements to use // string tableName = _tableNames[i].ToString(); string sql = string.Format( "SELECT * FROM {0}", tableName ); string identityOn = string.Format( "SET IDENTITY_INSERT [{0}] ON", tableName ); string identityOff = string.Format( "SET IDENTITY_INSERT [{0}] OFF", tableName ); // // Check for log tables etc. and ignore if set // if ( ( tableName == "umbracoLog" || tableName == "umbracoUserLogins" || tableName == "umbracoStatSession" ) && this.checkBoxIgnoreLogTable.Checked ) { continue; } this.toolStripStatusLabel1.Text = string.Format( "Getting data from {0}", tableName ); Application.DoEvents(); // // Grab column names for this table // SqlCommand command = new SqlCommand( sql, sourceConnection ); SqlDataAdapter adapter = new SqlDataAdapter( command ); DataSet ds = new DataSet(); adapter.Fill( ds ); string colNames = ""; for ( int n = 0; n < ds.Tables[0].Columns.Count; n++ ) { colNames += "[" + ds.Tables[0].Columns[n].ColumnName + "],"; } // Remove the last comma colNames = colNames.Remove( colNames.Length - 1 ); // // Work out if the table needs SET_IDENTITY on/off // bool identity = false; if ( this._identityTables.Contains( tableName ) ) identity = true; // Get the number of rows in the table, so the toolstrip // can give an indication of progress SqlCommand countCommand = new SqlCommand( string.Format( "SELECT COUNT(*) FROM {0}", tableName ), sourceConnection ); int rowCount = (int) countCommand.ExecuteScalar(); int j = 0; // Append ON if it has an identity if ( identity && rowCount > 0 ) insertStmt.AppendLine( identityOn ); // // Go through all rows // SqlDataReader reader = command.ExecuteReader(); while ( reader.Read() ) { // Return if the cancel button is pushed if ( this._cancel ) return; int cols = reader.FieldCount; StringBuilder colVals = new StringBuilder(); this.toolStripStatusLabel1.Text = string.Format( "Getting data from {0} {1}/{2}", tableName, j, rowCount ); Application.DoEvents(); // // Loop through all columns and grab their values // for ( int n = 0; n < cols; n++ ) { string comma = ","; // Remove the last comma if ( n == cols - 1 ) comma = ""; StringBuilder valBuilder = new StringBuilder(); if ( reader[n].GetType() == typeof( Boolean ) ) { if ( (bool) reader[n] ) valBuilder.Append( "1" ); else valBuilder.Append( "0" ); } else if ( reader[n] == DBNull.Value ) { valBuilder.Append( "NULL" ); } else { valBuilder.AppendFormat( "'{0}'", reader[n].ToString().Replace( "'", "''" ) ); } colVals.AppendFormat( "{0}{1}", valBuilder.ToString(), comma ); } // Add to the big statement insertStmt.AppendFormat( "INSERT INTO {0} ({1}) VALUES ({2})\r\n", tableName, colNames, colVals.ToString() ); j++; } // Append OFF if it has an identity if ( identity && rowCount > 0 ) insertStmt.AppendLine( identityOff ); // Close the reader explicitly reader.Close(); // Save to disk if ( filePerTable && insertStmt.Length > 0 ) { // Delete any existing File.Delete( string.Format( @"{0}\{1}.sql", path, tableName ) ); try { StreamWriter writer = new StreamWriter( string.Format( @"{0}\{1}.sql", path, tableName ), false, Encoding.Unicode ); writer.Write( insertStmt.ToString() ); writer.Close(); // Reset the script buffer insertStmt = new StringBuilder(); } catch ( IOException e ) { this.Log( e ); } } } } } catch ( SqlException e ) { this.Log( e ); errors = true; } if ( !errors ) { // // Save to single data file // if ( !filePerTable ) { try { StreamWriter writer = new StreamWriter(string.Format( @"{0}\{1}", path, "Step2.sql" ),false,Encoding.Unicode ); writer.Write( insertStmt.ToString() ); writer.Close(); } catch ( Exception e ) { this.Log( e ); errors = true; } } // // Script the foreign keys // if ( scriptDb ) { this.toolStripStatusLabel1.Text = "Saving data file..."; Application.DoEvents(); try { StreamWriter writer = new StreamWriter( string.Format( @"{0}\{1}", path, "Step3.sql" ), false, Encoding.Unicode ); writer.Write( this.GetCreateKeysSql() ); writer.Close(); } catch ( IOException e ) { this.Log( e ); errors = true; } } } this.buttonBackup.Text = "Backup"; this.buttonBackup.Enabled = true; this._cancel = false; // Recycle the builder insertStmt = new StringBuilder(); this.toolStripStatusLabel1.Text = "Done"; // // Show the log window if there were errors // if ( errors ) { DialogResult result = MessageBox.Show("The backup completed but with errors. It is advised that you don't run any of the scripts unless you expected the errors.\n\n Do you want to see the error log?","Error", MessageBoxButtons.YesNo, MessageBoxIcon.Warning); if ( result == DialogResult.Yes ) { FormErrorlog formLog = new FormErrorlog(this._log); formLog.Show(); } } }
/// <summary> /// The bit that does it all. /// </summary> private void ScriptUmbraco() { if (this.InvokeRequired) { MethodInvoker invoker = new MethodInvoker(this.ScriptUmbraco); this.Invoke(invoker); return; } // Some starting variables bool scriptDb = this.checkBoxCreate.Checked; bool filePerTable = this.checkBoxSingleFile.Checked; bool setDateFormat = this.checkBoxDateFormat.Checked; string path = this.textBoxOutputDir.Text; string sourceDsn = this.textBoxSource.Text; bool errors = false; // Clear the log this.ClearLog(); // // Delete Step1,Step2,Step3 // try { File.Delete(string.Format(@"{0}\{1}", path, "Step1.sql")); File.Delete(string.Format(@"{0}\{1}", path, "Step2.sql")); File.Delete(string.Format(@"{0}\{1}", path, "Step3.sql")); } catch (IOException e) { this.Log(e); errors = true; } // This holds the entire insert statement StringBuilder insertStmt = new StringBuilder(); this.toolStripStatusLabel1.Text = "Scripting objects"; // // Script the drop/create objects if asked for // if (scriptDb) { try { StreamWriter writer = new StreamWriter(string.Format(@"{0}\{1}", path, "Step1.sql"), false, Encoding.Unicode); writer.Write(this.GetCreateObjectsSql()); writer.WriteLine(""); writer.WriteLine("/* CREATE VIEWS */"); writer.Write(this.GetCreateViewsSql()); writer.Close(); } catch (IOException e) { this.Log(e); errors = true; } } // Return if the cancel button is pushed if (this._cancel) { return; } // Default the dateformat if (setDateFormat) { insertStmt.AppendLine(string.Format("SET DATEFORMAT {0}", this.textBoxDateFormat.Text)); } try { using (SqlConnection sourceConnection = new SqlConnection(sourceDsn)) { // Open the source sourceConnection.Open(); SqlCommand insertCommand = new SqlCommand(); // Loop through all tables we have for (int i = 0; i < _tableNames.Count; i++) { // Return if the cancel button is pushed if (this._cancel) { return; } // // Set up the statements to use // string tableName = _tableNames[i].ToString(); string sql = string.Format("SELECT * FROM {0}", tableName); string identityOn = string.Format("SET IDENTITY_INSERT [{0}] ON", tableName); string identityOff = string.Format("SET IDENTITY_INSERT [{0}] OFF", tableName); // // Check for log tables etc. and ignore if set // if ((tableName == "umbracoLog" || tableName == "umbracoUserLogins" || tableName == "umbracoStatSession") && this.checkBoxIgnoreLogTable.Checked) { continue; } this.toolStripStatusLabel1.Text = string.Format("Getting data from {0}", tableName); Application.DoEvents(); // // Grab column names for this table // SqlCommand command = new SqlCommand(sql, sourceConnection); SqlDataAdapter adapter = new SqlDataAdapter(command); DataSet ds = new DataSet(); adapter.Fill(ds); string colNames = ""; for (int n = 0; n < ds.Tables[0].Columns.Count; n++) { colNames += "[" + ds.Tables[0].Columns[n].ColumnName + "],"; } // Remove the last comma colNames = colNames.Remove(colNames.Length - 1); // // Work out if the table needs SET_IDENTITY on/off // bool identity = false; if (this._identityTables.Contains(tableName)) { identity = true; } // Get the number of rows in the table, so the toolstrip // can give an indication of progress SqlCommand countCommand = new SqlCommand(string.Format("SELECT COUNT(*) FROM {0}", tableName), sourceConnection); int rowCount = (int)countCommand.ExecuteScalar(); int j = 0; // Append ON if it has an identity if (identity && rowCount > 0) { insertStmt.AppendLine(identityOn); } // // Go through all rows // SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { // Return if the cancel button is pushed if (this._cancel) { return; } int cols = reader.FieldCount; StringBuilder colVals = new StringBuilder(); this.toolStripStatusLabel1.Text = string.Format("Getting data from {0} {1}/{2}", tableName, j, rowCount); Application.DoEvents(); // // Loop through all columns and grab their values // for (int n = 0; n < cols; n++) { string comma = ","; // Remove the last comma if (n == cols - 1) { comma = ""; } StringBuilder valBuilder = new StringBuilder(); if (reader[n].GetType() == typeof(Boolean)) { if ((bool)reader[n]) { valBuilder.Append("1"); } else { valBuilder.Append("0"); } } else if (reader[n] == DBNull.Value) { valBuilder.Append("NULL"); } else { valBuilder.AppendFormat("'{0}'", reader[n].ToString().Replace("'", "''")); } colVals.AppendFormat("{0}{1}", valBuilder.ToString(), comma); } // Add to the big statement insertStmt.AppendFormat("INSERT INTO {0} ({1}) VALUES ({2})\r\n", tableName, colNames, colVals.ToString()); j++; } // Append OFF if it has an identity if (identity && rowCount > 0) { insertStmt.AppendLine(identityOff); } // Close the reader explicitly reader.Close(); // Save to disk if (filePerTable && insertStmt.Length > 0) { // Delete any existing File.Delete(string.Format(@"{0}\{1}.sql", path, tableName)); try { StreamWriter writer = new StreamWriter(string.Format(@"{0}\{1}.sql", path, tableName), false, Encoding.Unicode); writer.Write(insertStmt.ToString()); writer.Close(); // Reset the script buffer insertStmt = new StringBuilder(); } catch (IOException e) { this.Log(e); } } } } } catch (SqlException e) { this.Log(e); errors = true; } if (!errors) { // // Save to single data file // if (!filePerTable) { try { StreamWriter writer = new StreamWriter(string.Format(@"{0}\{1}", path, "Step2.sql"), false, Encoding.Unicode); writer.Write(insertStmt.ToString()); writer.Close(); } catch (Exception e) { this.Log(e); errors = true; } } // // Script the foreign keys // if (scriptDb) { this.toolStripStatusLabel1.Text = "Saving data file..."; Application.DoEvents(); try { StreamWriter writer = new StreamWriter(string.Format(@"{0}\{1}", path, "Step3.sql"), false, Encoding.Unicode); writer.Write(this.GetCreateKeysSql()); writer.Close(); } catch (IOException e) { this.Log(e); errors = true; } } } this.buttonBackup.Text = "Backup"; this.buttonBackup.Enabled = true; this._cancel = false; // Recycle the builder insertStmt = new StringBuilder(); this.toolStripStatusLabel1.Text = "Done"; // // Show the log window if there were errors // if (errors) { DialogResult result = MessageBox.Show("The backup completed but with errors. It is advised that you don't run any of the scripts unless you expected the errors.\n\n Do you want to see the error log?", "Error", MessageBoxButtons.YesNo, MessageBoxIcon.Warning); if (result == DialogResult.Yes) { FormErrorlog formLog = new FormErrorlog(this._log); formLog.Show(); } } }