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