Syntax for 2 sql connections in a return statement

Question

I have a grid view that is being populated from 1 connected table. Code below:

    private void BindGridContent()
    {
        DataTable dtInfo = GetContent();

        if (dtInfo.Rows.Count > 0)
        {
            // Bind Data
            uxNoticeGv.DataSource = dtInfo;
            uxNoticeGv.DataBind();

            // Show GridView
            uxNoticeGv.Visible = true;

            // Hide Grid Message
            uxGridMessageLbl.Visible = false;
        }
        else
        {
            // Hide GridView
            uxNoticeGv.Visible = false;

            // Display Grid Message
            uxGridMessageLbl.Visible = true;
        }

        uxGridViewHeader.SetGridViewHeader(uxNoticeGv, dtInfo.Rows.Count);
    }

The issue that I'm having is that the current table doesn't have the description to some of the fields i'm returning in my gridview. ID values are being displayed instead. I also can't join the table unless I create a linked server, which I don't have permission to do. (Linked Server answers are ruled out)

Below is my data access class(layer which returns my current table):

public static DataTable ViewLogSearch(string lineNumber, DateTime createdDateBegin, string detailPurposeOrFunction, string connectionTypeDesc, DateTime createdDateEnd, string machineServerConnection)
       {
           var sqlStatement = new StringBuilder();


       sqlStatement.Append(" SELECT");
       sqlStatement.Append("   ae.AccountEntryID, ae.LastUpdatedBy, ae.PurposeOrFunctionDialInDesc, ae.PhysicalLocationDesc, ");
       sqlStatement.Append("   ae.PurposeOrFunctionDialOutDesc, ae.PasswordChangeFrequency, ae.OtherLayerAuthenticationDesc, ");
       sqlStatement.Append("   ae.TypeOfConnectionID, ae.CreatedDate, ct.ConnectionTypeDesc,");
       sqlStatement.Append("   ae.LineNumber, ae.DetailPurposeOrFunction");
       sqlStatement.Append(" From");
       sqlStatement.Append("   dbo.AccountEntry ae");
       //Added After
       sqlStatement.Append(" Join");
       sqlStatement.Append("   dbo.ConnectionType ct");
       sqlStatement.Append(" on ae.TypeOfConnectionID = ct.ConnectionTypeID");
       sqlStatement.Append(" WHERE 1=1 ");

       // SQL parameter collection
      var sqlParams = new List<SqlParameter>();

       // Commented out excess logic but I am passing parameters         
       // Create sql command
       var sqlCmd = new SqlCommand(sqlStatement.ToString());

       // Add sql parameters to sql command
       sqlCmd.Parameters.AddRange(sqlParams.ToArray());

       // Execute sql
       return DBAccess.SQLServer.GetDataTable(DBAccess.SQLServer.GetConnectionString("AccountDB"), sqlCmd);
   }

I can query the other database. So what I'm thinking is that I'll query the other database and return the field I'm interested in. That would entail that I have 2 connection strings.

Any ideas on how to create 2 connection strings for 1 method? Please note the joined table is not the table I'm interested in bringing in. I'm open for other approaches to this issue. Please provide psuedo code.

What kind of database is the _other database_? If it is another SQL Server DB is it in the same instance? — Steve, Aug 07 '15 at 18:04
The "Other Database" is one that sits on a different server. — user5085862, Aug 07 '15 at 18:45

score 0 · Answer 1 · edited May 23 '17 at 12:21

0

Since you are are loading DataTables, a better approach might be to keep two separate functions - one for each set of data. This is in keeping with the Single Responsibility Principle (SRP), which is an encouragement to keep small functions that do just one job each.

Third functions calls the two and then uses Linq to join the data. See this SO post for more on DataTable joins.

edited May 23 '17 at 12:21

Community

1
1

answered Aug 07 '15 at 18:20

sfuqua

5,797
1
32
33

Syntax for 2 sql connections in a return statement

1 Answers1