I have a DBML on a single database in a named instance. The instance has a linked server to another MSSQL database in another server/instance. Both instances and databases have the same dbo-level user. Both databases are MSSQL 2008.
I have some C# code (Framework 3.5) that uses the DBML that accesses the tables and views and sprocs on DatabaseA. I now need to access a table on DatabaseB. What is the smartest way for my code to get to the table/entity over the linked server connection?
Thanks.
One clean way of doing this is to create views inside DatabaseA that encapsulate enities on the other side. You will have to manually define the primary keys and relationships for these entities in your .dbml file. Once this is done they can work just like any other table with CRUD functionality as long as the DTC service is running on DatabaseA.
Try adding a linked server to your local:
EXEC sp_addlinkedserver
@server=N'SERVER',
@srvproduct=N'',
@provider=N'SQLNCLI',
@datasrc=N'SERVER';
SELECT * FROM sys.servers
EXEC sp_addlinkedsrvlogin '<SERVER>', 'false', '<DOMAIN>\<USERNAME>', '<USER>', '<PASSWORD>';
And access your local referring to the linked server:
SELECT * FROM SERVER.DB.SCHEMA.OBJECT
I had used SQL Synonyms in Entity Framework and LINQ-To-SQL, you can create a SQL Synonnym to point to a linked Server object, like this:
And then perform a SQL Query:
Northwnd db = new Northwnd(@"c:\northwnd.mdf");
IEnumerable<Customer> results = db.ExecuteQuery<Customer>
("SELECT contactname FROM customersSynonym WHERE city = {0}",
"London");
You can read the documentation here and also you can read another question like this one but using Entity Framework, which uses the same principle, using a SQL Synonym.
