SQLite implentation of DbConnection.
The ConnectionString property of the SqliteConnection class can contain the following parameter(s), delimited with a semi-colon: Parameter Values Required Default Data Source {filename} Y Version 3 N 3 UseUTF16Encoding True
False
N False
DateTimeFormat Ticks - Use DateTime.Ticks
ISO8601 - Use ISO8601 DateTime format
N ISO8601
BinaryGUID True - Store GUID columns in binary form
False - Store GUID columns as text
N True
Cache Size {size in bytes} N 2000 Synchronous Normal - Normal file flushing behavior
Full - Full flushing after all writes
Off - Underlying OS flushes I/O's
N Normal
Page Size {size in bytes} N 1024 Password {password} N Enlist Y - Automatically enlist in distributed transactions
N - No automatic enlistment
N Y
Pooling True - Use connection pooling
False - Do not use connection pooling
N False
FailIfMissing True - Don't create the database if it does not exist, throw an error instead
False - Automatically create the database if it does not exist
N False
Max Page Count {size in pages} - Limits the maximum number of pages (limits the size) of the database N 0 Legacy Format True - Use the more compatible legacy 3.x database format
False - Use the newer 3.3x database format which compresses numbers more effectively
N False
Default Timeout {time in seconds}
The default command timeout
N 30
Journal Mode Delete - Delete the journal file after a commit
Persist - Zero out and leave the journal file on disk after a commit
Off - Disable the rollback journal entirely
N Delete
Read Only True - Open the database for read only access
False - Open the database for normal read/write access
N False
Max Pool Size The maximum number of connections for the given connection string that can be in the connection pool N 100 Default IsolationLevel The default transaciton isolation level N Serializable
Inheritance: System.Data.Common.DbConnection, ICloneable
        public LibraryDatabaseManager(String dbFolderPath)
        {
            DatabaseFile = String.Format(dbFolderPath + "{0}Library.db",
                                         Path.DirectorySeparatorChar);
            Connection = new SqliteConnection (
                "Data Source = " + LibraryDatabaseManager.DatabaseFile +
                "; Version = 3;");

            bool exists = File.Exists (LibraryDatabaseManager.DatabaseFile);
            if (!exists) {
                SqliteConnection.CreateFile (LibraryDatabaseManager.DatabaseFile);
            }

            Connection.Open ();
            if (!exists) {
                using (SqliteCommand command = new SqliteCommand (Connection)) {
                    command.CommandText =
                        "CREATE TABLE Books (" +
                            "BookID INTEGER PRIMARY KEY NOT NULL, " +
                            "BookTitle TEXT, " +
                            "BookAuthor TEXT, " +
                            "BookGenre TEXT, " +
                            "BookPublishedYear INTEGER, " +
                            "BookPath TEXT);";
                    command.ExecuteNonQuery();
                }
            }
        }
Example #2
1
    public virtual bool CreateDatabase( string sFile, bool bKeepOpen = false )
    {
        myDatabase = new SqliteConnection();

        try {
            if( System.IO.File.Exists(sFile) ) {
                if( bKeepOpen == true ) {
                    myDatabase.ConnectionString = "Data Source=" + sFile + ";";
                    myDatabase.Open();
                }

                return false;
            }

            myDatabase.ConnectionString = "Data Source=" + sFile + ";";
            myDatabase.Open();

            if( bKeepOpen == false ) {
                myDatabase.Close();
                myDatabase.Dispose();
            }

            return true;
        } catch {
            return false;
        }
    }
Example #3
0
    public void GotoColorfulScene()
    {
        SceneName = "Colorfull";
        playGameUI.SetActive(true);
        string connection = "URI=file:" + Application.dataPath + "/StreamingAssets/User.db";

        Debug.Log(connection);
        IDbConnection dbcon = new Mono.Data.Sqlite.SqliteConnection(connection);

        dbcon.Open();

        IDbCommand  cmnd_read = dbcon.CreateCommand();
        IDataReader reader;
        string      query = "SELECT * FROM my_user";

        cmnd_read.CommandText = query;
        reader = cmnd_read.ExecuteReader();
        int index = 0;

        while (reader.Read())
        {
            //Debug.Log(reader[0].ToString());
            GameObject tempGO = Instantiate(rowSample) as GameObject;
            tempGO.transform.Find("Name").GetComponent <Text>().text            = reader[1].ToString();
            tempGO.transform.Find("ID").GetComponent <Text>().text              = reader[0].ToString();
            tempGO.transform.GetComponent <PlayGame_Button_Control>().id        = reader[0].ToString();
            tempGO.transform.GetComponent <PlayGame_Button_Control>().sceneName = "Colorfull";
            tempGO.transform.SetParent(rowParent.transform);
            data.Add(tempGO);
        }
        dbcon.Close();
        reader.Close();
        cmnd_read.Dispose();
    }
Example #4
0
    public void DataBaseRead(String query) //DB 읽어오기 - 인자로 쿼리문을 받는다.
    {
        IDbConnection dbConnection = new SqliteConnection(GetDBFilePath());

        dbConnection.Open();           // DB 열기
        IDbCommand dbCommand = dbConnection.CreateCommand();

        dbCommand.CommandText = query;                      // 쿼리 입력
        IDataReader dataReader = dbCommand.ExecuteReader(); // 쿼리 실행

        while (dataReader.Read())                           // 쿼리로 돌아온 레코드 읽기
        {
            Debug.Log(dataReader.GetInt32(5));              // 5번 점수 필드 읽기
            int maxScore = dataReader.GetInt32(5);
            MaxScore_Text.text = "최고점수 : " + maxScore;
            break; // 내림차순 정렬이므로 처음에 한 번만 레코드값을 가져오면 된다.
        }

        dataReader.Dispose();  // 생성순서와 반대로 닫아줍니다.
        dataReader = null;
        dbCommand.Dispose();
        dbCommand = null;
        // DB에는 1개의 쓰레드만이 접근할 수 있고 동시에 접근시 에러가 발생한다. 그래서 Open과 Close는 같이 써야한다.
        dbConnection.Close();
        dbConnection = null;
    }
Example #5
0
    public void DatabaseSQLAdd(string query) //삽입이라고 썼지만 삭제도 가능
    {
        IDbConnection dbConnection = new SqliteConnection(GetDBFilePath());

        dbConnection.Open();            // DB 열기
        IDbCommand dbCommand = dbConnection.CreateCommand();

        dbCommand.CommandText = query;  // 쿼리 입력
        try
        {
            dbCommand.ExecuteNonQuery();    // 쿼리 실행
        }

        catch (Exception e)
        {
            Debug.LogError(e);
            test.text = e.ToString();

            // 빌드시 에러확인을 위해 넣음 결과패널에 Game Over대신 에러 나오게 하는 코드
            test_Result.GetComponent <Text>().fontSize = 30; // 폰트 사이즈 작게 변경 - 이유 : 오류가 길어서
            test_Result.text = e.ToString();
        }

        dbCommand.Dispose();
        dbCommand = null;
        dbConnection.Close();
        dbConnection = null;
        Debug.Log("데이터 삽입 완료");
        test.text = "결과 데이터 삽입 완료";
    }
Example #6
0
        public static void Main(string[] args)
        {
            var bookmarks = new Bookmarks();

            using (var dbConn = new Mono.Data.Sqlite.SqliteConnection("Data Source=/mnt/ramdisk/test.db"))
            {
                dbConn.Open();

                var cmdGetGroups = dbConn.CreateCommand();
                cmdGetGroups.CommandText = "SELECT gid, name, IFNULL(pid,0) FROM rel JOIN groups ON rel.gid=groups.rowid WHERE depth=1";
                using (IDataReader groups = cmdGetGroups.ExecuteReader())
                {
                    ApplyRow(groups, (g) => bookmarks.GroupAdd(g.GetInt32(0), new Group {
                        Id = g.GetInt32(0), Name = g.GetString(1), Pid = g.GetInt32(2)
                    }));
                }
                var cmdGetRows = dbConn.CreateCommand();
                cmdGetRows.CommandText = "SELECT IFNULL(gid,0), key, value FROM data";
                using (IDataReader rows = cmdGetRows.ExecuteReader())
                {
                    ApplyRow(rows, (r) => bookmarks.LinkAdd(r.GetInt32(0), new LinkItem {
                        Title = r.GetString(1), Target = r.GetString(2)
                    }));
                }
            }
            var document = new XDocument();

            document.Add(bookmarks.ToXml());
            document.Save("test.xml");
        }
Example #7
0
        /**
         * Constructor will try and open the database if none exsists it will create one
         * In addition it will create new tables if non exsist.
         */
        public SqlWrapper(GameObjectList objectList)
        {
            gameObjectList = objectList;



            //try and open database, if failed make one!
            Database = new sqliteConnection("Data Source=Dungeon" + ";Version=3;FailIfMissing=True");
            try
            {
                Database.Open();
            }
            catch
            {
                Console.WriteLine("Open existing DB failed: So creating one");
                sqliteConnection.CreateFile("Dungeon");
                Database = new sqliteConnection("Data Source=Dungeon" + ";Version=3;FailIfMissing=True");
                Database.Open();
            }

            //do the disable roll back thingy
            //Not sure what this does, fixed a problem, found it on stack overflow
            String        disableRollback = "PRAGMA journal_mode = OFF";
            sqliteCommand cmd             = new sqliteCommand();

            cmd.Connection  = Database;
            cmd.CommandText = disableRollback;
            cmd.ExecuteNonQuery();

            //create tables
            new sqliteCommand(createTable + itemTableName + itemColumns, Database).ExecuteNonQuery();

            new sqliteCommand(createTable + playerTableName + playerColumns, Database).ExecuteNonQuery();
        }
Example #8
0
        public static bool Init()
        {
            try
            {
                m_cards = new Dictionary <int, CardData>();

                string currentPath = System.Reflection.Assembly.GetExecutingAssembly().Location;
                currentPath = Path.GetDirectoryName(currentPath) ?? "";
                string absolutePath = Path.Combine(currentPath, "Content/cards.cdb");

                if (!File.Exists(absolutePath))
                {
                    return(false);
                }

                using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + absolutePath))
                {
                    connection.Open();

                    const string select =
                        "SELECT datas.id, alias, type, level, race, attribute, atk, def, name, desc " +
                        "FROM datas INNER JOIN texts ON datas.id = texts.id";

                    SQLiteCommand command = new SQLiteCommand(select, connection);
                    using (SQLiteDataReader reader = command.ExecuteReader())
                        InitCards(reader);
                    command.Dispose();
                }
                return(true);
            }
            catch (Exception)
            {
                return(false);
            }
        }
Example #9
0
 private void connectDatabase()
 {
     foundDB = File.Exists(filename);
     if (!foundDB)
         SqliteConnection.CreateFile(filename);
     database = new SqliteConnection("Data Source=" + filename);
 }
Example #10
0
    // Use this for initialization
    void Start()
    {
        string conn = "URI=file:" + Application.dataPath + "/sqlite_userinfo";

        IDbConnection dbconn = new SqliteConnection(conn);
        dbconn.Open();
        IDbCommand dbcmd = dbconn.CreateCommand();

        string query = "SELECT idx, name, email FROM userinfo";
        dbcmd.CommandText = query;
        IDataReader reader = dbcmd.ExecuteReader();

        while(reader.Read())
        {
            int idx = reader.GetInt32(0);
            string name = reader.GetString(1);
            string email = reader.GetString(2);

            Debug.Log("idx : " + idx + ", name : " + name + ", email : " + email);
        }

        reader.Close();
        reader = null;
        dbcmd.Dispose();
        dbcmd = null;
        dbconn.Close();
        dbconn = null;
    }
Example #11
0
 public string proDataExc(string[] strTT)
 {
     try
     {
         if (strTT.Length < 1) { return "No SQL"; }
         string DatabaseName = "PUB.db3";
         string documents = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
         string db = System.IO.Path.Combine(documents, DatabaseName);
         var conn = new SqliteConnection("Data Source=" + db);
         var sqlitecmd = conn.CreateCommand();
         conn.Open();
         sqlitecmd.CommandType = CommandType.Text;
         for (int j = 0; j < strTT.Length; j++)
         {
             if (strTT[j] == "") { continue; }
             sqlitecmd.CommandText = strTT[j];
             sqlitecmd.ExecuteNonQuery();
         }
         conn.Close();
         conn.Dispose();
         return "";
     }
     catch (Exception Ex)
     {
         return Ex.ToString();
     }
 }
Example #12
0
        public bool ExcuteTransaction(string sql)
        {
            var cmds = sql.Split(';');
            using (SqliteConnection conn = new SqliteConnection(this.SqlConfig.ConnectionString))
            {
                conn.Open();
                SqliteCommand cmd = new SqliteCommand(conn);

                SqliteTransaction tran = conn.BeginTransaction();
                try
                {
                    foreach (var cmdSql in cmds)
                    {
                        cmd.CommandText = cmdSql;
                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                    conn.Close();
                    return true;
                }
                catch (Exception e)
                {
                    tran.Rollback();
                    conn.Close();
                    throw new Exception(e.Message + "  sql:" + sql);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// Returns the category for the specified id.
        /// </summary>
        /// <param name="categoryId">Category identifier.</param>
        public Category GetCategory(int categoryId)
        {
            Category category = null;

            using (var connection = new SqliteConnection("Data Source=" + dbPath))
            using (var query = new SqliteCommand("SELECT * FROM Categories WHERE id = @categoryId", connection))
            {
                query.Parameters.AddWithValue("@categoryId", categoryId);

                connection.Open();

                var reader = query.ExecuteReader(CommandBehavior.CloseConnection);

                while (reader.Read())
                {
                    category = new Category();
                    category.Id = int.Parse(reader["id"].ToString());
                    category.Name = reader ["name"].ToString();
                }

                reader.Close();
            }

            return category;
        }
        /// <summary>
        /// Returns all categories.
        /// </summary>
        public IEnumerable<Category> GetAllCategories()
        {
            var categories = new List<Category>();

            using (var connection = new SqliteConnection("Data Source=" + dbPath))
            using (var query = new SqliteCommand("SELECT * FROM Categories", connection))
            {
                connection.Open();

                var reader = query.ExecuteReader(CommandBehavior.CloseConnection);

                while (reader.Read())
                {
                    var category = new Category();
                    category.Id = int.Parse(reader["id"].ToString());
                    category.Name = reader ["name"].ToString();

                    categories.Add(category);
                }

                reader.Close();
            }

            return categories;
        }
		public ComboBoxDataSource (SqliteConnection conn, string tableName, string displayField)
		{
			// Initialize
			this.Conn = conn;
			this.TableName = tableName;
			this.DisplayField = displayField;
		}
Example #16
0
        public void DeleteAllData()
        {
            Mono.Data.Sqlite.SqliteConnection  conn  = null;
            Mono.Data.Sqlite.SqliteTransaction trans = null;

            try
            {
                conn  = openDbConnection();
                trans = conn.BeginTransaction();

                this.executeNonqueryCommand("DELETE FROM [Athlete]", conn, trans);
                this.executeNonqueryCommand("DELETE FROM [Result]", conn, trans);
                this.executeNonqueryCommand("DELETE FROM [Score]", conn, trans);

                trans.Commit();
            }
            catch (Exception exc)
            {
                if (trans != null)
                {
                    trans.Rollback();
                }
                throw exc;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }

            new DatabaseSetup().CreateEmptyAthleteRecord();
        }
 public SQLiteMonoTransformationProvider(Dialect dialect, string connectionString)
     : base(dialect, connectionString)
 {
     _connection = new SqliteConnection(_connectionString);
     _connection.ConnectionString = _connectionString;
     _connection.Open();
 }
Example #18
0
        public static void Init()
        {
            try
            {
                _cards = new Dictionary<int, CardData>();

                string currentPath = Assembly.GetExecutingAssembly().Location;
                currentPath = Path.GetDirectoryName(currentPath) ?? "";
                string absolutePath = Path.Combine(currentPath, "cards.cdb");

                if (!File.Exists(absolutePath))
                {
                    throw new Exception("Could not find the cards database.");
                }

                using (SqliteConnection connection = new SqliteConnection("Data Source=" + absolutePath))
                {
                    connection.Open();

                    const string select =
                        "SELECT datas.id, alias, type, level, race, attribute, atk, def, name, desc " +
                        "FROM datas INNER JOIN texts ON datas.id = texts.id";

                    using (SqliteCommand command = new SqliteCommand(select, connection))
                    using (SqliteDataReader reader = command.ExecuteReader())
                        InitCards(reader);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Could not initialize the cards database. Check the inner exception for more details.", ex);
            }
        }
Example #19
0
        private SqliteConnection openDbConnection()
        {
            var conn = new Mono.Data.Sqlite.SqliteConnection("Data Source=" + this.getDbFileName());

            conn.Open();
            return(conn);
        }
Example #20
0
File: folios.cs Project: BCMX/test
        //constructor
        static folios()
        {
            try {
                //conexion a DB
                var connection = new SqliteConnection(dbSource);
                connection.Open();

                //crear query
                var query = connection.CreateCommand();
                query.CommandText = "select consecutivo from folios where fecha='" + fecha + "'";

                //inicia en el folio de la DB
                generarConsecutivo=Convert.ToInt16(query.ExecuteScalar());

                //si el valor es 0, entonces creamos un nuevo renglon para ese dia
                //esto del 0 no necesariamente funciona en VistaDB
                if(generarConsecutivo.Equals(0))
                {
                    generarConsecutivo=1;
                    query = connection.CreateCommand();
                    query.CommandText = "insert into folios (fecha,consecutivo) values ('" + fecha + "'," + generarConsecutivo.ToString() + ")";
                    query.ExecuteNonQuery();
                }

                //cerrar conexion a DB
                connection.Close();

            } catch(Exception ex) {
                //en caso de error en la base de datos empezamos en 1
                generarConsecutivo=1;
            }
        }
Example #21
0
 public void Close()
 {
     if (conn != null) {
         conn.Close ();
         conn = null;
     }
 }
Example #22
0
        public void UpdateAllScores(List <Score> scores)
        {
            Mono.Data.Sqlite.SqliteConnection  conn  = null;
            Mono.Data.Sqlite.SqliteTransaction trans = null;
            try
            {
                conn  = openDbConnection();
                trans = conn.BeginTransaction();

                var command = conn.CreateCommand();
                command.Transaction = trans;
                command.CommandText = "DELETE FROM [Score]";
                command.ExecuteNonQuery();
                command.Dispose();

                foreach (var score in scores)
                {
                    this.addScore(conn, trans, score);
                }

                trans.Commit();
            }
            catch (Exception exc)
            {
                trans.Rollback();
                throw exc;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
Example #23
0
        public static IDbConnection Open(string dbPath, bool create)
        {
            if (dbPath == null)
                throw new ArgumentNullException("dbPath");

            if (create) {
                if (File.Exists(dbPath))
                    File.Delete(dbPath);
            #if WIN32
                SQLiteConnection.CreateFile(dbPath);
            #else
                SqliteConnection.CreateFile(dbPath);
            #endif
            } else {
                if (!File.Exists(dbPath))
                    throw new FileNotFoundException(string.Format("Database '{0}' not found", dbPath));
            }

            IDbConnection conn;
            string connStr = string.Format("Data Source={0}", dbPath);
            #if WIN32
            conn = new SQLiteConnection(connStr);
            #else
            conn = new SqliteConnection(connStr);
            #endif
            conn.Open();
            return conn;
        }
Example #24
0
        public void GetSnookerBests(int athleteID, out int?bestBreakPoints, out int?bestBreakBalls, out int?bestFrameScore)
        {
            bestBreakPoints = null;
            bestBreakBalls  = null;
            bestFrameScore  = null;

            Mono.Data.Sqlite.SqliteConnection conn = null;
            try
            {
                conn = openDbConnection();

                string sql  = "SELECT Count FROM [Result] WHERE AthleteID=" + athleteID + " ORDER BY Count DESC";
                var    obj1 = this.executeScalarCommand(sql, conn, null);
                if (obj1 != null && obj1 != DBNull.Value)
                {
                    bestBreakPoints = (int)obj1;
                }

                sql = "SELECT Count2 FROM [Result] WHERE AthleteID=" + athleteID + " ORDER BY Count2 DESC";
                var obj2 = this.executeScalarCommand(sql, conn, null);
                if (obj2 != null && obj2 != DBNull.Value)
                {
                    bestBreakBalls = (int)obj2;
                }

                bestFrameScore = null;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
        public void Initialise(string connectionString)
        {
            m_connectionString = connectionString;

            m_log.Info("[ESTATE DB]: Sqlite - connecting: "+m_connectionString);

            m_connection = new SqliteConnection(m_connectionString);
            m_connection.Open();

            Assembly assem = GetType().Assembly;
            Migration m = new Migration(m_connection, assem, "EstateStore");
            m.Update();

            //m_connection.Close();
           // m_connection.Open();

            Type t = typeof(EstateSettings);
            m_Fields = t.GetFields(BindingFlags.NonPublic |
                                   BindingFlags.Instance |
                                   BindingFlags.DeclaredOnly);

            foreach (FieldInfo f in m_Fields)
                if (f.Name.Substring(0, 2) == "m_")
                    m_FieldMap[f.Name.Substring(2)] = f;
        }
Example #26
0
        public List <int> GetAthleteNamesFromResults(bool includeDeleted)
        {
            Mono.Data.Sqlite.SqliteConnection conn = null;
            try
            {
                conn = openDbConnection();

                string sql = "SELECT AthleteID FROM [Result]";
                if (includeDeleted == false)
                {
                    sql += " WHERE IsDeleted=0";
                }
                sql += " ORDER BY [Date] DESC";

                List <int> ids = new List <int>();

                var command = new SqliteCommand(sql, conn);
                var reader  = command.ExecuteReader();
                while (reader.Read())
                {
                    int id = (int)reader["AthleteID"];
                    ids.Add(id);
                }

                return(ids);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
Example #27
0
    void Start ()
    {
        string connectionString = "URI=file:" + Application.dataPath + "/GameMaster"; //Path to database.
        IDbConnection dbcon = new SqliteConnection(connectionString) as IDbConnection;
        dbcon.Open(); //Open connection to the database.

        IDbCommand dbcmd = dbcon.CreateCommand();
        dbcmd.CommandText = "SELECT firstname, lastname " + "FROM addressbook";

        IDataReader reader = dbcmd.ExecuteReader();
        while(reader.Read())
        {
            string FirstName = reader.GetString (0);
            string LastName = reader.GetString (1);
            Console.WriteLine("Name: " + FirstName + " " + LastName);
            UnityEngine.Debug.LogWarning("Name: " + FirstName + " " + LastName);
        }
        reader.Close();
        reader = null;

        dbcmd.Dispose();
        dbcmd = null;

        dbcon.Close();
        dbcon = null;
    }
Example #28
0
        public void UpdateAllAthleteResults(int athleteID, List <Result> results)
        {
            Mono.Data.Sqlite.SqliteConnection  conn  = null;
            Mono.Data.Sqlite.SqliteTransaction trans = null;
            try
            {
                conn  = openDbConnection();
                trans = conn.BeginTransaction();

                var command = conn.CreateCommand();
                command.Transaction = trans;
                command.CommandText = "DELETE FROM [Result] WHERE AthleteID=" + athleteID;
                command.ExecuteNonQuery();
                command.Dispose();

                foreach (var result in results)
                {
                    result.AthleteID = athleteID;
                    addResult(conn, trans, result);
                }

                trans.Commit();
            }
            catch
            {
                trans.Rollback();
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
Example #29
0
        public static bool TestVersion(string version, SqliteConnection MainSQLiteConnection)
        {
            try
            {

                using (var contents = SQLiteUtilities.MainSQLiteConnection.CreateCommand())
                {
                    contents.CommandText = "SELECT Nr FROM [Version]";
                    var r = contents.ExecuteReader();
                    while (r.Read())
                    {
                        Console.WriteLine("Version: " + r["Nr"]);
                        if ( r["Nr"].ToString() == version)
                            return true;
                        else
                            return false;

                    }
                }
            }
            catch (Exception ex)
            {
                DataAccessLayer.ExceptionWriter.WriteLogFile(ex);
            }
            return false;
        }
		/// <summary>
		/// Initializes a new instance of the <see cref="Tasky.DL.TaskDatabase"/> TaskDatabase. 
		/// if the database doesn't exist, it will create the database and all the tables.
		/// </summary>
        public ADODatabase(string dbPath) 
		{
			var output = "";
			path = dbPath;
			// create the tables
			bool exists = File.Exists (dbPath);

			if (!exists) {
				connection = new SqliteConnection ("Data Source=" + dbPath);

				connection.Open ();
				var commands = new[] {
					"CREATE TABLE [Items] (_id INTEGER PRIMARY KEY ASC, Name NTEXT, Notes NTEXT, Done INTEGER);"
				};
				foreach (var command in commands) {
					using (var c = connection.CreateCommand ()) {
						c.CommandText = command;
						var i = c.ExecuteNonQuery ();
					}
				}
			} else {
				// already exists, do nothing. 
			}
			Console.WriteLine (output);
		}
Example #31
0
 public void CreateDataBase(string dbPath, string pwd)
 {
     if (!File.Exists(dbPath))
     {
         SqliteConnection.CreateFile(dbPath);
         if (string.IsNullOrEmpty(pwd))
         {
             using (SqliteConnection conn = new SqliteConnection("Data Source=" + dbPath))
             {
                 try
                 {
                     conn.SetPassword(pwd);
                 }
                 catch
                 {
                     conn.Close();
                     throw;
                 }
                 finally
                 {
                     conn.Close();
                 }
             }
         }
     }
     else
         throw new Exception("已经存在名叫:" + dbPath + "的数据库!");
 }
Example #32
0
        public IDbConnection Get()
        {
            IDbConnection dbConnection = new SQL.SqliteConnection(connectionString);

            dbConnection.Open();
            return(dbConnection);
        }
Example #33
0
    public void Open(string filePath)
    {
      if (_command != null)
        _command.Dispose();

      if (_conn != null)
        _conn.Close();

      try
      {
        ConnectionStringBuilder connstr = new ConnectionStringBuilder();
        _conn = new Connection();

        connstr.DataSource = (filePath.EndsWith("/") || filePath.EndsWith("\\")) ? filePath + "sys.db" : filePath + "/sys.db";
        _conn.ConnectionString = connstr.ToString();
        _conn.Open();

        _command = new Command(_conn);
        _command.CommandText = "SELECT Content FROM [Text] WHERE [Name]=:name AND [Language]=:language";
        _command.Parameters.Add(new Parameter(":name", DbType.Binary));
        _command.Parameters.Add(new Parameter(":language", DbType.Binary));
      }
      catch
      {
        if (_command != null)
          _command.Dispose();
        if (_conn != null)
          _conn.Dispose();

        _command = null;
        _conn = null;

        throw new DatabaseException("Cannot Open System Database");
      }
    }
Example #34
0
    internal static void DatabaseSQLAdd(string query) //삽입이라고 썼지만 삭제도 가능
    {
        IDbConnection dbConnection = new SqliteConnection(GetDBFilePath());

        dbConnection.Open();            // DB 열기
        dbCommand = dbConnection.CreateCommand();

        dbCommand.CommandText = query;  // 쿼리 입력
        try
        {
            dbCommand.ExecuteNonQuery();    // 쿼리 실행
        }

        catch (Exception e)
        {
            Debug.LogError(e);
            //test.text = e.ToString();
        }

        dbCommand.Dispose();
        dbCommand = null;
        dbConnection.Close();
        dbConnection = null;
        Debug.Log("데이터 삽입 완료");
        //test.text = "결과 데이터 삽입 완료";
    }
Example #35
0
        private void _CreateHighscoreDBV1(string filePath)
        {
            using (var connection = new SQLiteConnection())
            {
                connection.ConnectionString = "Data Source=" + filePath;

                try
                {
                    connection.Open();
                }
                catch (Exception)
                {
                    return;
                }

                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "CREATE TABLE IF NOT EXISTS Version ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Value INTEGER NOT NULL);";
                    command.ExecuteNonQuery();

                    command.CommandText = "INSERT INTO Version (id, Value) VALUES(NULL, 1 )";
                    command.ExecuteNonQuery();

                    command.CommandText = "CREATE TABLE IF NOT EXISTS Songs ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
                                          "Artist TEXT NOT NULL, Title TEXT NOT NULL, NumPlayed INTEGER);";
                    command.ExecuteNonQuery();

                    command.CommandText = "CREATE TABLE IF NOT EXISTS Scores ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
                                          "SongID INTEGER NOT NULL, PlayerName TEXT NOT NULL, Score INTEGER NOT NULL, LineNr INTEGER NOT NULL, Date BIGINT NOT NULL, " +
                                          "Medley INTEGER NOT NULL, Duet INTEGER NOT NULL, Difficulty INTEGER NOT NULL);";
                    command.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// Clears all items from the database where their PublishDate is before the date provided.
        /// </summary>
        /// <param name="date"></param>
        public void ClearItemsBeforeDate(DateTime date)
        {
            try
            {
                using (SqliteConnection connection = new SqliteConnection(ItemsConnectionString))
                {
                    connection.Open();
                    using (SqliteCommand command = new SqliteCommand(connection))
                    {
                        string sql = @"DELETE FROM items WHERE DATETIME(publishdate) <= DATETIME(@date)";
                        command.CommandText = sql;

                        SqliteParameter parameter = new SqliteParameter("@date", DbType.String);
                        parameter.Value = date.ToString("yyyy-MM-dd HH:mm:ss");
                        command.Parameters.Add(parameter);

                        int rows = command.ExecuteNonQuery();
                        Logger.Info("ClearItemsBeforeDate before {0} cleared {1} rows.", date.ToString("yyyy-MM-dd HH:mm:ss"), rows);
                    }
                }
            }
            catch (SqliteException e)
            {
                Logger.Warn("SqliteException occured while clearing items before {0}: \n{1}", date, e);
            }
        }
Example #37
0
        public void Init()
        {
            connection = new SQLiteConnection();
            connection.ConnectionString = "Data Source=" + "save.db";

            Open();
        }
		public CodeProjectDatabase ()
		{
			dbPath = Path.Combine (
				Environment.GetFolderPath (Environment.SpecialFolder.Personal),
				"items.db3");
			bool exists = File.Exists (dbPath);
			if (!exists) {
				SqliteConnection.CreateFile (dbPath);
			}

			var connection = new SqliteConnection ("Data Source=" + dbPath);
			connection.Open ();

			if (!exists) {
				var commands = new[]{
					"CREATE TABLE [Member] (Key integer, Name ntext, ArticleCnt integer, BlogCnt integer, Reputation ntext, IsMe integer);"
				};
				foreach (var command in commands) {
					using (var c = connection.CreateCommand ()) {
						c.CommandText = command;
						c.ExecuteNonQuery ();
					}
				}
			}
		}
        public SqliteVsMonoDSSpeedTests()
        {
            // create path and filename to the database file.
            var documents = Environment.GetFolderPath (
                Environment.SpecialFolder.Personal);
            _db = Path.Combine (documents, "mydb.db3");

            if (File.Exists (_db))
                File.Delete (_db);

            SqliteConnection.CreateFile (_db);
            var conn = new SqliteConnection("URI=" + _db);
            using (var c = conn.CreateCommand()) {
                c.CommandText = "CREATE TABLE DataIndex (SearchKey INTEGER NOT NULL,Name TEXT NOT NULL,Email Text NOT NULL)";
                conn.Open ();
                c.ExecuteNonQuery ();
                conn.Close();
            }
            conn.Dispose();

            // create path and filename to the database file.
            var documentsPath = Environment.GetFolderPath (Environment.SpecialFolder.MyDocuments);
            var libraryPath = Path.Combine (documentsPath, "..", "Library");
            _dataDirectory = Path.Combine (libraryPath, "MonoDS");
            _entity = "PersonEntity";
            _serializer = new Serializer();
        }
Example #40
0
		public static int DoNonQuery (SqliteConnection connection, string command_text, string[] param_names, object[] param_args)
		{
			int ret = 0;

			using (SqliteCommand command = new SqliteCommand ()) {
				command.Connection = connection;
				command.CommandText = command_text;

				if (param_names != null) {
					if (param_args == null || param_names.Length != param_args.Length)
						throw new ArgumentException ("param_names, param_args", "param_names and param_args should have same number of items");
					for (int i = 0; i < param_names.Length; ++i)
						command.Parameters.AddWithValue (param_names [i], param_args [i]);
				}

				while (true) {
					try {
						ret = command.ExecuteNonQuery ();
						break;
					} catch (SqliteException e) {
						if (e.ErrorCode == SQLiteErrorCode.Busy) {
							Thread.Sleep (50);
						} else {
							throw;
						}
					} catch (Exception e) {
						Log.Error (e, "SQL that caused the exception: {0}", command_text);
						throw;
					}
				}
			}

			return ret;
		}
Example #41
0
		public void DateTimeConvert ()
		{
			var dateTime = new DateTime (2016, 9, 15, 12, 1, 53);
			var guid = Guid.NewGuid ();

			using (var connection = new SqliteConnection ("Data Source=" + _databasePath)) {
				connection.Open ();

				var sqlCreate = "CREATE TABLE TestTable (ID uniqueidentifier PRIMARY KEY, Modified datetime)";
				using (var cmd = new SqliteCommand (sqlCreate, connection)) {
					cmd.ExecuteNonQuery ();
				}

				var sqlInsert = "INSERT INTO TestTable (ID, Modified) VALUES (@id, @mod)";
				using (var cmd = new SqliteCommand (sqlInsert, connection)) {
					cmd.Parameters.Add (new SqliteParameter ("@id", guid));
					cmd.Parameters.Add (new SqliteParameter ("@mod", dateTime));
					cmd.ExecuteNonQuery ();
				}
			}

			using (var connection = new SqliteConnection ("Data Source=" + _databasePath)) {
				connection.Open ();

				var sqlSelect = "SELECT * from TestTable";
				using (var cmd = new SqliteCommand (sqlSelect, connection))
				using (var reader = cmd.ExecuteReader ()) {
					while (reader.Read ()) {
						Assert.AreEqual (guid, reader.GetGuid (0), "#1");
						Assert.AreEqual (dateTime, reader.GetDateTime (1), "#2");
					}
				}
			}
		}
Example #42
0
    public void CloseSqlConnection()
    {
        if (dbCommand != null) {

            dbCommand.Dispose ();

        }

        dbCommand = null;

        if (reader != null) {

            reader.Dispose ();

        }

        reader = null;

        if (dbConnection != null) {

            dbConnection.Close ();

        }

        dbConnection = null;

        Debug.Log ("Disconnected from db.");
    }
 public SQLiteDatabase(string connectionString)
 {
     createDatabase(connectionString);
     _conn = new SqliteConnection();
     _conn.ConnectionString = connectionString;
     _conn.Open();
 }
Example #44
0
        public void Initialise(string connectionString)
        {
            m_connectionString = connectionString;

            m_log.Info("[ESTATE DB]: Sqlite - connecting: "+m_connectionString);

            m_connection = new SqliteConnection(m_connectionString);
            try
            {
                m_connection.Open();
            }
            catch (Exception ex)
            {
                throw new Exception("SQLite has errored out on opening the database. If you are on a 64 bit system, please run OpenSim.32BitLaunch.exe and try again. If this is not a 64 bit error :" + ex);
            }

            Assembly assem = GetType().Assembly;
            Migration m = new Migration(m_connection, assem, "EstateStore");
            m.Update();

            //m_connection.Close();
           // m_connection.Open();

            Type t = typeof(EstateSettings);
            m_Fields = t.GetFields(BindingFlags.NonPublic |
                                   BindingFlags.Instance |
                                   BindingFlags.DeclaredOnly);

            foreach (FieldInfo f in m_Fields)
                if (f.Name.Substring(0, 2) == "m_")
                    m_FieldMap[f.Name.Substring(2)] = f;
        }
Example #45
0
        /// <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;
        }
Example #46
0
        public static bool DatabaseHelperCanAccessDatabase(String overrideConnectionInfo)
        {
            bool result = false;

            SqliteConnection connection;

            if (
                (overrideConnectionInfo != null)
                && (overrideConnectionInfo.Length > 0)
              )
            {
                connection = new SqliteConnection(overrideConnectionInfo);
            }
            else
            {
                connection = new SqliteConnection(GetConnectionString());
            }

            try
            {
                connection.Open();
                result = (connection.State == ConnectionState.Open);

            }
            catch { }
            finally
            {
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }

            return result;
        }
Example #47
0
 internal static void Init(Mono.Data.Sqlite.SqliteConnection sqliteConnection)
 {
     ToLowerFunction.RegisterFunction(typeof(ToLowerFunction));
     ToUpperFunction.RegisterFunction(typeof(ToUpperFunction));
     ContainsFunction.RegisterFunction(typeof(ContainsFunction));
     FormatNumberFunction.RegisterFunction(typeof(FormatNumberFunction));
     FormatDateFunction.RegisterFunction(typeof(FormatDateFunction));
 }
        protected override void OnLoad(EventArgs e)
        {
            base.OnLoad(e);

            DataTable dtArtists = new DataTable();

            #region fetch data for artists

            Mono.Data.Sqlite.SqliteConnection  cn      = new Mono.Data.Sqlite.SqliteConnection("library.sqlite");
            Mono.Data.Sqlite.SqliteCommand     comm    = new Mono.Data.Sqlite.SqliteCommand(cn);
            Mono.Data.Sqlite.SqliteDataAdapter adapter = new Mono.Data.Sqlite.SqliteDataAdapter(comm);
            comm.CommandText = @"
SELECT  name, id, fetched
FROM    artists
";
            adapter.Fill(dtArtists);

            #endregion

            if (dtArtists.Rows.Count == 0)
            {
                List <SubsonicItem> artists = Subsonic.GetIndexes();

                foreach (SubsonicItem artist in artists)
                {
                    DataRow dr = dtArtists.NewRow();
                    dr["name"]     = artist.name;
                    dr["id"]       = artist.id;
                    dr["feteched"] = DateTime.Now.ToString();
                    dtArtists.Rows.Add(dr);

                    comm             = new Mono.Data.Sqlite.SqliteCommand(cn);
                    comm.CommandText = @"
INSERT INTO artists (name, id, fetched)
VALUES(@name, @id, @fetched);
";
                    comm.Parameters.AddWithValue("@name", artist.name);
                    comm.Parameters.AddWithValue("@id", artist.id);
                    comm.Parameters.AddWithValue("@fetched", DateTime.Now.ToString());

                    if (cn.State != ConnectionState.Open)
                    {
                        cn.Open();
                    }
                    comm.ExecuteNonQuery();
                }

                if (cn.State != ConnectionState.Closed)
                {
                    cn.Close();
                }
            }

            rptArtists.DataSource = dtArtists;
            rptArtists.DataBind();
        }
Example #49
0
        private bool _ConvertV2toV3(SQLiteConnection connection)
        {
            var command = new SQLiteCommand(connection)
            {
                CommandText = "ALTER TABLE Songs ADD DateAdded BIGINT"
            };

            command.ExecuteNonQuery();
            command.CommandText = "UPDATE Songs SET [DateAdded] = @DateAdded";
            command.Parameters.Add("@DateAdded", DbType.Int64, 0).Value = DateTime.Now.Ticks;
            command.ExecuteNonQuery();
            command.CommandText = "UPDATE Version SET [Value] = @version";
            command.Parameters.Add("@version", DbType.Int32, 0).Value = 3;
            command.ExecuteNonQuery();

            //Read NumPlayed from Scores and save to Songs
            command.CommandText = "SELECT SongID, Date FROM Scores ORDER BY Date ASC";

            SQLiteDataReader reader;

            try
            {
                reader = command.ExecuteReader();
            }
            catch (Exception)
            {
                return(false);
            }

            long       lastDateAdded = -1;
            int        lastID        = -1;
            DateTime   dt            = new DateTime(1, 1, 1, 0, 0, 5);
            long       sec           = dt.Ticks;
            List <int> ids           = new List <int>();

            while (reader.Read())
            {
                int  id        = reader.GetInt32(0);
                long dateAdded = reader.GetInt64(1);
                if (id != lastID || dateAdded > lastDateAdded + sec)
                {
                    ids.Add(id);
                    lastID        = id;
                    lastDateAdded = dateAdded;
                }
            }
            reader.Dispose();

            foreach (int id in ids)
            {
                _IncreaseSongCounter(id, command);
            }
            command.Dispose();

            return(true);
        }
Example #50
0
        private void addScore(Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans, Score score)
        {
            string sql     = @"
INSERT INTO [Score] ([AthleteAID],[AthleteBID],[Date],[IsUnfinished],[TimeModified],[Guid],[IsDeleted],[SportID],[VenueID],[Type1],[PointsA],[PointsB],[InnerPoints1A],[InnerPoints1B],[InnerPoints2A],[InnerPoints2B],[InnerPoints3A],[InnerPoints3B],[InnerPoints4A],[InnerPoints4B],[InnerPoints5A],[InnerPoints5B],[InnerPoints6A],[InnerPoints6B],[InnerPoints7A],[InnerPoints7B],[InnerPoints8A],[InnerPoints8B],[InnerPoints9A],[InnerPoints9B],[InnerPoints10A],[InnerPoints10B],[OpponentConfirmation],[ExtraData])
VALUES (@AthleteAID,@AthleteBID,@Date,@IsUnfinished,@TimeModified,@Guid,@IsDeleted,@SportID,@VenueID,@Type1,@PointsA,@PointsB,@InnerPoints1A,@InnerPoints1B,@InnerPoints2A,@InnerPoints2B,@InnerPoints3A,@InnerPoints3B,@InnerPoints4A,@InnerPoints4B,@InnerPoints5A,@InnerPoints5B,@InnerPoints6A,@InnerPoints6B,@InnerPoints7A,@InnerPoints7B,@InnerPoints8A,@InnerPoints8B,@InnerPoints9A,@InnerPoints9B,@InnerPoints10A,@InnerPoints10B,@OpponentConfirmation,@ExtraData)";
            var    command = createCommandForScore(sql, conn, trans, score);

            command.ExecuteNonQuery();
            command.Dispose();
        }
Example #51
0
        private bool _GetDataBaseSongInfos(int songID, out string artist, out string title, out int numPlayed, out DateTime dateAdded, string filePath)
        {
            artist    = String.Empty;
            title     = String.Empty;
            numPlayed = 0;
            dateAdded = DateTime.Today;

            using (var connection = new SQLiteConnection())
            {
                connection.ConnectionString = "Data Source=" + filePath;

                try
                {
                    connection.Open();
                }
                catch (Exception)
                {
                    return(false);
                }

                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "SELECT Artist, Title, NumPlayed, DateAdded FROM Songs WHERE [id] = @id";
                    command.Parameters.Add("@id", DbType.String, 0).Value = songID;

                    SQLiteDataReader reader;
                    try
                    {
                        reader = command.ExecuteReader();
                    }
                    catch (Exception)
                    {
                        return(false);
                    }

                    if (reader != null && reader.HasRows)
                    {
                        reader.Read();
                        artist    = reader.GetString(0);
                        title     = reader.GetString(1);
                        numPlayed = reader.GetInt32(2);
                        dateAdded = new DateTime(reader.GetInt64(3));
                        reader.Dispose();
                        return(true);
                    }
                    if (reader != null)
                    {
                        reader.Dispose();
                    }
                }
            }

            return(false);
        }
Example #52
0
 public virtual void Close()
 {
     lock (_Mutex)
     {
         if (_Connection != null)
         {
             _Connection.Dispose();
             _Connection = null;
         }
     }
 }
Example #53
0
        private SqliteCommand createCommandForScore(string sql, Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans, Score score)
        {
            var command = conn.CreateCommand();

            command.Transaction = trans;
            command.CommandText = sql;
            command.Parameters.Add(new SqliteParameter("@AthleteAID", score.AthleteAID));
            command.Parameters.Add(new SqliteParameter("@AthleteBID", score.AthleteBID));
            command.Parameters.Add(new SqliteParameter("@Date", score.Date));
            command.Parameters.Add(new SqliteParameter("@IsUnfinished", (int)(score.IsUnfinished ? 1 : 0)));
            command.Parameters.Add(new SqliteParameter("@TimeModified", score.TimeModified));
            command.Parameters.Add(new SqliteParameter("@Guid", score.Guid.ToString()));
            command.Parameters.Add(new SqliteParameter("@IsDeleted", score.IsDeleted));
            command.Parameters.Add(new SqliteParameter("@SportID", score.SportID));
            if (score.VenueID == null)
            {
                command.Parameters.Add(new SqliteParameter("@VenueID", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqliteParameter("@VenueID", score.VenueID));
            }
            if (score.Type1 == null)
            {
                command.Parameters.Add(new SqliteParameter("@Type1", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqliteParameter("@Type1", score.Type1));
            }

            command.Parameters.Add(new SqliteParameter("@PointsA", score.PointsA));
            command.Parameters.Add(new SqliteParameter("@PointsB", score.PointsB));

            for (int i = 1; i <= 10; ++i)
            {
                command.Parameters.Add(new SqliteParameter("@InnerPoints" + i.ToString() + "A", score.InnerPointsA[i - 1]));
                command.Parameters.Add(new SqliteParameter("@InnerPoints" + i.ToString() + "B", score.InnerPointsB[i - 1]));
            }

            command.Parameters.Add(new SqliteParameter("@OpponentConfirmation", score.AthleteBConfirmation));

            if (score.ExtraData == null)
            {
                command.Parameters.Add(new SqliteParameter("@ExtraData", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqliteParameter("@ExtraData", score.ExtraData));
            }

            return(command);
        }
Example #54
0
        private void executeNonqueryCommand(string sql, Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans)
        {
            var command = conn.CreateCommand();

            if (trans != null)
            {
                command.Transaction = trans;
            }
            command.CommandText = sql;
            command.ExecuteNonQuery();
            command.Dispose();
        }
Example #55
0
 public static void Create(string dbpath, params string[] sql)
 {
     if (!File.Exists(dbpath))
     {
         try{
             SQLiteConnection.CreateFile(dbpath);
             Command(dbpath, sql);
         }
         catch (Exception) {
         }
     }
     // CREATE TABLE wins(id INTEGER PRIMARY KEY AUTOINCREMENT,[IDCardNo] VARCHAR (50),endtime TimeStamp NOT NULL DEFAULT CURRENT_TIMESTAMP);
 }
        private string DBCreate()
        {
            //<summary><summary/>
            //<params><params/>
            //<output><output/>

            #region DBCreate_declarations
            string strPath   = Path.GetDirectoryName(Environment.GetCommandLineArgs()[0]);
            string strDBPath = strPath + "/" + _localDBName;
            var    testStr   = Path.GetPathRoot(strPath);
            #endregion

            #region DBCreate_validaton
            // Create the database if it doesn't already exist
            if (!File.Exists(strDBPath))
            {
                SqliteConnection.CreateFile(strDBPath);
            }
            else
            {
                return(strDBPath); //db exists so exit now and return default path
            }
            #endregion

            #region DBCreate_procedure
            // Create connection to the database
            var sqliteConn = new Mono.Data.Sqlite.SqliteConnection("Data Source=" + strDBPath);

            // Set the structure of the database
            if (File.Exists(strDBPath))
            {
                var sqliteCMD = new[] { "CREATE TABLE LocalSettings (Type TEXT, StringValue1 TEXT, StringValue2 TEXT, StringValue3 TEXT, StringValue4 TEXT, StringValue5 TEXT)" };

                sqliteConn.Open();
                foreach (var cmd in sqliteCMD)
                {
                    using (var c = sqliteConn.CreateCommand())
                    {
                        c.CommandText = cmd;
                        c.CommandType = System.Data.CommandType.Text;
                        c.ExecuteNonQuery();
                    }
                }
            }

            sqliteConn.Close();

            return(strDBPath);

            #endregion
        }
Example #57
0
        private object executeScalarCommand(string sql, Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans)
        {
            var command = conn.CreateCommand();

            if (trans != null)
            {
                command.Transaction = trans;
            }
            command.CommandText = sql;
            object result = command.ExecuteScalar();

            command.Dispose();
            return(result);
        }
Example #58
0
        public void CreateNew()
        {
            try
            {
                sqliteConnection.CreateFile(m_DataBaseName);

                m_Connection = new sqliteConnection("Data Source=" + m_DataBaseName + ";Version=3;FailIfMissing=True");
                m_Connection.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Create DB failed: " + ex);
            }
        }
Example #59
0
        public SQLTable(sqliteConnection connection, String tableName, String tableColumns, bool createNew)
        {
            m_Connection   = connection;
            m_TableName    = tableName;
            m_TableColumns = tableColumns;

            if (createNew)
            {
                sqliteCommand command = new sqliteCommand("create table " + m_TableName + " (" + m_TableColumns + ")", m_Connection);

                command.Parameters.Add("@name", System.Data.DbType.String).Value = m_TableName;
                command.ExecuteNonQuery();
            }
        }
Example #60
0
    public void MakeCvsThenSend()
    {
        /*Make CVS file*/
        //Filepath for the data.csv file
        string fileName = Application.persistentDataPath + "/" + "data.csv";

        //timestamp, page, date, feeling, urge, intensity, thoughts
        using (StreamWriter writer = new StreamWriter(fileName, false, Encoding.UTF8))
        {
            //Write the each columns title, for each field in pateint_info
            writer.Write("Timestamp | Page | Date | Feeling | Urge | Intensity | Thoughts");
            writer.Write(Environment.NewLine);

            //Write data from the the db
            conn = new sqliteConnection("URI=file:" + Application.persistentDataPath + "/" + dbName + ";Version=3;FailIfMissing=True;mode=cvs");
            conn.Open();
            var Sqlcommand = new sqliteCommand("select * from patient_info", conn);
            var reader     = Sqlcommand.ExecuteReader();
            while (reader.Read())
            {
                writer.Write(reader["timestamp"] + "|" + reader["page"] + "|" + reader["date"] + "|" + reader["feeling"] + "|" + reader["urge"] + "|" + reader["intensity"] + "|" + reader["thoughts"]);
                writer.Write(Environment.NewLine);
            }

            conn.Close();
        }


        /*SendMail*/
        MailMessage mail = new MailMessage();

        mail.From = new MailAddress("*****@*****.**");
        mail.To.Add(email.text);
        mail.Subject = "Patients Data from App";
        mail.Body    = "The File attached is the patients data (every entry is seperated by '|' when importing into excel or google sheets that custom seporator should be used) ";
        //Add the data.csv file to the email
        mail.Attachments.Add(new Attachment(fileName));

        SmtpClient smtpServer = new SmtpClient();

        smtpServer.Host           = "smtp.gmail.com";
        smtpServer.Port           = 587; //Has to be 587 so it works on Networks with port protections.
        smtpServer.DeliveryMethod = SmtpDeliveryMethod.Network;
        smtpServer.Credentials    = new System.Net.NetworkCredential("*****@*****.**", "HeartRateAppFxu2018") as ICredentialsByHost;
        smtpServer.EnableSsl      = true;
        ServicePointManager.ServerCertificateValidationCallback =
            delegate(object s, X509Certificate certificate, X509Chain chain, SslPolicyErrors sslPolicyErrors)
        { return(true); };
        smtpServer.Send(mail);
    }