Using LINQ with Stored Procedures to Create a Web Input Form
Pre-requisites
LINQ - Using Stored Procedures
Assignment 2 Review
This lecture uses the student class registration database outlined in Assignment 2, with the stored procedures from that assignment.
Useful Links for this Lecture
Using Session Variable in DOTNET to Identify User
Source Code - Visual Basic
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
Dim d As New DataClassesStudentsDataContext
Dim i As Integer
i = d.AddNewStudent(Me.txtStudentFirstName.Text, _
Me.txtStudentMiddleName.Text, _
Me.txtStudentLastName.Text, _
Me.txtStudentLocalAddress.Text, _
Me.TtxtStudentHomeAddress.Text, _
Me.txtStudentEmailAddress.Text, _
Me.txtStudentHomePhoneNumber.Text, _
Me.txtStudentMobilePhoneNumber.Text)
lblOutput.Text = i.ToString
End Sub
Source Code - SQL Server Stored Procedure
ALTER PROCEDURE [dbo].[AddNewStudent]
-- Add the parameters for the stored procedure here
@StudentFirstName varchar(100),
@StudentMiddleName varchar(100),
@StudentLastName varchar(100),
@StudentLocalAddress varchar(200) = NULL,
@StudentHomeAddress varchar(200) = NULL,
@StudentEmailAddress varchar(200),
@StudentHomePhoneNumber varchar(50),
@StudentMobilePhoneNumber varchar(50)
AS
DECLARE @ID1 INT
DECLARE @ID2 INT
BEGIN
-- Note we can also use the SCOPE_IDENTITY() function in place of @@Identity
INSERT INTO Address
(AddressText)
VALUES
(@StudentLocalAddress)
SELECT @ID1 = @@IDENTITY
INSERT INTO Address
(AddressText)
VALUES
(@StudentHomeAddress)
SELECT @ID2 = @@IDENTITY
-- With the other two insertions we need the identity field
INSERT INTO ContactInformation
(PersonCurrentAddressID, PersonHomeAddressID, EmailIDText, MobileTelephoneNumberText, TelephoneNumberText)
VALUES
(@ID1, @ID2, @StudentEmailAddress, @StudentMobilePhoneNumber,@StudentHomePhoneNumber)
SELECT @ID1 = @@IDENTITY
INSERT INTO PersonName
(PersonGivenNameText, PersonMiddleNameText, PersonSurnameText)
VALUES
(@StudentLastName, @StudentMiddleName, @StudentFirstName)
SELECT @ID2 = @@IDENTITY
INSERT INTO Student
(StudentNameID, StudentContactInformationID)
VALUES
(@ID2, @ID1 )
END
Comments (0)
You don't have permission to comment on this page.