Dataset and Datatable - Differences ??

[venerable bede]

I know this is going to sound naive but I am having a lot of trouble getting my head around ado.net.

Is this right ?

A dataset is a collection of tables and relationships
A table is a collection of rows and columns

===================================

Why is it that in some examples I find people bind controls to datasets AND sometimes tables ?

What are the advantages using each approach ?

===================================

If I fill a table using a dataadaptor (no dataset) and add a row can I reflect those changes back into the database ?

Thanks

[Lethal]

Your definition is correct and the datset also contains metadata about its contents. I typically do not use dataset, rather I create my own custom collection objects. In a Win32 application, databinding allows two-way binding, so you can persist your changes back to the datasource. In a web environment, databinding is one way. If you think about it, this is because the web is a stateless environment. This is a pretty big topic, so if you have a specific question you need answered, fire away.

[Magiaus]

Your definition is correct and the datset also contains metadata about its contents. I typically do not use dataset, rather I create my own custom collection objects. In a Win32 application, databinding allows two-way binding, so you can persist your changes back to the datasource. In a web environment, databinding is one way. If you think about it, this is because the web is a stateless environment. This is a pretty big topic, so if you have a specific question you need answered, fire away.

Why do you use your own custom collections? You know versus a datatable, dataset, datareader, array, ..... IEnumurable.....?

[hellswraith]

I also use my own custom collections. The reason to do this instead of using arraylists, datasets/datatables, etc. is that I can strongly type my collections. No chances that I will screw anything up in that department. Plus, because it is strongly typed, I then can do better databinding if I wish. Imagine this:

You have a list box. You want to bind a set of users to the listbox. The requirement is to show the user name in the list box as Lastname, Firstname.

Now, you could create a custom procedure in the database to do this, but then your procedure would be tied only to this instance of code that uses it. Seems like a waste since there are other things we do with user data in our application, and we already have a select stored procedure written. Plus you would be working with a datatable most likely and have to specify database column names to bind to in the UI. That is bad design.

Since I am working in a object oriented fashon, I have a user object already that exposes a FirstName property as well as a LastName property. It has a load method that calls the more generic select procedure to load the properties of the object from the database. I can easily add a property to this object called something like WholeName that returns the concated name. I bind to this property super easily, and the end result was an additional 2-3 lines of code.

If I wouldn't have done this, then I would have had the stored procedure code and any other plummng to get that returned dataset binded to the control.

I don't know if that makes sense. It is harder for me to explain than to just show you. Maybe sometime I will do up a full blown example on this subject.

[Lethal]

Hellswraith pretty much summed it up..

[Magiaus]

I understand that perfectly because I used to program in that fashion in windows based data programming. I like it. But suppose I have written a class SqlProcedure that exposes static methods such as:

ToDataReader(string procedure, string connection)
ToDataReader(string procedure, string connection, SqlParameter param)
ToDataReader(string procedure, string connection, SqlParameter[] param)

I also have ToDataSet, ToDataTable, ExecuteNonQuery, ExecuteScalar and umm XMlReader is coming soon. I also have a version of this that takes only sequel. I use that one usual for test my sql before I make the stored procedure.

Wait n/m. I see. Your saying that instead of multi select statements gather the data fill the properties and then you can add extender properties(in a sense) that further manage the data. Also so you could have Update/Delete functions.

So. If I understand this properly we have: a user class that serves as a cache for user information stored in the database, UserCollection class which fills it's self with data filled User classes. You would then specify DataSource = UserCollection DataMember = LastNameFirst. The Control loops through accessing the property.

I see the point but I want an app to make all those redundant classes for me. After creating all 20 tables it seems redundant to create them another 20 times.?

I was talking with a guy that programs in ASP and showing him some of the things I do in ASP.Net and he said it was overly complex and I haven't add strong typed data classes and collections though I did consider it(the recreating the table thing is really boring to me).

Using the strong typed collection how do you handle inserts and joining tables? Would the related data be a child class/collection of the owner datas class?

As I say, I see the power but what if Bob the new guy has to add 4 colums to the UserInformation Table?
A. Would he be expected to know to check the class and update it(becuase on his project write up it says so)?

B. The DBA would do the actual column adding and you the .net data man would add the new colums to the class and Bob the new guy does the interface?

C. It doesn't get updated?

Who am I kidding I'm Bob the new guy and all those other guys too. I have to many hats to wear. Graphic Designer, Web Designer, DBA, Server Admin, Network Tech. Mac Tech, PC Tech, Carpenter, Data Migration Analist, Anaylist. I have to handle support calls and clients too. Damn I'm wasting time again

[hellswraith]

I understand your questions, I have been though them all.

First, I have hacked together a application that builds the classes for me based off of the database tables. This saved me a bunch of time. I am updating the application and I am making it even more powerful. When I move it to the beta stages, I will send you a email and let you try it out and tell me what you think.

Next, you REALLY need to look at the Data Access Block from Microsoft. They have already written all the SQL data access code you are saying you have built/are building. You can find it at: http://msdn.microsoft.com/library/de...ml/daab-rm.asp It is just plain awesome, and saves a bunch of coding. I am centering my tool around this access block because it is a known constant.

Last is you expose your custom collections as a property of the parent class. For example, think of these forums. You have a parent object for the thread. It has the basic information about the thread and the initial post. That thread object contains a property that exposes a collection of reply objects. Then, you do binding to these objects.

You would be suprised at how much coding you save when you standardize on some basic things. After I finish the code generator, you will see just how much time it saves. I can build about 80% of my business layer in about 15 minutes. That is just awesome. The majority of my time is spent just hooking up the UI layer to the business layer and controlling user interaction.

[hellswraith]

Originally posted by Magiaus
Who am I kidding I'm Bob the new guy and all those other guys too. I have to many hats to wear. Graphic Designer, Web Designer, DBA, Server Admin, Network Tech. Mac Tech, PC Tech, Carpenter, Data Migration Analist, Anaylist. I have to handle support calls and clients too. Damn I'm wasting time again

I hear you, I am the same way. We are a small business that does software, and I am the developer, DB Admin, Desktop Support, etc., etc... Fun. The other developer doesn't know much about the technologies we are working with....so that really sucks for me.

[Magiaus]

Very cool, and your right it is fun. I'm going to look into the sql block. I can't take the time to use it on the current project but I'm definatly going to look into it.

[Magiaus]

A collection of data object would have methods like Delete(string key)/Delete(int index) .. update and insert. These Methods would peform database and collection op but it would also have Add and Remove that only effect the collection? Of course the Delete method invoes delete on any child collection and so on.....

This is very cool. What language does your app output? VB or C#?

I'm thinking if I have a lul after this project I may make one myself for C# code. Your using schema's to get table info with right?

[hellswraith]

It is doing C# code right now. I want to be able to generate either though. That is one reason why it is being redesigned.

Each object has a Save, Load, and Inactivate method.

Save will check the ID (primary key) to see if it is a insert or an update. In the absense (spell?) of the key, it will be a insert. If the key exists, then it is an update. This is fairly easy to control and the programmer can't modify the key property directly. Only the object can set this value.

Load takes a ID and loads all the properties of the object from the database.

Inactivate may sound foreign to you right now since you are saying delete. I NEVER delete anything from the database unless there is absolutely no possible reason to keep it.

Instead, I hold a bit column called IsActive (or InActive, whatever you prefer). When a record is deleted by the user, I don't delete it in the database, I just set the IsActive bit to false. This stops me from pulling with select statments because I pull data with a where clause that says "WHERE IsActive = 1".

So the Inactivate method will go to the database and inactivate the record.

This ends up being everything I need. I am truly excited about this project. I really want to get back into it as soon as possible. I have a ton of workload right now, but soon...

[Magiaus]

IsActive makes since. I would do that if I had the server space, I'm going to setup a dirty little export/import option though.

I think I like the way you geared it better then what I was thinking, about the collection handling it. Because, at times I may not need the big fat collection just one item.

[Magiaus]

hellswraith,

this is how I'm approaching the collection. The object that populates the collection has methods Delete and Save. I'm debating on if these should actualy be protected or internal because I am exposing them on the collection so that when a widget is Deleted it is removed from the collection automaticly.

    /// <summary></summary>
    public class Widgets: System.Collections.IEnumerable
    {
        private System.Collections.ArrayList values = null;

        public System.Collections.IEnumerator GetEnumerator(){return values.GetEnumerator();}
        public System.Collections.IEnumerator GetEnumerator(int index, int count){return values.GetEnumerator(index, count);}
        
        /// <summary>
        /// 
        /// </summary>
        public Widgets()
        {
                                      //peform load data logic
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="guid"></param>
        /// <returns></returns>
        public int IndexOf(string guid)
        {
            return 0;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public int Length()
        {
            return 0;
        }
        public void Delete(int index)
        {
            ((Widget)values[index]).Delete();
            values.RemoveAt(index);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="index"></param>
        public void Save(int index)
        {
            ((Widget)values[index]).Save();
        }
    } 

do you think that is a good way to go about it? I'm sure you notice that there are no Add/Remove functions.

[techgnome]

Originally posted by hellswraith
I understand your questions, I have been though them all.

First, I have hacked together a application that builds the classes for me based off of the database tables. This saved me a bunch of time. I am updating the application and I am making it even more powerful. When I move it to the beta stages, I will send you a email and let you try it out and tell me what you think.

If it isn't too much of a big deal, I'd be interested in this as well. I'm starting my own company and the first project is a SQL DB with a VB.NET front end. This would definatly be a time saver.

As a side note, MSDN has a LOT of those application blocks, not just ADO, they are probably the one of the best things to come out of MS. They are pre-built, tested and proven to work. Can't go wrong with them except to no use them.

TG

[Lethal]

There are some free .NET O/R mapper tools out there, which basically generate your business and data tiers and handle all database communication. Try doing a google and you will find a whole bunch.

[Magiaus]

I'll do that.

I just finished the first one and bound it to a datagrid no bound columns or anything just DataSource = xx; DataBind(); and it works ok like that. Is there anyway to make the this bound at designtime? I know I can create my bound columns even if it isn't bound but I'm just wondering.

[hellswraith]

Originally posted by Lethal
There are some free .NET O/R mapper tools out there, which basically generate your business and data tiers and handle all database communication. Try doing a google and you will find a whole bunch.

Yes, this is exactly what I am doing. I am rolling my own only so it fits in better with our standards.

[Magiaus]

Not to mention they all seem to cost. I might use a free one if it was close to what I would do, but it looks like I'm going to roll my own also. Well, I might anyway.

[Magiaus]

Do you guys put a Read method on your collections? I know there is the Enumerator and if it has an Indexer you can use a for loop but I was just thinking being able to do a while(widget.Read()) would be nice instead of haveing to code the for loop if I wanted to loop through manually. I don't know though that would require a second indexer that worked off the data classes properties in somefasion. I know it would be simple enough maybe use a custom attribute on the property or something. Thoughts?

[hellswraith]

I tend to inherit from CollectionBase for my collection classes. If I am going to bind the collection to a grid, then I will also implement the IBindable interface.

Here is a sample collection class I use, but this one isn't implementing the IBindable interface.

I think the CollectionBase already implements the IEnumerable interface.

(This holds a collection of Contact objects)

using System;

namespace MyNamespace
{
	public class ContactCollection : System.Collections.CollectionBase
	{
		#region Class Constructor(s)
		public ContactCollection()
		{}
		#endregion

		#region Class Methods
		public Contact this[int index]  
		{
			get  
			{return((Contact)List[index]);}
			set  
			{List[index] = value;}
		}

		public int Add(Contact value)  
		{
			return(List.Add(value));
		}

		public int IndexOf(Contact value)  
		{
			return(List.IndexOf(value));
		}

		public void Insert(int index, Contact value)  
		{
			List.Insert(index, value);
		}

		public void Remove(Contact value)  
		{
			List.Remove(value);
		}

		public bool Contains(Contact value)  
		{
			// If value is not of type Contact, this will return false.
			return(List.Contains(value));
		}
		#endregion
	}
}

[hellswraith]

Originally posted by Magiaus
Do you guys put a Read method on your collections? I know there is the Enumerator and if it has an Indexer you can use a for loop but I was just thinking being able to do a while(widget.Read()) would be nice instead of haveing to code the for loop if I wanted to loop through manually. I don't know though that would require a second indexer that worked off the data classes properties in somefasion. I know it would be simple enough maybe use a custom attribute on the property or something. Thoughts?

I don't, because it is a collection, it isn't something that is being retrieved from the database dynamically such as the Reader does. The standard way to traverse a collection is with the foreach statement. I don't think this is wrong to do, just not something I do personnally from a stylistic approach.

[Magiaus]

completely unrelated question here but.... have you ever tried using a sting indexer and an int indexer? I've seen it in the framework but when ever I try to do it I get an error I can't recall right now.

[Magiaus]

I agree about the read method it seemed helpful possibly but also it would be more work then it's worth and that extra layer of code to handle the collection["porpname"] would slow things down a tick or two.

[DNA7433]

For a recent (ASP.NET) project I created my own basic ORM layer. It had singleton mapper classes for each table that exposed methods to find, save, and delete objects. Then the Business Layer has the objects (and collections of those objects) that hold the data. The business objects also exposed properties for collections. Say for instance you have an Employee object. It would contain properties that would reference the boss Employee object, and the subordinate employees collection.

You would use it like this:
Employee joe = EmployeeMapper.Instance.Find(23); //23 is joe's employee ID
if (joe != null)
{
dlstPeopleUnderJoe.DataSource = joe.Subordinates;
dlstPeopleUnderJoe.DataBind();

joe.Sallary = 35000;
EmployeeMapper.Instance.Save(joe);
}

All the collections were lazy loaded, and (in this case) the boss would be lazy loaded because otherwise the loading would recurse all the way to the CEO. Anyways, each mapper class had caches, so for instance, the EmployeeMapper would have a cache (Dictionary of sorts) that would relate each Employee object to it's EmployeeID value. That way when someone else requests the data for Employee with ID X, the mapper checks the cache before going to the DB. Collections were also cached, so if two people requested the list of employees under employee X, the collection is only loaded from the DB once. All the cached data is held by WeakReferences in the mappers, so if no one is holding a reference to the data, and .NET needs memory the cached data is simply removed by .NET.

The collections can be sorted using custom IComparer objects, but they can't be filtered. I'm still trying to think of a way to build this around Datasets, DataTables, Views, etc. If it could use a typed dataset behind the scenes, you could still have the clarity of joe.CurrentProjects[0].Department.HeadManager.FullName and be able to filter/sort collections.

What do you guys think? Am I crazy?

[Magiaus]

I worked on project recently and we had an ILookup for some of the tables, but only the ones that were very redundant. Like they all had an ID, a name and a description. These were all cached in a global(session) cache. None of the more complex data classes were cached and I often wondered why we didn't have a Singleton because every thing out of the cache was cloned when it came out... but we had a pretty hefty concurancy model in place.

The concurancy modal hit the DB and compared version x with version fresh to see if it was the same. If two+ fields where not the same a concurency error was thrown in the BLL I think....

What really boggled my mind was that they weren't lazyloading to start with they only lazy loaded if things seemed slow... I didn't like a lot of things about the object modal but it worked... it had four layers DAL, DLL, BLL, and the IVL( Interface Validation) DAL, and DLL were redundant and DLL was in the BLL project... I found myself having to write special case data classes for reports because the classes they had were so f'n slow... no custom collections all ArrayList... anytime I did a find I had to cast to the class because they had an abstract base class and they wouldn't let me change it so that you could hide it's FindOne with new and have it come out the correct class type....

I got really fed up one day and documented an object modal I had been planing for my next project before I started there and they were like that won't work.... and then about a month later they were like oh that would work 50 times better than this... part of what I explained was that the DA/LL should be the bottom layer and should implement the structure of the table that way the BLL can simply inherit from it... and that the DA/LL should be one layer...

It was thier first .net project though and I know I didn't do great on my first .net project... what really got to me though was that I always had to argue for 4 hrs to get permission to make an improvement and explain and re explain why I wanted to do it. Man, one guy refused to use my code, because, well I don't really know why... I had created a base report class that extended the report class and would auto format the report, check the printer capabilities and ensure security rights to the printer. In truth I had made it to fix a lot of problem that were coming up with printing the reports and it did that. Later there were things all reports had to have that were put here but this guy just refused to use the class. The joke was that I was the better programmer and he couldn't take it... I was always having to fix his code...not for him but it get would put on my plate and it wouldn't be working... so... we got along in terms of going out back to bs and smoke a cig but... he would not use my code lol

anyway I think I got off on a tangent here. I don't think your crazy lol

[DNA7433]

I had one experience where the UI would show the user a list of databases and in each database would be a table of runs or trials. For some reason the customer wanted to be able to create new databases at run-time, presumably so they could easily transfer a group of 'runs' between computers. The original UI design had a drop down for the databases and then a New button next to it for a new DB. I was in charge of creating the GUI and I told them they should take the new button out and stick an option at the bottom of the DB list like <<New Database>> and it took them a while to bite. After working on it a while I thought it would be easier on me to have a seperate button because I wanted to databind the DB list. So the same people fight back and tell me that if theres a list and a button then the user will not know whether to select a DB or create a new one. I didn't know how to respond to that!