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;
}
}
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)
{
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();
}
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();
}
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();
}
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();
}
}
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
}
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");
}
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)
{
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();
}
}
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;
}
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);
}
}
}
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;
}
}
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");
}
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;
}
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();
}
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();
}