/// <summary>
/// Obtains a value corresponding to the SQL scalar request
/// </summary>
/// <param name="SQL">The SQL request to be executed</param>
/// <param name="msgErr">Advanced error message</param>
/// <remarks>Will automatically open and close a new connection if it is not opened</remarks>
/// <returns>The value of the scalar request</returns>
internal object _SQLscalar(string SQL, out SQLlog msgErr)
{
IsDisposed();
if (string.IsNullOrWhiteSpace(SQL))
{
throw new ArgumentNullException(nameof(SQL));
}
SQL = SQL.Trim();
msgErr = SQLlog.Empty;
object rslt = null;
try
{
using (SQLiteCommand cmd = CreatDBcommand(SQL))
rslt = cmd.ExecuteScalar(CommandBehavior.Default);
msgErr = new SQLlog(null, SQL);
}
catch (Exception ex)
{
rslt = null;
msgErr = new SQLlog(ex, SQL);
}
_logs.AddEntry(msgErr);
return(rslt);
}
/// <summary>
/// Create a new database with the Table containing the columns specify
/// </summary>
/// <param name="fileDB"></param>
/// <param name="tableName">Table to create</param>
/// <param name="columns">Columns of the Table</param>
/// <returns></returns>
static public SQLiteDataBase CreateDataBase(string fileDB, string tableName, SQLiteColumnsCollection columns)
{
try
{
if (File.Exists(fileDB))
{
throw new InvalidPathException("The target file \"" + fileDB + "\" already exist!");
}
SQLiteConnection.CreateFile(fileDB);
SQLiteDataBase db = new SQLiteDataBase(fileDB);
SQLlog err = SQLlog.Empty;
db.OpenConnection();
db._SQLcommand(SQLiteTable.SQL_AddTable(tableName, columns), out err);
if (!string.IsNullOrWhiteSpace(err.msgErr))
{
throw err.e;
}
db.CloseConnection();
return(db);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Add columns to the table
/// </summary>
/// <param name="tableName">Table to edit; Cannot be null or empty</param>
/// <param name="columns">Columns to add to the table; Cannot be null or empty</param>
/// <param name="msgErr"></param>
/// <returns></returns>
public int AddColumns(string tableName, SQLiteColumnsCollection columns, out SQLlog msgErr)
{
if (columns == null)
{
throw new ArgumentNullException(nameof(columns));
}
if (columns.Count == 0)
{
throw new ArgumentException("SQLiteColumns cannot be empty", nameof(columns));
}
msgErr = SQLlog.Empty;
int i = 0;
foreach (SQLiteColumn item in columns.Values)
{
AddColumn(tableName, item, out msgErr);
if (!msgErr.Succes)
{
return(i);
}
i++;
}
return(i);
}
/// <summary>
/// Obtains a value corresponding to the SQL scalar request
/// </summary>
/// <param name="SQL">The SQL request to be executed</param>
/// <param name="msgErr">Advanced error message</param>
/// <remarks>Will automatically open and close a new connection if it is not opened</remarks>
/// <returns>The value of the scalar request</returns>
public object ExecuteSQLscalar(string SQL, out SQLlog msgErr)
{
if (string.IsNullOrWhiteSpace(SQL))
{
throw new ArgumentNullException(nameof(SQL));
}
return(DataBase._SQLscalar(SQL, out msgErr));
}
void SetPragmatTable(string pragma, string value, out SQLlog msg)
{
bool openStart = ConnectionIsOpen;
OpenConnection();
ExecuteSQLcommand("PRAGMA " + pragma.Trim() + " = " + value, out msg);
if (!openStart)
{
CloseConnection();
}
}
/// <summary>
/// Get the Triggers of the database and the SQL command associate
/// </summary>
/// <returns></returns>
public SQLiteAssociateTable[] GetTriggers_SQL()
{
SQLlog msgErr = SQLlog.Empty;
List <SQLiteAssociateTable> lst = new List <SQLiteAssociateTable>();
DataTable dt = GetSQLite_master("type='trigger'", "tbl_name, name, sql", "tbl_name, name", out msgErr);
foreach (DataRow r in dt.Rows)
{
lst.Add(new SQLiteAssociateTable(r["name"].ToString(), r["tbl_name"].ToString(), r["sql"].ToString()));
}
return(lst.ToArray());
}
DataTable PragmatTable(string pragma, out SQLlog msg)
{
bool openStart = ConnectionIsOpen;
OpenConnection();
DataTable rslt = ExecuteSQLdataTable("PRAGMA " + pragma.Trim(), out msg);
if (!openStart)
{
CloseConnection();
}
return(rslt);
}
object PragmaValue(string pragma, out SQLlog msg)
{
DataTable dt = PragmatTable(pragma, out msg);
if (dt.Rows.Count > 0)
{
return(dt.Rows[0][0]);
}
else
{
return(null);
}
}
/// <summary>
/// Add one entries to the table
/// </summary>
/// <param name="tableName">Name of the table to updates; Cannot be null or empty</param>
/// <param name="values">Values to write in the table; Cannot be null or empty</param>
/// <param name="columns">Columns order of the values to write; null or empty for the default columns order</param>
/// <param name="msgErr"></param>
/// <returns></returns>
public int Insert(string tableName, string values, string columns, out SQLlog msgErr)
{
return(Insert(tableName, new string[] { values }, columns, out msgErr));
}
/// <summary>
/// Obtains the table with only the requested columns, in the desired order and with the values corresponding to the condition.
/// </summary>
/// <param name="tableName">Name of the table to get; Cannot be null or empty</param>
/// <param name="whereValues">null to retrieve all values</param>
/// <param name="onlyColumns">null for all columns</param>
/// <param name="orderColumns">null for the default order</param>
/// <param name="msgErr"></param>
/// <returns>The DataTable request</returns>
public DataTable GetTableWhere(string tableName, string whereValues, string onlyColumns, string orderColumns, out SQLlog msgErr)
{
return(ExecuteSQLdataTable(SQL_GetTableWhere(tableName, whereValues, onlyColumns, orderColumns), out msgErr));
}
/// <summary>
/// Obtains the table with only the requested columns, in the desired order and with the values corresponding to the condition.
/// </summary>
/// <param name="tableName">Name of the table to get; Cannot be null or empty</param>
/// <param name="whereValues">null to retrieve all values</param>
/// <param name="onlyColumns">null for all columns</param>
/// <param name="msgErr"></param>
/// <returns>The DataTable request</returns>
public DataTable GetTableWhere(string tableName, string whereValues, string onlyColumns, out SQLlog msgErr)
{
return(GetTableWhere(tableName, whereValues, onlyColumns, null, out msgErr));
}
/// <summary>
/// Obtains the table with only the requested columns, in the desired order and with the values corresponding to the condition.
/// </summary>
/// <param name="tableName">Name of the table to get; Cannot be null or empty</param>
/// <param name="msgErr"></param>
/// <returns>The DataTable request</returns>
public DataTable GetTable(string tableName, out SQLlog msgErr)
{
return(GetTable(tableName, null, null, out msgErr));
}
/// <summary>
/// Delete the table entries corresponding to the condition
/// </summary>
/// <param name="tableName">Name of the table to updates; Cannot be null or empty</param>
/// <param name="whereValues">Condition for delete row; Cannot be null or empty</param>
/// <param name="msgErr"></param>
/// <returns></returns>
public int Delete(string tableName, string whereValues, out SQLlog msgErr)
{
return(ExecuteSQLcommand(SQL_Delete(tableName, whereValues), out msgErr));
}
/// <summary>
/// Delete a column to the table
/// </summary>
/// <param name="tableName">Table to edit; Cannot be null or empty</param>
/// <param name="column">Columns to delete to the table; Cannot be null or empty</param>
/// <param name="msgErr"></param>
/// <returns></returns>
public int DeleteColumn(string tableName, string column, out SQLlog msgErr)
{
return(ExecuteSQLcommand(SQL_EditTable_DeleteColumn(tableName, column), out msgErr));
}
/// <summary>
/// Delete the table
/// </summary>
/// <param name="tableName">Table to delete; Cannot be null or empty</param>
/// <param name="msgErr"></param>
/// <returns></returns>
public int DeleteTable(string tableName, out SQLlog msgErr)
{
return(ExecuteSQLcommand(SQL_DeleteTable(tableName), out msgErr));
}
/// <summary>
/// Create new table with the specified columns
/// </summary>
/// <param name="tableName">Table to add; Cannot be null or empty</param>
/// <param name="columns">Columns assigned to the table; Cannot be null or empty</param>
/// <param name="msgErr"></param>
/// <returns></returns>
public int AddTable(string tableName, SQLiteColumnsCollection columns, out SQLlog msgErr)
{
return(ExecuteSQLcommand(SQL_AddTable(tableName, columns), out msgErr));
}
/// <summary>
/// Add multi entries to the table
/// </summary>
/// <param name="tableName">Name of the table to updates; Cannot be null or empty</param>
/// <param name="values">Values to write in the table; Cannot be null or empty</param>
/// <param name="msgErr"></param>
public int Insert(string tableName, string[] values, out SQLlog msgErr)
{
return(Insert(tableName, values, null, out msgErr));
}
/// <summary>
/// Add multi entries to the table
/// </summary>
/// <param name="tableName">Name of the table to updates; Cannot be null or empty</param>
/// <param name="values">Values to write in the table; Cannot be null or empty</param>
/// <param name="columns">Columns order of the values to write; null or empty for the default columns order</param>
/// <param name="msgErr"></param>
/// <returns></returns>
public int Insert(string tableName, string[] values, string columns, out SQLlog msgErr)
{
return(ExecuteSQLcommand(SQL_Insert(tableName, values, columns), out msgErr));
}
/// <summary>
/// Execute a Vaccum operation (aka compact the database file)
/// </summary>
public void ExecuteVaccum()
{
SQLlog log = SQLlog.Empty;
_SQLcommand("VACUUM", out log);
}
/// <summary>
/// Obtains a DataTable from 'sqlite_master'
/// </summary>
public DataTable GetSQLite_master(string whereValues, string onlyColumns, string orderColumns, out SQLlog msgErr)
{
IsDisposed();
return(ExecuteSQLdataTable(SQL_master(whereValues, onlyColumns, orderColumns), out msgErr));
}
/// <summary>
/// Add column to the table
/// </summary>
/// <param name="tableName">Table to edit; Cannot be null or empty</param>
/// <param name="column">Columns to add to the table; Cannot be null or empty</param>
/// <param name="msgErr"></param>
/// <returns></returns>
public int AddColumn(string tableName, SQLiteColumn column, out SQLlog msgErr)
{
return(ExecuteSQLcommand(SQL_EditTable_AddColumn(tableName, column), out msgErr));;
}
/// <summary>
/// Updates the table entries corresponding to the <see cref="SQLiteCaseValue"/>
/// </summary>
/// <param name="tableName">Name of the table to updates; Cannot be null or empty</param>
/// <param name="multiUpdate"><see cref="SQLiteCaseValue"/> to write in the table; Cannot be null or empty</param>
/// <param name="msgErr"></param>
/// <returns></returns>
/// <remarks>Generate a multiple UPDATE CASE for each entrie/column, combined in one request (no others simple way)</remarks>
public int Update(string tableName, SQLiteCaseValue[] multiUpdate, out SQLlog msgErr)
{
return(ExecuteSQLcommand(SQL_Update(tableName, multiUpdate), out msgErr));
}
/// <summary>
/// Modify the columns of a table
/// </summary>
/// <param name="tableName">Table to edit; Cannot be null or empty</param>
/// <param name="edtiting">Edit to be made; Cannot be null or empty</param>
/// <param name="msgErr"></param>
/// <returns></returns>
public int EditTable(string tableName, string edtiting, out SQLlog msgErr)
{
return(ExecuteSQLcommand(SQL_EditTable(tableName, edtiting), out msgErr));
}