Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        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;
        }
Ejemplo n.º 3
0
        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);
                        }
                    }
                }
            }
        }