C# Web API参数查询始终返回全部数据而非单条数据问题排查

我正在开发基于C#的Web API，从SQL数据库获取学生数据。无参数的Get方法能正常返回所有学生数据，但通过GET请求传入单个学生编号（比如https://localhost:XXXXX/custom-roles-api/campusCustomRoles/12345）时，接口仍返回全部数据，而非指定学生的单条数据。

相关代码 #

Roles类定义 #

public class Roles
{
    List<Roles> studentRoles = new List<Roles>();
    
    public string UserName { get; set; }
    public string PersonName { get; set; }
    public string Profile { get; set; }
    public string Level { get; set; }
    public int Year { get; set; }
    public string Department { get; set; }
}

public class readRoles : Roles
{
    public readRoles(DataRow dataRow)
    {
        UserName = (string)dataRow["UserName"];
        PersonName = (string)dataRow["PersonName"];
        Profile = (string)dataRow["Profile"];
        Level = (string)dataRow["Level"];
        Year = Convert.ToInt32(dataRow["Year"]);
        Department = (dataRow["Department"] == DBNull.Value) ? "No Department" : dataRow["Department"].ToString();
    }

    public string UserName { get; set; }
    public string PersonName { get; set; }
    public string Profile { get; set; }
    public string Level { get; set; }
    public int Year { get; set; }
    public string Department { get; set; }
}

无参数Get方法（可正常工作） #

List<Roles> studentRoles = new List<Roles>();

private SqlDataAdapter _adapter;
public IEnumerable<Roles> Get()
{
    //Create link to database
    string connString;
    SqlConnection con;
    connString = @"XXX";
    DataTable _dt = new DataTable();
    con = new SqlConnection(connString);
    con.Open();

    var sql = "some sql here";


    SqlCommand CMD = new SqlCommand();
    CMD.Connection = con;
    CMD.CommandText = sql;
    CMD.CommandType = System.Data.CommandType.Text;
    SqlDataReader dr = CMD.ExecuteReader();
    _adapter = new SqlDataAdapter
    {
        SelectCommand = new SqlCommand(sql, con)
    };
    _adapter.Fill(_dt);
    List<Roles> roles = new List<Roles>(_dt.Rows.Count);
    if (_dt.Rows.Count > 0)
    {
        foreach (DataRow studentrole in _dt.Rows)
        {
            roles.Add(new readRoles(studentrole));
        }
    }

    return roles;
}

带参数的Get方法（存在问题） #

[HttpGet] 
public IHttpActionResult Get(string userName)
{
    string connString;
    SqlConnection con;
    connString = @"XXX";
    DataTable _dt = new DataTable();
    con = new SqlConnection(connString);
    con.Open();

    var sql = "select distinct .... where student_reference = " + userName +;


    SqlCommand CMD = new SqlCommand();
    CMD.Connection = con;
    CMD.CommandText = sql;
    CMD.CommandType = System.Data.CommandType.Text;
    SqlDataReader dr = CMD.ExecuteReader();
    _adapter = new SqlDataAdapter
    {
        SelectCommand = new SqlCommand(sql, con)
    };
    _adapter.Fill(_dt);
    List<Roles> roles = new List<Roles>(_dt.Rows.Count);
    if (_dt.Rows.Count > 0)
    {
        foreach (DataRow studentrole in _dt.Rows)
        {
            roles.Add(new readRoles(studentrole));
        }
    }

    var singlestu =  roles.FirstOrDefault(e => e.UserName == userName);
    return Ok(singlestu);
}

WebConfig中的自定义路由配置 #

public static void Register(HttpConfiguration config)
{
    // Web API routes

    // This is the original Route
    //config.MapHttpAttributeRoutes();

    //config.Routes.MapHttpRoute(
    //    name: "DefaultApi",
    //    routeTemplate: "api/{Controller}/{id}",
    //    //routeTemplate: "api/{controller}/{action}/{id}",
    //    defaults: new { id = RouteParameter.Optional }
    //);

    // Custom Route  
    config.MapHttpAttributeRoutes();

    // Define route
    System.Web.Http.Routing.IHttpRoute rolesRoute = config.Routes.CreateRoute("custom-roles-api/{controller}/{id}",
                                            new { id = RouteParameter.Optional }, null);
    // Add route
    config.Routes.Add("DefaultApi", rolesRoute);
}

补充：使用参数化查询后的代码 #

[HttpGet] 
public IHttpActionResult Get(string userName)
{
    string connString;
    SqlConnection con;
    connString = @"XXXX";
    DataTable _dt = new DataTable();
    con = new SqlConnection(connString);
    con.Open();

    var sql = "select distinct .... where student_reference =@UserName " +
              "and department ='LAW' " +;

    SqlParameter param = new SqlParameter();
    param.ParameterName = "@UserName";
    param.Value = UserName;
    
    SqlCommand CMD = new SqlCommand();
    CMD.Connection = con;
    CMD.CommandText = sql;
    CMD.CommandType = System.Data.CommandType.Text;
    SqlDataReader dr = CMD.ExecuteReader();
    _adapter = new SqlDataAdapter
    {
        SelectCommand = new SqlCommand(sql, con)
    };
    _adapter.Fill(_dt);
    List<Roles> roles = new List<Roles>(_dt.Rows.Count);
    if (_dt.Rows.Count > 0)
    {
        foreach (DataRow studentrole in _dt.Rows)
        {
            roles.Add(new readRoles(studentrole));
        }
    }

    var singlestu =  roles.FirstOrDefault(e => e.UserName == userName);
    return Ok(singlestu);
}

排查指引 #

1. 路由参数不匹配：自定义路由定义的参数是{id}，但带参数的Get方法接收的是userName，Web API无法自动将URL中的12345映射到userName。解决方式二选一：

   • 将方法参数改为id，并同步修改SQL查询条件；
   • 修改路由模板为custom-roles-api/{controller}/{userName}，保证参数名一致。

2. SQL语法错误：原带参数方法和参数化查询版本的SQL语句末尾都有多余的+号，会导致语法错误，实际执行的可能是无过滤的查询。需修正SQL拼接：

   var sql = "select distinct .... where student_reference =@UserName and department ='LAW'";
   

3. 参数化查询未添加参数：参数化查询版本中创建了SqlParameter，但未将其添加到SqlCommand的Parameters集合，导致@UserName未被赋值，查询条件失效。需添加：

   CMD.Parameters.Add(param);
   

4. 方法重载匹配冲突：两个Get方法都标记[HttpGet]，当路由参数未正确映射时，Web API可能匹配到无参数版本。可给带参数的方法添加路由特性区分：

   [HttpGet("{userName}")]
   public IHttpActionResult Get(string userName)
   {
       // 方法逻辑
   }
   

5. 子类重复定义属性：readRoles继承自Roles但重复定义了所有属性，会隐藏基类属性，可能导致数据映射异常。建议删除readRoles中的重复属性，直接使用基类属性。

内容的提问来源于stack exchange，提问作者GBM