Exemple #1
0
        public override void Emit(XPathNavigator patternNavigator)
        {
            if (patternNavigator != null)
            {
                string firstOrLast = patternNavigator.SelectSingleNode("@FirstOrLast").Value;
                string status = patternNavigator.SelectSingleNode("@Status").Value;
                string notes = patternNavigator.SelectSingleNode("@Notes").Value;

                Connection tableConnection =
                    Connection.GetExistingConnection(VulcanPackage, LogtainerPattern.CurrentLog.TableConnectionName);

                string execSqlTaskName = LogtainerPattern.CurrentLog.LogName + Resources.Seperator + firstOrLast + Guid.NewGuid();
                TemplateEmitter te = new TemplateEmitter(VulcanPackage.TemplateManager["LogSelectQuery"]);
                if (
                    LogtainerPattern.CurrentLog.SourceColumn == null ||
                    LogtainerPattern.CurrentLog.DestinationColumn == null ||
                    LogtainerPattern.CurrentLog.TableConnectionName == null ||
                    LogtainerPattern.CurrentLog.Table == null)
                {
                    Message.Trace(Severity.Error,
                        "Could not perform LogUpdate (On Log: {0}), Parent Logtainer does not contain all of the necessary information.  Needs SourceColumn, DestinationColumn, TableConnectionName, and Table attributes.",LogtainerPattern.CurrentLog.LogName);
                    return;
                }

                te.SetNamedParameter("Source", LogtainerPattern.CurrentLog.SourceColumn);
                te.SetNamedParameter("Destination", LogtainerPattern.CurrentLog.DestinationColumn);
                te.SetNamedParameter("Table", LogtainerPattern.CurrentLog.Table);
                te.SetNamedParameter("Status", status);
                te.SetNamedParameter("Notes", notes);
                te.SetNamedParameter("SourceConvertStyle", "21");
                te.SetNamedParameter("DestinationConvertStyle", "21");

                string query;
                te.Emit(out query);

                SQLTask readForLogTask = new SQLTask(VulcanPackage, execSqlTaskName, execSqlTaskName, ParentContainer, tableConnection);
                readForLogTask.TransmuteToExpressionTask(String.Format("\"{0}\"", query));
                readForLogTask.ExecuteSQLTask.ResultSetType = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ResultSetType.ResultSetType_SingleRow;

                DTS.Variable sourceVar = LogtainerPattern.CurrentLog[firstOrLast+"SourceRecord"];
                DTS.Variable destVar = LogtainerPattern.CurrentLog[firstOrLast+"DestinationRecord"];
                DTS.Variable statusVar = LogtainerPattern.CurrentLog["Status"];
                DTS.Variable notesVar = LogtainerPattern.CurrentLog["Notes"];

                readForLogTask.BindResult("0", sourceVar.QualifiedName);
                readForLogTask.BindResult("1", destVar.QualifiedName);
                readForLogTask.BindResult("2", statusVar.QualifiedName);
                readForLogTask.BindResult("3", notesVar.QualifiedName);

                this.FirstExecutableGeneratedByPattern = readForLogTask.SQLTaskHost;
                this.LastExecutableGeneratedByPattern = this.FirstExecutableGeneratedByPattern;
            }
        }
Exemple #2
0
        public override void Emit(TextWriter outputWriter)
        {
            StringBuilder columnBuilder = new StringBuilder();


            foreach (string s in _columnList)
            {
                columnBuilder.AppendLine(s);
            }
            columnBuilder.Replace(",", "", columnBuilder.Length - 3,3);

            TemplateEmitter te = new TemplateEmitter("StoredProc", VulcanPackage, _procName, columnBuilder.ToString(), _body);
            te.Emit(outputWriter);
        }
Exemple #3
0
        public override void Emit(TextWriter outputWriter)
        {
            StringBuilder columnBuilder = new StringBuilder();


            foreach (string s in _columnList)
            {
                columnBuilder.AppendLine(s);
            }
            columnBuilder.Replace(",", "", columnBuilder.Length - 3, 3);

            TemplateEmitter te = new TemplateEmitter("StoredProc", VulcanPackage, _procName, columnBuilder.ToString(), _body);

            te.Emit(outputWriter);
        }
        public override void Emit(TextWriter outputWriter)
        {

            if(TableNavigator.Select("rc:DefaultValues/rc:Value",VulcanPackage.VulcanConfig.NamespaceManager).Count == 0)
            {
                outputWriter.Flush();
                return;
            }

            StringBuilder columnBuilder = new StringBuilder();

            bool containsIdentities = _tableHelper.KeyColumnType == KeyColumnType.Identity;
            foreach (XPathNavigator nav in TableNavigator.Select("rc:Columns/rc:Column", VulcanPackage.VulcanConfig.NamespaceManager))
            {
                /* Build Column List */
                string columnName = nav.SelectSingleNode("@Name", VulcanPackage.VulcanConfig.NamespaceManager).Value;

                columnBuilder.AppendFormat(
                     "[{0}],",
                     columnName
                 );
            }

            columnBuilder.Remove(columnBuilder.Length - 1, 1);

            if (containsIdentities)
            {
                outputWriter.Write("\n");
                outputWriter.Write(String.Format(System.Globalization.CultureInfo.InvariantCulture,"\nSET IDENTITY_INSERT {0} ON\n", TableName));
            }
            TemplateEmitter te = new TemplateEmitter("InsertDefaultValues",VulcanPackage,null);

            outputWriter.Write("\n");
            foreach (XPathNavigator nav in TableNavigator.Select("rc:DefaultValues/rc:Value", VulcanPackage.VulcanConfig.NamespaceManager))
            {
                te.SetParameters(TableName, columnBuilder.ToString(),nav.Value);
                te.Emit(outputWriter);
                outputWriter.Write("\n"); ;
            }

            if (containsIdentities)
            {
                outputWriter.Write(String.Format(System.Globalization.CultureInfo.InvariantCulture,"\nSET IDENTITY_INSERT {0} OFF", TableName));
            }

            outputWriter.Write("\nGO\n");
            outputWriter.Flush();
        }
        public override void Emit(TextWriter outputWriter)
        {
            if (TableNavigator.Select("rc:DefaultValues/rc:Value", VulcanPackage.VulcanConfig.NamespaceManager).Count == 0)
            {
                outputWriter.Flush();
                return;
            }

            StringBuilder columnBuilder = new StringBuilder();

            bool containsIdentities = _tableHelper.KeyColumnType == KeyColumnType.Identity;

            foreach (XPathNavigator nav in TableNavigator.Select("rc:Columns/rc:Column", VulcanPackage.VulcanConfig.NamespaceManager))
            {
                /* Build Column List */
                string columnName = nav.SelectSingleNode("@Name", VulcanPackage.VulcanConfig.NamespaceManager).Value;

                columnBuilder.AppendFormat(
                    "[{0}],",
                    columnName
                    );
            }

            columnBuilder.Remove(columnBuilder.Length - 1, 1);

            if (containsIdentities)
            {
                outputWriter.Write("\n");
                outputWriter.Write(String.Format(System.Globalization.CultureInfo.InvariantCulture, "\nSET IDENTITY_INSERT {0} ON\n", TableName));
            }
            TemplateEmitter te = new TemplateEmitter("InsertDefaultValues", VulcanPackage, null);

            outputWriter.Write("\n");
            foreach (XPathNavigator nav in TableNavigator.Select("rc:DefaultValues/rc:Value", VulcanPackage.VulcanConfig.NamespaceManager))
            {
                te.SetParameters(TableName, columnBuilder.ToString(), nav.Value);
                te.Emit(outputWriter);
                outputWriter.Write("\n");;
            }

            if (containsIdentities)
            {
                outputWriter.Write(String.Format(System.Globalization.CultureInfo.InvariantCulture, "\nSET IDENTITY_INSERT {0} OFF", TableName));
            }

            outputWriter.Write("\nGO\n");
            outputWriter.Flush();
        }
Exemple #6
0
        public override void Emit(TextWriter tw)
        {
            string sqlStatement;

            TemplateEmitter sqlTemplate = new TemplateEmitter("SimpleSelect", VulcanPackage, columns, tableName);
            sqlTemplate.Emit(out sqlStatement);

            if (where != null)
            {
                string whereStatement;
                TemplateEmitter whereTemplate = new TemplateEmitter("SimpleWhere",VulcanPackage,where);

                whereTemplate.Emit(out whereStatement);
                sqlStatement += whereStatement;
            }

            tw.WriteLine(sqlStatement);
            tw.Flush();
        }
Exemple #7
0
        public override void Emit(TextWriter tw)
        {
            string sqlStatement;

            TemplateEmitter sqlTemplate = new TemplateEmitter("SimpleSelect", VulcanPackage, columns, tableName);

            sqlTemplate.Emit(out sqlStatement);

            if (where != null)
            {
                string          whereStatement;
                TemplateEmitter whereTemplate = new TemplateEmitter("SimpleWhere", VulcanPackage, where);

                whereTemplate.Emit(out whereStatement);
                sqlStatement += whereStatement;
            }

            tw.WriteLine(sqlStatement);
            tw.Flush();
        }
Exemple #8
0
        public override void Emit(TextWriter outputWriter)
        {
            TemplateEmitter te = new TemplateEmitter("CreateIndex", VulcanPackage, null);

            foreach (XPathNavigator nav in TableNavigator.Select("rc:Indexes/rc:Index", VulcanPackage.VulcanConfig.NamespaceManager))
            {
                string indexName = nav.SelectSingleNode("@Name") == null ? null : nav.SelectSingleNode("@Name").Value;

                string unique = nav.SelectSingleNode("@Unique", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean
                                                     ? "UNIQUE"
                                                     : "";

                string clustered = nav.SelectSingleNode("@Clustered", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean
                                                     ? "CLUSTERED"
                                                     : "NONCLUSTERED";

                string dropExisting = nav.SelectSingleNode("@DropExisting", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean
                                                     ? "DROP_EXISTING = ON"
                                                     : "DROP_EXISTING = OFF";

                string ignoreDupKey = nav.SelectSingleNode("@IgnoreDupKey", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean
                                                     ? "IGNORE_DUP_KEY = ON"
                                                     : "IGNORE_DUP_KEY = OFF";

                string online = nav.SelectSingleNode("@Online", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean
                                                     ? "ONLINE = ON"
                                                     : "ONLINE = OFF";

                string padIndex = nav.SelectSingleNode("@PadIndex", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean
                                                     ? "PAD_INDEX = ON"
                                                     : "PAD_INDEX = OFF";

                string sortInTempdb = nav.SelectSingleNode("@SortInTempdb", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean
                                                     ? "SORT_IN_TEMPDB = ON"
                                                     : "SORT_IN_TEMPDB = OFF";

                StringBuilder columnsBuilder   = new StringBuilder();
                StringBuilder indexNameBuilder = new StringBuilder("IX_");
                indexNameBuilder.Append(TableName);

                outputWriter.Write("\n");
                foreach (XPathNavigator columnNav in nav.Select("rc:Column", VulcanPackage.VulcanConfig.NamespaceManager))
                {
                    // This should use a Column Emitter which has callbacks and such...
                    //string componentName = nav.SelectSingleNode("@Name", VulcanPackage.NamespaceManager).Value;
                    string name      = columnNav.SelectSingleNode("@Name", VulcanPackage.VulcanConfig.NamespaceManager).Value;
                    string sortOrder = columnNav.SelectSingleNode("@SortOrder", VulcanPackage.VulcanConfig.NamespaceManager).Value;

                    columnsBuilder.AppendFormat(
                        "[{0}] {1},\n",
                        name,
                        sortOrder
                        );
                    indexNameBuilder.AppendFormat("_{0}", name);
                }
                columnsBuilder.Replace(",", "", columnsBuilder.Length - 2, 1);
                // Remove any dots which happen to show up in the name
                indexNameBuilder.Replace(".", "_");

                if (!string.IsNullOrEmpty(indexName))
                {
                    indexNameBuilder = new StringBuilder(indexName);  // Throw out the old name :) We got ourselves a better one.
                }

                te.SetParameters(
                    unique,
                    clustered,
                    indexNameBuilder.ToString(),
                    TableName,
                    columnsBuilder.ToString(),
                    String.Format(System.Globalization.CultureInfo.InvariantCulture, "{0},\n{1},\n{2},\n{3},\n{4}", padIndex, sortInTempdb, dropExisting, ignoreDupKey, online)
                    );

                te.Emit(outputWriter);
                outputWriter.Flush();
            }
        }
Exemple #9
0
        public override void Emit(TextWriter outputWriter)
        {
            if (_constraint is PrimaryKeyConstraint || _constraint is SimpleConstraint)
            {

                SimpleConstraint sc = (SimpleConstraint)_constraint;

                string clustered = !sc.Properties.ContainsKey("Clustered")
                                                     ? ""
                                                     :
                                                     (Convert.ToBoolean(sc.Properties["Clustered"],System.Globalization.CultureInfo.InvariantCulture)
                                                        ? "CLUSTERED"
                                                        : "NONCLUSTERED"
                                                        );

                string ignoreDupKey = !sc.Properties.ContainsKey("IgnoreDupKey")
                                                     ? ""
                                                     : (Convert.ToBoolean(sc.Properties["IgnoreDupKey"],System.Globalization.CultureInfo.InvariantCulture)
                                                        ? "IGNORE_DUP_KEY = ON"
                                                        : "IGNORE_DUP_KEY = OFF"
                                                     );

                string padIndex = !sc.Properties.ContainsKey("PadIndex")
                                                     ? ""
                                                     :
                                                     (Convert.ToBoolean(sc.Properties["PadIndex"],System.Globalization.CultureInfo.InvariantCulture)
                                                        ? "PAD_INDEX = ON"
                                                        : "PAD_INDEX = OFF"
                                                     );

                StringBuilder columnsBuilder = new StringBuilder();
                string pkName = sc.Properties.ContainsKey("Name") ? sc.Properties["Name"] : sc.Name;
                foreach (Column col in sc.Columns)
                {
                    string name = col.Name;
                    string sortOrder = !col.Properties.ContainsKey("SortOrder")
                                                     ? " ASC"
                                                     : " "+col.Properties["SortOrder"];
                    columnsBuilder.AppendFormat(
                        "[{0}]{1},\n",
                        name,
                        sortOrder
                    );
                    pkName = pkName + "_" + name;
                }
                columnsBuilder.Replace(",", "", columnsBuilder.Length - 2, 2);

                StringBuilder optionsBuilder = new StringBuilder();

                if (! String.IsNullOrEmpty(padIndex))
                    optionsBuilder.AppendFormat("{0},", padIndex);
                if(!String.IsNullOrEmpty(ignoreDupKey))
                    optionsBuilder.AppendFormat("{0},", ignoreDupKey);

                if (optionsBuilder.Length > 0)
                {
                    optionsBuilder.Replace(",", "",optionsBuilder.Length-1,1);
                    optionsBuilder.Insert(0, "WITH(");
                    optionsBuilder.Append(")");
                }

                string primaryKeyString = sc is PrimaryKeyConstraint ? "PRIMARY KEY" : "";
                string unique = sc is PrimaryKeyConstraint ? "" : "UNIQUE ";

                TemplateEmitter te = new TemplateEmitter("ConstraintTemplate", VulcanPackage, null);
                te.SetParameters(
                    pkName,
                    unique + clustered,
                    columnsBuilder.ToString(),
                    optionsBuilder.ToString(),
                    primaryKeyString
                    );

                te.Emit(outputWriter);
                outputWriter.Flush();
            } // end if Primary Key Constraint
            else if (_constraint is ForeignKeyConstraint)
            {
                ForeignKeyConstraint fkc = (ForeignKeyConstraint)_constraint;
                TemplateEmitter te = new TemplateEmitter(
                    "ForeignKeyConstraintTemplate",
                    VulcanPackage,
                    _tableHelper.Name,
                    fkc.Name,
                    fkc.LocalColumn.Name,
                    fkc.ForeignTable,
                    fkc.ForeignColumn.Name
                    );
                te.Emit(outputWriter);
            }
        }
        public override void Emit(TextWriter outputWriter)
        {
            if (outputWriter != null)
            {
                string identityColumnName = _tableHelper.KeyColumn.Name;
                StringBuilder spParametersBuilder = new StringBuilder();
                StringBuilder execArgumentsBuilder = new StringBuilder();
                StringBuilder uniqueColumnsBuilder = new StringBuilder();

                outputWriter.Write("\n");
                foreach (XPathNavigator nav in _tableNavigator.Select("rc:Columns/rc:Column", VulcanPackage.VulcanConfig.NamespaceManager))
                {
                    /* Build Argument List */
                    string columnName = nav.SelectSingleNode("@Name", VulcanPackage.VulcanConfig.NamespaceManager).Value;
                    string columnType = nav.SelectSingleNode("@Type", VulcanPackage.VulcanConfig.NamespaceManager).Value;
                    bool isKey = false;

                    if (
                        String.Compare(_tableHelper.KeyColumn.Name, columnName, StringComparison.InvariantCultureIgnoreCase) == 0
                        )
                    {
                        isKey = true;
                    }

                    spParametersBuilder.AppendFormat(
                        "\t@{0} {1} {2},\n",
                        columnName,
                        columnType,
                        isKey ? " OUTPUT" : ""
                    );

                    /* Re-Use this loop to build the INSERT statement */

                    execArgumentsBuilder.AppendFormat(
                    "\t\t@{0}{1},\n",
                    columnName,
                    isKey ? " OUTPUT" : ""
                    );
                }

                foreach (XPathNavigator nav in _tableNavigator.Select("rc:InsertOrUpdateUniqueColumn", VulcanPackage.VulcanConfig.NamespaceManager))
                {
                    uniqueColumnsBuilder.AppendFormat(
                        "{0} = @{0} AND ",
                        nav.Value
                    );
                }

                StringBuilder updateParameterBuilder = new StringBuilder();
                foreach (Column c in _tableHelper.Columns.Values)
                {
                    if (!c.Name.Equals(_tableHelper.KeyColumn.Name))
                    {
                        updateParameterBuilder.AppendFormat(
                            "{0} = @{0}, ",
                            c.Name
                        );
                    }
                }

                // If its not > 0 then we have a problem, this stored proc should never get created.
                if (uniqueColumnsBuilder.Length <= 0)
                {
                    outputWriter.Flush();
                    return;
                }
                //remove trailing commas or newlines or ANDS
                spParametersBuilder.Replace(",", "", spParametersBuilder.Length - 2, 1);
                execArgumentsBuilder.Replace(",", "", execArgumentsBuilder.Length - 2, 1);
                uniqueColumnsBuilder.Replace("AND", "", uniqueColumnsBuilder.Length - 4, 3);
                updateParameterBuilder.Replace(",", "", updateParameterBuilder.Length - 2, 1);

                outputWriter.Write("\n");
                TemplateEmitter te =
                        new TemplateEmitter(
                        "InsertOrUpdateSP",
                        VulcanPackage,
                        InsertAndUpdateSPEmitter.GetInsertAndUpdateProcedureName(_tableName),
                        _tableName,
                        spParametersBuilder.ToString(),
                        identityColumnName,
                        uniqueColumnsBuilder.ToString(),
                        InsertSPEmitter.GetInsertProcedureName(_tableName),
                        execArgumentsBuilder.ToString(),
                        updateParameterBuilder.ToString(),
                        _tableHelper.KeyColumn.Properties["Type"]
                        );

                te.Emit(outputWriter);
                outputWriter.Write("\n");
                outputWriter.Flush();
            } // end if outputWriter != null
        }
        public override void Emit(TextWriter outputWriter)
        {
            if (outputWriter != null)
            {
                string        identityColumnName   = _tableHelper.KeyColumn.Name;
                StringBuilder spParametersBuilder  = new StringBuilder();
                StringBuilder execArgumentsBuilder = new StringBuilder();
                StringBuilder uniqueColumnsBuilder = new StringBuilder();

                outputWriter.Write("\n");
                foreach (XPathNavigator nav in _tableNavigator.Select("rc:Columns/rc:Column", VulcanPackage.VulcanConfig.NamespaceManager))
                {
                    /* Build Argument List */
                    string columnName = nav.SelectSingleNode("@Name", VulcanPackage.VulcanConfig.NamespaceManager).Value;
                    string columnType = nav.SelectSingleNode("@Type", VulcanPackage.VulcanConfig.NamespaceManager).Value;
                    bool   isKey      = false;

                    if (
                        String.Compare(_tableHelper.KeyColumn.Name, columnName, StringComparison.InvariantCultureIgnoreCase) == 0
                        )
                    {
                        isKey = true;
                    }

                    spParametersBuilder.AppendFormat(
                        "\t@{0} {1} {2},\n",
                        columnName,
                        columnType,
                        isKey ? " OUTPUT" : ""
                        );

                    /* Re-Use this loop to build the INSERT statement */

                    execArgumentsBuilder.AppendFormat(
                        "\t\t@{0}{1},\n",
                        columnName,
                        isKey ? " OUTPUT" : ""
                        );
                }


                foreach (XPathNavigator nav in _tableNavigator.Select("rc:CheckAndInsertUniqueColumn", VulcanPackage.VulcanConfig.NamespaceManager))
                {
                    uniqueColumnsBuilder.AppendFormat(
                        "{0} = @{0} AND ",
                        nav.Value
                        );
                }

                // If its not > 0 then we have a problem, this stored proc should never get created.
                if (uniqueColumnsBuilder.Length <= 0)
                {
                    outputWriter.Flush();
                    return;
                }
                //remove trailing commas or newlines or ANDS
                spParametersBuilder.Replace(",", "", spParametersBuilder.Length - 2, 1);
                execArgumentsBuilder.Replace(",", "", execArgumentsBuilder.Length - 2, 1);
                uniqueColumnsBuilder.Replace("AND", "", uniqueColumnsBuilder.Length - 4, 3);

                outputWriter.Write("\n");
                TemplateEmitter te =
                    new TemplateEmitter(
                        "CheckAndInsertSP",
                        VulcanPackage,
                        _tableName,
                        CheckAndInsertSPEmitter.GetCheckAndInsertProcedureName(_tableName),
                        spParametersBuilder.ToString(),
                        execArgumentsBuilder.ToString(),
                        identityColumnName,
                        uniqueColumnsBuilder.ToString(),
                        InsertSPEmitter.GetInsertProcedureName(_tableName),
                        _tableHelper.KeyColumn.Properties["Type"]
                        );

                te.Emit(outputWriter);
                outputWriter.Write("\n");
                outputWriter.Flush();
            } // end if outputWriter != null
        }
Exemple #12
0
        public override void Emit(System.IO.TextWriter outputWriter)
        {
            StringBuilder columnsBuilder    = new StringBuilder();
            StringBuilder constraintBuilder = new StringBuilder();

            foreach (Column c in _tableHelper.Columns.Values)
            {
                string columnName = c.Properties["Name"];
                string columnType = c.Properties["Type"];
                bool   isNullable = c.Properties.ContainsKey("IsNullable")
                                        ? Convert.ToBoolean(c.Properties["IsNullable"])
                                        : false;

                bool isIdentity = _tableHelper.IsIdentityColumn(columnName);

                columnsBuilder.AppendFormat(
                    "\t[{0}] {1}{2}{3},\n",
                    columnName,
                    columnType,
                    isIdentity ? " IDENTITY(1,1)" : "",
                    isNullable ? "" : " NOT NULL"
                    );
            }

            StringBuilder simpleConstraintBuilder = new StringBuilder();

            // Primary Key Constraints
            int constraintCount = 0;

            foreach (Constraint c in _tableHelper.Constraints)
            {
                if (c is SimpleConstraint)
                {
                    string cString;
                    Message.Trace(Severity.Debug, "Found Constraint {0}", c.Name);
                    ConstraintEmitter ce = new ConstraintEmitter(VulcanPackage, c, _tableHelper);
                    ce.Emit(out cString);
                    simpleConstraintBuilder.AppendFormat("{0},\n", cString);
                    constraintCount++;
                }
            }

            if (constraintCount > 0)
            {
                simpleConstraintBuilder.Replace(",", "", simpleConstraintBuilder.Length - 2, 2);
            }

            TemplateEmitter te = new TemplateEmitter(
                "CreateTable",
                VulcanPackage,
                _tableHelper.Name,
                columnsBuilder.ToString(),
                simpleConstraintBuilder.ToString());

            te.Emit(outputWriter);
            outputWriter.Flush();


            //Remove the extra Comma, Leave it in if a _constraint is there...
            if (constraintCount == 0)
            {
                columnsBuilder.Replace(",", "", columnsBuilder.Length - 2, 2);
            }

            //Foreign Key Constraints
            foreach (Constraint c in _tableHelper.Constraints)
            {
                Message.Trace(Severity.Debug, "Found constraint {0}", c.Name);
                if (c is ForeignKeyConstraint)
                {
                    Message.Trace(Severity.Debug, "Found FKC {0}", c.Name);
                    ConstraintEmitter ce = new ConstraintEmitter(VulcanPackage, c, _tableHelper);
                    outputWriter.Write("\n");
                    ce.Emit(outputWriter);
                }
            }

            //Indexes
            IndexEmitter ie = new IndexEmitter(_tableHelper.Name, _tableHelper.TableNavigator, VulcanPackage);

            ie.Emit(outputWriter);

            InsertDefaultValuesEmitter ide = new InsertDefaultValuesEmitter(_tableHelper.Name, _tableHelper.TableNavigator, _tableHelper, VulcanPackage);

            ide.Emit(outputWriter);

            outputWriter.Write("\n");
        }
Exemple #13
0
        public override void Emit(TextWriter outputWriter)
        {
            if (_constraint is PrimaryKeyConstraint || _constraint is SimpleConstraint)
            {
                SimpleConstraint sc = (SimpleConstraint)_constraint;

                string clustered = !sc.Properties.ContainsKey("Clustered")
                                                     ? ""
                                                     :
                                   (Convert.ToBoolean(sc.Properties["Clustered"], System.Globalization.CultureInfo.InvariantCulture)
                                                        ? "CLUSTERED"
                                                        : "NONCLUSTERED"
                                   );


                string ignoreDupKey = !sc.Properties.ContainsKey("IgnoreDupKey")
                                                     ? ""
                                                     : (Convert.ToBoolean(sc.Properties["IgnoreDupKey"], System.Globalization.CultureInfo.InvariantCulture)
                                                        ? "IGNORE_DUP_KEY = ON"
                                                        : "IGNORE_DUP_KEY = OFF"
                                                        );

                string padIndex = !sc.Properties.ContainsKey("PadIndex")
                                                     ? ""
                                                     :
                                  (Convert.ToBoolean(sc.Properties["PadIndex"], System.Globalization.CultureInfo.InvariantCulture)
                                                        ? "PAD_INDEX = ON"
                                                        : "PAD_INDEX = OFF"
                                  );


                StringBuilder columnsBuilder = new StringBuilder();
                string        pkName         = sc.Properties.ContainsKey("Name") ? sc.Properties["Name"] : sc.Name;
                foreach (Column col in sc.Columns)
                {
                    string name      = col.Name;
                    string sortOrder = !col.Properties.ContainsKey("SortOrder")
                                                     ? " ASC"
                                                     : " " + col.Properties["SortOrder"];
                    columnsBuilder.AppendFormat(
                        "[{0}]{1},\n",
                        name,
                        sortOrder
                        );
                    pkName = pkName + "_" + name;
                }
                columnsBuilder.Replace(",", "", columnsBuilder.Length - 2, 2);

                StringBuilder optionsBuilder = new StringBuilder();

                if (!String.IsNullOrEmpty(padIndex))
                {
                    optionsBuilder.AppendFormat("{0},", padIndex);
                }
                if (!String.IsNullOrEmpty(ignoreDupKey))
                {
                    optionsBuilder.AppendFormat("{0},", ignoreDupKey);
                }

                if (optionsBuilder.Length > 0)
                {
                    optionsBuilder.Replace(",", "", optionsBuilder.Length - 1, 1);
                    optionsBuilder.Insert(0, "WITH(");
                    optionsBuilder.Append(")");
                }

                string primaryKeyString = sc is PrimaryKeyConstraint ? "PRIMARY KEY" : "";
                string unique           = sc is PrimaryKeyConstraint ? "" : "UNIQUE ";

                TemplateEmitter te = new TemplateEmitter("ConstraintTemplate", VulcanPackage, null);
                te.SetParameters(
                    pkName,
                    unique + clustered,
                    columnsBuilder.ToString(),
                    optionsBuilder.ToString(),
                    primaryKeyString
                    );

                te.Emit(outputWriter);
                outputWriter.Flush();
            } // end if Primary Key Constraint
            else if (_constraint is ForeignKeyConstraint)
            {
                ForeignKeyConstraint fkc = (ForeignKeyConstraint)_constraint;
                TemplateEmitter      te  = new TemplateEmitter(
                    "ForeignKeyConstraintTemplate",
                    VulcanPackage,
                    _tableHelper.Name,
                    fkc.Name,
                    fkc.LocalColumn.Name,
                    fkc.ForeignTable,
                    fkc.ForeignColumn.Name
                    );
                te.Emit(outputWriter);
            }
        }
Exemple #14
0
        public override void Emit(TextWriter outputWriter)
        {
            TemplateEmitter te = new TemplateEmitter("CreateIndex", VulcanPackage, null);

            foreach (XPathNavigator nav in TableNavigator.Select("rc:Indexes/rc:Index", VulcanPackage.VulcanConfig.NamespaceManager))
            {
                string indexName = nav.SelectSingleNode("@Name") == null ? null : nav.SelectSingleNode("@Name").Value;

                string unique = nav.SelectSingleNode("@Unique", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean
                                                     ? "UNIQUE"
                                                     : "";

                string clustered =  nav.SelectSingleNode("@Clustered", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean
                                                     ? "CLUSTERED"
                                                     : "NONCLUSTERED";

                string dropExisting = nav.SelectSingleNode("@DropExisting", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean
                                                     ? "DROP_EXISTING = ON"
                                                     : "DROP_EXISTING = OFF";

                string ignoreDupKey = nav.SelectSingleNode("@IgnoreDupKey", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean
                                                     ? "IGNORE_DUP_KEY = ON"
                                                     : "IGNORE_DUP_KEY = OFF";

                string online = nav.SelectSingleNode("@Online", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean
                                                     ? "ONLINE = ON"
                                                     : "ONLINE = OFF";

                string padIndex = nav.SelectSingleNode("@PadIndex", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean
                                                     ? "PAD_INDEX = ON"
                                                     : "PAD_INDEX = OFF";

                string sortInTempdb = nav.SelectSingleNode("@SortInTempdb", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean
                                                     ? "SORT_IN_TEMPDB = ON"
                                                     : "SORT_IN_TEMPDB = OFF";

                StringBuilder columnsBuilder = new StringBuilder();
                StringBuilder indexNameBuilder = new StringBuilder("IX_");
                indexNameBuilder.Append(TableName);

                outputWriter.Write("\n");
                foreach (XPathNavigator columnNav in nav.Select("rc:Column", VulcanPackage.VulcanConfig.NamespaceManager))
                {
                    // This should use a Column Emitter which has callbacks and such...
                    //string componentName = nav.SelectSingleNode("@Name", VulcanPackage.NamespaceManager).Value;
                    string name = columnNav.SelectSingleNode("@Name", VulcanPackage.VulcanConfig.NamespaceManager).Value;
                    string sortOrder = columnNav.SelectSingleNode("@SortOrder", VulcanPackage.VulcanConfig.NamespaceManager).Value;

                    columnsBuilder.AppendFormat(
                        "[{0}] {1},\n",
                        name,
                        sortOrder
                    );
                    indexNameBuilder.AppendFormat("_{0}", name);
                }
                columnsBuilder.Replace(",", "", columnsBuilder.Length - 2, 1);
                // Remove any dots which happen to show up in the name
                indexNameBuilder.Replace(".", "_");

                if (!string.IsNullOrEmpty(indexName))
                {
                    indexNameBuilder = new StringBuilder(indexName);  // Throw out the old name :) We got ourselves a better one.
                }

                te.SetParameters(
                    unique,
                    clustered,
                    indexNameBuilder.ToString(),
                    TableName,
                    columnsBuilder.ToString(),
                    String.Format(System.Globalization.CultureInfo.InvariantCulture,"{0},\n{1},\n{2},\n{3},\n{4}", padIndex, sortInTempdb, dropExisting, ignoreDupKey, online)
                    );

                te.Emit(outputWriter);
                outputWriter.Flush();

            }
        }
Exemple #15
0
        public VulcanConfig XmlTemplateReplacement(VulcanConfig vulcanConfig)
        {
            List<XPathNavigator> nodeList = new List<XPathNavigator>();
            VulcanConfig vc = vulcanConfig;

            foreach (XPathNavigator node in vc.Navigator.SelectDescendants(XPathNodeType.Element, true))
            {
                if (node.XmlType != null && node.XmlType.Name != null)
                {
                    if (node.XmlType.Name.ToUpperInvariant().Contains("TEMPLATEXML"))
                    {
                        nodeList.Add(node);
                    }
                }
            }

            foreach (XPathNavigator node in nodeList)
            {
                XPathNavigator nameNode = node.SelectSingleNode("@Name");
                string templateName = nameNode.Value;
                nameNode.DeleteSelf();
                Message.Trace(Severity.Debug,"Replacing Template {0}", node.OuterXml);
                if (this.templateDictionary.ContainsKey(templateName))
                {
                    Template t = this[templateName];
                    TemplateEmitter te = new TemplateEmitter(t);

                    int parameterCount = 0;
                    if (node.MoveToFirstAttribute())
                    {
                        do
                        {
                            parameterCount++;
                            Message.Trace(Severity.Debug, "Mapping Parameter {0}={1}", node.Name, node.Value);
                            te.SetNamedParameter(node.Name, node.Value);
                        } while (node.MoveToNextAttribute());
                    }
                    if (parameterCount == t.MapDictionary.Keys.Count)
                    {
                        string newXml;
                        te.Emit(out newXml);
                        if (parameterCount > 0)
                        {
                            node.MoveToParent();
                        }
                        Message.Trace(Severity.Debug, "Old Node: {0}", node.OuterXml);
                        node.OuterXml = newXml;
                        Message.Trace(Severity.Debug, "New Node: {0}", node.OuterXml);
                    }
                    else
                    {
                        Message.Trace(Severity.Error, "Template parameters do not match up.  Contains {0} but the template requires {1}", parameterCount, t.MapDictionary.Keys.Count);
                    }
                }
                else
                {
                    Message.Trace(Severity.Error, "Invalid template {0}", templateName);
                }

            }
            string savePath = vc.Save();
            Message.Trace(Severity.Notification,"Saved new VulcanConfig to {0}", savePath);
            vc = new VulcanConfig(savePath);
            return vc;
        }
Exemple #16
0
        public override void Emit(XPathNavigator patternNavigator)
        {
            if (patternNavigator != null)
            {
                string name = patternNavigator.SelectSingleNode("@Name").Value;
                string body = patternNavigator.SelectSingleNode("rc:Body", VulcanPackage.VulcanConfig.NamespaceManager).Value.Trim();
                string type = patternNavigator.SelectSingleNode("@Type").Value;
                string resultSet = patternNavigator.SelectSingleNode("@ResultSet").Value;
                //   Message.Trace("\n\n{0}\n\n", patternNavigator.SelectSingleNode("@Name").);

                Connection c = Connection.GetExistingConnection(VulcanPackage, patternNavigator);

                if (c != null)
                {
                    SQLTask sqlTask = new SQLTask(VulcanPackage,
                       Resources.Create + name,
                       Resources.Create + name,
                        ParentContainer,
                        c
                        );

                    switch (type.ToUpperInvariant())
                    {
                        case "FILE":
                            string fileName = Resources.Create + name + Resources.ExtensionSQLFile;
                            string filePath = VulcanPackage.AddFileToProject(fileName);

                            TemplateEmitter te = new TemplateEmitter(new Template("temp", "temp", body));
                            te.SetParameters("");
                            te.Emit(filePath, false);

                            sqlTask.TransmuteToFileTask(fileName);
                            break;
                        case "EXPRESSION":
                            sqlTask.TransmuteToExpressionTask(body);
                            break;
                        default:
                            break;
                    }

                    switch (resultSet.ToUpperInvariant())
                    {
                        case "NONE":
                            sqlTask.ExecuteSQLTask.ResultSetType = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ResultSetType.ResultSetType_None;
                            break;
                        case "SINGLEROW":
                            sqlTask.ExecuteSQLTask.ResultSetType = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ResultSetType.ResultSetType_SingleRow;
                            break;
                        case "FULL":
                            sqlTask.ExecuteSQLTask.ResultSetType = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ResultSetType.ResultSetType_Rowset;
                            break;
                        case "XML":
                            sqlTask.ExecuteSQLTask.ResultSetType = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ResultSetType.ResultSetType_XML;
                            break;
                        default:
                            sqlTask.ExecuteSQLTask.ResultSetType = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ResultSetType.ResultSetType_None;
                            break;
                    }

                    foreach (XPathNavigator resultNavigator in patternNavigator.Select("rc:Result", VulcanPackage.VulcanConfig.NamespaceManager))
                    {
                        sqlTask.BindResult(resultNavigator.SelectSingleNode("@Name").Value, resultNavigator.SelectSingleNode("@VariableName").Value);
                    }
                    this.FirstExecutableGeneratedByPattern = sqlTask.SQLTaskHost;
                    this.LastExecutableGeneratedByPattern = this.FirstExecutableGeneratedByPattern;

                    this.ExecuteDuringDesignTime(patternNavigator,sqlTask.SQLTaskHost);
                } // if c != null
                else
                {
                    Message.Trace(Severity.Error, "{0}: Connection {1} does not exist.", name, patternNavigator.SelectSingleNode("rc:Connection", VulcanPackage.VulcanConfig.NamespaceManager).OuterXml);
                }
            }
        }
Exemple #17
0
        public override void Emit(XPathNavigator patternNavigator)
        {
            if (patternNavigator != null)
            {
                string taskName = patternNavigator.SelectSingleNode("@Name").Value;
                string connectionName = patternNavigator.SelectSingleNode("@Connection").Value;
                string varPrefix = patternNavigator.SelectSingleNode("@PreviousLogEntryVariablePrefix").Value;

                string sourceColumn = patternNavigator.SelectSingleNode("@SourceColumn") == null ? null : patternNavigator.SelectSingleNode("@SourceColumn").Value;
                string destinationColumn = patternNavigator.SelectSingleNode("@DestinationColumn") == null ? null : patternNavigator.SelectSingleNode("@DestinationColumn").Value;
                string table = patternNavigator.SelectSingleNode("@Table")== null ? null : patternNavigator.SelectSingleNode("@Table").Value;
                string tableConnectionName = patternNavigator.SelectSingleNode("@TableConnection") == null ? null : patternNavigator.SelectSingleNode("@TableConnection").Value;

                if (varPrefix.ToUpperInvariant() == "varLog")
                {
                    Message.Trace(Severity.Error, "Name: {0}: Error in PreviousLogEntryVariablePrefix: varLog is a reserved variable name.", taskName);
                    return;
                }

                Connection connection =
                    Connection.GetExistingConnection(VulcanPackage, connectionName);

                LogtainerLog ll = new LogtainerLog(
                                                    VulcanPackage,
                                                    Resources.LogVariablePrefix + VulcanPackage.Name + taskName,
                                                    sourceColumn,
                                                    destinationColumn,
                                                    table,
                                                    tableConnectionName
                                                    );

                string execSqlTaskName = Resources.Log + Resources.Seperator + VulcanPackage.Name + Resources.Seperator + taskName + Resources.Seperator;
                SQLTask logStartTask = new SQLTask(VulcanPackage, execSqlTaskName + Resources.Start, execSqlTaskName + Resources.Start, ParentContainer, connection);

                TemplateEmitter te = new TemplateEmitter(VulcanPackage.TemplateManager["LogStart"]);
                te.SetNamedParameter("ETLName", VulcanPackage.Name);
                te.SetNamedParameter("TaskName", taskName);

                if (_logStack.Count > 0)
                {
                    te.SetNamedParameter("ParentLogID", "?");
                    // Yes you have to hard code the data type as there is no lookup or cross-reference source, and the intger is dependent on the connection you create... bugbug!
                    logStartTask.BindParameter(CurrentLog.LogVariable, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Input, 3);
                }
                else
                {
                    te.SetNamedParameter("ParentLogID", "NULL");
                }
                logStartTask.BindParameter(ll.LogVariable, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output, 3);
                logStartTask.BindParameter(ll.LastRecordLogVariable, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output, 3);
                logStartTask.BindParameter(ll.IsAnotherInstanceCurrentlyRunningLogVariable, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output, 3);

                string sqlExpression;
                te.Emit(out sqlExpression);
                logStartTask.TransmuteToExpressionTask(sqlExpression);

                te = new TemplateEmitter(VulcanPackage.TemplateManager["LogGetValue"]);
                te.SetNamedParameter("LogID", ll.LastRecordLogVariable.QualifiedName);
                this.FirstExecutableGeneratedByPattern = logStartTask.SQLTaskHost;

                // Push the current log variable onto the stack!
                _logStack.Push(ll);

                //Add a new SQL Task to read the  previous log
                te = new TemplateEmitter("LogGetValue", VulcanPackage, ll.LastRecordLogVariable.QualifiedName);
                string readPreviousLogValuesQuery;
                te.Emit(out readPreviousLogValuesQuery);

                SQLTask readPreviousLogValues = new SQLTask(VulcanPackage, Resources.Log + Resources.LoadInto + varPrefix, Resources.Log + Resources.LoadInto + varPrefix, ParentContainer,connection);
                readPreviousLogValues.TransmuteToExpressionTask(readPreviousLogValuesQuery);
                VulcanPackage.AddPrecedenceConstraint(this.FirstExecutableGeneratedByPattern, readPreviousLogValues.SQLTaskHost, ParentContainer);

                //Bind and create variables for the SQL Task that reads the previous log. This is kinda hacky
                readPreviousLogValues.BindParameter(
                    VulcanPackage.AddVariable(varPrefix + "StartTime", new DateTime(1980,1,1)),
                    Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output,
                    (int)System.Data.OleDb.OleDbType.DBTimeStamp,
                    255);
                readPreviousLogValues.BindParameter(
                    VulcanPackage.AddVariable(varPrefix + "EndTime", new DateTime(1980,1,1)),
                    Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output,
                    (int)System.Data.OleDb.OleDbType.DBTimeStamp,
                    255);

                readPreviousLogValues.BindParameter(
                    VulcanPackage.AddVariable(varPrefix + "FirstSourceRecord", String.Empty),
                    Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output,
                    (int)System.Data.OleDb.OleDbType.WChar,
                    255);
                readPreviousLogValues.BindParameter(
                    VulcanPackage.AddVariable(varPrefix + "FirstDestinationRecord", String.Empty),
                    Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output,
                    (int)System.Data.OleDb.OleDbType.WChar,
                    255);
                readPreviousLogValues.BindParameter(
                    VulcanPackage.AddVariable(varPrefix + "LastSourceRecord", String.Empty),
                    Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output,
                    (int)System.Data.OleDb.OleDbType.WChar,
                    255);
                readPreviousLogValues.BindParameter(
                    VulcanPackage.AddVariable(varPrefix + "LastDestinationRecord", String.Empty),
                    Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output,
                    (int)System.Data.OleDb.OleDbType.WChar,
                    255);

                readPreviousLogValues.BindParameter(
                    VulcanPackage.AddVariable(varPrefix + "Status", String.Empty),
                    Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output,
                    (int)System.Data.OleDb.OleDbType.WChar,
                    255);

                readPreviousLogValues.BindParameter(
                    VulcanPackage.AddVariable(varPrefix + "Notes", String.Empty),
                    Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Output,
                    (int)System.Data.OleDb.OleDbType.WChar,
                    255);

                // Handle the sub-tasks just like the Container pattern
                DTS.Executable previousExec = readPreviousLogValues.SQLTaskHost;
                Pattern p = null;
                foreach (XPathNavigator nav in patternNavigator.SelectChildren(XPathNodeType.Element))
                {
                    p = PatternFactory.ProcessPattern(VulcanPackage, ParentContainer, nav, p);
                    VulcanPackage.AddPrecedenceConstraint(previousExec, p.FirstExecutableGeneratedByPattern, ParentContainer);
                    previousExec = p.LastExecutableGeneratedByPattern;
                }

                this.LastExecutableGeneratedByPattern = previousExec;

                // Pop the current log variable off of the stack and end the log.
                ll = _logStack.Pop();

                te = new TemplateEmitter(VulcanPackage.TemplateManager["LogSetValue"]);
                te.SetNamedParameter("LogID", ll.LogVariable.QualifiedName);
                StringBuilder writeValuesToLogQuery = new StringBuilder();
                foreach (string column in ll.LogColumnDictionary.Keys)
                {
                    string temp;
                    te.SetNamedParameter("Column", column);
                    te.SetNamedParameter("Value", ll.LogColumnDictionary[column].QualifiedName);
                    te.Emit(out temp);
                    writeValuesToLogQuery.Append(temp);
                    writeValuesToLogQuery.AppendFormat(" + \n");
                }

                te = new TemplateEmitter(VulcanPackage.TemplateManager["LogEnd"]);
                te.Emit(out sqlExpression);

                writeValuesToLogQuery.Append(sqlExpression);

                SQLTask logEndTask = new SQLTask(VulcanPackage, execSqlTaskName + Resources.Stop, execSqlTaskName + Resources.Stop, ParentContainer, connection);
                logEndTask.TransmuteToExpressionTask(writeValuesToLogQuery.ToString());
                logEndTask.BindParameter(ll.LogVariable, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ParameterDirections.Input, 3);
                if (previousExec != null)
                {
                    VulcanPackage.AddPrecedenceConstraint(previousExec, logEndTask.SQLTaskHost, ParentContainer);
                }
                this.LastExecutableGeneratedByPattern = logEndTask.SQLTaskHost;
            }
        }
Exemple #18
0
        public override void Emit(System.IO.TextWriter outputWriter)
        {
            StringBuilder columnsBuilder   = new StringBuilder();
            StringBuilder constraintBuilder = new StringBuilder();

            foreach (Column c in _tableHelper.Columns.Values)
            {
                string columnName = c.Properties["Name"];
                string columnType = c.Properties["Type"];
                bool isNullable = c.Properties.ContainsKey("IsNullable")
                                        ? Convert.ToBoolean(c.Properties["IsNullable"])
                                        : false;

                bool isIdentity = _tableHelper.IsIdentityColumn(columnName);

                columnsBuilder.AppendFormat(
                    "\t[{0}] {1}{2}{3},\n",
                    columnName,
                    columnType,
                    isIdentity ? " IDENTITY(1,1)" : "",
                    isNullable ? "" : " NOT NULL"
                );
            }

            StringBuilder simpleConstraintBuilder = new StringBuilder();

            // Primary Key Constraints
            int constraintCount = 0;
            foreach (Constraint c in _tableHelper.Constraints)
            {
                if (c is SimpleConstraint)
                {
                    string cString;
                    Message.Trace(Severity.Debug,"Found Constraint {0}", c.Name);
                    ConstraintEmitter ce = new ConstraintEmitter(VulcanPackage, c, _tableHelper);
                    ce.Emit(out cString);
                    simpleConstraintBuilder.AppendFormat("{0},\n", cString);
                    constraintCount++;
                }
            }

            if (constraintCount > 0)
            {
                simpleConstraintBuilder.Replace(",", "", simpleConstraintBuilder.Length - 2, 2);
            }

            TemplateEmitter te = new TemplateEmitter(
                "CreateTable", 
                VulcanPackage,
                _tableHelper.Name,
                columnsBuilder.ToString(),
                simpleConstraintBuilder.ToString());
            te.Emit(outputWriter);
            outputWriter.Flush();


            //Remove the extra Comma, Leave it in if a _constraint is there...
            if (constraintCount == 0)
            {
                columnsBuilder.Replace(",", "", columnsBuilder.Length - 2, 2);
            }

            //Foreign Key Constraints
            foreach (Constraint c in _tableHelper.Constraints)
            {
                Message.Trace(Severity.Debug,"Found constraint {0}", c.Name);
                if(c is ForeignKeyConstraint)
                {
                    Message.Trace(Severity.Debug,"Found FKC {0}", c.Name);
                    ConstraintEmitter ce = new ConstraintEmitter(VulcanPackage, c, _tableHelper);
                    outputWriter.Write("\n");
                    ce.Emit(outputWriter);
                }
            }

            //Indexes
            IndexEmitter ie = new IndexEmitter(_tableHelper.Name, _tableHelper.TableNavigator, VulcanPackage);
            ie.Emit(outputWriter);

            InsertDefaultValuesEmitter ide = new InsertDefaultValuesEmitter(_tableHelper.Name, _tableHelper.TableNavigator, _tableHelper, VulcanPackage);
            ide.Emit(outputWriter);

            outputWriter.Write("\n");
        }
Exemple #19
0
        public static DerivedColumns CreateIsNullPatcherFromXml(Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator nullNav)
        {
            if (nullNav == null || nullNav.Name.ToUpperInvariant() != "IsNullPatcher".ToUpperInvariant())
            {
                return null;
            }

            string componentName = nullNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value;
            Message.Trace(Severity.Debug, "Begin: IsNullPatcher variant DerivedColumns Transformation {0}", componentName);
            DerivedColumns dc = new DerivedColumns(vulcanPackage, dataFlowTask, parentComponent, componentName, componentName);

            IDTSVirtualInput90 vi = dc.Component.InputCollection[0].GetVirtualInput();

            TemplateEmitter te = new TemplateEmitter("NullPatcherIsnullTemplate", vulcanPackage, null);

            foreach (XPathNavigator nav in nullNav.Select("rc:Column", vulcanPackage.VulcanConfig.NamespaceManager))
            {
                string name = nav.SelectSingleNode("@Name").Value;
                string defaultValue = nav.SelectSingleNode("@DefaultValue").Value;

                dc.SetInputUsageType(vi, vi.VirtualInputColumnCollection[name], DTSUsageType.UT_READWRITE);
                IDTSInputColumn90 inputCol = dc.Component.InputCollection[0].InputColumnCollection[name];

                string expression;
                te.SetParameters("#" + vi.VirtualInputColumnCollection[name].LineageID.ToString(), defaultValue);
                te.Emit(out expression);

                string friendlyExpression;
                te.SetParameters(name, defaultValue);
                te.Emit(out friendlyExpression);

                inputCol.CustomPropertyCollection["Expression"].Value = expression;
                inputCol.CustomPropertyCollection["FriendlyExpression"].Value = friendlyExpression;
            }
            return dc;
        }
Exemple #20
0
        public override void Emit(TextWriter outputWriter)
        {
            string        identityColumnName  = "";
            StringBuilder spParametersBuilder = new StringBuilder();
            StringBuilder insertIntoBuilder   = new StringBuilder();
            StringBuilder valuesBuilder       = new StringBuilder();

            outputWriter.Write("\n");
            foreach (XPathNavigator nav in _tableNavigator.Select("rc:Columns/rc:Column", VulcanPackage.VulcanConfig.NamespaceManager))
            {
                /* Build Argument List */
                string columnName = nav.SelectSingleNode("@Name", VulcanPackage.VulcanConfig.NamespaceManager).Value;
                string columnType = nav.SelectSingleNode("@Type", VulcanPackage.VulcanConfig.NamespaceManager).Value;
                bool   isIdentity = _tableHelper.IsIdentityColumn(columnName);
                bool   isKey      = false;

                if (
                    String.Compare(_tableHelper.KeyColumn.Name, columnName, StringComparison.InvariantCultureIgnoreCase) == 0
                    )
                {
                    isKey = true;
                }


                if (isIdentity)
                {
                    identityColumnName = columnName;
                }

                spParametersBuilder.AppendFormat(
                    "\t@{0} {1} {2},\n",
                    columnName,
                    columnType,
                    isKey ? "OUTPUT" : ""
                    );

                /* Re-Use this loop to build the INSERT statement */

                if (!isIdentity)
                {
                    insertIntoBuilder.AppendFormat(
                        "\t[{0}],\n",
                        columnName
                        );

                    valuesBuilder.AppendFormat(
                        "\t@{0},\n",
                        columnName
                        );
                }
            }


            spParametersBuilder.Replace(",", "", spParametersBuilder.Length - 2, 1);
            insertIntoBuilder.Replace(",", "", insertIntoBuilder.Length - 2, 1);
            valuesBuilder.Replace(",", "", valuesBuilder.Length - 2, 1);


            string scopeIdentity = String.Empty;

            if (!String.IsNullOrEmpty(identityColumnName))
            {
                scopeIdentity = String.Format("IF @Exception = 0\nSET @{0} = SCOPE_IDENTITY()", identityColumnName);
            }

            TemplateEmitter te =
                new TemplateEmitter(
                    "InsertSP",
                    VulcanPackage,
                    _tableName,
                    GetInsertProcedureName(_tableName),
                    spParametersBuilder.ToString(),
                    insertIntoBuilder.ToString(),
                    valuesBuilder.ToString(),
                    scopeIdentity
                    );

            te.Emit(outputWriter);
            outputWriter.Flush();
        }
Exemple #21
0
        public override void Emit(TextWriter outputWriter)
        {
            string identityColumnName = "";
            StringBuilder spParametersBuilder = new StringBuilder();
            StringBuilder insertIntoBuilder = new StringBuilder();
            StringBuilder valuesBuilder = new StringBuilder();
            outputWriter.Write("\n");
            foreach (XPathNavigator nav in _tableNavigator.Select("rc:Columns/rc:Column", VulcanPackage.VulcanConfig.NamespaceManager))
            {
                /* Build Argument List */
                string columnName = nav.SelectSingleNode("@Name", VulcanPackage.VulcanConfig.NamespaceManager).Value;
                string columnType = nav.SelectSingleNode("@Type", VulcanPackage.VulcanConfig.NamespaceManager).Value;
                bool isIdentity = _tableHelper.IsIdentityColumn(columnName);
                bool isKey = false;

                if (
                    String.Compare(_tableHelper.KeyColumn.Name, columnName, StringComparison.InvariantCultureIgnoreCase) == 0
                    )
                {
                    isKey = true;
                }

                if(isIdentity)
                {
                    identityColumnName = columnName;
                }

                spParametersBuilder.AppendFormat(
                    "\t@{0} {1} {2},\n",
                    columnName,
                    columnType,
                    isKey ? "OUTPUT" : ""
                );

                /* Re-Use this loop to build the INSERT statement */

                if (!isIdentity)
                {
                    insertIntoBuilder.AppendFormat(
                    "\t[{0}],\n",
                    columnName
                    );

                    valuesBuilder.AppendFormat(
                    "\t@{0},\n",
                    columnName
                    );
                }
            }

            spParametersBuilder.Replace(",", "", spParametersBuilder.Length - 2, 1);
            insertIntoBuilder.Replace(",", "", insertIntoBuilder.Length - 2, 1);
            valuesBuilder.Replace(",", "", valuesBuilder.Length - 2, 1);

            string scopeIdentity = String.Empty;

            if(!String.IsNullOrEmpty(identityColumnName))
            {
             scopeIdentity =  String.Format("IF @Exception = 0\nSET @{0} = SCOPE_IDENTITY()", identityColumnName);
            }

            TemplateEmitter te =
                    new TemplateEmitter(
                    "InsertSP",
                    VulcanPackage,
                    _tableName,
                    GetInsertProcedureName(_tableName),
                    spParametersBuilder.ToString(),
                    insertIntoBuilder.ToString(),
                    valuesBuilder.ToString(),
                    scopeIdentity
                    );

            te.Emit(outputWriter);
            outputWriter.Flush();
        }