Navigator: Home - Advanced - LINQ Deleting from Database using ASP.NET 3.5 and VB

LINQ Deleting from Database using ASP.NET 3.5 and VB

This tutorial shows how we can use LINQ to bypass SQL statements to delete data from our SQL Database. VB version.

Download the Full Working Version of this Project written with Visual Studio.NET VB Here!

Looking for the C# 2005 Version? Click Here!

Looking for more .NET Database Tutorials? Click Here!

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>
</form>


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:

Dim db As New DataClassesDataContext()
Dim selectedID As Int16 = System.Convert.ToInt16(DropDownList1.SelectedValue)
Dim toDelete As tblPeople = db.tblPeoples.Single(Function(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:
Imports System
Imports System.Configuration
Imports System.Data
Imports System.Linq
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.HtmlControls
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Xml.Linq

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim db As New DataClassesDataContext()
Dim selectedID As Int16 = System.Convert.ToInt16(DropDownList1.SelectedValue)
Dim toDelete As tblPeople = db.tblPeoples.Single(Function(p) p.id = selectedID)
db.tblPeoples.DeleteOnSubmit(toDelete)
db.SubmitChanges()

DropDownList1.DataBind()
End Sub
End Class


Download the Full Working Version of this Project written with Visual Studio.NET VB Here!

Looking for the C# 2005 Version? Click Here!

Looking for more .NET Database Tutorials? Click Here!
411asp.net123aspxDotNetFreaksServer Intellect