private void btnDelete_Click(object sender, EventArgs e)
{
if (dgvResult.Rows.Count == 0)
{
MessageBox.Show("No record to delete.", "Delete", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else if ((from DataGridViewRow row in dgvColumns.Rows
where Convert.ToBoolean(row.Cells["isPrimaryKeyDataGridViewCheckBoxColumn"].Value)
select row).ToList().Count() == 0)
{
MessageBox.Show("Cannot delete record without primary key", "Delete", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (DialogResult.Yes == MessageBox.Show("Are you sure you want to delete rows?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question))
{
string dbName = this.GetDBName();
string tableName = this.GetTableName();
Dictionary <string, object> paramByValues = new Dictionary <string, object>();
int count = 0;
string whereQuery = "(";
foreach (DataGridViewRow row in dgvResult.SelectedRows)
{
string whereSubQuery = "";
foreach (ColumnDetail col in this.GetColumnDetails(true, row.Index).Where(x => x.IsPrimaryKey))
{
string parameterName = string.Format("@{0}{1}", col.ColumnName, count.ToString());
if (whereSubQuery != "")
{
whereSubQuery = " AND ";
}
whereSubQuery = string.Format("{0}={1}", col.ColumnName, parameterName);
paramByValues.Add(parameterName, col.ColumnValue);
}
string a = "";
if (whereQuery != "(")
{
whereQuery = whereQuery + " OR (";
}
whereQuery = whereQuery + whereSubQuery + ")";
count++;
}
string deleteQuery = string.Format("use {0};DELETE FROM {1} WHERE {2};", dbName, tableName, whereQuery);
try
{
MSSQLBase.SQLBase s = new MSSQLBase.SQLBase(DBConnection.DbCon.Connection);
s.ExecuteNonQuery(deleteQuery, paramByValues);
btnExec.PerformClick();
MessageBox.Show(string.Format("{0} row/s deleted", count.ToString(), "Delete", MessageBoxButtons.OK, MessageBoxIcon.Information));
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Delete", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void SetCmbDB()
{
cmbDB.Items.Clear();
MSSQLBase.SQLBase b = new MSSQLBase.SQLBase(DBConnection.DbCon.Connection);
List <string> databases = b.GetDatabaseList();
cmbDB.AddItems(databases.Cast <object>().ToList());
}
private void bwExecStoredProc_DoWork(object sender, DoWorkEventArgs e)
{
MainForm.StoredProcRequirement storedProcReq = (MainForm.StoredProcRequirement)e.Argument;
MSSQLBase.SQLBase s = new MSSQLBase.SQLBase(DBConnection.DbCon.Connection);
e.Result = s.ExecuteQuery(string.Format("[{0}].[dbo].[{1}]", storedProcReq.DBName, storedProcReq.StoredProcName), MSSQLBase.SQLBase.QueryType.StoredProc, storedProcReq.ParameterByValue);
if (bwExecStoredProc.CancellationPending)
{
e.Cancel = true;
}
}
public static List <TableDetail> GetTables(string dbName)
{
string query = string.Format(@"SELECT TABLE_NAME AS 'Table'
FROM {0}.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME", dbName);
MSSQLBase.SQLBase b = new MSSQLBase.SQLBase(DBConnection.DbCon.Connection);
DataTable dt = b.ExecuteQuery(query);
return((from DataRow row in dt.Rows
select new TableDetail
{
TableName = row["Table"].ToString()
}).ToList());
}
public static List <StoredProcDetail> GetStoredProcs(string dbName)
{
string query = string.Format(@"SELECT SPECIFIC_NAME AS 'StoredProcedure'
FROM {0}.information_schema.routines
WHERE routine_type = 'PROCEDURE'
ORDER BY SPECIFIC_NAME", dbName);
MSSQLBase.SQLBase b = new MSSQLBase.SQLBase(DBConnection.DbCon.Connection);
DataTable dt = b.ExecuteQuery(query);
List <StoredProcDetail> storedProcs = (from DataRow row in dt.Rows
select new StoredProcDetail
{
StoredProcName = row["StoredProcedure"].ToString()
}).ToList();
return(storedProcs);
}
public static List <StoredProcDetail.ParameterDetail> GetParameters(string dbName, string storedProcName)
{
string query = string.Format(@"SELECT PARAMETER_NAME as 'Parameter'
,DATA_TYPE as 'DataType'
FROM {0}.information_schema.parameters
WHERE specific_name = '{1}'", dbName, storedProcName);
MSSQLBase.SQLBase b = new MSSQLBase.SQLBase(DBConnection.DbCon.Connection);
DataTable dt = b.ExecuteQuery(query);
List <ParameterDetail> parameters = (from DataRow row in dt.Rows
select new StoredProcDetail.ParameterDetail
{
ParameterName = row["Parameter"].ToString(),
DataType = row["DataType"].ToString()
}).ToList();
return(parameters);
}
private void bgTableQuery_DoWork(object sender, DoWorkEventArgs e)
{
string query = e.Argument.ToString();
using (SqlConnection con = new SqlConnection(DBConnection.DbCon.Connection))
{
SqlCommand com = new SqlCommand(query, con);
}
MSSQLBase.SQLBase b = new MSSQLBase.SQLBase(DBConnection.DbCon.Connection);
e.Result = b.ExecuteQuery(query);
if (bgTableQuery.CancellationPending)
{
e.Cancel = true;
}
}
private void ExecuteUpdate(string query)
{
Dictionary <string, object> columnByValues = new Dictionary <string, object>();
foreach (ColumnDetail col in from t in this._ColumnDetails
where !t.IsIdentity && !this._TypesNotEditable.Contains(t.DataType)
select t)
{
columnByValues.Add(string.Format("@{0}", col.ColumnName), GetNewValue(col.ColumnName));
}
try
{
MSSQLBase.SQLBase s = new MSSQLBase.SQLBase(DBConnection.DbCon.Connection);
s.ExecuteNonQuery(query, columnByValues);
MessageBox.Show("Successfully Saved", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.SuccessSave = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "btnSave", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static List <ColumnDetail> GetColumns(string dbName, string tableName)
{
string query = string.Format(@"use {0};
SELECT col.COLUMN_NAME AS 'ColumnName'
,col.IS_NULLABLE AS 'IsNullable'
,col.DATA_TYPE AS 'DataType'
,ISNULL(CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR), 'Not Applicable') AS 'MaxLength'
,CASE COLUMNPROPERTY(OBJECT_ID(col.TABLE_NAME), col.COLUMN_NAME, 'IsIdentity')
WHEN 1
THEN CAST(1 AS BIT)
WHEN 0
THEN CAST(0 AS BIT)
ELSE 'W'
END AS 'IsIdentity'
,CASE
WHEN colUsage.COLUMN_NAME IS NULL
THEN CAST(0 AS BIT)
ELSE CAST(1 AS BIT)
END AS 'IsPrimaryKey'
FROM {0}.INFORMATION_SCHEMA.COLUMNS col
LEFT JOIN {0}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE colUsage ON OBJECTPROPERTY(OBJECT_ID(colUsage.constraint_name), 'IsPrimaryKey') = 1
AND colUsage.COLUMN_NAME = col.COLUMN_NAME
AND colUsage.TABLE_NAME = col.TABLE_NAME
WHERE col.TABLE_NAME = '{1}'", dbName, tableName);
MSSQLBase.SQLBase b = new MSSQLBase.SQLBase(DBConnection.DbCon.Connection);
DataTable dt = b.ExecuteQuery(query);
return((from DataRow row in dt.Rows
select new ColumnDetail
{
ColumnName = row["ColumnName"].ToString(),
IsPrimaryKey = Convert.ToBoolean(row["IsPrimaryKey"]),
DataType = row["DataType"].ToString(),
IsIdentity = Convert.ToBoolean(row["IsIdentity"]),
ColumnValue = ""
}).ToList());
}
