C# SqliteCommand.Dispose方法代码示例

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

        private void apply_Click(object sender, EventArgs e)
        {
            SqliteCommand command = new SqliteCommand(connection);
            String date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

            command.CommandText = "INSERT OR REPLACE INTO comments (guid, comment, date) VALUES (@guid, @comment, @date)";
            command.Parameters.Add(new SqliteParameter("@guid", guid));
            command.Parameters.Add(new SqliteParameter("@comment", input.Text));
            command.Parameters.Add(new SqliteParameter("@date", date));
            command.ExecuteNonQuery();

            command.Dispose();

            if (mode == "players")
            {
                Thread thread = new Thread(new ThreadStart(gui.thread_Player));
                thread.IsBackground = true;
                thread.Start();
            }
            else if (mode == "bans")
            {
                Thread thread = new Thread(new ThreadStart(gui.thread_Bans));
                thread.IsBackground = true;
                thread.Start();
            }
            else if (mode == "player database")
            {
                Thread thread = new Thread(new ThreadStart(gui.thread_Database));
                thread.IsBackground = true;
                thread.Start();
            }

            this.Close();
        }

    public static List<Dictionary<string, string>> QueryImpl(string handler, string statement, string[] param)
    {
        List<Dictionary<string,string>> result = new List<Dictionary<string,string>>();
         var db = dbs[handler];
         var dbcmd = new SqliteCommand(ConvertStatment(statement), db);

         // Hard and ugly hack to use '?'
         for (var i = 1; i <= param.Length; i++) {
            dbcmd.Parameters.AddWithValue("@a" + i, param[i-1]);
         }
         IDataReader reader = dbcmd.ExecuteReader();
         while(reader.Read())
         {
            Dictionary<string,string> row = new Dictionary<string,string>();
            for (var i = 0; i < reader.FieldCount; i++) {
               string val = reader.GetValue(i).ToString();
               row.Add(reader.GetName(i),val);
            }
            result.Add(row);
         }
         reader.Dispose();
         dbcmd.Dispose();

          	return result;
    }

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

    public static void ExecImpl(string handler, string statement, string[] param)
    {
        var db = dbs[handler];
         var dbcmd = new SqliteCommand(ConvertStatment(statement), db);

         // Hard and ugly hack to use '?'
         for (var i = 1; i <= param.Length; i++) {
            dbcmd.Parameters.AddWithValue("@a" + i, param[i-1]);
         }
         dbcmd.ExecuteNonQuery();
         dbcmd.Dispose();
    }

        public void Comment(GUImain gui, SqliteConnection connection, String name, String guid, String mode)
        {
            this.gui = gui;
            this.connection = connection;
            this.Text = "Comment " + name;
            this.guid = guid;
            this.mode = mode;

            SqliteCommand command = new SqliteCommand(connection);
            command.CommandText = "SELECT comment FROM comments WHERE guid = @guid";
            command.Parameters.Add(new SqliteParameter("@guid", guid));

            SqliteDataReader reader = command.ExecuteReader();

            if (reader.Read())
                input.Text = gui.GetSafeString(reader, 0);

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

        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 bool SetToken(UUID principalID, string token, int lifetime)
        {
            if (System.Environment.TickCount - m_LastExpire > 30000)
                DoExpire();

            SqliteCommand cmd = new SqliteCommand("insert into tokens (UUID, token, validity) values ('" + principalID.ToString() + 
                "', '" + token + "', datetime('now', 'localtime', '+" + lifetime.ToString() + " minutes'))");

            if (ExecuteNonQuery(cmd, m_Connection) > 0)
            {
                cmd.Dispose();
                return true;
            }

            cmd.Dispose();
            return false;
        }

        public void SqliteInsert10000Entries()
        {
            using (var conn = new SqliteConnection("URI=" + _db)) {
                SqliteCommand c;
                conn.Open ();
                for (int i = 1; i < 10001; i++) {

                    c = new SqliteCommand ( String.Format("INSERT INTO DataIndex (SearchKey, Name, Email) VALUES ({0}, '{1}', '{2}')", i, "Name " + i, "Email" + i) , conn );
                        c.ExecuteNonQuery ();
                        c.Dispose();

                    #if DEBUG
                    Console.WriteLine("INSERT: {0}", i);
                    #endif
                }
                conn.Close();
            }
            Assert.IsTrue(true);
        }

        private static int GetDataBaseSongID(string Artist, string Title, string FilePath, int DefNumPlayed)
        {
            SQLiteConnection connection = new SQLiteConnection();
            connection.ConnectionString = "Data Source=" + FilePath;
            SQLiteCommand command;

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

            command = new SQLiteCommand(connection);
            int id = GetDataBaseSongID(Artist, Title, DefNumPlayed, command);
            command.Dispose();
            connection.Close();
            connection.Dispose();

            return id;
        }

        /// <summary>
        /// see IRegionDatastore
        /// </summary>
        /// <param name="primID"></param>
        /// <param name="items"></param>
        public void StorePrimInventory(UUID primID, ICollection<TaskInventoryItem> items)
        {
//            m_log.DebugFormat(":[SQLITE REGION DB]: Entered StorePrimInventory with prim ID {0}", primID);

            RemoveItems(primID);

            using (SqliteCommand cmd = new SqliteCommand())
            {
                if (items.Count == 0)
                    return;

                cmd.CommandText = "insert into primitems (" +
                        "invType, assetType, name, " +
                        "description, creationDate, nextPermissions, " +
                        "currentPermissions, basePermissions, " +
                        "everyonePermissions, groupPermissions, " +
                        "flags, itemID, primID, assetID, " +
                        "parentFolderID, creatorID, ownerID, " +
                        "groupID, lastOwnerID) values (:invType, " +
                        ":assetType, :name, :description, " +
                        ":creationDate, :nextPermissions, " +
                        ":currentPermissions, :basePermissions, " +
                        ":everyonePermissions, :groupPermissions, " +
                        ":flags, :itemID, :primID, :assetID, " +
                        ":parentFolderID, :creatorID, :ownerID, " +
                        ":groupID, :lastOwnerID)";
                cmd.Connection = m_conn;

                foreach (TaskInventoryItem item in items)
                {
                    cmd.Parameters.Clear();

                    FillItemCommand(cmd, item);

                    cmd.ExecuteNonQuery();
                }

                cmd.Dispose();
            }
        }

		///////////////////////////////////////////////////////////////////

		// Return all attributes in the attributes database, used for merging

		private ICollection ReadAllAttributes () 
		{
			ArrayList attributes = new ArrayList ();
			
			SqliteCommand command;
			SqliteDataReader reader;
				
			lock (connection) {
				command = new SqliteCommand ();
				command.Connection = connection;
				command.CommandText =
					"SELECT unique_id, directory, filename, last_mtime, last_attrtime, filter_name, filter_version " +
					"FROM file_attributes";
				
				reader = SqliteUtils.ExecuteReaderOrWait (command);
				
				while (SqliteUtils.ReadOrWait (reader)) {
					attributes.Add (GetFromReader (reader));
				}
				reader.Close ();
				command.Dispose ();
			}
			
			return attributes;
		}

 public void NonQuery(string data, params object[] parameters)
 {
     Linux.SqliteCommand cmd = new Linux.SqliteCommand(data, m_dbConnection);
     int i = 0;
     foreach (var param in parameters)
     {
         cmd.Parameters.Add(new Linux.SqliteParameter(i++.ToString(), param));
     }
     cmd.ExecuteNonQuery();
     cmd.Dispose();
 }

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

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

        /// <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;
//.........这里部分代码省略.........