public int insert(MenuModels item)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("INSERT ");
sqlBuilder.Append("INTO ");
sqlBuilder.Append("gov_menu ");
sqlBuilder.Append("( ");
sqlBuilder.Append("id_parent, ");
sqlBuilder.Append("title, ");
sqlBuilder.Append("description, ");
sqlBuilder.Append("order_number, ");
sqlBuilder.Append("active_flg, ");
sqlBuilder.Append("link, ");
sqlBuilder.Append("entry_username, ");
sqlBuilder.Append("entry_datetime, ");
sqlBuilder.Append("update_username, ");
sqlBuilder.Append("update_datetime,");
sqlBuilder.Append("ishome");
sqlBuilder.Append(") ");
sqlBuilder.Append("values(");
sqlBuilder.Append("@1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11");
sqlBuilder.Append(")");
this.Sql = sqlBuilder.ToString();
MySqlCommand objCmd = new MySqlCommand(Sql, getConnection());
objCmd.Parameters.AddWithValue("@1", item.ParentId);
objCmd.Parameters.AddWithValue("@2", item.Title);
objCmd.Parameters.AddWithValue("@3", item.Description);
objCmd.Parameters.AddWithValue("@4", item.OrderNumber);
objCmd.Parameters.AddWithValue("@5", item.AtiveFlg);
objCmd.Parameters.AddWithValue("@6", item.Link);
objCmd.Parameters.AddWithValue("@7", item.EntryUsername);
objCmd.Parameters.AddWithValue("@8", item.EntryDatetime);
objCmd.Parameters.AddWithValue("@9", item.UpdateUsername);
objCmd.Parameters.AddWithValue("@10", item.UpdateDatetime);
objCmd.Parameters.AddWithValue("@11", item.IsHome);
objCmd.ExecuteNonQuery();
objCmd.Parameters.Clear();
objCmd.CommandText = "SELECT @@IDENTITY";
int identity = Convert.ToInt32(objCmd.ExecuteScalar());
return identity;
}
public int update(MenuModels item)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("UPDATE ");
sqlBuilder.Append("gov_menu ");
sqlBuilder.Append("SET ");
sqlBuilder.Append("id_parent = @1, ");
sqlBuilder.Append("title = @2, ");
sqlBuilder.Append("description = @3, ");
sqlBuilder.Append("order_number = @4, ");
sqlBuilder.Append("active_flg = @5, ");
sqlBuilder.Append("link = @6, ");
sqlBuilder.Append("update_username= @7, ");
sqlBuilder.Append("update_datetime = @8, ");
sqlBuilder.Append("entry_username= @9, ");
sqlBuilder.Append("entry_datetime = @10, ");
sqlBuilder.Append("ishome = @11 ");
sqlBuilder.Append("WHERE ");
sqlBuilder.Append("id = @12 ");
this.Sql = sqlBuilder.ToString();
MySqlCommand objCmd = new MySqlCommand(Sql, getConnection());
objCmd.Parameters.AddWithValue("@1", item.ParentId);
objCmd.Parameters.AddWithValue("@2", item.Title);
objCmd.Parameters.AddWithValue("@3", item.Description);
objCmd.Parameters.AddWithValue("@4", item.OrderNumber);
objCmd.Parameters.AddWithValue("@5", item.AtiveFlg);
objCmd.Parameters.AddWithValue("@6", item.Link);
objCmd.Parameters.AddWithValue("@7", item.UpdateUsername);
objCmd.Parameters.AddWithValue("@8", item.UpdateDatetime);
objCmd.Parameters.AddWithValue("@9", item.EntryUsername);
objCmd.Parameters.AddWithValue("@10", item.EntryDatetime);
objCmd.Parameters.AddWithValue("@11", item.IsHome);
objCmd.Parameters.AddWithValue("@12", item.Id);
int rs = objCmd.ExecuteNonQuery();
return rs;
}
public List<MenuModels> select(int page, int limit)
{
int start = (page - 1) * limit;
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("SELECT ");
sqlBuilder.Append("* ");
sqlBuilder.Append("FROM ");
sqlBuilder.Append("gov_menu gm ");
sqlBuilder.Append(getBaseSQL());
if(page > 0 && limit > 0){
sqlBuilder.Append(" LIMIT @limit ");
sqlBuilder.Append(" OFFSET @start ");
}
this.Sql = sqlBuilder.ToString();
MySqlCommand objCmd = new MySqlCommand(Sql, getConnection());
if(page > 0 && limit > 0){
objCmd.Parameters.AddWithValue("@limit", limit);
objCmd.Parameters.AddWithValue("@start", start);
}
MySqlDataReader dataReader = objCmd.ExecuteReader();
List<MenuModels> lstMenu = new List<MenuModels>();
MenuModels item;
while (dataReader.Read())
{
item = new MenuModels();
item.Id = Convert.ToInt32(dataReader["id"]);
item.ParentId = Convert.ToInt32(dataReader["id_parent"]);
item.Title = dataReader["title"].ToString();
item.Description = dataReader["description"].ToString();
item.OrderNumber = Convert.ToInt32(dataReader["order_number"]);
item.Link = dataReader["link"].ToString();
item.AtiveFlg = Convert.ToBoolean(dataReader["active_flg"]);
item.IsHome = Convert.ToBoolean(dataReader["ishome"]);
item.EntryUsername = dataReader["entry_username"].ToString();
item.EntryDatetime = Convert.ToDateTime(dataReader["entry_datetime"]);
item.UpdateUsername = dataReader["update_username"].ToString();
item.UpdateDatetime = Convert.ToDateTime(dataReader["update_datetime"]);
lstMenu.Add(item);
}
getConnection().Close();
return lstMenu;
}