Skip to content

Instantly share code, notes, and snippets.

@a7md0
Last active April 30, 2021 17:50
Show Gist options
  • Save a7md0/a23b4097ab2d9fc46d357cf53a0d4e2d to your computer and use it in GitHub Desktop.
Save a7md0/a23b4097ab2d9fc46d357cf53a0d4e2d to your computer and use it in GitHub Desktop.
Parse SqlDataReader.GetSchemaTable into useable object
using System.Data.SqlClient;
var connection = new SqlConnection(Properties.Settings.Default.ConnectionString);
connection.Open();
using (var command = connection.CreateCommand()) {
command.CommandText = $"SELECT * FROM [TableNameHere] WHERE 1 = 0;";
using (var reader = command.ExecuteReader(CommandBehavior.SchemaOnly)) {
var schema = reader.GetSchemaTable();
var tableSchema = new TableSchema(schema);
Console.WriteLine(tableSchema["Id"].IsAutoIncrement); // Will print either true or false
}
}
connection.Close();
using System;
using System.Data;
using System.Collections.Generic;
public class TableSchema {
private Dictionary<string, TableSchemaColumn> columns;
public TableSchema(DataTable dataTable) {
this.columns = new Dictionary<string, TableSchemaColumn>();
var properties = typeof(TableSchemaColumn).GetProperties();
foreach (DataRow row in dataTable.Rows) {
var column = new TableSchemaColumn();
foreach (var property in properties) {
var value = row[property.Name];
if (value is DBNull) {
value = null;
}
property.SetValue(column, value);
}
this.columns.Add(column.ColumnName, column);
}
}
public TableSchemaColumn this[string column] => this.columns[column];
}
public class TableSchemaColumn {
public string ColumnName { get; set; }
public int ColumnOrdinal { get; set; }
public int ColumnSize { get; set; }
public short NumericPrecision { get; set; }
public short NumericScale { get; set; }
public bool IsUnique { get; set; }
public bool IsKey { get; set; }
public string BaseServerName { get; set; }
public string BaseCatalogName { get; set; }
public string BaseColumnName { get; set; }
public string BaseSchemaName { get; set; }
public string BaseTableName { get; set; }
public Type DataType { get; set; }
public bool AllowDBNull { get; set; }
public int ProviderType { get; set; }
public bool IsAliased { get; set; }
public bool IsExpression { get; set; }
public bool IsIdentity { get; set; }
public bool IsAutoIncrement { get; set; }
public bool IsRowVersion { get; set; }
public bool IsHidden { get; set; }
public bool IsLong { get; set; }
public bool IsReadOnly { get; set; }
public Type ProviderSpecificDataType { get; set; }
public string DataTypeName { get; set; }
public string XmlSchemaCollectionDatabase { get; set; }
public string XmlSchemaCollectionOwningSchema { get; set; }
public string XmlSchemaCollectionName { get; set; }
public string UdtAssemblyQualifiedName { get; set; }
public int NonVersionedProviderType { get; set; }
public bool IsColumnSet { get; set; }
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment