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 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; }
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; }
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); } }
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; }
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); }
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); }
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(); }
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(); }
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(); }
/// <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(); }
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); }
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); }
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(); }
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(); }
/// <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; }
internal IEnumerable <Trip> GetAll() { string sql = "SELECT * FROM trips;"; return(_db.Query <Trip>(sql)); }
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); }
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 })); }
/// <summary> /// 获取考试试卷的总题数 /// </summary> /// <returns></returns> public static int GetAdminTestInfo() { using (IDbConnection conn = GetConnection()) { return(conn.Query <int>("SELECT Score FROM TestInfo WHERE OpenId='Admin'").First()); } }
//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)); }
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()); }
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)); }
public IEnumerable <T> Query <T>(string sql, object param = null) { return(connection.Query <T>(sql, param, transaction)); }
/// <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)); }
public IEnumerable <Pin> GetPins() { return(_db.Query <Pin>("SELECT * FROM pins WHERE public = 1;")); }
public List <Ticker> GetAll() { return(_database.Query <Ticker>("SELECT code, type, trading_name as tradingName FROM tickers").ToList()); }
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); }
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()); } }
/// <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)); }
public static UserInfo GetUserInfo(string OpenId) { using (IDbConnection conn = GetConnection()) { return(conn.Query <UserInfo>("SELECT * FROM UserInfo WHERE OpenId=@OpenId", new { OpenId }).FirstOrDefault()); } }
//Biertjes public ObservableCollection <Biertjes> GetBiertjes() { string sql = "Select * from biertjes order by naam"; return(db.Query <Biertjes>(sql).ToObservableCollection()); }
public IEnumerable <Vault> Get() { string sql = populateCreator; return(_db.Query <Vault, Profile, Vault>(sql, (vault, profile) => { vault.Creator = profile; return vault; }, splitOn: "id")); }
public IEnumerable <dynamic> Query(bool buffered = true) { return(_c.Query(_command, _params, _t, buffered, _timeout, _commandType)); }
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)); }
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()); }
public static List <ItemAction> GetItemActions(string OpenId) { using (IDbConnection conn = GetConnection()) { return(conn.Query <ItemAction>("SELECT * FROM ItemAction WHERE OpenId=@OpenId", new { OpenId }).ToList()); } }
public Date GetSingleDate(string date) { return(_db.Query <Date>("EXEC acc.pr_Date @date = @date", new { Date = date }).SingleOrDefault()); }
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)); }
public static ContestItem GetContestItem(int BranchID) { using (IDbConnection conn = GetConnection()) { return(conn.Query <ContestItem>("SELECT * FROM ContestItem WHERE BranchID=@BranchID", new { BranchID }).FirstOrDefault()); } }