Gridview Tutorial Series III

Drilldown and display hierarchical data in gridview without using datasource controls - Part 36

Suggested Videos 
Part 33 - Merging cells in gridview footer row 
Part 34 - Drilldown and display hierarchical data in gridview using sqldatasource control
Part 35 - Drilldown and display hierarchical data in gridview using objectdatasource control



In this video, we will discuss about drilling down and displaying hierarchical data in gridview controls without using any datasource control. We will be using the following 3 database tables for this demo.
1. tblContinents
2. tblCountries
3. tblCities



1. When the page loads, a gridview should display the list of all continents available in tblContinents table
2. When a "Select" button is clicked against a continent, all the countries belonging to the selected continent should be displayed in another gridview.
3. Along the same lines, when a "Select" button is clicked against a country, all the cities belonging to the selected country should be displayed in another gridview.


Use sql script from Part 34 by clicking here to create and populate the required tables.

To retrieve continents, countries and cities data and display in gridview without using datasource controls, we need to create respective data access layer class files. We discussed about this in Part 35. Click here if you need this code.

Now drag and drop 3 gridview controls on webform1.aspx.

Configure GridView1, to include 2 TemplateFields and 1 BoundField. 
1. The first TemplateField should contain an ItemTemplate, which includes a linkbutton, that the user can click.
2. Set the CommandName property of the LinkButton to SelectCountries.
3. The second TemplateField should contain an ItemTemplate, which includes a Label control, that binds to ContinentId column.
4. The BoundField get it's data from ContinentName column.
5. Set AutoGenerateColumns property of GridView1 to false.


Configure GridView2, to include 2 TemplateFields and 2 BoundFields. 
1. The first TemplateField should contain an ItemTemplate, which includes a linkbutton, that the user can click.
2. Set the CommandName property of the LinkButton to SelectCities.
3. The first TemplateField should contain an ItemTemplate, which includes a Label control, that binds to CountryId coulmn.
4. The first BoundField get it's data from CountryName column.
5. The second BoundField get it's data from ContinentId column.
6. Set AutoGenerateColumns property of GridView2 to false.
7. Set GridView2 property, DataKeyNames="CountryId".


GridView3 does not require any additonal configuration. Just the HTML as shown below.


Now generate RowCommand event handler methods for GridView1 and GridView2. At this point the HTML on WebForm1.aspx should be as shown below.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false"
    onrowcommand="GridView1_RowCommand">
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:LinkButton CommandName="SelectCountries"
                    ID="btnSelectCountries"
                    runat="server">Select</asp:LinkButton>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Continent Id">
            <ItemTemplate>
                <asp:Label ID="Label1" runat="server"
                    Text='<%# Bind("ContinentId") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="ContinentName"
            HeaderText="Continent Name" />
    </Columns>
</asp:GridView>
<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="false"
    onrowcommand="GridView2_RowCommand" DataKeyNames="CountryId">
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
            <asp:LinkButton CommandName="SelectCities" ID="btnSelectCities"
                runat="server">Select</asp:LinkButton>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Country Id">
            <ItemTemplate>
            <asp:Label ID="Label2" runat="server"
                Text='<%# Bind("CountryId") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="CountryName" HeaderText="Country Name" />
        <asp:BoundField DataField="ContinentId" HeaderText="Continent Id" />
    </Columns>
</asp:GridView>
<asp:GridView ID="GridView3" runat="server">
</asp:GridView>

Copy and paste the following code in webform1.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        GridView1.DataSource = ContinentDataAccessLayer.GetAllContinents();
        GridView1.DataBind();
    }
}

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
    if (e.CommandName == "SelectCountries")
    {
        int rowIndex = ((GridViewRow)((LinkButton)e.CommandSource).NamingContainer).RowIndex;
        Label lblContinent = (Label)GridView1.Rows[rowIndex].FindControl("Label1");
        int continentId = Convert.ToInt32(lblContinent.Text);

        GridView2.DataSource = CountryDataAccessLayer.GetCountriesByContinent(continentId);
        GridView2.DataBind();

        GridView1.SelectRow(rowIndex);

        if (GridView2.SelectedValue != null)
        {
            GridView3.DataSource = CityDataAccessLayer.GetCitiesByCountryId(Convert.ToInt32(GridView2.SelectedValue));
            GridView3.DataBind();
        }
    }
}

protected void GridView2_RowCommand(object sender, GridViewCommandEventArgs e)
{
    if (e.CommandName == "SelectCities")
    {
        int rowIndex = ((GridViewRow)((LinkButton)e.CommandSource).NamingContainer).RowIndex;
        Label lblCountry = (Label)GridView2.Rows[rowIndex].FindControl("Label2");
        int countryId = Convert.ToInt32(lblCountry.Text);

        GridView3.DataSource = CityDataAccessLayer.GetCitiesByCountryId(countryId);
        GridView3.DataBind();

        GridView2.SelectRow(rowIndex);
    }
}

Detailsview in asp.net - Part 37

Suggested Videos
Part 34 - Drilldown and display hierarchical data in gridview using sqldatasource control
Part 35 - Drilldown and display hierarchical data in gridview using objectdatasource control
Part 36 - Drilldown and display hierarchical data in gridview without using datasource controls



Just like gridview, detailsview is also a databound control. Detailsview is used to display one row at a time. Let us understand a practical use of details view with an example.

I have table tblEmployee with 10 columns. Use the sql script below to create and populate this table with sample data.
Create table tblEmployee
(
 Id int primary key identity(1,1),
 FirstName nvarchar(50),
 LastName nvarchar(50),
 City nvarchar(20),
 Gender nvarchar(10),
 DateOfBirth DateTime,
 Country nvarchar(10),
 Salary int,
 DateOfJoining DateTime,
 MaritalStatus nvarchar(20)
)



Insert into tblEmployee values ('Tom','Minst','Alexandria','Male','10/10/1980','USA',5000,'09/09/2003','Married')
Insert into tblEmployee values
('Pam','Bruney','Falls Church','Female','11/09/1979','USA',3400,'07/07/2008','Married')
Insert into tblEmployee values ('John','Oswin','London','Male','03/08/1980','UK',5500,'06/02/2001','Single')
Insert into tblEmployee values
('Nick','Lan','New Delhi','Male','05/09/1975','India',7800,'01/05/1995','Married')
Insert into tblEmployee values ('James','Marshal','London','Male','01/10/1977','UK',6300,'08/09/2001','Single')

In a gridview control, I just want to show 3 columns (Id, FirstName and City) from tblEmployee table. As soon as I select a row, in the gridview control, then all the columns of the selected row, should be displayed in details view control as shown below.


To achieve this
Step 1: Drag and drop a gridview and a detailsview control on webform1.aspx
Step 2: Drag and drop 2 sqldatasource controls
Step 3: Configure SqlDataSource1 to retrieve [Id], [FirstName], [City] columns from tblEmployee table
Step 4: Associate SqlDataSource1 with GridView1 control
Step 5: Configure SqlDataSource2 to retrieve all columns from tblEmployee table. Add a WHERE clause to filter the rows based on the selected row in GridView1 control.


Step 6: Associate SqlDataSource2 with DetailsView1 control

At this point the HTML of your webform should be as shown below.
<asp:GridView ID="GridView1" runat="server"
    AutoGenerateColumns="False"
    DataKeyNames="Id" DataSourceID="SqlDataSource1">
    <Columns>
        <asp:CommandField ShowSelectButton="True" />
        <asp:BoundField DataField="Id" HeaderText="Id"
            InsertVisible="False"
            ReadOnly="True" SortExpression="Id" />
        <asp:BoundField DataField="FirstName"
            HeaderText="FirstName"
            SortExpression="FirstName" />
        <asp:BoundField DataField="City" HeaderText="City"
            SortExpression="City" />
    </Columns>
</asp:GridView>
<br />
<asp:DetailsView ID="DetailsView1" runat="server"
    AutoGenerateRows="False"
    DataKeyNames="Id" DataSourceID="SqlDataSource2"
    Height="50px" Width="125px">
    <Fields>
        <asp:BoundField DataField="Id" HeaderText="Id"
            InsertVisible="False"
            ReadOnly="True" SortExpression="Id" />
        <asp:BoundField DataField="FirstName"
            HeaderText="FirstName"
            SortExpression="FirstName" />
        <asp:BoundField DataField="LastName"
            HeaderText="LastName"
            SortExpression="LastName" />
        <asp:BoundField DataField="City"
            HeaderText="City"
            SortExpression="City" />
        <asp:BoundField DataField="Gender"
            HeaderText="Gender"
            SortExpression="Gender" />
        <asp:BoundField DataField="DateOfBirth"
            HeaderText="DateOfBirth"
            SortExpression="DateOfBirth"
            DataFormatString="{0:d}" />
        <asp:BoundField DataField="Country"
            HeaderText="Country"
            SortExpression="Country" />
        <asp:BoundField DataField="Salary"
            HeaderText="Salary"
            SortExpression="Salary" />
        <asp:BoundField DataField="DateOfJoining"
            HeaderText="DateOfJoining"
            SortExpression="DateOfJoining"
            DataFormatString="{0:d}"/>
        <asp:BoundField DataField="MaritalStatus"
            HeaderText="MaritalStatus"
            SortExpression="MaritalStatus" />
    </Fields>
</asp:DetailsView>
<br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:DBCS %>"
    SelectCommand="SELECT [Id], [FirstName], [City] FROM [tblEmployee]">
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
    ConnectionString="<%$ ConnectionStrings:DBCS %>"
    SelectCommand="SELECT * FROM [tblEmployee] WHERE ([Id] = @Id)">
    <SelectParameters>
        <asp:ControlParameter ControlID="GridView1" Name="Id"
            PropertyName="SelectedValue" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

Now set the colour scheme of GridView1 and DetailsView1 to BrownSugar and run the application. DetailsView is rendered as an empty box. This is because we don't have any row selected in GridView1 intially. To correct this issue copy and paste the following code in webform1.aspx.cs
protected void Page_PreRender(object sender, EventArgs e)
{
    if (GridView1.SelectedRow == null)
    {
        DetailsView1.Visible = false;
    }
    else
    {
        DetailsView1.Visible = true;
    }
}

Detailsview can also be used to perform insert, edit, and delete. We will discuss about these in a later video session.

Using objectdatasource control with detailsview - Part 38

Suggested Videos 
Part 35 - Drilldown and display hierarchical data in gridview using objectdatasource control
Part 36 - Drilldown and display hierarchical data in gridview without using datasource controls
Part 37 - Detailsview in asp.net



In Part 37 of asp.net gridview tutorial, we discussed about using sqldatasource control with detailsview. In this video, we will discuss about using objectdatasource. We will be using tblEmployee table for this demo. If you need sql script to create this table, please refere to Part 37 by clicking here.



In a gridview control, I just want to show 3 columns (Id, FirstName and City) from tblEmployee table. As soon as I select a row, in the gridview control, then all the columns of the selected row, should be displayed in details view control as shown below.


To use objectdatasource controls, to retrieve employee data, we need to create employee data access layer.

Step 1: Add a class file with name EmployeeDataAccessLayer.cs. Copy and paste the following code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace Demo
{
    public class EmployeeBasic
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string City { get; set; }
    }

    public class Employee : EmployeeBasic
    {
        public string LastName { get; set; }
        public string Gender { get; set; }
        public DateTime DateOfBirth { get; set; }
        public string Country { get; set; }
        public int Salary { get; set; }
        public DateTime DateOfJoining { get; set; }
        public string MaritalStatus { get; set; }
    }

    public class EmployeeDataAccessLayer
    {
        public static List<EmployeeBasic> GetAllEmployeesBasicDetails()
        {
            List<EmployeeBasic> listEmployees = new List<EmployeeBasic>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new 
                    SqlCommand("Select Id, FirstName, City from tblEmployee", con);
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    EmployeeBasic employeeBasic = new EmployeeBasic();
                    employeeBasic.Id = Convert.ToInt32(rdr["Id"]);
                    employeeBasic.FirstName = rdr["FirstName"].ToString();
                    employeeBasic.City = rdr["City"].ToString();

                    listEmployees.Add(employeeBasic);
                }
            }

            return listEmployees;
        }

        public static Employee GetAllEmployeesFullDetailsById(int Id)
        {
            Employee employee = new Employee();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new
                    SqlCommand("Select * from tblEmployee where Id = @Id", con);
                SqlParameter parameter = new SqlParameter();
                parameter.ParameterName = "@Id";
                parameter.Value = Id;
                cmd.Parameters.Add(parameter);
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    employee.Id = Convert.ToInt32(rdr["Id"]);
                    employee.FirstName = rdr["FirstName"].ToString();
                    employee.City = rdr["City"].ToString();
                    employee.LastName = rdr["LastName"].ToString();
                    employee.Gender = rdr["Gender"].ToString();
                    employee.DateOfBirth = Convert.ToDateTime(rdr["DateOfBirth"]);
                    employee.Country = rdr["Country"].ToString();
                    employee.Salary = Convert.ToInt32(rdr["Salary"]);
                    employee.DateOfJoining = Convert.ToDateTime(rdr["DateOfJoining"]);
                    employee.MaritalStatus = rdr["MaritalStatus"].ToString();
                }
            }

            return employee;
        }
    }
}

Step 2: Build your solution, so that the employee data access layer class is compiled.

Step 3: Drag and drop a gridview and a detailsview control on webform1.aspx

Step 4: Drag and drop 2 objectdatasource controls

Step 5: Configure ObjectDataSource1 control to retrieve data from EmployeeDataAccessLayer. Use GetAllEmployeesBasicDetails() as the SELECT method.

Step 6: Asscociate ObjectDataSource1 control with Gridview1 control.

Step 7: Configure ObjectDataSource2 control to retrieve data from EmployeeDataAccessLayer. Use GetAllEmployeesFullDetailsById(int Id) as the SELECT method. The value for the parameter - Id should come fromt the selected row in GridView1 control.

Step 8: Associate ObjectDataSource2 with DetailsView1 control.

Step 9: Flip the webform to HTML source mode and set
GridView1 DataKeyNames property to "Id"

At this stage the HTML of your webform should be as shown below.
<asp:GridView ID="GridView1" runat="server"
    DataKeyNames="Id" DataSourceID="ObjectDataSource1"
    AutoGenerateColumns="False">
    <Columns>
        <asp:CommandField ShowSelectButton="True" />
        <asp:BoundField DataField="Id" HeaderText="Id"
            SortExpression="Id" />
        <asp:BoundField DataField="FirstName"
            HeaderText="FirstName"
            SortExpression="FirstName" />
        <asp:BoundField DataField="City" HeaderText="City"
            SortExpression="City" />
    </Columns>
</asp:GridView>
<br />
<asp:DetailsView ID="DetailsView1" runat="server"
    AutoGenerateRows="False" Width="125px"
    DataSourceID="ObjectDataSource2" Height="50px">
    <Fields>
        <asp:BoundField DataField="Id" HeaderText="Id"
            SortExpression="Id" />
        <asp:BoundField DataField="FirstName"
            HeaderText="FirstName"
            SortExpression="FirstName" />
        <asp:BoundField DataField="City" HeaderText="City"
            SortExpression="City" />
        <asp:BoundField DataField="LastName"
            HeaderText="LastName"
            SortExpression="LastName" />
        <asp:BoundField DataField="Gender"
            HeaderText="Gender"
            SortExpression="Gender" />
        <asp:BoundField DataField="DateOfBirth"
            HeaderText="DateOfBirth"
            SortExpression="DateOfBirth" />
        <asp:BoundField DataField="Country"
            HeaderText="Country"
            SortExpression="Country" />
        <asp:BoundField DataField="Salary"
            HeaderText="Salary"
            SortExpression="Salary" />
        <asp:BoundField DataField="DateOfJoining"
            HeaderText="DateOfJoining"
            SortExpression="DateOfJoining" />
        <asp:BoundField DataField="MaritalStatus"
            HeaderText="MaritalStatus"
            SortExpression="MaritalStatus" />
    </Fields>
</asp:DetailsView>
<br />
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
    SelectMethod="GetAllEmployeesBasicDetails"
    TypeName="Demo.EmployeeDataAccessLayer">
</asp:ObjectDataSource>
<asp:ObjectDataSource ID="ObjectDataSource2" runat="server"
    SelectMethod="GetAllEmployeesFullDetailsById"
    TypeName="Demo.EmployeeDataAccessLayer">
    <SelectParameters>
        <asp:ControlParameter ControlID="GridView1" Name="Id"
            PropertyName="SelectedValue" Type="Int32" />
    </SelectParameters>
</asp:ObjectDataSource>

Now set the colour scheme of GridView1 and DetailsView1 to BrownSugar and run the application. DetailsView is rendered without any employee data. This is because we don't have any employee row selected in GridView1 intially. To correct this issue copy and paste the following code in webform1.aspx.cs
protected void Page_PreRender(object sender, EventArgs e)
{
    if (GridView1.SelectedRow == null)
    {
        DetailsView1.Visible = false;
    }
    else
    {
        DetailsView1.Visible = true;
    }
}

Working with detailsview without using datasource controls - Part 39

Suggested Videos 
Part 36 - Drilldown and display hierarchical data in gridview without using datasource controls
Part 37 - Detailsview in asp.net
Part 38 - Using objectdatasource control with detailsview



In Part 37 of asp.net gridview tutorial, we discussed about using sqldatasource control with detailsview, and in Part 38, we discussed about using objectdatasource control. In this video, we will discuss about working with detailsview without using any datasource control.



In a gridview control, I just want to show 3 columns (Id, FirstName and City) from tblEmployee table. As soon as I select a row, in the gridview control, then all the columns of the selected row, should be displayed in details view control as shown below.


We will be using tblEmployee table for this demo. If you need sql script to create this table, please refer to Part 37 by clicking here. We will be using EmployeeDataAccessLayer, that we coded in Part 38. If you need EmployeeDataAccessLayer code, please refer to Part 38, by clicking here.

Drag and drop a gridview control and a detailsview control on webform1.aspx.
1. Configure GridView1, to include 1 TemplateField and 3 BoundFields.
2. The TemplateField should contain an ItemTemplate, which includes a linkbutton, that the user can click.
3. Set the CommandName property of the LinkButton to SelectFullDetails.
4. Configure the rest of the BoundFields to get data from Id, FirstName and City properties.
5. Set AutoGenerateColumns property of GridView1 to false.
6. Finally generate RowCommand event handler method for GridView1.
7. Set DataKeyNames property of GridView1
DataKeyNames="Id"

At this point the HTML on WebForm1.aspx should be as shown below
<asp:GridView ID="GridView1" runat="server"
    AutoGenerateColumns="False"
    onrowcommand="GridView1_RowCommand"
    DataKeyNames="Id">
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:LinkButton ID="LinkButton1"
                    runat="server"
                    CommandName="SelectFullDetails">
                    Select
                </asp:LinkButton>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="Id"
            HeaderText="Id" />
        <asp:BoundField DataField="FirstName"
            HeaderText="FirstName" />
        <asp:BoundField DataField="City"
            HeaderText="City" />
    </Columns>
</asp:GridView>
<br />
<asp:DetailsView ID="DetailsView1" runat="server">
</asp:DetailsView>

Copy and paste the following code in webform1.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        GridView1.DataSource =
            EmployeeDataAccessLayer.GetAllEmployeesBasicDetails();
        GridView1.DataBind();
    }
}

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
    if (e.CommandName == "SelectFullDetails")
    {
        int rowIndex =
            ((GridViewRow)((LinkButton)e.CommandSource).NamingContainer).RowIndex;
        GridView1.SelectRow(rowIndex);

        int employeeId = Convert.ToInt32(GridView1.SelectedValue);

        List<Employee> employeeList = new List<Employee>()
        {
            EmployeeDataAccessLayer.GetEmployeesFullDetailsById(employeeId)
        };

        DetailsView1.DataSource = employeeList;
        DetailsView1.DataBind();
    }
}

asp.net detailsview insert update delete using sqldatasource control - Part 40

Suggested Videos 
Part 37 - Detailsview in asp.net
Part 38 - Using objectdatasource control with detailsview
Part 39 - Working with detailsview without using datasource controls



In this video we will discuss about inserting, updating and deleting data using detailsview and sqldatasource control.

When the webform is loaded, the gridview control, should retrieve and display all the rows from tblEmployee table. As soon as I select a row, in the gridview control, then all the columns of the selected row, should be displayed in details view control. Then, we should be able to edit, delete and add a new employee using the DetailsView control, as shown in the image below.




We will be using tblEmployee table for this demo. If you need sql script to create this table, please refer to Part 37 by clicking here.

Step 1: Drag and drop a gridview control, a detailsview control and 2 sqldatasource controls on webform1.aspx.

Step 2: Configure SqlDataSource1 to retrieve [Id], [FirstName], [City] columns from tblEmployee table

Step 3: Associate SqlDataSource1 with GridView1 control

Step 4: Configure SqlDataSource2 to retrieve all columns from tblEmployee table. Add a WHERE clause to filter the rows based on the selected row in GridView1 control.


Step 5: While configuring SqlDataSource2 control, click on "Advanced" button, and make sure "Generate INSERT, UPDATE and DELETE statements" checkbox is selected.


Step 6: Associate SqlDataSource2 with DetailsView1 control and make sure the following checkboxes are selected.
Enable Inserting
Enable Editing
Enable Deleting

Step 7: Generate ItemInserted, ItemUpdated and ItemDeleted event handler methods for DetailsView1 control.

Step 8: Copy and paste the following code in webform1.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Page_PreRender(object sender, EventArgs e)
{
    if (GridView1.SelectedRow == null)
    {
        DetailsView1.Visible = false;
    }
    else
    {
        DetailsView1.Visible = true;
    }
}
protected void DetailsView1_ItemInserted(object sender, DetailsViewInsertedEventArgs e)
{
    GridView1.DataBind();
    GridView1.SelectRow(-1);
}
protected void DetailsView1_ItemDeleted(object sender, DetailsViewDeletedEventArgs e)
{
    GridView1.DataBind();
    GridView1.SelectRow(-1);
}
protected void DetailsView1_ItemUpdated(object sender, DetailsViewUpdatedEventArgs e)
{
    GridView1.DataBind();
    GridView1.SelectRow(-1);
}

asp.net detailsview insert update delete using objectdatasource control - Part 41

Suggested Videos 
Part 38 - Using objectdatasource control with detailsview
Part 39 - Working with detailsview without using datasource controls
Part 40 - asp.net detailsview insert update delete using sqldatasource control



In this video we will discuss about inserting, updating and deleting data using detailsview and objectdatasource control.

When the webform is loaded, the gridview control, should retrieve and display all the rows from tblEmployee table. As soon as I select a row, in the gridview control, then all the columns of the selected row, should be displayed in details view control. Then, we should be able to edit, delete and add a new employee using the DetailsView control, as shown in the image below.




We will be using tblEmployee table for this demo. If you need sql script to create this table, please refer to Part 37 by clicking here.

Step 1: Drag and drop a gridview control, a detailsview control and 2 objectdatasource controls on webform1.aspx.

Step 2: Add a class file, with name = EmployeeDataAccessLayer.cs to your project. Copy and paste the following code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace Demo
{
    public class EmployeeBasic
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string City { get; set; }
    }

    public class Employee : EmployeeBasic
    {
        public string LastName { get; set; }
        public string Gender { get; set; }
        public DateTime DateOfBirth { get; set; }
        public string Country { get; set; }
        public int Salary { get; set; }
        public DateTime DateOfJoining { get; set; }
        public string MaritalStatus { get; set; }
    }
   
    public class EmployeeDataAccessLayer
    {
        public static List<EmployeeBasic> GetAllEmployeesBasicDetails()
        {
            List<EmployeeBasic> listEmployees = new List<EmployeeBasic>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new
                    SqlCommand("Select Id, FirstName, City from tblEmployee", con);
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    EmployeeBasic employeeBasic = new EmployeeBasic();
                    employeeBasic.Id = Convert.ToInt32(rdr["Id"]);
                    employeeBasic.FirstName = rdr["FirstName"].ToString();
                    employeeBasic.City = rdr["City"].ToString();

                    listEmployees.Add(employeeBasic);
                }
            }

            return listEmployees;
        }

        public static Employee GetEmployeesFullDetailsById(int Id)
        {
            Employee employee = new Employee();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new
                    SqlCommand("Select * from tblEmployee where Id = @Id", con);
                SqlParameter parameter = new SqlParameter();
                parameter.ParameterName = "@Id";
                parameter.Value = Id;
                cmd.Parameters.Add(parameter);
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    employee.Id = Convert.ToInt32(rdr["Id"]);
                    employee.FirstName = rdr["FirstName"].ToString();
                    employee.City = rdr["City"].ToString();
                    employee.LastName = rdr["LastName"].ToString();
                    employee.Gender = rdr["Gender"].ToString();
                    employee.DateOfBirth = Convert.ToDateTime(rdr["DateOfBirth"]);
                    employee.Country = rdr["Country"].ToString();
                    employee.Salary = Convert.ToInt32(rdr["Salary"]);
                    employee.DateOfJoining = Convert.ToDateTime(rdr["DateOfJoining"]);
                    employee.MaritalStatus = rdr["MaritalStatus"].ToString();
                }
            }

            return employee;
        }

        public static void InsertEmployee(string firstName, string lastName,
                   string city, string gender, DateTime dateOfBirth, string country,
                   int salary, DateTime dateOfJoining, string maritalStatus)
        {
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                string insertQuery = "Insert into tblEmployee values (@FirstName, " +
                    "@LastName, @City, @Gender, @DateOfBirth, @Country, @Salary, " +
                    "@DateOfJoining, @MaritalStatus)";

                SqlCommand cmd = new SqlCommand(insertQuery, con);

                #region Parameters

                SqlParameter parameter_FirstName = new SqlParameter();
                parameter_FirstName.ParameterName = "@FirstName";
                parameter_FirstName.Value = firstName;
                cmd.Parameters.Add(parameter_FirstName);

                SqlParameter parameter_LastName = new SqlParameter();
                parameter_LastName.ParameterName = "@LastName";
                parameter_LastName.Value = lastName;
                cmd.Parameters.Add(parameter_LastName);

                SqlParameter parameter_city = new SqlParameter();
                parameter_city.ParameterName = "@City";
                parameter_city.Value = city;
                cmd.Parameters.Add(parameter_city);

                SqlParameter parameter_Gender = new SqlParameter();
                parameter_Gender.ParameterName = "@Gender";
                parameter_Gender.Value = gender;
                cmd.Parameters.Add(parameter_Gender);

                SqlParameter parameter_DateOfBirth = new SqlParameter();
                parameter_DateOfBirth.ParameterName = "@DateOfBirth";
                parameter_DateOfBirth.Value = dateOfBirth;
                cmd.Parameters.Add(parameter_DateOfBirth);

                SqlParameter parameter_Country = new SqlParameter();
                parameter_Country.ParameterName = "@Country";
                parameter_Country.Value = country;
                cmd.Parameters.Add(parameter_Country);

                SqlParameter parameter_Salary = new SqlParameter();
                parameter_Salary.ParameterName = "@Salary";
                parameter_Salary.Value = salary;
                cmd.Parameters.Add(parameter_Salary);

                SqlParameter parameter_DateOfJoining = new SqlParameter();
                parameter_DateOfJoining.ParameterName = "@DateOfJoining";
                parameter_DateOfJoining.Value = dateOfJoining;
                cmd.Parameters.Add(parameter_DateOfJoining);

                SqlParameter parameter_MaritalStatus = new SqlParameter();
                parameter_MaritalStatus.ParameterName = "@MaritalStatus";
                parameter_MaritalStatus.Value = maritalStatus;
                cmd.Parameters.Add(parameter_MaritalStatus);

                #endregion

                con.Open();
                cmd.ExecuteNonQuery();
            }
        }

        public static void UpdateEmployee(int id, string firstName, string lastName,
                   string city, string gender, DateTime dateOfBirth, string country,
                   int salary, DateTime dateOfJoining, string maritalStatus)
        {
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                string updateQuery = "Update tblEmployee set FirstName = @FirstName, " +
                    "LastName = @LastName, City = @City, Gender = @Gender, " +
                    "DateOfBirth = @DateOfBirth, Country = @Country, Salary = @Salary," +
                    "DateOfJoining = @DateOfJoining, MaritalStatus = @MaritalStatus where Id = @Id";

                SqlCommand cmd = new SqlCommand(updateQuery, con);

                #region Parameters

                SqlParameter parameter_Id = new SqlParameter();
                parameter_Id.ParameterName = "@Id";
                parameter_Id.Value = id;
                cmd.Parameters.Add(parameter_Id);

                SqlParameter parameter_FirstName = new SqlParameter();
                parameter_FirstName.ParameterName = "@FirstName";
                parameter_FirstName.Value = firstName;
                cmd.Parameters.Add(parameter_FirstName);

                SqlParameter parameter_LastName = new SqlParameter();
                parameter_LastName.ParameterName = "@LastName";
                parameter_LastName.Value = lastName;
                cmd.Parameters.Add(parameter_LastName);

                SqlParameter parameter_city = new SqlParameter();
                parameter_city.ParameterName = "@City";
                parameter_city.Value = city;
                cmd.Parameters.Add(parameter_city);

                SqlParameter parameter_Gender = new SqlParameter();
                parameter_Gender.ParameterName = "@Gender";
                parameter_Gender.Value = gender;
                cmd.Parameters.Add(parameter_Gender);

                SqlParameter parameter_DateOfBirth = new SqlParameter();
                parameter_DateOfBirth.ParameterName = "@DateOfBirth";
                parameter_DateOfBirth.Value = dateOfBirth;
                cmd.Parameters.Add(parameter_DateOfBirth);

                SqlParameter parameter_Country = new SqlParameter();
                parameter_Country.ParameterName = "@Country";
                parameter_Country.Value = country;
                cmd.Parameters.Add(parameter_Country);

                SqlParameter parameter_Salary = new SqlParameter();
                parameter_Salary.ParameterName = "@Salary";
                parameter_Salary.Value = salary;
                cmd.Parameters.Add(parameter_Salary);

                SqlParameter parameter_DateOfJoining = new SqlParameter();
                parameter_DateOfJoining.ParameterName = "@DateOfJoining";
                parameter_DateOfJoining.Value = dateOfJoining;
                cmd.Parameters.Add(parameter_DateOfJoining);

                SqlParameter parameter_MaritalStatus = new SqlParameter();
                parameter_MaritalStatus.ParameterName = "@MaritalStatus";
                parameter_MaritalStatus.Value = maritalStatus;
                cmd.Parameters.Add(parameter_MaritalStatus);

                #endregion

                con.Open();
                cmd.ExecuteNonQuery();
            }
        }

        public static void DeleteEmployee(int id)
        {
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("delete from tblEmployee where Id = @Id", con);

                SqlParameter parameter_Id = new SqlParameter();
                parameter_Id.ParameterName = "@Id";
                parameter_Id.Value = id;
                cmd.Parameters.Add(parameter_Id);

                con.Open();
                cmd.ExecuteNonQuery();
            }
        }
    }
}

Step 3: Build your solution, so that the employee data access layer class is compiled.

Step 4: Configure ObjectDataSource1 control to retrieve data from EmployeeDataAccessLayer. Use GetAllEmployeesBasicDetails() as the SELECT method.

Step 5: Asscociate ObjectDataSource1 control with Gridview1 control, and  make sure "Enable Selection" checkbox is checked.

Step 6: Configure ObjectDataSource2 control to retrieve data from EmployeeDataAccessLayer. Use GetEmployeesFullDetailsById(int Id) as the SELECT method. The value for the parameter - Id should come from the selected row in GridView1 control. Also, select INSERT, UPDATE and DELETE methods.
INSERT Method - InsertEmployee()
UPDATE Method - UpdateEmployee()
DELETE Method - DeleteEmployee()

Step 7: Associate ObjectDataSource2 with DetailsView1 control and make sure the following checkboxes are selected.
Enable Inserting
Enable Editing
Enable Deleting

Step 8: Generate ItemInserted, ItemUpdated and ItemDeleted event handler methods for DetailsView1 control.

Step 9: Copy and paste the following code in webform1.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Page_PreRender(object sender, EventArgs e)
{
    if (GridView1.SelectedRow == null)
    {
        DetailsView1.Visible = false;
    }
    else
    {
        DetailsView1.Visible = true;
    }
}
protected void DetailsView1_ItemInserted(object sender, DetailsViewInsertedEventArgs e)
{
    GridView1.DataBind();
    GridView1.SelectRow(-1);
}
protected void DetailsView1_ItemDeleted(object sender, DetailsViewDeletedEventArgs e)
{
    GridView1.DataBind();
    GridView1.SelectRow(-1);
}
protected void DetailsView1_ItemUpdated(object sender, DetailsViewUpdatedEventArgs e)
{
    GridView1.DataBind();
    GridView1.SelectRow(-1);
}

Step 10: Set GridView1, DataKeyNames to "Id"
DataKeyNames="Id"

Step 11: Run the application. Select a row in gridview, and click "Edit". Notice that Id is also editable. To make it non editable, set ReadOnly attribute to true.

Step 12: Click on "New" link button in detailsview to insert a new row. Notice that, the interface shows a textbox for Id column. This column in the database is identity column, and we don't have to supply a value for it. To make it invisible, set InsertVisible attribute to false.

Setp 13: Click on Edit, change a few values, and click "Update". Notice that none of the rows are updated. To solve this issue, set DataKeyNames="Id" on DetailsView1 control.

 

asp.net detailsview insert update delete without using data source controls - Part 42

Suggested Videos
Part 39 - Working with detailsview without using datasource controls
Part 40 - Detailsview insert update delete using sqldatasource control
Part 41 - Detailsview insert update delete using objectdatasource control



In this video we will discuss about inserting, updating and deleting data from detailsview without using any datasource control.

When the webform is loaded, the gridview control, should retrieve and display all the rows from tblEmployee table. As soon as I select a row, in the gridview control, then all the columns of the selected row, should be displayed in details view control. Then, we should be able to edit, delete and add a new employee using the DetailsView control, as shown in the image below.




We will be using tblEmployee table for this demo. If you need sql script to create this table, please refer to Part 37 by clicking here.

We implemented EmployeeDataAccessLayer with methods to Select, Insert, Update and Delete data in Part 41. We will be using EmployeeDataAccessLayer for this demo, if you need the code, please refer to Part 41 by clicking here.

Step 1: Drag and drop "GridView" control on WebForm1.aspx

Step 2: On GridView1, set
DataKeyNames=Id
AutoGenerateSelectButton="true"

Step 3: Generate event handler method for SelectedIndexChanged event of GridView1 control

Step 4: Drag and drop "DetailsView" control on WebForm1.aspx

Step 5: On DetailsView1, set
DataKeyNames=Id
AutoGenerateRows="False"
AutoGenerateSelectButton="True"
AutoGenerateDeleteButton="True"
AutoGenerateEditButton="True"
AutoGenerateInsertButton="True"

Setp 6: Add 10 BoundField's and set DataField and HeaderText accordingly.

Setp 7: Set ReadOnly="True" and InsertVisible="False" on the bound field that display, Id column value.

Step 8: Generate event handler method for the following events of DetailsView1 control.
ModeChanging
ItemInserting
ItemDeleting
ItemUpdating

Step 9: Copy and paste the following code in webform1.aspx.cs file
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        GridViewDataBind();
    }
}

private void GridViewDataBind()
{
    GridView1.DataSource = EmployeeDataAccessLayer.GetAllEmployeesBasicDetails();
    GridView1.DataBind();
}

protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
    DetailsViewDataBind();
}

private void DetailsViewDataBind()
{
    if (GridView1.SelectedDataKey != null)
    {
        DetailsView1.Visible = true;

        List<Employee> listEmployee = new List<Employee>();
        listEmployee.Add(EmployeeDataAccessLayer.GetEmployeesFullDetailsById((int)GridView1.SelectedDataKey.Value));

        DetailsView1.DataSource = listEmployee;
        DetailsView1.DataBind();
    }
    else
    {
        DetailsView1.Visible = false;
    }
}

protected void DetailsView1_ModeChanging(object sender, DetailsViewModeEventArgs e)
{
    DetailsView1.ChangeMode(e.NewMode);
    DetailsViewDataBind();
}

protected void DetailsView1_ItemInserting(object sender, DetailsViewInsertEventArgs e)
{
    EmployeeDataAccessLayer.InsertEmployee(e.Values["FirstName"].ToString(),
        e.Values["LastName"].ToString(), e.Values["City"].ToString(),
        e.Values["Gender"].ToString(), Convert.ToDateTime(e.Values["DateOfBirth"]),
        e.Values["Country"].ToString(), Convert.ToInt32(e.Values["Salary"]),
        Convert.ToDateTime(e.Values["DateOfJoining"]), e.Values["MaritalStatus"].ToString());

    DetailsView1.ChangeMode(DetailsViewMode.ReadOnly);

    GridViewDataBind();
    GridView1.SelectRow(-1);
}

protected void DetailsView1_ItemDeleting(object sender, DetailsViewDeleteEventArgs e)
{
    EmployeeDataAccessLayer.DeleteEmployee((int)GridView1.SelectedDataKey.Value);

    GridViewDataBind();
    GridView1.SelectRow(-1);
}

protected void DetailsView1_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)
{
    int employeeId = (int)GridView1.SelectedDataKey.Value;
    EmployeeDataAccessLayer.UpdateEmployee(employeeId,
        e.NewValues["FirstName"].ToString(), e.NewValues["LastName"].ToString(),
        e.NewValues["City"].ToString(), e.NewValues["Gender"].ToString(),
        Convert.ToDateTime(e.NewValues["DateOfBirth"]), e.NewValues["Country"].ToString(),
        Convert.ToInt32(e.NewValues["Salary"]), Convert.ToDateTime(e.NewValues["DateOfJoining"]),
        e.NewValues["MaritalStatus"].ToString());

    DetailsView1.ChangeMode(DetailsViewMode.ReadOnly);

    GridViewDataBind();
    GridView1.SelectRow(-1);
}

 

asp.net detailsview insert update delete without using data source controls - Part 42

Suggested Videos
Part 39 - Working with detailsview without using datasource controls
Part 40 - Detailsview insert update delete using sqldatasource control
Part 41 - Detailsview insert update delete using objectdatasource control



In this video we will discuss about inserting, updating and deleting data from detailsview without using any datasource control.

When the webform is loaded, the gridview control, should retrieve and display all the rows from tblEmployee table. As soon as I select a row, in the gridview control, then all the columns of the selected row, should be displayed in details view control. Then, we should be able to edit, delete and add a new employee using the DetailsView control, as shown in the image below.




We will be using tblEmployee table for this demo. If you need sql script to create this table, please refer to Part 37 by clicking here.

We implemented EmployeeDataAccessLayer with methods to Select, Insert, Update and Delete data in Part 41. We will be using EmployeeDataAccessLayer for this demo, if you need the code, please refer to Part 41 by clicking here.

Step 1: Drag and drop "GridView" control on WebForm1.aspx

Step 2: On GridView1, set
DataKeyNames=Id
AutoGenerateSelectButton="true"

Step 3: Generate event handler method for SelectedIndexChanged event of GridView1 control

Step 4: Drag and drop "DetailsView" control on WebForm1.aspx

Step 5: On DetailsView1, set
DataKeyNames=Id
AutoGenerateRows="False"
AutoGenerateSelectButton="True"
AutoGenerateDeleteButton="True"
AutoGenerateEditButton="True"
AutoGenerateInsertButton="True"

Setp 6: Add 10 BoundField's and set DataField and HeaderText accordingly.

Setp 7: Set ReadOnly="True" and InsertVisible="False" on the bound field that display, Id column value.

Step 8: Generate event handler method for the following events of DetailsView1 control.
ModeChanging
ItemInserting
ItemDeleting
ItemUpdating

Step 9: Copy and paste the following code in webform1.aspx.cs file
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        GridViewDataBind();
    }
}

private void GridViewDataBind()
{
    GridView1.DataSource = EmployeeDataAccessLayer.GetAllEmployeesBasicDetails();
    GridView1.DataBind();
}

protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
    DetailsViewDataBind();
}

private void DetailsViewDataBind()
{
    if (GridView1.SelectedDataKey != null)
    {
        DetailsView1.Visible = true;

        List<Employee> listEmployee = new List<Employee>();
        listEmployee.Add(EmployeeDataAccessLayer.GetEmployeesFullDetailsById((int)GridView1.SelectedDataKey.Value));

        DetailsView1.DataSource = listEmployee;
        DetailsView1.DataBind();
    }
    else
    {
        DetailsView1.Visible = false;
    }
}

protected void DetailsView1_ModeChanging(object sender, DetailsViewModeEventArgs e)
{
    DetailsView1.ChangeMode(e.NewMode);
    DetailsViewDataBind();
}

protected void DetailsView1_ItemInserting(object sender, DetailsViewInsertEventArgs e)
{
    EmployeeDataAccessLayer.InsertEmployee(e.Values["FirstName"].ToString(),
        e.Values["LastName"].ToString(), e.Values["City"].ToString(),
        e.Values["Gender"].ToString(), Convert.ToDateTime(e.Values["DateOfBirth"]),
        e.Values["Country"].ToString(), Convert.ToInt32(e.Values["Salary"]),
        Convert.ToDateTime(e.Values["DateOfJoining"]), e.Values["MaritalStatus"].ToString());

    DetailsView1.ChangeMode(DetailsViewMode.ReadOnly);

    GridViewDataBind();
    GridView1.SelectRow(-1);
}

protected void DetailsView1_ItemDeleting(object sender, DetailsViewDeleteEventArgs e)
{
    EmployeeDataAccessLayer.DeleteEmployee((int)GridView1.SelectedDataKey.Value);

    GridViewDataBind();
    GridView1.SelectRow(-1);
}

protected void DetailsView1_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)
{
    int employeeId = (int)GridView1.SelectedDataKey.Value;
    EmployeeDataAccessLayer.UpdateEmployee(employeeId,
        e.NewValues["FirstName"].ToString(), e.NewValues["LastName"].ToString(),
        e.NewValues["City"].ToString(), e.NewValues["Gender"].ToString(),
        Convert.ToDateTime(e.NewValues["DateOfBirth"]), e.NewValues["Country"].ToString(),
        Convert.ToInt32(e.NewValues["Salary"]), Convert.ToDateTime(e.NewValues["DateOfJoining"]),
        e.NewValues["MaritalStatus"].ToString());

    DetailsView1.ChangeMode(DetailsViewMode.ReadOnly);

    GridViewDataBind();
    GridView1.SelectRow(-1);
}

Nested gridview in asp.net - Part 43

Suggested Videos 
Part 40 - Detailsview insert update delete using sqldatasource control
Part 41 - Detailsview insert update delete using objectdatasource control
Part 42 - Detailsview insert update delete without using data source controls



In this video we will discuss about nesting gridview controls.

Here is what we want to achieve
1. The outermost grdiview control, should display all the continents available.
2. The Countries column in the outermost gridview control, should display all the countries belonging to the continent in another gridview control. This means we are nesting countries gridview in continents gridview.
3. The Cities column in the countries gridview control, should display all the cities belonging to the country in another gridview control. This means we are nesting cities gridview in countries gridview.





So, this effectively means we are nesting Countries gridview inside Continents gridview and Cities gridview is nested inside Countries gridview
Continents GridView Control => Countries GridView Control  => Cities GridView Control

We will be using the following tables for this demo. If you need the sql script to create and populate these tables, please refer to Part 34 of asp.net gridview tutorial
tblContinents
tblCountries
tblCities

Step 1: Create an asp.net web application project. Add a class file with name = ContinentDataAccessLayer.cs.
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 Continent
    {
        public int ContinentId { get; set; }
        public string ContinentName { get; set; }
    }
   
    public class ContinentDataAccessLayer
    {
        public static List<Continent> GetAllContinents()
        {
            List<Continent> listContinents = new List<Continent>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("Select * from tblContinents", con);
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Continent continent = new Continent();
                    continent.ContinentId = Convert.ToInt32(rdr["ContinentId"]);
                    continent.ContinentName = rdr["ContinentName"].ToString();

                    listContinents.Add(continent);
                }
            }

            return listContinents;
        }
    }
}

Step 2: Add a class file with name = CountriesDataAccessLayer.cs.
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 Country
    {
        public int CountryId { get; set; }
        public string CountryName { get; set; }
        public int ContinentId { get; set; }
    }
   
    public class CountryDataAccessLayer
    {
        public static List<Country> GetCountriesByContinent(int ContinentId)
        {
            List<Country> listCountries = new List<Country>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("Select * from tblCountries where ContinentId = @ContinentId", con);
                SqlParameter parameter = new SqlParameter();
                parameter.ParameterName = "@ContinentId";
                parameter.Value = ContinentId;
                cmd.Parameters.Add(parameter);
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Country country = new Country();
                    country.CountryId = Convert.ToInt32(rdr["CountryId"]);
                    country.CountryName = rdr["CountryName"].ToString();
                    country.ContinentId = Convert.ToInt32(rdr["ContinentId"]);

                    listCountries.Add(country);
                }
            }

            return listCountries;
        }
    }
}

Step 3: Add a class file with name = CitiesDataAccessLayer.cs.
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 City
    {
        public int CityId { get; set; }
        public string CityName { get; set; }
        public int CountryId { get; set; }
    }
   
    public class CityDataAccessLayer
    {
        public static List<City> GetCitiesByCountryId(int CountryId)
        {
            List<City> listCities = new List<City>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("Select * from tblCities where CountryId = @CountryId", con);
                SqlParameter parameter = new SqlParameter();
                parameter.ParameterName = "@CountryId";
                parameter.Value = CountryId;
                cmd.Parameters.Add(parameter);
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    City city = new City();
                    city.CityId = Convert.ToInt32(rdr["CityId"]);
                    city.CityName = rdr["CityName"].ToString();
                    city.CountryId = Convert.ToInt32(rdr["CountryId"]);

                    listCities.Add(city);
                }
            }

            return listCities;
        }
    }
}

Step 4: Add Countries property to Continent class, in ContinentDataAccessLayer.cs file as shown below.
public class Continent
{
    public int ContinentId { get; set; }
    public string ContinentName { get; set; }
    public List<Country> Countries
    {
        get
        {
            return CountryDataAccessLayer.GetCountriesByContinent(this.ContinentId);
        }
    }
}

Step 5: Add Cities property to Country class, in CountryDataAccessLayer.cs file as shown below.
public class Country
{
    public int CountryId { get; set; }
    public string CountryName { get; set; }
    public int ContinentId { get; set; }
    public List<City> Cities
    {
        get
        {
            return CityDataAccessLayer.GetCitiesByCountryId(this.CountryId);
        }
    }
}

Step 6: Add a gridview control to your webform. Configure it, to include 2 BoundFields and 1 template field. Set AutoGenerateColumns="False". The 2 bound fields are for displaying ContinentId and ContinentName. Set DataField and HeaderText properties of these 2 bound fields accordingly.

Step 7: Drag and drop another gridview control, in TemplateField of GridView1 control. By Default the ID for the newly added gridview control will be GridView2. Now configure GridView2 to include 3 bound fields and a template field. The 3 bound fields are for displaying CountryId, CountryName and ContinentId. Set DataField and HeaderText properties of these 3 bound fields accordingly. Set AutoGenerateColumns="False". Set DataSource attribute of GridView2 control to bind to Countries property.
DataSource='<%# Bind("Countries") %>'

Step 8: Drag and drop another gridview control, in TemplateField of GridView2 control. By Default the ID for the newly added gridview control will be GridView3. Set DataSource attribute of GridView3 control to bind to Cities property.
DataSource='<%# Bind("Cities") %>'

At this point the HTML of your webform should be as shown below.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
<Columns>
    <asp:BoundField DataField="ContinentId" HeaderText="ContinentId" />
    <asp:BoundField DataField="ContinentName" HeaderText="ContinentName" />
    <asp:TemplateField HeaderText="Countries">
        <ItemTemplate>
            <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False"
                DataSource='<%# Bind("Countries") %>'>
                <Columns>
                    <asp:BoundField DataField="CountryId" HeaderText="CountryId" />
                    <asp:BoundField DataField="CountryName" HeaderText="CountryName" />
                    <asp:BoundField DataField="ContinentId" HeaderText="ContinentId" />
                    <asp:TemplateField HeaderText="Cities">
                        <ItemTemplate>
                            <asp:GridView ID="GridView3" runat="server"
                                DataSource='<%# Bind("Cities") %>'>
                            </asp:GridView>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
        </ItemTemplate>
    </asp:TemplateField>
</Columns>
</asp:GridView>

Step 9: Copy and paste the following code in Page_Load() event of WebForm1.aspx.cs
GridView1.DataSource = ContinentDataAccessLayer.GetAllContinents();
GridView1.DataBind();

Sorting a gridview that uses objectdatasource control and a dataset - Part 45

Suggested Videos 
Part 42 - Detailsview insert update delete without using data source controls
Part 43 - Nested gridview in ASP.NET
Part 44 - Sorting a gridview that uses sqldatasource control



In this video we will discuss about sorting a gridview control that uses objectdatasource control. We will discuss about sorting in both ascending and descending order.

We will be using tblEmployee table for this demo. Please refer to Part 13 by clicking here, if you need the sql script to create and populate these tables.



Step 1: Drag and drop a gridview and an objectdatasource control on webform1.aspx

Step 2: Add a class file with name = "EmployeeDataAccessLayer.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 Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public string City { get; set; }
    }
   
    public class EmployeeDataAccessLayer
    {
        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 3: Configure ObjectDataSource1 control to retrieve data from Demo.EmployeeDataAccessLayer business object, using GetAllEmployees() method.

Step 4: Associate GridView1, with ObjectDataSource1 control.

Step 5: Flip WebForm1.aspx to source mode and set AllowSorting="true" on GridView1 control.

Step 6: Run the application now. Click on any of the column headers to sort data. Notice that you get a runtime error stating - The data source 'ObjectDataSource1' does not support sorting with IEnumerable data. Automatic sorting is only supported with DataView, DataTable, and DataSet.

There are 2 ways to solve this issue. In this video, we will discuss about solving it using dataset. In our next video, we will discuss about the second way.

Change the implementation of GetAllEmployees() method to return DataSet, as shown below.
public class EmployeeDataAccessLayer
{
    public static DataSet GetAllEmployees()
    {
        string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
        using (SqlConnection con = new SqlConnection(CS))
        {
            SqlDataAdapter da = new SqlDataAdapter("Select * from tblEmployee", con);

            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds;
        }
    }
}

Here is the HTML
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    DataSourceID="ObjectDataSource1" AllowSorting="true">
    <Columns>
        <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">
</asp:ObjectDataSource>

Sorting a gridview that uses sqldatasource control - Part 44

Suggested Videos 
Part 41 - Detailsview insert update delete using objectdatasource control
Part 42 - Detailsview insert update delete without using data source controls
Part 43 - Nested gridview in ASP.NET



In this video we will discuss about sorting a gridview control that use sqldatasource control. We will discuss about sorting in both ascending and descending order.

We will be using tblEmployee table for this demo. Please refer to Part 13 by clicking here, if you need the sql script to create and populate these tables.



Step 1: Drag and drop a gridview and a sqldatasource control on webform1.aspx

Step 2: Configure SqlDataSource1 control to retrieve data from tblEmployee table.

Step 3: Associate GridView1, with SqlDataSource1 control and make sure to select "Enable Sorting" checkbox.


That's it we are done. Run the application. Click on any column to sort the data. Notice that, when you click once on the column header, the data will be sorted in ascending order, and if you click again the data will be sorted in descending order. A gridview that is sortable in both directions is called as bi-directional sortable gridview.

Points to remember
1. When sorting is enabled, AllowSorting attribute of GridView1 is set to true.
AllowSorting="True"
2. If you don't want to sort on a specific column, remove SortExpression attribute from that column

Here is the HTML
<asp:GridView ID="GridView1" runat="server" AllowSorting="True"
    AutoGenerateColumns="False" DataKeyNames="EmployeeId"
    DataSourceID="SqlDataSource1">
    <Columns>
        <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId" 
            InsertVisible="False" ReadOnly="True" 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:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:SampleConnectionString %>"
    SelectCommand="SELECT * FROM [tblEmployee]">
</asp:SqlDataSource>

 

Sorting a gridview that uses objectdatasource control and business objects - Part 46

Suggested Videos 
Part 43 - Nested gridview in ASP.NET
Part 44 - Sorting a gridview that uses sqldatasource control
Part 45 - Sorting a gridview that uses objectdatasource control and a dataset



In this video we will discuss about sorting a gridview control that uses objectdatasource control. We will discuss about sorting in both ascending and descending order.

We will be using tblEmployee table for this demo. Please refer to Part 13 by clicking here, if you need the sql script to create and populate these tables.

Please watch Part 45, before proceeding with this video.



Step 1: Drag and drop a gridview and an objectdatasource control on webform1.aspx

Step 2: Add a class file with name = "EmployeeDataAccessLayer.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 Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public string City { get; set; }
    }
   
    public class EmployeeDataAccessLayer
    {
        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 3: Configure ObjectDataSource1 control to retrieve data from Demo.EmployeeDataAccessLayer business object, using GetAllEmployees() method.

Step 4: Associate GridView1, with ObjectDataSource1 control.

Step 5: Flip WebForm1.aspx to source mode and set AllowSorting="true" on GridView1 control.

Step 6: Run the application now. Click on any of the column headers to sort data. Notice that you get a runtime error stating - The data source 'ObjectDataSource1' does not support sorting with IEnumerable data. Automatic sorting is only supported with DataView, DataTable, and DataSet.

There are 2 ways to solve this issue. We discussed about one way of solving it in Part 45.

In this video, we will discuss about solving it without using a dataset. There are 2 changes that we need to make.
1. Change the implementation of GetAllEmployees() method to accept a parameter that specifies the column by which we want to sort. We will then use this parameter when building the sqlquery.
// sortColumn parameter will be used in order by clause later
public static List<Employee> GetAllEmployees(string sortColumn)
{
    List<Employee> listEmployees = new List<Employee>();

    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    using (SqlConnection con = new SqlConnection(CS))
    {
        string sqlquery = "Select * from tblEmployee";
        // if sortColumn is not null or empty
        // then append order by clause to the sql query
        if (!string.IsNullOrEmpty(sortColumn))
        {
            sqlquery += " order by " + sortColumn;
        }

        SqlCommand cmd = new SqlCommand(sqlquery, 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;
}

2. Specify ObjectDataSource1 control's, SortParameterName attribute. 
<asp:ObjectDataSource ID="ObjectDataSource1" 
    TypeName="Demo.EmployeeDataAccessLayer"
    SortParameterName="sortColumn"
    SelectMethod="GetAllEmployees"
    runat="server">
</asp:ObjectDataSource>

Notice that we have set SortParameterName="sortColumn". This is the parameter used by GetAllEmployees(string sortColumn) method.

Here is the complete HTML
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    AllowSorting="True" DataSourceID="ObjectDataSource1">
    <Columns>
        <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>
<br />
<asp:ObjectDataSource ID="ObjectDataSource1"
    TypeName="Demo.EmployeeDataAccessLayer"
    SortParameterName="sortColumn" SelectMethod="GetAllEmployees"
    runat="server">
</asp:ObjectDataSource>