frmmain.cs

数据库操作的小工具

        // clear log
        private void btnClearLog_Click(object sender, EventArgs e)
        {
            this.richTxtLog.Text = string.Empty;
        }

        // get all server objects (include databases,tables and sps) in Async mode
        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {            
            this.sql.GetServerDatabases(this.treeView1, this.imageList1, 0, 1, 10);
        }

        // release ui that freezed for retrieving data from dataSource and print result to log
        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            this.btnConnect.Enabled = true;
            this.btnGenerateCode.Enabled = true;
            this.PrintToLog(string.Format("Database object(s) has been completed, Found {0} database(s) in the selected datasource.", this.treeView1.Nodes.Count));
        }

        private void radioGenSp_CheckedChanged(object sender, EventArgs e)
        {
            if(radioGenSp.Checked)
                this.PrintToLog("Generate Stored Procedures Selected");
        }

        private void radioGenSpCode_CheckedChanged(object sender, EventArgs e)
        {
            if(radioGenSpCode.Checked)
                this.PrintToLog("Generate Code for Stored Procedures Selected");
        }

        private void checkGenTblClass_CheckedChanged(object sender, EventArgs e)
        {
            this.PrintToLog(string.Format("Generate Class for Tables set to : {0}", this.checkGenTblClass.Checked));
        }

        private void menuItemAboutUs_Click(object sender, EventArgs e)
        {
            frmAbout frm = new frmAbout();
            frm.ShowDialog();
        } 

        #endregion        

        // print action information to log
        private void PrintToLog(string text)
        {
            this.richTxtLog.Text += text + "\n";
            this.richTxtLog.SelectionStart = this.richTxtLog.TextLength;
            this.richTxtLog.ScrollToCaret();
        }

        // generate sql sps and class code together
        private void GenerateSqlSpAndClassCodeAndGetSps()
        {            
            //this.btnGenerateCode.Enabled = false;

            #region initialize new some DataColumn objects for retreieve all informations about stored procedures in db            

            DataColumn dcSpName = new DataColumn("SpName");
            DataColumn dcParameterName = new DataColumn("ParameterName");
            DataColumn dcSystemType = new DataColumn("SystemType");
            DataColumn dcLength = new DataColumn("Length");
            DataColumn dcIsOutputParameter = new DataColumn("IsOutputParameter");

            this.dtSPs = new DataTable();
            this.dtSPs.Columns.Add(dcSpName);
            this.dtSPs.Columns.Add(dcParameterName);
            this.dtSPs.Columns.Add(dcSystemType);
            this.dtSPs.Columns.Add(dcLength);
            this.dtSPs.Columns.Add(dcIsOutputParameter);
            #endregion

            string dbName;
            string tableName;
            string colName;
            string spName;
            string sp_parameter;
            string sqlScript = string.Empty;
            
            TreeNode currentNode = this.treeView1.SelectedNode;
            if (currentNode.Parent != null)
            {
                // this node either Tables/Procedures or table/procedure or column/procedureParameter
                TreeNode parentNode1 = currentNode.Parent;
                if (parentNode1.Parent != null)
                {
                    // this node is table/procedure or column/procedureParameter
                    if (parentNode1.Parent.Parent != null)
                    {
                        // this node is column/procedureParameter
                        TreeNode dbNode = parentNode1.Parent.Parent;
                        dbName = dbNode.Text;
                        int tblCount = 0;
                        int spCount = 0;

                        #region Actions to Create code for Stored Procedures

                        if (radioGenSpCode.Checked)
                        {
                            this.sql.GetDatabaseSps(this.treeView1, dbNode.Nodes[0], this.imageList1, 10);
                            foreach (TreeNode spNode in dbNode.Nodes[0].Nodes)
                            {
                                this.sql.GetProcedureParameters(this.treeView1, spNode, this.imageList1, 11);
                                spCount++;
                                spName = spNode.Text;
                                foreach (TreeNode sp_paramNode in spNode.Nodes)
                                {
                                    string[] txts = sp_paramNode.Text.Split(new char[] { ',' });
                                    string colParameterName = "";
                                    string colSystemType = "";
                                    string colLength = "";
                                    int colIsOutputParameter = 0;

                                    if (txts.Length > 2)
                                    {
                                        colParameterName = txts[0];
                                        colIsOutputParameter = txts[2] == "OutPut" ? 1 : 0;

                                        string[] txts2 = txts[1].Split('(', ')');
                                        colSystemType = txts2[0];
                                        colLength = txts2[1];
                                        this.dtSPs.Rows.Add(spName, colParameterName, colSystemType, colLength, colIsOutputParameter);
                                    }
                                    else
                                    {
                                        colParameterName = txts[0];
                                        string[] txts2 = txts[1].Split('(', ')');
                                        colSystemType = txts2[0];
                                        colLength = txts2[1];
                                        this.dtSPs.Rows.Add(spName, colParameterName, colSystemType, colLength, colIsOutputParameter);
                                    }
                                }
                                this.dtSPs.Rows.Add(spName, "NULL", "NULL", "NULL", "NULL");
                            }
                            if (this.cmbLanguage.Text == "C#")
                                this.GenerateDocumentForSps(dbName, "DAL", Providers.CsProvider);
                            if (this.cmbLanguage.Text == "VB")
                                this.GenerateDocumentForSps(dbName, "DAL", Providers.VbProvider);
                        }
                        #endregion

                        #region Actions to Create class for tables or Cenerate Stored Procedures

                        // initialize objects for query through database to generate sql sps
                        string connStr = GetConnectionString();
                        DbObject dbo = new DbObject(connStr);

                        string x = string.Format(Resources.strTablesAndColumns, dbName);
                        DataSet dsTablesAndColumns = dbo.RunQuery(x, "TablesAndColumns");

                        this.sql.GetDatabaseTables(this.treeView1, dbNode.Nodes[1], this.imageList1, 1);
                        foreach (TreeNode tblNode in dbNode.Nodes[1].Nodes)
                        {
                            this.sql.GetTableColumns(this.treeView1, tblNode, this.imageList1, 6);
                            tableName = tblNode.Text;
                            tblCount++;

                            if (this.radioGenSp.Checked)
                            {
                                // execute some actions, then generate sql sps
                                this.PrintToLog(string.Format("Generating Stored Procedures(SelectAll,SelectRow,Insert,Update,DeleteRow) for table '{0}' ...", tableName));
                                System.Windows.Forms.Application.DoEvents();
                                DataRow[] rows = dsTablesAndColumns.Tables[0].Select("Table_Name = '" + tableName + "'");
                                sqlScript += GenerateSQL(dbName, tableName, rows);                                
                            }

                            List<CodeDomDatabaseSQLDMO.Column> columnCollection = new List<CodeDomDatabaseSQLDMO.Column>();
                            foreach (TreeNode colNode in tblNode.Nodes)
                            {
                                string[] txts = colNode.Text.Split(new char[] { ',' });
                                string colName3 = txts[0];
                                string columnType = txts[1];

                                CodeDomDatabaseSQLDMO.Column col = new CodeDomDatabaseSQLDMO.Column(colName3, columnType);
                                columnCollection.Add(col);
                            }

                            // check if user select generate class for tables, then generate class for tables
                            if (this.checkGenTblClass.Checked)
                            {
                                this.PrintToLog(string.Format("Generating Class '{0}' for table '{1}' in Namespace '{2}' ...", this.ToUpperFirstChar(tableName), tableName, dbName));
                                System.Windows.Forms.Application.DoEvents();
                                this.GenerateDocumentForCode(dbName, tableName, columnCollection);                                
                            }
                        }
                        if (this.radioGenSp.Checked == true && sqlScript.Length > 0)
                            this.PrintToLog(string.Format("Sp generation successfully completed for {0} table(s).", tblCount));
                        if (tblCount > 0 && this.checkGenTblClass.Checked)
                            this.PrintToLog(string.Format("Code generation successfully completed for {0} table(s)", tblCount));
                        #endregion
                    }
                    else
                    {
                        // this node is table/procedure
                        TreeNode dbNode = parentNode1.Parent;
                        dbName = dbNode.Text;
                        int tblCount = 0;
                        int spCount = 0;

                        #region Actions to Create code for Stored Procedures

                        if (radioGenSpCode.Checked)
                        {
                            this.sql.GetDatabaseSps(this.treeView1, dbNode.Nodes[0], this.imageList1, 10);
                            foreach (TreeNode spNode in dbNode.Nodes[0].Nodes)
                            {
                                this.sql.GetProcedureParameters(this.treeView1, spNode, this.imageList1, 11);
                                spCount++;
                                spName = spNode.Text;
                                foreach (TreeNode sp_paramNode in spNode.Nodes)
                                {
                                    string[] txts = sp_paramNode.Text.Split(new char[] { ',' });
                                    string colParameterName = "";
                                    string colSystemType = "";