C# IDbConnection.CreateCommand方法代码示例

 public IDbCommand CreateCommand(string cmdText, IDbConnection connection, IDbTransaction transaction)
 {
     IDbCommand cmd = connection.CreateCommand();
     cmd.CommandText = cmdText;
     cmd.Transaction = transaction;
     return cmd;
 }

        private void ConvertQualityTitle(IDbConnection conn, IDbTransaction tran)
        {
            using (IDbCommand namingConfigCmd = conn.CreateCommand())
            {
                namingConfigCmd.Transaction = tran;
                namingConfigCmd.CommandText = @"SELECT StandardEpisodeFormat, DailyEpisodeFormat, AnimeEpisodeFormat FROM NamingConfig LIMIT 1";

                using (IDataReader configReader = namingConfigCmd.ExecuteReader())
                {
                    while (configReader.Read())
                    {
                        var currentStandard = configReader.GetString(0);
                        var currentDaily = configReader.GetString(1);
                        var currentAnime = configReader.GetString(2);

                        var newStandard = GetNewFormat(currentStandard);
                        var newDaily = GetNewFormat(currentDaily);
                        var newAnime = GetNewFormat(currentAnime);

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;

                            updateCmd.CommandText = "UPDATE NamingConfig SET StandardEpisodeFormat = ?, DailyEpisodeFormat = ?, AnimeEpisodeFormat = ?";
                            updateCmd.AddParameter(newStandard);
                            updateCmd.AddParameter(newDaily);
                            updateCmd.AddParameter(newAnime);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

	public static int InsertFigurine(string pmFigurineName, string pmImageName, int pmCharacterId, IDbConnection pmConnection)
	{
		IDbCommand dbcmd = pmConnection.CreateCommand();
		string sqlQuery = "INSERT INTO FIGURINES (FIGURINE_NAME, PICTURE_NAME, CHARACTER_ID) VALUES ('" + pmFigurineName + "', '" + pmImageName + "', " + pmCharacterId + ") ";
		dbcmd.CommandText = sqlQuery;
		dbcmd.ExecuteScalar ();

		dbcmd.Dispose ();

		dbcmd = pmConnection.CreateCommand();
		sqlQuery = "SELECT last_insert_rowid()";
		dbcmd.CommandText = sqlQuery;

		int id = 0;

		IDataReader reader = dbcmd.ExecuteReader();
		if (reader.Read())
		{
			id = reader.GetInt32(0);
		}

		Debug.Log(id);

		dbcmd.Dispose ();
		dbcmd = null;


		return id;
	}

        private void ConvertQualityProfiles(IDbConnection conn, IDbTransaction tran)
        {
            var qualityProfileItemConverter = new EmbeddedDocumentConverter(new QualityIntConverter());

            // Convert 'Allowed' column in QualityProfiles from Json List<object> to Json List<int> (int = Quality)
            using (IDbCommand qualityProfileCmd = conn.CreateCommand())
            {
                qualityProfileCmd.Transaction = tran;
                qualityProfileCmd.CommandText = @"SELECT Id, Allowed FROM QualityProfiles";
                using (IDataReader qualityProfileReader = qualityProfileCmd.ExecuteReader())
                {
                    while (qualityProfileReader.Read())
                    {
                        var id = qualityProfileReader.GetInt32(0);
                        var allowedJson = qualityProfileReader.GetString(1);

                        var allowed = Json.Deserialize<List<Quality>>(allowedJson);

                        var items = Quality.DefaultQualityDefinitions.OrderBy(v => v.Weight).Select(v => new QualityProfileItem { Quality = v.Quality, Allowed = allowed.Contains(v.Quality) }).ToList();

                        var allowedNewJson = qualityProfileItemConverter.ToDB(items);

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "UPDATE QualityProfiles SET Items = ? WHERE Id = ?";
                            updateCmd.AddParameter(allowedNewJson);
                            updateCmd.AddParameter(id);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

        private void SetSortTitles(IDbConnection conn, IDbTransaction tran)
        {
            using (IDbCommand getSeriesCmd = conn.CreateCommand())
            {
                getSeriesCmd.Transaction = tran;
                getSeriesCmd.CommandText = @"SELECT Id, Title FROM Series";
                using (IDataReader seriesReader = getSeriesCmd.ExecuteReader())
                {
                    while (seriesReader.Read())
                    {
                        var id = seriesReader.GetInt32(0);
                        var title = seriesReader.GetString(1);

                        var sortTitle = Parser.Parser.NormalizeTitle(title).ToLower();

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "UPDATE Series SET SortTitle = ? WHERE Id = ?";
                            updateCmd.AddParameter(sortTitle);
                            updateCmd.AddParameter(id);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

        private void SetMetadataFileExtension(IDbConnection conn, IDbTransaction tran)
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.Transaction = tran;
                cmd.CommandText = "SELECT Id, RelativePath FROM MetadataFiles";

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var id = reader.GetInt32(0);
                        var relativePath = reader.GetString(1);
                        var extension = relativePath.Substring(relativePath.LastIndexOf(".", StringComparison.InvariantCultureIgnoreCase));

                        using (var updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "UPDATE MetadataFiles SET Extension = ? WHERE Id = ?";
                            updateCmd.AddParameter(extension);
                            updateCmd.AddParameter(id);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

        public ArticuloView(long id)
            : base(Gtk.WindowType.Toplevel)
        {
            this.Build ();

            dbConnection = ApplicationContext.Instance.DbConnection;

            IDbCommand dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = string.Format ("select * from articulo where id={0}", id);

            IDataReader dataReader = dbCommand.ExecuteReader ();
            dataReader.Read ();

            entryNombre.Text = (string)dataReader["nombre"];
            spinButtonPrecio.Value = Convert.ToDouble( (decimal)dataReader["precio"] );

            dataReader.Close ();

            saveAction.Activated += delegate {
                Console.WriteLine("saveAction.Activated");

                IDbCommand dbUpdateCommand = dbConnection.CreateCommand ();
                dbUpdateCommand.CommandText = "update articulo set nombre=:nombre, precio=:precio where id=:id";

                DbCommandExtensions.AddParameter (dbUpdateCommand, "nombre", entryNombre.Text);
                DbCommandExtensions.AddParameter (dbUpdateCommand, "precio", Convert.ToDecimal (spinButtonPrecio.Value ));
                DbCommandExtensions.AddParameter (dbUpdateCommand, "id", id);

                dbUpdateCommand.ExecuteNonQuery ();

                Destroy ();
            };
        }

        private void ConvertQualities(IDbConnection conn, IDbTransaction tran)
        {
            // Convert QualitySizes to a more generic QualityDefinitions table.
            using (IDbCommand qualitySizeCmd = conn.CreateCommand())
            {
                qualitySizeCmd.Transaction = tran;
                qualitySizeCmd.CommandText = @"SELECT QualityId, MinSize, MaxSize FROM QualitySizes";
                using (IDataReader qualitySizeReader = qualitySizeCmd.ExecuteReader())
                {
                    while (qualitySizeReader.Read())
                    {
                        var qualityId = qualitySizeReader.GetInt32(0);
                        var minSize = qualitySizeReader.GetInt32(1);
                        var maxSize = qualitySizeReader.GetInt32(2);

                        var defaultConfig = Quality.DefaultQualityDefinitions.Single(p => (int)p.Quality == qualityId);

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "INSERT INTO QualityDefinitions (Quality, Title, Weight, MinSize, MaxSize) VALUES (?, ?, ?, ?, ?)";
                            updateCmd.AddParameter(qualityId);
                            updateCmd.AddParameter(defaultConfig.Title);
                            updateCmd.AddParameter(defaultConfig.Weight);
                            updateCmd.AddParameter(minSize);
                            updateCmd.AddParameter(maxSize);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }        

        private void ConvertRestrictions(IDbConnection conn, IDbTransaction tran)
        {
            using (IDbCommand getRestictionsCmd = conn.CreateCommand())
            {
                getRestictionsCmd.Transaction = tran;
                getRestictionsCmd.CommandText = @"SELECT [Value] FROM Config WHERE [Key] = 'releaserestrictions'";

                using (IDataReader configReader = getRestictionsCmd.ExecuteReader())
                {
                    while (configReader.Read())
                    {
                        var restrictions = configReader.GetString(0);
                        restrictions = restrictions.Replace("\n", ",");

                        using (IDbCommand insertCmd = conn.CreateCommand())
                        {
                            insertCmd.Transaction = tran;
                            insertCmd.CommandText = "INSERT INTO Restrictions (Ignored, Tags) VALUES (?, '[]')";
                            insertCmd.AddParameter(restrictions);

                            insertCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

        private void UpdateRelativePaths(IDbConnection conn, IDbTransaction tran)
        {
            using (IDbCommand getSeriesCmd = conn.CreateCommand())
            {
                getSeriesCmd.Transaction = tran;
                getSeriesCmd.CommandText = @"SELECT Id, Path FROM Series";
                using (IDataReader seriesReader = getSeriesCmd.ExecuteReader())
                {
                    while (seriesReader.Read())
                    {
                        var seriesId = seriesReader.GetInt32(0);
                        var seriesPath = seriesReader.GetString(1) + Path.DirectorySeparatorChar;

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "UPDATE EpisodeFiles SET RelativePath = REPLACE(Path, ?, '') WHERE SeriesId = ?";
                            updateCmd.AddParameter(seriesPath);
                            updateCmd.AddParameter(seriesId);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

        private void UpdateTransmissionSettings(IDbConnection conn, IDbTransaction tran)
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.Transaction = tran;
                cmd.CommandText = "SELECT Id, Settings FROM DownloadClients WHERE Implementation = 'Transmission'";

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var id = reader.GetInt32(0);
                        var settingsJson = reader.GetString(1);

                        var settings = Json.Deserialize<Dictionary<string, object>>(settingsJson);

                        var tvCategory = settings.GetValueOrDefault("tvCategory") as string;
                        if (tvCategory.IsNotNullOrWhiteSpace())
                        {
                            settings["tvCategory"] = "." + tvCategory;

                            using (var updateCmd = conn.CreateCommand())
                            {
                                updateCmd.Transaction = tran;
                                updateCmd.CommandText = "UPDATE DownloadClients SET Settings = ? WHERE Id = ?";
                                updateCmd.AddParameter(settings.ToJson());
                                updateCmd.AddParameter(id);

                                updateCmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
            }
        }

        internal FileStore(FileInfo storeFile)
        {
            StoreFile = storeFile;
              connection = Sqlite.GetDatabaseConnection(storeFile);

              using (var transaction = connection.BeginTransaction()) {
            using (var pragma = connection.CreateCommand()) {
              pragma.CommandText = "PRAGMA journal_mode = MEMORY";
              pragma.ExecuteNonQuery();
              pragma.CommandText = "PRAGMA temp_store = MEMORY";
              pragma.ExecuteNonQuery();
              pragma.CommandText = "PRAGMA synchonous = OFF";
              pragma.ExecuteNonQuery();
            }
            using (var create = connection.CreateCommand()) {
              create.CommandText = "CREATE TABLE IF NOT EXISTS store (key TEXT PRIMARY KEY ON CONFLICT REPLACE, size INT, time INT, data BINARY, cover BINARY)";
              create.ExecuteNonQuery();
            }
            transaction.Commit();
              }

              select = connection.CreateCommand();
              select.CommandText = "SELECT data FROM store WHERE key = ? AND size = ? AND time = ?";
              select.Parameters.Add(selectKey = select.CreateParameter());
              selectKey.DbType = DbType.String;
              select.Parameters.Add(selectSize = select.CreateParameter());
              selectSize.DbType = DbType.Int64;
              select.Parameters.Add(selectTime = select.CreateParameter());
              selectTime.DbType = DbType.Int64;

              selectCover = connection.CreateCommand();
              selectCover.CommandText = "SELECT cover FROM store WHERE key = ? AND size = ? AND time = ?";
              selectCover.Parameters.Add(selectCoverKey = select.CreateParameter());
              selectCoverKey.DbType = DbType.String;
              selectCover.Parameters.Add(selectCoverSize = select.CreateParameter());
              selectCoverSize.DbType = DbType.Int64;
              selectCover.Parameters.Add(selectCoverTime = select.CreateParameter());
              selectCoverTime.DbType = DbType.Int64;

              insert = connection.CreateCommand();
              insert.CommandText = "INSERT OR REPLACE INTO store VALUES(?,?,?,?,?)";
              insert.Parameters.Add(insertKey = select.CreateParameter());
              insertKey.DbType = DbType.String;
              insert.Parameters.Add(insertSize = select.CreateParameter());
              insertSize.DbType = DbType.Int64;
              insert.Parameters.Add(insertTime = select.CreateParameter());
              insertTime.DbType = DbType.Int64;
              insert.Parameters.Add(insertData = select.CreateParameter());
              insertData.DbType = DbType.Binary;
              insert.Parameters.Add(insertCover = select.CreateParameter());
              insertCover.DbType = DbType.Binary;

              InfoFormat("FileStore at {0} is ready", storeFile.FullName);

              vacuumer.Add(connection);
        }

        private void UpdateTransmissionSettings(IDbConnection conn, IDbTransaction tran)
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.Transaction = tran;
                cmd.CommandText = "SELECT Id, Settings FROM Notifications WHERE Implementation = 'PushBullet'";

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var id = reader.GetInt32(0);
                        var settingsJson = reader.GetString(1);
                        var settings = Json.Deserialize<Dictionary<string, object>>(settingsJson);

                        if (settings.ContainsKey("deviceIds"))
                        {
                            var deviceIdsString = settings.GetValueOrDefault("deviceIds", "") as string;

                            if (deviceIdsString.IsNotNullOrWhiteSpace())
                            {
                                var deviceIds = deviceIdsString.Split(new[] { "," }, StringSplitOptions.RemoveEmptyEntries);

                                settings["deviceIds"] = deviceIds;
                            }
                        }

                        if (settings.ContainsKey("channelTags"))
                        {
                            var channelTagsString = settings.GetValueOrDefault("channelTags", "") as string;

                            if (channelTagsString.IsNotNullOrWhiteSpace())
                            {
                                var channelTags = channelTagsString.Split(new[] {","}, StringSplitOptions.RemoveEmptyEntries);

                                settings["channelTags"] = channelTags;
                            }
                        }

                        using (var updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "UPDATE Notifications SET Settings = ? WHERE Id = ?";
                            updateCmd.AddParameter(settings.ToJson());
                            updateCmd.AddParameter(id);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

    private void Vacuum(IDbConnection connection)
    {
      DebugFormat("VACUUM {0}", connection.Database);
      var files = new List<string>();

      using (var q = connection.CreateCommand()) {
        q.CommandText = "SELECT key FROM store";
        using (var r = q.ExecuteReader()) {
          while (r.Read()) {
            files.Add(r.GetString(0));
          }
        }
      }
      var gone = (from f in files
                  let m = new FileInfo(f)
                  where !m.Exists
                  select f);
      lock (connection) {
        using (var trans = connection.BeginTransaction()) {
          using (var q = connection.CreateCommand()) {
            q.Transaction = trans;
            q.CommandText = "DELETE FROM store WHERE key = ?";
            var p = q.CreateParameter();
            p.DbType = DbType.String;
            q.Parameters.Add(p);
            foreach (var f in gone) {
              p.Value = f;
              lock (connection) {
                q.ExecuteNonQuery();
              }
              DebugFormat("Purging {0}", f);
            }
          }
        }
      }
      lock (connection) {
        using (var q = connection.CreateCommand()) {
          q.CommandText = "VACUUM";
          try {
            q.ExecuteNonQuery();
          }
          catch (Exception ex) {
            Error("Failed to vacuum", ex);
          }
        }
      }
      Debug("Vacuum done!");
    }

 public static List<MeetingRoomCheckin> List(IDbConnection db, string email, long day)
 {
     using (IDbCommand command = db.CreateCommand())
      {
          command.CommandText = "select id, day, timestamp, _user, _meetingroom, _booking form checkin where _user = @Email and day = @Day order by timestamp";
          List<MeetingRoomCheckin> MRC = new List<MeetingRoomCheckin>();
          IDbDataParameter p = command.CreateParameter();
          p.ParameterName = "@Email";
          p.Value = email;
          command.Parameters.Add(p);
          IDbDataParameter p2 = command.CreateParameter();
          p2 = command.CreateParameter();
          p2.ParameterName = "@Day";
          p2.Value = day;
          command.Parameters.Add(p2);
          using (IDataReader reader = command.ExecuteReader())
          {
              while (reader.Read())
              {
                  MRC.Add(new MeetingRoomCheckin(reader));
              }
              return MRC;
          }
      }
 }