Use the ASP. NET 2.0 objectdatasource Control

Release date: 12/3/2004 | Updated on: 8/4/2005

Stephen Walther
Superexpert

Applicable:
Microsoft ASP. Network 2.0
Microsoft Visual maxcompute 2005

Summary: Ado. NET andSqldatasourceThis makes it easy for people to access two layers of data in ASP. NET 2.0. However, they are applied at Layer n.ProgramIs not so effective, andObjectdatasourceBut it can provide the same ease of use for business objects in n-tier applications. Learn how to use ASP. NET 2.0 Framework and useObjectdatasourceControls generate multi-layer Web applications in a strict sense.

Content on this page

	Introduction
	Use the objectdatasource control to display data
	Use parameters in combination with the objectdatasource Control
	Use the objectdatasource control to edit data
	Use the objectdatasource control and sqldatasource control in combination
	Summary

Introduction

In Microsoft ASP. NET 2.0 Framework, database access is greatly simplified. Use the brand newSqldatasourceControl, you do not need to write a lineCodeYou can select, update, insert, and delete database data.

When a simple application is generated,SqldatasourceControls are a good choice. If you need to quickly generate a Web page that allows users to display and edit database records, useSqldatasourceThe widget can complete this operation within several minutes.

For example, I generated such a page on time. Combined UseSqldatasourceControls andGridviewControl. Within 1 minute 15 seconds, a page is generated to display the content of the northwind products database table. It's so fast!

However,SqldatasourceControl. If you useSqldatasourceControl, you are doing a bad thing.SqldatasourceThe disadvantage of the control is that it forces you to mix the user interface layer with the business logic layer. Any application architect will tell you that the behavior of mixing multiple layers is not advisable.

When generating multi-layer Web applications in a strict sense, you should have a clear user interface layer, business logic layer, and data access layer. Only becauseSqldatasourceIt is totally wrong to reference SQL statements or stored procedures at the user interface layer.

So why do you need to care about these things? Yes. In many cases, you don't have to worry about it. If you are creating a simple web application, you can useSqldatasourceControl. For example, if you need to generate an application composed of individual pages to display the contents of the database table, it is unwise to divide the application into multiple application layers.

Unfortunately (if you have already paid tuition fees, You will be lucky), not all web applications are simple. After an application reaches a certain level of complexity, if it is divided into multiple application layers, it is easier to generate and maintain them.

Dividing an application into multiple application layers has many advantages. If you have a clear business logic layer, you can create a keystore library that can be called from multiple pages. In other words, creating a clear business logic layer improves code reuse. In addition, creating a clear and independent application layer makes the application easier to modify. For example, a clear hierarchy allows you to modify the user interface without modifying the data access code.

If you need to use ASP. NET Framework to generate multi-layer Web applications, you can use another new control introduced by ASP. NET 2.0 Framework:ObjectdatasourceWidgetObjectdatasourceControls allow youGridviewAndDropdownlistThis user interface control is bound to an intermediate layer component.

This articleArticleIsObjectdatasourceControl. In this article, you will learn how to use this control to display and edit database data. We will also discuss how to use them in combination.ObjectdatasourceControls andSqldatasourceControls to simplify database access.

Back to Top

Use the objectdatasource control to display data

We assume that you need to create a web page to display the content of the products database table. Further imagine that an existing business component contains a method for retrieving this data.

For example, the component in Listing 1 containsGetproductsThis method returns a datareader to represent the content of the products database table.

Listing 1: productinfo. CS (C #)

Using system; using system. data; using system. data. sqlclient; public class productinfo {const string constring = "Server = localhost; trusted_connection = true; database = northwind"; public static sqldatareader getproducts () {sqlconnection con = new sqlconnection (constring ); string selectstring = "select * from products"; sqlcommand cmd = new sqlcommand (selectstring, con); con. open (); sqldatareader DTR = cmd. executereader (commandbehavior. closeconnection); Return DTR ;}}
 

Listing 1: productinfo. VB (Visual Basic. Net)

Imports system. dataimports system. data. sqlclientpublic class productinfoconst constring as string = _ "Server = localhost; trusted_connection = true; database = northwind" Public Function getproducts () as sqldatareaderdim con as new sqlconnection (constring) dim selectstring as string = "select * from products" dim cmd as new sqlcommand (selectstring, con) con. open () dim DTR as sqldatareader = _ cmd. executereader (commandbehavior. closeconnection) return dtrend functionend class
 

If you add the class contained in Listing 1 to the Code directory of the application, ASP. NET Framework automatically compiles the class. In other words, if you add this class to the Code directory, you can use it on the ASP. NET page immediately.

We will useGridviewControls (in ASP. NET2.0ReplacedDataGridControl) to displayGetproductsMethod. The ASP. NET page in Listing 2 contains a boundObjectdatasourceControlGridview.

Listing 2: showproducts. aspx

<HTML>  
 
TheObjectdatasourceControls contain two important attributes.TypenameAttribute indicates the class name, whileSelectmethodProperty indicates the name of the method to be called on this class when selecting data.
 

 
In Listing 2,ObjectdatasourceControls are used to callProductinfoClassGetproductsMethod. BecauseGridviewControl boundObjectdatasourceControlGridviewControlPerformanceidAttribute,GridviewControl to display the product list (see figure 1 ).
 

 
Figure 1. Use the objectdatasource control to display the product
 

You can useObjectdatasourceControls are bound to any standard ASP. NET data (for example,Gridview,Dropdownlist,TreeviewAndRepeaterControls ).ObjectdatasourceControls allow you to bind any standard controls to components.
 

 
SelectmethodYou can reference static methods (shared in Visual Basic. net) or instance methods. If you are using an instance methodObjectdatasourceThe control automatically creates an instance of this component before calling this method. After the method call is completed, the component is automatically destroyed.
 
Back to Top
 
 
Use parameters in combination with the objectdatasource Control
 
You can useObjectdatasourceControls. When you call a method, this method is useful if you need to pass certain values (such as control properties or query string values) to this method.
 

 
In the previous section, we usedObjectdatasourceControls create a page to display all records from the products database table. In this section, we will modify this page to allow usersDropdownlistControl (see figure 2) Select a product category.
 

 
Figure 2. Select a product category from dropdownlist
 

 
Listing 3 contains the modified productinfo component.
 

Listing 3: productinfo2.cs (C #)
 

 
Using system; using system. data; using system. data. sqlclient; public class productinfo2 {const string constring = "Server = localhost; trusted_connection = true; database = northwind"; Public sqldatareader getproducts (string category) {sqlconnection con = new sqlconnection (constring); string selectstring = "select products. * "+" from products inner join categories "+" on products. categoryid = categories. categoryid "+" where categoryname = @ categoryname "; sqlcommand cmd = new sqlcommand (selectstring, con); cmd. parameters. addwithvalue ("@ categoryname", category); con. open (); sqldatareader DTR = cmd. executereader (commandbehavior. closeconnection); Return DTR ;}}
 
 
Listing 3: productinfo2.vb (Visual Basic. Net)
 

Imports system. dataimports system. data. sqlclientpublic class productinfo2const constring as string = _ "Server = localhost; trusted_connection = true; database = northwind" Public Function getproducts (byval category as string) _ as sqldatareaderdim con as new sqlconnection (constring) dim selectstring as string = "select products. * "&" from products inner join categories "& _" on products. categoryid = categories. categoryid "& _" where categoryname = @ categoryname "dim cmd as new sqlcommand (selectstring, con) cmd. parameters. addwithvalue ("@ categoryname", category) con. open () dim DTR as sqldatareader = _ cmd. executereader (commandbehavior. closeconnection) return dtrend functionend class
 
 
The modified productinfo component in listing 3 contains a modifiedGetproductsMethod, which contains a parameter for the category name. This parameter is used to limit the products returned from the database.
 

 
Listing 4 containsDropdownlist,GridviewAndObjectdatasourceControl, so that you can select different types of products to be displayed.
 

Listing 4: showproducts2.aspx
 

 
<HTML>  
 
FromDropdownlistWhen the control selects a new category,GridviewThe control automatically displays only products from the selected category.
 

Note thatObjectdatasourceControl containsSelectparametersElement. This element listsObjectdatasourceControlSelectmethodAll parameters used for the method specified by the property. In this example,SelectedparametersThe element contains a single parameter named category. This parameter indicates thatDropcategoriesDropdownlistControlSelectedvalueAttribute Value.
 
Back to Top
 
 
Use the objectdatasource control to edit data
 
ObjectdatasourceThe control contains four important attributes:SelectmethodProperties,UpdatemethodProperties,InsertmethodAttributes andDeletemethodAttribute. Using these attributes, you can specify all the methods required to perform standard database operations.
 

 
For example, you can useObjectdatasourceControl to edit database data. In listing 5ProductinfoClass contains a newUpdateproductAndDeleteproductMethod.
 

 
Listing 5: productinfo3.cs (C #)
 

Using system; using system. data; using system. data. sqlclient; public class productinfo3 {const string constring = "Server = localhost; trusted_connection = true; database = northwind"; public static sqldatareader getproducts () {sqlconnection con = new sqlconnection (constring ); string selectstring = "select productid, productname," + "unitprice from products order by productid"; sqlcommand cmd = new sqlcommand (selectstring, con); con. open (); sqldatareader DTR = cmd. executereader (commandbehavior. closeconnection); Return DTR;} public static void updateproduct (INT original_productid, string productname, decimal unitprice) {sqlconnection con = new sqlconnection (constring ); string updatestring = "update products set" + "productname = @ productname, unitprice = @ unitprice" + "where productid = @ productid"; sqlcommand cmd = new sqlcommand (updatestring, con ); cmd. parameters. addwithvalue ("@ productname", productname); cmd. parameters. addwithvalue ("@ unitprice", unitprice); cmd. parameters. addwithvalue ("@ productid", original_productid); con. open (); cmd. executenonquery (); con. close ();} public static void deleteproduct (INT original_productid) {sqlconnection con = new sqlconnection (constring); string deletestring = "delete products" + "where productid = @ productid "; sqlcommand cmd = new sqlcommand (deletestring, con); cmd. parameters. addwithvalue ("@ productid", original_productid); con. open (); cmd. executenonquery (); con. close ();}}
  listing 5: productinfo3.vb (Visual Basic. Net)  

Imports system. dataimports system. data. sqlclientpublic class productinfo3const constring as string = _ "Server = localhost; trusted_connection = true; database = northwind" public shared function getproducts () as sqldatareaderdim con as new sqlconnection (constring) dim selectstring as string = "select productid," & _ "productname, unitprice from products order by productid" dim cmd as new sqlcommand (selectstring, con) con. open () dim DTR as sqldatareader = _ cmd. executereader (commandbehavior. closeconnection) return dtrend functionpublic shared sub updateproduct (byval original_productid _ as integer, byval productname as string, _ byval unitprice as decimal) dim con as new sqlconnection (constring) dim updatestring as string = "update products" & _ "set productname = @ productname, unitprice = @ unitprice" & _ "where productid = @ productid" dim cmd as new sqlcommand (updatestring, con) cmd. parameters. addwithvalue ("@ productname", productname) cmd. parameters. addwithvalue ("@ unitprice", unitprice) cmd. parameters. addwithvalue ("@ productid", original_productid) con. open () cmd. executenonquery () con. close () end subpublic shared sub deleteproduct (byval original_productid _ as integer) dim con as new sqlconnection (constring) dim deletestring as string = "delete products" & _ "where productid = @ productid" dim cmd as new sqlcommand (deletestring, con) cmd. parameters. addwithvalue ("@ productid", original_productid) con. open () cmd. executenonquery () con. close () end subend class
You can modifyProductinfoClass andObjectdatasourceTo edit the contents of the Products Database Table.
 

 
Listing 6: showproducts3.aspx
 

    show products    
   
    
     
      
      
     
    
    
     
      
      
      
     
    
     
In Listing 6,GridviewControl displays the values of the productname and unitprice columns. BecauseGridviewControlAutogenerateeditbuttonAndAutogeneratedeletebuttonProperty is set to true,GridviewThe user interface used to edit and delete the product line is automatically generated (see figure 3 ).
 

 
Figure 3. Use the objectdatasource control to edit data
 

 
When you click the update link to update a product,ObjectdatasourceControl will be calledUpdateproductMethod. Please note that,ObjectdatasourceControl in itsUpdateparametersTheUpdateproductMethod parameters.
 

 
There is an additional parameter that needs to be discussed. We need to pass the value of the productid column of the updated rowUpdateproductThe method is notGridviewThe productid column is displayed. We must allocate the productid columnGridviewControlDatakeynamesAttribute. The name of this column is changed to original_productid instead of productid, because we are passing the unedited version of the productid column to the update method.
 

If you click the delete link,ObjectdatasourceControl will be calledDeleteproductMethod. BecauseGridviewControlDatakeynamesThe property has a value productid, so a parameter named original_productid is automatically passedDeleteproductMethod.
 
Back to Top
 
 
Use the objectdatasource control and sqldatasource control in combination
 
So far, we haveObjectdatasourceControls and usageSqldatareaderObject components to retrieve database data. There is another option here. That is, it is not used in the component.SqldatareaderOrDatasetSuch as ADO. Net object, but used in componentsSqldatasourceControl.
 

 
You can useSqldatasourceThe fact of controls seems strange. Generally, you do not use controls in components, because the controls generally have a visual representation and participate in the page execution lifecycle. WhileDatasourceControls are somewhat special at this point.
 

 
If you want to simplify the database access code in the component, you can useSqldatasourceControl. Listing 7 shows this method.
 

 
Listing 7: productinfo4.cs (C #)
 

Using system; using system. collections; using system. web. ui; using system. web. UI. webcontrols; public class productinfo4 {const string constring = "Server = localhost; trusted_connection = true; database = northwind"; public static ienumerable getproducts () {string selectstring = "select * from products"; sqldatasource DSRC = new sqldatasource (constring, selectstring); DSRC. datasourcemode = sqldatasourcemode. dataset; return DSRC. select (datasourceselectarguments. empty );}}
 
 
Listing 7: productinfo4.vb (Visual Basic. Net)
 

Imports system. collectionsimports system. web. uiimports system. web. UI. webcontrolspublic class productinfo4const constring as string = _ "Server = localhost; trusted_connection = true; database = northwind" public shared function getproducts () as ienumerabledim selectstring as string = "select * from products" dim DSRC as new sqldatasource (constring, selectstring) DSRC. datasourcemode = sqldatasourcemode. datasetreturn DSRC. select (datasourceselectarguments. empty) end functionend class
 
 
In listing 7, we instantiateSqldatasourceA new instance of the control.SqldatasourceThe constructor accepts a parameter for database connection strings and a parameter for command text used with the SELECT command. Next, SetPerformancemodeSet the attribute value to dataset (another option here is datareader ). FinallySqldatasourceCallSelectMethod, and return all records from the products database tableDataview.
 

 
You canProductinfoClass andObjectdatasourceControls are used together:
 

Listing 8: showproducts4.aspx
 

 
<HTML>  
 
In listing 8,GridviewControl boundObjectdatasourceControl. WhileObjectdatasourceThe control is called again.Productinfo4ClassGetproductsMethod to retrieveGridviewThe data displayed. Finally,GetproductsUsageSqldatasourceControl to retrieve database data.
 
Back to Top
 
 
Summary
ASP. NET 2.0 Framework greatly simplifies database access, allowing you to easily generate simple and complex ASP. NET applications. If you need to generate a simple database to drive web applications, you can use the newSqldatasourceControl. If you need to generate a more complex application or example, you can useObjectdatasourceControl.
 

 
ObjectdatasourceControl allows you to continue to use the middle layer components on the data driver page. The main advantage is that you can bind to a component without writing any code, which greatly simplifies your user interface. UseObjectdatasourceControl, I can generate a component and page that can display the product database table in 3 minutes and 20 seconds. Although this method takes more timeSqldatasourceControls are long, but I feel that the page architecture is much better when doing so.
 

 
About the author
 

 
Stephen WaltherHe has written the best-selling books on ASP. NET.ASP. NET unleashed. He is also an architect and chief developer of ASP. NET Community Starter Kit (an example ASP. NET application developed by Microsoft. He also provides ASP. NET training to a number of American companies, including NASA and Microsoft, through his company superexpert (http://www.superexpert.com.