Server Intellect

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: <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" />

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.<br /><br />";
BindRepeater();
}
else
{
lit_Status.Text = "Unable to add person.<br /><br />";
}
}

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: <asp:DropDownList ID="ddl_Cities" runat="server"
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!

Leave a Comment

Comments on this Article

Post a Comment
Name:
Website:
Email:
Comments:

#1 Rusty68

Posted By: Rusty68 | 4.02.2009 at 12:05 PM

I plan to tell a friend about this since I think it would be helpful to him.

#2 Dulwan Baddewithana

Posted By: Dulwan Baddewithana | 4.08.2009 at 9:35 PM

Guys all I have to say is it's nicely done..

#3 download video poker games for free

Posted By: download video poker games for free | 7.17.2009 at 6:13 AM

I've tried Josh's dynamic data filtering 2 month ago all works except MultiSelect for some reason. Did you tried MultiSelect?

#4 management degree

Posted By: management degree | 10.15.2009 at 5:46 AM

This is really a great post . I am very much glad to know about tour dates. Thnak you so much for this posting.

#5 management diploma

Posted By: management diploma | 10.15.2009 at 5:46 AM

Throughout her career Jessica has met and touched the lives of thousands of fans across the world

#6 management diploma

Posted By: management diploma | 10.15.2009 at 5:46 AM

Does anyone know if and when Jessica will be coming to Florida? Really want to see her live but haven't had a chance to yet.

#7 management school

Posted By: management school | 10.15.2009 at 5:47 AM

i must say she has made her career as the most succeeded career... congrats for that!

#8 Marketing degree

Posted By: Marketing degree | 10.15.2009 at 5:47 AM

Em this is great stuff and i feel really good by looking at it.Your work is very impressive and i hope you will continue doing good work.I appreciate your work dude

#9 marketing diploma

Posted By: marketing diploma | 10.15.2009 at 5:47 AM

Kenali dan kunjungi objek wisata di pandeglang is very late to comment on this post. However, I do like the way you announce something. any update of this event?

#10 Tiffany Rings

Posted By: Tiffany Rings | 10.23.2009 at 7:39 AM

i like

#11 web hosting reviews

Posted By: web hosting reviews | 11.09.2009 at 5:18 PM

I was having such problem in inserting a new object into a SQL Database, and not to mention filtering data from the database using the custom class but thanks to this article, now I can work easily do that. Very imformative and step by step guided article. Thanks.

#12 baska voda

Posted By: baska voda | 11.11.2009 at 10:37 PM

We need to apply certain custom objects in C language if we want to filter the data. Thanks

#13 Promotional Products

Posted By: Promotional Products | 11.16.2009 at 2:48 AM

Using the custom data in C# object like inserting and filtering has become easy by the above mentioned. You just need to enter the code and no need of separate coding. Thanks

#14 Gucci Eyeglasses

Posted By: Gucci Eyeglasses | 11.20.2009 at 2:27 PM

Inserting data filters in the C# is really very helpful, thanks for sharing the code.

#15 Annuity Calculator

Posted By: Annuity Calculator | 11.23.2009 at 1:31 AM

Hey This is a great post. Thanks for taking the effort to put it up here. Really appreciate it.

#16 free games

Posted By: free games | 11.24.2009 at 10:56 PM

Throughout her career Jessica has met and touched the lives of thousands of fans across the world

#17 Casino Online

Posted By: Casino Online | 11.25.2009 at 11:27 AM

nice post thank you

#18 new bank account

Posted By: new bank account | 12.01.2009 at 11:23 AM

I now know how to insert a new object into a SQL Database. Thanks

#19 Non Chexsystems Banks

Posted By: Non Chexsystems Banks | 12.02.2009 at 4:38 AM

I must say, the article has been written with a lot of care so as to make people understand it in a simple & easy way. Appreciate it.

#20 james

Posted By: james | 12.02.2009 at 4:57 AM

Very helpful post I must say. Detailed instruction makes it easier to understand.Thanks.till next time

<a href="http://movingtruck-rental.com">Truck Renting</a>

#21 work at home

Posted By: work at home | 12.06.2009 at 8:19 AM

Yes this article is really very nice. Thank you author.

#22 Limousine NYC

Posted By: Limousine NYC | 12.07.2009 at 11:21 AM

Very helpful post for Inserting and Filtering Data using Custom Objects in C#. Codes are very helpful. Thanks for sharing.

#23 Limousine NYC

Posted By: Limousine NYC | 12.07.2009 at 11:21 AM

Very helpful post for Inserting and Filtering Data using Custom Objects in C#. Codes are very helpful. Thanks for sharing.

#24 Collections Etc.

Posted By: Collections Etc. | 12.11.2009 at 3:59 PM

Very interesting read! I had a blast loading the information. Looking forward to your future posts.

#25 electric golf trolleys

Posted By: electric golf trolleys | 12.14.2009 at 11:35 AM

The post is written in very a good manner and it entails many useful information about Inserting and filtering data using custom objects in C for me. I am happy to find your distinguished way of writing the post. Now you make it easy for me to understand and implement the concept. Thank you for the post.

#26 chimineas

Posted By: chimineas | 12.14.2009 at 10:25 PM

Great resource. Thanks for the info.

#27 electric water heaters

Posted By: electric water heaters | 12.19.2009 at 6:14 AM

Really nice and informative post about Inserting and Filtering Data using Custom Objects in C#. I gained very helpful knowledge from this post. Codes are really helpful. Thanks

#28 making beats software

Posted By: making beats software | 12.22.2009 at 7:15 AM

Really nice and helpful post for Using Custom Objects to Manage Data in C#. I feel Inserting and Filtering is easy by reading this post. thanks for sharing.

#29 24 Hr Fitness

Posted By: 24 Hr Fitness | 12.23.2009 at 10:26 AM

My nose just bled. Just kidding. A very comprehensive dissection of making a program. Very unique concept! thanks and keep posting

#30 Implant Dentist in London

Posted By: Implant Dentist in London | 12.24.2009 at 4:35 AM

Thanks for sharing all the vital codes, this will be of great help for many working professionals. Thanks!

#31 links of london

Posted By: links of london | 12.26.2009 at 12:24 AM

i like

#32 poker on line casinos

Posted By: poker on line casinos | 12.26.2009 at 1:08 AM

Fact is the MVC framework means more work for me without much benefit. I like the speed and convenience of building small pages with ASP.NET Webforms. More work and less time delivering value to the customers wow MVC is simply brilliant... NAWT.

#33 Tom Ford Sunglasses

Posted By: Tom Ford Sunglasses | 12.27.2009 at 7:13 PM

The post is written in very a good manner and it contains many useful information for me. You have a very impressive writing style. Thanks for sharing.

#34 Geek Gadgets

Posted By: Geek Gadgets | 12.28.2009 at 5:29 PM

These kind of articles are always attractive and I am happy to find so many good point here in the post, writing is simply great, thanks for sharing.

#35 car agmes

Posted By: car agmes | 1.01.2010 at 5:53 AM

Your work is very impressive and i hope you will continue doing good work.

#36 Watch The Spy Next Door Online

Posted By: Watch The Spy Next Door Online | 1.02.2010 at 6:14 PM

Excellent! THanks

#37 Galvanizing Service Virginia

Posted By: Galvanizing Service Virginia | 1.05.2010 at 4:43 AM

This is bit complicated but after implementation of this code it becomes far more easier. Thanks for sharing this vital information. :)

#38 tiensshop

Posted By: tiensshop | 1.09.2010 at 2:08 AM

Thanks for this article. I have an idea now on how to insert new records to a database, as well as filter the data.This is great, thanks a lot for sharing.

#39 tiensshop

Posted By: tiensshop | 1.09.2010 at 3:08 AM

Thanks for this information, it is important to know these tour days, thanks a lot.

#40 Used Car Dealers

Posted By: Used Car Dealers | 1.11.2010 at 8:11 AM

This will be pretty useful for many working professionals as well as students working on some sorts of projects. :)

#41 Zahnimplantate

Posted By: Zahnimplantate | 1.11.2010 at 8:39 AM

Great Tutorial! Very appreciated... searched the whole day to find my answer.

#42 Bauchmuskel

Posted By: Bauchmuskel | 1.11.2010 at 8:42 AM

I was doing it wrong all the time... Now all of a sudden everything works perfect. If i would only have known earlier! Great site btw!

#43 leaky gut syndrome

Posted By: leaky gut syndrome | 1.14.2010 at 12:11 PM

I love filtering data. Thanks for all your help. filtering data has really helped our business.

#44 flowers dallas

Posted By: flowers dallas | 1.16.2010 at 1:11 PM

THis is great job!

#45 web design ny

Posted By: web design ny | 1.18.2010 at 3:46 AM

This is really a great post . I am very much glad to know about tour dates. Thnak you so much for this posting.

#46 Russian Food

Posted By: Russian Food | 1.18.2010 at 3:47 AM

I am happy to find this post very useful for me, as it contains lot of information.

#47 1800contacts

Posted By: 1800contacts | 1.18.2010 at 1:34 PM

When the sources of information is ran by an undercurrent of a language that is reserved for those who have dedicated their time in being versed at it, have become the very essence of how information is carried about, the world will soon be surprised on how much information they already have missed.

#48 Movers Staten Island

Posted By: Movers Staten Island | 1.19.2010 at 4:37 AM

Throughout her career Jessica has met and touched the lives of thousands of fans across the world

#49 tour bus new York

Posted By: tour bus new York | 1.19.2010 at 4:37 AM

Hi webmaster, commenters and everybody else ! The blog was absolutely fantastic! Lots of great information and inspiration, both of which we all need!b Keep 'em coming... you all do such a great job at such Concepts... can't tell you how much I, for one appreciate all you do.

#50 Internet Marketing Affiliate Program

Posted By: Internet Marketing Affiliate Program | 1.19.2010 at 4:21 PM

Very informative post. Some of it was a little above my head but good infor just the same

#51 Dustin Smith

Posted By: Dustin Smith | 1.20.2010 at 8:57 AM

I must say this is very informative and well guided article to insert and filter data using custom objects in C#. It has made simpler especially for beginners to implement the concept. This blog is awesome and I must congratulate author for sharing the knowledge with us.

#52 business accounting software

Posted By: business accounting software | 1.20.2010 at 7:15 PM

Great post! Anybody tried MultiSelect?

#53 dentist in phoenix

Posted By: dentist in phoenix | 1.21.2010 at 1:45 PM

Tutorial about Inserting and Filtering of Data by using Custom objects is very helpful for me, I am happy to find it. Thanks for sharing the coding as well, that's makes the process very simple.

#54 free tips to lose stomach fat

Posted By: free tips to lose stomach fat | 1.23.2010 at 1:48 PM

interesting post thanks for sharing , I will bookmark you for future referrence

#55 Eye Care Associates

Posted By: Eye Care Associates | 1.26.2010 at 10:39 PM

Very thorough and well thought out. Thanks

#56 Karastan Rugs

Posted By: Karastan Rugs | 1.27.2010 at 11:41 PM

Thank for the information...they are very usful...

#57 Spyware Blocker Reviews

Posted By: Spyware Blocker Reviews | 1.28.2010 at 12:09 AM

Great info..very useful..keep writing...thanks

#58 Organic SEO Services

Posted By: Organic SEO Services | 1.28.2010 at 8:16 AM

It will really useful to use our Custom Class to insert new data into a database and also filter data.

#59 Tiensestore

Posted By: Tiensestore | 2.04.2010 at 6:16 AM

I admire how much you know. I really envy your knowledge. But what pleases me the most is how you free share it with others. Thanks a lot

#60 stair climber hand truck

Posted By: stair climber hand truck | 2.04.2010 at 12:44 PM

Very nice tutorial on inserting & deleting using custom objects.I didn't have the clear idea earlier,thanks for it.

#61 pop up stands

Posted By: pop up stands | 2.06.2010 at 5:21 PM

had been searching everywhere to find out how to use custom class. thanks

#62 watch she's out of me league online

Posted By: watch she's out of me league online | 2.08.2010 at 6:48 AM

I plan to tell a friend about this since I think it would be helpful to him.

#63 watch gamer online

Posted By: watch gamer online | 2.08.2010 at 6:49 AM

Guys all I have to say is it's nicely done..

#64 watch ninja assassin online

Posted By: watch ninja assassin online | 2.08.2010 at 6:49 AM

I've tried Josh's dynamic data filtering 2 month ago all works except MultiSelect for some reason. Did you tried MultiSelect?

#65 internet marketing affiliate program

Posted By: internet marketing affiliate program | 2.10.2010 at 12:20 PM

Great read but the information is a little above my head!

#66 ned i vekt

Posted By: ned i vekt | 2.11.2010 at 3:32 PM

Thank you for posting this, very useful. I need this for my upcoming website:)

#67 MB2-631

Posted By: MB2-631 | 2.13.2010 at 5:02 AM

We ifromative article =]

#68 tadalafil

Posted By: tadalafil | 2.17.2010 at 3:08 PM

this super article! Is help me!

#69 Monster Jam toys

Posted By: Monster Jam toys | 2.23.2010 at 7:45 PM

I was looking for a tutorial related to using Custom Objects in C# as it is one of the difficult area for me. Your post has great information for me, about inserting and filtering data with coding. Thank you.

#70 Acai Berry

Posted By: Acai Berry | 2.28.2010 at 12:50 PM

I am happy to find so many useful information here in the post, we need develop more strategies in this regard, thanks for sharing.

#71 Increase vertical jump

Posted By: Increase vertical jump | 3.06.2010 at 5:17 PM

Coding is great! I will definitively tell my best friend about this!

#72 free puzzle games

Posted By: free puzzle games | 3.07.2010 at 3:32 AM

Could you pls provide more details on this subject?? BTW your blog is wonderful. Sincerely!!!

#73 dermajuv reviews

Posted By: dermajuv reviews | 3.09.2010 at 4:59 AM

I happy to find many good point here in the post, writing is simply great, thank you for the post..thanks for giving us nice info.Fantastic walk-through.I appreciate this post.

#74 350-001

Posted By: 350-001 | 3.18.2010 at 4:17 AM

Excellent! THanks