tableadapters encapsulates details such as data access,But by default these properties are either marked as internal or private, we can use some classes,Use methods or properties marked public in some classes.


In this series we use typed datasets to build the data access layer.As discussed in Chapter 1,Datatables of typed datasets are used as "warehouses" for storing data, and tableadapters are used as channels to connect to the database.To retrieve and modify data.tableadapters encapsulates many of the complex details of working with databases,Free usEliminate the pain of writing code to connect to the database, issue names, and populate datatables.

But at some point we need to delve deeper into the tableadapter and write code directly to handle ado.net objects. In Chapter 61, "Encapsulating database modifications in transactionsIn this article, we added multiple methods to tableadapter to start, commit, and roll back ado.net transactions. These methods use the built-in, manually created sqltransaction object to assign values ​​to tableadapter's sqlcommand object.

In this article, we will examine how to access tableadapter's "database connection" and "database command" level settings.Specifically,We will add functions to productstableadapter,To access the "connection string" and "command timeout" settings.

Processing data with ado.net

The Microsoft .NET framework contains many special-purpose classes for processing data.These classes are created with the system.data namespace,These include the ado.net classe. Some classes under the ado.net name depend on a specific data provider to work.You can imagine that between the ado.net classes and a data store, there is a data provider that acts as a communication channel for passing information.Data providers include oledb, odbc, and other data providers specifically designed to connect to a specific database system. For example,We cannot use oledb to connect to a Microsoft SQL Server database. SQLClient can, because it is optimized and designed to connect to SQL Server.

When programmatically accessing data,The following patterns are usually used:

1. Create a new database connection

2. Issue a command

3. Use select query to return records

Each of the above 3 steps has a separate ado.net classes to execute.For example, use the sqlconnection class to connect to the database;to issue insert, update, delete, or select commands, use the sqlcommand class.

Except Chapter 61Encapsulating database modifications in transactions》 Besides, we did not write any ado.net code ourselves, because the code automatically generated by tableadapters contains some necessary functions:connect to the database, issue commands, retrieve data, and populate datatables. But sometimes we have to customize these settings ourselves.In the next few steps we will explore the ado.net objects used internally by tableadapters.

Step 1:Examine the connection properties

Each tableadapter class has a connection property for specifying database connection information.The data type of this property and the value of the connectionstring depend on the configuration made by the tableadapter setup wizard.We remember,When adding a tableadapter to the typed dataset, the wizard asks us to specify the data source (see Figure 1). The web.config file specifies the database to connect to in the drop-down list.And the database in the data connections of the server explorer.If the database we want to connect to does not appear in the drop-down list,Click the "new connection" button to provide the necessary connection information.

Figure 1:The first step of the tableadapter setup wizard

Let's take a moment to look at the code of the tableadapter's connection property,Just like in chapter one,Create a data access layerThe same thing is discussed in "We can view the automatically generated tableadapter code in the class view window, go to the corresponding class,Then double-click the member name.

Open the "view" menu and select "class view" (or press and 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 part, as shown in Figure 2. Double-click the connection property to view the code.

Figure 2:Double-click on the connection to see the automatically generated code

The connection property of tableadapter 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 {
 if ((this.adapter.insertcommand!=null)) {
 if ((this.adapter.deletecommand!=null)) {
 if ((this.adapter.updatecommand!=null)) {
 for (int i=0;(i<this.commandcollection.length);i=(i + 1)) {
  if ((this.commandcollection [i]!=null)) {
   (this.commandcollection [i])). connection=value;

When the tableadapter class is "instantiated", the value of the member variable _connection is equal to null. When accessing the connection property, first check whether the member variable _connection has been instantiated. If not, call the initconnection method. This method instantiates _connection,Then assign it with the connection string specified by the tableadapter setup wizard.

You can also use the connection property to assign a value to a sqlconnection object,In this way, you can associate this new sqlconnection object with the tableadapter's sqlcommand object.

Step 2:Access the "Database Connection" level settings

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's connection information.

Let's extend the productstableadapter of the northwind dataset,To include a connectionstring property, in order to read and change the connection string used by the tableadapter in the business logic layer.

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

Like in the first chapterCreate a data access layerAs discussed in》The automatically generated classes of typed datasets can be extended by using partial classes.First, create a new folder named connectionandcommandsettings in the ~/app_code/dal folder.

Figure 3:Add a folder named connectionandcommandsettings

Add a productstableadapter.connectionandcommandsettings.cs file and 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
  return this.connection.connectionstring;

This local class adds a public type property named connectionstring to the productstableadapter class. This property allows reading and changing the connection string used by the tableadapter at any level.

When the local class is created and saved,Open the productsbll class. Open one of these methods,Type adapter, then enter a keyword within its scope to turn on IntelliSense,You should see this newly added connectionstring attribute appear in IntelliSense,This shows that we can programmatically read or change its value at the bll layer.

Access the entire connection object

This local class extends only one of the many properties of the connection object:connectionstring. If i want to access the entire connection object outside the scope of the tableadapter,You can change the protection level of the connection property.As we examined in the first step,The connection attribute of tableadapter is marked as internal, which means thatIt can only be accessed in sibling classes.However, we can change its access scope through the connectionmodifier property of tableadapter.

Open the northwind dataset, right-click on productstableadatper,Open the properties window,You will see the connectionmodifier set to the default assembly. To access the connection property outside the scope of the dataset, we changed it to public.

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

After saving, return to the productsbll class, just type adapter in an existing method as before, and then enter a keyword within its scope to turn on IntelliSense.You should also see a connection property, which means we can programmatically read or assign connection settings in the business logic layer.

Step 3:Examine the attributes related to command

A tableadapter has a main query. By default, the main query automatically generates insert, update, and delete statements. The insert, update, and delete statements of the main query use the adapter property in the tableadapter code to create an ado.net data adapter object (ado.net data adapter object).

Since our tutorial uses the sqlclient provider, the adapter property is of type sqldataadapter.

The adapter property of tableadapter has 3 properties of type sqlcommand,Used to issue insert, update, delete statements:




A sqlcommand object is responsible for issuing a specific query to the database,And have corresponding properties,For example:the commandtext attribute contains the ad-hoc sql statement or stored procedure to be executed;The parameters attribute is a set of sqlparameter objects. Just like we did in the first chapter,Create a data access layer"It ’s the same thing,These command objects can be customized by the properties window.

In addition to the main query,tableadapter also contains a series of methods,When calling these methods,Issue specific commands to the database.The command object of the main query and the command object used by all other methods are stored in the commandcollection property of the tableadapter.

Let's take a moment to look at the productstableadapter in the northwind data set about these two attributes and the member variables and methods they support:

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 other eight methods-omitted for brevity ...
protected system.data.sqlclient.sqlcommand [] commandcollection {
 get {
 if ((this._commandcollection == null)) {
  this.initcommandcollection ();
 return this._commandcollection;

The code for the adapter and commandcollection properties is very similar to the code for the connection property.The get module of these properties first checks whether the corresponding member variable is null, and if so, calls a method,To create an instance of this member variable,Then assign values ​​to the command-related properties.

Step 4:Access command-related settings

Ideally,Command-level information should be encapsulated in the data access layer.Of course, we can also extend it with a partial class,Just like the database connection-level setting.

Because tableadapter only has a single connection property, the code used to extend database connection level settings is very intuitive and easy to understand.If i want to modify the command-level settings, it is more complicated.This is because the tableadapter contains multiple command objects-insertcommand, updatecommand, deletecommand, and commandcollection properties that contain a variable number of command objects. When updating command-level settingsThese settings need to inform all of these command objects.

For example, if some queries in a tableadapter take a long time to execute.When using this tableadapter to execute one of these queries,We may want to increase the value of the commandtimeout property of the command object.This attribute specifies how long to wait for the command to execute,The default is 30 seconds.

If i want to adjust the commandtimeout attribute from the business logic layer,You can use the partial class we created in the second step to add a public method to productsdatatable.As follows:(added in productstableadapter.connectionandcommandsettings.cs file):

public void setcommandtimeout (int timeout)
 if (this.adapter.insertcommand!=null)
 if (this.adapter.deletecommand!=null)
 if (this.adapter.updatecommand!=null)
 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 for all commands issued by a certain tableadapter instance.

Note:The adapter and commandcollection attributes are marked as private, which means they can only be accessed inside the tableadapter.Different from the connection property,We cannot change its access configuration.So if i want to extend it to access it from other layers of the system,As discussed above,We must use some classes to provide a public type method or property,Read and write these command objects marked as private.


Tableadapters encapsulates details such as data access,So when we use tableadapters, we don't need to care about handwritten ado.net code to connect to the database, issue commands, populate datatable with retrieved data, etc. because it has generated these codes automatically.

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. The tableadapter automatically generates the connection, adapter, and commandcollection properties, but by default these properties are either marked internal or private We can extend this internal information,The method is to use partial classes,Use methods or properties marked public in some classes.In addition, for the connection property of the tableadapter, we can change its access permissions through the connectionmodifier property of the tableadapter.

Happy programming!

About the Author

  • Previous Analysis of URIphp's _fetch_uri_string () function usage in CI framework source code
  • Next JS + Canvas achieves rain and snow effect