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);
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s