Design issues...

W

wyrd

Senior Contributor
Joined
Aug 23, 2002
Messages
1,405
Location
California
Alrighty.. 99% of what I've programmed up 'til now has been all text based. Now, I'm moving into the wonderful world of GUI. To start off I'm building a small business type app using MSDE (as you might have guessed from my posts in the database forum). However, I've run into a slight problem due to my lack of knowledge with the GUI controls in which .NET provides.

Here's what I'm looking to accomplish;
A simple inventory type program that will fit the needs of a generic business. Easy enough, right? Well, I'd like to build it so it works similar to the way an Excel sheet would work (ie; you can click cells and edit at free will). Obviously I'm not going to give the THAT much free will, as there will be contraints etc. in which the user must abide by to edit/add columns.

My possible solutions;
-Possible solution 1: Using a DataGrid with DataSets/DataViews/DataAdapters. That way I can hold the info. in which the user wants to retrieve in memory (DataSet) and only display what the user really needs to see (DataView). Hopefully this would also allow the flexibility of being able to view the data however the user wants (within the limits I set) without having to query the DB over and over.
-Possible solution 2: Once again using DataGrid and DataSets/DataAdapters, however instead of using DataViews in memory, I was thinking of building Views in the database itself, in which the program will interract with, rather then the tables directly.

So what are my questions?
-Is this a good solution?
-Is there another control that offers the same excel style editing that the DataGrid does? One thing I particular like about DataGrids is the ability to display error messages next to the cells where errors reside.
-In this situation, would using DataSets/DataAdapters to hold data in memory (disconnected mode) be too much?
-Should I look into the old style of connected mode, in which case would I still be able to make use of DataGrids?

Any advice would be appreciated for a first time GUIer (uhh.. okay that may not be a word) :D
Thanks.
 
wyrd, I use diconnected mode but bung the data into textboxes on the form. The users can then edit whatever textboxes I allow them to. My objects perform validation prior to attempting to refresh the database and Optimistic Concurrency ensures data integrity. Haven't moved onto using the datagrid yet as to date the method I use seems to work fine for me
 
The DataGrid is always in a bound mode in one fashion or another. It may not be a DataSet or a DataSet connected (bad term there, too) to a database, but it's bound to something. But, binding the DataGrid to a DataSet is perfect for what you want... with one exception.

You mention you want an Excel-like interface. To me, that means almost unlimited columns and rows. Assuming you'll bind to a DataSet, that means being able to dynamically add rows and columns to your DataTable as the user moves around, OR starting with a DataTable with 1000 rows and 1000 columns (or some similar large number). Unfortunately, the DataSet is not a sparse type of storage. Meaning, if you say you want 1000 rows and 1000 columns, you'll get 1000x1000 = 1,000,000 objects in memory. Not too good if the user only edits 3 cells.

There are a number of controls that you can buy that DO work like Excel. I haven't used them in .NET so I can't say how they work. Also, since this sounds like a test project, you probably don't have $300+ to spend on a 3rd party control.

I don't know of any unbound controls that work like Excel other than 3rd party controls.

Knowing all that, I'd suggest abandoning the Excel interface for now and just use a DataSet/DataGrid. Make sure you check out this FAQ for help with the built-in DataGrid.

-Ner
 
I appreciate the feed back.

I've decided to go in a different direction, with the 'ol DataList and connected mode. I got into programming this a bit, and realized that for such a small project I was definitely sucking up a lot of needless ram with the DataSet and DataGrids, not to mention with the amount of contraints I was putting on the DataGrid I might as well of just been using a DataList. Also, the database will be on the same computer anyway, so there's no reason to hold entire DataTables in memory.

The excel style I was going for may of been a bit of an illusion, especially with the simplisticness of this project. Going with a DataList and not a DataGrid will force me to add a lot more user validation code, but that's okay. I think a simple user interface for a simple project fits far more then a complex user interface.

Maybe when I'm done with this user interface I'll re-design the DataGrid idea I had to see if I can make it work in a more realistic fasion.
 
A DataList? Isn't that a web control?

If the point is to allow them to modify fields, one record at a time, you could also use bound controls (textboxes, comboboxes, etc.) using a CurrencyManager to MoveNext, MovePrev, etc. Just another idea.

For the record, when you get into real world applications where tables have thousands or millions of rows, you almost never show a grid/table with all of the rows at once. Generally, a table with so many records can be filtered somehow. For instance, you would never show a table full of customer addresses where the user is free to edit all of them at once. You'd most likely be showing a customer's data and a grid of their addresses, so you'll only be showing 1-5 addresses, give or take.

The only time I've shown a grid of an entire table is for the maintenance pieces of the application, usually for lookup tables or simple 1-row tables. Maybe you need to spend some time thinking about the overall design, such as how the user gets to what they want quickly, and worry about the specific controls once you have a better feel for what's needed.

-ner
 
A simple inventory program is really a database mining program.
The real art is in the database structure.

A program designed to maintain an inventory database needs to consider how you want to query the inventory first, i.e. what data do you want access to regarding your inventory.

The user interface should then reflect a logical representation of the database.
You may want to create a dropdown with all your inventory listed alphabetically to select an item (by name or product ID or both)
Then utilizing several textboxes, you can bind them to the datarow with the concurrency manager per Nersues' suggestion.

The dataadapter/dataset is definitlely the right way to gain access to your data if you want to manipulate it in any way....
change an inventory item price, quantity, supplier etc.

If there are times you simply want to view the inventory, create a new form with a different interface...here, the datagrid is perfect, but use a datareader to fill it....fast and efficient, but note, you can't manipulate the data from a datareader.

Jon
 
Yes, Cassio. You can fill a datagrid from almost anything, but you must do so through binding. Assuming you're binding the grid to a DataSet, you'd have to fill your dataset from the datareader - which is mostly pointless :)

-Nerseus
 
Sorry I misspoke. Ner's right of course.
Datagrids go best with datasets.

The following uses the classic example of the northwind database to create an output into a console.writeline output (could just as easily be a textbox), displaying the contents of the customerID and CompanyName fields of the Customers Table using a datareader. Like I said, fast and efficient, no frills data display.

Wyrd, working with MSDE this is your format using sqlclient.

Visual Basic: 
Imports System.Data
Imports System.Data.SqlClient
Imports System

Dim sqlConn As SqlConnection = New SqlConnection("Initial Catalog = Northwind; Data Source = localhost; Integrated Security = SSPI")
Dim cmdSqlCommand As SqlCommand = New SqlCommand("Select Customer ID, CompanyName From Customers", sqlConn)

sqlConn.Open()

Dim drSqlDataReader As SqlDataReader = cmdSqlCommand.ExecuteReader()

Do While drSqlDAtaReader.Read()
      Console.Writeline(vbTab & "{0}" & vbTab & "{1}", drSqlDataReader.Item(0).ToString(), drSqlDataReader.GetString(1))
Loop

drSqlDataReader.Close()
sqlConn.Close()

If you want to scroll through or edit the data in any way, the datasets the way to go.

Jon
 
Back
Top