SQL

Please take into consideration using Named Parameters instead of the formatted strings as in the examples below.

SQL (using named parameters)

SelectValue

Read a single Value from the DataBase. If no value could be found, the NoSqlResultException is thrown. If more than one value is found, the InvalidOperationException is thrown.

int max = Sql.SelectValue("SELECT  MAX(nr)  FROM  versand_vorschlag ").ToInt(0);

SelectRow

Selects multiple values from the DataBase. All values have the same where-clause. Returns NULL if no result is found.

var resultRow = Sql.SelectRow(
    "Select lager_nr, bestand from bestand where lager_nr = {0} and art_nr = '{1}'",
    versandRow.lager_nr, versandRow.artnr);

SelectTable

Select multiple values and rows from the DataBase. Returns NULL if no rows are found.

DataTable resultRows = Sql.SelectTable(
    "Select lager_nr, lagerort, charge, seriennr, bestand, fifodatum from bestand_detail where lager_nr = {0} and art_nr = '{1}'",
     versandRow.lager_nr, versandRow.artnr);

TrySelectValue

Read a single Value from the DataBase.

No exceptions are thrown but the results are returned in the ret variable.

Attention! ret corresponds to the number of rows found (usually 1=ok). It is NOT the SQL returncode, where 0 stands for ok.

var hasSerChr = Sql.TrySelectValue(out ret, 
    "Select lagerdetail from art where artnr = '{0}'", 
    versandRow.artnr).ToInt(0);

Methods interrogating the db

public virtual int GetMitarbKuerzel(string currentMitarb)
{
    //Method used for getting the Employee Abbreviation
    return Sql.TrySelectValue("SELECT kuerzel FROM mitarb JOIN users ON mitarb.user_id = users.id WHERE mitarb.kuerzel = '{0}'", currentMitarb).ToStringNN();
}