public static void DBFill(SqliteDataReader reader, User user)
{
user.UserID = Convert.ToInt32(reader["user_id"]);
user.UserName = reader["username"].ToString();
user.Password = reader["password"].ToString();
user.FirstName = reader["first_name"].ToString();
user.LastName = reader["last_name"].ToString();
user.Email = reader["email"].ToString();
user.IsActive = Convert.ToBoolean(reader["is_active"]);
user.Roles = Role.GetRolesByUser(user.UserID);
}
// login
public static User Login(string _username, string _password, ref string error)
{
User user = new User();
string query = @"SELECT * FROM kk_user
WHERE username = @username
AND password = @password LIMIT 1";
// get database connection
using (SqliteConnection conn = new SqliteConnection(connectionString))
{
conn.Open();
// execute cmd
using (SqliteCommand cmd = new SqliteCommand(query, conn))
{
// load parameters
cmd.Parameters.Add("@username", System.Data.DbType.String).Value = _username;
cmd.Parameters.Add("@password", System.Data.DbType.String).Value = _password;
// open database connection
// user reader to fill data
using (SqliteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DBFill(reader, user);
}
}
}
}
return user;
}
public static User GetByUserName(string userName)
{
User user = new User();
string query = "SELECT * FROM kk_user WHERE username = @username;";
using (SqliteConnection conn = new SqliteConnection(connectionString))
{
using (SqliteCommand cmd = new SqliteCommand(query, conn))
{
cmd.Parameters.Add("@username", System.Data.DbType.String).Value = userName;
conn.Open();
using (SqliteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DBFill(reader, user);
}
}
}
}
return user;
}
public static void LoadParams(SqliteCommand cmd, User user)
{
cmd.Parameters.Add("@username", System.Data.DbType.String).Value = user.UserName;
cmd.Parameters.Add("@first_name", System.Data.DbType.String).Value = user.FirstName;
cmd.Parameters.Add("@last_name", System.Data.DbType.String).Value = user.LastName;
if (user.CreatedByID != null)
{
cmd.Parameters.Add("@created_by", System.Data.DbType.Int32).Value = user.CreatedByID;
}
else
{
cmd.Parameters.Add("@created_by", System.Data.DbType.Int32).Value = DBNull.Value;
}
cmd.Parameters.Add("@email", System.Data.DbType.String).Value = user.Email;
}
public static List<User> GetAll()
{
List<User> users = new List<User>();
string query = "SELECT * FROM kk_user ORDER BY last_name, first_name;";
using (SqliteConnection conn = new SqliteConnection(connectionString))
{
using (SqliteCommand cmd = new SqliteCommand(query, conn))
{
conn.Open();
using (SqliteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
User user = new User();
DBFill(reader, user);
users.Add(user);
}
}
}
}
return users;
}