LINQ to SQL

DataContext Class

• It establishes a connection to a database
• It contains a Table(Of T) public property for each table you have mapped in the database
• You should use a class derived from the DataContext class, this class should be named as [DatabaseName]DataContext

Insert Data

• Create a DataContext object
• Create an entity class object
• Call InsertOnSubmit of the DataContext object to insert the entity object into System.Data.Linq.Table(Of T)
• Call SubmitChanges of the DataContext object

Demo:

1. Create a VS2008 project, add a LINQ to SQL Classes (DataClasses1.dbml) to the project.

2. Drag and drop the database table from the Server Explorer to the dbml design surface. In this case, I will drag a Course Table which contains a CourseID and a CourseName column from a Database named Test. VS2008 automatically generates the DataClasses1DataContext class which is derived from the DataContext class. VS2008 also generates the Course entity class. Here is DataClasses1.designer.vb

'------------------------------------------------------------------------------

' <auto-generated>

' This code was generated by a tool.

' Runtime Version:2.0.50727.1433

'

' Changes to this file may cause incorrect behavior and will be lost if

' the code is regenerated.

' </auto-generated>

'------------------------------------------------------------------------------

Option Strict On

Option Explicit On

Imports System

Imports System.Collections.Generic

Imports System.ComponentModel

Imports System.Data

Imports System.Data.Linq

Imports System.Data.Linq.Mapping

Imports System.Linq

Imports System.Linq.Expressions

Imports System.Reflection

<System.Data.Linq.Mapping.DatabaseAttribute(Name:="Test")> _

Partial Public Class DataClasses1DataContext

Inherits System.Data.Linq.DataContext

Private Shared mappingSource As System.Data.Linq.Mapping.MappingSource = New AttributeMappingSource

#Region "Extensibility Method Definitions"

Partial Private Sub OnCreated()

End Sub

Partial Private Sub InsertCourse(ByVal instance As Course)

End Sub

Partial Private Sub UpdateCourse(ByVal instance As Course)

End Sub

Partial Private Sub DeleteCourse(ByVal instance As Course)

End Sub

#End Region

Public Sub New()

MyBase.New(Global.System.Configuration.ConfigurationManager.ConnectionStrings("TestConnectionString").ConnectionString, mappingSource)

OnCreated()

End Sub

Public Sub New(ByVal connection As String)

MyBase.New(connection, mappingSource)

OnCreated()

End Sub

Public Sub New(ByVal connection As System.Data.IDbConnection)

MyBase.New(connection, mappingSource)

OnCreated()

End Sub

Public Sub New(ByVal connection As String, ByVal mappingSource As System.Data.Linq.Mapping.MappingSource)

MyBase.New(connection, mappingSource)

OnCreated()

End Sub

Public Sub New(ByVal connection As System.Data.IDbConnection, ByVal mappingSource As System.Data.Linq.Mapping.MappingSource)

MyBase.New(connection, mappingSource)

OnCreated()

End Sub

Public ReadOnly Property Courses() As System.Data.Linq.Table(Of Course)

Get

Return Me.GetTable(Of Course)()

End Get

End Property

End Class

<Table(Name:="dbo.Course")> _

Partial Public Class Course

Implements System.ComponentModel.INotifyPropertyChanging, System.ComponentModel.INotifyPropertyChanged

Private Shared emptyChangingEventArgs As PropertyChangingEventArgs = New PropertyChangingEventArgs(String.Empty)

Private _CourseID As Integer

Private _CourseName As String

#Region "Extensibility Method Definitions"

Partial Private Sub OnLoaded()

End Sub

Partial Private Sub OnValidate(ByVal action As System.Data.Linq.ChangeAction)

End Sub

Partial Private Sub OnCreated()

End Sub

Partial Private Sub OnCourseIDChanging(ByVal value As Integer)

End Sub

Partial Private Sub OnCourseIDChanged()

End Sub

Partial Private Sub OnCourseNameChanging(ByVal value As String)

End Sub

Partial Private Sub OnCourseNameChanged()

End Sub

#End Region

Public Sub New()

MyBase.New()

OnCreated()

End Sub

<Column(Storage:="_CourseID", AutoSync:=AutoSync.OnInsert, DbType:="Int NOT NULL IDENTITY", IsPrimaryKey:=True, IsDbGenerated:=True)> _

Public Property CourseID() As Integer

Get

Return Me._CourseID

End Get

Set(ByVal value As Integer)

If ((Me._CourseID = value) _

= False) Then

Me.OnCourseIDChanging(value)

Me.SendPropertyChanging()

Me._CourseID = value

Me.SendPropertyChanged("CourseID")

Me.OnCourseIDChanged()

End If

End Set

End Property

<Column(Storage:="_CourseName", DbType:="VarChar(50)")> _

Public Property CourseName() As String

Get

Return Me._CourseName

End Get

Set(ByVal value As String)

If (String.Equals(Me._CourseName, value) = False) Then

Me.OnCourseNameChanging(value)

Me.SendPropertyChanging()

Me._CourseName = value

Me.SendPropertyChanged("CourseName")

Me.OnCourseNameChanged()

End If

End Set

End Property

Public Event PropertyChanging As PropertyChangingEventHandler Implements System.ComponentModel.INotifyPropertyChanging.PropertyChanging

Public Event PropertyChanged As PropertyChangedEventHandler Implements System.ComponentModel.INotifyPropertyChanged.PropertyChanged

Protected Overridable Sub SendPropertyChanging()

If ((Me.PropertyChangingEvent Is Nothing) _

= False) Then

RaiseEvent PropertyChanging(Me, emptyChangingEventArgs)

End If

End Sub

Protected Overridable Sub SendPropertyChanged(ByVal propertyName As [String])

If ((Me.PropertyChangedEvent Is Nothing) _

= False) Then

RaiseEvent PropertyChanged(Me, New PropertyChangedEventArgs(propertyName))

End If

End Sub

End Class

3. Here is a demo page to show how to add/delete courses:

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="TestLinq._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

<title>Untitled Page</title>

</head>

<body>

<form id="form1" runat="server">

<div>

<asp:dropdownlist ID="ddlCourse" runat="server"></asp:dropdownlist>

<asp:Button ID="btnAdd" Text="Add Course" runat="server"/>

<asp:Button ID="btnDelete" Text="Delete Course" runat="server"/>

</div>

</form>

</body>

</html>

Partial Public Class _Default

Inherits System.Web.UI.Page

Dim dc As New DataClasses1DataContext()

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If Not IsPostBack Then

ddlCourse_Bind()

End If

End Sub

Private Sub ddlCourse_Bind()

Dim courses = From c In dc.Courses _

Select c

ddlCourse.DataTextField = "CourseName"

ddlCourse.DataValueField = "CourseID"

ddlCourse.DataSource = courses

ddlCourse.DataBind()

End Sub

Private Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdd.Click

Dim c As New Course()

c.CourseName = DateTime.Now.ToString()

dc.Courses.InsertOnSubmit(c)

dc.SubmitChanges()

ddlCourse_Bind()

End Sub

Private Sub btnDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDelete.Click

'This will throw the following exception

'Unable to cast object of type 'System.Data.Linq.DataQuery`1[TestLinq.Course]' to type 'TestLinq.Course'.

'Dim course = From c In dc.Courses _

' Where c.CourseID = ddlCourse.SelectedValue _

' Select c

'dc.Courses.DeleteOnSubmit(course)

'dc.SubmitChanges()

'ddlCourse_Bind()

Dim course = dc.Courses.Single(Function(c) c.CourseID = ddlCourse.SelectedValue)

dc.Courses.DeleteOnSubmit(course)

dc.SubmitChanges()

ddlCourse_Bind()

End Sub

End Class