问题描述

如何插入 Firebird，主键自动递增?

How do INSERT INTO Firebird, with autoincrement for the primary key?

对于我拥有的表格字段:

For the table fields I have:

fstPriority VARCHAR(30), fstInfo VARCHAR(100), fstDateCreated VARCHAR(30), fstDateModified VARCHAR(30), fiKeyID INTEGER PRIMARY KEY

对于 INSERT INTO 我有:

For the INSERT INTO I have:

FbConnection fbConn = new FbConnection(stOpenConn))
fbConn.Open();
...
FbTransaction fbTransaction = fbConn.BeginTransaction();
FbCommand fbCmd = new FbCommand("INSERT INTO " + stTableName + "(" + stFieldNames + ") VALUES ( @p0, @p1, @p2, @p3, @p4 ) RETURNING fiKeyID ", fbConn, fbTransaction);

但不确定应该使用什么fbCmd.Parameters.AddWithValue

but am uncertain what should be used for the fbCmd.Parameters.AddWithValue

fbCmd.Parameters.AddWithValue("@p0", "1st value");
fbCmd.Parameters.AddWithValue("@p1", "2nd value");
fbCmd.Parameters.AddWithValue("@p2", "3rd value");
fbCmd.Parameters.AddWithValue("@p3", "4th value");

然后呢?对于fiKeyID，我是否添加

Then what? For fiKeyID, do I add

fbCmd.Parameters.AddWithValue("@p4", "");

另外，我在 http://www.firebirdfaq.org/faq29/ 看到创建一个自动增量列，但我不确定如何在 C# ... Firebird ADO.NET ... FirebirdClient.5.8.0 ... Visual Studio 2013 中执行此操作.

Also, I see at http://www.firebirdfaq.org/faq29/ creating an autoincrement column, but am uncertain how to do this in C# ... Firebird ADO.NET ... FirebirdClient.5.8.0 ... Visual Studio 2013.

CREATE GENERATOR ...;
SET GENERATOR ...;

set term !! ;
CREATE TRIGGER ...

Visual Studio 编译器无法识别.

are not recognized by the Visual Studio compiler.

推荐答案

重要的是 SET TERM 不是 Firebird 语句语法的一部分，而是在 ISQL 等查询工具中设置语句终止符的客户端功能.该终止符对于知道语句何时完成并可以发送到服务器是必要的.默认情况下，这些工具在分号 (;) 上执行此操作，但这不适用于 PSQL(存储过程、触发器)，因为 PSQL 代码也使用分号.为了解决这个问题，这些工具有 SET TERM 来切换这个终止符.

An important thing is that SET TERM is not part of the Firebird statement syntax, instead it is a client-side feature to set the statement terminator in query tools like ISQL. This terminator is necessary to know when a statement is complete and can be sent to the server. By default these tools do that on a semi-colon (;), but that doesn't work with PSQL (stored procedures, triggers), because PSQL code uses the semi-colon as well. To address this, these tools have SET TERM to switch this terminator.

但是，使用 Firebird ADO.net 提供程序时，您需要一次执行一条语句，因此语句终止符无关紧要.

Using the Firebird ADO.net provider however, you need to execute statements one at a time, so a statement terminator is irrelevant.

为了能够生成主键，您可以使用以下解决方案:

To be able to generate a primary key you can use the following solutions:

1. Firebird 3 有一个标识类型列，因此您无需创建序列并自行触发:

1. Firebird 3 has an identity type column, so you don't need to create a sequence and trigger yourself:

create table withgeneratedid(
    id integer generated by default as identity primary key, 
    column2 varchar(100)
)

create table withgeneratedid(
    id integer primary key,
    column2 varchar(100)
);

create sequence seq_withgeneratedid;

set term #;
create trigger withgeneratedid_bi before insert on withgeneratedid
as
begin
    if (new.id is null) then new.id = next value for seq_withgeneratedid;
end#
set term ;#

当您向表中插入值并希望生成键时，您应该不在列列表中包含 id 列.包含 id 列允许您覆盖键值，但这可能会导致将来的插入生成重复键！.如果确实包含 id 列，则在 Firebird 3 示例中不会生成键，在 Firebird 2.5 示例中，如果该列的值为 null，则将生成键，否则将采用提供的值.

When you insert values into a table and want to have a generated key, you should not include the id column in the column-list. Including the id column allows you to override the key value, but that might lead to future inserts generating a duplicate key!. If you do include the id column, then no key will be generated in the Firebird 3 example, in the Firebird 2.5 example a key will be generated if the value of the column is null, otherwise it will take the provided value.

在 ADO.net 中，您通常需要单独执行语句(而不是使用 set term).或者，您可以使用 FbScript 解析 DDL 脚本并执行解析语句.请注意，FbScript 确实支持(甚至需要)set term.

In ADO.net you'd normally need to execute the statements individually (and not use set term). Alternatively, you could use FbScript to parse a DDL script and execute the parse statements. Note that FbScript does support (and even requires) set term.

要使用 Firebird ADO.net 提供程序执行此操作，您可以执行以下示例中的操作.我提供了三种用于创建表 Firebird3、Firebird2_5 和 FbScriptFB2_5(与 Firebird2_5 相同)的替代方法但使用 FbScript).它还展示了如何检索生成的密钥:

To execute this with the Firebird ADO.net provider, you can do something like the example below. I have included three alternatives for creating the table Firebird3, Firebird2_5, and FbScriptFB2_5 (which is the same as Firebird2_5 but uses FbScript). It also show how to retrieve the generated key:

namespace FbGeneratedKeys
{
    class Program
    {
        private static SolutionType solutionType = SolutionType.FbScriptFB2_5;

        static void Main(string[] args)
        {
            var connectionString = new FbConnectionStringBuilder
            {
                Database = @"D:	empgeneratedkey.fdb",
                ServerType = FbServerType.Default,
                UserID = "SYSDBA",
                Password = "masterkey",
            }.ToString();
            FbConnection.CreateDatabase(connectionString, pageSize: 8192, overwrite : true);

            using (FbConnection connection = new FbConnection(connectionString))
            using (FbCommand cmd = new FbCommand())
            {
                connection.Open();

                cmd.Connection = connection;
                switch (solutionType) {
                    case SolutionType.Firebird3:
                        Firebird3Example(cmd);
                        break;
                    case SolutionType.Firebird2_5:
                        Firebird2_5Example(cmd);
                        break;
                    case SolutionType.FbScriptFB2_5:
                        FbScriptFB2_5Example(cmd);
                        break;
                }

                cmd.CommandText = @"insert into withgeneratedid(column2) values (@column2) returning id";
                cmd.Parameters.AddWithValue("@column2", "some value");
                cmd.Parameters.Add(new FbParameter() { Direction = System.Data.ParameterDirection.Output });
                cmd.ExecuteNonQuery();

                Console.WriteLine("Id:" + cmd.Parameters[1].Value);
                Console.ReadLine();
            }
        }

        private static void Firebird3Example(FbCommand cmd)
        {
            // Firebird 3 identity column
            cmd.CommandText = @"create table withgeneratedid(
    id integer generated by default as identity primary key, 
    column2 varchar(100)
)";
            cmd.ExecuteNonQuery();
        }

        private static void Firebird2_5Example(FbCommand cmd)
        {
            // Firebird 2.5 and earlier normal primary key with trigger to generate key
            // Table
            cmd.CommandText = @"create table withgeneratedid(
    id integer primary key,
    column2 varchar(100)
)";
            cmd.ExecuteNonQuery();

            // Sequence
            cmd.CommandText = "create sequence seq_withgeneratedid";
            cmd.ExecuteNonQuery();

            // Trigger
            cmd.CommandText = @"create trigger withgeneratedid_bi before insert on withgeneratedid
as
begin
    if (new.id is null) then new.id = next value for seq_withgeneratedid;
end";
            cmd.ExecuteNonQuery();
        }

        private static void FbScriptFB2_5Example(FbCommand cmd)
        {
            string script = @"
create table withgeneratedid(
    id integer primary key,
    column2 varchar(100)
);

create sequence seq_withgeneratedid;

set term #;
create trigger withgeneratedid_bi before insert on withgeneratedid
as
begin
    if (new.id is null) then new.id = next value for seq_withgeneratedid;
end#
set term ;#
";
            FbScript fbScript = new FbScript(script);
            fbScript.Parse();
            FbBatchExecution exec = new FbBatchExecution(cmd.Connection);
            exec.AppendSqlStatements(fbScript);
            exec.Execute();
        }
    }

    enum SolutionType
    {
        Firebird3,
        Firebird2_5,
        FbScriptFB2_5
    }
}

这篇关于如何插入到 Firebird，主键自动递增?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！