/// <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); }