Manipulating data in ASP.net 2.0 70: Configuring database connections and command level settings _ self-study Process

Introduction:

In this series we use typed datasets to build the data access layer. As discussed in the first chapter, a typed Datasets DataTables is used as a "warehouse" for storing data, and TableAdapters as a channel to connect to the database to retrieve and modify data. TableAdapters encapsulates many of the complex details of the processing database, freeing us from the pain of writing code to connect to a database, give a name, and populate a DataTable with data.

But at some point we need to delve into the TableAdapter and write the code directly to handle the Ado.net object. In the 61st chapter "in the transaction to the database modification carries on the encapsulation" We have added several methods to TableAdapter to open, submit, Rolls back the ado.net transaction. These methods use intrinsic, manually created SqlTransaction objects to assign values to TableAdapter SqlCommand objects.

In this article, we will examine how to access the TableAdapter database connection and database command level settings. Specifically, we will add functions to ProductsTableAdapter to access the connection string (connection string) and the Command Timeout setting.

Processing data with Ado.net

The Microsoft. NET Framework contains many special-purpose classes for processing data. These classes are created with System.Data namespace, including the Ado.net Classe class, and some ado.net-name classes need to rely on a particular Data provider to work. You can imagine in ado.net A data provider acts as a connection channel (communication channel) between the classes class and a datastore, which is used to deliver information. Data provider includes OLE DB, ODBC, and other data provider specifically designed to connect to a particular database system. For example, we can't connect to a Microsoft SQL with OLE DB Server database. And SqlClient is OK, because it is optimized specifically designed to connect to SQL Server.

When you programmatically access data, you typically use the following pattern:

1. New Database connection
2. Order issued
3. Use a select query to return records

Each of the above 3 steps has a separate Ado.net classes class to perform. For example, connect to a database with the SqlConnection class classes; To emit inserts, updates, deletes, or select commands, with the SqlCommand class class.

In addition to the 61st chapter, "Encapsulation of database modifications in a transaction", we do not write any ado.net code ourselves, since TableAdapters automatically generates code that includes the necessary functions of connecting to a database, issuing commands, retrieving data, Fill DataTables. But sometimes we have to customize these settings ourselves. In the next few steps we'll explore the Ado.net objects used internally by TableAdapters.

The first step: Examine the connection attribute

Each of the TableAdapter class classes has a connection attribute. Used to specify database connection information. The data type of the property and the value of the ConnectionString are based on the configuration made by the TableAdapter Setup Wizard. We remember that when you add a TableAdapter to a typed dataset, the wizard wants us to specify the data source (see Figure 1). The Web.config file specifies the connected database in the Drop-down list, and the database in the Server Explorer's data connections. If the database we want to connect to does not appear in the dropdown, click the "New Connection" button, To provide the necessary connection information.

Figure 1:tableadapter The first step in the Setup Wizard

Let's take a moment to view the code for the TableAdapter connection property, as we discussed in the first chapter, "Creating a data access layer," we can look at the automatically generated TableAdapter code in the Class View window and go to the appropriate class. Double-click the member name.

Open the View menu, select Class View (or hold ctrl+shift+c). In the upper part of the Class View window, select the Northwindtableadapters namespace, Then select the ProductsTableAdapter class. This will display the members of the ProductsTableAdapter in the lower half, as shown in Figure 2. Double-click the Connection property to view the code.

Figure 2: Double-click Connection to view automatically generated code

TableAdapter's connection properties and other connection-related code are as follows:

Private System.Data.SqlClient.SqlConnection _connection;
 private void Initconnection () {this._connection = new System.Data.SqlClient.SqlConnection (); This._connection. ConnectionString = configurationmanager.connectionstrings["northwndconnectionstring"].
ConnectionString; } internal System.Data.SqlClient.SqlConnection Connection {get {if ((this._connection = = null)) {this.
 Initconnection ();
 return this._connection;
 } set {this._connection = value; if (this. Adapter.insertcommand!= null)) {this.
 Adapter.InsertCommand.Connection = value; } if (this. Adapter.deletecommand!= null)) {this.
 Adapter.DeleteCommand.Connection = value; } if (this. Adapter.updatecommand!= null)) {this.
 Adapter.UpdateCommand.Connection = value; for (int i = 0; (I < this.) Commandcollection.length); i = (i + 1)) {if (this. Commandcollection[i]!= (null)) {(System.Data.SqlClient.SqlCommand) (this. Commandcollection[i]).
  Connection = value;

 }
 }
 }
}

When the TableAdapter class class is instantiated (instantiated), the value of the member variable _connection is equivalent to NULL. When you access the Connection property, first check to see if the member variable _ Connection is instantiated, and if not, the Initconnection method is invoked, the method instantiates the _connection, and assigns the value to the connection string specified by the TableAdapter Setup Wizard.

You can also assign a value to a SqlConnection object with the connection property, so that you can associate the new SqlConnection object with the TableAdapter SqlCommand object.

Step Two: Access the settings at the database connection level

The database connection information is encapsulated inside the TableAdapter, and it is difficult to access it from other layers of the application. However, at some point, someone's query, user, or ASP.net page needs to access or customize the TableAdapter connection information.

Let's extend the ProductsTableAdapter of the Northwind dataset to include a ConnectionString property to facilitate reading and changing the connection string used by TableAdapter at the business logic level.

　　Note: A connection string is a string that specifies the database connection information. For example, provider provider, location of the database, authentication, and other database-related settings. For more information please refer to the website connectionstrings.com

As discussed in the first chapter, "Creating a data access layer," classes that are automatically generated by typed datasets can be augmented by using partial classes (partial classes). First, in ~/app_code/ A new folder named Connectionandcommandsettings is created in the Dal folder.

Figure 3: Add a folder named Connectionandcommandsettings

To add a ProductsTableAdapter.ConnectionAndCommandSettings.cs file inside, type the following code:

Using System;
Using System.Data;
Using System.Configuration;
Using System.Web;
Using System.Web.Security;
Using System.Web.UI;
Using System.Web.UI.WebControls;
Using System.Web.UI.WebControls.WebParts;
Using System.Web.UI.HtmlControls;

Namespace Northwindtableadapters
{public
 partial class ProductsTableAdapter
 {public
 string ConnectionString
 {get {return this
  . connection.connectionstring;
  }
  Set
  {this
  . connection.connectionstring = value;
  }}}

The Department class adds a public type, named ConnectionString, to the ProductsTableAdapter class classes. This property allows the connection string used by TableAdapter to be read and changed at any level.

When the department is created and saved, open the Productsbll class. Open one of these methods, type adapter, and then enter a keyword in its range to turn on IntelliSense, and you should see that the newly added connectionstring attribute appears in IntelliSense, which means that we can programmatically read or change its value at the BLL level.

Access the entire Connection object

The department extends only one of the many properties of the Connection object: ConnectionString. If you want to access the entire connection object outside the TableAdapter range, you can change the protection level of the connection attribute. Like we did in the first step. , the connection attribute of TableAdapter is marked as internal, which means that it can be accessed only in classes of the same class. But we can go through TableAdapter. Connectionmodifier property to change its access scope.

Open the Northwind DataSet, right-click on the Productstableadatper, open the Properties window, and you will see Connectionmodifier set as the default assembly. To access the connection property outside of the dataset scope, we change it to public.

Figure 4: The access scope of the connection property can be modified through the Connectionmodifier property

After saving in return to PRODUCTSBLL class classes, just like the front side type adapter in an existing method, and then enter a keyword in its range to turn on IntelliSense, you should also see a connection attribute, This means that we can programmatically read or assign the connection settings at the business logic level.

Step three: Examine the attributes associated with command

A TableAdapter has a main query, main query, which automatically generates insert, UPDATE, and DELETE statements by default. Insert of the main query, Update,delete Statements in TableAdapter's code through the adapter attribute to a Ado.net data adapter object (ADO. NET data adapter object) to execute.
Because our tutorial uses the SqlClient provider, the adapter property is SqlDataAdapter type.

The adapter property of the TableAdapter has 3 SqlCommand type properties that emit the INSERT, Update,delete statements:

. InsertCommand
. UpdateCommand
. DeleteCommand

A SqlCommand object is responsible for sending a specific query to the database with corresponding properties, such as: The CommandText property contains the Ad-hoc SQL statement or stored procedure to execute The Parameters property is a set of SqlParameter objects. As we discussed in the first chapter, "Creating a data access layer," These command objects can be customized through the Properties window.

In addition to the main query, TableAdapter also contains a series of methods that, when invoked, A specific command is issued to the database. The command object for the main query and all other methods used by the command object are saved in the TableAdapter Commandcollection property.

Let's take a moment to look at the productstableadapter generated in the Northwind dataset about these 2 properties and their supported member variables, methods:

 private System.Data.SqlClient.SqlDataAdapter _adapter;
 
 private void Initadapter () {this._adapter = new System.Data.SqlClient.SqlDataAdapter (); ...

Code that creates the InsertCommand, UpdateCommand, ... and DeleteCommand instances-omitted for brevity ...} Private System.Data.SqlClient.SqlDataAdapter Adapter {get {if (This._adapter = null)) {this.
 Initadapter ();
 return this._adapter;

} private system.data.sqlclient.sqlcommand[] _commandcollection;

 private void Initcommandcollection () {this._commandcollection = new system.data.sqlclient.sqlcommand[9]; ... Code that creates the command objects for the main query and the ...

Productstableadapters eight methods-omitted for brevity ...} Protected system.data.sqlclient.sqlcommand[] Commandcollection {get {if ((this._commandcollection = = null)) {THIS.I
 Nitcommandcollection ();
 return this._commandcollection; }
}

Adapter and The code for the Commandcollection property is very similar to the code for the Connection property. The Get module of these properties first detects whether the corresponding member variable is NULL, and if so, calls a method to create an instance of the member variable and then assigns the command-related property .

Step Fourth: Access command-related settings

Ideally, command-level (command-level) information should be encapsulated in the data access layer. Of course, we can also extend it through a partial class, just like the database connection level (Connection-level) setting.

Since TableAdapter has only a single connection attribute, So the code to extend the database connection-level settings is straightforward. And if you want to modify the command-level settings, it's a bit more complicated because TableAdapter contains multiple command objects--insertcommand, UpdateCommand, DeleteCommand, and the Commandcollection attribute contains an unequal number of command objects. When you update command-level settings, these settings need to inform all of these command objects.

Like what If in a TableAdapter some queries take a long time to execute. When you use this TableAdapter to execute one of the queries, we may want to increase the value of the Command object's CommandTimeout property. This property specifies the time to wait for the command to execute, and the default is 30 Seconds.

To adjust the CommandTimeout attribute from the business logic layer, you can add a public type method to productsdatatable with some of the classes we created in the second step, as follows: ( Add in ProductsTableAdapter.ConnectionAndCommandSettings.cs file):

public void setcommandtimeout (int timeout)
{
 if (this. Adapter.insertcommand!= null)
 this.Adapter.InsertCommand.CommandTimeout = timeout;

 if (this. Adapter.deletecommand!= null)
 this.Adapter.DeleteCommand.CommandTimeout = timeout;

 if (this. Adapter.updatecommand!= null)
 this.Adapter.UpdateCommand.CommandTimeout = timeout;

 for (int i = 0; i < this.CommandCollection.Length; i++)
 if (This.commandcollection[i]!= null)
  This.commandcollection[i]. CommandTimeout = timeout;
}

We can call this method from the BLL layer or the presentation layer to set the command timeout value of all commands issued by a TableAdapter instance.

　　Note: The Adapter and commandcollection attributes are marked private, This means that it can only be accessed within the TableAdapter. Unlike the connection attribute, we cannot change its access permission configuration. So if you want to extend it to access it from other layers of the system, as discussed above, we have to use part of the class to provide a public type of side method or attribute to read and write to these command objects that are marked private.

Conclusion:

TableAdapters encapsulates the details of data access, so when we use TableAdapters, we don't have to care about handwriting ado.net code to connect to the database, issue commands, populate the DataTable with retrieved data, and so on. Because it has automatically generated the code .

But at some point, we need to customize these ado.net details, such as changing the connection string or the default command timeout and connection timeout values. TableAdapter automatically generates connection, Adapter, and Commandcollection properties, However, by default, these properties are either marked as internal or private. We can extend these internal information by using a partial class that uses a method or property that is marked public in a partial class. In addition, for the connection attribute of TableAdapter, we can pass T Ableadapter the Connectionmodifier property to change its access rights.

I wish you a happy programming!

Author Introduction

Scott Mitchell, author of this series of tutorials, has six asp/asp. NET book, is the founder of 4GuysFromRolla.com, has been applying Microsoft Web technology since 1998. You can click to see all Tutorials "[translation]scott Mitchell asp.net 2.0 data tutorial," I hope to learn asp.net help.