下面以.NET Framework4.6平台为例,介绍ExecuteNonQuery(),ExecuteReader(),以及ExecuteScalar()的常规用法。
官方参考网址如下:https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executenonquery?view=netframework-4.6
以上三个方法所调用的库是System.Data.SqlClient:
(1)ExecuteNonQuery():返回值是Int32,用于返回数据库中受影响的行数。可用于更新,插入,删除数据库中的语句(UPDATE, INSERT, or DELETE)。
(注意:UPDATE, INSERT, or DELETE可以返回受影响的行数,但其他的指令CREAT ,DROP等其它指令会返回-1)
private static void CreateCommand(string queryString,
string connectionString)
{
using (SqlConnection connection = new SqlConnection(
connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
command.Connection.Open();
command.ExecuteNonQuery();
}
}
(2)ExecuteReader():返回值是SqlDataReader。主要用于SELETE语句。
private static void CreateCommand(string queryString,
string connectionString)
{
using (SqlConnection connection = new SqlConnection(
connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(queryString, connection);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(String.Format("{0}", reader[0]));
}
}
}
(3)ExecuteScalar():返回值是Object。如果插入新行,函数将返回新的标识列值(new Identity column value),0为失败。
static public int AddProductCategory(string newName, string connString)
{
Int32 newProdID = 0;
string sql =
"INSERT INTO Production.ProductCategory (Name) VALUES (@Name); "
+ "SELECT CAST(scope_identity() AS int)";
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@Name", SqlDbType.VarChar);
cmd.Parameters["@name"].Value = newName;
try
{
conn.Open();
newProdID = (Int32)cmd.ExecuteScalar();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
return (int)newProdID;
}
网友评论