1. Connection
A connection knows how to physically connect to the database. It's ConnectionString property stores all needed info. A connection is nothing more than a ConnectionString.

2. DataSet
A DataSet is a disconnected sub-copy of the original database, containing multiple tables and restraints between them. The database is maintained by the DBMS, while the sub-copy of data is maintained by the DataSet itself. When data in DataSet is changed, it caches the changes, until it is updated back to the source database.

3. DataAdapter
A data adapter represents a set of methods used to perform a two-way data updating mechanism between a disconnected DataTable and the database. It aggregates four commands: select, update, insert and delete command. One adapter can only generate and fill one table in a DataSet. Therefore to deal with multiple tables in a DataSet you need multiple DataAdapters.

4. Command
A command represents a particular method to get data from or set data into the database, usually in the form of a SQL query or stored procedure. It has to be conducted through a connection, so a command has a Connection property pointing to the connection.

1.2. SqlConnection vs. OleDbConnection

OleDbConnection uses OLE DB data providers to communicate with different kinds of databases. You can make use of Data Links dialog to construct the connection string for an OleDbConnection. You can not use this dialog to construct connection string for other data sources. Therefore, for a SqlConnection, e.g., you have to type in the connection string yourself.

Here is a connection string for an OleDbConnection using OLE DB Provider for SQL Database:

User ID=silan;Password=donottellyou;Persist Security Info=True;Initial Catalog=Northwind;Data Source=ELLEN;Provider=SQLOLEDB.1

Here is the connection string for a SqlConnection:

User ID=silan;Password=donottellyou;Persist Security Info=True;Initial Catalog=Northwind;Data Source=ELLEN

As you can see, the two connection strings are identical except that the connection string for an OleDbConnection has an extra parameter “Provider=SQLOLEDB.1”.

1.3. Three Ways to Create a Connection

As said before, a connection is all about a connection string. It can be manually keyed in, but Visual Studio.NET provides a “Data Link” dialog to help you simplify the job.

A connection can be created in three ways.

¨ Create a connection at run time

To acquire the connection string with the help of “Data Link” dialog, create an empty .udl file, then double-click it. The“Data Link” dialog will be brought up and the connection string will be finally saved as text in this file. You can use the .udl file directly in the connection string:

mcn.ConnectionString = “File Name=mydatalink.udl”

This udl file should be in the “bin\debug” folder of your project.

¨ Create a connection at design time with Server Explorer

Right-click the “Data Connections” item and choose “Add connection”. The “Data Link” dialog will be brought up. The created connection will be listed under the “Data Connections” item in Server Explorer, and can be dragged on to the component tray of a form or chosen by the “Data Adapter Configuration Wizard”.

¨ Create a connection when creating DataAdapter at design time

You can choose to use existing connection or create new one. It you choose to create a new connection, the “Data Link” dialog will be brought up.

1.4. Connection Pooling

A connection is implicitly openned if it is not yet openned when DataAdapter’s Fill or Update is called, and implicitly closed if it had been implicitly openned. This approach will produce unnecessary overhead if we need to call a batch of Fill and/or Update methods. In this case we can explicitly open the connection before the batch of calls, and explicitly close it after them.

In fact, a used connection is not destroyed. Instead it is by default pooled. When you open a connection with the same connection string as the used one before it times out, you are actually using the same pooled connection. To turn off the default connection pooling, add “OLE DB Services=-4” into the connection string of a OLE DB .NET data provider, or “Pooling=False” to the connection string of a SQL Client .NET data provider.

Because an existing connection in the pool is only reused if the new one has the same connection string, if you have a data access middle tier to talk to database, to enable that the same connection is reused when different users acquire the same data, you can not include the user’s credentials in the connection string. you have to use the same connection string for different users. This means that instead of letting the database to validate the user, the middle tier should do it itself using network security measures such as SSL.

1.5. Data Type Length of Database

Data type length of a column can be confusing if overlooked: if the cell data (number or string) entered by user exceeds the column length, the data will be simply truncated without any warning message. This may lead to all sorts of tedious problem such as violation of constraints.

Also note that the length of number is the number of bytes, not the number of digits. For example, if the data type of a column is smallint and the length is 2, then the maximum decimal number the column can hold is 2¹⁵– 1 = 32767, because smallint is 8 bits long, length of 2 is 16 bits, and we need one bit for sign.

1.6. Transaction within a Database

Note that there are two types of transactions: transaction within a database and transaction across databases i.e. distributed transaction. COM+ and .NET Enterprise Services handles the Distributed Transactions. Here we only discuss about transactions within a database, which is a lot easier, because the DBMS provides this functionality.

A transaction object is created from a connection by calling its BeginTransaction method. It starts a transaction in the database. When a transaction’s Commit or Rollback method is called, it notices the database to commit or roll back.

Once a transaction has been created for a connection, no command whose Transaction property is not pointing to the transaction can work through this connection, including a SELECT command. Once a transaction is committed/rolled back/closed, the transaction is finished. If you call Commit or Rollback or Close again an exception will be thrown.

mcn.Open()

Dim txn As OleDbTransaction = mcn.BeginTransaction

mda.UpdateCommand.Transaction = txn

mda.InsertCommand.Transaction = txn

mda.DeleteCommand.Transaction = txn

mda.Update(mds)

txn.Rollback()

1.7. Create an Assembly with Strong Name

Two simply steps to create an assembly with a strong name:

1. Create a cryptographic key pair file using the .NET tool with any name:

sn –k myfilename.snk

2. Set the key pair file name in the AssemblyInfo.cs of your project:

<Assembly: AssemblyKeyFile("myfilename.snk")>

The key pair file is only needed for compilation, and not needed when the assembly is run. When you compile, it should be in the “obj\debug” directory if it is a debug build or “obj\release” if it is a release build.

1.8. Storing Binary Data in SQL Server Database

Binary data are stored in SQL Server database as byte arrays. There are two SQL Server data types that can be used to store byte arrays:

1) binary
For fixed-length byte array of 50 bytes long. Even if you put in a 20 byte long array, when you get it out, it will still be 50 byte long, with the rest bytes being 0.

2) image
Variable-length byte array. If you put in an array of 23 bytes, when you get it out, it is 23 bytes.

This image data type only accept byte [], but it can be used to store any type of objects. The following code shows how to serialize an float array into a byte array and store into the “BinaryData” column of type image, and later retrieve this float array back:

DataSet ds = new DataSet();

mda.Fill(ds);

// Create and populate a float array

float [] floats = new float[10];

for (int i = 0; i < floats.Length; i++)

floats[i] = i * 1.1f;

// Serialize the float array into a byte array

MemoryStream stream = new MemoryStream();

BinaryFormatter formatter = new BinaryFormatter();

formatter.Serialize(stream, floats);

byte [] bytes = stream.GetBuffer();

stream.Close();

// Store the byte array in the image column in the database

DataRow row = ds.Tables["Test"].NewRow();

row["ID"] = 0;

row["BinaryData"] = bytes;

ds.Tables["Test"].Rows.Add(row);

mda.Update(ds);

Then, later...

DataSet ds = new DataSet();

mda.Fill(ds);

// Retrieve the stored byte array

byte [] bytes = (byte [])ds.Tables["Test"].Rows[0]["BinaryData"];

// Deserialize the byte array back to the float array

MemoryStream stream = new MemoryStream(bytes);

BinaryFormatter formatter = new BinaryFormatter();

float[] floats = (float[])formatter.Deserialize(stream);

stream.Close();

2. ADO Data Objects

2.1. DataReader

DataReader is designed for speed. It supports very limited functionality: it is read-only, and once you've read one row, you can not go back and read it again. It can only hold the result of one query, so unless the database supports batch queries, a DataReader can only hold one data table.

In comparison, DataSet has more powerful functionality. It can hold the result of multiple queries i.e. multiple tables.

If the DataReader contains results of a batch query, you can call its NextResult to move to the result of next query.

2.2. Accessing a Cell in a DataRow

There are four ways to access the content of a cell in a DataRow:

Dim ds As DataSet = New DataSet()

da.Fill(ds)

Dim row As DataRow = ds.Tables(0).Rows(0)

MessageBox.Show(row("CustomerID"))

MessageBox.Show(row(0))

MessageBox.Show(row.Item("CustomerID"))

MessageBox.Show(row.Item(0))

Item is a parameterized property of DataRow. It’s its default property, so the first two ways are in fact the same as the last two.

2.3. Different Versions of Cell Data

Each cell's value has two versions: original, proposed and current. After you have modified the value of a cell, its current version will be the modified value, and its original version remains unchanged, until you call AcceptChanges of the row. Then the original version will become the new value.

If you wrap the modification code with BeginEdit and EndEdit, after you have modified a cell and before you call EndEdit, the original and current version are both unchanged, while the proposed version is the new value. After you call EndEdit, current version becomes the new value, and proposed version becomes invalid (will throw exception if you try to access it).

ds.Orders(0).BeginEdit()

ds.Orders(0).CustomerID = "ABCDE"

Dim strOriginal, strCurrent, strProposed, msg As String

strOriginal = ds.Tables("Orders").Rows(0)("CustomerID", DataRowVersion.Original)

strCurrent = ds.Tables("Orders").Rows(0)("CustomerID", DataRowVersion.Current)

strProposed = ds.Tables("Orders").Rows(0)("CustomerID", DataRowVersion.Proposed)

msg = "Original value = " & strOriginal & ", Current Value = " & strCurrent & ", Proposed Value = " & strProposed

MessageBox.Show(msg)

ds.Orders(0).EndEdit()

2.4. DataRow.IsNull

DataRow has a method called IsNull which takes a column name or index and checks whether that item is null.

2.5. Expression Column

When your table need an expression column, you have two ways. First, you can put the expression in the SQL query such as

“Select OrderID, ProductID, UnitPrice, Quantity, UnitPrice * Quantity As Total From [Order Details]”

Then the dataset which is filled with the result will contain column called “Total”. This column has no difference from other columns such as “OrderID”, “ProductID”, etc. When “UnitPrice” or “Quantity” is changed, the corresponding “Total” will not change, because the evaluation of the expression is done by the database, not the dataset.

If you want “Total” to change when “UnitPrice” or “Quantity” is changed, you should not query for the expression. Instead you add an expression column into the DataTable and let it do the calculation:

Dim cn As OleDbConnection = New OleDbConnection("File Name=DataLink.udl")

Dim cmd As OleDbCommand = cn.CreateCommand()

cmd.CommandText = "Select OrderID, ProductID, UnitPrice, Quantity From [Order Details]"

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)

Dim tableMapping As DataTableMapping = da.TableMappings.Add("Table", "OrderDetails")

Dim ds As DataSet = New DataSet()

Dim table As DataTable = ds.Tables.Add("OrderDetails")

table.Columns.Add("Quantity", GetType(Decimal))

table.Columns.Add("UnitPrice", GetType(Decimal))

table.Columns.Add("Total", GetType(Decimal), "Quantity * UnitPrice")

da.Fill(ds)

mdg.DataSource = ds.Tables("OrderDetails")

The expression is stored in DataColumn.Expression property. It is by default an empty string. Setting it to anything other than empty string will meantime set the ReadOnly property to True.

Note: the parameter columns from which the expression column is calculated should have already been added into the table before the expression column is added, because DataTable evaluates the expression when the expression column is added and checks whether the parameter columns already exists in the table. If not, it will throw an exception.

The expression can be an aggregate result (sum, count or average) of a column of a child table, or a column of a parent table, if a proper DataRelation has been set up. You can only use aggregate result of a child table column but not the column itself, because for each row there are multiple rows in the child table.

Dim daOrders As OleDbDataAdapter = New OleDbDataAdapter()

daOrders.SelectCommand = New OleDbCommand( _

"SELECT OrderID, CustomerID, EmployeeID, ShipCountry FROM Orders")

daOrders.SelectCommand.Connection = mcn

Dim daOrderDetails As OleDbDataAdapter = New OleDbDataAdapter()

daOrderDetails.SelectCommand = New OleDbCommand("SELECT OrderID, ProductID, UnitPrice, Quantity FROM [Order Details]")

daOrderDetails.SelectCommand.Connection = mcn

Dim ds As DataSet = New DataSet()

Dim orders As DataTable = ds.Tables.Add("Orders")

Dim orderDetails As DataTable = ds.Tables.Add("OrderDetails")

daOrders.FillSchema(orders, SchemaType.Source)

daOrderDetails.FillSchema(orderDetails, SchemaType.Source)

ds.Relations.Add("Orders_OrderDetails", orders.Columns("OrderID"), orderDetails.Columns("OrderID"), False)

orderDetails.Columns.Add("ItemTotal", GetType(Decimal), "Quantity * UnitPrice")

orderDetails.Columns.Add("ShipCountry", GetType(String), "Parent(Orders_OrderDetails).ShipCountry")

orders.Columns.Add("NumOfItems", GetType(Integer), "Count(Child.OrderID)")

orders.Columns.Add("OrderTotal", GetType(Decimal), "Sum(Child.ItemTotal)")

orders.Columns.Add("AverageItemTotal", GetType(Decimal), "Avg(Child.ItemTotal)")

daOrders.Fill(orders)

daOrderDetails.Fill(orderDetails)

mdg1.DataSource = ds

mdg1.DataMember = "Orders"

mdg2.DataSource = ds

mdg2.DataMember = "OrderDetails"

If the table has multiple child tables, you can give the relation name as parameter to the “Child” qualifier.

2.6. DataView

DataView represents a view on the data of a specific version on some selected rows and all columns of a single DataTable. It is defined by the following properties:

1. Table - the DataTable it is viewing;

2. RowFilter - a string representing the filtering criteria based on the content of the rows, such as "OrderID > 10254".

3. RowStateFilter - it's of an enumeration type called DataViewRowState. See section Filtering DataRows with Enumeration DataViewRowState for details about the enumeration. Default value is CurrentRows.

4. Sort - a string representing the sorting column and order, such as "OrderID DESC".

The following constructor takes all properties as parameters:

Dim view As New DataView(table, "OrderID > 10254", "OrderID DESC", _

DataViewRowState.ModifiedOriginal Or DataViewRowState.Deleted)

mDataGrid.DataSource = view

2.7. Filtering DataRows with Enumeration DataViewRowState

Enumeration DataViewRowState is used for two purposes:

1. Representing a filtering criteria based on the RowState property of the rows (DataRowState.Unchanged, Modified, Inserted or Deleted),

2. Specifying the data version to be viewed through a DataView based on the SourceVersion property of the rows (DataRowVersion.Current or Original).

Therefore, this enumeration is a combination of the two enumerations DataRowState and DataRowVersion. It is used by DataTable.Select to select rows of a certain RowState, and also used as DataView's RowStateFilter property to specify both the row filtering rule and the data version to be viewed.

2.8. HasErrors Method of a DataSet & DataRow

A DataRow's HasErrors property will return True if its RowError string property is not empty, or one of its columns has an error:

mds2.Customers(0).RowError = "Something"

mds2.Customers(1).SetColumnError(2, "Other things")

A DataSet's HasErrors property will return True if any of its tables have any rows whose HasErrors property returns True.

2.9. Creating a New DataRow

A DataRow has to belong to a certain DataTable. Its Table property is read-only. Therefore you can not create a separate DataRow and add it into a table later. You have to call a table’s NewRow method to create a new row, whose Table property already points to the table, then add it into the table by calling its Rows property’s Add method. You can not ask one table for a new row, then add it into another table.

2.10. Finding a DataRow by Primary Key

Suppose you have a table with a primary key and you want to locate one record with a primary key, instead of using DataTable.Select method to get back a one-element array, you can use the DataRowCollection.Find method to get back one single row:

DataRow row = ds.Tables(“Customers”).Rows.Find(“FLIU”);

If the table has more than one primary key, you can pass in an array of objects representing the keys.

If it is a strongly typed dataset you can simply say

DataRow row = ds.Customers.FindByCustomerID(“FLIU”);

3. Data Binding

3.1. Binding a Property of a Control to a DataTable Column

If we want text box tbOrderID to display the OrderID column of of a row in table Orders in dataset mds, we should bind the textbox’s Text property to that column:

tbOrderID.DataBindings.Add(“Text”, mds, “Orders.OrderID”)

If we want the BackColor property of the text box to be bound to the BColor column of table Colors:

tbOrderID.DataBindings.Add(“BackColor”, Colors, “BColor”)

3.2. CurrencyManager

All data-awareness controls including Form inherit from class Control, which has a parameterized property BindingContext of type BindingContext, which manages all data-awareness controls that the control contains. This property takes a data source (and a path if the data source has more than one path, e.g. a dataset has multiple tables) as a parameter and returns a BindingManagerBase-derived object – a PropertyManager if the control is single-pathed or a CurrencyManager if multi-pathed, which is used to keep all controls that are bound to the same data source synchronized. The BindingManagerBase can be created before any control has been bound to the data source.

Dim cManager As CurrencyManager = Me.BindingContext(dsMain, "Orders")

One container control like a Form may have multiple data sources, each of which is managed by a BindingManagerBase. A CurrencyManager knows the current record of the data source – e.g. the current data row in a DataTable, and makes sure that all controls that are bound to this data source refers to the same record.

¨ Current, Position & Count property

The CurrencyManager lets you set or get the current record (Current property), its integer index (Position property), and count of records (Count property). If the controls can display multiple records, such as a DataGrid, the Position property reflects the current record (the row you click). If the controls can only display one record, you then need nevigation buttons, which simply increment/decrement the Position property.

Dim currentRow As DataRow = CType(currencyManager.Current, DataRowView).Row

¨ PositionChanged & ItemChanged Event

When Position is changed, a PositionChanged event happens. When the current record is changed, an ItemChanged event happens.

¨ AddNew and RemoveAt

CurrencyManager.AddNew and RemoveAt adds a new record to or removes a record from the data source.

¨ EndCurrentEdit

When you make some change on the control, CurrencyManager does not submit the change to the data source until you change Position or call EndCurrentEdit.

Public Class Form1

Inherits System.Windows.Forms.Form

Dim cManager As CurrencyCManager

Dim strCustomerID As String = "ALFKI"

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'ds is the dataset created on the component tray

cManager = CType(BindingContext(ds, "Orders"), CurrencyCManager)

AddHandler cManager.PositionChanged, AddressOf cManager_PositionChanged

daOrders.SelectCommand.Parameters(0).Value = strCustomerID

daOrders.Fill(ds.Orders)

End Sub

Private Sub DisplayOrdersPosition() 'Generate text such as “order 3 of 7”

textbox1.Text = "Order " & (cManager.Position + 1) & " of " & cManager.Count

End Sub

Private Sub cManager_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs)

DisplayOrdersPosition()

End Sub

Private Sub btnOrdersMovePrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOrdersMovePrevious.Click

cManager.Position -= 1

End Sub

Private Sub btnOrdersMoveNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOrdersMoveNext.Click

cManager.Position += 1

End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

cManager.AddNew()

DisplayOrdersPosition()

End Sub

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

cManager.RemoveAt(cManager.Position)

DisplayOrdersPosition()

End Sub

End Class

3.3. Binding Data Source to Controls on a Child Form

To bind a data source to controls in a child form, pass a reference of the CurrencyManager of this data source to the child form. Then the child form can get a DataView from the CurrencyManager and bind its controls to that DataView.

Public Sub EditDetail(ByVal cm As CurrencyManager)

mDataRowView = CType(cm.Current, DataRowView)

mDataView = mDataRowView.DataView

Me.BindingContext(vueDetail).Position = cm.Position

textBox1.DataBindings.Add("Text", mDataView, "OrderID")

End Sub

3.4. ListControl’s Data Binding

Data binding for a ListControl such as a ComboBox or ListBox is complicated. Four properties needs to be set:

1. DataSource: such as a DataSet;

2. ValueMember: String, the primary key of the source table. Used to look up rows in the table, such as “Employees.EmployeeID”.

3. DisplayMember: String, the column in the source table that you want to display, can be the same as the ValueMember, or a different column, such as “Employees.EmployeeName”;

4. SelectedValue: Object, the value to be provided to the ValueMember i.e. the primary key, to look up the row, such as an Orders.EmployeeID of 1447.

These properties are need in the following example. Suppose the controls on the form are bound to a record in table Orders, and the combo box is used to display column Orders.EmployeeID, which is a foreign key to table Employees. If we want to provide more convenience to user, so that the combo box displays the name of the employee instead of its ID, then we need to perform a SELECT query on table Employees, to acquire a set of EmployeeName with EmployeeID and put them into a dataset. Then we point the DataSource property of the combo box to this dataset, provide a search criteria such as EmployeeID, and ask the combo box to display the corresponding EmployeeName.

3.5. Customizing Data Flow between the Control and its Data Source

The DataBindings property of a control is of type ControlBindingsCollection. Its method Add is called to setup a binding relationship between the control and a data source, as shown in section Binding a Property of a Control to a DataTable Column. Add returns a Binding object, which has two events: Format and Parse. Format events fires when Binding loads data from data source into control, and the Value property of the event returns an Object which is the data being loaded. You can change the format of the data here. Parse event fires when Binding assigns the data in the control back to data source. You can parse the data here.

Private Sub FormatOrderDate(ByVal sender As Object, ByVal cevent As ConvertEventArgs)

If cevent.Value Is DBNull.Value Then

cevent.Value = strNull

Else

cevent.Value = CDate(cevent.Value).ToShortDateString

End If

End Sub

Private Sub ParseOrderDate(ByVal sender As Object, ByVal cevent As ConvertEventArgs)

If CStr(cevent.Value) <> strNull Then

cevent.Value = CDate(cevent.Value)

Else

cevent.Value = DBNull.Value

End If

End Sub

4. DataSet Schema

4.1. By default , minimum schema is filled into dataset by DataAdapter.Fill

A dataset’s schema can contain a set of constraints, including

1. Column-level constraints: ReadOnly, AllowDBNull, MaxLength, etc.;

2. Table-level constraints: UniqueConstraint, PrimaryKey, ForeignKeyConstraint.

If you create a brand-new dataset and immediately call DataAdapter.Fill, only the minimum set of schema – the names and types of the table’s columns are filled into the dataset. This is because retrieving schema from database takes extra time.

This fact is proved by the fact that the following code runs OK. Table “test1” has its first column being primary key, and already has a row with primary key “a”. Now we do a fill and add a new row with the same primary key “a”, the dataset accepts it without a complaint. Moreover, the last line of code obviously shows that the dataset contains a table whose name is “Table”, not the expected “test1”. If you change this to “test1”, there will be no data shown in the datagrid “dg” because there isn’t such a table in the dataset. This may really confuse novices. These facts proves that those schema information are not present in the dataset.

SqlConnection cn = new SqlConnection(strSql);

SqlCommand selectCmd = cn.CreateCommand();

selectCmd.CommandText = "Select * from test1";

SqlDataAdapter da = new SqlDataAdapter();

da.SelectCommand = selectCmd;

da.Fill(mds);

DataRow r = mds.Tables[0].NewRow();

r[0] = "a";

r[1] = "b";

mds.Tables[0].Rows.Add(r);

dg.DataSource = mds;

dg.DataMember = mds.Tables["Table"].TableName;

4.2. How to Create a Dataset with Schema

Normally you would want your dataset have all the constraints in it before you start to use it. Compared with having no constraint in the dataset and submitting whatever data to database and getting rejected at the last step, enforcing data integrity at the dataset can reduce network traffic and improve performance.

There are different ways to create a dataset with schema. You can create a typed dataset class at design time with schema built in, which is called a typed dataset (class), or you can create an empty dataset at run time and add or fill schema information into it.

¨ Create dataset schema at design time

Generating a strongly typed dataset class at design time is the most efficient option, because typed dataset offers compile-time type checking and at run time it already knows its schema.There are two ways to generate a typed dataset:

1. Drag a data adapter onto the design view of the form, then right-click the DataAdapter and choose “Generate DataSet”. A strongly typed dataset class will be generated, using the schema retrieved from database through the data adapter. Using this method, the dataset can only contain one table, because the data adapter is designed to be a pipeline between the dataset and ONE database table.

2. Second, you can use dataset schema designer to create the XML Schema Definition (XSD) file of a dataset, by dragging database tables from the server explorer onto the schema designer, or even by creating elements yourself. Note this is the only way to create a dataset containg more than one table, and the dataset can contain the full set of schema, including ForeignKeyContaints and DataRelations.

¨ Manually add schema information into an empty dataset at run time

This is the most transperent way because you do everything by writing your own code. You basically create DataTables, add columns and their constraints such as primary key into it, then add the tables into the dataset. Then you add foreign key constraints and data relations into the dataset. From performance point of view it is slower then having a typed dataset class, but still generally acceptable for a production-scale product.

¨ Retrieve dataset schema from database at run time

This approach is the slowest and not recommended for production-scale products, because retrieving schema from database takes a lot of time.

You can either retrieve the schema from database separately or together with data. To retrieve schema separately, call DataAdapter.FillSchema. It can be done either before or after Fill. When you call FillSchema, you have a choice to use the original table and column names in the database as the table and column names of the dataset, or to use different names at your choice by providing a mapping of the table and column names. The first parameter of FillSchema is the dataset, and the second is an enumeration indicating whether you want to use original namesin the database (SchemaType.Source), or the TableMappings and ColumnMappings that you have added into the dataset (SchemaType.Map). See section Mapping Table Names in DataAdapter.Fill and Mapping column Names in DataAdapter.Fill for details.

To fill schema together with data, set DataAdapter's MissingSchemaAction property to enumeration MissingSchemaAction.AddWithKey before calling DataAdapter.Fill.

In the following example, the code to retrieve schema from database into dataset has been commented out, so the result will contain no schema.

Dim cn As OleDbConnection = New OleDbConnection("File Name=DataLink.udl")

Dim cmd As OleDbCommand = cn.CreateCommand()

cmd.CommandText = "Select * from Customers where Country = 'Germany'"

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)

'da.MissingSchemaAction = MissingSchemaAction.AddWithKey

Dim ds As DataSet = New DataSet()

'da.FillSchema(ds, SchemaType.Source)

da.Fill(ds)

'da.FillSchema(ds, SchemaType.Source)

Dim col As DataColumn

Dim constraints As String

For Each col In ds.Tables(0).Columns

constraints = "Col Name: " & col.ColumnName & ". AutoIncrement: " & col.AutoIncrement &

" MaxLength: " & col.MaxLength & " AllowDBNull: " & col.AllowDBNull

MessageBox.Show(constraints)

MessageBox.Show("Table Customers's primary key: " & ds.Tables(0).PrimaryKey(0).ColumnName)

4.3. ForeignKeyConstraint vs. DataRelation

A foreign key relationship between two tables in a dataset can be represented by two entities:

1. ForeignKeyConstraint – it corresponds to the concept of constraint in relational database, which guarantees the data integrity when records are inserted, deleted or updated in the child or parent table. Therefore it is part of the schema. It belongs to the table level, and is stored in the Constraints collection of the child table. Through its AcceptRejectRule, DeleteRule and UpdateRule properties, it instructs the database what to do on the foreign key when the primary key is changed, when a row containing the primary key is deleted or its AcceptChanges/RejectChanges method called. For example, if you change a OrderID in table [Order Details] that does not exist in table Orders, you will be warned for violation.

2. DataRelation – it is not a concept of a relational database. It belongs to the dataset-level, and is used by the dataset to navigate between tables through the parent-child relationship. Therefore it is not part of the schema. It is stored in the Relations collection of the dataset.

A DataRelation contains a ForeignKeyConstraint. When you create a DataRelation, by default the corresponding ForeignKeyConstraint is automatically created in the DataTable.Constraints collection (if there is already one it will be used). On the other hand, when you create a ForeignKeyConstraint, the corresponding DataRelation is not created. You can pass false as the third parameter to the DataRelation constructor to instruct it not to create corresponding ForeignKeyConstraint.

The constructor of a ForeignKeyConstraint takes two columns as parameters: a parent column (the column that acts as a primary key) and a child column (the one which acts as foreign key). The constructor of a DataRelation takes a relation name and the same two columns.

4.4. Turning Off Contraints before Fill

We normally do not need schema/constraints when retrieving data from database into dataset, because the database is has all the needed constraints already in place, and the data in the database already conform to these constraints. On the other hand, we normally need those constraints in the dataset when we change data in it.

We can solve this dilemma by setting the DataSet.EnforceConstraints property to false right before calling DataAdapter.Fill, and set it back to true right after the call. These way when data is filled into the dataset all the constraints does not function but when we update data they do function.

4.5. Navigating Between Tables with DataRelations

With a dataset with full schema, we can use DataRelations to navigate between rows in child and parent tables. To navigate from one row to another, call DataRow’s GetChildRows and GetParentRow method passing a DataRelation as a parameter.

In the above example, to get all records in the association table “Order Details” with OrderID 10249, you say

Dim row As DataRow = ds.Tables("Orders").Rows.Find(10249)

Dim orderDetailsRows As DataRow() = row.GetChildRows(ds.Relations("Orders_OrderDetails"), DataRowVersion.Current);

DataGrid knows to invoke these methods and relations of the dataset that is bound to it, so that user can get a row’s child rows conveniently by clicking the “unfold” buttons at the left of a parent row.

When using a strongly typed dataset, the generated code provides you with a lot more convenience – you do not need to know the DataRelation to be able to navigate from a parent table to a child table or the other way around. For example, in the above example, suppose the typed dataset is “MyTypedDS”, all you need to say is

Dim ordersRows As MyTypedDS.OrdersRow() = ds.OrderDetails.FindByCustomerID(“10249”).GetOrdersRows();

In the above example, table “OrderDetails” already knows that “OrderID” is the primary key, so you can directly pass an OrderID expecting to get back only one row.

4.6. Do Not Join Database Tables in DataSet

When we want to join tables in the database, the easiest way is to use SQL join queries to get the results from database directly, and store the returned result in a table in a dataset. For example, the following SQL query joins “Orders” table and “Products” table through an association table “Order Details”:

SELECT O.OrderID, O.CustomerID, O.EmployeeID, P.ProductID, P.ProductName

FROM Orders O, [Order Details] OD, Products P

WHERE O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID AND O.OrderID <= 10250

But this approach has two drawbacks:

1. It produces redundant data, and thus is slower;

2. If you change anything in the joined table, the DataAdapter can not guarantee to update the database correctly, because it is designed to work with tables that parallel the tables in the database.

Therefore, the better way to do a join in ADO.NET is to split the joining select query into multiple queries, each of which only returns the columns of one database table, so that they all parallel the database tables, and use DataRelations to navigate between tables. Especially when you use strongly typed dataset, navigating between tables requires only one method call.

Working code is listed below:

'Create DataAdapters

Dim strQuery As String

Dim daOrders As OleDbDataAdapter = New OleDbDataAdapter()

strQuery = "SELECT O.OrderID, O.CustomerID, O.EmployeeID " & _

"FROM Orders O " & _

"WHERE O.OrderID <= 10250"

daOrders.SelectCommand = New OleDbCommand(strQuery, mcn)

Dim daOrderDetails As OleDbDataAdapter = New OleDbDataAdapter()

strQuery = "SELECT OD.OrderID, OD.ProductID, OD.Quantity " & _

"FROM [Order Details] OD " & _

"WHERE OD.OrderID <= 10250"

daOrderDetails.SelectCommand = New OleDbCommand(strQuery, mcn)

Dim daProducts As OleDbDataAdapter = New OleDbDataAdapter()

strQuery = "SELECT DISTINCT P.ProductID, P.ProductName " & _

"FROM Orders O, [Order Details] OD, Products P " & _

"WHERE O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID AND O.OrderID <= 10250"

daProducts.SelectCommand = New OleDbCommand(strQuery, mcn)

'Create DataSet and its tables

Dim ds As DataSet = New DataSet()

Dim orders As DataTable = ds.Tables.Add("Orders")

Dim orderDetails As DataTable = ds.Tables.Add("OrderDetails")

Dim products As DataTable = ds.Tables.Add("Products")

'Fill tables

daOrders.Fill(orders)

daProducts.Fill(products)

daOrderDetails.Fill(orderDetails)

'Add DataRelations

ds.Relations.Add("Orders_OrderDetails", orders.Columns("OrderID"), orderDetails.Columns("OrderID"))

ds.Relations.Add("Products_OrderDetails", products.Columns("ProductID"), orderDetails.Columns("ProductID"))

'Display tables

mdg1.DataSource = orders

mdg2.DataSource = orderDetails

mdg3.DataSource = products

Keyword DISTINCT above is used because otherwise the result will contain redundant rows that violate unique constraint.

4.7. Schema Table

After you create a DataAdapter with a select command, all schema information such as base catalog (Northwind), base table name (Order Details), column names, column size, data type, allow null, primary key etc. can be retrieved through a schema table:

Dim strConn, strSQL As String

strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _

"Initial Catalog=Northwind;Trusted_Connection=Yes;"

strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " & _

"FROM [Order Details] WHERE OrderID = 10503 " & _

"ORDER BY ProductID"

Dim cn As New OleDbConnection(strConn)

Dim cmd As New OleDbCommand(strSQL, cn)

cn.Open()

Dim reader As OleDbDataReader

reader = cmd.ExecuteReader(CommandBehavior.KeyInfo Or CommandBehavior.SchemaOnly)

Dim schemaTable As DataTable = reader.GetSchemaTable

reader.Close()

cn.Close()

mdg.DataSource = schemaTable

4.8. Mapping Table Names in DataAdapter.Fill

¨ Map table name

As stated before, after passing an empty dataset to a data adapter’s Fill method, the dataset only contains column names and types, but not table name. This is because data adapter uses a SELECT SQL command to retrieve the data, which might be a join of multiple tables. Data adapter has not way to decide the table name of the result set. By default, a table named “Table” is created in the dataset and the result of the SELECT is placed into this table. Therefore, if your SELECT command is “SELECT * FROM Employees WHERE …”, do not ask the dataset for a table named “Employees”. There is no such table. Instead, ask for table “Table”:

Dim daOrderDetails As OleDbDataAdapter ...

Dim mdg As DataGrid ...

Dim ds As DataSet = New DataSet()

daOrderDetails.Fill(ds)

mdg.DataSource = ds.Tables("Table")

To change this default behaviour , you have to tell DataAdapter how to name the table in the dataset. There are two ways to do this.

First, you can create an empty table in the dataset yourself with the desired name, then instruct the DataAdapter.Fill method to fill data into this existing table:

Dim daOrderDetails As OleDbDataAdapter ...

Dim mdg As DataGrid ...

Dim ds As DataSet = New DataSet()

ds.Tables.Add("OrderDetails")

daOrderDetails.Fill(ds.Tables("OrderDetails"))

mdg.DataSource = ds.Tables("OrderDetails")

Or, if you want Fill to create the new table with your desired name, you should create a DataTableMapping object, store it in the TableMappings collection of the data adpater, and pass to Fill as the second parameter the name of the table mapping.

As said in MSDN, when a new DataTableMapping object is added into the DataAdapter.TableMappings collection, the Add method takes two strings: the name of the database table name and the desired dataset table name. The database table name is also used as the mapping name, which is passed as the second parameter of Fill.

Dim ds As DataSet = New DataSet()

daOrderDetails.TableMappings.Add("OrderDetails", "MyTable")

daOrderDetails.Fill(ds, "OrderDetails ")

mdg.DataSource = ds.Tables("MyTable")

However, in fact, the first string is MERELY used as the mapping name. As said before, the result set retrieved from the database does not contain the table name. Therefore, instead of saying “create a tabled named MyTable, and fill it with data retrieved from table OrderDetails in the database”, the table mapping is actually saying “create a tabled named MyTable, and fill it with data retrieved from one or several tables in the database that I don’t know”. To prove this claim, the following code also retrieves the data from the database table OrderDetails as before:

Dim ds As DataSet = New DataSet()

daOrderDetails.TableMappings.Add("Anything", "MyTable")

daOrderDetails.Fill(ds, " Anything")

mdg.DataSource = ds.Tables("MyTable")

¨ Use one data adapter and different table mappings

You can call Fill of the same data adapter with two DataTableMappings, to fill data into two tables in the same dataset:

Dim ds As DataSet = New DataSet()

daOrderDetails.TableMappings.Add("Mapping1", "OrderDetails1")

daOrderDetails.TableMappings.Add("Mapping2", "OrderDetails2")

daOrderDetails.Fill(ds, "Mapping1")

mdg2.DataSource = ds.Tables("OrderDetails1")

'After a while ...

daOrderDetails.Fill(ds, "Mapping2")

mdg2.DataSource = ds.Tables("OrderDetails2")

4.9. Mapping column names in DataAdapter.Fill

As shown in previous section Mapping Table Names in DataAdapter.Fill, DataAdapter.TableMappings.Add is used to add table name mapping. It returns a DataTableMapping object, which has a ColumnMappings property, which can be used to map original column names to those in the dataset. Because column names are among the minimum schema set which is always retrieved from the database, you don’t need to use column mappings unless you specifically want to use an alternative column name.

Suppose you only want to change the name of one column and leave others unchanged. You do not need to provide column mappings for all the columns. DataAdapter has a MissingMappingAction property, which has three enumeration values: PassThrough (default), Ignore and Error:

1. When it is PassThrough and the Fill result contains a column that is not in the ColumnMappings collection, it will let the new column name pass through to the dataset.

2. If it is Ignore, the result column will be discarded.

3. If it is Error, an exception will be generated. Therefore, if you want to use the same column name as the database for all columns, you don't need to populate the ColumnMappings collection at all.

In the following example, the DataSet will only contain the first column "CustomerID", although the query actually returns three columns:

Dim cn As OleDbConnection = New OleDbConnection("File Name=DataLink.udl")

Dim cmd As OleDbCommand = cn.CreateCommand()

cmd.CommandText = "Select CustomerID, CompanyName, ContactName from Customers where Country = 'Germany'"

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)

Dim tableMapping As DataTableMapping = da.TableMappings.Add("Table", "CustomerNameAndCompany")

tableMapping.ColumnMappings.Add("CustomerID", "CustomerCode")

da.MissingMappingAction = MissingMappingAction.PassThrough

Dim ds As DataSet = New DataSet()

da.Fill(ds)

mdg.DataSource = ds.Tables("CustomerNameAndCompany")

4.10. Strongly Typed DataSet

A strongly typed dataset is represented by a normal class, which inherits from dataset and also have methods and properties that represent the schema of a set of specific tables. It can be generated from a XML Schema Definition file (.xsd). It can also be generated by Visual Studio .NET development environment directly from a DataAdapter. The development environment actually does the following things under the hood:

1. Use DataAdapter.FillSchema to fill the schema into an empty dataset;

2. Use DataSet.WriteXmlSchema to write the schema into a file and add this file into the project;

3. Use XML Schema Definition tool XSD.exe to generate a class file, and add this file into the project;

¨ Generate a XML Schema Definition (XSD) file for a typed DataSet

To generate a XSD file for a typed dataset containing several tables and full set of schema programmatically:

Dim daOrders As OleDbDataAdapter = New OleDbDataAdapter()

daOrders.SelectCommand = New OleDbCommand("SELECT OrderID, CustomerID, EmployeeID, ShipCountry FROM Orders")

daOrders.SelectCommand.Connection = mcn

Dim daOrderDetails As OleDbDataAdapter = New OleDbDataAdapter()

daOrderDetails.SelectCommand = New OleDbCommand("SELECT OrderID, ProductID, UnitPrice, Quantity FROM [Order Details]")

daOrderDetails.SelectCommand.Connection = mcn

Dim ds As DataSet = New DataSet()

Dim orders As DataTable = ds.Tables.Add("Orders")

Dim orderDetails As DataTable = ds.Tables.Add("OrderDetails")

daOrders.FillSchema(orders, SchemaType.Source)

daOrderDetails.FillSchema(orderDetails, SchemaType.Source)

ds.Relations.Add("Orders_OrderDetails", orders.Columns("OrderID"), orderDetails.Columns("OrderID"))

ds.WriteXmlSchema("DSOrder_OrderDetails.XSD")

To generate a XSD file at design time, right-click the project icon, choose “Add new item”, then choose “DataSet”. In the design pane create a DataSet or table by “Add new group”, then choose “Add new element” to add new columns. Or you can simply drag a table or a stored procedure from the server explorer into the schema design pane.

If the corresponding tables and the constraints and relations already exist in the database, which is true in most of the cases, you only need to drap the table in Server Explorer into the design pane.

¨ Generate a dataset class from the XSD file

To generate a class file from a XSD file on command line, enter the following command:

xsd ...\ DSOrder_OrderDetails.XSD /d /l:VB

By default, without parameter “l:VB”, the tool generates C# class files. "/d" is a short term for "/dataset", which means generating sub-classed DataSet for this schema. You may have to rename the class because it is named by default “NewDataSet”.

To generate a class file from a XSD file in Visual Studio .NET, add this XSD file into project, double-click it to open its designer pane, and right-click and choose “Generate DataSet”. Note: the namespace of the generated dataset is determined by the “RootNamespace” setting in the project file (csproj).

¨ Typed DataSet simplifies coding and enables compile-time checking

Then, you can add this class file into the project, and use it as a normal class.

Dim ds As DSOrders_OrderDetails = New DSOrders_OrderDetails()

daOrders.Fill(ds.Orders)

daOrderDetails.Fill(ds.OrderDetails)

mdg1.DataSource = ds

mdg1.DataMember = "Orders"

mdg2.DataSource = ds

mdg2.DataMember = "OrderDetails"

The following code shows how strongly typed database can make coding easier and enables compile-time checking:

'Untyped DataSet: Adding a new row and editing a cell

Dim row1 As DataRow = ds.Tables("Orders").NewRow

row1("OrderID") = 10246

row1("CustomerID") = "VINET"

row1("EmployeeID") = 5

row1("ShipCountry") = "P.R.China"

ds.Tables("Orders").Rows.Add(row1)

'Strongly typed DataSet: Adding a new row and editing a cell

Dim row2 As DS_Orders_OrderDetails.OrdersRow = ds.Orders.NewOrdersRow

row2.OrderID = 10247

row2.CustomerID = "VINET"

row2.EmployeeID = 5

row2.ShipCountry = "P.R.China"

ds.Orders.AddOrdersRow(row2)

'Untyped DataSet: Finding a row

row1 = ds.Tables("Orders").Rows.Find(10246)

'Strongly typed DataSet: Finding a row

row2 = ds.Orders.FindByOrderID(10247)

¨ Typed DataSet simplifies DataRelation navigation

To see how typed dataset greatly simplifies navigating through data tables, see section "Navigating Between Tables with DataRelations”.

4.11. Challenges Brought By Using Strongly Typed DataSet

I once ran into a bug with caused me quite some effort to find. I generated a strongly typed dataset. I wrote my own data adapter code for it using my own SQL queries. Initially all worked fine.

Then I decided to add an extra table into the dataset and an extra column in an existing table, which is a foreign key to the new table. I re-generated the typed dataset, and added code for the new data adapter for the new table. But I forgot to add the extra column in the SELECT command of the existing table – the command text used to be

SELECT OrderID, ProductID, Quantity

FROM Orders

and now should have been changed to

SELECT OrderID, ProductID, Quantity, CustomerID

FROM Orders

When I ran the code, exception was thrown saying “rows violating non-null, unique or foreign key constraints”.

This was because of the typed nature of the typed dataset. If we are using a untyped dataset, the dataset becomes whatever the SELECT command got from the database. If we are using a typed dataset, however, the SELECT command must conform to the dataset schema.

4.12. How are Column Constraints Represented in Strongly Typed DataSet

How database constraints are stored in the typed dataset is shown below:

1) Primary key constraints are stored;

2) Foreign key relationships are stored;

3) Data type is stored;

4) Length is not stored, because it is represented by the data type;

5) AllowNull constraint is marked as minOccurs attribute. If it is 0 then null is allowed. If absent null is not allowed.

6) Identity constraint is marked by ReadOnly attribute;

7) Unique is not stored, because only the database can check this;

8) Default value is not stored, because the database knows.

5. Updating DataBase

5.1. RowState, AcceptChanges and RejectChanges

DataSet, DataTable and DataRow all have properties called HasError and RowState, and methods called AcceptChanges and RejectChanges.

RowState property is used to submit changes (modifies, inserts or deletes) back to the database. The code which does the submitting job (can be your own code or DataAdapter.Update) will go through all rows and check its RowState property. If the property is Added, Modified or Deleted, the code will go get the corresponding columns of this row for the parameter collection of the corresponding command, and call its ExecuteNonQuery to submit the change.

If the number returned by ExecuteNonQuery (indicating how many rows in database has been affected) is 1, it means the operation is successful. The AcceptChanges method of the row in the dataset will be called. Then, the RowState property of the row (or all changed rows that a table or dataset contains) will be set to Unchanged if it was Added or Modified. If the RowState used to be Deleted, the row will be removed.

When the RejectChanges method of DataSet, DataTable or DataRow is called, if its RowState property is Modified or Deleted, it will be reset to Unchanged, and the row will go back to its previous state. If the property is Added, the row will be removed from the dataset.

5.2. Submitting Changes with Ad hoc Queries or Stored Procedures

¨ Parameters

If you want to use the same command text to submit the changes in multiple rows, you need to use parameters in the command text, and provide a mapping between the parameters and the corresponding columns. You may also need to specify which version of the column to use, e.g. the current version or the original version.

¨ Submitting changes using ad hoc queries

Property SourceVersion's default value is DataRowVersion.Current, so you only need to set it if you want it to be DataRowVersion.Original.

Note: the SQL queries wrapped by the commands are a simplified version, which does not accommadate NULL scenarios. See section Accommadating NULL Values for details.

Dim param As OleDbParameter

' SelectCommand

mda.SelectCommand = New OleDbCommand("SELECT OrderID, ProductID, Quantity FROM [Order Details] WHERE OrderID < 10254")

mda.SelectCommand.Connection = mcn

' UpdateCommand

Dim cmd1 As OleDbCommand = mcn.CreateCommand()

cmd1.CommandText = "UPDATE [Order Details] SET OrderID = ?, ProductID = ?, Quantity = ? " & _

"WHERE OrderID = ? AND ProductID = ?"

cmd1.Parameters.Add("OrderID_New", OleDbType.Integer, 0, "OrderID")

cmd1.Parameters.Add("ProductID_New", OleDbType.VarChar, 5, "ProductID")

cmd1.Parameters.Add("Quantity_New", OleDbType.VarChar, 15, "Quantity")

param = cmd1.Parameters.Add("OrderID_Orig", OleDbType.Integer, 0, "OrderID")

param.SourceVersion = DataRowVersion.Original

param = cmd1.Parameters.Add("ProductID_Orig", OleDbType.VarChar, 5, "ProductID")

param.SourceVersion = DataRowVersion.Original

mda.UpdateCommand = cmd1

' Here we need to specify the SourceVersion to be Original, because the current version is the

' modified version, and we need the original version to look up in the database.

' DeleteCommand

Dim cmd2 As OleDbCommand = mcn.CreateCommand()

cmd2.CommandText = "DELETE FROM [Order Details] WHERE OrderID = ? AND ProductID = ?"

cmd2.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

cmd2.Parameters.Add("ProductID", OleDbType.VarChar, 5, "ProductID")

mda.DeleteCommand = cmd2

' InsertCommand

Dim cmd3 As OleDbCommand = mcn.CreateCommand()

cmd3.CommandText = "INSERT INTO [Order Details] (OrderID, ProductID, Quantity) VALUES(?, ?, ?)"

cmd3.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

cmd3.Parameters.Add("ProductID", OleDbType.VarChar, 5, "ProductID")

cmd3.Parameters.Add("Quantity", OleDbType.VarChar, 15, "Quantity")

mda.InsertCommand = cmd3

mda.Fill(mds)

mdg.DataSource = mds

mdg.DataMember = "Table"

Note: because here we do not use named parameters like with stored procedures, we can not reuse parameters even if they are the same. Suppose a query needs 6 parameters and two of them are the same, you still have to provide 6 parameters instead of 5.

¨ Submitting changes using stored procedure

The use of parameters in a stored procedure is very similar to the commands. The difference is that parameters are named in stored procedures. The four stored procedures are:

ALTER PROCEDURE dbo.SelectOrderDetails

SELECT OrderID, ProductID, Quantity

FROM [Order Details]

WHERE OrderID < 10254

ALTER PROCEDURE dbo.UpdateOrderDetails

(

@OrderID_New int,

@ProductID_New int,

@Quantity_New smallint,

@OrderID_Orig int,

@ProductID_Orig int

)

UPDATE [Order Details]

SET OrderID = @OrderID_New, ProductID = @ProductID_New, Quantity = @Quantity_New

WHERE OrderID = @OrderID_Orig AND ProductID = @ProductID_Orig

ALTER PROCEDURE dbo.InsertOrderDetails

(

@OrderID int,

@ProductID int,

@Quantity smallint

)

INSERT INTO [Order Details] (OrderID, ProductID, Quantity) VALUES(@OrderID, @ProductID, @Quantity)

ALTER PROCEDURE dbo.DeleteOrderDetails

(

@OrderID int,

@ProductID int

)

DELETE FROM [Order Details]

WHERE OrderID = @OrderID AND ProductID = @ProductID

There is little change to the code to use stored procedure: simply replace the query string with the name of the stored procedure, and change the CommandType property of the command from default value CommandType.Text to CommandType.StoredProcedure:

Dim param As OleDbParameter

' SelectCommand

Dim cmd1 As OleDbCommand = mcn.CreateCommand()

cmd1.CommandText = "SelectOrderDetails"

cmd1.CommandType = CommandType.StoredProcedure

mda.SelectCommand = cmd1

' UpdateCommand

Dim cmd2 As OleDbCommand = mcn.CreateCommand()

cmd2.CommandText = "UpdateOrderDetails"

cmd2.CommandType = CommandType.StoredProcedure

cmd2.Parameters.Add("OrderID_New", OleDbType.Integer, 0, "OrderID")

cmd2.Parameters.Add("ProductID_New", OleDbType.VarChar, 0, "ProductID")

cmd2.Parameters.Add("Quantity_New", OleDbType.VarChar, 0, "Quantity")

param = cmd2.Parameters.Add("OrderID_Orig", OleDbType.Integer, 0, "OrderID")

param.SourceVersion = DataRowVersion.Original

param = cmd2.Parameters.Add("ProductID_Orig", OleDbType.VarChar, 0, "ProductID")

param.SourceVersion = DataRowVersion.Original

mda.UpdateCommand = cmd2

' DeleteCommand

Dim cmd3 As OleDbCommand = mcn.CreateCommand()

cmd3.CommandText = "DeleteOrderDetails"

cmd3.CommandType = CommandType.StoredProcedure

cmd3.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

cmd3.Parameters.Add("ProductID", OleDbType.VarChar, 0, "ProductID")

mda.DeleteCommand = cmd3

' InsertCommand

Dim cmd4 As OleDbCommand = mcn.CreateCommand()

cmd4.CommandText = "InsertOrderDetails"

cmd4.CommandType = CommandType.StoredProcedure

cmd4.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

cmd4.Parameters.Add("ProductID", OleDbType.VarChar, 0, "ProductID")

cmd4.Parameters.Add("Quantity", OleDbType.VarChar, 0, "Quantity")

mda.InsertCommand = cmd4

mda.Fill(mds)

mdg.DataSource = mds

mdg.DataMember = "Table"

Note: because the parameters in the stored procedures are named, you can reuse parameters if they are the same – suppose a query needs 6 parameters and two of them are the same, you only need to provide 5 parameters.

5.3. Accommadating NULL Values When Updating

The SQL queries in section Submitting changes using your own commands are a simplified version, which does not accommadate NULL scenarios. For example, the updating command uses the following SQL query:

UPDATE [Order Details] SET ... WHERE ... AND ProductName = ?

If the passed parameter is NULL and the ProductName column of the a row in database is also NULL, we regard it as a match. However, the above query will become

UPDATE [Order Details] SET ... WHERE ... AND ProductName = NULL

because database can not compare NULL values with "=" operator, it does not think it is a match. Database can only check a variable’s nullness with "IS NULL". Therefore, to accommadate NULL value for column ProductName, the comparison of ProductName should become

UPDATE [Order Details] SET ... WHERE ... AND (ProductName = ? OR ((ProductName IS NULL) AND (? IS NULL))

5.4. Concurrency Control with Time Stamp

When you are submitting a change using the SQL UPDATE command, if the WHERE clause only includes the primary key column(s), then the change submitted by you will simply overwrite all changes submitted after you retrieved the original data from database.

To prevent this, you can check in the WHERE clause of your SQL query whether the original version of all the columns are equal to the current values in the database. Then, if some one updated any column after your retrieval, your update attempt will fail and DataAdapter will throw a DBConcurrencyException. But this approach has drawbacks. If there are a lot of columns in the table, or especially if one column is binary large object like pictures, comparing all columns will be awkward or even unacceptable.

In such a case, a time stamp column can be used. The value of the time stamp column is changed automatically by the database every time the row is modified. Therefore, if you check both the original version of the primary key column(s) and the time stamp column in the WHERE clause, you can guarantee that your update will not overwrite others.

Adding a time stamp column into a SQL Server database is the same as adding any other column: simply specify the type to be "timestamp", and the length and AllowNull property of the column will be automatically set by the database. However, note that the OleDbType of the corresponding parameter in the update command's parameter collection should be Binary.

Because the value of the time stamp column is automatically generated by the database, after you submit a change or insert a new row, the time stamp column in the database has a new value, while that in your dataset is still the old value (if it is an update) or null (if it is an insert). If you modify that row again and try to submit, you will fail, because the time stamp value in your row is no longer the same as the database. Therefore, you have to retrieve the new time stamp value from the database every time you do an update or insert.

Refer to section Refreshing DataSet After Submitting Changes for code example of using time stamping.

5.5. Refreshing DataSet After Submitting Changes

For normal columns, you don't need to retrieve data back after submitting changes. Only those special columns that are modified automatically by the database need to be retrieved back after submitting, such as auto-increment or time stamp columns. Otherwise your next submit may fail.

The command text generated by the “Data Adapter Configuration Wizard” always refreshes all columns after submitting:

UPDATE [Order Details] SET OrderID = ?, ProductID = ?, UnitPrice = ?, Quantity = ?, Discount = ?

WHERE (OrderID = ?) AND (ProductID = ?) AND (Discount = ?) AND ("Quantity = ?)

AND (TStamp = ? OR ? IS NULL AND TStamp IS NULL) AND (UnitPrice = ?);

SELECT OrderID, ProductID, UnitPrice, Quantity, Discount, TStamp

FROM [Order Details] WHERE (OrderID = ?) AND (ProductID = ?)

There are two ways to retrieve data back after submitting:

1. Using batch queries – as you have just seen above. You must have a second SELECT query that returns a record containing the column(s) that you want to retrieve in your UPDATE and INSERT command. The problem is: not all databases support batch queries. SQL Server supports it, but Oracle and Access doesn't.

2. Using output parameters - you must use stored procedures for your UPDATE and INSERT command, and the stored procedure must have output parameters returning the value of the column that you want to refresh.

After the UPDATE or INSERT command returns, both the first row in the returned result set and the stored procedure’s output parameter may contain the refreshed values. The command can be configured to use just one, or use one and if fails use another. This behaviour is controlled by the command's UpdatedRowSource property, which can be UpdateRowSource.Both (default value), FirstReturnedRecord, OutputParameters or None. When it is None, DataAdapter will simply not refresh the dataset. It can save a very little bit of time.

¨ Using batch queries in ad hoc queries

' The following example is based on Northwind table [Order Details], with an added

' time stamp column called "TStamp"

Dim param As OleDbParameter

mda.SelectCommand = New OleDbCommand("SELECT OrderID, ProductID, Quantity, TStamp FROM [Order Details] " & _

"WHERE OrderID < 10254")

mda.SelectCommand.Connection = mcn

' UpdateCommand

Dim updateCmd As OleDbCommand = mcn.CreateCommand()

updateCmd.CommandText = "UPDATE [Order Details] SET OrderID = ?, ProductID = ?, Quantity = ? " & _

"WHERE OrderID = ? AND ProductID = ? AND TStamp = ?; " & _

"SELECT TStamp FROM [Order Details] WHERE OrderID = ? AND ProductID = ?"

updateCmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord

updateCmd.Parameters.Add("OrderID_New", OleDbType.Integer, 0, "OrderID")

updateCmd.Parameters.Add("ProductID_New", OleDbType.VarChar, 5, "ProductID")

updateCmd.Parameters.Add("Quantity_New", OleDbType.VarChar, 15, "Quantity")

param = updateCmd.Parameters.Add("OrderID_Orig", OleDbType.Integer, 0, "OrderID")

param.SourceVersion = DataRowVersion.Original

param = updateCmd.Parameters.Add("ProductID_Orig", OleDbType.VarChar, 5, "ProductID")

param.SourceVersion = DataRowVersion.Original

param = updateCmd.Parameters.Add("TimeStamp_Orig", OleDbType.Binary, 8, "TStamp")

param.SourceVersion = DataRowVersion.Original

updateCmd.Parameters.Add("OrderID_New", OleDbType.Integer, 0, "OrderID")

updateCmd.Parameters.Add("ProductID_New", OleDbType.VarChar, 5, "ProductID")

mda.UpdateCommand = updateCmd

' DeleteCommand

Dim deleteCmd As OleDbCommand = mcn.CreateCommand()

deleteCmd.CommandText = "DELETE FROM [Order Details] WHERE OrderID = ? AND ProductID = ?"

deleteCmd.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

deleteCmd.Parameters.Add("ProductID", OleDbType.VarChar, 5, "ProductID")

mda.DeleteCommand = deleteCmd

' InsertCommand

Dim insertCmd As OleDbCommand = mcn.CreateCommand()

insertCmd.CommandText = "INSERT INTO [Order Details] (OrderID, ProductID, Quantity) VALUES(?, ?, ?); " & _

"SELECT TStamp FROM [Order Details] WHERE OrderID = ? AND ProductID = ?"

insertCmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord

insertCmd.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

insertCmd.Parameters.Add("ProductID", OleDbType.VarChar, 5, "ProductID")

insertCmd.Parameters.Add("Quantity", OleDbType.VarChar, 15, "Quantity")

insertCmd.Parameters.Add("OrderID_New", OleDbType.Integer, 0, "OrderID")

insertCmd.Parameters.Add("ProductID_New", OleDbType.VarChar, 5, "ProductID")

mda.InsertCommand = insertCmd

mda.Fill(mds)

mdg.DataSource = mds

mdg.DataMember = "Table"

¨ Using batch queries in stored procedures

Database Northwind has the following stored procedures for table [Order Details]:

(1)–––––––––––––––––––––––––––––––––––––––––––––––

ALTER PROCEDURE dbo.SelectOrderDetails

SELECT OrderID, ProductID, Quantity, TStamp FROM [Order Details] WHERE OrderID < 10254

(2) –––––––––––––––––––––––––––––––––––––––––––––––

ALTER PROCEDURE dbo.UpdateOrderDetails

(

@OrderID_New int,

@ProductID_New int,

@Quantity_New smallint,

@OrderID_Orig int,

@ProductID_Orig int,

@TStamp timestamp

)

UPDATE [Order Details]

SET OrderID = @OrderID_New, ProductID = @ProductID_New, Quantity = @Quantity_New

WHERE OrderID = @OrderID_Orig AND ProductID = @ProductID_Orig AND TStamp = @TStamp;

IF @@ROWCOUNT = 1

SELECT TStamp FROM [Order Details] WHERE OrderID = @OrderID_New and ProductID = @ProductID_New

(3) –––––––––––––––––––––––––––––––––––––––––––––––

ALTER PROCEDURE dbo.InsertOrderDetails

(

@OrderID int,

@ProductID int,

@Quantity smallint

)

INSERT INTO [Order Details] (OrderID, ProductID, Quantity) VALUES(@OrderID, @ProductID, @Quantity);

SELECT TStamp FROM [Order Details] WHERE OrderID = @OrderID and ProductID = @ProductID

(4) –––––––––––––––––––––––––––––––––––––––––––––––

ALTER PROCEDURE dbo.DeleteOrderDetails

(

@OrderID int,

@ProductID int

)

DELETE FROM [Order Details] WHERE OrderID = @OrderID AND ProductID = @ProductID

The code is listed as follow:

Dim param As OleDbParameter

' SelectCommand

Dim selectCmd As OleDbCommand = mcn.CreateCommand()

selectCmd.CommandText = "SelectOrderDetails"

selectCmd.CommandType = CommandType.StoredProcedure

mda.SelectCommand = selectCmd

' UpdateCommand

Dim updateCmd As OleDbCommand = mcn.CreateCommand()

updateCmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord

updateCmd.CommandText = "UpdateOrderDetails"

updateCmd.CommandType = CommandType.StoredProcedure

updateCmd.Parameters.Add("OrderID_New", OleDbType.Integer, 0, "OrderID")

updateCmd.Parameters.Add("ProductID_New", OleDbType.VarChar, 0, "ProductID")

updateCmd.Parameters.Add("Quantity_New", OleDbType.VarChar, 0, "Quantity")

param = updateCmd.Parameters.Add("OrderID_Orig", OleDbType.Integer, 0, "OrderID")

param.SourceVersion = DataRowVersion.Original

param = updateCmd.Parameters.Add("ProductID_Orig", OleDbType.VarChar, 0, "ProductID")

param.SourceVersion = DataRowVersion.Original

param = updateCmd.Parameters.Add("TimeStamp_Orig", OleDbType.Binary, 8, "TStamp")

param.SourceVersion = DataRowVersion.Original

mda.UpdateCommand = updateCmd

' DeleteCommand

Dim deleteCmd As OleDbCommand = mcn.CreateCommand()

deleteCmd.CommandText = "DeleteOrderDetails"

deleteCmd.CommandType = CommandType.StoredProcedure

deleteCmd.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

deleteCmd.Parameters.Add("ProductID", OleDbType.VarChar, 0, "ProductID")

mda.DeleteCommand = deleteCmd

' InsertCommand

Dim insertCmd As OleDbCommand = mcn.CreateCommand()

insertCmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord

insertCmd.CommandText = "InsertOrderDetails"

insertCmd.CommandType = CommandType.StoredProcedure

insertCmd.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

insertCmd.Parameters.Add("ProductID", OleDbType.VarChar, 0, "ProductID")

insertCmd.Parameters.Add("Quantity", OleDbType.VarChar, 0, "Quantity")

mda.InsertCommand = insertCmd

mda.Fill(mds)

mdg.DataSource = mds

mdg.DataMember = "Table"

Note that in the UPDATE command the time stamp parameter is used for input only, and in the INSERT command there is no time stamp parameter.

¨ Using stored procedure output parameters

Suppose database Northwind has the following stored procedures for table [Order Details]:

(1) –––––––––––––––––––––––––––––––––––––––––––––––

ALTER PROCEDURE dbo.SelectOrderDetails

SELECT OrderID, ProductID, Quantity, TStamp FROM [Order Details] WHERE OrderID < 10254

(2) –––––––––––––––––––––––––––––––––––––––––––––––

ALTER PROCEDURE dbo.UpdateOrderDetails

(

@OrderID_New int,

@ProductID_New int,

@Quantity_New smallint,

@OrderID_Orig int,

@ProductID_Orig int,

@TStamp timestamp OUTPUT

)

UPDATE [Order Details]

SET OrderID = @OrderID_New, ProductID = @ProductID_New, Quantity = @Quantity_New

WHERE OrderID = @OrderID_Orig AND ProductID = @ProductID_Orig AND TStamp = @TStamp;

IF @@ROWCOUNT = 1

SELECT @TStamp = TStamp FROM [Order Details] WHERE OrderID = @OrderID_New and ProductID = @ProductID_New

(3) –––––––––––––––––––––––––––––––––––––––––––––––

ALTER PROCEDURE dbo.InsertOrderDetails

(

@OrderID int,

@ProductID int,

@Quantity smallint,

@TStamp timestamp OUTPUT

)

INSERT INTO [Order Details] (OrderID, ProductID, Quantity) VALUES(@OrderID, @ProductID, @Quantity);

SELECT @TStamp = TStamp FROM [Order Details] WHERE OrderID = @OrderID and ProductID = @ProductID

(4) –––––––––––––––––––––––––––––––––––––––––––––––

ALTER PROCEDURE dbo.DeleteOrderDetails

(

@OrderID int,

@ProductID int

)

DELETE FROM [Order Details] WHERE OrderID = @OrderID AND ProductID = @ProductID

The code is listed as follow:

Dim param As OleDbParameter

' SelectCommand

Dim selectCmd As OleDbCommand = mcn.CreateCommand()

selectCmd.CommandText = "SelectOrderDetails"

selectCmd.CommandType = CommandType.StoredProcedure

mda.SelectCommand = selectCmd

' UpdateCommand

Dim updateCmd As OleDbCommand = mcn.CreateCommand()

updateCmd.UpdatedRowSource = UpdateRowSource.OutputParameters

updateCmd.CommandText = "UpdateOrderDetails"

updateCmd.CommandType = CommandType.StoredProcedure

updateCmd.Parameters.Add("OrderID_New", OleDbType.Integer, 0, "OrderID")

updateCmd.Parameters.Add("ProductID_New", OleDbType.VarChar, 0, "ProductID")

updateCmd.Parameters.Add("Quantity_New", OleDbType.VarChar, 0, "Quantity")

param = updateCmd.Parameters.Add("OrderID_Orig", OleDbType.Integer, 0, "OrderID")

param.SourceVersion = DataRowVersion.Original

param = updateCmd.Parameters.Add("ProductID_Orig", OleDbType.VarChar, 0, "ProductID")

param.SourceVersion = DataRowVersion.Original

param = updateCmd.Parameters.Add("TimeStamp_Orig", OleDbType.Binary, 8, "TStamp")

param.SourceVersion = DataRowVersion.Original

param.Direction = ParameterDirection.InputOutput

mda.UpdateCommand = updateCmd

' DeleteCommand

Dim deleteCmd As OleDbCommand = mcn.CreateCommand()

deleteCmd.CommandText = "DeleteOrderDetails"

deleteCmd.CommandType = CommandType.StoredProcedure

deleteCmd.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

deleteCmd.Parameters.Add("ProductID", OleDbType.VarChar, 0, "ProductID")

mda.DeleteCommand = deleteCmd

' InsertCommand

Dim insertCmd As OleDbCommand = mcn.CreateCommand()

insertCmd.UpdatedRowSource = UpdateRowSource.OutputParameters

insertCmd.CommandText = "InsertOrderDetails"

insertCmd.CommandType = CommandType.StoredProcedure

insertCmd.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

insertCmd.Parameters.Add("ProductID", OleDbType.VarChar, 0, "ProductID")

insertCmd.Parameters.Add("Quantity", OleDbType.VarChar, 0, "Quantity")

param = insertCmd.Parameters.Add("TStamp", OleDbType.Binary, 8, "TStamp")

param.Direction = ParameterDirection.Output

mda.InsertCommand = insertCmd

mda.Fill(mds)

mdg.DataSource = mds

mdg.DataMember = "Table"

Note that in the UPDATE command the time stamp parameter is used for both input and output, and in the INSERT command the time stamp parameter is used for output only.

¨ Using DataAdapter Events

Some database such as Microsoft Access supports neither batch query nor output parameters on stored procedures. In this case we have to get back the new time stamp value using a command and set it into the row explicitly. We could do it right after we call DataAdapter.Update, but there is a better place to do it – the event handler of DataAdapter.RowUpdated event. Reasons:

1. When there are multiple rows updated, the event will be fired multiple times after each row is updated;

2. The event handler gets a event argument with a pointer to the updated row in the DataSet, therefore you don't need to find this row yourself.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Try

Dim param As OleDbParameter

' SelectCommand

mda.SelectCommand = New OleDbCommand("SELECT OrderID, ProductID, Quantity, TStamp FROM [Order Details] " & _

"WHERE OrderID < 10254")

mda.SelectCommand.Connection = mcn

' UpdateCommand

Dim updateCmd As OleDbCommand = mcn.CreateCommand()

updateCmd.CommandText = "UPDATE [Order Details] SET OrderID = ?, ProductID = ?, Quantity = ? " & _

"WHERE OrderID = ? AND ProductID = ? AND TStamp = ? "

updateCmd.Parameters.Add("OrderID_New", OleDbType.Integer, 0, "OrderID")

updateCmd.Parameters.Add("ProductID_New", OleDbType.VarChar, 5, "ProductID")

updateCmd.Parameters.Add("Quantity_New", OleDbType.VarChar, 15, "Quantity")

param = updateCmd.Parameters.Add("OrderID_Orig", OleDbType.Integer, 0, "OrderID")

param.SourceVersion = DataRowVersion.Original

param = updateCmd.Parameters.Add("ProductID_Orig", OleDbType.VarChar, 5, "ProductID")

param.SourceVersion = DataRowVersion.Original

param = updateCmd.Parameters.Add("TimeStamp_Orig", OleDbType.Binary, 8, "TStamp")

param.SourceVersion = DataRowVersion.Original

mda.UpdateCommand = updateCmd

' DeleteCommand

Dim deleteCmd As OleDbCommand = mcn.CreateCommand()

deleteCmd.CommandText = "DELETE FROM [Order Details] WHERE OrderID = ? AND ProductID = ?"

deleteCmd.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

deleteCmd.Parameters.Add("ProductID", OleDbType.VarChar, 5, "ProductID")

mda.DeleteCommand = deleteCmd

' InsertCommand

Dim insertCmd As OleDbCommand = mcn.CreateCommand()

insertCmd.CommandText = "INSERT INTO [Order Details] (OrderID, ProductID, Quantity) VALUES(?, ?, ?)"

insertCmd.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

insertCmd.Parameters.Add("ProductID", OleDbType.VarChar, 5, "ProductID")

insertCmd.Parameters.Add("Quantity", OleDbType.VarChar, 15, "Quantity")

mda.InsertCommand = insertCmd

mda.Fill(mds)

mdg.DataSource = mds

mdg.DataMember = "Table"

mCmdGetNewTs.CommandText = "Select TStamp From [Order Details] WHERE OrderID = ? and ProductID = ?"

mCmdGetNewTs.Connection = mcn

mCmdGetNewTs.Parameters.Add("OrderID", OleDbType.Integer)

mCmdGetNewTs.Parameters.Add("ProductID", OleDbType.Integer)

Catch ex As Exception

MessageBox.Show(ex.ToString)

End Try

End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

Try

mda.Update(mds)

Catch ex As Exception

MessageBox.Show(ex.ToString)

End Try

End Sub

Private Sub mda_RowUpdated(ByVal sender As Object, ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles mda.RowUpdated

If e.Status = UpdateStatus.Continue AndAlso _

(e.StatementType = StatementType.Insert OrElse e.StatementType = StatementType.Update) Then

mCmdGetNewTs.Parameters("OrderID").Value = e.Row("OrderID")

mCmdGetNewTs.Parameters("ProductID").Value = e.Row("ProductID")

e.Row("TStamp") = CType(mCmdGetNewTs.ExecuteScalar(), Byte())

e.Row.AcceptChanges()

End If

End Sub

5.6. Retrieving Auto-generated Identity (SQL Server)

Retrieving auto-generated primary key after submitting changes is a special case, which is different from the normal approach in Refreshing DataSet After Submitting Changes. For a non-identity column like a time stamp, the auto-generated column can be simply selected with the known primary key. For a auto-generated identity column, however, because the primary key is generated by the database and your code doesn’t know, you have no way to select the row.

To solve this problem, SQL server introduces a special SELECT command: SELECT @@IDENTITY. This command will return the lastest primary key which is generated by the database, like those auto-increment columns. Note that this command is not scoped – it will return the lastest identity generated anywhere – even if it is in another table. So if there is a trigger in the stored procedure which writes a logging record into a log table after you update your table, and that log table also have a auto-increment identity column, then the returned identity will be that of the log table.

To solve this problem, SQL server introduces a scoped SELECT command: SELECT SCOPE_IDENTY(). It will only return the auto-generated identify of the table that you have submitted. When you are using stored procedure, you should use output parameter to pass out the result of the SELECT SCOPE_IDENTY() command. After the stored procedure is executed, this command will return null.

Only the INSERT query needs to get back the newly generated identity. UPDATE query does not cause the identity to be regenerated.

When you update or insert, do not try to submit the auto-generated column, otherwise an exception will happen.

You can set the AutoIncrement property of the column in the dataset which corresponds to the auto-generated identity column in the database to true, and the AutoIncrementSeed and AutoIncrementStep to –1, so that user will know that this is a auto-generated column.

¨ Using batch query

mcn.ConnectionString = "File Name=DataLink.udl"

Dim param As OleDbParameter

' SelectCommand

mda.SelectCommand = New OleDbCommand("SELECT OrderID, CustomerID, ShipCountry FROM Orders " & _

"WHERE OrderID < 10254 OR OrderID > 11070")

mda.SelectCommand.Connection = mcn

' UpdateCommand

Dim updateCmd As OleDbCommand = mcn.CreateCommand()

updateCmd.CommandText = "UPDATE Orders SET CustomerID = ?, ShipCountry = ? WHERE OrderID = ?"

updateCmd.Parameters.Add("CustomerID_New", OleDbType.VarChar, 5, "CustomerID")

updateCmd.Parameters.Add("ShipCountry_New", OleDbType.VarChar, 15, "ShipCountry")

param = updateCmd.Parameters.Add("OrderID_Orig", OleDbType.Integer, 0, "OrderID")

param.SourceVersion = DataRowVersion.Original

mda.UpdateCommand = updateCmd

' DeleteCommand

Dim deleteCmd As OleDbCommand = mcn.CreateCommand()

deleteCmd.CommandText = "DELETE FROM Orders WHERE OrderID = ?"

deleteCmd.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

mda.DeleteCommand = deleteCmd

' InsertCommand

Dim insertCmd As OleDbCommand = mcn.CreateCommand()

insertCmd.CommandText = "INSERT INTO Orders (CustomerID, ShipCountry) VALUES(?, ?); " & _

"SELECT SCOPE_IDENTITY() AS OrderID"

'Or "SELECT @@IDENTITY AS OrderID"

insertCmd.Parameters.Add("CustomerID", OleDbType.VarChar, 5, "CustomerID")

insertCmd.Parameters.Add("ShipCountry", OleDbType.VarChar, 15, "ShipCountry")

mda.InsertCommand = insertCmd

mda.Fill(mds)

' The following use of -1 is so that when you add new records into the dataset, before updating,

' they all have ids like “-1”, “-2”, “-3”, so that user can easily distinguish these

' dataset-generated ids from database-generated official ids.

Dim colId As DataColumn = mds.Tables(0).Columns("OrderID")

colId.AutoIncrement = True

colId.AutoIncrementSeed = -1

colId.AutoIncrementStep = -1

mdg.DataSource = mds

mdg.DataMember = "Table"

¨ Using stored procedure output parameters

The stored procedure for INSERT command is as follow:

ALTER PROCEDURE dbo.InsertOrders

(

@CustomerID nchar(5),

@ShipCountry nvarchar(15),

@OrderID int OUTPUT

)

INSERT INTO Orders (CustomerID, ShipCountry) VALUES(@CustomerID, @ShipCountry)

SELECT @OrderID = SCOPE_IDENTITY()

The code for the INSERT command is (all the rest can be the same as retrieving using batch query):

' InsertCommand

Dim insertCmd As OleDbCommand = mcn.CreateCommand()

insertCmd.CommandText = "InsertOrders"

insertCmd.CommandType = CommandType.StoredProcedure

insertCmd.Parameters.Add("CustomerID", OleDbType.VarChar, 5, "CustomerID")

insertCmd.Parameters.Add("ShipCountry", OleDbType.VarChar, 15, "ShipCountry")

param = insertCmd.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

param.Direction = ParameterDirection.Output

mda.InsertCommand = insertCmd

¨ Retrieving the identity individually

In the above code, the insertion was done through a data adapter and a dataset, and the retrieved identity was set back to the row in the dataset. Now if you do the insertion directly through a command, you can retrieve the identity from the command’s parameter. In the following example, table test4 has two columns: ID of bigint which is the identity column, and Name of varchar. The stored procedure is:

create procedure testsp_insert_test4

@Name varchar,

@ID bigint OUTPUT

insert into test4 values(@Name)

select @ID = SCOPE_IDENTITY()

The code to insert a new row and retrieve the generated identity is:

SqlParameter param = null;

SqlConnection cn = new SqlConnection(strConnStr);

SqlCommand cmd = cn.CreateCommand();

cmd.CommandText = "testsp_insert_test4";

cmd.CommandType = CommandType.StoredProcedure;

param = cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 50, "Name"));

param.Value = "Silan Liu";

param = cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.BigInt, 8, "ID"));

param.Direction = ParameterDirection.Output;

cn.Open();

cmd.ExecuteNonQuery();

MessageBox.Show("Identity = " + cmd.Parameters["@ID"].Value.ToString());

cn.Close();

5.7. SET NOCOUNT ON/OFF

Each time a table is changed by the UPDATE query, the database will send a “n row(s) affected.” message. The DataAdapter uses the total number of rows to judge whether the update is successful – if it is 0, the update is deemed to have failed. If it is more than 0, it is successful.

If a stored procedure writes into a log table about whether your update succeeded, then even if it failed, the DataAdapter will still get one successful row affected because of the log record. To prevent this from happening, use SET ONCOUNT ON to turn off the sending of the message for all updates except for your update.

5.8. DataSet.Merge

DataSet.Merge merges data rows together on primary keys. When you call the target dataset’s Merge method to merge the source DataSet in, if the they both contain a row with the same primary key, the row in the source will by default overwrite the row in the target – the original version of the source row will overwrite the original version of the target row, and and the current version of the source row will overwrite the current version of the target row.

If you do not set up the primary key for at least the target DataSet, the two rows in both DataSets will exist in the target DataSet after merging. This is usually not what we want.

Note that the overwrite is done on a whole-row basis – one row is either totally overwritten by another, or not at all.

If you pass True to Merge’s second parameter bool preserveChanges, then it will only allow the original version of the target row to be overwritten, while still keeping the current version of the target row unchanged. This feature is very useful when there is a concurrency conflict – the database record has been changed by another user. After refreshing the original version of the record in your dataset, you can successfully submit it next time –to avoid conflict UPDATE command is normally set up to require the original version of your record to be the same as the database record.

Look at the following testing code:

Dim mds1 As New DataSetCustomers()

Dim mds2 As New DataSetCustomers()

Dim view1, view2 As DataView

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

view1 = New DataView(mds1.Customers, "", "", DataViewRowState.OriginalRows)

mdg1.DataSource = view1

view2 = New DataView(mds1.Customers, "", "", DataViewRowState.CurrentRows)

mdg2.DataSource = view2

mdg3.DataSource = mds2

mdg3.DataMember = mds2.Customers.TableName

End Sub

Private Sub btnChange_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChange.Click

mds1.Customers(0).CustomerName = "Frank1"

mds2.Customers(0).Title = "Engineer1"

mds2.AcceptChanges()

mds2.Customers(0).Title = "Engineer2"

End Sub

Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click

Try

mds1.Clear()

mds2.Clear()

mda.Fill(mds1)

mda.Fill(mds2)

mds1.Customers.PrimaryKey = New DataColumn() {mds1.Customers.CustomerIDColumn}

Catch ex As Exception

MessageBox.Show(ex.ToString)

End Try

End Sub

Private Sub btnMerge_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMerge.Click

mds1.Merge(mds2)

End Sub

Private Sub btnMergeRetain_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMergeRetain.Click

mds1.Merge(mds2, True)

End Sub

After btnLoad and btnChange clicked, the target row is

Original	1	Frank	Engineer
Current	1	Frank1	Engineer

the source row is

Original	1	Frank	Engineer1