public MemberCardCategory find(int id)
{
MemberCardCategory category = null;
String sql = String.Format("SELECT * FROM members_cards_categories WHERE id = {0}", id);
using (MySqlDataReader rdr = Tools.MySqlHelper.ExecuteReader(Tools.MySqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql))
{
category = fillMemberCardCategory(rdr);
}
return(category);
}
public int update(MemberCardCategory model)
{
List <MySqlParameter> parameters = this.fillParameters(model);
parameters.Add(new MySqlParameter("@id", MySqlDbType.Int32, 11)
{
Value = model.Id
});
MySqlParameter[] param = this.ConvertMySqlParameters(parameters);
String sql = "UPDATE members_cards SET card_no = @card_no, category_id = @category_id, updated_at = @updated_at WHERE id = @id;";
return(Tools.MySqlHelper.ExecuteNonQuery(Tools.MySqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, param));
}
public int save(MemberCardCategory model)
{
List <MySqlParameter> parameters = this.fillParameters(model);
parameters.Add(new MySqlParameter("@id", MySqlDbType.Int32, 11)
{
Value = model.Id
});
MySqlParameter[] param = this.ConvertMySqlParameters(parameters);
String sql = "INSERT INTO members_cards(card_no, category_id, created_at) values(@card_no, @category_id, @created_at);";
return(Tools.MySqlHelper.ExecuteNonQuery(Tools.MySqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, param));
}
public int update(MemberCardCategory model)
{
List <SQLiteParameter> parameters = this.fillParameters(model);
parameters.Add(new SQLiteParameter("@id", DbType.Int32, 11)
{
Value = model.Id
});
SQLiteParameter[] param = this.ConvertSQLiteParameters(parameters);
String sql = "UPDATE members_cards_categories SET name = @name, begin_at = @begin_at, end_at = @end_at, not_allow = @not_allow, updated_at = @updated_at WHERE id = @id;";
return(Tools.SQLiteHelper.ExecuteNonQuery(Tools.SQLiteHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, param));
}
private MemberCardCategory fillMemberCardCategory(MySqlDataReader rdr)
{
MemberCardCategory category = null;
if (rdr.Read())
{
category = new MemberCardCategory();
category.Id = rdr["id"] == DBNull.Value ? 0 : Convert.ToInt32(rdr["id"]);
category.Name = rdr["name"] == DBNull.Value ? "" : rdr["name"].ToString();
category.BeginAt = rdr["begin_at"] == DBNull.Value ? "" : rdr["begin_at"].ToString();
category.EndAt = rdr["end_at"] == DBNull.Value ? "" : rdr["end_at"].ToString();
category.CreatedAt = rdr["created_at"] == DBNull.Value ? 0 : Convert.ToInt32(rdr["created_at"]);
category.UpdatedAt = rdr["updated_at"] == DBNull.Value ? 0 : Convert.ToInt32(rdr["updated_at"]);
}
return(category);
}
private List <MySqlParameter> fillParameters(MemberCardCategory model)
{
MySqlParameter[] parameters =
{
new MySqlParameter("@name", MySqlDbType.VarChar, 11),
new MySqlParameter("@begin_at", MySqlDbType.String, 10),
new MySqlParameter("@end_at", MySqlDbType.String, 11),
new MySqlParameter("@created_at", MySqlDbType.Int32, 11),
new MySqlParameter("@updated_at", MySqlDbType.Int32, 11),
};
parameters[0].Value = model.Name;
parameters[1].Value = model.BeginAt;
parameters[2].Value = model.EndAt;
parameters[3].Value = model.CreatedAt;
parameters[4].Value = model.UpdatedAt;
return(parameters.ToList());
}
public int save(MemberCardCategory model)
{
List <SQLiteParameter> parameters = this.fillParameters(model);
SQLiteParameter[] param = this.ConvertSQLiteParameters(parameters);
String sql = "INSERT INTO members_cards_categories(name, begin_at, end_at, not_allow, created_at) values(@name, @begin_at, @end_at, @not_allow, @created_at);";
int row = Tools.SQLiteHelper.ExecuteNonQuery(Tools.SQLiteHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, param);
if (row > 0)
{
sql = sql.Replace("@name", String.Format("'{0}'", model.Name));
sql = sql.Replace("@begin_at", String.Format("{0}", model.BeginAt));
sql = sql.Replace("@end_at", String.Format("{0}", model.EndAt));
sql = sql.Replace("@not_allow", String.Format("'{0}'", model.Name));
sql = sql.Replace("@created_at", String.Format("{0}", model.CreatedAt));
this.SaveQueue(sql);
}
return(row);
}
private List <SQLiteParameter> fillParameters(MemberCardCategory model)
{
SQLiteParameter[] parameters =
{
new SQLiteParameter("@name", DbType.String, 11),
new SQLiteParameter("@begin_at", DbType.String, 10),
new SQLiteParameter("@end_at", DbType.String, 11),
new SQLiteParameter("@created_at", DbType.Int32, 11),
new SQLiteParameter("@updated_at", DbType.Int32, 11),
new SQLiteParameter("@not_allow", DbType.String, 1000),
};
parameters[0].Value = model.Name;
parameters[1].Value = model.BeginAt;
parameters[2].Value = model.EndAt;
parameters[3].Value = model.CreatedAt;
parameters[4].Value = model.UpdatedAt;
return(parameters.ToList());
}
public List <MemberCardCategory> findAll()
{
List <MemberCardCategory> categories = null;
String sql = String.Format("SELECT * FROM members_cards_categories");
using (SQLiteDataReader rdr = Tools.SQLiteHelper.ExecuteReader(Tools.SQLiteHelper.ConnectionStringLocalTransaction, CommandType.Text, sql))
{
while (true)
{
if (categories == null)
{
categories = new List <MemberCardCategory>();
}
MemberCardCategory category = fillMemberCardCategory(rdr);
if (category == null)
{
break;
}
categories.Add(category);
}
}
return(categories);
}
public bool EditMemberCardCategory(MemberCardCategory model)
{
throw new System.NotImplementedException();
}