Understanding Types, Data and a lot more
The subject of the Microsoft Data Access Application Block comes up frequently in many different contexts in some of the newsgroups I frequent. If you aren't familiar with the DAAB, it's a generic data access class written by Microsoft's Patterns and Practices group.
Well, here's a typical question:
<<Hey I am using SqlHelper and I need to convert returned dataset to a typed dataset. Any ideas how can I do this ? >>
This leads to an interesting discussion. You see, the problem is that the method he's talking about returns a System.Data.DataSet object but he's using a Typed Dataset. First, why would you want to use a Typed Dataset. Well, the better question is why wouldn't you? In most instances, you should definitely opt for Typed DataSets. Why? Among other things, Strongly typed objects resolve faster and as such, perform better. Moreoever, look at the following situation:
String FirstName = dataset.Tables[0].Rows[0]["FirstName"].ToString();
|
What if FirstName isn't the actual name of the column? You're not going to know immediately and you'll have to wait until you run your unit tests to or step through it manually to find out there's a problem. More likely though, the name may change sometime later, so code that worked could be broken, and you won't know until you run your unit tests or a customer calls complaining. If you used a Typed DataSet like this:
String FirstName = dataset.People.FirstNameColumn.ToString();
then as soon as the name was changed and you compiled, the compiler would bark because there was no column named FirstName any more.
Now, it's impotant to understand what a Typed DataSet is. If you create a Typed DataSet and go over to your Solution Explorer, then select the Show All Files option, you'll see a DataSetName.cs file. Let's take a look inside:
Your Typed DataSet's cs file should look something like this:
namespace DataSetStuff {
using System;
using System.Data;
using System.Xml;
using System.Runtime.Serialization;
[Serializable()]
[System.ComponentModel.DesignerCategoryAttribute("code")]
[System.Diagnostics.DebuggerStepThrough()]
[System.ComponentModel.ToolboxItem(true)]
public class PeopleData : DataSet {
|
So this is a DataSet object. It's a direct descendant. Everyone claims to understand inheritance today, but I see a lot of code and questions which makes me think otherwise. Ok, so since this is a DataSet, everything that you can do with a DataSet can be done to this guy, right? Well, for the most part yes. There could be members inaccessible because of their access levels, but every public method for instance, or property, is totally available.
The same thing happens when you use an Interface. If I have the IMammal interface, I might have a Breathe, method. I may have Two Lung properties. I would have a Heart Property. So in order for me to properly implement IMammal, I'd have to make sure all of those features were there. And if I can be sure they are all there, I can be sure that I can call those methods. However, while all dogs are mammals, not all mammals are dogs (I'll explain why this matters shortly).
Ok, so if my Typed DataSet is in fact a DataSet, then it has a Tables Property right? Yes. For all intents and purposes, everything you'd normally do to an untyped dataset is visible in an untyped one. So yes, you can pass it right in to a method expecting a DatSet as a parameter:
DALC.cs
#region Public Methods
public void FillTypedDataSet(DataSet ds)
{
SqlConnection cn = new SqlConnection(Connection.ConnectionString);
SqlCommand cmd = new SqlCommand("GetPeople", cn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
cmd.CommandType = CommandType.StoredProcedure;
da.Fill(ds.Tables[0]);
}
#endregion
|
Form1.cs
private void btnGetData_Click(object sender, System.EventArgs e)
{
DALC Data = new DALC();
PeopleData dataset = new PeopleData();
Data.FillTypedDataSet(dataset);
dataGrid1.DataSource = dataset;
Debug.Assert(dataset.Tables.Count == 1, "You have more or less tables than you should");
}
PeopleData.xsd
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="PeopleData" targetNamespace="http://tempuri.org/PeopleData.xsd" elementFormDefault="qualified"
attributeFormDefault="qualified" xmlns="http://tempuri.org/PeopleData.xsd" xmlns:mstns="http://tempuri.org/PeopleData.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="PeopleData" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="People">
<xs:complexType>
<xs:sequence>
<xs:element name="FirstName" type="xs:string" minOccurs="0" />
<xs:element name="LastName" type="xs:string" minOccurs="0" />
<xs:element name="DateOfBirth" type="xs:dateTime" minOccurs="0" />
<xs:element name="SSN" type="xs:string" minOccurs="0" />
<xs:element name="ConsultantID" msdata:ReadOnly="true" msdata:AutoIncrement="true" type="xs:int" />
<xs:element name="ProjectHours" type="xs:decimal" />
<xs:element name="Award" type="xs:boolean" minOccurs="0" />
xs:sequence>
xs:complexType>
xs:element>
xs:choice>
xs:complexType>
<xs:unique name="PeopleDataKey1" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:People" />
<xs:field xpath="mstns:ProjectHours" />
xs:unique>
xs:element>
xs:schema>
|
So what happens when we click out button? It works exactly like we want it to. You see, our Typed DataSet does pass the muster as a DataSet so our DALC class accepts it. It fills the table (whos' schema matches exactly) and all is good. Now, if the columns didn't match or the tables didn't, then we'd need to add another parameter or two in there for the TableMappings and ColumnMappings which would handle that for us, but in this instance, the DataSet names match the database's names, so the adapter does everything for us.
Now,when using the DAAB, there's a FillDataSet method. When you see it, you can always pass in a Typed DataSet. Note that if you don't augment it, then passing in and calling Fill on a DataSet will default to the first table in your DataSet (unless you have batched Select statements). In most instances, you'd want to specify Table and ColumnMappings just to be safe but this isn't supported out of the box. The ApplicationBlocks are great, but they aren't exactly 'complete' . Fortunately , there's nothing stopping you from augmenting them yourself.
There's also an ExecuteDataSet method that returns a DataSet. Will this allow you to declare an Untyped Dataset and then set it to the return value of ExecuteDataSet? NO! Why? Because all dogs are mammals but not all mammals are dogs. A cat for instance, has a tail but I don't. So if you were expecting a Bill but got back a Cat, what would you do with the tail? The Runtime would throw an exception if it was possible to get that far, and instead, the compiler catches it for us.
Anyway, by understanding this subtelty, you can use Typed DataSets (and IMHO, should use them whenever possible) with the DAAB or most data access layers that you've already written. You can also enhance the functionailty of the DAAB (like specifying TableMappings or ColumnMappings). At the end of the day, you can do quite a bit!