We have a strored procedure like below , It is a very simple SP returning grades of the student. This stored procedure name is GetStudentGrade
USE [School]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetStudentGrades]
@StudentID int
AS
SELECT EnrollmentID, Grade, CourseID, StudentID FROM dbo.StudentGrade
WHERE StudentID = @StudentID
So to call this stored procedure, we need to create instance of DataContext class and call the stored procedure as normal function
In intellisense stored procedure name can be seen.
Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ConsoleApplication4
{
class Program
{
static void Main(string[] args)
{
DataClasses1DataContext context = new DataClasses1DataContext();
var result = context.GetStudentGrades(2);
foreach (var r in result)
{
Console.WriteLine(r.Grade);
}
Console.ReadKey(true);
}
}
}
Output
Stored Procedure with out parameter
Let us say we have one stored procedure which is returning count of students in output parameter.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetStudentCount
@StudentCount int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @StudentCount = COUNT(*) from dbo.Person
END
GO
Now at using this stored procedure, you can see we need to pass ref variable to get the output
And we can call this stored procedure like below
Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ConsoleApplication4
{
class Program
{
static void Main(string[] args)
{
DataClasses1DataContext context = new DataClasses1DataContext();
int? abc = 1;
context.GetStudentCount(ref abc);
Console.WriteLine(abc);
Console.ReadKey(true);
}
}
}
Output
Dhananjay Kumar 
Leave a Reply