Inserting and Filtering Data using Custom Objects in C#

Category: ASP.NET

Using Custom Objects to Manage Data in C# - Inserting and Filtering

Introduction

In the previous article, Creating Custom Objects in ASP.NET 3.5 and C#, we looked at how to create Custom Objectsand a Collection Class to display database data to a page. In this article, we will be building on this and showing you how to use the same object to insert new records to the database, as well as filter the data.

Again, this article is written with Visual Studio 2008, but 2005 should be largely the same. It is recommended that you follow the previous article, linked above, before proceeding.

What we will learn in this article:

  • How to use a custom class to insert a new object into a SQL Database;
  • How to filter data from the database using the custom class.

Getting Started
We will be using the same project as used in the previous article, metioned above. You can download the project using the download link at the bottom of either this article or the previous article. 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:

CREATE 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()

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, which 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 write our INSERT statement and finally, we SELECT the Scope Identity so that we can tell if the record was successfully added.
Next up, the SP to filter People by City:

CREATE PROCEDURE dbo.sp_GetPeopleFromCity

@City varchar(50)

AS

SELECT * FROM People WHERE City = @City

We migrated our web sites to Server Intellect over one weekend and the setup was so smooth that we were up and running right away. They assisted us with everything we needed to do for all of our applications. With Server Intellect's help, we were able to avoid any headaches!

This will simply return all records from the database that match the City string that is passed in.
To make use of these Stored Procedures, we need to now create the methods within the Person class. Let us first start with the Insert:

public static Int32 InsertPerson(Person thePerson)
{
Int32 newPersonID = 0;
SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
try
{
SqlCommand cmd = new SqlCommand("sp_InsertPerson", connection);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@FirstName", thePerson.FirstName);
cmd.Parameters.AddWithValue("@LastName", thePerson.LastName);
cmd.Parameters.AddWithValue("@City", thePerson.City);
cmd.Parameters.AddWithValue("@Age", thePerson.Age);
cmd.Parameters.AddWithValue("@DateTimeAdded", DateTime.Now);

connection.Open();
newPersonID = Convert.ToInt32(cmd.ExecuteScalar());
connection.Close();
}
catch
{
connection.Close();
}
return newPersonID;
}

We have the method returning an Int32 value, which will be the ID of the newly-created record. If it returns 0, we know that the record failed to create. When we call the Stored Procedure, we need to pass the parameters too. We are passing a Person object to the method so that we can pass its properties to the Stored Procedure to be inserted. We have to make sure the SP parameters are of the same type as we specified. Finally, we open the connection and execute the command, which will return the Scope Identity, which in turn, we return from the method (newPersonID).

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 GetPeopleFromCity(String theCity)
{
People PeopleCollection = new People();
SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
try
{
SqlCommand cmd = new SqlCommand("sp_GetPeopleFromCity", connection);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@City", theCity);

connection.Open();
SqlDataReader objReader = cmd.ExecuteReader();
while (objReader.Read())
{
Person newPerson = new Person(objReader);
PeopleCollection.Add(newPerson);
}
objReader.Close();
connection.Close();
}
catch
{
connection.Close();
}
return PeopleCollection;
}

We moved our web sites to Server Intellect and have found them to be incredibly professional. Their setup is very easy and we were up and running in no time.

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:

Last Name:

City Name:

Age:

Notice that we reference a handler for the OnClick event of the button. We will also need to add a Literal control, lit_Status, in order to notify the user if the Person was successfully added or not. The OnClick handler will look something like this:

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.

";
BindRepeater();
}
else
{
lit_Status.Text = "Unable to add person.

";
}
}

Because the Insert method we created takes a Person object as a parameter, that is what we need to supply. It is real simple to do this, though, as we declare a new object and then set the properties (making sure the data types match). Make sure that your code-behind is referencing the People namespace before trying to reference the class, though.
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: AutoPostBack="true" OnSelectedIndexChanged="ddl_Cities_SelectedIndexChanged" />

We chose Server Intellect for its dedicated servers, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.

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.

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. And with ASP.NET 3.5, AJAX is already built-in.

What we have Learned

We have learned how to use our Custom Class to insert new data into a database and also filter data.



Download Project Source - Enter your Email to be emailed a link to download the Full Source Project used in this Tutorial!



100% SPAM FREE! We will never sell or rent your email address!