Fetch data from sql database in multiple servers

Question

I am planning to design an application which would fetch data from a common database located in different servers.

Ex:

• Database A in Server SQLSRV001
• Database A in Server SQLSRV002
• etc

I have a sql select query which is to be executed in Database A located in different servers.

    select * from Database A where column1='' and column2=''

I wanted to know if it is possible to create a dynamic connection object such that it uses the same select query and takes the several server number as user (or a hard-coded) input. It executes the select query in the database and brings back the result. Then takes the next server number in line as input, fetches the data, etc.. This goes on until all the server numbers are done with.

Any insight on this would be gladly accepted.

Answer 1

yes you can do that indeed... Create a new class and make it an object of type SQLConnection or w/e it is.

It's purpose should be to create and maintain a connection with a database and perform various tasks (this part is optional, as you can directly pass in the queries).

Got this from online

class DBConnect
{
    private MySqlConnection connection;
    private string server;
    private string database;
    private string uid;
    private string password;

    //Constructor
    public DBConnect()
    {
        Initialize();
    }

    //Initialize values
    private void Initialize(String[] connectionInfo)
    {
        server = connectionInfo[0];
        database = connectionInfo[1];
        uid = connectionInfo[2];
        password = connectionInfo[3];
        string connectionString;
        connectionString = "SERVER=" + server + ";" + "DATABASE=" + 
        database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

        connection = new MySqlConnection(connectionString);
    }

    //open connection to database
    private bool OpenConnection()
    {
    }

    //Close connection
    private bool CloseConnection()
    {
    }

    //Insert statement
    public void Insert()
    {
    }

    //Update statement
    public void Update()
    {
    }

    //Delete statement
    public void Delete()
    {
    }

    //Select statement
    public List <string> [] Select()
    {
    }

    //Count statement
    public int Count()
    {
    }

    //Backup
    public void Backup()
    {
    }

    //Restore
    public void Restore()
    {
    }
}

So when you have different databases you can go like:

DBConnect srv001 = new DBConnect({server1,db1,uid1,pw1});
DBConnect srv002 = new DBConnect({server2,db2,uid2,pw2});

So now you have 2 concurrent connections to 2 different databases