插入行并在C#中输出标识值的SQL语句 [英] SQL statement to insert a row and output the identity value in C#

问题描述

我具有需要将图像插入sql表并获取插入行的标识密钥的功能.这是我要插入的C#代码块:

Hi,

I have functionality where I need to insert an image into sql table and get the identity key of the row inserted. Here is the C# code block where I do my insert:

using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connstring"].ConnectionString))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("INSERT INTO ImageTable(photo,imagetype,imageName) OUTPUT inserted.photoid VALUES(@photo,@type,@Imgname)", conn))
    {
        cmd.Parameters.Add("@photo", System.Data.SqlDbType.Image).Value = myData;
        cmd.Parameters.Add("@type", System.Data.SqlDbType.VarChar, 20).Value = type;
        cmd.Parameters.Add("@Imgname", System.Data.SqlDbType.VarChar, 253).Value = imageName;
        imageId = (int)cmd.ExecuteScalar();
    }
    conn.Close();
}

在执行此语句时，出现错误:
如果DML语句的目标表"ImageTable"包含不带INTO子句的OUTPUT子句，则该语句不能具有任何已启用的触发器.

我在数据库中的那个表上有一个触发器.
任何人都可以告诉我如何解决此问题.
提前谢谢.
Scorpion

While I am executing this statement I am getting an error:
The target table ''ImageTable'' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

I have a trigger on that table in DB.
Can any one tell how I can solve this issue.
Thanks in advance.
Scorpion

推荐答案

根据Marcus的建议，我还将研究@@IDENTITY关键字

进一步阅读 @@ IDENTITY关键字 [
SCOPE_IDENTITY()函数通过与函数调用本身相同作用域的语句返回最后插入的行的标识.

With what Marcus has suggested I would also look at the @@IDENTITY keyword

further reading @@IDENTITY keyword[^]

--

Actually, the SCOPE_IDENTITY() function would be rather more suitable. If the trigger that blocks the first approach (though I''ve never seen that approach before!) does any inserts in tables with identity columns, @@IDENTITY will reflect the identity of the "globally last inserted" row - NOT the row you care about.

The SCOPE_IDENTITY() function returns the identity of the last inserted row by a statement in the same scope as the function call itself.

您在此处有几个选项.
1)您可以编写一个存储过程，该存储过程使用OUTPUT参数执行插入.成功插入该值后，可以将OUTPUT参数设置为SCOPE_IDENTITY().
2)您可以删除查询的OUTPUT部分，然后在插入成功检索到ID后运行第二个查询. SELECT Max(ID) FROM ImageTable WHERE imageType=''your value'' AND imageName=''your value''

You have a couple of options here.
1) You could write a stored procedure that performs the insert with an OUTPUT parameter. When you have successfully inserted the value you can set the OUTPUT parameter to SCOPE_IDENTITY().
2) You could remove the OUTPUT part of the query and then run a second query after the insert succeeds to retrieve the ID. SELECT Max(ID) FROM ImageTable WHERE imageType=''your value'' AND imageName=''your value''

这是可能的，但是现在您需要使用ExecuteScalar来获取该值.如果性能根本不重要，请改用输出参数.

遵循以下原则:

That is possible, but you now need to use ExecuteScalar to get hold of the value. If performance matters at all, use an output parameter instead.

Along these lines:

cmd.CommandText = "declare @id int OUTPUT; insert T vaules(1, 'abc', 0); set @id = SCOPE_IDENTITY()";

var idParam = cmd.Parameters.AddWithValue("@id", 0); 
idParam.Direction = ParameterDirection.Output;

-

还可以考虑使用存储的proc；在许多情况下建议这样做.

--

Also consider using stored procs; it is adviseable in many cases.

这篇关于插入行并在C#中输出标识值的SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！