This tutorial shows how we can use LINQ to bypass SQL statements to delete data from our SQL Database. C# version.
Looking for the VB.NET 2005 Version? Click Here!
Looking for more .NET Database Tutorials? Click Here!
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!
Visual Studio .NET 2008 using LINQ to SQL will make it very easy for us to create classes to represent our database structure using the Object Relational Designer. Using this, we can skip dealing with SQL and use LINQ to interact with our database.
This tutorial will show how we can simply use the LINQ to SQL Classes to delete items from our database. We will be using a DropDownList to display items from our database, and then use a button to delete the selected item.
We will start by creating a simple SQL database with one table and two columns - id and name. We will create sample data, so that we will have something to delete. If you already have a database you want to use, you can use that instead. Once we have our database though, we can go ahead and add our LINQ to SQL Classes. To do this, right-click our project in Solution Explorer and choose Add New Item.. LINQ to SQL Classes. If you are asked to add to the App_Code folder, choose yes. Doing this will bring up the designer for our classes. All we need to do is drag the tables we want to work with from the Server Explorer to the design view, and then save. Simple.
Once we have done that, VS will create classes to represent our database. We will now add a drop down list, a button and a LinqDataSource to our ASPX page. It will look something like this:
<form id="form1" runat="server">
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True"
DataSourceID="LinqDataSource1" DataTextField="name" DataValueField="id"> </asp:DropDownList> <asp:Button ID="Button1" runat="server" Text="Delete" onclick="Button1_Click" /> <asp:LinqDataSource ID="LinqDataSource1" runat="server" ContextTypeName="DataClassesDataContext" EnableDelete="True" Select="new (id, name)" TableName="tblPeoples"> </asp:LinqDataSource> |
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.
Notice that our LinqDataSource has the EnableDelete attribute set to true. We also configured our LinqDataSource to use our data classes we just added before. We did this by clicking the smart tag in design view and choosing Configure Data Source.
The DropDownList is also set to use the LinqDataSource. If we run this now, the DropDownList should correctly display the data from the database, but the button will not do anything - let alone delete our selection from the database.
So let's add some functionality. We will put the following code under the click event of the button:
DataClassesDataContext db = new DataClassesDataContext();
var selectedID = Convert.ToInt16(DropDownList1.SelectedValue);
tblPeople toDelete = db.tblPeoples.Single(p => p.id == selectedID);
db.tblPeoples.DeleteOnSubmit( toDelete );
db.SubmitChanges();
DropDownList1.DataBind(); |
This block of code defines an instance of our DataContext Class we created, and then we create a variable to store the selected index of the drop down list. We then go on to build our query by instantiating the class that was created for our table and then assigning the value of the currently selected item in the drop down, so that the matching record is selected. Then we proceed to delete it from the database, and SubmitChanges commits the changes back to the database. Finally, we bind the data to the dropdownlist to get the updated list.
The entire code-behind looks like this:
using System;
using System.Configuration;
using System.Data;
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;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e) { } protected void Button1_Click(object sender, EventArgs e) {
DataClassesDataContext db = new DataClassesDataContext();
var selectedID = Convert.ToInt16(DropDownList1.SelectedValue);
tblPeople toDelete = db.tblPeoples.Single(p => p.id == selectedID);
db.tblPeoples.DeleteOnSubmit( toDelete );
db.SubmitChanges();
DropDownList1.DataBind(); } |
Looking for the VB.NET 2005 Version? Click Here!
Looking for more .NET Database Tutorials? Click 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.
|