frmmain.cs

数据库操作的小工具

                                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.Nodes[1], this.imageList1, 1);

                foreach (TreeNode tblNode in currentNode.Nodes[1].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
            }

            if (this.radioGenSp.Checked)
            {
                // save sql sps to file
                DirectoryInfo dirInfo = Directory.CreateDirectory(this.txtSaveTo.Text + "\\Sql");
                StreamWriter sw = new StreamWriter(File.Create(dirInfo.FullName + "\\" + dbName + "_Sps.sql"));
                sw.Write(sqlScript);
                sw.Flush();
                sw.Close();
            }                
        }

        // initialize document to generate code for stored procedures and create DAL (Data Access Layer)
        private void GenerateDocumentForSps(string NameSpace, string Class, Providers languageProvider)
        {
            this.PrintToLog(string.Format("Generating some initialized code and methods ..."));
            System.Windows.Forms.Application.DoEvents();
            CodeCompileUnit compileUnit = new CodeCompileUnit();
            CodeNamespace ns = new CodeNamespace(NameSpace);
            ns.Imports.Add(new CodeNamespaceImport("System"));
            ns.Imports.Add(new CodeNamespaceImport("System.Text"));
            ns.Imports.Add(new CodeNamespaceImport("System.Data"));
            ns.Imports.Add(new CodeNamespaceImport("System.Data.SqlClient"));

            // create our class
            CodeTypeDeclaration ctd = new CodeTypeDeclaration(Class);
            ctd.IsClass = true;
            ns.Types.Add(ctd);
            compileUnit.Namespaces.Add(ns);

            // create private field  for our class (connectionString)
            string strConnectionString = this.ToLowerFirstChar("connectionString");
            CodeMemberField class_field_connectionString = new CodeMemberField(typeof(string), strConnectionString);
            class_field_connectionString.Attributes = MemberAttributes.Private;
            ctd.Members.Add(class_field_connectionString);

            // encapsulate our field(connectionString)
            CodeMemberProperty propertyCon = new CodeMemberProperty();
            propertyCon.Name = this.ToUpperFirstChar(strConnectionString);
            propertyCon.Type = new CodeTypeReference(typeof(string));
            propertyCon.Attributes = MemberAttributes.Public;
            propertyCon.GetStatements.Add(new CodeMethodReturnStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), strConnectionString)));
            propertyCon.SetStatements.Add(new CodeAssignStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), strConnectionString), new CodePropertySetValueReferenceExpression()));
            ctd.Members.Add(propertyCon);

            #region Create 3 constructor
            // create constructor for our class
            CodeConstructor constructor1 = new CodeConstructor();
            constructor1.Attributes = MemberAttributes.Public;
            ctd.Members.Add(constructor1);

            // add parameter to our constructor mothod
            CodeParameterDeclarationExpression constructor1_connectionString = new CodeParameterDeclarationExpression(class_field_connectionString.Type, strConnectionString);
            constructor1.Parameters.Add(constructor1_connectionString);

            // in body of constructor, assign value of parameter to field's property of class (ConnectionString)
            CodeAssignStatement fieldAssignment = new CodeAssignStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), strConnectionString), new CodeArgumentReferenceExpression(constructor1_connectionString.Name));
            constructor1.Statements.Add(fieldAssignment);

            // create constructor2 for our class with 2 args
            CodeConstructor constructor2 = new CodeConstructor();
            constructor2.Attributes = MemberAttributes.Public;
            CodeParameterDeclarationExpression constructor2_server = new CodeParameterDeclarationExpression(typeof(string), "server");
            CodeParameterDeclarationExpression constructor2_database = new CodeParameterDeclarationExpression(typeof(string), "database");
            constructor2.Parameters.Add(constructor2_server);
            constructor2.Parameters.Add(constructor2_database);
            CodeAssignStatement constructor2_assignment = new CodeAssignStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), class_field_connectionString.Name), new CodeSnippetExpression("string.Format(\"Server={0};Database={1};Trusted_Connection=True;\", server, database)"));
            constructor2.Statements.Add(constructor2_assignment);
            ctd.Members.Add(constructor2);

            // create constructor3 for our class with 4 args
            CodeConstructor constructor3 = new CodeConstructor();
            constructor3.Attributes = MemberAttributes.Public;
            CodeParameterDeclarationExpression constructor3_server = new CodeParameterDeclarationExpression(typeof(string), "server");
            CodeParameterDeclarationExpression constructor3_database = new CodeParameterDeclarationExpression(typeof(string), "database");
            CodeParameterDeclarationExpression constructor3_user = new CodeParameterDeclarationExpression(typeof(string), "user");
            CodeParameterDeclarationExpression constructor3_pass = new CodeParameterDeclarationExpression(typeof(string), "pass");
            constructor3.Parameters.Add(constructor3_server);
            constructor3.Parameters.Add(constructor3_database);
            constructor3.Parameters.Add(constructor3_user);
            constructor3.Parameters.Add(constructor3_pass);
            CodeAssignStatement constructor3_assignment = new CodeAssignStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), class_field_connectionString.Name), new CodeSnippetExpression("string.Format(\"Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False\", server, database,user,pass)"));
            constructor3.Statements.Add(constructor3_assignment);
            ctd.Members.Add(constructor3);
            #endregion

            #region Create some useful methods (GetData(SqlCommand cmd))
            // create method named GetData()
            CodeMemberMethod method_getData = new CodeMemberMethod();
            method_getData.Attributes = MemberAttributes.Public;
            method_getData.Name = "GetData";
            method_getData.ReturnType = new CodeTypeReference(typeof(DataTable));
            ctd.Members.Add(method_getData);

            // add a SqlCommand object as parameter in 'method_getData' method
            CodeParameterDeclarationExpression method_getData_p1 = new CodeParameterDeclarationExpression(typeof(System.Data.SqlClient.SqlCommand), "cmd");
            method_getData.Parameters.Add(method_getData_p1);

            // declare variable with type SqlConnection and assign value of field's property (ConnectionString) to it
            CodeVariableDeclarationStatement cvds_con_getData = new CodeVariableDeclarationStatement(typeof(System.Data.SqlClient.SqlConnection), "con");
            method_getData.Statements.Add(cvds_con_getData);
            CodeAssignStatement assignment_newConnection_getData = new CodeAssignStatement(new CodeVariableReferenceExpression(cvds_con_getData.Name), new CodeObjectCreateExpression(typeof(System.Data.SqlClient.SqlConnection), new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), propertyCon.Name)));
            method_getData.Statements.Add(assignment_newConnection_getData);

            // Assign con object to cmd.Connection property
            CodeAssignStatement assign_cmd_con_getData = new CodeAssignStatement(new CodePropertyReferenceExpression(new CodeVariableReferenceExpression(method_getData_p1.Name), "Connection"), new CodeVariableReferenceExpression(cvds_con_getData.Name));
            method_getData.Statements.Add(assign_cmd_con_getData);

            // decalre a SqlDataReader object to hold data
            CodeVariableDeclarationStatement cvds_dr_getData = new CodeVariableDeclarationStatement(typeof(System.Data.SqlClient.SqlDataReader), "dr");

            // declare a DataTable object to load data from SqlDataReader object
            CodeVariableDeclarationStatement cvds_dt_getData = new CodeVariableDeclarationStatement(typeof(System.Data.DataTable), "dt");
            CodeAssignStatement assignment_new_dt_getData = new CodeAssignStatement(new CodeVariableReferenceExpression(cvds_dt_getData.Name), new CodeObjectCreateExpression(typeof(System.Data.DataTable)));

            // open the SqlConnection object
            CodeMethodInvokeExpression invoke_con_open_getData = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(cvds_con_getData.Name), "Open");

            // run ExecuteReader method and pass resultset to SqlDataReader object
            CodeMethodInvokeExpression invoke_execute_reader_getData = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(method_getData_p1.Name), "ExecuteReader");
            CodeAssignStatement assignment_execReaderToReader_getData = new CodeAssignStatement(new CodeVariableReferenceExpression(cvds_dr_getData.Name), invoke_execute_reader_getData);

            CodeMethodInvokeExpression invoke_dt_load_getData = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(cvds_dt_getData.Name), "Load", new CodeVariableReferenceExpression(cvds_dr_getData.Name));

            // close SqlConnection object
            CodeMethodInvokeExpression invoke_con_close_getData = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(cvds_con_getData.Name), "Close");

            // return DataTable object
            CodeMethodReturnStatement return_method_getData = new CodeMethodReturnStatement(new CodeVariableReferenceExpression(cvds_dt_getData.Name));

            CodeTryCatchFinallyStatement tryCatch_method_getData = new CodeTryCatchFinallyStatement();
            tryCatch_method_getData.TryStatements.Add(invoke_con_open_getData);
            tryCatch_method_getData.TryStatements.Add(cvds_dr_getData);
            tryCatch_method_getData.TryStatements.Add(cvds_dt_getData);
            tryCatch_method_getData.TryStatements.Add(assignment_new_dt_getData);
            tryCatch_method_getData.TryStatements.Add(assignment_execReaderToReader_getData);
            tryCatch_method_getData.TryStatements.Add(invoke_dt_load_getData);
            tryCatch_method_getData.TryStatements.Add(invoke_con_close_getData);
            tryCatch_method_getData.TryStatements.Add(return_method_getData);

            // catch 1 for sql errors (Note : this catch clause must define first)
            CodeCatchClause catchSql_getData = new CodeCatchClause("ex", new CodeTypeReference("System.Data.SqlClient.SqlException"));