PC TRAINING: ADO.NET

ADO.NET

At the releasing time of the .NET Framework, Microsoftintroduced a new data access model, called ADO.NET. TheActiveX Data Object acronym was no longer relevant, asADO.NET was not ActiveX, but Microsoft kept the acronym due to the huge success of ADO. In reality, it’s an entirely new data access model written in the .NET Framework.

ADO.NET supports various data sources through both ODBCand OLE-DB, but it also offers another option of using database-specific data providers.

These data providers offer greater performance by being able to take advantage of data-source-specific optimizations.

The original release of ADO.NET included a SQL provider and an OLE-DB provider, with the ODBC and Oracle providers being introduced later. But it supports a number of other providers too.

Data Source, Data Control Parameters & Different views

Understanding Data Source Controls

ASP.NET includes data source controls that allow you to work with different types of data sources such as a database, an XML file, or a middle-tier business object. Data source controls connect to and retrieve data from a data source and make it available for other controls to bind to, without requiring code. They can also support modifying data.

This topic provides information about the different types of data source controls inASP.NET. The data source control model is extensible, so you can also create your own data source controls that interact with different data sources or that provide additional functionality for an existing data source.

Data Source Control Comparison

The .NET Framework includes data source controls to support different data-binding scenarios. The following table describes the built-in data source controls. More detail about each type of data source control is provided later in this topic.

Data source control	Description
LinqDataSource	Enables you to use Language-Integrated Query (LINQ) in an ASP.NET Web page through declarative markup in order to retrieve and modify data from a data object. Supports automatic generations of select, update, insert, and delete commands. The control also supports sorting, filtering, and paging.
ObjectDataSource	Enables you to work with a business object or other class and create Web applications that rely on middle-tier objects to manage data. Supports advanced sorting and paging scenarios unavailable with the other data source controls.
SqlDataSource	Enables you to work with Microsoft SQL Server, OLE DB, ODBC, or Oracle databases. When used with SQL Server, supports advanced caching capabilities. The control also supports sorting, filtering, and paging when data is returned as a DataSet object.
AccessDataSource	Enables you to work with a Microsoft Access database. Supports sorting, filtering, and paging when data is returned as a DataSet object.
XmlDataSource	Enables you to work with an XML file, especially for hierarchicalASP.NET server controls such as the TreeView or Menu control. Supports filtering capabilities using XPath expressions and enables you to apply an XSLT transformation to the data. The XmlDataSource allows you to update data by saving the entire XMLdocument with changes.
SiteMapDataSource	Used with ASP.NET site navigation.

LinqDataSource Control

The LinqDataSource control enables you to use LINQ in an ASP.NET page to retrieve data from a database table or an in-memory data collection. You can use declarative markup to write all the conditions that are required in order retrieve, filter, order, and group the data.

When you retrieve data from a SQL database table, you can also configure a LinqDataSource control to handle update, insert, and delete operations. You can do this writing SQL commands to perform these tasks. By using the LinqDataSource control, you can reduce the amount of code that is required for data operations, compared to performing the same operations in other data source controls.

ObjectDataSource Control

The ObjectDataSource control works with a business object or other class in Web applications that rely on middle-tier business objects to manage data. The control is designed to interact with an object that implements one or more methods to retrieve or modify data. When data-bound controls interact with the ObjectDataSource control to retrieve or modify data, the ObjectDataSource control passes values from the bound control to the source object as parameters in method calls.

The source object's data-retrieval methods must return a DataSet, DataTable, or DataView object, or an object that implements the IEnumerable interface. If the data is returned as a DataSet, DataTable, or DataView object, the ObjectDataSource control can cache and filter the data. You can also implement advanced paging scenarios if the source object accepts page size and record index information from the ObjectDataSource control.

SqlDataSource Control

The SqlDataSource control retrieves and modifies data using SQL commands. The SqlDataSource control works with Microsoft SQL Server, OLE DB, ODBC, and Oracle databases.

The SqlDataSource control can return results as a DataReader or a DataSet object. It supports sorting, filtering, and caching when the results are returned as a DataSet. When you are working with Microsoft SQL Server, the control has the added benefit that cache results can be invalidated when the database changes, using a SqlCacheDependency object.

AccessDataSource Control

The AccessDataSource control is a specialized version of the SqlDataSource control, designed to work specifically with Microsoft Access .mdb files. As with the SqlDataSource control, you use SQL statements to define how the control fetches and retrieves data.

XmlDataSource Control

The XmlDataSource control reads and writes XML data so that you can work with it using controls such as the TreeView and Menu controls. The XmlDataSource control can read either an XML file or string of XML. If the control is working with an XML file, it can write modified XML back to the source file. If a schema is available that describes the data, the XmlDataSource control can use the schema to expose data using typed members.

You can apply an XSLT transformation to the XML data, which allows you to restructure the raw data from the XML file into a format better suited to the control you want to bind to the XML data.

You can also apply XPath expressions to the XML data, which allows you to filter the XMLdata to return only certain nodes in the XML tree, to look for nodes that have specific values in them, and so on. Using an XPath expression disables the ability to insert new data.

SiteMapDataSource Control

The SiteMapDataSource control works with ASP.NET site maps and provides site navigation data. It is most commonly used with the Menu control. The SiteMapDataSource control is also useful when you want to customize site navigation using site map data with Web server controls that are not specifically designed for navigation, such as the TreeView or DropDownList controls.

Data Source, Data Control Parameters & Different views

Working with Data Source Control Parameters

Data source controls accept input parameters which enable you to pass any values to them at the runtime. Parameters are very useful in many conditions, such as using search for obtaining data or to supply data values for deleting, inserting or even for updating operations on a data store. Parameters are also allow you to filter the data and to create the master detail applications with very little or absolutely no code.

Data-bound controls, such as GridView or FormView controls, can be used for accepting the customized values are passed by the parameters. You can also make your applications secure by means of parameterized queries which in turn project against the SQL injection attacks.

Several sources can be used to obtain the parameter values. Web server control properties like cookies, session state, QueryString fields, user profile properties etc. supply values to parameterized data operations by mean of Parameter objects.

Parameter Types

You can specify several types of parameter objects in your Web application. The type of the parameter object determines where the parameter value comes from. The Parameter class is the base class for all parameter objects and includes Name, Type, Direction, and DefaultValue properties that are common to all parameter types.

You typically use the Parameter base class to specify how a data source should handle parameter values obtained from an associated data-bound control, such as the values passed by a GridView control for an Update or Delete operation. You can use parameter types that derive from the Parameter class to obtain values from other sources, as described in the following table.

Parameter Type	Description
ControlParameter	Sets a parameter to the property value of a Control on an ASP.NET Web page. You specify the Control using the ControlID property. You specify the name of the property that supplies the parameter value using the ControlParameter object's PropertyName property. Some controls that derive from Control define a ControlValuePropertyAttribute, which determines the default property from which to retrieve the control's value. The default property is used whenever the PropertyName property is not explicitly set. The ControlValuePropertyAttribute is applied to the following control properties: System.Web.UI.WebControls.Calendar.SelectedDate System.Web.UI.WebControls.CheckBox.Checked System.Web.UI.WebControls.DetailsView.SelectedValue System.Web.UI.WebControls.FileUpload.FileBytes System.Web.UI.WebControls.GridView.SelectedValue System.Web.UI.WebControls.Label.Text System.Web.UI.WebControls.TextBox.Text System.Web.UI.WebControls.TreeView.SelectedValue
CookieParameter	Sets a parameter to the value of an HttpCookie object. You specify the name of the HttpCookie object using the CookieName property. If the specified HttpCookie object does not exist, then the value of the DefaultValue property is used as the parameter value. Note: Only single-valued cookies are supported.
FormParameter	Sets a parameter to the value of an HTML form field. You specify the name of the HTML form field using the FormField property. If the specified HTML form field value does not exist, then the value of the DefaultValue property is used as the parameter value.
ProfileParameter	Sets a parameter to the value of a property from the current user profile (Profile). You specify the name of the profile property using the PropertyName property. If the specified profile property does not exist, then the value of the DefaultValue property is used as the parameter value..
QueryStringParameter	Sets a parameter to the value of a QueryString field. You specify the name of the QueryString field using the QueryStringField property. If the specified QueryString field does not exist, then the value of the DefaultValue property is used as the parameter value.
SessionParameter	Sets a parameter to the value of a Session object. You specify the name of the Session object using the SessionField property. If the specified Session object does not exist, then the value of the DefaultValue property is used as the parameter value.

Strongly Typing Parameter Values

By default, parameters are typed as Object. If a parameter value is of another type, such as DateTime or Int32, you can create Parameter objects explicitly and set the parameter's Type property to a TypeCode value.

Parameter Direction

Parameters are input parameters by default. In some cases, such as when you use stored procedures, you might need to read a value returned from the data source. If so, you can set the Parameter object's Direction property to ensure that you capture information that the data source returns to your Web application. The supported parameter direction settings are Input, InputOutput, Output, and ReturnValue.

You will typically handle a data source control event, such as an Inserted or Updated event, to obtain the parameter's return value after the data operation is completed.

Data Source Controls and Parameters

There are various ways to perform parameterized operations with data source controls. TheSQL statement, such as SelectCommand can be used in association with SqlDataSource and AccessDataSource controls. These controls allow you to specify placeholders for the parameters in an SQL used. The ObjectDataSource control, however, uses the parameters to determine the most suitable method signature to call for a particular data operation, such as the SelectMethod.

For each type of data operation, there is a parameter collection commonly included in a DataSource control. The SelectParameters collection can be used while selecting the data, whereas you can use UpdateParameters collection for updating a data item and so on. Then these parameters collection contents are collected and supplied to the underlying data source for a particular action.

The SqlDataSource control uses the System.Data.SqlClient data provider to bind with theSQL Server as the Data Source. The System.Data.SqlClient provider supports named parameters as placeholders, as shown in the following example:

SELECT * From Employees WHERE LastName = @LastName AND FirstName = @FirstName

If you are connecting to an OLE DB or ODBC data source, you can configure the SqlDataSource control to use the System.Data.OleDb or System.Data.Odbc provider to work with your data source, respectively. The System.Data.OleDb and System.Data.Odbc providers support only positional parameters identified by the ? character, as shown in the following example:

SELECT * FROM Employees WHERE LastName = ? AND FirstName = ?

Data Source, Data Control Parameters & Different views

Data Binding using Databound Controls

As with a number of Windows controls, some Web Server controls support complex data binding, such as the DataGrid control(other such controls include list boxes, check box lists, data lists, and so on) and some of the newly added controls, such as GridView control and FormView control, DetailsView, TreeView and Menu Control.

Binding of these data bound controls is like complex binding as a control can display multiple fields at once, as in a data grid, which can display an entire table. You can find the noteworthy properties you use to support complex data binding in Web applications (not all complex-bound controls will support all these properties) in the given table:

Noteworthy properties of Databound controls
Property	Description
DataSource	Obtains the source of a list of data values.
DataMember	Obtains the data member in a data source to bind to
DatakeyField	Obtains the primary key field in the data source assigned to the DataSource property.
DataTextField	Obtains the field name from a data source to bind to
DataTextFormatString	Obtains the string that specifies a data display format
DataValue	Obtains the data field to use for the value of each list item, much like the ValueMember property in Windows controls

Note that you use the DataTextFormatString property to create a custom display format for data. The data format string consists of two parts, separated by a colon, in the form {X:Ynn}. The value before the colon, i.e. X, specifies the parameter index in a zero-based list of parameters (currently, this should always be 0), the charater after the colon, i.e. Y, specifies the format to display the value in, and nn specifies format options. Here are the possible formats for the Y parameter:

? C – Uses currency format
? D – Uses decimal format
? E – Uses scientific (exponential) format
? F – Uses fixed format
? G – Uses general format
? N – Uses number format
? X – Uses hexadecimal format

For example, the formatting string {0:D4} formats a data item with four decimal places.

Data Source, Data Control Parameters & Different views

Working with GridView Control

In ASP.NET 2.0, we have a new and more powerful control named GridView to display the data. In addition, you can also display, edit, delete, sort and page through the data records. The data in the GridView control can be fetched from different kinds of data sources, such as databases, XML files, and other objects used for exposing data. GridView control can be used to perform the following tasks:

? Binding to data source control automatically and displaying data.

? Sorting, editing, selecting, deleting, and paging through the displayed data as retrieved from the data source control.

Besides, the GridView control is a highly customizable control and you can set its behaviors and appearance in the following ways:

? By specifying custom columns and styles
? By using templates
? By adding user-defined code to the functionality of the GridView control

The major enhancement in GridView control is that you can now perform actions, like sorting, paging, with a click of mouse, instead of writing your own custom code as compared to DataGrid control which exists in ASP.NET 1.1.

Data Binding with the GridView Control

The following are the two options available for binding data to the GridView control:

? Using the DatasourceID Property – This approach enables you to bind the GridView control to a data source control. It is often used as it allows GridView control to take benefits of the functionalities of the data source control and also allows you to sort, page through, and update the control.

? Using the DataSource Property – It allows you to bind objects, like ADO.NET datasets and data readers. However, it requires much of coding for sorting, paging, and updating data records.

You can specify the format of the GridView control for every row and column. You can specify whether the row will be displayed as items, selected items, edit-mode items or the alternating items. You can also supply the settings for layout, color, fonts and even for the alignment.

You can use the GridView’s handling events to customize its functionality. This control provides the events that occur before and after every navigation or edit operation.

GridView control displays data in read-only mode, which is the default property of this control. However, it also supports the Edit mode. In this mode, it displays rows as the editable controls, such as TextBox control, etc.

GridView control can also display the ‘Delete’ button that can be clicked to delete the corresponding record from the data source. The editing and deleting operations can also be performed without having to write code. This can be achieved by associating the GridView control with the DataSource control. You can customize the input controls, such as TextBox control, with using the templates.

Example (GridView Control)

Here is an example of GridView control. First create a web form and drag and drop GridView control from Data Toolbox to web form.

Select “Choose Data Source” option from GridView smart tag. From “Choose Data Source”option select <New data source…> option.

A new Data Source Configuration Wizard will be open. Select database as data source.

A new Data Source object has been created named SqlDataSource1 (In case of SQLServer Data Provider). Now, click on OK button. “Configure Data Source” screen will be open. Click on New Connection button to make database connection. A new Add Connection screen will be open.

Select you Server Name and Select your database name and click on test connection to make sure that connection was established or not. After making a successful connection, click on OK button.

Here you can see your connection string.

Click on ‘Next’ button.

If you want tot save your new connection in your configuration file then check your ‘Yes, save this connection as’ checkbox other wise uncheck this checkbox. Click on ‘Next’ button. A ‘Configure your Select statement’ screen will be open.

Now, select ‘specify a custom SQL statement or stored procedure’ radio button and click on ‘Next’ button. A new ‘Define Customs Statements or Stored procedures’ screen will be open.

Select you query operation type, and click on ‘Query Builder’ button. A new ‘Add Table’ screen will be open.

Select your table name and click on Add button to add this table into your query builder.

Select your table fields and click on OK button. You can see your SQL statement in your query builder. Click on next button.

To test your query result, click on ‘Test Query’ button. Click on ‘Finish’ button to close this wizard. Now, you can see your FormView control like this:

Now, run this application and see the result like this

Data Source, Data Control Parameters & Different views

Working with DetailsView Control

The DetailsView control is used to display a single record from a data source. It’s often used in combination with a GridView control for master/details presentation of data. You can display, edit, insert or even delete a single record from the underlying data source.

It is basically used for updating and inserting new records. In a master/details scenario, where the selected record value of the master control decides the record to be displayed in the DetailsView control, only a single data record is displayed at a given time, even if its data source contains multiple records.

To perform tasks, such as updating, inserting and deleting data records, the DetailsView control basically depends on the associated data source control. However, one point that’s equally noteworthy here is that DetailsView control doesn’t support sorting. This control supports paging and hence provides user interface for navigating between the data records. The paging behavior can be enabled by setting the AllowPaging property to True.

Data Binding with the DetailsView Control

The following options are available for binding data to the DetailsView control:

? Using the DataSourceID property – This allows you to bind the DetailsView control with the data source control. This is the mostly used option as it allows the DetailsView control to make use of the capabilities of the data source control and provides paging and updating.

? Using the DataSource property – This makes binding possible with various objects, such as ADO.NET datasets and data readers.

? Using the DataSourceID property – Binding to data source using the DataSourceID property is a two way binding procedure. Automatically, it supports displaying data as well as insert, update, and delete operations on the bound data.

Working with the DetailsView Control Data

? The data source control is responsible for handling tasks of data store connections and retrieval of the selected data from the data store. Hence, DetailsView control can be easily connected to the data by setting the DataSourceID property either declaratively or in code.

? If you want to perform editing of data by means of the DetailsView control, you must set the AutoGenerateEditButton property to True. As a result, an additional ‘Edit’ button, the control will enter into the edit mode. In the edit mode, the CurrentMode property of the DetailsView control changes from Readonly to Edit, and each field will be rendered as an editable UI, such as text box or check box. To modify the appearance of this editable UI, you can use different style properties, DataControlField objects or even templates.

? The DetailsView control can be configured to display the ‘Delete’ and ‘Insert’ buttons as well. You delete the data from the data source by clicking the ‘Delete’ button. Similarly, you can use the ‘Insert’ button to add any new data to the data source. When you set the AutoGenerateInsertButton property to True, an additional button gets added to the DetailsView control. A ‘New’ button gets displayed on the control and the CurrentMode property of the DetailsView control changes from Readonly to Insert.

Customizing the DetailsView Control User Interface

? Like GridView control, you can use style properties to customize the UI of the DetailsView control. These style properties are HeaderStyle, RowStyle, AlternatingRowStyle, CommonRowStyle, FooterStyle, PagerStyle, and EmptyDataRowStyle.

? You can also perform additional customization by using templates. Templates, as you know, provide more flexibility to modify the appearance of elements. A template can be created for each individual field by adding the TemplateField object to the Fields collection. You can also define customized EmptyDataTemplate, HeaderTemplate, FooterTemplate, and PagerTemplate properties for the DetailsView control.

? The DetailsView control contains a Fields collection property. Fields property contains DataControlField objects of type BoundField, CommandField, or HyperLinkField. The DetailsView control’s Column collection is much similar in function to this, except for that in DetailsView, each field is rendered as a row and not as a column.

? The DetailsView exposes various events that can be handled to execute your code. The events are often raised before and after the operations, like insert, update, and delete.

Example (DetailsView Control)

Here is an example of DetailsView control. First create a web form and drag and drop DetailsView control from Data Toolbox to web form.

Select “Choose Data Source” option from GridView smart tag. From “Choose Data Source”option select option.

A new Data Source Configuration Wizard will be open. Select database as data source.

Select you Server Name and Select your database name and click on test connection to make sure that connection was established or not. After making a successful connection, click on OK button.

Here you can see your connection string.

Click on ‘Next’ button.

Now, select ‘specify a custom SQL statement or stored procedure’ radio button and click on ‘Next’ button. A new ‘Define Customs Statements or Stored procedures’ screen will be open.

Select you query operation type, and click on ‘Query Builder’ button. A new ‘Add Table’ screen will be open.

Select your table name and click on Add button to add this table into your query builder.

Select your table fields and click on OK button. You can see your SQL statement in your query builder. Click on next button.

To test your query result, click on ‘Test Query’ button. Click on ‘Finish’ button to close this wizard. Now, you can see your FormView control like this:

Now, run this application and see the result like this:

Data Source, Data Control Parameters & Different views

Binding Standard Web Server Control

You can use a number of common Web server controls bound to a data source. You can easily bind ListBox, DropDownList control, and CheckBoxList control in a few simple steps. A data access control can do all the binding work for you. Place any compatible DataAccess control and configure it with the underlying data source. Then, all you need to do is to specify the values for the following two properties:

? DataTextField – It specifies the name of the bound column in the database.
? DataValueField – It specifies the column name of the primary key column in the database.

Data Source, Data Control Parameters & Different views

Controls Designed for use with Databases

There are some Web server controls that are specifically designed to be used with data sources. We have already seen one of them at work – DataGridView. The other two are data lists and repeaters. Here’s an overview of these controls:

? DataList – This control creates a (non-tabular) list display that can be customized. Data lists have an auto-format option and supports single selection of items. You can edit the contents if you display text boxes in the list.

? Repeater – This control creates simple, read-only output. In fact, repeaters only let you iterate over the records they are bound to; they have no default appearance at all. You are responsible for adding any HTML you want to use to display data.

ASP.NET 2.0 has introduced some more functionality rich controls which can be used with much ease for binding databases or some other data stores. They are as follows:

• DetailsView control
• FormsView control
• GridView control

Data Source, Data Control Parameters & Different views

Using Data Lists in Web Applications

You use a data list to display a data-bound list of items. The formatting of each item is handled with templates.

The given example displays the ContactID, FirstName, LastName and EmailAddress of all the contactsin the contact table in a data list, formatted with a simple border around the list. Each row in the list is created with a template, which in this case, is a Label control bound to the FirstName of contact, followed by a space and a second Label control bound to the LastName of the contact. This is very simple template; you can add all kinds of HTML to customize template as you like.

To follow along, create a Web application named WebDataListApplication, and add a DataList control available under the ‘Data’ tab in the Toolbox. How do you actually bind the controls in a template to a data source? You start by binding the data list to a data table. To do that, click the small arrow at the top right corner. This will display the ‘DataList Tasks’ menu.

From the menu, click the drop-down list for the ‘Choose DataSource’ option. From the list that appears, select the ‘ ’ option. As you click this option, a new Data Source Configuration Wizard opens. Select the data source type from the available list. For example, select ‘Database’ option and it will automatically add the ‘Data source ID’ to the textbox displayed below in the DataSource Configuration Wizard. Finally click OK. This will add SqlDataSource to the Web form.

The next screen will now be Add Connection screen. Provide the server and the database name you want to use, for example we are using AdventureWorks database. When the configure Data Source wizard appears, click ‘Next’ for the ‘Choose your Data Connection’ screen. In the ‘Save Connection’ screen which appears next, specify the name for the connection string if you don’t want to use the default option. The next screen will be the ‘Configure the Select Statement’ screen. Select the fields you want to display on the DataList control.

Click ‘Finish’ to complete the wizard. Next, to create a template to display a data item, click the arrow at the top right corner to display the ‘Data List Tasks Menu’ and click the ‘Edit Templates’ option. This will display the Edit Template mode. Choose ‘Item Template’ from the Display list. This display the templates used to display data items. You can see four labels bound to the Contact ID(‘ContactID’), First Name(‘FirstName’), LastName(‘LastName’), Email Address(‘EmailAddress’).

Example (DetailsView Control)

Here is an example of Datalist control. First create a web form and drag and drop Datalist control from Data Toolbox to web form.

Select “Choose Data Source” option from GridView smart tag. From “Choose Data Source”option select New Data Source option.. .

A new Data Source Configuration Wizard will be open. Select database as data source.

Select you Server Name and Select your database name and click on test connection to make sure that connection was established or not. After making a successful connection, click on OK button.

Here you can see your connection string.

Click on ‘Next’ button.

Now, select ‘specify a custom SQL statement or stored procedure’ radio button and click on ‘Next’ button. A new ‘Define Customs Statements or Stored procedures’ screen will be open.

Select you query operation type, and click on ‘Query Builder’ button. A new ‘Add Table’ screen will be open.

Select your table name and click on Add button to add this table into your query builder.

Select your table fields and click on OK button. You can see your SQL statement in your query builder. Click on next button.

To test your query result, click on ‘Test Query’ button. Click on ‘Finish’ button to close this wizard.

Now, you can see your FormView control like this:

Now, run this application and see the result like this:

Data Source, Data Control Parameters & Different views

Using Repeaters in Web Applications

Unlike data grids and data lists, repeaters have no intrinsic appearance at all; it’s up to you to create that appearance, which is good if you want to write the HTML for a custom data display from scratch. A repeater is a template-driven control, but here, you must edit the template directly in HTML. In other words, you use a repeater when you want to fill your ownHTML with data from a dataset or data view.

As with Windows forms, you can use data readers to access data, not just datasets. TheADO.NET DataReader can be used to retrieve a read-only and forward-only stream of data from a database.

This can increase application performance – both by retrieving data as soon as it is available, and (by default) storing only one row at a time in memory, thus reducing system overhead. As the query executes, the results are returned, and stored in the network buffer on the client, until you request them using the Read method of the DataReader.

Data Source, Data Control Parameters & Different views

Working with FormView

The FormView control is used to display on the Web form only a single record retrieved from a data source in a table. We often use templates to display and edit the bound values in a FormView. As you already know, the templates consist of formatting, controls, and binding expressions to create the form. You can create a master/details scenario by using the combination of FormView control with a GridView control.

In the case when data source provides multiple records to the FormView control, the control allows you to page through the individual records. FormView control supports basic functions like editing, inserting, and deleting data records. You can perform the following tasks, while using FormView:

• Displaying master/detail information
• Creating a free-form layout for a data record
• Using FormView control for editing

Note: The FormView control and GridView control have similar event model except that the FormView control doesn’t support the selection event. In FormView control, the current record is always the selected item.

To enable the FormView control to provide the navigation capability between the data records, you must set the AllowPaging property to True and also provide a PagerTemplate value. The FormView class displays the values of t single record from a data source using user-defined templates. It also allows you to edit, delete, and insert records.

Data Binding with the FormView Control

The options available for the binding data in the FormView control are as follows:

• Data binding in FormView control can be achieved by setting the DataSourceID property or in other words, by using the data source control. This is the efficient and recommended option as it provides the predefined functionality, like updating and paging and enables FormView to take advantage of the capabilities of data source control. This approach supports two-way binding.

• Data binding in FormView control can also be achieved by using the DataSource property. It makes binding possible with almost any object like ADO.NET datasets and readers. However, this approach requires writing codes for each and every additional functionality, such as updating and paging.

Creating the FormView control User Interface

You can create templates to build the user interface (UI) for the FormView control. There are different templates to be used with different actions. For example, to display, insert and edit the data records, you can use ItemTemplate. Pager template can be used to control the paging. FormView’s header and footer can be customized by using the Header template and Footer template, respectively.

However, if there’s no data available for display, or in other words when the data source control doesn’t return any data record, you can use the EmptyDataTemplate. To control the content to be displayed on the FormView control, you will use the Item template.

In order to customize the appearance of the FormView control, you can also use the different style properties, such as EditRowStyle, EmptyDataRowStyle, FooterStyle, HeaderStyle, InsertRowStyle, PagerStyle and RowStyle properties.

Example (DetailsView Control)

Here is an example of FormView control. First create a web form and drag and drop Datalist control from Data Toolbox to web form.

Select “Choose Data Source” option from GridView smart tag. From “Choose Data Source”option select New Data Source option.. .

A new Data Source Configuration Wizard will be open. Select database as data source.

Select you Server Name and Select your database name and click on test connection to make sure that connection was established or not. After making a successful connection, click on OK button.

Here you can see your connection string.

Click on ‘Next’ button.

Now, select ‘specify a custom SQL statement or stored procedure’ radio button and click on ‘Next’ button. A new ‘Define Customs Statements or Stored procedures’ screen will be open.

Select you query operation type, and click on ‘Query Builder’ button. A new ‘Add Table’ screen will be open.

Select your table name and click on Add button to add this table into your query builder.

Select your table fields and click on OK button.

You can see your SQL statement in your query builder.

Click on next button.

To test your query result, click on ‘Test Query’ button.

Click on ‘Finish’ button to close this wizard. Now, you can see your FormView control like this:

Now, run this application and see the result like this:

New Features of ADO.NET 2.0

Salient features of ADO.NET 2.0

Enhancements to the DataSet and the DataTable classes

In ADO.NET 2.0, the DataTable supports two methods Load and Save. Note that the DataSet class supports both these methods from the earlier version of ADO.NET. While the Load method can load data from XML into the DataTable instance, the Save method can persist the DataTable instance to a persistence storage media. Further, unlike in ADO.NET1.1, the DataTable class now supports serialization.

Moreover, the DataTable class in ADO.NET 2.0 contains a method called CreateTableReader that returns a DataTableReader instance that can be used not only to read forward only data but also in a disconnected mode of operation.

Further, the DataTable class in ADO.NET 2.0 is serializable, unlike in ADO.NET 1.1 where one had to store the DataTable instance inside a DataSet instance to make it serializable.

Optimized DataSet Serialization

The DataSet object in ADO.NET is an in-memory representation of disconnected, cached set of data and provides a consistent relational programming model regardless of the data source. When we require sending a DataSet class instance across process boundaries, the DataSet needs to be serialized.

But, what is Serialization? Serialization is the process of converting an in-memory object into a serial stream of bytes. Serialization and De-Serialization is mostly used to transport objects or to persist the state of the objects to a persistent storage media (e.g. to a file or database).

Unfortunately, the DataSet class in the earlier version of ADO.NET used to serialize data as XML even if the BinarySerializer was specified. This resulted in slower serialization and an overhead of large sized serialized data.

In ADO.NET 2.0 however, DataSet serialization has been improved to a large extent and you can now use the RemotingFormat property of the DataSet class to specify that the data is to be stored directly in binary format. Hence, the DataSet class ADO.NET 2.0, unlikeADO.NET 1.1, supports both XML and Binary Serialization formats. This is shown in the code example below.

BinaryFormatter binaryFormatter = new BinaryFormatter();

FileStream fileStream = new FileStream("c:\\emp.dat", FileMode.CreateNew);

DataSet empDataSet = GetEmployeeDataSet(); 
//This is a custom method that creates, populates 
and then returns a //DataSet instance. 

empDataSet.RemotingFormat = SerializationFormat.Binary; 

//Serialize the employee Data Set instance as binary. In order to
 //serialize the same instance as XML, specify SerializationFormat.XML

binaryFormatter.Serialize(fileStream,empDataSet);

fileStream.Close();

Conversion of a DataReader to DataSet or DataTable and vice-versa

ADO.NET 2.0 allows loading a DataReader object into a DataSet or a DataTable and vice versa. Both the DataSet and the DataTable classes in ADO.NET 2.0 contain the Load method that can be used to load a DataReader instance into a DataSet or a DataTable.

The following piece of code shows how a DataTable can be loaded in a DataReader instance.

string connectionString = ....; //Some connection string

SqlConnection sqlConnection = new SqlConnection(connectionString); 

sqlConnection.Open(); 

SqlCommand sqlCommand = new SqlCommand

("Select * from Employee", sqlConnection); 

SqlDataReader sqlDataReader = sqlCommand.ExecuteReader
(CommandBehavior.CloseConnection);

DataTable dataTable = new DataTable("Employee"); 

dataTable.Load(sqlDataReader);

The GetDataReader method of both the DataSet and the DataTable classes can be used to retrieve a DataReader instance from either a DataSet or a DataTable. If ithe DataSet instance on which the method is called contains multiple DataTable instances, the resultant DataReader would also contain multiple resultsets.

Data Paging

Data Paging is a very powerful feature in ADO.NET. It can be recollected that in the earlier version of ADO.NET we needed to make use of stored procedures for incorporating Data Paging functionality in our applications. Now, with ADO.NET 2.0, it is much simplified with the introduction of the ExecutePageReader method in the SqlDataReader class. The following code snippet illustrates how this feature can be achieved.

string connectionString = ....; //some connection string

SqlConnection sqlConnection = new SqlConnection(connectionString); 

sqlConnection.Open(); 

SqlCommand sqlCommand = new SqlCommand

("Select * from Employee", sqlConnection); 

SqlDataReader sqlDataReader = sqlCommand.ExecutePageReader
(CommandBehavior.CloseConnection, 1, 25);

Batch Updates — Reducing Database Roundtrips

Database round trips degrade the performance of applications to a large extent due to increased network traffic. The Batch update of ADO.NET 2.0 promises to improve the performance of applications to a large extent by reducing the number of round trips to the database.

In the earlier version of ADO.NET, if we made any changes to the DataSet and then saved the DataSet using the Update method of the DataAdapter class, it made round trips to the database for each modified row in the DataSet. This was a major performance hindrance.

But, how does it know which row or rows have been changed? When we make changes to a DataRow, its RowState changes to reflect the change. Now, for every row that has been changed (depending on whether the RowState property has been changed or not), the DataAdapter communicates with the database in such a type of operation.

This is a terrible performance drawback with large volumes of data. In ADO.NET 2.0however, there is a property known as UpdateBatchSize that can be used to specify a group or batch of rows for a particular hit to the database. It provides the number of rows to be updated in a batch. In other words, the UpdateBatchSize property of the DataAdapter class actually determines the number of changed rows changed rows that send to the database server in a single operation.

Asynchronous Data Access

In the earlier version of ADO.NET, the ExecuteReader, ExecuteScalar and the ExecuteNonQuery methods used to block the current executing thread. However, ADO.NET 2.0 supports asynchronous data access mode.In ADO.NET 2.0, these methods come with Begin and End methods that support asynchronous execution.

The Common Provider Model

In the earlier version of ADO.NET, if we wanted to implement a provider independent Data Access Layer, we had to implement the Factory Design Pattern where a class would have been responsible for returning the specific type of Command,Data Reader, DataAdapter or Connection.

In ADO.NET 2.0 we can create provider-independent data access code even without referencing the provider-specific classes using the System.Data.Common namespace that exposes a number of factory classes.

The DbProviderFactory class contains two methods called the GetFactoryClasses method and the Getfactory method. While the former is responsible for retrieving all the providers supported, the later can be used to retrieve the specific provider. Refer to the code snippet below that demonstrates how we can make use of this class to create a sql connection seamlessly.

DbProviderFactory dbProviderFactory =
DbProviderFactories.GetFactory("System.Data.SqlClient"); DbConnection dbConnection = dbProviderFactory.CreateConnection();

Bulk Copy Feature

In the earlier version of ADO.NET, copying a large volume of data from a source data store to a destination table in SQL database had performance drawbacks due to the repeated database accesses that were required.

The SqlBulkCopy feature in ADO.NET 2.0 enables us to copy a large volume of data between a source data store and a destination data table. This class can be used to specify the source and the target data sources for this copy operation. The following code snippet illustrates how this feature can be implemented.

SqlConnection connectionObjSource = new SqlConnection(Conn_str);
connectionObjSource.Open();
SqlConnection connectionObjTarget = new SqlConnection(Conn_str1);
connectionObjTarget.Open();
SqlCommand sqlCommand = new SqlCommand
("Select * from Employee", connectionObjSource);
SqlDataReader sqlDataReader = 
sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
DataTable dt = new DataTable("Employee");
SqlBulkCopy sqlBulkcopy = new SqlBulkCopy(connectionObjTarget);
bulkcopy.DestinationTableName = "Employees";
bulkcopy.WriteToServer(sqlDataReader);

Multiple Active Result Set (MARS)

MARS is a new feature in ado.net 2.0 and Sql Server 2005 that allows for multiple forward only read only result sets.

MARS allows you to avoid seeing the dreaded "There is already an open DataReader." exception when executing on separate SqlCommands associated with the same connection. You can have multiple SqlDataReaders open on a single connection (again, each reader must be started on a new SqlCommand) and you don't have to worry about Transaction isolation level scope locks.

Advantages of MARS

MARS provides the following advantages:

• It provides a lighter weight alternative for applications that use multiple connections to overcome lack-of-MARS limitations. However, this is not always the case because multiple connections do provide parallel execution in the server (provided they're not enlisted in the same transaction).

• It enables multiple SqlDataReaders. Prior to MARS, only one command or resultset could be active at one time on a connection. This required the use of two or more connections to execute multiple queries. As the code example showed, MARS allows you to execute multiple queries against a single connection. However, note that MARS does not enable parallel execution of queries; it enables only sequential execution of multiple queries.

PC TRAINING

ADO.NET - PART IV

0 comments:

CATEGORIES

POPULAR POSTS

BLOG ARCHIVE

ABOUT ME

Categories

About

Link list 2

Follow us

Link list 3