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