Web API - Search SQL data

Question

Error:

The parameters dictionary contains a null entry for parameter 'id' of non-nullable type 'System.Int32' for method 'CumulativeP1.Models.Teacher FindTeacher(Int32)' in 'CumulativeP1.Controllers.TeacherDataController'. An optional parameter must be a reference type, a nullable type, or be declared as an optional parameter.

teacherid - int, primary key in SQL.

API controller:

 [HttpGet]
 public List<Teacher> ListTeachers()
 {
     // Create an instance of a connection
     MySqlConnection Connection = School.AccessDatabase();

     // Open the connection between the web server and database
     Connection.Open();

     // Establish a new command (query) for our database
     MySqlCommand Command = Connection.CreateCommand();

     // SQL query
     Command.CommandText = "Select * from teachers";

     // Gather result set of query into a variable
     MySqlDataReader ResultSet = Command.ExecuteReader();

     // Create an empty list of Teacher Names
     List<Teacher> Teachers = new List<Teacher>();

     // Loop through each row the result set
     while (ResultSet.Read())
     {
         // Access column information by the DB column name as an index
         string TeacherName = ResultSet["teacherfname"] + " " + ResultSet["teacherlname"];
         string EmployeeNumber = Convert.ToString(ResultSet["employeenumber"]);
         decimal TeacherSalary = Convert.ToDecimal(ResultSet["salary"]);
         DateTime TeacherHireDate = Convert.ToDateTime(ResultSet["hiredate"]);

         Teacher NewTeacher = new Teacher();
         NewTeacher.TeacherName= TeacherName;
         NewTeacher.TeacherSalary= TeacherSalary;
         NewTeacher.TeacherHireDate= TeacherHireDate;
         NewTeacher.EmployeeNumber= EmployeeNumber;

         // Add the Teacher elements to the List
         Teachers.Add(NewTeacher);
     }

     // Close the connection between the MySQL database and the web server
     Connection.Close();

     // Return the final list of author names
     return Teachers;
 }

 [HttpGet]
 public Teacher FindTeacher(int id)
 {
     Teacher NewTeacher = new Teacher();

     // Create an instance of a connection
     MySqlConnection Connection = School.AccessDatabase();

     // Open the connection between the web server and database
     Connection.Open();

     // Establish a new command (query) for our database
     MySqlCommand Command = Connection.CreateCommand();

     // SQL query
     Command.CommandText = "SELECT * FROM teachers WHERE teacherid = "+id;

     // Gather result set of query into a variable
     MySqlDataReader ResultSet = Command.ExecuteReader();

     while (ResultSet.Read())
     {
         string TeacherName = ResultSet["teacherfname"] + " " + ResultSet["teacherlname"];
         string EmployeeNumber = Convert.ToString(ResultSet["employeenumber"]);
         decimal TeacherSalary = Convert.ToDecimal(ResultSet["salary"]);
         DateTime TeacherHireDate = Convert.ToDateTime(ResultSet["hiredate"]);

         NewTeacher.TeacherName = TeacherName;
         NewTeacher.TeacherSalary = TeacherSalary;
         NewTeacher.TeacherHireDate = TeacherHireDate;
         NewTeacher.EmployeeNumber = EmployeeNumber;
     }

     return NewTeacher;
 }

My model class:

 public class Teacher
 {
     // we use this class to describe what teachers' fields are for other components
     public string TeacherName { get; set; }
     public string EmployeeNumber { get; set; }
     public decimal TeacherSalary { get; set; }
     public DateTime TeacherHireDate { get; set; }
     public int TeacherID { get; set; }
 }

My ASP.NET MVC controller:

// GET: teacher/show/{id}
public ActionResult Show(int id)
{   
    TeacherDataController controller = new TeacherDataController();
    Teacher NewTeacher = controller.FindTeacher(id);
    
    return View(NewTeacher);
}

Haven't started to work on html, very simple now.

@model CumulativeP1.Models.Teacher
@{
    ViewBag.Title = "Show";
}

<h2>Show Teachers</h2>
@Model.TeacherName

I suppose the problem is about teacherid, is my code right in getting SQL data?

Command.CommandText = "SELECT * FROM teachers WHERE teacherid = "+id;

Not sure why the error message indicating null parameter, teacherid is the primary key in the database...

Answer 1

The parameters dictionary contains a null entry for parameter 'id' of non-nullable type 'System.Int32' for method 'CumulativeP1.Models.Teacher FindTeacher(Int32)' in 'CumulativeP1.Controllers.TeacherDataController'. An optional parameter must be a reference type, a nullable type, or be declared as an optional parameter.

Based on your shared code snippet, the error message indicates that the id parameter being passed to the FindTeacher method is null. This can happen if the routing or the way the parameters are being passed in your MVC controller is incorrect.

This might also happend the way you are initiating the API controller within your MVC controller. Becuase the way you are calling API controller from your API controller this is what we dont do this way at all.

I am mostly foucsing on your current implementation, so not teaching what would be the ideal practice as the way you are dealing with raw sql query or API controller and MVC controller data interaction has lot of flows but for testing or learning purpose, I am considering that's okay.

I suppose the problem is about teacherid, is my code right in getting SQL data?

I don't think so. Becuase, I have cheked that line of code which getting the value accordingly and related data has been fetched as you can see below:

So, first of all while sending request form your MVC view, make sure you are calling the action with the correct parameter passed.

So I did as following:

MVC Controller Code:

public class TeacherController : Controller
{
    private readonly TeacherDataController _teacherDataController;

    public TeacherController(TeacherDataController teacherDataController)
    {
        _teacherDataController = teacherDataController;
    }


    // GET: Teacher/Show/{id}
    public IActionResult Show(int id)
    {
        var teacher = _teacherDataController.FindTeacher(id);
        return View(teacher);
    }
}

You should get the Id like below:

API Controller:

[ApiController]
[Route("api/[controller]")]
public class TeacherDataController : ControllerBase
{
    private readonly string _connectionString;

    public TeacherDataController(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
    }

    private SqlConnection AccessDatabase()
    {
        return new SqlConnection(_connectionString);
    }

    

    [HttpGet("FindTeacher/{id}")]
    public Teacher FindTeacher(int id)
    {
        Teacher NewTeacher = new Teacher();
        SqlConnection Connection = AccessDatabase();
        Connection.Open();
        SqlCommand Command = Connection.CreateCommand();
        Command.CommandText = "SELECT * FROM teachers WHERE teacherid = @id";
        Command.Parameters.AddWithValue("@id", id);
        SqlDataReader ResultSet = Command.ExecuteReader();

        while (ResultSet.Read())
        {
            string TeacherName = ResultSet["teacherfname"] + " " + ResultSet["teacherlname"];
            string EmployeeNumber = Convert.ToString(ResultSet["employeenumber"]);
            decimal TeacherSalary = Convert.ToDecimal(ResultSet["salary"]);
            DateTime TeacherHireDate = Convert.ToDateTime(ResultSet["hiredate"]);

            NewTeacher.TeacherID = Convert.ToInt32(ResultSet["teacherid"]);
            NewTeacher.TeacherName = TeacherName;
            NewTeacher.TeacherSalary = TeacherSalary;
            NewTeacher.TeacherHireDate = TeacherHireDate;
            NewTeacher.EmployeeNumber = EmployeeNumber;
        }

        Connection.Close();
        return NewTeacher;
    }
}

Ensure that, your API controller instance has been initiated accordingly and received the parameter as expected.

Note: I kept your API controller code same as yours.

Program.cs:

If you are using .NET 8 or higher than .NET 6, you should inject the DI of your TeacherDataController instance, you should register that within program.cs file in order overcome service initialization exception.

I did that as following:

builder.Services.AddControllersWithViews();
builder.Services.AddScoped<TeacherDataController>();

Output:

Note: Based on my testing I am getting the data as expected. However, You could improve this code following couple of ways. You could use any ORM like entity framework in order to project your raw sql malicious attack. Apart from that, you could use asp.net core service for sharing data in between and many more. You haven't manage your request threading wisely which might create request blocking so try using asynchronous reqeust. You could refer to this official document if you want to refactor your code.

Answer 2

Make sure you have defined following route convention for optional parameter

in your WebApiConfig You have to change to

config.Routes.MapHttpRoute(
            name: "DefaultApi",//name can be anything
            routeTemplate: "api/{controller}/{action}/{id}",
            defaults: new { id = RouteParameter.Optional }