/// <summary>
/// Method retrives the columns of the destination sql table
/// </summary>
/// <returns></returns>
private void GetSqlTableColumns()
{
string tableName = m_SqlTableName;
string sql = string.Format("select COLUMN_NAME as ColumnName ,DATA_TYPE as DataType, CHARACTER_MAXIMUM_LENGTH as MaximumLength FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'{0}'", tableName);
using (SqlConnection connection = new SqlConnection(m_SqlConnectionString))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
try
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int columnNameOrdinal = reader.GetOrdinal("ColumnName");
int dataTypeOrdinal = reader.GetOrdinal("DataType");
int maximumLengthOrdinal = reader.GetOrdinal("MaximumLength");
string columnName = reader.GetString(columnNameOrdinal);
string columnDataType = reader.GetString(dataTypeOrdinal);
int columnLength = 0;
bool maxLengthColumnIsNull = reader.IsDBNull(maximumLengthOrdinal);
if (!maxLengthColumnIsNull)
{
columnLength = reader.GetInt32(maximumLengthOrdinal);
}
bool columnAlreadyAdded = m_SqlTableColumns.ContainsKey(columnName);
if (!columnAlreadyAdded)
{
SqlTableColumnSchema columnSchema = new SqlTableColumnSchema();
columnSchema.ColumnName = columnName;
columnSchema.DataType = columnDataType;
columnSchema.MaximumLength = columnLength;
m_SqlTableColumns.Add(columnName, columnSchema);
}
}
}
}
catch (Exception exception)
{
string errorMessage = "Failed to retrieve if destination table’s columns. Please verify that sql user account has the necessary permissions to perform this action.";
throw new ApplicationException(errorMessage, exception);
}
}
}
}
public List <SqlTableColumnSchema> GetMissingColumns()
{
List <SqlTableColumnSchema> missingColumns = new List <SqlTableColumnSchema>();
foreach (DataColumn column in m_TableSchema.Columns)
{
string columnName = column.ColumnName;
bool columnExistsInTable = m_SqlTableColumns.ContainsKey(columnName);
if (!columnExistsInTable)
{
SqlTableColumnSchema newColumnSchema = new SqlTableColumnSchema();
newColumnSchema.ColumnName = columnName;
newColumnSchema.DataType = SQLGetType(column);
newColumnSchema.MaximumLength = 0;
}
}
return(missingColumns);
}