public void SelectQueryTest() { DbConnection dbConnection = new SqlServerDbConnection(MockData.BullHost, MockData.BullPort, MockData.BullDb, MockData.BullUser, MockData.BullPass); DataTable tableResult = dbConnection.RunQuery(MockData.QueryGetAtRecords); Assert.AreNotEqual(0, tableResult.Rows.Count); }
public async Task <int> Add(Card card) { var query = @"Insert cards (question,answer,categoryId,userId) values (@question , @answer , @categoryId, @userId)"; return(await SqlServerDbConnection.ExecuteAsync(query, new { card.question, card.answer, card.categoryId, card.userId })); }
public async Task <int> Add(Category category) { var query = @"Insert categories (title,userId) values (@title , @userId)"; return(await SqlServerDbConnection.ExecuteAsync(query, new { category.title, category.userId })); }
public void ConnectTest() { DbConnection dbConnection = new SqlServerDbConnection(MockData.BullHost, MockData.BullPort, MockData.BullDb, MockData.BullUser, MockData.BullPass); Assert.IsFalse(dbConnection.IsAlive); Assert.AreEqual(System.Data.ConnectionState.Closed, dbConnection.MyCurrentState); dbConnection.Connect(); Assert.IsTrue(dbConnection.IsAlive); Assert.AreEqual(System.Data.ConnectionState.Open, dbConnection.MyCurrentState); }
public async Task <List <Category> > GetAll(int userId) { var query = @"select c.id, c.createdAt, c.isActive, c.title, c.userId, c.createdAt, COUNT(cards.id) as card_count from Categories c left join cards on cards.categoryId = c.id where c.userId = 1 group by c.id, c.createdAt, c.isActive, c.title, c.userId, c.createdAt"; return((await SqlServerDbConnection.QueryAsync <Category>(query, new { userId })).ToList()); }
public static IAVSCDatabase GetIAVSCDatabase ( DatabaseType databaseType, string serverName, string username, string password, string databaseName ) { IAVSCDatabase db; switch (databaseType) { case DatabaseType.Postgres: { db = new PostgresDbConnection( serverName, username, password, databaseName); } break; case DatabaseType.MySql: { db = new MySqlDbConnection( serverName, username, password, databaseName); } break; case DatabaseType.SqlServer: { db = new SqlServerDbConnection( serverName, username, password, databaseName); } break; default: { throw new NotSupportedDatabaseException(); } } return(db); }
public async Task <Card> GetNextTodayCard(int categoryId, int userId) { var query = @"select top 1 cards.id, question, answer, card_level, updateAt, categoryId, cards.isActive, cards.userId from cards inner join categories on cards.categoryId = categories.Id where DATEDIFF(DAY, updateAt, getdate()) >= POWER(2, card_level) and cards.userId = @userId and (categoryId > CASE WHEN @categoryId = 0 THEN 0 END OR categoryId = CASE WHEN @categoryId <> 0 THEN @categoryId END) order by updateAt"; return((await SqlServerDbConnection.QueryAsync <Card>(query, new { categoryId, userId })) .FirstOrDefault()); }
public static IAVSCDatabase GetIAVSCDatabase ( DatabaseType databaseType, string connectionString ) { IAVSCDatabase db = null; switch (databaseType) { case DatabaseType.Postgres: { db = new PostgresDbConnection(connectionString); } break; case DatabaseType.MySql: { db = new MySqlDbConnection(connectionString); } break; case DatabaseType.SqlServer: { db = new SqlServerDbConnection(connectionString); } break; default: { throw new NotSupportedDatabaseException(); } } return(db); }
public async Task <int> Update(Card card) { var query = @"update cards set card_level=@card_level , updateAt=GetDate() where Id=@Id"; return(await SqlServerDbConnection.ExecuteAsync(query, new { card.Id, card.card_level })); }
public async Task <List <Card> > GetAll() { var query = "select * from Cards"; return((await SqlServerDbConnection.QueryAsync <Card>(query)).ToList()); }
public async Task <User> Login(UserLoginRequestDTO loginRequestDTO) { var query = "select * from Users where username=@username and password =@password"; return((await SqlServerDbConnection.QueryAsync <User>(query, new { username = loginRequestDTO.UserName, password = loginRequestDTO.Password })).FirstOrDefault()); }
public async Task <Category> GetByCategoryId(int categoryId) { var query = "select top 1 * from Categories where Id=@categoryId"; return((await SqlServerDbConnection.QueryAsync <Category>(query, new { categoryId })).FirstOrDefault()); }
/// <summary> /// 使用链接类初始化oracle的链接 /// </summary> /// <param name="Connection"></param> public SqlServerDbHelper(SqlServerDbConnection Connection) : base(Connection) { }