/// <summary>
/// Adds an sql column list
/// </summary>
/// <param name="sqlColumns"></param>
public void AddSqlColumnDefinitions(SqlColumnList sqlColumns)
{
foreach (ColumnConfig config in this.ColumnConfigList)
{
config.SetSqlColumnDefinitions(sqlColumns);
}
}
/// <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>
/// Creates, adds and returns a column configuration
/// </summary>
/// <param name="sqlColumns">Sql column list</param>
/// <returns>column configuration</returns>
public ColumnConfig AddColumnConfig(SqlColumnList sqlColumns)
{
ColumnConfig result = new ColumnConfig();
result.SetSqlColumnDefinitions(sqlColumns);
result.Default = "";
result.Function = "";
this.ColumnConfigList.Add(result);
return(result);
}
/// <summary>
/// Creates an sql column list from a database table
/// </summary>
/// <param name="con">sql connection</param>
/// <param name="destinationTableName">destination table name</param>
/// <returns>Sql column list</returns>
public SqlColumnList AddSqlColumnDefinitions(SqlConnection con, string destinationTableName)
{
SqlColumnList result = new SqlColumnList();
try
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
DataTable schema;
SqlConnection sqlConnnection = con;
SqlCommand sqlCom = sqlConnnection.CreateCommand();
sqlCom.CommandText = "select TOP 0 * from " + SqlCreator.Brackets(destinationTableName);
SqlDataReader reader = sqlCom.ExecuteReader(CommandBehavior.KeyInfo);
schema = reader.GetSchemaTable();
foreach (DataRow row in schema.Rows)
{
result.Add(row["ColumnName"].ToString(), row["DataTypeName"].ToString(), row["DataType"].ToString(),
Int32.Parse(row["ColumnSize"].ToString()), Int32.Parse(row["NumericPrecision"].ToString()),
Int32.Parse(row["NumericScale"].ToString()),
(row["IsKey"].ToString() == "True"), (row["IsAutoIncrement"].ToString() == "True"), (row["AllowDBNull"].ToString() == "True"));
}
sqlConnnection.Close();
foreach (ColumnConfig config in this.ColumnConfigList)
{
config.SetSqlColumnDefinitions(result);
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
/// <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>
/// Sets list of description of sql columns
/// </summary>
/// <param name="sqlColumns">sql columns</param>
public void SetSqlColumnDefinitions(SqlColumnList sqlColumns)
{
_sqlColumns = sqlColumns;
SetOutputColumnDefinition();
}