I decided to do some exploration of using Firebird. I wanted to explore using it in the embedded mode, since it would be easy to deploy that way as any type of application.
At first, I tried to get to a final ASP.NET 2.0 and Firebird Embedded database working through a web application. I started to set up pages to manage the Database creation and definition process, but then when I had problems I was unsure if it was the environment or my lack of understanding.
I really wanted to be able to create and define my database in c#. I found many GUI tools to use to manage Firebird databases (most easily in the server model), and not so much documentation on the DDL for Firebird.
I used a couple GUI tools to try to create databases, tables, and insert rows into them to get the basic syntax of the SQL statements. I then used Sharpdevelop to make a console application that creates a database, creates a table (and associated other database objects), and inserts a record.
My environment is Sharpdevelop 1.1, fbembed.dll version 1.5.2.4731, and FirebirdSql.Data.Firebird.dll version 1.6.3.0.
Here's the commented code that did the job. Feel free to use whatever you might find useful or ask about anything that does not seem to make sense.
/*
* Created by SharpDevelop.
* User: Brian Lakstins
* Date: 9/8/2005
* Time: 6:50 AM
*
*/
using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.IO;
using System.Text;
using FirebirdSql.Data.Firebird;
namespace FireBird_Console_11
{
class MainClass
{
public static void Main(string[] args)
{
//Just to make sure the program is doing "something".
Console.WriteLine("Hello World!");
string lsDatabaseName = "test";
//Call the method to create my "test" database.
MainClass.CreateDatabase(lsDatabaseName);
//Call the method to create my "Person" table.
MainClass.CreatePersonTable(lsDatabaseName);
//Insert a record into my "Person" table and get the ID of the record I inserted.
Int32 lnNewID = MainClass.PersonTableInsert(lsDatabaseName,"Brian","Alan","Lakstins");
//Write out the ID.
Console.WriteLine(lnNewID.ToString());
}
public static void CreateDatabase(string lsDatabaseName){
//The directory used will be the application's directory which contains the fbembed.dll
//Any extension should work, but Firebird Data Base seems to make sense.
string lsFileName = lsDatabaseName + ".fdb";
//The existing database file is deleted each time the code is run so that the
//database can be created new each time.
if (File.Exists(lsFileName)) File.Delete(lsFileName);
if (!File.Exists(lsFileName))
{
Hashtable loValues = new Hashtable();
//Supposedly the User and Password do not matter with an embedded database, but the
//.NET Provider throws an error if it is not included.
loValues.Add("User", "ConsoleUser");
loValues.Add("Password", "pass");
//I'm not sure what the valid servertypes are, but I think 1 means embedded.
loValues.Add("ServerType", "1");
//This seems pretty self explanatory. I think that a full path should work
//here too, but I'm just sending the file name which should create the database
//in the same directory as the fbembed.dll
loValues.Add("Database", lsFileName);
//I'm not sure if I want to use UNICODE_FSS character set with my database (since
//C# String are Unicode, or not, so I put the code here, but did not use it.
//loValues.Add("Charset", "UNICODE_FSS");
Console.WriteLine("Creating database");
//Uses a static method of the FbConnection object to create the database.
FbConnection.CreateDatabase(loValues);
}
}
public static void CreatePersonTable(string lsDatabaseName){
//This is the SQL Text from a Firebird Database Management utility to create
//a table that has an AutoIncrementing field called "id" that is also the
//primary key of the table.
/*
CREATE TABLE "Person" (
"id" INTEGER
NOT NULL
)
alter table "Person"
add constraint "PK_Person"
primary key ("id")
CREATE GENERATOR "Person_id_GEN"
SET GENERATOR "Person_id_GEN" TO 0
CREATE TRIGGER "AI_Person_id" FOR "Person"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW."id" IS NULL) THEN
NEW."id" = GEN_ID("Person_id_GEN", 1);
END
*/
//Build strings for each command. I could probably have just done one big command, but
//doing them individually helps me to debug easier.
StringBuilder loSQL = new StringBuilder("CREATE TABLE \"Person\" (");
loSQL.Append("\"id\" INTEGER NOT NULL,");
loSQL.Append("\"namefirst\" VARCHAR(255),");
loSQL.Append("\"namemiddle\" VARCHAR(255),");
loSQL.Append("\"namelast\" VARCHAR(255))");
string lsSQLPrimaryKeyCreate = "ALTER TABLE \"Person\" ADD CONSTRAINT \"PK_Person\" PRIMARY KEY (\"id\")";
string lsSQLGeneratorCreate = "CREATE GENERATOR \"Person_id_GEN\"";
string lsSQLGeneratorSet = "SET GENERATOR \"Person_id_GEN\" TO 0";
string lsSQLTriggerCreate = "CREATE TRIGGER \"AI_Person_id\" FOR \"Person\" \r\n" +
"ACTIVE BEFORE INSERT POSITION 0 \r\n" +
"AS \r\n" +
"BEGIN \r\n" +
"IF (NEW.\"id\" IS NULL) THEN \r\n" +
"NEW.\"id\" = GEN_ID(\"Person_id_GEN\", 1); \r\n" +
"END";
//This is not just defining the table, but creating a procedure to insert a row
// and return the automatically generated ID
string lsSQLStoredProcedureCreate = "CREATE PROCEDURE SP_PersonINSERT (" +
"\"namefirst\" VARCHAR(255),\r\n" +
"\"namemiddle\" VARCHAR(255),\r\n" +
"\"namelast\" VARCHAR(255))\r\n" +
"RETURNS ( \r\n" +
"id INTEGER)\r\n" +
"AS \r\n" +
"BEGIN \r\n" +
"id = GEN_ID(\"Person_id_GEN\",1);\r\n" +
"INSERT INTO \"Person\" (\"id\", \"namefirst\", \"namemiddle\", \"namelast\") " +
"VALUES (:id, :\"namefirst\", :\"namemiddle\", :\"namelast\");\r\n" +
"SUSPEND;\r\n" +
"END";
//Show my Create table statement.
Console.WriteLine(loSQL.ToString());
string lsFileName = lsDatabaseName + ".fdb";
//This just ... uh ... gets the current directory...
string lsDirectory = Directory.GetCurrentDirectory();
//Using just the file name to connect did not seem to work for me with the
//connection string. I needed the full path.
string lsDatabaseFile = lsDirectory + "\\" + lsFileName;
//This the the connection string. I tried without username and password and it did
//not seem to work.
string lsConnection = "User=ConsoleUser;Password=pass;Database=" + lsDatabaseFile + ";ServerType=1;";
Console.WriteLine(lsConnection);
//All this stuff is just basically connecting to the database
//and running a bunch of different SQL statements.
FbConnection loConnection = new FbConnection(lsConnection);
FbCommand loCommandTableCreate = new FbCommand(loSQL.ToString(), loConnection);
FbCommand loCommandPrimaryKeyCreate = new FbCommand(lsSQLPrimaryKeyCreate, loConnection);
FbCommand loCommandGeneratorCreate = new FbCommand(lsSQLGeneratorCreate, loConnection);
FbCommand loCommandGeneratorSet = new FbCommand(lsSQLGeneratorSet, loConnection);
FbCommand loCommandTriggerCreate = new FbCommand(lsSQLTriggerCreate, loConnection);
FbCommand loCommandStoredProcedureCreate = new FbCommand(lsSQLStoredProcedureCreate, loConnection);
loConnection.Open();
try {
loCommandTableCreate.ExecuteNonQuery();
loCommandPrimaryKeyCreate.ExecuteNonQuery();
loCommandGeneratorCreate.ExecuteNonQuery();
loCommandGeneratorSet.ExecuteNonQuery();
loCommandTriggerCreate.ExecuteNonQuery();
loCommandStoredProcedureCreate.ExecuteNonQuery();
} catch (Exception loE){
Console.WriteLine(loE.ToString());
}
finally {
loConnection.Close();
}
}
public static Int32 PersonTableInsert(string lsDatabaseName,
string lsNameFirst,
string lsNameMiddle,
string lsNameLast){
//This is the same stuff that had to go on above to connect to the database. Not all that interesting.
string lsFileName = lsDatabaseName + ".fdb";
string lsDirectory = Directory.GetCurrentDirectory();
string lsDatabaseFile = lsDirectory + "\\" + lsFileName;
string lsConnection = "User=ConsoleUser;Password=pass;Database=" + lsDatabaseFile + ";ServerType=1;";
FbConnection loConnection = new FbConnection(lsConnection);
//This is to run the stored procedure.
FbCommand loCommand = new FbCommand("SP_PersonINSERT", loConnection);
loCommand.CommandType = CommandType.StoredProcedure;
//This was the tricky part for me. Creating a parameter to get the result.
//ExecuteScaler would not just return the single return parameter for me, although
//it seemed like it would.
FbParameter loR = new FbParameter();
loR.Direction = ParameterDirection.ReturnValue;
loR.FbDbType = FbDbType.Integer;
loR.ParameterName = "id";
loCommand.Parameters.Add(loR);
//This is just adding the parameters passed.
loCommand.Parameters.Add("@namefirst",lsNameFirst);
loCommand.Parameters.Add("@namemiddle",lsNameMiddle);
loCommand.Parameters.Add("@namelast",lsNameLast);
//Just connecting to the database and running the Stored procedure.
loCommand.Connection.Open();
try
{
loCommand.ExecuteNonQuery();
} catch (Exception loE){
Console.WriteLine(loE.ToString());
}
finally
{
loCommand.Connection.Close();
}
//Sending back the result. Had to cast it since it comes back generic from the database.
return (Int32)loR.Value;
}
}
}