Beginning LINQ to SQL Using Visual Studio 2008 - CRUD operations using the LINQ to SQL application (without LINQ designer): Source in VB.NET
(Page 5 of 6 )
Once the UI is designed as discussed in the previous section, we need to code for the button clicks (in code-behind) for all add, update, delete, search and refresh buttons.
The following is the source code in VB.NET 2008:
Imports System.Data.Linq
Partial Public Class CRUDSample
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Me.lblMsg.Text = ""
End Sub
Protected Sub btnSelectStar_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSelectStar.Click
Dim db As New DataContext(New System.Data.SqlClient.SqlConnection("data source=.sql2k5;initial catalog=Sample;user id=sa;Password=eXpress2005"))
Dim tblEmps As Table(Of Emp) = db.GetTable(Of Emp)()
Me.GridView1.DataSource = tblEmps
Me.GridView1.DataBind()
End Sub
Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSearch.Click
Try
'getting table
Dim db As New DataContext(New System.Data.SqlClient.SqlConnection("data source=.sql2k5;initial catalog=Sample;user id=sa;Password=eXpress2005"))
Dim tblEmps As Table(Of Emp) = db.GetTable(Of Emp)()
'getting an exiting row
Dim objEmp As Emp = tblEmps.SingleOrDefault(Function(p) p.empno = Me.txtEmpno.Text)
If objEmp IsNot Nothing Then
Me.txtEname.Text = objEmp.ename
Me.txtSal.Text = objEmp.Sal
Me.txtDeptno.Text = objEmp.deptno
Else
Me.lblMsg.Text = "Employee not found"
End If
Catch ex As Exception
Me.lblMsg.Text = ex.Message
End Try
End Sub
Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnInsert.Click
Try
'getting table
Dim db As New DataContext(New System.Data.SqlClient.SqlConnection("data source=.sql2k5;initial catalog=Sample;user id=sa;Password=eXpress2005"))
Dim tblEmps As Table(Of Emp) = db.GetTable(Of Emp)()
'adding a new row
Dim rEmp As Emp = New Emp With {.empno = Me.txtEmpno.Text, .ename = Me.txtEname.Text, .Sal = Me.txtSal.Text, .deptno = Me.txtDeptno.Text}
tblEmps.InsertOnSubmit(rEmp)
'saving rows added
db.SubmitChanges()
Me.lblMsg.Text = "Added Successfully"
Catch ex As Exception
Me.lblMsg.Text = ex.Message
End Try
End Sub
Private Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Try
'getting table
Dim db As New DataContext(New System.Data.SqlClient.SqlConnection("data source=.sql2k5;initial catalog=Sample;user id=sa;Password=eXpress2005"))
Dim tblEmps As Table(Of Emp) = db.GetTable(Of Emp)()
'getting an exiting row
Dim objEmp As Emp = tblEmps.SingleOrDefault(Function(p) p.empno = Me.txtEmpno.Text)
If objEmp IsNot Nothing Then
'modifying the row
objEmp.ename = Me.txtEname.Text
objEmp.Sal = Me.txtSal.Text
objEmp.deptno = Me.txtDeptno.Text
'saving rows added
db.SubmitChanges()
Me.lblMsg.Text = "Updated Successfully"
Else
Me.lblMsg.Text = "Employee not found"
End If
Catch ex As Exception
Me.lblMsg.Text = ex.Message
End Try
End Sub
Private Sub btnDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Try
'getting table
Dim db As New DataContext(New System.Data.SqlClient.SqlConnection("data source=.sql2k5;initial catalog=Sample;user id=sa;Password=eXpress2005"))
Dim tblEmps As Table(Of Emp) = db.GetTable(Of Emp)()
'getting an exiting row
Dim objEmp As Emp = tblEmps.SingleOrDefault(Function(p) p.empno = Me.txtEmpno.Text)
If objEmp IsNot Nothing Then
'delete the row
tblEmps.DeleteOnSubmit(objEmp)
'saving rows deleted
db.SubmitChanges()
Me.lblMsg.Text = "Deleted Successfully"
Else
Me.lblMsg.Text = "Employee not found"
End If
Catch ex As Exception
Me.lblMsg.Text = ex.Message
End Try
End Sub
End Class
Once executed, it should give the output as follows (Fig 06):
Next: CRUD operations using the LINQ to SQL application (without LINQ designer): Source in C# >>
More .NET Articles
More By Jagadish Chaterjee
