This chapter is from the book
This chapter is from the book
This chapter is from the book
Working with Typed Datasets
As mentioned earlier in the chapter, typed datasets are not a separate set of ADO.NET classes. Instead, they are derived directly from the standard ADO.NET classes and define extra members that are specific to your database schema. To understand the main benefit of typed datasets, think of how data has been accessed throughout this chapter using the generic DataRow::Item property, where column name or index value is specified. Because the Item property has no intimate knowledge of the underlying data schema, any mistakes made when using it—such as such as misspelling the column name or using an incompatible data type—aren't realized until runtime. Conversely, typed datasets are generated directly from the database schema and thus produce members that allow for compile-time checking of these common mistakes.
In this section, I'll first illustrate—step-by-step—how to generate a typed dataset for the SQL Server sample Northwind database's Employees table. After that, I'll introduce an MFC SDI application that uses a typed dataset to list all records from the Employees table. Finally, I'll wrap up the section—and chapter—with a listing of the pros and cons of using untyped vs. typed datasets.
Generating a Typed Dataset
Now let's see how to use Visual Studio .NET to generate a typed dataset. As stated at the outset of this chapter, the demo will be using the SQL Server sample Northwind database.
-
Either create a new project with support for Managed Extensions, or open an existing Managed Extensions project.
-
Select the Add New Item option from the Project menu (or from the project's context menu in the Solution Explorer).
-
When the Add New Item dialog appears (Figure 6-5), click the Data folder (category) on the left and then the DataSet icon (template) on the right.
Figure 6-5. Visual Studio .NET provides a template for generating typed datasets. -
Enter a name for the header file that will contain the definitions of the typed dataset and any included DataTable objects. While this demo will only use the Northwind Employees table, you might want add other DataTable definitions later, so name the file Northwind and click the Open button.
-
At this point a script will run that will generate the file containing the typed dataset information. It's important to realize that this script might trigger a response from your anti-virus software. If that is the case, just select the option to allow the script to run. The Visual Studio .NET Output window will display the progress of the script.
-
When a typed dataset file is generated, an XSD file (XML Schema Definition) file is created to describe the dataset. This file is automatically opened upon the creation of the typed dataset (Figure 6-6) in a window that is sometimes called a canvas. Database entities can be added to the dataset by dragging them from the Server Explorer onto the canvas, so at this point, click the canvas's Server Explorer link (also available from the Project menu).
Figure 6-6. A drop-target canvas is used to visually represent a typed dataset. -
If the desired database is on your local machine, simply browse to that database using the Server Explorer tree view. However, if your database is on a remote machine, you'll need to click the Connect to Server button at the top of the Server Explorer dialog. When the Add Server dialog is displayed, simply type the name of the server. (There's no need to add the UNC path information.)
-
At this point, you should have a database displayed in the Server Explorer—local or remote. Figure 6-7 shows my particular configuration. As you can see, JEANVALJEAN is the name of my programmer machine, and it has no SQL Server databases. Therefore, I added (connected to) a server named FANTINE to my Server Explorer so that I could access its SQL Server databases.
Figure 6-7. The Server Explorer allows you to drag database entities—such as tables, views, and stored procedures—onto the typed dataset
view. -
While the icons next to the various databases include an "x", once you expand the item the Server Explorer connects to the database and retrieves the selected database's information—such as its Tables, Views, Stored Procedures, and so on. Here you would simply drag the desired database entity to the dataset palette. For purposes of this demo—and so we can make an "apples and apples" comparison between the untyped datasets you've been using throughout the chapter and a typed dataset—drag the Employees table from the Northwind database to the typed dataset canvas.
-
If you open the Northwind.h file, you won't see any Employee DataTable information. That information isn't generated until you build the project. Building the project now will result in Visual Studio .NET generating the expected classes and members in the Northwind.h file for the Employees table.
)
Figure 6-5.)
Figure 6-6.)
Figure 6-7.A tremendous amount of code is actually generated for a typed dataset, so we'll just briefly look at some of it—but enough so that you get an idea of what all has been done for you. When you open the Northwind.h file, the first thing you notice is that the file is free-standing in that the appropriate #using and using namespace directives have been inserted. Also note that the typed dataset classes have been defined within a namespace of the same name as the current project. That means you'll have to either qualify all uses of the types defined in this file or insert a using namespace directive into any of your typed dataset client code.
The first class that you encounter will be the DataSet-derived Northwind class. This class also defines nested classes for the Employees table that include a DataTable-derived class, a DataRow-derived class, and a delegate for subscribing to row-change events.
public __gc class Northwind : public System::Data::DataSet {
public : __gc class EmployeesDataTable;
public : __gc class EmployeesRow;
public : __gc class EmployeesRowChangeEvent;
...
You'll note that the file does not include any connection-specific information as you might expect. This is so that the typed dataset can be used against any connection. Therefore, the typed dataset only refers to the data entity's schema—not to where the underlying data store is located or how to connect to it. Therefore, the way you would use these types is to connect and build a data adapter just as you would with an untyped dataset, and then use the typed DataSet-derived and DataTable-derived classes as follows.
#include "Northwind.h" using namespace System::Data::SqlClient; using namespace TypedDataSetDemo; ... SqlConnection* conn = new SqlConnection(S"Server=FANTINE;" S"Database=Northwind;" S"Integrated Security=true;"); SqlDataAdapter* adapter = new SqlDataAdapter(S"SELECT * FROM Employees", conn); SqlCommandBuilder* commandBuilder = new SqlCommandBuilder(adapter); conn->Open(); Northwind* northwind = new Northwind(); adapter->Fill(northwind, S"Employees"); conn->Close(); // No longer needed Northwind::EmployeesDataTable* employees = northwind->Employees; ...
Let's look at this code—especially in comparison to the connection code used previously in this chapter using untyped datasets. Obviously, I need to include the Northwind.h file, as it defines the typed dataset types. After that, I need to specify the System::Data::SqlClient namespace, as the Northwind.h file is using generic base classes and the SqlClient namespace is specific to SQL Server ADO.NET types. Finally, the TypedDataSetDemo namespace is referenced so that we don't have to qualify the references to the various typed dataset types.
After the standard connection object creation and instantiation of a data adapter object, I declare an instance of the DataSet-derived Northwind class. From there, I call the adapter's Fill method. However, note that I've changed my table name from the "AllEmployees" value that I've used throughout the chapter to "Employees". While seemingly trivial, I'm actually forced to do this because the Northwind::Employees property is hard-coded to return an EmployeesDataTable object with a name of "Employees".
One last thing that I'll point out before you see a demo illustrating how to use a typed dataset in an MFC application is that no DataRowCollection-derived class is generated for us. This is because the EmployeesDataTable implements the IEnumerable interface and acts as a collection for its rows. The various collection classes, the IEnumerable interface, and even creating your own enumerable classes are covered in Chapter 11. The EmployeesDataTable also implements the Count and Item properties so that an EmployeesDataTable object can be iterated like an array.
public :
[System::Diagnostics::DebuggerStepThrough]
__gc class EmployeesDataTable :
public System::Data::DataTable,
public System::Collections::IEnumerable
{
...
__property System::Int32 get_Count();
...
public:
__property TypedDataSetDemo::Northwind::EmployeesRow* get_Item(
System::Int32 index
);
Because these properties have been implemented, the EmployeesDataTable can be enumerated as simply as this:
...
// For every employee record
for (int i = 0; i < employees->Count; i++)
{
int id = *dynamic_cast<__box int*>(employees->Item[i]->EmployeeID);
String* firstName = employees->Item[i]->FirstName;
String* lastName = employees->Item[i]->LastName;
}
As you can see, each row is retrieved via the Item property, and, as the row is a Northwind::EmployeesRow, there are properties with the same names and types as the actual Employees table:
public : [System::Diagnostics::DebuggerStepThrough]
__gc class EmployeesRow : public System::Data::DataRow {
...
public: __property System::Int32 get_EmployeeID();
public: __property void set_EmployeeID(System::Int32 value);
public: __property System::String* get_LastName();
public: __property void set_LastName(System::String*_u32 ?value);
public: __property System::String* get_FirstName();
public: __property void set_FirstName(System::String* value);
...
Using Typed Datasets
Now let's see how to use typed dataset types in an MFC demo. We'll keep the application simple, as it will connect to the Employees table, read all the employee records, and display them in a list view. However, combining what you'll see here with what you've learned throughout the chapter should make it easy for you to use typed datasets for all your dataset needs should you decide to go that route.
-
To get started, create and MFC SDI application called TypedDataSetDemo, setting the view base class to CListView and updating the Project settings to support Managed Extensions.
-
As explained in the previous section, create a typed dataset called Northwind and add to it the Employees table.
-
Add the following directives to the stdafx.h file:
#using <mscorlib.dll> #using <system.dll> #using <system.windows.forms.dll> using namespace System; using namespace System::Windows::Forms; #undef MessageBox
-
Open the TypedDataSetDemoView.h file and add the following directives just before the CTypedDataSetDemoView class declaration.
#include "Northwind.h" using namespace System::Data::SqlClient; using namespace TypedDataSetDemo;
-
Declare the following ADO.NET objects in the CTypedDataSetDemoView class.
class CTypedDataSetDemoView : public CListView { ... protected: gcroot<Northwind*>northwind; gcroot<SqlDataAdapter*>adapter; gcroot<Northwind::EmployeesDataTable*>employees; gcroot<SqlCommandBuilder*>commandBuilder; ... -
Add the following code to the view's OnInitialUpdate function to initialize the list view.
void CTypedDataSetDemoView::OnInitialUpdate() { CListView::OnInitialUpdate(); // Add columns to listview CListCtrl& lst = GetListCtrl(); lst.InsertColumn(0, _T("ID")); lst.InsertColumn(1, _T("First Name")); lst.InsertColumn(2, _T("Last Name")); ... -
In the view class's PreCreateWindow function, set the view window's style to "report":
BOOL CTypedDataSetDemoView::PreCreateWindow(CREATESTRUCT& cs) { cs.style |= LVS_REPORT; return CListView::PreCreateWindow(cs); } -
Insert the following code just before the end of the OnInitialUpdate function.
#pragma push_macro("new") #undef new try { SqlConnection* conn = new SqlConnection(S"Server=localhost;" S"Database=Northwind;" S"Integrated Security=true;"); adapter = new SqlDataAdapter(S"SELECT * FROM Employees", conn); commandBuilder = new SqlCommandBuilder(adapter); conn->Open(); northwind = new Northwind(); adapter->Fill(northwind, S"AllEmployees"); conn->Close(); // No longer needed employees = northwind->Employees; ReadAllEmployees(); } catch(Exception* e) { MessageBox::Show(e->Message, S".NET Exception Thrown", MessageBoxButtons::OK, MessageBoxIcon::Error); } #pragma pop_macro("new") -
Finally, implement the ReadAllEmployees function as follows:
void CTypedDataSetDemoView::ReadAllEmployees() { try { CWaitCursor wc; CListCtrl& lst = GetListCtrl(); lst.DeleteAllItems(); for (int i = 0; i < employees->Count; i++) { int idx = lst.InsertItem(i, (CString)__box(employees->Item[i]->EmployeeID)-> ToString()); lst.SetItemText(idx, 1, (CString)employees->Item[i]->FirstName); lst.SetItemText(idx, 2, (CString)employees->Item[i]->LastName); } } catch(Exception* e) { MessageBox::Show(e->Message, S".NET Exception Thrown", MessageBoxButtons::OK, MessageBoxIcon::Error); } }
Building and running the application will result in something similar to what you see in Figure 6-8. The code in this section was really not much different than what you've seen throughout this chapter, with the principal difference being that the use of typed dataset types enabled you to have the compiler check for common errors as opposed to having them realized at runtime. This segues nicely into the next part of this chapter—weighing the pros and cons of using typed datasets.
)
Figure 6-8. Example of running the TypedDataSetDemo application
Weighing the Pros and Cons of Typed Datasets
Covering the entirety of typed datasets would take an entire chapter by itself. However, you've learned enough about typed datasets in this section to realize some of the positive points regarding using them in your ADO.NET code. Let's now briefly look at some of the advantages and disadvantages of using typed datasets (vs. using untyped datasets).
Advantages of typed datasets:
-
Compile-time type checking— Reduces runtime errors by having members based on the data's actual schema as opposed to untyped datasets, where you call a generic function and can pass an object of any type.
-
Schema-specific members— Typed datasets define properties for getting and setting values where the property name is the same as the underlying column name. They also define properties for determining if the column is null and methods for searching the table via primary key(s).
-
Data binding support in VS.NET— Only useful with Windows Forms applications, but bears mentioning if you plan on doing development based entirely on .NET in addition to the mixed-mode programming that is the focus of this book.
-
Intellisense support— When using untyped datasets, you have to know beforehand the names of the columns and the types that the respective columns work with. With typed datasets, as soon as you enter the name of the type, Intellisense displays its members, thereby saving you development and debugging time.
Disadvantages of typed datasets:
-
Versioning— Typed datasets can actually increase development time in situations where your schema changes, because you'll need to update the typed dataset information manually. This is obviously the same problem we've battled for years with CRecordset classes—having to modify them manually when the underlying schema changes.
-
Tightly coupled— In its current design, typed datasets are difficult to extend and can't be modified (as they're auto-generated each time the project is built). In addition, they force a tight coupling of client to data access code, which might not be best for all situations.
As mentioned earlier in the chapter, I use untyped datasets in code snippets and demos, as this provides for shorter, to-the-point code listings. However, I personally choose to use typed datasets in my production code, as once I'm to the implementation stage of a project, I'm finished with any major changes to the database schema, and with the introduction of dataset annotations, the only two real disadvantages for me are moot. (Annotations are extensions to the XSD format used to define typed datasets that allow some changes such as the renaming of classes and properties as well as defining how to deal with null values.)