I have a system that supports multiple products. Each product has its own database with the same exact schema.
When I pass in the connection string as a parameter to my Data Context constructor it always uses the default database listed in the connection string, or the default database of the user connecting if I do not provide an Initial Catalog in the connection string.
I would like to be able to have the system utilize a database without having to change the connection string and by passing in the database name as a parameter.
Here is an example of the code I am using:
class Program
{
static void Main(string[] args)
{
var d = new Data("Data Source=(LOCAL);Initial Catalog=Database1;Integrated Security=true;");
var d1 = new Data("Data Source=(LOCAL);Initial Catalog=Database2;Integrated Security=true;");
Console.ReadLine();
}
}
internal class Data
{
public Data(string connection)
{
using (var ctx = new DataClassDataContext(connection))
{
var query = from c in ctx.MyTable select c;
try
{
Console.WriteLine(query.Count());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
If this code gets executed, then the first result will pull from Database1 and the second result will pull from Database2. I would like it to have the ability to pull from a database that is not provided in the connection string. The reason for this is because the database could change based on a specific scenario but the connection string will remain the same.
Here is an example of what I am using to "fake" it, but I don't really think this is the best solution for this:
class oConnection
{
public string Server { get; set; }
public string Database { get; set; }
public bool IntegratedSecurity { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
}
class Program
{
static void Main(string[] args)
{
var d = new Data(new oConnection
{
Database = "Database1",
Server = "(Local)",
IntegratedSecurity = true
});
var d1 = new Data(new oConnection
{
Database = "Database2",
Server = "(Local)",
IntegratedSecurity = true
});
Console.ReadLine();
}
}
internal class Data
{
private static string BuildConnection(oConnection connection)
{
var sb = new StringBuilder();
sb.Append("Data Source=" + connection.Server + ";Initial Catalog=" + connection.Database + ";");
if(connection.IntegratedSecurity)
{
sb.Append("Integrated Security=true;");
}
else
{
sb.Append("user id=" + connection.UserName + ";password=" + connection.Password);
}
return sb.ToString();
}
public Data(oConnection connection)
{
using (var ctx = new DataClassDataContext(BuildConnection(connection)))
{
var query = from c in ctx.MyTable select c;
try
{
Console.WriteLine(query.Count());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
Another note: the goal of this is really to be able to support not having multiple different connection strings when running queries that will span across multiple databases. For example: If I want to query the account records from a database and then query some sort of lookup data from another database, I would have to create a new connection string for the context.
Any help would be appreciated. Thanks