how is SQLConenction associated to a Database

[Abhishek]

Hello
When we open a connection using some connectionstring the connection is
associated to a database which we specify in the ConnectionString. Now
my question is
1) When i do a changedatabase does the connection object closes the
connection to the server and opens the connection again for a new
Database or does it somehow keep the connection open and somehow just
changes the database it is pointing to.
2) When we do a changedatabase on the connection what happens to the
Transaction context in which the Connction was. Does it provide us with
a new Transaction Context or does it continue with the previous
transaction context.
Any comments would really help

Thanks
Abhishek

 

[Jim Hughes]

The connection stays open and the transaction context remains the same when
using ChangeDatabase

Note, you don't have to use ChangeDatabase, you could use the three part
notation (database.owner.object)

select * from pubs.dbo.authors

select * from northwind.dbo.customers

Try this sample code to demonstrate the connection

Dim conn As New
SqlClient.SqlConnection("Server=(local);Database=Pubs;Integrated
Security=True")

conn.Open()

Dim tran As SqlClient.SqlTransaction = conn.BeginTransaction()

Trace.WriteLine(conn.Database)

conn.ChangeDatabase("Northwind")

Trace.WriteLine(conn.Database)

tran.Rollback()

conn.Close()