Tutorial RSS
 
Navigator: Home - Advanced - Inserting Custom Object to SQL Database in VB.NET

Inserting Custom Object to SQL Database in VB.NET

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.

<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Cars.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
</connectionStrings>


If you need help with the Connection String, see ConnectionStrings.com

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:

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


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.

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:

#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:

#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


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 that this method is private, so it can only be called within the class.
Next, we add the second constructor:

''' <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:

CREATE PROCEDURE dbo.sp_GetAllCars

AS

SELECT * FROM Cars


Next, add InsertCar:

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:

''' <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


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.

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:

#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:

''' <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


Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!

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:

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:

Imports DBTutorials.Car


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

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:

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
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!

411asp.net123aspxDotNetFreaksServer Intellect