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)
{
}
