Lesson 02: The SqlConnection Object(SqlConnection对象)

 

Lesson 02: The SqlConnection Object

This lesson describes the SqlConnection object and how to connect to a data base.  Here are the objectives of this lesson:

这节课描述了 SqlConnection 对象，和如何连接数据库。以下是本课目标

• Know what connection objects are used for.
• Learn how to instantiate a SqlConnection object.
• Understand how the SqlConnection object is used in applications.
• Comprehend the importance of effective connection lifetime management.
• 知道连接对象用来干什么
• 学习如何初始化SqlConnection对象
• 了解SqlConnection对象如何在应用程序中使用
• 理解有效连接生命周期管理的重要性

Introduction

引入

The first thing you will need to do when interacting with a data base is to create a connection.  The connection tells the rest of the ADO.NET code which data base it is talking to.  It manages all of the low level logic associated with the specific data base protocols.  This makes it easy for you because the most work you will have to do in code is instantiate the connection object, open the connection, and then close the connection when you are done.  Because of the way that other classes in ADO.NET are built, sometimes you don't even have to do that much work.

     当与数据库交互时首先应该创建连接。此连接告诉其余的 ADO.NET 代码：它将与哪个数据库打交道。它管理所有与特定数据库协议有关联的低级逻辑。这种方式使与数据库连接十分简单，你需要写的代码只是实体化 connection 对象，打开 connection ，在完事之后关闭 connection 。因为 ADO.NET 以这种方式构造其它的类，某些时候你甚至不需要做太多的工作。

Although working with connections is very easy in ADO.NET, you need to understand connections in order to make the right decisions when coding your data access routines.  Understand that a connection is a valuable resource.  Sure, if you have a stand-alone client application that works on a single data base one one machine, you probably don't care about this.  However, think about an enterprise application where hundreds of users throughout a company are accessing the same data base.  Each connection represents overhead and there can only be a finite amount of them.  To look at a more extreme case, consider a Web site that is being hit with hundreds of thousands of hits a day.  Applications that grab connections and don't let them go can have seriously negative impacts on performance and scalability.

     尽管在 ADO.NET 中使用连接非常简单，你需要理解连接以便在数据存取规则中采用正确的策略。理解连接是非常有价值的。当然，如果在一台机器上有一个单独的客户端应用程序使用单独的数据库，你可能不用关心这个。然而，考虑一个企业级应用程序，全公司的大量用户都存取同样的数据库。每一次连接都代表损耗并且连接的数量都是有限的。再来看一个更加极端的情况，考虑 web 站点每天要承受成千上万的访问。应用程序保持连接而不让它们断开会对性能和可测量性带来严重的负面影响。

Creating a SqlConnection Object

创建 SqlConnection 对象

A SqlConnection is an object, just like any other C# object.  Most of the time, you just declare and instantiate the SqlConnection all at the same time, as shown below:

SqlConnection 是对象，就像 C# 中其它的对象一样。很多时候，你只需要声明并实例化 SqlConnection ，如下所示：

SqlConnection conn = new SqlConnection(
    "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");

The SqlConnection object instantiated above uses a constructor with a single argument of type string.  This argument is called a connection string.  Table 1 describes common parts of a connection string.

上面实例化 SqlConnection 对象使用了带一个 string 类型参数的构造函数。这个参数叫做连接字符串（ connection string ）。表 1 描述了连接字符串的通常部分。

Table 1.  ADO.NET Connection Strings contain certain key/value pairs for specifying how to make a data base connection.  They include the location, name of the database, and security credentials.

表 1.ADO.NET 连接字符串包括某些键 / 值对来指示如何连接数据库。它们包括位置、数据库的名字、和安全认证。

Connection String Parameter Name	Description
Data Source	Identifies the server.  Could be local machine, machine domain name, or IP Address.
Initial Catalog	Data base name.
Integrated Security	Set to SSPI to make connection with user's Windows login
User ID	Name of user configured in SQL Server.
Password	Password matching SQL Server User ID.

 

连接字符串参数名	描述
Data Source	指明服务器。可以是本地机器，机器域名或者 IP 地址
Initial Catalog	数据库名字
Integrated Security	设置为 SSPI ，使连接使用用户的 Windows 登录
User ID	配置在 SQL Server 中的用户名
Password	与 SQL Server 的用户名匹配的密码

 

Integrated Security is secure when you are on a single machine doing development.  However, you will often want to specify security based on a SQL Server User ID with permissions set specifically for the application you are using.  The following shows a connection string, using the User ID and Password parameters:

当你在一个独立的机器上面做开发的时候，集成安全是安全的。然而，你通常希望指明所使用的应用程序的基于 SQL Server 用户 ID 的安全许可。下面显示的连接字符串使用了 User ID 和 Password 参数：

SqlConnection conn = new SqlConnection(
"Data Source=DatabaseServer;Initial Catalog=Northwind;User ID=YourUserID;Password=YourPassword");

Notice how the Data Source is set to DatabaseServer to indicate that you can identify a data base located on a different machine, over a LAN, or over the Internet.  Additionally, User ID and Password replace the Integrated Security parameter.

注意 Data Source 被设置为 DatabaseServer 来指示你能够指明位于不同机器――跨局域网或者 Internet ――的数据库。另外， User ID 和 Password 替换调了集成安全参数。

Using a SqlConnection

使用 SqlConnection

The purpose of creating a SqlConnection object is so you can enable other ADO.NET code to work with a data base.  Other ADO.NET objects, such as a SqlCommand and a SqlDataAdapter take a connection object as a parameter.  The sequence of operations occurring in the lifetime of a SqlConnection are as follows:

创建 SqlConnection 对象的需要是使你能够让其它的 ADO.NET 代码使用数据库。其它的 ADO.NET 对象，比如 SqlCommand 和 SqlDataAdapter 使用 connection 对象作为参数。在 SqlConnection 的生命周期中发生的操作顺序如下：

1. Instantiate the SqlConnection.
2. Open the connection.
3. Pass the connection to other ADO.NET objects.
4. Perform data base operations with the other ADO.NET objects.
5. Close the connection.

1．            实例化 SqlConnection

2．            打开连接

3．            传递连接给其它的 ADO.NET 对象

4．            使用其它的 ADO.NET 对象执行数据库操作

5．            关闭连接

We've already seen how to instantiate a SqlConnection.  The rest of the steps, opening, passing, using, and closing are shown in Listing 1.

我们已经看到如何实例化 SqlConnetion 。其它的步骤：打开、传递、使用和关闭参见 Listing1 ：

Listing 1.  Using a SqlConnection

using System;
using System.Data;
using System.Data.SqlClient;

///<summary>
/// Demonstrates how to work with SqlConnection objects
///</summary>
class SqlConnectionDemo
{
    static void Main()
    {
        // 1. Instantiate the connection 实例化连接
        SqlConnection conn = new SqlConnection(
            "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");

        SqlDataReader rdr = null;

        try
        {
            // 2. Open the connection 打开连接
            conn.Open();

            // 3. Pass the connection to a command object 传递连接给 command 对象
            SqlCommand cmd = new SqlCommand("select * from Customers", conn);

            //
            // 4. Use the connection 使用连接
            //

            // get query results 得到查询结果
            rdr = cmd.ExecuteReader();

            // print the CustomerID of each record 打印每条记录的 CustomerID
            while (rdr.Read())
            {
                Console.WriteLine(rdr[0]);
            }
        }
        finally
        {
            // close the reader 关闭 reader
            if (rdr != null)
            {
                rdr.Close();
            }

            // 5. Close the connection 关闭连接
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
}

As shown in Listing 1, you open a connection by calling the Open() method of the SqlConnection instance, conn.  Any operations on a connection that was not yet opened will generate an exception.  So, you must open the connection before using it.

如 Listing1 所示，打开连接需要调用 SqlConnection 实例 conn 的 Open() 方法。如果没有打开连接，任何对连接的操作都会产生异常。所以，你必须在使用连接之前打开它。

Before using a connection, you must let the ADO.NET code know which connection it needs.  In Listing 1, we set the second parameter to the SqlCommand object with the SqlConnection object, conn.    Any operations performed with the SqlCommand will use that connection.

在使用连接之前，你必须让 ADO.NET 代码知道它需要的连接。在 Listing1 中，我们将 SqlCommand 对象的第二个参数设置为 SqlConnection 对象 conn 。任何对 SqlCommand 对象的操作将使用此连接。

The code that uses the connection is a SqlCommand object, which performs a query on the Customers table.  The result set is returned as a SqlDataReader and the while loop reads the first column from each row of the result set, which is the CustomerID column.  We'll discuss the SqlCommand and SqlDataReader objects in later lessons.  For right now, it is important for you to understand that these objects are using the SqlConnection object so they know what database to interact with.

使用连接的代码是 SqlCommand 对象，它执行对 Customers 表的查询。结果集被作为 SqlDataReader 返回，并且 While 循环从结果集的每一行中读取第一列，即 CustomerID 列。我们将在以后的课程中讨论 SqlCommand 和 SqlDataReader 对象。现在，你需要着重了解的是这些对象使用 SqlConnection 对象，所以它们知道要操作哪个数据库。

When you are done using the connection object, you must close it.  Failure to do so could have serious consequences in the performance and scalability of your application.  There are a couple points to be made about how we closed the connection in Listing 1:  the Close() method is called in a finally block and we ensure that the connection is not null before closing it.

当你使用完连接对象以后必须关闭它。如果关闭失败则会对应用程序的性能和可测量性造成严重的后果。在 Listing1 中如何关闭连接有两点：在 finally 语句块中调用 Close() 方法，并且在关闭连接之前保证它不为 null 。

Notice that we wrapped the ADO.NET code in a try/finally block.  As described in Lesson 15:  Introduction to Exception Handling of the C# Tutorial, finally blocks help guarantee that a certain piece of code will be executed, regardless of whether or not an exception is generated.  Since connections are scarce system resources, you will want to make sure they are closed in finally blocks.

注意我们将 ADO.NET 代码外覆一层 try/finally 语句块。在第 15 课：异常处理中， finally 语句块有助于保证某些代码无论是否产生异常的情况下都被执行。因为连接是稀有的系统资源，你需要保证它们在 finally 语句块中被关闭掉。

Another precaution you should take when closing connections is to make sure the connection object is not null.  If something goes wrong when instantiating the connection, it will be null and you want to make sure you don't try to close an invalid connection, which would generate an exception.

另外关闭连接的时候你应该预防的是确保连接对象非空。当实例化连接时出现错误，它将是 null ，而你确信不会试图关闭一个无效的连接，它应该会产生异常。

This example showed how to use a SqlConnection object with a SqlDataReader, which required explicitly closing the connection.  However, when using a disconnected data model, you don't have to open and close the connection yourself.  We'll see how this works in a future lesson when we look at the SqlDataAdapter object.

这个例子演示了如何通过 SqlDataReader 使用 SqlConnection 对象，它需要显式的关闭连接。然而，当使用断开连接模型的时候，并不需要你自己打开并关闭连接。当以后讲到 SqlDataAdapter 对象的时候我们将会看到它如何使用。

Summary

总结

SqlConnection objects let other ADO.NET code know what data base to connect to and how to make the connection.  They are instantiated by passing a connection string with a set of key/value pairs that define the connection.  The steps you use to manage the lifetime of a connection are create, open, pass, use, and close.  Be sure to close your connection properly when you are done with it to ensure you don't have a connection resource leak.

SqlConnection 对象让其它的 ADO.NET 代码知道连接哪一个数据库并且如何连接。它们通过传递一个使用键 / 值对定义的连接字符串来实例化。使用来管理连接的声明周期是创建、打开、传递、使用和关闭。当你完成了工作后确信适时的关闭连接，避免遇到连接资源泄漏的问题。

I hope you enjoyed this lesson and invite you to view the next one in this series, Lesson 03:  The SqlCommand Object.

For further questions, you are welcome to participate in the C# Station Discussion Forums.

Your feedback is very important and I appreciate any constructive contributions you have.  Please feel free to contact me for feedback or comments you may have about this lesson.