How to connect multi server and database in C#

Question

public class MyDatabaseConnection
{
    string connectionString = "Data Source= my DS3;Initial Catalog = MyCATA;Persist Security Info=True;User ID=sa;Password=mypsw*";

    public MyDatabaseConnection(string connectionString)
    {
        this.connectionString = connectionString;
        // create a database connection perhaps
    }

    // some methods for querying a database
    public void execute(string query) { }
}

and this is my code

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    string connectionString ;

    public void searchOnAllDatabases(string query)
    {

        MyDatabaseConnection con1 = new MyDatabaseConnection("Data Source= 10.232.1.15\\SERVER1;Initial Catalog = My Catalog;Persist Security Info=True;User ID=sa;Password=myPSW");  //----1st search here 
        MyDatabaseConnection con2 = new MyDatabaseConnection("Data Source= 10.232.1.15\\SERVER2;Initial Catalog = My Catalog;Persist Security Info=True;User ID=sa;Password=myPSW");  //---- 2nd search here 
        MyDatabaseConnection con3 = new MyDatabaseConnection("Data Source= 10.232.1.15\\SERVER3;Initial Catalog = My Catalog;Persist Security Info=True;User ID=sa;Password=myPSW");  //---- 3rd search here 

        MyDatabaseConnection[] cons = new MyDatabaseConnection[] { con1, con2, con3 };

        foreach (MyDatabaseConnection con in cons)
        {


            MessageBox.Show(Convert.ToString(cons)); //--to see the result only
        }

    }

My objective is when I search this filename it will search entire database from different server. example it will search on server 1 database if not found it will proceed to next server 2 databases to server 3 database if found it will give me the result in datagridview

Answer 1

You can change your form some thing like this :

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }


public void searchOnAllDatabases(string query)
{

MyDatabaseConnection con1 = new MyDatabaseConnection("Data Source= 10.232.1.15\\SERVER1;Initial Catalog = My Catalog;Persist Security Info=True;User ID=sa;Password=myPSW");  //----1st search here 
    MyDatabaseConnection con2 = new MyDatabaseConnection("Data Source= 10.232.1.15\\SERVER2;Initial Catalog = My Catalog;Persist Security Info=True;User ID=sa;Password=myPSW");  //---- 2nd search here 
    MyDatabaseConnection con3 = new MyDatabaseConnection("Data Source= 10.232.1.15\\SERVER3;Initial Catalog = My Catalog;Persist Security Info=True;User ID=sa;Password=myPSW");  //---- 3rd search here 

MyDatabaseConnection[] cons = new MyDatabaseConnection[] { con1, con2, con3 };

foreach (MyDatabaseConnection con in cons)
{
    var result = con.execute(query);
    if (result)
       break;
}

 }
}

Here you can update your search code:

public class MyDatabaseConnection
{

string connectionString = "Data Source= my DS3;Initial Catalog = MyCATA;Persist Security Info=True;User ID=sa;Password=mypsw*";

public MyDatabaseConnection(string connectionString)
{
    this.connectionString = connectionString;
    // create a database connection perhaps
}

    // some methods for querying a database
 public bool execute(string query)
    {
        SqlConnection sqlCon = new SqlConnection(connectionString);
        try
        {
            sqlCon.Open();
            SqlDataAdapter sqlDaMonitor = new SqlDataAdapter("select * from TLogging where BatchNumber like '%" + query + "%' ", sqlCon);
            DataTable dtblMonitor = new DataTable();
            sqlDaMonitor.Fill(dtblMonitor);
            if ((dtblMonitor == null) || (dtblMonitor.Rows.Count == 0)) {
                MessageBox.Show("SEARCH OTHER DATABASE");
                myCon.Val += 1;
                MessageBox.Show(myCon.MyDTConn);

                return false;
            }


        }
        catch(Exception ex)
        {
            return false;
        }
        return true;
    }


}