C# all database connections in a directory

Question

I currently have a directory with many project folders, and each project folder contains a MysqlCE database. These databases all have the same tables; they are identical aside from the data. My goal is to display the data from _Users tables from each of the databases inside a root directory tree.

I have limited experience with C#, but I am confident I can pull this off with a little research. However, I'd appreciate any pointers - since project folders could be added in the future, I would need the application to "scan" for databases at every run (if this is even possible).

Is this sort of dynamic database connection possible with C#? Can I use multiple data sources together in, say, a dataGridView?

Don't keep multiple connections open. .NET ADO.net objects are designed to be short lived. You could however keep a dictionary of database connection strings. — Scott Chamberlain, Apr 20 '15 at 15:23
I don't think this approach will scale. One approach might be to use LINQ to join the database tables together. — neontapir, Apr 20 '15 at 15:24

score 1 · Answer 1 · edited May 23 '17 at 10:10

I guess you have a solution with multiple projects and every project has a .config file where is placed the connection string. You can create a shared .config file for multiple projects in the solution but this have it issues: -config file must be for each application, what about if in one project you need to put something in the .config file that breaks other projects?. You can use a shared resource for multiple projects but i think(i'm not sure) if you want to change the values you'll have to compile again. Anyway. To use resource file you can see this: How to use shared resource file between projects in one solution?

You can read this too: Placing Database Connection Strings in .resx file

And for shared .config files: Common config file in a multiple-project solution?

score 0 · Accepted Answer · answered Apr 20 '15 at 15:41

A T4 template could gather the full path of the Databases.

<#@ template debug="true" hostSpecific="true" #>
    <#@ output extension=".cs" #>
    <#@ Assembly Name="System.Core" #>

    <#@ import namespace="System" #>
    <#@ import namespace="System.IO" #>
    <#@ import namespace="System.Collections.Generic" #>

    <# 
    string path  = @"~\YourProjectPath";
    List<string> dbFiles = GetDBFilesRecursive(path)
    #>
    //Your class code goes here
    <#+
    public List<string> GetDBFilesRecursive(string path)
    {

        List<string> files = new List<string>();

        try
        {
            string[] fileEntries = Directory.GetFiles(path);
            foreach (string fileName in fileEntries)
                files.Add(fileName);
            string [] subdirectoryEntries = Directory.GetDirectories(path);
            foreach(string subdirectory in subdirectoryEntries)
                files.AddRange(GetDBFilesRecursive(subdirectory));
        }
        catch(Exception e)
        {
            throw new Exception("exception occured in tt gen",e );
        }
        return files;
    }
#>

I've already got a list of all database paths, my problem is working with the data in these databases — fpscolin, Apr 20 '15 at 15:49
If you've already got a list of all database paths, why do you need to scan for them? — lloyd, Apr 20 '15 at 15:51
I got the scan working shortly after posting this. Now that I have the paths to databases, I don't know how to interact with them (extract table data, modify rows, add rows) — fpscolin, Apr 20 '15 at 15:54
One option is [Entity Framework](http://www.entityframeworktutorial.net/) which has some nice tutorials. — lloyd, Apr 20 '15 at 16:00

C# all database connections in a directory

2 Answers2