23 April, 2012

Retrieve Store Procedure's Output parameter by C# (ASP.net)

How to retrieve store procedure's output parameter by C#? Here I have explained with a simple example. I created a simple store procedure with output parameter. Then I catch it from code behind page of asp.net and displayed it to a label. Here I used a class DBConnector to connect with database. Lets look on the demo.

Step 1: Set connection string in web.config.
       
            
       
     
Step 2: Create a store procedure with output parameter.
CREATE PROCEDURE [dbo].[sp_output_param]
(
     @input_param VARCHAR(200)
    , @Response VARCHAR(250) OUTPUT
)
AS
    --DECLARE @myName


BEGIN
    
    SET @Response = 'Welcome ' + @input_param
    
END

Step 3: Create a DBConnector Class to retrieve data from database.
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public class DBConnector
{
    private string connectionString = null;
    private SqlConnection sqlConn = null;
    private SqlCommand cmd = null;

    public DBConnector()
    {
        connectionString = ConfigurationManager.ConnectionStrings["SQLServerConnectionString"].ToString();
    }


    public SqlCommand GetCommand()
    {
        cmd = new SqlCommand();
        cmd.Connection = sqlConn;
        return cmd;
    }

    public SqlConnection GetConn()
    {
        sqlConn = new SqlConnection(connectionString);
        return sqlConn;
    }

}

Step 4 : Create a label name lblMsg in asp page. Then in a button event, write the following code. You will see your desired output in the lebel which is actually the value of output parameter. Here btnGetOutputParam_Click is the click event of Button btnGetOutputParam.
protected void btnGetOutputParam_Click(object sender, EventArgs e)
{
        SqlConnection sqlConn;
        SqlCommand cmd;

        DBConnector objDBConnector = new DBConnector();
        sqlConn = objDBConnector.GetConn();

        cmd = objDBConnector.GetCommand();

        SqlDataReader rdr = null;

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_output_param";
        cmd.Parameters.AddWithValue("@input_param", "Mahedee");
        cmd.Parameters.Add("@Response", SqlDbType.VarChar, 250);
        cmd.Parameters["@Response"].Direction = ParameterDirection.Output;

        try
        {
            if (sqlConn.State == ConnectionState.Closed)
                sqlConn.Open();

            rdr = cmd.ExecuteReader();

            string outputValue = cmd.Parameters["@Response"].Value.ToString();
            this.lblMsg.Text = outputValue;
        }
        catch (Exception exp)
        {
            throw (exp);
        }
        finally
        {
            if (sqlConn.State == ConnectionState.Open)
                sqlConn.Close();
        }

    }

You will see in label - Welcome Mahedee which is provided by output parameter of store procedure.

29 comments:

  1. It is a very useful code that you describe very
    simply. Thanks.............

    ReplyDelete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
  3. I go to see day-to-day some websites and sites to read articles,
    however this website presents feature based content.

    Review my website: understanding quotes

    ReplyDelete
  4. If some one wishes to be updated with most up-to-date technologies afterward he must
    be pay a visit this website and be up to date daily.

    Here is my site kahlil gibran quotes

    ReplyDelete
  5. What i do not realize is in reality how you're no longer really much more neatly-liked than you may be now. You're so intelligent.

    You understand thus considerably relating to this subject, produced me
    personally imagine it from so many varied angles. Its like men and women
    aren't fascinated except it is something to do with Lady gaga! Your own stuffs great. All the time maintain it up!

    Feel free to visit my page: discipline quotes

    ReplyDelete
  6. I just like the valuable information you provide on your
    articles. I will bookmark your blog and check once more here frequently.
    I'm relatively sure I will learn lots of new stuff right right here! Good luck for the following!

    Review my blog: best quotes ever

    ReplyDelete
  7. I really like it when people get together and share views.
    Great site, continue the good work!

    Feel free to surf to my weblog :: friedrich nietzsche quotes

    ReplyDelete
  8. What's up it's me, I am also visiting this web site daily, this web site is actually
    pleasant and the people are really sharing fastidious thoughts.


    My blog; tired quotes

    ReplyDelete
  9. These are truly impressive ideas in about blogging.
    You have touched some pleasant things here.
    Any way keep up wrinting.

    my web page; future quotes

    ReplyDelete
  10. I could not resist commenting. Exceptionally well written!


    Here is my site ... depressing quotes

    ReplyDelete
  11. This article offers clear idea for the new people of blogging,
    that actually how to do running a blog.

    Feel free to visit my site - amazing quotes

    ReplyDelete
  12. What's Taking place i'm new to this, I stumbled upon this I have found
    It positively helpful and it has aided me out loads.
    I hope to give a contribution & assist other customers
    like its helped me. Good job.

    My weblog: brother and sister quotes

    ReplyDelete
  13. Hi there! This is my first visit to your blog! We are a collection of volunteers and starting a new initiative
    in a community in the same niche. Your blog provided us valuable information to work
    on. You have done a marvellous job!

    Also visit my page :: unusual animals

    ReplyDelete
  14. I was recommended this web site by way of my cousin. I am not sure whether or not this submit is written by him as nobody else understand such precise approximately my difficulty.
    You are incredible! Thanks!

    My web-site - madea quotes

    ReplyDelete
  15. It is perfect time to make some plans for the future and it
    is time to be happy. I've read this post and if I could I want to suggest you few interesting things or tips. Perhaps you can write next articles referring to this article. I want to read more things about it!

    Also visit my web site: zayn malik quotes

    ReplyDelete
  16. It's an awesome post for all the internet visitors; they will take advantage from it I am sure.

    my web site - ignorance quotes

    ReplyDelete
  17. Write more, thats all I have to say. Literally, it seems as though
    you relied on the video to make your point. You definitely
    know what youre talking about, why waste your intelligence on just posting videos to your
    blog when you could be giving us something informative to read?


    my web site - zayn malik quotes

    ReplyDelete
  18. Greetings! Very helpful advice within this article!
    It is the little changes that will make the largest changes.
    Many thanks for sharing!

    Take a look at my blog post: self esteem quotes

    ReplyDelete
  19. Howdy very nice web site!! Guy .. Beautiful .. Superb .
    . I will bookmark your web site and take the feeds additionally?

    I am glad to search out a lot of helpful info right here within the publish, we want develop extra strategies in this regard,
    thanks for sharing. . . . . .

    Feel free to visit my web page ... commitment quotes

    ReplyDelete
  20. Hi there i am kavin, its my first occasion to commenting anyplace, when
    i read this paragraph i thought i could also make comment due
    to this good paragraph.

    my webpage - future quotes

    ReplyDelete
  21. Wow, this piece of writing is fastidious, my sister is analyzing these things, thus
    I am going to tell her.

    Look into my web page ... friedrich nietzsche quotes

    ReplyDelete
  22. Hi my family member! I wish to say that this article is amazing, great written
    and come with approximately all significant infos. I'd like to peer more posts like this .

    Feel free to surf to my page kahlil gibran quotes

    ReplyDelete
  23. Definitely believe that which you said. Your favorite justification seemed to be on the net the simplest thing
    to be aware of. I say to you, I definitely get annoyed
    while people consider worries that they plainly do not know about.
    You managed to hit the nail upon the top and defined out the whole thing without having side-effects ,
    people could take a signal. Will likely be back to get more.
    Thanks

    My homepage country flags

    ReplyDelete
  24. If you are going for most excellent contents like myself,
    just pay a quick visit this web site daily since it presents quality contents, thanks

    Feel free to visit my web site ignorance quotes

    ReplyDelete
  25. I like the helpful info you provide to your articles.

    I'll bookmark your weblog and take a look at once more here regularly. I am reasonably sure I will learn a lot of new stuff right right here! Best of luck for the following!

    My weblog :: sian

    ReplyDelete
  26. Thanks for finally writing about > "Retrieve Store Procedure's Output parameter by C# (ASP.net)" < Loved it!

    Also visit my website - Garage Rubber flooring

    ReplyDelete
  27. what will be the code for displaying the output parameter if we have written the insertion code in a function inside a class file??
    please help

    ReplyDelete
    Replies
    1. how to use parameters which are inside a function in class file into code behind file?

      Delete