CODEWITHSUNDEEP
c#sql.nett-sqlstored-procedures
user1479485
user1479485

Reputation: 79

Stored Procedure in C#

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

Upvotes: 1

Views: 910

Answers (3)

Aghilas Yakoub
Aghilas Yakoub

Reputation: 28970

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);

Upvotes: 5

Darren
Darren

Reputation: 70728

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);

Upvotes: 2

Marc Gravell
Marc Gravell

Reputation: 1062780

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...
    }
}

Upvotes: 5

Related Questions