检索SQL语句的输出参数
I'm using a table with two columns, customer_id
and customer_name
.
我正在使用一个包含两列的表,customer_id和customer_name。
customer_name
is a simple varchar
, customer_id
is an auto incrementing primary key.
customer_name是一个简单的varchar,customer_id是一个自动递增的主键。
I want to use my C# application to insert a customer_name
, and retrieve the value of the customer_id
.
我想使用我的C#应用程序插入customer_name,并检索customer_id的值。
Just inserting is simply solved by using
只需插入就可以通过使用来解决
using (SqlConnection connection = new SqlConnection(AppConstants.ConnectionString))
{
using (SqlCommand command = new SqlCommand("INSERT INTO custom_customer (customer_name) VALUES (@name);"))
{
command.Parameters.Add(new SqlParameter("name", customer));
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
}
I found some documentation of the OUTPUT
statement, which can be used to retrieve a value.
我找到了一些OUTPUT语句的文档,可用于检索值。
I think the correct syntax would be
我认为正确的语法是
INSERT INTO custom_customer (customer_name)
OUTPUT Inserted.customer_id
VALUES (@name);
And I figure I need to use
我认为我需要使用
command.ExecuteReader();
But then I'm stuck. How should I go about getting the value from the query?
但后来我被困住了。我该如何从查询中获取值?
4 个解决方案
#1
3
First, use the ExecuteNonQuery
to write operations. After execute command, you can read the parameter from Parameters
collection, since you have set the parameter as a output parameter, for sample:
首先,使用ExecuteNonQuery编写操作。执行命令后,您可以从Parameters集合中读取参数,因为您已将参数设置为输出参数,对于示例:
command.Parameters["name"].Direction = System.Data.ParameterDirection.Output;
command.ExecuteNonQuery();
object name = command.Parameters["name"].Value;
If you want to know what Id
the identity column generated after the insert, you could use SCOPE_IDENTITY()
sql command, and use the ExecuteScalar()
to get the result of command, for sample:
如果您想知道插入后生成的标识列的Id,您可以使用SCOPE_IDENTITY()sql命令,并使用ExecuteScalar()获取命令的结果,对于示例:
int id;
using (SqlConnection connection = new SqlConnection(AppConstants.ConnectionString))
{
string sql = @"INSERT INTO custom_customer (customer_name)
VALUES (@name);
SELECT SCOPE_IDENTITY();"
using (SqlCommand command = new SqlCommand(sql))
{
command.Parameters.Add(new SqlParameter("name", customer));
connection.Open();
id = (int) command.ExecuteScalar();
connection.Close();
}
}
更多相关文章
- oracle:使用cmd命令在远程oracle服务器上执行sql语句
- sql server2008 日志收缩 命令行
- CentOS系统操作mysql的常用命令
- android-exploitme(四):参数篡改
- Android 自动编译、打包生成apk文件 1 - 命令行方式
- android 命令修改时间或程序修改系统时间
- 如何在函数中将两个参数从1个类传递给另一个?
- Android的startActivityForResult()与onActivityResult()与setRe
- 常用命令(Linux、Android、adb)