Data Access using ADO.NET

Lesson 1

Disconnected Data Access

Previous technologies connected oriented. Failed:

Open connection resource intensive
Difficult to scale

ADO.NET = disconnected by default – connection maintained only while perform requisite action (e.g. perform query)
Relies on 2 components – DataSet and DataProvider

Data Set

Disconnected, in memory data representation
Load data into DataSet from any valid source

SQL Server
Access Database
XML file

Contains 0+ DataTable objects (represent single table)

DataTable structure determined by schema, comprises of

DataColumns collection (enumerates columns of a table)
Constraint collection (enumerates table constraints)

1 DataRelations collection

Create associations between different table rows by enumerating DataRelation objects

ExtendedProperties

store customised data

SELECT statement to generate result set
Date of generation
Etc.

Persisted with schema information for

DataSet
DataTable
DataColumn

Data Provider

Provides and maintains link to database
Lightweight
Set of related components

Connection

actual connection to DB
.NET provides two providers – SQL Server and OLE DB

SQL Server much lighter than OLE DB

Info required for connection stored in ConnectionString property

Similar to previous OLE DB version
Only set when connection closed
Must always specify server (can be set to local)
Security info (e.g. password) not returned unless “Persist Security Info = true” present
Argument Exception generated if ConnectionString cannot be parsed
Don’t need to specify provider (e.g. SQLOLEDB) if using SqlConnection
Avoid using UDL (Universal Data Link) files as parsed every time connection opened (because file may have changed) – use static connection string

Automatically pooled

system will reuse existing connection if ConnectionString matches and not currently in use (i.e. Close() or Dispose() has been called)
connection removed if lifetime expired or connection severed

Raises 2 events related to connection state

InfoMessage – Warnings and Information messages from data source (errors raise exceptions)
StateChange – when state of connection changes, determine state change by comparing OriginalState and CurrentState properties

Command

Execute DB commands across Connection
.NET provides two providers – SQL Server and OLE DB
Constructor takes optional args

SQL statement to execute at data source
Connection object
Transaction object

ExecuteNonQuery – execute commands with no return values, e.g. Data Definition Language DDL statements like ‘CREATE TABLE’ and some SPs
ExecuteScalar – execute commands returning single value (value of first column of first row in result set)
ExecuteReader – retrieve result as stream of data via DataReader object
When using with stored proc may set CommandType property to StoredProcedure and use Parameters property to access input, output and return values (if using ExecuteReader then output and return values not available until DataReader closed)

DataReader

Cannot be directly instantiated
Forward only, read only, connected recordset from database
Can increase app performance due to reduced system overhead (only 1 row in memory at a time)
Useful when do not need to keep data cache in memory
Only one row in memory at a time – low overhead
Requires exclusive use of Connection object during its lifetime

DataAdapter

Core of disconnected data access
Facilitates communication between database and DataSet
Fills DataTable or DataSet with info from DB when Fill method called
Transmit changes back to DB when Update called (using appropriate insert, delete or update command)
4 properties represent database commands

SelectCommand – contains command text or object to select data from db. Executed when Fill method called.
Insertcommand – contains command text or object to insert data into table.
DeleteCommand – contains command text or object to delete row from table.
UpdateCommand – contains command text or object to update data in db.

DataReader Vs DataSet

Use DataSet for following

Remote data between tiers or from XML Web Service
Dynamic data interaction

Binding to Form Controls
Combining and relating data from multiple sources

Cache data locally
Provide hierarchical XML view of data – use XSLT or XPath query on data
Perform extensive processing without requiring open connection to data source

DataReader

Improved performance

Save memory consumed by DataSet
Save time used to fill create and fill DataSet

Lesson 2

Connecting to DB

Easiest way to implement connection = use IDE Server Explorer window
Available data connections = child nodes of Data Connections node
Create new connection by right clicking Data Connections and choosing Add Connection – configure details on Data Link Providers dialog box
Drag connection from Server Explorer to designer – creates + configures Connection object
From code create connection object and set connection string

OleDbConnection myConnection = new OleDbConnection();

myConnection.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\\Northwind.mdb”;

Using Command

Do not require DataAdapter to interact with DB
Create:

Drag stored procedure from Server Explorer to designer
Drag SqlCommand / OleDbCommand from data tab or toolbox
Declare and instantiate instance of Command object

CommandType property indicates its type

Text – CommandText contains SQL query
StoredProcedure – CommandText contains name of SP
TableDirect – name of table(s) indicated by CommandText. Returns all columns and rows of specified tables

Set Connection property to active connection
Parameters are values that fill placeholders in command text at run time, stored in Parameters property and read at runtime

SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

SqlCommand salesCMD = new SqlCommand("SalesByCategory", nwindConn);

salesCMD.CommandType = CommandType.StoredProcedure;

SqlParameter myParm = salesCMD.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15);

myParm.Value = "Beverages";

nwindConn.Open();

SqlDataReader myReader = salesCMD.ExecuteReader();

Console.WriteLine("{0}, {1}", myReader.GetName(0), myReader.GetName(1));

while (myReader.Read())

{

Console.WriteLine("{0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1));

}

myReader.Close();

nwindConn.Close();

Some properties exposed by Parameters collection

DbType – type represented in CTS (common type system)
Direction - is parm Input, Output or InputOutput
OleDbType – type as exists in database
ParameterName – name to be used as key in code (if not using ordinal)
Precision – maximum number of digits in value (numeric and decimal)
Scale – number of decimal places in value (numeric and decimal)
Size – maximum size of data in column (string and binary)
SourceColumn – column to look up or map values
SourceVersion – version of column to use when editing
Value – Value represented by parameter

Can provide CommandBehavior argument to extract BLOBs from database. Set to CommandBehavior.SequentialAccess and use GetBytes() to access data. Must retrieve data in order presented – once reader steps beyond that column it is no longer available

SqlConnection pubsConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=pubs;");

SqlCommand logoCMD = new SqlCommand("SELECT pub_id, logo FROM pub_info", pubsConn);

FileStream fs; // Writes the BLOB to a file

// (*.bmp).

BinaryWriter bw; // Streams the BLOB to the FileStream object.

int bufferSize = 100; // Size of the BLOB buffer.

byte[] outbyte = new byte[bufferSize]; // The BLOB byte[] buffer to

// be filled by GetBytes.

long retval; // The bytes returned from

// GetBytes.

long startIndex = 0; // The starting position in

// the BLOB output.

string pub_id = ""; // The publisher id to use in

// the file name.

// Open the connection and read data into the DataReader.

pubsConn.Open();

SqlDataReader myReader = logoCMD.ExecuteReader(CommandBehavior.SequentialAccess);

while (myReader.Read())

{

// Get the publisher id, which must occur before getting the logo.

pub_id = myReader.GetString(0);

// Create a file to hold the output.

fs = new FileStream("logo" + pub_id + ".bmp", FileMode.OpenOrCreate, FileAccess.Write);

bw = new BinaryWriter(fs);

// Reset the starting byte for the new BLOB.

startIndex = 0;

// Read the bytes into outbyte[] and retain the number of bytes

// returned.

retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);

// Continue reading and writing while there are bytes beyond the

// size of the buffer.

while (retval == bufferSize)

{

bw.Write(outbyte);

bw.Flush();

// Reposition the start index to the end of the last buffer and

// fill the buffer.

startIndex+= bufferSize;

retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);

}

// Write the remaining buffer.

bw.Write(outbyte);

bw.Flush();

// Close the output file.

bw.Close();

fs.Close();

}

// Close the reader and the connection.

myReader.Close();

pubsConn.Close();

Using DataReaders

Created implicitly

System.Data.SqlClient.SqlDataReader mySqlReader;

mySqlReader = mySqlCommand.ExecuteReader();

Access columns simply – either by ordinal (based from 0) or column name

while(mySqlReader.Read())

{

object myObject = myDataReader[3];

object myOtherObject = mydataReader[“CustID”];

}

Data exposed by DataReader types as objects

Can retrieve as typed data – Get followed by type name

e.g. GetBoolean(3)
Must know ordinal (doesn’t work with column name)
Can retrieve ordinal from column name using GetOrdinal() method
Useful if know type of particular column

Can retrieve multiple result sets from single command

Set CommandType of Command object to Text
Separate SQL statements using ;
Each SQL statement executed sequentially, each result set returned separately by DataReader
First result set returned automatically from DataReader
Subsequent result sets accessed by calling NextResult() method

{

while(myReader.Read())

{

// Loop through records of RecordSet

}

} while (myReader.NextResult());

Executing ad hoc SQL queries

First build command string
Set CommandText and Connection properties for Command object at construction

string Cmd = “DELETE * FROM Employees WHERE Name=’” & aString & “’”;

OleDbCommand myCommand = new OleDbCommand(Cmd, myConnection);

myConnection.Open();

myCommand.ExecuteNonQuery();

myConnection.Close();

Obtain schema info about result using GetSchemaTable() method. Returns DataTable populated with schema for current result set. Contains one row for each column in result set, each column maps to a property of colum returned in result set where ColumnName = name of property and value is value of property

DataTable schemaTable = myReader.GetSchemaTable();

foreach (DataRow myRow in schemaTable.Rows)

{

foreach (DataColumn myCol in

schemaTable.Columns)

{

Console.WriteLine(myCol.ColumnName + " = " +

myRow[myCol]);

}

Console.WriteLine();

}

Always call Close() method when finished with DataReader

Creating Data Adapters

Link data source and DataSet - providing functionality to retrieve data, populate DataSet and perform updates
Manages exchange between single DataTable in DataSet and data source. DataSet frequently contains multiple DataTables – create DataAdapter for each DataTable
2 primary adapters

SqlDataAdapter
OleDbAdapter

Create DataAdpter

By Server Explorer

Drag table from Data Connection node of Server Explorer to Designer Window
Can return subset of columns in table by selection desired columns from Data Connection node to Server Explorer

By Data Adapter Configuration Wizard

Drag DataAdapter from Data tab of toolbox into Designer Window
Select appropriate Data Connection from wizards drop down list
Choose Query Type

SQL Statements
New Stored Procedures
Existing Stored Procedures

Generate query / choose appropriate stored procedure

By Code

Using Data Adapters

Populate Dataset with data using DataAdapter fill method

DataSet myDataSet = new DataSet();

// Assume existence of Data Adapter named myDataAdapter

myDataAdapter.Fill(myDataSet, “Customers”);

A single DataSet can be target of multiple Fill commands – each DataAdapter responsible for a single table held within the DataSet

· Preview Data returned by DataAdapter by selecting ‘Preview Data’ from Data menu

Available DataAdapter objects listed in drop-down menu
Click Fill button to execute DataAdapter Fill() method

Implicitly opens and closes the associated connection. Can improve performance by manually opening and closing connection around series of operations.

// Populate list of customers from SQL 2000 database and Orders from

// Access database, establish relation between tables, display list

// of customers and their orders

SqlConnection custConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind;");

SqlDataAdapter custDA = new SqlDataAdapter("SELECT * FROM Customers", custConn);

OleDbConnection orderConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source=c:\\Program Files\\Microsoft Office\\Office\\Samples\\northwind.mdb;");

OleDbDataAdapter orderDA = new OleDbDataAdapter("SELECT * FROM Orders", orderConn);

custConn.Open();

orderConn.Open();

DataSet custDS = new DataSet();

custDA.Fill(custDS, "Customers");

orderDA.Fill(custDS, "Orders");

custConn.Close();

orderConn.Close();

DataRelation custOrderRel = custDS.Relations.Add("CustOrders",

custDS.Tables["Customers"].Columns["CustomerID"],

custDS.Tables["Orders"].Columns["CustomerID"]);

foreach (DataRow pRow in custDS.Tables["Customers"].Rows)

{

Console.WriteLine(pRow["CustomerID"]);

foreach (DataRow cRow in pRow.GetChildRows(custOrderRel))

Console.WriteLine("\t" + cRow["OrderID"]);

}

Features InsertCommand, UpdateCommand and DeleteCommand properties that are called when DataAdapter encounters change to DataRow wjem processing Update() method. If call Update() before appropriate command set (e.g. no DeleteColumn for deleted rows) then exception thrown.

Add Primary Key constraint details using FillSchema method() prior to calling Fill() on Data Adapter. Note, if AutoIncrment column found the Data Adapter will have AutoIncrment set to TRUE, but you will have to set AutoIncrementStep and AutoIncrmentKey manually. FilSchema() is costly to call – if know primary-key information at design time then these should be set explicitly to increase performance.

// Obtain schema via FillSchema()

DataSet custDS = new DataSet();

custDA.FillSchema(custDS, SchemaType.Source, "Customers");

custDA.Fill(custDS, "Customers");

// Obtain schema via Fill()

DataSet custDS = new DataSet();

custDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;

custDA.Fill(custDS, "Customers");

TableMappings permit use of column names in DataTable different to those in database, e.g. to create mapping called AuthorsMapping for the MyAuthors table use

workAdapter.TableMappings.Add(“AuthorsMapping”, “MyAuthors”);

If no mapping name is passed to Fill() method, then the DataAdapter will look for mapping called “Table”. If any column is not present in mapping then it is provided with default name of SourceColumnN (N being incremented). Likewise if no table name provided then default name of SourceTabeN is used. Following creates default mapping:

DataTableMapping custMap = custDA.TableMappings.Add("Table",

"NorthwindCustomers");

custMap.ColumnMappings.Add( "CompanyName", "Company");

custMap.ColumnMappings.Add( "ContactName", "Contact");

custMap.ColumnMappings.Add( "PostalCode", "ZIPCode");

custDA.Fill(custDS);

If SelectCommand returns multiple tables, Fill will generate Table names with incremental names starting from specified name. Use table mappings to map automatically generated names to names want to use, e.g. following SelectCommand returns two tables that will be named Customers and Customes1, map Customers1 to Orders

custDA.TableMappings.Add("Customers1", "Orders")

custDA.Fill(custDS, "Customers")

Typed DataSets

· Standard DataSet = weekly typed – must convert data point from object to desired type

· Typed DataSet = strongly typed

· Instance of class derived from DataSet

· Class structure defined by XML schema file (XSD) defining table names and types

· Dependence on XSD means only use where know structure of data being used in advance

· Create using Generate Dataset button in Data dialog box – generates XSD file and optionally adds instance of DataSet to designer

Lesson 3

Filling DataSet without DataAdapter

Create DataSet programmatically, and provide it with structure

DataSet myDataSet = new DataSet();

DataTable myTable = new DataTable();

myDataSet.Tables.Add(myTable);

DataColumn AccountsColumn = new DataColumn(“Accounts”);

myDataSet.Tables[0].Columns.Add(accountsColumn);

DataRow myRow;

myRow = myDataSet.Tables[0].NewRow();

for(int i=0; I < StringCollection.Count; i++)

{

myRow.Item[counter] = StringCollection [i];

}

myDataSet.Tables(0).Rows.Add(myRow);

Access Flat File and populate DataSet using programmatic technique outlined above
When reading flat file must no delimiters – frequently , or ; or : between columns and new-line between rows
Create DataSet around file structure

DataSet myDataSet = new DataSet();

DataTable aTable = new DataTable(“Table 1”);

myDataSet.Tables.Add(aTable);

DataColumn aColumn;

for(int counter=0; counter <7 ;counter++)

{

aColumn = new DataColumn(“Column “ +

counter.ToStgring());

mydataSet.Tables[“Table 1”].Columns.Add

(aColumn);

}

System.IO.StreamReader myReader = new System.IO.StreamReader(“C:\\File.txt”);

String myString;

while (myReader.Peek() != -1)

{

myString = myReader.ReadLine();

myDataSet.Tables[“Table 1”].Rows.Add

(myString.Split(char.Parse(“,”)));

}

Data Relation Objects

Represents relationship between two columns in different tables within DataSet
Contained in Relations property of DataSet
Create by specifying name of DataRelation, parent column and child column

DataRelation myRelation = new DataRelation(“Data Relation 1”, column1, column2);

myDataSet.Relations.Add(myRelation);

Use to retrieve parent and child rows by calling GetChildRows or GetParentRow on DataRow providing DataRelation as parm

DatasRow[] ChildRows;

DataRow ParentRow;

// Return all child rows of row 1 as defined by CustomersOrders

ChildRows = myDataSet.Tables[“Customers”].Rows[1].GetChildRows(CustomersOrders);

// Return parent of row 5 as defined by CustomersOrders

ParentRow = myDataSet.Tables[“Orders”].Rows[5].GetParentRow(CustomersOrders);

Constraints

Work closely with DataRelation objects
Define rules by which data added and manipulated in DataTable
2 types

Unique – specifies column(s) have no duplicate entries
Foreign Key – rules used to update child rows when parent edited

Create Unique Constraint

Easiest is set Unique property of DataRow to true
Can create UniqueConstraint and manually add to Constraints collection

UniqueConstraint myConstraint = new

UniqueConstraint(myDataRow);

myDataTable.Constraints.Add(myConstraint);

o Can specify multiple columns

DataColumn[] myColumns = new DataColumn[2];

myColumns[0] =

EmployeesTable.Columns[“FirstName”];

myColumns[0] =

EmployeesTable.Columns[“LastName”];

UniqueConstraint myConstraint = new

UniqueConstraint(myColumns);

EmployeesTable.Constraints.Add(myConstraint);

· Create Foreign Key Constraint by specifying parent and child columns and adding to Constraints collection of parent table

ForeignKeyConstraint myConstraint = new

ForeignKeyConstraint(CustomersTbl.Columns[“Cust

omerID”], OrdersTbl.Columns[“CustomerID”]);

CustomersTbl.Constraints.Add(myConstraint);

· ForeignKeyConstraint has 3 rules exposed as properties

o UpdateRule – enforce when parent updated

o DeleteRule – enforce when parent deleted

o AcceptRejectRule – enforce when AcceptChanges method of DataTable to which constraint belongs is called

· Set 3 rules to one of following values (default = Cascade)

o Cascade – Changes in parent cascaded to child rows

o None – changes in parent have no effect on child rows. May lead to child records referencing invalid parents

o SetDefault – foreign key in child records set to its default value (see columns DefaultValue property)

o SetNull – foreign key in child table set to Null – can result in invalid data within children

Editing Data

DataSet maintain 2 versions of itself

o Original state

o Current state

When Update method of DataAdapter called appropriate UPDATE, INSERT and Delete commands issued based on differences between states

Changes made through data-bound controls automatically reflected in relevant row
Can programmatically add values to DataRow by setting its Item property (default property – no need to explicitly reference)

myDataRow[2] = “Splunge”;

myDataRow[“Customers”] = “Winthrop”;

Can roll-back changes to DataRow by calling RejectChanges method()

myDataRow.RejectChanges();

Can accept changes by calling AcceptChanges() method. This overwrites the original version of the DataRow with the edited version. Do not call before using Update method on DataAdapter, otherwise meaningless UPDA