Binding GridView with Linq SQL Data without using Linq to SQL Data Source

Binding GridView with Linq SQL Data without using Linq to SQL Data Source
Add new item
Linq to SQL classes
will get DataClasses.dbml in App_code

Double Click on Dataclass.dbml
A blank Page will open
Drag and Drop your SQL Express Tables eg. temp to here from solution explorer.
This willl automatically create code in datacontext vb file and connectionstring in web.config file.
(Click to enlarge)

Save ALL.

Have a Button1, GridView1 in your aspx page.

for connecting with DBML Imports System.Linq in ur vb code

write ur button code

Imports System.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

        'connecting with DBML

        Dim d As DataClassesDataContext = New DataClassesDataContext

        Dim d1 = From d2 In d.temps Where d2.eid > 100 Select d2

        GridView1.DataSource = d1

        GridView1.DataBind()

    End Sub

End Class

2 ####################### Joining in Linq #################

Again Drag another Table named Vin to Dataclass.dbml page
Both table should have a primary key secondary key association (or similar datatype column for joining)

Imports System.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

        'connecting with DBML

        Dim d As DataClassesDataContext = New DataClassesDataContext

        ' Dim d1 = From d2 In d.vins Where d2.eid > 100 Select d2

       

        '' joining

        Dim c2 = From d1 In d.temps Join d2 In d.vins On d1.eid Equals d2.eid _

                 Order By d1.name Select tname = d1.name, vname = d2.name

        ' For Each cc In c2

        'Response.Write(cc.vname & "
")

        'Next

        GridView1.DataSource = c2

        GridView1.DataBind()

    End Sub

End Class

3 ####################### Updating the Database ################

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click

        'connecting with DBML

        Dim d As DataClassesDataContext = New DataClassesDataContext

        Dim d1 = (From p In d.temps Where p.eid = "009383" Select p).Single

        d1.name = "changed name"

        d.SubmitChanges()

        

Dim g1 = From g2 In d.vins Select g2

        GridView1.DataSource = g1

        GridView1.DataBind()

    End Sub




4 #################### Inserting a new Row in table #####################

Protected Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click

        'connecting with DBML

        Dim d As DataClassesDataContext = New DataClassesDataContext

        ''accessing table class

        Dim v As vin = New vin  'v represent a row of table vin

        v.name = "xxx"

        v.eid = "111"

        d.vins.InsertOnSubmit(v)

        'final update

        d.SubmitChanges()

         Dim g1 = From g2 In d.vins Select g2

        GridView1.DataSource = g1

        GridView1.DataBind()

    End Sub

5 ######################### Deleting A Row after adding it ###############

Protected Sub Button4_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button4.Click

        'connecting with DBML

        Dim d As DataClassesDataContext = New DataClassesDataContext

        ''accessing table class

        Dim v As vin = New vin  'v represent a row

        v.name = "ddd"

        v.eid = "114"

        ''add record

        d.vins.Attach(v, False)

        'delete record

        d.vins.DeleteOnSubmit(v)

        'final update

        d.SubmitChanges(Data.Linq.ConflictMode.ContinueOnConflict)

        'exception handling

        ' if Not e.Exception = null AND e.Exception.GetType() == typeof(ChangeConflictException)

        '      lblMessage.Text = "Someone changed this record while you were working on it."

        '       e.ExceptionHandled = true

        '       e.KeepInEditMode = true

        'END IF

        Dim p1 = (From p In d.vins Where p.eid > 100 Select p).Skip(1).Take(1)

        GridView1.DataSource = p1

        GridView1.DataBind()

    End Sub

6 ##################### Joining more than 2 table using Group Join ##########



joining 3 tables vin, temp, pinx


view2 contain join of vin and temp
view 3 contain join of view2 and pinx




dim ds = From a In d.vins Group Join b In d.temps on a.eid Equals b.eid Into view2 = Group Group Join l1 In d.pinxs on l1.eid Equals a.eid Into view3 = Group Select a.eid

- Vinod Kotiya