第二课 SqlConnection对象(翻译)
第二课 SqlConnection对象(翻译) 本文档由李欣蔚(nirvana_li)翻译自http://www.csharp-station.com/,转载请注名出处!
更新日期2006-2-14
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的生命周期中发生的操作顺序如下：
Instantiate the SqlConnection. Open the connection. Pass the connection to other ADO.NET objects. Perform data base operations with the other ADO.NET objects. 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.
