asp.net2.0 SQL Server database connection _ Practical Tips

There are four types of

Connection: Sqlconnection,oledbconnection,odbcconnection and OracleConnection.

The object of the SqlConnection class connects to the SQL Server database, the object of the OracleConnection class connects to the Oracle database, and the

OleDbConnection object connection to the class supports OLE DB databases, such as access, while objects of the OdbcConnection class connect to any ODBC-enabled database. All communication with the database is ultimately done through the connection object. The

SqlConnection class

Connection is used to "dialog" with the database and is represented by classes of a particular provider, such as SqlConnection. Although the SqlConnection class is for SQL Server, many of the properties, methods, and events of this class are similar to those of OleDbConnection and OdbcConnection. This chapter will focus on SqlConnection specific properties and methods, and other connection classes you can refer to the appropriate Help documentation.

Note: You need to import different namespaces using different connection objects. The OleDbConnection namespace is System.Data.OleDb. The SqlConnection namespace is System.Data.SqlClient. The OdbcConnection namespace is System.Data.Odbc. The OracleConnection namespace is System.Data.OracleClinet.

SqlConnection property:

Property	Description
ConnectionString	The return type is string, gets or sets the string used to open the SQL Server database.
ConnectionTimeout	The return type is int to get the time to wait before terminating the attempt and generating an error when attempting to establish a connection.
Database	The return type is string, obtaining the name of the database to use when the current database or connection is opened.
DataSource	The return type is string, obtaining the name of the SQL Server instance to which you want to connect.
State	The return type is connectionstate, obtaining the current connection status: Broken, Closed, connecting, fetching, or open.
ServerVersion	The return type is string, and gets a string containing the version of the SQL Server instance of the client connection.
PacketSize	Gets the size, in bytes, of the network packet used to communicate with an instance of SQL Server . This property applies only to SqlConnection types

SqlConnection method:

Method	Description
Close ()	The return type is void, and the connection to the database is turned off.
CreateCommand ()	The return type is SqlCommand, and a SqlCommand object associated with SqlConnection is created and returned.
Open ()	The return type is void, opening the database connection with the property specified by the connection string property

SqlConnection event:

Event	Description
StateChange	Occurs when the event state changes. (inherited from DbConnection.) ）
InfoMessage	Occurs when SQL Server returns a warning or informational message.

Tip: You can use events to let an object notify another object in some way of something. For example, we select the Start menu in the Windows system, and once you click the mouse, an event occurs that informs the operating system that the Start menu is displayed.

　　To connect to a SQL Server database using the SqlConnection object

We can generate a new SqlConnection object with the SqlConnection () constructor. This function is overloaded, that is, we can call different versions of the constructor. The constructor for SqlConnection () is shown in the following table:

Constructors	Description
SqlConnection ()	Initializes a new instance of the SqlConnection class.
SqlConnection (String)	Initializes a new instance of the SqlConnection class, given a string containing the connection string.

Assuming that we imported the System.Data.SqlClient namespace, you can generate a new SqlConnection object with the following statement:

SqlConnection mysqlconnection = new SqlConnection ();

Program code Description: In the above syntax example of the program code, we use the "new" keyword to generate a new SqlConnection object, and named it mysqlconnection.

Now we can connect to the database in two ways, using Integrated Windows authentication and using SQL Server Authentication to log on to the database.

Integrated Windows authentication Syntax paradigm

String connectionstring= "Server=localhost;database=northwind; Integrated SECURITY=SSPI ";

Program code Description: In the program code of the syntax example above, we set up a connection string for the SQL Server database. Where server represents the computer name for running SQL Server , because in this book, ASP. NET program and database system are located on the same computer, so we can replace the current computer name with localhost. The database represents the name of the databases used, which is set up as a sample database--northwind with SQL Server. Because we want to adopt Integrated Windows authentication method, we set integrated security as SSPI.

The Windows Authentication mode in SQL Server 2005 is as follows:

Note: When using Integrated Windows authentication, we do not require you to enter a username and password, but instead pass the username and password entered when you log on to Windows to SQL Server. SQL Server then checks the user manifest to see if it has permission to access the database. Also, the database connection string is case-insensitive.

Syntax examples with SQL Server authentication

String connectionString = "Server=localhost;database=northwind;uid=sa;pwd=sa";

Program code Description: In the above syntax examples of the program code, using a known username and password Authentication database login. The UID is the specified database user name, PWD to the specified user password. For security reasons, generally do not include user names and passwords in your code, you can use the previous Integrated Windows authentication method or the Web. Config file to enhance the security of the program.

The SQL Server authentication mode in SQL Server 2005 is as follows:

If you use other data providers, the resulting connection string also has a similar form. For example, we want to connect to an Oracle database in OLE DB with the following connection string:

String connectionString = "Data source=localhost;initial catalog=sales; Use id=sa;password=;p Rovider=msdaora ";

Program code Description: In the program code of the syntax example above, the connection to the database is implemented through an OLE DB provider specifically for the Oracle database. Data source indicates the name of the computer running the Oracle database, and initial catalog represents the database name used. Provider indicates that the OLE DB provider used is MSDAORA.

The connection string for the Access database is in the form of the following:

String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" @ "Data Source=c:\datasource\northwind.mdb";

Program code Description: In the program code of the syntax example above, the connection to the database is implemented through the OLE DB provider specifically for the Access database. This uses an OLE DB provider for microsoft.jet.oledb.4.0, and the database is stored in the C:\DataSource directory, and its database file is Northwind.mdb.

Now we can descendant the database connection string SqlConnection () constructor, for example:

String connectionString = "Server=localhost;database=northwind;uid=sa;pwd=sa"; SqlConnection mysqlconnection = new SqlConnection (connectionString);

or write

SqlConnection mysqlconnection =new SqlConnection ( "Server=localhost;database=northwind;uid=sa;pwd=sa");

In the previous example, a new SqlConnection object was generated by using the "new" keyword. So we can also set the ConnectionString property of the object to specify a database connection string for it. This is the same as the ability to SqlConnection () a database connection string to a constructor.

SqlConnection mysqlconnection = new SqlConnection (); mysqlconnection.connectionstring = "Server=localhost;database=northwind;uid=sa;pwd=sa";

Note: You can only set the ConnectionString property when you close the Connection object.

　　To Open and close a database connection

After you build the connection object and set the ConnectionString property to the appropriate details for the database connection, you can open the database connection. To do this, you can invoke the open () method of the Connection object. The method is as follows:

Mysqlconnection.open ();

After completing the connection to the database, we can call the close () method of the Connection object to turn off the database connection. For example:

Mysqlconnection.close ();

The following is an instance program that shows how to connect a SQL Server Northwind database with a SqlConnection object, and displays some of the properties of the SqlConnection object.

The sample program code is as follows:

Partial class _default:system.web.ui.page 02 { protected void Page_Load (object sender, EventArgs e) 04 { 05//Establish database connection string A string connectionString = "Server=localhost;database=northwind; Modified integrated SECURITY=SSPI "; 08//Pass the connection string into the constructor of the SqlConnection object SqlConnection mysqlconnection = new SqlConnection (connectionString); Ten try 11 { 12//Open connection Mysqlconnection.open (); 14///using the Label control to display the ConnectionString property of the Mysqlconnection object Lblinfo.text = "<b> Mysqlconnection object ConnectionString property is: <b>" + Mysqlconnection.connectionstring + "<br>"; Lblinfo.text = "<b> Mysqlconnection object's ConnectionTimeout property is <b>" + Mysqlconnection.connectiontimeout + "<br>"; Lblinfo.text = "<b> Mysqlconnection object's Database property is <b>" + Mysqlconnection.database + "<br>"; Lblinfo.text = "<b> Mysqlconnection object's DataSource property is <b>" + Mysqlconnection.datasource + "<br>"; Lblinfo.text + = "<b> Mysqlconnection object's PacketSize property is <b>" + Mysqlconnection.packetsize + "<br>"; Lblinfo.text = "<b> Mysqlconnection object's ServerVersion property is <b>" + Mysqlconnection.serverversion + "<br>"; Lblinfo.text = "<b> Mysqlconnection object's current state is <b>" + Mysqlconnection.state + "<br>"; 29} catch (Exception err) 31 { Lblinfo.text = "Error reading database"; Lblinfo.text = Err. message; 34} Finally 36 { 37//Close connection to the database Mysqlconnection.close (); Lblinfo.text + = "<br> <b> Closes the state of the connected Mysqlconnection object as: </b>"; Lblinfo.text + + mySqlConnection.State.ToString (); 41} 42} 43}

Program code Description: In the above example of the program code, we use try Catch finally to the database connection for exception handling. An exception is thrown when the database cannot be connected and an error message is displayed, as shown in the catch code block. In this program, whether or not an exception occurs, you can close the database connection through finally block, thus saving the computer resources and improving the efficiency and scalability of the program.

Execution results:

Of course, we can also use a more convenient way to achieve the functions of the above program. This is to include the SqlConnection object in a using block, so that the program automatically calls the Dispose () method to free the system resources occupied by the SqlConnection object without using the close () method of the SqlConnection object.

The sample program code is as follows:

Partial class _default:system.web.ui.page 02 { protected void Page_Load (object sender, EventArgs e) 04 { A string connectionString = "Server=localhost;database=northwind; Integrated SECURITY=SSPI "; Modified SqlConnection mysqlconnection = new SqlConnection (connectionString); using (mysqlconnection) 09 { Ten Mysqlconnection.open (); One lblinfo.text = "<b> Mysqlconnection object ConnectionString property is: <b>" + Mysqlconnection.connectionstring + "<br>"; Lblinfo.text = "<b> Mysqlconnection object's ConnectionTimeout property is <b>" + Mysqlconnection.connectiontimeout + "<br>"; Lblinfo.text = "<b> Mysqlconnection object's Database property is <b>" + Mysqlconnection.database + "<br>"; Lblinfo.text = "<b> Mysqlconnection object's DataSource property is <b>" + Mysqlconnection.datasource + "<br>"; Lblinfo.text = "<b> Mysqlconnection object's PacketSize property is <b>" + Mysqlconnection.packetsize + "<br>"; Lblinfo.text = "<b> Mysqlconnection object's ServerVersion property is <b>" + Mysqlconnection.serverversion + "<br>"; Lblinfo.text + = "<b> Mysqlconnection object's current state is <b>" + Mysqlconnection.state + "<br>"; 25} Lblinfo.text + = "<br> <b> Closes the status of the Mysqlconnection object after the connection is: </b>"; Lblinfo.text + + mySqlConnection.State.ToString (); 28} 29}

Program code Description: In the above example of the program code, using the form of using (mysqlconnection) to make the code more concise, and its biggest advantage is that no need to write finally block code, you can automatically close the connection to the database.

　　Connection Pool

It is time-consuming to open and close the database. For this, ADO. NET automatically stores the database connection in the connection pool. Connection pooling can greatly improve the performance and efficiency of the program, because we do not have to wait for the creation of a new database connection process, but directly with the existing database connection. Note that when closing a connection with the close () method, the connection is not physically closed, but the connection is marked as unused and placed in a connection pool for the next reuse.

If you provide the same details in the connection string, that is, the same database, user name, password, and so on, you can directly obtain and return the connection in the pool. You can then access the database using this connection.

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 is 100), or you can specify that the min pool size indicates the minimum number of connections allowed by the connection pool (default is 0). The following code specifies that the max pool size for the SqlConnection object is 10,min pool size 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 above example program code, the program initially generates 5 SqlConnection objects in the pool. Up to 10 SqlConnection objects can be stored in a pool. If you want to open a new SqlConnection object, all of the objects in the pool are in use, the request waits for a SqlConnection object to close before you can use the new SqlConnection object. If the request wait time exceeds the number of seconds specified by the ConnectionTimeout property, an exception is thrown.

The following is a program to show the performance advantages of the connection pool. In applying this program we first refer to the System.Data.SqlClinet and System.Text namespaces.

The sample program code is as follows:

Partial class _default:system.web.ui.page 02 { protected void Page_Load (object sender, EventArgs e) 04 { 05//Set the maximum connection number of the connection pool is 5, the minimum is 1 SqlConnection mysqlconnection =new SqlConnection ( "Server=localhost;database=northwind;integrated Security=sspi;" + "Max Pool size=5;min pool size=1"); 09//Create a new StringBuilder object StringBuilder htmstr = new StringBuilder (""); for (int count = 1; count <= 5; count++) 12 { 13//Use the Append () method to append the string to the end of the StringBuilder object Htmstr.append ("Connection object" +count); Htmstr.append ("<br>"); 16//Set the start time of a connection DateTime start = DateTime.Now; Mysqlconnection.open (); 19//The time spent in the connection TimeSpan Timetaken = Datetime.now-start; Htmstr.append ("Connection time is" +timetaken.milliseconds+ "millisecond"); Htmstr.append ("<br>"); Htmstr.append ("Mysqlconnection object's state is" + mysqlconnection.state); Htmstr.append ("<br>"); Mysqlconnection.close (); 26} 27//Displays the contained string of the StringBuilder object in the Label control Lblinfo.text = Htmstr.tostring (); 29} 30}

Program code Description: In the above example program code, we will repeat 5 times in the connection pool open a SqlConnection object, DateTime.Now represents the current time. Timetaken represents the interval between the start of a connection and the time it takes to open the connection. As you can see, opening the first connection takes longer than opening subsequent connections, because the first connection actually connects to the database. After being closed, this connection is stored in the connection pool. When you open the connection again, you can read it directly from the pool, very quickly.

Tip: The String object is immutable. Each time you use one of the methods in the System.String class, you create a new string object in memory, which requires a new space to be allocated for the new object. The system overhead associated with creating a new string object can be expensive when you need to perform repeated modifications to the string. If you want to modify a string without creating a new object, you can use the System.Text.StringBuilder class. For example, when you concatenate many strings together in a loop, using the StringBuilder class can improve performance. The Append method can be used to add the string representation of text or an object to the end of the string represented by the current StringBuilder object.

Execution results:

　　setting of the connection string in asp.net 2.0

In ASP.net 2.0, a new declarative expression syntax that resolves to a connection string value at run time is used, referencing the database connection string by name. The connection string itself is stored under the configuration section in the Web.config file so that it is easy to maintain for all pages in the application in a single location.

The sample program code is as follows:

> Providername= "System.Data.SqlClient"/> Providername= "System.Data.SqlClient"/>

Program code Description: In the program code of the example above, we set up two database connection strings under the configuration node in the Web.config file, pointing to the pubs and Northwind two sample databases respectively. Note that the data source control, such as the SqlDataSource control, is introduced in 2.0, and we can set the ConnectionString property of the SqlDataSource control to the expression <%$ connectionstrings:pubs%>, The expression is parsed by the ASP.net parser into a connection string at run time. You can also specify an expression for the SqlDataSource ProviderName property, such as <%$ ConnectionStrings:Pubs.ProviderName%>. Its specific usage and new features will be described in detail in later chapters. Now there is a basic understanding can be.

Of course, we can also read the database connection string directly from the configuration file in the following way. First we need to reference the using System.Web.Configuration namespace, which contains the classes that are used to set up the ASP.net configuration.

String connectionString =configurationmanager.connectionstrings["Northwind". ConnectionString;

Program code Description: In the above example's program code, we can use connectionstrings["Northwind" to read the corresponding Northwind string. Similarly, you can use connectionstrings["pubs" to read the corresponding pubs string.