frmmain.cs

数据库操作的小工具

                                    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 Tables or Procedures node                                
                    dbName = parentNode1.Text;
                    TreeNode dbNode = currentNode.Parent;
                    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, currentNode, this.imageList1, 1);
                    foreach (TreeNode tblNode in currentNode.Nodes)
                    {
                        this.sql.GetTableColumns(this.treeView1, tblNode, this.imageList1, 6);
                        tableName = tblNode.Text;
                        tblCount++;

                        if (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 database node
                dbName = currentNode.Text;
                int tblCount = 0;
                int spCount = 0;

                #region Actions to Create code for Stored Procedures

                if (radioGenSpCode.Checked)
                {
                    this.sql.GetDatabaseSps(this.treeView1, currentNode.Nodes[0], this.imageList1, 10);
                    foreach (TreeNode spNode in currentNode.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;