ADO.NET – Connected Layer

Intro

The connected layer provides functionality for running SQL syntax against a connected database. The statements can be select, insert, update or delete as well as execute schema statements and the ability to run stored procedures and functions.

The connected layer requires the database connection to remain open while the database transactions are being performed.

Source Code

All source code can be found on GitHub here.

This is part of my HowTo in .NET seriies. An overview can be seen here,

Command Objects

The Command object represents a SQL statement to be run; select, insert, update, delete, execute schema, execute stored procedure etc.

The DbCommand is configured to execute an sql statement or stored procedure via the CommandText property.

The CommandType defines the type of sql statement to be executed which is stored in the CommandText property.

CommandType

Description

StoredProcedure

CommandText contains the name of a StoredProcedure or UserFunction.

TableDirect

CommandText contains a table name. All rows and columns will be returned from the table.

Text

CommandText defines an SQL statement to be executed.

The CommandType will default to Text however it is good practice to explicitly set it.

var connectionDetails =
ConfigurationManager.ConnectionStrings ["MyDatabase"];

var providerName = connectionDetails.ProviderName;
var connectionString = connectionDetails.ConnectionString;

var dbFactory = DbProviderFactories.GetFactory (providerName);

using(var cn = dbFactory.CreateConnection())
{
cn.ConnectionString = connectionString;
cn.Open();

var cmd = cn.CreateCommand();

cmd.CommandText = "Select * from MyTable";
cmd.CommandType = CommandType.Text;
cmd.Connection = cn;
}

The command should be passed the connection via the Connection property.

The command object will not touch the database until the either the ExecuteReader, ExecuteNonQuery or equivalent has been called upon.

DataReader

The DataReader class allows a readonly iterative view of the data returned from a configured command object.

A DataReader is instantiated by calling ExecuteReader upon the command object.

The DataReader exposes a forward only iterator via the Read method which returns false when the collection has been completely iterated.

The DataReader represents both the collection of records and also the individual records; access to the data is made by calling methods upon the DataReader.

DataReader implements IDisposable and should be used within a using scope.

Data can be accessed by field name and also ordinal position via the index method[] which returns the data cast into object. Alternatively methods exist to get the data for any .NET primitive type.

using (var dr = cmd.ExecuteReader ()) {
while (dr.Read ()) {
var val1 = (string)dr ["FieldName"];
var val2 = (int)dr [0];

// Get the field name or its ordinal position
var name = dr.GetName (1);
var pos = dr.GetOrdinal ("FieldName");

// Strongly Types Data
var val3 = dr.GetInt32 (1);
var val4 = dr.GetDecimal (2);
var val5 = dr.GetDouble (3);
var val6 = dr.GetString (4);

var isNull = dr.IsDBNull (5);

var fdType = dr.GetProviderSpecificFieldType (6);
}
}

The IsDBNull method can be used to determine if a value contains a null. A .NET type representation of the contained field can be retrieved with the GetProviderSpecificFieldType method.

Multiple Results

If the select statement of a command has multiple results the DataReader.NextResult() method exposes a forward only iterator through each result group:

string strSQL = "Select * From MyTable;Select * from MyOtherTable";

NextResult increments to the next result group, just like Read it returns false once the collection has been exhausted:

using (var dr = cmd.ExecuteReader ()) {
while (dr.NextResult ()) {
while (dr.Read ()) {
}
}
}

DataSet

DataReader can be used to fill a DataSet. We talk more about DataSet within the disconnected layer in the next chapter.

var dtable = new DataTable();

using(var dr = cmd.ExecuteReader())
{
dtable.Load(dr);
}

ExecuteNonQuery

ExecuteNonQuery allows execution of an insert, update or delete statement as well as executing a schema statement.

The method returns an integral representing the number of affected rows.

using (var cn = dbFactory.CreateConnection ()) {
cn.ConnectionString = connectionString;
cn.Open ();

var cmd = cn.CreateCommand ();

cmd.CommandText = @"Insert Into MyTable (FieldA) Values ('Hello’)";
cmd.CommandType = CommandType.Text;
cmd.Connection = cn;

var count = cmd.ExecuteNonQuery ();
}

Command Parameters

Command parameters allow configuring stored procedure parameters as well as parameterized sql statements which can help protect against SQL injection attacks.

It is strongly advised that any information collected from a user should be sent to the database as parameter regardless if it is to be persisted or used within a predicate.

Parameters can be added with the Command.Parameters.AddWithValue or by instantiating a DbParameter class.

The example below shows the former.

using (var cn = dbFactory.CreateConnection ()) {
cn.ConnectionString = connectionString;
cn.Open ();

var cmd = cn.CreateCommand ();

cmd.CommandText = @"Insert Into MyTable (FieldA) Values (@Hello)";
cmd.CommandType = CommandType.Text;

var param = cmd.CreateParameter ();

param.ParameterName = "@Hello";
param.DbType = DbType.String;
param.Value = "Value";
param.Direction = ParameterDirection.Input;
cmd.Parameters.Add (param);

cmd.Connection = cn;

var count = cmd.ExecuteNonQuery ();
}

The DbCommand has a DbType property which allows setting the type of the parameter, it is vendor agnostic.

SqlCommand and MySqlCommand also provide SqlDbType and MySqlDbType which can be used to set the type field from a vendor specific enum. Setting the DbType will maintain the vendor specific column and vice versa.

Executing a Stored Procedure

A stored procedure is executed by configuring a DbCommand against the name of the stored procedure along with any required parameters followed by calling ExecuteScalar or ExecuteReader.

ExecuteScalar is used to return a single value. If multiple result sets, rows and columns are returned it will return the first column from the first row in the first result set.

Parameters can either be input or output.

using(var cn = dbFactory.CreateConnection())</span></pre>
{
cn.ConnectionString = connectionString;
cn.Open();

var cmd = cn.CreateCommand();
cmd.CommandText = "spGetFoo";
cmd.Connection = cn;

cmd.CommandType = CommandType.StoredProcedure;

// Input param.
var paramOne = cmd.CreateParameter();
paramOne.ParameterName = "@inParam";
paramOne.DbType = DbType.Int32;
paramOne.Value = 1;
paramOne.Direction = ParameterDirection.Input;
cmd.Parameters.Add(paramOne);

// Output param.
var paramTwo = cmd.CreateParameter();
paramTwo.ParameterName = "@outParam";
paramTwo.DbType = DbType.String;
paramTwo.Size = 10;
paramTwo.Direction = ParameterDirection.Output;
cmd.Parameters.Add(paramTwo);

// Execute the stored proc.
var count = cmd.ExecuteScalar();

// Return output param.
var outParam = (int)cmd.Parameters["@outParam"].Value;

// This can be made on the parameter directly
var outParam2 = (int)paramTwo.Value;
}

 

Member name

Description

Input

The parameter is an input parameter (default).

InputOutput

The parameter is capable of both input and output.

Output

The parameter is an output parameter and has be suffixed with the out keyword in the parameter list of a stored procedure, built in function or user defined function.

ReturnValue

The parameter is a return value, scalar or similar but not a return set. This is determined by the return keyword in a stored procedure, built in function or user defined function.

If the stored procedure returns a set and not a single value the ExecuteReader can be used to iterate over the result:

using (var cn = dbFactory.CreateConnection ()) {
cn.ConnectionString = connectionString;
cn.Open ();

var cmd = cn.CreateCommand ();
cmd.CommandText = "spGetFoo";
cmd.Connection = cn;

cmd.CommandType = CommandType.StoredProcedure;

using (var dr = cmd.ExecuteReader ()) {
while (dr.NextResult ()) {
while (dr.Read ()) {
}
}
}
}

Database Transactions

When the writable transaction of a database involves more than one writable actions, it is essential that all the actions are wrapped up in a unit of work called a database transaction.

ACID

The desired characteristics of a transaction are defined by the term ACID:

Member name

Description

Atomicity

All changes within a unit of work complete or none complete; they are atomic.

Consistency

The state of the data is consistent and valid. If upon completing all of the changes the data is considered invalid, all the changes are undone to return the data to the original state.

Isolation

All changes within a unit of work occur in isolation from other readable and writable transactions.

No one can see any changes until all changes have been completed in full.

Durability

Once all the changes within a unit of work have completed, all the changes will be persisted.

No one can see any changes until all changes have been completed in full.
Durability
Once all the changes within a unit of work have completed, all the changes will be persisted.

Syntax

A transaction is started through the connection object and attached to any command which should be run in the same transaction.

Commit should be called upon the transaction upon a successful completion while Rollback should be called if an error occured. This can be achieved by a try catch statement:

using (var cn = dbFactory.CreateConnection ()) {
cn.ConnectionString = connectionString;
cn.Open ();

var cmdOne = cn.CreateCommand ();
cmdOne.CommandText = "spUpdateFoo";
cmdOne.Connection = cn;
cmdOne.CommandType = CommandType.StoredProcedure;

var cmdTwo = cn.CreateCommand ();
cmdTwo.CommandText = "spUpdateMoo";
cmdTwo.Connection = cn;
cmdTwo.CommandType = CommandType.StoredProcedure;

var tran = cn.BeginTransaction ();

try {

cmdOne.Transaction = tran;
cmdTwo.Transaction = tran;

cmdOne.ExecuteNonQuery ();
cmdTwo.ExecuteNonQuery ();

tran.Commit ();
} catch (Exception ex) {
tran.Rollback ();
}
}

The DbTransaction class implements IDisposable and can therefore be used within a using statement rather than a try catch. The Dispose method will be called implicitly upon leaving the scope of the using statement; this will cause any uncommitted changes to be rolled back while allowing any committed changes to remain as persisted. This is the preferred syntax for writing transactions in ADO.NET:

var connectionDetails =
ConfigurationManager.ConnectionStrings ["MyDatabase"];

var providerName = connectionDetails.ProviderName;
var connectionString = connectionDetails.ConnectionString;

var dbFactory = DbProviderFactories.GetFactory (providerName);

using (var cn = dbFactory.CreateConnection ()) {
cn.ConnectionString = connectionString;
cn.Open ();

var cmdOne = cn.CreateCommand ();
cmdOne.CommandText = "spUpdateFoo";
cmdOne.Connection = cn;
cmdOne.CommandType = CommandType.StoredProcedure;

var cmdTwo = cn.CreateCommand ();
cmdTwo.CommandText = "spUpdateMoo";
cmdTwo.Connection = cn;
cmdTwo.CommandType = CommandType.StoredProcedure;

using (var tran = cn.BeginTransaction ()) {

cmdOne.Transaction = tran;
cmdTwo.Transaction = tran;

cmdOne.ExecuteNonQuery ();
cmdTwo.ExecuteNonQuery ();

tran.Commit ();
{
}
}
}

Concurrency Issues

Writing and reading to a database does suffer from concurrency issues; multiple transactions occuring at the same time.

Concurrency Issue

Description

Lost Update

Two or more transactions perform write actions on the same record at the same time without being aware of each others change to the data. The last transaction will persist its state of the data overwriting any changes to the same fields made by the first.

Dirty Read

Data is read while a transaction has started but not finished writing. The data is considered dirty as it represents a state of the data which should not have existed.

Nonrepeatable Read

A transaction reads a record multiple times and is presented with multiple versions of the same record due to another transaction writing to the same record.

Phantom Read

Data from a table is read while inserts or deletes are being made. The result set contains missing data from the inserts which have not finished as well as records which are no longer found within table due to the deletes.

Isolation Level

Isolation levels define rules for accesing data when another transaction is running.

The level is set when creating a transaction with the BeginTransaction method and can be read with the IsolationLevel property:

using (var tran = cn.BeginTransaction(IsolationLevel.ReadCommitted)) {

cmdOne.Transaction = tran;
cmdTwo.Transaction = tran;
IsolationLevel isolationLevel = tran.IsolationLevel;

cmdOne.ExecuteNonQuery ();
cmdTwo.ExecuteNonQuery ();

tran.Commit ();
}

The IsolationLevel enum has levels considered lowest as unspecified and highest as Serializable as we traverse through the table; opposite to the amount of concucrency allowed. Chaso and snapshop are considered outside of this low to high range.

 

Isolation Level

Description (MSDN)

Unspecified

The isolation level is undetermined.

A different isolation level than the one specified is being used, but the level cannot be determined.

Often found in older technologies which work differently from current standards such as OdbcTransaction,

ReadUncommitted

A transaction reading data places no shared or exclusive locks upon the data being read.

Other transactions are free to insert, delete and edit the data being read.

This level of isolation allows a high level of concurrency between transactions which is good for performance.

This level of isolation suffers from the possibility of dirty, nonrepeatable and phantom reads.

ReadCommitted

A transaction reading data places a shared lock on the data being read.

Other transactions are free to insert or delete rows but are not given permission to edit the data being read.

This level of isolation does not suffer from dirty reads.

This level of isolation suffers from non repeatable and phantom reads.

This is the default isolation in most databases.

RepeatableRead

A transaction reading data places an exclusive lock on the data being read.

Other transactions are free to insert data into the table but are not free to edit or delete the data being read.

This level of isolation does not suffer from dirty and nonrepeatable reads.

This level of isolation suffers from phantom reads.

Serializable

A transaction reading data places an exclusive lock on the data being read.

Other transactions are not free to insert, update or delete the data being read.

This level of isolation does not suffer from dirty, nonrepeatable or phantom reads.

This level of isolation suffers from the least amount of concurrency and is bad on performance.

Chaos

Allows dirty, lost updates, phantom and nonrepeatable reads but not concurrently with other transactions with a higher isolation level than themselves.

Snapshot

Every writable transaction causes a virtual state of the data before the transaction is made. No exclusive lock is made on the data though isolation is guaranteed by allowing other  transactions to perform read actions on the data at the state before the writable transaction started,

Checkpoints

Checkpoints provide a way of defining temporary save points; data can be rolled back to these intermediatory points.

Save does not persist the changes to the database and a Commit would still be required upon completion of all writable transactions.

using (var tran = cn.BeginTransaction (IsolationLevel.ReadCommitted)) {

cmdOne.Transaction = tran;
cmdTwo.Transaction = tran;

cmdOne.ExecuteNonQuery ();

tran.Save ("Charlie");

cmdTwo.ExecuteNonQuery ();

tran.Rollback ("Charlie");
tran.Commit ();
}

Not all database vendors provide checkpoints. SQL Server does. MySQL does though the MySql Connector ADO.NET data provider does not currently appear to support this feature.

Nested Transactions

If the database vendor / ADO.NET Data Provider does not support checkpoints, nested transactions can always be implemented to achieve the same result. Here a new transaction can be created within the scope of another transaction.

using (var tranOuter = cn.BeginTransaction ()) {

cmdOne.Transaction = tranOuter;
cmdOne.ExecuteNonQuery ();

using (var tranInner = cn.BeginTransaction ()) {
cmdTwo.Transaction = tranInner;

cmdTwo.ExecuteNonQuery ();
tranInner.Rollback ();
}

tranOuter.Commit ();
}
Advertisements

ADO.NET – Disconnected Layer

Intro

The disconnected layer allows caching of data offline. The data can then be queried and manipulated completely disjoint from the data source before being persisted at a later time.

The main class is the DataSet which represents a subset of the data from a data source. Each table is represented by a DataTable. The schema, relationships and constraints can all be modelled.

Source Code

All source code can be found on GitHub here.

This is part of my HowTo in .NET seriies. An overview can be seen here,

Getting Data

The DataAdapter class is used to cache a subset of a database offline into a series of DataTables.

If the executed query yields only one result set a DataTable can be filled instead of a DataSet:

ConfigurationManager.ConnectionStrings ["MyDatabase"];

var providerName = connectionDetails.ProviderName;
var connectionString = connectionDetails.ConnectionString;

var queryString = "SELECT A,B,C FROM MyTable";

var dbFactory = DbProviderFactories.GetFactory (providerName);

// Create and configure a connection
 var connection = dbFactory.CreateConnection ();
 connection.ConnectionString = connectionString;

// Create and configure a DbCommand
 var command = dbFactory.CreateCommand ();
 command.CommandText = queryString;
 command.Connection = connection;

// Create and configure a DbDataAdapter
 var adapter = dbFactory.CreateDataAdapter ();
 adapter.SelectCommand = command;

// Fill A DataTable
 var dataTable = new DataTable ();
 adapter.Fill (dataTable);

If the result set yields multiple tables a DataSet can be used to hold a series of DataTables; each populated with a yielded result set.

Each DataTable is named by default Table, Table1 etc however the TableMappings property can be used to rename the tables to something sensible:

 var queryString = "SELECT A,B,C FROM MyTable;
 SELECT A,B,C FROM YourTable";

var dbFactory = DbProviderFactories.GetFactory (providerName);

// Create and configure a connection
 var connection = dbFactory.CreateConnection ();
 connection.ConnectionString = connectionString;

// Create and configure a DbCommand
 var command = dbFactory.CreateCommand ();
 command.CommandText = queryString;
 command.Connection = connection;

// Create and configure a DbDataAdapter
 var adapter = dbFactory.CreateDataAdapter ();
 adapter.SelectCommand = command;

// Rename table mappings
 adapter.TableMappings.Add ("Table", "MyTable");
 adapter.TableMappings.Add ("Table1", "YourTable");

// Fill A DataTable
 var dataSet = new DataSet ();
 adapter.Fill (dataSet);

Calling Stored Procedures

Stored procedures, in built functions and user defined functions call all be called to fill a DataSet or DataTable object. The connected layer (previous chapter) details how to configure command objects with the required parameters. Below shows how to call a stored procedure which takes no parameters:

var connectionDetails =
ConfigurationManager.ConnectionStrings ["MyDatabase"];

var dbFactory = DbProviderFactories.GetFactory (connectionDetails.ProviderName);

// Create and configure a connection
 using (var connection = dbFactory.CreateConnection ()) {
 connection.ConnectionString = connectionDetails.ConnectionString;

// Create and configure a DbCommand
 var command = dbFactory.CreateCommand ();
 command.Connection = connection;
 command.CommandText = "spGetFoo";
 command.CommandType = CommandType.StoredProcedure;

// Create and configure a DbDataAdapter
 var adapter = dbFactory.CreateDataAdapter ();
 adapter.SelectCommand = command;

// Fill A DataTable
 var dataSet = new DataSet ();
 adapter.Fill (dataSet);

return dataSet;
 }

Accessing Data

Once a DataSet or DataTable has been filled it’s contents can be accessed.

DataSet

A DataSet is a class which holds a collection of DataTables which can be accessed by the Tables property:

var tables = dataSet.Tables;

The name of the data set can also be returned via the DataSetName property:

var name = dataSet.DataSetName;

The ExtendedProperties property exposes an instance PropertyCollection which can be used as a scratch collection to store arbitrary information about the DataSet or its contents as seen fit by the user:

PropertyCollection properties = dataSet.ExtendedProperties;

PropertyCollection inherits from HashSet:

properties.Add( "Connected", DateTime.Now);

foreach(DictionaryEntry de in properties)
{
var aKey = de.Key;
var aValue = de.Value;
}
</pre>
<h2>DataTable</h2>
<pre>
The DataTable represents a cache of a table or a subset of a table within a database.

A collection of DataTables can be accessed via the Tables property upon DataSet, it returns a TableCollection:


DataTableCollection tables = dataSet.Tables;

The TableCollection allows access to the contained tables via their table mapping name and it’s ordinal position:

DataTable dataTable = dataSet.Tables[0];
DataTable namedDataTable = dataSet.Tables["MyTable"];

The DataTable has properties to return its name, its owning DataSet and its Primary Key:

var tableName = dataTable.TableName;
var parentDataSet = dataTable.DataSet;
var primaryKey = dataTable.PrimaryKey;

DataRow

Each DataTable stores a collection of DataRows which represent the contained database records. The DataRowCollection can be accessed via the Rows property upon the DataTable.

DataRowCollection rows = dataTable.Rows;

var count = dataTable.Rows.Count;

A DataRow can be access via its ordinal position within the collection:

DataRow row = dataTable.Rows[0];

DataColumn

A DataColumn represents a column within a DataTable. It can be access via its ordinal position or by its name.

A DataColumnCollection holds a collection of DataColumns and can be accessed via the Columns property of the DataTable.

Alternatively a DataColumn can be accessed via the index method [] from the DataRowCollection.

var count = dataTable.Columns.Count;

DataColumnCollection columns = dataTable.Columns;
DataColumn columnOne = dataTable.Columns[0];
DataColumn columnTwo = dataTable.Columns["Field"];

int idValue = (int)dataTable.Rows [0] [0];
string columnFour = (string)dataTable.Rows [0] ["StringField"];

If a DataColumn is requested by a name which does not exist an ArgumentException is thrown.

If a DataColumn is requested by its ordinal position which does not exist a IndexOutOfRangeException is thrown.

Meta information about a DataColumn can found by a series of properties upon the DataColumn class. Here was access its name, ordinal position and containing DataTable.

var columnName = column.ColumnName;
var ParentDataTable = column.Table;
var position = column.Ordinal;

The columns value and whether it can take the value of null:

var defaultValue = column.DefaultValue;
var isNullable = column.AllowDBNull;

Type

The DataType property of a DataColumn returns a value from the System.Data.Type enum which represents the data type of the column which maps directly to .NET types:

* Boolean
* Byte
* Char
* DateTime
* Decimal
* Double
* Guid
* Int16
* Int32
* Int64
* SByte
* Single
* String
* TimeSpan
* UInt16
* UInt32
* UInt64

DataTableReader

Though it is possible to access the data as defined by each contained entity of the DataSet above, the DataTableReader class provides a read-only forward iterative access to data within a DataTable similar to a DataReader:

using (DataTableReader reader = dataTable.CreateDataReader ()){
while (reader.Read ()) {
// Access via the fields name or ordinal position
string aValue = (string)reader [0];
int bValue = (int)reader ["FieldOne"];

// Access should be preferred through strongly typed values
var isNull = reader.IsDBNull (1);
var aBool = reader.GetBoolean (1);
var aDateTime = reader.GetDateTime (1);
var aDecimal = reader.GetDecimal (1);
var aDouble = reader.GetDouble (1);
var aChar = reader.GetChar (1);

// Access the .NET field type or the data providers type
Type aType = reader.GetFieldType (1);
Type bType = reader.GetProviderSpecificFieldType (1);

// Access field name and ordinal position
var fieldName = reader.GetName (1);
int position = reader.GetOrdinal ("FieldOne");
}
}

Querying Data

A DataSet can be queried to return an Array of DataRows which have passed a predicate which is supplied in a syntax similar to SQL upon a DataTable.

Return all rows:

DataRow[] rows = dataTable.Select();

Return all rows passing a predicate:

DataRow[] rows = dataTable.Select("FieldA='AValue'");

The syntax for querying a DataTable and also a DataView (see below) is relatively simple though relatively extensive.

The following section defines the syntax with an example usage:

Literals

Rules for defining literals

Type Description Example
Number Numbers are simply referenced inside the query string without quotes. “FieldA = 10”
Sting Strings are contained within the query string inside single quotes. “FieldA = ‘FooFooey'”
Dates Dates are contained between prefixing and suffixing # symbols.

They can be segmented with – and / and can also contain times”Date = #12/31/2008#”

“Date = #2008-12-31#”

“Date = #12/31/2008 16:44:58#”Escape CharsThe following characters must be escaped: ~ ( ) # \ / = > < + – * % & | ^ ‘ ” [ ].

They can be escaped with [] or \“FieldA = ‘[Name*Surname]’”

Comparison Operators

Comparison operators allow comparing two operands.

Type Description Example
= Equals “AField = 1”
<> Not equals “AField <> 1”
< Less than “AField < 1”
<= Less than or equal to “AField <= 1”
> Greater than “AField > 1”
>= Greater than or equal to “AField >= 1”
IN The value is contained within a set of values “AField IN (‘1, 2, 3)”
LIKE Wildcard comparison.

Wildcards % and * can be used for one or one or more characters respectively.

They can only be placed at the start or the end of a value but not in the middle.”AField LIKE ‘Baz*'”

 

Logical Operators

Logical operators work between other operators

Type Description Example
NOT Negates a LIKE or IN expression “AField NOT IN (1, 2, 3)”
AND Two expressions must be evaluated to true “Afield = ‘Avalue’ AND BField >= 50”
OR Either of two expressions must evaluate to true. “Afield = ‘Avalue’ OR BField >= 50”

The precedence of the operators is the comparison operators is NOT, AND and then OR.

Arithmetic Operators

Operators for working on numeric fields.

Type Description Example
+ Addition “AField + 10 = 12”
Subtraction “AField – 10 = 12”
* Multiplication “(AField * 0.2) > 10”
/ Division “(Afield / 0.8) > 10”
% Modulus “(Afield % 0.8) > 10”

String Operators

Operator(s) for working on string fields.

Type Description Example
+ Concatenation “(AField + BField) = ‘James Pond'”

Parent-Child Relation Referencing

Relationships between tables can be traversed with the notation ..

Parent to children traversals must be used within an aggregate function due to the 1:n nature (they can potentially return a set of records).

Where multiple relationships exist on the same parent column; the relationship name must be used. (RelationsipName).ChidlField.

We look at adding relationships into the schema in the following sections.

Aggregate Functions

Aggregate functions work on a single column of a set of data and return a value.

Type Description Example
SUM Sum “SUM(ParentFK.ChildField) > 10″
COUNT Count “COUNT(ParentFK.ChildField) > 10”
MIN Minimum “MIN(ParentFK.ChildField) > 10”
MAX Maximum “MAX(ParentFK.ChildField) > 10”
AVG Average (Mean) “AVG(ParentFK.ChildField) > 10”

Functions

Various other functions:

Type Description Example
CONVERT Convert a value to a .NET type “Convert(FieldAString, ‘System.Int32’) > 10”
LEN Returns the length of a string “LEN(FieldA) > 10”
ISNULL Replaces NULL with a defined value “IsNull(FieldA, -1)”
IIF Evaluates a condition; returns the second operand if true or the third if false. “IIF(FieldA = 1, ‘One’, ‘Not One’)”
TRIM Removes white space from the start and end of a string “TRIM(FieldA) = ‘Hello'”
SUBSTRING Returns a subset of a string between the start and end character positions “SUBSTRING(FieldA, 1, 2)=’UB’”

Sorting Data

The last parameter of the Select method allows a sort criteria:

Sort by a field ascending:

var rows = dataTable.Select(string.Empty, "FieldA");

Sort by multiple fields:

var rows = dataTable.Select(string.Empty, "FieldA, FieldB");

Sort descending:

var rows = dataTable.Select(string.Empty, "FIELDA DESC");

DataView

A DataView is a customized view of a DataTable. It allows sorting, filtering, searching, and navigating data. The data is connected to the DataTable it was created against; changes to the data affects the data in the parent DataTable.

A Data View is created from a DataTable:

var dv = new DataView(dataTable);
[/sourcecode ]

RowFilter allows providing predicates to a DataView. It takes the same syntax string as DataTable.Select() however instead of returning an array of DataRows which pass the condition it changes the view of the data automatically:


dv.RowFilter = "Field='Value";

All the syntax of querying a DataTable is applicable for the RowFilter property of the DataView mentioned above.

Schema

The following is a quick run through of how to manually create the schema of a DataSet and its contained entities:

DataSet

Instantiate a DataSet:

var dataSet = new DataSet ("MyDataSet");
dataSet.ExtendedProperties ["Create"] = DateTime.Now;

DataTable

Instantiate a DataTable and add it into a DataSet:

DataTable dataTable = new DataTable ("MyTable");
dataSet.Tables.Add (dataTable);

DataColumns

Add columns into a DataTable:

var column = new DataColumn ("ID", typeof(int)) {
Caption = "My Table ID",
ReadOnly = true,
AllowDBNull = false,
Unique = true
};

dataTable.Columns.Add(column);

PrimaryKey

The PrimaryKey property is an Array of DataColumns which represent the primary key:

dataTable.PrimaryKey = new DataColumn[]
{ dataTable.Columns[0], dataTable.Columns[1] };

AutoIncrement Column

AutoIncrement columns provides an auto generating integer similar to an identity index, though uniqueness is only guaranteed locally.

The seed is the starting index value and the increment is the amount to increment each time:

var seedColumn = new DataColumn ("ID", typeof(int)) {
Caption = "My Table ID",
ReadOnly = true,
AllowDBNull = false,
Unique = true,
AutoIncrement = true,
AutoIncrementSeed = 0,
AutoIncrementStep = 1,
};

Relationships

Creation

Relationships are created by mapping the foreign keys between DataTables contained within a DataSet:

A DataRelation is created by defining the relationship name, the parent key column and then the child key column. The DataRelation should then be added into the Relations property of a DataSet which returns a DataRelationCollection:

var dr = new DataRelation ("ParentChild",
dataSet.Tables ["Parent"].Columns ["ID"],
dataSet.Tables ["Child"].Columns ["ParentID"]);

dataSet.Relations.Add (dr);

Navigation

Navigation between child and parent rows or viceversa can be performed once a relationship has been set up. In both cases the relationship should be used against either the child or parent rows:

Children Rows:

dataSet.Tables ["Parent"].Rows[0].
GetChildRows(dataSet.Relations["ParentChild"]);

Parent Row:

dataSet.Tables ["Child"].Rows [0].
GetParentRow (dataSet.Relations ["ParentChild"]);

Constraints

ADO.NET provides two forms of constraints, foreign keys and unique. The enforcement can be disabled or enabled with the EnforceConstraints property. By default constraints are enforced:

dataSet.EnforceConstraints = false;

Foreign Key Constraints

ADO.NET provides a Delete and Update constraints for maintaining data integrity between DataRows connected via relationships. They both default to None:

Rule Description
Cascade During update, related rows have their affected columns updated to that of the related row.

During delete, related rows are themselves deleted.SetNullRelated rows have affected columns set to DBNullSetDefaultRelated rows have affected columns set to their default valueNoneNo action is performed; this is the default action

The AcceptRejectRule defines what happens to child rows when AcceptChanges or Rejected changes is called upon the parent row or the containing DataSet; we talk about these methods more during the section on manipulating and persisting changes.

AcceptRejectRule Description
Cascade Changes are accepted or rejected upon child rows
None No action is performed upon child rows; this is the default action.
var parent = dataSet.Tables[0].Columns[0];
var child= dataSet.Tables[0].Columns[1];

var fk = new ForeignKeyConstraint( "FK", parent, child );

fk.DeleteRule = Rule.SetNull;
fk.UpdateRule = Rule.Cascade;
fk.AcceptRejectRule = AcceptRejectRule.None;

dataSet.Tables [0].Constraints.Add (fk);

Relationships ( see above ) automatically add in a constraint.

Unique Constraints

Unique constraints ensure that no column, or combination of columns contain duplicates.

A unique constraint for all columns contained within the primary key is automatically created.

For a single column requiring a unique constraint the Unique property on DataColumn can be set

var uniqueColumn = new DataColumn ();
uniqueColumn.Unique = true;

For multiple columns a UniqueConstraint class containing an array of columns should be created and added to the DataTable’s Constraints property which returns a ConstraintCollection:

dataTable = dataSet.Tables["MyTable"];
Var uniqueConstraint = new UniqueConstraint(new DataColumn[] {
dataTable.Columns["FieldA"],
dataTable.Columns["FieldB"]
});

dataSet.Tables["MyTable"].Constraints.Add(uniqueConstraint );

Reading Schema

Calling DbDataAdapter.Fill(aDataSet) is optimized for read-only scenarios. Only the schema required to access the data itself is retrieved; the data, the type and the name of the column.

The schema can be loaded by a call to DataAdapter.FillSchema or with DataAdapter.Fill with explicit reference to load the missing schema.

The bounds of the schema returned is defined by the SelectCommand and the returned columns defined upon the DataAdapter. If multiple result sets are found, only the first defined within the SelectCommand will have the schema returned.

The following is a synopsis of what schema is actually loaded:

A DataTable is added into the DataSet with all columns returned by the select statement. Each column will have the following schema set:

MissingSchemaAction Description
AllowDBNull Whether the column allows null data.
AutoIncrement Whether the column should be incremented. AutoIncrementStep and AutoIncrementSeed must be set manually.
MaxLength The maximum size of data the column can take
Readonly If the column is readonly
Unique If the column has a unique constraint placed upon it

The primary key information is set if any of the primary key columns are returned from the data source. If the primary keys are not returned from the data source any unique columns returned are used.

Foreign key constraints are not returned and must be loaded manually.

MissingSchemaAction

MissingSchemaAction property on DbDataAdapter is used to set the amount of schema to loaded during calls to DataAdapter.Fill() and more importantly what to do if the schema already contained within a DataSet does not match the incoming schema.

adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.Fill(aDataSet);
MissingSchemaAction Description
Add Adds the missing tables and columns as required.
AddWithKey Adds the missing tables and columns as required. Also ensures that the primary key and it’s constrains match the incoming schema. Foreign key constraints are not included and must be created explicitly.
Error An InvalidOperationException is thrown if any columns exist in the DataTable which don’t exist in the data source.
Ignore Any columns in the DataTable which are not in the data source are simply ignored.

FillSchema

The DataAdapter.FillSchema() is used load the schema within a data source disjointly from a call to DataAdapter.Fill().

SchemaType Description
Mapped Any existing table and column mappings within the DataSet are ignored in favor of those retrieved from the data source.
Source Any existing table and column mappings within the DataSet are used in favor of those retrieved from the data source.

It must be noted that FillSchema being called before a Fill method leaves the connection open. If a FillSchema is called after a Fill method the connection is closed afterwards.

Fill vs. FillSchema

It must be pointed out that Fill will load schema every call if the schema has been asked for. It is advised to use FillSchema when multiple calls to Fill is made which will result in the same schema being read.

It is advised to use neither if only a read only data is required as the schema is not required.

It is advised to use neither if XML data is being used and the defined XSD schema is to be used instead of the auto generated schema when reading the XML.

It is advised to use neither when using Visual Studio design tools as the schema for updating the data source is auto generated.

Manipulating Data

Data is manipulated within a DataSet by either deleting, editing or inserting DataRows.

Deleting

DataRows can be deleted via the Delete method on an instance of the DataRow class:

aDataTable.Rows[0].Delete();
aDataRow.Delete();

Updating

DataRows can be modified via the index methods upon an instance of the DataRow class:

Updating Rows via the DataRowCollection:

aDataTable.Rows[0][0] = "NewValue";
aDataTable.Rows[0]["FieldA"] = "NewValue";

Updating via a DataRow:

aDataRow[0] = "NewValue";
aDataRow["Field"] = "NewValue";

Inserting

Inserting a new DataRow into a DataRowCollection entails creating a new DataRow, setting the data and then adding the DataRow into the DataRowCollection:

Create a template row:

DataRow dataRow = aDataTable.NewRow();

Setting the data:

dataRow [0] = 1;
dataRow ["Field"] = "Value";

Adding the row into the table:

aDataTable.Rows.Add (dataRow);

Accepting and Rejecting Changes

A transactional state of data is maintained throughout editing. The changes can be rejected and committed through a series of methods on the DataRow and DataTable classes.

Method Description
DataRow.BeginEdit() Notifies the DataRow as being edited. Disables change events on the DataRow
DataRow.CancelEdit() Notifies the DataRow as no longer being edited. Any changes are discarded.
DataRow.EndEdit() Notifies the DataRow as no longer being edited. The changes are signalled to be kept but no changes are committed locally into the DataSet until AcceptChanges has been called.
DataRow.AcceptChanges() Notifies the DataRow as no longer being edited. Pending changes are all committed locally into the DataSet.
DataTable.AcceptChanges() All DataRows which have had uncommited changes have their changes committed locally into the DataSet.
DataRow.RejectChanges() All DataRows which have had uncommited changes have their changes undone.

Validating Data

Validation errors can be placed upon fields within a DataRow with the SetColumnError method:

dataRow.SetColumnError(0, "Invalid Data");
dataRow.SetColumnError("FieldName", "Invalid Data");

Validation errors can then be retrieved with the following methods:

var dsHasErrors = aDataSet.HasErrors;
var dtHasErrors = aDataSet.Tables [0].HasErrors;
var errorRows = aDataSet.Tables [0].GetErrors ();
var errorCols = dataRow.GetColumnsInError ();
var errorMsg = dataRow.GetColumnError (0);

Validation messages should be cleared once an action has been made upon them. This can be at row level via the ClearErrors method or at the DataSet level with RejectChanges:

errorRows[0].ClearErrors();

The error messages can be iterated over and the changes accepted or rejected::

var errorResult = new StringBuilder ();

var dataSetChanged = DS.GetChanges (DataRowState.Modified);

if (dataSetChanged != null && dataSetChanged.HasErrors) {
foreach (DataTable dataTable in dataSetChanged.Tables) {
if (!dataTable.HasErrors) {
continue;
}

foreach (var dataRow in dataTable.GetErrors()) {
foreach (var dataColumn in dataRow.GetColumnsInError()) {
var errorDesc = dataRow.GetColumnError (dataColumn);
// Implicitly called with aDataSet.RejectChanges:
//dataRow.ClearErrors();
errorResult.AppendLine (errorDesc);
}
}
}

DS.RejectChanges ();
} else if (dataSetChanged != null) {
Adapter.Update(dataSetChanged);
// implicitly called with adapter
//Update:_dataSet.AcceptChanges();
}

return errorResult.ToString ();

Persisting Data

Data persistence has two levels; locally into the disconnected DataSet and then at a later time back into the data source.

Row State

ADO.NET maintains a status against each row to track local changes which are then used to persist the data back to the database in a single hit.

The RowState allows analysis of the state of the row in relation to its state when it was loaded into the DataSet and against any changes which are occurring or have occurred.

The state of a row can be returned by the RowState property:

DataRowState state = aDataRow.RowState;

The DataRowState Enum values:

DataRowState Description
Added The row has been added into the DataRowCollection but the changes have not been accepted via the AcceptChanges methods.
Deleted The row has been deleted.
Detached The row has been created but has not been added into a DataTable.
Modified The row has been modified but the changes have not been accepted via the AcceptChanges methods.
Unchanged The row has not changed since it was loaded into the DataTable or since a call to AcceptChanges or RejectChanges has been made.

Consider the following process flow in the life of a DataRow: creation, add, modify, and deletion. The state of the row will change as follows:

var row = aDataTable.NewRow (); // RowState is Detached

aDataTable.Rows.Add (row); // RowState is Added

row = aDataTable.Rows [0];
row ["Field"] = 10; // RowState is Modified

row.AcceptChanges (); // RowState is Unchanged

row ["Field"] = 11; // RowState is Modified

row = aDataTable.Rows [1];
aDataTable.Rows [0].Delete (); // RowState is Deleted

Row Versions

The motion of state is actually more complex; up to three versions of the DataRow could actually exist at any time.

Any three of the following row versions can exist:

DataRowVersion Description
Current A row containing the current data.
Default The default version of DataRowState.

For a DataRowState value of Added, Modified or Deleted, the default version is Current.

For a DataRowState value of Detached, the version is Proposed.OriginalA row containing the original dataProposedA row containing proposed data

The row versions are set and maintained by the following methods:

Method Description
DataRow.BeginEdit() Change the row’s value, the Current and Proposed values become available
DataRow.CancelEdit() The Proposed value is deleted.
DataRow.EndEdit() The Proposed value becomes the Current value
DataRow.AcceptChanges() The Original value becomes identical to the Current value

If this is called before DataAdapter.Update no changes to the row within the

data source will be persisted. (It is called implicitly by DataAdapter.Update)DataTable.AcceptChanges()The Original value becomes identical to the Current value

If this is called before DataAdapter.Update no changes to the data within the DataSet will be persisted back into the data source. (It is called implicitly by DataAdapter.Update)DataRow.RejectChanges()The Proposed value is discarded, and the original version becomes Current

The index method upon DataRow has been overloaded to take an instance of the DataRowVersion enum. The value of the field upon the specified version is returned.

var aValue = aDataTable.Rows [0] ["AField",
DataRowVersion.Original].ToString ();
[/sourcecode language='csharp']</pre>
<h2>DataAdapters</h2>
<pre>
We have already seen the DataAdapter previously for filling a DataSet via the SelectCommand. The DataProvider also provides an InsertCommand, UpdateCommand and DeleteCommand. These are SQL template commands used to automatically generate SQL which is then persisted back to the database.

The SelectCommand, InsertCommand, UpdateCommand and DeleteCommand can all be configured manually or via Visual Studio designer.

Only the SelectCommand is required if only the DataSet.Fill() is required with no data persistence.

The InsertCommand, UpdateCommand and DeleteCommand are all required if persistence is required.

Persistence is made by calling Update on the DataAdapter. It automatically generates the required SQL with insert, update and delete statements to create, edit or delete the database data to the state contained within the DataSet. It uses the RowState of each row to determine what and if any action is required.

One advantage the DataAdapter has is that the connection is automatically managed. The only requirement is the creation of the connection, connection string and the assignment to the DataAdapter. The connection and any required transactions is automatically handled inside the DataAdapter.


var connectionDetails =
ConfigurationManager.ConnectionStrings ["MyDatabase"];

var dbFactory = DbProviderFactories.GetFactory (connectionDetails.ProviderName);

Adapter = dbFactory.CreateDataAdapter ();

// Create and configure a connection
using (var connection = dbFactory.CreateConnection ()) {
connection.ConnectionString = connectionDetails.ConnectionString;

// Create Select Command
DbCommand selCmd = dbFactory.CreateCommand ();
selCmd.CommandText = "SELECT idMyTable,FieldA FROM MyTable";
selCmd.Connection = connection;

Adapter.SelectCommand = selCmd;

// Parameters
var fieldAParam = dbFactory.CreateParameter ();
fieldAParam.DbType = DbType.String;
fieldAParam.ParameterName = "@FieldA";
fieldAParam.SourceColumn = "FieldA";
fieldAParam.DbType = DbType.String;

var idParam = dbFactory.CreateParameter ();
idParam.DbType = DbType.Int32;
idParam.ParameterName = "@idMyTable";
idParam.SourceColumn = "idMyTable";
idParam.DbType = DbType.Int32;

// Configure Insert Command
var insCmd = dbFactory.CreateCommand ();

insCmd.CommandText = "INSERT INTO MyTable (FieldA) VALUES (@FieldA)";

insCmd.Parameters.Add (fieldAParam);
insCmd.Connection = connection;
insCmd.CommandType = CommandType.Text;
Adapter.InsertCommand = insCmd;

// Delete Command
var delCmd = dbFactory.CreateCommand ();

delCmd.CommandText = "DELETE FROM MyTable WHERE idMyTable = @idMyTable";
delCmd.Parameters.Add (idParam);
delCmd.Connection = connection;
delCmd.CommandType = CommandType.Text;
Adapter.DeleteCommand = delCmd;

// Configure Update Command.
var upCmd = dbFactory.CreateCommand ();

upCmd.CommandText = "UPDATE MyTable SET FieldA = @FieldA WHERE idMyTable = @idMyTable";
upCmd.Parameters.Add (idParam);
upCmd.Parameters.Add (fieldAParam);
upCmd.Connection = connection;
upCmd.CommandType = CommandType.Text;
Adapter.UpdateCommand = upCmd;
}
Adapter.Fill (DS);

SqlCommandBuilder

The SQLCommandBuilder automatically configures the insert, updated and deleted commands based upon the SelectCommand.

DataAdapter.Update() reads the databases schema to generate the sql, however this requires additional database reads and as such degrades performance.

SQLCommandBuilder can only be used if all the following conditions hold true:

* The select command interacts with a single table
* The table has a defined primary key
* All columns making the primary key must be returned with the select command

SQLCommandBuilder usage:

var connectionDetails =
ConfigurationManager.ConnectionStrings ["MyDatabase"];

var providerName = connectionDetails.ProviderName;
var connectionString = connectionDetails.ConnectionString;
var dbFactory = DbProviderFactories.GetFactory (providerName);

var adapter = dbFactory.CreateDataAdapter ();

// Create and configure a connection
var connection = dbFactory.CreateConnection();
connection.ConnectionString = connectionString;

// Create Select Command
var selCmd= dbFactory.CreateCommand();
selCmd.CommandText = "SELECT A,B,C FROM MyTable";
selCmd.Connection = connection;
adapter.SelectCommand = selCmd;

// Create and configure DbCommandBuilder
var builder = dbFactory.CreateCommandBuilder();
builder.DataAdapter = adapter;

// Create and fill a DataTable
var dt = new DataTable("MyTable");
adapter.Fill (dt);
// Changes would occur here….

// Persist changes
adapter.Update(dt);

Strongly Typed DataSets

The XSD tool can be used to create a strongly typed DataSet. The DataSet can be filled, manipulated, queried and persisted back to the DataSet. The benefit is that it exposes named properties which are generated from the provided schema.

Visual Studio Server Explorer and SQL Server Management Studio can be used to generate an XSD for a subset of a database’s schema. Similarly MySQL Workbench offers similar functionality for MySQL.

The XML Schema Definition Tool (xsd.exe) can be used to create XSD from C# classes and XML and viceversa. The tool can be accessed via the Visual Studio command prompt.

Mono offers a number of tools including DTD2XSD and Mono XSD.

Creating

A strongly typed DataSet can be created via the xsd.exe tool and an xsd file representing the required schema of the DataSet to be generated:

xsd.exe /d /l:CS MyDataSet.xsd /eld /n:MyDataSet

Switch Description
/d A DataSet should be generated
l:CS The generated code should be in the C# language
/eld The generated DataSet should allow Linq queries to be executed against it.
/n The namespace the generated code should be placed into

The generated code is a series of classes inheriting from the inbuilt DataSet, DataTable, DataRow etc and all named after their XSD schema definitions.

Filling

The strongly typed dataset generated can be filled:

var myDataSet = new MyDataSet ();
var myTable = new MyDataSet.MyTableDataTable ();

adapter.Fill (people);</pre>
<h2>Accessing Data</h2>
<pre>
foreach (MyDataSet.MyTableRow myTableRow in myTable) {
var id = myTableRow.Id;
var name = myTableRow.Name;
var isMale = myTableRow.IsMale;
}</pre>
<h2>Inserting Rows</h2>
<pre>
MyDataSet.MyTableRow newRow = myTable.NewMyTableRow();

newRow.Name = "Bobby McFroggy";
newRow.Id = 1;
newRow.IsMale = true;

myTable.AddMyTableRow(newRow);

adapter.Update(myTable);

DataTable also exposes a method to create and add a DataRow from the required field values:

myTable.AddMyTableRow (2, "James Pond", true);

More

Strongly typed methods for finding, deleting and also executing stored procedures will be generated.

LINQ to DataSet

DataSet can be queried and manipulated with Linq due to a series of extension methods within System.Data.DataSetExtensions.dll. The methods extends elements within the System.Data namespace namespace:

Extension Name

Description

DataTableExtensions

Allows querying DataTables with Linq

DataRowExtensions

Allows querying DataRows with Linq

TypedTableBaseExtensions

Allows querying strongly typed datasets with Linq

The functionality is identical to Linq though here are some pointers for working with DataSets and Linq.

To use Linq on a DataTable, AsEnumerable() must be called.

Exposing Linq:

IEnumerable queryOne =
from row in aDataTable.AsEnumerable ()
select row;

Predicates:

IEnumerable queryTwo =
aDataTable.AsEnumerable ().
Where (p => p.Field ("Name") == "Bob");

Ordering:

IEnumerable queryThree =
from p in aDataTable.AsEnumerable ()
orderby p.Field("Age") descending
select p;

With anonymous types:

var query = aDataTable.AsEnumerable ().Select (p => new
{
Name = p.Field ("Name"),
Age = p.Field ("Age")
});

ADO.NET & XML

ADO.NET can be used with XML as a data source through exposed methods on the DataSet class.

Read XML from a file:

aDataSet.ReadXml("data.xml");

Write a DataSet to an XML file:

aDataSet.WriteXml("data.xml");

Populate a DataSets schema from a an xsd file:

aDataSet.ReadXmlSchema("data.xsd");

Write a DataSets schema into a xsd file:

aDataSet.WriteXmlSchema("data.xsd");

Binary Serialization

A DataSet can be serialized to XML, Soap or binary. Here we show binary serialization:

Serialize A DataSet

aDataSet.RemotingFormat = SerializationFormat.Binary;

using (var fs = new FileStream ("data.dat", FileMode.Create)) {
var bf = new BinaryFormatter ();
bf.Serialize (fs, aDataSet);
}

DeSerialize A DataSet

aDataSet.RemotingFormat = SerializationFormat.Binary;

using (Stream fs = File.OpenRead ("data.dat")) {
var bf = new BinaryFormatter ();
aDataSet = (DataSet)bf.Deserialize (fs);
}

ADO.NET – Connections And Data Providers

Intro

ADO.NET is a framework for accessing, interrogating, manipulating and persisting data in relational databases.

Source Code

All source code can be found on GitHub here.

This is part of my HowTo in .NET seriies. An overview can be seen here,

Data Providers

The framework abstracts consuming code away from the specifics of each vendor’s database implementation allowing code to be written which is virtually agnostic to the data source.

Each ADO.NET capable database provides an ADO.NET data provider which handles the databases specific implementation needs.

Through a series of classes, abstract classes and interfaces, ADO.NET provides functionality which is consistant regardless of the database vendor.

ADO.NET data providers exist for most database vendors. A list can be found here: http://msdn.microsoft.com/en-gb/data/dd363565.aspx

Configuring A Database Provider

Depending upon your choice of database and data provider, you might need to configure the provider to be used with .NET.

SQL Server is configured automatically on Windows/.NET. Under Linux/Mono SQLIte is configured automatically yet MySQL is not.

If you are using MySQL I would recommend the Connector/Net ADO.NET data provider. You can download the driver here: http://dev.mysql.com/downloads/connector/net/

The data provider is written in .NET and is provided as an assembly which needs to be installed into the GAC.

sudo gacutil -i MySql.Data.dll

To check the installation went ok. You can list the contents of the gac with the gacutil command. The command below uses the POSIX command grep to filter the results:

gacutil -l | grep My

Data providers are registered within the machine.config of the .NET version you are running.

For windows the machine.config location will look something like:

C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine.config

For Mono and Debian based Linux the machine.config location will look something like:

/usr/local/etc/mono/2.0/machine.config

The configuration will be specific to the version of your data provider. You should check the installation page of you vender to determine this. The following is for MySQL Connector provider version 6.3.5.0:

<add name=”MySQL Data Provider”
invariant=”MySql.Data.MySqlClient”
description=”.Net Framework Data Provider for MySQL”
type=”MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data,
Version=6.3.5.0, Culture=neutral,
PublicKeyToken=c5687fc88969c44d” />

This should be copied into the Machine.Config within the <DbProviderFactories> node which sits within the <System.Data> node. You should find other providers already configured.

The data provider can then be referenced as any other assembly within the GAC.

Bases Classes

ADO.NET provides a series of core and abstract classes; they provide common functionlaity that is not specific to any data priovider.

ADO.NET also provides a series of interfaces. ADO.NET providers implement these interfaces.

The data providers are welcome to inherit from the abstract classes, and most do, to reuse the common functionality associated with them.

ADO.NET API consumes the interfaces rather than the abstract classes to allow a data provider with greater control of their required funcitonality.

Below we describe the core abstract classes and their interfaces.

Class / Name Space

Description

System.Data

Provides a common namespace for most of the abstract classes and interfaces which each data provider inherit from or implement.

It also contains common entities which are not specific to a data provider; datasets, tables, rows, columns, relational constraints etc.

DbConnection

IDbConnection

Supports for configuring connections

DbTransaction

IDbTransaction

Support for database transactions

DbCommand

IDbCommand

Support for calling SQL statements, stored procedures and parameterized queries.

Provides access to the an instance of the  DataReader class through the ExecuteReader() method

DbParameterCollection

IDataParameterCollection

Provides a collection of DbParameters to IDBCommand.

DbParameter

IDbDataParameter

IDataParameter

Provides a parameter to sql statements and stored procedures. It is used by IDBCommand.

DbDataReader

IDataReader

Provides a read only iterative view of the data returned from a SQL statement.

Provides access to strongly typed data via field names or their ordinal positions.

DbDataAdapter

IDbDataAdapter

IDataAdapter

Provides access to a cached subset of data, monitoring of any changes which can then be persisted back into the database at a later time.

Below we map the abstract classes and interfaces to the SQL Server and MySQL data provider classes. As you can see the naming convention is consistent:

Class / Name Space

SQL Server

MySQL

System.Data

System.Data.SqlClient

MySql.Data.MySqlClient

DbConnection

SqlConnection

MySqlConnection

DbTransaction

SqlTransaction

MySqlTransaction

DbCommand

SqlCommand

MySqlCommand

DbParameterCollection

SqlParameterCollection

MySqlParameterCollection

DbParameter

IDbDataParameter

IDataParameter

SqlParameter

MySqlParameter

DbDataReader

IDataReader

SqlDataReader

MySqlDataReader

DbDataAdapter

IDbDataAdapter

IDataAdapter

SqlDataAdapter

MySqlDataAdapter

Though it is possible to code directly to the MySQL or SQL Server data provider classes, it is not recommended and considered bad practice.

All the examples will be make use of the DbProviderFactories class and the associated DbProviderFactory class to create instance of the required data provider specific classes. The DbProviderFactories uses late binding to to determine which class the data provider has provided as part of their implementation.

Database Connections

Connection Strings

ADO.NET allows a virtually database agnostic approach to database access within .NET.

The connection strings are vendor specific and can turn off and on a number of features each of which might be specific to each vendor.

In short the minimum requirements is the server name, the database name and some form of login criteria. As each vendors connection string is specific to the vendor and data provider. Here are some examples:

Database
Connection String
Additional
SQL Server
Data Source=localhost;Integrated Security=SSPI;Initial Catalog=MyDB

SSPI means the logged in user will be used to connect with windows integrated security.

Alternative you could provide the username and password
MySQL
Server=localhost;Database=MyDB;Uid=myUsername;Pwd=myPassword;
MySQL data adapter does not appear to support SSI

It is advised to use connection pooling when concurrent database access is required. You should check each vendor for how to set this up.

http://www.connectionstrings.com provides an excellent knowledge base for connecting to most databases in most technologies including ADO.NET.

Machine.Config

Though connection strings can be defined anywhere, it is good practice to place them within the machine.config. This can allow changing connection criteria including the database server without compiling code; great for having separate databases for development, testing and production.

Connection Strings should be placed between the appSettings and the connectionStrings node.

The connection string can be named with the name field for reference in code later.

The providers assembly is referenced with the providerName field.

SQL Server:

<connectionStrings>
<add name =”MyDatabase”
providerName=”System.Data.SqlClient”
connectionString = “Data Source=localhost; Integrated
Security=SSPI;Initial Catalog=MyDB”/>
</connectionStrings>

MySQL:

<connectionStrings>
<add name=”MyDatabase”
providerName=”MySql.Data.MySQLClient”
connectionString=”Server=localhost;Database=MyDB;User
ID=Me;Password=MyPassword” />
</connectionStrings>

ConfigurationManager

The ConfigurationManager provides access to the defined connections within the Machine.Config (and App.Config) files via the name provided. Above we defined a connection called MyDatabase:

var connectionDetails =
ConfigurationManager.ConnectionStrings["MyDatabase"];

var providerName = connectionDetails.ProviderName;
var connectionString = connectionDetails.ConnectionString;

Connecting To a Database

To connect to a database an instance of DbConnection should be initialised against the database vendor specific connection string.

The The DbProviderFactories can be used to create the database connection object. It takes the database provider name which we configured as part of the connection information.

DbProviderFactory dbFactory =
DbProviderFactories.GetFactory(providerName);

using (DbConnection connection = dbFactory.CreateConnection())
{
connection.ConnectionString = connectionString;
connection.Open();
}

DbConnection implements IDisposable; it should be contained within a using scope.

ConnectionStringBuilder

The ConnectionStringBuilder class can be used in conjunction with a connection string to help customise a connection string in code. The index method [] can be used to set key value pairs representing connection properties.

It can be initialised with a connection string:

var connectionDetails =
ConfigurationManager.ConnectionStrings ["MyDatabase"];

var providerName = connectionDetails.ProviderName;
var connectionString = connectionDetails.ConnectionString;

var dbFactory = DbProviderFactories.GetFactory (providerName);

var builder = dbFactory.CreateConnectionStringBuilder ();
builder.ConnectionString = connectionString;
builder ["ConnectionTimeout"] = 60;

using (var connection = dbFactory.CreateConnection ()) {
connection.ConnectionString = builder.ConnectionString;
connection.Open ();
}

Connected vs Disconnected Layers

ADO.NET provides two conceptual layers; connected and disconnected.

The connected layer requires a live connection to the database to remain open during interaction. It is used for short transactions to the database such as calling stored procedures, running schema update or writeable transactions. The layer can be used for reading data though as the connection remains open; it is advised to limit the usage for short use.

The disconnected layer allows populating a DataSet object which allows an offline representation of the data. The connection remains open only for the amount of time it takes to populate the required data into the DataSet. The dataset can then be manipulated independently without the connection being open. The data can even be persisted back to the database at a later date.

Day 8.3 – Agnostic database connection with ADO.NET

The Source Code

This post is part of my Dot Net On Linux posts. All the source code is available on GitHub here

Intro

The ADO.NET framework abstracts each database provider such that our code needs to know very little of the actual implemented database. What little we do require can be abstracted into a factory for each provider and then injected into our code via a DI kernel. Only each database provider requires any knowledge of the actual database.

The following constitutes the few items which require an understanding of the actual database implemented.

  • Connection and connection string
    • Initiation of MySQLConnection or SQLiteConnection with the correct connection string
    • Connection string should be placed within machine.config ConnectionStrings.
    • Each connection implements IDbConnection
  • DataAdapter
    • Initiation of MySQLDataAdapter or SQLiteDataAdapter
    • Each adapter implements IDbDataAdapter
  • Command
    • Though not actually required by our factory it feels right to include this here.
    • Initiation of DbCommand with our connection via the CreateCommand upon the connection object
  • Database Schema
    • Even between SQLServer and .NET we don’t have the CTE. SQLite’s 4 data types proved a little troublesome.

Connection and connection string

A good place for storing our ADO.NET DataProvider connection string is in the machine.config, there is even a pre existing section to keep them all together called ‘ConnectionStrings’. We can then reference the hard coded database provider specific connection string via:

Creating an instance of our database provider connection is as simple as:

new MySQLConnection(connectionString);
new SQLiteConnection(connectionString);

DataAdapter

Our factory simply requires to return an instantiated version of our providers data adapter

new SqliteDataAdapter();
new MySqlDataAdapter();

Command

connection.CreateCommand();

An Example Factory

 public class MySQLADOProviderFactory : IADODataProviderFactory
 {
        public IDbConnection GetConnection()
        {
            return new MySqlConnection(GetDBConnectionString());
        }

        public IDbDataAdapter GetDataAdapter()
        {
            return new MySqlDataAdapter();
        }

        public string GetDBConnectionString()
        {
            return System.Configuration.ConfigurationManager.ConnectionStrings ["DotNetOnLinux"].ConnectionString;
        }
 }
public class ProviderFactory : IProviderFactory
{
        public IADODataProviderFactory GetADODataProviderFactory()
        {
            return InjectionFactory.Instance.kernel.Get<IADODataProviderFactory>();
        }
}

Injecting The Factory

public class ProviderFactory : IProviderFactory
{
        public IADODataProviderFactory GetADODataProviderFactory()
        {
            return InjectionFactory.Instance.kernel.Get<IADODataProviderFactory>();
        }
}

Database Schema

The hardest part of this work was getting the model and the two database table schemas to behave nicely with each other.

I had already coded the Id field in MySQL as INT and the associated Id column on SampleModel.cs as int. I had casting issues when mapping SQLite Integer data type as it actually mapped more directly to MySQL BIGINT(20) and c# long types.

The name field had to be changed in MySQL to a VARCHAR(45) to accommodate the size of SQLite TEXT data type.

Dates worked fine though both databases required the format to be ‘yyyy-MM-dd

Day 8.2 – Dot NET On Linux – Connecting & Consuming SQLite with ADO.NET

Intro

SQLite is as light weight and simple yet remarkably powerful relational database. It requires no installation other than the existence of the executable and requires no running services SQLite does not support concurrent databases writes and in fact locks the whole database from other write transactions while writing. It also does not support network access;  it requires the executable, database file and implementing software to exist on the same machine.

SQLite has a good website for appropriate use here. Though this might not be suitable for a live website requiring concurrent usage across a network it still has several usages including strong integration within the Java based Android framework and is strongly promoted for storing application and user settings.

Mono has a SQLite ADO.NET data provider installed as default.

This post will run through installing, database and scheme creation and then connection and consumption through code.

The Source Code

This post is part of my Dot Net On Linux posts. All the source code is available on GitHub here

Installation

We are going to install sqllite3, the documentation and sqlitebrowserl  a simple yet convenient tool for managing and using SQLite databases. Version 3 is in the LMDE repositories along with sqlitebrowser and can be installed with the following command.

sudo apt-get install sqlite3 sqlite3-doc sqlitebrowser

Create a new Database

Open up sqllitebrowser via the launch icon which should be sitting inside the Programming menu. From the File menu select File –> New Database. You will be provided with a file dialog box. Find a suitable location to create the database file and select OK.

Create our Sample Table

To create a sample table go to the Edit menu and select; Edit File –> Create Table

Provide a sample name and then hit Add. To add a new field provide a name and a type from one of; TEXT, NUMERIC, BLOB and INTEGER PRIMARY KEY.

The screen shots below should be enough information to create the same SampleTable which mimics the same table we created in MySQL previously.

We could go into MySQL and generate a SQL script to create our SampleTable but this won’t actually work. I talk about this more in the next blog when I show how we can write code to interface with both MySQL and Sqlite with virtually no differences other than the connection string and a few tweaks.

SQLite DataTypes

You can read about the SQLite data types  here to help understand why they are very limited. In short the data type matches what you store and not what you define. This caused a few problems for me as you can imagine when I initially swapped out MySQL for SQLite in the previous code we wrote. This also explains why I have picked text to store a DateTime type.Connection.

The SQLite data provider comes installed and registered with mono. We don’t need to touch the machine.config to register the data provider like we did with MySQL. We still want to register the connection string in the machine.config. Search for and add in the following section:

<pre><connectionStrings>
    <add name="DotNetOnLinuxSQLLite" connectionString="Data Source=/PersonalData/DevProjects/SandBox/Git/DOT-NET-on-Linux/Day8ADOExample/ADOExample/SqliteDB/SampleSqlite.db;Version=3;" />
	</connectionStrings>

As you can see the connection string is pretty simple requiring just the location of the file and the version of SQLite.

The connection string can be easily accessed in code by:

System.Configuration.ConfigurationManager.ConnectionStrings ["DotNetOnLinuxSQLLite"].ConnectionString;

The code

The nice thing about ADO.NET is that once you learnt the framework you can use it for any database which provides an ADO.NET data provider. The code is virtually the same which allows the code behave agnostic to the database; see my next post where I talk about how to implement this.

There are plenty of tutorials on how to implement ADO.NET so I will not add to them here. The code is available for download ( see at the top) where I have tried to implement a simple DAL layer with basic ORM capability.

Day 8.1 – Dot NET On Linux – Connecting & Consuming MySQL with ADO.NET

Introduction

The ADO.NET framework provides a consistent way to connect and consume databases through the .NET languages. Microsoft provide ADO.NET data providers for a number of databases however support for MySQL come from Oracle.

The Source Code

This post is part of my Dot Net On Linux posts. All the source the source code is available on GitHub here

Installation

The Mono provider for MySQL can be used in both mono and .NET and can be downloaded here.

Download and extract the file taking note of the version you have downloaded.

The dll which was shipped as part of the downloaded was configured incorrectly. It has been named all in lower case and needs to be named in upper camel case just like its namespace; rename the dll to MySql.Data.dll which you can do with the following command.

cd path_to_your mysql.data.dll
cp mysql.data.dll MySql.Data.dll

GAC Registration

The MySQL ADO.Net data provider is required to be installed in the GAC. Mono provides a gacutil just like .Net on windows and it follows the same function arguments. You can register the dll with the following command;

sudo gacutil -i MySql.Data.dll

You need to register the dll in the GAC with root privileges; hence why we sudo. You can check the dll was successfully registered with the following commands.

Through the GAC utility application

gacutil -l | grep My

Or by searching the physical location of the GAC.

cd /usr/lib/mono/gac
ls | grep MySql.Data

No matter how many .NET run-time environment versions you have installed you will onyl have one GAC by default. The dlls are version registered which is automatically resolved during run-time. When you reference a dll which has been installed in the GAC you  reference a copy of a correct versioned dll anywhere on your hard disk; this is resolved to the version in the GAC during run-time.

Machine.Config Registration

The MySQL data provider needs to be registered within the Machine.Config. Depending upon how many .Net run-time versions you have installed on your machine you could have more than one machine.config on your disk. I have .NET 2, 3 and 4 with a machine.config inside each of the installations.

I am coding in the latest available version of .Net 4 so I placed my config inside this file; /etc/mono/4.0/machine.config. You will need to edit this file as root. Search for the section and add in a section for our MySQL data provider.

<system.data>
<...>
<OTHER PROVIDERS>
    <DbProviderFactories>
        <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL"
	    type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.3.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
</system.data>

Please note the version number of the download against the version you have downloaded. The PublicKeyToken should remain constant.

Connecting through ADO.NET

Firstly register our database as a connection string within the machine.config used previously. Search for and add in the connection details for our MySQL database named DotNetOnLinuxExample.

    <connectionStrings>
        <.....>
        <OTHER CONNECTION STRIGNS>
        <add name="DotNetOnLinux" connectionString="Server=localhost;Database=DotNetOnLinuxExample;User ID=ReadWriteUser;Password=MYPASSWORD;Pooling=false" providerName="MySql.Data." />
</connectionStrings>

This registers our MySQL database as running on our local host. The Database connected to is Database={DataBaseName} which we created previously as DotNetOnLinuxExample. I will create a new user with ReadWrite access which will only have access to read and write data. Using our AdminUser user would allow our code more rights than is required. I have kept connection pooling turned off as we are running locally with no concurrent users. The final part is the name of the dll we downloaded and regsitered in the GAC for the MySQL data provider.

We can then simply refer to this string as DotNetOnLinux through our applications strings like so:

System.Configuration.ConfigurationManager.ConnectionStrings ["DotNetOnLinux"].ConnectionString;

Next we need to add a reference to the MySql.Data.dll. As noted before we need to locate this dll as if it is a standalone dll and not registerd in the GAC

The code

The nice thing about ADO.NET is that once you learnt the framework you can use it for any database which provides an ADO.NET data provider. The code is virtually the same which allows a database agnostic connection and consumption which I will prove later when we swap MySQL for Sqlite with minimal effort; cough*.

There are plenty of tutorials on how to implement ADO.NET so I will not add to them here. The code is available for download where I have tried to implement a simple DAL layer with basic ORM capability.

Creating a MySQL User with Read and Write Access

We don’t want our run time environment to have access to any commands which we don’t need. We will create a user for our ADO.NET provider to use on our database DotNetOnLinuxExample which only has read and write access to this database.

The following creates a user called ReadWrite on the database named DotNetOnLinuxExample which can only connect to our database from the local host. Change the password NEWPD to something meaningful. We then grant permission to run the commands SELECT, INSERT, DELETE and UPDATE on any table within this database. This might not be as secure as required however for development purposes this is secure enough.

INSERT INTO mysql.user (User,Host,Password) VALUES('ReadWriteUser','localhost',PASSWORD('NEWPD'));
FLUSH PRIVILEGES;

GRANT SELECT, INSERT, DELETE, UPDATE ON DotNetOnLinuxExample.* TO ReadWriteUser@'localhost';
FLUSH PRIVILEGES;

show grants for 'ReadWriteUser'@'localhost';

Day 6 – Dot NET On Linux – Installing and a quick tour of Workbench

Intro

Work bench is a GUI for managing and interacting with MySQL; just like SQL Management Studio for SQL Server. It allows you to perform tasks such as creating, querying and maintaining data, creating and modifying schema as well as database administration tasks. This post gives instructions on how to install, connect and interact with our new MySQL database which we created previously in the last blog post here.. We also have a quick  tour of the more common features we will require. You can read more here.

The Source Code

This post is part of my Dot Net On Linux posts. All the source is available on GitHub here

Installation

You can install the latest version of Workbench from the website above however installing everything from your supported Linux distro’s repositories is a recommend practice. If you installed the latest version of MySQL Server then you should follow suit here; however assuming you have come from my previous blog then you are using the version as installed in your repositories.

sudo apt-get install mysql-workbench

Run

An icon should have been placed within your start menu (Programming menu tree for me). Alternatively you can run the following command

mysql-workbench

Connect to our database

Configure A Connection

We are going to configure a database connection which will persist; we will then be able to connect to the database by selecting our configured connection in our connection list. Go to Database –> Manage Connections and the Manage DB Connections dialog will open. Enter the information as shown in the screen below replacing any details for your installation.

This will create a configured entry call DotNetOnLinux which connects to our service running on local host (127.0.0.1) on the default port (which we did not change this during installation) of 3306. The user we configured was called AdminUser.

For password hit the Store in Keychain.

Hit test connection and make sure it succeeds

Open a connection

To start working on our database we need to open the Connect to Database dialog box. This can be found under the Database menu; Database –> Query Database.

Select the configured connection from the previous step, select OK and when prompted enter your password. This is the MySQL user’s password and not your desktop user’s password.

Lets check the connection; we can see our database in the sachems tree view on the far left. You can open the tree view to see any tables and views.

Lets test the connection further by running a database query. Run the following to see which databases we are connected to:

show databases

You should see DotNetOnLinuxExample (or the name of you database you have created) and information_schema

A quick tour

Far left the schema tree view which will show any databases, tables, stored procedures, views etc. Highlighting a table allows pre-configured scripts to insert, update, delete and select statement into a query pane.

Far right is a snippets pane with pre-configured SQL which can be used as a template.

To create a new query tab there is a menu item under the File menu.

Results are displayed bottom middle.

Create a table

Highlight tables under the DotNetOnLinuxExample database schema entry.

Enter the table name. This one is called SampleTable. Leave the Schema to be our database. Leave the collation and engine as the defaults.

The following screen shot shows what to put in. Double click the last row under column name to enter a new name. Enter the names and data types as shown below.

The check boxes represent:

PX: Primary Key
NN: Non Null (mandatory)
AI: Identity Insert
Default: Default value when not provided.

Being a simple example we can leave everything else. Hit the apply button and.

Our table is now created and we can validate this by running the following query in a Query tab.

<pre>select * from DotNetOnLinuxExample.SampleTable

Lets insert some data,

INSERT INTO DotNetOnLinuxExample.SampleTable
(Name, Height, DateOfBirth)
VALUES ( 'Test', 180, '1978-10-25' )

Lets check the data was created correctly.

select * from DotNetOnLinuxExample.SampleTable

Also note the ID column was automatically populated. We can insert many entries and this will automatically be incremented.

Additional Worthy Notes

I am loving the beautify script option. My only gripe is it does not put key words into upper-case.

Toggle Auto Commit option along with Commit / Roll-back is pretty sweet as well.