Creating & Calling SQL Stored Procedures, SQL Stored Functions through C#

In this Post we shall learn how to create stored procedures, and functions  in MS SQL Server, and also call them from a C# application.

First of all let us create a table

create table scores(Sno int primary key,batsman varchar(50),runs int)   

Next we shall create a stored procedure for inserting data into the table:-

create procedure InsertIntoScores(@batsmanname varchar(50) ,@runsscored int)
as
declare @sno int;
select @sno=max(sno) from scores;
if(@sno is NULL)
set @sno=1;
else
set @sno=@sno+1;
insert into scores values(@sno,@batsmanname,@runsscored);

To execute the procedure we shall write 




exec InsertIntoScores 'Champak',99

Next we create a function




create function GetScores(@batsmanname varchar(50))
returns int
begin
declare @runs int;
declare @total int;
set @total=0;
DECLARE runs_cursor CURSOR FOR SELECT runs FROM scores WHERE batsman=@batsmanname;
OPEN runs_cursor
FETCH NEXT FROM runs_cursor INTO @runs

WHILE @@FETCH_STATUS = 0
BEGIN
SET @total=@runs+ @total;

FETCH NEXT FROM runs_cursor INTO @runs
END

CLOSE runs_cursor
DEALLOCATE runs_cursor
return @total;
end
The function uses a cursor to loop through the results and add up the scores.Using the sum aggregate query would be better, but I wanted to also provide an example of a cursor.

To test the procedure follow this procedure :-

Next give the value for the parameter

Here is the output:-

To access the procedure from a C# application we need to establish a connection, then create a command object to which we pass the required parameters. For the function we also require a return parameter. Here is how you access the stored procedure:-

System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=smartsystem;Integrated Security=True");
        con.Open();
        System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("InsertIntoScores", con);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("batsmanname", "Champak");
        cmd.Parameters.AddWithValue("runsscored", 100);
        cmd.ExecuteNonQuery();


Here is how you access the stored function :-


 System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=smartsystem;Integrated Security=True");
        con.Open();
        System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("GetScores", con);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("batsmanname", "Champak");
        System.Data.SqlClient.SqlParameter retvalue =new System.Data.SqlClient.SqlParameter("MyRuns",System.Data.SqlDbType.Int );
        retvalue.Direction = System.Data.ParameterDirection.ReturnValue;
        cmd.Parameters.Add(retvalue);
        cmd.ExecuteNonQuery();

        TextBox1.Text = "" + retvalue.Value;