VS2012 Express Web API连接SQL Server返回值全为null求助

Hey there, let's figure out why your Web API is returning all null values even though your SQL statement works perfectly in SQL Server 2014. I've hit similar snags before, so here are the most likely fixes to test out:

1. Column Name ↔ Class Property Mismatch #

This is the most common culprit. SQL Server is case-insensitive by default, but C# is strictly case-sensitive. If your database column is ProductName but your C# class uses productName, or if the names don't match at all (like Prod_Name vs ProductName), the mapper won't populate the properties correctly, leaving them null.

Fix:

• Double-check that every property name in your entity class exactly matches the corresponding database column name.
• If you can't rename properties/columns, use the [Column] attribute to explicitly map them:

using System.ComponentModel.DataAnnotations.Schema;

public class Product
{
    [Column("Prod_Name")] // Maps to the database column "Prod_Name"
    public string ProductName { get; set; }
    // Add other mapped properties here
}

2. Data Type Incompatibility #

If your database column's data type doesn't align with your C# property type, the data can't be converted properly, resulting in null values. For example:

• A nvarchar database column mapped to an int property
• A datetime column mapped to a string property (without proper conversion)

Fix:

• Cross-reference each database column's data type with your class property type. Ensure they're compatible (e.g., nvarchar ↔ string, int ↔ int, datetime ↔ DateTime).

3. Incorrect Data Reading Logic (SqlDataReader) #

If you're using SqlDataReader to fetch results, you might be mishandling DBNull values or referencing the wrong column name.

Bad Example:

// This will throw an error if the value is DBNull, or return null if the column name is wrong
product.ProductName = reader["ProductName"].ToString();

Fixed Example:

// Check for DBNull first and use ordinal lookup for reliability
int productNameOrdinal = reader.GetOrdinal("ProductName");
product.ProductName = reader.IsDBNull(productNameOrdinal) 
    ? null 
    : reader.GetString(productNameOrdinal);

4. Wrong Database Target in Connection String #

Even if your SQL runs in SSMS, your Web API might be connecting to a different database instance or a test copy that has empty/NULL data.

Fix:

• Compare your connection string in web.config with the one you use in SSMS. Ensure the Initial Catalog (database name) and Data Source (server instance) match exactly.
  Example connection string:

<connectionStrings>
  <add name="MyDbConn" 
       connectionString="Data Source=.\SQL2014;Initial Catalog=YourDatabase;Integrated Security=True;" 
       providerName="System.Data.SqlClient" />
</connectionStrings>

5. Malformed SQL in Code #

Sometimes the SQL string you build in code isn't identical to the one you tested in SSMS. For example, incorrect keyword concatenation, missing quotes, or typos in table/column names that only show up when variables are inserted.

Fix:

• Log the exact SQL statement executed by your code (print it to the debug console or a log file) and compare it to the working version from SSMS. Look for differences in WHERE clauses, table names, or parameter handling.
• Pro Tip: Always use parameterized queries instead of string concatenation to avoid this issue and prevent SQL injection:

string sql = "SELECT * FROM Products WHERE Name LIKE @SearchKeyword";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
    cmd.Parameters.AddWithValue("@SearchKeyword", $"%{searchKeyword}%");
    // Execute command and read results
}

6. ORM Mapping Issues (If Using Entity Framework) #

If you're using EF (Code First or Database First), misconfigured mappings can cause null values. Common issues include missing [Table]/[Key] attributes, or incorrect table/column name conventions.

Fix:

• Explicitly specify table names and keys using data annotations:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

[Table("Products")] // Ensure this matches your database table name
public class Product
{
    [Key]
    public int ProductId { get; set; } // Maps to the primary key column
    public string ProductName { get; set; }
}

Start with the first two checks—column name and data type mismatches are almost always the root cause here. Let me know if any of these fix your issue!

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