예제 #1
0
        /// <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);
        }
예제 #2
0
        /// <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;
            }
        }
예제 #3
0
        /// <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);
        }
예제 #4
0
        /// <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));
        }
예제 #5
0
        void SetPragmatTable(string pragma, string value, out SQLlog msg)
        {
            bool openStart = ConnectionIsOpen;

            OpenConnection();
            ExecuteSQLcommand("PRAGMA " + pragma.Trim() + " = " + value, out msg);
            if (!openStart)
            {
                CloseConnection();
            }
        }
예제 #6
0
        /// <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());
        }
예제 #7
0
        DataTable PragmatTable(string pragma, out SQLlog msg)
        {
            bool openStart = ConnectionIsOpen;

            OpenConnection();
            DataTable rslt = ExecuteSQLdataTable("PRAGMA " + pragma.Trim(), out msg);

            if (!openStart)
            {
                CloseConnection();
            }
            return(rslt);
        }
예제 #8
0
        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);
            }
        }
예제 #9
0
 /// <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));
 }
예제 #10
0
 /// <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));
 }
예제 #11
0
 /// <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));
 }
예제 #12
0
 /// <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));
 }
예제 #13
0
 /// <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));
 }
예제 #14
0
 /// <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));
 }
예제 #15
0
 /// <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));
 }
예제 #16
0
 /// <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));
 }
예제 #17
0
 /// <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));
 }
예제 #18
0
 /// <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));
 }
예제 #19
0
        /// <summary>
        /// Execute a Vaccum operation (aka compact the database file)
        /// </summary>
        public void ExecuteVaccum()
        {
            SQLlog log = SQLlog.Empty;

            _SQLcommand("VACUUM", out log);
        }
예제 #20
0
 /// <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));
 }
예제 #21
0
 /// <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));;
 }
예제 #22
0
 /// <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));
 }
예제 #23
0
 /// <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));
 }