C# program does not execute Stored Procedure

Question

I want to run my Stored Procedure on an Azure SQL-Server from C# but it does not work and I do not know how to find out why. The stored procedure has two input parameters and based on these insert information into a table. So I can see in the table if there is a new entry or not (SP worked or not).

Using my SP in the SQL-Server as

exec Commentary.Add_Information @ID = 34926, @year = '2020'

works absolutely fine. But executing it from c# does not make an entry in the table

public void CreateBasicInformation()
{
    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
    builder.DataSource = "myServer.database.windows.net";
    builder.UserID = "admin";
    builder.Password = "myPass";
    builder.InitialCatalog = "myDB";

    try
    {
        SqlConnection cn = new SqlConnection(builder.ConnectionString);
        cn.Open();
        SqlCommand cmd = new SqlCommand("Add_Information", cn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ID", "34926");
        cmd.Parameters.AddWithValue("@year", "2020");
        cn.Close();
    }
    catch (SqlException sqlEx)
    {
        Console.WriteLine(sqlEx.Message);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

I already tried to catch an error but there is none. All I get at the end is

The program '[9480] test_SP.exe' has exited with code 0 (0x0).

Is there a mistake in the code or is there a way I can find out why C# and the SP are not working together?

Where is your ExecuteNonQuery(), ExecuteReader(), etc command? — Thom A
– Thom A ♦, Commented Dec 11, 2019 at 10:45
Side note, you should really be making use of using here as well. — Thom A
– Thom A ♦, Commented Dec 11, 2019 at 10:48
It appears you are passing a number in exec code and a string here cmd.Parameters.AddWithValue("@ID", "34926"); — Mary
– Mary, Commented Dec 14, 2019 at 10:44

Salah Akbari · Accepted Answer · 2019-12-11 10:53:13Z

7

You didn't actually execute your command, you need to add the following line to execute it:

cmd.ExecuteNonQuery();
cn.Close();

It is also highly recommended to use the Using statement nowadays:

The purpose of the using statement is to provide a simpler way to specify when the unmanaged resource is needed by your program, and when it is no longer needed.

edited Dec 11, 2019 at 10:53

answered Dec 11, 2019 at 10:47

Salah Akbari

40k10 gold badges85 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Athanasios Kataras Over a year ago

Missed you by a moment :) Good spot on the using, as close is not sufficient to dispose the object properly. A very good explanation here: stackoverflow.com/questions/42719398/…

ruedi Over a year ago

That was it. Using does not support Catch Exception that was why I used it. But I read more about it and change back to using. Thanks a lot!

Athanasios Kataras · Accepted Answer · 2019-12-11 10:49:24Z

You are not executing your command: https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executenonquery?view=netframework-4.8

Try

public void CreateBasicInformation()
{
    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
    builder.DataSource = "myServer.database.windows.net";
    builder.UserID = "admin";
    builder.Password = "myPass";
    builder.InitialCatalog = "myDB";

    try
   {
        SqlConnection cn = new SqlConnection(builder.ConnectionString);
        cn.Open();
        SqlCommand cmd = new SqlCommand("Add_Information", cn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ID", "34926");
        cmd.Parameters.AddWithValue("@year", "2020");
        // Execute and get rows affected count. 
       var rowsAffected = cmd.ExecuteNonQuery();
        cn.Close();
    }
    catch (SqlException sqlEx)
    {
        Console.WriteLine(sqlEx.Message);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

Anthony Griggs · Accepted Answer · 2024-06-17 14:13:39Z

0

I came across a circumstance where I couldn't get a stored procedure to execute in C#, however it executed fine from SSMS! I was using a table variable in my stored procedure which turned out to be the culprit. When I switched it to a temp table, it not only executed faster (as expected) it also would execute from C#. Obviously your circumstance was not similar, but I thought I would post this if anyone else came across this unusual circumstance as I've not seen anyone else mention this possibility.

answered Jun 17, 2024 at 14:13

Anthony Griggs

1,6572 gold badges19 silver badges42 bronze badges

Collectives™ on Stack Overflow

C# program does not execute Stored Procedure

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related