Database ponderings

[Shaggy Hiker]

Let me state up front that I am currently morally opposed to DataBound controls. I can't say that I have good reason for this, I just prefer the greater flexibility of doing things myself. However, this may cost me somewhat in ease-of-use.

In the past, when I worked on DB using programs, I generally had the luxury of knowing that only one person would be using the data at any one time. This is a great simplification, as there are no scalability issues.

Right now, I am working on some of the same types of things in .NET, though there is a larger Read-Only app looming on the work horizon. I got thinking about how I wanted to handle the data management in the current program, with some consideration of extending the design to new programs that are currently unconsidered. That's what this thread is about.

I came up with a design where the database is encapsulated in a class. Originally, the connection itself was a class within this class, but I have scrapped that idea as valueless. The obvious reason for doing this is so that the implementation of the class can be changed without altering any other part of the program. However, this means that other parts of the program have to be insulated from the DB class.

The next part of the design, was to make classes for each table in the DB. Naturally, once I started on this, I finished it without thinking it through very carefully, and many of the classes turned out to have no purpose in real-life. Mendhak pointed out an add-on from MS (I think) that does this sort of thing, so I guess I wasn't totally crazy.

My intention was that I would have a function in the DB class that took an two arguments (or more if needed). The first argument was a criteria, while the second argument was a class passed in ByRef. The function would return the record that met that criteria, and fill the class. This might include multiple joins, or none at all, but the DB class filled the data class. The data class was then used by the form to populate controls.

An empty data class would receive new data, and do any manipulation necessary on it. Then the data class could be passed to a function in the DB class that would perform an UPDATE/INSERT on the DB.

It also occured to me that I could make a container class that could hold an array of data classes. Then it dawned on me that this would effectively be a dataset. Whereas a datareader is a forward-only data mechanism, the dataset is a bi-directional datareader. That is what my container class would be, as well.

This lead me to the question in this long post: Am I nuts?

Am I just re-inventing something that is already available? I really like the idea of a dataclass to encapsulate the data moved through a form, but perhaps the whole idea of populating data classes from the DB is overblown.

The reason I ask is that for an earlier project, I devised a data description language. A couple years later, I realized that I had re-created SQL, and my language could be converted into pure SQL in a single pass. The only advantage to my language was that you could create proper english sentences out of my language faster than you could from SQL.

I'm looking for any suggestions here. I enjoy coming up with odd designs, but I fear that I am duplicating what is already there.

[sixfeetsix]

I understand your problem/dilemma. Writing and maintaining by hand the db classes is long and boring. We used to do that and we would end up with inconsistent classes cause nobody would use the exact same coding style/structure. It seemed like if the development cycle got even longer.

Since we almost only use SQL Server and MSDE, we decided to code a tool that generates the code of the classes for us. Having automatically generated classes for our dbs saves us a lot of time cause we never debug our code because of db stuff.

[Shaggy Hiker]

Boy, that answers a question I hadn't wanted to ask. Looks like I'm not off in a totally weird space.

[nemaroller]

I think what you're saying it that you want to keep the database seperate from the business layer - and that you want a flexible dataAccess layer.

You could define XSD's that can then be generated to strongly-typed datasets. You can use VS to easily create the XSD's, then once you satisfied with it, do a right-click on it in the Solution Explorer and choose Run Custom Tool, which will build a strongly-typed dataset for you.

 <?xml version="1.0" encoding="utf-8" ?> 
- <xs:schema id="BaseTypes" targetNamespace="http://someplace.com/schemas/BaseTypes.xsd" elementFormDefault="qualified" xmlns="http://someplace.com/schemas/BaseTypes.xsd" xmlns:mstns="http://someplace.com/schemas/BaseTypes.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
- <xs:complexType name="SomeThing">
- <xs:sequence>
  <xs:element name="number" type="xs:int" /> 
  <xs:element name="address" type="xs:string" /> 
  <xs:element name="city" type="xs:string" /> 
  <xs:element name="state" type="xs:string" /> 
  <xs:element name="zip" type="xs:string" /> 
  <xs:element name="Email" type="xs:string" /> 
  <xs:element name="phone" type="xs:string" /> 
  <xs:element name="fax" type="xs:string" /> 
  </xs:sequence>
  </xs:complexType>
- <xs:complexType name="Login">
- <xs:sequence>
  <xs:element name="id" type="xs:int" /> 
  <xs:element name="username" type="xs:string" /> 
  <xs:element name="password" type="xs:string" /> 
  <xs:element name="role" type="xs:string" /> 
  <xs:element name="primaryEmail" type="xs:string" /> 
  <xs:element name="secondaryEmail" type="xs:string" /> 
  <xs:element name="approved" type="xs:boolean" /> 
  <xs:element name="active" type="xs:boolean" /> 
  <xs:element name="contactFirstName" type="xs:string" /> 
  <xs:element name="contactLastName" type="xs:string" /> 
  </xs:sequence>
  </xs:complexType>
  </xs:schema>

Then all you will be doing in your app is along the lines of:

using (SqlConnection connection = GetNewConnection())
using (SqlCommand command = GetNewCommand(connection))
using (SqlDataAdapter dataAdapter = new dataAdapter());
{
  connection.Open();
  dataAdapter.SelectCommand = command;
  BaseTypes bs = new BaseTypes();
  dataAdapter.Fill(bs);

  SomeThings someThings = bs.SomeThings;
  SomeThingDataRow someThingDataRow = someThings.Rows[0];
  someThingDataRow.city = "Montreal";
  dataAdapter.Update(bs);
  
}
}

Give this a look-over:
http://www.codeguru.com/vb/gen/vb_da...cle.php/c5141/

Hellwraith has worked a lot with this type of setup... you should PM him.

[Shaggy Hiker]

That's pretty good. However, at this point I'm not sure whether I want datasets. I may end up with a combined approach, so that looks like a good way to go.