== Firebird Database in .NET ==
== Resources ==
  * Download from [[http://www.firebirdsql.org/index.php?op=files&id=netprovider|Main Site]] or [[http://www.firebirdsql.org/dotnetfirebird/download/index.html|DotNetFirebird]]
  * [[http://www.firebirdfaq.org|Firebird FAQ]]
  * [[http://www.firebirdsql.org|Firebird SQL]]
  * [[http://msdn.microsoft.com/en-us/library/bwy42y0e.aspx|MSDN: DataSet Class]]
  * [[http://msdn.microsoft.com/en-us/library/9186hy08.aspx|MSDN: DataTable Class]]
  * [[http://msdn.microsoft.com/en-us/library/7f2d84ta.aspx|MSDN: DataRow Class]]
  * [[http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.aspx|MSDN:SqlDataAdapter Class]]
  * [[http://www.akadia.com/services/dotnet_databinding.html|Data Binding in .NET / C# Windows Forms]]
  * [[http://en.csharp-online.net/Presenting_Data_with_the_DataGridView_Control_in_.NET_2.0|Presenting Data with DataGridView]]
  * [[http://www.codeproject.com/KB/database/Using_Firebird_ADO_NET.aspx|Beginner’s Tutorial on Using the Firebird ADO.NET Client 2.5]]
  * [[http://ibexpert.net/ibe/index.php?n=Doc.StoredProcedure|Stored Procedures]]

== Installation and Setup ==
 * [[http://www.firebirdsql.org/dotnetfirebird/download/index.html|Download]] the following and install them:
    * VS2005/2008:
      * ADO.NET Data Provider: FirebirdClient 2.0.1 for .NET Framework 2.0
      * Firebird support for Visual Studio: DDEX Provider for VS2005 (should work for VS2008)
    * [[http://sourceforge.net/projects/firebird/files/firebird-net-provider/|VS2010]]:
      * .NET Data Provider: FirebirdClient 2.5.1 for .NET Framework 4.0
      * Firebird support for Visual Studio: Firebird DDEX Provider 2.0.5 for VS2010
 * Copy files (or Install).  IMPORTANT: Both packages must be installed in the same folder.
    * ADO.NET Data Provider (FirebirdClient): Install files to ''C:\Program Files\FirebirdClient''
    * Firebird support for Visual Studio: Copy files to ''C:\Program Files\FirebirdClient''

=== DDEX: Support for Visual Studio ===

==== Registry Update ===
 * Add references in Registry:
   * Change ''%Path%'' to firebird client installation path in file ''C:\Program Files\FirebirdClient\DDEXProviderVS2010\FirebirdDDEXProviderPackageLess32.reg'' (or the 64-bit equivalent file). Remember to escape backslashes. Eg: ''C:\\Program Files\\FirebirdClient\\FirebirdSql.VisualStudio.DataTools.dll''. <code reg>[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\10.0\DataProviders\{92421248-F044-483A-8237-74C7FBC62971}]
@=".NET Framework Data Provider for Firebird"
...
"CodeBase"="C:\\Program Files\\FirebirdClient\\FirebirdSql.VisualStudio.DataTools.dll"
...</code>
   * Verify the correct Visual Studio version:
     * For VS2005: ''HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\8.0\...''
     * For VS2008: ''HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\9.0\...''
     * For VS2010: ''HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\10.0\...''
   * Add registry (perform with admin rights) using ''FirebirdDDEXProviderPackageLess32.reg'' (double click file).

==== Install Assembly in GAC ====
 * Verify that ''FirebirdSql.Data.FirebirdClient'' is in the .NET Global Assembly Cache (GAC): 
    * In Explorer, go to ''C:\Windows\assembly'', and browse for ''FirebirdSql.Data.FirebirdClient''
    * From the VS command line (Start > All Programs > Microsoft Visual Studio > Visual Studio Tools > Visual Studio Command Prompt), type: <code > C:\> gacutil /l FirebirdSql.Data.FirebirdClient</code> If it does not show up, add it (must use the gacutil for the .Net 2.0, so go into the same folder): <code>C:\> CD "C:\Program Files\FirebirdClient"
C:\> gacutil /i "C:\Program Files\FirebirdClient\FirebirdSql.Data.FirebirdClient.dll"</code>

==== Machine.Config Update ====
 * Edit ''machine.config'' file under Config folder in the related Framework folder to register the provider:
    * .NET 2.0: ''C:\Windows\Microsoft.NET\Framework\v2.0.50727\Config\machine.config''  
    * .NET 4.0: ''C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config''  
    * Add this: <code xml>
<configuration>
    <configSections>
        ...
        <section name="firebirdsql.data.firebirdclient" 
            type="System.Data.Common.DbProviderConfigurationHandler, 
            System.Data, Version=%RuntimeVersion%, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
        ...
    <configSections>

    <system.data>
        <DbProviderFactories>
		...
		<add name="FirebirdClient Data Provider" invariant="FirebirdSql.Data.FirebirdClient" 
                    description=".Net Framework Data Provider for Firebird" 
                    type="FirebirdSql.Data.FirebirdClient.FirebirdClientFactory, FirebirdSql.Data.FirebirdClient, 
                          Version=%Version%, Culture=%Culture%, PublicKeyToken=%PublicKeyToken%" />
		...
        </DbProviderFactories>
	</system.data>
	....
</configuration>	
</code> And substitute:
        * ''%RuntimeVersion%'' with the version of .NET Framework, either 2.0.0.0 (.NET 2.0, .NET 3.0, .NET 3.5) or 4.0.0.0 (.NET 4).
        * ''%Version%'' with the version of the provider assembly that you have in the GAC.
        * ''%Culture%'' with the culture of the provider assembly that you have in the GAC.
        * ''%PublicKeyToken%'' with the PublicKeyToken of the provider assembly that you have in the GAC.
        * To see the version, culture, and PublicKeyToken:
           * In Explorer, go to ''C:\Windows\assembly'', and browse for ''FirebirdSql.Data.FirebirdClient''
           * In VS command line (Start > All Programs > Microsoft Visual Studio > Visual Studio Tools > Visual Studio Command Prompt), use ''gacutil'':<code> C:\> gacutil /l FirebirdSql.Data.FirebirdClient</code>
        * Example (using .NET 4.0 Framework): <code xml>
<configuration>
    <configSections>
        ...
        <section name="firebirdsql.data.firebirdclient" 
            type="System.Data.Common.DbProviderConfigurationHandler, 
            System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
        ...
    <configSections>

    <system.data>
        <DbProviderFactories>
           ...
           <add name="FirebirdClient Data Provider" invariant="FirebirdSql.Data.FirebirdClient" 
             description=".Net Framework Data Provider for Firebird" 
             type="FirebirdSql.Data.FirebirdClient.FirebirdClientFactory, FirebirdSql.Data.FirebirdClient, 
                 Version=2.5.2.0, Culture=neutral, PublicKeyToken=3750abcc3150b00c"/>
		...
        </DbProviderFactories>
    </system.data>
    ....
</configuration>	

</code>

== Adding Firebird Support in an Application ==
  * Open MS Visual Studio, and create or open a project:
    * Windows Form project (support for appropriate .NET framework, eg. .NET 2.0, .NET 4.0, etc.).
    * WPF, select Target Framework = ''.NET Framework 4''.  **NOTE**: ''.NET Framework 4 (Client Profile)'' will not work.

=== Data Connection ===
  * Add Data Connection.  
    * Select View > Server Explorer, and try to add connection to Firebird server (right-click on Data Connections > Add Connection).
    * Find Firebird provider in list (if not listed, the registry file was not imported correctly).
    * Type something in the next dialog.  If it closes, it usually means the FirebirdClient assembly cannot be loaded. Verify whether the assembly is in GAC and the information in file ''machine.config'' matches the one in GAC.
  * Add the relevant information for the data source:
    * Data Source: localhost (or whatever the server name is)
    * Data Source Port: 3050 (standard Interbase/Firebird TCP/IP port)
    * Dialect: 1
    * Charset: NONE
    * Database: C:\programming\audina\ezFIT5\src\data\ezfitdb.gdb (or relevant path)
    * User: SYSDBA
    * Password: masterkey
    * Role: < empty >
    * Server Type: Super/Classic Server
{{:swdev:howto:firebird-dotnet-vs2008-addconnection.jpg|Adding a Firebird data connection in MS Visual Studio 2008}} {{:swdev:howto:firebird-dotnet-vs2008-serverexplorer.jpg|Server Explorer after adding Firebird data connection in MS Visual Studio 2008}}

=== Data Source ===
  * Add Data Source. 
    * Select Data > Add New Data Source... (or from Server Explorer: go to Data Source tab (or Data > Show Data Source tab...), then Add New Data Source). 
    * Data Source Type: 
      * Select Database > Next. 
      * {{:swdev:dotnet:firebird-dotnet-proj-datasourceconfig-datasourcetype.jpg?500|Data Source Type}}
    * Database Model:
      * Select dataset. Eg: localhost (ezfitdb).  Then Next.
      * {{:swdev:dotnet:firebird-dotnet-proj-datasourceconfig-databasemodel.jpg?500|Database Model}}
    * Data Connection:
      * Select the appropriate data connection. Eg: localhost (ezfitdb).  Then Next.
      * {{:swdev:dotnet:firebird-dotnet-proj-datasourceconfig-dataconnection.jpg?500|Data Connection}}
    * Connection String:
      * Check to save connection
      * Enter connection string: ezFITDB_localhost (no special characters or spaces)
      * {{:swdev:dotnet:firebird-dotnet-proj-datasourceconfig-connectionstring.jpg?500|Connection String}}
    * Database Objects:
      * Select Database Objects: Tables, Views, Stored Procedures, Functions.
      * Enter Dataset name: datasetEzFITDB
      * {{:swdev:dotnet:firebird-dotnet-proj-datasourceconfig-databaseobject.jpg?500|Database Objects}}
    * Review DataSource and DataSet:
      * {{:swdev:dotnet:firebird-dotnet-proj-datasources.jpg|Data Sources listed in Server Explorer (MS Visual Studio 2010) after adding Firebird data source}}{{:swdev:dotnet:firebird-dotnet-proj-solutionexplorer-dataset.jpg|DataSet listed in Solution Explorer (MS Visual Studio 2010) after adding Firebird data source}}

=== Data-aware Controls ===
  * In the application form (GUI), add DataSet component, add DataGridView component and link to Data Source in current project.
  * In the DataGridView control, go to Properties dialog box, then select DataMember to point to the required table (eg. ''PATIENT'') .
  * Add a TableAdapter for each table you need, by selecting one from the Toolbox (eg.: ''PATIENTTableAdapter'' becomes ''patientTableAdapter1'' on the form)

WPF:
<code xml>
<Window x:Class="FirebirdDBApp.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="350" Width="525">
    <Grid>
        <DataGrid ItemsSource="{Binding}"></DataGrid>
    </Grid>
</Window>
</code>

C#:
<code csharp>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace FirebirdDBApp
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();

            // construct dataset
            datasetEzFITDB ds = new datasetEzFITDB();

            // use a table adapter to populate the patients table
            datasetEzFITDBTableAdapters.PATIENTTableAdapter adapt = new datasetEzFITDBTableAdapters.PATIENTTableAdapter();
            adapt.Fill(ds.PATIENT);

            // use the Patient table as the DataConext for this Window
            this.DataContext = ds.PATIENT.DefaultView;
        }
    }
}
</code>

=== Queries ===
  * If necessary, edit table queries (and table adapters). 
    * Test queries by selecting TableAdapter component in form, then right-clicking and "Preview Data".
    * If editing is necessary, select DataSet or TableAdapter component in form, then right-click and "Edit in DataSet Designer".
    * Rearrange tables for easy view. {{:swdev:howto:firebird-dotnet-vs2008-dataset-designer.jpg|VS2008 DataSet Designer}}
    * Create queries using Query Builder. While in the DataSet Designer, right-click on a table, then Configure > Query Builder. Select necessary fields, then < Finish >. {{:swdev:howto:firebird-dotnet-vs2008-tableadapterconfigwzd.jpg|VS2008 TableAdapter Configuration Wizard}} {{:swdev:howto:firebird-dotnet-vs2008-querybuilder.jpg|VS2008 Query Builder}}
  * Fill DataGridView with table data. Add the following code to the main form: <code csharp>
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace Sample1
{
  public partial class frmMain : Form
  {
     public frmMain()
     {
         InitializeComponent();
     }
     private void frmMain_Load(object sender, EventArgs e)
     {
         this.patientTableAdapter1.Fill(this.datasetEzFITDB.PATIENT);
     }
  }
}
</code>
  * Compile and run application.  Data should be displayed on DataGridView.

=== Installer using InnoSetup ===

In ''[Files]]'' section, add global assembly cache (GAC) registration for ''FirebirdSql.Data.FirebirdClient.dll'' (one line, where {#LibDir} is where the source file is located):
<code>
Source: {#LibDir}\FirebirdClient\FirebirdSql.Data.FirebirdClient.dll; 
   DestDir: {win}\assembly; Flags: gacinstall sharedfile; 
   StrongAssemblyName: "FirebirdSql.Data.FirebirdClient, Version=2.6.0.0, Culture=neutral, 
      PublicKeyToken=3750abcc3150b00c, ProcessorArchitecture=MSIL"
</code>

== Components with Data Binding ==

=== DataGridView ===
  * Add DataGridView component to a form.  Set the following properties:
    * DataSource: point to BindingSource (eg. ''dataSetEzFITDB1BindingSource'' or ''PATIENTBindingSource'').
    * DataMember: point to the required table (eg. ''PATIENT'') if DataSource points only to a DataSet binding source (eg. ''dataSetEzFITDB1BindingSource''), as opposed to a table binding source (eg. ''PATIENTBindingSource'').
    * Columns: choose the required columns to display.

=== TextBox ===
  * Add TextBox component to a form.
  * Add Data Binding.  
     * In TextBox component properties, go to Data > DataBindings > Advanced.
     * Select Bindings > Other Data Sources > Project Data Sources, and pick one data source. 
{{:swdev:howto:firebird-dotnet-vs2008-advancebinding-dialog1.jpg|Advanced Data Binding dialog}}
       * Select a table under the data source. Eg: For table ''PATIENT''...
{{:swdev:howto:firebird-dotnet-vs2008-advancebinding-dialog2.jpg|Advanced Data Binding dialog}}
       * Select a field under the table. Eg: For table ''PATIENT'' and field ''LASTNAME''...
{{:swdev:howto:firebird-dotnet-vs2008-advancebinding-dialog3.jpg|Advanced Data Binding dialog}}
     * The data binding string should look like ''PATIENTBindingSource - LASTNAME''.  The data binding dialog allows customization:
{{:swdev:howto:firebird-dotnet-vs2008-advancebinding-dialog4.jpg|Advanced Data Binding dialog}}

=== BindingNavigator ===
  * Add BindingSource component to a form for the required table. Eg: For table ''PATIENT'' :
     * Name: ''PATIENTBindingSource''
     * DataSource: ''dataSetEzFITDB1''
     * DataMember: ''PATIENT''
  * Add BindingNavigator component to a form for the required table. Eg: For table ''PATIENT'' :
     * Name: ''PATIENTBindingNavigator''
     * Text: ''Navigator for Patient Table''
     * BindingSource: ''PATIENTBindingSource''
     * Items: Choose what buttons and controls are displayed.

=== ListBox ===
  * Add ListBox component to form.
  * Set properties. Eg: for table ''PATIENT'' and field ''LASTNAME'':
     * DataSource: ''PATIENTBindingSource''
     * DisplayMember: ''LASTNAME''

== Deploying a Firebird Application ==

=== Configuration File ===

Add the following to the ''//app//.exe.config'' file (must reside in the same folder and the ''//app//.exe''.  See [[http://msdn.microsoft.com/en-us/library/k4s6c3a0(v=VS.100).aspx|Application Settings]] and [[http://msdn.microsoft.com/en-us/library/system.configuration.configuration.connectionstrings.aspx#Y300|Configuration.ConnectionStrings]] for more details):

Using database server:
<code xml>
<?xml version="1.0"?>
<configuration>
<configSections>
</configSections>
...
<connectionStrings>
   <!--<add name="Acme.App.Database.Properties.Settings.MyAppDB_localhost"
        connectionString="character set=NONE;
            ServerType=0;
            data source=localhost;
            initial catalog=C:\Program Files\Acme\MyApp\data\mydb.gdb;
            user id=SYSDBA;
            password=masterkey;
            dialect=3"
        providerName="FirebirdSql.Data.FirebirdClient" />-->
   <add name="Acme.App.Database.Properties.Settings.MyAppDB_localhost"
        connectionString="Charset=NONE;
            ServerType=0;
            DataSource=localhost;
            Database=C:\Program Files\Acme\MyApp\data\mydb.gdb;
            User=SYSDBA;
            Password=masterkey;
            Dialect=3"
        providerName="FirebirdSql.Data.FirebirdClient" />     
</connectionStrings>
<startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/></startup>
...
</configuration>
</code>

Using embedded database:
<code xml>
<?xml version="1.0"?>
<configuration>
<configSections>
</configSections>
...
<connectionStrings>
   <add name="Acme.App.Database.Properties.Settings.MyAppDB_localhost"
        connectionString="Charset=NONE;
            ServerType=1;
            Database=C:\Program Files\Acme\MyApp\data\mydb.gdb;
            User=SYSDBA;
            Password=masterkey;
            Dialect=3"
        providerName="FirebirdSql.Data.FirebirdClient" />
</connectionStrings>
<startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/></startup>
...
</configuration>
</code>

=== InnoSetup script ===
Add the following files to distribute:
<code inno>
[Files]
...
;---------------------------------------------------
; Data files
;---------------------------------------------------
Source: ..\Data\mydb.gdb; DestDir: {app}\data\; Flags: onlyifdoesntexist
;---------------------------------------------------
; Database Engine files
;---------------------------------------------------
; Firebird Client
Source: {#LibDir}\FirebirdClient\FirebirdSql.Data.FirebirdClient.dll; DestDir: {win}\assembly; 
    Flags: gacinstall sharedfile; 
    StrongAssemblyName: FirebirdSql.Data.FirebirdClient, Version=2.6.0.0, Culture=neutral, PublicKeyToken=3750abcc3150b00c, ProcessorArchitecture=MSIL
;Source: {#LibDir}\firebird\fbembed.dll; DestDir: {app}; DestName: gds32.dll
Source: {#LibDir}\firebird\fbembed.dll; DestDir: {app} 
; Firebird Embedded Server
Source: {#LibDir}\firebird\ib_util.dll; DestDir: {app}
Source: {#LibDir}\firebird\icudt30.dll; DestDir: {app}
Source: {#LibDir}\firebird\icuin30.dll; DestDir: {app}
Source: {#LibDir}\firebird\icuuc30.dll; DestDir: {app}
Source: {#LibDir}\firebird\firebird.msg; DestDir: {app}; Flags: ignoreversion
Source: {#LibDir}\firebird\firebird.conf; DestDir: {app}; Flags: ignoreversion
Source: {#LibDir}\firebird\udf\fbudf.dll; DestDir: {app}\udf
Source: {#LibDir}\firebird\intl\fbintl.dll; DestDir: {app}\intl; Flags: regserver noregerror
Source: {#LibDir}\firebird\intl\fbintl.conf; DestDir: {app}\intl; Flags: ignoreversion
...
</code>

Add support for creating the ''//app//.config'' file:
<code inno>
...
[Code]
//----------------------------------------------------------------------------
// global types
//----------------------------------------------------------------------------
Type
   ...
//----------------------------------------------------------------------------
// global variables
//----------------------------------------------------------------------------
var
   ...
      
//----------------------------------------------------------------------------
// function prototypes
//----------------------------------------------------------------------------
procedure DoPostInstall(); forward;
procedure CreateConfigFile(ServerName: string; DBPath: string); forward;

//----------------------------------------------------------------------------
// description: PostInstall custom code.  This code executes after wizard is done.
// parameters : void
// return     : void
//----------------------------------------------------------------------------
procedure DoPostInstall();
var
  ResultCode: integer;
begin
  Log('DoPostInstall(): entering...');  //debug
  //---------------------------
  // Post installation
  //---------------------------
  // unregister old DLLs
  // register DLLs
  // create registry entries
  // remove old registry entries
  // create Icon group
  ...


  // Create Config (Settings) file
  CreateConfigFile('localhost', ExpandConstant('{app}\data\mydb.gdb') );
end;

//----------------------------------------------------------------------------
// description: Create the config file.
// parameters : void
// return     : void
//----------------------------------------------------------------------------
procedure CreateConfigFile(ServerName: string; DBPath: string);
var
	str: array [0..15] of string;
begin
	str[0]  := '<?xml version="1.0"?>';
	str[1]  := '<configuration>';
	str[2]  := '<configSections>';
	str[3]  := '</configSections>';
	str[4]  := '<connectionStrings>';
	str[5]  := '   <add name="Acme.App.Database.Properties.Settings.MyAppDB_localhost"';
        // For Database Server:
	//str[6]  := '        connectionString="character set=NONE;ServerType=0;data source=localhost;initial catalog=C:\myapp\data\mydb.gdb;user id=SYSDBA;password=masterkey;dialect=3"';
	//str[6]  := '        connectionString="character set=NONE;ServerType=0;data source='+ ServerName + ';Database=' + DBPath + ';user id=SYSDBA;password=masterkey;dialect=3"';
        // For Embedded Database:
	str[6]  := '        connectionString="character set=NONE;ServerType=1;Database=' + DBPath + ';user id=SYSDBA;password=masterkey;dialect=3"';
	str[7]  := '        providerName="FirebirdSql.Data.FirebirdClient" />';
	str[8]  := '</connectionStrings>';
	str[9]  := '<startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/></startup>';
	str[10] := '</configuration>';

	SaveStringsToFile(ExpandConstant('{app}\myapp.exe.config'), str, False);
end;
...
</code>