如何将主键自动递增并插入Firebird？ [英] How do INSERT INTO Firebird, with autoincrement for the primary key?
问题描述
如何在主键自动递增的情况下插入Firebird？
How do INSERT INTO Firebird, with autoincrement for the primary key?
对于表字段，我有：
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:
-
Firebird 3具有一个标识类型列，因此您无需创建序列并触发自己：
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)
)
对于Firebird 2.5及更早版本，您需要创建序列和触发器：
For Firebird 2.5 and earlier you will need to create a sequence and trigger:
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 确实支持（甚至要求）设置项。
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:\temp\generatedkey.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？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
