This tutorial will show you how to use Custom Objects to Insert to a SQL Server Database using VB.NET

In this tutorial, we are going to look at how we can create our own class to represent database data, and how we can use that object to insert new data to the database. ASP.NET is object-orientated and allows us to create our own objects to use, which can make it easier for us to work with and manipulate data. When working with databases, we can create custom objects that directly relate to our database structure. For instance, instead of working with DataTables, where we have to reference the data by the row number, we can create a class that represents our data and reference by Property names.

We will be creating an object Car, and storing a number of different Make and Models into a SQL Server Database. We will demonstrate how to use this class to create new objects and insert them into the database. We will also create a collection class of the Car object so that we can group them together, and display them with a Repeater control.

To begin, start a new web application in Visual Studio, then right-click the App_Data folder in Solution Explorer and choose to Add New Item.. SQL Server Database. You should see that the database opens up in the Server Explorer window. Right-click the Tables folder and choose Add New Table. We will create three columns: ID, Make, and Model. We will use the data types int, varchar(50), and varchar(50) respectively. We also want to make the ID the Primary Key, and the Primary Specification (in the Properties). Close and save this table when you’re done, and name it Cars.

Now right-click the table you just created in Server Explorer and choose to Show Table Data. Add one or two entries for sample data. Now go into your Web.config and add the following ConnectionString. Once done, we will work on our class.

We need to create a representation of this table data. Right-click your project in Solution Explorer, then choose to Add ASP.NET Folder > App_Code. Then right-click the App_Code folder and choose to Add New Item.. Class. Name it Car.vb

The first thing we will do is to wrap the new class in a namespace. This is useful to do especially when we have a lot of code, and multiple classes we can group together in one namespace. We also want to add a few references. Let’s add System.Data, System.Data.SqlClient, System.Web.Configuration, and System.Collections. We should have something that looks like this:

All we are presented with is a default constructor, which when called, will instantiate the class. What we will do next is build out the Properties. We will add these after the constructor:

Notice we declare the properties as public so that they are accessible beyond class level. We also set the default values and data type of each, which mirrors our Cars table structure.

Now that we have our Properties set, we can move onto the Methods. In this example, the only methods we will create are:

SetObjectData – used to set the object Car from a SqlDataReader;
InsertCar – used to insert a Car object into the database and return its ID;
GetAllCars – used to retrieve a collection of all Car objects from the database.

First, we will add the SetObjectData method and also create a second constructor, which will use the method to build the Car object:

Notice that this method is private, so it can only be called within the class.
Next, we add the second constructor:

Now that we have a constructor that can build an object from a SqlDataReader we pass to it, we need to create a method to pass it a SqlDataReader.
But first, let’s go ahead and add the Stored Procedures we will be using for the next two methods. Open up the Server Explorer window and right-click the Stored Procedures folder and choose Add New Stored Procedure. First, add GetAllCars:

Next, add InsertCar:

We are using the SELECT SCOPE_IDENTITY() to return the ID of the newly created record. We will use this to find out if the add was successful.
Now before we can add the GetAllCars method, we will need to create a Collection Class for it to use. So let’s get the InsertCar method in first, which will take a parameter of the Car object. Add the method below SetObjectData. We want to make use of the Connection String in the Web.config, and also we make sure to return the ID of the new record. Our method will look like this:

In order for us to return all the Cars in the database as objects, we are going to have to create a Collection class of the Car object. To do this, we inherit from the CollectionBase class. The Collection class is defined within the namespace, but outside of the Car class:

Finally for our class, we add the GetAllCars method, which will return the collection we just defined:

We grab all of the records from the database, and then loop through each one, adding them to a collection, which we eventually return.

Now we can move onto our ASPX page and build the front-end. All we need is a Repeater to display all the objects we will retrieve in the collection, and then two textboxes and a button to add new objects to the database.
Our ASPX page should look something like this:

We are displaying all of the properties of the Car object with the repeater. Using Eval, we reference the Property names of the object, as we will be binding the collection to the Repeater in the code-behind. Moving to the code-behind, we will bind the repeater on Page_Load, using the GetAllCars method. First, we need to add a reference to the class we just created:

Next, we bind the Repeater on page load with the Collection we created:

Finally, on the button click, we want to create a new object from the two text fields, then add that to the database, and update the Repeater to display all Cars:

Download Source Files