The new Microsoft Data Access Application Block is out, and it contains some nice changes.
One of the nicest (and simplest) is the ability to fill a dataset(that may or may not be strongly typed) using the SQLHelper class.
Let’s say that I have created a strongly typed DataSet named ds.
Also, let’s say I have 3 tables in the DB that I’d like to fill my dataset from.
It’s pretty simple, actually.
SqlHelper.FillDataset(Connection String ,Command Type,SELECT statement , Dataset variable ,Table mapping array)
Connection string: You guessed it.
Command Type: Can be either Text(Select statement) or StoredProcedure(name of an SP in the database).
CommandType.TableDirect will not work with the .Net Sql client data provider.
Select Statement: You guessed it again
Dataset variable: the dataset to fill
Table mapping array: this is pretty important. You need to pass in an array containing the name of the actual table you are going to fill in your dataset, otherwise the sql helper will create a new table in your dataset named “table”, “table1”.. and so on.
So this is the code I wrote to fill my dataset:
public ApartmentDataset GetDataset()
{
ApartmentDataset ds = new ApartmentDataset();
SqlHelper.FillDataset(CON_STRING,CommandType.Text,
"select * from Apartments",ds,new string[]{"Apartments"});
SqlHelper.FillDataset(CON_STRING,CommandType.Text,
"select * from Cities",ds, new string[]{"Cities"});
SqlHelper.FillDataset(CON_STRING,CommandType.Text,
"select * from Statuses",ds, new string[]{"Statuses"});
return ds;
}
Kinda wordy, ain’t it? Can you see the pattern here?
So I created this small helper function :
private void FillDatasetTable(string tableName,DataSet ds)
{
SqlHelper.FillDataset(
CON_STRING,
CommandType.Text,
"select * from " + tableName,
ds,
new string[]{tableName});
}
So now my code for filling and returning the dataset looks and reads much better:
public ApartmentDataset GetDataset()
{
ApartmentDataset ds = new ApartmentDataset();
FillDatasetTable("Apartments",ds);
FillDatasetTable("Cities",ds);
FillDatasetTable("Statuses",ds);
return ds;
}
Update:
This just goes to show how an outside perspective can help out. Lior Rozner just sent over a much cleaner way of filling your Dataset object, filling multiple tables in one go:
public ApartmentDataset GetDataset()
{
ApartmentDataset ds = new ApartmentDataset();
SqlHelper.FillDataset(CON_STRING,CommandType.Text,
"select * from Apartments " +
"select * from Cities " +
"select * from Statuses ",
ds,
new string[]{"Apartments",
"Cities",
"Statuses"});
return ds;
}
Thanks, Lior!
Another Update:
There's a bug in the Data Access Blocks. Here's the fix!