/// <summary>
/// This is the main method that does the copying. It calls the SQLLaptop class
/// to do the inserting and the return result is the rows affected by the call.
/// If there are more than 0 rows, it will let the user know how many rows were affect.
/// If not it will display the error and a tip
/// </summary>
/// <returns></returns>
private bool CopyContent()
{
bool copyStatus = false;
DataTable dataTable1 = new DataTable();
DataTable dataTable2 = new DataTable();
int rowsAffected = SQLLaptop.InsertInto(connectionString1Database,
selectedCombo1Table, selectedCombo2Table, selectedCombo1Database, selectedCombo2Database);
if (rowsAffected >= 1)
{
lbl_Error_Copy.Show();
LabelChanger.ChangeColor("Green", lbl_Error_Copy);
LabelChanger.ChangeText("Successfully Copied. " + rowsAffected + " rows have been affected.", lbl_Error_Copy);
}
else
{
// Clear the new table that didnt work
lbl_Error_Copy.Show();
LabelChanger.ChangeColor("Red", lbl_Error_Copy);
LabelChanger.ChangeText("Error when copying the tables. Double check your entered schema name and table. (eg. dbo.test)", lbl_Error_Copy);
cb_Table_2.SelectedIndex = -1;
}
return(copyStatus);
}
/// <summary>
/// This is the sign in method that will be used to handle to logic of the two different
/// sign in buttons. We will pass the users number (1 = left side, 2 = right side) to the
/// method so that the appropriate fields will be used and changed. We are creating a
/// generic method to handle all the logic because we will have two different sign in forms
/// that will do the exact same thing but with different pieces. Why repeat code, right?
/// </summary>
/// <param name="UserNumber">Which User is signing in (1 = Left, 2 = Right)</param>
/// <returns>Validation</returns>
private string SignInChecker(int UserNumber)
{
string connectionString = "Failed";
// These fields will be linked to the specific user. User 1 is the left side of the
// windows form where user 2 is the right side.
int userNumber = UserNumber;
if (UserNumber == 2 || UserNumber == 1)
{
// Send to "Check Credentials method and return the status to see if it was valid
if (userNumber == 1)
{
connectionString = SQLLaptop.CreateConnectionString(cb_Provider_1.Text, txt_User_1.Text.ToString(), txt_Pass_1.Text.ToString(), txt_DataSource_1.Text.ToString());
}
else
{
connectionString = SQLLaptop.CreateConnectionString(cb_Provider_2.Text, txt_User_2.Text.ToString(), txt_Pass_2.Text.ToString(), txt_DataSource_2.Text.ToString());
}
if (!SQLLaptop.CheckCredential(connectionString))
{
connectionString = "Failed";
}
}
else
{
connectionString = "Failed";
}
return(connectionString);
}
/// <summary>
/// This method handles the event that the sign in button on the left side is pressed.
/// We will check to see if the user can sign in. If they can, lets notify them that
/// their connection was successful and load the combobox with the database list on the left.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_Signin_1_Click(object sender, EventArgs e)
{
// Check that user 1 can successfully connect. If he can, lets prompt the result
lbl_Error_User_1.Show();
string connectionString = SignInChecker(USER_ONE);
if (connectionString != "Failed")
{
LabelChanger.ChangeText("Successfully Logged in.", lbl_Error_User_1);
LabelChanger.ChangeColor("Green", lbl_Error_User_1);
SQLLaptop.LoadSchemaList(connectionString, cb_DataBase_1);
connectionString1 = connectionString;
if (assignmentRequirement)
{
connectionString2 = connectionString1;
SQLLaptop.LoadSchemaList(connectionString, cb_DataBase_2);
}
}
else
{
LabelChanger.ChangeText("Invalid Credentials.", lbl_Error_User_1);
LabelChanger.ChangeColor("Red", lbl_Error_User_1);
connectionString1 = "Failed";
}
}
/// <summary>
/// Event handler for selection changes in the Database combo boxes on both the left
/// and right side. Once a selection has been made or chosen for the first time,
/// we will find out the selection and append it to the end of the old connection string.
/// From there, we will set the initial catalog to our chosen database. Then we will
/// load the contents of the table combo boxes
/// </summary>
/// <param name="sender">The combo box that triggered the event</param>
/// <param name="e"></param>
private void Database_ComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
ComboBox combo = (ComboBox)sender;
if (sender == cb_DataBase_1)
{
selectedCombo1Database = (string)combo.SelectedItem;
selectedCombo1Table = null;
lbl_Error_Copy.Hide();
cb_Table_1.Items.Clear();
// append the old string with the database and store into the global string
connectionString1Database = connectionString1 + ";Initial Catalog=" + selectedCombo1Database;
SQLLaptop.LoadTableList(connectionString1Database, cb_Table_1);
if (assignmentRequirement)
{
connectionString2Database = connectionString1Database;
}
}
else if (sender == cb_DataBase_2)
{
selectedCombo2Database = (string)combo.SelectedItem;
selectedCombo2Table = null;
cb_Table_2.Items.Clear();
// append the old string with the database and store into the global string
connectionString2Database = connectionString2 + ";Initial Catalog=" + selectedCombo2Database;
// Add the first entry as "New Table"
if (!assignmentRequirement)
{
cb_Table_2.Items.Add("New Table...");
}
else if (assignmentRequirement && (connectionString1Database == connectionString2Database))
{
cb_Table_2.Items.Add("New Table...");
}
SQLLaptop.LoadTableList(connectionString2Database, cb_Table_2);
}
}
/// <summary>
/// This method handles the assignment requirement checkbox. If the requirement button is toggled on or
/// off, it will show/hide the correct information.
/// </summary>
private void HandleAssignmentRequirements()
{
// If we are following the assignment requirements, we need to disable a few buttons
// text fields, and data that is linked to those
// Otherwise lets duplicate all info
if (assignmentRequirement == true)
{
HideSecondUserLogin();
btn_Second_Account.Hide();
lbl_Or.Hide();
btn_Copy.Hide();
lbl_Error_User_2.Hide();
cb_DataBase_2.Items.Clear();
cb_Table_2.Items.Clear();
connectionString2 = connectionString1;
connectionString2Database = connectionString1Database;
selectedCombo2Database = selectedCombo1Database;
selectedCombo2Table = selectedCombo1Table;
lbl_Error_Copy.Hide();
if (connectionString2 != null && connectionString2Database != null)
{
SQLLaptop.LoadSchemaList(connectionString2, cb_DataBase_2);
SQLLaptop.LoadSchemaList(connectionString2Database, cb_Table_2);
}
}
else
{
btn_Second_Account.Show();
lbl_Or.Show();
btn_Copy.Show();
lbl_Error_Copy.Hide();
connectionString2 = null;
connectionString2Database = null;
selectedCombo2Database = null;
selectedCombo2Table = null;
cb_DataBase_2.Items.Clear();
cb_Table_2.Items.Clear();
}
}
/// <summary>
/// This method handles the sign in button on the right side. Its pretty identicle to the above
/// method, but handles the right side fields and right side combo box.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_Signin_2_Click(object sender, EventArgs e)
{
lbl_Error_User_2.Show();
// Find the provider. If none is selected, loop through them all
string connectionString = SignInChecker(USER_TWO);
if (connectionString != "Failed")
{
LabelChanger.ChangeText("Successfully Logged in.", lbl_Error_User_2);
LabelChanger.ChangeColor("Green", lbl_Error_User_2);
SQLLaptop.LoadSchemaList(connectionString, cb_DataBase_2);
connectionString2 = connectionString;
}
else
{
LabelChanger.ChangeText("Invalid Credentials.", lbl_User_2);
LabelChanger.ChangeColor("Red", lbl_Error_User_2);
connectionString2 = "Failed";
}
}
/// <summary>
/// This method is support in non-assignment mode. Is just duplicates the first account
/// into the second account
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_Same_Account(object sender, EventArgs e)
{
HideSecondUserLogin();
// Retrieve information from the first sign in form
string connectionString = SignInChecker(USER_ONE);
lbl_Error_User_2.Show();
// If successfully logged in, display success
if (connectionString != "Failed")
{
SQLLaptop.LoadSchemaList(connectionString, cb_DataBase_2);
LabelChanger.ChangeText("Successfully Logged in.", lbl_Error_User_2);
LabelChanger.ChangeColor("Green", lbl_Error_User_2);
connectionString2 = connectionString;
}
else
{
LabelChanger.ChangeText("Invalid Credentials.", lbl_User_2);
LabelChanger.ChangeColor("Red", lbl_Error_User_2);
}
// else if failed to log in, display error
}
/// <summary>
/// This method is used to populate the datagrid inside of the form. This method checks
/// which side (left or right) is calling it, and then populates it.
/// </summary>
private bool PopulateDataGrid()
{
bool containsInvalidDatatypes = false;
int rowCount = 0;
DataTable dt = null;
Dictionary <DataColumn, string> invalidColumns = new Dictionary <DataColumn, string>();
string columnName = string.Empty;
DataColumn invalidColumn;
// Check which preview button was pressed. Left or right side. Send the query to the SQL
// class to SELECT all.
if (whichSide == USER_ONE)
{
dt = SQLLaptop.QuerySelectAll(connectionStringLeft, selectedTableLeft);
}
else if (whichSide == USER_TWO)
{
dt = SQLLaptop.QuerySelectAll(connectionStringRight, selectedTableRight);
}
// We want to seach the columns for the datatype Byte[]. Currently our
// datagridview cannot support some Byte[] rows. Lets not display them.
// We will warn the user if a Byte[] column is found. From here, we will give
// the user the choice to convert the column and row datatypes to something viewable (a
// string in our case). This WILL take a while depending on how many rows there are.
// This may also cause exceptions which we will catch.
foreach (DataColumn x in dt.Columns)
{
if (x.DataType == typeof(Byte[]))
{
invalidColumn = x;
columnName = x.ColumnName;
invalidColumns.Add(invalidColumn, columnName);
// Count how many rows this table contains to display in the message box.
if (!containsInvalidDatatypes)
{
rowCount = dt.Rows.Count;
}
containsInvalidDatatypes = true;
}
}
// The user will have 2 possible message boxes that will pop up. The first being an option
// to change the datatable to make it viewable. The second will be a message saying its
// impossible to open the preview because of the row count.
DialogResult result = System.Windows.Forms.DialogResult.No;
if (containsInvalidDatatypes && (rowCount <= 200))
{
result = MessageBox.Show("This table contains a column or columns that have an non-viewable data type." +
"We have the ability to convert that columns rows to blank entries. This will make the rest of the data viewable." +
"Be Warned though.. This could take a VERY long time depending on the amount of rows.\n" +
"We suggest only doing this if the row count is less than 50. \n" +
"The row count for this Table is " + rowCount + ".",
"WARNING: Invalid data type in a column detected.",
MessageBoxButtons.YesNo, MessageBoxIcon.Question);
}
else if (containsInvalidDatatypes)
{
result = MessageBox.Show("Currently this application does not support the ability to display a data table " +
"that has an invalid data type in one or more of the columns and more than 200 rows. It would take too long to convert every row containing this datatype" +
"\nSorry for the inconvenience." +
"To better understand why, your tables row count is below.\n" +
"The row count for this Table is " + rowCount + ".",
"ERROR: Invalid data type in a column detected.",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
// We will only convert the table if the user clicks "YES"
// We will display a "temp" table so we dont change the main table.
if (result == System.Windows.Forms.DialogResult.Yes)
{
DataTable tempTable = ChangeColumnDataType(dt, columnName, typeof(string));
dataGridView1.DataSource = tempTable;
}
// We will only display the result if either
// 1. The user agrees to converting the column
// 2. The user's chosen table does not contain unsupported datatypes.
if (!containsInvalidDatatypes)
{
dataGridView1.DataSource = dt;
}
return(containsInvalidDatatypes);
}
/// <summary>
/// This is the main method that does the copying. It uses a few features inside the SQLlaptop
/// class to work with the database and figure out results.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_Transfer_Click(object sender, EventArgs e)
{
bool shouldCopy = false;
bool emptyTable = false;
// Check to make sure all fields are selected
if (AllFieldsFilled())
{
if (!assignmentRequirement)
{
string selectedTable = (string)cb_Table_2.SelectedItem;
if (SQLLaptop.TableContainsContent(connectionString2Database, selectedTable))
{
emptyTable = false;
}
else
{
// Table 2 is currently empty. Just copy over data
emptyTable = true;
shouldCopy = true;
}
}
else
{
if (connectionString1Database != connectionString2Database)
{
emptyTable = true;
shouldCopy = false;
}
}
if (!emptyTable)
{
DialogResult result = System.Windows.Forms.DialogResult.No;
//Prompt that current table will be over-written with message box
result = MessageBox.Show("Are you sure you'd like to copy the first table to the second table?",
"Copy",
MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == System.Windows.Forms.DialogResult.Yes)
{
shouldCopy = true;
}
}
}
if (shouldCopy)
{
//Copy content from table 1 to table 2
CopyContent();
}
else if ((emptyTable == true) && (shouldCopy == false))
{
lbl_Error_Copy.Show();
lbl_Error_Copy.Text = "Database do not match. This is do-able via disables the assignment requirement checkbox";
}
else
{
lbl_Error_Copy.Show();
lbl_Error_Copy.Text = "You are missing some selections.. Please choose a database and table for source and destination";
}
}
