ADO.NET 之Connecting to SQL Server

ADO.NET 之Connecting to SQL Server

The main challenge is to understand how the code that accesses the database works. The .NET technology that permits accessing a database from C# code is called ADO.NET. ADO.NET groups all .NET classes that are related to database access. ADO.NET is a complex subject that requires a separate book by itself, so we’ll cover just enough to help you understand how your business tier works. For more information about ADO.NET, refer to Beginning C# 2008 Databases: From Novice to Professional (Apress, 2008).
The data access class named GenericDataAccess that you’l l write will  make extensive use of many ADO.NET features. The GenericDataAccess class deals with accessing the database, executing stored procedures, and returning the retrieved data. This class will provide generic data access functionality for the business tier classes, which need to read or manipulate that data.Each database operation always consists of three steps:
1. Open a connection to the SQL Server database.
2. Perform the needed operations with the database and get back the results.
3. Close the connection to the database.

Before you implement the GenericDataAccess class itself, which implements all these 
steps, we’ll have a quick look at each step individually.

■Tip 

Always try to make the second step (executing the commands) as fast as possible.

Keeping a data connection open for too long or having too many database connections open at the same time is expensive for your application’s performance. The golden rule is to open the connection as late as possible, perform the necessary operations, and then close it immediately.The class used to connect to SQL Server is SqlConnection. When creating a new database connection, you always need to specify at least three important pieces of data: 
• The name of the SQL Server instance you’re connecting to
• The authentication information that will permit you to access the server
• The database you want to work with
This connection data is grouped in a connection string, which needs to be passed to it
SqlConnection object. The following code snippet(代码片段) demonstrates how to create and open a database connection. (You don’t need to type anything just yet—here we’re just explaining theory, and we’ll put it into practice in a step-by-step exercise just a little bit later.)
    // Create the connection object
    SqlConnection connection = new SqlConnection();
    // Set the connection string
    connection.ConnectionString = "Server=(local)/SqlExpress; " +
                                  "User ID=balloonshop; Password=ecommerce;" +
                                  "Database=BalloonShop";
    // Open the connection
    connection.Open();

The code is fairly straightforward: you first create a SqlConnection object, then set its ConnectionString property, and finally open the connection. A connection needs to be opened before it is used for any operations.
Understanding the connection string is important—if your program has problems connecting to the database, these problems likely can be solved by fixing the connection string (assuming that SQL Server is properly configured and that you actually have access to it).
The connection string contains the three important elements. The first is the name of the SQL Server instance you’re connecting to. For SQL Server 2008 Express Edition, the default instance name is (local)/SqlExpress. You’ll want to change this if your SQL Server instance has another name. You can use your computer name instead of (local). Of course, if you connect to a remote SQL Server instance, you’ll need to specify the complete network path instead of (local).After specifying the server, you need to supply security information needed to log in to the server. You can log in to SQL Server either by using SQL Server Authentication (in which case you need to supply a SQL Server username and password as shown in the code snippet) or by using Windows Authentication (also named Windows Integrated Security). With Windows 
Integrated Security, you don’t have to supply a username and password, because SQL Server uses the Windows login information of the currently logged-in user.To log in using Windows Authentication, you’ll need to supply Integrated Security=True (or Integrated Security=SSPI) instead of User ID=username; Password=password. The final part of the connection string specifies the database you’ll be working with.Instead of setting the connection string after creating the SqlConnection object, you can 
provide the connection string right when creating the SqlConnection object:
    // Create the connection object and set the connection string
    SqlConnection connection = new SqlConnection("... connection string ...");
    // Open the connection
    connection.Open();
A final note about the connection string is that several synonyms can be used inside it; for example, instead of Server, you can use Data Source or Data Server, and instead of Database, you can use Initial Catalog（目录,目录册，目录簿）. The list is much longer, and the complete version can be found 
in SQL Server Books Online.

Not original,copied from, :Beginning ASP.NET E-Commerce in C# From Novice to Professional