private void btnAdd_Click(object sender, EventArgs e)
{
var query = "ALTER TABLE " + tablename + " ADD ";
var columnName = txtColumn.Text;
var columnType = cmbColumnType.Text;
query = query + columnName + " " + columnType;
var serverName = ClsCommon.ServerName;
var databaseName = ClsCommon.DatabaseName;
var userName = ClsCommon.LoginName;
var passWord = ClsCommon.PasswordName;
try
{
ClsCommon.ExecuteInsertStatements(serverName, userName, passWord, databaseName, query);
MessageBox.Show("New column has added", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void frmExportReport_Load(object sender, EventArgs e)
{
var common = new ClsCommon();
var dst = common.GetDatasetFromSQLQuery(_query);
dgrdReport.DataSource = dst.Tables[0];
lblCount.Text = dgrdReport.Rows.Count.ToString() + " records found.";
}
private void ListSQLTables()
{
DataSet dst = new DataSet();
dst = new ClsCommon().GetTables();
lstSQL.DataSource = dst.Tables[0];
lstSQL.DisplayMember = "SchemaTable";
}
private void LoadColumnDetails()
{
DataSet dst = new DataSet();
dst = new ClsCommon().GetColumnsofTable(serverName, userName, passWord, databaseName, tableName);
dst.Tables[0].Columns[0].ColumnName = "Column Name";
dst.Tables[0].Columns[1].ColumnName = "Column Type";
dst.Tables[0].Columns[2].ColumnName = "Size";
dgrdColumnsDetails.DataSource = dst.Tables[0];
}
private void dgrdTables_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
var tableName = dgrdTables.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
var common = new ClsCommon();
var dst = new DataSet();
dst = common.GetColumnsofTable(ClsCommon.ServerName, ClsCommon.LoginName,
ClsCommon.PasswordName, ClsCommon.DatabaseName, tableName);
dgrdColumns.DataSource = dst.Tables[0];
}
private void btnOk_Click(object sender, EventArgs e)
{
if (tabControl1.SelectedTab.Text.Trim() == "Backup")
{
if (txtDatabasemdf.Text.Length < 1)
{
MessageBox.Show("No Database Backup file path found!!", "Information: Database Backup", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
try
{
var common = new ClsCommon();
ClsCommon.BackupDatabase(txtDatabasemdf.Text);
MessageBox.Show("Database Backup completed successfully.", "Information: Database Backup", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error: Database Backup", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
else if (tabControl1.SelectedTab.Text.Trim() == "Restore")
{
if (txtRestoreDatabase.Text.Length < 1 || txtDbName.Text.Length < 1)
{
MessageBox.Show("No Database Restore file path | Database Name found!!", "Information: Database Backup", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
try
{
var common = new ClsCommon();
ClsCommon.RestoreDatabase(txtRestoreDatabase.Text, txtDbName.Text);
MessageBox.Show("Database Restore completed successfully.\nYou can use it by login again.", "Information: Database Restore", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error: Database Restore", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
else if (tabControl1.SelectedTab.Text.Trim() == "Backup-External")
{
ClsCommon.ExcelMode = ExcelBackupMode.SeparateSheet;
var frmtakingbackup = new frmTakingBackup();
frmtakingbackup.ShowDialog();
}
}
private void btnAttach_Click(object sender, EventArgs e)
{
//Validations
var databaseName = txtDatabaseName.Text;
var dbMDF = txtDatabasemdf.Text;
var dbLDF = txtDatabaseldf.Text;
var query = string.Empty;
var flag = true;
if (databaseName.Trim().Length < 1)
{
MessageBox.Show("Database name is mandatory.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
flag = false;
}
if (dbMDF.Trim().Length < 1)
{
MessageBox.Show("Database .mdf is mandatory.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
flag = false;
}
try
{
if (flag != true)
{
return;
}
if (dbMDF.Trim().Length > 0 && dbLDF.Trim().Length < 1)
{
query = "CREATE DATABASE " + databaseName + " ON " + "PRIMARY ( FILENAME = '" + dbMDF + "' ) " + " FOR ATTACH";
}
else if (dbMDF.Trim().Length > 0 && dbLDF.Trim().Length > 0)
{
query = "CREATE DATABASE " + databaseName + " ON " + "PRIMARY ( FILENAME = '" + dbMDF + "' ), " + "FILEGROUP MyDatabase_Log ( FILENAME = '" + dbLDF + "')" + "FOR ATTACH";
}
ServerExistsinPervasiveDataset(_selectedServer); //To arrange the connection string elements
var common = new ClsCommon();
ClsCommon.AttachSqlDatabase(ClsCommon.ServerName, ClsCommon.LoginName, ClsCommon.PasswordName, query);
MessageBox.Show("Database has attached successfully.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error: Database Attach", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void frmCreateReports_Load(object sender, EventArgs e)
{
//Fill the Databases
//-----------------------------------------------------
var common = new ClsCommon();
dgrdTables.DataSource = common.GetTables().Tables[0];
dgrdTables.Columns[0].Visible = false;
dgrdTables.Columns[2].Visible = false;
dgrdTables.Columns[3].Visible = false;
//For SelectedColumns datagridview
_dstSelectedColumns.Tables.Add("Cols");
_dstSelectedColumns.Tables["Cols"].Columns.Add("Columns");
}
private void btnConnect_Click(object sender, EventArgs e)
{
try
{
var common = new ClsCommon();
var message = string.Empty;
//Validations
message = message + common.ValidateInput(txtServerName.Text, lblServerName.Text, ValidationType.EmptyCheck);
message = message + common.ValidateInput(txtLogin.Text, lblLogin.Text, ValidationType.EmptyCheck);
message = message + common.ValidateInput(txtPassword.Text, lblPassword.Text, ValidationType.EmptyCheck);
if (message.Length > 1)
{
MessageBox.Show("Following information is mandatory to provide:\n\n\n" + message, "Information", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
else
{
//Authenticate the user
if (common.IsUserAuthenticated(txtServerName.Text, txtLogin.Text, txtPassword.Text))
{
//Fills the Combobox with the permitted database list
//======================================================================================
cmbDatabase.Enabled = true;
cmbDatabase.DataSource = common.GetDatabases().Tables[0];
cmbDatabase.DisplayMember = "name";
cmbDatabase.ValueMember = "name";
cmbDatabase.SelectedIndex = 0;
//======================================================================================
btnProceed.Enabled = true;
}
else
{
MessageBox.Show("Invalid Credentials provided.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
}
catch (Exception ex)
{
var error = "Cannot Proceed or not functional due to the following Exception: \n\n" + ex.Message + "\n\nFor further information please contact to vendor or visit www.krayknot.com";
MessageBox.Show(error, "LooDon: Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnCreate_Click(object sender, EventArgs e)
{
var dbParam = new ClsCommon.DatabaseParam();
dbParam.DatabaseName = txtDBName.Text;
//Assign Data file parameters
if (rdbDFinmegabytes.Checked == true)
{
dbParam.DataFileGrowth = txtDFinmegabytes.Text;
}
else
{
dbParam.DataFileGrowth = txtDFbypercent.Text + "%";
}
dbParam.DataFileName = txtDFDataFilename.Text;
dbParam.DataFileSize = "2";//2MB at the init state
dbParam.DataPathName = txtDFMDFFilename.Text;
//Assign Log file parameters
if (rdbLFinmegabytes.Checked == true)
{
dbParam.LogFileGrowth = txtLFinimegabytes.Text;
}
else
{
dbParam.LogFileGrowth = txtLFbypercent.Text + "%";
}
dbParam.LogFileName = txtLFFilename.Text;
dbParam.LogFileSize = "1";//1MB at the init state
dbParam.LogPathName = txtLFLDFFilename.Text;
ClsCommon.CreateDatabase(dbParam);
MessageBox.Show("Database created successfully.\nTo view the database, you need to re-login.", "Loodon: Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
Close();
}
public bool GenerateAccessDatabase(string myPath)
{
//CatalogClass cat = new CatalogClass();
string strSQL;
string cs;
bool response = false;
string tablename = string.Empty;
string columns = string.Empty;
string server = ClsCommon.ServerName;
string login = ClsCommon.LoginName;
string password = ClsCommon.PasswordName;
string dB = ClsCommon.DatabaseName;
PrintResponse("Access DB:" + myPath);
//for (int i = 0; i <= lstSQL.Items.Count - 1; i++)
//{
foreach (DataRowView items in lstSQL.Items)
{
try
{
cs = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + myPath + ";Jet OLEDB:Engine Type=5";
//lstSQL.SelectedItem = i;
//tablename = lstSQL.SelectedItem.ToString(); //Get the tablename from sql
tablename = items[0].ToString();
tablename = tablename.Substring(tablename.IndexOf('.') + 1);
//get the columns in a single string variable for the sql table
DataSet dstCols = new DataSet();
dstCols = new ClsCommon().GetColumnsofTable(server, login, password, dB, tablename);
for (int c = 0; c <= dstCols.Tables[0].Rows.Count - 1; c++)
{
columns = columns + dstCols.Tables[0].Rows[c]["column_name"].ToString() + " TEXT(100),";
}
columns = columns.Substring(0, columns.Length - 1); //Removes the trail comman
//strSQL = "CREATE TABLE Issues (mID AUTOINCREMENT, mUser TEXT(100) NOT NULL " +
// ", mError TEXT(100) NOT NULL, " +
// "mDescription TEXT(100) NOT NULL, mDate DATETIME NOT NULL)";
strSQL = "CREATE TABLE " + tablename + "(" + columns + ")";
//cat.Create(cs);
using (OleDbConnection cnn = new OleDbConnection(cs))
{
OleDbCommand cmd = new OleDbCommand();
try
{
cmd.CommandText = strSQL;
cmd.CommandType = CommandType.Text;
cmd.Connection = cnn;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
response = true;
}
catch (Exception ex)
{
response = false;
throw ex;
}
finally
{
cnn.Close();
cmd.Dispose();
}
lstAccess.Items.Add(tablename);
lstAccess.SelectedItem = lstAccess.Items.Count - 1;
lstAccess.Refresh();
}
}
catch (Exception ex)
{
//throw ex;
//lstProgress.Items.Add(ex.Message);
PrintResponse(ex.Message + " Table: " + tablename);
}
columns = string.Empty; //Initialize
}
return(response);
//finally
//{
// //Marshal.FinalReleaseComObject(cat);
//}
}
private void BackupExcelSeparateSheet(string destination, string SQLQuery, string Server, string Username, string Password, string Database, string TableName)
{
var dataError = false;
var sqlConnectionString = "Data Source=" + Server + ";User Id=" + Username + ";Password="******";Initial Catalog=" + Database + ";";
var con = new SqlConnection(sqlConnectionString);
var dstTemp = new DataSet();
try
{
dstTemp = new LoodonDAL.ClsCommon().GetDatasetFromSqlQuery(SQLQuery, con);
}
catch (Exception)
{
dataError = true;
}
var common = new ClsCommon();
if (File.Exists(destination + "\\" + TableName + ".xls"))
{
File.Delete(destination + "\\" + TableName + ".xls");
}
//si Excel,
//créer un classeur avec un nom de table EmployeeData. La table a different champs
//string tableName = string.Empty;
var conn = new OleDbConnection();
conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + destination + "\\" + TableName + ".xls; Extended Properties='Excel 8.0;HDR=YES'";;
conn.Open();
if (dataError == false)
{
if (dstTemp.Tables.Count > 0)
{
//extraire les colonnes de la table de données
var cols = string.Empty;
for (var i = 0; i <= dstTemp.Tables[0].Columns.Count - 1; i++)
{
cols = cols + "[" + dstTemp.Tables[0].Columns[i].ColumnName.ToString() + "] varchar(255), ";
}
cols = cols.Substring(0, cols.Length - 2);
var cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "CREATE TABLE [" + TableName + "] (" + cols + ")";
cmd.ExecuteNonQuery();
//extraire les colonnes de la table source
var colsDest = string.Empty;
var colsValues = string.Empty;
var fieldValue = string.Empty;
for (var i = 0; i <= dstTemp.Tables[0].Columns.Count - 1; i++)
{
colsDest = colsDest + "left([" + dstTemp.Tables[0].Columns[i].ColumnName + "],255) , ";
}
//colsDest = colsDest.Substring(0, colsDest.Length - 2);
//Insérez l'enregistrement de la table
for (var rowData = 0; rowData <= dstTemp.Tables[0].Rows.Count - 1; rowData++)
{
for (var colData = 0; colData <= dstTemp.Tables[0].Columns.Count - 1; colData++)
{
fieldValue = dstTemp.Tables[0].Rows[rowData][colData].ToString();
if (fieldValue.Contains(""))
{
fieldValue = string.Empty;
}
if (fieldValue.Length >= 250)
{
try
{
colsValues = colsValues + "'" + fieldValue.Substring(1, 250).Replace("'", "''") + "',";
}
catch (Exception)
{
// ignored
}
}
else
{
colsValues = colsValues + "'" + fieldValue.Replace("'", "''") + "',";
}
}
colsValues = colsValues.Substring(0, colsValues.Length - 1);
cmd.CommandText = "INSERT INTO [" + TableName + "] values (" + colsValues + ")";
try
{
cmd.ExecuteNonQuery();
}
catch (Exception)
{
// ignored
}
colsValues = string.Empty;
}
}
}
conn.Close();
}
