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/


Do you find this FAQ incorrect or incomplete? Please e-mail us what needs to be changed. To ensure quality, each change is checked by our editors (and often tested on live Firebird databases), before it enters the main FAQ database. If you desire so, the changes will be credited to your name. To learn more, visit our add content page.



All contents are copyright © 2007-2024 FirebirdFAQ.org unless otherwise stated in the text.


Links   Firebird   News   FlameRobin   Powered by FB: Home Inventory   Euchre  
Add content   About  

Categories
 Newbies
 SQL
 Installation and setup
 Backup and restore
 Performance
 Security
 Connectivity and API
 HOWTOs
 Errors and error codes
 Miscellaneous