GridView With ObjectDataSource

In this article I will show you how you can use the ObjectDataSource with the GridView control to do editing, updating, deleting and adding new records. There are several ways to perform these operations, I am using the simplest approach. The project files are also attached with this article so please feel free to download them.

Introduction:

In this article I will show you how you can use the ObjectDataSource with the GridView control to do editing, updating, deleting and adding new records. There are several ways to perform these operations, I am using the simplest approach. The project files are also attached with this article so please feel free to download them.

This application was implemented in Visual Studio.NET 2005 Professional.

Creating the User Interface:

Let's first see the user interface that we are going to be using in this article.

As you can see in the image above our GridView contains five columns. UserID, FirstName and LastName are template columns and Edit and Delete columns are Command Columns which are added using smart tag option (You can view the smart tag if you right click on the GridView control).

In the footer I have added TextBoxes for the FirstName and LastName as I will be inserting new records. The Add button simply adds the new records to the database.

Below is the complete HTML code of the GridView control:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowEditing="GridView1_RowEditing" DataKeyNames="ID" OnRowUpdating="GridView1_RowUpdating" OnRowUpdated="GridView1_RowUpdated" OnRowDeleting="GridView1_RowDeleting" ShowFooter="True" OnRowCommand="GridView1_RowCommand" DataSourceID="objUser">

<asp:TemplateField HeaderText="UserID">

<asp:Label ID="lblUserID" runat="server" Text='<%# Bind("ID") %>'></asp:Label>

</ItemTemplate>

<asp:Button ID="Btn_Add" runat="server" CommandName="AddUser" Text="Add" />

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="FirstName">

<asp:Label ID="lblFirstName" Text='<%# Eval("FirstName") %>' runat="server"></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtFirstName" Text='<%# Eval("FirstName") %>' runat="server"></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="txtAddFirstName" runat="server"></asp:TextBox>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="LastName">

<asp:Label ID="lblLastName" Text='<%# Eval("LastName") %>' runat="server"></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtLastName" Text='<%# Eval("LastName") %>' runat="server"></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="txtAddLastName" runat="server"></asp:TextBox>

</FooterTemplate>

</asp:TemplateField>

<asp:CommandField HeaderText="Edit" ShowEditButton="True" />

<asp:CommandField HeaderText="Delete" ShowDeleteButton="True" />

</Columns>

</asp:GridView>

Creating a User Class:

Since, we are working with ObjectDataSource control we need to make an entity class. Take a look at the simple User class below:

public class User

{

/* PRIVATE FIELDS */

private int _ID;

private string _firstName;

private string _lastName;

/* PUBLIC PROPERTIES */

public int ID

{

get { return _ID; }

}

public string FirstName

{

get

{

if (!String.IsNullOrEmpty(_firstName))

return _firstName;

else return null;

}

set { _firstName = value; }

}

public string LastName

{

get

{

if (!String.IsNullOrEmpty(_lastName))

return _lastName;

else return null;

}

set { _lastName = value; }

}

This is just a small part of the class you can view the whole class when you download the project files. The class contains three properties which are ID, FirstName and LastName. We will be playing around with these properties.

Creating the ObjectDataSource Control:

After setting up the GridView control on the form the next task is to create an ObjectDataSource control. This is pretty simply just drag and drop the ObjectDataSource control from the toolbox on the form and that's it. After defining all the properties of the ObjectDataSource control it looks something like this:

<asp:ObjectDataSource DeleteMethod="DeleteUser" InsertMethod="AddUser" ID="objUser" runat="server" SelectMethod="GetAllUsers" UpdateMethod="UpdateUser" TypeName="User">

</asp:ObjectDataSource>

IMPLEMENTING A SELECT METHOD:

The first task is to display the data in the GridView control. We need a method that can go to the database and fetch the results. I have implemented this method in the User class. Take a look at the code below:

public List<User> GetAllUsers()

{

List<User> userList = new List<User>();

SqlConnection myConnection = new SqlConnection(ConnectionString);

SqlCommand myCommand = new SqlCommand(SP_GET_ALL_USERS, myConnection);

myConnection.Open();

SqlDataReader reader = myCommand.ExecuteReader();

while (reader.Read())

{

User user = new User((int)reader["UserID"],(string) reader["FirstName"],(string) reader["LastName"]);

userList.Add(user);

}

myConnection.Close();

reader.Close();

myCommand.Dispose();

if (userList != null && userList.Count > 0)

return userList;

else return null;

}

As you can see it is a simple method that returns the Generic List of type User. You have to set the ObjectDataSource SelectMethod property to "GetAllUsers" and set the DataSourceID of GridView to "objUser" (objUser is the name of the ObjectDataSource control). Once, the GridView's DataSourceID is set to the ObjectDataSource control it will automatically bind the ObjectDataSource to the GridView control. By default the SelectMethod is bind to the GridView control.

IMPLEMENTING THE INSERT METHOD:

Now, let's see how we can implement the insert functionality in the GridView. I have added TextBoxes in the Footer of the GridView control which can also be added in the Header of the GridView control. Let's see the AddUser method which is defined in the User class.

public void AddUser(string firstName, string lastName)

{

SqlConnection myConnection = new SqlConnection(ConnectionString);

SqlCommand myCommand = new SqlCommand(SP_ADD_USER, myConnection);

myCommand.Parameters.AddWithValue("@FirstName", firstName);

myCommand.Parameters.AddWithValue("@LastName", lastName);

myConnection.Open();

myCommand.ExecuteNonQuery();

myConnection.Close();

}

The AddUser method simply accepts firstName and lastName as a parameter and adds them to the database. SP_ADD_USER is a simply string which contains the insert query you can download the complete code and view it.

The "Add" button inside the GridView has the CommandName property set to "AddUser". This way we can easily catch this event inside the GridView_RowCommand event. Take a look at the code below which catches the Add button click.

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)

{

if (e.CommandName == "AddUser")

{

string firstName = ((TextBox)GridView1.FooterRow.FindControl("txtAddFirstName")).Text;

string lastName = ((TextBox)GridView1.FooterRow.FindControl("txtAddLastName")).Text;

objUser.InsertParameters.Add("firstName", firstName);

objUser.InsertParameters.Add("lastName", lastName);

objUser.Insert();

}

The first parameter of the objUser.InsertParameters.Add("thisone",firstName) has to be the exact same in the AddUser method in the User class.

This also applies to objUser.DeleteParameters and objUser.UpdateParameters.

Once, the parameters reaches the AddUser method in the User.cs class then it is a simple insert using the SqlCommand object and running a simply INSERT query.

IMPLEMENTING THE DELETE METHOD:

I am sure you are with me so far. Okay on to delete functionality, once again first we implement a method in the User.cs class "DeleteUser". Here is the implementation of "DeleteUser" method:

public void DeleteUser(int id)

{

SqlConnection myConnection = new SqlConnection(ConnectionString);

SqlCommand myCommand = new SqlCommand(SP_DELETE_USER, myConnection);

myCommand.Parameters.AddWithValue("@UserID", id);

myConnection.Open();

myCommand.ExecuteNonQuery();

myConnection.Close();

}

As you can see it simply accepts the id as a parameter and attaches to the command object and runs the appropriate delete query. Now that you have made the "DeleteUser" method you can implement the GridView_RowDeleting event which is fired when the row is being deleted.

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

//int index = e.RowIndex;

//int userID = Convert.ToInt32(GridView1.Rows[index].Cells[0].Text);

//objUser.DeleteParameters.Add("userID",TypeCode.Int32,userID.ToString());

int index = e.RowIndex;

int userID = Convert.ToInt32(((Label)GridView1.Rows[index].FindControl("lblUserID")).Text);

objUser.DeleteParameters.Add("id",TypeCode.Int32,userID.ToString());

objUser.Delete();

//BindData();

}

First I get the index of the row that is being clicked using the e.RowIndex property. After I get the index I simply use the FindControl method to locate the userID. lblUserID is the name of the Label which displays the UserID. Once again remember that the parameters "id" should be the exact same as you have defined in the DeleteUser method in User.cs class.

You must be wondering that why have I placed the commented out code in the above code snippet. That code can be used if you are using BoundColumns. I don't think it is a good idea to use the Bound Columns when you plan to extract values from those columns, the reason being that BoundColumns depends on the index position as you can see Cells[0],Col[1] and so on. If later that index changes then you need to go to your code and change it manually.

IMPLEMENTING THE UPDATE METHOD:

Finally, we are to the last method which is update. To be really honest this took me some time since I was trying to use the e.NewValues and it was keep returning me nulls. Anyway, here is a small and simply solution but I am sure there are many better ways to do the same task.

First let's take a look at the UpdateUser method in the User.cs class.

public void UpdateUser(int id, string firstName, string lastName)

{

SqlConnection myConnection = new SqlConnection(ConnectionString);

SqlCommand myCommand = new SqlCommand(SP_UPDATE_USER, myConnection);

myCommand.Parameters.AddWithValue("@FirstName", firstName);

myCommand.Parameters.AddWithValue("@LastName", lastName);

myCommand.Parameters.AddWithValue("@UserID", id);

myConnection.Open();

myCommand.ExecuteNonQuery();

myConnection.Close();

}

I don't think I have to explain the above method. It simply takes id, firstName, lastName and updates the record. Now let's take a look at the GridView_RowUpdating event which is fired when the row is being updated.

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

int userID = Convert.ToInt32(((Label)GridView1.Rows[e.RowIndex].FindControl("lblUserID")).Text);

string firstName = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtFirstName")).Text;

string lastName = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtLastName")).Text;

objUser.UpdateParameters.Add("id", TypeCode.Int32, userID.ToString());

objUser.UpdateParameters.Add("firstName", firstName);

objUser.UpdateParameters.Add("lastName", lastName);

objUser.Update();

GridView1.EditIndex = -1;

}

I am using the same approach that I used for "Delete" and "Add" functionality. In short I am using the rowIndex to find the row that the user has clicked and then using FindControl method I find the new value from the TextBox control. And finally send the new values to the UpdateUser method which is defined in User.cs file.

Although I have demonstrated a very simple way of update, delete and add functionality I am sure there exist a much better way of doing the same tasks more efficiently.

I have attached the source code files with this project so please free to download them.

I hope you liked this article, happy coding!

If you are one of the thousands that visit GridViewGuy for your .NET articles and resources, you might be interested in making a donation. Extra cash helps pay for the hosting services and speed things up around here, and makes this website possible.

Make a Donation

Once, again thank you very much and remember its because of you FINE people that this website is up and running.

Export Button is a custom control that let's you export your DataGrid or TextBox data to several different formats. The control is extremely easy to use and also exposes design time features. In this article I will discuss some of the features of the Export Button and how it benefits the developer.

BUY IT NOW