CODEWITHSUNDEEP
c#sql-serverasp.net-mvcstored-procedures
Vigs
Vigs

Reputation: 351

Sql Server Stored Procedure not executing from MVC

I am trying to execute a stored procedure in my Sql Server Database from an Asp.Net MVC project. I have set it up in a way that I can use it for testing purposes, which is why the variable "procedureTest" is a constant value (I know that "2044" is an existing record in my database). I will change this once I accomplish a successful run. Also, I know that my stored procedure works because I have executed it in Sql Server Management Studio successfully. The procedure has the task of adding ID from one table to another, but I have yet to see it appear in this table. I am not receiving an error in my catch block so I am kind of lost at the moment. I could definitely use your help.

try
{
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();

        int procedureTest = 2044;

        var command = new SqlCommand("SELECT ID FROM Images WHERE ID = @id", connection);
        var paramDate = new SqlParameter("@id", procedureTest);
        command.Parameters.Add(paramDate);
        var reader = command.ExecuteReader();

        while (reader.Read())
        {
            var storedProcCommand = new SqlCommand("EXEC addToNotificationTable @ID", connection);
            var paramId = new SqlParameter("@ID", reader.GetInt32(0));
            storedProcCommand.Parameters.Add(paramId);
            command.ExecuteNonQuery();
        }
    }
}
catch (Exception e)
{
    string exceptionCause = String.Format("An error occurred: '{0}'", e);
    System.IO.File.WriteAllText(@"C:\Users\Nathan\Documents\Visual Studio 2013\Projects\MVCImageUpload\uploads\exception.txt", exceptionCause);
}

Stored Procedure:

CREATE PROCEDURE addToNotificationTable @ID int
AS
Insert NotificationTable (ID)
SELECT ID 
FROM Images
Where ID = @ID

Upvotes: 0

Views: 1606

Answers (2)

Adersh M
Adersh M

Reputation: 586

First of all, you missed to specify the command type. Also using EXEC in SqlCommand is not a proper way.

Please try with the below code

while (reader.Read())
        {
            using(SqlCommand storedProcCommand = new SqlCommand("addToNotificationTable", connection)) //Specify only the SP name
            { 
                storedProcCommand.CommandType = CommandType.StoredProcedure; //Indicates that Command to be executed is a stored procedure no a query
                var paramId = new SqlParameter("@ID", reader.GetInt32(0));
                storedProcCommand.Parameters.Add(paramId);
                storedProcCommand.ExecuteNonQuery()
            }
        }

Since you are calling the sp inside a while loop, wrap the code in using() { } to automatically dispose the command object after each iteration

Upvotes: 0

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

Change you code like this

while (reader.Read())
        {
            var storedProcCommand = new SqlCommand("EXEC addToNotificationTable @ID", connection);
            var paramId = new SqlParameter("@ID", reader.GetInt32(0));
            storedProcCommand.Parameters.Add(paramId);
            storedProcCommand.ExecuteNonQuery();
        }

Upvotes: 1

Related Questions