Example #1
3
        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);
        }
Example #2
1
        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 ca.id=@id";
     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 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;
        }
Example #7
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 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 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;
        }
Example #10
0
        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 GroupName=@0, Commands=@1;";

                            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);
            }
        }
Example #11
0
        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)=@userName OR lower(email)=@userName ", 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 last_login_at=@last_login_at, country_code=@country_code, country_name=@country_name", user.Data);

            return user;
        }
        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;
        }
Example #13
0
 public Result<CustomerAccount_ToRemove> GetCustomerByUserName(IDbConnection db, string username)
 {
     string sqlString = "SELECT ca.* FROM customer_account AS ca " +
                        "INNER JOIN customer_login_password AS cl ON cl.customer_account_id = ca.id " +
                        "WHERE cl.username=@username";
     var customer = db.Query<CustomerAccount_ToRemove>(sqlString, new { username }).FirstOrDefault();
     return Result<CustomerAccount_ToRemove>.Make(customer, ErrorCodes.InvalidUserName);
 }
        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<active_gamer_scheme> GetActiveGamerScheme(IDbConnection db, int customerId)
        {
            string sqlQuery = @"SELECT * FROM active_gamer_scheme
                WHERE customer_account_id = @customerId";

            var activeGamer = db.Query<active_gamer_scheme>(sqlQuery, new { customerId }).FirstOrDefault();
            return Result<active_gamer_scheme>.Make(activeGamer, ErrorCodes.InvalidUserId);
        }
Example #16
0
        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);
        }
Example #17
0
 public Result<List<friend>> GetFriendByFriend1Id(IDbConnection db, int friend1_id)
 {
     string sql = @"SELECT * FROM friend 
         WHERE 
         friend1_id = @friend1_id";
     var friend = db.Query<friend>(sql, new { friend1_id }).AsList();
     return Result<List<friend>>.Make(friend, errorIfNull: ErrorCodes.NON_EXISTING_USER);
 }
        private bool DoesIndexExists(IDbConnection conn, string tableName, string indexName)
        {
            var query = $@"show index from {tableName} where Key_name='{indexName}'";

            return conn
                .Query(query)
                .Any();
        }
Example #19
0
        public bool HasStudioPermission(IDbConnection db, int userId, int studio_id)
        {
            string sqlQuery = @"SELECT EXISTS (SELECT 1
                                                FROM  studio_admin_assignment 
                                                WHERE studio_admin_assignment.game_admin_id = @userId
					                            AND studio_admin_assignment.studio_id = @studio_id)";
            return db.Query<bool>(sqlQuery, new { userId, studio_id }).FirstOrDefault();
        }
Example #20
0
        public int CreateLogApi(IDbConnection db, ApiLog log)
        {
            string sql = @"INSERT INTO api_log 
            (action, version, user_agent, status, message, ip_address, country_code, data, customer_username, partner_identifier) 
            VALUES 
            (@action, @version, @user_agent, @status, @message, @ip_address, @country_code, @data, @customer_username, @partner_identifier)";

            return db.Query<int>(sql, log).FirstOrDefault();
        }
Example #21
0
        /// <summary>
        /// Insert data into friend table
        /// </summary>
        /// <param name="db"></param>
        /// <param name="customerAccount"></param>
        /// <returns></returns>
        public int CreateFriend(IDbConnection db, friend friend)
        {
            string sql = @"INSERT INTO friend 
            (sent_at, status, friend1_id, friend2_id) 
            VALUES 
            (@sent_at, @status, @friend1_id, @friend2_id)";

            return db.Query<int>(sql, friend).FirstOrDefault();
        }
Example #22
0
 public Result<List<Studio>> GetStudios(IDbConnection db, int userId)
 {
     string sqlQuery = @"SELECT studio.*
                         FROM studio
                         JOIN studio_admin_assignment ON studio_admin_assignment.studio_id = studio.id
                         WHERE studio_admin_assignment.game_admin_id = @userId";
     var studios = db.Query<Studio>(sqlQuery, new { userId }).AsList();
     return Result<List<Studio>>.Make(studios);
 }
Example #23
0
 public Result<List<CustomerAccount>> GetUserByConditions(IDbConnection db, string conditions)
 {
     string sqlString = string.Format(@"SELECT customer_account.*,partner.Name as partner_name
                         FROM customer_account 
                         LEFT JOIN partner ON partner.identifier = customer_account.partner_identifier
                         WHERE {0};", conditions);
     var customers = db.Query<CustomerAccount>(sqlString).AsList();
     return Result<List<CustomerAccount>>.Make(customers);
 }
Example #24
0
        public Result<List<Package>> GetPackagesForAdminUser(IDbConnection db)
        {
            string sqlQuery = @"SELECT p.*, g.name as game_name FROM package
                p JOIN game g ON p.game_id = g.id
                ORDER BY game_id";

            var packages = db.Query<Package>(sqlQuery).AsList();
            return Result<List<Package>>.Make(packages, errorIfNull: ErrorCodes.NotFound);
        }
 public void Execute(IDbConnection connect)
 {
     var query = "select * from distributors";
     var results = connect.Query(query);
     foreach (var result in results)
     {
         Console.WriteLine($"{result.id} - {result.name}");
     }
 }
        public List<Customer> GetAll(IDbConnection connection)
        {
            const string sql = _customerSelectStatement;

            var dbCustomers = connection.Query<DbCustomer>(sql).ToList();
            var customers = dbCustomers.Select( MapToCustomer );

            return customers.ToList();
        }
Example #27
0
        public Result<List<GtokenPackage>> GetBasicGtokenPackages(IDbConnection db)
        {
            string sqlString = @"SELECT * FROM gtoken_package
                                WHERE name != 'GToken' AND currency != 'IDR' AND is_archived = False
                                ORDER BY price";

            var result = db.Query<GtokenPackage>(sqlString).ToList();
            return Result<List<GtokenPackage>>.Make(result);
        }
        public SubProcessor(uint subID)
        {
            SubID = subID;

            DbConnection = Database.GetConnection();

            PackageName = DbConnection.ExecuteScalar<string>("SELECT `Name` FROM `Subs` WHERE `SubID` = @SubID LIMIT 1", new { SubID });
            CurrentData = DbConnection.Query<PICSInfo>("SELECT `Name` as `KeyName`, `Value`, `Key` FROM `SubsInfo` INNER JOIN `KeyNamesSubs` ON `SubsInfo`.`Key` = `KeyNamesSubs`.`ID` WHERE `SubID` = @SubID", new { SubID }).ToDictionary(x => x.KeyName, x => x);
        }
        /// <summary>
        /// Create exchange rate -- get from yahoo and used in a month
        /// </summary>
        /// <param name="db"></param>
        /// <param name="exchangeRateData"></param>
        /// <returns>new id</returns>
        public int CreateExchangeRate(IDbConnection db, ExchangeRateData exchangeRateData)
        {
            string sql = @"INSERT INTO exchange_rate 
            (month, source_currency, destination_currency, exchange_rate, year) 
            VALUES 
            (@month, @source_currency, @destination_currency, @exchange_rate, @year)";

            return db.Query<int>(sql, exchangeRateData).FirstOrDefault();
        }
Example #30
0
        /// <summary>
        /// Run all defined data migrations that have not been run yet
        /// </summary>
        /// <param name="db">current database connection</param>
        /// <returns>Count of migrations run</returns>
        public static int RunMigrations(IDbConnection db)
        {
            var migrationsRun = 0;
            var migrationTypes = GetMigrationTypes();

            var alreadyExecutedMigrations = db.Query<DataMigration>("select * from [{0}]".Fmt(db.GetTableName<DataMigration>())).ToList();

            if (!alreadyExecutedMigrations.Any(x => x.Name == typeof(InitializeDbMigration).Name))
            {
                // Create all data, run data init migration
                var schemaBuilder = new SchemaBuilder(db);
                schemaBuilder.GenerateSchema(false);

                var dataInitMigration = new InitializeDbMigration();
                dataInitMigration.Execute(db);

                // Populate all migrations as run
                var migrations = migrationTypes.Select(x => new DataMigration
                    {
                        Name = x.Name
                    });
                return db.BulkInsert(migrations);
            }

            var scriptedObjectsToRecreate = new List<Type>();
            foreach (var migration in migrationTypes.Where(t => !alreadyExecutedMigrations.Any(m => m.Name == t.Name)).Select(x => (IMigration)Activator.CreateInstance(x)).OrderBy(x => x.Order))
            {
                // Collect the scripted objects to recreate after migrations are run
                if (migration.ScriptedObjectsToRecreate != null && migration.ScriptedObjectsToRecreate.Any())
                {
                    foreach (var viewType in migration.ScriptedObjectsToRecreate.Where(viewType => !scriptedObjectsToRecreate.Any(v => v == viewType)))
                    {
                        scriptedObjectsToRecreate.Add(viewType);
                    }
                }

                migration.Execute(db);
                // add migration to database
                var migrationData = new DataMigration
                                        {
                                            CreatedOn = DateTime.UtcNow,
                                            Name = migration.GetType().Name
                                        };
                db.Save(migrationData);
                migrationsRun++;
            }
            if (scriptedObjectsToRecreate.Any())
            {
                foreach (var viewType in scriptedObjectsToRecreate)
                {
                    db.RecreateScriptedObject(viewType);
                }
            }

            return migrationsRun;
        }
Example #31
0
        internal IEnumerable <Trip> GetAll()
        {
            string sql = "SELECT * FROM trips;";

            return(_db.Query <Trip>(sql));
        }
Example #32
0
        public void Test_Like()
        {
            var sql = Db <DB> .Sql(db =>
                                   Select(new SelectData
            {
                Id = db.tbl_staff.id
            }).
                                   From(db.tbl_staff).
                                   Where(Like(db.tbl_staff.name, "%a%")));

            var datas = _connection.Query(sql).ToList();

            Assert.IsTrue(0 < datas.Count);
            AssertEx.AreEqual(sql, _connection,
                              @"SELECT
	tbl_staff.id AS Id
FROM tbl_staff
WHERE tbl_staff.name LIKE @p_0",
                              "%a%");
        }
        public IEnumerable <Categories> GetAllCategories()
        {
            var depos = _connection.Query <Categories>("SELECT * FROM categories");

            return(depos);
        }
Example #34
0
 public List <Temperature> GetTemperatures()
 {
     return(_db.Query <Temperature>("Temperature_Get", commandType: CommandType.StoredProcedure).ToList());
 }
 public IEnumerable <Vault> GetALL(string userId)
 {
     return(_db.Query <Vault>("SELECT * FROM vaults WHERE userId = @userId", new { userId }));
 }
Example #36
0
 /// <summary>
 /// 获取考试试卷的总题数
 /// </summary>
 /// <returns></returns>
 public static int GetAdminTestInfo()
 {
     using (IDbConnection conn = GetConnection()) {
         return(conn.Query <int>("SELECT Score FROM TestInfo WHERE OpenId='Admin'").First());
     }
 }
Example #37
0
        //NOTE dotnet add package dapper - to be able to communicate with db
        public IEnumerable <Drink> GetAll()
        {
            string sql = "SELECT * FROM drinks;";

            return(_db.Query <Drink>(sql));
        }
Example #38
0
        public static TModel Find <TModel>(this IDbConnection cnn, object param, Expression <Func <TModel, object> > selector = null, IDbTransaction transaction = null) where TModel : ModelBase
        {
            QueryTest query = GetQuery(true, param, selector);

            return(cnn.Query <TModel>(query.Query, query.Param, transaction: transaction).FirstOrDefault());
        }
Example #39
0
        public static IEnumerable <TModel> All <TModel>(this IDbConnection cnn, object param = null, Expression <Func <TModel, object> > selector = null, IDbTransaction transaction = null) where TModel : ModelBase
        {
            QueryTest query = GetQuery <TModel>(false, param as object, selector);

            return(cnn.Query <TModel>(query.Query, query.Param, transaction: transaction));
        }
Example #40
0
 public IEnumerable <T> Query <T>(string sql, object param = null)
 {
     return(connection.Query <T>(sql, param, transaction));
 }
Example #41
0
 /// <summary>
 /// Запрос, возвращающий результат
 /// </summary>
 public IEnumerable <dynamic> Query(string query, object param = null, CommandType?type = null)
 {
     return(connection?.State == ConnectionState.Closed
         ? null
         : connection?.Query <dynamic>(query, param, commandType: type, transaction: transaction));
 }
Example #42
0
 public IEnumerable <Pin> GetPins()
 {
     return(_db.Query <Pin>("SELECT * FROM pins WHERE public = 1;"));
 }
Example #43
0
 public List <Ticker> GetAll()
 {
     return(_database.Query <Ticker>("SELECT code, type, trading_name as tradingName FROM tickers").ToList());
 }
Example #44
0
        public dynamic Insert <T>(IDbConnection connection, T entity, IDbTransaction transaction, int?commandTimeout) where T : class
        {
            IClassMapper        classMap = SqlGenerator.Configuration.GetMap <T>();
            List <IPropertyMap> nonIdentityKeyProperties = classMap.Properties.Where(p => p.KeyType == KeyType.Guid || p.KeyType == KeyType.Assigned).ToList();
            var identityColumn        = classMap.Properties.SingleOrDefault(p => p.KeyType == KeyType.Identity);
            var triggerIdentityColumn = classMap.Properties.SingleOrDefault(p => p.KeyType == KeyType.TriggerIdentity);

            foreach (var column in nonIdentityKeyProperties)
            {
                if (column.KeyType == KeyType.Guid && (Guid)column.PropertyInfo.GetValue(entity, null) == Guid.Empty)
                {
                    Guid comb = SqlGenerator.Configuration.GetNextGuid();
                    column.PropertyInfo.SetValue(entity, comb, null);
                }
            }

            IDictionary <string, object> keyValues = new ExpandoObject();
            string sql = SqlGenerator.Insert(classMap);

            if (identityColumn != null)
            {
                IEnumerable <long> result;
                if (SqlGenerator.SupportsMultipleStatements())
                {
                    sql   += SqlGenerator.Configuration.Dialect.BatchSeperator + SqlGenerator.IdentitySql(classMap);
                    result = connection.Query <long>(sql, entity, transaction, false, commandTimeout, CommandType.Text);
                }
                else
                {
                    connection.Execute(sql, entity, transaction, commandTimeout, CommandType.Text);
                    sql    = SqlGenerator.IdentitySql(classMap);
                    result = connection.Query <long>(sql, entity, transaction, false, commandTimeout, CommandType.Text);
                }

                // We are only interested in the first identity, but we are iterating over all resulting items (if any).
                // This makes sure that ADO.NET drivers (like MySql) won't actively terminate the query.
                bool hasResult   = false;
                int  identityInt = 0;
                foreach (var identityValue in result)
                {
                    if (hasResult)
                    {
                        continue;
                    }
                    identityInt = Convert.ToInt32(identityValue);
                    hasResult   = true;
                }
                if (!hasResult)
                {
                    throw new InvalidOperationException("The source sequence is empty.");
                }

                keyValues.Add(identityColumn.Name, identityInt);
                identityColumn.PropertyInfo.SetValue(entity, identityInt, null);
            }
            else if (triggerIdentityColumn != null)
            {
                var dynamicParameters = new DynamicParameters();
                foreach (var prop in entity.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Instance | BindingFlags.Public)
                         .Where(p => p.Name != triggerIdentityColumn.PropertyInfo.Name))
                {
                    dynamicParameters.Add(prop.Name, prop.GetValue(entity, null));
                }

                // defaultValue need for identify type of parameter
                var defaultValue = entity.GetType().GetProperty(triggerIdentityColumn.PropertyInfo.Name).GetValue(entity, null);
                dynamicParameters.Add("IdOutParam", direction: ParameterDirection.Output, value: defaultValue);

                connection.Execute(sql, dynamicParameters, transaction, commandTimeout, CommandType.Text);

                var value = dynamicParameters.Get <object>(SqlGenerator.Configuration.Dialect.ParameterPrefix + "IdOutParam");
                keyValues.Add(triggerIdentityColumn.Name, value);
                triggerIdentityColumn.PropertyInfo.SetValue(entity, value, null);
            }
            else
            {
                connection.Execute(sql, entity, transaction, commandTimeout, CommandType.Text);
            }

            foreach (var column in nonIdentityKeyProperties)
            {
                keyValues.Add(column.Name, column.PropertyInfo.GetValue(entity, null));
            }

            if (keyValues.Count == 1)
            {
                return(keyValues.First().Value);
            }

            return(keyValues);
        }
Example #45
0
 public static ItemAction GetItemAction(string OpenId, string BranchID)
 {
     using (IDbConnection conn = GetConnection()) {
         return(conn.Query <ItemAction>("SELECT * FROM ItemAction WHERE OpenId=@OpenId AND BranchID=@BranchID", new { OpenId, BranchID }).FirstOrDefault());
     }
 }
Example #46
0
 /// <summary>
 /// 查询方法
 /// </summary>
 /// <typeparam name="T"></typeparam>
 /// <param name="sql"></param>
 /// <param name="param"></param>
 /// <param name="transaction"></param>
 /// <param name="buffered"></param>
 /// <param name="commandTimeout"></param>
 /// <param name="commandType"></param>
 /// <returns></returns>
 public IEnumerable <T> Query <T>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int?commandTimeout = null, CommandType?commandType = null)
 {
     return(_dbConnection.Query <T>(sql, param, transaction, buffered, commandTimeout, commandType));
 }
Example #47
0
 public static UserInfo GetUserInfo(string OpenId)
 {
     using (IDbConnection conn = GetConnection()) {
         return(conn.Query <UserInfo>("SELECT * FROM UserInfo WHERE OpenId=@OpenId", new { OpenId }).FirstOrDefault());
     }
 }
Example #48
0
        //Biertjes
        public ObservableCollection <Biertjes> GetBiertjes()
        {
            string sql = "Select * from biertjes order by naam";

            return(db.Query <Biertjes>(sql).ToObservableCollection());
        }
Example #49
0
        public IEnumerable <Vault> Get()
        {
            string sql = populateCreator;

            return(_db.Query <Vault, Profile, Vault>(sql, (vault, profile) => { vault.Creator = profile; return vault; }, splitOn: "id"));
        }
Example #50
0
 public IEnumerable <dynamic> Query(bool buffered = true)
 {
     return(_c.Query(_command, _params, _t, buffered, _timeout, _commandType));
 }
Example #51
0
 public static IEnumerable <Item> GetByAuctionId(IDbConnection db, int auctionId)
 {
     return(db.Query <Item>(@"select * from AA_Items where AuctionId = @id order by rank", new { id = auctionId }));
 }
 public IEnumerable <Products> GetAllProducts()
 {
     return(_conn.Query <Products>("SELECT * FROM PRODUCTS;"));
 }
        public List <T> ToList()
        {
            SqlProvider.FormatToList();

            return(DbCon.Query <T>(SqlProvider.SqlString, SqlProvider.Params).ToList());
        }
        public IEnumerable <Wishlist> Get()
        {
            string sql = "SELECT * from wishlists";

            return(_db.Query <Wishlist>(sql));
        }
Example #55
0
 public Employee Login(string Email, string Password)
 {
     return(_db.Query <Employee>("SELECT [Email],[Password] FROM [Employee] WHERE Email = @Email & Password = @Password",
                                 new { Email = Email, Password = Password }).FirstOrDefault());
 }
Example #56
0
 public static List <ItemAction> GetItemActions(string OpenId)
 {
     using (IDbConnection conn = GetConnection()) {
         return(conn.Query <ItemAction>("SELECT * FROM ItemAction WHERE OpenId=@OpenId", new { OpenId }).ToList());
     }
 }
Example #57
0
 public Date GetSingleDate(string date)
 {
     return(_db.Query <Date>("EXEC acc.pr_Date @date = @date", new { Date = date }).SingleOrDefault());
 }
Example #58
0
 protected override IEnumerable <int> GetExists(IDbConnection conn)
 {
     return(conn.Query <int>("select id from workflow"));
 }
        internal IEnumerable <Classroom> Get()
        {
            string sql = "SELECT * FROM classrooms";

            return(_db.Query <Classroom>(sql));
        }
Example #60
0
 public static ContestItem GetContestItem(int BranchID)
 {
     using (IDbConnection conn = GetConnection()) {
         return(conn.Query <ContestItem>("SELECT * FROM ContestItem WHERE BranchID=@BranchID", new { BranchID }).FirstOrDefault());
     }
 }