Parent-Child data in ASP.NET C#

HungryHippos

HungryHippos

HungryHippos

Soldato
Joined
25 Mar 2004
Posts
16,007
Location
Fareham
Hi all,

I've some basic Parent-Child data I want to work with for updates etc.

Am coding in Visual Studio 2010 using ASP.NET C# as my language of choice.

My DB in production is using MS SQL 2008 though I am messing around with a test version of MS SQL 2005 at the moment to house my test data.

Please see this image for the table layout, which shows how evrerything relates up:

nBUNs.png


The Subscriptions table is the 'Parent' table, the exch_mailboxes table is the 'Child' Element.

What I would like is for people to be able to search the Parent element for a subscription, once found they can click into it and then be presented with the child elements, or all the records from the exch_mailboxes that meet the criteria for an inner join across the tables.

Wondering what the best approach is for this?

I have some experience with throwing in SQL TableAdapters into a dataset.xsd page and then calling this into GridView, but updating the data is going to prove a bit trickier I would expect.

Any pointers welcome.
 
I would personally use Entity Framework. You can then use linq statements to access whatever data you need. Updating data in the database becomes a trivial task.
 
As above, use MEF or NHibernate (basically, use any decent Object Relational Mapper) and it becomes a non-issue :)

Don't use LinqToSQL or I will cause you physical* harm!


*well ok, just virtual harm
 
Whilst PetaPoco has it's place for fast data-access, it is blown out of the water by nhibernate with fluent tbh. It's nothing more than a re-drawn DAO/ADO dll. :)

NHibernate is much, much more than that.

I also heavily dislike data-access "things" that require I decorate my classes. Persistence ignorance is a rule that should always be applied imo.
 
Last edited:
don't you decorate your entity's when using nHibernate, we do by using a modified version of sharparch which gives base entity, entitywithcompositeid etc type classes and also attributes for validation and uniqueness.
 
Dj_Jestar said:
Whilst PetaPoco has it's place for fast data-access, it is blown out of the water by nhibernate with fluent tbh. It's nothing more than a re-drawn DAO/ADO dll. :)

NHibernate is much, much more than that.

I also heavily dislike data-access "things" that require I decorate my classes. Persistence ignorance is a rule that should always be applied imo.
Click to expand...

I saw this nHibernate mentioned in a job spec lately - can you give a beginners explanation of what it does?
 
It's a framework that helps you communicate with a database using objects, it maps the sql data to your objects for you and can also help out with the actual t-sql queries. You can query with linq for simpler queries using std objects and it will do all the heavy lifting.

It can get more complex than this as well by providing support for level 1 and 2 caching, also it's fairly easy to batch up several command style operations such as updates/inserts under one transaction which is very handy for grouping up more complex business logic.
 
Last edited:
Ok. I'm currently working on a project where we have a mid tier layer with all the insert/update/deletes etc and all database stuff. Is the architecture completely different when using an ORM? Does it cut out the need for a complex mid tier?
 
You could replace some of this mid layer with an orm layer. It really depends on how complex your solution is really. You can go full CQRS to 3 tier to a simple 2 tier. The orm helps as it's a framework but you can use it in several different ways.
 
ringo747 said:
Ok. I'm currently working on a project where we have a mid tier layer with all the insert/update/deletes etc and all database stuff. Is the architecture completely different when using an ORM? Does it cut out the need for a complex mid tier?
Click to expand...

I wouldn't say so.
What it does give you is the ability to utilise a proper domain model more easily and effectively.
The ORM then tracks what has changed on each of the objects in a graph and issues the appropriate inserts/updates/deletes based solely on the state of your objects.

There are limitations though. I'm currently working on a project that has a lot of data warehouse features and many of the tables are modelled as Type 2 Slowly Changing Dimensions
http://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2

NHibernate doesn't deal with that particularly well, so we've had to come up with alternatives for accessing data there.

ringo747 said:
Does the ORM handle all the DB connection stuff - if you wanted to point an application at different DBs easily can you just update mappings?
Click to expand...


Pretty much, yes - though, again there are some limitations.
NHibernate has drivers and SQL dialects for the different databases it supports and there are some differences between what is supported on some different databases.
For instance, you can do multi-queries in SQL Server (batching up multiple queries in one database round trip) but that's not supported running against an Oracle database.
The majority of stuff should work though and it would likely be a whole lot less effort that trying to convert from SQL Server to Oracle if you were using stored procs, or had SQL in the application code.
 
Last edited:
I'm far from being a pro as I've just done a few years with ASPX so sorry for the newb questions! Is there any syntax differences when using something like this or do you just call nHibernate classes/methods etc?
 
NHibernate has a session object, which is the thing you'll use for the most part when doing stuff.
To query data there are a few options, the one I prefer is QueryOver, which provides a LINQ like syntax to do queries.

Something like
Code: 
session.QueryOver<YourType>().Where(x => x.PropertyName == "PropertyValue");

Would translate to
Code: 
SELECT *
FROM TableName
WHERE PropertyName = 'PropertyValue'

But you get the advantages of strong typing in the .NET code.
This is obviously a pretty simple example, but should give you an idea.
If you're interested there are some links on the NHibernate home page that you should go through
http://nhforge.org/Default.aspx
 
Yep to take it further there are several querying options for nHibernate now including linq, HQL (nHibernates take on sql) and t-sql. One of the things you quickly find when using nHibernate is that you'll be swapping between them in different situations. The linq provider is pretty good but it can't do more complex operations. Hql is also very powerful but you need to learn it's differences to plain t-sql. You can fall right back to t-sql but then you have issues of having column names in your query.

nHibernate can also do some funky queries if your not very careful with your queries and mappings as in it's fairly easy to get it to do n+1 selects and so on. There's some handy programs to help work with it like nhprof which shows you the exact queries that are being generated. It's also very slow if used in a direct query layer and loading up larger result sets such as 100k records for say locations/postcodes. It really chokes compared to a simple sproc datareader approach.

I think this is the main issue with it, you really need to learn it well to know what to use it for and how to use it properly otherwise you can really kill performance.
 
Can do the above with just Linq<>() now that NHibernate supports linq natively. :)

DanF: No need to decorate classes. Use an nhibernate.hbm.xml file to describe mappings, or better yet, with Fluent NHibernate create a ClassMap<> like this one:

Code: 
public class MyObjectMapping : ClassMap<MyObject> 
{
  public MyObjectMapping()
  {
    Id(x => x.Id).GeneratedBy.Guid();
    Map(x => x.SomeProp).Column("ColumnName");
  }
}
etc.

EDIT: We use Linq<>() for very complex queries and haven't hit any problems. If you could elaborate DanF, I could help?
 
Last edited:
Dj_Jestar said:
Can do the above with just Linq<>() now that NHibernate supports linq natively. :)
Click to expand...

Not heard of that before, is it new in 3.2?
I know they had Session.Query<>() for a while when going to version 3, but I thought that QueryOver was the preferred way of doing things as it's got NHibernate specific things on it rather than just relying on LINQ methods.
 
I'll have to look into this a bit more. All over my head at the moment but I'll work it out.

When using all this Linq stuff are you effectively having queries built into your code? On the SQL side are Stored Procs totally unncessary? If you need to change a query does that mean recompiling code? Surely not.
 
You must log in or register to reply here.
Back
Top Bottom