Inserting and Filtering Data using Custom Objects in ASP.NET 4.0 C#
In the previous tutorial, Creating Custom Objects in ASP.NET 4.0 C#, we looked at how to create Custom Objects and a Collection Class to display database data on a page. In this tutorial, we will be building onto this and showing you how to use the same object to insert new records to the database, as well as filter the data. It is recommended that you follow the previous tutorial, linked above, before proceeding.
Creating the Procedure
We will be using the same project as used in the previous tutorial, metioned above. You can download the project using the download link at the bottom of either this tutorial or the previous tutorial. Up to this point, we have our Custom class, Person, and our Collection Class, People, both of which reside in the People namespace. We have a default constructor, and a constructor to SetObjectData, then we have just two methods: SetObjectData and GetAllPeople. We will be adding two new methods in this tutorial: InsertPerson and GetPeopleFromCity. For each of these methods, we will need to create a Stored Procedure, as both will require interaction with the database. Let us first write the Stored Procedures. Right-click the Stored Procedures folder of the database in Server Explorer, then choose Add New Stored Procedure. Enter the following and the Save:
ALTER PROCEDURE dbo.sp_InsertPerson
@FirstName varchar(50),
@LastName varchar(50),
@City varchar(50),
@Age smallint,
@DateTimeAdded datetime
AS
INSERT INTO People
(FirstName, LastName, City, Age, DateTimeAdded)
VALUES (@FirstName, @LastName, @City, @Age, @DateTimeAdded)
SELECT SCOPE_IDENTITY()
Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.
Once Save is hit, the Stored Proced will be created and CREATE will change to ALTER. In this SP, we are specifying all Properties of Person except the ID. This is not required when creating a new record, as it will be auto-generated by the database. We have to make sure that the data types we specify match up with the ones declared in the table. Next, we will write our INSERT statement and finally, we’ll SELECT the Scope Identity so that we can tell if the record was successfully added. Next up, the SP to filter People by City:
ALTER PROCEDURE dbo.sp_GetPeopleFromCity
@City varchar(50)
AS
SELECT * FROM People WHERE City = @City
public Person(Int32 thePersonID)
{
SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
SqlCommand cmd;
cmd = new SqlCommand("sp_GetPersonByID", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PersonID", thePersonID);
connection.Open();
SqlDataReader objReader = cmd.ExecuteReader();
while (objReader.Read())
{
SetObjectData(objReader);
}
objReader.Close();
connection.Close();
}
The filter method is similar to the GetAllPeople method in that it sets and returns a People collection, but instead of retrieving all records, we will retrieve the ones that match the input string only. The method will look like this:
public static People GetAllPeople()
{
People PeopleCollection = new People();
SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
//try
//{
SqlCommand cmd = new SqlCommand("sp_GetAllPeople", connection);
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlDataReader objReader = cmd.ExecuteReader();
while (objReader.Read())
{
Person newPerson = new Person(objReader);
PeopleCollection.Add(newPerson);
}
objReader.Close();
connection.Close();
return PeopleCollection;
}
I just signed up at Server Intellect and couldn’t be more pleased with my fully scalable & redundant cloud hosting! Check it out and see for yourself.
Now to implement these methods, we will move to our ASPX page. Above our Repeater, we will add four text boxes, and a button. These will be for the addition of a new Person: First Name, Last Name, City, and Age. Next to that, we will have a DropDownList. This will be to filter the data by City. Our Insert form should look something like this:First Name: <asp:TextBox ID="fld_FirstName" runat="server" /><br />
Last Name: <asp:TextBox ID="fld_LastName" runat="server" /><br />
City Name: <asp:TextBox ID="fld_City" runat="server" /><br />
Age: <asp:TextBox ID="fld_Age" runat="server" Columns="3" /><br />
<asp:Button ID="btn_Add" runat="server" Text="Add" OnClick="btn_Add_OnClick" /><br />
protected void btn_Add_OnClick(object sender, EventArgs e)
{
Person personToAdd = new Person();
personToAdd.FirstName = fld_FirstName.Text;
personToAdd.LastName = fld_LastName.Text;
personToAdd.City = fld_City.Text;
personToAdd.Age = Convert.ToInt16(fld_Age.Text);
personToAdd.PersonID = Person.InsertPerson(personToAdd);
if (personToAdd.PersonID > 0)
{
lit_Status.Text = "Person added.<br /><br />";
BindRepeater();
}
else
{
lit_Status.Text = "Unable to add person.<br /><br />";
}
}
Once we have built our Person object, we call the Insert method and pass the object, then we check to see if the ID is greater than 0 – an indication if the add was successful, then notify the user accordingly. Once our class and methods are defined, we no longer need to deal with database logic in the code-behind, at page level. All really simple stuff.
The filter we are going to perform is slightly more complex. What we will do is populate the dropdown only with cities that are present in the database. This means that a user can only filter by Cities that exist. To do this, we first create our DropDown like so:
Filter by City: <asp:DropDownList ID="ddl_Cities" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddl_Cities_SelectedIndexChanged" />
Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.
Notice we set the dropdown to auto postback, and we set the event handler when an option is selected. We do this so that we can populate the data each time a selection is made. On page load, we populate the dropdown by selecting all records from the database and then going through each one and choosing distinct Cities to add to the dropdown:
protected void SetDropDown()
{
ddl_Cities.Items.Clear();
foreach (Person person in Person.GetAllPeople())
{
if (!ddl_Cities.Items.Contains(new ListItem(person.City,person.City)))
{
ddl_Cities.Items.Add(new ListItem(person.City,person.City));
}
}
ddl_Cities.Items.Insert(0, new ListItem("Show All","Show All"));
}
After all Cities are added, we insert a Show All option to revert back to displaying all records.
The event handler will look something like this:
protected void ddl_Cities_SelectedIndexChanged(object sender, EventArgs e)
{
if (ddl_Cities.SelectedValue == "Show All")
{
BindRepeater();
}
else
{
repeater_People.DataSource = Person.GetPeopleFromCity(ddl_Cities.SelectedValue);
repeater_People.DataBind();
}
}
The BindRepeater method was added in the previous article, and simply populates the repeater with all records from the database. If an option other than Show All is selected, then we call the method we just added – to filter by the selected City.
Success!
If you run this page now, you will get a form to input data to the database, and also the ability to filter the data by City. This can be made a lot more smoother and neater with the addition of a ScriptManager and UpdatePanel to the ASPX page using AJAX which is built into ASP.NET 4.0.
InsertingCustomObjects
