GridView insert update delete in asp.net using objectdatasource - Part 24

Suggested Videos 
Part 21 - GridView TemplateField in asp.net
Part 22 - Using validation controls with asp.net gridview when editing data
Part 23 - GridView insert update delete in asp.net




In this video we will discuss about performing an insert, update and delete on asp.net gridview control using objectdatasource. Please watch Part 23 of asp.net gridview tutorial, before proceeding. In Part 23, we discussed about doing exactly the same thing using sqldatasource control. So we will be modifying the same example to use objectdatasource control instead of sqldatasource control.



Add a class file with name EmployeeDataAccessLayer.cs to your project. Copy and paste the following code.
using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;

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
{
    // Select Method for ObjectDataSource control
    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;
    }

    // Delete Method for ObjectDataSource control
    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();
        }
    }

    // Update Method for ObjectDataSource control
    public static int 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 paramOriginalEmployeeId = new 
                SqlParameter("@EmployeeId", EmployeeId);
            cmd.Parameters.Add(paramOriginalEmployeeId);
            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();
            return cmd.ExecuteNonQuery();
        }
    }

    // Insert Method for ObjectDataSource control
    public static int InsertEmployee(string Name, string Gender, string City)
    {
        string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
        using (SqlConnection con = new SqlConnection(CS))
        {
            string updateQuery = "Insert into tblEmployee (Name, Gender, City)" + 
                " values (@Name, @Gender, @City)";
            SqlCommand cmd = new SqlCommand(updateQuery, con);
            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();
            return cmd.ExecuteNonQuery();
        }
    }
}
}

1. Compile your project. 
2. On WebForm1.aspx delete "SqlDataSource1" control. 
3. Drag and drop ObjectDataSource control onto the webform. 
4. Now configure ObjectDataSource control to use 
GetAllEmployees() as "Select" Method
DeleteEmployee(int EmployeeId) as "Delete" method
UpdateEmployee(int EmployeeId, string Name, string Gender, string City) as "Update" method
InsertEmployee(string Name, string Gender, string City) as "Insert" method

The HTML of ObjectDataSource1 control, should be as shown below
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" 
    DeleteMethod="DeleteEmployee" InsertMethod="InsertEmployee" 
    SelectMethod="GetAllEmployees" TypeName="Demo.EmployeeDataAccessLayer" 
    UpdateMethod="UpdateEmployee">
    <DeleteParameters>
        <asp:Parameter Name="EmployeeId" Type="Int32" />
    </DeleteParameters>
    <InsertParameters>
        <asp:Parameter Name="Name" Type="String" />
        <asp:Parameter Name="Gender" Type="String" />
        <asp:Parameter Name="City" Type="String" />
    </InsertParameters>
    <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>

Now change DataSourceID property of GridView1 control from "SqlDataSource1" to "ObjectDataSource1" and Save. You will get a message asking, if you want to "Refresh Fields and Keys for GridView1". Click No.

Finally in code-behind file change the implementation of "lbInsert_Click" as shown below.
protected void lbInsert_Click(object sender, EventArgs e)
{
    ObjectDataSource1.InsertParameters["Name"].DefaultValue =
        ((TextBox)GridView1.FooterRow.FindControl("txtName")).Text;
    ObjectDataSource1.InsertParameters["Gender"].DefaultValue =
        ((DropDownList)GridView1.FooterRow.FindControl("ddlInsertGender")).SelectedValue;
    ObjectDataSource1.InsertParameters["City"].DefaultValue =
        ((TextBox)GridView1.FooterRow.FindControl("txtCity")).Text;
    ObjectDataSource1.Insert();
}