Xem mẫu

126 Microsoft ADO .NET 4 Step by Step The .NET Framework also includes string builders for OLE DB (System.Data.OleDb.OleDb ConnectionStringBuilder) and ODBC (System.Data.Odbc.OdbcConnectionStringBuilder) con-nections. As with connection strings, the builders include a large number of platform-specific properties used to set the supported keys and values. See the Visual Studio documentation of each string builder class for specific property lists. Storing Connection Strings Because they are standard text strings, how or where you store the connection strings used in your applications is up to you. The Data Source Connection Wizard, demonstrated in Chapter 1, offers to store its generated connection string in your application’s settings file. As mentioned in that chapter, storing the string in the “user” settings file makes it possible to modify this string within the application, perhaps based on user-updated values. Storing the string in the “application” settings file provides consistent access to the connection string, but it can’t be modified by the application itself. Wherever you store the string, be sure to weigh the risks of storing a plain-text key into the database system’s locking mechanism. If your connection string includes the Password ele-ment, you might want to encrypt the entire string before storing it in a disk file or registry entry. UnderstandingDataProviders ADO.NET provides a generic interface to many different types of data stores, including SQL Server, Microsoft Access file-based databases, comma-delimited text files, and Excel spread-sheets, among others. To link these varied data sources with the common DataSet model, ADO.NET includes providers, class libraries that understand how to interact with a specific dataplatformsuchasSQLServer,oracommondatalayersuchasOLEDB.Othervendors offer additional providers beyond those included with Visual Studio that enable access to more third-party database systems and file formats. The ADO.NET Framework comes with three providers: ■■ The Microsoft SQL Server provider, expressed through the System.Data.SqlClient namespace. ■■ The OLE DB provider, expressed through the System.Data.OleDb namespace. ■■ The ODBC provider, expressed through the System.Data.Odbc namespace. Although all providers are conceptually identical, classes that expose similar functionality be-tween the providers sometimes have different names. For instance, the SQL Server provider Chapter 8 Establishing External Connections 127 class that establishes a connection to a database is called SqlConnection. The equivalent class in the OLE DB provider is called OleDbConnection. (They both derive from the System.Data. Common.DbConnection class.) Each provider also includes many classes that are specific to its provider experience. The SqlClient namespace includes SqlBulkCopy, a class that provides ac-cess to SQL Server’s bulk copy features, and that has no counterpart in either the OLE DB or ODBC providers. This book focuses on the most commonly used classes found in the System. Data.SqlClient namespace. Note Prior to version 4 of ADO.NET, Microsoft also included a functional Oracle provider with the .NET Framework. This provider, stored in the System.Data.OracleClient namespace, still ships with Visual Studio. However, its classes have been marked as deprecated and obsolete. Microsoft will likely remove the provider completely in a future release and recommends that Oracle users obtain a third-party provider. Providersexisttotransportdatabetweenproprietarydataplatformsandthegeneric ADO.NETdata layer. They include platform-specific classes that access data resources through connection strings, establish communications with those data sources, pass query and data modification commands from the application tothedatastore,andreturndata records back to the application in a form understood by a DataSet and its related classes. The connection string builder classes discussed earlier in this chapter exist within the provider-specific namespaces. The key classes within each provider (with their SQL Server provider-specific class names) in-clude Command (SqlCommand), Connection (SqlConnection), DataAdapter (SqlDataAdapter), andDataReader(SqlDataReader).Thechaptersinthissectionofthebookdiscussthese classes plus a few others that form the basis of data management between ADO.NET and external data sources. Note ADO.NET includes an “Entity Client” provider that enables provider-like functionality to the new ADO.NET Entity Framework system. It does not communicate with databases directly, but piggybacks on other ADO.NET providers to enable access to external data. Chapter 15, “Querying Data in the Framework,” discusses this provider. ConnectingtoSQLServerviaaDataProvider Connecting to a SQL Server database with ADO.NET requires three components: an active SQL Server database, an instance of SqlClient.SqlConnection, and a valid connection string. 128 Microsoft ADO .NET 4 Step by Step Creating and Opening Connections To create a new database connection, pass a valid SQL Server connection string to the SqlConnection constructor. After the instance exists, your code must specifically open and close and dispose of the connection. C# SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(); // ----- Fill in the builder properties as needed, then... SqlConnection linkToDB = new SqlConnection(builder.ConnectionString); linkToDB.Open(); // ------ Do various database activities, then... linkToDB.Close(); linkToDB.Dispose(); Visual Basic Dim builder As New SqlConnectionStringBuilder ` ----- Fill in the builder properties as needed, then... Dim linkToDB As New SqlConnection(builder.ConnectionString) linkToDB.Open() ` ------ Do various database activities, then... linkToDB.Close() linkToDB.Dispose() Again, you must close and dispose of the connection when you are finished with it. Letting the connection object go out of scope will not automatically close the database connection; you must close it manually. Note Calling the connection’s Dispose method will automatically call Close (if you haven’t done so already). Calling Close will not automatically call Dispose. To simplify the process, employ a using/Using block to automatically dispose of the connec-tion object. C# using (SqlConnection linkToDB = new SqlConnection(builder.ConnectionString)) { linkToDB.Open(); // ----- Additional code here. } Chapter 8 Establishing External Connections 129 Visual Basic Using linkToDB As New SqlConnection(builder.ConnectionString) linkToDB.Open() ` ----- Additional code here. End Using For effective connection pooling (discussed later in this chapter), it is best to open the con-nection as late as you can, and close it again as soon as you can after that. Opening a Database Connection: C# 1. Open the “Chapter 8 CSharp” project from the installed samples folder. The project in-cludes a single Windows.Forms class: ConnectionTest. 2. Open the source code view for the ConnectionTest form. Locate the BuildConnection function. This routine creates a SqlConnectionStringBuilder instance based on the user-specified connection settings. 3. Just after the “Add the server name” comment, add the following code: if (LocalServer.Checked == true) connection.DataSource = "(local)"; else connection.DataSource = ServerName.Text; if (IsExpressEdition.Checked == true) connection.DataSource += @"\SQLEXPRESS"; This code defines the main SQL Server data source. The code differentiates between the Express Edition (and its default name extension) and standard instances. 4. Just after the “Add the authentication” comment, add the following code: if (AuthenticateWindows.Checked == true) connection.IntegratedSecurity = true; else { connection.IntegratedSecurity = false; connection.UserID = UserName.Text; connection.Password = UserPassword.Text; } This conditional code supports two types of authentication: integrated security based on the current Windows login and SQL Server user-based security. 5. Locate the ActTest_Click event handler. This routine attempts the connection with the configured data source. Just after the “Test the connection” comment, add the follow-ing statements: testLink = new SqlConnection(connection.ConnectionString); testLink.Open(); 130 Microsoft ADO .NET 4 Step by Step 6. Run the program. Use the fields on the form to test your local configuration of SQL Server. For my test setup, I selected the Local Server option, selected the SQL Server Express Installation field, entered StepSample in the Initial Catalog field, and left the other fields at their default settings. Then I clicked Test, which ran successfully. If you installed the sample database described in the book’s Introduction, your settings will be similar, although you should set the Server Name field to your own server’s name for nonlocal databases. Opening a Database Connection: Visual Basic 1. Open the “Chapter 8 VB” project from the installed samples folder. The project includes a single Windows.Forms class: ConnectionTest. 2. Open the source code view for the ConnectionTest form. Locate the BuildConnection function. This routine creates a SqlConnectionStringBuilder instance based on the user-specified connection settings. 3. Just after the “Add the server name” comment, add the following code: If (LocalServer.Checked = True) Then connection.DataSource = "(local)" Else connection.DataSource = ServerName.Text End If If (IsExpressEdition.Checked = True) Then connection.DataSource &= "\SQLEXPRESS" ... - tailieumienphi.vn