Data Access Component to Update & Delete from SQL Database in ASP.NET 3.5
Introduction
In this article, we will be looking at the Command Object and creating our own Data Access Component using the Object Data Source control. We will be using a SQL database, and then creating a class to represent our database. We will use this class (DAC) to interact with an Object Data Source.
Try Server Intellect for Windows Server Hosting. Quality and Quantity!
What we will learn in this article:
- How to use the SqlCommand object to delete records from a SQL Database;
- How to use the SqlCommand object to update records in a SQL Database;
- How to build a Data Access Component to interact with a SQL Database.
Getting Started
The very first thing we need to do is to create our database. We will be using a SQL database, so open up Visual Studio and create a new web project in C#. Right-click the project in Solution Explorer and choose Add New Item.. SQL Server Database. If you are asked to place in the App_Data folder, choose Yes.
[Click to enlarge]
Once the database has been added to the project, we can then goto Server Explorer and right-click on the Tables folder for the new database and choose to Add New Table. We should then be presented with the table designer. For this example, we will have two columns: ID (int) and Name(varchar(50)). We also want to make sure that ID is the Primary Key and also set Identity Specification to Yes. Then we can save the table, name it tblNames.
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.
Now we will add some data to the database by right-clicking the table in Server Explorer and choosing Show Table Data. Notice we are required to only enter a name as the ID will be auto-generated for each record.
Next, we want to add a class to our project, where we will build the methods used for manipulating our data. Right-click the project in Solution Explorer, and then choose Add ASP.NET Folder > App_Code. An App_Code folder should be added to the project, which we will now right-click and choose Add New Item.. Class, and name it Names. We should be presented with the default code for the new class:
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
///
/// Summary description for Names
///
public class Names
{
{
// TODO: Add constructor logic here
//
If you're looking for a really good web host, try Server Intellect - we found the setup procedure and control panel, very easy to adapt to and their IT team is awesome!
We need a connection string to work within this class, so we will add just before the system.web in our Web.config:
Now we can save Web.config and go back to the class.
The first thing we need to do is define the variable we will be using - one for the connection string, and one for each column we will be working with in the database. Then we create a get and set for each column, also, and finally assign our connection string. In order to use the WebConfigurationManager to retrieve our Connection String from the Web.config, we need to add the System.Web.Configuration assembly reference, which is not usually added by default.
Now this is the base upon which we can build our class. From here we will add methods to both retrieve data and then also delete data from the database. Our basis looks like this:
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.Configuration;
///
/// Summary description for Names
///
public class Names
{
private int _id;
private string _name;
public int ID
{
{
set
{
public string Name
{
{
set
{
static Names()
{
If you're ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.
For the retrieval method, we will be using a SqlCommand and a List collection, for which we will need to add the System.Data.SqlClient and System.Collections.Generic assembly references. We will use the List collection with the class we created as our object, then we use SQL to loop through the data and add each record to the collection.
{
SqlConnection con = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("SELECT ID,Name FROM tblNames", con);
using (con)
{
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
theName.ID = System.Convert.ToInt32((int)(dr["ID"]));
theName.Name = System.Convert.ToString(dr["Name"]);
results.Add(theName);
return results;
Notice that when we reference the Names variable (theName), we can access the properties of the class (ID, Name), which are even shown in the IntelliSense. This makes it very easy for us to work with custom classes:
The method we are going to create to enable us to delete records from the database will be significantly easier to code than the one we just created. We are going to simply use a SQL statement to delete the record that we are given the ID to, and then apply that query to the database to save changes.
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!
{
SqlCommand cmd = new SqlCommand("DELETE tblNames WHERE [email protected]", con);
cmd.Parameters.AddWithValue("@Id", id);
using (con)
{
cmd.ExecuteNonQuery();
Again we are using SqlCommand to apply a regular SQL statement against the database to delete a record matching the ID that has been passed to the method.
Similar to this, we can also add a third method to update the database data.
{
SqlCommand cmd = new SqlCommand("UPDATE tblNames SET [email protected] WHERE [email protected]", con);
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@name", name);
using (con)
{
cmd.ExecuteNonQuery();
The last thing we need to do is to incorporate the class into our ASPX page, and make use of it. To do this, we will be using the GridView control and the ObjectDataSource, which will use the class we just created. We use the Object Data Source attributes to specify the methods to use for Selecting, Deleting, and Updating of database data, and TypeName refers to the class we created.
Because we are using in Visual Studio.NET 2008, also, we can easily add AJAX functionality by including a ScriptManager and an UpdatePanel.