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

Gaurav · Accepted Answer · 2019-01-18T06:37:29.820

0

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;
    }


}

edited Jan 18 '19 at 06:37

answered Jan 17 '19 at 06:35

Gaurav

782
5
12

this is what i mean sir, i want to query that search each server and database if there's no result in server 1 it will proceed to server 2 and if server 2 found it will not go to server 3 – Milestone89 Jan 18 '19 at 00:05
How you will identify that you have got the result, I mean you did you not provide the code for search? – Gaurav Jan 18 '19 at 04:27
public void Tlogging() { MyDatabaseConnection myCon = new MyDatabaseConnection(); SqlConnection sqlCon = new SqlConnection(myCon.MyDTConn); try { sqlCon.Open(); if ((dtblMonitor == null) || (dtblMonitor.Rows.Count == 0)) { MessageBox.Show("SEARCH OTHER DATABASE"); Tlogging(); } – Milestone89 Jan 18 '19 at 05:40
basically it will show in dataGridView result as my query. it will search on the 1st database if there's no result in datagrid it will move to other database. can you give me example of that code. – Milestone89 Jan 18 '19 at 05:43
What is dtblMonitor in you code? I can not find its declaration. – Gaurav Jan 18 '19 at 05:46
sqlCon.Open(); SqlDataAdapter sqlDaMonitor = new SqlDataAdapter("select * from TLogging where BatchNumber like '%"+_batch+"%' ", 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); Tlogging(); } – Milestone89 Jan 18 '19 at 06:12
I have update code as your requirements. Please check and if it helps then please mark it as answer. – Gaurav Jan 18 '19 at 06:37
hi Gaurav thank you very much for the effort but how can i view my result in dataGridView? and also how to trigger the result? i want to use button to see the result using that which database it catch thank you so much in advance if you have time you can add me to skype gary.john.yu – Milestone89 Jan 19 '19 at 00:44
I have added you on skype my id is valueclients. – Gaurav Jan 19 '19 at 08:21
done adding you up, one last left is how to view my query in datagridview – Milestone89 Jan 20 '19 at 04:02
thank you this is ok now forgot to comment back – Milestone89 Nov 23 '21 at 02:58

How to connect multi server and database in C#

1 Answers1