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, Dec 11 '19 at 10:45
*Side note, you should really be making use of `using` here as well.* — Thom A, Dec 11 '19 at 10:48
It appears you are passing a number in exec code and a string here cmd.Parameters.AddWithValue("@ID", "34926"); — Mary, Dec 14 '19 at 10:44

Salah Akbari · Accepted Answer · 2019-12-11T10:53:13.443

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 '19 at 10:53

answered Dec 11 '19 at 10:47

Salah Akbari

39,330
10
79
109

1

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: https://stackoverflow.com/questions/42719398/is-close-same-as-using-statement – Athanasios Kataras Dec 11 '19 at 11:04
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! – ruedi Dec 11 '19 at 11:43

score 1 · Answer 2 · answered Dec 11 '19 at 10:49

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

C# program does not execute Stored Procedure

2 Answers2