Strong Typed Dataset best practices

[Fernando Chilvarguer]

I'm new to ADO.NET 2.0 so I have some questions you may be able to help me
with.

1. I decided to take advantage of Strong Typed datasets. Everything worked
but I did not really like the way I had to deal with the TableAdapters. So,
I went ahead and did NOT use the TableAdapters provided by the wizards in
VS2005.
So, my code now looks something like this:

SERVER CODE (class library):

//My typed dataset is named CompanyDS
CompanyDS dataset = new CompanyDS();
string connectionString =
ConfigurationManager.ConnectionStrings["dataBaseConnection"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connectionString))
{
// Create the command and set its properties.
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = "sp_SelectCompany";
command.CommandType = CommandType.StoredProcedure;
// Add the input parameter and set its properties.
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@CompanyID";
parameter.SqlDbType = SqlDbType.Int;
parameter.Direction = ParameterDirection.Input;
parameter.Value = ID;
command.Parameters.Add(parameter);
//Create the data adapter and populate the dataset
SqlDataAdapter adapter = new SqlDataAdapter();
conn.Open();
adapter.SelectCommand = command;
adapter.Fill(dataset,"Company");
conn.Close();
}
CLIENT CODE:
//My typed dataset is inside a class names "CompanyDAL"
CompanyDAL company = new CompanyDAL();
CompanyDS ds = company.loadCompany(1);
CompanyDS.CompanyDataTable dt = (CompanyDS.CompanyDataTable) ds.Tables[0];

MY QUESTION:
a. Is this an acceptable (best practice) way of doing this or am I way off
on this one?
b. Why do I have to cast my datatable as "CompanyDS.CompanyDataTable"? I
thought the type dataset would have typed tables of type CompanyDataTable
being returned to me.

2. Has anyone used (or use) the Data Access Application Block from Microsoft
Patterns and Practices? If so, is it really productive or is it a little too
much for simpler jobs? I know those are subjective questions, I'm just
trying to gather some input on how the community is using those things
before I dive deep into one way or another of doing things in 2.0

Thanks,
Fernando

 

[Miha Markic [MVP C#]]

Hi Fernando,

CLIENT CODE:
//My typed dataset is inside a class names "CompanyDAL"
CompanyDAL company = new CompanyDAL();
CompanyDS ds = company.loadCompany(1);
CompanyDS.CompanyDataTable dt = (CompanyDS.CompanyDataTable) ds.Tables[0];

MY QUESTION:
a. Is this an acceptable (best practice) way of doing this or am I way off
on this one?

Yes, why not. You could use table directly (without dataset) if you are
transfering only one table.

b. Why do I have to cast my datatable as "CompanyDS.CompanyDataTable"? I
thought the type dataset would have typed tables of type CompanyDataTable
being returned to me.

You should have a strongtyped property ds.Company that does the casting for
you.

2. Has anyone used (or use) the Data Access Application Block from
Microsoft Patterns and Practices? If so, is it really productive or is it
a little too much for simpler jobs? I know those are subjective questions,
I'm just trying to gather some input on how the community is using those
things before I dive deep into one way or another of doing things in 2.0

I prefer doing ado.net code by myself (perhaps using a template code
generator such as CodeSmith)

 

[Cowboy \(Gregory A. Beamer\)]

I'm new to ADO.NET 2.0 so I have some questions you may be able to help me
with.

1. I decided to take advantage of Strong Typed datasets. Everything worked
but I did not really like the way I had to deal with the TableAdapters.
So, I went ahead and did NOT use the TableAdapters provided by the wizards
in VS2005.
So, my code now looks something like this:

SERVER CODE (class library):

//My typed dataset is named CompanyDS
CompanyDS dataset = new CompanyDS();
string connectionString =
ConfigurationManager.ConnectionStrings["dataBaseConnection"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connectionString))
{
// Create the command and set its properties.
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = "sp_SelectCompany";
command.CommandType = CommandType.StoredProcedure;
// Add the input parameter and set its properties.
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@CompanyID";
parameter.SqlDbType = SqlDbType.Int;
parameter.Direction = ParameterDirection.Input;
parameter.Value = ID;
command.Parameters.Add(parameter);
//Create the data adapter and populate the dataset
SqlDataAdapter adapter = new SqlDataAdapter();
conn.Open();
adapter.SelectCommand = command;
adapter.Fill(dataset,"Company");
conn.Close();
}
CLIENT CODE:
//My typed dataset is inside a class names "CompanyDAL"
CompanyDAL company = new CompanyDAL();
CompanyDS ds = company.loadCompany(1);
CompanyDS.CompanyDataTable dt = (CompanyDS.CompanyDataTable) ds.Tables[0];

MY QUESTION:
a. Is this an acceptable (best practice) way of doing this or am I way off
on this one?

The TableAdapters are considered the "best practice" and are quite scalable
if you use them correctly. Scott Guthrie's blog has plenty of examples that
are quite nice. You method is fine; it is more of a 1.1 style load, but
there is nothing inherently wrong with it. I would consider rebuilding
without the table adapters if you are not going to use them (delete out the
code is an option). They do not hurt anything, but they are just extra stuff
to compile in your example.

b. Why do I have to cast my datatable as "CompanyDS.CompanyDataTable"? I
thought the type dataset would have typed tables of type CompanyDataTable
being returned to me.

I do not see the method signature for loadCompany. To use the
CompanyDataSet, it is:

public CompanyDS loadCompany(int ID)
{
}

If you have it as DataSet, you have to recast when you use it to get the
strong typing.

2. Has anyone used (or use) the Data Access Application Block from
Microsoft Patterns and Practices? If so, is it really productive or is it
a little too much for simpler jobs? I know those are subjective questions,
I'm just trying to gather some input on how the community is using those
things before I dive deep into one way or another of doing things in 2.0

In 2.0, use the Enterprise Library for 2.0 instead:
http://www.microsoft.com/downloads/...70-406b-4f2a-b723-97ba84ae80b5&DisplayLang=en

It allows a quick switch on Database providers. The downside of most of this
stuff (if it is a downside to you) is the fact they all use configuration
off of the <connectionStrings> section of the web.config. As it can be
ecnrypted, it is not as big of a deal as it could be.


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside of the box!
*************************************************