public static int CreatePermissionGroup(string groupName)
{
const string query = "INSERT INTO (name) permission_groups VALUES (@name)";
DbManager.PrepareQuery(query);
DbManager.BindValue("@name", groupName);
DbManager.ExecutePreparedInsertUpdateDelete();
return(DbManager.GetLastId());
}
public static int CreateCategory(Category category)
{
const string query = "INSERT INTO categories (parent_category_id, name) VALUES (@parentCategoryId, @name)";
DbManager.PrepareQuery(query);
DbManager.BindValue("@parentCategoryId", category.ParentCategoryId);
DbManager.BindValue("@name", category.Name);
DbManager.ExecutePreparedInsertUpdateDelete();
return(DbManager.GetLastId());
}
public static int CreatePost(int topicId, string content, int userId, DateTime createTime)
{
const string query =
"INSERT INTO posts (topic_id, content, user_id, create_time) VALUES (@topicId, @content, @userId, @createTime)";
DbManager.PrepareQuery(query);
DbManager.BindValue("@topicId", topicId);
DbManager.BindValue("@content", content);
DbManager.BindValue("@userId", userId);
DbManager.BindValue("@createTime", createTime);
DbManager.ExecutePreparedInsertUpdateDelete();
return(DbManager.GetLastId());
}
public static User GetUser(string username)
{
string query = "SELECT id, password " +
"FROM users " +
"WHERE username = @username";
DbManager.PrepareQuery(query);
DbManager.BindValue("@username", username.ToLower());
var reader = DbManager.ExecutePreparedSelect();
reader.Read();
if (!reader.HasRows)
{
reader.Close();
return(null);
}
var id = reader.GetInt32(0);
var passwordHash = reader.GetString(1);
reader.Close();
return(new User(id, username, passwordHash));
}
public static void SelectWithPrepare()
{
var filter = "test";
const string query = "SELECT * FROM users WHERE username LIKE @filter";
_dbManagerAuth.PrepareQuery(query);
_dbManagerAuth.BindValue("@filter", $"%{filter}%");
var reader = _dbManagerAuth.ExecutePreparedSelect();
var foundUsers = new List <User>();
while (reader.Read())
{
var id = reader.GetInt32(0);
var username = reader.GetString(1);
foundUsers.Add(new User(id, username));
}
}
public static int CreateTopic(Topic topic)
{
const string query =
"INSERT INTO topics (parent_category_id, title, user_id, closed, sticky, create_time) VALUES (@parent_category_id, @title, @user_id, @closed, @sticky, @create_time)";
DbManager.PrepareQuery(query);
DbManager.BindValue("@parent_category_id", topic.Category.Id);
DbManager.BindValue("@title", topic.Title);
DbManager.BindValue("@closed", topic.Closed);
DbManager.BindValue("@sticky", topic.Sticky);
DbManager.BindValue("@create_time", topic.CreateTime);
DbManager.ExecutePreparedInsertUpdateDelete();
return(DbManager.GetLastId());
}
