Those of you who know me well, will know that I love Linq, it’s simply one of the best technologies that MS has come up with in recent years, but it has one drawback.
The configuration of the database connection strings, when using it in a WinForms project.
Let me explain:
Up until now, everything I did with SQL server in a WinForm app, I did using ADO, and as such my configuration parameters where always stored in the registry. Using Linq means that you now have to have an extra file with your app in the form of an XML configuration file.
This in itself is not a problem, after all a lot of apps have a lot of supplementary files these days, however in this case the SQL server connection string is held in plain text inside the file, and is easily readable by anyone with access to the computers file system (and yes I know, before I get jumped on or flamed or whatever, if you know how, the registry is easy enough to read).
Anyway, this very reason, and the fact that building a way of allowing the programs user to reconfigure, also meant writing a configuration parser (MS decided to put connection strings in a section of their own, thats not accessible with the normal App.Config API) meant I’ve been reluctant up until now to use Linq in WinForms apps.
Using it in an ASP.NET web app is a different kettle of fish, beacuse the web server config will usually not let you download the Web.Config file, and so the connection strings are generally only accessible to those with authorised and/or physical access to the server itself.
So, hence I was pondering… Linq would make the program easier to develop, but I didn’t want visible connection strings in a file in "Program Files", then as I was browsing through a DBML file generated by Visual Studio, I noticed that there was 2 constructors for the Data Context class to create the Linq Classes.
After further investigation, it turns out that this second constructor (there’s actually more than 2) takes a string as it’s parameter, and more so, this parameter can be… yup you guessed it a full connection string.
It was decided… Linq in Winforms it was, and so the configuration system I present here was born.
The project (Which can be downloaded here) builds a dll called "configSystem", this DLL exposes a class and a number of methods to make configuration of database parameters really easy, and even includes a routine to search the local network for instances of MS-SQL server.
Because of the SQL server routines, it will only run on a machine that has the MS-SQL Server management objects installed in the GAC, but if you have Server management studio installed (or any other SMO based client utils), then they will be there.
Iv’e tried to make the project as easy to use as possible, to use it, add the DLL as a reference, then at the top of your file add a
line. Once you do this, you can then make use of the library as follows:
In your apps main initialisation function…
private configForm myConfig = new configForm();
private configSettings mySettings = new configSettings();
// Public form constructor
// VS generated form/component initialisation
// Set the default app and company key, this will eventually generate:
// Override config system defaults. If no config exsists, these values
// will be used to populate the form with sensible values.
// Initialise the config system, and if need be show the config dialog
// to create one.
// Populate the mySettings structure with the set values.
mySettings = myConfig.configuredSettings;
This will check if a config exsists, and if not popup the config dialog, which will attempt to auto populate with SQL server instances (hence the delay after calling it 😉 ), if the dialog is popped up, the cancel button is disabled, beacuse this is a required step to create a config.
If a config exists, then the dialog is not shown, and the settings are read into ‘MySettings’ where they can be retrieved as needed.
To provide a button on the form to allow re-configuration, simply add the following to the buttons click handler:
// Set config dialog instructions as required
// Show the config dialog
// Synch the settings in case any where changed.
mySettings = myConfig.configuredSettings;
and this will change and reload the config into the application.
Last but not least however, comes the bit that’s in there for you Linq users 🙂
When you create your data context objects, simply use:
mydatabaseDataContext dc = new mydatabaseDataContext(myConfig.connectionString);
and all will be performed automagicly.
Unfortunately, the app will still need the XML config file, as the Linq system will try to load it at app start, however just do what I did and clear out anything in the actual string itself, so all your left with is connectionstring="" in your XML.
Your app will still work, but no default opening of DB’s will take place, well not unless you try to use the default datacontext constructor anyway 😉
Have fun, and enjoy. If you use it in your projects, then a "This was written by Shawty of Digital Solutions UK" notice , or thanks would be appreciated, and a quick contact to let me know where it’s been used, but other than that I’m not tying any specific licence to it, if you improve it I would also appreciate you sending me an updated copy, but again I’m not going to hold you to that.
The project files are on my MSN Skydrive HERE