Inserting Custom Object to SQL Database in VB.NET
See more tutorials in Advanced. This post has No Comments.
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.
1 2 3 |
<connectionStrings> <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Cars.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings> |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Imports Microsoft.VisualBasic Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Imports System.Collections Namespace DBTutorials.Car ''' <summary> ''' Summary description for Car ''' </summary> Public Class Car Public Sub New() End Sub End Class End Namespace |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
#Region "properties" ''' <summary> ''' Gets or sets Car ID. [Default value is 0]. ''' </summary> Public Property ID() As Int32 Get Return _ID End Get Set(ByVal value As Int32) _ID = value End Set End Property Private _ID As Int32 = 0 ''' <summary> ''' Gets or sets the Make of the Car. [Default value is '']. ''' </summary> Public Property Make() As String Get Return _Make End Get Set(ByVal value As String) _Make = value End Set End Property Private _Make As String = "" ''' <summary> ''' Gets or sets the Model of the Car. [Default value is '']. ''' </summary> Public Property Model() As String Get Return _Model End Get Set(ByVal value As String) _Model = value End Set End Property Private _Model As String = "" #End Region |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
#Region "methods" ''' <summary> ''' Sets the Car object using the SqlDataReader object passed to it. ''' </summary> ''' <param name="theObjReader">SqlDataReader object that contains DB info to set the Car object.</param> Private Sub SetObjectData(ByVal theObjReader As SqlDataReader) Try Me._ID = Convert.ToInt32(theObjReader("ID")) Me._Make = theObjReader("Make").ToString() Me._Model = theObjReader("Model").ToString() Catch End Try End Sub #End Region |
Notice that this method is private, so it can only be called within the class.
Next, we add the second constructor:
1 2 3 4 5 6 7 |
''' <summary> ''' Contructor for Car object, sets the Car object from the SqlDataReader passed to it. ''' </summary> ''' <param name="theObjReader">SqlDataReader value that contains the DB information to set </param> Public Sub New(ByVal theObjReader As SqlDataReader) SetObjectData(theObjReader) End Sub |
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:
1 2 3 4 5 |
CREATE PROCEDURE dbo.sp_GetAllCars AS SELECT * FROM Cars |
Next, add InsertCar:
1 2 3 4 5 6 7 8 9 10 11 |
CREATE PROCEDURE dbo.sp_InsertCar @Make varchar(50), @Model varchar(50) AS INSERT INTO Cars (Make, Model) VALUES (@Make, @Model) SELECT SCOPE_IDENTITY() |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
''' <summary> ''' Inserts a Car object into the database. ''' </summary> ''' <param name="theCar">Car object to insert.</param> ''' <returns>Returns Int32 value of the new Car ID.</returns> Public Shared Function InsertCar(ByVal theCar As Car) As Int32 Dim newCarID As Int32 = 0 Dim connection As New SqlConnection(WebConfigurationManager.ConnectionStrings("ConnectionString").ToString()) Try Dim cmd As New SqlCommand("sp_InsertCar", connection) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue("@Make", theCar.Make) cmd.Parameters.AddWithValue("@Model", theCar.Model) connection.Open() newCarID = Convert.ToInt32(cmd.ExecuteScalar()) connection.Close() Catch connection.Close() End Try Return newCarID End Function |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
#Region "Collection Classes" ''' <summary> ''' Represents a collection of Car objects ''' </summary> <Serializable()> _ Public Class Cars Inherits CollectionBase Public Property TotalRecords() As Integer Get Return _TotalRecords End Get Set(ByVal value As Integer) _TotalRecords = value End Set End Property Protected _TotalRecords As Integer = 0 ''' <summary> ''' Adds a new Car to the collection ''' </summary> ''' <param name="theCar">The Car to add to the collection</param> Public Function Add(ByVal theCar As Car) As Integer Me.TotalRecords += 1 Return List.Add(theCar) End Function ''' <summary> ''' Inserts a new Car to the collection, at the specified index ''' </summary> ''' <param name="index">The Index to insert the Car into</param> ''' <param name="theCar">The Car to insert into the collection</param> Public Sub Insert(ByVal index As Int32, ByVal theCar As Car) List.Insert(index, theCar) End Sub ''' <summary> ''' Removes a Car from the collection ''' </summary> ''' <param name="theCar">The Car to remove from the collection</param> Public Sub Remove(ByVal theCar As Car) List.Remove(theCar) End Sub ''' <summary> ''' Checks to see if the Car object exists in the collection ''' </summary> ''' <param name="theCar">The Car to search for in the collection</param> ''' <returns>Returns true if the Car exists in the collection</returns> Public Function Contains(ByVal theCar As Car) As Boolean Return List.Contains(theCar) End Function ''' <summary> ''' Returns the index of the Car object in the collection (if it exists) ''' </summary> ''' <param name="theCar">The Car to search for in the collection</param> ''' <returns>Returns the int index of the Car object in the collection</returns> Public Function IndexOf(ByVal theCar As Car) As Integer Return List.IndexOf(theCar) End Function Public Sub CopyTo(ByVal array() As Car, ByVal index As Integer) List.CopyTo(array, index) End Sub Default Public Property Item(ByVal index As Integer) As Car Get Return CType(List(index), Car) End Get Set(ByVal value As Car) List(index) = value End Set End Property End Class #End Region |
Finally for our class, we add the GetAllCars method, which will return the collection we just defined:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
''' <summary> ''' Gets all the Cars in the database. ''' </summary> ''' <returns>Returns Cars Collection that contains all Cars.</returns> Public Shared Function GetAllCars() As Cars Dim CarsCollection As New Cars() Dim connection As New SqlConnection(WebConfigurationManager.ConnectionStrings("ConnectionString").ToString()) Try Dim cmd As New SqlCommand("sp_GetAllCars", connection) cmd.CommandType = CommandType.StoredProcedure connection.Open() Dim objReader As SqlDataReader = cmd.ExecuteReader() Do While objReader.Read() Dim newCar As New Car(objReader) CarsCollection.Add(newCar) Loop objReader.Close() connection.Close() Catch connection.Close() End Try Return CarsCollection End Function |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Make: <asp:TextBox ID="fld_Make" runat="server" /><br /> Model: <asp:TextBox ID="fld_Model" runat="server" /><br /> <asp:Button ID="btn_Add" runat="server" Text="Add" OnClick="btn_Add_OnClick" /> <br /> <br /> <asp:Repeater ID="repeater_Cars" runat="server"> <HeaderTemplate> <table width="350"><tr> <td><strong>ID</strong></td> <td><strong>Make</strong></td> <td><strong>Model</strong></td> </tr> </HeaderTemplate> <ItemTemplate> <tr> <td><%# Eval("ID") %></td> <td><%# Eval("Make") %></td> <td><%# Eval("Model") %></td> </tr> </ItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater> |
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:
1 |
Imports DBTutorials.Car |
Next, we bind the Repeater on page load with the Collection we created:
1 2 3 4 5 6 7 8 9 10 |
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load If (Not IsPostBack) Then BindRepeater() End If End Sub Protected Sub BindRepeater() repeater_Cars.DataSource = Car.GetAllCars() repeater_Cars.DataBind() End Sub |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Protected Sub btn_Add_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_Add.Click If (Not String.IsNullOrEmpty(fld_Make.Text)) AndAlso (Not String.IsNullOrEmpty(fld_Model.Text)) Then Dim newCar As New Car() newCar.Make = fld_Make.Text newCar.Model = fld_Model.Text If Car.InsertCar(newCar) > 0 Then fld_Make.Text = "" fld_Model.Text = "" fld_Make.Focus() BindRepeater() End If End If End Sub |