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

do

{

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

• 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

• UpdateRule – enforce when parent updated

• DeleteRule – enforce when parent deleted

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

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

  • Cascade – Changes in parent cascaded to child rows

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

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

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

Editing Data

• DataSet maintain 2 versions of itself

• Original state

• 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 UPDATE commands will be issued.

• Current row state available in RowState property

  • Unchanged – original or not changed since AcceptChanges called

  • Modified – row edited since last AcceptChanges

  • Added – Newly created row added to DataRowCollection

  • Deleted – Row deleted using DataRow.Delete method

  • Detached – Row created but not part of any DataRow collection

Updating Database

• Copy data back to database using Update method of each DataAdapter – optionally specify DataSet, DataTable or array of DataRows as basis for update

myDataAdapter.Update();

myOtherDataAdapter.Update(myDataTable);

• Can perform update within transaction

• Call BeginTransaction of open Connection object to obtain transaction

• Execute commands to be encapsulated by transaction

• Call Commit on transaction object to accept, or Rollback to reject

System.Data.OleDb.OleDbTransaction myTransaction = null;

try

{

myConnection.Open();

myTransaction = myConnection.BeginTransaction();

…

myTransaction.Commit();

}

catch(Exception ex)

{

mytransaction.Rollback();

}

finally

{

myConnection.Close();

}

Handling Update Errors

• DataAdapter provides RowUpdate event that fires after update event but before exception thrown – opportunity to handle errors without resource-intensive exception blocks

• RowUpdate provides RowUpdatedEventArgs that is used to determine error that has occurred and how to proceed

  • Command – command to execute when performing update

  • Errors – errors generated by Data Provider when command executes

  • RecordsAffected – number of records affected by execution of command

  • Row – row updated

  • Status - UpdateStatus of command

• Determine if error occurred by examining Status.

  • Continue – no errors have occured

  • ErrorsOccured – error occurred while attempting to update this row

  • SkipAllRemainingRows – updates for current and subsequent rows should be skipped

  • SkipCurrentRow – update for current row should be skipped, but rest of updates should proceed normally

• Can choose to handle error in RowUpdated event by setting Status property to something other than ErrorsOccured

  • Note, if set Status to Continue the DataAdapter will ignore the error and continue but unpredictable results may arise (safer to use SkipAllRemainingRows or SkipCurrentRow)

  • If Status remains ErrorsOccured an exception thrown

private void myDataAdapter_RowUpdated( object sender, System.Data.SqlClient.SqlRowUpdatedEventArgs e)

{

if(e.Status == UpdateStatus.ErrorsOccured)

{

…

e.Status = UpdateStatus.SkipCurrentRow;

}

}

Lesson 4

Data Binding

• Relationship between data provider and consumer

• Example = TextBox which displays column in data source at current row. As row changes, the value displayed changes

Data Providers

• Provides data to bound properties and controls

• Note – not a DataProvider (components providing disconnected database access)

• Any object implementing IList can be data provider (includes arrays and collections)

• Manage data currency (i.e. current record) through CurrencyManager object

Data Consumers

• Can bind any run-time accessible property of any control to a data source

• Two types of binding

  • Simple – binds one record at a time to a control

  • Complex – binds multiple records simultaneously to a control, e.g. bind ListBox to a column in DataTable and it will display all members of that column

Create Simple bound control

• Use DataBindings property (instance of ControlBindingsCollection)

• Displayed as node in Properties window, expands to show properties that are commonly data bound

• To bind property click box next to it in DataBindings node and select desired provider (DataSets and DataViews can be expanded to show DataTables that can be expanded to list available columns

• If property not listed in DataBindings click Advanced box followed by Elipses (…) which lists all run time available properties

• At run-time can add, remove or clear data binding info by setting appropriate member of DataBindings collection

• Bind property using DataBindings.Add method passing in property name, data source to bind to and member of data source to bind to

TextBox1.DataBindings.Add(“Text”, DataSet1.Customers, “CustomerID”);

• If binding to source without multiple data members, e.g. collection or array, set third parm to blank (“”)

Data Currency

• Every data source manages navigation using a CurrencyManager object

• Data source = 1 or 2 D store (DataTable, DataView, array, collection)

• DataSet contains many DataTables, thus many data sources

• CurrencyManager keeps track of current record for its particular data source

• Form may have multiple CurrencyManagers associated within its data sources, manages these through the BindingContext

• Can use BindingContext to manage position of current record for each data source

this.BindingContext[DataSet1.Customers].Position = 0;

thid.bindingContext[DataSet1.Customers].Position++;

• .Net will not permit Position property to be set out of range

• Change of Position property causes PositionChanged event to be fired – use to provide additional processing on move (e.g. to indicate when range limit reached)

Complex Binding

• Some controls (e.g. ListBox, ComboBox, DataGrid) can be bound to more than one record simultaneously

• Usually used to display choices

• Create by setting DataSource property, either at design time or via code

• Some controls, such as ListBox and ComboBox, must be bound to single column – do this by setting DisplayMember property

ComboBox1.DataSource = DataSet1.Customers;

ComboBox1.DisplayMember = “CustomerID”;

Filtering and Sorting

• DataView permits working with subset of data held within DataTable

• DataView provides methods for sorting and filtering data

• DataView can update DataTable it represents

• Create DataView – can provide DataTable at construction, or later via Table property

DataView myDataView = new DataView(myDataTable);

Or

DataView myDataView = new DataView();

myDataView.Table = myDataTable;

• Can create at design time. Drag DataView from Data tab of Toolbox to designer and set its Table property in the properties window

• Bind controls to DataView by setting the DataBindings properties in the Properties window

• Sorting accomplished by setting Sort property to appropriate column name (or multiple columns separated by “,”). To sort descending append DESC to name

myDataView.Sort = “CustomerID”;

myOtherView.Sort = “State, City”;

myDescView.Sort = “State DESC, City”;

• Filter data through RowFilter property that takes string expression to be evaluated when selecting records. Build complex expressions using AND, OR and NOT together with arithmetic, concatenation and relational operations. IN and LIKE operators permit searches for specific strings.

myDataView.RowFilter = “City = ‘Seattle’”;

myDataView.RowFilter = “City = ‘Des Moines’ AND (NOT State =’IA’)”;

myDataView.RowFilter = “Price *1.086 <= 500”;

myDataView.RowFilter = “City IN (‘Seattle’, ‘Tacoma’, ‘Blaine’);

myDataView.RowFilter = “City LIKE ‘Se*t%e’”;

• Additional filtering provided by RowState property that can take 1 (or more) of the following values

  • Unchanged – displays rows that have not been changed

  • Added – displays rows that have been added

  • Deleted – displays rows that have been deleted

  • OrigianlRows – original rows including unchanged and deleted rows

  • CurrentRows – current rows (including added, modified and unchanged)

  • ModifiedCurrent – Modified version of original data

  • ModifiedOrigianl – Original version

• DataView has 3 properties that determine if underlying data can be edited. By default fully editable, deny access by setting appropriate property to false

  • AllowDelete – is deletion of rows in underlying DataTable allowed

  • AllowEdit – is editing of rows in underlying DataTable allowed

  • AllowNew – can rows be added to underlying DataTable

• DataViewManager is to DataSet what DataView is to DataTable.

• DataViewManager associated with DataSet and manages DataView objects for tables contained within DataSet

DataViewManager myManager = new

DataViewManager(myDataSet);

DataViewManager myOtherManager = new

DataViewManager();

myOtherManager.DataSet = myOtherDataSet;

• Manage RowFilter, Sort, etc. through DataViewSettings collection

// Set RowFilter property of DataView associated

// with Customers table

myDataViewManager.DataViewSettings[“Customers”]

.RowFilter = “State = ‘WA’”;

• Can retievde DataViews via CreateDataView method – requires reference to DataTable

DataView myDataView;

myDataView =

myDataViewManager.CreateDataView(DataSet1.Tables[0]);

Lesson 5

Retrieving XmlReader from SqlCommand

• XmlReader analogous to DataReader class – read only, forward only access to XML returned by query

• XmlReader (like DataReader) requires exclusive use of connection

• SqlCommand provides ExecuteXmlReader method to retrieve XMLReader object that exposed data from SqlCommand as XML rows

• SQL query executed by SqlCommand must contain FOR XML clause

• Always close XmlReader to free Connection

System.Xml.XmlReader myReader;

SqlCommand mySQLCommand = new SqlCommand(“SELECT *

FROM Customers FOR XML AUTO, XMLDATA”,

SqlConnection1);

SqlConnection1.Open();

myReader = mySQLCommand.ExecuteXmlReader();

while(myReader.Read())

{

// Write content and markup of node and its

// children

Console.WriteLine(myReader.ReadOuterXml());

}

myReader.Close();

SqlConnection1.Close();

Reading XML into DataSet

• Use DataSet.ReadXml to specify XML file, stream, XmlReader or TextReader

• Use DataSet.ReadXmlSchema to create DataSet with specified structure but no data

Writing XML from DataSet

• Use DataSet.WriteXml to export data in XML format

• Use DataSet.WriteXmlSchema to export structure of DataSet without its data

XmlDataDocument

• In memory representation of XML data

• Works closely with its associated DataSet – access to it via XmlDataDocument.DataSet property

• Can be manipulated by W3C Document Object Model (DOM)

• Serves as source for Extensible Style Sheet Transformations (XSLT)

• When create can provide associated DataSet to constructor

XmlDataDocument myDocument = new

XmlDataDocument(myDataSet);

• If no DataSet provided to constructor then new, empty one created automatically

• To load data into XmlDataDocument must first call ReadXmlSchema() method on DataSet. Then call Load() method on XmlDataDocument

XmlDataDocument mydocument = new XmlDataDocument();

myDocument.DataSet.ReadXmlSchema(“C:\\myXml.xml”);

myDocument.Load(“C:\\myXml.xml”);

XSLT Transformations

• Transform XML data into different formats, e.g. HTML

• XslTransform class provided for this purpose

• First load style sheet into class via XslTransform.Load()

• Call XslTransform.Transform()

  • Parm 1 = object to transform

  • Parm 2 = any parms required by stylesheet, otherwise NULL

  • Parm 3 = object to receive output (Stream, TextWriter or XmlWriter)

Xml.Xsl.XslTransform myTransform = new

Xml.Xsl.XslTransform();

myTransform.Load(“C:\\myStyle.xsl”);

System.IO.StreamWriter myWriter = new

System.IO.StreamWriter(“myfile.txt”);

myTransform.Transform(myDocument, null, myWriter);