[Reprinted] ASP. net2.0 connection to SQL Server database details

This article describes in detail how to use a connection object to connect to a database. For different. NET data providers, ADO. net uses different connection objects to connect to the database. These connection objects shield us from specific implementation details and provide a unified implementation method.

There are four connection types: sqlconnection, oledbconnection, odbcconnection, and oracleconnection.

Objects in the sqlconnection class are connected to the SQL Server database; objects in the oracleconnection class are connected to the Oracle database;

Objects in the oledbconnection class are connected to databases that support ole db, such as Access. Objects in the odbcconnection class are connected to any databases that support ODBC. All communication with the database is ultimately completed through the connection object.

　　Sqlconnection

Connection is used to "talk" to the database and is represented by a Class (such as sqlconnection) of a specific provider. Although the sqlconnection class is for SQL Server, many attributes and methods of this class are similar to events and oledbconnection and odbcconnection. This chapter will focus on the specific attributes and methods of sqlconnection. For other connection classes, you can refer to the corresponding help documentation.

Note: To use different connection objects, you must import different namespaces. The namespace of oledbconnection is system. Data. oledb. The namespace of sqlconnection is system. Data. sqlclient. The namespace of odbcconnection is system. Data. ODBC. The namespace of oracleconnection is system. Data. oracleclinet.

Sqlconnection attributes:

Attribute	Description
Connectionstring	The return type is string, which gets or sets the string used to open the SQL Server database.
Connectiontimeout	The return type is int, which gets the waiting time before the connection is terminated and an error is generated.
Database	The return type is string. Obtain the name of the database to be used after the current database or connection is opened.
Datasource	The return type is string. Obtain the name of the SQL server instance to be connected.
State	The return type is connectionstate, and the current connection status is obtained: broken, closed, connecting, fetching, or open.
Serverversion	The return type is string, which gets the string that contains the version of the SQL server instance connected to the client.
Packetsize	Obtains the size (in bytes) of network data packets used to communicate with SQL Server instances ). This attribute applies only to the sqlconnection type.

Sqlconnection method:

Method	Description
Close ()	The return type is void. Close the connection to the database.
Createcommand ()	The return type is sqlcommand. A sqlcommand object associated with sqlconnection is created and returned.
Open ()	The return type is void. Use the property specified by the connection string attribute to open the database connection.

Sqlconnection event:

Event	Description
Statechange	This event occurs when the event status changes. (Inherited from dbconnection .)
Infomessage	Occurs when SQL Server Returns a warning or informative message.

Tip: An event can be used to notify another object in some way to generate something. For example, we select the Start menu in windows. Once you click the mouse, an event occurs, notifying the operating system to display the Start Menu.

　　Use the sqlconnection object to connect to the SQL Server database

We can use the sqlconnection () constructor to generate a new sqlconnection object. This function is overloaded, that is, we can call different versions of the constructor. Sqlconnection () constructor is shown in the following table:

Constructor	Description
Sqlconnection ()	Initializes a new instance of the sqlconnection class.
Sqlconnection (string)	If a string containing the connection string is specified, a new instance of the sqlconnection class is initialized.

Program Code Description: In the program code of the preceding syntax example, we use the "new" keyword to generate a new sqlconnection object and name it mysqlconnection.

Now we can use the following two methods to connect to the database: Integrated Windows Authentication and SQL Server Authentication for database login.

Example of Integrated Windows Authentication syntax

String connectionstring = "Server = localhost; database = northwind;
Integrated Security = sspi ";

Program Code Description: In the program code of the preceding syntax example, we set a connection string for the SQL Server database. Server indicates the name of the computer that runs SQL Server. Because ASP. net programs and database systems are on the same computer in this book, we can replace the current computer name with localhost. Database indicates the name of the database in use. Here it is set to a sample database-northwind that comes with SQL Server. Because we want to adopt the integrated Windows authentication method, set the Integrated Security to sspi.

In SQL Server 2005, the Windows Authentication mode is as follows:

Note: when using the Integrated Windows authentication method, you do not need to enter the user name and password, but pass the user name and password entered during Windows logon to SQL Server. SQL Server then checks the user list and whether the user has the permission to access the database. The database connection string is case-insensitive.

SQL Server Authentication syntax example

String connectionstring = "Server = localhost; database = northwind; uid = sa; Pwd = sa ";

Program Code Description: In the program code of the preceding syntax example, database logon is performed using known user names and passwords. UID is the specified database user name, And PWD is the specified user password. For the sake of security, do not include the user name and password in the code. You can use the Integrated Windows authentication method or the web. the connection string in the config file is encrypted to improve program security.

The Authentication Mode of SQL Server in SQL Server 2005 is as follows:

If you use other data providers, the generated connection strings are similar. For example, if you want to connect to an Oracle database using ole db, the connection string is as follows:

String connectionstring = "Data Source = localhost; initial catalog = sales;
Use Id = sa; Password =; provider = msdaora ";

Program Code Description: In the program code of the preceding syntax example, the database connection is realized through the ole db provider for the Oracle database. Data source indicates the name of the computer that runs the Oracle database, and initial catalog indicates the name of the database used. Provider indicates that the ole db provider used is msdaora.

The Access database connection string is as follows:

String connectionstring = "provider = Microsoft. Jet. oledb.4.0;
@ "Data source = C:/datasource/northwind. mdb ";

Program Code Description: In the program code of the preceding syntax example, the database is connected through the ole db provider for the access database. The ole db provider used here is Microsoft. Jet. oledb.4.0, and the data inventory is stored in the C:/datasource directory. The database file is northwind. MDB.

Now we can transmit the database connection string to the sqlconnection () constructor, for example:

String connectionstring = "Server = localhost; database = northwind; uid = sa; Pwd = sa ";
Sqlconnection mysqlconnection = new sqlconnection (connectionstring );

Or write it

Sqlconnection mysqlconnection = new sqlconnection (
"Server = localhost; database = northwind; uid = sa; Pwd = sa ");

In the previous example, a new sqlconnection object is generated by using the "new" keyword. Therefore, you can set the connectionstring attribute of the object to specify a database connection string. This is the same as passing the database connection string to the sqlconnection () constructor.

Sqlconnection mysqlconnection = new sqlconnection ();
Mysqlconnection. connectionstring = "Server = localhost; database = northwind; uid = sa; Pwd = sa ";

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

 

　　Enable and disable database connection

After generating the connection object and setting the connectionstring attribute to the relevant details of the database connection, you can open the database connection. Therefore, you can call the open () method of the connection object. The method is as follows:

Mysqlconnection. open ();

After the database connection is completed, we can call the close () method of the connection object to close the database connection. For example:

Mysqlconnection. Close ();

The following is an instance program that shows how to use the sqlconnection object to connect to the SQL Server northwind database, and displays some attributes of the sqlconnection object.

The sample code is as follows:

01 public partial class _ default: system. Web. UI. Page
02 {
03 protected void page_load (Object sender, eventargs E)
04 {
05 // create a database connection string
06 string connectionstring = "Server = localhost; database = northwind;
07 Integrated Security = sspi ";
08 // pass the connection string to the constructor of the sqlconnection object
09 sqlconnection mysqlconnection = new sqlconnection (connectionstring );
10 try
11 {
12 // open the connection
13 mysqlconnection. open ();
14 // use the label control to display the connectionstring attribute of the mysqlconnection object
15 lblinfo. Text = "<B> the connectionstring attribute of mysqlconnection object is: <B>" +
16 mysqlconnection. connectionstring + "<br> ";
17 lblinfo. Text + = "<B> the connectiontimeout attribute of mysqlconnection object is <B>" +
18 mysqlconnection. connectiontimeout + "<br> ";
19 lblinfo. Text + = "<B> the database attribute of mysqlconnection object is <B>" +
20 mysqlconnection. Database + "<br> ";
21 lblinfo. Text + = "<B> the datasource attribute of the mysqlconnection object is <B>" +
22 mysqlconnection. datasource + "<br> ";
23 lblinfo. Text + = "<B> the packetsize attribute of the mysqlconnection object is <B>" +
24 mysqlconnection. packetsize + "<br> ";
25 lblinfo. Text + = "<B> the serverversion attribute of mysqlconnection object is <B>" +
26 mysqlconnection. serverversion + "<br> ";
27 lblinfo. Text + = "<B> the current status of the mysqlconnection object is <B>" +
28 mysqlconnection. State + "<br> ";
29}
30 catch (exception ERR)
31 {
32 lblinfo. Text = "An error occurred while reading the Database ";
33 lblinfo. Text + = err. message;
34}
35 finally
36 {
37 // close the connection to the database
38 mysqlconnection. Close ();
39 lblinfo. Text + = "<br> <B> the status of the mysqlconnection object after the connection is closed is: </B> ";
40 lblinfo. Text + = mysqlconnection. state. tostring ();
41}
42}
43}

Program Code Description: In the program code of the preceding example, we use try catch finally to handle exceptions in database connections. When the database cannot be connected, an exception is thrown and an error message is displayed, as shown in the catch code block. In this program, you can close the database connection through the Finally block regardless of whether an exception occurs, which saves computer resources and improves program efficiency and scalability.

Execution result:

Of course, we can also use a simpler method to implement the functions of the above program. This is to include the sqlconnection object in the using block, so that the program will automatically call the dispose () method to release the system resources occupied by the sqlconnection object, without the need to use the close () method of the sqlconnection object.

The sample code is as follows:

01 public partial class _ default: system. Web. UI. Page
02 {
03 protected void page_load (Object sender, eventargs E)
04 {
05 string connectionstring = "Server = localhost; database = northwind;
06 Integrated Security = sspi ";
07 sqlconnection mysqlconnection = new sqlconnection (connectionstring );
08 using (mysqlconnection)
09 {
10 mysqlconnection. open ();
11 lblinfo. Text = "<B> the connectionstring attribute of mysqlconnection object is: <B>" +
12 mysqlconnection. connectionstring + "<br> ";
13 lblinfo. Text + = "<B> the connectiontimeout attribute of mysqlconnection object is <B>" +
14 mysqlconnection. connectiontimeout + "<br> ";
15 lblinfo. Text + = "<B> the database attribute of mysqlconnection object is <B>" +
16 mysqlconnection. Database + "<br> ";
17 lblinfo. Text + = "<B> the datasource attribute of the mysqlconnection object is <B>" +
18 mysqlconnection. datasource + "<br> ";
19 lblinfo. Text + = "<B> the packetsize attribute of the mysqlconnection object is <B>" +
20 mysqlconnection. packetsize + "<br> ";
21 lblinfo. Text + = "<B> the serverversion attribute of mysqlconnection object is <B>" +
22 mysqlconnection. serverversion + "<br> ";
23 lblinfo. Text + = "<B> the current status of the mysqlconnection object is <B>" +
24 mysqlconnection. State + "<br> ";
25}
26 lblinfo. Text + = "<br> <B> after the connection is closed, the status of the mysqlconnection object is: </B> ";
27 lblinfo. Text + = mysqlconnection. state. tostring ();
28}
29}

Program Code Description: In the code of the above example, the code is simpler in the form of using (mysqlconnection), and the biggest advantage is that you do not need to write Finally block code, you can automatically close the connection to the database.

 

　　Connection Pool

It is time consuming to open or close a database. Therefore, ADO. Net automatically stores database connections in the connection pool. The connection pool can greatly improve the performance and efficiency of the program, because we do not have to wait for a new database connection process, but directly use the existing database connection. Note: When you close a connection by using the close () method, the connection is not actually closed. Instead, the connection is marked as unused and placed in the connection pool for next reuse.

If the same details are provided in the connection string, such as the same database, user name, and password, you can directly obtain and return the connection in the pool. Then you can use this connection to access the database.

When using the sqlconnection object, you can specify the Max pool size in the connection string to indicate the maximum number of connections allowed by the connection pool (the default value is 100 ), you can also specify the min pool size to indicate the minimum number of connections allowed by the connection pool (default value: 0 ). The following code specifies that the Max pool size of the sqlconnection object is 10, and the min pool size is 5.

Sqlconnection mysqlconnection = new sqlconnection ("Server = localhost; database = northwind;
Integrated Security = sspi; "+" Max pool size = 10; min pool size = 5 ");

Program Code Description: In the program code of the preceding example, the program initially generates five sqlconnection objects in the pool. The pool can store up to 10 sqlconnection objects. If you want to open a new sqlconnection object and all the objects in the pool are in use, you need to wait until a sqlconnection object is closed before using the new sqlconnection object. If the request wait time exceeds the number of seconds specified by the connectiontimeout attribute, an exception is thrown.

The following shows the performance advantages of the connection pool through a program. In the process of applying this program, we must first reference the system. Data. sqlclinet and system. Text namespaces.

The sample code is as follows:

01 public partial class _ default: system. Web. UI. Page
02 {
03 protected void page_load (Object sender, eventargs E)
04 {
05 // set the maximum number of connections in the connection pool to 5 and the minimum to 1
06 sqlconnection mysqlconnection = new sqlconnection (
07 "Server = localhost; database = northwind; Integrated Security = sspi;" +
08 "Max pool size = 5; min pool size = 1 ");
09 // create a stringbuilder object
10 stringbuilder htmstr = new stringbuilder ("");
11 For (int count = 1; count <= 5; count ++)
12 {
13 // append the string to the end of the stringbuilder object using the append () method
14 htmstr. append ("connection object" + count );
15 htmstr. append ("<br> ");
16 // set the start time of a connection
17 datetime start = datetime. now;
18 mysqlconnection. open ();
19 // connection time
20 timespan timetaken = datetime. Now-start;
21 htmstr. append ("connection time:" + timetaken. milliseconds + "millisecond ");
22 htmstr. append ("<br> ");
23 htmstr. append ("the status of the mysqlconnection object is" + mysqlconnection. State );
24 htmstr. append ("<br> ");
25 mysqlconnection. Close ();
26}
27 // display the string contained in the stringbuilder object in the label control.
28 lblinfo. Text = htmstr. tostring ();
29}
30}

Program Code Description: In the program code of the preceding example, we will open a sqlconnection object five times in the connection pool. datetime. Now indicates the current time. Timetaken indicates the time interval from the start of the connection to the start of the connection. It can be seen that opening the first connection takes longer than opening the subsequent connection because the first connection actually connects to the database. After the connection is closed, the connection is stored in the connection pool. When you enable the connection again, you only need to read it directly from the pool, which is very fast.

Tip: The String object cannot be changed. Every time you use a method in the system. string class, you must create a new String object in the memory, which requires a new space for the new object. If you need to modify the string repeatedly, the system overhead associated with creating a new String object may be very expensive. If you want to modify the string without creating a new object, you can use the system. Text. stringbuilder class. For example, when many strings are connected together in a loop, using the stringbuilder class can improve performance. The append method can be used to add the string representation of a text or object to the end of a string represented by the current stringbuilder object.

Execution result:

　　Set the connection string in ASP. NET 2.0

In ASP. NET 2.0, a new declarative expression syntax is used to parse a connection string value at runtime, referencing the database connection string by name. The connection string is stored under the <connectionstrings> Configuration section in the web. config file to facilitate maintenance of all pages in the application at a single location.

The sample code is as follows:

<? XML version = "1.0"?>
<Configuration>
<Connectionstrings>
<Add name = "pubs" connectionstring = "Server = localhost;
Integrated Security = true; database = pubs; persist Security info = true"
Providername = "system. Data. sqlclient"/>
<Add name = "northwind" connectionstring = "Server = localhost;
Integrated Security = true; database = northwind; persist Security info = true"
Providername = "system. Data. sqlclient"/>
</Connectionstrings>
<System. Web>
<Pages stylesheettheme = "default"/>
</System. Web>
</Configuration>

Program Code Description: In the program code of the preceding example. under the <connectionstrings> Configuration node in the config file, two database connection strings are set, pointing to the pubs and northwind sample databases respectively. Note that a data source control is introduced in 2.0, such as the sqldatasource control. We can set the connectionstring attribute of the sqldatasource control to an expression <% $ connectionstrings: pubs %>, this expression is run by ASP.. Net analyzer is parsed as a connection string. You can also specify an expression for the providername attribute of sqldatasource, for example, <% $ connectionstrings: pubs. providername %>. The specific usage and new features will be detailed in the subsequent sections. Now you have a basic understanding.

Of course, we can also use the following method to directly read the database connection string from the configuration file. First, we need to reference the using system. Web. Configuration namespace, which contains classes used to set ASP. NET configurations.

String connectionstring = configurationmanager. connectionstrings ["northwind"]. connectionstring;

Program Code Description: In the program code of the preceding example, we can use connectionstrings ["northwind"] to read the corresponding northwind string. Similarly, you can use connectionstrings ["pubs"] to read the corresponding pubs string.