Ryan Ryan - 4 months ago 18
C# Question

C# - Dynamically accessing a list of Microsft Access database files (.mdb) and opening them to search for data

I'm writing a program where I have a somewhat large number of .mdb files and I need to be able to connect to each file and search through them and check user data against the values in the database. I don't want to hard code each connection because the databases contain firewall info and rules for specific locations and network administrators here have a tendency to add more access files to the folder and it would be bad to have to go and alter the program's source code each time this happens. Especially once the project is no longer in my hands and another developer would have to work on it.

Right now, I have created a function that creates a list of files (of sorts) of files that end with .mdb or .ldb extensions in the current directory. I also have a string array of the file names and output them with a message box just to confirm that the general idea behind the method works. The program is able to correctly list the names of each file with the .mdb extension so I know it does at least work in pulling each file with that extension. Now I face the problem of looping through it and opening each database to pull the info that I need. This is where I'm confused. Here's the function so far:

private void SelectDstIPTable()
{
//Write SQL code to select table
//Select the table and pull the info
//Do something?
//Sort?

//Does this need to be moved out of the method scope to the whole class?
string strQuery = "SELECT * FROM devices WHERE dst_IP like '% " + textBox1.Text + "%' OR src_ip Like '%" + textBox1.Text + "%'";

//This will be the list of files that end in .mbd that the code will loop through looking for the IP
string currentDirectory = Directory.GetCurrentDirectory();
var ext = new List<string> { "mdb", "ldb" };
var myFiles = Directory.GetFiles(currentDirectory, "*.*", SearchOption.AllDirectories).Where(s => ext.Contains(Path.GetExtension(s)));
string[] fileArray = Directory.GetFiles(currentDirectory, "*.mdb", SearchOption.AllDirectories);

string allNames = "";

//Just confirmation that this actually works
foreach (string name in fileArray)
{
allNames += name + "\n";
}

MessageBox.Show(allNames);

//Now we actually need to do something
}


I also have another function that establishes a connection for specific database to make sure my code for checking the database info is valid. Unfortunately that's not the case either but here's the relevant code anyway:

private string connParam = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=PathToDatabaseFile.mdb";
private void OpenConnection()
{
firewallConn.ConnectionString = connParam;
firewallConn.Open();
successfulConnection = true;
MessageBox.Show("Connection successful!");
}

private void EstablishConnection()
{
//There's a slight delay in making the connection now that I wrapped it in exception handling
//TODO: Try to speed that up and figure out why it was slowed down by a noticable ammount
try
{
if (firewallConn != null && firewallConn.State == ConnectionState.Closed)
OpenConnection();
else
MessageBox.Show("The connection has already been established.\nType ''Close me'' to close the connection.");
}
catch (Exception exception)
{
MessageBox.Show(exception.ToString());
}
}


If any more detail is needed, just let me know and I'll provide what I can.

Answer

What about this:

// the loop
foreach (string name in fileArray)
{    
    EstablishConnection(name); // you might need a full path!
    //do search...
}

// a little change in the EstablishConnection method
private void EstablishConnection(String dbPath)
{
    string connParamTemplate = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}";
    String conn = String.Format(connParamTemplate, dbPath);

    //connect... notice the change in OpenConnection!

// a little change in the OpenConnection method
private void OpenConnection(String connParam)

I just create the connection string according to the name of the DB, and pass that to the relevant method.