VB.NET - Direct Database Access

Question

I'm making a VB.NET application with an SQL Server 2005 in the background. Naturally the user can not edit the database directly but will use a number of UI features to be able to add and modify the data.

However, there are a few tables that should be easily accessible from the admin interface such as specific information about a vendor. What's the easiest way to let the user edit this data freely? One way would be to use a DataGridView but this could appear complicated to the user, plus I'm not sure exactly when to save the edited data back to the database.

The best way that I can think of is to create custom dialog boxes for adding, deleting and changing the information, but this seems like too much work for such a small feature.

Answer 1

You are either going to have to give them the data in a table format (like with the DataGridView) or you are going to need to build something that lets them edit individual records (like custom forms). It seems like a lot of work (and it can be) but there are some ways to cut down on the amount of work.

Check out how to use databinding in VB.NET. There's a tutorial here, another one here and many others out there. You can use databinding for both a table view or for individual records. Using a DataGridView isn't too complicated for a user as long as you build in the necessary support in code - make sure the row is saved if it has been changed and they move to another row (or prompt them), disable editing on columns that they shouldn't be able to change, validate the data before writing it back to the database, etc.

There are also code-generation tools like CodeSmith that can create a data access layer between the GUI and the database. Some of the templates you can get will even generate the actual forms for you.

The only other option I can think of is to give them direct access to the database through tools like SQL Server Management Studio and setting up logins that only have permission to specific tables/views but I would STRONGLY recommend against that.