Create a Data Access Component to Update and Delete in C#
In this tutorial, 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.
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.
We used over 10 web hosting companies before we found Server Intellect. They offer dedicated servers, and they now offer cloud hosting!
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.
using System;
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
{
public Names()
{
//
// TODO: Add constructor logic here
//
}
}
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.
We need a connection string to work within this class, so we will add just before the system.web in our Web.config:
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
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;
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;
using System.Data.SqlClient;
using System.Collections.Generic;
/// <summary>
/// Summary description for Names
/// </summary>
public class Names
{
private static readonly string _connectionString;
private int _id;
private string _name;
public int ID
{
get
{
return _id;
}
set
{
_id = value;
}
}
public string Name
{
get
{
return _name;
}
set
{
_name = value;
}
}
public List<Names> GetData()
{
List<Names> results = new List<Names>();
SqlConnection con = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("SELECT ID,Name FROM tblNames", con);
using (con)
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Names theName = new Names();
theName.ID = System.Convert.ToInt32((int)(dr["ID"]));
theName.Name = System.Convert.ToString(dr["Name"]);
results.Add(theName);
}
}
return results;
}
public void Delete(int id)
{
SqlConnection con = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("DELETE tblNames WHERE ID=@Id", con);
cmd.Parameters.AddWithValue("@Id", id);
using (con)
{
con.Open();
cmd.ExecuteNonQuery();
}
}
public void Update(int id, string name)
{
SqlConnection con = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("UPDATE tblNames SET Name=@name WHERE ID=@id", con);
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@name", name);
using (con)
{
con.Open();
cmd.ExecuteNonQuery();
}
}
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" />
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:GridView ID="GridView1" runat="server" DataSourceID= "ObjectDataSource1" DataKeyNames="ID" AutoGenerateDeleteBut ton="true" Width="350px" AutoGenerateEditButton="true" />
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="Names" SelectMethod="GetData" DeleteMethod=" Delete" UpdateMethod="Update" />
</ContentTemplate>
</asp:UpdatePanel>
</form>
We used over 10 web hosting companies before we found Server Intellect. Our new server with cloud hosting,was set up in less than 24 hours. We were able to confirm our order over the phone. They responded to our inquiries within an hour. Server Intellect’s customer support and assistance are the best we’ve ever experienced.
Success!
Congratulations! We have learned how to create our own Data Access Component to connect, retrieve and delete data from a SQL Server Database. Download the source file below for a more in depth look at the Data Access Component, have a good one!
DataAccessComponent
