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;
public ConnectionStringSettings ConnectionStringSettings
{
get { return connectionStringSettings; }
private set { connectionStringSettings = value; }
}
private ISchemaProvider schemaProvider;
#endregion Propeteries and fields
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;
}
}
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;
public string Name
{
get { return name; }
set { name = value; }
}
private int length;
public int Length
{
get { return length; }
set { length = value; }
}
private DbType dataType;
public DbType DataType
{
get { return dataType; }
set { dataType = value; }
}
private Constraints constraint;
public Constraints Constraint
{
get { return constraint; }
set { constraint = value; }
}
private bool isnullAlble;
public bool IsNullAble
{
get { return isnullAlble; }
set { isnullAlble = value; }
}
}
}
Constraints:
Code:
using System;
namespace DbSchema
{
public enum Constraints
{
None,
PrimaryKey,
ForeignKey,
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
{
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;
public List<ColumnInfo> Columns
{
get { return columns; }
set { columns = value; }
}
private string tableName;
public string TableName
{
get { return tableName; }
set { tableName = value; }
}
#endregion Fields and propeteries
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.
