FunctionsDb

Description

Is a very easy to use library for connect to different DBMS and exec queries.
Unpretending but effective. Is NOT a framework or a substitute to Hibernate.

Examples

Inizialize variables:

public static MixLib.GlobalDB DbData;
public static MixLib.FunctionsDb dbf;
...
DbData = new MixLib.GlobalDB(dbServer, dbUser, dbPassw, dbDatabase, dbType);
dbf = new MixLib.FunctionsDb(DbData);

Simple select

string value = dbf.ExecuteScalar("Select top 1 name from ana");
DataTable dt = dbf.GetDataTable("Select * from ana");

Dynamic use

describe fields

ArrayList fields= new ArrayList();
fields.Add(new ArrayList() { "code", varCode, "VARCHAR", 20 });
fields.Add(new ArrayList() { "age", varTot, "SMALLINT", 3 });
fields.Add(new ArrayList() { "name", varDescr, "VARCHAR", 255 });

exec actions

// insert
int retCode = Globals.dbf.ExecSql("INSERT", fields, "ana", "");
// update
int retCode = Globals.dbf.ExecSql("UPDATE", fields, "ana", "code");
// delete
int retCode = Globals.dbf.ExecSql("DELETE", fields, "ana", "code");

if there is a multi-key table

int retCode = Globals.dbf.ExecSql("UPDATE", fields, "ana_ord", "code_ana,id_ord");

Advanced use

n() function

this function is useful if you will use different DBMS in the same projects. You have to use it in all names, will add the right apex.

"SELECT " + n("name") + " FROM " + n("ana");

MVVM Helper

Prerequisites

With Load and Save check with reflection the fields of the Model with the table of Databases.
This is an example of a Model.

~~~~~~~~~~~~
class Anagrafica : EntityBase<anagrafica>
{
private string PRIMARY_KEY = "id";
public static string NOME_TABELLA = "anagrafica";</anagrafica>

...

// FIELD IN DB:
// id              INTEGER         PRIMARY KEY AUTOINCREMENT,
// nome            VARCHAR( 50 ),
// cognome         VARCHAR( 50 ),
// ...
//

private long? _id;
private string _nome;
private string _cognome;

public long? id
{
    get { return this._id; }
    set { this._id = value; this.OnPropertyChanged("id"); }
}
public string nome
{
     get { return _nome; }
     set { _nome = value; OnPropertyChanged("nome"); }
}
public string cognome
{
     get { return _cognome; }
     set { _cognome = value; OnPropertyChanged("cognome"); }
}

}

Functions in the models
---------------

public void Load(string id)
{
string sql = "SELECT * FROM "
+ Globals.dbf.n(NOME_TABELLA)
+ " WHERE " + PRIMARY_KEY
+ " = '" + UtilityWK.FormattaSqlString(id) + "'";

Boolean res = MixLib.Db.FunctionsDbSupport.FillClassFromDb(
        Globals.dbf, 
        sql, 
        typeof(Anagrafica), 
        this, 
        out _Field_InitialValues);

if (res) IsNewRecord = false;
else IsNewRecord = true;

}

public Boolean Save()
{
this._Save(
Globals.dbf,
Elenco_Campi,
Field_InitialValues,
PRIMARY_KEY,
NOME_TABELLA,
IsNewRecord);

return true;

}

NB: the property Elenco_Campi is the List of Fields and is loaded in this way:

private static Dictionary<string, dictionary\<string,="" string="">> _elenco_Campi;
public static Dictionary<string, dictionary\<string,="" string="">> Elenco_Campi
{
get
{
if (_elenco_Campi == null)
{
_elenco_Campi = Globals.dbf.GetTableInfo(NOME_TABELLA);
}</string,></string,>

    return _elenco_Campi;
}

}

Collections of Models (for lists, etc)
--------------------

class AnagraficaCollection : ObservableCollectionWithCurrent<anagrafica>
{
public void GetAllFromDb()
{
this.Clear();</anagrafica>

    string sql = "SELECT * FROM " + Globals.dbf.n(Anagrafica.NOME_TABELLA) + " ";

    List<object> lista = MixLib.Db.FunctionsDbSupport.FillClassFromDb(
            Globals.dbf, 
            sql, 
            typeof(Anagrafica));

    foreach (object o in lista) this.Add((Anagrafica)o);           
}

}
~~~~~~