C# SqliteConnection.Dispose方法代码示例

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

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

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

    private void Cleanup(SqliteConnection cnn)
    {
      if (_disposeConnection)
        cnn.Dispose();

      _transaction = null;
      _scope = null;
    }

		public EvolutionSummaryTracker (string directory, string account_name, string folder_name)
		{
			// Make the on-disk files for folders have sane names
			folder_name = folder_name.Replace ('/', '-');
			folder_name = folder_name.Replace (':', '_');
			folder_name = folder_name.Replace (',', ' '); // Causes problems with the ConnectionString otherwise

			string filename = Path.Combine (directory, String.Format ("SummaryTracker-{0}-{1}.db", account_name, folder_name));
			bool create_new_db = ! File.Exists (filename);
			bool purge_old_db = false;

			connection = GetConnection (filename);
			try {
				connection.Open ();
			} catch (ApplicationException) {
				purge_old_db = true;
			}

			if (! create_new_db && ! purge_old_db) {
				// Run a dummy SELECT statement to catch more errors
				// indicating sqlite version mismatches.
				using (SqliteCommand command = new SqliteCommand ()) {
					command.Connection = connection;
					command.CommandText = "SELECT flags FROM mapping WHERE uid = 'fo/ky'";

					SqliteDataReader reader;

					try {
						reader = SqliteUtils.ExecuteReaderOrWait (command);
						reader.Close ();
					} catch (ApplicationException) {
						purge_old_db = true;
					}
				}
			}

			if (purge_old_db) {
				connection.Dispose ();

				// Purge the old database and create a new one
				File.Delete (filename);
				connection = GetConnection (filename);
				connection.Open ();

				create_new_db = true;
			}

			if (create_new_db)
				CreateDatabase ();

			// Start a transaction for any updates
			SqliteUtils.DoNonQuery (connection, "BEGIN");
		}

        public string proDataCreate()
        {
            string DatabaseName = "PUB.db3";
            string documents = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
            string db = System.IO.Path.Combine(documents, DatabaseName);
            bool BFile = File.Exists(db);
            if (!BFile) { SqliteConnection.CreateFile(db); }

            var conn = new SqliteConnection("Data Source=" + db);
            string[] commands = proDataCreateFmt();
            try
            {
                foreach (var cmd in commands)
                {
                    var sqlitecmd = conn.CreateCommand();
                    sqlitecmd.CommandText = cmd;
                    sqlitecmd.CommandType = CommandType.Text;
                    conn.Open();
                    sqlitecmd.ExecuteNonQuery();
                    conn.Close();
                }
                if (!BFile) // 初始化
                {
                    var sqlitecmd = conn.CreateCommand();
                    conn.Open();
                    sqlitecmd.CommandType = CommandType.Text;
                    sqlitecmd.CommandText = "INSERT INTO T_PUB_USERID (USERID) VALUES (0);";
                    sqlitecmd.ExecuteNonQuery();
                    sqlitecmd.CommandText = "INSERT INTO T_PUB_USERTELNO (USERTELNO) VALUES ('');";
                    sqlitecmd.ExecuteNonQuery();
                    sqlitecmd.CommandText = "INSERT INTO T_PUB_USERTYPE (USERTYPE) VALUES (0);";
                    sqlitecmd.ExecuteNonQuery();
                    conn.Close();
                }
                conn.Dispose();
                return "";
            }
            catch (System.Exception sysExc)
            {
                return "创建数据出错: " + sysExc.Message;
            }
        }

        public static void Execute(string command, Object obj, Dictionary<string, DbType> lookup)
        {
            SqliteConnection conn = new SqliteConnection (data_source);
            SqliteCommand cmd;

            conn.Open ();
            cmd = new SqliteCommand (command, conn);

            try {
                if (obj != null)
                    Database.AddParameters (cmd, obj, lookup);
                cmd.ExecuteNonQuery ();
            } catch (KeyNotFoundException) {
                Log.Warning ("Missing a parameter somewhere; not executing SQL statement");
            } catch (Exception e) {
                Log.Exception ("Exception occurred while executing query", e);
            } finally {
                cmd.Dispose ();
                conn.Dispose ();
            }
        }

 public static void InitXBansDB()
 {
     if (!EBConfig.UseMysql)
     {
         string sql = Path.Combine(EBDB);
         if (!File.Exists(EBDB))
         {
             SqliteConnection.CreateFile(EBDB);
         }
         DBSqlite = new SqliteConnection(string.Format("uri=file://{0},Version=3", sql));
         CheckTables(DBSqlite);
         DBSqlite.Dispose();
     }
     else
     {
         DBMysql = new MySqlConnection(string.Format("Data Source={0};User Id={1};Password={2}", EBConfig.MysqlHost, EBConfig.MysqlLogin, EBConfig.MysqlPassword));
         string CMD = string.Format("CREATE DATABASE IF NOT EXISTS {0}", EBConfig.MysqlDatabase);
         RunExec(CMD);
         DBMysql = new MySqlConnection(string.Format("Database={0};Data Source={1};User Id={2};Password={3}", EBConfig.MysqlDatabase, EBConfig.MysqlHost, EBConfig.MysqlLogin, EBConfig.MysqlPassword));
         CheckTables(DBMysql);
         ImportToMysql();
     }
 }

 internal static void ImportToMysql()
 {
     string sql = Path.Combine(EBDB);
     if (File.Exists(EBDB))
     {
         string[] baninfo = new string[5];
         DBSqlite = new SqliteConnection(string.Format("uri=file://{0},Version=3", sql));
         var DBQuery = DBSqlite.QueryReader("SELECT * FROM BannedIP");
         while (DBQuery.Read())
         {
             baninfo[0] = DBQuery.Get<string>("IP");
             baninfo[1] = DBQuery.Get<int>("BanDate").ToString();
             baninfo[2] = DBQuery.Get<int>("UnbanDate").ToString();
             baninfo[3] = DBQuery.Get<string>("BannedBy");
             baninfo[4] = DBQuery.Get<string>("Reason");
             RunExec("INSERT INTO BannedIP (IP, BanDate, UnbanDate, BannedBy, Reason) VALUES ('" + baninfo[0] + "', '" + int.Parse(baninfo[1]) + "', '" + int.Parse(baninfo[2]) + "', '" + baninfo[3] + "', '" + baninfo[4] + "')");
         }
         DBSqlite.Dispose();
         DBQuery.Dispose();
         DBQuery = DBSqlite.QueryReader("SELECT * FROM BannedPlayer");
         while (DBQuery.Read())
         {
             baninfo[0] = DBQuery.Get<string>("Player");
             baninfo[1] = DBQuery.Get<int>("BanDate").ToString();
             baninfo[2] = DBQuery.Get<int>("UnbanDate").ToString();
             baninfo[3] = DBQuery.Get<string>("BannedBy");
             baninfo[4] = DBQuery.Get<string>("Reason");
             RunExec("INSERT INTO BannedPlayer (Player, BanDate, UnbanDate, BannedBy, Reason) VALUES ('" + baninfo[0] + "', '" + int.Parse(baninfo[1]) + "', '" + int.Parse(baninfo[2]) + "', '" + baninfo[3] + "', '" + baninfo[4] + "')");
         }
         DBSqlite.Dispose();
         DBQuery.Dispose();
         DBQuery = DBSqlite.QueryReader("SELECT * FROM MutedPlayer");
         while (DBQuery.Read())
         {
             baninfo[0] = DBQuery.Get<string>("Player");
             baninfo[1] = DBQuery.Get<int>("MuteDate").ToString();
             baninfo[2] = DBQuery.Get<int>("UnmuteDate").ToString();
             baninfo[3] = DBQuery.Get<string>("BannedBy");
             baninfo[4] = DBQuery.Get<string>("MutedBy");
             RunExec("INSERT INTO MutedPlayer (Player, MuteDate, UnmuteDate, MutedBy, Reason) VALUES ('" + baninfo[0] + "', '" + int.Parse(baninfo[1]) + "', '" + int.Parse(baninfo[2]) + "', '" + baninfo[3] + "', '" + baninfo[4] + "')");
         }
         DBSqlite.Dispose();
         DBSqlite.Close();
         DBQuery.Dispose();
         File.Delete(EBDB);
     }
 }

        public static int AddScore(SPlayer player)
        {
            SQLiteConnection connection = new SQLiteConnection();
            connection.ConnectionString = "Data Source=" + _HighscoreFilePath;
            SQLiteCommand command;

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

            command = new SQLiteCommand(connection);

            int DataBaseSongID = GetDataBaseSongID(player, command);
            int result = AddScore(player, command, DataBaseSongID);

            connection.Close();
            connection.Dispose();

            return result;
        }

        /// <summary>
        /// Converts a USDX 1.1 database into the Vocaluxe format
        /// </summary>
        /// <param name="FilePath">Database file path</param>
        /// <returns>True if succeeded</returns>
        private static bool ConvertFrom110(string FilePath)
        {
            SQLiteConnection connection = new SQLiteConnection();
            connection.ConnectionString = "Data Source=" + FilePath;
            SQLiteCommand command;

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

            command = new SQLiteCommand(connection);

            //The USDX database has no column for LineNr, Medley and Duet so just fill 0 in there
            command.CommandText = "INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, Difficulty) SELECT SongID, Player, Score, '0', Date, '0', '0', Difficulty from US_Scores";
            command.ExecuteNonQuery();

            command.CommandText = "INSERT INTO Songs SELECT ID, Artist, Title, TimesPlayed from US_Songs";
            command.ExecuteNonQuery();

            List<SData> scores = new List<SData>();
            List<SData> songs = new List<SData>();

            SQLiteDataReader reader = null;
            command.CommandText = "SELECT id, PlayerName, Date FROM Scores";
            try
            {
                reader = command.ExecuteReader();
            }
            catch (Exception)
            {
                throw;
            }

            if (reader != null && reader.HasRows)
            {
                while (reader.Read())
                {
                    SData data = new SData();
                    data.id = reader.GetInt32(0);
                    data.str1 = reader.GetString(1);
                    data.ticks = UnixTimeToTicks((int)reader.GetInt64(2));

                    scores.Add(data);
                }
                reader.Close();
            }

            command.CommandText = "SELECT id, Artist, Title FROM Songs";
            try
            {
                reader = command.ExecuteReader();
            }
            catch (Exception)
            {
                throw;
            }

            if (reader != null && reader.HasRows)
            {
                while (reader.Read())
                {
                    SData data = new SData();
                    data.id = reader.GetInt32(0);
                    data.str1 = reader.GetString(1);
                    data.str2 = reader.GetString(2);
                    songs.Add(data);
                }
                reader.Close();
            }

            reader.Dispose();

            SQLiteTransaction _Transaction = connection.BeginTransaction();
            // update Title and Artist strings
            foreach (SData data in songs)
            {
                command.CommandText = "UPDATE Songs SET [Artist] = @artist, [Title] = @title WHERE [ID] = @id";
                command.Parameters.Add("@title", System.Data.DbType.String, 0).Value = data.str2;
                command.Parameters.Add("@artist", System.Data.DbType.String, 0).Value = data.str1;
                command.Parameters.Add("@id", System.Data.DbType.Int32, 0).Value = data.id;
                command.ExecuteNonQuery();
            }

            // update player names
            foreach (SData data in scores)
            {
                command.CommandText = "UPDATE Scores SET [PlayerName] = @player, [Date] = @date WHERE [id] = @id";
                command.Parameters.Add("@player", System.Data.DbType.String, 0).Value = data.str1;
                command.Parameters.Add("@date", System.Data.DbType.Int64, 0).Value = data.ticks;
                command.Parameters.Add("@id", System.Data.DbType.Int32, 0).Value = data.id;
//.........这里部分代码省略.........

//.........这里部分代码省略.........

                        byte[] bytes = Sqlite3.sqlite3_column_rawbytes(Stmt, 1);
                        if (bytes != null)
                            data.str1 = UTF8.GetString(Encoding.Convert(CP1252, UTF8, bytes));
                        else
                            data.str1 = "Someone";

                        bytes = Sqlite3.sqlite3_column_rawbytes(Stmt, 2);
                        if (bytes != null)
                            data.str2 = UTF8.GetString(Encoding.Convert(CP1252, UTF8, bytes));
                        else
                            data.str2 = "Someone";

                        songs.Add(data);
                    }
                    Sqlite3.sqlite3_finalize(Stmt);
                }

                Stmt = new Sqlite3.Vdbe();

                if (!dateExists)
                    res = Sqlite3.sqlite3_prepare_v2(OldDB, "SELECT id, PlayerName FROM Scores", -1, ref Stmt, 0);
                else
                    res = Sqlite3.sqlite3_prepare_v2(OldDB, "SELECT id, PlayerName, Date FROM Scores", -1, ref Stmt, 0);

                if (res != Sqlite3.SQLITE_OK)
                {
                    CLog.LogError("Error query Database: " + FilePath + " (" + Sqlite3.sqlite3_errmsg(OldDB) + ")");
                }
                else
                {
                    //Sqlite3.sqlite3_step(Stmt);

                    Encoding UTF8 = Encoding.UTF8;
                    Encoding CP1252 = Encoding.GetEncoding(1252);

                    while (Sqlite3.sqlite3_step(Stmt) == Sqlite3.SQLITE_ROW)
                    {
                        SData data = new SData();

                        data.id = Sqlite3.sqlite3_column_int(Stmt, 0);

                        byte[] bytes = Sqlite3.sqlite3_column_rawbytes(Stmt, 1);
                        if (bytes != null)
                            data.str1 = UTF8.GetString(Encoding.Convert(CP1252, UTF8, bytes));
                        else
                            data.str1 = "Someone";

                        if (dateExists)
                            data.ticks = UnixTimeToTicks(Sqlite3.sqlite3_column_int(Stmt, 2));

                        scores.Add(data);
                    }
                    Sqlite3.sqlite3_finalize(Stmt);
                }
            }
            Sqlite3.sqlite3_close(OldDB);

            SQLiteTransaction _Transaction = connection.BeginTransaction();      
             
            // update Title and Artist strings
            foreach (SData data in songs)
            {
                command.CommandText = "UPDATE Songs SET [Artist] = @artist, [Title] = @title WHERE [ID] = @id";
                command.Parameters.Add("@title", System.Data.DbType.String, 0).Value = data.str2;
                command.Parameters.Add("@artist", System.Data.DbType.String, 0).Value = data.str1;
                command.Parameters.Add("@id", System.Data.DbType.Int32, 0).Value = data.id;
                command.ExecuteNonQuery();
            }           

            // update player names
            foreach (SData data in scores)
            {
                if (!dateExists)
                    command.CommandText = "UPDATE Scores SET [PlayerName] = @player WHERE [id] = @id";
                else
                {
                    command.CommandText = "UPDATE Scores SET [PlayerName] = @player, [Date] = @date WHERE [id] = @id";
                    command.Parameters.Add("@date", System.Data.DbType.Int64, 0).Value = data.ticks;
                }
                command.Parameters.Add("@player", System.Data.DbType.String, 0).Value = data.str1;
                command.Parameters.Add("@id", System.Data.DbType.Int32, 0).Value = data.id;
                command.ExecuteNonQuery();
            }
            _Transaction.Commit();

            //Delete old tables after conversion
            command.CommandText = "DROP TABLE US_Scores;";
            command.ExecuteNonQuery();

            command.CommandText = "DROP TABLE US_Songs;";
            command.ExecuteNonQuery();

            reader.Dispose();
            command.Dispose();
            connection.Close();
            connection.Dispose();

            return true;
        }

        public static bool GetCreditsRessource(string FileName, ref STexture tex)
        {
            bool result = false;

            SQLiteConnection connection = new SQLiteConnection();
            connection.ConnectionString = "Data Source=" + _CreditsRessourcesFilePath;
            SQLiteCommand command;
            try
            {
                connection.Open();
            }
            catch (Exception)
            {
                return false;
            }
            command = new SQLiteCommand(connection);

            command.CommandText = "SELECT id, width, height FROM Images WHERE [Path] = @path";
            command.Parameters.Add("@path", System.Data.DbType.String, 0).Value = FileName;

            SQLiteDataReader reader = null;
            try
            {
                reader = command.ExecuteReader();
            }
            catch (Exception)
            {
                throw;
            }

            if (reader != null && reader.HasRows)
            {
                reader.Read();
                int id = reader.GetInt32(0);
                int w = reader.GetInt32(1);
                int h = reader.GetInt32(2);
                reader.Close();

                command.CommandText = "SELECT Data FROM ImageData WHERE ImageID = " + id.ToString();
                try
                {
                    reader = command.ExecuteReader();
                }
                catch (Exception)
                {
                    throw;
                }

                if (reader.HasRows)
                {
                    result = true;
                    reader.Read();
                    byte[] data = GetBytes(reader);
                    tex = CDraw.AddTexture(w, h, ref data);
                }
            }

            if (reader != null)
            {
                reader.Close();
                reader.Dispose();
            }
            command.Dispose();
            connection.Close();
            connection.Dispose();

            return result;
        }

        /// <summary>
        /// Creates a new Vocaluxe Database if no file exists. Converts an existing old Ultrastar Deluxe highscore database into vocaluxe format.
        /// </summary>
        /// <param name="FilePath">Database file path</param>
        /// <returns></returns>
        private static bool CreateOrConvert(string FilePath)
        {
            SQLiteConnection connection = new SQLiteConnection();
            connection.ConnectionString = "Data Source=" + FilePath;
            SQLiteCommand command;

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

            SQLiteDataReader reader = null;
            command = new SQLiteCommand(connection);

            command.CommandText = "PRAGMA user_version";
            reader = command.ExecuteReader();
            reader.Read();

            int version = reader.GetInt32(0);

            reader.Close();
            reader.Dispose();

            //Check if old scores table exists
            command.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='US_Scores';";
            reader = command.ExecuteReader();
            reader.Read();
            bool scoresTableExists = reader.HasRows;

            reader.Close();
            reader.Dispose();

            command.CommandText = "SELECT Value FROM Version";
            reader = null;

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

            if (reader == null)
            {
                // create new database/tables
                if (version == 1) //Check for USDX 1.1 DB
                {
                    CreateHighscoreDBV1(FilePath);
                    ConvertFrom110(FilePath);
                    UpdateDatabase(1, connection);
                }
                else if (version == 0 && scoresTableExists) //Check for USDX 1.01 or CMD Mod DB
                {
                    CreateHighscoreDBV1(FilePath);
                    ConvertFrom101(FilePath);
                    UpdateDatabase(1, connection);
                }
                else
                    CreateHighscoreDB(FilePath);
            }
            else if (reader.FieldCount == 0)
            {
                // create new database/tables
                if (version == 1) //Check for USDX 1.1 DB
                {
                    CreateHighscoreDBV1(FilePath);
                    ConvertFrom110(FilePath);
                    UpdateDatabase(1, connection);
                }
                else if (version == 0 && scoresTableExists) //Check for USDX 1.01 or CMD Mod DB
                {
                    CreateHighscoreDBV1(FilePath);
                    ConvertFrom101(FilePath);
                    UpdateDatabase(1, connection);
                }
                else
                    CreateHighscoreDB(FilePath);
            }
            else
            {
                reader.Read();
                int CurrentVersion = reader.GetInt32(0);
                if (CurrentVersion < CSettings.iDatabaseHighscoreVersion)
                {
                    // update database
                    UpdateDatabase(CurrentVersion, connection);
                }
            }

//.........这里部分代码省略.........