Connect to multiple databases with the same structure using LINQ to SQL

In actual work, we may encounter a situation where multiple databases with the same structure are linked in the same program. Although I think this is not a good design, but this may not be under your control. For example, if your company has two customers and uses the same system, if they do not separate their databases, it may lead to difficulties in functional design and data confusion. Therefore, two sets of databases are generated with the same structure, but the data is different. Now, you need to design an external interface for other programs. The program may request data from database a or database B. How can this problem be solved?

Obviously, it is okay to use different connection strings, but it takes a little bit of effort to make the code more neat. The demo I wrote is as follows:

First, create a few simple databases for testing in the local SQL Express, create a simple table, and insert two simple records at will:

Create Database testdb1gouse testdb1gocreate table tb_user (user_id int identity, user_name nvarchar (20) not null, real_name nvarchar (25) not null, descriptions nvarchar (400) null ,) goinsert into tb_user (user_name, real_name, descriptions) values ('jgg ', 'jiang guogang', 'canononname') insert into tb_user (user_name, real_name) values ('zs ', 'zhang San ') gocreate database testdb2gouse testdb2gocreate table tb_user (user_id int identity, user_name nvarchar (20) not null, real_name nvarchar (25) not null, descriptions nvarchar (400) null ,) goinsert into tb_user (user_name, real_name, descriptions) values ('us', 'Uncle Sam ', 'America') insert into tb_user (user_name, real_name) values ('jp ', 'Japan ') gocreate database testdb3gouse testdb3gocreate table tb_user (user_id int identity, user_name nvarchar (20) not null, real_name nvarchar (25) not null, descriptions nvarchar (400) null ,) goinsert into tb_user (user_name, real_name, descriptions) values ('abcd', 'efg', 'hijklmn ') insert into tb_user (user_name, real_name, descriptions) values ('opq ', 'rst', 'uvwxyz ') Go

Configure your app. config:

<? XML version = "1.0" encoding = "UTF-8"?> <Configuration> <connectionstrings> <Add name = "testdb1" connectionstring = "Data Source =. \ sqlexpress; initial catalog = testdb1; Integrated Security = true "providername =" system. data. sqlclient "/> <Add name =" testdb2 "connectionstring =" Data Source =. \ sqlexpress; initial catalog = testdb2; Integrated Security = true "providername =" system. data. sqlclient "/> <Add name =" testdb3 "connectionstring =" Data Source =. \ sqlexpres S; initial catalog = testdb3; Integrated Security = true "providername =" system. Data. sqlclient "/> </connectionstrings> <! -- Omitted many contents --> </configuration>

Create a tool class to help you generate datacontext objects linked to different libraries (so your code is much more neat ):

using System;using System.Data.Linq;using System.Diagnostics;using System.Configuration;namespace MultiDbmsConnectionsDemo{    public static class DataContextFactory    {        public static DataContext Create(Type typeDataContext, string strDbName)        {            Debug.Assert(typeDataContext.IsSubclassOf(typeof(DataContext)));            string strDbConn;            try            {                strDbConn = ConfigurationManager.ConnectionStrings[strDbName].ConnectionString;            }            catch (Exception)            {                return (DataContext) Activator.CreateInstance(typeDataContext);            }            return (DataContext)Activator.CreateInstance(typeDataContext, strDbConn);        }    }}

Activator is used, and activator creates the corresponding datacontext instance based on the input type. Of course, you can also use the generic type; in addition, if the name of the input connection string does not exist in the configuration file, the default link is used, that is, the parameter is not included when the datacontext is created.

This is an example:

// The general practice is: // linquserdatacontext DB = new linquserdatacontext (); // and now it is: linquserdatacontext DB = (linquserdatacontext) datacontextfactory. create (typeof (linquserdatacontext), combodbmschoosing. text );

TIPS: I hope your code can be better.

Complete code: Download

The Code uses a help class of LINQ to SQL. For details, refer to my another blog: The usage tutorial of LINQ to SQL.