How to go from a Web DB Schema to a Desktop one?

[jbubrisk]

Hi guys,
I'm a somewhat experienced web programmer in PHP and MySQL, but I've been wondering how you would "convert" a web data structure to desktop programming. For example, on the web, from what I have seen, you generally have a database filled with tables and each column is a single field. Database inserts and updates occur through direct SQL, or an API doing direct SQL. Data usually comes from POST or GET data, so it can be put directly into the queries or API (minding security of course).
It doesn't seem to be the case in Desktop programming. While making a C# app, and storing your data in objects, and collections of objects, what is the best practice for storing this data in a database?
Serialize and dump it into a blob field? But then you loose the ability to effectively query the data.
You could serialize and dump the data, but then store some columns anyway. At least ones you might want to search on, but that doesn't seem like the optimal approach either.
Finally, it looks like there are some things you could do to "extract" the data from the objects, and break them up into datasets, but that feels like you're rewriting the whole app. i.e., you would be converting the objects from one form to another, but for no real purpose, other than storage. But again, seems like a lot of work.

Any C#, ADO.NET gurus out there? Thanks in advance.

jbubrisk

 

[imported_Dhaval00]

What is the structure of your data? What about scalability - are you dealing with large amounts of data?

 

[Crusty]

Im having a hard time trying to figure out what you want to do. Are you talking about taking an existing web application and port the UI into a desktop application while keeping the same database intact? Or do you want to develop a completely separate standalone desktop application that has nothing to do with aforementioned web application?

Either way, you want to use a DataSet. In Visual Studio once you connect to your DataSource you'll have the schema available to you so you can design your DataSet around it. Then you just use the DataSet to perform your I/O operations for your data. I personally create a set of objects on top of the DataSet that store all my data for runtime and use DataSets only to read/write my data. I found in VS 2005 it was too cumbersome to use the DataSet throughout the entire application, but apparently VS 2008 alleviates most of that.

 

[hooflung]

I don't quite understand what your asking. Your structure will be identical. Your data objects replace the session/object/array you would init from the user or db. You would use whatever your language has to offer and then use DataSet like Crusty said to read or write your data. This questioning is in the realm of why Web Based J2EE applications like PeopleSoft cost a lot of money to make and maintain over your basic store front. You have to make sure you treat the business objects properly and make sure that you use as little database interaction as possible. Use of triggers, transactions and stored procedures are a must to make it all work or it turns into gheto code.

 

[Markbnj]

I'll take a shot and assume you're just trying to figure out what the alternatives are for storing data. They're really pretty much the same regardless of whether it is a web or desktop application. It's just that web apps tend to need certain things, while desktop apps tend to need other things. You can use a full-blown database like SQL Server or MySQL, or you can serialize to XML or binary formats, etc.

Suppose you had an existing web app with a SQL Server back-end and you wanted to convert it to a desktop app... you could use embedded SQL and continue with the same data schema, or you could convert it to serialization into text, XML, or whatever.

This is one reason why it's good to keep the data layer distinct and have it accessed through an abstraction at the middle tiers, so that you can swap out if you need to.

 

[jbubrisk]

Thanks for the replies guys. I think I was just asking how to efficiently convert objects into database columns, is there is in fact an "easy" way.

Assume we have a Person class, with first name, last name, and ssn. It seems like dataset go straight into the DB without much work. But is there some sort of way to "auto-convert" the person object into a dataset? This is a simple example. Assuming you have to write code to do the conversion, what do you do about complex and nested objects?

Let's say we had a Child class too, and the Person class stores a collection of the Child objects (infinite). When someone hits "save" in our app, what happens? How does the data go from nested objects, to relational tables? Do they? Or are we stuck writing crazy code to do that, or just serialize the whole damn thing?

 

[KB]

The goal of layered programming (UI, Business and Data object layers) is to build business and data objects such that they can be used in both web and desktop projects.

The data object layer is where the business objects are converted into CRUD (Create Read Update Delete) statements. This can be done with creating SQL strings to execute manually from the properties of the object or using frameworks to automatically generate them.

I would suggest you start looking into some Object Relational Mapping projects out there since this is the problem they were built to resolve. The goals of these projects are to take your database schema and to build objects and collections from your database for you. They will prevent you from writing all of those CRUD statements and instead of working with SQL you work mostly with objects.

At first you will be confused by them, then you will hate them, then you will respect them and finally you will love them.


Here are just a few ORMs
www.subsonicproject.com (SubSonic, the one I like the most right now)
http://www.hibernate.org/343.html (Nhibernate)
http://msdn.microsoft.com/en-u...ramework/aa904594.aspx (LINQ)

 

[jbubrisk]

Great, thanks KB! KB = Knowledge Base?

 

[Markbnj]

Assume we have a Person class, with first name, last name, and ssn. It seems like dataset go straight into the DB without much work. But is there some sort of way to "auto-convert" the person object into a dataset? This is a simple example. Assuming you have to write code to do the conversion, what do you do about complex and nested objects?

KB's suggestion isn't a bad one, and it will lessen the amount of thought you have to give to these subjects, but the other side of that coin is that an ORM won't always deal with these issues in the most efficient way. For example, you might have a set of 100 objects, each of which has two contained objects, with the data coming from one primary and two dependent tables joined on ID. If efficiency matters to you then you can write a single query to join these tables and populate a recordset, and then a factory to take that record set and efficiently create the runtime object structure.

The ORM (such as Hibernate) will initially populate the set of primary objects and create lazily-resolved query methods to get the dependent objects inidividually. That might be the right approach; and it might not. You can tune it, but then that often means making changes to the generated code that may remove the ability to regenerate it, and thus some of the value. I'm not a big fan of ORMs for data intensive applications of any non-trivial scope, for that and other reasons we've discussed here in the past. I don't see the database as a persistence layer for objects, but I think that in the right application ORMs do have a lot of value.

 

[Crusty]

Agree 100% with what Markbnj has to say. ORM's have their place and it's up to the developer to decide whether or not it's the right set of tools to use. I've had very limited success with Hibernate simply because the datasets I work on are quite complex and getting Hibernate to play nicely and quickly was a challenge.

My best experience with Hibernate was over a year ago and using the Netbeans plugin to write some Java code. The biggest success I had with Hibernate was using it in conjunction with custom classes I used to transform a database from mysql into Oracle. It wasn't a simple import, the entire structure of the database was different so we needed to do lots of manipulation to the data in order for it to work. We used Hibernate to link into the Oracle DB and the custom classes to pull from mysql and transformed it from there right into the classes Hibernate generated.

To this day the code works perfectly and quickly, it's used to import data from a vendor 😉