Data Binding in ASP.NET

Overview

Data Binding is an ASP.NET feature that allows you to pop data directly into HTML elements and fully formatted controls.

These notes present a thorough overview of Data Binding in ASP.NET. First, you'll learn an interesting way to create dynamic text with simple data binding. You'll also learn how ASP.NET builds on this infrastructure with much more useful features, including repeated-value binding for quick-and-easy data display in a list control, and data source controls, which let you create code-free bound pages.

Contents

• Quick Reference???
• Examples ??
• Key Tips

Tutorial

• Single-Value or "Simple" Data Binding
• Repeated-Value or "List" Data Binding
• Data-Bound Web Server Controls ??
• Data Source Controls
• Updating Records using Rich Data Controls
• Strict Concurrency Checking
• Understanding the Page Life Cycle with Data Binding

Quick Reference

• Single-Value Data Binding (simple): Allows you to take a Page variable, property, or an expression and insert it dynamically into a page. In other words, it allows you to pop data into HTML elements. To implement single-value Data Binding, use a Binding expression like <%# Var_Prop_Expr %> within your .aspx file. Then, convert all the data binding expressions on the Page via the DataBind() method of the Page object.
• Repeated-Value Data Binding (list): Bind data to List controls (e.g. ListBox)? or Rich Data controls (e.g. GridView), which are all controls with a DataSource property.?To use Repeated-Value Data Binding, create / fill / retrieve your data object (i.e. SqlDataReader or DataSet) and then assign it as your control's DataSource (often done in the Page.Load event or in the aspx file). When you call DataBind(), the control automatically creates a full list using all the corresponding values.?
• Data Source Controls: Using the ASP.NET Data Source controls, you can retrieve data from a database without writing a line of ADO.NET code. Data Source controls work particularly well with List and Rich Data controls. One example is SqlDataSource, for which command logic is supplied through four properties — SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand — each of which takes a string. Note that with the SqlDataSource that you can still use parameterized commands (e.g. ControlParameter, QueryStringParameter, etc.). Also note that many Rich Data controls have a DataKeyNames and Auto Generate Insert/Delete/Edit Button properties.?
• Concurrency Checking: Concurrency checking is recommended when developing systems that have frequent data updates and many users. The problem with updating a record based on its ID is that it opens the door to an update that overwrites the changes of another user, if these changes are made between the time your page is requested and the time your page commits its update. One way to solve this problem is to use an approach called match-all-values concurrency, but a better approach is to use a Timestamp field.?

Examples

1. Simple Data Binding Example - SimpleDataBinding.aspx?
2. Simple Data Binding with Control Properties - DataBindingUrl.aspx?
3. A Simple List Binding Example - ListDataBinding.aspx???
4. Data Binding with a Dictionary - DictionaryCollection.aspx
5. Data Binding with a DataSet - DataSetBinding.aspx ??
6. Creating a Record Editor (Label to display + ListBox to select + Button to update) - RecordEditor.aspx
7. SqlDataSource with Parameters (ControlParameter) for use in SelectCommand & UpdateCommand - RecordEditorDataSource.aspx ??
8. SqlDataSource with Error Handling that includes setting Parameter value set in code-Behind (SqlDataSource Selecting event) and displays a read-Only GridView - CustomParameters.aspx ??
9. Updating, Deleting, and Inserting records using a DetailsView and corresponding SqlDataSource Commands - UpdateDeleteInsert.aspx ??
10. Concurrency Checking - SqlDataSource with match-all-values concurrency (w/ OldValuesParameterFormatString, ConflictDetection, and OnUpdated) + DetailsView (w/ AutoGenerateEditButton) - RecordEditorDataSource_MatchAll.aspx
11. Bonus - The Repeater Control

1) Simple Data Binding Example - SimpleDataBinding.aspx?

Convert all the data binding expressions on the page via DataBind() method on of?different controls objects (after setting DataSource) or the Page object. You can assign Page class variable values in your code-behind and then call this.DataBind() in Page_Load().

This example uses Simple Data Binding to bind a page class variable and an expression within the aspx via Data Binding Expression (i.e. <%# Var_Prop_Expr %> ).?

Note: Another use of where you would also use a Data Binding Expression is with the Repeater control (Repeated-Value Data Binding).

ASPX:

<!-- We could have used %= instead of %#, and if so then we would not have to call DataBind() -->
There were <%# TransactionCount %> transactions today. <br /><br />
I see that you are using <%# Request.Browser.Browser %>.?        

Code-Behind:

public partial class SimpleDataBinding : System.Web.UI.Page
{
	protected int TransactionCount;

	protected void Page_Load(object sender, EventArgs e)
	{
		// You could use database code here to look up a value for TransactionCount.
		TransactionCount = 10;

		// Now convert all the data binding expressions on the page.
		this.DataBind();
	}
}        

2) Simple Data Binding with Control Properties - DataBindingUrl.aspx?

This example demonstrates Simple Data Binding with Page Properties and using this technique to set control properties (i.e. HyperLink.NavigateUrl or Image.ImageUrl).?

We'll create and set Page class variable, then call this.DataBind(). The variable value (a URL) then gets used to set a property within a Label, CheckBox, Hyperlink, and Image control.

ASPX:

<asp:Label id="lblDynamic" runat="server"><%# URL %></asp:Label><br /><br />
<asp:CheckBox id="chkDynamic" Text="<%# URL %>" runat="server" /><br /><br />
<asp:Hyperlink id="lnkDynamic" Text="Click here!" NavigateUrl="<%# URL %>" runat="server" /><br /><br />
<asp:Image id="imgDynamic" ImageUrl="<%# URL %>" runat="server" />        

Code-Behind:

public partial class DataBindingUrl : System.Web.UI.Page
{
	protected string URL;

	protected void Page_Load(Object sender, EventArgs e)
	{
		URL = "Images/picture.jpg";
		this.DataBind();
	}
}        

3) A Simple List Binding Example - ListDataBinding.aspx?

In this example, we'll populate a List<string> collection, then set it as the DataSource for the List controls.?Then DataBind the page.

Note: DataTextField & DataValueField are key properties of List controls.?

ASPX:

<h3>ListBox</h3>
<asp:ListBox id="MyListBox" runat="server"></asp:ListBox><br /><br />


<h3>HtmlSelect</h3>
<select id="MyHTMLSelect"? size="1"? runat="server"/><br /><br />


<h3>DropDownList</h3>
<asp:DropDownList id="MyDropDownListBox" runat="server"></asp:DropDownList><br /><br />


<h3>CheckBoxList</h3>
<asp:CheckBoxList id="MyCheckBoxList"? runat="server"></asp:CheckBoxList><br /><br />


<h3>RadioButtonList</h3>
<asp:RadioButtonList id="MyRadioButtonList" runat="server"></asp:RadioButtonList>        

Code-Behind:

protected void Page_Load(object sender, EventArgs e)
{
	// Create and fill the collection.
	List<string> fruit = new List<string>();
	fruit.Add("Strawberry");
	fruit.Add("Banana");
	fruit.Add("Blueberry");
	fruit.Add("Peach");
	fruit.Add("Pear");

	// Define the binding for the list controls.
	// Note: List is straightforward.? If you were using a DataTable or DataSet,?
	// you would have to specify additional information (i.e. .DataSource = ds.Tables[0]), as seen in:?
	// https://aspsnippets.com/Articles/Bind-Populate-ASPNet-DropDownList-using-DataTable-DataSet-in-C-and-VBNet.aspx	
	MyListBox.DataSource = fruit;
	MyHTMLSelect.DataSource = fruit;
	MyDropDownListBox.DataSource = fruit;? ? ? ??
	MyCheckBoxList.DataSource = fruit;
	MyRadioButtonList.DataSource = fruit;

	// Activate the binding.
	this.DataBind();
}        

Note: In the example above, the collection is created manually just before it's displayed.?In a more realistic application, however, you might be using a function that returns a ready-made collection to you.?

List<string> fruit = GetFruitsInSeasons("Summer");
lstItems.DataSource = fruit;
this.DataBind();        

4) Data Binding with a Dictionary - DictionaryCollection.aspx

In this example, we'll create a Dictionary collection with an integer key and string value (Dictionary<int, string> fruit), then Bind it to a ListBox (DataTextField & DataValueField).

We'll utiize the ListControl properties DataTextField & DataValueField, which we'll set to "Value" and "Key" of the Dictionary object respectively. Note, before doing so, we'll set ListControl DataSource property to our Dictionary object.

Note: Each item in a Dictionary-style collection has both a key and a value associated with it. If you don’t specify which property you want to display, ASP.NET simply calls the ToString() method on each collection item (we assign DataTextField & DataValueField otherwise the result is the key and the value in square brackets).?

ASPX:

<asp:ListBox ID="MyListBox" runat="server" AutoPostBack="True" OnSelectedIndexChanged ="MyListBox_SelectedIndexChanged" ></asp:ListBox>
<asp:Label ID="lblMessage" runat="server" Font-Bold="True"></asp:Label>        

Code-Behind:

protected void Page_Load(object sender, EventArgs e)
{
	// The technique in this example is particularly useful with a database.You could embed a unique ID into the value property?
	// and be able to quickly look up a corresponding record depending on the user’s selection by examining the value of the SelectedItem object.?
	// Note that for this to work, you can’t regenerate the list after every postback.If you do, the selected item information will be lost and an error will occur.
	if (!this.IsPostBack)
	{
		Dictionary<int, string> fruit = new Dictionary<int, string>();? ?
		fruit.Add(1, "Kiwi");
		fruit.Add(2, "Pear");
		fruit.Add(3, "Mango");
		fruit.Add(4, "Blueberry");

		// Define the binding for the list controls.
		MyListBox.DataSource = fruit;

		// When DataBinding to a Dictionary collection, you might use the DataTextField & DataValueField of your List Control,?
		// and set them to the "Value" and "Key" of the Dictionary object respectively:

		// Choose what field to display in the list
		MyListBox.DataTextField = "Value";? ??
		
		// Sets value attribute of option tag
		MyListBox.DataValueField = "Key";? ??
		
		// Activate the binding.
		this.DataBind();
	}
}

protected void MyListBox_SelectedIndexChanged(object sender, EventArgs e)
{
	lblMessage.Text = "You picked: " + MyListBox.SelectedItem.Text;
	lblMessage.Text += " which has the key: " + MyListBox.SelectedItem.Value;?
}        

5) Data Binding with a DataSet - DataSetBinding.aspx ?

This example creates a DataSet (create DataSet & DataTable, add DataColumn objects, add DataRows) by hand and Binds it to a list.

We'll set DataSource of the ListBox to the DataTable within the Dataset, and set the DataTextField and DataValueFields to Columns of that DataTable.

ASPX:

<asp:ListBox ID="lstUser" runat="server" OnSelectedIndexChanged ="lstUser_SelectedIndexChanged" AutoPostBack="True"></asp:ListBox>
<br />
<asp:Label ID="lblMessage" runat="server" Text="Select User"></asp:Label>        

Code-Behind:

protected void Page_Load(object sender, EventArgs e)
{
	if(!this.IsPostBack) // needed, otherwise you won't be able to capture which ListBox item gets selected
	{
		// Define a DataSet with a single DataTable.

		// 1 - Create DataSet
		DataSet dsInternal = new DataSet();

		// 2 - Create DataTable
		dsInternal.Tables.Add("Users");

		// 3 - Define structure by adding DataColumn objects
		dsInternal.Tables["Users"].Columns.Add("Name");? ? ? ? ?
		dsInternal.Tables["Users"].Columns.Add("Country");

		// Add some actual information into the table.


		// 4 - Create DataRow objects
		// - NewRow() creates a new blank row that has the same structure as your DataTable
		// - Set the data in all fields
		// - Add to the DatatTable.Rows collection

		DataRow rowNew = dsInternal.Tables["Users"].NewRow();? ?
		rowNew["Name"] = "John";? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
		rowNew["Country"] = "Uganda";
		dsInternal.Tables["Users"].Rows.Add(rowNew);? ? ? ? ? ??

		rowNew = dsInternal.Tables["Users"].NewRow();
		rowNew["Name"] = "Samantha";
		rowNew["Country"] = "Belgium";
		dsInternal.Tables["Users"].Rows.Add(rowNew);

		rowNew = dsInternal.Tables["Users"].NewRow();
		rowNew["Name"] = "Rico";
		rowNew["Country"] = "Japan";
		dsInternal.Tables["Users"].Rows.Add(rowNew);

		// 5 - Bind the DataTable from the DataSet to the control
		// Define the binding. (method #1)
		lstUser.DataSource = dsInternal.Tables["Users"];? ? ? ?
		lstUser.DataTextField = "Name"; // DataTable has multiple fields, List control can only display one, so set DataText field
		lstUser.DataValueField = "Country";

		// Alternatively, you could use the entire DataSet for the data source, instead of just the appropriate table.
		// In that case, you would have to select a table by setting the control's DataMember Property.

		// Define the binding. (method #2)
		// lstUser.DataSource = dsInternal;?
		// lstUser.DataMember = "Users";

		// 6 - Activate binding?
		this.DataBind(); // alternative: lstItems.DataBind() to bind just the list box.? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
	}	
}	

protected void lstUser_SelectedIndexChanged(object sender, EventArgs e)
{
	lblMessage.Text = "You picked: " + lstUser.SelectedItem.Text;
	lblMessage.Text += " which has the key: " + lstUser.SelectedItem.Value;? ?// *
}        

6) Creating a Record Editor (Label to display + ListBox to select + Button to update) - RecordEditor.aspx

This example is a Record Editor that includes a DropDownList of Products, a?Label to display the selected Product's Details, and a ListBox to update the Product's Category property. It combines knowledge of SqlClient (Update button) & Data Binding (Data-Bound List controls) and is a practical example of how you might use Data Binding in a full ASP.NET application.?

The SqlCommand (within the lstProduct_SelectedIndexChanged event handler method) used to retrieve the Product details uses Parameters and a SQL Join query (to get Categories.CategoryName).??

Flow of application:

1. Populate the Products DropDown (in Page_Load) by Data Binding with SqlDataReader object.?Set the List control's DataTextField and DataValueField properties to the appropriate column names.?
2. Display Product Details when a Product gets selected (SelectIndexChanged).?Our SqlCommand object will use an SQL command that JOINs the Products and Categories tables.?
3. Allow the Product Category to be updated via a ListBox control. Handle Update button click event (UPDATE SQL Statement rather than SELECT).?Make your code more secure by using SqlCommand Parameters.?

Note: The Product list population in Page_Load does not need to use Parameters because it does not use User input.?

Note: This example uses ADO.NET's SqlClient namespace and Data Binding, but opts not to use use Data Controls (e.g. SqlDataSource) to populate the List controls (see #7).

ASPX:

<!-- Note the use of DropDownList AutoPostBack property -->?
<asp:DropDownList ID="lstProduct" runat="server" AutoPostBack="True" OnSelectedIndexChanged ="lstProduct_SelectedIndexChanged">
</asp:DropDownList><br /><br />

<asp:Panel ID="pnlDetails" runat="server" Visible="False">
	<table>
		<tr>
			<td valign="top">
				<asp:Label ID="lblRecordInfo" runat="server"></asp:Label>
			</td>
			<td>
				<asp:ListBox ID="lstCategory" runat="server"></asp:ListBox>
				<br /><br />
				<asp:Button ID="cmdUpdate" runat="server" Text="Update Category" OnClick ="cmdUpdate_Click" />
			</td>
		</tr>
	</table>
</asp:Panel>	        

Code-Behind:

public partial class RecordEditor : System.Web.UI.Page
{
	private string connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString; 
 
	protected void Page_Load(object sender, EventArgs e)
	{
		// The list is only filled the first time the page is requested and stored in view state automatically
		if (!this.IsPostBack)?
		{
			// Populate a DropDownList using an SqlDataReader
			// Note: In a real application, you'd need to use exception handling

			// Define the ADO.NET objects for selecting Products.
			string selectSQL = "SELECT ProductName, ProductID FROM Products"; // only retrieve the data we'll use
			SqlConnection con = new SqlConnection(connectionString);
			SqlCommand cmd = new SqlCommand(selectSQL, con);

			// Open the connection.
			con.Open();

			// Define the binding.
			lstProduct.DataSource = cmd.ExecuteReader(); // notice no explicit SqlDataReader object?
			lstProduct.DataTextField = "ProductName";
			lstProduct.DataValueField = "ProductID";

			// Activate the binding.
			lstProduct.DataBind(); // calls DataBind on the controls, not the page

			// Close the connection.
			con.Close();
			
			// Note: Alternatively refer to Example #8 which shows? 
			//an alterative way to do this (via DropDownList.AppendDataBoundItems)
			lstProduct.Items.Insert(0, "---- Select Product -----");?

			// Make sure nothing is currently selected.
			lstProduct.SelectedIndex = -1;
		}
	}

	protected void lstProduct_SelectedIndexChanged(object sender, EventArgs e)
	{
		if (lstProduct.SelectedIndex == 0) // "---- Select Product -----"
			return;


		/* Retrieve the Product Record */

		// Create a command for selecting the matching product record.
		// Note: Usage of Join query
		string selectProduct = "SELECT ProductName, QuantityPerUnit, " +
		 "CategoryName FROM Products INNER JOIN Categories ON " +? ?
		 "Categories.CategoryID=Products.CategoryID " +
		 "WHERE ProductID=@ProductID";
		
		// Create the Connection and Command objects.
		SqlConnection con = new SqlConnection(connectionString);
		SqlCommand cmdProducts = new SqlCommand(selectProduct, con);
		cmdProducts.Parameters.AddWithValue("@ProductID", lstProduct.SelectedItem.Value);

		// Retrieve the information for the selected product.
		using (con)
		{
			con.Open();
			SqlDataReader reader = cmdProducts.ExecuteReader();
			reader.Read();

			// Update the display.
			lblRecordInfo.Text = "<b>Product:</b> " + reader["ProductName"] + "<br />";
			lblRecordInfo.Text += "<b>Quantity:</b> " + reader["QuantityPerUnit"] + "<br />";
			lblRecordInfo.Text += "<b>Category:</b> " + reader["CategoryName"];

			// Store the corresponding CategoryName for future reference.
			string matchCategory = reader["CategoryName"].ToString();

			// Close the reader.
			reader.Close();

			/* Retrieve all the Categories, then Select the Product's Category */

			// Note: We could move this code to be in Page_Load, but would need to add?
			// code to close SqlDataReader and wanted to show that we didn't use an?
			// explicit SqlDataReader object (also also seen below)

			// Create a new Command for selecting categories.
			string selectCategory = "SELECT CategoryName, CategoryID FROM Categories";
			SqlCommand cmdCategories = new SqlCommand(selectCategory, con);

			// Retrieve the category information, and bind it.
			lstCategory.DataSource = cmdCategories.ExecuteReader();
			lstCategory.DataTextField = "CategoryName";
			lstCategory.DataValueField = "CategoryID";
			lstCategory.DataBind();

			// Highlight the matching category in the list.
			lstCategory.Items.FindByText(matchCategory).Selected = true;? ?
		}
		
		pnlDetails.Visible = true;
	}

	protected void cmdUpdate_Click(object sender, EventArgs e)
	{
		// Define the Command.
		string updateCommand = "UPDATE Products " +
		 "SET CategoryID=@CategoryID WHERE ProductID=@ProductID";

		SqlConnection con = new SqlConnection(connectionString);
		SqlCommand cmd = new SqlCommand(updateCommand, con);

		cmd.Parameters.AddWithValue("@CategoryID", lstCategory.SelectedItem.Value);
		cmd.Parameters.AddWithValue("@ProductID", lstProduct.SelectedItem.Value);

		// Perform the update.
		using (con)
		{
			con.Open();
			cmd.ExecuteNonQuery();
		}
	}
}        

Tip: This example doesn't attempt to deal with errors.?In a real application, you'd need to use the exception handling approach.?

Note: The query used in this example to display Product details uses a database table join in order to include Category Name (Product table only has CategoryID)

SELECT ProductName, QuantityPerUnit, CategoryName?
FROM Products INNER JOIN Categories?
ON Categories.CategoryID=Products.CategoryID
WHERE ProductID=@ProductID;	        

Tip: You can select a particular List control ListItem via (e.g. handy when loading a record where the U.I. has one of its properties displayed in a List control):?

lstCategory.Items.FindByText("MatchItemText").Selected = true;        

Tip: You could improve this code in several ways. It probably makes the most sense to remove these data access routines from the event handler and put them into more generic functions. For example, you could use a function that accepts a ProductID and returns a single DataRow with the associated product information. Another improvement would be to use a stored procedure to retrieve this information.

7) SqlDataSource with Parameters (ControlParameter) for use in SelectCommand & UpdateCommand - RecordEditorDataSource.aspx

In this example we'll create a Record Editor using a DropDownList control (select Product), a DetailsView control (display & update Product details), and two SqlDataSource controls (used by the DropDownList & DetailsView).?This example does not require you to write any C# code!?

First we'll retrieve data based on a DropDownList selection. Then we'll display and make updates via a DetailsView with AutoGenerateEditButton enabled. The SqlDataSource that the DetailsView uses has a Parameterized SelectCommand & UpdateCommand (which uses a ControlParameter that looks at the DropDownList control's selected value).?

Flow of application: SqlDataSource (sourceProducts) ? DropDownList (lstProduct) ? SqlDataSource (sourceProductDetails) ? DetailsView (DetailsView1)

Note: This page allows overlapping edits. See RecordEditorDataSource_MatchAllValues.aspx for a version that doesn't.

ASPX:

<asp:DropDownList ID="lstProduct" runat="server" AutoPostBack="True"?
	DataSourceID="sourceProducts" DataTextField="ProductName"?
	DataValueField="ProductID" AppendDataBoundItems="True">
	<asp:ListItem Value="0">Select Product</asp:ListItem>
</asp:DropDownList>

<asp:SqlDataSource ID="sourceProducts" runat="server" ProviderName="System.Data.SqlClient"
	ConnectionString="<%$ ConnectionStrings:Northwind %>"?
	SelectCommand="SELECT ProductName, ProductID FROM Products" />

<br /><br />

<asp:DetailsView ID="DetailsView1" runat="server" DataSourceID="sourceProductDetails" AutoGenerateEditButton="True">
</asp:DetailsView>

<asp:SqlDataSource ID="sourceProductDetails" runat="server" ProviderName="System.Data.SqlClient"
	ConnectionString="<%$ ConnectionStrings:Northwind %>"?
	SelectCommand="SELECT ProductID, ProductName, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued?
		FROM Products WHERE ProductID=@ProductID"
	UpdateCommand="UPDATE Products SET ProductName=@ProductName, UnitPrice=CONVERT(money, @UnitPrice), UnitsInStock=@UnitsInStock,?
		UnitsOnOrder=@UnitsOnOrder, ReorderLevel=@ReorderLevel, Discontinued=@Discontinued WHERE ProductID=@ProductID">
	<SelectParameters>
		<asp:ControlParameter ControlID="lstProduct" Name="ProductID" PropertyName="SelectedValue" />
	</SelectParameters>
</asp:SqlDataSource>        

Note: This example does not have any Code-Behind (empty .cs file)!?

Tip: You can add a "(Select)" item to your DropDownList by setting DropDownList.AppendDataBoundItems="True" and adding an asp:ListItem within your asp:DropDownList.

8) SqlDataSource with Error Handling that includes setting Parameter value set in code-Behind (SqlDataSource Selecting event) and displays a read-Only GridView - CustomParameters.aspx

In this example, Users start by selecting a Customer record from a DropDownList. We'll then display the associated Order records in a GridView underneath. We'll use two SqlDataSource controls, the 2nd having a ControlParameter (for CustomerID) and a second Parameter.?

When the User picks a customer from the list, the page is posted back (ListBox.AutoPostBack="True") and the matching orders are shown in a GridView underneath, using a second data source. This data source pulls the CustomerID for the currently selected customer from the DropDown list using a ControlParameter (like the in the previous example).

In the code-behind, while'll add handler methods for the Selecting and Selected events of the Order SqlDataSource. Within the Selecting handler we'll set a SqlDataSource Parameter value and and within Selected we'll catch any Exception errors.?

The key part of this is example is the addition of Error Handling for the SqlDataSource via its Selected event.

Flow of application: SqlDataSource (sourceCustomers) ? DropDownList (lstCustomers) ? SqlDataSource (sourceOrders) ? gridOrders

Note: This example is similar to RecordEditorDataSource.aspx (above) except top DropDownown displays Customers (instead of Products) and bottom control is a GridView that shows Orders (instead of DetailsView that shows Product record).

Tip: AllowSorting is a useful property of the GridView control.?

ASPX:

<asp:DropDownList ID="lstCustomers" runat="server" AutoPostBack="True" AppendDataBoundItems="True"
	DataSourceID="sourceCustomers" DataTextField="ContactName" DataValueField="CustomerID">
	<asp:ListItem Value="0">Select Customer</asp:ListItem>
</asp:DropDownList>

<asp:SqlDataSource ID="sourceCustomers" runat="server"
? ProviderName="System.Data.SqlClient"?
? ConnectionString="<%$ ConnectionStrings:Northwind %>"
? SelectCommand="SELECT CustomerID, ContactName FROM Customers" />

<br /><br />

<asp:GridView ID="gridOrders" runat="server" DataSourceID="sourceOrders"?
		AutoGenerateColumns="False" DataKeyNames="OrderID" AllowSorting="true">
	<Columns>
		<asp:boundfield DataField="OrderID" HeaderText="OrderID" InsertVisible="False"?
			ReadOnly="True" SortExpression="OrderID"></asp:boundfield>
		<asp:boundfield DataField="OrderDate" HeaderText="OrderDate" DataFormatString="{0:d}"
			SortExpression="OrderDate"></asp:boundfield>
		<asp:boundfield DataField="ShippedDate" DataFormatString="{0:d}"??
			HeaderText="ShippedDate"?
			SortExpression="ShippedDate"></asp:boundfield>
	</Columns>
</asp:GridView>

<!-- Note: Parameter value modified & Error handling added in Code-Behind -->		
<asp:SqlDataSource ID="sourceOrders" runat="server"
	ProviderName="System.Data.SqlClient"
	ConnectionString="<%$ ConnectionStrings:Northwind %>"
	SelectCommand="SELECT OrderID,OrderDate,ShippedDate FROM Orders WHERE CustomerID=@CustomerID AND OrderDate>=@EarliestOrderDate"
	OnSelecting ="sourceOrders_Selecting" OnSelected ="sourceOrders_Selected">
	<SelectParameters>
		<asp:ControlParameter Name="CustomerID" ControlID="lstCustomers" PropertyName="SelectedValue" />
		<asp:Parameter Name="EarliestOrderDate" DefaultValue="1900/01/01" />
	</SelectParameters>
</asp:SqlDataSource>

<!-- Error Handling Message -->
<asp:Label ID="lblError" runat="server" ForeColor="Red"></asp:Label>        

Code-Behind:

// The SqlDataSource Selecting event (OnSelecting handler) can be used to modify Parameters (further results filtering)
protected void sourceOrders_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
	// SqlDataSource SelectCommand has OrderDate>=@EarliestOrderDate
	// but don't apply the line below because Northwind data has old dates
	e.Command.Parameters["@EarliestOrderDate"].Value = DateTime.Today.AddYears(-10);
}

// Handling Errors - The Selected event can be used to handle SqlDataSource errors
protected void sourceOrders_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
	if (e.Exception != null)
	{
		lblError.Text = "An exception occurred performing the query.";

		// Consider the error handled
		e.ExceptionHandled = true;
	}
}	        

9) Updating, Deleting, and Inserting records using a DetailsView and corresponding SqlDataSource Commands - UpdateDeleteInsert.aspx

In this example, we'll use a DetailsView control with Paging?and Auto Generated Buttons to show multiple fields in a Data Source. When using a DetailsView, you can give Users a way to update values by simply setting the AutoGenerateEditButton property to true.?To enable deleting and inserting, you need to set the AutoGenerateDeleteButton and AutoGenerateInsertButton properties to true.

When using a SqlDataSource, you define the InsertCommand, DeleteCommand, and UpdateCommand in the same way you define the command for the SelectCommand property—by using a parameterized query.

DetailsView has built-in tabular rendering, whereas FormView requires user-defined template for its rendering.?They are similar (both display a single data item and enable edit, insert, and deletion), but the FormView allows for more control over the layout.

When you insert a new record it will be given an automatically generated ID and placed at the end of the table. To see it, jump to the last record (using the paging buttons).

Note: The SqlDataSource used has an SQL statement for all 4 CRUD Operations specified within the appropriate properties - SelectedCommand, UpdateCommand, DeleteCommand, and InsertCommand?(the previous examples only had 1 or 2 of these defined).?

Note: The DetailsView in this example has the DataKeyNames and AllowPaging properties defined.

ASPX:

<asp:SqlDataSource ID="sourceProducts" runat="server" ProviderName="System.Data.SqlClient"
	ConnectionString="<%$ ConnectionStrings:Northwind %>"?
	SelectCommand="SELECT ProductID, ProductName, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products"
	UpdateCommand="UPDATE Products SET ProductName=@ProductName, UnitPrice=CONVERT(money, @UnitPrice), UnitsInStock=@UnitsInStock, UnitsOnOrder=@UnitsOnOrder, ReorderLevel=@ReorderLevel, Discontinued=@Discontinued WHERE ProductID=@ProductID"
	DeleteCommand="DELETE Products WHERE ProductID=@ProductID"?
	InsertCommand="INSERT INTO Products (ProductName, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued) VALUES (@ProductName, CONVERT(money, @UnitPrice), @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued)">
</asp:SqlDataSource>

<asp:DetailsView ID="DetailsView1" runat="server"?
	DataSourceID="sourceProducts" DataKeyNames="ProductID" AllowPaging="True"?
	AutoGenerateInsertButton="true" AutoGenerateDeleteButton="true" AutoGenerateEditButton="true">
	<PagerSettings PageButtonCount="25" />
</asp:DetailsView>		        

Note: This example does not have any Code-Behind (empty .cs file)!?

10) Concurrency Checking - SqlDataSource with match-all-values concurrency (w/ OldValuesParameterFormatString, ConflictDetection, and OnUpdated) + DetailsView (w/ AutoGenerateEditButton) - RecordEditorDataSource_MatchAll.aspx

This example is an enhanced version of the "SqlDataSource with Parameters (ControlParameter)" (RecordEditorDataSource.aspx) example found above.?

This version prevents overlapping edits by implementing Concurrency Checking.

To try this out, open two copies of the page in two browser windows. Start editing the same record in both pages. Make changes in both pages to same field (for example, use two different UnitCost values). Now try applying the changes. The first change will be applied, but the second will fail due to the concurrency conflict.?To replicate this issue, the DetailsView must be in Edit-mode in both windows at the same time, update 1, and then update the other.

Matching every field (e.g. verifying that no column data has been changed within a record while during editing) is an acceptable approach for small records, but it isn’t the most efficient strategy if you have tables with huge amounts of data. You could instead add a timestamp field to your database table, and use that for concurrency checking.

ASPX:

<asp:DropDownList ID="lstProduct" runat="server" AutoPostBack="True"?
	DataSourceID="sourceProducts" DataTextField="ProductName"?
	DataValueField="ProductID" AppendDataBoundItems="True">
	<asp:ListItem Value="0">Select Product</asp:ListItem>
</asp:DropDownList>

<asp:SqlDataSource ID="sourceProducts" runat="server" ProviderName="System.Data.SqlClient"
	ConnectionString="<%$ ConnectionStrings:Northwind %>"?
	SelectCommand="SELECT ProductName, ProductID FROM Products" />

<br /><br />

<asp:DetailsView ID="DetailsView1" runat="server"?
	DataSourceID="sourceProductDetails" AutoGenerateEditButton="True">
</asp:DetailsView>

<asp:SqlDataSource ID="sourceProductDetails" runat="server" ProviderName="System.Data.SqlClient"
	ConnectionString="<%$ ConnectionStrings:Northwind %>" SelectCommand="SELECT ProductID, ProductName, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products WHERE ProductID=@ProductID"
	UpdateCommand="UPDATE Products SET ProductName=@ProductName, UnitPrice=@UnitPrice, UnitsInStock=@UnitsInStock, UnitsOnOrder=@UnitsOnOrder, ReorderLevel=@ReorderLevel, Discontinued=@Discontinued WHERE ProductID=@ProductID AND ProductName=@original_ProductName AND UnitPrice=@original_UnitPrice AND UnitsInStock=@original_UnitsInStock AND UnitsOnOrder=@original_UnitsOnOrder AND ReorderLevel=@original_ReorderLevel AND Discontinued=@original_Discontinued"
	OldValuesParameterFormatString="original_{0}" ConflictDetection="CompareAllValues"
	OnUpdated="sourceProductDetails_Updated">
	<SelectParameters>
		<asp:ControlParameter ControlID="lstProduct" Name="ProductID" PropertyName="SelectedValue" />
	</SelectParameters>
</asp:SqlDataSource>

<asp:Label ID="lblInfo" runat="server" EnableViewState="False" Font-Bold="True" ForeColor="#C00000"></asp:Label>        

RecordEditorDataSource.aspx vs.?RecordEditorDataSource_MatchAllValues.aspx:

// RecordEditorDataSource.aspx		
UpdateCommand="UPDATE Products SET ProductName=@ProductName, UnitPrice=CONVERT(money, @UnitPrice), UnitsInStock=@UnitsInStock, UnitsOnOrder=@UnitsOnOrder, ReorderLevel=@ReorderLevel, Discontinued=@Discontinued?
WHERE ProductID=@ProductID">

// RecordEditorDataSource_MatchAllValues.aspx - extended WHERE clause and add OldValuesParameterFormatString & ConflictDetection to match all values, and handle error in OnUpdated event handler
UpdateCommand="UPDATE Products SET ProductName=@ProductName, UnitPrice=@UnitPrice, UnitsInStock=@UnitsInStock, UnitsOnOrder=@UnitsOnOrder, ReorderLevel=@ReorderLevel, Discontinued=@Discontinued?
WHERE ProductID=@ProductID?
AND ProductName=@original_ProductName AND UnitPrice=@original_UnitPrice AND UnitsInStock=@original_UnitsInStock AND UnitsOnOrder=@original_UnitsOnOrder AND ReorderLevel=@original_ReorderLevel AND? Discontinued=@original_Discontinued"
OldValuesParameterFormatString="original_{0}" ConflictDetection="CompareAllValues"
OnUpdated        ="sourceProductDetails_Updated">		        

Note: Notice the use of OldValuesParameterFormatString & ConflictDetection properties.?

Code-Behind:

// Handling Errors - check # records affected (0 indicates an error)		
protected void sourceProductDetails_Updated(object sender, SqlDataSourceStatusEventArgs e)
{
	if (e.AffectedRows == 0)
	{
		lblInfo.Text = "No update was performed. A concurrency error is likely, or the command is incorrectly written.";
	}
	else
	{
		lblInfo.Text = "Record successfully updated.";
	}
}	        

11) Bonus - The Repeater Control

ASPX:

<asp:Repeater ID="rpCountries" runat="server">
	<HeaderTemplate>
		<h4>Countries</h4>
		<ul>
	</HeaderTemplate>
	
	<ItemTemplate><li><%#Eval("text")%> (<%#Eval("value") %>)</li></ItemTemplate>? ? ? ? ? ??
	
	<AlternatingItemTemplate></AlternatingItemTemplate>? ? ? ? ? ??
	<SeparatorTemplate></SeparatorTemplate>

	<FooterTemplate>
		</ul>
	</FooterTemplate>? ? ? ? ? ? ? ??
</asp:Repeater>        

Code-Behind:

protected void Page_Load(object sender, EventArgs e)
{
	// XML
	DataSet mycountries_xml = new DataSet();? // Load XML file into a DataSet?
	mycountries_xml.ReadXml(MapPath("App_Data/countries.xml"));?


	// Repeater
	rpCountries.DataSource = mycountries_xml;
	rpCountries.DataBind();
}
        

Reference

• ASP.NET The Repeater Control (W3Schools.com mirror site)
• Repeater control Tutorial with example in ASP.Net using C# and VB.Net

Key Tips

• Typically when populating a list, it is only filled the first time the page is requested (!Page.IsPostBack) and stored in the view state automatically.
• Your SqlCommand object's Command Text can contain an SQL Join.
• It makes sense to remove these data access routines from control event handlers and put them into more generic (utility) functions. For example, you could use a function that accepts a ProductID and returns a single DataRow with the associated product information. Another approach would be to use a stored procedure to retrieve this information.
• You can select a List control ListItem via:

lstCategory.Items.FindByText("MatchItemText").Selected = true;        

• You can add an initial "(Select)" item to your DropDownList by setting DropDownList.AppendDataBoundItems="True" and adding a <asp:ListItem> within your <asp:DropDownList>. Alternatively, you can do:

DropDownList1.Items.Insert(0, New ListItem("--Select Customer--", "0"))        

• In a real application, remember to use Exception Handling.?
• AllowSorting is a very useful property of the GridView control.

Single-Value or "Simple" Data Binding

• To use single-value binding, you must insert a data binding expression into the markup in the .aspx file (not the code-behind file).?
• Simple / Single-value is implemented by binding to a Page variable, property, or expression via a Binding Expression in the .aspx file: <%# Var_Prop_Expr %>. You can use Simple Data Binding as an approach to dynamic text, but it can also be a useful way to set control properties (i.e. Label.Text, HyperLink.NavigateUrl, Image.ImageUrl , etc.). Alternatively, you can set these properties in the Code-Behind.?
• Single-value data binding doesn’t necessarily have anything to do with ADO.NET. Instead, single-value data binding allows you to take a variable, a property, or an expression and insert it dynamically into a page.
• You can use single-value data binding properties to set other types of information on your page including control properties (Text, NavigateURL, ImageUrl, etc.)
• When the page loads, data binding expressions are automatically replaced with the appropriate values.
• Simple Data Binding is useful for building Rich Data Control templates. As an example, another place of where you would also use a Data Binding Expression is with the Repeater control (Repeated-Value Data Binding).
• If you decide not to use single-value data binding, you can accomplish the same thing using code (i.e. set the control propery in your code behind file). The trade-off is: complicating your markup with additional details about your code vs. complicating your code (the reverse) with additioanl details about your page markup (i.e. text).

The difference between <%= and <%# in ASP.NET:

• The <%= expressions are evaluated at render time
• The <%# expressions are evaluated at DataBind() time and are not evaluated at all if DataBind() is not called.

When should I use # and = in ASP.NET controls?

• <%# - Data Binding. It can only used where Data Binding is supported, or at the page level if you call Page.DataBind() in your code-behind.
• <%= - Explicit output to page, equivalent to <% Response.Write( ) %>
• <%: - Equivalent to <%=, but it also html-encodes the output.
• <%@ - Page/Control/Import/Register directive
• <%$ - Resource access and Expression building
• <%-- - Server-side comment block

How To

• Use Binding expression like <%# Var_Prop_Expr %> within HTML markup and also populate the data and call this.DataBind() in Page_Load.
• Typically, you call the DataBind() method in the Page.Load event handler.
• Example binding to page class variable or property and an expression:

<%# Request.Browser.Browser %>

<%# TransactionCount %>        

• Example of using binding to set a control property:

<asp:Hyperlink id="lnkDynamic" runat="server" 
               Text="Click here!" NavigateUrl="<%# URL %>"  />        

Repeated-Value or "List" Data Binding

• Repeated-value Data Binding works with the ASP.NET List controls (e.g. ListBox) and Rich Data Controls (e.g. GridView), which are all controls with a DataSource property. To implement, create / fill / retrieve your data object (i.e. DataSet) and assign as your control's DataSource, which is typically done in the Page.Load event or in the aspx file.
• To use repeated-value binding, you link one of these controls to a data source (such as a field in a data table). When you call DataBind(), the control automatically creates a full list using all the corresponding values. This saves you from writing code that loops through the array or data table and manually adds elements to a control. Repeated-value binding can also simplify your life by supporting advanced formatting and template options that automatically configure how the data should look when it’s placed in the control.
• As with single-value binding, repeated-value binding doesn’t necessarily need to use data from a database, and it doesn’t have to use the ADO.NET objects. For example, you can use repeated-value binding to bind data from a collection, an array, or an XML document.
• GridView, DetailsView, FormView, and ListView - These rich web controls allow you to provide repeating lists or grids that can display more than one field of information at a time.
• Once you specify data binding, you need to activate it. You accomplish this task by calling the DataBind() method (for the control or page).
• You get the greatest advantage of data binding when you use it in conjunction with a database.
• When you’re using data binding with the information drawn from a database, the data binding process takes place in the same three steps. First you create your data source, which will be a DataReader or DataSet object. A DataReader generally offers the best performance, but it limits your data binding to a single control because it is a forward-only reader. As it fills a control, it traverses the results from beginning to end. Once it’s finished, it can’t go back to the beginning; so it can’t be used in another data binding operation. For this reason, a DataSet is a more common choice (supports sorting, filtering, caching).

How To

• Link one of the List controls to a DataSource (a control property) such as a collection (retrieved from database, XML, etc.). When you call the DataBind method (of the Page class or control), the control automatically creates a full list using all the corresponding values. This saves you from writing code that loops through the array or data table and manually adds elements to a control.
• Create and fill a collection manually:

List<string> fruit = new List<string>();

fruit.Add("Kiwi");        

• In a realistic application, you would add code to retrieve from a database or XML file, or you might be using a function that returns a ready-made collection to you. Example:

List<string> fruit = GetFruitsInSeasons("Summer");        

• Once you have your collection, set your List control's DataSource property and then call the DataBind method:

lstItems.DataSource = fruit;

this.DataBind();        

• When DataBinding to a Dictionary collection, you might use the DataTextField & DataValueField of your List Control, and set them to the "Value" and "Key" of the Dictionary object respectively:

protected void Page_Load(object sender, EventArgs e)
{
	if (!this.IsPostBack)
	{
		Dictionary<int, string> fruit = new Dictionary<int, string>();
		fruit.Add(1,"Kiwi");
		// add more items
		
		MyListBox.DataSource = fruit; // define binding
		MyListBox.DataTextField = "Value"; // choose field to display
		MyListBox.DataValueField = "Key"; // set value attribute of option tag
		this.DataBind(); // activate binding
	}
}        

• And then you could retrieve the value later using the SelectedItem property:

lblMessage.Text = "You picked: " + MyListBox.SelectedItem.Text 
                + " which has the key: " + MyListBox.SelectedItem.Value;        

• This technique is particularly useful with a database. You could embed a unique ID into the value property and be able to quickly look up a corresponding record depending on the user’s selection by examining the value of the SelectedItem object. Note that for this to work, you can’t regenerate the list after every postback. If you do, the selected item information will be lost and an error will occur.

Strongly Typed Collections

• You can use data binding with the Hashtable and ArrayList, two of the more useful collection classes in the System.Collections namespace. However, .NET includes a more stringent set of collections in another namespace: System.Collections.Generic. These collections are ideal in cases where you want your collection to hold just a single type of object.?
• When you pull an item out of a generic collection, you don’t need to write casting code to convert it to the right type, because the compiler already knows what type of objects you’re using. This behavior is safer and more convenient, and it’s what you’ll want most of the time.?

Data Binding with a Dictionary Collection

• A dictionary collection is a special kind of collection in which every item (or definition, to use the dictionary analogy) is indexed with a specific key (or dictionary word).?
• By utilizing keys it makes it easier to retrieve the item you want. In ordinary collections, like the ArrayList or List, you need to find the item you want by its index number position, or—more often—by traveling through the whole collection until you come across the right item. With a dictionary collection, you retrieve the item you want using its key. Generally, ordinary collections make sense when you need to work with all the items at once, while dictionary collections make sense when you frequently retrieve a single specific item.?
• You can use two basic dictionary-style collections in .NET. The Hashtable collection (in the System.Collections namespace) allows you to store any type of object and use any type of object for the key values. The Dictionary collection (in the System.Collections.Generic namespace) uses generics to provide the same “locking in” behavior as the List collection.?

Data-Bound Web Server Controls

List controls

• BulletedList
• CheckBoxList (option - choose multi)
• DropDownList (select - short list)
• ListBox (select - long list)
• RadioButtonList (option – choose one)

Rich Data controls

• DetailsView - Displays one record at a time in a tabular layout and enables you to edit, delete, and insert records. You can also page through multiple records.
• FormView - Similar to DetailsView, but enables you to define a free-form layout for each record. DetailsView has built-in tabular rendering, whereas FormView requires user-defined template for its rendering. They are similar (both display a single data item and enable edit, insert, and deletion), but the FormView allows for more control over the layout.
• GridView - Displays data in a table and includes support for editing, updating, deleting, sorting, and paging data without requiring code.
• ListView - Enables you to define the data layout by using templates. Supports automatic sort, edit, insert, and delete operations. You can also enable paging by using an associated DataPager control.
• Repeater - Renders data in a list. Each item is rendered using an item template that you define.
• Menu - Renders data in a hierarchical dynamic menu that can include submenus.
• TreeView - Renders data in a hierarchical tree of expandable nodes.
• Tip: Use SqlDataSource controls to bind (repeated-value) database data to List controls.

References

• Comparing ListView with GridView,DataList and Repeater - CodeProject
• Repeater vs. DataList vs. ListView vs. GridView
• Display (Show) data in ListView from database in ASP.Net using C# and VB.Net
• Bind (Populate) ASP.Net DropDownList using DataTable (DataSet) in C# and VB.Net

Data Source Controls

Data source controls allow you to create data-bound pages without writing any data access code at all. The Data Source controls include any control that implements the IDataSource interface. The .NET Framework includes the following data source controls (found in the Data tab of the Toolbox in Visual Studio):

• SqlDataSource (connect to any data source that has an ADO.NET data provider - SqlClient, OracleClient, OleDb, Odbc)
• AccessDataSource (doesn't scale well)
• ObjectDataSource (custom data access class)
• XmlDataSource (connect to XML file)
• SiteMapDataSource (connect to .sitemap file)
• EntityDataSource (query a database using LINQ to Entities)
• LinqDataSource (query a database using LINQ to SQL, the predessor to LINQ to Entities)

Reference

• ASP.NET - How to use SelectParameters in SqlDataSource

Data source controls turn up in the .aspx markup portion of your web page like ordinary controls. Here’s an example:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:[NameOfConnectionString] %>" ... />        

Data source controls can perform two key tasks:

1. They can retrieve data from a data source and supply it to bound controls. When you use this feature, your bound controls are automatically filled with data. You don’t even need to call DataBind().
2. They can update the data source when edits take place. In order to use this feature, you must use one of ASP.NET’s rich data controls, like the GridView or DetailsView.

SqlDataSource

• After creating your SqlDataSource, you need to bind it to the control(s) where you want to display the data. You do this by setting the DataSourceID property of the control.
• You can use each SqlDataSource control you create to retrieve a single query. Optionally, you can also add corresponding commands for deleting, inserting, and updating rows.
• The SqlDataSource command logic is supplied through four properties — SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand — each of which takes a string.

<asp:DropDownList ID="lstCustomers" runat="server" AutoPostBack="True"DataSourceID="sourceCustomers" DataTextField="ContactName" DataValueField="CustomerID">
</asp:DropDownList>


<asp:SqlDataSource ID="sourceCustomers" runat="server" ProviderName="System.Data.SqlClient" ConnectionString="<%$ ConnectionStrings:Northwind %>" SelectCommand="SELECT CustomerID, ContactName FROM Customers" />        

• You can configure a SqlDataSource via the Properties window.
• Tip: If you want some help creating your connection string, select the SqlDataSource, open the Properties window, and select the ConnectionString property. A drop-down arrow will appear at the right side of the value. If you click that drop-down arrow, you’ll see a list of all the connection strings in your web.config file.
• Tip: You can write the data source logic by hand, or you can use a design-time wizard that lets you create a connection and create the command logic in a graphical query builder. To launch this tool, select the data source control on the design surface of your page, and click the Configure Data Source link in the smart tag.
• SqlDataSource control supports multiple DataSourceMode: SqlDataSourceMode.DataSet (the default) or SqlDataSourceMode.DataReader. The DataSet mode is almost always better, because it supports advanced sorting, filtering, and caching settings that depend on the DataSet. All these features are disabled in DataReader mode. However, you can use the DataReader mode with extremely large grids, because it’s more memory-efficient. That’s because the DataReader holds only one record in memory at a time—just long enough to copy the record’s information to the linked control.
• An important fact to understand about the data source controls is that when you bind more than one control to the same data source, you cause the query to be executed multiple times. For example, if two controls are bound to the same data source, the data source control performs its query twice — once for each control. This is somewhat inefficient—after all, if you wrote the data binding code yourself by hand, you’d probably choose to perform the query once and then bind the returned DataSet twice. However, most of the time you won't be binding more than one control to a data source. That's because rich data controls - the GridView, DetailsView, and FormsView - have the ability to present multiple pieces of data in a flexible layout.
• You can avoid multiple-query overhead using caching, which allows you to store the retrieved data in a temporary memory location where it will be reused automatically. The SqlDataSource supports automatic caching if you set EnableCaching to true.
• You can use parameterized commands with the SqlDataSource. These parameter types include: ControlParameter, QueryStringParameter, SessionParameter, CookieParameter, ProfileParameter, RouteParameter, FormParameter, and Parameter.

<asp:SqlDataSource ID="sourceOrders" runat="server"
?ProviderName="System.Data.SqlClient"
?ConnectionString="<%$ ConnectionStrings:Northwind %>"
?SelectCommand="SELECT OrderID,OrderDate,ShippedDate FROM Orders?
?WHERE CustomerID=@CustomerID AND OrderDate>=@EarliestOrderDate"
?OnSelecting="sourceOrders_Selecting">
?<SelectParameters>
	<asp:ControlParameter Name="CustomerID"?
	 ControlID="lstCustomers" PropertyName="SelectedValue" />
	<asp:Parameter Name="EarliestOrderDate" DefaultValue="1900/01/01" />
?</SelectParameters>
</asp:SqlDataSource>        

Handling Errors with the SqlDataSource

• When you deal with an outside resource such as a database, you need to protect your code with a basic amount of error-handling logic. Even if you’ve avoided every possible coding mistake, you still need to defend against factors outside your control — for example, if the database server isn’t running or the network connection is broken. You can count on the SqlDataSource to properly release any resources (such as connections) if an error occurs. However, the underlying exception won’t be handled. Instead, it will bubble up to the page and derail your processing. As with any other unhandled exception, the user will receive a cryptic error message or an error page. However, you can prevent this behavior by adding code to the Selected, Updated, Deleted, or Inserted event of the control and then setting the SqlDataSourceStatusEventArgs.ExceptionHandled property to true.

protected void sourceOrders_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
	if (e.Exception != null)
	{
		// Show a more suitable error message
		lblError.Text = "An exception occurred performing the query.";
		// Consider the error handled
		e.ExceptionHandled = true;
	}
}        

Updating Records using Rich Data Controls

• ASP.NET’s rich data controls — including the GridView, DetailsView, and FormView — all have editing features (insert, update, delete) you can switch on.
• Before you can switch on the editing features in a given control, you need to define suitable commands for the operations you want to perform in your data source. That means supplying commands for inserting (InsertCommand), deleting (DeleteCommand), and updating (UpdateCommand). If you know you will allow the user to perform only certain operations (such as updates) but not others (such as insertions and deletions), you can safely omit the commands you don’t need.
• You define the InsertCommand, DeleteCommand, and UpdateCommand in the same way you define the command for the SelectCommand property—by using a parameterized query.

<asp:SqlDataSource ID="sourceProductDetails" runat="server"
	ProviderName="System.Data.SqlClient"
	ConnectionString="<%$ ConnectionStrings:Northwind %>"
	SelectCommand="SELECT ProductID, ProductName, UnitPrice, UnitsInStock,
	UnitsOnOrder, ReorderLevel, Discontinued FROM Products WHERE ProductID=@ProductID"
	UpdateCommand="UPDATE Products SET ProductName=@ProductName, UnitPrice=@UnitPrice,
	UnitsInStock=@UnitsInStock, UnitsOnOrder=@UnitsOnOrder, ReorderLevel=@ReorderLevel,
	Discontinued=@Discontinued WHERE ProductID=@ProductID">
	<SelectParameters>
		<asp:ControlParameter ControlID="lstProduct" Name="ProductID"
		 PropertyName="SelectedValue" />
	</SelectParameters>
</asp:SqlDataSource>        

• As long as you give each parameter the same name as the field it affects, and preface it with the @ symbol (so ProductName becomes @ProductName), you don’t need to define the parameter.
• When using a DetailsView, you can give users a way to update values by simply setting the AutoGenerateEditButton property to true. To enable deleting and inserting, you need to set the AutoGenerateDeleteButton and AutoGenerateInsertButton properties to true.

<asp:DetailsView ID="detailsProduct" runat="server" 
 DataSourceID="sourceProductDetails" AutoEditGenerateButton="True" />        

Strict Concurrency Checking

• Concurrency checking is recommended when developing systems that have frequent data updates and many users.
• The problem with updating a record based on its ID is that it opens the door to an update that overwrites the changes of another user, if these changes are made between the time your page is requested and the time your page commits its update.
• One way to solve this problem is to use an approach called match-all-values concurrency. To use this approach, you need to add a Where clause that tries to match every field.
• Extended WHERE clause to match all values by adding OldValuesParameterFormatString & ConflictDetection to SqlDataSource tag, then handle any errors in SqlDataSource OnUpdated event handler.
• Before:

UpdateCommand="UPDATE Products 
SET ProductName=@ProductName, UnitPrice=CONVERT(money, @UnitPrice), 
UnitsInStock=@UnitsInStock, UnitsOnOrder=@UnitsOnOrder, 
ReorderLevel=@ReorderLevel, Discontinued=@Discontinued
WHERE ProductID=@ProductID">        

• After (extended WHERE clause to match all values and added OldValuesParameterFormatString, ConflictDetection, and OnUpdated)

UpdateCommand="UPDATE Products SET ProductName=@ProductName, UnitPrice=@UnitPrice, UnitsInStock=@UnitsInStock, UnitsOnOrder=@UnitsOnOrder, ReorderLevel=@ReorderLevel, Discontinued=@Discontinued
WHERE ProductID=@ProductID AND ProductName=@original_ProductName AND UnitPrice=@original_UnitPrice AND UnitsInStock=@original_UnitsInStock AND UnitsOnOrder=@original_UnitsOnOrder AND ReorderLevel=@original_ReorderLevel?
AND Discontinued=@original_Discontinued"
OldValuesParameterFormatString="original_{0}"?
ConflictDetection="CompareAllValues"?
OnUpdated       ="sourceProductDetails_Updated">        

• Matching every field is an acceptable approach for small records, but it isn’t the most efficient strategy if you have tables with huge amounts of data. In this situation, you have two possible solutions: you can match some of the fields (leaving out the ones with really big values) or you can add a timestamp field to your database table, and use that for concurrency checking.
• Using a timestamp field is a better way to implement concurrency rather than match-all-values.
• Timestamps are special fields that the database uses to keep track of the state of a record. Whenever any change is made to a record, the database engine updates the timestamp field, giving it a new, automatically generated value. The purpose of a timestamp field is to make strict concurrency checking easier. When you attempt to perform an update to a table that includes a timestamp field, you use a Where clause that matches the appropriate unique ID value (like ProductID) and the timestamp field:

// A better way to implement concurrency rather than match-all-values
// is to use a timestamp field
UpdateCommand="UPDATE Products SET ProductName=@ProductName, UnitPrice=@UnitPrice,
UnitsInStock=@UnitsInStock, UnitsOnOrder=@UnitsOnOrder,
ReorderLevel=@ReorderLevel, Discontinued=@Discontinued
WHERE ProductID=@ProductID AND RowTimestamp=@RowTimestamp"        

• The database engine uses the ProductID to look up the matching record. Then, it attempts to match the timestamp in order to update the record. If the timestamp matches, you know the record hasn’t been changed. The actual value of the timestamp isn’t important, because that’s controlled by the database. You just need to know whether it’s changed.
• Creating a timestamp is easy. In SQL Server, you create a timestamp field using the timestamp data type. In other database products, timestamps are sometimes called row versions.
• When performing an update with SqlDataSource, it doesn’t raise an exception to notify you if no update is performed. So, if you use the command shown in this example, you need to handle the SqlDataSource.Updated event and check the SqlDataSourceStatusEventArgs.AffectedRows property. If it’s 0, no records have been updated, and you should notify the user about the concurrency problem so the update can be attempted again, as shown here:

protected void sourceProductDetails_Updated(object sender,SqlDataSourceStatusEventArgs e)
{
	if (e.AffectedRows == 0)
	{
	? ?lblInfo.Text = "No update was performed. " +
	? ?"A concurrency error is likely, or the command is incorrectly written.";
	}
	else
	{
	? ?lblInfo.Text = "Record successfully updated.";
	}
}        

Understanding the Page Life Cycle with Data Binding

1. The page object is created (based on the .aspx file).?
2. The page life cycle begins, and the Page.Init and Page.Load events fire. Note, code inside the page load event typically checks for PostBack and then sets control properties (or retrieve data) appropriately.
3. All other control events fire.?
4. If the user is applying a change, the data source controls perform their update operations. If a row is being updated, the Updating and Updated events fire. If a row is being inserted, the Inserting and Inserted events fire. If a row is being deleted, the Deleting and Deleted events fire.?
5. The Page.PreRender event fires. Note, this event takes place after all regular PostBack events have taken place.
6. The Data Source controls perform their queries and insert the data they retrieve into the bound controls. This step happens the first time your page is requested and every time the page is posted back, ensuring you always have the most up-to-date data. The Selecting and Selected events fire at this point. Note, the SqlDataSource supports automatic caching if you set EnableCaching to True.
7. The page is rendered and disposed. Note, Events - SaveStateComplete, Render and UnLoad.