This tutorial will show you how to use Custom Objects to Insert to a SQL Server Database using C#
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.cs
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:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Collections;
/// <summary>
/// Summary description for Car
/// </summary>
namespace DBTutorials.Car
{
/// <summary>
/// Summary description for Car
/// </summary>
public class Car
{
public Car()
{
}
} |
We used over 10 web hosting companies before we found Server Intellect. Their dedicated servers and add-ons were setup swiftly, in less than 24 hours. We were able to confirm our order over the phone. They respond to our inquiries within an hour. Server Intellect's customer support and assistance are the best we've ever experienced.
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 Int32 ID
{
get
{
return _ID;
}
set
{
_ID = value;
}
private Int32 _ID = 0;
/// <summary>
/// Gets or sets the Make of the Car. [Default value is ''].
/// </summary>
public String Make
{
get
{
return _Make;
}
set
{
_Make = value;
}
private String _Make = "";
/// <summary>
/// Gets or sets the Model of the Car. [Default value is ''].
/// </summary>
public String Model
{
get
{
return _Model;
}
set
{
_Model = value;
}
private String _Model = "";
#endregion
|
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 void SetObjectData(SqlDataReader theObjReader)
{
try
{
this._ID = Convert.ToInt32(theObjReader["ID"]);
this._Make = theObjReader["Make"].ToString();
this._Model = theObjReader["Model"].ToString();
}
catch
{ }
#endregion
|
We are using Server Intellect and have found that by far, they are the most friendly, responsive, and knowledgeable support team we've ever dealt with!
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 Car(SqlDataReader theObjReader)
{
SetObjectData(theObjReader);
}
|
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 static Int32 InsertCar(Car theCar)
{
Int32 newCarID = 0;
SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
try
{
SqlCommand cmd = 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();
}
return newCarID; |
Yes, it is possible to find a good web host. Sometimes it takes a while. After trying several, we went with Server Intellect and have been very happy. They are the most professional, customer service friendly and technically knowledgeable host we've found so far.
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 : CollectionBase
{
public int TotalRecords
{
get
{
return _TotalRecords;
}
set
{
_TotalRecords = value;
}
}
protected int _TotalRecords = 0;
/// <summary>
/// Adds a new Car to the collection
/// </summary>
/// <param name="theCar">The Car to add to the collection</param>
public int Add(Car theCar)
{
this.TotalRecords++;
return List.Add(theCar);
}
/// <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 void Insert(Int32 index, Car theCar)
{
List.Insert(index, theCar);
}
/// <summary>
/// Removes a Car from the collection
/// </summary>
/// <param name="theCar">The Car to remove from the collection</param>
public void Remove(Car theCar)
{
List.Remove(theCar);
}
/// <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 bool Contains(Car theCar)
{
return List.Contains(theCar);
}
/// <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 int IndexOf(Car theCar)
{
return List.IndexOf(theCar);
}
public void CopyTo(Car[] array, int index)
{
List.CopyTo(array, index);
}
public Car this[int index]
{
get
{
return (Car)List[index];
}
set
{
List[index] = value;
}
}
#endregion
|
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 static Cars GetAllCars()
{
Cars CarsCollection = new Cars();
SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
try
{
SqlCommand cmd = new SqlCommand("sp_GetAllCars", connection);
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlDataReader objReader = cmd.ExecuteReader();
while (objReader.Read())
{
Car newCar = new Car(objReader);
CarsCollection.Add(newCar);
}
objReader.Close();
connection.Close();
}
catch
{
connection.Close();
}
return CarsCollection; |
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> |
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:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindRepeater();
}
protected void BindRepeater()
{
repeater_Cars.DataSource = Car.GetAllCars();
repeater_Cars.DataBind();
}
|
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 void btn_Add_OnClick(object sender, EventArgs e)
{
if (!String.IsNullOrEmpty(fld_Make.Text) && !String.IsNullOrEmpty(fld_Model.Text))
{
Car newCar = new Car();
newCar.Make = fld_Make.Text;
newCar.Model = fld_Model.Text;
if (Car.InsertCar(newCar) > 0)
{
fld_Make.Text = "";
fld_Model.Text = "";
fld_Make.Focus();
BindRepeater();
}
} |
|