how is SQLConenction associated to a Database

29 Mar 2005 1:39 PM

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

29 Mar 2005 3:08 PM

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()

Show quote

"Abhishek" <luckyabhis***@gmail.com> wrote in message
news:1112103577.890745.84380@l41g2000cwc.googlegroups.com...
> 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
>