frmmain.cs

数据库操作的小工具

            CodeMethodInvokeExpression error_showSql_getData = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression("System.Windows.Forms.MessageBox"), "Show");
            CodeSnippetExpression error_messageSql_getData = new CodeSnippetExpression("ex.Message");
            CodeVariableDeclarationStatement titleSqlVar1_getData = new CodeVariableDeclarationStatement(typeof(string), "errorTitle", new CodePrimitiveExpression("Error "));
            CodeVariableDeclarationStatement titleSqlVar2_getData = new CodeVariableDeclarationStatement(typeof(string), "numError", new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(catchSql_getData.LocalName), "Number.ToString"));
            CodeAssignStatement error_titleSqlAssignment_getData = new CodeAssignStatement(new CodeVariableReferenceExpression(titleSqlVar1_getData.Name), new CodeSnippetExpression(titleSqlVar1_getData.Name + " + " + titleSqlVar2_getData.Name));
            error_showSql_getData.Parameters.Add(error_messageSql_getData);
            error_showSql_getData.Parameters.Add(new CodeVariableReferenceExpression(titleSqlVar1_getData.Name));
            catchSql_getData.Statements.Add(titleSqlVar1_getData);
            catchSql_getData.Statements.Add(titleSqlVar2_getData);
            catchSql_getData.Statements.Add(error_titleSqlAssignment_getData);
            catchSql_getData.Statements.Add(error_showSql_getData);
            catchSql_getData.Statements.Add(new CodeSnippetExpression("return null"));
            tryCatch_method_getData.CatchClauses.Add(catchSql_getData);

            // catch 2 for general errors
            CodeCatchClause catchGeneral_getData = new CodeCatchClause("ex", new CodeTypeReference("System.Exception"));
            CodeMethodInvokeExpression error_showGeneral_getData = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression("System.Windows.Forms.MessageBox"), "Show");
            CodeSnippetExpression error_messageGeneral_getData = new CodeSnippetExpression("ex.Message");
            CodePrimitiveExpression error_titleGeneral_getData = new CodePrimitiveExpression("Error!");
            error_showGeneral_getData.Parameters.Add(error_messageGeneral_getData);
            error_showGeneral_getData.Parameters.Add(error_titleGeneral_getData);
            catchGeneral_getData.Statements.Add(error_showGeneral_getData);
            catchGeneral_getData.Statements.Add(new CodeSnippetExpression("return null"));
            tryCatch_method_getData.CatchClauses.Add(catchGeneral_getData);
            method_getData.Statements.Add(tryCatch_method_getData);
            #endregion

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

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

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

            // Assign con object to cmd.Connection property
            CodeAssignStatement assign_cmd_con_execNonQuery = new CodeAssignStatement(new CodePropertyReferenceExpression(new CodeVariableReferenceExpression(method_execNonQuery_p1.Name), "Connection"), new CodeVariableReferenceExpression(cvds_con_execNonQuery.Name));
            method_execNonQuery.Statements.Add(assign_cmd_con_execNonQuery);

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

            // run ExecuteNoneQuery method and pass resultset to our int variable
            CodeVariableDeclarationStatement output_execNonQuery = new CodeVariableDeclarationStatement(typeof(int), "output");
            CodeMethodInvokeExpression invoke_executeNoneQuery_execNonQuery = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(method_execNonQuery_p1.Name), "ExecuteNonQuery");
            CodeAssignStatement assignment_noneQueryToOutput_execNonQuery = new CodeAssignStatement(new CodeVariableReferenceExpression(output_execNonQuery.Name), invoke_executeNoneQuery_execNonQuery);

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

            // return DataTable object
            CodeMethodReturnStatement return_method_execNonQuery = new CodeMethodReturnStatement(new CodeVariableReferenceExpression(output_execNonQuery.Name));

            CodeTryCatchFinallyStatement tryCatch_method_execNonQuery = new CodeTryCatchFinallyStatement();
            tryCatch_method_execNonQuery.TryStatements.Add(output_execNonQuery);
            tryCatch_method_execNonQuery.TryStatements.Add(invoke_con_open_execNonQuery);
            tryCatch_method_execNonQuery.TryStatements.Add(assignment_noneQueryToOutput_execNonQuery);
            tryCatch_method_execNonQuery.TryStatements.Add(invoke_con_close_execNonQuery);
            tryCatch_method_execNonQuery.TryStatements.Add(return_method_execNonQuery);

            // catch 1 for sql errors (Note : this catch clause must define first)
            CodeCatchClause catchSql_execNonQuery = new CodeCatchClause("ex", new CodeTypeReference("System.Data.SqlClient.SqlException"));
            CodeMethodInvokeExpression error_showSql_execNonQuery = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression("System.Windows.Forms.MessageBox"), "Show");
            CodeSnippetExpression error_messageSql_execNonQuery = new CodeSnippetExpression("ex.Message");
            CodeVariableDeclarationStatement titleSqlVar1_execNonQuery = new CodeVariableDeclarationStatement(typeof(string), "errorTitle", new CodePrimitiveExpression("Error "));
            CodeVariableDeclarationStatement titleSqlVar2_execNonQuery = new CodeVariableDeclarationStatement(typeof(string), "numError", new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(catchSql_execNonQuery.LocalName), "Number.ToString"));
            CodeAssignStatement error_titleSqlAssignment_execNonQuery = new CodeAssignStatement(new CodeVariableReferenceExpression(titleSqlVar1_execNonQuery.Name), new CodeSnippetExpression(titleSqlVar1_execNonQuery.Name + " + " + titleSqlVar2_execNonQuery.Name));
            error_showSql_execNonQuery.Parameters.Add(error_messageSql_execNonQuery);
            error_showSql_execNonQuery.Parameters.Add(new CodeVariableReferenceExpression(titleSqlVar1_execNonQuery.Name));
            catchSql_execNonQuery.Statements.Add(titleSqlVar1_execNonQuery);
            catchSql_execNonQuery.Statements.Add(titleSqlVar2_execNonQuery);
            catchSql_execNonQuery.Statements.Add(error_titleSqlAssignment_execNonQuery);
            catchSql_execNonQuery.Statements.Add(error_showSql_execNonQuery);
            catchSql_execNonQuery.Statements.Add(new CodeSnippetExpression("return -1"));
            tryCatch_method_execNonQuery.CatchClauses.Add(catchSql_execNonQuery);

            // catch 2 for general errors
            CodeCatchClause catchGeneral_execNonQuery = new CodeCatchClause("ex", new CodeTypeReference("System.Exception"));
            CodeMethodInvokeExpression error_showGeneral_execNonQuery = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression("System.Windows.Forms.MessageBox"), "Show");
            CodeSnippetExpression error_messageGeneral_execNonQuery = new CodeSnippetExpression("ex.Message");
            CodePrimitiveExpression error_titleGeneral_execNonQuery = new CodePrimitiveExpression("Error!");
            error_showGeneral_execNonQuery.Parameters.Add(error_messageGeneral_execNonQuery);
            error_showGeneral_execNonQuery.Parameters.Add(error_titleGeneral_execNonQuery);
            catchGeneral_execNonQuery.Statements.Add(error_showGeneral_execNonQuery);
            catchGeneral_execNonQuery.Statements.Add(new CodeSnippetExpression("return -1"));
            tryCatch_method_execNonQuery.CatchClauses.Add(catchGeneral_execNonQuery);
            method_execNonQuery.Statements.Add(tryCatch_method_execNonQuery);
            #endregion

            #region Retrieve storedProcedure(s) and create method for it
            // create new DataTable object (dtHelper) to import unique records from dtSPs
            // because in dtSPs, each stored Procedure + parameter has a single record, therefore, each sp that has a multiple parameters
            // cause multiple rows insert into dtSPs object
            DataTable dtHelper = this.dtSPs.Clone();
            dtHelper.PrimaryKey = new DataColumn[] { dtHelper.Columns[0] };
            foreach (DataRow row in this.dtSPs.Rows)
            {
                if (!dtHelper.Rows.Contains(row["SpName"]))
                {
                    dtHelper.ImportRow(row);
                    DataRow[] rowCol = this.dtSPs.Select("SpName = '" + row["SpName"].ToString() + "'");
                    StoredProcedureParameterCollection parameterCollection = new StoredProcedureParameterCollection();
                    foreach (DataRow rowParameter in rowCol)
                    {
                        StoredProcedureParameter parameter = new StoredProcedureParameter();
                        parameter.ParameterName = rowParameter["ParameterName"].ToString();
                        parameter.ParameterType = rowParameter["SystemType"].ToString();
                        parameter.ParameterLength = rowParameter["Length"].ToString() != "NULL" ? int.Parse(rowParameter["Length"].ToString()) : -1;
                        if (rowParameter["IsOutputParameter"].ToString() != "NULL")
                        {
                            if (rowParameter["IsOutputParameter"].ToString() == "1")
                                parameter.IsParameterOutput = true;
                            else
                                parameter.IsParameterOutput = false;
                        }
                        else
                        {
                            parameter.IsParameterOutput = false;
                        }
                        parameterCollection.Add(parameter);
                    }

                    this.PrintToLog(string.Format("Generating method for {0} ...", row["SpName"]));
                    System.Windows.Forms.Application.DoEvents();
                    // create method for each storedProcedure in this loop and pass parameters to this method as StoredProcedureParameterCollection object
                    this.CreateMethodForExecuteSP(ctd, row["SpName"].ToString(), strConnectionString, parameterCollection);
                }
            }
            #endregion

            // define language provider and generate code
            CodeDomProvider provider;
            if (languageProvider == Providers.CsProvider)
            {
                // output is in csharp
                provider = new CSharpCodeProvider();
            }
            else
            {
                // output is in viusal basic
                provider = new VBCodeProvider();
            }
            // finally, generate our code to specified codeProvider
            this.GenerateCode(provider, compileUnit, this.txtSaveTo.Text + "\\" + Class);

            this.PrintToLog(string.Format("Code generation successfully completed for {0} Stored Procedure(s) in Database : {1}", dtHelper.Rows.Count, NameSpace));
        }

        // create document code for each stored procedure that retrieve from database
        private void CreateMethodForExecuteSP(CodeTypeDeclaration ctd, string spName, string connectionString, StoredProcedureParameterCollection parameterCollection)
        {
            StoredProcedureParameter outPutParameterInfo = null;
            CodeVariableDeclarationStatement commandOutputParameterInfo = null;
            CodeAssignStatement assignReturnedValueToOutputParameter = null;

            // declaration method
            CodeMemberMethod method = new CodeMemberMethod();
            method.Attributes = MemberAttributes.Public;
            method.ReturnType = new CodeTypeReference(typeof(System.Data.DataTable));
            method.Name = spName;

            // declare variable with type SqlConnection and assign value of field's property (ConnectionString) to it
            CodeVariableDeclarationStatement cvds_con = new CodeVariableDeclarationStatement(typeof(System.Data.SqlClient.SqlConnection), "con");
            CodeAssignStatement assignment_new_con = new CodeAssignStatement(new CodeVariableReferenceExpression(cvds_con.Name), new CodeObjectCreateExpression(typeof(System.Data.SqlClient.SqlConnection), new CodePropertyReferenceExpression(new CodeThisReferenceExpression(), "ConnectionString")));

            // declare variable with type SqlCommand and assign value of cvds_con to SqlCommand.Connection property
            CodeVariableDeclarationStatement cvds_cmd = new CodeVariableDeclarationStatement(typeof(System.Data.SqlClient.SqlCommand), "cmd");
            CodeAssignStatement assignment_new_cmd = new CodeAssignStatement(new CodeVariableReferenceExpression(cvds_cmd.Name), new CodeObjectCreateExpression(typeof(System.Data.SqlClient.SqlCommand)));
            CodeAssignStatement assignment_cmd_commandText = new CodeAssignStatement(new CodePropertyReferenceExpression(new CodeVariableReferenceExpression(cvds_cmd.Name), "CommandText"), new CodePrimitiveExpression(spName));
            CodeAssignStatement assignment_cmd_commandType = new CodeAssignStatement(new CodePropertyReferenceExpression(new CodeVariableReferenceExpression(cvds_cmd.Name), "CommandType"), new CodeSnippetExpression("CommandType.StoredProcedure"));

            // Assign con object to cmd.Connection property
            CodeAssignStatement assign_cmd_con = new CodeAssignStatement(new CodePropertyReferenceExpression(new CodeVariableReferenceExpression(cvds_cmd.Name), "Connection"), new CodeVariableReferenceExpression(cvds_con.Name));

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

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

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

            // run ExecuteReader method and pass resultset to SqlDataReader object
            CodeMethodInvokeExpression invoke_execute_reader = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(cvds_cmd.Name), "ExecuteReader");
            CodeAssignStatement assignment_dr_cmdExecute = new CodeAssignStatement(new CodeVariableReferenceExpression(cvds_dr.Name), invoke_execute_reader);

            // load data from SqlDataReader to DataTable object
            CodeMethodInvokeExpression dt_load = new CodeMethodInvokeExpression(new CodeVariableReferenceExpression(cvds_dt.Name), "Load", new CodeVariableReferenceExpression(cvds_dr.Name));

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

            // return DataTable object
            CodeMethodReturnStatement return_method = new CodeMethodReturnStatement(new CodeVariableReferenceExpression(cvds_dt.Name));

            // insert our method statements into TryCatch clause
            CodeTryCatchFinallyStatement tryCatch_method = new CodeTryCatchFinallyStatement();

            tryCatch_method.TryStatements.Add(cvds_con);
            tryCatch_method.TryStatements.Ad