C# IDbConnection.Query方法代码示例

        private static void Normalise(IDbConnection cnn, IDbTransaction transaction)
        {
            cnn.Execute("INSERT INTO FormType (Description) VALUES ('ChangeRequest')", null, transaction);
            cnn.Execute(@"INSERT INTO FieldType (FormTypeId, Description) VALUES (1, 'Alpha')", null, transaction);
            cnn.Execute(@"INSERT INTO FieldType (FormTypeId, Description) VALUES (1, 'Bravo')", null, transaction);
            cnn.Execute(@"INSERT INTO FieldType (FormTypeId, Description) VALUES (1, 'Charlie')", null, transaction);
            cnn.Execute(@"INSERT INTO FieldType (FormTypeId, Description) VALUES (1, 'Delta')", null, transaction);

            var rows = cnn.Query<TestData>("SELECT * FROM Test", null, transaction);

            foreach (var row in rows)
            {
                cnn.Execute(@"INSERT INTO Form (FormTypeId) VALUES (1)", null, transaction);
                var formId = cnn.Query<int>("SELECT CAST(@@IDENTITY AS INT)", null, transaction).First();

                cnn.Execute(@"INSERT INTO Field (FieldTypeId, FormId, Value) VALUES (1, @formId, @value)", new { formId, value = row.Alpha }, transaction);
                cnn.Execute(@"INSERT INTO Field (FieldTypeId, FormId, Value) VALUES (2, @formId, @value)", new { formId, value = row.Bravo }, transaction);
                cnn.Execute(@"INSERT INTO Field (FieldTypeId, FormId, Value) VALUES (3, @formId, @value)", new { formId, value = row.Charlie }, transaction);
                cnn.Execute(@"INSERT INTO Field (FieldTypeId, FormId, Value) VALUES (4, @formId, @value)", new { formId, value = row.Delta }, transaction);
            }

            var results = cnn.Query(@"SELECT * FROM Form fo
                                      INNER JOIN Field fi ON fo.FormId = fi.FormId
                                      INNER JOIN FieldType ft ON ft.FieldTypeId = fi.FieldTypeId
                                      WHERE fo.FormId = 1", null, transaction).ToDictionary(
                row => row.Description,
                row => row.Value);
        }

        public Database(IDbConnection db)
        {
            _db = db;

            _db.Query(ChannelUser_Creation_Query);
            _db.Query(Channel_Creation_Query);
            _db.Query(Channel_has_users_Creation_Query);
            _db.Query(ChannelBan_Creation_Query);
        }

        public void Execute(IDbConnection connect)
        {
            var query = "select * from users";
            var results = connect.Query(query);
            var initialCount = results.Count();

            var name = Faker.NameFaker.Name();
            var insert = $"insert into users(name) values('{name}')";
            var result = connect.Execute(insert);

            var newResult = connect.Query(query);
            var newCount = newResult.Count();
            Console.WriteLine($"{initialCount} became {newCount}");
        }

 public Result<customer_account> GetCustomerById(IDbConnection db, int id)
 {
     string sqlString = @"SELECT ca.* FROM customer_account AS ca 
                       WHERE [email protected]";
     var customer = db.Query<customer_account>(sqlString, new { id }).FirstOrDefault();
     return Result<customer_account>.Make(customer, errorIfNull: ErrorCodes.InvalidUserNameOrPassword);
 }

        /// <summary>
        /// Get RecordDownloadHistory for admin
        /// </summary>
        public Result<List<ImportReferralHistory>> GetImportHistory(IDbConnection db, int? gameId = null,
            DateTime? from = null, DateTime? to = null, int? skip = null, int? take = null)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.AppendLine(@"SELECT rdh.*, rc.game_name, rc.title as campaign_name
                FROM import_referral_history rdh
                JOIN referral_campaign rc on rc.id = rdh.referral_campaign_id
                WHERE 1 = 1");

            if (gameId.HasValue)
                sqlBuilder.AppendLine("AND rdh.game_id = @gameId");
            if (to.HasValue && to.Value >= SqlDateTime.MinValue.Value)
                sqlBuilder.AppendLine("AND rdh.created_at <= @to");
            if (from.HasValue && from.Value >= SqlDateTime.MinValue.Value)
                sqlBuilder.AppendLine("AND rdh.created_at >= @from");

            sqlBuilder.AppendLine("ORDER BY rdh.id DESC");

            if (skip.HasValue && take.HasValue && skip.Value >= 0 && take.Value > 0)
                sqlBuilder.AppendLine("OFFSET @skip LIMIT @take");

            string sqlQuery = sqlBuilder.ToString();
            var importHistory = db.Query<ImportReferralHistory>(sqlQuery, new
            {
                gameId,
                to,
                from,
                skip,
                take
            }).AsList();

            return Result<List<ImportReferralHistory>>.Make(importHistory, ErrorCodes.NotFound);
        }

        public int Insert(
            IDbConnection connection,
            IDbTransaction transaction,
            int? commandTimeout,
            string tableName,
            string columnList,
            string parameterList,
            IEnumerable<PropertyInfo> keyProperties,
            object entityToInsert,
            bool autoIncrement)
        {
            var cmd = string.Format("insert into {0} ({1}) values ({2})", tableName, columnList, parameterList);

            connection.Execute(cmd, entityToInsert, transaction: transaction, commandTimeout: commandTimeout);

            if (autoIncrement)
            {
                // http://stackoverflow.com/questions/8517841/mysql-last-insert-id-connector-net
                var id = (int)(long)connection.Query<ulong>(
                        "SELECT CAST(LAST_INSERT_ID() AS UNSIGNED INTEGER)",
                        transaction: transaction,
                        commandTimeout: commandTimeout).FirstOrDefault();

                var keyProperty = keyProperties.FirstOrDefault();
                if (keyProperty != null)
                {
                    keyProperty.SetValue(entityToInsert, id, null);
                }

                return id;
            }

            return 0;
        }

        public static GetNextNumberResponse GetNextNumber(IDbConnection connection, GetNextNumberRequest request,
            Field field)
        {
            var prefix = request.Prefix ?? "";

            var max = connection.Query<string>(new SqlQuery()
                .From(field.Fields)
                .Select(Sql.Max(field.Expression))
                .Where(
                    field.StartsWith(prefix) &&
                    field >= prefix.PadRight(request.Length, '0') &&
                    field <= prefix.PadRight(request.Length, '9')))
                .FirstOrDefault();

            var response = new GetNextNumberResponse();

            long l;
            response.Number = max == null ||
                !long.TryParse(max.Substring(prefix.Length), out l) ? 1 : l + 1;

            response.Serial = prefix + response.Number.ToString()
                .PadLeft(request.Length - prefix.Length, '0');

            return response;
        }

        public GroupManager(IDbConnection db)
        {
            database = db;

            var table = new SqlTable("GroupList",
                new SqlColumn("GroupName", MySqlDbType.VarChar, 32) { Primary = true },
                new SqlColumn("Parent", MySqlDbType.VarChar, 32),
                new SqlColumn("Commands", MySqlDbType.Text),
                new SqlColumn("ChatColor", MySqlDbType.Text)
            );
            var creator = new SqlTableCreator(db, db.GetSqlType() == SqlType.Sqlite ? (IQueryBuilder)new SqliteQueryCreator() : new MysqlQueryCreator());
            creator.EnsureExists(table);

            //Add default groups
            AddGroup("default", "canwater,canlava,warp,canbuild");
            AddGroup("newadmin", "default", "kick,editspawn,reservedslot");
            AddGroup("admin", "newadmin", "ban,unban,whitelist,causeevents,spawnboss,spawnmob,managewarp,time,tp,pvpfun,kill,logs,immunetokick,tphere");
            AddGroup("trustedadmin", "admin", "maintenance,cfg,butcher,item,heal,immunetoban,ignorecheatdetection,ignoregriefdetection,usebanneditem,manageusers");
            AddGroup("vip", "default", "canwater,canlava,warp,canbuild,reservedslot");

            String file = Path.Combine(TShock.SavePath, "groups.txt");
            if (File.Exists(file))
            {
                using (StreamReader sr = new StreamReader(file))
                {
                    String line;
                    while ((line = sr.ReadLine()) != null)
                    {
                        if (!line.Equals("") && !line.Substring(0, 1).Equals("#"))
                        {
                            String[] info = line.Split(' ');
                            String comms = "";
                            int size = info.Length;
                            for (int i = 1; i < size; i++)
                            {
                                if (!comms.Equals(""))
                                    comms = comms + ",";
                                comms = comms + info[i].Trim();
                            }

                            string query = "";
                            if (TShock.Config.StorageType.ToLower() == "sqlite")
                                query = "INSERT OR IGNORE INTO GroupList (GroupName, Commands) VALUES (@0, @1);";
                            else if (TShock.Config.StorageType.ToLower() == "mysql")
                                query = "INSERT IGNORE INTO GroupList SET [email protected], [email protected];";

                            db.Query(query, info[0].Trim(), comms);

                        }
                    }
                }
                String path = Path.Combine(TShock.SavePath, "old_configs");
                String file2 = Path.Combine(path, "groups.txt");
                if (!Directory.Exists(path))
                    Directory.CreateDirectory(path);
                if (File.Exists(file2))
                    File.Delete(file2);
                File.Move(file, file2);
            }
        }

        public int Insert(
            IDbConnection connection,
            IDbTransaction transaction,
            int? commandTimeout,
            string tableName,
            string columnList,
            string parameterList,
            IEnumerable<PropertyInfo> keyProperties,
            object entityToInsert,
            bool autoIncrement)
        {
            var cmd = string.Format("insert into {0} ({1}) values ({2})", tableName, columnList, parameterList);

            connection.Execute(cmd, entityToInsert, transaction: transaction, commandTimeout: commandTimeout);

            if (autoIncrement)
            {
                // NOTE: would prefer to use IDENT_CURRENT('tablename') or IDENT_SCOPE but these are not available on SQLCE
                var r = connection.Query("select scope_identity() id", transaction: transaction, commandTimeout: commandTimeout);
                var id = (int)r.First().id;
                var keyProperty = keyProperties.FirstOrDefault();
                if (keyProperty != null)
                {
                    keyProperty.SetValue(entityToInsert, id, null);
                }

                return id;
            }

            return 0;
        }

        public Customer Get(string customerId, IDbConnection connection)
        {
            const string sql = _customerSelectStatement +
                               @" WHERE CustomerID = @customerId";

            return connection.Query<DbCustomer>(sql, new {customerId = customerId}).Select(MapToCustomer).FirstOrDefault();
        }

        public Result<customer_account> Login(IDbConnection db, string userName, string pwd, IPAddress ip)
        {
            string userNameLC = userName.ToLower();

            var loginInfo = db.Query<customer_login_password>("SELECT * FROM customer_login_password WHERE lower(username)[email protected] OR lower(email)[email protected] ", new { userName = userNameLC }).FirstOrDefault();

            if (loginInfo == null || string.Compare(loginInfo.unhashed_password, pwd, StringComparison.OrdinalIgnoreCase) != 0)
            {
                return Result<customer_account>.Null(ErrorCodes.InvalidUserNameOrPassword);
            }

            var user = GetCustomerById(db, loginInfo.customer_account_id);
            int id = user.Data.id;

            // Change country code only IF the database has not store this value before //
            if (string.IsNullOrEmpty(user.Data.country_code) || string.IsNullOrEmpty(user.Data.country_name))
            {
                ip.GetCountryCode(c => user.Data.country_code = c, n => user.Data.country_name = n);
            }
          

            // TODO: Update
            user.Data.last_login_at = DateTime.UtcNow;
            db.Execute("UPDATE customer_account SET [email protected]_login_at, [email protected]_code, [email protected]_name", user.Data);

            return user;
        }

        public async Task<int> InsertAsync(
            IDbConnection connection,
            IDbTransaction transaction,
            int? commandTimeout,
            string tableName,
            string columnList,
            string parameterList,
            IEnumerable<PropertyInfo> keyProperties,
            object entityToInsert,
            bool autoIncrement)
        {
            var cmd = string.Format("insert into {0} ({1}) values ({2})", tableName, columnList, parameterList);

            await
                connection.ExecuteAsync(cmd, entityToInsert, transaction: transaction, commandTimeout: commandTimeout)
                    .ConfigureAwait(false);

            if (autoIncrement)
            {
                var r = connection.Query(
                    "select last_insert_rowid() id",
                    transaction: transaction,
                    commandTimeout: commandTimeout);
                var id = (int)r.First().id;
                if (keyProperties.Any())
                {
                    keyProperties.First().SetValue(entityToInsert, id, null);
                }

                return id;
            }

            return 0;
        }

        private bool DoesIndexExists(IDbConnection conn, string tableName, string indexName)
        {
            var query = [email protected]"show index from {tableName} where Key_name='{indexName}'";

            return conn
                .Query(query)
                .Any();
        }

        public AppProcessor(uint appID)
        {
            AppID = appID;

            DbConnection = Database.GetConnection();

            CurrentData = DbConnection.Query<PICSInfo>("SELECT `Name` as `KeyName`, `Value`, `Key` FROM `AppsInfo` INNER JOIN `KeyNames` ON `AppsInfo`.`Key` = `KeyNames`.`ID` WHERE `AppID` = @AppID", new { AppID }).ToDictionary(x => x.KeyName, x => x);
        }

        public Result<List<Game>> GetAllGames(IDbConnection db)
        {
            string sqlString = @"SELECT game.*, studio.name AS studio_name FROM game
                                INNER JOIN studio on studio.id = game.studio_id";
            var games = db.Query<Game>(sqlString).AsList();

            return Result<List<Game>>.Make(games);
        }