Grid View Tutorial Series I

Datagrid in asp.net - Part 1

Suggested Video Tutorials
Dot Net Basics
C# video tutorial
asp.net video tutorial
Sql server video tutorial



In this video, we will discuss about
1. The difference between datagrid and gridview controls
2. What to choose datagrid or gridview



Difference between DataGrid and GridView
1. DataGrid is introduced in asp.net 1.1 and is still supported today. GridView is introduced in asp.net 2.0.
2. Declarative datasource controls can be used with DataGrid only for data selection. Tasks like paging, sorting, deletes and updates must be done in code. The GridView control can achieve all of these using the declarative datasource controls.
3. GridView introduces new column types.

What to choose datagrid or gridview
If you are using asp.net 2.0 or later, I personally suggest using gridview over datagrid control.

DataGrid control is not found in visual toolbox?
If you are using visual studio 2008 or later versions, by default DataGrid control is not shown in the visual studio toolbox. If you want the datagrid control, to appear in visual studio toolbox, follow these steps
1. Right click on toolbox, and select "Choose Items"
2. From "Choose Toolbox Items" dialog box, select "DataGrid" that is present in "System.Web" assembly.
3. Finally click OK. You should now see "DataGrid" control in visual studio toolbox.

Data source controls in asp.net - Part 2

Suggested Video Tutorials
Dot Net Basics
C#
Sql server
ado.net
asp.net 



The following are some of the different data source controls that are available in asp.net
SqlDataSource - Use to work with SQL Server, OLE DB, ODBC, or Oracle databases
ObjectDataSource - Use to work business objects, that manages data
AccessDataSource - Use to work with Microsoft Access
XmlDataSource - Use to work with XML files
LinqDataSource - Enables us to use LINQ, to retrieve and modify data from a data object
EntityDataSource - Use to work with Entity Data Model



Prior to the introduction of data source controls, developers had to write a few lines of code to retrieve and bind data with data-bound controls like DataGrid, GridView, DataList etc. With the introduction of these data source controls, we don't have to write even a single line of code, to retrieve and bind data to a data-bound control.

We will discuss about, each of these data source controls in our upcoming videos.

To bind data to a data bound control, like gridview there are 2 ways
1. Without using data-source controls
2. Using data-source controls

If we are not using data source controls, then we have to write code to 
1. Read connection string
2. Create connection object
3. Create SQL Command object
4. Execute the command
5. Retrieve and bind the results to the data-bound control.

Code to bind data to a gridview control, without using data-source controls:
string cs = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
    SqlCommand cmd = new SqlCommand("Select * from tblProducts", con);
    con.Open();
    GridView1.DataSource = cmd.ExecuteReader();
    GridView1.DataBind();
}

If we are using data source controls, we don't have to write even a single line of code. All, you have to do is, drag and drop a data-source control on the webform. Configure the data-source control to connect to a data source and retrieve data. Finally associate the data-source control, to a data-bound control using "DataSourceID" property.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:DBConnectionString %>"
    SelectCommand="SELECT * FROM [tblProducts]">
</asp:SqlDataSource>
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    DataKeyNames="Id" DataSourceID="SqlDataSource1">
    <Columns>
        <asp:BoundField DataField="Id" HeaderText="Id" InsertVisible="False"
            ReadOnly="True" SortExpression="Id" />
        <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
        <asp:BoundField DataField="Description" HeaderText="Description"
            SortExpression="Description" />
    </Columns>
</asp:GridView>

Points to remember:
1. "ConnectionString" property of the "SqlDataSource" control is used to determine the database it has to connect, to retrieve data
2. "SelectCommand" property specifies the command that needs to be executed.
3. DataSource control is associated, with the gridview control, using "DataSourceID" property of the GridView control.

Please Note: All databound controls has DataSourceID property. A few examples of databound controls include
1. DropDownList
2. CheckBoxList
3. Repeater
4. DataList etc...

Object datasource in asp.net - Part 3

Suggested Videos 
Part 1 - Datagrid in asp.net
Part 2 - Data source controls in asp.net

In Part 2 of the asp.net gridview tutorial, we discussed about sqldatasource controls. Please watch Part 2, before proceeding with this video.



Sqldatasource control include sql queries, in the HTML of the aspx page. This may lead to the complexity of the project. That's why, in real time, sqldatasource control is very rarely used.

Let us understand object datasource, with an example. 



Let's assume our project has the following 3 layers.
1. Presentation Layer - This includes the asp.net web application project, where we have webforms, their code-behind files etc.
2. Business Logic Layer - This is usually a class library project, which includes business objects, like customers, orders, products etc.
3. Data Access Layer - This is usually a class library project, that is responsible for all the database CRUD(Create, Read, Update and Delete) operations.

The above 3 layers interact in the following way.
Presentation Layer => Business Logic Layer => Data Access Layer => Database

Presentation Layer, calls Business Logic Layer which in turn will call, Data Access Layer. Data Access Layer performs all the CRUD operations on the database.

In reality, we may have each layer in it's own project, but for the purpose of this demo, we will include the data access layer classes in our asp.net web application. We will discuss about building an asp.net web application with n-tier architecture and the difference between layers and tiers in a later video session.

Create an asp.net web application, and add a class file with name,  ProductDataAccessLayer.cs. Copy and paste the following code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
    }

    public class ProductDataAccessLayer
    {
        public static List<Product> GetAllProducts()
        {
            List<Product> listProducts = new List<Product>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("select * from tblProducts", con);
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Product product = new Product();
                    product.Id = Convert.ToInt32(rdr["Id"]);
                    product.Name = rdr["Name"].ToString();
                    product.Description = rdr["Description"].ToString();

                    listProducts.Add(product);
                }
            }

            return listProducts;
        }
    }
}

At this point, drag "ObjectDataSource" control from the toolbox and drop it on WebForm1.aspx. Steps to configure "ObjectDataSource" 

1. Flip the webform to design mode
2. Click on the little right arrow(Smart Tag). This should display "ObjectDataSource Tasks"
3. Click on "Configure DataSource" link
4. From "Choose your business object" dropdownlist select "Demo.ProductDataAccessLayer" and click "Next"
5. From "Choose a method" dropdownlist, select "GetAllProducts()" and click "Finish"

At this point, we are done, configuring ObjectDataSource control. Now, drag a GridView control from the toolbox and drop it on the webform. Finally associate "ObjectDataSource1" with "GridView1" control using DataSourceID property of "GridView1" control. Run the application and data from "tblProducts" table should now be displayed in the gridview control.

The HTML on WebForm1.aspx should be as shown below
<div>
    <asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
        SelectMethod="GetAllProducts" TypeName="Demo.ProductDataAccessLayer">
    </asp:ObjectDataSource>
    <br />
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
        DataSourceID="ObjectDataSource1">
        <Columns>
            <asp:BoundField DataField="Id" HeaderText="Id" SortExpression="Id" />
            <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
            <asp:BoundField DataField="Description" HeaderText="Description"
                SortExpression="Description" />
        </Columns>
    </asp:GridView>
</div>

XMLDataSource in asp.net - Part 4

Suggested Videos 
Part 1 - Datagrid in asp.net
Part 2 - Data source controls in asp.net
Part 3 - Object datasource in asp.net

We discussed about ObjectDataSource in Part 3 of the asp.net gridview tutorial. Please watch Part 3, before proceeding with this video.



If you want to display xml data in a gridview control, without writing a single line of code, xmldatasource control can be used. Let's understand using XmlDataSource control with an example. Create an asp.net web application. Add a folder with name=Data to the project. Right click on the "Data" folder and add an XML file with name=Countries.xml.



Copy and paste the following XML in Countries1.xml
<?xml version="1.0" encoding="utf-8" ?>
<Countries>
  <Country>
    <Id>101</Id>
    <Name>India</Name>
    <Continent>Asia</Continent>
  </Country>
  <Country>
    <Id>102</Id>
    <Name>China</Name>
    <Continent>Asia</Continent>
  </Country>
  <Country>
    <Id>103</Id>
    <Name>Frnace</Name>
    <Continent>Europe</Continent>
  </Country>
  <Country>
    <Id>104</Id>
    <Name>United Kingdom</Name>
    <Continent>Europe</Continent>
  </Country>
  <Country>
    <Id>105</Id>
    <Name>United State of America</Name>
    <Continent>North America</Continent>
  </Country>
</Countries>

Drag "XmlDataSource" control from the toolbox, onto WebForm1.aspx. Click on the smart tag button of the "XmlDataSource" control, and then click on "Configure Data Source". Click on "Browse" button and select "Countries.xml" from "Data" folder. Finally click OK.

Now, drag and drop a gridview control on WebForm1.aspx. Click on smart tag button on the gridview control, and select "XmlDataSource1" from "Choose Data Source" dropdownlist. At this point, GridView control reports an error stating "The data source for GridView with id 'GridView1' did not have any properties or attributes from which to generate columns.  Ensure that your data source has content.". This is because, XmlDataSource control works with attributes, not child xml entities. 

I can think of 3 ways to solve this issue
1. Rewrite Countries.xml file, using attributes instead of xml nodes. We will discuss this in this video session.
2. Use an XSLT transformation file, to convert xml nodes to attributes.
3. Load Countries.xml data into a dataset and then bind to the gridview control

We will discuss about options 2 and 3 in our next video.

Now, copy and paste the following xml in "Countries.xml". Notice that, we have rewritten, the xml using attributes.
<Countries>
  <Country Id="101" Name="India" Continent="Asia"/>
  <Country Id="102" Name="China" Continent="Asia"/>
  <Country Id="103" Name="Frnace" Continent="Europe"/>
  <Country Id="104" Name="United Kingdom" Continent="Europe"/>
  <Country Id="105" Name="United State of America" Continent="North America"/>
</Countries>

Delete the gridview control, that is already on WebForm1.aspx. Drag and drop the gridview control again, and configure data source. Notice that, the gridview control displays xml data as expected. 

Transform xml using xslt and bind to a gridview control using xmldatasource control - Part 5

Suggested Videos 
Part 2 - Data source controls in asp.net
Part 3 - Object datasource in asp.net
Part 4 - XMLDataSource in asp.net

In Part 4 of the asp.net gridview tutorial, we discussed about binding an xml file to a gridview control using xmldatasource control. XmlDataSource control did not work as expected, when the xml file, contained child xml nodes. We had to manually rewrite the entire xml file using attributes instead. However, manually rewriting the xml file is laborious and error prone, especially if the file is very large.



We can use an XSLT transform file, to dynamically convert the child xml nodes to attributes. Countries xml with child nodes is shown below. Create an asp.net web application. Add a folder with name=Data to the project. Right click on the "Data" folder and add an XML file with name=Countries.xml.



Copy and paste the following XML in Countries.xml
<Countries>
  <Country>
    <Id>101</Id>
    <Name>India</Name>
    <Continent>Asia</Continent>
  </Country>
  <Country>
    <Id>102</Id>
    <Name>China</Name>
    <Continent>Asia</Continent>
  </Country>
  <Country>
    <Id>103</Id>
    <Name>Frnace</Name>
    <Continent>Europe</Continent>
  </Country>
  <Country>
    <Id>104</Id>
    <Name>United Kingdom</Name>
    <Continent>Europe</Continent>
  </Country>
  <Country>
    <Id>105</Id>
    <Name>United State of America</Name>
    <Continent>North America</Continent>
  </Country>
</Countries>

We want to transform the above xml using XSLT. The output should be as shown below.
<Countries>
  <Country Id="101" Name="India" Continent="Asia"/>
  <Country Id="102" Name="China" Continent="Asia"/>
  <Country Id="103" Name="Frnace" Continent="Europe"/>
  <Country Id="104" Name="United Kingdom" Continent="Europe"/>
  <Country Id="105" Name="United State of America" Continent="North America"/>
</Countries>

To achieve this, right click on "Data" folder and add an XSLT file with name=CountriesXSLT.xslt. Copy and paste the following code in CountriesXSLT.xslt file.
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="Countries">
    <Countries>
      <xsl:apply-templates/>
    </Countries>
  </xsl:template>

  <xsl:template match="Country">
    <Country>
      <xsl:for-each select="*">
        <xsl:attribute name="{name()}">
          <xsl:value-of select="text()"/>
        </xsl:attribute>
      </xsl:for-each>
    </Country>
  </xsl:template>
</xsl:stylesheet>

Drag "XmlDataSource" control from the toolbox, onto WebForm1.aspx. Click on the smart tag button of the "XmlDataSource" control, and then click on "Configure Data Source". Click on "Browse" button next to "Data file" textbox and select "Countries.xml" from "Data" folder. Now, click on "Browse" button next to "Transform file" textbox and select "CountriesXSLT.xslt" from "Data" folder. Finally click OK.

Now, drag and drop a gridview control on WebForm1.aspx. Click on smart tag button on the gridview control, and select "XmlDataSource1" from "Choose Data Source" dropdownlist. Notice that, the gridview control displays xml data as expected.

If you want to display the same xml data in a gridview control without using xslt transform, then load the xml data into a dataset and then bind it to the gridview control.
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("~/Data/Countries.xml"));
GridView1.DataSource = ds;
GridView1.DataBind();

AccessDataSource in asp.net - Part 6

Suggested Videos 
Part 3 - Object datasource in asp.net
Part 4 - XMLDataSource in asp.net
Part 5 - Using XSLTTransform with xmldatasource control



If you want to retrieve data from Microsoft Access Database, and then display it in a gridview control, without writing a single line of code, AccessDataSource control can be used. Let's understand using AccessDataSource control with an example. Create an asp.net web application. Add a folder with name=Data to the project. Copy and paste your microsoft access database in the "Data" folder of the application.



Now drag and drop, AccessDataSource, on the webform. Click on the smart tag button of the "AccessDataSource" control, and then click on "Configure Data Source". Click on "Browse" button and select "Products.accdb" from "Data" folder. Now click "Next".

On "Configure the Select Statement" window, select the "table" and the "table columns". Click next. Here you will have the opportunity to "Test Query". Finally click "Finish".

Now, drag and drop a gridview control on the web form. Click on smart tag button on the gridview control, and select "AccessDataSource1" from "Choose Data Source" dropdownlist.

Run the application, and notice that, data from access database is now displayed on the web form, as expected.

Please Note: You may get an error stating, The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. If this is the case, you will need to install Microsoft office data connectivity components from the following link.
http://www.microsoft.com/en-us/download/details.aspx?id=13255

If your operating system is 64 bit, and MS Office is 64 bit, then install AccessDatabaseEngine_x64.exe version.
If your operating system is 64 bit, and MS Office is 32 bit, then install AccessDatabaseEngine.exe version.

If you still have the issue, please make sure, Enable 32 bit applications property is set to true, for the application pool that is running your web application.

Formatting asp.net gridview control - Part 7

Suggested Videos 
Part 4 - XMLDataSource in asp.net
Part 5 - Using XSLTTransform with xmldatasource control
Part 6 - AccessDataSource in asp.net



We will be using tblEmployee table for this demo. Use the script below to create employees table and populate it with sample data.

CREATE TABLE [tblEmployee]
(
 [EmployeeId] [int] IDENTITY PRIMARY KEY,
 [FirstName] [nvarchar](50),
 [LastName] [nvarchar](50),
 [DateOfBirth] [datetime],
 [AnnualSalary] [int],
 [Gender] [nvarchar](50),
 [DepartmentName] [nvarchar](50)
)



Insert into tblEmployee values
('John','Johnson','08/10/1982',55000,'Male','IT')
Insert into tblEmployee values
('Pam','Roberts','11/11/1979',62000,'Female','HR')
Insert into tblEmployee values
('David','John','01/15/1980',45000,'Male','IT')
Insert into tblEmployee values
('Rose','Mary','08/18/1967',78000,'Female','Payroll')
Insert into tblEmployee values
('Mark','Mell','12/10/1974',69000,'Male','HR')

Use "SqlDataSource" control to retrieve data from the database and bind it to gridview control. The HTML of the webform, is shown below.
<div style="font-family: Arial">
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:DBConnectionString %>"
    SelectCommand="SELECT * FROM [tblEmployee]"></asp:SqlDataSource>
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    DataKeyNames="EmployeeId" DataSourceID="SqlDataSource1">
    <Columns>
        <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId"
            InsertVisible="False" ReadOnly="True" SortExpression="EmployeeId" />
        <asp:BoundField DataField="FirstName" HeaderText="FirstName"
            SortExpression="FirstName" />
        <asp:BoundField DataField="LastName" HeaderText="LastName"
            SortExpression="LastName" />
        <asp:BoundField DataField="DateOfBirth" HeaderText="DateOfBirth"
            SortExpression="DateOfBirth" />
        <asp:BoundField DataField="AnnualSalary" HeaderText="AnnualSalary"
            SortExpression="AnnualSalary" />
        <asp:BoundField DataField="Gender" HeaderText="Gender"
            SortExpression="Gender" />
        <asp:BoundField DataField="DepartmentName" HeaderText="DepartmentName"
            SortExpression="DepartmentName" />
    </Columns>
</asp:GridView>
</div>

Notice that, by default, all the columns from the table are displayed in the gridview control. For example, I don't want to display EmployeeId column in the gridview control. There are 3 ways to achieve this
1. You can modify "SqlDataSource" control "SELECT" query, to include only the columns that you need.
2. Remove the "BoundField" column from gridview control that displays, EmployeeId
3. Set visibility property to false on the "BoundField" column from gridview control that displays, EmployeeId

Notice the column headers in the gridview control. They are same as the column names in the table. For example, we want a space between First and Name in FirstName. Similary, we want spaces for LastName, DateOfBirth, AnnualSalary and DepartmentName.
FirstName = First Name
LastName = Last Name
DateOfBirth = Date Of Birth
AnnualSalary = Annual Salary
DepartmentName = Department Name

To achieve this, change "HeaderText" property of the respective column, in the gridview control.

When you run the application, DateOfBirth column displays both Date and Time. I want only the "date" to be displayed. We can very easily achieve this using DataFormatString property as shown below. We have set DataFormatString="{0:d}", which will display short date pattern. Setting DataFormatString="{0:D}", will display long date pattern.
<asp:BoundField DataField="DateOfBirth" DataFormatString="{0:d}"
HeaderText="DateOfBirth" SortExpression="DateOfBirth" />

For the complete list of "Standard Date and Time Format Strings", please visit MSDN link below.
http://msdn.microsoft.com/en-gb/library/az4se3k1.aspx

For the annual salary column, I want to display currency symbol. For example, depending on your application requirement, you may want to display, either United States dollar ($), Great British Pound(£) or Indian Rupee Symbol. To achieve this use currency format string "0:c" as shown below. If you want to display 2 decimal places using "0:c2"
<asp:BoundField DataField="AnnualSalary" DataFormatString="{0:c}"
HeaderText="AnnualSalary" SortExpression="AnnualSalary" />

For the complete list of "Standard Numeric Format Strings", please visit MSDN link below.
http://msdn.microsoft.com/en-gb/library/dwhawy9k.aspx

Currency symbol will be displayed, based on the default culture setting. For example,
If your default culture setting is "en-US", United States Dollar symbol will be displayed
If your default culture setting is "en-GB", Great British Pound symbol will be displayed
If your default culture setting is "en-IN", Indian Rupee symbol will be displayed

Culture can be specified in web.config as shown below. This setting will be applicable for all pages in the asp.net web application.
<system.web>
  <globalization culture="en-US"/>
</system.web>

It is possible to override culture setting on a page-by-page basis. To set the culture declaratively in the aspx page, use "Culture" attribute of the "Page" directive as shown below.
<%@ Page Culture="en-GB" 

To override the culture programmatically
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-GB");

Formatting gridview using rowdatabound event - Part 8

Suggested Videos 
Part 5 - Using XSLTTransform with xmldatasource control
Part 6 - AccessDataSource in asp.net
Part 7 - Formatting asp.net gridview control



Please watch Part 7 of the asp.net gridview tutorial before proceeding with this video.

This video is in response to one of my youtube subscriber's question. The subscriber's question is How can we have multiple cultures for a single page. For example, each row of the grid displays a record for a person belonging to a different country.



Let us understand this with an example. I have a table, tblEmployee as shown below.


When, this data is displayed in a gridview control, we want the country specific currency symbol to be displayed, next to their salary. The output should be as shown below.


HTML of WebForm1.aspx:
<div style="font-family:Arial">
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:DBConnectionString %>"
    SelectCommand="SELECT * FROM [tblEmployee]"></asp:SqlDataSource>
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    DataKeyNames="EmployeeId" DataSourceID="SqlDataSource1"
    BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px"
    CellPadding="3" CellSpacing="2" onrowdatabound="GridView1_RowDataBound">
    <Columns>
        <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId"
            InsertVisible="False" ReadOnly="True" SortExpression="EmployeeId" />
        <asp:BoundField DataField="FirstName" HeaderText="FirstName"
            SortExpression="FirstName" />
        <asp:BoundField DataField="AnnualSalary" HeaderText="AnnualSalary"
            SortExpression="AnnualSalary"/>
        <asp:BoundField DataField="Country" HeaderText="Country"
            SortExpression="Country" />
    </Columns>
    <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
    <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
    <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
    <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
    <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
    <SortedAscendingCellStyle BackColor="#FFF1D4" />
    <SortedAscendingHeaderStyle BackColor="#B95C30" />
    <SortedDescendingCellStyle BackColor="#F1E5CE" />
    <SortedDescendingHeaderStyle BackColor="#93451F" />
</asp:GridView>
</div>

To achieve this, we can make use of RowDataBound event of GridView control. RowDataBound event is raised when a record in the datasource is bound to a row in gridview control. Please note that in RowDataBound event, we are first checking, if the row, that is being bound, is a datarow. This event is also raised for other row types like Header, Footer etc.
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    // Check if the row that is being bound, is a datarow
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        if (e.Row.Cells[3].Text == "US")
        {
            int salary = Convert.ToInt32(e.Row.Cells[2].Text);
            string formattedString = string.Format(new System.Globalization.CultureInfo("en-US"), "{0:c}", salary);
            e.Row.Cells[2].Text = formattedString;
        }
        else if (e.Row.Cells[3].Text == "UK")
        {
            int salary = Convert.ToInt32(e.Row.Cells[2].Text);
            string formattedString = string.Format(new System.Globalization.CultureInfo("en-GB"), "{0:c}", salary);
            e.Row.Cells[2].Text = formattedString;
        }
        else if (e.Row.Cells[3].Text == "India")
        {
            int salary = Convert.ToInt32(e.Row.Cells[2].Text);
            string formattedString = string.Format(new System.Globalization.CultureInfo("en-IN"), "{0:c}", salary);
            e.Row.Cells[2].Text = formattedString;
        }
        else if (e.Row.Cells[3].Text == "South Africa")
        {
            int salary = Convert.ToInt32(e.Row.Cells[2].Text);
            string formattedString = string.Format(new System.Globalization.CultureInfo("en-ZA"), "{0:c}", salary);
            e.Row.Cells[2].Text = formattedString;
        }
        else if (e.Row.Cells[3].Text == "Malaysia")
        {
            int salary = Convert.ToInt32(e.Row.Cells[2].Text);
            string formattedString = string.Format(new System.Globalization.CultureInfo("en-MY"), "{0:c}", salary);
            e.Row.Cells[2].Text = formattedString;
        }
    }
}

Use the code below, to get the list of all supported cultures in asp.net
foreach (CultureInfo ci in CultureInfo.GetCultures(CultureTypes.AllCultures))
{
    Response.Write(ci.Name + " => " + ci.DisplayName + "<br/>");
}

The list of countries and their currencies can be found at the following wikipedia article
http://en.wikipedia.org/wiki/List_of_circulating_currencies

Formatting gridview based on row data - Part 9

Suggested Videos 
Part 6 - AccessDataSource in asp.net
Part 7 - Formatting asp.net gridview control
Part 8 - Formatting gridview using rowdatabound event



Please watch Part 8 of the asp.net gridview tutorial before proceeding with this video. I have tblEmployee table, as shown below.




Now, we want to retrieve and display above data in a gridview control. The data in the gridview control, should be formatted as shown in the image below. Notice that, based on the country culture, we need to format and display AnnualSalary column with correct currency symbol. The important thing is that, Culture column should not visible to the end user in the gridview control.


WebForm1.aspx HTML:
<div style="font-family:Arial">
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:DBConnectionString %>"
    SelectCommand="SELECT * FROM [tblEmployee]"></asp:SqlDataSource>
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    DataKeyNames="EmployeeId" DataSourceID="SqlDataSource1"
    onrowdatabound="GridView1_RowDataBound">
    <Columns>
        <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId"
            InsertVisible="False" ReadOnly="True" SortExpression="EmployeeId" />
        <asp:BoundField DataField="FirstName" HeaderText="FirstName"
            SortExpression="FirstName" />
        <asp:BoundField DataField="AnnualSalary" HeaderText="AnnualSalary"
            SortExpression="AnnualSalary" />
        <asp:BoundField DataField="Country" HeaderText="Country"
            SortExpression="Country" />
        <asp:BoundField DataField="CountryCulture" HeaderText="CountryCulture"
            SortExpression="CountryCulture" />
    </Columns>
</asp:GridView>
</div>

RowDataBound event of the GridView control can be used to achieve this very easily. RowDataBound event is raised every time, a row from the datasource is bound to row in the gridview control. In row RowDataBound event retrieve country culture information and turn off the visibility of CountryCulture cell. Make sure to hide CountryCulture header table cell as well.
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    // If the row being databound is a header row
    if (e.Row.RowType == DataControlRowType.Header)
    {
        // Hide CountryCulture header table cell 
        e.Row.Cells[4].Visible = false;
    }
    // If the row being databound is a data row
    else if (e.Row.RowType == DataControlRowType.DataRow)
    {
        // Retrieve the unformatted salary
        int salary = Convert.ToInt32(e.Row.Cells[2].Text);
        // Retrieve the culture 
        string countryCulture = e.Row.Cells[4].Text;
        // Turn off the visibility of CountryCulture cell
        e.Row.Cells[4].Visible = false;
        // Format the currency using specific country culture
        string formattedString = string.Format
            (new System.Globalization.CultureInfo(countryCulture), "{0:c}", salary);
        // Finally set the formatted currency as text for 
        // display purpose in the gridview control
        e.Row.Cells[2].Text = formattedString;
    }
}

Run the application now, and notice that, the data is displayed as expected. If you are using Google chrome browser, right click on the page, and select "View Page Source". Notice that, in the page source, the table cell (TD) for countryCulture is not rendered.

You can also use, "Display:None" style to achieve the same thing. There are 4 steps to follow.
Step 1. First, add the following CSS class. It's always a good practise, to define all the styles in a stylesheet. Add a stylesheet to your asp.net project, and copy and paste the following style class.
.DisplayNone
{
    display: none
}

Step 2. Drag and drop the stylesheet on the webform. This should generate a reference to the stylesheet, using "link" element as shown below.
<link href="Styles/Site.css" rel="stylesheet" type="text/css" />

Step 3. Make the following changes to the boundcolumn, that displays "CountryCulture". Notice that, ItemStyle-CssClass and HeaderStyle-CssClass is set to "DisplayNone"
<asp:BoundField DataField="CountryCulture" HeaderText="CountryCulture" 
SortExpression="CountryCulture" ItemStyle-CssClass="DisplayNone" 
HeaderStyle-CssClass="DisplayNone" />

Step 4: Finally in the code-behind file, we can get rid of the code that turns off the visibility of "CountryCulture" cell in both the header and datarow. The modified code is shown below
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        int salary = Convert.ToInt32(e.Row.Cells[2].Text);
        string countryCulture = e.Row.Cells[4].Text;
        string formattedString = string.Format
            (new System.Globalization.CultureInfo(countryCulture), "{0:c}", salary);
        e.Row.Cells[2].Text = formattedString;
    }
}

Run the application now, and notice that, the data is displayed as expected. Now, right click on the page, and select "View Page Source". Notice that, in the page source, the table cell (TD) for countryCulture is rendered, with class="DisplayNone". So, when we use "display: none" the data gets rendered, but will not be visible in the control to the end user.

What is the difference between Visible=False and Display:None? 
Visible=False will not render data, where as Display:None will render data , but will not be visible in the control.

Design time and runtime formatting of gridview - Part 10

Suggested Videos 
Part 7 - Formatting asp.net gridview control
Part 8 - Formatting gridview using rowdatabound event
Part 9 - Formatting gridview based on row data



GridView control provides several inbuilt styles to format different sections of the gridview. The following are some of the style properties and the sections they can format.
ControlStyle - Use to apply the formatting on the entire gridview control
RowStyle - Use to format all rows in the gridview
AlternatingRowStyle - Use to format all alternating rows in the gridview
HeaderStyle - Use to format the header of the gridview control
FooterStyle - Use to format the footer of the gridview control
EditRowStyle - Use to format when the row is in edit mode.



By default, the footer of the girdview control is not visible. To show the footer, set "ShowFooter" property of the gridview control to true.

If you are not very good at designing, then you may use "AutoFormat" feature of the gridview control.

These styles can be applied declaratively at design time or dynamically at runtime based on the underlying data. At design time, the styles can be applied using properties window or directly in the HTML source.

Now, let us understand changing the styles dynamically based on underlying data. I have tblEmployee table, as shown below.


I want to display this data in a gridview control. All the employee rows, with salary greater than 70,000 should have a "RED" background color and white font colour. Obviously this cannot be done at design time. At runtime, we need to check the value in "AnnualSalary" column for each row, and if it is greater than 70,000, then we need to set the "BackColor" property of that row to "Red". This can be very easily achieved using RowDataBound event. The output should be as shown below.


protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    // Loop thru each datarow in the gridview
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        // You can also retrieve salary using cell index in the row
        // Avoid using index, as this may not work correctly if the
        // order of columns is changed in the gridview control
        // int salary = Convert.ToInt32(e.Row.Cells[2].Text);
        // Retrieve salary
        int salary = Convert.ToInt32
            (DataBinder.Eval(e.Row.DataItem, "AnnualSalary"));
        // If Salary > 70000, set your styles
        if (salary > 70000)
        {
            e.Row.BackColor = System.Drawing.Color.Red;
            e.Row.ForeColor = System.Drawing.Color.White;
        }
    }
}

Using stored procedures with sqldatasource control - Part 11

Suggested Videos
Part 8 - Formatting gridview using rowdatabound event
Part 9 - Formatting gridview based on row data
Part 10 - Design time and runtime formatting of gridview



In this video we will discuss about using a stored procedure with sqldatasource control, to retrieve and display data in a gridview control. First let's create required tables and populate them with sample data.

Create Table tblDepartment
(
 DepartmentId int primary key identity,
 Name nvarchar(50)
)



Insert into tblDepartment values ('IT')
Insert into tblDepartment values ('HR')
Insert into tblDepartment values ('Payroll')
Insert into tblDepartment values ('Administration')

Create Table tblEmployee
(
 EmployeeId int Primary key identity,
 Name nvarchar(50),
 DeptId int Foreign key references tblDepartment(DepartmentId)
)

Insert into tblEmployee values ('Mark',1)
Insert into tblEmployee values ('John',1)
Insert into tblEmployee values ('Mary',3)
Insert into tblEmployee values ('Mike',4)
Insert into tblEmployee values ('Pam',3)
Insert into tblEmployee values ('David',1)
Insert into tblEmployee values ('Tom',2)
Insert into tblEmployee values ('Jason',4)
Insert into tblEmployee values ('Vicky',3)
Insert into tblEmployee values ('Todd',1)
Insert into tblEmployee values ('Ben',2)

Stored procedure to retrieve department Ids and Names
Create Procedure spGetDepartments
as
Begin
 Select DepartmentId, Name from tblDepartment
End

Stored procedure to retrieve employees by department
Create Procedure spGetEmployeesByDepartmentId
@DepartmentId int
as
Begin
 Select EmployeeId, tblEmployee.Name as EmployeeName,
                    tblDepartment.Name as DepartmentName
 from tblEmployee
 join tblDepartment
 on tblDepartment.DepartmentId = tblEmployee.DeptId
 where tblDepartment.DepartmentId = @DepartmentId
End

Create an asp.net web application. Drag and drop 2 sqldatasource controls, a dropdownlist and a gridview control on WebForm1.aspx.

Configure "SqlDataSource1" control to retrieve departments data using stored procedure "spGetDepartments"
1. Right click on "SqlDataSource1" control and select "Show Smart Tag"
2. Now click on "Configure Data Source" link
3. Select connection string, from the dropdownlist on "Choose your data connection" screen. You need to have a connection string specified in web.config file.
4. Click Next
5. Select "Specify a custom SQL statement or stored procedure" radio button on "Configure the Select statement" screen and click "Next"
6. Select stored procedure "spGetDepartments" from the dropdownlist on "Define custom statements or stored procedures" screen and click "Next"
7. On the subsequent screen, you can test the query, if you wish to.
8. Finally click Finish.

We are now done configuring "SqlDataSource1" control. 

Let's now configure "DropDownList1" to use "SqlDataSource1" control.
1. Right click on "DropDownList1" control and select "Show Smart Tag"
2. Now click on "Choose Data Source" link
3. Select "SqlDataSource1" from "Select a Data Source" dropdownlist
4. Select "Name" from "Select a data field to display in the dropdownlist"
5. Select "DepartmentId" from "Select a data field for the value of the dropdownlist" and click OK

At this point, if you run the application, all the departments should be displayed in the dropdownlist control

Now let us configure "SqlDataSource2" control, to retrieve employees by department.
1. Right click on "SqlDataSource2" control and select "Show Smart Tag" 
2. Now click on "Configure Data Source" link
3. Select connection string, from the dropdownlist on "Choose your data connection" screen. You need to have a connection string specified in web.config file.
4. Click Next
5. Select "Specify a custom SQL statement or stored procedure" radio button on "Configure the Select statement" screen and click "Next"
6. Select stored procedure "spGetEmployeesByDepartmentId" from the dropdownlist on "Define custom statements or stored procedures" screen and click "Next"
7. On "Define Parameters" screen, select "Control" from "Parameter Source" dropdownlist
8. Select "DropDownList1" from "ControlID" dropdownlist and click "Next"
9. On "Test Query" screen click "Finish"

Now let us associate "SqlDataSource2" control with "GridView1" control
1. Right click on "GridView1" control and select "Show Smart Tag"
2. Select "SqlDataSource2" from "Choose Data Source" dropdownlist

Finally set AutoPostBack property of DropDownList1 control to true, so that the webform will automatically postback to the server whenever the selection in the dropdownlist changes.

Run the application and notice that, as the selection in the dropdownlist changes, the data in gridview control also changes.

For your reference, here is the complete HTML of WebForm1.aspx
<div style="font-family:Arial">
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True"
    DataSourceID="SqlDataSource1" DataTextField="Name"
    DataValueField="DepartmentId">
</asp:DropDownList>
<br /><br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px"
    CellPadding="3" CellSpacing="2" DataKeyNames="EmployeeId"
    DataSourceID="SqlDataSource2">
    <Columns>
        <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId"
            InsertVisible="False" ReadOnly="True" SortExpression="EmployeeId" />
        <asp:BoundField DataField="EmployeeName" HeaderText="EmployeeName"
            SortExpression="EmployeeName" />
        <asp:BoundField DataField="DepartmentName" HeaderText="DepartmentName"
            SortExpression="DepartmentName" />
    </Columns>
    <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
    <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
    <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
    <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
    <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
    <SortedAscendingCellStyle BackColor="#FFF1D4" />
    <SortedAscendingHeaderStyle BackColor="#B95C30" />
    <SortedDescendingCellStyle BackColor="#F1E5CE" />
    <SortedDescendingHeaderStyle BackColor="#93451F" />
</asp:GridView>
<br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:SampleConnectionString %>"
    SelectCommand="spGetDepartments" SelectCommandType="StoredProcedure">
    </asp:SqlDataSource>
<br />
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
    ConnectionString="<%$ ConnectionStrings:SampleConnectionString %>"
    SelectCommand="spGetEmployeesByDepartmentId"
    SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:ControlParameter ControlID="DropDownList1" DefaultValue=""
            Name="DepartmentId" PropertyName="SelectedValue" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>
</div>

Using stored procedures with objectdatasource control - Part 12

Suggested Videos 
Part 9 - Formatting gridview based on row data
Part 10 - Design time and runtime formatting of gridview
Part 11 - Using stored procedures with sqldatasource control



In this video we will discuss about using a stored procedure with objectdatasource control, to retrieve and display data in a gridview control. Please watch Part 11 of asp.net gridview tuorial before proceeding with this video. We will be using same sql server tables and stored procedures that we used in Part 11 of asp.net gridview tuorial.



Create an asp.net web application. Add a class file with name, DepartmentDataAccessLayer.cs to the web application project. Copy and paste the following code in DepartmentDataAccessLayer.cs file.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
    //Business object to hold department related data
    public class Department
    {
        public int DepartmentId { get; set; }
        public string DepartmentName { get; set; }
    }
   
    public class DepartmentDataAccessLayer
    {
        //Method to return all departments
        public static List<Department> GetAllDepartments()
        {
            List<Department> listDepartments = new List<Department>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("spGetDepartments", con);
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Department department = new Department();
                    department.DepartmentId = Convert.ToInt32(rdr["DepartmentId"]);
                    department.DepartmentName = rdr["Name"].ToString();

                    listDepartments.Add(department);
                }
            }

            return listDepartments;
        }
    }
}

Now, add another class file with name, EmployeeDataAccessLayer.cs. Copy and paste the following code in this file.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
    //Business object to hold employee related data
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string EmployeeName { get; set; }
        public string DepartmentName { get; set; }
    }
   
    public class EmployeeDataAccessLayer
    {
        //Method to return all employees
        public static List<Employee> GetEmployeesByDepartment(int departmentId)
        {
            List<Employee> listEmployees = new List<Employee>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("spGetEmployeesByDepartmentId", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter parameter = new SqlParameter("@DepartmentId", departmentId);
                cmd.Parameters.Add(parameter);
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Employee employee = new Employee();
                    employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
                    employee.EmployeeName = rdr["EmployeeName"].ToString();
                    employee.DepartmentName = rdr["DepartmentName"].ToString();

                    listEmployees.Add(employee);
                }
            }

            return listEmployees;
        }
    }
}

Right click on the solution in solution explorer, and select "Build Solution", so that the classes we added are compiled.

Drag and drop 2 objectdatasource controls, a dropdownlist and a gridview control on WebForm1.aspx.

Configure "ObjectDataSource1" control to retrieve departments data
1. Right click on "ObjectDataSource1" control and select "Show Smart Tag"
2. Now click on "Configure Data Source" link
3. Select "Demo.DepartmentDataAccessLayer" from "Choose your business object" dropdownlist and click "Next"
4. Select "GetAllDepartments()" from "Choose a method" dropdownlist and click finish

We are now done configuring "ObjectDataSource1" control. 

Let's now configure "DropDownList1" to use "ObjectDataSource1" control.
1. Right click on "DropDownList1" control and select "Show Smart Tag"
2. Now click on "Choose Data Source" link
3. Select "ObjectDataSource1" from "Select a Data Source" dropdownlist
4. Select "DepartmentName" from "Select a data field to display in the dropdownlist"
5. Select "DepartmentId" from "Select a data field for the value of the dropdownlist" and click OK

At this point, if you run the application, all the departments should be displayed in the dropdownlist control

Now let us configure "ObjectDataSource2" control, to retrieve employees by department.
1. Right click on "ObjectDataSource2" control and select "Show Smart Tag"
2. Now click on "Configure Data Source" link
3. Select "Demo.EmployeeDataAccessLayer" from "Choose your business object" dropdownlist and click "Next"
4. Select "GetEmployeesByDepartment()" from "Choose a method" dropdownlist and click "Next"
5. On "Define Parameters" screen, select "Control" from "Parameter Source" dropdownlist
6. Select "DropDownList1" from "ControlID" dropdownlist and click "Finish"

Now let us associate "ObjectDataSource2" control with "GridView1" control
1. Right click on "GridView1" control and select "Show Smart Tag"
2. Select "ObjectDataSource2" from "Choose Data Source" dropdownlist

Finally set AutoPostBack property of DropDownList1 control to true, so that the webform will automatically postback to the server whenever the selection in the dropdownlist changes.

Run the application and notice that, as the selection in the dropdownlist changes, the data in gridview control also changes.

For your reference, here is the complete HTML of WebForm1.aspx
<div style="font-family:Arial">
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true"
    DataSourceID="ObjectDataSource1" DataTextField="DepartmentName"
    DataValueField="DepartmentId">
</asp:DropDownList>
<br /><br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px"
    CellPadding="3" CellSpacing="2" DataSourceID="ObjectDataSource2">
    <Columns>
        <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId"
            SortExpression="EmployeeId" />
        <asp:BoundField DataField="EmployeeName" HeaderText="EmployeeName"
            SortExpression="EmployeeName" />
        <asp:BoundField DataField="DepartmentName" HeaderText="DepartmentName"
            SortExpression="DepartmentName" />
    </Columns>
    <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
    <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
    <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
    <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
    <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
    <SortedAscendingCellStyle BackColor="#FFF1D4" />
    <SortedAscendingHeaderStyle BackColor="#B95C30" />
    <SortedDescendingCellStyle BackColor="#F1E5CE" />
    <SortedDescendingHeaderStyle BackColor="#93451F" />
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
    SelectMethod="GetAllDepartments" TypeName="Demo.DepartmentDataAccessLayer">
    </asp:ObjectDataSource>
<asp:ObjectDataSource ID="ObjectDataSource2" runat="server"
    SelectMethod="GetEmployeesByDepartment" TypeName="Demo.EmployeeDataAccessLayer">
    <SelectParameters>
        <asp:ControlParameter ControlID="DropDownList1" Name="departmentId"
            PropertyName="SelectedValue" Type="Int32" />
    </SelectParameters>
</asp:ObjectDataSource>
</div>

Deleting data from gridview using sqldatasource control - Part 13

Suggested Videos 
Part 10 - Design time and runtime formatting of gridview
Part 11 - Using stored procedures with sqldatasource control
Part 12 - Using stored procedures with objectdatasource control



In this video we will discuss about deleting data from gridview control using sqldatasource control.

We will be using tblEmployee table for this demo. Please use the SQL script below to create and populate this table.
Create Table tblEmployee
(
 EmployeeId int Primary key identity,
 Name nvarchar(50),
 Gender nvarchar(10),
 City nvarchar(50)
)



Insert into tblEmployee values ('Mark','Male','London')
Insert into tblEmployee values ('John','Male','Chennai')
Insert into tblEmployee values ('Mary','Female','New York')
Insert into tblEmployee values ('Mike','Male','Sydeny')
Insert into tblEmployee values ('Pam','Female','Toronto')
Insert into tblEmployee values ('David','Male','Sydeny')

Drag and drop sqldatasource control on WebForm1.aspx.

Configure "SqlDataSource1" control 
1. Right click on "SqlDataSource1" control and select "Show Smart Tag"
2. Now click on "Configure Data Source" link
3. Select connection string, from the dropdownlist on "Choose your data connection" screen. You need to have a connection string specified in web.config file.
4. Click Next
5. On "Configure the Select Statement" screen, select "tblEmployee" table from dropdownlist.
6. Click on "Advanced" button
7. Select "Generate INSERT, UPDATE and DELETE statements" checkbox and click OK
8. Click Next and Finish

Now flip "WebForm1.aspx" to "HTML Source" mode. Notice that, the wizard has automatically generated INSERT, UPDATE and DELETE statements. Since, we only want to enable the gridview control to delete data, get rid of InsertCommand, InsertParameters, UpdateCommand, and  UpdateParameters.

Drag and drop gridview control on WebForm1.aspx. Now let us associate "SqlDataSource1" control with "GridView1" control
1. Right click on "GridView1" control and select "Show Smart Tag"
2. Select "SqlDataSource1" from "Choose Data Source" dropdownlist
3. Select "Enable Deleting" checkbox. At this point "Delete" button should appear on the gridview control.

Run the application. Click "Delete" button. The row gets deleted without any confirmation. Since deleting data cannot be undone, it is better to show a confirmation, before we delete the row.

To show the confirmation, before a row is deleted, we need to associate javascript with the delete "LinkButton". To do this
1. Right click on the gridview control and select "Properties"
2. In the "Properties" window click on events button
3. Double click on RowDataBound event. This should generate the event handler method in the code-behind file. Copy and paste the following code
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        Control control = e.Row.Cells[0].Controls[0];
        if (control is LinkButton)
        {
            ((LinkButton)control).OnClientClick =
                "return confirm('Are you sure you want to delete? This cannot be undone.');";
        }
    }
}

Run the application, and when you click the "Delete" button, we should get a confirmation dialog box. When you click OK the row will be deleted, and when you click "Cancel" nothing happens.

ConflictDetection property of SqlDataSource control - Part 14

Suggested Videos 
Part 11 - Using stored procedures with sqldatasource control
Part 12 - Using stored procedures with objectdatasource control
Part 13 - Deleting data from gridview using sqldatasource control



In this video we will discuss about ConflictDetection property of SqlDataSource control. Please watch Part 13 of asp.net gridview tutorial before proceeding. In this example, we will be using tblEmployee table that we created in Part 13.

Drag and drop a GridView, Label and a SqlDataSource datasource control on WebForm1.aspx. Flip WebForm1.aspx to source mode and change the "ID" of Label control from "Label1" to "lblMessage". Flip WebForm1.aspx to design mode.



Now let's configure "SqlDataSource1" control 
1. Right click on "SqlDataSource1" control and select "Show Smart Tag"
2. Now click on "Configure Data Source" link
3. Select connection string, from the dropdownlist on "Choose your data connection" screen. You need to have a connection string specified in web.config file.
4. Click Next
5. On "Configure the Select Statement" screen, select "tblEmployee" table from dropdownlist.
6. Click on "Advanced" button
7. Make sure you check CheckBoxes next to "Generate INSERT, UPDATE and DELETE statements" and "Use Optimistic Concurrency". Click OK.
8. Click Next and Finish

Now flip "WebForm1.aspx" to "HTML Source" mode. Notice that, the wizard has automatically generated INSERT, UPDATE and DELETE statements. Since, we only want to enable the gridview control to delete data, get rid of InsertCommand, InsertParameters, UpdateCommand, and  UpdateParameters.

Since we have selected "Use Optimistic Concurrency" when configuring "SqlDataSource1" control, the generated "DeleteCommand" compares the row data with original values.
DeleteCommand="DELETE FROM [tblEmployee] WHERE [EmployeeId] = @original_EmployeeId AND (([Name] = @original_Name) OR ([Name] IS NULL AND @original_Name IS NULL)) AND (([Gender] = @original_Gender) OR ([Gender] IS NULL AND @original_Gender IS NULL)) AND (([City] = @original_City) OR ([City] IS NULL AND @original_City IS NULL))"

Also, ConflictDetection property is set to "CompareAllValues".

Had we not selected "Use Optimistic Concurrency" checkbox, the generated "DeleteCommand" would have been as shown below.
DeleteCommand="DELETE FROM [tblEmployee] WHERE [EmployeeId] = @original_EmployeeId"

When Optimistic Concurrency is not enabled, ConflictDetection property is set to "OverwriteChanges".

So, when optimistic concurrency option is used, and when we try to delete or update a row thru gridview control, then all the columns of the row that is being deleted are compared to check if the data has changed since the row was loaded into the gridview control. If data has changed, the row will not be deleted, and the gridview control simply refreshes with new data.

Now let us associate "SqlDataSource1" control with "GridView1" control
1. Right click on "GridView1" control and select "Show Smart Tag"
2. Select "SqlDataSource1" from "Choose Data Source" dropdownlist
3. Select "Enable Deleting" checkbox. At this point "Delete" button should appear on the gridview control.

Run the application. Click "Delete" button. The row gets deleted as expected.

Now execute the following SQL statement to update "Name" from "John" to "Johny" for employee with EmployeeId=2
Update tblemployee set Name='Johny' where EmployeeId = 2

At this point in the gridview control, row with EmployeeId=2, still shows the name as "John". Now, click Delete button in this row. Notice that, the row is not deleted and the grid gets refreshed with new data. This is because, we are using optmistic concurrency option and the data for this row has changed in the database table, after it was loaded into the gridview control.

If the row data has changed, and when that row is not deleted, then I want to display a message in a label control stating - "Employee Row with EmployeeID =  2 is not deleted due to data conflict"

To achieve this, we can use "RowDeleted" event of the gridview control. This event is raised after a row is deleted from the gridview control. Now let's generate the event handler method for "RowDeleted" event.
1. Right click on the gridview control and select "Properties"
2. In the "Properties" window click on events button
3. Double click on RowDeleted event. This should generate the event handler method in the code-behind file. Copy and paste the following code.
protected void GridView1_RowDeleted(object sender, GridViewDeletedEventArgs e)
{
    lblMessage.Visible = true;
    // AffectedRows property will be zero, if no rows are deleted
    if (e.AffectedRows > 0)
    {
        lblMessage.Text = "Employee row with EmployeeID = \"" 
            + e.Keys[0].ToString() + "\" is successfully deleted";
        lblMessage.ForeColor = System.Drawing.Color.Navy;
    }
    else
    {
        lblMessage.Text = "Employee Row with EmployeeID = \"" 
            + e.Keys[0].ToString() + "\" is not deleted due to data conflict";
        lblMessage.ForeColor = System.Drawing.Color.Red;
    }
}

Run the application. After the data is loaded into gridview control, update any row in the database table, and try to delete that, same row in the gridview control.  You should get the data conflict message as expected

Deleting data from gridview using objectdatasource control - Part 15

Suggested Videos 
Part 12 - Using stored procedures with objectdatasource control
Part 13 - Deleting data from gridview using sqldatasource control
Part 14 - ConflictDetection property of SqlDataSource control



We will be using tblEmployee table for this demo. For SQL script to create and populate this table, please refer to, Part 13 - Deleting data from gridview using sqldatasource control of asp.net gridview tutorial.





Steps to delete from gridview using objectdatasource control the following are the steps
1. Create EmployeeDataAccessLayer class
2. Create Employee business object in EmployeeDataAccessLayer.cs file
3. Add a static method to select all employees in EmployeeDataAccessLayer class
4. Add a static method to delete employee record using EmployeeId, in EmployeeDataAccessLayer class
5. Configure objectdatasource and gridview control.
6. Set "EmployeeId" as the value for DataKeyNames property of the gridview control.

Now let us look at the steps in detail
Step 1: Create EmployeeDataAccessLayer class
Right click on the web application project and add a class file with name EmployeeDataAccessLayer.cs

Step 2: Create Employee business object in EmployeeDataAccessLayer.cs file
public class Employee
{
    public int EmployeeId { get; set; }
    public string Name { get; set; }
    public string Gender { get; set; }
    public string City { get; set; }
}

Step 3: Add a static method to select all employees in EmployeeDataAccessLayer class
public static List<Employee> GetAllEmployees()
{
    List<Employee> listEmployees = new List<Employee>();

    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    using (SqlConnection con = new SqlConnection(CS))
    {
        SqlCommand cmd = new SqlCommand("Select * from tblEmployee", con);
        con.Open();
        SqlDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            Employee employee = new Employee();
            employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
            employee.Name = rdr["Name"].ToString();
            employee.Gender = rdr["Gender"].ToString();
            employee.City = rdr["City"].ToString();

            listEmployees.Add(employee);
        }
    }

    return listEmployees;
}

Step 4: Add a static method to delete employee record using EmployeeId, in EmployeeDataAccessLayer class
public static void DeleteEmployee(int EmployeeId)
{
    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    using (SqlConnection con = new SqlConnection(CS))
    {
        SqlCommand cmd = new SqlCommand("Delete from tblEmployee where EmployeeId = @EmployeeId", con);
        SqlParameter param = new SqlParameter("@EmployeeId", EmployeeId);
        cmd.Parameters.Add(param);
        con.Open();
        cmd.ExecuteNonQuery();
    }
}

Step 5: Configure objectdatasource and gridview control.
Compile the project. If the project is not compiled, EmployeeDataAccessLayer class may not show up in the wizard when configuring objectdatasource control.
1. Right click on "ObjectDataSource1" control and select "Show Smart Tag"
2. Now click on "Configure Data Source" link
3. Select "EmployeeDataAccessLayer" class from "Choose your business object dropdownlist" and click next
4. On "Define Data Methods" screen, select "GetAllEmployees" method
5. Now click on "DELETE" tab and select "DeleteEmployee" method and click Finish

Now, associate "ObjectDataSource1" with "GridView1"

Step 6: Set "EmployeeId" as the value for DataKeyNames property of the gridview control.
This can be done in HTML or code. Since, EmployeeId is the primary key, we need to set it as the value for "DataKeyNames" property of the gridview control, otherwise deleting does not work.

Setting DataKeyNames property of the gridview control in HTML
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
DataKeyNames="EmployeeId" DataSourceID="ObjectDataSource1">

Setting DataKeyNames property of the gridview control in code
GridView1.DataKeyNames = new string[1] { "EmployeeId" };

ConflictDetection property of objectdatasource control - Part 16

Suggested Videos 
Part 13 - Deleting data from gridview using sqldatasource control
Part 14 - ConflictDetection property of SqlDataSource control
Part 15 - Deleting data from gridview using objectdatasource control



In Part 15 of the asp.net gridview tutorial we discussed about deleting data from gridview control using objectdatasource control. We did not discuss about concurrency in Part 15. Let me explain what I mean.
1. When you access the webform, data is loaded into gridview control.
2. While you are looking at this data in the gridview control, someone else has updated a row in the database table
3. Now, in the gridview, when you click the delete button on the row that has changed, the row gets deleted.



This may be fine in most cases. However, let's say we don't want to allow the row to be deleted, in case if it has changed, then, we can make use of ConflictDetection property of the objectdatasource control.

We will be modifying the "DeleteEmployee()" method in EmployeeDataAccessLayer.cs file. We discussed about this in Part 15. Please change the implementation of DeleteEmployee() method as shown below. Notice that the ORIGINAL EmployeeId, Name, Gender and City are now passed as parameters to the DeleteEmployee() method. These parameters are then used in the "DELETE" query, to check if the data has changed after it was loaded into the gridview control.
public static void DeleteEmployee(int original_EmployeeId, string original_Name,
    string original_Gender, string original_City)
{
    string CS = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(CS))
    {
        string deleteQuery = "DELETE FROM tblEmployee WHERE EmployeeId = @original_EmployeeId " + "AND Name = @original_Name AND Gender = @original_Gender AND City = @original_City";
        SqlCommand cmd = new SqlCommand(deleteQuery, con);
        SqlParameter paramEmployeeId = new SqlParameter("@original_EmployeeId", original_EmployeeId);
        cmd.Parameters.Add(paramEmployeeId);
        SqlParameter paramName = new SqlParameter("@original_Name", original_Name);
        cmd.Parameters.Add(paramName);
        SqlParameter paramGender = new SqlParameter("@original_Gender", original_Gender);
        cmd.Parameters.Add(paramGender);
        SqlParameter paramCity = new SqlParameter("@original_City", original_City);
        cmd.Parameters.Add(paramCity);
        con.Open();
        cmd.ExecuteNonQuery();
    }
}

Compile the project and re-configure ObjectDataSource1 control, to use the above method as it's DELETE method.

Finally on "ObjectDataSource1" control, set properties
ConflictDetection="CompareAllValues"
OldValuesParameterFormatString="original_{0}"

Setting ConflictDetection="CompareAllValues", will pass original values for EmployeeId, Name, Gender and City to DeleteEmployee() method.

Notice the parameters of the DeleteEmployee() method. All of them have a prefix of "original_". ObjectDataSource control uses "OldValuesParameterFormatString" property to figure out the exact name of the parameters for the original values. This is the reason we have set OldValuesParameterFormatString="original_{0}"
public static void DeleteEmployee(int original_EmployeeId, string original_Name, string original_Gender, string original_City)

At this point, the declarative HTML markup of gridview and objectdatasource control, should be as shown below.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="ObjectDataSource1">
    <Columns>
        <asp:CommandField ShowDeleteButton="True" />
        <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId" SortExpression="EmployeeId" />
        <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
        <asp:BoundField DataField="Gender" HeaderText="Gender" SortExpression="Gender" />
        <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetAllEmployees"
    TypeName="Demo.EmployeeDataAccessLayer" DeleteMethod="DeleteEmployee"
    OldValuesParameterFormatString="original_{0}"
    ConflictDetection="CompareAllValues">
    <DeleteParameters>
        <asp:Parameter Name="original_EmployeeId" Type="Int32" />
        <asp:Parameter Name="original_Name" Type="String" />
        <asp:Parameter Name="original_Gender" Type="String" />
        <asp:Parameter Name="original_City" Type="String" />
    </DeleteParameters>
</asp:ObjectDataSource>

Editing and updating data in gridview using sqldatasource control - Part 17

Suggested Videos 
Part 14 - ConflictDetection property of SqlDataSource control
Part 15 - Deleting data from gridview using objectdatasource control
Part 16 - ConflictDetection property of objectdatasource control



In this video we will discuss about editing and updating data in gridview control using sqldatasource control

We will be using tblEmployee table for this demo. SQL script to create and populate this table with sample data is available in Part 13 of asp.net gridview tutorial.

Create an asp.net web application. Drag and drop a sqldatasource control and a gridview control on webform1.aspx.



Configure "SqlDataSource1" control 
1. Right click on "SqlDataSource1" control and select "Show Smart Tag"
2. Now click on "Configure Data Source" link
3. Select connection string, from the dropdownlist on "Choose your data connection" screen. You need to have a connection string specified in web.config file.
4. Click Next
5. On "Configure the Select Statement" screen, select "tblEmployee" table from dropdownlist.
6. Click on "Advanced" button
7. Select "Generate INSERT, UPDATE and DELETE statements" checkbox and click OK
8. Click Next and Finish

Now flip "WebForm1.aspx" to "HTML Source" mode. Notice that, the wizard has automatically generated INSERT, UPDATE and DELETE statements. Since, we only want to enable the gridview control to eidt and update data, get rid of InsertCommand, InsertParameters, DeleteCommand, and  DeleteParameters.

Now let us associate "SqlDataSource1" control with "GridView1" control
1. Right click on "GridView1" control and select "Show Smart Tag"
2. Select "SqlDataSource1" from "Choose Data Source" dropdownlist
3. Select "Enable Editing" checkbox. At this point "Edit" button should appear on the gridview control.

Now run the application. Click "Edit" button. Notice that "Update" and "Cancel" buttons are displayed, when the row is in EDIT mode. Also, notice that except "EmployeeId", all the other columns display a TextBox control, so the user can change data. By default, bound fields display a textbox control as the editing interface. It makes more sense to display a dropdownlist control as the editing interface, for "Gender" and "City". We will discuss about doing this in a later video session.

EmployeeId column is the primary key, and it is unusal to change key column values. Notice the HTML of the gridview control. The bound column that displays "EmployeeId" is marked with ReadOnly="True". This is the reason EmployeeId is not editable, when the row is in EDIT mode.

1. Click "Edit" button in a row
2. Delete the value in "Name" column
3. Click "Update"
4. Check the data for this column in the database.

Notice that NULL is stored. This is because, "BoundField" and "UpdateParameters" has a property "ConvertEmptyStringToNull". This property is true by default. If you want to store an "EmptyString" instead of NULL, set this property to false.

This property need to be set at both places.
1. On the BoundField and
2. On the respective update parameter

Editing and updating data in gridview using objectdatasource control - Part 18

Suggested Videos 
Part 15 - Deleting data from gridview using objectdatasource control
Part 16 - ConflictDetection property of objectdatasource control
Part 17 - Editing and updating data in gridview using sqldatasource control



In this video we will discuss about editing and updating data in gridview control using objectdatasource control.

We will be using tblEmployee table for this demo. SQL script to create and populate this table with sample data is available in Part 13 of asp.net gridview tutorial.

Create an asp.net web application. Drag and drop a objectdatasource control and a gridview control on webform1.aspx.



Steps to update a row in gridview using objectdatasource control.
1. Create EmployeeDataAccessLayer class
2. Create Employee business object in EmployeeDataAccessLayer.cs file
3. Add a static method to select all employees in EmployeeDataAccessLayer class
4. Add a static method to update employee record using EmployeeId, in EmployeeDataAccessLayer class
5. Configure objectdatasource and gridview control.

Now let us look at the steps in detail.
Step 1: Create EmployeeDataAccessLayer class
Right click on the web application project and add a class file with name EmployeeDataAccessLayer.cs

Step 2: Create Employee business object in EmployeeDataAccessLayer.cs file
public class Employee
{
    public int EmployeeId { get; set; }
    public string Name { get; set; }
    public string Gender { get; set; }
    public string City { get; set; }
}

Step 3: Add a static method to select all employees in EmployeeDataAccessLayer class
public static List<Employee> GetAllEmployees()
{
    List<Employee> listEmployees = new List<Employee>();

    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    using (SqlConnection con = new SqlConnection(CS))
    {
        SqlCommand cmd = new SqlCommand("Select * from tblEmployee", con);
        con.Open();
        SqlDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            Employee employee = new Employee();
            employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
            employee.Name = rdr["Name"].ToString();
            employee.Gender = rdr["Gender"].ToString();
            employee.City = rdr["City"].ToString();

            listEmployees.Add(employee);
        }
    }

    return listEmployees;
}

Step 4: Add a static method to update employee record in EmployeeDataAccessLayer class
public static void UpdateEmployee(int EmployeeId, string Name,
                                    string Gender, string City)
{
    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    using (SqlConnection con = new SqlConnection(CS))
    {
        string updateQuery = "Update tblEmployee SET Name = @Name,  " +
            "Gender = @Gender, City = @City WHERE EmployeeId = @EmployeeId ";
        SqlCommand cmd = new SqlCommand(updateQuery, con);
        SqlParameter paramEmployeeId = new SqlParameter("@EmployeeId", EmployeeId);
        cmd.Parameters.Add(paramEmployeeId);
        SqlParameter paramName = new SqlParameter("@Name", Name);
        cmd.Parameters.Add(paramName);
        SqlParameter paramGender = new SqlParameter("@Gender", Gender);
        cmd.Parameters.Add(paramGender);
        SqlParameter paramCity = new SqlParameter("@City", City);
        cmd.Parameters.Add(paramCity);
        con.Open();
        cmd.ExecuteNonQuery();
    }
}

Step 5: Configure objectdatasource and gridview control.
Compile the project. If the project is not compiled, EmployeeDataAccessLayer class may not show up in the wizard when configuring objectdatasource control.
1. Right click on "ObjectDataSource1" control and select "Show Smart Tag"
2. Now click on "Configure Data Source" link
3. Select "EmployeeDataAccessLayer" class from "Choose your business object dropdownlist" and click Next
4. On "Define Data Methods" screen, select "GetAllEmployees" method
5. Now click on "UPDATE" tab and select "UpdateEmployee()" method and click Finish

Now, associate "ObjectDataSource1" with "GridView1" and make sure you select "Enable Editing" check box, in "GridView Tasks" pane.

Run the application and click "Edit" button. Notice that "EmployeeId" is also editable. This column is the primary key column and should not be allowed to change. If you don't want a specific column to be editable in gridview, set the column's "ReadOnly" attribute to "true". Once we set "EmployeeId" bound column's "ReadOnly" attribute to "true", the row is not updated. This is because, EmployeeId is passed as ZERO. To correct this issue, set GridView1 control's DataKeyNames="EmployeeId". After these 2 changes, the gridview's EDIT and UPDATE functionality should work as expected.

Here is the Complete HTML for your reference.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    DataSourceID="ObjectDataSource1" DataKeyNames="EmployeeId">
    <Columns>
        <asp:CommandField ShowEditButton="True" />
        <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId"
            SortExpression="EmployeeId" ReadOnly="true" />
        <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
        <asp:BoundField DataField="Gender" HeaderText="Gender"
            SortExpression="Gender" />
        <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
    SelectMethod="GetAllEmployees" TypeName="Demo.EmployeeDataAccessLayer"
    UpdateMethod="UpdateEmployee">
    <UpdateParameters>
        <asp:Parameter Name="EmployeeId" Type="Int32" />
        <asp:Parameter Name="Name" Type="String" />
        <asp:Parameter Name="Gender" Type="String" />
        <asp:Parameter Name="City" Type="String" />
    </UpdateParameters>
</asp:ObjectDataSource>

Using optimistic concurrency when editing data in gridview - Part 19

Suggested Videos 
Part 16 - ConflictDetection property of objectdatasource control
Part 17 - Editing and updating data in gridview using sqldatasource control
Part 18 - Editing and updating data in gridview using objectdatasource control



In Part 18 of the asp.net gridview tutorial we discussed the basics of editing and updating data in gridview control. We did not discuss about concurrency in Part 18. Let me explain what I mean.
1. When you access the webform, data is loaded into gridview control.
2. Now click on Edit, while you are still editing data, someone else has updated the same row in the underlying database table
3. Now, in the gridview, when you click UPDATE button on the row that has changed, the row gets overwritten with your new data. This may or may not be a problem, depending on the requirements of your application. But if your project requirement warrants that, the data should not be overwritten, if it has changed since it was loaded into gridview control, then we need to think about implementing optimistic concurrency.



We will be modifying the "UpdateEmployee()" method in EmployeeDataAccessLayer.cs file. We discussed about this in Part 18. Please change the implementation of UpdateEmployee() method as shown below. Notice that, along with the new values, the ORIGINAL EmployeeId, Name, Gender and City are also passed as parameters to the UpdateEmployee() method. These parameters are then used in the "UPDATE" query, to check if the data has changed after it was loaded into the gridview control.
public static void UpdateEmployee(int original_EmployeeId, string original_Name, string original_Gender, string original_City, string Name, string Gender, string City)
{
    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    using (SqlConnection con = new SqlConnection(CS))
    {
        string updateQuery = "Update tblEmployee SET Name = @Name,  " +
            "Gender = @Gender, City = @City WHERE EmployeeId = @original_EmployeeId " +
            "AND Name = @original_Name AND Gender = @original_Gender AND City = @original_City";
        SqlCommand cmd = new SqlCommand(updateQuery, con);
        SqlParameter paramOriginalEmployeeId = new SqlParameter("@original_EmployeeId", original_EmployeeId);
        cmd.Parameters.Add(paramOriginalEmployeeId);
        SqlParameter paramOriginalName = new SqlParameter("@original_Name", original_Name);
        cmd.Parameters.Add(paramOriginalName);
        SqlParameter paramOriginalGender = new SqlParameter("@original_Gender", original_Gender);
        cmd.Parameters.Add(paramOriginalGender);
        SqlParameter paramOriginalCity = new SqlParameter("@original_City", original_City);
        cmd.Parameters.Add(paramOriginalCity);
        SqlParameter paramName = new SqlParameter("@Name", Name);
        cmd.Parameters.Add(paramName);
        SqlParameter paramGender = new SqlParameter("@Gender", Gender);
        cmd.Parameters.Add(paramGender);
        SqlParameter paramCity = new SqlParameter("@City", City);
        cmd.Parameters.Add(paramCity);
        con.Open();
        cmd.ExecuteNonQuery();
    }
}

Compile the project and re-configure ObjectDataSource1 control, to use the above method as it's UPDATE method.

On "ObjectDataSource1" control, set properties
ConflictDetection = "CompareAllValues"
OldValuesParameterFormatString = "original_{0}"

Setting ConflictDetection="CompareAllValues", will pass original values for EmployeeId, Name, Gender and City to UpdateEmployee() method.

Notice the parameters of the UpdateEmployee() method. Some of them have a prefix of "original_". ObjectDataSource control uses "OldValuesParameterFormatString" property to figure out the exact name of the parameters for the original values. This is the reason we have set OldValuesParameterFormatString="original_{0}"
public static void UpdateEmployee(int original_EmployeeId, string original_Name,
string original_Gender, string original_City, string Name, string Gender, string City)

To make EmployeeId Non-Editable, set ReadOnly="true", on EmployeeId bound column
<asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId"
    SortExpression="EmployeeId" ReadOnly="true" />

Finally, on "GridView1" set DataKeyNames="EmployeeId"

With all the above changes, the declarative HTML markup of gridview and objectdatasource control, should be as shown below.
<div style="font-family:Arial">
    <asp:GridView ID="GridView1" runat="server"
    AutoGenerateColumns="False" BackColor="#DEBA84"
    BorderColor="#DEBA84" BorderStyle="None"
    BorderWidth="1px" CellPadding="3" CellSpacing="2"
    DataSourceID="ObjectDataSource1" DataKeyNames="EmployeeId">
        <Columns>
            <asp:CommandField ShowEditButton="True" />
            <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId"
                SortExpression="EmployeeId" ReadOnly="true" />
            <asp:BoundField DataField="Name" HeaderText="Name"
                SortExpression="Name" />
            <asp:BoundField DataField="Gender" HeaderText="Gender"
                SortExpression="Gender" />
            <asp:BoundField DataField="City" HeaderText="City"
                SortExpression="City" />
        </Columns>
        <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
        <HeaderStyle BackColor="#A55129" Font-Bold="True"
            ForeColor="White" />
        <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
        <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
        <SelectedRowStyle BackColor="#738A9C" Font-Bold="True"
            ForeColor="White" />
        <SortedAscendingCellStyle BackColor="#FFF1D4" />
        <SortedAscendingHeaderStyle BackColor="#B95C30" />
        <SortedDescendingCellStyle BackColor="#F1E5CE" />
        <SortedDescendingHeaderStyle BackColor="#93451F" />
    </asp:GridView>
    <br />
    <asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
        SelectMethod="GetAllEmployees" TypeName="Demo.EmployeeDataAccessLayer"
        UpdateMethod="UpdateEmployee"
        OldValuesParameterFormatString="original_{0}"
        ConflictDetection="CompareAllValues">
        <UpdateParameters>
            <asp:Parameter Name="original_EmployeeId" Type="Int32" />
            <asp:Parameter Name="original_Name" Type="String" />
            <asp:Parameter Name="original_Gender" Type="String" />
            <asp:Parameter Name="original_City" Type="String" />
            <asp:Parameter Name="Name" Type="String" />
            <asp:Parameter Name="Gender" Type="String" />
            <asp:Parameter Name="City" Type="String" />
        </UpdateParameters>
    </asp:ObjectDataSource>
</div>

Keep gridview in edit mode when update fails due to data conflict - Part 20

Suggested Videos 
Part 17 - Editing and updating data in gridview using sqldatasource control
Part 18 - Editing and updating data in gridview using objectdatasource control
Part 19 - Using optimistic concurrency when editing data in gridview



In this video, we will discuss about 
1. Diplaying appropriate status messages depending on the success of update.
2. If the update fails, leave the row in "Edit" mode.



Please watch Part 19 of asp.net gridview tutorial before proceeding.

If there are no concurrency conflicts and if the update succeeds, then we want to display successful status message as shown below.


On the other hand if there are data conflicts, and if the update fails, then we want to display failure status message as shown below. At the same time, we also want to retain the row in EDIT mode.


There are 3 steps to achieve this
Step 1: In EmployeeDataAccessLayer class, change the return type of UpdateEmployee() method from "void" to "int"
public static int UpdateEmployee(int original_EmployeeId, string original_Name, string original_Gender, string original_City, string Name, string Gender, string City)

ExecuteNonQuery() method of the command object, returns an integer indicating the number of rows updated. We want our UpdateEmployee() method to return this value.
So, instead of "cmd.ExecuteNonQuery()" use "return cmd.ExecuteNonQuery()"

Step 2: Copy and paste the following code in the "Updated" event handler of "ObjectDataSource1" control. This event is fired after the "Update" operation has completed, that is, after the execution of UpdateEmployee() method. Remember UpdateEmployee() method returns an integer back. Notice that we are checking if the "ReturnValue" is an integer. If it is an integer and greater than zero, then we are using the "RetunValue"(the value that is returned by UpdateEmployee() method) to initialize "AffectedRows" property. This "AffectedRows" property will be used in "GridView1_RowUpdated" event handler.
protected void ObjectDataSource1_Updated(object sender, ObjectDataSourceStatusEventArgs e)
{
    if (e.ReturnValue is int && (int)e.ReturnValue > 0)
    {
        e.AffectedRows = (int)e.ReturnValue;
    }
}

Step 3: Copy and paste the following code in the "RowUpdated" event handler of "GridView1" control. Notice that we are using "AffectedRows" property to determine, if the update has succeeded or not. Remember this is the property that we initialized in Step 2.
protected void GridView1_RowUpdated(object sender, GridViewUpdatedEventArgs e)
{
    if (e.AffectedRows < 1)
    {
        e.KeepInEditMode = true;
        lblMessage.Text = "Row with EmployeeId = " + e.Keys[0].ToString() +
            " is not update due to data conflict";
        lblMessage.ForeColor = System.Drawing.Color.Red;
    }
    else
    {
        lblMessage.Text = "Row with EmployeeId = " + e.Keys[0].ToString() +
            " is successfully updated";
        lblMessage.ForeColor = System.Drawing.Color.Navy;
    }
}

To make things clear
1. First UpdateEmployee() method gets executed, and this method returns an integer value indicating the number of rows updated.
2. Then "Updated" event of "ObjectDataSource1" control is invoked. In this event handler method, we retrieve the value returned by "UpdateEmployee()" and then use it to initialize "AffectedRows" property.
3. Finally "RowUpdated" event of "GridView1" control is invoked. In this event handler method, we use "AffectedRows" property to determine, if the update has succeeded or not.

Make sure to include a "Label" control on your webform with ID=lblMessage.

GridView TemplateField in asp.net - Part 21

Suggested Videos 
Part 18 - Editing and updating data in gridview using objectdatasource control
Part 19 - Using optimistic concurrency when editing data in gridview
Part 20 - Keep gridview in edit mode when update fails due to data conflict

In this video we will discuss about using TemplateField with GridView control when editing data. Please watch part 20 of asp.net gridview tutorial before proceeding.



By default, when editing data, a textbox is rendered for all the bound columns. It would make more sense to use dropdownlist as the editing interface for "Gender". To achieve this we have to use a "TemplateField" instead of "BoundField" for "Gender" column. This can be very easily achieved using the deisgner or directly thru the HTML source.




Step 1: Click on "Edit Columns" link from GridView Tasks pane


Step 2: Click on "Gender" from "Selected Fields" section, and then click on "Convert this field into a Template Field" link and finally click OK.


Step 3: At this point, you should be back on gridview tasks pane. Click on "Edit Templates" link


Step 4: From "Display" dropdownlist, select "EditItemTemplate". Replace the "TextBox" control with a "DropDownList".


Step 5: Now click on "Edit Items" link from "DropDownList Tasks" pane. Add the 3 listem items (Please Select, Male, Female) to the dropdownlist and click OK. Finally click on "End Template Editing" from GridView Tasks pane.


Now run the application and click on EDIT. Notice that, "Gender" DropDownList  always has "Please Select" as the selected item. This is incorrect. When the row is in "Edit" mode we want to have the correct "Gender" selected. To achieve this follow the instructions in Step 6 below.

Step 6: Now flip the webform to HTML source mode. Set "SelectedValue" property of dropdownlist as shown below. Steps 1 to 6 has converted, the gender bound field to template field. The template field HTML markup that is auto-generated is shown below. 


Now run the application and click on EDIT. Notice that a dropdownlist control is now used as editing interface for gender column in the gridview control.

Using validation controls with asp.net gridview when editing data - Part 22

Suggested Videos 
Part 19 - Using optimistic concurrency when editing data in gridview
Part 20 - Keep gridview in edit mode when update fails due to data conflict
Part 21 - GridView TemplateField in asp.net



Please watch Part 21 of the asp.net gridview tutorial before proceeding. We want to make "Gender" required field. When editing data in the gridview control, if a user has selected "Select Gender", then the user should not be able to submit the page. We should get "Gender is a required field" error message as shown in the screen shot below.




At the moment we do not have any validation in place when editing data in gridview control. If you have selected "Select Gender" from dropdownlist and submit the page, we get a crash with error message - String or binary data would be truncated.

This is because "Gender" column length in the database is nvrahcar(10). This size is not enough to store the word "Select Gender". To get rid of the error change the length to nvarchar(20) using the sql script below.
ALTER TABLE tblemployee
ALTER COLUMN Gender nvarchar(20)

Now, if you have selected "Select Gender" from dropdownlist and submit the page, the word "Select Gender" will be stored in the database table. We want to avoid this. We only want the page to be submitted, if the user has selected the gender - Male or Female.

Since we are already using a TemplateField to display Gender in the GridView control, this should be very easy to achieve. All we need to do is drag and drop a "RequiredFieldValidator" control next to the dropdownlist in EditItemTemplate. Then configure "RequiredFieldValidator" as shown below.
<asp:TemplateField HeaderText="Gender" SortExpression="Gender">
    <EditItemTemplate>
        <asp:DropDownList ID="DropDownList1" runat="server"
                          SelectedValue= '<%# Bind("Gender") %>'>
            <asp:ListItem>Select Gender</asp:ListItem>
            <asp:ListItem>Male</asp:ListItem>
            <asp:ListItem>Female</asp:ListItem>
        </asp:DropDownList>
        <asp:RequiredFieldValidator ID="RequiredFieldValidator1"
            runat="server" ErrorMessage="Gender is a required field"
            ControlToValidate="DropDownList1" InitialValue="Select Gender"
            Text="*" ForeColor="Red" >
        </asp:RequiredFieldValidator>
    </EditItemTemplate>
    <ItemTemplate>
        <asp:Label ID="Label1" runat="server" Text='<%# Bind("Gender") %>'>
        </asp:Label>
    </ItemTemplate>
</asp:TemplateField>

Finally drag and drop a "ValidationSummary" control on the webform, after the closing tag of GridView. Set ForeColor="Red" on "ValidationSummary" control.
<asp:ValidationSummary ID="ValidationSummary1" runat="server" ForeColor="Red" />

That's it. We are done. Run the application, and when you try to submit the page without selecting "Gender", you get a validation error message as expected. If you are new to validation controls, please watch videos on  validation controls from asp.net video tutorial.