How to design interface using data adapter/dataset binding to a single record.

Posted on 2005-05-10
Last Modified: 2010-04-23
First, I am new to VB.NET programming and ADO.NET in particular is giving me trouble.  please bear with me, i know just enough to be dangerous.  

In the application I am trying to develop, navigation through the data is implemented using a tree structure, and if you want to add a record, edit a record, or delete a record, you can do this from the context menu for each object on the tree.  This all works, i have the basic data read and the context menu firing the appropriate commands with the appropriate id's passed to the procedures.  SO, now i want to be able to design an input form, that is used for adding new records or updating existing records, one at a time.  The deletions i think I can handle.  

i would like to design an input form, to be used for adding new or updating one existing record.  I want to bind the text fields on the input form to a dataset based on either a returned record for editing, or a new record for adding, but am having conceptual difficulty designing the objects to perform this.   I guess i need some help getting started.  

I can see two different approaches.  Ideally, I want to design a data access class, that will return a bindable object, but this seems a little over my head.  

So, I think the easiest way to accomplish what i want is use a dataset/dataadapter interface, binding the text fields to the dataset, and submitting updates using the command methods of the data adapter.   When an add happens, i don't know if i want to add the record using a command query, then open it in edit mode.  

The simplest table in my database is tblLab.  it has 3 fields, LabID (autoincrement, primary key, Random sequence)  
LabName is a string, and BldgName is a string.

i want to add a new lab or edit an existing lab (will know the LabID when form opens, if an add or edit, but not sure where i should add the new record, and how to do it vs. when i bind the data to the fields).

I do know how to create a typed dataset, and have played around with manually creating a dataset/dataadapter and modifying, and actually wrote an edit retrieval that worked be returning one row, but i keep thinking i am going the long way around, so i thought it was time to ask an expert.

I don't really expect anyone to fully code this out with steps.  what i need is an overview of a logical way to try to do this, and maybe a list of steps, things that i need to make sure happen.  maybe code examples for any "tricky" parts.   i understand this is quite a bit, so i will make it worth as many points as i can.




Question by:EricJamesOlson
    LVL 1

    Author Comment

    One comment i forgot in reading this over, on exiting the input form, i intend to have a Save button and a Cancel Update option.  

    For an edit, Save would update the edited record, and Cancel would not update the edited record.  
    For an Add, Save would either Save or Create the new record (depending if interface is adding or editing existing at this point), and ideally i would want to delete the existing record if Cancel.  
    LVL 18

    Accepted Solution

    Hi EricJamesOlson,

    Just one comment... while binding window controls to datasets is appealing because it simplifies your code a bunch, I rarely use this approach because it almost always causes problems on the database side with open connections, table locking and the like.  This is probably much improved under ADO.NET as all recordsets are basically "disconnected" anyway, but my preference is for a totally disconnected (ie unbound) approach from the get-go.

    Our database is SQL Server on the back-end, and we do almost all of our database selects by calling stored procedures, and the same for database insert/updates and deletes.

    I wrote a module with some global routines that pretty much wrap all of the low level database access code... basically, the programmer calls one of 4 or 5 functions that will send back either a datareader or a dataset (depending on what you want), will operate with a command object or execute some sql string provided, or execute an action query.  Each of these routines call another routine for opening the connection, then they run the query and close the connection returning the filled data object.

    To call a stored proc that requires parameters, the programmer sets up a command object and appends parameter objects to the command before calling the function to execute the query... again, a global function buries all the steps necessary to create a parameter object, and then append it to the command object.

    When I get the datareader (datareaders are faster, so I use this whenever possible) or dataset, I'll usually iterate through it manually loading data to the screen... on occassion, I'll set the data source of a grid to a table in a dataset.  For most updates, I take the data off the screen manually and put them into parameters and pass them to an update stored procedure.

    If the user is updating data in a grid, most of the time, I do the record update in the rowcol_changed event of the grid (after verifying that it was indeed a row change event).  But, in the rare case where we let the user edit several things in a grid and only save once when finished with everything, I'll usually take the data out of a grid and put it into a dataset (perhaps created just for this purpose) and then extract everything using the XML property and pass the XML to the stored procedure as a parameter.  Then, in SQL Server, it's pretty easy to load the contents into a temp table using the xml prepare doc command.  Another approach to the "batch" update is to let the user edit the grid at will, keeping a hidden column with a flag to know which rows have been updated/added/deleted.  Then, when the user clicks Save, iterate one row at a time, looking at the flag and calling the update routine for the data in that row....

    Performance is surprisingly not much of an issue on any of these screens.

    The only downside to this whole approach is that you have to manually take data out of a dataset or datareader and put it on the screen, or load it into a grid, or combo box or whatever... but, that's not too difficult.

    LVL 1

    Author Comment

    okay, makes sense.  was hoping there was an easier way to do this but i can follow what you are saying and give it a try.

    LVL 18

    Expert Comment

    Thanks, as I said, it's not too difficult.. if you have any difficulty with any one of these concepts, just post a follow-up comment to this question.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    I'm currently working for a company where I have to upgrade over 50 VB6 programs to VB.NET 2008.  So far I'm about half way through, and I've learned quite a few tricks that drastically improve the performance of VB.NET apps. Because there are a…
    The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now