Programming Pattern Using Typed Datasets in VS 2008
I am currently using typed datasets in vs2008:
Right click on "app_code", add a new dataset, name it tableDS.
Open tableDS, right click, add "table adapter"
In the wizard select the predefined connection string, "use SQL statements"
select * from tablename and next + next to complete. (I am creating one table adapter for each table in my DB)
In my code, I do the following to get a row of data when I only need one:
cpcDS.tbl_cpcRow tr = (cpcDS.tbl_cpcRow) (new cpcDSTableAdapters.tbl_cpcTableAdapter ()) GetData () Select ("cpcID =" + cpcID) [0]; ..
I believe this will bring the entire table from the database and filtering to dotnet (i.e. not optimal), is there a way I can force the tableadapter to set the database to the database instead (IE what I want is sending select * from tbl_cpc where cpcID = 1 to database)
And as a side note, I think this is a pretty good design pattern for getting data from a database in vs2008. It's pretty easy to code, read, and write. But I would like to know that there are other design patterns that are better out there? I am using datasets for read / update / insert and delete.
source to share
A bit of a shift, but you are asking about different patterns - what about LINQ? Since you are using VS2008, it is possible (although not guaranteed) that you can use .NET 3.5 as well.
The LINQ-to-SQL data context provides much more controllable data access (filtered, etc.). Is this an option? I'm not sure if I'll be using "Entity Framework" ( here ) at this point .
Modify query:
to get a string from the data context, you just need to specify a "predicate" - in this case, match the primary key:
int id = ... // the primary key we want to look for
using(var ctx = new MydataContext()) {
SomeType record = ctx.SomeTable.Single(x => x.SomeColumn == id);
//... etc
// ctx.SubmitChanges(); // to commit any updates
}
The use of Single above is intentional - this particular use of [Single (predicate)] allows the data context to make full use of local data in memory - that is, if the predicate is only in the primary key columns, it may not need to touch the database at all if the data context has already seen this entry.
LINQ, however, is very flexible; you can also use "query syntax" - for example, a slightly different (list) query:
var myOrders = from row in ctx.Orders
where row.CustomerID = id && row.IsActive
orderby row.OrderDate
select row;
etc.
source to share
There are two potential problems with using typed datasets,
one is testability. This is a pretty tricky job of setting up the objects you want to unit test when using typed datasets.
The other is maintainability. Using typed datasets is usually a symptom of a deeper problem, my guess is that all of your business rules live outside of datasets, and some of the few that take data as input and output some aggregate values based on it. This leads to business logic flowing all over the place, and while this will all be fair in the first 6 months, it will start to bite you after a while. This use of DataSets is fundamentally non-object oriented.
Thus, it is perfectly possible to have a sane architecture using datasets, but this does not happen naturally. The ORM will be harder to set up initially, but will do its best to write maintainable and testable code, so you don't have to look back at the mess you made 6 months later.
source to share
You can add a where clause query to the tableadapter for the table of interest.
LINQ is good, but it's really just a syntax shortcut to what the OP is already doing.
Typed datasets make sense if your data model is not very complex. Then writing your own ORM would be the best choice. I'm a little confused that Andreas thinks that typed datasets are difficult to maintain. The only annoying thing about them is that the insert, update, and delete commands are removed whenever the select command changes.
In addition, the speed advantage of creating a typed dataset over your own ORM allows you to focus on the application itself, rather than the data access code.