Windows Dev: Add, Remove, Edit DataGridView

Problem:

This is very symptomatic of people like me who started out in .Net Framework 1.0's DataGrid class. When first encountering the new paradigm in .Net Framework 2.0 we still wonder why it they still made it so hard to work with the underlying DataTable or DataSet, and it must be a common knowledge that no site will tell you what to do a simple Insert, Update and Deletes.

For example, to find out which row was selected in the corresponding row of the DataTable. You probably even tried to hide a key column as the column number 0, and dismayed to find out that as soon as it is hidden the DataGridView won't even return the hidden field (though on the web control that technique does work).

Fix:

They actually did simplified it, but you do need to RTFM. You will find out that spending about an hour trying to understand the BindingSource class and utilizing it will significantly reduce or completely remove that grid and data sync problem headache.

Basically all you need to do is to drag the instance of BindingSource to the designer pane. Then using the DataSource property of the DataGridView, assign the newly created BindingSource.

Then in the BindingSource, assign the actual DataSet with the right table member and now they go magically in sync. For example, if you do an Insert and Update in the underlying DataSet's DataTable, it will automatically reflect the change in the grid. In other words, the DataGridView will automatically follow what you do with the underlying DataSet. I think finally that's significantly more convenient. Note that you should not invoke the Insert function of the TableAdapter class and hoping to the DataGridView to update. That do not go through the underlying DataSet, instead it directly fires the Insert SQL without going through any datasets.

Windows Dev: Storing and Changing SQL Connection String from Configuration File

When writing a .NET application with SQL database, we always need to store a configuration string somewhere. This can be done in many ways. I happened to like the Application Configuration capability that started to appear in Visual Studio 2005 with .NET Framework 2.0

This is a very typical thing we do, but at least in .NET Framework 2.0 there still is not an easy solution for this, and using the App.config approach, you will soon realize that you run into two problems (I am not going into the security aspects of it.)

1. You could store the entire connection string as the Application level configuration parameter in app.config but if you do that then you cannot change and save the string from anything in the applications section.
2. But you have to change the instance of that string in order to easily propagate the connection string to all TableAdapters

Well, it turns out that there is simple and easy way of doing this. You can modify the memory instance of the application connection string using the array indexer with the name rather than calling as a member of the property. If you can successfully make the change in the connection string in the constructor of your application in the following manner, the changed configuration string will propagate to all of the TableAdapters.

public Form1()
{
InitializeComponent();
string s = Properties.Settings.Default.MyConnectionString;
s = s.Replace("(local)", "192.168.0.100"); // This can come from another user configuration string.
Properties.Settings.Default["MyConnectionString"] = s;
}