/// <summary>
/// Create SQL statement for creating temporary table (if merge command is used, an index is created)
/// </summary>
/// <returns>sql create temporary table command</returns>
public static string GetCreateTempTable(IsagCustomProperties properties, string tempTableName)
{
string returnValue = "";
string indexColumns = "";
foreach (ColumnConfig config in properties.BulkCopyColumnConfigLIst)
{
if (returnValue != "")
{
returnValue += ", ";
}
returnValue += Brackets(config.BulkColumnName) + " ";
returnValue += config.BulkDataType + Environment.NewLine;
if (properties.UseMerge && config.Key)
{
if (indexColumns != "")
{
indexColumns += ", ";
}
indexColumns += Brackets(config.BulkColumnName) + " ASC";
}
}
returnValue = "CREATE TABLE " + tempTableName + Environment.NewLine +
"( " + returnValue + " )";
if (properties.UseMerge)
{
returnValue += ";" + Environment.NewLine + Constants.CREATE_INDEX.Replace("<table>", tempTableName).Replace("<columns>", indexColumns);
}
return(returnValue);
}
/// <summary>
/// Create SQL statement for creating destination table
/// </summary>
/// <param name="properties">componets custom properties</param>
/// <returns>sql statement for creating destination table</returns>
public static string GetCreateDestinationTable(IsagCustomProperties properties)
{
StringBuilder result = new StringBuilder("CREATE TABLE [TableLoader Destination] (" + Environment.NewLine);
bool appendComma = false;
foreach (ColumnConfig config in properties.ColumnConfigList)
{
if (config.HasInput)
{
if (appendComma)
{
result.Append("," + Environment.NewLine);
}
appendComma = true;
result.Append(" ");
result.Append(Brackets(config.InputColumnName));
result.Append(" ");
result.Append(config.DataTypeInput);
}
}
result.Append(Environment.NewLine);
result.Append(")");
return(result.ToString());
}
/// <summary>
/// Apply standard configuration to custom properties
/// </summary>
/// <param name="isagCustomProperties">component custom porperties</param>
public void SetStandardConfiguration(ref IsagCustomProperties isagCustomProperties)
{
if (!isagCustomProperties.AutoUpdateStandardConfiguration)
{
return;
}
if (!HasConnection)
{
throw new Exception("The Connection Manager for the Standard Configuration is missing.");
}
else
{
DataTable dt = GetStandardConfigurationAsDataTable();
if (dt.Rows.Count == 0)
{
throw new Exception("The Configuration Table is empty or the Database connection is not valid.");
}
Dictionary <string, DataRow> cfgList = GetStandardConfigurationAsDictionary();
if (!cfgList.ContainsKey(isagCustomProperties.StandarConfiguration))
{
throw new Exception("The Standard Configuration \"" + isagCustomProperties.StandarConfiguration + "\" could not be found in the configuration table.");
}
SetStandardConfiguration(ref isagCustomProperties, cfgList[isagCustomProperties.StandarConfiguration]);
}
}
/// <summary>
/// Create SQL statement for altering destination table
/// </summary>
/// <param name="properties">componets custom properties</param>
/// <param name="tempTableName">temporary table name</param>
/// <returns>sql statement for altering destination table</returns>
public static string GetAlterDestinationTable(IsagCustomProperties properties, SqlColumnList sqlColumns)
{
string result = "";
if (properties.HasDestinationTable)
{
foreach (ColumnConfig config in properties.ColumnConfigList)
{
string outputColumnName = sqlColumns.GetMatchingColumnname(config.InputColumnName, properties.PrefixInput, properties.PrefixOutput);
if (config.HasInput && outputColumnName == "")
{
if (result == "")
{
result += " ADD ";
}
else
{
result += "," + Environment.NewLine + " ";
}
result += Brackets(config.InputColumnName) + " " + config.DataTypeInput;
}
}
}
return("ALTER TABLE " + Brackets(properties.DestinationTable) + Environment.NewLine + result);
}
/// <summary>
/// Apply standard configuration to custom properties
/// </summary>
/// <param name="isagCustomProperties">component custom porperties</param>
/// <param name="row">data row</param>
public void SetStandardConfiguration(ref IsagCustomProperties isagCustomProperties, DataRow row)
{
try
{
isagCustomProperties.ChunckSizeBulk = (long)row["ChunkSizeBulk"];
isagCustomProperties.ChunkSizeDbCommand = (long)row["ChunkSizeDbCommand"];
isagCustomProperties.TimeOutDb = (int)row["DbTimeout"];
isagCustomProperties.MaxThreadCount = (long)row["MaxThreadCount"];
isagCustomProperties.PrefixInput = row["PreFixInput"].ToString();
isagCustomProperties.PrefixOutput = row["PreFixOutput"].ToString();
IsagCustomProperties.TableLoaderType tableLoaderType =
(IsagCustomProperties.TableLoaderType)Enum.Parse(typeof(IsagCustomProperties.TableLoaderType), row["TableLoaderType"].ToString());
IsagCustomProperties.DbCommandType dbCommand =
(IsagCustomProperties.DbCommandType)Enum.Parse(typeof(IsagCustomProperties.DbCommandType), row["DbCommand"].ToString());
IsagCustomProperties.TransactionType transaction =
(IsagCustomProperties.TransactionType)Enum.Parse(typeof(IsagCustomProperties.TransactionType), row["TransactionType"].ToString());
isagCustomProperties.TlType = tableLoaderType;
isagCustomProperties.DbCommand = dbCommand;
isagCustomProperties.Transaction = transaction;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// constructor
/// </summary>
/// <param name="isagCustomProperties">custom properties for the component</param>
/// <param name="events">Isag events</param>
/// <param name="componentMetaData">SSIS metadata for the component</param>
/// <param name="variableDispenser">SSIS variable dispenser</param>
public TlDbCommand(IsagCustomProperties isagCustomProperties, IsagEvents events,
IDTSComponentMetaData100 componentMetaData, IDTSVariableDispenser100 variableDispenser)
{
_IsagCustomProperties = isagCustomProperties;
_events = events;
_componentMetaData = componentMetaData;
_variableDispenser = variableDispenser;
}
/// <summary>
/// Constructor (altering a table)
/// </summary>
/// <param name="properties">SSIS components properites</param>
/// <param name="sqlColumns">Sql column list</param>
/// <param name="con">Sql connection</param>
public frmCreateTable(IsagCustomProperties properties, SqlColumnList sqlColumns, SqlConnection con)
{
InitializeComponent();
this.Text = "Alter Table";
tbSql.Text = SqlCreator.GetAlterDestinationTable(properties, sqlColumns);
_con = con;
}
/// <summary>
/// Constructor (creating a table)
/// </summary>
/// <param name="properties">SSIS components properites</param>
/// <param name="con">Sql connection</param>
public frmCreateTable(IsagCustomProperties properties, SqlConnection con)
{
InitializeComponent();
this.Text = "Create Table";
tbSql.Text = SqlCreator.GetCreateDestinationTable(properties);
_con = con;
}
/// <summary>
/// load this properties from an xml string
/// </summary>
/// <param name="xml">xml string</param>
/// <returns>instance of IsagCustomProperties</returns>
public static IsagCustomProperties LoadFromXml(string xml)
{
XmlSerializer serializer = new XmlSerializer(typeof(IsagCustomProperties));
StringReader reader = new StringReader(xml);
IsagCustomProperties result = (IsagCustomProperties)serializer.Deserialize(reader);
return(result);
}
/// <summary>
/// constructor
/// </summary>
/// <param name="events">Isag events</param>
/// <param name="conn">Main sql connection</param>
/// <param name="isagCustomProperties">components custom properties</param>
/// <param name="dbCommand">Database command type</param>
/// <param name="bulkConn">Bulk csql connection</param>
/// <param name="componentMetaData">SSIS component metadata</param>
public TxAll(IsagEvents events, SqlConnection conn,
IsagCustomProperties isagCustomProperties, TlDbCommand dbCommand, SqlConnection bulkConn,
IDTSComponentMetaData100 componentMetaData)
{
_events = events;
_conn = conn;
_IsagCustomProperties = isagCustomProperties;
_dbCommand = dbCommand;
_bulkConn = bulkConn;
_componentMetaData = componentMetaData;
}
/// <summary>
/// Constructor (creating an SCD table)
/// </summary>
/// <param name="properties">SSIS components properites</param>
/// <param name="columnConfigList">Column config list</param>
/// <param name="con">Sql connection</param>
public frmCreateTable(IsagCustomProperties properties, BindingList <ColumnConfig> columnConfigList, SqlConnection con)
{
InitializeComponent();
btnOk.Enabled = false;
this.Text = "Create SCD Table";
SCDList scdList = new SCDList(columnConfigList, properties.DestinationTable);
tbSql.Text = scdList.GetCreateScdTables();
_con = con;
}
/// <summary>
/// Creates SQL Update command
/// <param name="properties">componets custom properties</param>
/// <param name="tempTableName">temporary table name</param>
/// <returns>sql update command</returns>
public static string GetSqlUpdate(IsagCustomProperties properties, string tempTableName)
{
string destTable = Brackets(properties.DestinationTable);
string tempTable = Brackets(tempTableName);
string result = "update " + destTable;
if (!properties.DisableTablock)
{
result += " WITH (tablockx)";
}
result += Environment.NewLine + "set ";
string sqlSet = "";
string sqlfrom = "from " + tempTable;
string sqlWhere = "";
foreach (ColumnConfig config in properties.ColumnConfigList)
{
if (config.Update)
{
if (sqlSet != "")
{
sqlSet += "," + Environment.NewLine + " ";
}
sqlSet += destTable + "." + Brackets(config.OutputColumnName) + "=";
if (config.HasFunction)
{
sqlSet += config.Function + " ";
}
else
{
sqlSet += tempTable + "." + Brackets(config.BulkColumnName);
}
}
//create where clause
if (config.Key)
{
if (sqlWhere != "")
{
sqlWhere += " and ";
}
sqlWhere += tempTable + "." + Brackets(config.BulkColumnName) + "=" + destTable + "." + Brackets(config.OutputColumnName);
}
}
result += sqlSet + Environment.NewLine + sqlfrom + Environment.NewLine + "where " + sqlWhere;
return(ReplacePlaceHolderInputColumn(result, tempTable + "."));
}
/// <summary>
/// Initializes custom properties
/// </summary>
/// <param name="needsStandardConfiguration">Is standard configuration needed?</param>
private void InitProperties(bool needsStandardConfiguration)
{
try
{
_IsagCustomProperties = IsagCustomProperties.Load(ComponentMetaData, needsStandardConfiguration);
}
catch (Exception ex)
{
_events.FireError(new string[] { "InitProperties", "Load", ex.Message });
}
_events = new IsagEvents(ComponentMetaData, VariableDispenser, _IsagCustomProperties.DestinationTable, _IsagCustomProperties.CustumLoggingTemplate, _IsagCustomProperties.LogLevel);
Logging.Events = _events;
}
/// <summary>
/// constructor
/// </summary>
/// <param name="input">SSIS input</param>
/// <param name="isagCustomProperties">Components custom properties</param>
/// <param name="cstr">Conectionststring</param>
/// <param name="conn">Sql connection</param>
/// <param name="events">Isag events</param>
/// <param name="dbCommandEventType">Database command event type</param>
/// <param name="dbCommandTemplate">Database command template</param>
/// <param name="status"></param>
public ThreadHandler(IDTSInput100 input, IsagCustomProperties isagCustomProperties,
string cstr, SqlConnection conn, IsagEvents events,
IsagEvents.IsagEventType dbCommandEventType, string[] dbCommandTemplate, Status status)
{
_conn = conn;
if (dbCommandTemplate != null)
{
_dbCmdThread = new ThreadDbCommand(conn, dbCommandEventType, isagCustomProperties.TimeOutDb, isagCustomProperties.Reattempts, dbCommandTemplate);
}
_maxAllowdThreads = isagCustomProperties.MaxThreadCount;
_input = input;
_isagCustomProperties = isagCustomProperties;
_timeoutDb = isagCustomProperties.TimeOutDb;
_reattempts = isagCustomProperties.Reattempts;
_cstr = cstr;
_events = events;
_status = status;
}
/// <summary>
/// Provides the component properties
/// </summary>
public override void ProvideComponentProperties()
{
base.ProvideComponentProperties();
_IsagCustomProperties = new IsagCustomProperties();
_IsagCustomProperties.SetDefaultValues();
ComponentMetaDataTools.UpdateVersion(this, ComponentMetaData);
//Set metadata version to DLL-Version
DtsPipelineComponentAttribute componentAttr =
(DtsPipelineComponentAttribute)Attribute.GetCustomAttribute(this.GetType(), typeof(DtsPipelineComponentAttribute), false);
int binaryVersion = componentAttr.CurrentVersion;
ComponentMetaData.Version = binaryVersion;
//Clear out base implmentation
this.ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
this.ComponentMetaData.InputCollection.RemoveAll();
this.ComponentMetaData.OutputCollection.RemoveAll();
//Input
IDTSInput100 input = this.ComponentMetaData.InputCollection.New();
input.Name = Constants.INPUT_NAME;
input.Description = Constants.INPUT_NAME;
input.HasSideEffects = true;
//New connection managers
IDTSRuntimeConnection100 conn = this.ComponentMetaData.RuntimeConnectionCollection.New();
conn.Name = Constants.CONNECTION_MANAGER_NAME_MAIN;
conn.Description = "Main Connection to SQL Server";
//Custom Properties hinzufügen
IDTSCustomProperty100 prop = ComponentMetaData.CustomPropertyCollection.New();
prop.Name = Constants.PROP_CONFIG;
_IsagCustomProperties.Save(ComponentMetaData);
}
/// <summary>
/// load this properties from an xml string (taken from component metadatas custom properties
/// and loads standard configuration if needed
/// </summary>
/// <param name="componentMetaData">the components metddata</param>
/// <param name="needsStandardConfiguration">Is standard configuration needed?</param>
/// <returns>instance of IsagCustomProperties</returns>
public static IsagCustomProperties Load(IDTSComponentMetaData100 componentMetaData, bool needsStandardConfiguration)
{
IsagCustomProperties properties;
try
{
properties = LoadFromXml(componentMetaData.CustomPropertyCollection[Constants.PROP_CONFIG].Value.ToString());
}
catch (Exception ex)
{
if (!needsStandardConfiguration)
{
properties = new IsagCustomProperties();
properties.SetDefaultValues();
}
else
{
throw new Exception("Cannot load the Configuration: " + ex.Message);
}
}
try
{
StandardConfiguration stdConfiguration = new StandardConfiguration(componentMetaData.RuntimeConnectionCollection, properties.AutoUpdateStandardConfiguration);
stdConfiguration.SetStandardConfiguration(ref properties);
}
catch (Exception ex)
{
if (needsStandardConfiguration)
{
throw new Exception("Cannot load Standard Configuration: " + ex.Message);
}
}
return(properties);
}
/// <summary>
/// Creates SQL insert command
/// </summary>
/// <param name="properties">componets custom properties</param>
/// <param name="tempTableName">temporary table name</param>
/// <returns>sql insert command</returns>
public static string GetSqlInsert(IsagCustomProperties properties, string tempTableName)
{
return(GetSqlInsert(properties, tempTableName, false));
}
/// <summary>
/// Creates SQL merge command (only SQL Server 2008 and above)
/// </summary>
/// <param name="properties">componets custom properties</param>
/// <param name="tempTableName">temporary table name</param>
/// <param name="overrideCustomMergeCommand">If true, only custom merge template is returned (used for preview) </param>
/// <returns>sql merge command</returns>
public static string GetSqlMerge(IsagCustomProperties properties, string tempTableName, bool overrideCustomMergeCommand)
{
if (properties.UseCustomMergeCommand && !overrideCustomMergeCommand)
{
return(properties.CustomMergeCommand);
}
else
{
string destTable = properties.DestinationTable;
string tempTable = tempTableName;
string result = "merge ";
string sqlInto = "into " + Brackets(destTable); // +" with (tablockx) as dest ";
if (!properties.DisableTablock)
{
sqlInto += " with (tablockx)";
}
sqlInto += " as dest ";
string sqlUsing = "using " + Brackets(tempTable) + " as src ";
string sqlOn = "";
string sqlUpdate = "";
string sqlInsert = "";
string sqlValues = "";
foreach (ColumnConfig config in properties.ColumnConfigList)
{
//Update
if (config.Update)
{
if (sqlUpdate != "")
{
sqlUpdate += ", ";
}
sqlUpdate += "dest." + Brackets(config.OutputColumnName) + " = ";
if (config.HasFunction)
{
sqlUpdate += config.Function + " ";
}
else
{
sqlUpdate += "src." + Brackets(config.BulkColumnName) + " ";
}
}
//Insert
if (config.Insert)
{
if (sqlInsert != "")
{
sqlInsert += ", ";
}
if (sqlValues != "")
{
sqlValues += ", ";
}
sqlInsert += Brackets(config.OutputColumnName);
//Default Values for Insert: isnull(<columnname>, <defaultValue>)
sqlValues += config.GetColumnExpression();
}
//Join
if (config.Key)
{
if (sqlOn != "")
{
sqlOn += " and ";
}
sqlOn += config.GetColumnExpression() + " = " + "dest." + Brackets(config.OutputColumnName) + " "; //"src." + Brackets(config.BulkColumnName)
}
}
sqlOn = "ON " + sqlOn;
if (sqlUpdate != "")
{
sqlUpdate = "WHEN MATCHED THEN UPDATE SET " + sqlUpdate;
}
if (sqlInsert != "")
{
sqlInsert = "WHEN NOT MATCHED BY TARGET THEN INSERT (" + sqlInsert + ") ";
sqlValues = "VALUES (" + sqlValues + ")";
}
sqlValues += ";";
result += sqlInto + Environment.NewLine + sqlUsing + Environment.NewLine + sqlOn + Environment.NewLine +
sqlUpdate + Environment.NewLine + sqlInsert + Environment.NewLine + sqlValues;
result = ReplacePlaceHolderInputColumn(result, "src.");
if (properties.HasScd)
{
SCDList scd = new SCDList(properties.ColumnConfigList, properties.DestinationTable);
result = scd.InsertIntoMergeStatement(result, properties, tempTableName.Replace("#", "SCD_"), properties.EnableIndexOnSCD);
}
return(result);
}
}
/// <summary>
/// Creates SQL insert command
/// </summary>
/// <param name="properties">componets custom properties</param>
/// <param name="tempTableName">temporary table name</param>
/// <param name="overrideCustomMergeCommand">If true, only custom sql template is returned (used for preview) </param>
/// <returns></returns>
public static string GetSqlInsert(IsagCustomProperties properties, string tempTableName, bool overrideCustomMergeCommand)
{
if (properties.UseCustomMergeCommand && !overrideCustomMergeCommand)
{
return(properties.CustomMergeCommand);
}
else
{
string insert = "";
string insertValues = "";
string insertDefault;
string insertValue;
string placeholder;
foreach (ColumnConfig config in properties.ColumnConfigList)
{
if (config.Insert)
{
if (insert != "")
{
insert += ", ";
}
if (insertValues != "")
{
insertValues += ", ";
}
insert += Brackets(config.OutputColumnName);
insertValue = Brackets(config.BulkColumnName);
if (config.HasDefault)
{
insertDefault = "isnull(cast(" + insertValue + " as " + config.DataTypeOutput + ") , "
+ config.Default + ")";
if (config.HasFunction)
{
placeholder = "@(" + config.BulkColumnName + ")";
insertValues += config.Function.Replace(placeholder, insertDefault);
}
else
{
insertValues += insertDefault;
}
}
else if (config.HasFunction)
{
placeholder = "@(" + config.BulkColumnName + ")";
insertValues += config.Function.Replace(placeholder, config.BulkColumnName);
}
else
{
insertValues += insertValue;
}
}
}
string insertTmp = "INSERT INTO " + Brackets(properties.DestinationTable);
if (!properties.DisableTablock)
{
insertTmp += " WITH (tablockx)";
}
insertTmp += Environment.NewLine +
" (" + insert + ")" + Environment.NewLine +
"SELECT " + insertValues + Environment.NewLine +
"FROM " + Brackets(tempTableName);
insert = insertTmp;
return(ReplacePlaceHolderInputColumn(insert, ""));
}
}
/// <summary>
/// Creates pseudo SQL merge command
/// (merge statement is not available for SQL Server < 2008)
/// </summary>
/// <param name="properties">componets custom properties</param>
/// <param name="tempTableName">temporary table name</param>
/// <param name="overrideCustomMergeCommand">If true, only the updated_id tablename is replaced (used for preview) </param>
/// <returns>pseudo sql merge command</returns>
public static string GetSqlMerge2005(IsagCustomProperties properties, string tempTableName, bool overrideCustomMergeCommand)
{
if (properties.UseCustomMergeCommand && !overrideCustomMergeCommand)
{
return(properties.CustomMergeCommand
.Replace(Constants.TEMP_UPD_TABLE_PLACEHOLDER, "[#updated_ids_" + Guid.NewGuid().ToString() + "]"));
}
else
{
string varUpdated_ids;
if (properties.UseCustomMergeCommand)
{
varUpdated_ids = Constants.TEMP_UPD_TABLE_PLACEHOLDER;
}
else
{
varUpdated_ids = "[#updated_ids_" + Guid.NewGuid().ToString() + "]";
}
string destTable = properties.DestinationTable;
string declare = "";
//update
string sqlUpdate = "";
string sqlUpdateOutput = "";
string sqlUpdateWhere = "";
//insert
string sqlInsert = "";
string sqlInsertValues = "";
string sqlInsertLeftJoinOn = "";
string sqlInsertLeftJoinWhere = "";
string sqlInsertValuesDefinition = "";
foreach (ColumnConfig config in properties.ColumnConfigList)
{
//Keys
if (config.Key)
{
//Declare
if (declare != "")
{
declare += ", ";
}
declare += config.OutputColumnName + " " + config.DataTypeOutput;
//Output
if (sqlUpdateOutput != "")
{
sqlUpdateOutput += ", ";
}
sqlUpdateOutput += "inserted." + Brackets(config.OutputColumnName);
//where (Update)
if (sqlUpdateWhere != "")
{
sqlUpdateWhere += " AND ";
}
sqlUpdateWhere += "src." + Brackets(config.BulkColumnName) + " = " + "dest." + Brackets(config.OutputColumnName);
//where (insert: Left Join On)
if (sqlInsertLeftJoinOn != "")
{
sqlInsertLeftJoinOn += " AND ";
}
sqlInsertLeftJoinOn += "upd." + Brackets(config.OutputColumnName) + " = " + "src." + Brackets(config.BulkColumnName);
//where (insert: Left Join WHere)
if (sqlInsertLeftJoinWhere != "")
{
sqlInsertLeftJoinWhere += " AND ";
}
sqlInsertLeftJoinWhere += "upd." + Brackets(config.OutputColumnName) + " IS NULL";
}
//Update
if (config.Update)
{
if (sqlUpdate != "")
{
sqlUpdate += ", ";
}
sqlUpdate += Brackets(config.OutputColumnName) + " = ";
if (config.HasFunction)
{
sqlUpdate += config.Function + " ";
}
else
{
sqlUpdate += "src." + Brackets(config.BulkColumnName) + " ";
}
}
//Insert
if (config.Insert)
{
if (sqlInsertValuesDefinition != "")
{
sqlInsertValuesDefinition += ", ";
}
if (sqlInsertValues != "")
{
sqlInsertValues += ", ";
}
sqlInsertValuesDefinition += Brackets(config.OutputColumnName);
if (config.HasDefault) //Default Values for Insert: isnull(<columnname>, <defaultValue>)
{
sqlInsertValues += " isnull(cast(src." + Brackets(config.BulkColumnName) + " as " + config.DataTypeOutput + ") ," + config.Default + ")";
}
else if (config.HasFunction)
{
sqlInsertValues += config.Function + " ";
}
else
{ //no Default Value or function
sqlInsertValues += "src." + Brackets(config.BulkColumnName) + " ";
}
}
}
if (sqlUpdate != "")
{
declare = "CREATE TABLE " + varUpdated_ids + " (" + declare + ");" + Environment.NewLine;
}
else
{
declare = "";
}
//update
if (sqlUpdate != "")
{
string sqlUpdateTmp = "UPDATE " + destTable + Environment.NewLine;
if (!properties.DisableTablock)
{
sqlUpdateTmp += "WITH (Tablockx)" + Environment.NewLine;
}
sqlUpdateTmp += "SET " + sqlUpdate + Environment.NewLine +
"OUTPUT " + sqlUpdateOutput + " INTO " + varUpdated_ids + Environment.NewLine +
"FROM " + destTable + " dest, " + tempTableName + " src" + Environment.NewLine +
"WHERE " + sqlUpdateWhere + ";" + Environment.NewLine;
sqlUpdate = sqlUpdateTmp;
}
//insert
sqlInsert = "INSERT INTO " + destTable;
if (!properties.DisableTablock)
{
sqlInsert += " WITH (Tablockx)";
}
sqlInsert += Environment.NewLine +
"(" + sqlInsertValuesDefinition + ")" + Environment.NewLine +
"SELECT " + sqlInsertValues + " FROM " + tempTableName + " src" + Environment.NewLine;
if (sqlUpdate != "")
{
sqlInsert +=
"LEFT JOIN " + varUpdated_ids + " upd" + Environment.NewLine +
"ON " + sqlInsertLeftJoinOn + Environment.NewLine +
"WHERE " + sqlInsertLeftJoinWhere + Environment.NewLine;
}
sqlInsert += ";";
return(ReplacePlaceHolderInputColumn(declare + sqlUpdate + sqlInsert, "src."));
}
}
/// <summary>
/// Creates SQL insert (using stored procedure) command
/// </summary>
/// <param name="properties">componets custom properties</param>
/// <param name="tempTableName">temporary table name</param>
/// <returns>sql insert (using stored procedure) command</returns>
public static string GetSqlInsertSP(IsagCustomProperties properties, string tempTableName)
{
string result = "";
string spStart =
"CREATE PROCEDURE " + "<SPName>" + Environment.NewLine +
"as" + Environment.NewLine +
"BEGIN" + Environment.NewLine +
" set nocount on" + Environment.NewLine + Environment.NewLine;
string spEnd =
Environment.NewLine +
" Close myInsertCursor" + Environment.NewLine +
" Deallocate myInsertCursor" + Environment.NewLine +
" set nocount off" + Environment.NewLine +
"END" + Environment.NewLine;
string initVar = "";
string fetch = "";
string initCursor = "";
string initCursorLeftJoin = "";
string initCursorWhere = "";
string insert = "";
string insertValues = "";
string insertDefault;
string insertValue;
foreach (ColumnConfig config in properties.ColumnConfigList)
{
if (config.Insert)
{
if (insert != "")
{
insert += ", ";
}
if (insertValues != "")
{
insertValues += ", ";
}
insert += config.OutputColumnName;
insertValue = "@(" + config.BulkColumnName + ")";
if (config.HasDefault)
{
insertDefault = "isnull(cast(" + insertValue + " as " + config.DataTypeOutput + ") , "
+ config.Default + ")";
if (config.HasFunction)
{
insertValues += config.Function.Replace(insertValue, insertDefault);
}
else
{
insertValues += insertDefault;
}
}
else if (config.HasFunction)
{
insertValues += config.Function;
}
else
{
insertValues += insertValue;
}
if (initCursor != "")
{
initCursor += ", ";
}
initCursor += Brackets(tempTableName) + "." + Brackets(config.BulkColumnName);
initVar += " Declare @" + config.BulkColumnName +
" as " + config.BulkDataType + Environment.NewLine;
if (fetch != "")
{
fetch += ", ";
}
fetch += " @" + config.BulkColumnName;
}
if (config.Key)
{
if (initCursorLeftJoin != "")
{
initCursorLeftJoin += " and ";
}
initCursorLeftJoin += Brackets(tempTableName) + "." + Brackets(config.BulkColumnName) +
" = " +
Brackets(properties.DestinationTable) + "." + Brackets(config.OutputColumnName);
if (initCursorWhere == "")
{
initCursorWhere = Brackets(properties.DestinationTable) + "." + Brackets(config.OutputColumnName) +
" Is Null";
}
}
}
insert = " insert into " + Brackets(properties.DestinationTable) + Environment.NewLine +
" (" + insert + ")" + Environment.NewLine +
" Values (" + insertValues + ")";
initCursor = Environment.NewLine +
" Declare myInsertCursor cursor FORWARD_ONLY for" + Environment.NewLine +
" Select " + initCursor + Environment.NewLine +
" from " + Brackets(tempTableName) + Environment.NewLine +
" Left Join " + Brackets(properties.DestinationTable) + Environment.NewLine +
" On " + initCursorLeftJoin + Environment.NewLine +
" Where " + initCursorWhere + Environment.NewLine + Environment.NewLine;
fetch = Environment.NewLine + Environment.NewLine +
" FETCH NEXT from myInsertCursor into" + Environment.NewLine + fetch + Environment.NewLine;
result = spStart + initVar + initCursor + " Open myInsertCursor" + fetch + Environment.NewLine +
" While @@fetch_status = 0 begin" + Environment.NewLine + Environment.NewLine + insert +
fetch.Replace(Environment.NewLine, Environment.NewLine + " ") + Environment.NewLine +
" END --while Insert" + Environment.NewLine +
spEnd;
return(ReplacePlaceHolderInputColumn(result, "@"));
}
