示例#1
0
        public static void OperationNonClusteredCoveringIndexesBridgeTables(DatabaseData dbs, string procNameTemplate, string procFileNameTemplate, bool returnAllData, string creator, string dateformat, bool questarSpecificDates, TableData.Operation currentOper, string temlateSQLScriptPath, string finalDir)
        {
            if (!Directory.Exists(finalDir + @"\" + currentOper.ToString().ToUpper()))
            {
                Directory.CreateDirectory(finalDir + @"\" + currentOper.ToString().ToUpper());
            }

            foreach (TableData tb in dbs.Tables)
            {
                foreach (ColumnData col in tb.Columns)
                {
                    if (col.isFK)
                    {
                        FileInfo      file            = new FileInfo(temlateSQLScriptPath);
                        StreamReader  SqlScriptReader = file.OpenText();
                        StringBuilder getAllScript    = new StringBuilder();
                        getAllScript.Append(SqlScriptReader.ReadToEnd());
                        string indexName    = procNameTemplate.Replace("!tblNAM3!", tb.TableName).Replace("!C0lumn!", col.ColumnName.ToString());
                        string procFileName = procFileNameTemplate.Replace("!tblNAM3!", tb.TableName).Replace("!C0lumn!", col.ColumnName.ToString());

                        getAllScript.Replace("!cr3ationDAT3!", DateTime.Now.ToString(dateformat.Replace("/", "\\/")));
                        getAllScript.Replace("!cr3ator!", creator);
                        getAllScript.Replace("!tblNAM3!", tb.TableName);

                        #region scriptCode
                        StringBuilder sqlScriptCode = new StringBuilder();
                        sqlScriptCode.Append(
                            "\n CREATE NONCLUSTERED INDEX [" + indexName + "] ON [dbo].[" + tb.TableName + "]                        "
                            + "\n (                                                                                                                              "
                            + "\n  [" + col.ColumnName + "]                                                                                                   "
                            + "\n )"
                            + "\nINCLUDE"
                            + "\n (");
                        bool firstAdded = false;
                        foreach (ColumnData colIncl in tb.Columns)
                        {
                            if (colIncl.isFK && colIncl.ColumnName != col.ColumnName)
                            {
                                sqlScriptCode.Append("\n " + (firstAdded == true ? "," : "") + " [" + colIncl.ColumnName + "]");
                                firstAdded = true;
                            }
                        }
                        sqlScriptCode.Append("\n )"
                                             + "\n GO"
                                             );
                        if (firstAdded)
                        {
                            getAllScript.Replace("!pr0cC0d3!", sqlScriptCode.ToString());
                            #endregion
                            CreateNewFile(finalDir + @"\" + currentOper.ToString().ToUpper() + @"\" + procFileName + ".sql", getAllScript);
                        }
                    }
                }
            }
        }
示例#2
0
        public static void OperationDelete(DatabaseData dbs, string procNameTemplate, string procFileNameTemplate, bool returnAllData, string creator, string dateformat, bool questarSpecificDates, TableData.Operation currentOper, string temlateSQLScriptPath, string finalDir)
        {
            if (!Directory.Exists(finalDir + "\\" + currentOper.ToString().ToUpper()))
            {
                Directory.CreateDirectory(finalDir + "\\" + currentOper.ToString().ToUpper());
            }

            foreach (TableData tb in dbs.Tables)
            {
                FileInfo      file            = new FileInfo(temlateSQLScriptPath);
                StreamReader  SqlScriptReader = file.OpenText();
                StringBuilder deleteScript    = new StringBuilder();
                deleteScript.Append(SqlScriptReader.ReadToEnd());

                string procName     = procNameTemplate.Replace("!tblNAM3!", tb.TableName).Replace("!Operati0n!", currentOper.ToString());
                string procFileName = procFileNameTemplate.Replace("!tblNAM3!", tb.TableName).Replace("!Operati0n!", currentOper.ToString());
                deleteScript.Replace("!pr0cNam3!", procName);
                deleteScript.Replace("!cr3ationDAT3!", DateTime.Now.ToString(dateformat.Replace("/", "\\/")));
                deleteScript.Replace("!cr3ator!", creator);
                deleteScript.Replace("!Operati0n!", currentOper.ToString().ToUpper());
                deleteScript.Replace("!tblNAM3!", tb.TableName);

                #region TestedStrings
                StringBuilder testedStrings = tb.TestedStrings(returnAllData, tb, procName, questarSpecificDates, TableData.Operation.Delete, null);
                deleteScript.Replace("!T3sTStrings!", testedStrings.ToString());
                #endregion

                #region scriptParameters
                StringBuilder parameters = tb.ParameterStrings(returnAllData, tb, procName, questarSpecificDates, TableData.Operation.Delete, null);
                deleteScript.Replace("!param3t3rs!", parameters.ToString());
                #endregion

                #region scriptCode
                StringBuilder sqlScriptCode = new StringBuilder();
                sqlScriptCode.Append("                 DELETE \n");
                sqlScriptCode.Append("                   FROM [dbo].[" + tb.TableName + "] \n");
                sqlScriptCode.Append("                  WHERE [" + tb.PrimaryKey + "] = @" + tb.PrimaryKey + "; \n");
                sqlScriptCode.Append("                 SELECT @@ROWCOUNT AS [RowCount];");
                deleteScript.Replace("!pr0cC0d3!", sqlScriptCode.ToString());
                #endregion
                CreateNewFile(finalDir + @"\" + currentOper.ToString().ToUpper() + @"\" + procFileName + ".sql", deleteScript);
            }
        }
示例#3
0
        public static void OperationInsert(DatabaseData dbs, string procNameTemplate, string procFileNameTemplate, bool returnAllData, string creator, string dateformat, bool questarSpecificDates, TableData.Operation currentOper, string temlateSQLScriptPath, string finalDir)
        {
            if (!Directory.Exists(finalDir + "\\" + currentOper.ToString().ToUpper()))
            {
                Directory.CreateDirectory(finalDir + "\\" + currentOper.ToString().ToUpper());
            }

            foreach (TableData tb in dbs.Tables)
            {
                FileInfo      file            = new FileInfo(temlateSQLScriptPath);
                StreamReader  SqlScriptReader = file.OpenText();
                StringBuilder insertScript    = new StringBuilder();
                insertScript.Append(SqlScriptReader.ReadToEnd());
                string procName     = procNameTemplate.Replace("!tblNAM3!", tb.TableName).Replace("!Operati0n!", currentOper.ToString());
                string procFileName = procFileNameTemplate.Replace("!tblNAM3!", tb.TableName).Replace("!Operati0n!", currentOper.ToString());
                insertScript.Replace("!pr0cNam3!", procName);
                insertScript.Replace("!cr3ationDAT3!", DateTime.Now.ToString(dateformat.Replace("/", "\\/")));
                insertScript.Replace("!cr3ator!", creator);
                insertScript.Replace("!Operati0n!", currentOper.ToString());
                insertScript.Replace("!tblNAM3!", tb.TableName);

                #region TestedStrings
                StringBuilder testedStrings = tb.TestedStrings(returnAllData, tb, procName, questarSpecificDates, TableData.Operation.Insert, dbs.ManagedInsertColumns);
                insertScript.Replace("!T3sTStrings!", testedStrings.ToString());
                #endregion

                #region scriptParameters
                StringBuilder parameters = tb.ParameterStrings(returnAllData, tb, procName, questarSpecificDates, TableData.Operation.Insert, dbs.ManagedInsertColumns);
                insertScript.Replace("!param3t3rs!", parameters.ToString());
                #endregion

                #region scriptCode
                StringBuilder sqlScriptCode = tb.SqlScriptCodeStrings(returnAllData, tb, procName, questarSpecificDates, TableData.Operation.Insert, dbs.ManagedInsertColumns);
                insertScript.Replace("!pr0cC0d3!", sqlScriptCode.ToString());
                #endregion

                //save file for table
                CreateNewFile(finalDir + @"\" + currentOper.ToString().ToUpper() + @"\" + procFileName + ".sql", insertScript);
            }
        }