Re: [Firebird-net-provider] where to get .net provider and exmples

[Miguel M <bo...@ne...>]

""David B"" <NO_...@ya...> wrote in message
news:bcts6o$ovf$1...@ne......
> Hi,
>
> Could somebody please tell me where I can get the .Net provider for
firebird
> and possibly a good xample of connecting to a database, issuing select
> statements, using stored procedures from VB.NET.
>
> I am been searching through the archieves for this group and think that I
> can piece together various bits of info to make a connection once I have
the
> provider, although an example would be good.
>
> My back group has mostly been in classic ASP development to MS SQL
> databases, so hopefully I won't find this toooo hard.
>
> any help is very much appreciated.
>
>
> cheers
>
> martin.

Here is my  C# example. I've come to this code when I was learning how to
run procedures in asp.net with firebird( a few days ago :)  ).
It hasn't been fully tested and I haven't put any error control stuff but
it's working fine and helped me to learn.

Based on the book ASP.NET Website programming - problem -designed -solution
I've "adapted" a few functions to run with Firebird that will make like
easier when running stored procedures.

Base class that provides support functions as well as automatic
instantiation and configuration of FbConnection object:

FbDbObject.cs

using System;
using System.Data;
using FirebirdSql.Data.Firebird;


namespace FbData
{
 public class FbDbObject
 {
  protected FbConnection Connection;
  protected FbTransaction tx;
  private string connectionString;

  public FbDbObject(string newConnectionString)
  {
   connectionString = newConnectionString;
   Connection = new FbConnection(connectionString);
  }

  protected string ConnectionString
  {
   get
   {
    return connectionString;
   }
  }

  private FbCommand BuildIntCommand(string storedProcName, IDataParameter[]
parameters)
  {
   FbCommand command= BuildQueryCommand(storedProcName,parameters);

   command.Parameters.Add(new
FbParameter("ReturnValue",FbType.Integer)).Direction =
ParameterDirection.ReturnValue;

    return command;
  }

  private FbCommand BuildQueryCommand(string storedProcName,
IDataParameter[] parameters)
  {

   FbCommand command = new FbCommand(storedProcName,Connection,tx);
   command.CommandType = CommandType.StoredProcedure;

   foreach(FbParameter parameter in parameters)
   {
    command.Parameters.Add(parameter).Direction = ParameterDirection.Input;
   }

   return command;
  }

  private FbCommand BuildQueryCommand(string storedProcName)
  {
   FbCommand command = new FbCommand(storedProcName,Connection,tx);
   return command;
  }

  public int RunProcedure(string storedProcName, IDataParameter[]
parameters, out int rowsAffected)
  {
   int result;
   Connection.Open();
   tx = Connection.BeginTransaction();
   FbCommand command = BuildIntCommand(storedProcName, parameters);
   rowsAffected = command.ExecuteNonQuery();
   result = (int)command.Parameters["ReturnValue"].Value;
   tx.Commit();
   tx.Dispose();
   Connection.Close();

   return result;
  }

  public void RunProcedure_1(string storedProcName, IDataParameter[]
parameters)
  {
   Connection.Open();
   tx = Connection.BeginTransaction();
   FbCommand command = BuildQueryCommand(storedProcName, parameters);
   command.ExecuteNonQuery();
   tx.Commit();
   tx.Dispose();
   Connection.Close();
  }


  public FbDataReader RunProcedure(string storedProcName, IDataParameter[]
parameters)
  {
   FbDataReader returnReader;
   Connection.Open();
   tx = Connection.BeginTransaction();
   FbCommand command = BuildIntCommand(storedProcName, parameters);
   command.CommandType = CommandType.StoredProcedure;

   returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
   tx.Commit();
   tx.Dispose();

   return returnReader;
  }

  public DataSet RunProcedure(string storedProcName, IDataParameter[]
parameters, string tableName)
  {
   DataSet dataSet = new DataSet();
   Connection.Open();
   tx = Connection.BeginTransaction();
   FbDataAdapter fbDA = new FbDataAdapter();
   fbDA.SelectCommand = BuildQueryCommand(storedProcName, parameters);
   fbDA.Fill(dataSet, tableName);
   tx.Commit();
   tx.Dispose();
   Connection.Close();

   return dataSet;

  }

  public DataSet RunProcedure(string storedProcName, string tableName)
  {
   DataSet dataSet = new DataSet();
   Connection.Open();
   tx = Connection.BeginTransaction();
   FbDataAdapter fbDA = new FbDataAdapter();
   fbDA.SelectCommand = BuildQueryCommand(storedProcName);
   fbDA.Fill(dataSet, tableName);
   tx.Commit();
   tx.Dispose();
   Connection.Close();

   return dataSet;
  }


  public void RunProcedure(string storedProcName, IDataParameter[]
parameters, DataSet dataSet, string tableName)
  {
   Connection.Open();
   tx = Connection.BeginTransaction();
   FbDataAdapter fbDA = new FbDataAdapter();
   fbDA.SelectCommand = BuildQueryCommand(storedProcName, parameters);
   fbDA.Fill(dataSet, tableName);
   tx.Commit();
   tx.Dispose();
   Connection.Close();
  }
 }
}

To use this I created a simple web page with one datagrid, 2 textboxes, a
combobox and a submit button.
Here is part of the codebehind file (I removed unnecessary parts):

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using FirebirdSql.Data.Firebird;
using FbData;
using System.Configuration;


namespace fire
{
 public class WebForm1 : System.Web.UI.Page
 {
  protected System.Web.UI.WebControls.TextBox txtName;
  protected System.Web.UI.WebControls.TextBox txtEmail;
  protected System.Web.UI.WebControls.DropDownList cboActivated;
  protected System.Web.UI.WebControls.Button btnInsert;
  protected System.Web.UI.WebControls.DataGrid DataGrid1;
  FbDbObject dataT;

 //get the connection sctring from web.config
 static string myConnectionString =
ConfigurationSettings.AppSettings["connStr"];


/********* in web.config *************/
<configuration>
   <appSettings>
    <add key="connStr"
value="User=xxxxx;Password=xxxx;Database=f:\fire\data\firetest.fdb;DataSourc
e=localhost;Port=3050;Dialect=3;Charset=ISO8859_1;Role=;Connection
lifetime=30;Pooling=true;Packet Size=8192" />
  </appSettings>
</configuration>
/********************************/


  private void Page_Load(object sender, System.EventArgs e)
  {
   dataT  = new FbDbObject(myConnectionString);
   ShowData();
  }

  // Populates the datagrid
private void ShowData()
  {
   DataGrid1.DataSource = dataT.RunProcedure("SELECT * FROM
GET_CLIENTES","clientes");
   DataGrid1.DataBind();
  }

//inserts new row
private void btnInsert_Click(object sender, System.EventArgs e)
  {
   FbParameter[] parameters = {new FbParameter("@NAME",FbType.VarChar),
          new FbParameter("@EMAIL",FbType.VarChar),
          new FbParameter("@ACTIVATED",FbType.Char)};

   parameters[0].Value = Server.HtmlEncode(txtName.Text);
   parameters[1].Value = Server.HtmlEncode(txtEmail.Text);
   parameters[2].Value = cboActivated.SelectedItem.Value;
   dataT.RunProcedure_1("EXECUTE PROCEDURE
ADD_CLIENTE(@NAME,@EMAIL,@ACTIVATED)",parameters);

      ShowData();
  }
 }
}


If I did anything wrong or if someone has ideas how to improve it, I welcome
them :)

Miguel