public bool UpdateUserById(int userId, SQLiteUser update, SQLiteConnecter connecter)
{
string updateCommand = string.Format("UPDATE User set Username='******', Password='******', Age='{2}', Gender='{3}' where id='{5}'",
update.Username, update.Password, update.Age, update.Gender, userId);
return(ExecuteCommand(updateCommand, connecter.connection));
}
public override void CreateTable(SQLiteConnecter connecter)
{
string createCommand = string.Format("CREATE TABLE IF NOT EXISTS User {0}, {1}, {2}, {3}, {4})",
"([id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT", "Username varchar(50)", "Password varchar(50)", "Age int", "Gender varchar(10)");
SQLiteCommand command = new SQLiteCommand(createCommand, connecter.connection);
command.ExecuteNonQuery();
}
public override void CreateTable(SQLiteConnecter connecter)
{
string createCommand = string.Format("CREATE TABLE IF NOT EXISTS Template {0}, {1}, {2}, {3})",
"([id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT", "Name varchar(50)", "Type varchar(50)", "Description varchar(100)");
SQLiteCommand command = new SQLiteCommand(createCommand, connecter.connection);
command.ExecuteNonQuery();
}
public SQLiteUser GetUserById(int userId, SQLiteConnecter connecter)
{
DataSet data = new DataSet();
SQLiteDataAdapter adapter = new SQLiteDataAdapter(
string.Format("select id, Username, Password, Age, Gender from User where id='{0}'", userId),
connecter.connection);
adapter.Fill(data);
DataRow entity = data.Tables[0].Rows[0];
return(Utilities.ToObject <SQLiteUser>(entity));
}
public IEnumerable <SQLiteUser> GetListUsers(SQLiteConnecter connecter)
{
DataSet data = new DataSet();
SQLiteDataAdapter adapter = new SQLiteDataAdapter(
string.Format("select id, Username, Password, Age, Gender from User"),
connecter.connection);
adapter.Fill(data);
DataRowCollection dataList = data.Tables[0].Rows;
int count = dataList.Count;
for (int i = 0; i < count; i++)
{
yield return(Utilities.ToObject <SQLiteUser>(dataList[i]));
}
}
public Result Login(string username, string password, SQLiteConnecter connecter)
{
DataSet data = new DataSet();
SQLiteDataAdapter adapter = new SQLiteDataAdapter(
string.Format("select id, Username, Password, Age, Gender from User where Username='******' AND Password='******'", username, password),
connecter.connection);
adapter.Fill(data);
DataRow entity = data.Tables[0].Rows[0];
SQLiteUser user = Utilities.ToObject <SQLiteUser>(entity);
return(new Result
{
status = Status.OK,
data = user
});
}
public Result InsertUser(SQLiteUser user, SQLiteConnecter connecter)
{
string insertCommand = string.Format("INSERT INTO User(Username,Password,Age,Gender) VALUES('{0}', '{1}', '{2}', '{3}')",
user.Username, user.Password, user.Age, user.Gender);
if (ExecuteCommand(insertCommand, connecter.connection))
{
string getIdCoommand = "SELECT last_insert_rowid()";
int _id = Convert.ToInt32(ExecuteScalar(getIdCoommand, connecter.connection));
return(new Result
{
status = Status.Created,
data = _id
});
}
else
{
return new Result
{
status = Status.BadRequest,
data = Messages.BadRequest
}
};
}
public bool DeleteUserById(int userId, SQLiteConnecter connecter)
{
string updateCommand = string.Format("DELETE FROM User where id='{0}'", userId);
return(ExecuteCommand(updateCommand, connecter.connection));
}
public virtual void CreateTable(SQLiteConnecter connecter)
{
}