コード例 #1
0
        void Insert_GenerateSQLQuery(string DBType, string TableName, string SPName,Boolean refreshGrid)
        {
            try
            {

                if (this.isQueryExist(TableName, SPName) == true)
                {
                    return;
                }

                string HeaderComments = "-- ======================================================" + Environment.NewLine +
                                        "-- Name: " + SPName + Environment.NewLine +
                                        "-- Created By: " + this.ToolName + Environment.NewLine +
                                        "-- Author: " + this.Author + Environment.NewLine +
                                        "-- Created At: " + DateTime.Now.ToString(this.DateTimeFormat) + Environment.NewLine +
                                        "-- Updated At: " + DateTime.Now.ToString(this.DateTimeFormat) + Environment.NewLine +
                                        "-- Description : Insert" + Environment.NewLine +
                                        "-- ======================================================";
                string QueryContent = "";
                DataRowView myRows;
                int ColumnsNo = 0;
                string inputerParameters = "";
                string ColumnsName = "";
                string ColumnsValues = "";
                string OutputQuery = "";
                Boolean isIdentityInserted = false;

                SolutionsDBQueryColumnsDAL SolutionsDBQueryColumnsDAL = new SolutionsDBQueryColumnsDAL();

                SolutionsDBQueryColumnsDAL.QueryId = 0;
                SolutionsDBQueryColumnsDAL.SolutionsDBID = this.CurrentDBID;
                SolutionsDBQueryColumnsDAL.QueryName = SPName;
                SolutionsDBQueryColumnsDAL.TableId=0;
                SolutionsDBQueryColumnsDAL.TableName = TableName;
                SolutionsDBQueryColumnsDAL.isActive = true;

                if (DBType.ToLower() == "my sql")
                {
                    #region "my sql"
                    foreach (DataGridViewRow myRow in this.dvgTableColumns.Rows)
                    {
                        DataGridViewCheckBoxCell myCell = (DataGridViewCheckBoxCell)myRow.Cells["clmSelect"];

                        if (myCell.Value.ToString() == "1")
                        {
                            myRows = (DataRowView)myRow.DataBoundItem;

                            if (Boolean.Parse(myRows["isIdentity"].ToString()) != true)
                            {
                                if (ColumnsNo == 0)
                                {
                                    inputerParameters = "   in " + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString(); ;
                                    ColumnsName = myRows["ColumnName"].ToString();
                                    ColumnsValues = myRows["ColumnName"].ToString();
                                }
                                else
                                {
                                    inputerParameters = inputerParameters + " , " + Environment.NewLine + "   in " + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString();// +Environment.NewLine;

                                    if (ColumnsNo % 5 == 0)
                                    {
                                        ColumnsName = ColumnsName + "," + Environment.NewLine + "                " + myRows["ColumnName"].ToString();
                                        ColumnsValues = ColumnsValues + "," + Environment.NewLine + "               " + myRows["ColumnName"].ToString();
                                    }
                                    else
                                    {
                                        if (isIdentityInserted == false)
                                        {
                                            isIdentityInserted = true;
                                            ColumnsName = myRows["ColumnName"].ToString();
                                            ColumnsValues = myRows["ColumnName"].ToString();
                                        }
                                        else
                                        {
                                            ColumnsName = ColumnsName + " , " + myRows["ColumnName"].ToString();
                                            ColumnsValues = ColumnsValues + " , " + myRows["ColumnName"].ToString();
                                        }
                                    }
                                }
                            }
                            else
                            {
                                if (ColumnsNo == 0)
                                {
                                    inputerParameters = "   out " + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString();// +Environment.NewLine;
                                    OutputQuery = " Set " + myRows["ColumnName"].ToString() + " = LAST_INSERT_ID();";
                                }
                                else
                                {
                                    inputerParameters = inputerParameters + " , " + Environment.NewLine + "   out " + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString();// +Environment.NewLine;
                                    OutputQuery = " Set " + myRows["ColumnName"].ToString() + " = LAST_INSERT_ID();";
                                }
                            }

                            ColumnsNo++;

                            SolutionsDBQueryColumnsDAL.ColumnName = myRows["ColumnName"].ToString();
                            SolutionsDBQueryColumnsDAL.ColumnType = myRows["ColumnType"].ToString();
                            SolutionsDBQueryColumnsDAL.ColumnDataType = myRows["ColumnDataType"].ToString();
                            SolutionsDBQueryColumnsDAL.DataType = myRows["DataType"].ToString();
                            SolutionsDBQueryColumnsDAL.COLUMN_KEY = myRows["COLUMN_KEY"].ToString();
                            SolutionsDBQueryColumnsDAL.Insert();

                        }

                    }

                    QueryContent = HeaderComments + Environment.NewLine + Environment.NewLine + Environment.NewLine +
                                                  " DELIMITER $$ " + Environment.NewLine +
                                                  " Create procedure " + SPName + " (" + Environment.NewLine +
                                                  inputerParameters + Environment.NewLine +
                                                  ") " + Environment.NewLine +
                                                  " Begin " + Environment.NewLine +
                                                  "     Insert into " + TableName + Environment.NewLine +
                                                  "         ( " + Environment.NewLine + "              " + ColumnsName + Environment.NewLine + "           )" + Environment.NewLine +
                                                  "         Values " + Environment.NewLine +
                                                  "         ( " + Environment.NewLine + "               " + ColumnsValues + Environment.NewLine + "         ); " + Environment.NewLine +
                                                  "     " + OutputQuery + "" + Environment.NewLine +
                                                  " END$$ " + Environment.NewLine +
                                                  " DELIMITER ;";

                    #endregion
                }
                else if (DBType.ToLower() == "ms sql")
                {
                    #region "Ms sql"
                    foreach (DataGridViewRow myRow in this.dvgTableColumns.Rows)
                    {
                        DataGridViewCheckBoxCell myCell = (DataGridViewCheckBoxCell)myRow.Cells["clmSelect"];

                        if (myCell.Value.ToString() == "1")
                        {
                            myRows = (DataRowView)myRow.DataBoundItem;

                            if (Boolean.Parse(myRows["isIdentity"].ToString()) != true)
                            {
                                if (ColumnsNo == 0)
                                {
                                    inputerParameters = "   @" + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString();
                                    ColumnsName = myRows["ColumnName"].ToString();
                                    ColumnsValues = "@" + myRows["ColumnName"].ToString();
                                }
                                else
                                {
                                    inputerParameters = inputerParameters + " , " + Environment.NewLine + "   @" + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString();// +Environment.NewLine;

                                    if (ColumnsNo % 5 == 0)
                                    {
                                        ColumnsName = ColumnsName + "," + Environment.NewLine + "                " + myRows["ColumnName"].ToString();
                                        ColumnsValues = ColumnsValues + "," + Environment.NewLine + "               " + "@" + myRows["ColumnName"].ToString();
                                    }
                                    else
                                    {
                                        if (isIdentityInserted == false)
                                        {
                                            isIdentityInserted = true;
                                            ColumnsName = myRows["ColumnName"].ToString();
                                            ColumnsValues = "@" + myRows["ColumnName"].ToString();
                                        }
                                        else
                                        {
                                            ColumnsName = ColumnsName + " , " + myRows["ColumnName"].ToString();
                                            ColumnsValues = ColumnsValues + " , " + "@" + myRows["ColumnName"].ToString();
                                        }
                                    }
                                }

                                ColumnsNo++;
                            }
                            else
                            {
                                if (ColumnsNo == 0)
                                {
                                    //inputerParameters = "   out " + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString() + Environment.NewLine;
                                    OutputQuery = " Select @@Identity;";
                                }
                                else
                                {
                                    //inputerParameters = inputerParameters + " , " + "   out " + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString() + Environment.NewLine;
                                    OutputQuery = " Select @@Identity;";
                                }
                            }

                            SolutionsDBQueryColumnsDAL.ColumnName = myRows["ColumnName"].ToString();
                            SolutionsDBQueryColumnsDAL.ColumnType = myRows["ColumnType"].ToString();
                            SolutionsDBQueryColumnsDAL.ColumnDataType = myRows["ColumnDataType"].ToString();
                            SolutionsDBQueryColumnsDAL.DataType = myRows["DataType"].ToString();
                            SolutionsDBQueryColumnsDAL.COLUMN_KEY = myRows["COLUMN_KEY"].ToString();
                            SolutionsDBQueryColumnsDAL.Insert();

                        }

                    }

                    //HeaderComments = " SET ANSI_NULLS ON " + Environment.NewLine +
                    //                 "   GO " + Environment.NewLine +
                    //                 "   SET QUOTED_IDENTIFIER ON " + Environment.NewLine +
                    //                 "   GO" + Environment.NewLine + Environment.NewLine +
                    //                 HeaderComments;

                    QueryContent = HeaderComments + Environment.NewLine + Environment.NewLine + Environment.NewLine +
                                                  " Create procedure " + SPName + " (" + Environment.NewLine +
                                                  inputerParameters + Environment.NewLine +
                                                  ") " + Environment.NewLine +
                                                  "AS " + Environment.NewLine +
                                                  " Begin " + Environment.NewLine + Environment.NewLine + Environment.NewLine +
                                                  "     SET NOCOUNT ON; " + Environment.NewLine +
                                                  "     Insert into " + TableName + Environment.NewLine +
                                                  "         ( " + Environment.NewLine + "              " + ColumnsName + Environment.NewLine + "           )" + Environment.NewLine +
                                                  "         Values " + Environment.NewLine +
                                                  "         ( " + Environment.NewLine + "               " + ColumnsValues + Environment.NewLine + "         ); " + Environment.NewLine +
                                                  "     " + OutputQuery + "" + Environment.NewLine +
                                                  " END " + Environment.NewLine;

                    #endregion
                }
                this.Insert_SolutionsDBQuery(TableName, SPName, "Insert", "Store Procedure", QueryContent,refreshGrid);
            }
            catch (Exception ex)
            {
                CommonClasses.Messages.GeneralError(ex.Message, " Insert Query");
            }
        }
コード例 #2
0
        void GetList_GenerateSQLQuery(string DBType, string TableName, string SPName, Boolean refreshGrid)
        {
            try
            {

                if (this.isQueryExist(TableName, SPName) == true)
                {
                    return;
                }

                string HeaderComments = "/*" + Environment.NewLine +
                                        "Name: " + SPName + Environment.NewLine +
                                        "Created By: " + this.ToolName + Environment.NewLine +
                                        "Author: " + this.Author + Environment.NewLine +
                                        "Created At: " + DateTime.Now.ToString(this.DateTimeFormat) + Environment.NewLine +
                                        "Updated At: " + DateTime.Now.ToString(this.DateTimeFormat) + Environment.NewLine +
                                        "Description : Get List" + Environment.NewLine +
                                        "*/";
                string QueryContent = "";
                DataRowView myRows;
                int ColumnsNo = 0;
                string inputerParameters = "";
                string SelectedColumns = "";
                string OutputQuery = "";
                string myWhereCondition = "";
                Boolean isIdentityInserted = false;

                SolutionsDBQueryColumnsDAL SolutionsDBQueryColumnsDAL = new SolutionsDBQueryColumnsDAL();

                SolutionsDBQueryColumnsDAL.QueryId = 0;
                SolutionsDBQueryColumnsDAL.SolutionsDBID = this.CurrentDBID;
                SolutionsDBQueryColumnsDAL.QueryName = SPName;
                SolutionsDBQueryColumnsDAL.TableId = 0;
                SolutionsDBQueryColumnsDAL.TableName = TableName;
                SolutionsDBQueryColumnsDAL.isActive = true;

                if (DBType.ToLower() == "my sql")
                {
                    #region "My Sql"

                    foreach (DataGridViewRow myRow in this.dvgTableColumns.Rows)
                    {
                        DataGridViewCheckBoxCell myCell = (DataGridViewCheckBoxCell)myRow.Cells["clmSelect"];

                        if (myCell.Value.ToString() == "1")
                        {
                            myRows = (DataRowView)myRow.DataBoundItem;

                            if (Boolean.Parse(myRows["isIdentity"].ToString()) != true)
                            {
                                if (ColumnsNo == 0)
                                {

                                    SelectedColumns = myRows["ColumnName"].ToString();
                                }
                                else
                                {

                                    if (ColumnsNo % 5 == 0)
                                    {
                                        SelectedColumns = SelectedColumns + "," + Environment.NewLine + "                " + myRows["ColumnName"].ToString();
                                    }
                                    else
                                    {
                                        if (isIdentityInserted == false)
                                        {
                                            isIdentityInserted = true;
                                            SelectedColumns = myRows["ColumnName"].ToString(); ;
                                        }
                                        else
                                        {
                                            SelectedColumns = SelectedColumns + " , " + myRows["ColumnName"].ToString(); ;
                                        }
                                    }
                                }
                            }
                            else
                            {
                                isIdentityInserted = true;
                                if (ColumnsNo == 0)
                                {
                                    SelectedColumns = myRows["ColumnName"].ToString();
                                    inputerParameters = "   in org_" + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString() + Environment.NewLine;
                                    OutputQuery = " select 1 as ReturnValue; ";// +myRows["ColumnName"].ToString() + " = LAST_INSERT_ID();";
                                    myWhereCondition = "(org_" + myRows["ColumnName"].ToString() + " = 0 Or " + myRows["ColumnName"].ToString() + " = org_" + myRows["ColumnName"].ToString() + ")";
                                }
                                else
                                {
                                    SelectedColumns = SelectedColumns + " , " + myRows["ColumnName"].ToString();
                                    inputerParameters = inputerParameters + " , " + "   in org_" + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString() + Environment.NewLine;
                                    myWhereCondition = "(org_" + myRows["ColumnName"].ToString() + " = 0 Or " + myRows["ColumnName"].ToString() + " = org_" + myRows["ColumnName"].ToString() + ")";
                                }

                            }

                            ColumnsNo++;

                            SolutionsDBQueryColumnsDAL.ColumnName = myRows["ColumnName"].ToString();
                            SolutionsDBQueryColumnsDAL.ColumnType = myRows["ColumnType"].ToString();
                            SolutionsDBQueryColumnsDAL.ColumnDataType = myRows["ColumnDataType"].ToString();
                            SolutionsDBQueryColumnsDAL.DataType = myRows["DataType"].ToString();
                            SolutionsDBQueryColumnsDAL.COLUMN_KEY = myRows["COLUMN_KEY"].ToString();
                            SolutionsDBQueryColumnsDAL.Insert();

                        }

                    }

                    QueryContent = HeaderComments + Environment.NewLine + Environment.NewLine + Environment.NewLine +
                                                  " DELIMITER $$ " + Environment.NewLine +
                                                  " Create procedure " + SPName + " (" + Environment.NewLine +
                                                  inputerParameters + Environment.NewLine +
                                                  ") " + Environment.NewLine +
                                                  " Begin " + Environment.NewLine +
                                                  "     Select " + SelectedColumns + Environment.NewLine +
                                                  "     From       " + TableName + " " + Environment.NewLine +
                                                  "     Where " + myWhereCondition + " ;" + Environment.NewLine +
                                                  " END$$ " + Environment.NewLine +
                                                  " DELIMITER ;";

                    #endregion
                }
                else if (DBType.ToLower() == "ms sql")
                {
                    #region "Ms Sql"

                    foreach (DataGridViewRow myRow in this.dvgTableColumns.Rows)
                    {
                        DataGridViewCheckBoxCell myCell = (DataGridViewCheckBoxCell)myRow.Cells["clmSelect"];

                        if (myCell.Value.ToString() == "1")
                        {
                            myRows = (DataRowView)myRow.DataBoundItem;

                            if (Boolean.Parse(myRows["isIdentity"].ToString()) != true)
                            {
                                if (ColumnsNo == 0)
                                {

                                    SelectedColumns = myRows["ColumnName"].ToString();
                                }
                                else
                                {

                                    if (ColumnsNo % 5 == 0)
                                    {
                                        SelectedColumns = SelectedColumns + "," + Environment.NewLine + "                " + myRows["ColumnName"].ToString();
                                    }
                                    else
                                    {
                                        if (isIdentityInserted == false)
                                        {
                                            isIdentityInserted = true;
                                            SelectedColumns = myRows["ColumnName"].ToString(); ;
                                        }
                                        else
                                        {
                                            SelectedColumns = SelectedColumns + " , " + myRows["ColumnName"].ToString(); ;
                                        }
                                    }
                                }
                            }
                            else
                            {
                                isIdentityInserted = true;
                                if (ColumnsNo == 0)
                                {
                                    SelectedColumns = myRows["ColumnName"].ToString();
                                    inputerParameters = "   @org_" + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString() + Environment.NewLine;
                                    OutputQuery = " select 1 as ReturnValue; ";// +myRows["ColumnName"].ToString() + " = LAST_INSERT_ID();";
                                    myWhereCondition = "(@org_" + myRows["ColumnName"].ToString() + " = 0 Or " + myRows["ColumnName"].ToString() + " = @org_" + myRows["ColumnName"].ToString() + ")";
                                }
                                else
                                {
                                    SelectedColumns = SelectedColumns + " , " + myRows["ColumnName"].ToString();
                                    inputerParameters = inputerParameters + " , " + "   @org_" + myRows["ColumnName"].ToString() + " " + myRows["ColumnType"].ToString() + Environment.NewLine;
                                    myWhereCondition = "(@org_" + myRows["ColumnName"].ToString() + " = 0 Or " + myRows["ColumnName"].ToString() + " = @org_" + myRows["ColumnName"].ToString() + ")";
                                }

                            }

                            ColumnsNo++;

                            SolutionsDBQueryColumnsDAL.ColumnName = myRows["ColumnName"].ToString();
                            SolutionsDBQueryColumnsDAL.ColumnType = myRows["ColumnType"].ToString();
                            SolutionsDBQueryColumnsDAL.ColumnDataType = myRows["ColumnDataType"].ToString();
                            SolutionsDBQueryColumnsDAL.DataType = myRows["DataType"].ToString();
                            SolutionsDBQueryColumnsDAL.COLUMN_KEY = myRows["COLUMN_KEY"].ToString();
                            SolutionsDBQueryColumnsDAL.Insert();

                        }

                    }

                    QueryContent = HeaderComments + Environment.NewLine + Environment.NewLine + Environment.NewLine +
                                                  " Create procedure " + SPName + " (" + Environment.NewLine +
                                                  inputerParameters + Environment.NewLine +
                                                  ") " + Environment.NewLine +
                                                  " AS " + Environment.NewLine +
                                                  " Begin " + Environment.NewLine +
                                                  "     Select " + SelectedColumns + Environment.NewLine +
                                                  "     From       " + TableName + " " + Environment.NewLine +
                                                  "     Where " + myWhereCondition + " ;" + Environment.NewLine +
                                                  " END " + Environment.NewLine;

                    #endregion
                }

                this.Insert_SolutionsDBQuery(TableName, SPName, "Get", "Store Procedure", QueryContent,refreshGrid);

            }
            catch (Exception ex)
            {
                CommonClasses.Messages.GeneralError(ex.Message, " Get Query");
            }
        }