How should a Windows Form app communicate to a DB?

Question

I inherited a Windows Form application written in VB that is really showing its age and needs to be refactored.

The project connects to a MySQL DB, but has no Data Sources.  It seems it was originally developed by displaying rows in a DataGridView, creating unbound TextBoxes and such; but then wrote their own code and event handlers to handle the browsing of records along with DML calls to the DB.

Trying to maintain the homegrown event handlers and tracking down unhandled exceptions is not worth it anymore.  I'm going to refactor it, but need some advice on the best path forward.

Given that this project connects to a MySQL DB and I'm currently developing the Windows Form application with Visual Studio 2010:

• What frameworks are available to me for browsing the records and supporting DML calls?
• What factors should I be taking into account to help me decide which of the frameworks should be used?

Note 1:  moving away from VS2010 is negotiable, however I have no control over the decision to use MySQL.

Note 2:  I'm not sure if it matters; but the project also uses Crystal Reports to allow end-users to generate some printable reports.

I'm unfamiliar with some terminology and options; but from what I've read so far, at least one of these frameworks would be the Entity Data Model.

So, who has experience in this area and can provide some recommendations and answers to my queries.

Answer 1

Hi,

Since this issue is related to the MySql, to help you resolve this issue as soon as possible, you could post this issue to the MySQL forum for better support:

http://forums.mysql.com/

Best Regards,

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 2

Hello,

First off, MySQL has a data provider to access data, like any data provider them must implement certain things like a connection and command class that have specific methods and properties. For example, a connection is the gateway to opening a database while the command reads and writes to and from tables in the database. One example, create a connection, issue a SQL select statement against a table which you then populate a DataTable with data from the select statement executed in the command object. Now the DataTable (keeping things simple) is used to populate a DataGridView and/or data bind to things like TextBox controls. We can add a BindingSource to the mix to provide additional functionality.

In regards to events, the first place to start is with events supported at the level of the data, keeping in mind we are using a DataTable there are events for a DataTable that allows you to monitor changes in the data. Then when appropriate use events of user interface controls i.e. DataGridView, TextBox, ComboBox etc. What many programmers fail prey to is focusing on events for controls instead of events for data i.e. DataTable, DataView etc. Always when possible work at the data level. 

In regards to reports, consider VS2010 or higher report designer in place of Crystal Reports. Like anything in programming, you will need to speed quality time with this to learn how to use the report designer.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

Answer 3

Hey Kevin,

It seems like you are advocating the status quo for my project; i.e. a hand-written interface between the DB and form controls.  Your suggestion did have some modifications:

• add a BindingSource
• add handlers for the DataTable events.
  
• where possible, place the functionality within the data event handlers.  But, the form control event handlers can be used where/when needed.

Is this how you implement your DB to form control interfaces?  To paraphrase your post:

• Code the DB connection
• issue a SQL query on that connection
• place the results within a DataTable
• add a DataGridView to your form
• use the DataTable to populate the contents of the DataGridView
• create a BindingSource
• write data event handlers to support needed functionality

This seems different than what I was reading up on last week which revolved around dataset vs Entity Data Model.  Ref:  http://msdn.microsoft.com/en-us/library/vstudio/h0y4a0f6%28v=vs.100%29.aspx

I'm not completely familiar with the VS terminology; so my apologies for when I use terminology incorrectly.

I'll be taking a look at the BindingSource today to see what this gets me.

Answer 4

I would say you have a good understanding to what I suggested to start with. One note on working with DataSet, they are working with data from multiple tables, could be master-detail (see my article on manually creating a master-detail relationship along with BindingSource) or simply reference tables that are connected using DataRelation class in tangent with the tables in the DataSet.

Now the reason I am advocating the above is because a) I am not sure how well Data Entity model is supported for MySQL similar to me mainly working with IBM databases where Data Entity or DataContext is not supported thus no choice but to hand code. b) understanding the manual method makes it easier to learn the other methods like Data Entity, DataContext or strongly type data classes.

Once you understand the available options what will work with your database then make a choice but do not attempt to take what may appear to be an easy method yet do not take one that requires a good deal of code without good reason.

To go along with my suggestions

Working with DataTable events

BindingSource Find and Filter

Working with parameters of a command object

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.