In ancient times and legends of lore, information was shepherded amongst a collective of elders, magicians, storytellers, and jesters. This collective served as the data storehouse for all that was known and catalogued in the world. This method for data storage and retrieval resulted in an entropic fallacy of facts and events. Technology advanced, and information started to be written down on parchment, greatly increasing its accuracy. Data eventually started to be stored in voluminous repositories of books. Time passed, and the world ultimately began storing data in the first “databases,” known as libraries. These libraries established the idea of standardizing how data was stored and retrieved. Without standards, finding specific information would prove to be a chaotic and grueling process. The usefulness of any data storage is proportional to the storage size and retrieval efficiency. Hundreds of years have passed since those ancient times, and we have evolved into an era where computers can store more information than the human brain.
Almost every application handles and stores data to some extent, whether in the form of a database, a spreadsheet, or a flat text file. Today, developers have a multitude of databases and persistence frameworks that can be used to store and retrieve millions of records at lightning speed. As time passes, so do these databases and frameworks. The latest and greatest data access technology from Microsoft is ADO.NET, which is basically a collection of classes, methods, and attributes that are used to facilitate the efficient communication between an application and a data store. Functionally, ADO.NET is an overhaul of ADO (ActiveX Data Objects) with a continuation and extension of the key concepts.
Because of dependencies, overhead, or maintenance support, most games do not have a database system to store information; tools used in content creation, however, especially those used for role-playing games, often store information about game entities in a database for a variety of reasons. Technical designers using a content tool connected to a database benefit from real-time changes that are immediately in effect when another designer makes a change to the data. Imagine if game entity information were stored in XML files that had to be versioned somehow amongst all the technical designers so that everyone worked off the same data. A centralized data store is the solution to this problem, and implementing such a beast is very easy using ADO.NET.
In this chapter, I discuss the advantages of ADO.NET and cover the ADO.NET object model. I then proceed into some simple vanilla examples of using some components of ADO.NET, and finish off with an editor front-end for editing database entries for potion items.
Perhaps the greatest glory of ADO.NET is its ability to access structured data from a variety of diverse data sources, like Microsoft SQL Server, XML, and other data sources that are exposed with OLE DB. Microsoft SQL Server and OLE DB do not need much of an introduction, but the XML support is a real gem for ADO.NET. Interoperability support is very strong, since all data in ADO.NET is transferred in XML so that any platform can understand the data. This allows developers to separate data processing and the user interface onto separate servers, greatly improving performance and maintainability for systems where scalability is important.
In addition to the XML structure, ADO.NET also supports disconnected datasets along with the typical client-server model, without retaining locks or connections that consume limited system resources. Disconnected datasets also allow for user growth without demanding many additional resources for the server. In addition to disconnected datasets, ADO.NET also includes support for automatic connection pooling.
Even though there is a learning curve, once you have grasped the concepts behind ADO.NET, your overall development time will decrease, and you will produce more bug-free code. Therefore, productivity gains can also be considered when describing the advantages and benefits of ADO.NET.
The ADO.NET object model is divided into a couple of group classifications: content components and managed provider components. The content components are those that actually store the data. These components include the DataSet, DataView, DataTable, DataRelation, DataColumn, and DataRow classes. The managed provider components are those that communicate with the data sources to facilitate the retrieval and updating of data. These components include the various connection, command, and data reader classes. In fact, managed provider components themselves are divided into two group classifications. The first group contains provider components that interface with regular data sources (System.Data.OleDb). The second group contains a provider that is finely tuned and optimized for use with SQL Server 2000 or higher (System.Data.SqlClient).
The DataView class is quite similar to a view you would use in the database. A DataView can be customized to display a subset of data from a DataTable class. This feature allows you to have two controls bound to the same DataTable object but showing a different subset of data. You can also apply filtering and sorting rules against the data rows without altering the actual data itself. For example, you can configure a DataView to only show rows that have been deleted from a DataTable.
The DataSet class is very similar to the old Recordset class that existed in ADO, except it can hold multiple tables of data. The DataSet class also has the ability to define internal constraints and relationships, as well as enforcing them. DataSet serves as a storage container for data traveling to and from the database.
In addition to database usage, you can also use a DataSet to load and manipulate XML data. Microsoft recognizes that the industry has largely embraced the use of XML for cross-platform communication, and so it has built a number of classes to work with XML data (including the DataSet class).
You can access the XML functionality of the DataSet class with the ReadXml(), WriteXml(), and GetXml() methods.
There are two group classifications for managed provider components: one to communicate with regular data sources and one that is optimized for communication with SQL Server 2000 and higher. All of these providers comply with the standards defined in the System.Data.Common namespace.
The first component is the connection object. Just like ADO, this object manages the connection string and connection state. This object still has the usual Open() and Close() methods. There is now a BeginTransaction() method that is used to control a database transaction. The regular group has the OleDbConnection, while the optimized SQL Server provider is SqlConnection.
The next component is the command object. This object serves as the transfer pipe for the data. You can execute queries that do not return any rows (using the ExecuteNonQuery() method), execute a query that returns a single value like an ID (using the ExecuteScalar() method), or execute a query that returns a data reader (using the ExecuteReader() method). The regular group has the OleDbCommand, while the optimized SQL Server provider is SqlCommand.
Another component is the data reader object. This object associates itself with a data stream from the command object and provides a mechanism to perform forward-only reading. This method is very efficient, but intensive queries should be avoided since this uses a server-side cursor, tying up a connection resource until it finishes. The regular group has the OleDbDataReader, while the optimized SQL Server provider is SqlDataReader.
The last component is the data adapter. This object consolidates many of the other components into this easy-to-use class. A data adapter basically uses your connection to retrieve results, and then passes the data to a DataSet, which can then be updated or displayed. If rows are changed, the DataSet can be passed back into the data adapter to be persisted into the database. You can set the SQL statements using the InsertCommand, UpdateCommand, SelectCommand, and DeleteCommand properties. The regular group has the OleDbDataAdapter, while the optimized SQL Server provider is SqlDataAdapter.
The following example shows how to select rows from an Access database file and display a message box for all the rows in SomeTextColumn.
using System;
using System.Data.OleDb;
using System.Data.Common;
using System.Windows.Forms;
string connectionString
= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:YourDB.mdb";
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbCommand command = new OleDbCommand("SELECT * FROM YourTable",
connection); connection.Open();
OleDbDataReader reader = null;
try
{
command.ExecuteReader();
while (reader.Read())
{
MessageBox.Show((string)reader["SomeTextColumn"]);
}
}
catch (OleDbException exception)
{
// ... Handle database exceptions here
}
finally
{
if (reader != null)
reader.Close();
if (connection != null)
connection.Close();
}The following example shows how to select rows from an Access database file, fill a DataSet with the rows, and display a message box for the first row in SomeTextColumn.
using System;
using System.Data;
using System.Data.OleDb;
using System.Data.Common;
using System.Windows.Forms;
string connectionString
= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:YourDB.mdb";
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM YourTable",
connection);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "YourTable");
MessageBox.Show((string)dataSet.Tables["YourTable"].Rows[0]["SomeTextColumn"]);This example only shows the select, but after any sort of editing, you can call the following method to persist your changes back to the database.
adapter.Update(dataSet);
Before showing how to load an XML document, we should first define a simple XML document that we can load (Books.xml).
<?xml version='1.0'?>
<!— This file represents a fragment of a book store inventory database —>
<bookstore>
<book genre="autobiography" publicationdate="1981" ISBN="1-861003-11-0">
<title>The Autobiography of Benjamin Franklin</title>
<author>
<first-name>Benjamin</first-name>
<last-name>Franklin</last-name>
</author>
<price>8.99</price>
</book>
<book genre="novel" publicationdate="1967" ISBN="0-201-63361-2">
<title>The Confidence Man</title>
<author>
<first-name>Herman</first-name>
<last-name>Melville</last-name>
</author>
<price>11.99</price>
</book>
<book genre="philosophy" publicationdate="1991" ISBN="1-861001-57-6">
<title>The Gorgias</title>
<author>
<name>Plato</name>
</author>
<price>9.99</price>
</book>
</bookstore>The following example shows how to load an XML file into a DataSet and then retrieve and update node values.
using System;
using System.Data;
using System.Windows.Forms;
DataSet dataSet = new DataSet();
dataSet.ReadXml(@"C:Books.xml");
MessageBox.Show("Row Count: " + dataSet.Tables["book"].Rows.Count.ToString());
MessageBox.Show("First Author => Last Name: " +
(string)dataSet.Tables["author"].Rows[0]["last-name"]);
// Update the last name of the first author
dataSet.Tables["author"].Rows[0]["last-name"] = "Wihlidal";
// Persist the changes back out to the xml file
dataSet.WriteXml(@"C:Books.xml");The Companion Web site contains an example for this chapter that demonstrates how to use a data reader to build a simple editor. The editor is for a fictitious role-playing game, and it handles the database management of potions. You can add new potions, modify the stats of existing potions, or delete potions from the database. This editor could have been built using any number of the objects discussed throughout this chapter but was done with a data reader because of personal preference. The editor uses a simple Access database file so that you do not have to configure SQL Server to run this example.
Figure 24.1 shows the interface for the potion editor on the Companion Web site.
Many applications store data in some fashion or another, but typically, any application that processes significantly large amounts of data is using some sort of database like SQL Server. The need arises when there are complex queries to perform, or there are a number of associated entities and constraints to enforce. Database servers are optimized for this type of storage and retrieval, so developing a home-grown system will only work in certain situations. Databases also allow you to enforce security settings determining which accounts can do certain tasks, and this can aid in ensuring data integrity and thwarting data tampering.
There are some new features that have just been introduced with .NET 2.0 that are not addressed by this chapter (like the new TableAdapter). Since this chapter only serves as a quick overview of basic ADO.NET functionality, I recommend that you investigate the latest version of ADO.NET in greater detail if you are planning on doing any significant work with it.