Strongly typed dataset is a good thing... well, sort of, if you just need a quick and dirty Data Access Layer for your Microsoft Access backend in your .net code. What is unknown to many is that it is possible to set the the connection string of a strongly typed dataset from App.Config file. This is extremely handy if you have to deploy your application on client's computer because you can compile the application once and just change the config file when the application is installed on client's computer.
In this post I am going to demonstrate how to use the config file to set the connection string for strongly typed dataset.
- Create a VS 2008 solution with the following c# projects. Note that I create a separate c# project for my data access layer code. This is better than lumping the datasets in the application project because it encourages better separation of concern.
- Add a new app.config file in the AppConfigJob project by navigating to the app.config file in DAL project and choose the "Add As Link" option.
- Note that you have to create the app.config file for AppConfigJob in this way-- you can't simply create a app.config file by running the C# project property option and choose setting. This is because the app.config file for AppConfigJob and DAL must have the same connection string, or else you will not be able to set the connection string from the config file later, when your application is built.
- Compile the solution in debug mode.
- In your bin\debug folder, you should see that there is an AppConfigJob.exe.config file. This is the config file that holds information to your connection string.
- The AppConfigJob.exe.config file will have the following configuration.
<?xml version="1.0" encoding="utf-8">
<add name="DAL.Properties.Settings.sundaybusinessConnectionString"connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\sundaybusiness.mdb;Persist Security Info=True"providerName="System.Data.OleDb" />
- After your application is installed on client's machine, you can always point the location of the mdb database by changing the Data Source property. To test this, create a label, put the following code into the Form1_Load method
AdministratorTableAdapter adapter = new AdministratorTableAdapter();
label1.Text = adapter.Connection.ConnectionString;
- So when the Form1 is loaded, you will see the connection string information
- Now change the data source in the AppConfigJob.exe.config to something else, maybe
- Rerun the Form1, now this is what you will see.
- The connection string is changed, without recompiling the application.
This is how App.Config can be used to set the connection string of a database at install time. You can manually change the config file, or you can use some scripts to change it during installation time, but this is a topic for another post.