返回选择语句的值
本文关键字:语句 选择 返回 | 更新日期: 2023-09-27 17:49:38
我想将选择语句的结果值检索到一个字符串变量中。这样的:
OleDbCommand cmd1 = new OleDbCommand();
cmd1.Connection = GetConnection();
cmd1.CommandText = "SELECT treatment FROM appointment WHERE patientid = " + text;
cmd1.ExecuteNonQuery();
我想将选择的处理值放入字符串变量中。我该怎么做呢?
使用ExecuteReader()
而不是ExecuteNonQuery()
。ExecuteNonQuery()
只返回受影响的行数。
try
{
SqlDataReader dr = cmd1.ExecuteReader();
}
catch (SqlException oError)
{
}
while(dr.Read())
{
string treatment = dr[0].ToString();
}
或者使用using
语句更好。
using(SqlDataReader dr = cmd1.ExecuteReader())
{
while(dr.Read())
{
string treatment = dr[0].ToString();
}
}
但是如果你的SqlCommand
只返回1
列,你可以使用ExecuteScalar()
方法。返回第一行的第一列,如下所示:-
cmd.CommandText = "SELECT treatment FROM appointment WHERE patientid = " + text;
string str = Convert.ToString(cmd.ExecuteScalar());
你也可以打开你的代码SQL注入。始终使用参数化查询。Jeff有一篇很酷的博客文章叫《要么给我参数化SQL,要么给我死亡》。请仔细阅读。另请阅读DotNetPerl SqlParameter文章。
Execute Scalar: Getting Single Value from Database方法从数据库中检索单个值(例如,聚合值)。
cmd1.Connection = GetConnection();
cmd1.CommandText = "SELECT treatment FROM appointment WHERE patientid = " + text;
if(cmd.ExecuteScalar()==null)
{
var treatment = cmd.ExecuteScalar();
}
其他方式:ExecuteReader()
try
{
cmd1.CommandText ="SELECT treatment FROM appointment WHERE patientid=@patientID";
cmd1.Parameters.AddWithValue("@patientID", this.DropDownList1.SelectedValue);
conn.Open();
SqlDataReader dr = cmd1.ExecuteReader();
while (dr.Read())
{
int PatientID = int.Parse(dr["treatment"]);
}
reader.Close();
((IDisposable)reader).Dispose();//always good idea to do proper cleanup
}
catch (Exception exc)
{
Response.Write(exc.ToString());
}
答案:
String res = cmd1.ExecuteScalar();
注释:使用参数化查询来防止SQL注入
你的示例代码有很多错误。
- 你有内联sql,这打开了sql注入的主要方式。
-
你正在使用ExecuteNonQuery(),这意味着你没有得到任何数据。
string sSQL = "SELECT treatment FROM appointment WHERE patientid = @patientId"; OleDbCommand cmd1 = new OleDbCommand(sSQL, GetConnection()); // This may be slight different based on what `GetConnectionReturns`, just put the connection string in the second parameter. cmd1.Parameters.AddWithValue("@patientId", text); SqlDataReader reader = cmd1.ExecuteReader(); string returnValue; while(reader.Read()) { returnValue = reader[0].ToString(); }
您只需要使用命令的ExecuteScalar方法—这将为您提供结果集的第一行和第一列的值。
OleDbCommand cmd1 = new OleDbCommand();
cmd1.Connection = GetConnection();
cmd1.CommandText = "SELECT treatment FROM appointment WHERE patientid = " + text;
var result = cmd1.ExecuteScalar();
如果您的SQL语句返回多个行/列,那么您可以使用ExecuteReader()
您需要使用OleDbAdapter。
string connection = "your connection";
string query = "SELECT treatment FROM appointment WHERE patientid = " + text;
OleDbConnection conn = new OleDbConnection(connection);
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand(query, conn);
adapter.Fill(dataset);
SqlConnection dbConnect = new SqlConnection("your SQL connection string");
string name = " 'ProjectName' ";
string strPrj = "Select e.type, (e.surname +' '+ e.name) as fulln from dbo.tblEmployees e where id_prj = " + name;
SqlCommand sqlcmd = new SqlCommand(strPrj, dbConnect);
SqlDataAdapter sda = new SqlDataAdapter(strPrj, dbConnect);
ds = new DataSet();
sda.Fill(ds);
dbConnect.Open();
sqlcmd.ExecuteNonQuery();
dbConnect.Close();