Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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>

1 comment:

  1. A warm hello to you. First of all I would like to express many thanks for all your kind work that you are sharing here through your teachings and videos. You are a great gifted teacher who I am sure is helping many people all over the world.

    I was wondering if you had any videos/ tutorials similar to this that could advise me on the best way to sort through gridview data by user entering a value, say a name - and then to search through the data to display the filtered results?

    I am a bit lost on how to achieve this. I have watched so many of your videos now, that I think I have over confused myself. I think after watching some of your videos it seems ObjectDataSource is best to use - but not sure.

    Could you please advise?

    Many Thanks from Australia :)

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.