How to connect to multiple SQL servers

[MikeMV]

Hello,

I am starting to learn VB2008 coming from a VF9 background, I am trying to figure how I can connect to 2 different SQL servers to extract data from both merged into one dataset. I have the first connection setup and working, but I am lost as to how to add the 2nd, I setup a class for the first connection as follows:

Imports System.Data.SqlClient

Public Class SysproDB
Public Shared Function GetConnection() As SqlConnection
Dim connectionString As String _
= "Data Source=ACCESS_SQL;Initial Catalog=SysproCompanyT;" _
& "Integrated Security=True"
Return New SqlConnection(connectionString)
End Function
End Class

Then in my code to extract data I have the following:

Imports System.Data.SqlClient

Public Class SalesOrderDB

Public Shared Function GetSalesOrder(ByVal SalesOrder As String) As SalesOrderClass
Dim SalesOrderClass As New SalesOrderClass
Dim connection As SqlConnection = SysproDB.GetConnection
Dim selectStatement As String _
= "SELECT SorMaster.SalesOrder, SorMaster.Customer, SorMaster.CustomerPoNumber, SorMaster.OrderDate, SorMaster.OrderStatus,ArCustomer.Name, SUM((SorDetail.MOrderQty*SorDetail.MPrice)) AS Gross " _
& "FROM SorMaster " _
& "INNER JOIN ArCustomer ON SorMaster.Customer = ArCustomer.Customer " _
& "INNER JOIN SorDetail ON SorDetail.SalesOrder = SorMaster.SalesOrder " _
& "WHERE SorMaster.SalesOrder = @SalesOrder " _
& "Group by SorMaster.SalesOrder,SorMaster.Customer,SorMaster.CustomerPoNumber, SorMaster.OrderDate, SorMaster.OrderStatus,ArCustomer.Name"
Dim selectCommand As New SqlCommand(selectStatement, connection)
selectCommand.Parameters.AddWithValue("@SalesOrder", SalesOrder)
Try
connection.Open()
Dim reader As SqlDataReader _
= selectCommand.ExecuteReader(CommandBehavior.SingleRow)
If reader.Read Then
SalesOrderClass.SalesOrder = CInt(reader("SalesOrder"))
SalesOrderClass.Customer = reader("Customer").ToString
SalesOrderClass.CustomerPoNumber = reader("CustomerPoNumber").ToString
SalesOrderClass.OrderDate = reader("OrderDate").ToString
' SalesOrderClass.OrderAmount = reader("OrderAmount").ToString
SalesOrderClass.OrderStatus = reader("OrderStatus").ToString
SalesOrderClass.Name = reader("Name").ToString
SalesOrderClass.Gross = reader("Gross").ToString
Else
SalesOrderClass = Nothing
End If
reader.Close()
Catch ex As SqlException
Throw ex
Finally
connection.Close()
End Try
Return SalesOrderClass
End Function
End Class

I will greatly appreciate any feedback you can provide. I am also curious to know if it would be more efficient to merge the data into 1 server from the point of view of VB?
I would not want to load the one server too much, but I am curious to know what the pros and cons are.

Thanks,

Mike.

 

[gmmastros]

My suggestion would be to do this at the database level. The database is likely to be more efficient at querying across servers than any vb code.

I encourage you to do a little research on "SQL SERVER LINKED SERVERS".

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom