Introduction
The DAL are a collection of classes that sit between your
business logic code and the .net database classes like “SqlCommand”.
There are 2 benefits:
·
Wrapping the low level .net classes with something a bit more
code friendly
·
Abstracting the database, you should be able to swap the
database, for example; Microsoft SQL to MySQL without your application noticing
The DAL includes methods for retrieving a database’s schema
(table definitions, layout) at run time. You may think of a database schema as
a static thing that you design before you start coding and make the odd change
here and there, other than that your only reference to the layout is the
diagram that you started with. That may well be true in many situations, but
having control of database schemas in code lets you really throw data around
quickly and with a few lines of code; you can copy a database to a different
server type, quickly visualise the schema (reverse engineer), maybe just load
some data into a temporary (throw away) database to help you analyse some data
quickly.
There are 3 standard ways of getting schema information:
·
Reader – Execute a DataReader with the flags “SchemaOnly” and
“KeyInfo” then call “GetSchemaTable()”.
·
Standard – Call the “GetSchema()” method on Connection object.
·
Native - Directly querying the system tables of the database
By digging around in these tables you can find column and
key information on the tables. The thing is it’s all a bit of a mess, you
cannot get to everything (like foreign key columns) through the “Reader” or
“Standard” method, also different databases use these tables in slightly
different ways (like using different types). What this all boils down to is
that you cannot write database portable code using these 3 available methods.
The DAL includes the methods SchemaReader(),
SchemaStandard(), and SchemaNative() so you can look at these low level tables
if you want, but you shouldn’t need to use them.
The DAL attempts to solve this schema nightmare by providing
another method called “Schema()” it’s results can be used to quickly visualise
a database’s schema and use it in a portable way with other database types.
The Schema() method on the DAL will return a DataSet with 2
tables (Columns & Keys) for each table in the current database (There is
also an overload that allows you to specify which tables).
_Columns Table
|
ColumnName
|
Ordinal
|
DataTypeId
|
MaxLength
|
Precision
|
Scale
|
PrimaryKey
|
AllowDBNull
|
AutoIncrement
|
Longest
|
|
String*
|
Int32
|
String
|
Int32
|
Int32
|
Int32
|
Boolean
|
Boolean
|
Boolean
|
Int32
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
_Keys Table
|
KeyName
|
KeyType
|
Ordinal
|
ColumnName
|
Decending
|
ForeignTable
|
ForeignColumn
|
|
String
|
String
|
Int32
|
String
|
Boolean
|
String
|
String
|
The Spludlow Schema format is highly simplified to make
viewing and coding with it as simple as possible, it only includes the very
basics that are required.
KeyTypes
·
P Primary All tables should have a
primary key it is always unique and don’t’ allow nulls.
·
U Unique The column(s) must be unique
within the table.
·
I Index Index to improve
performance when querying on this column(s).
·
F Foreign Referential Integrity to a
primary key of the parent table. For example, an order’s customer ID must exist
in the customer table.
·
T Text Full Text Index
NOTE: You can get away with not using a primary key if your
table does not need records being found directly. For example; you may have a
temporary table where you will always go through the whole table a row at a
time. Keys do have a performance penalty that may be noticeable when inserting
large amounts of rows (but will always benefit reads).
This schema DataSet then can be logged for easy viewing,
saved out as text tables, used by the DAL to create clones of the database, or
in code for whatever you require.
There is also a total nightmare going off with database
types, besides different databases having different types or calling them
different things (you would expect this anyway), there are specific datatype
enums that don’t always match the datatype names, and there are the CLR types
and some providers use the standard DBType enum that’s another enum.
The DAL resolves this balls up by defining another datatype
definition (“DataTypeId”) with mappings to; CLR types, Generic datatypes
(DbType), then 2 mappings for each supported database type for database
datatype name and the datatype enum, used in code, (which don’t always match).
These mappings are kept in the “DataTypes.txt” file, additional database types
can be supported by adding 2 more columns to the table for each. The mapping is
not perfect as all databases are different but it should get the job done most
of the time, you should only really hit problems if your database is using daft
datatypes.
Here are some of the first columns of the table including
SQL Server and MySQL mappings:
|
DataTypeId
|
TypeCode
|
DbType
|
SQL
|
SqlDbType
|
MySql
|
MySqlDbType
|
|
String*
|
String
|
String
|
String
|
String
|
String
|
String
|
|
Boolean
|
Boolean
|
Boolean
|
bit
|
Bit
|
bit
|
Bit
|
|
UInt8
|
Byte
|
Byte
|
tinyint
|
TinyInt
|
tinyint
|
UByte
|
|
Int8
|
SByte
|
SByte
|
tinyint
|
TinyInt
|
tinyint unsigned
|
Byte
|
|
Int16
|
Int16
|
Int16
|
smallint
|
SmallInt
|
smallint
|
Int16
|
|
UInt16
|
UInt16
|
UInt16
|
smallint
|
SmallInt
|
smallint unsigned
|
UInt16
|
|
Int32
|
Int32
|
Int32
|
int
|
Int
|
int
|
Int32
|
|
UInt32
|
UInt32
|
UInt32
|
int
|
Int
|
int unsigned
|
UInt32
|
|
Int24
|
Int32
|
Int32
|
int
|
Int
|
mediumint
|
Int24
|
|
UInt24
|
UInt32
|
UInt32
|
int
|
Int
|
mediumint unsigned
|
UInt24
|
|
Int64
|
Int64
|
Int64
|
bigint
|
BigInt
|
bigint
|
Int64
|
|
UInt64
|
UInt64
|
UInt64
|
bigint
|
BigInt
|
bigint unsigned
|
UInt64
|
|
Decimal
|
Decimal
|
Decimal
|
decimal
|
Decimal
|
decimal
|
Decimal
|
|
Float
|
Single
|
Single
|
real
|
Real
|
float
|
Float
|
|
Double
|
Double
|
Double
|
float
|
Float
|
double
|
Double
|
|
DateTime2
|
DateTime
|
DateTime2
|
datetime2
|
DateTime2
|
datetime
|
DateTime
|
|
DateTime
|
DateTime
|
DateTime
|
datetime
|
DateTime
|
datetime
|
DateTime
|
|
Date
|
DateTime
|
Date
|
date
|
Date
|
date
|
Date
|
|
DateTimeOffset
|
DateTime
|
DateTimeOffset
|
datetimeoffset
|
DateTimeOffset
|
datetime
|
DateTime
|
|
Time
|
DateTime
|
Time
|
time
|
Time
|
time
|
Time
|
|
NVarChar
|
String
|
String
|
nvarchar
|
NVarChar
|
varchar
|
VarChar
|
|
NChar
|
String
|
StringFixedLength
|
nchar
|
NChar
|
char
|
String
|
|
VarChar
|
String
|
AnsiString
|
varchar
|
VarChar
|
varchar
|
VarChar
|
|
Char
|
String
|
AnsiStringFixedLength
|
char
|
Char
|
char
|
String
|
|
Money
|
Decimal
|
Currency
|
money
|
Money
|
decimal
|
Decimal
|
|
Type
|
Type
|
String
|
nvarchar
|
NVarChar
|
varchar
|
VarChar
|
|
Guid
|
Guid
|
Guid
|
uniqueidentifier
|
UniqueIdentifier
|
varchar
|
VarChar
|
|
VarBinary
|
Byte[]
|
Binary
|
varbinary
|
VarBinary
|
varbinary
|
VarBinary
|
|
Binary
|
Byte[]
|
Binary
|
binary
|
Binary
|
binary
|
Binary
|
|
Enum
|
Enum
|
String
|
nvarchar
|
NVarChar
|
varchar
|
VarChar
|
|
Object
|
Object
|
Object
|
nvarchar
|
NVarChar
|
varchar
|
VarChar
|
|
Xml
|
String
|
String
|
xml
|
Xml
|
varchar
|
VarChar
|
|
RowVersion
|
UInt64
|
UInt64
|
rowversion
|
Timestamp
|
bigint unsigned
|
UInt64
|
|
Json
|
String
|
String
|
nvarchar
|
NVarChar
|
json
|
JSON
|