Stored Procedure in C#

Question

I want to use a stored procedure in c#. I create the stored procedure in sql server and I call it in the program. But when I use the breakpoint feature, I come to know that the data is not retrieved from the db as the breakpoint skips over the loop..

.aspx code:

<asp:Button ID="Button1" runat="server" Text="Button" OnClick="store" />
<asp:Label ID="Label9" runat="server" Text="Label"></asp:Label>

c# code:

public void store(object sender, EventArgs ser)
{
    try
    {
        // c reate and open a connection object
        SqlConnection conn = Class3.GetConnection();

        // 1. create a command object identifying the stored procedure
        SqlCommand cmd = new SqlCommand("storeprocedure3", conn);

        // 2. set the command object so it knows to execute a stored procedure
        cmd.CommandType = CommandType.StoredProcedure;

        // 3. add parameter to command, which will be execute the command
        SqlDataReader rdr = cmd.ExecuteReader();

        // iterate through results, printing each to console
        while (rdr.Read())
        {
            Label9.Text = rdr["menuename"].ToString();
        }
    }
    catch (Exception sa)
    {
        Console.WriteLine(sa);
    }
}

stored procedure:

CREATE PROCEDURE procedure3     
AS
BEGIN
    select menuename from menue;

END
GO

Does your stored procedure return data if you directly run it against the database eg in Sql Management Studio? — Chris Moutray
– Chris Moutray, Commented Jun 26, 2012 at 11:49
I notice that you asign a scalar value to the property: Label9.Text= rdr["menuename"].ToString(); You should consider using cmd.ExecuteScalar() instead of cmd.ExecuteReader() in case you are retrieving single value from the stored procedure — Nas
– Nas, Commented Jun 26, 2012 at 11:50
- is there an exception happening? (hint: Console.WriteLine is not very useful in a web application) - is the connection executing as the right user? (from menue is ambiguous - it depends on the user) - when is this running? also; needs a lot more using... — Marc Gravell
– Marc Gravell, Commented Jun 26, 2012 at 11:50
Have you tried running your procedure directly against the database (using Sql Server Management Studio) to see it it really returns any rows? Also, using Console.WriteLine in an ASP.Net-project is kinda useless, make sure to put a breakpoint in your catch-clause as well to see if any exceptions occur and then find a better way to handle your exceptions. — Karl-Johan Sjögren
– Karl-Johan Sjögren, Commented Jun 26, 2012 at 11:52

Marc Gravell · Accepted Answer · 2012-06-26 11:53:58Z

IMO, here's the biggest and most likely problem:

catch (Exception sa)
{
    Console.WriteLine(sa);
}

I imagine it is trying really hard to tell you what is wrong, but you silenced it. There is no reason whatsoever to try/catch this; if that doesn't work, something is very wrong - let it error. Read the exception details.

If I were being picky (and, frankly, I am) - you need a lot more using here, i.e.

using(SqlConnection conn = Class3.GetConnection())
using(SqlCommand cmd = new SqlCommand("whatever", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    using(SqlDataReader rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
           // do something
        }
    }
}

or, frankly easier, use a tool like dapper:

using(SqlConnection conn = Class3.GetConnection())
{
    foreach(var obj in conn.Query("whatever",
          commandType: CommandType.StoredProcedure))
    {
        string menuename = obj.menuename;
        // do something...
    }
}

Chris Moutray · Accepted Answer · 2012-06-26 11:56:16Z

5

You have a mismatch in your program ( procedure3 vs storeprocedure3)

use this code

 SqlCommand cmd = new SqlCommand("procedure3 ", conn);

and close your connection

 SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

edited Jun 26, 2012 at 11:56

Chris Moutray

18.4k7 gold badges49 silver badges66 bronze badges

answered Jun 26, 2012 at 11:51

Aghilas Yakoub

29k5 gold badges49 silver badges51 bronze badges

1 Comment

BlackTigerX Over a year ago

...if this is a C# stored procedure, the connection would come from the context, and you wouldn't have (want) to close it or dispose it

Darren · Accepted Answer · 2012-06-26 11:55:07Z

2

Does EXEC procedure produce any results?

Also in your code you have referenced the stored procedure as storeprocedure3 when in fact the procedure name appears to be procedure3

Change the line to:

SqlCommand cmd = new SqlCommand("procedure3", conn);

answered Jun 26, 2012 at 11:55

Darren

71.2k24 gold badges141 silver badges146 bronze badges

Collectives™ on Stack Overflow

Stored Procedure in C#

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related