Sqlconnection class --- establish a connection with sqlserver

 

1. sqlconnection

• Syntax: Public sealed class sqlconnection: dbconnection, icloneable
• Indicates the connection to the SQL Server database. This class cannot be inherited.
• Namespace: system. Data. sqlclient
  Assembly: system. Data (in system. Data. dll)
• The sqlconnection object indicates a unique session with the SQL server data source. In the Client/Server database system, it is equivalent to a network connection to the server. Sqlconnection and sqldataadapter
  Used with sqlcommand to improve performance when connecting to the Microsoft SQL Server database.
• When creating an instance of sqlconnection, all attributes are set to their initial values, as shown in the following table. Only
  The connectionstring attribute changes the value of these attributes.

Connectionstring	Null String ("")
Connectiontimeout	15
Database	Null String ("")
Datasource	Null String ("")

 

• If sqlconnection is out of the range, it will not be closed. Therefore, you must explicitly close the connection by calling close or dispose. The function of close is equivalent to that of dispose. To ensure that the connection is always closed, open the connection in the using block, as shown in the following code segment. This ensures that the connection is automatically closed when the code exits the code block.

For example:

Using (sqlconnection connection = new sqlconnection (connectionstring ))

{

Connection. open ();

// Do work here; Connection closed on following line.

}

• If the sqlcommand method is executed to generate sqlexception, when the severity level is less than or equal
  19, sqlconnection will remain open. When the severity level is greater than or equal to 20, the server usually closes sqlconnection. However, you can re-open the connection and continue the operation.
• The following example creates a sqlcommand and a sqlconnection. Open sqlconnection and set it to sqlcommand
  . This example then calls executenonquery.
  To complete this task, a connection string and a query string are passed for executenonquery, which is a Transact-SQL insert statement. When the code exits with blocks, the connection is closed automatically.

Private Static void createcommand (string querystring,

String connectionstring)

{

Using (sqlconnection connection = new sqlconnection (

Connectionstring ))

{

Sqlcommand command = new sqlcommand (querystring, connection );

Command. Connection. open ();

Command. executenonquery ();

}

}

 

 

2. Two Constructors

 

(1) Public sqlconnection ()

(2) Public sqlconnection (string connectionstring)

 

Iii. common attributes

1. Get the current connection status ------- state

[Browsableattribute (false)]

Public override connectionstate state {Get ;}

Enumerative member of connectionstate

Closed	The connection is closed.
Open	The connection is open.
Connecting	The connection object is connecting to the data source. (This value is reserved for future versions of this product .)
Executing	The connection object is executing the command. (This value is reserved for future versions of this product .)
Fetching	The connection object is retrieving data. (This value is reserved for future versions of this product .)
Broken	The connection to the data source is interrupted. This can only happen after the connection is opened. You can close the connection in this status and then open it again. (This value is reserved for future versions of this product .)

2. Obtain or set the string used to open the SQL Server database. ----------- Connectionstring

[Settingsbindableattribute (true)]

Public override string connectionstring {Get; set ;}

The connection string, which contains the source database name and other parameters required to establish the initial connection.

If the value of "Persist Security Info" is set to false (default), the returned connection string is the same as the connectionstring set by the user, but the security information is removed. Unless "Persist Security Info" is set to true, the SQL server. NET Framework data provider does not maintain or return the password in the connection string.

The connectionstring attribute can be set only when the connection is closed.

3. Obtain the name of the SQL server instance to be connected. --------- Datasource

[Browsableattribute (true)]

Public override string datasource {Get ;}

If the connection string of sqlconnection is "context connection = true", datasource
The property returns NULL.

4. Obtain the name of the current database or the name of the database to be used after the connection is opened. ---- Datebase

Public override string database {Get ;}

The database attributes are dynamically updated. If you use a Transact-SQL statement or changedatabase
Method to change the current database, an information message is sent and this attribute is automatically updated.

5. Obtain the waiting time (in seconds ). ----------- Connectiontimeout

public override int ConnectionTimeout { get; }

 

Iv. Common Methods

1. Open the connection with the database --------- open

Public override void open ()

2. Close the connection to the database -------- close

Public override void close ()

This method is the preferred method to close any opened connection.

The application can call close multiple times. No exception is generated.

 

3. Public void dispose ()

Released
All resources used by component

 

4. Differences between the close () method and dispose () method for closing a connection

The close method is used to close a connection, while the dispose method not only closes a connection, but also clears the resources occupied by the connection. After closing the connection by using the close method, you can call the Open Method to open the connection. If you use the dispose method to close the connection, you cannot directly call the Open Method to open the connection again. You must initialize the connection and then open it to re-establish the sqlconnection object.

 

5. Create and returnSqlconnection
AssociatedSqlcommandObject -------- createcommand

Public sqlcommand createcommand ()