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