作者:翔念式的天空_549 | 来源:互联网 | 2022-12-02 15:42
我正在尝试查询SQL Server数据库并以JSON格式返回响应.我正在尝试下面的代码
using (SqlConnection cOnnection= new SqlConnection(connStr))
{
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add("@ROOM_Data", SqlDbType.VarChar);
command.Parameters["@ROOM_Data"].Value = ROOM;
connection.Open();
List records = new List();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var row = new DatabaseResult
{
request_id = (int)reader["request_id"],
room = (string)reader["room"],
jrs_no = (int)reader["jrs_no"],
submit_date = (DateTime)reader["submit_date"],
sample_no = (int)reader["sample_no"],
animal_id = (string)reader["animal_id"],
pen_id = (string)reader["pen_id"],
ped_no = (string)reader["ped_no"],
gender = (string)reader["gender"],
dob = (DateTime)reader["dob"],
parent_mating = (string)reader["parent_mating"],
generation = (string)reader["generation"],
allele = (string)reader["allele"],
status_type = (string)reader["status_type"],
genotype = (string)reader["genotype"],
comments = (string)reader["comments"],
completion_date = (DateTime)reader["completion_date"],
disposition = (string)reader["disposition"],
};
records.Add(row);
}
return Ok(records);
在任何字段中都存在null时,我遇到问题.我试着查看其他论坛,我可以理解我们需要检查每一列,如果它在呈现之前是否为null.但我正在检查我是否可以通过可以处理所有案件的方法做到?
1> Tetsuya Yama..:
您可以DBNull.Value
像这样检查列值:
request_id = (reader["request_id"] == DBNull.Value) ? default(int) : (int)reader["request_id"];
如果要将过程简化为所有列,请创建一个DBNull.Value
与此示例类似的扩展方法:
// using DBNull.Value comparison
public static T GetValue(this SqlDataReader reader, string columnName)
{
var value = reader[columnName]; // read column value
return value == DBNull.Value ? default(T) : (T)value;
}
// alternative using GetOrdinal and IsDBNull
public static T GetValue(this SqlDataReader reader, string columnName)
{
int index = reader.GetOrdinal(columnName); // read column index
return reader.IsDBNull(index) ? default(T) : (T)reader.GetValue(index);
}
用法示例:
request_id = GetValue(reader, "request_id");