Data Access Using ADO.NET

ADO.NET is the standard way to access data in databases in Microsoft .NET. However, the framework is extensible, and, as you’ll see later in this chapter, ADO.NET can actually do a whole lot more if you’re willing to put in the effort.

Whichever data provider you use, whether it’s one of the four supplied in the framework (SQL Server, OLE DB, Oracle, and ODBC) or another, you’re likely to get at your data using a DataReader or DataSet object. Both have advantages and disadvantages. We’ll look at using both of these technologies in conjunction with Web services.

DataReader Objects

DataReader objects are the simplest and quickest way to get at your data. You typically use a DataReader object in the following way:

1. Open a connection to a database.

2. Execute a command to obtain a reader.

3. Use the reader to extract the results of the command, one row at a time.

4. Close the connection.

In code it looks like this:

SqlConnection conn = new SqlConnection(ConnectionString); SqlCommand cmd = new SqlCommand(CommandText, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default); while (reader.Read()) {     // extract data from reader. } conn.Close();

Here we use the SQL Server provider for illustrative purposes, but the principles are the same for the OLE DB provider and other .NET data providers. The command you execute (CommandText, in the previous code) might be a SQL SELECT statement, a stored procedure, or something provider-specific. Obviously, if you’re executing a command that doesn’t return data (perhaps a simple DELETE statement), you don’t need to use a DataReader, but when you’re reading data from a database, you do.

When you’re exposing data for read-only access, DataReader objects are extremely powerful. For one thing, they enable you to access data up to four times faster than when you use a DataSet object. They are also great at repackaging data into whatever form you want. For example, let’s say you have a Web method that obtains title and price data about a list of books. Rather than returning a DataSet object, which (as you’ve seen in previous chapters) can result in a lot of superfluous information, you can return an array of classes (or structures). A suitable Book class is shown here:

public class Book {     private string title;     private float price;     public Book()     {     }     public string Title     {         get         {             return title;         }         set         {             title = value;         }     }     public float Price     {         get         {             return price;         }         set         {             price = value;         }     } }

Packaging data into an array of this type (Book) is simple. The following Web method takes data from the SQL Server sample database called pubs. The actual data isn’t that important because we’re primarily concerned with the code here. The only point worth noting is that the NULL values in the Price column necessitate a check for DBNull values—hence the additional code in the while loop.

[WebMethod] public Book[] GetBooks() {     SqlConnection conn =         new SqlConnection("Server=SQL Server Name;User=sa;DataBase=pubs");     SqlCommand cmd = new SqlCommand("SELECT Title, Price FROM Titles",          conn);     conn.Open();     SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);     ArrayList booksToReturn = new ArrayList();         int index = 0;     while (reader.Read())     {         Book newBook = new Book();         newBook.Title = reader["Title"] as string;         if (reader["Price"].GetType() != typeof(DBNull))         {             newBook.Price = Convert.ToSingle(reader["Price"]);         }         else         {             newBook.Price = -1;         }         booksToReturn.Add(newBook);         index++;     }     conn.Close();     return booksToReturn.ToArray(typeof(Book)) as Book[]; }

Note

Using an ArrayList object in this way isn’t always the most efficient approach because this class resizes itself as necessary, copying objects to a larger array when it needs to. It might be more efficient to first find out how many records will be returned by the query, perhaps via the SQL Count function. You can then initialize an array of the right size before reading data. However, this work should really be done as part of a transaction because the data might change between the request to find out how many records there are and the request to read them. For our purposes here, we’ll ignore the performance hit of the ArrayList object, which will be far less than the performance hit of passing data over the Internet via a Web service anyway.

The SOAP returned by this Web method contains XML like this:

<ArrayOfBook ...>   <Book>     <Title>The Busy Executive's Database Guide</Title>     <Price>19.99</Price>   </Book>   <Book>     <Title>Cooking with Computers: Surreptitious Balance Sheets</Title>     <Price>11.95</Price>   </Book>   ⋮ </ArrayOfBook>

This XML can be interpreted easily by any client, regardless of what language is used. .NET makes it easy to read the data straight into a Book array form, but this isn’t a necessity.

Another advantage of using an array of objects like this is that you can easily modify the XML that’s generated—using XML serialization attributes, for example:

public class Book {     ⋮     [XmlAttribute]     public string Title     {         ⋮     }     [XmlAttribute]     public float Price     {         ⋮     } }

This simple modification has an immediate and dramatic effect on the XML generated:

<ArrayOfBook ...>   <Book Title="The Busy Executive's Database Guide"         Price="19.99" />   <Book Title="Cooking with Computers: Surreptitious Balance Sheets"         Price="11.95" />   ⋮ </ArrayOfBook>

Applying XML serialization attributes in this way can make it even easier for disparate clients to consume data.

So, to summarize, DataReader objects are great for speedy read-only access of data, and they make it easy to obtain data in a variety of formats for use by clients. However, in some situations, particularly when you’re dealing with “fat” .NET clients, DataSet objects are the way to go, as you’ll see in the next section.

DataSet Objects

The other way to extract data from a database via ADO.NET is to use a DataSet object. (Admittedly, you can get scalar results using a Command object, and DataSet objects use DataReader objects internally, but in essence that statement is true.) DataSet objects are fairly heavyweight beasts, exposing far more functionality than you might need, but they certainly have their uses. In particular, when you’re dealing with .NET clients of Web services, the concept of a “disconnected data set” can be extremely useful. Clients can obtain a data set, bind it to a DataGrid or other bindable control, edit data quickly and easily, and inform a Web service of the changes—all with relatively simple code. DataSet objects include members that deal with concurrency, too, so often you can make changes to the data source intelligently. Of course, having disconnected DataSet objects taken from a database that changes rapidly has its own problems—the client-side copy of the data might go out of date quickly—but these problems are surmountable.

The decision to use a DataSet object, though, should never be taken lightly, particularly when Web services are involved. Often a combination of DataReader objects and Command objects that access stored procedures is preferable (in particular, as noted above, when you can’t rely on clients being .NET clients).

The basic code for using a DataSet object is as follows:

SqlConnection conn = new SqlConnection(ConnectionString); SqlDataAdapter adapter = new SqlDataAdapter(SelectCommand, conn); DataSet ds = new DataSet(); conn.Open(); adapter.Fill(ds, TableToFill); conn.Close();

Here TableToFill is the name of the table in ds that will contain the result of the query. By default, the name will be something like Table1, so it’s usually worth being explicit here, to make it easier to reference our data.

Subsequent code can examine and modify data, although you can’t transmit changes back to the database without supplying insert, update, and delete commands to the DataAdapter object. One way of doing this is to use the SqlCommandBuilder class as follows:

SqlConnection conn = new SqlConnection(ConnectionString); SqlDataAdapter adapter = new SqlDataAdapter(SelectCommand, conn); SqlCommandBuilder builder = new SqlCommandBuilder(adapter); DataSet ds = new DataSet(); conn.Open(); adapter.Fill(ds, TableToFill); conn.Close();

This procedure works fine and allows you to use code as follows:

conn.Open(); adapter.Update(ds, TableToUpdateFrom); conn.Close();

However, the SqlCommandBuilder class is far from perfect. For starters, it works only if the string for the SelectCommand property of the data adapter returns primary key data. This isn’t such a problem—the SelectCommand string usually does this. What’s worse is the format of the commands generated. For example, our command builder generates the following DeleteCommand string:

DELETE FROM Titles WHERE  ( (title_id = @p1)  AND ((@p2 = 1 AND Title IS NULL) OR (Title = @p3))  AND ((@p4 = 1 AND Price IS NULL) OR (Price = @p5)) )

Although this command works fine, the following would suffice:

DELETE FROM Titles WHERE title_id = @p1

The UpdateCommand string that the command builder generates is of a similar form, including plenty of redundant information, although the InsertCommand string is more like what we’d expect to see.

So, you can get better performance by adding Insert, Update, and Delete commands manually, but even with their problems the command builders can be a useful shortcut in basic situations.

Briefly, then, let’s look at what happens when we return the same data as in the previous section, but using a DataSet object instead. This time, the Web method code is as follows:

[WebMethod] public DataSet GetBooks() {     SqlConnection conn =         new SqlConnection("Server=SQL Server Name;User=sa;DataBase=pubs");     SqlDataAdapter adapter =         new SqlDataAdapter("SELECT Title, Price FROM Titles", conn);     DataSet ds = new DataSet();     conn.Open();     adapter.Fill(ds, "Books");     conn.Close();     return ds; }

And here is the XML result:

<DataSet xmlns="...">   <xs:schema  xmlns=""              xmlns:xs="http://www.w3.org/2001/XMLSchema"              xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">     <xs:element name="NewDataSet" msdata:IsDataSet="true"                 msdata:Locale="en-GB">       <xs:complexType>         <xs:choice maxOccurs="unbounded">           <xs:element name="Books">             <xs:complexType>               <xs:sequence>                 <xs:element name="Title" type="xs:string" minOccurs="0" />                 <xs:element name="Price" type="xs:decimal" minOccurs="0" />               </xs:sequence>             </xs:complexType>           </xs:element>         </xs:choice>       </xs:complexType>     </xs:element>   </xs:schema>   <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"                    xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">     <NewDataSet xmlns="">       <Books diffgr: msdata:rowOrder="0">         <Title>The Busy Executive's Database Guide</Title>         <Price>19.9900</Price>       </Books>       <Books diffgr: msdata:rowOrder="1">         <Title>Cooking with Computers: Surreptitious Balance Sheets</Title>         <Price>11.9500</Price>       </Books>       ⋮     </NewDataSet>   </diffgr:diffgram> </DataSet>

There’s nothing wrong with this XML, but it does seem awfully lengthy, especially compared to the XML in the previous section. This data includes schema information for the DataSet object and full contents with DiffGram information (as described in the next section), which is fine if we want to reconstitute a DataSet object on the client side, but it’s overkill if all we want is the data!

The way DataSet objects (and other behavioral types) are serialized is impossible to describe using WSDL. This has implications when you’re using non-.NET clients, which might rely completely on WSDL to generate types to hold returned data. In such cases, it might be necessary to create your own type based on the .NET DataSet class, which can be counterproductive.

Finally, before we look at DiffGrams in more detail, it’s worth pointing out that you do have some control over the XML generated when a DataSet object is serialized via the ColumnMapping property of each column. As you can see in the following example, you can set this property to a value from the MappingType enumeration, effectively customizing the XML as we did in the last section with XML serialization attributes:

[WebMethod] public DataSet GetBooks() {     SqlConnection conn =         new SqlConnection("Server=SQL Server Name;User=sa;DataBase=pubs");     SqlDataAdapter adapter =         new SqlDataAdapter("SELECT Title, Price FROM Titles", conn);     DataSet ds = new DataSet();     conn.Open();     adapter.Fill(ds, "Books");     conn.Close();     ds.Tables[0].Columns[0].ColumnMapping = MappingType.Attribute;     ds.Tables[0].Columns[1].ColumnMapping = MappingType.Attribute;     return ds; }

This code changes the XML as follows:

    ⋮     <NewDataSet xmlns="">       <Books diffgr: msdata:rowOrder="0"              Title="The Busy Executive's Database Guide"              Price="19.9900" />       <Books diffgr: msdata:rowOrder="1"              Title="Cooking with Computers: Surreptitious Balance Sheets"              Price="11.9500" />       ⋮     </NewDataSet>     ⋮ 

We don’t have as much control here—we can’t make as many kinds of modifications as we can with XML serialization attributes. Also, when we send DataSet objects via a Web service, we typically expect the client to understand what a DataSet object is and be able to use it, so one could argue that modifying the intermediate exchange XML isn’t that useful. This functionality of DataSet classes is more useful for reading and writing data from XML files, where specific schema information must be adhered to.

DiffGrams

The .NET Framework uses DiffGrams to communicate modifications made to DataSet objects so that you can update data sources without having to send all the data in a DataSet object back to the server. Instead, you just need information concerning which data has changed, which decreases the volume of information transmitted.

DataSet objects maintain DiffGram information without any intervention on your part. However, to use them effectively with Web services, you must use them explicitly because, by default, sending a modified DataSet object to a Web service not only includes DiffGram information, but it also includes all the information in the DataSet object. To send just the DiffGram information, you must use the GetChanges method of the DataSet object. Consider the following simple console code:

SqlConnection conn =     new SqlConnection("Server=SQL Server Name;User=sa;DataBase=pubs"); SqlDataAdapter adapter =     new SqlDataAdapter("SELECT title_id, Title, Price FROM Titles",conn); SqlCommandBuilder builder = new SqlCommandBuilder(adapter); DataSet ds = new DataSet(); conn.Open(); adapter.Fill(ds, "Books"); conn.Close(); DataRow dr = ds.Tables[0].NewRow(); dr["title_id"] = 99; dr["Title"] = "A new book about fishing."; dr["Price"] = 33.33; ds.Tables[0].Rows.Add(dr); ds.WriteXml(Console.Out, XmlWriteMode.DiffGram);

This code takes a DataSet object, adds data to it, and then writes out the XML representation of the object using the XmlWriteMode.DiffGram mode, which is exactly what happens if you pass a DataSet to or from a Web method (but without being wrapped in other XML, including the DataSet schema). We could of course do this via a Web service, but this code is simpler and does what we want.

The XML displayed by this code is as follows:

<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"                  xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">   <NewDataSet>     <Books diffgr: msdata:rowOrder="0">       <title_id>BU1032</title_id>       <Title>The Busy Executive's Database Guide</Title>       <Price>19.9900</Price>     </Books>     ⋮     <Books diffgr: msdata:rowOrder="18"            diffgr:hasChanges="inserted">       <title_id>99</title_id>       <Title>A new book about fishing.</Title>       <Price>33.33</Price>     </Books>   </NewDataSet> </diffgr:diffgram>

Here we see every record in the DataSet object displayed, including the new one. However, you can see that this is a modified DataSet object—the last record, which we just added, has the diffgr:hasChanges="inserted" attribute.

Alternatively, if we use the GetChanges method, as follows, we get just the XML for the new row in the <diffgr:diffgram> element. We also get any other changes, such as updated or deleted rows.

ds.GetChanges().WriteXml(Console.Out, XmlWriteMode.DiffGram);

What this means is that our client-side code shouldn’t use code such as this:

myWebService.UpdateData(myUpdatedDataSet);

Instead, we should use code like this:

myWebService.UpdateData(myUpdatedDataSet.GetChanges());

This won’t affect the processing required by the Web service because only the changes are required to update data, but it sends a lot less XML down the wire. However, making this change can speed things up a great deal if you’re dealing with large amounts of data—this is especially important in asymmetric bandwidth situations, where minimizing data sent to a Web service is crucial.

Typed DataSet Objects

You can use typed DataSet objects to create strongly typed classes, which make data access easier. An XSD schema is examined and used to create the code for a class that inherits from the DataSet class, and that includes various properties and methods that simplify the code dealing with data. For example, rather than having a plain old DataRow object and having to use code such as this:

string myName = myDataRow["Name"] as string;

we can use this code instead:

string myName = myTypedDataRow.Name;

This approach makes the code much easier to work with and is surprisingly easy to set up. If you haven’t used typed DataSet objects before, now is the time to try them out—you won’t be disappointed. To get you up and running, here’s a quick example, called TypedDataSetExample, which you can find in the chapter’s sample files.

To add a typed DataSet to a project, you simply select the DataSet type when you add a new item. This adds an .xsd schema file that appears in Solution Explorer, plus a couple of files that don’t appear there unless you have the Show All Files option selected. The reason these files don’t normally appear is that the files (a class file and a resource file) are generated automatically based on the contents of the schema.

Microsoft Visual Studio .NET includes a graphical editor for typed DataSet schema files, which includes—and this is the bit I really like—the ability to drag and drop tables (and even views) from Server Explorer to create the required schema information for your data source.

This approach is used in the TypedDataSetExample, which has a typed DataSet named CDDBDataSet generated from the Records table in DansRecords.mdb. The schema is shown graphically, as shown in Figure 9-1.

Figure 9-1: Schema created from the Records table in DansRecords.mdb

This typed DataSet is used in code as follows:

OleDbConnection conn =     new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;"         + "User ID=Admin;"         + @"Data Source=C:\WSCR\dansrecords.mdb;"); OleDbDataAdapter adapter =     new OleDbDataAdapter("SELECT RecordID, Artist, Title, Genre, "         + "Compilation, Num_discs, Single_or_Album, Format, Signed, "         + "Promo, Case_Type, InStorage "         + "FROM Records WHERE Artist = 'Ezio'", conn); CDDBDataSet ds = new CDDBDataSet(); conn.Open(); adapter.Fill(ds, "Records"); conn.Close(); foreach (CDDBDataSet.RecordsRow dataRow in ds.Records) {     Console.WriteLine("Artist: '{0}' Title: '{1}'", dataRow.Artist,         dataRow.Title); } Console.WriteLine(); ds.WriteXml(Console.Out); Console.ReadLine();

The first section of output code shows how easy it is to use the typed DataSet object, including a very readable foreach format. The code simply outputs the Artist and Title fields for the two records found. Next we output the XML, which looks pretty much like the XML we’d get from the other kind of DataSet object.

In fact, the advantages of using typed DataSet objects are purely cosmetic. They’re great when you’re writing code, but when you use them with Web services the pros and cons are pretty much the same as when you use standard DataSet objects. The only real difference is that the client also has access to the new class—assuming we’re talking about a .NET client. And let’s face it, there’s not much point in using DataSet objects, standard or typed, for non-.NET clients.

ADO.NET Data Access Recommendations

In the past few sections, we’ve looked at when to use DataReader objects and when to use DataSet objects for Web service data access. However, we also have to consider what exactly we expose to clients, keeping in mind that we might not have so much control over who has access to our Web services—at least not until we cover security in later chapters. It is quite possible to give people too much control over your data, and unless you’re careful, you could lose control of the data.

One common problem, which you really should try to avoid, is exposing a Web method as shown here:

[WebMethod] public DataSet GetRecordById(string idString) {     SqlConnection conn = new SqlConnection(ConnectionString);     SqlDataAdapter adapter = new SqlDataAdapter(         "SELECT * FROM MyTable WHERE Id = " + idString, conn);     DataSet ds = new DataSet();     conn.Open();     adapter.Fill(ds, "Records");     conn.Close();     return ds; }

This syntax leaves you wide open to what is known as a SQL Insertion (or SQL Injection) attack. Even though you expect clients to supply just an ID value here, nothing can stop them from doing something like this:

MyService.GetRecordById("REC001; DROP TABLE MyTable;");

This approach can lead to disastrous results! Now, it’s worth pointing out that this sort of thing is database management system (DBMS)–specific. Microsoft Access, for example, doesn’t allow you to execute batches of SQL statements, so it isn’t so vulnerable to this problem. SQL Server, though, does allow you to execute batches of SQL statements. So take precautions!

There are two ways around this vulnerability. First, you can parse the string, checking for dangerous SQL. However, this can be tricky (even with regular expressions) and is not recommended. The other, far easier, way is to use parameterized commands. The previous code can be rewritten as follows:

[WebMethod] public DataSet GetRecordById(string idString) {     SqlConnection conn = new SqlConnection(ConnectionString);     SqlDataAdapter adapter =         new SqlDataAdapter("SELECT * FROM MyTable WHERE Id = @id", conn);     adapter.SelectCommand.Parameters.Add("@id", SqlDbType.VarChar);     adapter.SelectCommand.Parameters["@id"].Value = idString;     DataSet ds = new DataSet();     conn.Open();     adapter.Fill(ds, "Records");     conn.Close();     return ds; }

This means the parameter will be automatically parsed, and you are shielded from extra SQL.

Because it’s best to use commands, I also recommend that you use stored procedures (if available) as well. In fact, I advocate stored procedures for every action taken on a database—and I’m not alone in making this recommendation. Apart from not being vulnerable to SQL Insertion attacks, stored procedures have other advantages. DBMSs tend to be optimized for stored procedures, which are compiled inside the DBMS and will run faster. They also make your code easier to understand—less SQL text is distributed among your code—and provide a centralized location for SQL statement modification. Rather than having to change multiple SQL statements every time you modify a commonly used select command, all you do is change it in one place.

To round off this section, then, here’s a summary of recommendations for using ADO.NET in Web services:

• Where possible, use DataReader objects for better performance.

• Repackage data into arrays of simple types to cut down on the volume of XML transmitted and make it easier for non-.NET clients to use your Web methods.

• When you use DataSet objects, use the GetChanges method on the client to avoid sending data that isn’t required to the Web service.

• In situations where you might use DataSet objects, use typed DataSet objects. The extra work you put in to create them will pay you back a hundredfold when you come to use them.

• Be sure that your data access code is SQL Insertion–proof.

• Always use stored procedures if you can.

If you follow all this advice, your data access Web service will be a much happier data access Web service, and your data will thank you for it.