C# Sqlite.SqliteConnection类代码示例

    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;
        }
    }

        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();
                }
            }
        }

        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);
            }
        }

        /// <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);
            }
        }

		public ComboBoxDataSource (SqliteConnection conn, string tableName, string displayField)
		{
			// Initialize
			this.Conn = conn;
			this.TableName = tableName;
			this.DisplayField = displayField;
		}

        /// <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;
        }

    // 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;
    }

        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();
                }
            }
        }

 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 + "的数据库！");
 }

        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;
        }

        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;
        }

        /// <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;
        }

 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();
     }
 }

    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;
    }

        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();
        }