This is another in an occasional series of short articles about working with SQL Server data in a .Net application, to explain how to work with with SQL Server data, what is possible, and how to work with the issues that can come up. For the next article I decided to focus on the DataSet object which is part of the ADO.Net Framework.
The ADO.Net Framework is a collection of objects that provides a consistent way to access data using any .Net language, regardless of the original format of the data. For example ADO.Net objects can work with text files, XML, and SQL Server and Oracle databases as data sources.
The ADO.Net DataSet object is the set of objects in ADO.Net that represents data in a way that can be accessed in code. A data set is disconnected from the data source. The data from a SQL Server database can be filled from the data source, accessed and changed, separate from the data source, and then reconciled back to the database by using various ADO.Net objects and methods.
The collections of DataTable objects, DataRelation objects and constraint objects (ForeignKeyConstraint and UniqueConstraint) of the DataSet object correspond to the data tables, relationships and constraints of the SQL Server database (or whatever other kind of data set). The DataSet also includes a collection of Extended Properties like the SQL Server database Extended Properties.
Once the data is in a data set, it can be attached to many Windows and Web-based components that accept a data source. For example I have used data sets created in C# code to build a display grid for a web page, and to customize the set of options for selection in a list box on a Windows form.
With methods of the DataSet object you can create a copy of an existing data set, or create a clone with the same structure but none of the data, or with a subset of the data. For example you can create a copy of a dataset that has only rows that have been changed or added since the last update to pass as an update back to the SQL Server database. With other methods you can merge data sets. Tables, rows or an entire data set can be merged into another data set, with options to specify matching resolving schema differences between the data sets.
This article is a short introduction to the DataSet object, and a demonstration of how to create and fill a data set with code. Then I will go into some of the options for maintining the data integrity within your .Net application using a DataSet object using .Net code.
Sample Application
I created a simple console application to try out the options for working with data in a DataSet. The .zip file of the complete test is included below.
I added a class called DataSetAccess with two methods, TestDataSet() and BuildDataSet(). The namespace DataTest1 comes from the name I gave to the project. Here is the code for the basic class:
using System;
using System.Data;
namespace DataTest1
{
class DataSetAccess
{
// Demonstrates features of data sets
// by creating a sample database with relations
public void TestDataSet()
{
DataSet salesInventory = BuildDataSet();
// test code goes here ... } private DataSet BuildDataSet() { DataSet newData = new DataSet(); // build the data set here ... return newData; } } }
The TestDataSet() method first invokes the BuildDataSet() method to create the test DataSet. After that is where we will insert the code to insert rows and test the data relations.
The BuildDataSet() method in the code above creates a new instance of a DataSet object and returns it. We will be inserting the code to add the tables and relations in between the DataSet creation and the return statement.
To run the test code I added code to the Main() method to create an instance of my class and run the test.
static void Main(string[] args)
{
DataSetAccess ds = new DataSetAccess();
ds.TestDataSet();
}Getting the Data
There are different ways of getting the data from SQL Server into a data set that can be used depending on your needs.
The DataAdapter (SqlDataAdapter) object is like an interface between the DataSet and the database. The SQLDataAdapter's methods are used to manage the connection between the data in the DataSet and the data in SQL Server. The SQLDataAdapter object has methods to fill data sets from a SQL Server source and reconcile any changes back to the source.
The Command (SqlCommand) object offers various options for executing T-SQL statements or stored procedures, with or without parameters. There are three types of execute methods for executing the T-Sql statement or stored procedure.
- ExecuteNonQuery() is for queries that do not require any data returned, however parameters of the query can be used to exchange information. The return value of the method is an integer that indicates the number of rows affected.
- ExecuteScalar() returns the first column of the first row of the result returned by the command.
- ExecuteReader() executes the command and returns a SQLDataReader object to access the results of the command.
The SQLDataReader object is only created by the execution of the ExecuteReader method of a SQLCommand object and cannot be created using a constructor. The data reader object provides rows in a forward-only manner for processing.
The definition of the data set in ADO.Net is not specific to SQL Server so the data types and constraints in ADO.Net are similar but not exactly the same as the ones in SQL Server.
The SQLDataReader object has SQL Server–specific accessor methods that return the SQL Server data in a way that is more accurate. There is an article in BOL about mapping SQL Server data types to .Net types is here.
Or No Database...
For my demonstration I am going to show how to build and populate the data set entirely in code, with no connection to any database.
The first line in the BuildDataSet() method in the code example above creates the DataSet object that will be the "container" for our sample database.
DataSet newData = new DataSet(); You can also give the data set a name, which is saved as the property, DataSetName. The code above creates a DataSet with the DataSetName of "NewDataSet." The code example below creates DataSet with the DataSetName, "OrderProcessing."
DataSet newData = new DataSet("OrderProcessing"); For the sample data set I created a simple order inventory database with orders, customers, and inventory. The inventory is in a table called Catalog. Here is the T-SQL code for the table:
CREATE TABLE [dbo].[Catalog](
[item_id] [int] NOT NULL,
[item_name] [varchar](50) NULL,
CONSTRAINT [PK_Catalog] PRIMARY KEY NONCLUSTERED ( [item_id] ASC)
)This code, which should be added to the BuildDataSet() method, creates the Catalog table in C#.
DataTable Catalog = new DataTable("Catalog");
Catalog.Columns.Add("item_id", typeof(int));
Catalog.Columns["item_id"].AllowDBNull = false;
Catalog.Columns["item_id"].Unique = true;
Catalog.Columns.Add("item_name", typeof(string));
Catalog.Columns["item_name"].MaxLength = 50;
Catalog.PrimaryKey = new DataColumn[] { Catalog.Columns["item_id"] };
newData.Tables.Add(Catalog);The first line creates a DataTable object called "Catalog" in both the C# code and the name of the DataTable. The fields in the table are represented by a collection of Column objects (called Columns).
Then the next line uses the Columns.Add method to create a Column object called "item_id" and add it to the Columns collection. The parameters supply a column name and a type, but you can create a column with just the name. When I tried this, it created a column with a string data type.
In the next 2 lines we use the name of the column as an index to set some of the properties of the item_id column in the Columns collection. These properties restrain the item_id column to be unique and to not allow nulls.
The item name column is next, which is of type string with a maximum length to enforce the size of the varchar field. Then the next line sets the primary key to an array containing one item, the item_id column. The last line adds the Catalog table to the Tables collection of the data set.
The primary key is NONCLUSTERED because the rows are not sorted. They go into the table in the order they are inserted However, with ADO.Net you can create a DataView object to sort rows and also filter the data.
To insert a row into a DataTable with code, you create a DataRow object by invoking the NewRow() method of the DataTable. The NewRow() method uses the set of columns in the DatTable's Columns collection to create a DataRow object with all the columns (and constraints) included.
To access the values of the columns in the DataRow, you can use the column names as an index to the values in the columns, as we did above to set the properties of the columns. This code sample creates two rows ind inserts them into the Catalog table's Rows collection. This code would be inserted into the TestDataSet() method.
DataRow newItem = salesInventory.Tables["Catalog"].NewRow();
newItem["item_id"] = 1;
newItem["item_name"] = "Widget";
salesInventory.Tables["Catalog"].Rows.Add(newItem);
newItem = salesInventory.Tables["Catalog"].NewRow();
newItem["item_id"] = 2;
newItem["item_name"] = "Fidget";
salesInventory.Tables["Catalog"].Rows.Add(newItem);You can also use the integer, zero-based index of the column as in this sample.
newItem = salesInventory.Tables["Catalog"].NewRow();
newItem[0] = 3;
newItem[1] = "Blodge";
salesInventory.Tables["Catalog"].Rows.Add(newItem);The columns are indexed in the order they are added to the Columns collection.
Once you have data in your DataTable the rows are indexed as zero-based integers in the order added. The second index is the column name. This code sample writes the values in the first Catalog item to the console:
Console.WriteLine("Item ID: {0} Name: {1}",
salesInventory.Tables["Catalog"].Rows[0]["item_id"],
salesInventory.Tables["Catalog"].Rows[0]["item_name"]);This is the output in the console window for the example above:
Item ID: 1 Name: Widget
And also here you can use the integer position of the columns:
Console.WriteLine("Item ID: {0} Name: {1}",
salesInventory.Tables["Catalog"].Rows[0][0],
salesInventory.Tables["Catalog"].Rows[0][1]);This produces the same output.
You can use foreach to spin through the collection of rows like this:
foreach (DataRow dr in salesInventory.Tables["Catalog"].Rows)
{
ConsoleWriteLine("Item ID: {0} Name: {1}", dr["item_id"], dr["item_name"]);
}Which prints the test records like this:
Item ID: 1 Name: Widget
Item ID: 2 Name: Fidget
Item ID: 3 Name: BlodgeFor our sample data set we will add customers, orders and order details tables as defined in T-SQL this way:
CREATE TABLE [dbo].[Customers](
[customer_id] [int] NOT NULL,
[first_name] [varchar](50) NULL,
[last_name] [varchar](50) NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY NONCLUSTERED ([customer_id] ASC)
)
CREATE TABLE [dbo].[Orders](
[order_id] [int] NOT NULL,
[customer_id] [int] NULL,
[order_date] [date] NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY NONCLUSTERED ([order_id] ASC)
)
CREATE TABLE [dbo].[OrderDetail](
[order_id] [int] NOT NULL,
[detail_num] [int] NOT NULL,
[item_id] [int] NULL,
[quantity] [int] NULL,
[unit_price] [decimal](18, 2) NULL,
CONSTRAINT [PK_OrderDetail] UNIQUE NONCLUSTERED ([order_id] ASC, [detail_num] ASC)[PRIMARY]
)This code which would be inserted in the BuildDataSet() method. Note that I have included 2 columns in the key of the OrderDetail table.
// Customers
DataTable Customers = new DataTable("Customers");
Customers.Columns.Add("customer_id", typeof(int));
Customers.Columns["customer_id"].AllowDBNull = false;
Customers.Columns["customer_id"].Unique = true;
Customers.PrimaryKey = new DataColumn[] { Customers.Columns["customer_id"] };
Customers.Columns.Add("first_name", typeof(string));
Customers.Columns["first_name"].MaxLength = 50;
Customers.Columns.Add("last_name", typeof(string));
Customers.Columns["last_name"].MaxLength = 50;
newData.Tables.Add(Customers);
// Orders
DataTable Orders = new DataTable("Orders");
Orders.Columns.Add("order_id", typeof(int)); // Primary key
Orders.Columns["order_id"].AllowDBNull = false;
Orders.Columns["order_id"].Unique = true;
Orders.PrimaryKey = new DataColumn[] { Orders.Columns["order_id"] };
Orders.Columns.Add("customer_id", typeof(int));
Orders.Columns.Add("order_date", typeof(DateTime));
newData.Tables.Add(Orders);
// Order Detail
DataTable OrderDetail = new DataTable("OrderDetail");
OrderDetail.Columns.Add("order_id", typeof(int));
OrderDetail.Columns["order_id"].AllowDBNull = false;
OrderDetail.Columns.Add("detail_num", typeof(int));
OrderDetail.Columns["detail_num"].AllowDBNull = false;
OrderDetail.PrimaryKey = new DataColumn[] { OrderDetail.Columns["order_id"], OrderDetail.Columns["detail_num"] };
OrderDetail.Columns.Add("item_id", typeof(int));
OrderDetail.Columns.Add("quantity", typeof(int));
OrderDetail.Columns.Add("unit_price", typeof(decimal));
newData.Tables.Add(OrderDetail);To insert some customers, an order and an order detail row, add this code to the TestDataSet() method:
DataRow customer = salesInventory.Tables["Customers"].NewRow();
customer["customer_id"] = 1;
customer["first_name"] = "Abby";
customer["last_name"] = "Adams";
salesInventory.Tables["Customers"].Rows.Add(customer);
customer = salesInventory.Tables["Customers"].NewRow();
customer["customer_id"] = 2;
customer["first_name"] = "Bob";
customer["last_name"] = "Barkley";
salesInventory.Tables["Customers"].Rows.Add(customer);
customer = salesInventory.Tables["Customers"].NewRow();
customer["customer_id"] = 3;
customer["first_name"] = "Chris";
customer["last_name"] = "Corwin";
salesInventory.Tables["Customers"].Rows.Add(customer);
DataRow newOrder = salesInventory.Tables["Orders"].NewRow();
newOrder["order_id"] = 1;
newOrder["customer_id"] = 1;
newOrder["order_date"] = DateTime.Now.Date;
salesInventory.Tables["Orders"].Rows.Add(newOrder);
DataRow newOrderDetail = salesInventory.Tables["OrderDetail"].NewRow();
newOrderDetail["order_id"] = 1;
newOrderDetail["detail_num"] = 1;
newOrderDetail["item_id"] = 1;
newOrderDetail["quantity"] = 1;
newOrderDetail["unit_price"] = 1;
salesInventory.Tables["OrderDetail"].Rows.Add(newOrderDetail);Adding Relations and Constraints
To add integrity constraints to the DataSet, we add DataRelation objects to the Relations collection of the DataSet. There is a BOL article on DataTable constraints here.
To represent a foreign key relationship between Orders and Customers to our DataSet, like this example in T-SQL:
ALTER TABLE [dbo].[Orders] WITH CHECK ADD CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([customer_id])
REFERENCES [dbo].[Customers] ([customer_id]) ON UPDATE CASCADE
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customers]We can add a foreign key constraint to the DataSet. This code will be added to the BuildDataSet() method:
DataRelation fk1 = new DataRelation("FK_Orders_Customers", Customers.Columns["customer_id"], Orders.Columns["customer_id"]);
newData.Relations.Add(fk1);
ForeignKeyConstraint fkc1 = fk1.ChildKeyConstraint;
fkc1.UpdateRule = Rule.Cascade;
fkc1.DeleteRule = Rule.SetNull; // Child key constraint rules.
fkc1.AcceptRejectRule = AcceptRejectRule.None;In this example the first two lines create a DataRelation object and add it to the Relations collection of the DataSet.
In the next line the ChildKeyConstraint property of the new DataRelation returns the ForeignKeyConstraint object that represents the parent-child relationship between Orders and Customers in the DataRelation object.
In the next line we set the UpdateRule property of the ForeignKeyConstraint object to implement the update rule to cascade updates from the Customer table to any rows in the Orders table. Then in the next two lines the other rules associated with the relation are disabled.
Next we will add these relations to our test DataSet to cascade changes to the key fields of the Orders and Catalog tables to the related OrderDetail records.
ALTER TABLE [dbo].[OrderDetail] WITH CHECK ADD CONSTRAINT [FK_OrderDetail_Orders] FOREIGN KEY([order_id])
REFERENCES [dbo].[Orders] ([order_id]) ON UPDATE CASCADE
ALTER TABLE [dbo].[OrderDetail] CHECK CONSTRAINT [FK_OrderDetail_Orders]
ALTER TABLE [dbo].[OrderDetail] WITH CHECK ADD CONSTRAINT [FK_OrderDetail_Catalog] FOREIGN KEY([item_id])
REFERENCES [dbo].[Catalog] ([item_id]) ON UPDATE CASCADE
ALTER TABLE [dbo].[OrderDetail] CHECK CONSTRAINT [FK_OrderDetail_Catalog]This code is added to the BuildDataSet() method.
DataRelation fk2 = new DataRelation("FK_OrderDetail_Orders", Orders.Columns["order_id"], OrderDetail.Columns["order_id"]);
newData.Relations.Add(fk2);
ForeignKeyConstraint fkc2 = fk2.ChildKeyConstraint;
fkc2.DeleteRule = Rule.SetNull;
fkc2.UpdateRule = Rule.Cascade;
fkc2.AcceptRejectRule = AcceptRejectRule.None;
DataRelation fk3 = new DataRelation("FK_OrderDetail_Catalog", Catalog.Columns["item_id"], OrderDetail.Columns["item_id"]);
newData.Relations.Add(fk3);
ForeignKeyConstraint fkc3 = fk3.ChildKeyConstraint;
fkc3.DeleteRule = Rule.SetNull; // Child key constraint rules.
fkc3.UpdateRule = Rule.Cascade;
fkc3.AcceptRejectRule = AcceptRejectRule.None;
To tell the DataSet to enforce the rules we need to set the EnforceConstraints property of the DataSet by setting it to true.
newData.EnforceConstraints = true; Testing the Constraints
In the following code examples we will be testing the constraints. All of the following code will be added to the TestDataSet() method. To test whether changes to the customer_id in a Customer record will cascade to the customer_id in the order, we can insert this code:
salesInventory.Tables["Customers"].Rows[0]["customer_id"] = 4;
Console.WriteLine("Customer ID For the first order after update to customer: {0}",
salesInventory.Tables["Orders"].Rows[0]["customer_id"].ToString());This code alters the value of customer_id column of the first Cutomer row and then displays the value of the customer_id in the related Order row. The result displays the new value as expected which demonstrates that the value is cascading.
Customer ID For the first order after update to customer: 4By catching esceptions when problem data is inserted or updated in the DataSet, you can catch problems in your application before resolving the changes back to the SQL Server database.
I added this simple method to the DataSetAccess class that catches exceptions when inserting rows and displays the exception type and the result.
private bool AddDataRow(DataTable dt, DataRow dr)
{
// Insert the row dr in table dt and catch exceptions
bool result = true;
try
{
dt.Rows.Add(dr);
}
catch (Exception ex)
{
result = false;
Console.WriteLine("Insert Error: {0}", ex.GetType().ToString());
Console.WriteLine("Message: {0}", ex.Message);
}
return result;
}
This method adds the DataRow to the DataTable's Rows collection in a "try" block, and writes the resulting error type and the Message property of the Exception object, which gives more information about what caused the error.
For the next test I used the AddDataRow() method to try to insert an order for a customer that does not exist:
newOrder = salesInventory.Tables["Orders"].NewRow();
newOrder["order_id"] = 2;
newOrder["customer_id"] = 5; // customer_id 5 does not exist in the parent table.
newOrder["order_date"] = DateTime.Now.Date;
AddDataRow(salesInventory.Tables["Orders"], newOrder);And this was the Exception type and Message that was displayed.
Insert Error: System.Data.InvalidConstraintException
Message: ForeignKeyConstraint FK_Orders_Customers requires the child key values (5) to exist in the parent table.When trying to insert a duplicate detail number, a part of the key for the OrderDetail table with this code:
newOrderDetail = salesInventory.Tables["OrderDetail"].NewRow();
newOrderDetail["order_id"] = 1;
newOrderDetail["detail_num"] = 1; // duplicate detail number.
newOrderDetail["item_id"] = 1;
newOrderDetail["quantity"] = 1;
newOrderDetail["unit_price"] = 1;
AddDataRow(salesInventory.Tables["OrderDetail"], newOrderDetail);
This was the error that results.
Insert Error: System.Data.ConstraintException
Message: Column 'order_id, detail_num' is constrained to be unique. Value '1, 1' is already present.A string that is too long for the field resulted in an ArgumentException type:
newItem = salesInventory.Tables["Catalog"].NewRow();
newItem["item_id"] = 5;
newItem["item_name"] = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"; // Too long for name
salesInventory.Tables["Catalog"].Rows.Add(newItem);
AddDataRow(salesInventory.Tables["Catalog"], newItem);With this result:
Insert Error: System.ArgumentException
Message: Cannot set column 'item_name'. The value violates the MaxLength limit of this column.Coming Soon?
This article demonstrates some of the features of the ADO.Net DataSet by building tables and relations with .Net code then demonstrates how to address the data and metadata of the tables and relations. It also demonstrates how to insert data with code and respond to integrity violations.
For the next part of the series I will show how to read the schema for a SQL Server database and use it to build a data set on the fly with the same schema. In my research for this article I discovered a method of the SQLDataReader, GetSchemaTable(), that returns different parts of the schema of a SQL Server database. This article describes all the columns returned by SqlDataReader.GetSchemaTable method.
Thanks for reading. Please provide feedback in the discussion about what you think or if you have any other ideas for topics. I will provide my list of possible topics in the discussion.
