Stored Procedure in C# (ASP.NET CORE)

Question

We have a stored procedure created in the database. It is working just fine but I am not able to figure out how to call it within my code. For some reason I keep getting back

Procedure or function 'GetNextApplicationNumber' expects parameter '@ApplicationId', which was not supplied.

My code:

string connectionString = "THE_CONNECTION_STRING";
using (IDbConnection connection = new SqlConnection(connectionString))
using (IDbCommand command = connection.CreateCommand())
{
     command.CommandType = CommandType.StoredProcedure;

     IDbDataParameter parameter = command.CreateParameter();
     parameter.ParameterName = "@ApplicationId";
     parameter.Value = applicationId;
     parameter.DbType = DbType.String;

     command.Parameters.Add(parameter);

     command.CommandText = "[dbo].[GetNextApplicationNumber]";

     connection.Open();

     using (IDataReader reader = command.ExecuteReader())
     {
          string applicationNumber = string.Empty;

          if (reader.Read())
          {
              applicationNumber = reader.GetValue(0).ToString();
          }

          return applicationNumber;
     }
}

I have tried quite a few different ways (most of which seem to be outdated and no longer available).

You could try to use SQL Server Profiler to analize the SQL sent to your SQL Server. Try to not set the DbType property of your parameter. — musium
– musium, Commented Mar 16, 2017 at 23:34

Daniel A. White · Accepted Answer · 2017-03-17 00:32:56Z

2

When you set the CommandText property, it clears the parameters collection.

answered Mar 17, 2017 at 0:32

Daniel A. White

192k49 gold badges389 silver badges473 bronze badges

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

Comments

kwingkwingko · Accepted Answer · 2017-03-17 03:09:36Z

0

it seems like you are using the old ways.

Try this mate:

public string GetNextApplicationNumber(int applicationId)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    conn.Open();

                    using (SqlDataAdapter da = new SqlDataAdapter("GetNextApplicationNumber", conn))
                    {

                        da.SelectCommand.Parameters.AddWithValue("@ApplicationId", applicationId);
                        da.SelectCommand.CommandType = CommandType.StoredProcedure;
                        da.Fill(dt);

                        string applicationNumber = string.Empty;

                        applicationNumber = dt.Rows[0]["Column_Name"].ToString();

                        return applicationNumber;
                    }
                }
            }
            catch
            {
                throw;
            }
        }

answered Mar 17, 2017 at 3:09

kwingkwingko

8019 silver badges24 bronze badges

2 Comments

marc_s Over a year ago

You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results...

Brandon Turpy Over a year ago

I am using .net core and it does not seem the SqlDataAdapter class is compatible with core :(

Denis Staforkin · Accepted Answer · 2017-03-17 07:43:36Z

0

Consider using this one:

public static SqlCommand WithParameter(this SqlCommand sqlCommand, SqlParameter parameter)
    {
        sqlCommand.Parameters.Add(parameter);
        return sqlCommand;
    }

and then

SqlCommand command = new SqlCommand("[dbo].[GetNextApplicationNumber]",sqlConnection)
                     .WithParameter(new SqlParameter("@ApplicationId", SqlDbType.BigInt) {Value = 1});

using (SqlDataReader reader = command.ExecuteReader())
 {...}

answered Mar 17, 2017 at 7:43

Denis Staforkin

214 bronze badges

Collectives™ on Stack Overflow

Stored Procedure in C# (ASP.NET CORE)

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related