从SQL Server数据库获取1个单元格的更简单方法

Easier way of getting 1 cell from a SQL Server database
2021-05-11
  •  译文(汉语)
  •  原文(英语)

现在,我将数据添加到数据集中,然后在该数据的第一张表中选择第一行和第一列.必须有一些更短的方法吗?

SqlCommand LoadCmd = new SqlCommand("Select start_tid from EKGDATA where ekgmaaleid = @ekgmaaleid", GlobalVariables.offCon);
LoadCmd.Parameters.AddWithValue("@ekgmaaleid", maaleid);

SqlDataAdapter da = new SqlDataAdapter(LoadCmd);
DataSet ds = new DataSet();

da.Fill(ds);

string dato = Convert.ToString(ds.Tables[0].Rows[0][0]);
解决过程1

使用SqlCommand.ExecuteScalar

执行查询,并返回查询返回的结果集中第一行的第一列.其他列或行将被忽略.

using(SqlCommand LoadCmd = new SqlCommand("Select start_tid from EKGDATA where ekgmaaleid = @ekgmaaleid", GlobalVariables.offCon))
{
   LoadCmd.Parameters.AddWithValue("@ekgmaaleid", maaleid);
   GlobalVariables.offCon.Open();
   var result = LoadCmd.ExecuteScalar();
}

ExecuteScalar返回object类型数据,您可以将其转换/转换为所需的类型.

(考虑using statement与您的SqlCommand对象一起使用)

解决过程2
string dato;
using (var offCon = new SqlConnection("..."))
using (var LoadCmd = new SqlCommand("Select start_tid from EKGDATA where ekgmaaleid = @ekgmaaleid", GlobalVariables.offCon))
{
    LoadCmd.Parameters.AddWithValue("@ekgmaaleid", maaleid);
    offCon.Open();
    dato = (string)LoadCmd.ExecuteScalar();
}
速聊1:
(字符串)代替Convert.ToString?聪明的.
解决过程3

如果您的查询仅返回一行且只有一列,则正确的方法是ExecuteScalar

SqlCommand LoadCmd = new SqlCommand("Select start_tid from EKGDATA where ekgmaaleid = @ekgmaaleid", GlobalVariables.offCon);
LoadCmd.Parameters.AddWithValue("@ekgmaaleid", maaleid);
object result = LoadCmd.ExecuteScalar();

然后,根据期望的数据类型,检查是否为空返回值并尝试转换结果.
因此,假设返回的值是一个字符串

if(result != null)
{
    string dato = result.ToString();
}

除非您完全确定WHERE子句标识的行始终存在于数据表中,否则在您的情况下必须强制检查null.还要注意,ExecuteScalar返回NULL,DBNull.Value如果找不到所需的值,则不返回NULL

速聊1:
您不必担心start_tid列中是否为null,但是如果您的参数@ekgmaaleid始终与该ekgmaaleid列匹配.如果where子句找不到与参数值匹配的任何记录,则ExecuteScalar的结果将为null.
速聊2:
啊,我明白你的意思了.嗯,也不会发生这种情况,但是下次使用SQL连接创建程序时,我将回头看您的代码.

Right now I'm adding data to a dataset and then selecting the first row and column in the first table of that data. There must be some shorter way of doing this?

SqlCommand LoadCmd = new SqlCommand("Select start_tid from EKGDATA where ekgmaaleid = @ekgmaaleid", GlobalVariables.offCon);
LoadCmd.Parameters.AddWithValue("@ekgmaaleid", maaleid);

SqlDataAdapter da = new SqlDataAdapter(LoadCmd);
DataSet ds = new DataSet();

da.Fill(ds);

string dato = Convert.ToString(ds.Tables[0].Rows[0][0]);
Solutions1

Use SqlCommand.ExecuteScalar

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

using(SqlCommand LoadCmd = new SqlCommand("Select start_tid from EKGDATA where ekgmaaleid = @ekgmaaleid", GlobalVariables.offCon))
{
   LoadCmd.Parameters.AddWithValue("@ekgmaaleid", maaleid);
   GlobalVariables.offCon.Open();
   var result = LoadCmd.ExecuteScalar();
}

ExecuteScalar returns object type data, You can cast/convert it to your required type.

(Consider using using statement with your SqlCommand object)

Solutions2
string dato;
using (var offCon = new SqlConnection("..."))
using (var LoadCmd = new SqlCommand("Select start_tid from EKGDATA where ekgmaaleid = @ekgmaaleid", GlobalVariables.offCon))
{
    LoadCmd.Parameters.AddWithValue("@ekgmaaleid", maaleid);
    offCon.Open();
    dato = (string)LoadCmd.ExecuteScalar();
}
Talk1:
(string) is instead of Convert.ToString? Clever.
Solutions3

If your query returns just one row with only one column then the right method is ExecuteScalar

SqlCommand LoadCmd = new SqlCommand("Select start_tid from EKGDATA where ekgmaaleid = @ekgmaaleid", GlobalVariables.offCon);
LoadCmd.Parameters.AddWithValue("@ekgmaaleid", maaleid);
object result = LoadCmd.ExecuteScalar();

Then, depending on the datatype expected, you check for null return and try to convert the result.
So, assuming that the value returned is a string

if(result != null)
{
    string dato = result.ToString();
}

The checking for null is mandatory in your case unless you are absolutely certain that the row identified by the WHERE clause always exists in your datatable. Also keep note that ExecuteScalar returns NULL and not DBNull.Value if the value required is not found

Talk1:
You should not be concerned for null in the start_tid column, but if your parameter @ekgmaaleid is always matched against ekgmaaleid column. If the where clause doesn't find any record matching the parameter value then the result of ExecuteScalar will be null.
Talk2:
Ah I see what you mean. Well that won't happen either, but I'll look back on your code next time I make a program with an SQL connection.
转载于:https://stackoverflow.com/questions/20200143/easier-way-of-getting-1-cell-from-a-sql-server-database

本人是.net程序员,因为英语不行,使用工具翻译,希望对有需要的人有所帮助
如果本文质量不好,还请谅解,毕竟这些操作还是比较费时的,英语较好的可以看原文

留言回复
我们只提供高质量资源,素材,源码,坚持 下了就能用 原则,让客户花了钱觉得值
上班时间 : 周一至周五9:00-17:30 期待您的加入