public int insert(RoleModels item)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("INSERT ");
sqlBuilder.Append("INTO ");
sqlBuilder.Append("gov_role ");
sqlBuilder.Append("( ");
sqlBuilder.Append("role_name, ");
sqlBuilder.Append("update_username, ");
sqlBuilder.Append("update_datetime ");
sqlBuilder.Append(") ");
sqlBuilder.Append("values(");
sqlBuilder.Append("@1, @2, @3");
sqlBuilder.Append(")");
this.Sql = sqlBuilder.ToString();
MySqlCommand objCmd = new MySqlCommand(Sql, getConnection());
objCmd.Parameters.AddWithValue("@1", item.RoleName);
objCmd.Parameters.AddWithValue("@2", item.EntryUsername);
objCmd.Parameters.AddWithValue("@3", item.EntryDatetime);
int rs = objCmd.ExecuteNonQuery();
return rs;
}
public List<RoleModels> select(int page, int limit)
{
int offset = (page - 1) * limit;
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("SELECT ");
sqlBuilder.Append("* ");
sqlBuilder.Append("FROM ");
sqlBuilder.Append("gov_role gr ");
sqlBuilder.Append(getBaseSQL());
if(page > 0 && limit > 0){
sqlBuilder.Append(" LIMIT @limit ");
sqlBuilder.Append(" OFFSET @offset ");
}
this.Sql = sqlBuilder.ToString();
MySqlCommand objCmd = new MySqlCommand(Sql, getConnection());
if(page > 0 && limit > 0){
objCmd.Parameters.AddWithValue("@limit", limit);
objCmd.Parameters.AddWithValue("@offset", offset);
}
MySqlDataReader dataReader = objCmd.ExecuteReader();
List<RoleModels> lstDocument = new List<RoleModels>();
RoleModels item;
while (dataReader.Read())
{
item = new RoleModels();
item.Id = Convert.ToInt32(dataReader["id"]);
item.RoleName = dataReader["role_name"].ToString();
item.EntryUsername = dataReader["update_username"].ToString();
item.EntryDatetime = Convert.ToDateTime(dataReader["update_datetime"]);
lstDocument.Add(item);
}
getConnection().Close();
return lstDocument;
}