Как сделать INSERT INTO Firebird, с автоинкрементом для первичного ключа?
Как сделать INSERT INTO Firebird, с автоинкрементом для первичного ключа?
Для полей таблицы у меня есть:
fstPriority VARCHAR(30), fstInfo VARCHAR(100), fstDateCreated VARCHAR(30), fstDateModified VARCHAR(30), fiKeyID INTEGER PRIMARY KEY
Для INSERT INTO у меня есть:
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
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, я должен добавить
fbCmd.Parameters.AddWithValue("@p4", "");
Кроме того, я вижу на http://www.firebirdfaq.org/faq29/ создание столбца автоинкремента, но не знаю, как это сделать в C# ... Firebird ADO.NET ... FirebirdClient.5.8.0 ... Visual Студия 2013.
CREATE GENERATOR ...;
SET GENERATOR ...;
set term !! ;
CREATE TRIGGER ...
не распознаются компилятором Visual Studio.
2 ответа
Важно то, что SET TERM не является частью синтаксиса оператора Firebird, а является клиентской функцией для установки терминатора оператора в инструментах запросов, таких как ISQL. Этот терминатор необходим, чтобы знать, когда оператор завершен, и может быть отправлен на сервер. По умолчанию эти инструменты делают это с помощью точки с запятой (;), но это не работает с PSQL (хранимые процедуры, триггеры), потому что код PSQL также использует точку с запятой. Для решения этой проблемы эти инструменты имеют SET TERM переключить этот терминатор.
Однако, используя поставщика Firebird ADO.net, вы должны выполнять операторы по одному, поэтому терминатор операторов не имеет значения.
Чтобы иметь возможность генерировать первичный ключ, вы можете использовать следующие решения:
В Firebird 3 есть столбец идентификаторов, поэтому вам не нужно создавать последовательность и запускать ее самостоятельно:
create table withgeneratedid( id integer generated by default as identity primary key, column2 varchar(100) )Для Firebird 2.5 и более ранних версий вам нужно создать последовательность и триггер:
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в противном случае он примет предоставленное значение.
В ADO.net вам обычно нужно выполнять инструкции по отдельности (а не использовать set term). В качестве альтернативы, вы можете использовать FbScript проанализировать сценарий DDL и выполнить операторы синтаксического анализа. Обратите внимание, что FbScript поддерживает (и даже требует) set term,
Чтобы выполнить это с поставщиком Firebird ADO.net, вы можете сделать что-то вроде примера ниже. Я включил три варианта для создания таблицы Firebird3, Firebird2_5, а также FbScriptFB2_5 (что так же, как Firebird2_5 но использует FbScript). Также показано, как получить сгенерированный ключ:
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
}
}
Определения:
public const string stMAIN_TABLE_NAME = " OrgTable ";
public const string stDELETED_TABLE_NAME = " BackupTable ";
public const string stFIELD_DEFINITIONS = " fstPriority VARCHAR(30)" +
", fstInfo VARCHAR(100)" +
", fstDateCreated VARCHAR(30)" +
", fstDateModified VARCHAR(30)" +
", fiKeyID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ";
public const string stFIELD_NAMES = " fstPriority" +
", fstInfo" +
", fstDateCreated" +
", fstDateModified" +
", fiKeyID ";
public const string stFIELD_NAMES_NO_KEY_ID = " fstPriority" +
", fstInfo" +
", fstDateCreated" +
", fstDateModified ";
Код:
//------------------------------
static private bool boCreateDatabaseTables(string stPathFilename,
string stUserID,
string stPassword,
List<string> liststTableNames,
List<string> liststFieldDefinitions)
{
bool boErrorFlag = false;
int iTablesCount = liststTableNames.Count();
string stOpenConn = new FbConnectionStringBuilder {
Database = stPathFilename,
UserID = stUserID,
Password = stPassword,
ServerType = FbServerType.Embedded,
ClientLibrary = stCLIENT_LIBRARY
}.ToString();
using (FbConnection fbConn = new FbConnection(stOpenConn)) {
try {
fbConn.Open();
FbTransaction fbTransaction = fbConn.BeginTransaction();
for (int ii = 0; ii < iTablesCount; ii++) {
string stSql = "CREATE TABLE " + liststTableNames[ii] + "( " + liststFieldDefinitions[ii] + ")";
FbCommand fbCmd = new FbCommand(stSql, fbConn, fbTransaction);
fbCmd.ExecuteNonQuery();
}
fbTransaction.Commit();
}
catch (Exception ex) {
boErrorFlag = true;
MessageBox.Show("catch ... GlobalsFirebird ... boCreateDatabaseTables ... " + ex.Message);
}
}
return boErrorFlag;
}//boCreateDatabaseTables
//------------------------------
//------------------------------
static public bool boAddRow(string stPathFilename,
string stUserID,
string stPassword,
string stTableName,
string stFieldNamesNoKeyId,
List<string> liststFieldValuesNoKeyId)
{
bool boErrorFlag = false;
string stOpenConn = new FbConnectionStringBuilder {
Database = stPathFilename,
UserID = stUserID,
Password = stPassword,
ServerType = FbServerType.Embedded,
ClientLibrary = stCLIENT_LIBRARY
}.ToString();
using(FbConnection fbConn = new FbConnection(stOpenConn)) {
fbConn.Open();
try {
string stValuesPlaceHolder = "@p0";
for (int iii = 1; iii < liststFieldValuesNoKeyId.Count; iii++)
stValuesPlaceHolder += ", @p" + (iii).ToString();
FbTransaction fbTransaction = fbConn.BeginTransaction();
string stCmd = "INSERT INTO " + stTableName + "(" + stFieldNamesNoKeyId + ") VALUES ( " + stValuesPlaceHolder + " ) RETURNING fiKeyID ";
FbCommand fbCmd = new FbCommand(stCmd, fbConn, fbTransaction);
for (int iii = 0; iii < liststFieldValuesNoKeyId.Count; iii++) {
string stPlaceHolder = "@p" + (iii).ToString();
string stValue = liststFieldValuesNoKeyId[iii];
fbCmd.Parameters.AddWithValue(stPlaceHolder, stValue);
}
fbCmd.Parameters.Add(new FbParameter() { Direction = System.Data.ParameterDirection.Output });
fbCmd.ExecuteNonQuery();
fbTransaction.Commit();
}
catch (Exception ex) {
boErrorFlag = true;
MessageBox.Show("catch ... GlobalsFirebird ... boAddRow ... " + ex.Message);
}
}
return boErrorFlag;
}//boAddRow
//------------------------------