尝试使用C＃和OleDb创建外键并将其值插入.mdb数据库

I have a database with 3 tables. One of them holds a relation of the other two. E.g.:

我有一个包含3个表的数据库。其中一个持有另外两个的关系。例如。:

Table1 = idTable1 (PK_Table1), attribute1, attribute2, attribute3

Table2 = idTable2 (PK_Table2), attribute1

Table3 = idTable3 (PK_Table3), attribute1 (FK relating to idTable1), attribute2 (FK relating to idTable2)

All primary keys are auto-incrementing fields assigned automatically by Access (2002 version, that's why my db is a .mdb).

所有主键都是由Access自动分配的自动递增字段(2002版本,这就是我的db是.mdb的原因)。

In my code I insert data in Table1 and Table2 using some code like this:

在我的代码中,我使用如下代码在Table1和Table2中插入数据:

public void insert()
{
    string query = "INSERT INTO Table1 (attribute1,attribute2,attribute3) VALUES (?,?,?)";
    OleDbConnection dbConnection = new OleDbConnection();
    dbConnection.ConnectionString = connStr;
    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();
    }
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
    }
}

(and then something like that for Table2 as well).

(然后是类似于Table2的东西)。

For each row in Table1, I insert about 40 rows in Table2 (they're tables holding info of a contest and their contestants).

对于Table1中的每一行,我在Table2中插入大约40行(它们是包含比赛和参赛者信息的表格)。

Now I need to create a relation between those two, using Table3, which must reference the id of both tables as foreign keys.

现在我需要使用Table3创建这两者之间的关系,Table3必须将两个表的id作为外键引用。

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".

这就是我迷失的地方。我不知道怎么说“把你刚刚插入Table1中的行的id,然后是你刚插入Table2的行的id,并将它们作为Table3中的新记录插入”。

Is there a way to get the autoincrementing IDs that are being assigned by the database, as soon as I insert a record?

有插入记录后,有没有办法获取数据库分配的自动增量ID?

1 个解决方案

#1

After inserting a row you can use SELECT @@Identity to retrieve the autoincremented value. Store it, insert the other row, do the same select and then insert these values to the link table.

插入行后,您可以使用SELECT @@ Identity来检索自动增量值。存储它,插入另一行,执行相同的选择,然后将这些值插入链接表。

More information (for VB.NET, but easily understandable) from Microsoft at HOW TO: Retrieve the Identity Value While Inserting Records into Access Database

Microsoft提供的更多信息(适用于VB.NET,但易于理解)如何:在将记录插入Access数据库时检索标识值

#1