public int insertKeyBand(KeyBandModels item) { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.Append("INSERT "); sqlBuilder.Append("INTO "); sqlBuilder.Append("gov_key_band "); sqlBuilder.Append("( "); sqlBuilder.Append("key_band, "); sqlBuilder.Append("entry_username, "); sqlBuilder.Append("entry_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.Key); objCmd.Parameters.AddWithValue("@2", item.EntryUsername); objCmd.Parameters.AddWithValue("@3", item.EntryDatetime); int rs = objCmd.ExecuteNonQuery(); return rs; }
public List<KeyBandModels> selectKey(int page, int limit) { int offset = (page - 1) * limit; StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.Append("SELECT "); sqlBuilder.Append("* "); sqlBuilder.Append("FROM "); sqlBuilder.Append("gov_key_band gkb "); this.Sql = sqlBuilder.ToString(); 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<KeyBandModels> lstKey = new List<KeyBandModels>(); KeyBandModels item; while (dataReader.Read()) { item = new KeyBandModels(); item.Id = Convert.ToInt32(dataReader["id"]); item.Key = dataReader["key_band"].ToString(); item.EntryUsername = dataReader["entry_username"].ToString(); item.EntryDatetime = Convert.ToDateTime(dataReader["entry_datetime"]); lstKey.Add(item); } getConnection().Close(); return lstKey; }