| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!

View
 

LINQ - Using Stored Procedures

Page history last edited by PBworks 15 years, 11 months ago

Calling Stored Procedures Using LINQ  

 

Description

 

This lecture uses the database I use for myself to keep track of code and code snippets and also manage database connections to various databases I access. The lecture demonstrates using LINQ to create a simple page that fills a drop down lists from the database allowing the user to see a list of connections for a database (from the database). Users should have a general knowledge of database structure and the concept of connection strings for this lecture.

 

Pre-requisite Lecture - Getting Started with LINQ 

 

 

 

LINQ References on MSDN (reference) Download Link  

 

 

Visual Basic Code

 

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim db As New DataClassesDataContext

        Dim qr = From dbcRow In db.DBConnections Select id = dbcRow.DBConnectionID, name = dbcRow.DBConnectionName

        DropDownList1.DataSource = qr
        DropDownList1.DataTextField = "name"
        DropDownList1.DataValueField = "id"
        DropDownList1.DataBind()

    End Sub

    Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
        Dim db As New DataClassesDataContext

        Dim i As Long = DropDownList1.SelectedValue

        gridview1.DataSource = db.spGetDBConnection(i).ToList
        gridview1.DataBind()

    End Sub
End Class

 

 

Stored Procedures and Tables

 

 

CREATE TABLE [dbo].[CodeSnippet](
	[CodeSnippetID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[CodeSnippetDescriptionText] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CodeSnippetText] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CodeSnippetLanguage] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CodeSnippetVersion] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CodeSnippetSource] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CodeSnippetUser] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CodeSnippetDate] [datetime] NULL,
 CONSTRAINT [PK_CodeSnippet] PRIMARY KEY CLUSTERED 
(
	[CodeSnippetID] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[CodeSnippetComment](
	[CodeSnippetCommentID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[CodeSnippetID] [numeric](18, 0) NULL,
	[CodeSnippetCommentText] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CodeSnippetCommentType] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CodeSnippetCommentUser] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CodeSnippetCommentDate] [datetime] NULL,
 CONSTRAINT [PK_CodeSnippetComment] PRIMARY KEY CLUSTERED 
(
	[CodeSnippetCommentID] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[DBConnection](
	[DBConnectionID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[DBConnectionName] [varchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DBConnectionString] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DBCommentsText] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DBTablePolicyText] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DBStoredProcedurePolicyText] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CreatedDate] [datetime] NULL,
	[CreatedUser] [varchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LastUpdatedDate] [datetime] NULL,
	[LastUpdatedUser] [varchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


CREATE PROCEDURE [dbo].[spGetCodeSnippet]
@CodeSnippetID numeric(18,0) 
AS
BEGIN
SELECT * FROM  CodeSnippet where CodeSnippetID=@CodeSnippetID
END;

CREATE PROCEDURE [dbo].[spGetCodeSnippetIDInformation]
AS
BEGIN
SELECT CodeSnippetID, CodeSnippetDescriptionText, CodeSnippetUser FROM  CodeSnippet 
END;

CREATE PROCEDURE [dbo].[spGetDBConnection]
 @DBConnectionID numeric 
AS
BEGIN
SELECT * FROM DBConnection WHERE DBConnectionID=@DBConnectionID
END;

CREATE PROCEDURE [dbo].[spGetDBDocumentation]
     @DBDocumentationID int
AS
BEGIN
SELECT * FROM DBDocumentation WHERE DBDocumentationID = @DBDocumentationID
END;

Comments (0)

You don't have permission to comment on this page.