Schema czyli jak pobrać informacje o tabeli w MS SQL

Często zdarza się że w programie potrzebujemy pobrać informację nt. tabeli. Np. chcemy dowiedzieć się jakiego typu jest dana kolumna czy też poznać długość kolumny znakowej. Przydaje się to np. kiedy chcemy stworzyć kolumnę w Gridzie która będzie mogła pomieścić cały string.

SQL Server przechowuje dane nt. schemy bazy danych w widokach które można znaleźć w schemie INFORMATION_SCHEMA.

Mamy tutaj takie widoki jak:
CHECK_CONSTRAINTS - zwraca informacje nt. checków w bazie
COLUMN_DOMAIN_USAGE - zwraca informacje do których obiektów bazy użytkownik ma dostęp
COLUMN_PRIVILEGES - zwraca informacje nt. tego czy dana kolumna ma przypisanego użytkownika
COLUMNS - zwraca wszystkie kolumny w danej bazie
CONSTRAINT_COLUMN_USAGE - zwraca ograniczenia nałożone na kolumny
CONSTRAINT_TABLE_USAGE - zwraca ograniczenia nałożone na tabelę
TABLES - zwraca wszystkie tabele w danej bazie
VIEW_COLUMN_USAGE - zwraca nazwy kolumn użytych w widokach
VIEWS - zwraca wszystkie widoki dla danej bazy
TABLE_CONSTRAINTS - zwraca ograniczenia nałożone na tabele

Pobranie np. wszystkich kolumn które tyczą się tabeli Address przedstawia się następująco:

Code:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name='Address'

Oczywiście informacje w taki sposób są przydane, jednak aby wykorzystać je w projekcie przydałaby się jakaś klasa umożliwiająca łatwe ich pobranie. Stworzyłem przykładowy projekt umożliwiający pobranie podstawowych informacji nt. tabeli której zawartość chcemy zbadać. Zobaczmy na schemat:

Kilka słów objaśnienia na czym całość polega.
Na początku myślałem że stworzę prostą klasę która zwróci co trzeba i na tym się skończy cała zabawa. Jednak po przemyśleniu sprawy doszedłem do wniosku że takie podejście nie prowadzi do niczego dobrego. Chodzi przede wszystkim o możliwość dalszego rozwoju aplikacji i dodanie nowych możliwości.

DbSchema - główna klasa całego przedsięwzięcia. Zwraca Schemat danej tabeli za pomocą metody GetSchema. Tutaj następuje także inicjalizacja, jaki provider ma zostać użyty do wyciągnięcia danych z odnośnie schemy. Wykorzystuje tutaj wzorzec projektowy Strategy.

ISchemaProvider - interfejs z jedną metodą zwracającą schemę tabeli

ISchemaCreator - reprezentuje interfejs przedstawiający w jaki sposób będzie pozyskiwana schma tabeli

TableSchema - obiekt reprezentujący schemę tabeli - najważniejszą tutaj właściwością jest Columns czyli lista kolumn danej tabeli

ColumnInfo - przedstawia pojedynczą kolumnę w tabeli. Mamy takie informacje jak ograniczenie, typ danych, czy pole może przechowywać pola typu null, długość pola tekstowego oraz oczywiście nazwę kolumny

Constrains - typ wyliczeniowy reprezentujący ograniczenia mogące być nałożone na kolumnę

Dwie klasy pomocnicze SqlDataTypes oraz SqlConstrains pozwalają w łatwy sposób zwrócić wartość odpowiedniego typu wyliczeniowego.

Sam kod biblioteki przedstawia się następująco:

DbSchema:

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;

namespace DbSchema
{
    public class DbSchema
    {
        #region Propeteries and fields
        private ConnectionStringSettings connectionStringSettings;
        /// <summary>
        /// Connection string settings (from app.config for exaple)
        /// </summary>
        public ConnectionStringSettings ConnectionStringSettings
        {
            get { return connectionStringSettings; }
            private set { connectionStringSettings = value; }
        }

        /// <summary>
        /// Schema provider 
        /// </summary>
        private ISchemaProvider schemaProvider;

        #endregion Propeteries and fields

        /// <summary>
        /// Default constructor
        /// </summary>
        /// <param name="connectionString">Connection string information (from App.setings for example)</param>
        public DbSchema(ConnectionStringSettings connectionStringSettings)
        {
            this.connectionStringSettings = connectionStringSettings;
            CreateProvider();
        }

        private void CreateProvider()
        {
            switch (connectionStringSettings.ProviderName)
            {
                case "System.Data.SqlClient":
                    schemaProvider = new SqlSchemaProvider(connectionStringSettings.ConnectionString);
                    break;
                default:
                    break;
            }
        }

        /// <summary>
        /// Get Schema of table
        /// </summary>
        /// <param name="tableName">Table name</param>
        /// <returns></returns>
        public TableSchema GetSchema(string tableName)
        {
            return schemaProvider.GetTableSchema(tableName);
        }
    }
}

ColumnInfo:

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DbSchema
{
    public class ColumnInfo
    {
        private string name;
        /// <summary>
        /// Get Column name
        /// </summary>
        public string Name
        {
            get { return name; }
            set { name = value; }
        }

        private int length;
        /// <summary>
        /// Get column data length (especialy usesful for character fields)
        /// </summary>
        public int Length
        {
            get { return length; }
            set { length = value; }
        }

        private DbType dataType;
        /// <summary>
        /// Get column data type
        /// </summary>
        public DbType DataType
        {
            get { return dataType; }
            set { dataType = value; }
        }

        private Constraints constraint;
        /// <summary>
        /// Constrain for column
        /// </summary>
        public Constraints Constraint
        {
            get { return constraint; }
            set { constraint = value; }
        }

        private bool isnullAlble;
        /// <summary>
        /// Determin if column can be null
        /// </summary>
        public bool IsNullAble
        {
            get { return isnullAlble; }
            set { isnullAlble = value; }
        }

    }
}

Constraints:

Code:

using System;

namespace DbSchema
{
    public enum Constraints
    {
        /// <summary>
        /// No constrains on column
        /// </summary>
        None,
        /// <summary>
        /// Primary key
        /// </summary>
        PrimaryKey,
        /// <summary>
        /// Foreign key
        /// </summary>
        ForeignKey,
        /// <summary>
        /// Check
        /// </summary>
        Check
    }
}

ISchemaCeationProcess:

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DbSchema
{
    public interface ISchemaCreationProcess
    {
        List<ColumnInfo> GetColumns(string tableName);
        void AddConstraints(string tableName, List<ColumnInfo> columns);
    }
}

ISchemaProvider:

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DbSchema
{
    public interface ISchemaProvider
    {
        /// <summary>
        /// Getting Schema for table
        /// </summary>
        /// <param name="tableName">Table name for which we want to get schema</param>
        /// <returns>Return table schema</returns>
        TableSchema GetTableSchema(string tableName);
    }
}

SqlConstraints:

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DbSchema
{
    public static class SqlConstrains
    {
        public static Constraints GetConstrains(string constrains)
        {
            Constraints constrain = Constraints.PrimaryKey;
            switch (constrains)
            {
                case "FOREIGN KEY":
                    constrain = Constraints.ForeignKey;
                    break;
                case "PRIMARY KEY":
                    constrain = Constraints.PrimaryKey;
                    break;
                case "CHECK":
                    constrain = Constraints.Check;
                    break;
            }

            return constrain;
        }
    }
}

SqlDataTypes:

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DbSchema
{
    public static class SqlDataTypes
    {
        public static DbType GetType(string type)
        {
            DbType dbType = DbType.String;
            switch (type)
            {
                case "int":
                    dbType = DbType.Int32;
                    break;
                case "nvarchar":
                    dbType = DbType.String;
                    break;
                case "datetime":
                    dbType = DbType.DateTime;
                    break;
            }

            return dbType;
        }
    }
}

SqlSchemaProvider:

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;

namespace DbSchema
{
    public class SqlSchemaProvider : ISchemaProvider, ISchemaCreationProcess
    {
        private string connectionString;

        public SqlSchemaProvider(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public TableSchema GetTableSchema(string tableName)
        {
            TableSchema tableSchema = new TableSchema(tableName);
            List<ColumnInfo> columns = GetColumns(tableName);
            tableSchema.Columns = columns;
            AddConstraints(tableName, columns);
            return tableSchema;
        }

        public List<ColumnInfo> GetColumns(string tableName)
        {
            List<ColumnInfo> columns = new List<ColumnInfo>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = @"select column_name, is_nullable, data_type, character_maximum_length
                                        from INFORMATION_SCHEMA.COLUMNS
                                        WHERE table_name=@table";
                    cmd.Parameters.AddWithValue("@table", tableName);
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                ColumnInfo columnInfo = new ColumnInfo();
                                columnInfo.Name = (string)dr["column_name"];
                                columnInfo.DataType = SqlDataTypes.GetType((string)dr["data_type"]);
                                columnInfo.IsNullAble = (string)dr["is_nullable"] == "YES" ? true : false;
                                if (dr["character_maximum_length"] != DBNull.Value)
                                {
                                    columnInfo.Length = int.Parse(dr["character_maximum_length"].ToString());
                                }
                                columns.Add(columnInfo);
                            }
                            dr.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }

            return columns;
        }

        public void AddConstraints(string tableName, List<ColumnInfo> columns)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = @"SELECT constraint_type, column_name
                                        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu
                                         ON tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME 
                                        WHERE tc.table_name=@table";
                    cmd.Parameters.AddWithValue("@table", tableName);
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                string columnName = (string)dr["column_name"];
                                columns.Find(x => x.Name == columnName).Constraint = SqlConstrains.GetConstrains(dr["constraint_type"].ToString());
                                dr.Close();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }
    }
}

TableSchema:

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DbSchema
{
    public class TableSchema
    {
        #region Fields and propeteries
        private List<ColumnInfo> columns;
        /// <summary>
        /// Column list for table
        /// </summary>
        public List<ColumnInfo> Columns
        {
            get { return columns; }
            set { columns = value; }
        }

        private string tableName;
        /// <summary>
        /// Table name for which schema is creating
        /// </summary>
        public string TableName
        {
            get { return tableName; }
            set { tableName = value; }
        }
        
        #endregion Fields and propeteries

        /// <summary>
        /// Default constructor 
        /// </summary>
        /// <param name="tableName">Table name</param>
        public TableSchema(string tableName)
        {
            this.tableName = tableName;
        }
    }
}

Powyższa implementacja oczywiście nie wyczerpuje tematu. Wraz z rozwojem dojdą z pewnością nowe typy, pola oraz oczywiście same providery.

Powyższa implementacja miała za zadanie pokazanie w jaki sposób można się dobrać do informacji nt. schemy tabel w MS SQL Serverze.