父子表结构已建立主外键,运行插入存储过程后返回最大ID号正确,但是数据库中确早不记录,而且数据库中的表的ID确实自动增长了!!!
CREATE PROCEDURE Insert_daya_info (
@daya_id int OUTPUT,
@daya_amountfamily int,
@daya_amountarea numeric,
@daya_call int,
@daya_man int,
@daya_salefamily int,
@daya_salemoney numeric,
@daya_re nvarchar(2000),
@daya_datetime datetime,
@daya_fistvisit int,
@daya_secondvisit int
)
AS
INSERT INTO [daya_info]
(
[daya_amountfamily],
[daya_amountarea],
[daya_call],
[daya_man],
[daya_salefamily],
[daya_salemoney],
[daya_re],
[daya_datetime],
[daya_fistvisit],
[daya_secondvisit]
)
VALUES
(
@daya_amountfamily,
@daya_amountarea,
@daya_call,
@daya_man,
@daya_salefamily,
@daya_salemoney,
@daya_re,
@daya_datetime,
@daya_fistvisit,
@daya_secondvisit
)
SELECT CAST(SCOPE_IDENTITY() AS INTEGER)
GO
public virtual daya_infoTable Insert(DataSet ds, SqlTransaction tx) { daya_infoTable addedTable = (daya_infoTable)ds.Tables["daya_info"].GetChanges(DataRowState.Added); if (addedTable != null && addedTable.Rows.Count > 0) {
int tempID, newID;
foreach (daya_infoRow row in addedTable.Rows) {
try {
tempID = row.daya_id;
newID = (int)SqlHelper.ExecuteScalar(tx, CommandType.StoredProcedure, "Insert_daya_info", InsertParameters(row));
row.daya_id = newID;
//Update any child row's ForeignKeys with the new value
Helpers.DataSetNavigator.UpdateChildTableForeignKeys(ds, "daya_info", "daya_id", tempID, newID);
}
catch(Exception ex){
throw(ex);
}
}
addedTable.AcceptChanges();
}
return addedTable;
} public class DataSetNavigator { public static void UpdateChildTableForeignKeys(DataSet data, string parentTableName, string foreignKeyName, int tempFKID, int newFKID) {
//step through the relationships
foreach(DataRelation rel in data.Relations) {
if(rel.ParentTable.TableName == parentTableName) {
// update any child rows
foreach(DataRow dr in rel.ChildTable.Rows) {
if (int.Parse(dr[foreignKeyName].ToString()) == tempFKID) { //update to the new ID
dr[foreignKeyName] = newFKID;
}
}
}
}
}
}
CREATE PROCEDURE Insert_daya_info (
@daya_id int OUTPUT,
@daya_amountfamily int,
@daya_amountarea numeric,
@daya_call int,
@daya_man int,
@daya_salefamily int,
@daya_salemoney numeric,
@daya_re nvarchar(2000),
@daya_datetime datetime,
@daya_fistvisit int,
@daya_secondvisit int
)
AS
INSERT INTO [daya_info]
(
[daya_amountfamily],
[daya_amountarea],
[daya_call],
[daya_man],
[daya_salefamily],
[daya_salemoney],
[daya_re],
[daya_datetime],
[daya_fistvisit],
[daya_secondvisit]
)
VALUES
(
@daya_amountfamily,
@daya_amountarea,
@daya_call,
@daya_man,
@daya_salefamily,
@daya_salemoney,
@daya_re,
@daya_datetime,
@daya_fistvisit,
@daya_secondvisit
)
SELECT CAST(SCOPE_IDENTITY() AS INTEGER)
GO
public virtual daya_infoTable Insert(DataSet ds, SqlTransaction tx) { daya_infoTable addedTable = (daya_infoTable)ds.Tables["daya_info"].GetChanges(DataRowState.Added); if (addedTable != null && addedTable.Rows.Count > 0) {
int tempID, newID;
foreach (daya_infoRow row in addedTable.Rows) {
try {
tempID = row.daya_id;
newID = (int)SqlHelper.ExecuteScalar(tx, CommandType.StoredProcedure, "Insert_daya_info", InsertParameters(row));
row.daya_id = newID;
//Update any child row's ForeignKeys with the new value
Helpers.DataSetNavigator.UpdateChildTableForeignKeys(ds, "daya_info", "daya_id", tempID, newID);
}
catch(Exception ex){
throw(ex);
}
}
addedTable.AcceptChanges();
}
return addedTable;
} public class DataSetNavigator { public static void UpdateChildTableForeignKeys(DataSet data, string parentTableName, string foreignKeyName, int tempFKID, int newFKID) {
//step through the relationships
foreach(DataRelation rel in data.Relations) {
if(rel.ParentTable.TableName == parentTableName) {
// update any child rows
foreach(DataRow dr in rel.ChildTable.Rows) {
if (int.Parse(dr[foreignKeyName].ToString()) == tempFKID) { //update to the new ID
dr[foreignKeyName] = newFKID;
}
}
}
}
}
}
ForeignKeyConstraint FK_dayb_info_daya_info 要求在父表中存在子键值(24)。
父表ID是自增长的.