代码之家 › 专栏 › 技术社区 › Cruachan

ADO.NET中插入时不层叠的主详细信息关系

ironpython ado.net

Cruachan · 技术社区 · 14 年前

具体代码-首先我添加我的关系如下:

parentColumn = self._dataSetBookings.Tables["booking"].Columns["intBookingID"]
childColumn = self._dataSetBookings.Tables["lines"].Columns["intBookingID"]
bookingRelationship = DataRelation("mediabooking",parentColumn,childColumn,True)
self._dataSetBookings.Relations.Add(bookingRelationship)
self._dataSetBookings.EnforceConstraints = True

我的应用更新过程如下所示,请注意行上添加的事件已更新。使用的sql命令实际上是'select*from where intBookingID=@intBookingID)

# update booking
adapter = SqlDataAdapter()
adapter.RowUpdated += self.AdapterUpdateBooking
cmd = adapter.SelectCommand = self.conn.CreateCommand()

cmd.CommandText = self.bookingSqlCmd
parameter = cmd.Parameters.Add("@intBookingID", SqlDbType.Int)
parameter.Value = self.BookingID 

builder = System.Data.SqlClient.SqlCommandBuilder(adapter)
adapter.InsertCommand = builder.GetInsertCommand()
adapter.UpdateCommand = builder.GetUpdateCommand()
adapter.Update(self._dataSetBookings,"booking")

# update lines
adapter = SqlDataAdapter()
cmd = adapter.SelectCommand = self.conn.CreateCommand()             
cmd.CommandText = self.lineSqlCmd
parameter = cmd.Parameters.Add("@intLineBookingID", SqlDbType.Int)
parameter.Value = self.BookingID 

builder = System.Data.SqlClient.SqlCommandBuilder(adapter)
adapter.InsertCommand = builder.GetInsertCommand()
adapter.UpdateCommand = builder.GetUpdateCommand()
adapter.UpdateCommand = builder.GetDeleteCommand()      
adapter.Update(self._dataSetBookings,"lines")

    def AdapterUpdateBooking(self, sender, e):
    cmd = self.conn.CreateCommand()
    #cmd.CommandText = "SELECT SCOPE_IDENTITY()" - this does not work!
    cmd.CommandText = "SELECT @@IDENTITY "
    newBookingID = cmd.ExecuteScalar()

    if newBookingID != System.DBNull:
        for bookingRow in self._dataSetBookings.Tables["booking"].Rows:
            bookingRow["intBookingID"] = newBookingID

当应用更新时,通过跟踪这个过程,“SELECT@@IDENTITY”调用将为新记录检索正确的ID值(但是SCOPE\u IDENTITY返回null,我不理解它,这可能是一个线索?)。但是,当该值应用于主(预订)表中的intBookingID字段时,它不会级联更新明细(行)表中的intBookingID字段,因此插入失败。

但是,如果主(预订)记录是从数据库加载的,并且添加了新行,则关系确实有效,intBookingID字段设置正确,插入成功。

1 回复 | 直到 14 年前

A G 14 年前

你已经为级联编码了吗?我看不出来。试试这个。

ForeignKeyConstraint oFKey;
oFKey = new ForeignKeyConstraint("BookingForeignkey", parentColumn, childColumn);
oFKey.DeleteRule = Rule.Cascade;
oFKey.UpdateRule = Rule.Cascade;
oFKey.AcceptRejectRule = AcceptRejectRule.Cascade;
self._dataSetBookings.Tables["booking"].Constraints.Add(oFKey);
self._dataSetBookings.EnforceConstraints = True

DataTable Constraints (ADO.NET)

编辑:我不明白这个部分

if newBookingID != System.DBNull:
        for bookingRow in self._dataSetBookings.Tables["booking"].Rows:
            bookingRow["intBookingID"] = newBookingID

为什么要分配所有行“newBookingID”?由此我猜主键可能没有为数据集定义。如果是的话,赋值行肯定抛出了一些错误。