可以使用 SqlCommandBuilder(不使用存储过程)在插入时检索 IDENTITY 列值吗?

Possible to retrieve IDENTITY column value on insert using SqlCommandBuilder (without using Stored Proc)?(可以使用 SqlCommandBuilder(不使用存储过程)在插入时检索 IDENTITY 列值吗?)

问题描述

仅供参考:我在 dotnet 3.5 SP1 上运行

FYI: I am running on dotnet 3.5 SP1

我试图在执行更新后(使用 SqlDataAdapter 和 SqlCommandBuilder)将标识列的值检索到我的数据集中.执行 SqlDataAdapter.Update(myDataset) 后，我希望能够读取 myDataset.tables(0).Rows(0)("ID") 的自动分配值，但它是 System.DBNull(尽管插入了行).

I am trying to retrieve the value of an identity column into my dataset after performing an update (using a SqlDataAdapter and SqlCommandBuilder). After performing SqlDataAdapter.Update(myDataset), I want to be able to read the auto-assigned value of myDataset.tables(0).Rows(0)("ID"), but it is System.DBNull (despite the fact that the row was inserted).

(注意:我不想显式地写一个新的存储过程来做这个！)

(Note: I do not want to explicitly write a new stored procedure to do this!)

一种经常发布的方法http://forums.asp.net/t/951025.aspx 像这样修改 SqlDataAdapter.InsertCommand 和 UpdatedRowSource:

One method often posted http://forums.asp.net/t/951025.aspx modifies the SqlDataAdapter.InsertCommand and UpdatedRowSource like so:

SqlDataAdapter.InsertCommand.CommandText += "; SELECT MyTableID = SCOPE_IDENTITY()"
InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord

显然，这在过去似乎对许多人有用，但对我不起作用.

Apparently, this seemed to work for many people in the past, but does not work for me.

另一种技术:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=619031&SiteID=1 对我也不起作用，因为在执行 SqlDataAdapter.Update 后，SqlDataAdapter.InsertCommand.Parameters 集合被重置为原始集合(丢失额外的添加参数).

Another technique: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=619031&SiteID=1 doesn't work for me either, as after executing the SqlDataAdapter.Update, the SqlDataAdapter.InsertCommand.Parameters collection is reset to the original (losing the additional added parameter).

有谁知道这个问题的答案???

Does anyone know the answer to this???

推荐答案

这是我之前遇到的一个问题，bug好像是你调用的时候da.更新(ds)；插入命令的参数数组被重置为从您的命令生成器创建的初始列表，它会删除您为身份添加的输出参数.

This is a problem that I've run into before, the bug seems to be that when you call da.Update(ds); the parameters array of the insert command gets reset to the inital list that was created form your command builder, it removes your added output parameters for the identity.

解决方案是创建一个新的 dataAdapter 并复制命令，然后使用这个新的来执行您的 da.update(ds);

The solution is to create a new dataAdapter and copy in the commands, then use this new one to do your da.update(ds);

喜欢

SqlDataAdapter da = new SqlDataAdapter("select Top 0 " + GetTableSelectList(dt) + 
"FROM " + tableName,_sqlConnectString);
SqlCommandBuilder custCB = new SqlCommandBuilder(da);
custCB.QuotePrefix = "[";
custCB.QuoteSuffix = "]";
da.TableMappings.Add("Table", dt.TableName);

da.UpdateCommand = custCB.GetUpdateCommand();
da.InsertCommand = custCB.GetInsertCommand();
da.DeleteCommand = custCB.GetDeleteCommand();

da.InsertCommand.CommandText = String.Concat(da.InsertCommand.CommandText, 
"; SELECT ",GetTableSelectList(dt)," From ", tableName, 
" where ",pKeyName,"=SCOPE_IDENTITY()");

SqlParameter identParam = new SqlParameter("@Identity", SqlDbType.BigInt, 0, pKeyName);
identParam.Direction = ParameterDirection.Output;
da.InsertCommand.Parameters.Add(identParam);

da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

//new adaptor for performing the update                     
SqlDataAdapter daAutoNum = new SqlDataAdapter();
daAutoNum.DeleteCommand = da.DeleteCommand;
daAutoNum.InsertCommand = da.InsertCommand;
daAutoNum.UpdateCommand = da.UpdateCommand;

daAutoNum.Update(dt);

这篇关于可以使用 SqlCommandBuilder(不使用存储过程)在插入时检索 IDENTITY 列值吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！