/// <summary>
        /// Creates and opens a database.
        /// </summary>
        /// <param name="name">Database name.</param>
        /// <returns>Created database, or null if cannot be created.</returns>
        public override Database CreateDatabase(string name)
        {
            Database database = null;
            try
            {
                database = new Database(name, DEFAULT_DATABASE_OPTION_NEW, DEFAULT_DATABASE_OPTION_COMPRESS);
                SQLiteConnection connection = new SQLiteConnection(
                    "Data Source=" + Path.Combine(GetBasePath(),name) +
                    ";Version=" + DEFAULT_DATABASE_VERSION +
                    ";New=" + DEFAULT_DATABASE_OPTION_NEW +
                    ";Compress=" + DEFAULT_DATABASE_OPTION_COMPRESS + ";");

                connection.Open();
                StoreConnection(name, connection);
                StoreDatabase(name, database);
            }
            catch (Exception e)
            {
#if DEBUG
                Console.WriteLine("Exception creating database: " + e.Message);
#endif
                database = null;
            }

            return database;
        }
        /// <summary>
        /// Creates and opens a database.
        /// </summary>
        /// <param name="name">Database name.</param>
        /// <returns>Created database, or null if cannot be created.</returns>
        public override Database CreateDatabase(string name)
        {
            Database database = null;
            try
            {
                database = new Database(name, DEFAULT_DATABASE_OPTION_NEW, DEFAULT_DATABASE_OPTION_COMPRESS);
                string dbPath = Path.Combine(GetBasePath(), name);

                // Checks if a file already exists, if not creates the file.
                bool exists = File.Exists (dbPath);
                if (!exists)
                {
                    SqliteConnection.CreateFile (dbPath);
                }

                SqliteConnection connection = new SqliteConnection(
                    "Data Source=" + dbPath +
                    ",version=" + DEFAULT_DATABASE_VERSION);

                connection.Open();
                StoreConnection(name, connection);
                StoreDatabase(name, database);

            }
            catch (Exception e)
            {
                SystemLogger.Log(SystemLogger.Module.PLATFORM, "Exception creating database.", e);
                database = null;
            }

            return database;
        }
        /// <summary>
        /// Executes SQL statement, on the given database.
        /// </summary>
        /// <param name="db">Database reference.</param>
        /// <param name="statement">Statement to be executed.</param>
        /// <param name="statementParams">Replacement parameters to be applied, by order, on the statement string.</param>
        /// <returns></returns>
        public override bool ExecuteSQLStatement(Database db, string statement, string[] statementParams)
        {
            bool result = false;
            if (db != null)
            {
                DbConnection connection = GetConnection(db.Name);
                if (connection != null)
                {
                    string SQL = statement;
                    try
                    {
                        if (statementParams != null)
                        {
                            SQL = String.Format(SQL, statementParams);
                        }
                        SQLiteDataAdapter adapter = new SQLiteDataAdapter(SQL, (SQLiteConnection)connection);
                        adapter.Fill(new DataSet());
                        result = true;
                    }
                    catch (Exception e)
                    {
#if DEBUG
                        Console.WriteLine("Exception replacement strings on statement: " + e.Message);
#endif
                    }
                }
                else
                {
#if DEBUG
                    Console.WriteLine("SQLiteConnection not found for database name: " + db.Name);
#endif
                }
            }
            else
            {
#if DEBUG
                Console.WriteLine("Provided database is null");
#endif
            }
            return result;
        }
        /// <summary>
        /// Creates a table inside the given database, using the provided columns definitions.
        /// </summary>
        /// <param name="db">Database reference.</param>
        /// <param name="tableName">New table name.</param>
        /// <param name="columnDefinitions">Columns definitions (sqlite syntax).</param>
        /// <returns>True if successful table creation.</returns>
        public bool CreateTable(Database db, string tableName, string[] columnDefinitions)
        {
            bool result = false;
            if (db != null) {
                try {
                    DbConnection connection = GetConnection (db.Name);
                    if (connection != null) {
                        StringBuilder buffer = new StringBuilder ();
                        string columnDefs = "";
                        foreach (string columnDef in columnDefinitions) {
                            buffer.Append ((buffer.Length > 0 ? "," + columnDef : columnDef));
                        }
                        if (buffer.Length > 0) {
                            columnDefs = "(" + buffer.ToString () + ")";
                        }

                        string SQL = "CREATE TABLE " + tableName + columnDefs;
                        SystemLogger.Log (SystemLogger.Module .CORE, "SQL to execute: " + SQL);

                        DbCommand command = connection.CreateCommand ();
                        command.CommandText = SQL;
                        command.CommandType = CommandType.Text;
                        command.ExecuteNonQuery ();
                        result = true;
                    } else {
                        SystemLogger.Log (SystemLogger.Module .CORE, "SQLiteConnection not found for database name: " + db.Name);
                    }
                } catch (Exception e) {
                    SystemLogger.Log (SystemLogger.Module .CORE, "Exception creating table[" + tableName + "] on database[" + db.Name + "]", e);
                }
            } else {
                SystemLogger.Log (SystemLogger.Module .CORE, "Provided database is null");
            }

            return result;
        }
        /// <summary>
        /// Adds the given database to the dictionary.
        /// </summary>
        /// <param name="key">The key to apply when adding to dictionary for the given database.</param>
        /// <param name="connection">Database to be added.</param>
        protected void StoreDatabase(string key, Database database)
        {
            if (Databases == null) {
                Databases = new Dictionary<string, Database> ();
            }

            Databases [key] = database;
        }
        /// <summary>
        /// Returns a list of table names for the given database. 
        /// </summary>
        /// <param name="db">
        /// A <see cref="Database"/>
        /// </param>
        /// <returns>
        /// A <see cref="System.String[]"/>
        /// </returns>
        public string[] GetTableNames(Database db)
        {
            List<string> list = new List<string> ();

            if (db != null) {
                DbConnection connection = GetConnection (db.Name);
                if (connection != null) {
                    DataTable dt = connection.GetSchema ("TABLES");
                    foreach (DataRow row in dt.Rows) {
                        foreach (DataColumn col in dt.Columns) {
                            //Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
                            if (col.ColumnName == "TABLE_NAME") {
                                list.Add (row [col].ToString ());
                            }
                        }
                    }

                } else {
                    SystemLogger.Log (SystemLogger.Module.PLATFORM,
                                     "SQLiteConnection not found for database name: " + db.Name);
                }
            } else {
                SystemLogger.Log (SystemLogger.Module.PLATFORM, "Provided database is null");
            }
            return list.ToArray ();
        }
        /// <summary>
        /// Checks whether a table (given its name) exists on the given database.
        /// </summary>
        /// <param name="db">
        /// A <see cref="Database"/>
        /// </param>
        /// <param name="tableName">
        /// A <see cref="System.String"/>
        /// </param>
        /// <returns>
        /// A <see cref="System.Boolean"/>
        /// </returns>
        public bool Exists(Database db, string tableName)
        {
            bool exists = false;

            if (db != null) {
                DbConnection connection = GetConnection (db.Name);
                if (connection != null) {
                    DataTable dt = connection.GetSchema ("TABLES", new string[] {
                        null,
                        null,
                        tableName,
                        null
                    });
                    if (dt.Rows.Count > 0) {
                        exists = true;
                    }
                } else {
                    SystemLogger.Log (SystemLogger.Module.PLATFORM,
                                     "SQLiteConnection not found for database name: " + db.Name);
                }
            } else {
                SystemLogger.Log (SystemLogger.Module.PLATFORM, "Provided database is null");
            }
            return exists;
        }
 /// <summary>
 /// Checks whether the given database exists or not.
 /// </summary>
 /// <param name="db">Database to be checked.</param>
 /// <returns>True if database exists.</returns>
 public bool Exists(Database db)
 {
     return ExistsDatabase (db.Name);
 }
 public abstract bool ExecuteSQLTransaction(Database db, string[] statements, bool rollbackFlag);
        /// <summary>
        /// Executes a SQL Query that should return data.
        /// </summary>
        /// <param name="db">Database reference.</param>
        /// <param name="queryText">Query to be executed.</param>
        /// <param name="queryParams">Values to be replaced on query string.</param>
        /// <returns>Result set.</returns>
        public override IResultSet ExecuteSQLQuery(Database db, string queryText, string[] queryParams)
        {
            IResultSet resultSet = null;

            if (db != null)
            {
                DbConnection connection = GetConnection(db.Name);
                if (connection != null)
                {
                    string queryString = queryText;
                    try
                    {
                        if (queryParams != null)
                        {
                            queryString = String.Format(queryString, queryParams);
                        }
                        DataSet ds = new DataSet();
                        SQLiteDataAdapter adapter = new SQLiteDataAdapter(queryString, (SQLiteConnection)connection);
                        adapter.Fill(ds);
                        resultSet = new ResultSet(ds);
                    }
                    catch (Exception e)
                    {
#if DEBUG
                        Console.WriteLine("Exception replacement strings on statement: " + e.Message);
#endif
                    }
                }
                else
                {
#if DEBUG
                    Console.WriteLine("SQLiteConnection not found for database name: " + db.Name);
#endif
                }
            }
            else
            {
#if DEBUG
                Console.WriteLine("Provided database is null");
#endif
            }
            return resultSet;
        }
 public abstract IResultSet ExecuteSQLQuery(Database db, string queryText, string[] queryParams);
 /// <summary>
 /// Executes a SQL Query that should return data (without query replacement values).
 /// </summary>
 /// <param name="db">Database reference.</param>
 /// <param name="queryText">Query to be executed.</param>
 /// <returns>Result set.</returns>
 public IResultSet ExecuteSQLQuery(Database db, string queryText)
 {
     return ExecuteSQLQuery (db, queryText, null);
 }
        /// <summary>
        /// Deletes table - given the table name - on the given database.
        /// </summary>
        /// <param name="db">Database reference.</param>
        /// <param name="tableName">Table name.</param>
        /// <returns>True on successful deletion.</returns>
        public bool DeleteTable(Database db, string tableName)
        {
            bool result = false;
            if (db != null) {
                try {
                    DbConnection connection = GetConnection (db.Name);
                    if (connection != null) {
                        string SQL = "DROP TABLE " + tableName;
                        SystemLogger.Log (SystemLogger.Module .CORE, "SQL to execute: " + SQL);

                        DbCommand command = connection.CreateCommand ();
                        command.CommandText = SQL;
                        command.CommandType = CommandType.Text;
                        command.ExecuteNonQuery ();
                        result = true;
                    } else {
                        SystemLogger.Log (SystemLogger.Module .CORE, "SQLiteConnection not found for database name: " + db.Name);
                    }
                } catch (Exception e) {
                    SystemLogger.Log (SystemLogger.Module .CORE, "Exception deleting table[" + tableName + "] on database[" + db.Name + "]", e);
                }
            } else {
                SystemLogger.Log (SystemLogger.Module .CORE, "Provided database is null");
            }

            return result;
        }
        /// <summary>
        /// Deletes the given database.
        /// </summary>
        /// <param name="db">Database reference to be deleted.</param>
        /// <returns>True on successful deletion.</returns>
        public bool DeleteDatabase(Database db)
        {
            bool result = false;
            if (db != null) {
                DbConnection connection = GetConnection (db.Name);
                if (connection != null) {
                    connection.Close ();
                    RemoveConnection (db.Name);
                    RemoveDatabase (db.Name);
                    DeleteDatabaseFile (db.Name);
                    connection = null;
                    result = true;
                }
            }

            return result;
        }
Example #15
0
        /// <summary>
        ///  Executing SQL Statement 
        ///  (using Mono/monotouch apis for SqliteConnection and SqliteDataAdapter classes).
        /// </summary>
        /// <param name="db">
        /// A <see cref="Database"/>
        /// </param>
        /// <param name="statement">
        /// A <see cref="System.String"/>
        /// </param>
        /// <param name="statementParams">
        /// A <see cref="System.String[]"/>
        /// </param>
        /// <returns>
        /// A <see cref="System.Boolean"/>
        /// </returns>
        public override bool ExecuteSQLStatement(Database db, string statement, string[] statementParams)
        {
            bool result = false;
            if (db != null)
            {
                DbConnection connection = GetConnection(db.Name);
                if (connection != null)
                {
                    string SQL = statement;
                    try
                    {
                        if (statementParams != null)
                        {
                            SQL = String.Format(SQL, statementParams);
                        }

                        SqliteDataAdapter adapter = new SqliteDataAdapter(SQL, (SqliteConnection)connection);
                        adapter.Fill(new DataSet());
                        result = true;
                    }
                    catch (Exception e)
                    {
                        SystemLogger.Log(SystemLogger.Module.PLATFORM, "Exception replacement strings on statement.", e);
                    }
                }
                else
                {
                    SystemLogger.Log(SystemLogger.Module.PLATFORM, "SQLiteConnection not found for database name: " + db.Name);
                }
            }
            else
            {
                SystemLogger.Log(SystemLogger.Module.PLATFORM, "Provided database is null");
            }
            return result;
        }
Example #16
0
        /// <summary>
        /// Executing SQL Transaction 
        /// (using Mono/monotouch apis for SqliteConnection, SqliteTransaction  and SqliteCommand classes).
        /// </summary>
        /// <param name="db">
        /// A <see cref="Database"/>
        /// </param>
        /// <param name="statements">
        /// A <see cref="System.String[]"/>
        /// </param>
        /// <param name="rollbackFlag">
        /// A <see cref="System.Boolean"/>
        /// </param>
        /// <returns>
        /// A <see cref="System.Boolean"/>
        /// </returns>
        public override bool ExecuteSQLTransaction(Database db, string[] statements, bool rollbackFlag)
        {
            bool result = false;
            if (db != null)
            {
                SqliteConnection connection = (SqliteConnection)GetConnection(db.Name);
                if (connection != null)
                {
                    // Start a local transaction.
                    SqliteTransaction transaction = connection.BeginTransaction();
                    // Enlist a command in the current transaction.
                    SqliteCommand command = connection.CreateCommand();
                    command.Transaction = transaction;

                    try
                    {
                        // Execute separate commands.
                        foreach (string statement in statements)
                        {
                            command.CommandText = statement;
                            command.ExecuteNonQuery();
                        }

                        // Commit the transaction.
                        transaction.Commit();
                        result = true;
                    }
                    catch (Exception ex)
                    {
                        // Handle the exception if the transaction fails to commit.
                        SystemLogger.Log(SystemLogger.Module.PLATFORM, "SQL Transaction fails to commit.", ex);
                        try
                        {
                            if (rollbackFlag)
                            {
                                // Attempt to roll back the transaction.
                                transaction.Rollback();
                            }
                            else
                            {
                                // No rollback specified.
                                SystemLogger.Log(SystemLogger.Module.PLATFORM, "No rollback specified.");

                                // Commit the transaction (sucess statements, previous to exception, are committed)
                                transaction.Commit();
                            }
                        }
                        catch (Exception exRollback)
                        {
                            // Throws an InvalidOperationException if the connection
                            // is closed or the transaction has already been rolled
                            // back on the server.
                            SystemLogger.Log(SystemLogger.Module.PLATFORM, "Error when doing Rollback on SQL Transaction.", exRollback);
                        }
                    }
                }
                else
                {
                    SystemLogger.Log(SystemLogger.Module.PLATFORM, "SQLiteConnection not found for database name: " + db.Name);
                }
            }
            else
            {
                SystemLogger.Log(SystemLogger.Module.PLATFORM, "Provided database is null");
            }
            return result;
        }
 /// <summary>
 /// Executes an SQL statements without parameter replacements.
 /// </summary>
 /// <param name="db">Database reference.</param>
 /// <param name="statement">Statement to be executed.</param>
 /// <returns>True on successful execution.</returns>
 public bool ExecuteSQLStatement(Database db, string statement)
 {
     return ExecuteSQLStatement (db, statement, null);
 }
Example #18
0
        /// <summary>
        /// 
        /// </summary>
        /// <param name="db">
        /// A <see cref="Database"/>
        /// </param>
        /// <param name="queryText">
        /// A <see cref="System.String"/>
        /// </param>
        /// <param name="queryParams">
        /// A <see cref="System.String[]"/>
        /// </param>
        /// <returns>
        /// A <see cref="IResultSet"/>
        /// </returns>
        public override IResultSet ExecuteSQLQuery(Database db, string queryText, string[] queryParams)
        {
            IResultSet resultSet = null;

            if (db != null)
            {
                DbConnection connection = GetConnection(db.Name);
                if (connection != null)
                {
                    string queryString = queryText;
                    try
                    {
                        if (queryParams != null)
                        {
                            queryString = String.Format(queryString, queryParams);
                        }
                        DataSet ds = new DataSet();
                        SqliteDataAdapter adapter = new SqliteDataAdapter(queryString, (SqliteConnection)connection);
                        adapter.Fill(ds);
                        resultSet = new ResultSet(ds);
                    }
                    catch (Exception e)
                    {
                        SystemLogger.Log(SystemLogger.Module.PLATFORM, "Exception replacement strings on statement.", e);
                    }
                }
                else
                {
                    SystemLogger.Log(SystemLogger.Module.PLATFORM, "SQLiteConnection not found for database name: " + db.Name);
                }
            }
            else
            {
                SystemLogger.Log(SystemLogger.Module.PLATFORM, "Provided database is null");
            }
            return resultSet;
        }
 public abstract bool ExecuteSQLStatement(Database db, string statement, string[] statementParams);