尝试使用OleDb创建外键并将其值插入.mdb数据库中 [英] Trying to create foreign keys and insert their values in a .mdb database using OleDb
问题描述
我有一个包含3个表的数据库.其中一个拥有其他两个的关系.例如:
I have a database with 3 tables. One of them holds a relation of the other two. E.g.:
Table1 = idTable1(PK_Table1)，attribute1，attribute2，attribute3
Table1 = idTable1 (PK_Table1), attribute1, attribute2, attribute3
Table2 = idTable2(PK_Table2)，attribute1
Table2 = idTable2 (PK_Table2), attribute1
Table3 = idTable3(PK_Table3)，attribute1(与idTable1相关的FK)，attribute2(与idTable2相关的FK)
Table3 = idTable3 (PK_Table3), attribute1 (FK relating to idTable1), attribute2 (FK relating to idTable2)
所有主键都是Access自动分配的自动递增字段(2002版，这就是我的数据库是.mdb的原因).
All primary keys are autoincrementing fields assigned automatically by Access (2002 version, that's why my db is a .mdb).
在我的代码中，我使用如下代码在Table1和Table2中插入数据:
In my code I insert data in Table1 and Table2 using some code like this:
public void insert()
{
string query = "INSERT INTO Table1 (attribute1,attribute2,attribute3) VALUES (?,?,?)";
OleDbConnection dbConnection = new OleDbConnection();
dbConnection.ConnectionString = connStr;
试试 { dbConnection.Open(); OleDbCommand commandStatement =新的OleDbCommand(); OleDbCommand primarykey ＝ new OleDbCommand("ALTER TABLE Table1 ADD CONSTRAINT pk_Table1 primary key(idTable1)"，dbConnection)； primarykey.Connection = dbConnection; commandStatement.Connection = dbConnection; commandStatement.CommandText =查询； commandStatement.Parameters.Add("attribute1"，OleDbType.Integer).Value ＝ attribute1；
try { dbConnection.Open(); OleDbCommand commandStatement = new OleDbCommand(); OleDbCommand primarykey = new OleDbCommand("ALTER TABLE Table1 ADD CONSTRAINT pk_Table1 primary key(idTable1)", dbConnection); primarykey.Connection = dbConnection; commandStatement.Connection = dbConnection; commandStatement.CommandText = query; commandStatement.Parameters.Add("attribute1", OleDbType.Integer).Value = attribute1;
commandStatement.Parameters.Add("attribute2"，OleDbType.Integer).Value ＝ attribute2；
commandStatement.Parameters.Add("attribute3"，OleDbType.Integer).Value ＝ attribute3；
commandStatement.ExecuteNonQuery();
primarykey.ExecuteNonQuery();
dbConnection.Close();
dbConnection.Dispose();
commandStatement.Dispose();
primarykey.Dispose();
}
捕获(异常e)
{
Console.WriteLine(e.Message);
}
}
commandStatement.Parameters.Add("attribute2", OleDbType.Integer).Value = attribute2;
commandStatement.Parameters.Add("attribute3", OleDbType.Integer).Value = attribute3;
commandStatement.ExecuteNonQuery();
primarykey.ExecuteNonQuery();
dbConnection.Close();
dbConnection.Dispose();
commandStatement.Dispose();
primarykey.Dispose();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
(然后也适用于Table2).
(and then something like that for Table2 as well).
对于表1中的每一行，我在表2中插入约40行(它们是包含比赛及其参赛者信息的表).
For each row in Table1, I insert about 40 rows in Table2 (they're tables holding info of a contest and their contestants).
现在，我需要使用Table3在这两个表之间创建一个关系，该表必须将两个表的ID都作为外键引用.
Now I need to create a relation between those two, using Table3, which must reference the id of both tables as foreign keys.
那是我迷路的地方.我不知道怎么说将您刚刚插入到Table1中的行的ID，然后将您刚刚插入到Table2中的行的ID，然后将它们作为新记录插入到Table3中".
And that's where I'm lost. I don't know how to say "take the id of the row you just inserted in Table1 and then the id of a row you just inserted in Table2 and insert them as a new record in Table3".
插入记录后，有没有办法获取数据库分配的自动增量ID?
Is there a way to get the autoincrementing IDs that are being assigned by the database, as soon as I insert a record?
谢谢！
推荐答案
获取插入到SQL表中的最后一行的ID(在当前作用域中) )，则可以使用SCOPE_IDENTITY().这必须作为发送到数据库的语句批处理的一部分来完成，因为它的作用域是当前批处理.如果你是 一次更新多个表我个人建议您使用一个存储过程通过事务将其插入两个表中.如果第二次插入失败，则可能不希望第一个插入成功.您可以使用一组批处理来执行此操作 语句，但是您的代码变得杂乱无章，可以通过SQL更好地进行处理.
To get the ID of the last row that was inserted into a SQL table (in the current scope) you would use SCOPE_IDENTITY(). This has to be done as part of the batch of statements you send to the DB because it is scoped to the current batch. If you're updating multiple tables at once I would personally recommend that you use a sproc to insert into both tables with a transaction. If the second insert fails you probably don't want the first insert to succeed. You can do this using a set of batch statements but your code becomes cluttered with stuff that can be better handled via SQL.
开始翻译
插入第一张表
id = SCOPE_IDENTITY()
插入第二张表
提交
Begin trans
insert into first table
id = SCOPE_IDENTITY()
insert into second table
commit
迈克尔·泰勒
http://blogs.msmvps.com/p3net
Michael Taylor
http://blogs.msmvps.com/p3net
这篇关于尝试使用OleDb创建外键并将其值插入.mdb数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
