Reporting with LINQ Using Database Views

Pre-requisite Video

Using Session Variable in DOTNET to Identify User 

Getting Started with LINQ   

Source Code

View Query

USE [ClassRegistration]
GO
/****** Object:  View [dbo].[StudentView]    Script Date: 04/04/2008 10:50:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[StudentView] AS
SELECT s.StudentID as 'Student ID',
      dbo.PersonName.PersonSurNameText as 'First Name', 
      dbo.PersonName.PersonGivenNameText as 'Last Name',
      dbo.ContactInformation.EmailIDText as 'E-Mail', 
      dbo.ContactInformation.MobileTelephoneNumberText as 'Cell Phone', 
      dbo.ContactInformation.TelephoneNumberText as 'Home Phone',   
       
      (SELECT  dbo.Address.AddressText 
           FROM dbo.Address, dbo.ContactInformation, dbo.Student
           WHERE dbo.Student.StudentID = s.StudentID
             AND dbo.ContactInformation.PersonContactInformationID = dbo.Student.StudentContactInformationID
             AND dbo.ContactInformation.PersonHomeAddressID = dbo.Address.AddressID) as 'Home Address',
     
      (SELECT  dbo.Address.AddressText 
           FROM dbo.Address, dbo.ContactInformation, dbo.Student
           WHERE dbo.Student.StudentID = s.StudentID
             AND dbo.ContactInformation.PersonContactInformationID = dbo.Student.StudentContactInformationID
             AND dbo.ContactInformation.PersonCurrentAddressID = dbo.Address.AddressID) as 'Current Address'


 FROM dbo.Student s
      
      INNER JOIN dbo.PersonName
      ON s.StudentNameID = dbo.PersonName.NameID

      INNER JOIN dbo.ContactInformation
      ON s.StudentContactInformationID = dbo.ContactInformation.PersonContactInformationID

USE [ClassRegistration]
GO
/****** Object:  View [dbo].[StudentView2]    Script Date: 04/04/2008 10:51:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[StudentView2]
AS
SELECT     dbo.PersonName.*, dbo.Student.*
FROM         dbo.PersonName INNER JOIN
                      dbo.Student ON dbo.PersonName.NameID = dbo.Student.StudentNameID

GO

Visual Basic Code

Partial Class StudentView
    Inherits System.Web.UI.Page

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

        If Not IsPostBack Then

            ' Show all students in gridview

            Dim dc As New DataClassesStudentsDataContext

            Dim dv = From svRow In dc.StudentView2s

            Me.GridViewStudents.DataSource = dv
            Me.GridViewStudents.DataBind()

            Dim dv2 = From svRow In dc.StudentViews

            Me.GridView2.DataSource = dv2
            Me.GridView2.DataBind()


            ' Populate Drop Down List

            Me.ddlSelectStudent.DataSource = dc.UniqueStudents

            Me.ddlSelectStudent.DataTextField = "Name"
            Me.ddlSelectStudent.DataValueField = "ID"

            Me.ddlSelectStudent.DataBind()

        End If
 
    End Sub

    Protected Sub ddlSelectStudent_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlSelectStudent.SelectedIndexChanged
        Dim dc As New DataClassesStudentsDataContext

        Dim dv = From svRow In dc.StudentView2s Where svRow.StudentID = CInt(Me.ddlSelectStudent.SelectedValue)

        Me.GridViewStudents.DataSource = dv
        Me.GridViewStudents.DataBind()

        Dim dv2 = From svRow In dc.StudentViews Where svRow.Student_ID = CInt(Me.ddlSelectStudent.SelectedValue)

        Me.GridView2.DataSource = dv2
        Me.GridView2.DataBind()
    End Sub
End Class