How to update sql database when you don't want to hard code the column names in the application

One of my first applications I was asked to do was an intranet based reporting tool and my customer was a software department. I wasn't given many constraints apart from that it would have to be a ASP.Net application and nothing project specific should be hard coded into the web application to allow multiple uses for the same page. I quickly realized that the gridview control in ASP.Net would make the perfect control for showing and allowing for editing of the data regarding the projects. Pretty much all the coding was done in the C# "code behind" of the application with the .aspx page containing only the named control linked up with the event methods in the code behind where all the data binding and formatting was taking place.

It was relatively easy to produce a nice looking table retrieved from the sql database in the gridview but how would I go about saving any changes without specifying any columns names in the code? It turned out it wasn't so hard after all as the information was already known to the application as the column names were present in the gridview as the gridview column names and the data to be saved was in the data row below.

In the GridView1_RowUpdating event I stored the column names and the data in two lists as shown below:

Code here
In the sql update method I first created the sql string with the column name information and made sure that every update was made using parameters to avoid the risk of sql injection. The data was then taken from the second list and added in the Parameters.AddWithValue function and the sql query was executed.

Code here

For this to work you need to have all your data in one database table but you don't necessarily have to show (or update) the whole table. Also if you have different names for your columns in the gridview compared to your column names in your database table you could potentially just create a look up table in the database and call this for each name in the list of column names before calling the sql updating method.

Right, finally I took the plunge!

The first post in a blog has a lot in common with the first entry in a diary. Something has happened and it suddenly it feels like it needs writing down. There is a pre-"this has now happened" and a post-"this has now happened"  in time. So what has now happened that justifies using up yet more server space and clogging up the ether (well the slow cables that serve us our broadband here in our tiny hamlet anyway). Well I have finally taken the step from being a user of coding projects/help forums and other people's blogs to actually contributing something myself to the vast pool of knowledge that is present on the World Wide Web!

Was it something extraordinary? A massive piece of software showcasing some genius programming concepts? No of course not! It was small, self contained and pretty safe bit of code to go in as an add-on to a GitHub project (and it might not even get included!) But the step has been taken and I have now entered the post-"this has now happened" phase and I feel that now is the time to share a little of what I know to offer the same help as everyone out there provided me when I started coding.

It is quite funny how this also has coincided with the fact I don't get my own questions answered by searching the web in the same way as when I started two years ago. Possibly because then I gave up if there wasn't an answer to it on the web or just compromised to get something working that was similar but not exactly what I needed. Now I feel more confident in doing my own thing and trying to create exactly what need (unless searching for it makes me realize that what I really needed was not at all what I originally wanted!).

So this is it! Start of a new era for me (but for human-kind a very non-significant change to an already amazing era).