public static ColumnInfoCollection GetColumnData(this SqlConnection connection, string tableName)
{
#region Testing
//string oleConnectionString = connection.ConnectionString;
//if (!oleConnectionString.Contains("Provider"))
//{
// oleConnectionString = oleConnectionString.Prepend("Provider=SQLOLEDB;");
//}
//using (OleDbConnection oleConnection = new OleDbConnection(oleConnectionString))
//{
// return oleConnection.GetColumnData(tableName);
//}
#endregion Testing
const string CMD_COLUMN_INFO_FORMAT =
@"SELECT COLUMN_NAME, COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{0}'";
const string CMD_IS_PRIMARY_KEY_FORMAT =
@"SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'IsPrimaryKey') = 1
AND TABLE_NAME = '{0}'";
ColumnInfoCollection list = new ColumnInfoCollection();
try
{
ForeignKeyInfoCollection foreignKeyColumns = connection.GetForeignKeyData(tableName);
connection.Open();
using (SqlCommand command = new SqlCommand(string.Format(CMD_COLUMN_INFO_FORMAT, tableName), connection))
{
command.CommandType = CommandType.Text;
using (SqlDataReader reader = command.ExecuteReader())
{
ColumnInfo columnInfo = null;
while (reader.Read())
{
columnInfo = new ColumnInfo();
if (!reader.IsDBNull(0))
{
columnInfo.ColumnName = reader.GetString(0);
}
if (!reader.IsDBNull(1))
{
columnInfo.DefaultValue = reader.GetString(1);
}
else
{
columnInfo.DefaultValue = string.Empty;
}
if (foreignKeyColumns.Contains(columnInfo.ColumnName))
{
columnInfo.KeyType = KeyType.ForeignKey;
}
//else
//{
try
{
columnInfo.DataType = DataTypeConvertor.GetSystemType(reader.GetString(2).ToEnum <SqlDbType>(true));
}
catch (ArgumentNullException)
{
columnInfo.DataType = typeof(object);
}
catch (ArgumentException)
{
columnInfo.DataType = typeof(object);
}
//}
if (!reader.IsDBNull(3))
{
columnInfo.MaximumLength = reader.GetInt32(3);
}
if (!reader.IsDBNull(4))
{
if (reader.GetString(4).ToUpperInvariant().Equals("NO"))
{
columnInfo.IsNullable = false;
}
else
{
columnInfo.IsNullable = true;
}
}
list.Add(columnInfo);
}
}
}
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
#region Primary Keys
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = string.Format(CMD_IS_PRIMARY_KEY_FORMAT, tableName);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string pkColumn = reader.GetString(0);
ColumnInfo match = list[pkColumn];
if (match != null)
{
match.KeyType = KeyType.PrimaryKey;
}
}
}
connection.Close();
}
#endregion Primary Keys
return(list);
}
public static ColumnInfoCollection GetColumnData(this OleDbConnection connection, string tableName)
{
var restrictions = new object[] { null, null, tableName };
var foreignKeyRestrictions = new object[] { null, null, null, null, null, tableName };
connection.Open();
DataTable columnsSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, restrictions);
DataTable primaryKeySchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, restrictions);
DataTable foreignKeySchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, foreignKeyRestrictions);
connection.Close();
var columnData = new ColumnInfoCollection();
foreach (DataRow row in columnsSchema.Rows)
{
var columnInfo = new ColumnInfo
{
ColumnName = row.Field <string>("COLUMN_NAME"),
DataType = DataTypeConvertor.GetSystemType((OleDbType)row.Field <int>("DATA_TYPE")),
IsNullable = row.Field <bool>("IS_NULLABLE")
};
if (row["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
{
columnInfo.MaximumLength = row.Field <long>("CHARACTER_MAXIMUM_LENGTH");
}
columnInfo.OrdinalPosition = row.Field <long>("ORDINAL_POSITION");
if (row.Field <bool>("COLUMN_HASDEFAULT"))
{
columnInfo.DefaultValue = row.Field <string>("COLUMN_DEFAULT");
}
if (primaryKeySchema != null)
{
foreach (DataRow pkRow in primaryKeySchema.Rows)
{
if (columnInfo.ColumnName == pkRow.Field <string>("COLUMN_NAME"))
{
columnInfo.KeyType = KeyType.PrimaryKey;
break;
}
}
}
if (columnInfo.KeyType == KeyType.None)
{
if (foreignKeySchema != null)
{
foreach (DataRow fkRow in foreignKeySchema.Rows)
{
if (columnInfo.ColumnName == fkRow.Field <string>("FK_COLUMN_NAME"))
{
columnInfo.KeyType = KeyType.ForeignKey;
break;
}
}
}
}
columnData.Add(columnInfo);
}
columnsSchema.DisposeIfNotNull();
primaryKeySchema.DisposeIfNotNull();
foreignKeySchema.DisposeIfNotNull();
return(columnData);
}