C# - Call SQL Server's Stored Procedure with Data Reader

Hey.. In this post, i will share about How to Call SQL Server's Stored Procedure from C# Console Application. You can do this in Windows Form, Web Form, ASP.NET MVC, etc. The concept is same but for simplicity, i will use Console Application for this example.

First, you must create the Stored Procedure inside your database. In this example, i will create a stored procedure that accepts numeric score for its input parameter, and return letter score for its output. Here is the syntax :

Second, you must create a client application that will create a connection with the database. It will also send an input as a parameter for the Stored Procedure and return the letter value. I encapsulate it as a method in this example. Here is the code :

Here is the explanation. It creates a connection with SqlConnection object. It gives a connection string as a parameter for its constructor. It also defines SqlCommand object for its command container. SqlDataReader object is a reader for the database that will read a result for the Stored Procedure. It's often called Connected Mechanism. After defining some important variable,  it executes the Stored Procedure, gets the result and returns the result as a return data for this method.

After define the method to communicate with the database, we can give a simple program for user to input their score and get his/her letter score. Here is the code..

Execute the program, and you will get the result like below : 

Insert your numeric score, and you will get your letter score.

You can download the full source for this example here. Hopefully this post will help you and don't forget to visit my blog again.. Regards.... ^_^