System.Data.SqlClient.SqlException: 'Could not find stored procedure 'EXEC sp_GetmonthlyReport 99,2'.'

Question

I try to write Web API code for reading data from my database and exporting this data in an Excel sheet, but when I try to run code, I get error

System.Data.SqlClient.SqlException: 'Could not find stored procedure 'EXEC sp_GetmonthlyReport 99,2'.'

Here is my API controller code:

[HttpGet] 
[Route("api/controller/ExportToExcel")] 
public HttpResponseMessage ExportToExcel(int EmployeeId, int MonthNum)
{ 
    // Get the data from the database 
    List<DailyTaskModel> model = mr.GetDailyTasks(EmployeeId, MonthNum);

    // Create the Excel package 
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial; 

    using (ExcelPackage excelPackage = new ExcelPackage())
    {
        ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("TaskList");

        // Write the header row worksheet.Cells[1, 1].Value = "EmployeeId"; 
        worksheet.Cells[1, 2].Value = "Taskdate"; 
        worksheet.Cells[1, 3].Value = "Capex_hr"; 
        worksheet.Cells[1, 4].Value = "Opex_Hr"; 
        worksheet.Cells[1, 5].Value = "Cr_No"; 
        worksheet.Cells[1, 6].Value = "Cr_Name"; 
        worksheet.Cells[1, 7].Value = "Project"; 
        worksheet.Cells[1, 8].Value = "Pr_Status"; 
        worksheet.Cells[1, 9].Value = "Opex_Description"; 
        worksheet.Cells[1, 10].Value = "Cr_Live_Date"; 
        worksheet.Cells[1, 11].Value = "Actual_Capex"; 
        worksheet.Cells[1, 12].Value = "Actual_Opex";

        // Write the data rows 
        int row = 2; 

        foreach (var task in model)
        { 
            worksheet.Cells[row, 1].Value = task.EmployeeId; 
            worksheet.Cells[row, 2].Value = task.Taskdate; 
            worksheet.Cells[row, 3].Value = task.Capex_Hr; 
            worksheet.Cells[row, 4].Value = task.Opex_Hr; 
            worksheet.Cells[row, 4].Value = task.Cr_No; 
            worksheet.Cells[row, 4].Value = task.Cr_Name; 
            worksheet.Cells[row, 4].Value = task.Project; 
            worksheet.Cells[row, 4].Value = task.Pr_Status; 
            worksheet.Cells[row, 4].Value = task.Opex_Description;           
            worksheet.Cells[row, 4].Value = task.Cr_Live_Date;   
            worksheet.Cells[row, 4].Value = task.Actual_Capex; 
            worksheet.Cells[row, 4].Value = task.Actual_Opex;

            row++;
        }

        // Generate the file contents 
        byte[] fileContents = excelPackage.GetAsByteArray();

        // Set the response content  
        HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK); 
        response.Content = new ByteArrayContent(fileContents); 
        response.Content.Headers.ContentType = new    MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); 
        response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
                { 
                     FileName = "TaskList.xlsx"
                };

        return response;
    }
}

And here is my business logic

public static string GetConnectionString(string connectionName = "MyConnection")
{ 
    return ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
}

public List<DailyTaskModel> GetDailyTasks(int EmployeeId, int MonthNumber)
{ 
    List<DailyTaskModel> model = new List<DailyTaskModel>();

    using (IDbConnection conn = new SqlConnection(GetConnectionString()))
    {
        var sql = @"EXEC sp_GetmonthlyReport " + EmployeeId + "," + MonthNumber; 

        SqlCommand cmd = new SqlCommand(sql, (SqlConnection)conn); 
        cmd.CommandType = CommandType.StoredProcedure;

        conn.Open(); 

        SqlDataReader rdr = cmd.ExecuteReader();

        while (rdr.Read())
        { 
            DailyTaskModel task = new DailyTaskModel(); 
            task.EmployeeId = (int)rdr["EmployeeId"]; 
            task.Taskdate = rdr["Taskdate"].ToString(); 
            task.Capex_Hr = (int)rdr["Capex_Hr"]; 
            task.Opex_Hr = (int)rdr["Opex_Hr"]; 
            task.Cr_No = (int)rdr["Cr_No"]; 
            task.Cr_Name = rdr["Cr_Name"].ToString(); 
            task.Project = rdr["Project"].ToString(); 
            task.Pr_Status = rdr["Pr_Status"].ToString(); 
            task.Opex_Description = rdr["Opex_description"].ToString(); 
            task.Cr_Live_Date = rdr["Cr_Live_Date"].ToString(); 
            task.Actual_Capex = (int)rdr["Actual_Capex"]; 
            task.Actual_Opex = (int)rdr["Actual_Opex"];
 
            model.Add(task);
        }
    }

    return model;
}

And here is my stored procedure:

ALTER PROCEDURE [dbo].[sp_GetmonthlyReport] 
    (
--EXEC sp_GetmonthlyReport 99, 2 @EmployeeId INT, @MonthNumber INT ) AS     
BEGIN 
    SELECT  
        EmployeeId, Taskdate, Capex_Hr, Opex_Hr, Cr_No, Cr_Name, 
        Project, Pr_Status, Opex_Description, Actual_Capex, 
        Actual_Opex, Cr_Live_Date 
    FROM 
        Daily_Task_History 
    WHERE 
        EmployeeId = @EmployeeId 
        AND MONTH(Taskdate) = @MonthNumber  
END

My stored procedure is not getting read. What's wrong in this code?

Answer 1

When the CommandType property is set to StoredProcedure, the CommandText property should be set to the name of the stored procedure to be accessed. You can add the parameters with cmd.Parameters look at What's the best method to pass parameters to SQLCommand?