C# SQL Server connection string with multiple databases on same server

Question

I have my SQL Server connection string set up like this:

String strConnection = @"Data Source=servername;Initial Catalog=dbname; User ID =user; Password =pass;";

Then I have a combobox which shows all tables in that database. Connection string works fine with one database (initial catalog). But what if I want to pull tables from 2 databases on the same server. User in SQL Server has access to both databases. What connection string do I use then?

The easy way would be Initial Catalog=dbname,db2name. But that does not work of course.

Answer 1

If that user does have in fact permissions to access the other database - you could just simply use the ChangeDatabase method on your SqlConnection - like this:

string strConnection = @"Data Source=servername;Initial Catalog=dbname; User ID =user; Password =pass;";

using (SqlConnection conn = new SqlConnection(strConnection))
{
     // do what you want to do with "dbname"
     ......

     // switch to "db2name" - on the same server, with the same credentials 
     conn.ChangeDatabase("db2name");

     // do what you want to do with "db2name"
     ......
}

Answer 2

I ended up using two comboboxes on my form. One for databases and one for tables. When i choose a database in first combobox, the second one automaticly shows tabels in that database. It is much easier for me to work wit two comboboxes using different connections. Here is some part of my code with solution:

 public partial class Form1 : Form                  
    {
    SqlDataAdapter sda;
    SqlCommandBuilder scb;
    DataTable dt;

    SqlDataAdapter sda2;
    SqlCommandBuilder scb2;
    DataTable dt2;

    public Form1()
    {
        InitializeComponent();
    }
//ON FORM LOAD
 private void Form1_Load(object sender, EventArgs e)
        {
            String stringConnection = @"Data Source=SERVER_NAME;    Initial Catalog =DB_NAME; User ID =USER; Password =PASS;";
            SqlConnection con2 = new SqlConnection(stringConnection);

            try
            {
            con2.Open();
            SqlCommand sqlCmd2 = new SqlCommand();
            sqlCmd2.Connection = con2;
            sqlCmd2.CommandType = CommandType.Text;
            sqlCmd2.CommandText = "SELECT name FROM sys.databases EXCEPT SELECT name FROM sys.databases WHERE name='master' OR name='model' OR name='msdb' OR name='tempdb'";
            SqlDataAdapter sqlDataAdap2 = new SqlDataAdapter(sqlCmd2);
            DataTable dtRecord2 = new DataTable();
            sqlDataAdap2.Fill(dtRecord2);
            dtRecord2.DefaultView.Sort = "name ASC";
            comboBox2.DataSource = dtRecord2;
            comboBox2.DisplayMember = "NAME";
            comboBox2.DisplayMember = "NAME";
            con2.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
//BUTTON FOR SHOWING TABELS IN DATAGRIDVIEW
private void ShowTbl_Click(object sender, EventArgs e)
    {
        string selected = this.ComboBox1.GetItemText(this.ComboBox1.SelectedItem);
        string DBselected = this.comboBox2.GetItemText(this.comboBox2.SelectedItem);
        SqlConnection con = new SqlConnection(@"Data Source=SERVER_NAME;Initial Catalog =" + DBselected + "; User ID=USER;Password=PASS;");
        sda = new SqlDataAdapter(@"SELECT *  FROM dbo.[" + selected + "]", con);
        dt = new DataTable();
        sda.Fill(dt);
        dataGridView1.DataSource = dt;
        string ComboBoxSelected = ComboBox1.GetItemText(ComboBox1.SelectedItem);
        con.Close();
    }
//WHEN I SELECT DATABASE IN COMBOBOX2, COMBOBOX1 DISPLAYS TABLES IN THAT DATABASE
private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
    {
        string selectedbase = this.comboBox2.GetItemText(this.comboBox2.SelectedItem);
        string aa = comboBox2.SelectedText;
        String strConnection = @"Data Source=SERVER_NAME;Initial Catalog =" + selectedbase+ "; User ID =USER; Password =PASS;";
        SqlConnection con = new SqlConnection(strConnection);
        try
        {
            con.Open();
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.Connection = con;
            sqlCmd.CommandType = CommandType.Text;
            sqlCmd.CommandText = "Select table_name from information_schema.tables";
            SqlDataAdapter sqlDataAdap = new SqlDataAdapter(sqlCmd);
            DataTable dtRecord = new DataTable();
            sqlDataAdap.Fill(dtRecord);
            dtRecord.DefaultView.Sort = "table_name ASC";
            ComboBox1.DataSource = dtRecord;
            ComboBox1.DisplayMember = "TABLE_NAME";
            ComboBox1.DisplayMember = "TABLE_NAME";

            con.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }
}

Answer 3

Edit Start

1) If you want to show all the tables in all the data bases please populate the combo box the same. refer: How do I list all tables in all databases in SQL Server in a single result set?

2) Or you can provide another combo box to display list of data bases, prior to tables combo box, once user chooses a DB name, pass it to the query. This is useful choice, as there is a possibility of having a table with same name in two data bases. (Eg: t_users in db_1 and db_2)

3) If you have confined to two data bases, use where clause in the above said (1)

Edit end

If you want to access multiple data bases of one server and one instance, It is enough to have one connection to one database in that server and instance, provided user has access to those DBs. You can query any data base if all DB are in same server and instance, see example below.

SELECT a.ID, b.ID
FROM Database1.dbo.table1 a
INNER JOIN Database2.dbo.table2 b on a.ID = b.ID

In your case, you can have your query of stored procedure similar to below queries.

select * 
from sys.tables --to fetch list of tables from the DB which you app connected to 

select * 
from IAMS_Discr_Complaints.sys.tables --to fetch tables from another DB on the same server