private void button_createview_Click(object sender, EventArgs e)
{
string _query = string.Empty;
foreach (DataGridViewRow row in dataGridView_table.Rows)
{
if (row.Cells[0].Value != null)
{
if ((bool)row.Cells[0].Value == true)
{
try
{
SqlConnection con = new SqlConnection(MiscClass.ConnectionString);
con.Open();
_query = @"DECLARE @SQL as varchar(4000)
SET @SQL = 'CREATE VIEW " + (row.DataBoundItem as Table).TableName + @"_VW as SELECT * FROM " + (row.DataBoundItem as Table).TableName + @"_TB'
IF NOT EXISTS ( SELECT * FROM sys.objects where type='V' and name = '" + (row.DataBoundItem as Table).TableName + @"_VW')
BEGIN
EXEC(@SQL);
END";
SqlCommand cmd = new SqlCommand(_query, con);
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
MessageBox.Show("View(s) Created Successfully For Selected Tables");
HelpingClass.PopulateDataGridView(View.GetViewsInfo(), dataGridView_view);
}
public MainView()
{
InitializeComponent();
List <Table> listoftables = Table.GetTablesInfo();
List <View> listofviews = View.GetViewsInfo();
List <StoredProcedure> listofsps = StoredProcedure.GetStoredProceduresInfo();
HelpingClass.PopulateDataGridView(listoftables, dataGridView_table);
HelpingClass.PopulateDataGridView(listofviews, dataGridView_view);
HelpingClass.PopulateDataGridView(listofsps, dataGridView_storedprocedures);
FormatGridView(dataGridView_table);
FormatGridView(dataGridView_view);
FormatGridView(dataGridView_storedprocedures);
btn_map.Enabled = false;
}
private void button_create_sp_Click(object sender, EventArgs e)
{
string _query = string.Empty;
foreach (DataGridViewRow row in dataGridView_table.Rows)
{
if (row.Cells[0].Value != null)
{
if ((bool)row.Cells[0].Value == true)
{
try
{
// ADD Stored Procedures Start
SqlConnection con = new SqlConnection(MiscClass.ConnectionString);
con.Open();
_query = @"DECLARE @SQL as varchar(4000)
SET @SQL = ' PROCEDURE Add" + (row.DataBoundItem as Table).TableName + @"_SP
@" + (row.DataBoundItem as Table).TableName.Substring(0, 3) + @"_TVP " + (row.DataBoundItem as Table).TableName + @"_Type READONLY
AS";
_query += @" INSERT INTO " + (row.DataBoundItem as Table).TableName + @"_TB(";
//First Loop
for (int i = 0; i < (row.DataBoundItem as Table).FieldsForQuery.Count; i++)
{
_query += (row.DataBoundItem as Table).FieldsForQuery[i].Item3 == 1 ? "" : "[" + (row.DataBoundItem as Table).FieldsForQuery[i].Item1 + "]";
if (((row.DataBoundItem as Table).FieldsForQuery.Count - i) != 1 && (row.DataBoundItem as Table).FieldsForQuery[i].Item3 != 1)
{
_query += ",";
}
}
_query += @")
SELECT ";
for (int i = 0; i < (row.DataBoundItem as Table).FieldsForQuery.Count; i++)
{
_query += (row.DataBoundItem as Table).FieldsForQuery[i].Item3 == 1 ? "" : "[" + (row.DataBoundItem as Table).FieldsForQuery[i].Item1 + "]";
if (((row.DataBoundItem as Table).FieldsForQuery.Count - i) != 1 && (row.DataBoundItem as Table).FieldsForQuery[i].Item3 != 1)
{
_query += ",";
}
}
_query += @" FROM @" + (row.DataBoundItem as Table).TableName.Substring(0, 3) + @"_TVP;'";
_query += @" IF NOT EXISTS (SELECT * FROM sys.objects where type='P' and name ='Add" + (row.DataBoundItem as Table).TableName + @"_SP')
BEGIN
SET @SQL = 'CREATE '+@SQL
END;
ELSE
BEGIN
SET @SQL = 'ALTER '+@SQL
END;
exec(@SQL);";
SqlCommand cmd = new SqlCommand(_query, con);
cmd.ExecuteNonQuery();
con.Close();
// ADD Stored Procedures End
// Update Stored Procedures Start
con = new SqlConnection(MiscClass.ConnectionString);
con.Open();
_query = @"DECLARE @SQL as varchar(4000)
SET @SQL = ' PROCEDURE Update" + (row.DataBoundItem as Table).TableName + @"_SP
@" + (row.DataBoundItem as Table).TableName.Substring(0, 3) + @"_TVP " + (row.DataBoundItem as Table).TableName + @"_Type READONLY
AS
Update " + (row.DataBoundItem as Table).TableName + @"_TB SET";
for (int i = 0; i < (row.DataBoundItem as Table).FieldsForQuery.Count; i++)
{
_query += (row.DataBoundItem as Table).FieldsForQuery[i].Item3 == 1 ? "" : "[" + (row.DataBoundItem as Table).FieldsForQuery[i].Item1 + "] = i.[" + (row.DataBoundItem as Table).FieldsForQuery[i].Item1 + "]";
if (((row.DataBoundItem as Table).FieldsForQuery.Count - i) != 1 && (row.DataBoundItem as Table).FieldsForQuery[i].Item3 != 1)
{
_query += @",
";
}
}
_query += @"
FROM (SELECT * FROM @" + (row.DataBoundItem as Table).TableName.Substring(0, 3) + @"_TVP) i
WHERE " + (row.DataBoundItem as Table).TableName + "_TB." + (row.DataBoundItem as Table).FieldsForQuery.Single(a => a.Item3 == 1).Item1 + " = i." + (row.DataBoundItem as Table).FieldsForQuery.Single(a => a.Item3 == 1).Item1 + ";'";
_query += @" IF NOT EXISTS (SELECT * FROM sys.objects where type='P' and name ='Update" + (row.DataBoundItem as Table).TableName + @"_SP')
BEGIN
SET @SQL = 'CREATE '+@SQL
END;
ELSE
BEGIN
SET @SQL = 'ALTER '+@SQL
END;
exec(@SQL);";
cmd = new SqlCommand(_query, con);
cmd.ExecuteNonQuery();
con.Close();
// Update Stored Procedures End
// Delete Stored Procedures Start
con = new SqlConnection(MiscClass.ConnectionString);
con.Open();
_query = @"DECLARE @SQL as varchar(4000)
SET @SQL = ' PROCEDURE Delete" + (row.DataBoundItem as Table).TableName + @"_SP
@" + (row.DataBoundItem as Table).TableName.Substring(0, 3) + @"_TVP " + (row.DataBoundItem as Table).TableName + @"_Type READONLY
AS
DELETE FROM " + (row.DataBoundItem as Table).TableName + @"_TB WHERE " + (row.DataBoundItem as Table).FieldsForQuery.Single(a => a.Item3 == 1).Item1 + @" IN (SELECT " + (row.DataBoundItem as Table).FieldsForQuery.Single(a => a.Item3 == 1).Item1 + @" FROM @" + (row.DataBoundItem as Table).TableName.Substring(0, 3) + "_TVP);'";
_query += @" IF NOT EXISTS (SELECT * FROM sys.objects where type='P' and name ='Delete" + (row.DataBoundItem as Table).TableName + @"_SP')
BEGIN
SET @SQL = 'CREATE '+@SQL
END;
ELSE
BEGIN
SET @SQL = 'ALTER '+@SQL
END;
exec(@SQL);";
cmd = new SqlCommand(_query, con);
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Stored Procedure(s) Created Successfully For Selected Tables");
HelpingClass.PopulateDataGridView(StoredProcedure.GetStoredProceduresInfo(), dataGridView_storedprocedures);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + " " + ex.StackTrace);
}
}
}
}
}