I do some freelance programming here and there and I came across a job that called for comparing the data from over 1,000 different databases. I had never come across this problem before and really had no idea on how to do this. I had always used the GUI in Visual Studio to create a database connection but that only works for a handful of databases. If I were to connect to all of these this way, it would really bog down the program and create a source code too bulky to deal with. There was also the fact that I did not have all of the databases. This was for medical research so I could not be given the databases even if I wanted them to jumble up my hard drive. That is when it hit me to use variables. If the databases are designed similarly, you can use a variable and a simple text file to access as many databases as you want with C#. All of the SELECT statements, Command Objects, and Adapters stay the same as any other program. The difference is in the Connection. Whether you are using OLE or SQL, the connections are the same. Here is the code that I came up with.
First you will need to create a small text file to store the names of the databases. To do this you will need to use a text editor software and not a word processor. Word processor software will add invisible characters that are used for formatting. C# will try to read these characters as code and it will just mess up your whole program. The fact that they are invisible characters will keep you troubleshooting for days before you finally catch what is going on. Notepad is great for creating these text files. You can even use notepad to create an entire program (not recommended but possible). There are several of these text writer programs out there and the best part is; most of them are free. When you make your text file, you will need it to be a list. Each database will need to be entered on a separate line and you will want to include the file extension. The file extension does not have to be there if they are all the same file type, but it does make it simpler. If you do not want to include the file extension, you can add it later to the connection statement as long as all of the databases are the same file type. Here is an example:
13020.mdb
A07719.mdb
A13525.mdb
A23139.mdb
A24893.mdb
A42214.mdb
57982.mdb
A06015.mdb
A06456.mdb
A20111.mdb
A23552.mdb
Once you have the text file set up you can begin your coding. In my program I needed to display all of the database names in a list box in the GUI. This means that my stream reader object was in the form load section. You can put the reader in any section, depending on where you want it. The first thing that you need to do though is set up your stream reader object. If you are displaying the file names in a list box, you will need another reader later when you are ready to process the data. Here is the code for that:
StreamReader NameRead = new StreamReader(“C:/your file path/filename.txt”);
string strNewName = NameRead.ReadLine();
It is important to create the variable that will store the line being read. This is the key to making this work. The variable that you set up to store the file name will be used to either add the database to the list box or to process the data. The only thing that the variable needs to hold is the file name. If you are accessing databases in multiple locations then you will also need it contain the file path. If the path name needs to be included in the variable as well, you will need to include it in the text file. The easiest thing to do is store all of the databases in the same folder. This will reduce the amount of data that you have to store in variables and make it easier to find and access them. It will also cut down on the amount of variables and code that you need to write. If all of the databases are in the same folder, here is the code to set up your variables:
string strFileName = strNextDB;
string strFileLocation = “C:/your file path” + strFileName;
Once you get the variable set up you can create the loop statement to process each database in the list. This is a simple Do While loop that will use the variable you set up to store the database names. It will basically tell the program to process the code over and over as long as there is a value in the variable. Once the stream reader gets to the end of the file, it will empty out the variable and that will stop the loop. The loop statement is very simple to set up and it will need to house any code that you want done to all of the databases. Here is the loop statement:
while (strNewName != null)
{
Your code will go here.
}
To access the different databases in your code, you will set up your connection string. After you declare your variables and set up the start of your code, you will want to start your connection command. The connection string is what you will change from the normal code that you would use. In the connection string you will need to include normal database connection information, the file path, and the file name variable. When you create a database connection you will need to know what type of database you are accessing. This example uses an Access database so it is an OLE object. You will need to set up an SQL object to access your database. Here is the code to start the connection:
string strConnection = “Provider=Microsoft.Jet.OLEDB.4.0; Data Source=” + strFileLocation;
OleDbConnection myOLEConnection = new OleDbConnection(strConnection);
The rest of the database information will be set up normally. You can set up all of your SELECT statements, Adapters, and Datasets the way they would normally be set up. These are the only necessary changes that will need to be made to make your program access unlimited databases. There are other ways to do this, but this is the one that I was able to come up with. It is simple and easy to understand and can be used for any type of database. The thing with programming is that there are several different ways to do the same task. This is just one way that I had to come up with since it is very difficult to find any code snippets on multiple databases. Hope this helps you with your program.
Recent Comments