We help IT Professionals succeed at work.

VS 2008 C# SQL Connections with multiple instances

If you look at my SQL statement you will see it reference two tables:
1) POP10100
for this client the POP10100 table exists in one instance of SQL and the POHEADER table exists in another instance.  If they were both in the same connection, a single dataconnection would suffice. However, in this case I have two separate instances of SQL. I would prefer not to have to hardcode the instance names. How can I make this code portable so when delivered through ini files or whatever technique that works best, my code could read a configuration file and pass the appropriate connection strings into the code.
public partial class frmPOsNotTransferred : DexUIForm
        /* Define a string variable to hold connection string */
        string ConnectionString;

        /* Set up SQL Connection Handlers */
        private System.Data.SqlClient.SqlConnection DataConnection;
        private System.Data.DataSet DataDataSet;
        private System.Data.SqlClient.SqlCommand DataCommand;
        private System.Data.SqlClient.SqlDataAdapter DataDataAdapter;
        public frmPOsNotTransferred()

                SqlConnection DataConnection = GetConnection();

                /* Define dataset for GL distribution amounts */
                DataDataSet = new System.Data.DataSet();
                DataDataSet.CaseSensitive = false;

                DataCommand = new System.Data.SqlClient.SqlCommand();
                DataCommand.Connection = DataConnection;
                DataCommand.CommandText = "select case when charindex('-', POH.ponum) = 0 then POH.ponum else " +
                "left(POH.ponum,patindex('%-%',POH.ponum)-1) end,VENDORID,CONVERT(CHAR(10),PODATE,101), " +
                "POH.JOBNUMBER from [gse2000server].[gse2000sql].[dbo].[poheader] POH, [gse2000server].[gse2000sql].[dbo].[JOBS] JOB " +
                "where POH.ponum not in (select ponumber from [gse].[dbo].[pop10100] union all " +
                "select ponumber from [gse].[dbo].[pop30100]) AND POH.JOBNUMBER=JOB.JOBNUMBER " +
                "and JOB.CONTRACTCOMPLETE=0 ORDER BY convert(datetime,convert(char(10),PODATE,101),101) desc"; 

                DataDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                DataDataAdapter.SelectCommand = DataCommand;
                dgvViewPOs.DataSource = DataDataSet.Tables[0].DefaultView;
                dgvViewPOs.ReadOnly = true;
                dgvViewPOs.RowHeadersVisible = false;
                dgvViewPOs.AllowUserToAddRows = false;
                dgvViewPOs.Columns[0].Width = 100;
                dgvViewPOs.Columns[0].HeaderText = "PO Number";

                dgvViewPOs.Columns[1].Width = 100;
                dgvViewPOs.Columns[1].HeaderText = "Vendor ID";

                dgvViewPOs.Columns[2].Width = 100;
                dgvViewPOs.Columns[2].HeaderText = "PO Date";

                dgvViewPOs.Columns[3].Width = 100;
                dgvViewPOs.Columns[3].HeaderText = "Job Number";
            catch (Exception ex)
                MessageBox.Show("Error: " + ex.Message);

        private void btnExit_Click(object sender, EventArgs e)

        private SqlConnection GetConnection()
            /* Create reader and open file containing connection string */
            TextReader tr = new StreamReader(@"C:\Program Files\Microsoft Dynamics\GP\Addins\GSE.INI");

            /* Read line containing the connection string */
            ConnectionString = tr.ReadLine();

            /* Close the stream */

            DataConnection = new System.Data.SqlClient.SqlConnection(ConnectionString);

            return DataConnection;

Open in new window

Watch Question

Expert of the Quarter 2010
Expert of the Year 2010
If one of them is SQL Server 2005, then you can create a SYNONYM in the database to the table in the other server.
Instead of configuring the application (.config file), you maintain the SYNONYM in the database.

CREATE SYNONYM other_poheader for [gse2000server].[gse2000sql].[dbo].[poheader]
CREATE SYNONYM other_JOBS for [gse2000server].[gse2000sql].[dbo].[JOBS]

Your query becomes

                "POH.JOBNUMBER from other_poheader POH, other_JOBS JOB " +


That is cool. So since both servers are SQL Server 2005, I create the synonym in each copy of SQL, make one adjustment to my code and I am done. I will need to put this synonym creation into my installation routines.


That worked!