Home > Articles > Data > SQL Server

This chapter is from the book

This chapter is from the book

Developer's Guide to SQL Server 2005, ADeveloper's Guide to SQL Server 2005, A

Learn More Buy

This chapter is from the book

This chapter is from the book

Developer's Guide to SQL Server 2005, ADeveloper's Guide to SQL Server 2005, A

Learn More Buy

Connections

As already mentioned, when you are at server side and a client executes, you are part of that client’s connection context, which in SQL Server 2005 is exposed by using a special connection string. The SqlConnection object exposes the public methods, properties, and events listed in Table 4-2. (Note that the table doesn’t show members inherited from System.Object.)

Table 4-2: Public Members of SqlConnection

Name

Return Value/Type

Member Type

Constructor

 

Constructor

Constructor(String)

 

Constructor

BeginTransaction()

SqlTransaction

Method

BeginTransaction(IsolationLevel)

SqlTransaction

Method

BeginTransaction(IsolationLevel, String)

SqlTransaction

Method

BeginTransaction(String)

SqlTransaction

Method

ChangeDatabase(String)

void

Method

ChangePassword(String, String)

void

Static Method

ClearAllPools

void

Static Method

Close()

void

Method

CreateCommand()

SqlCommand

Method

EnlistDistributedTransaction(ITransaction)

void

Method

EnlistTransaction(Transaction)

void

Method

GetSchema()

DataTable

Method

GetSchema(String)

DataTable

Method

GetSchema(String, String[])

DataTable

Method

Open()

void

Method

ResetStatistics

void

Method

RetrieveStatistics

Hashtable

Method

ConnectionString

String

Property

ConnectionTimeout

Int32

Property

Database

String

Property

DataSource

String

Property

FireInfoMessageOnUserErrors

Boolean

Property

PacketSize

Int32

Property

ServerVersion

String

Property

State

String

Property

StatisticsEnabled

Boolean

Property

WorkStationId

String

Property

InfoMessage

SqlInfoMessage EventHandler

Event

You can create only one SqlConnection at a time with the special "context connection=true" string. Attempting to create a second SqlConnection instance will fail, but you can create an "internal" SqlConnection and another external SqlConnection back to the same instance using an ordinary connection string. Opening this additional SqlConnection will start a distributed transaction, however,2 because you have multiple SPIDs (SQL Server sessions) possibly attempting to update the same data. There is no way to knit the two sessions together through the ADO.NET API into a single local transaction, however, as you can in an extended stored procedure with sp_bindtoken. You can call the SqlConnection’s Close() method and reopen it, if you like, although it’s unlikely that you ever actually need to do this. Keeping the SqlConnection open doesn’t use any additional resources after you originally refer to it in code.

Although the same System.Data.SqlClient.SqlConnection class is used for both client and server code, some of the features and methods will not work inside the server:

  • ChangePassword method
  • GetSchema method
  • Connection pooling and associated parameters and methods
  • Transparent failover when database mirroring is used
  • Client statistics
  • PacketSize, WorkstationID, and other client information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.