How to use Firebird with .Net?

The .Net driver for Firebird is a sub-project of the Firebird project. Here are some usage examples. To set up the connection, you would use some code like this:

FbConnection c = new FbConnection(@"Server=localhost;User=SYSDBA;Password=masterkey;Database=C:\data\test.fdb");


1. Use FbCommand with simple SQL statements:

- FbCommand.ExecuteReader() to SELECT or EXECUTE stored procedure with SUSPEND
- FbCommand.ExecuteScalar() to EXECUTE stored procedure returning a single row
- FbCommand.ExecuteNonQuery() to execute DDL and DML (insert, update, etc.)

1.1. Using FbCommand with plain SQL

FbCommand cmd = new FbCommand("insert into t1(id, text) values (@id, @text);");
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@id", 123);
cmd.Parameters.Add("@text", "my string");
using (cmd.Connection = new FbConnection(ConfigurationManager.ConnectionStrings["mydb"].ConnectionString)){
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}

using() ensures that the FbCommand will be disposed of even if there is an exception.

1.2. Using FbCommand with a stored procedure

FbCommand cmd = new FbCommand("insert_proc");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@text", "my string");
using (cmd.Connection = new FbConnection(ConfigurationManager.ConnectionStrings["mydb"].ConnectionString)){
cmd.Connection.Open();
int id = Convert.ToInt32(cmd.ExecuteScalar());
}

ExecuteScalar() method can be used when the stored procedure returns a single value (without SUSPEND).

2. FbDataAdapter

Use FbDataAdapter to load data from the database to a DataSet or a DataTable. You can also use it to submit the changes made in the DataSet back to the database.

2.1. FbDataAdapter usage with plain SQL

DataTable dt = new DataTable();
FbDataAdapter da = new FbDataAdapter("select * from mytable where id = @id", ConfigurationManager.ConnectionStrings["mydb"].ConnectionString);
da.SelectCommand.Parameters.Add("@id", 123);
da.Fill(dt);


2.2. FbDataAdapter usage with stored procedures

DataTable dt = new DataTable();
FbDataAdapter da = new FbDataAdapter("select_mytable", ConfigurationManager.ConnectionStrings["mydb"].ConnectionString);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.Add("@id", 123);
da.Fill(dt);


3. DbProviderFactory

FirebirdClient supports generic database access though FirebirdClientFactory (which implements DbProviderFactory).

The following method uses DbProviderFactory to executes the SELECT command on the database specified by the providerName and connectionString.

DbProviderFactory f = DbProviderFactories.GetFactory("FirebirdSql.Data.FirebirdClient");
DbConnection connection = f.CreateConnection();
connection.ConnectionString = "DataSource=localhost;Database=mydb;UserID=SYSDBA;Password=masterkey";

DbCommand command = f.CreateCommand();
command.CommandText = "SELECT * FROM mytable";
command.Connection = connection;
DbDataAdapter da = f.CreateDataAdapter();
da.SelectCommand = command;

DataTable dt = new DataTable();
da.Fill(dt);


You can find more usage examples here:

http://www.firebirdsql.org/en/net-examples-of-use/