public static async Task AddManyUsersAsync(User[] users)
{
using (var conn = DapperConnectionFactory.CreateConnection()){
var affectedRows = await conn.ExecuteAsync("insert into user (username, password) values (@username, @password)", users.Select(u => new { username = u.Username, password = u.Password }));
Console.WriteLine($"Number of rows effected {affectedRows}");
}
}
public static async Task <bool> DeleteUserAsync(int id)
{
using (var conn = DapperConnectionFactory.CreateConnection()){
var rowsAffected = await conn.ExecuteAsync("delete User where userid = @userid", new { userid = id });
return(rowsAffected > 0);
}
}
public static async Task <IEnumerable <User> > SelectUsersAsync()
{
IEnumerable <User> users = null;
using (var conn = DapperConnectionFactory.CreateConnection()){
users = await conn.QueryAsync <User>("SELECT * from User");
}
return(users);
}
public static IEnumerable <User> SelectUsers()
{
IEnumerable <User> users = null;
using (var conn = DapperConnectionFactory.CreateConnection()){
users = conn.Query <User>("SELECT * from User");
}
return(users);
}
//* Initialize an example DB
public static async Task GettingStarted()
{
string dropTableSql = "drop table user;";
string createTableSql = "create table user(userid integer primary key autoincrement, username varchar(300), password varchar(300) default \"password\", email varchar(300) null);";
string populateTableSql = "insert into user(username) values (@uname);";
string[] initialUsers = new string[] {
"user1",
"user2",
"user3"
};
using (var conn = DapperConnectionFactory.CreateConnection()){
//* Try to drop DB, and its ok to fail if not there
await conn.ExecuteAsync(dropTableSql);
//* Creating a table to use
await conn.ExecuteAsync(createTableSql);
//* Add basic users
var users = initialUsers.Select(u => new { uname = u }).ToArray();
await conn.ExecuteAsync(populateTableSql, users);
}
}
public static async Task UpdateUsersAsync(int id)
{
using (var conn = DapperConnectionFactory.CreateConnection()){
await conn.ExecuteAsync("UPDATE User SET username = @username where userid = @userid", new { username = "******", userid = id });
}
}