DDL Firebird C# (edit)

• Determine Firebird Version
• How to detect the server version
• Create Database from scratch
• Create Table from scratch

Determine Firebird Version

SELECT rdb$get_context('SYSTEM', 'ENGINE_VERSION')
FROM rdb$database;

The database experts Doc/Firebird 3.0 DDL triggers (ibexpert.net)

How to use Firebird with .Net? (firebirdfaq.org)

Embedded Firebird: Full-Featured Embedded Database with 2 MB Runtime - CodeProject

c# - How do INSERT INTO Firebird, with autoincrement for the primary key? - Stack Overflow

Create Database + Determine Firebird Version

using FirebirdSql.Data.FirebirdClient;
using System;
using System.Data;
using System.Diagnostics;

namespace ConsoleApp1
{
 class Program
 {
 private static string m_ConnectionString = "";

 private static void Main(string[] args)
 {
 string exeFilePath = @"C:\Program Files\Firebird\Firebird_3_0\gstat.exe";
 string firebirdDBFilePath = @"C:\Git\DB\asclepiosqualificationNew.FDB";
 string exeArgs = $" -h {firebirdDBFilePath}";

 //https://stackoverflow.com/questions/49338030/how-to-easily-determine-version-of-fdb-file-firebird-database
 //https://stackoverflow.com/questions/15821016/c-sharp-execute-shell-command-and-get-result

 System.Diagnostics.Process proc = new System.Diagnostics.Process();
 proc.StartInfo.FileName = exeFilePath;
 proc.StartInfo.Arguments = exeArgs;
 proc.StartInfo.UseShellExecute = false;
 proc.StartInfo.RedirectStandardOutput = true;
 proc.Start();
 string output = proc.StandardOutput.ReadToEnd();
 string[] arrOutput = output.Split(System.Environment.NewLine);
 foreach (var line in arrOutput)
 {
 if (line.Contains("ODS version"))
 {
 string[] arr = line.Trim().Split("ODS version");
 if (arr.Length > 0)
 {
 string version = arr[1].Trim();
 switch (version)
 {
 case "10":
 case "10.0":
 version = "Firebird 1";
 break;

 case "11":
 case "11.0":
 case "11.2":
 version = "Firebird 2.5";
 break;

 case "12":
 case "12.0":
 version = "Firebird 3";
 break;

 case "13":
 case "13.0":
 version = "Firebird 4";
 break;

 default:
 break;
 }
 Console.WriteLine(version);
 }
 }
 }

 FbConnectionStringBuilder builder = new FbConnectionStringBuilder();
 builder.DataSource = "localhost";
 builder.UserID = "SYSDBA";
 builder.Password = "masterkey";
 builder.Database = "Test";
 //builder.Database = @"C:\Git\DB\Test.fdb";
 //builder.ServerType = FbServerType.Embedded;
 builder.ServerType = FbServerType.Default;
 //{"Dynamic SQL Error\r\nSQL error code = -104\r\nToken unknown - line 1, column 14\r\nUSER"}
 m_ConnectionString = builder.ConnectionString;

 //https://stackoverflow.com/questions/16354712/how-to-programmatically-create-firebird-database
 //CreateDatabase();

 //https://firebirdsql.org/manual/migration-mssql-data-types.html
 //CreateTables();

 //https://stackoverflow.com/questions/13402003/how-to-populate-a-datatable-from-a-stored-procedure
 //CallStored();
 //CallStoredProcedure();
 }

 private static void CreateTables()
 {
 //https://stackoverflow.com/questions/43020306/how-do-insert-into-firebird-with-autoincrement-for-the-primary-key (HAY)
 //https://stackoverflow.com/questions/7195365/firebird-dot-net-provider-doesnt-fully-execute-query
 //https://firebirdsql.org/manual/migration-mssql-data-types.html
 //https://www.codeproject.com/articles/17196/firebird-sqlhelper-a-data-access-application-block
 }

 private static void CreateDatabase()
 {
 try
 {
 FbConnection.CreateDatabase(m_ConnectionString);
 }
 catch (Exception ex)
 {
 Debug.WriteLine(ex.ToString());
 }
 }

 private static DataTable CallStoredProcedure()
 {
 DataTable table = new DataTable();
 using (var con = new FbConnection(m_ConnectionString))
 using (var cmd = new FbCommand("usp_GetUsers", con))
 using (var da = new FbDataAdapter(cmd))
 {
 cmd.CommandType = CommandType.StoredProcedure;
 da.Fill(table);
 }
 return table;
 }

 private static DataTable CallStored()
 {
 var dt = new DataTable();

 using (var con = new FbConnection(m_ConnectionString))
 {
 using (var cm = new FbCommand("usp_GetUsers", con))
 {
 cm.CommandType = CommandType.StoredProcedure;

 using (var da = new FbDataAdapter(cm))
 {
 da.Fill(dt);
 }
 }
 }

 return dt;
 }
 }
}

Create Tables

using FirebirdSql.Data.FirebirdClient;
using FirebirdSql.Data.Isql;
using System;
using System.Diagnostics;

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

 private static void Main(string[] args)
 {
 var connectionString = new FbConnectionStringBuilder
 {
 Database = @"C:\Git\DB\Test.fdb",
 ServerType = FbServerType.Default,
 UserID = "SYSDBA",
 Password = "masterkey",
 DataSource = "localhost"
 }.ToString();

 Debug.WriteLine(connectionString);

 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();
 }

 public enum SolutionType
 {
 FbScriptFB2_5,
 Firebird2_5,
 Firebird3
 }
 }
}