public int add(ref push_server obj) { using (NpgsqlDBConnection db = new NpgsqlDBConnection()) { try { int ID = -1; if (db.open_connection()) { string sql_command = "INSERT INTO push_server ( ftp_ip, " + " ftp_username, ftp_pwd, ftp_folder, ftp_flag, ftp_lasted, ftp_lasted_manual)" + " VALUES (:ftp_ip, " + " :ftp_username, :ftp_pwd, :ftp_folder, :ftp_flag, :ftp_lasted, :ftp_lasted_manual)"; sql_command += " RETURNING id;"; using (NpgsqlCommand cmd = db._conn.CreateCommand()) { cmd.CommandText = sql_command; cmd.Parameters.Add(":ftp_ip", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftp_ip; cmd.Parameters.Add(":ftp_username", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftp_username; cmd.Parameters.Add(":ftp_pwd", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftp_pwd; cmd.Parameters.Add(":ftp_folder", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftp_folder; cmd.Parameters.Add(":ftp_flag", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.ftp_flag; cmd.Parameters.Add(":ftp_lasted", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.ftp_lasted; cmd.Parameters.Add(":ftp_lasted_manual", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.ftp_lasted_manual; ID = Convert.ToInt32(cmd.ExecuteScalar()); obj.id = ID; db.close_connection(); return(ID); } } else { db.close_connection(); return(-1); } } catch { if (db != null) { db.close_connection(); } return(-1); } finally { db.close_connection(); } } }
public int update_with_id(ref push_server obj, int id) { using (NpgsqlDBConnection db = new NpgsqlDBConnection()) { try { if (db.open_connection()) { string sql_command = "UPDATE push_server set " + " ftp_ip = :ftp_ip, " + " ftp_username =:ftp_username, " + " ftp_pwd =:ftp_pwd, " + " ftp_folder = :ftp_folder, " + " ftp_flag = :ftp_flag, " + " ftp_lasted = :ftp_lasted, " + " ftp_lasted_manual = :ftp_lasted_manual " + " where id = :id"; using (NpgsqlCommand cmd = db._conn.CreateCommand()) { cmd.CommandText = sql_command; cmd.Parameters.Add(":ftp_ip", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftp_ip; cmd.Parameters.Add(":ftp_username", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftp_username; cmd.Parameters.Add(":ftp_pwd", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftp_pwd; cmd.Parameters.Add(":ftp_flag", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.ftp_flag; cmd.Parameters.Add(":ftp_folder", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftp_folder; cmd.Parameters.Add(":ftp_lasted", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.ftp_lasted; cmd.Parameters.Add(":ftp_lasted_manual", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.ftp_lasted_manual; cmd.Parameters.Add(":id", NpgsqlTypes.NpgsqlDbType.Integer).Value = id; cmd.ExecuteNonQuery(); db.close_connection(); return(obj.id); } } else { db.close_connection(); return(-1); } } catch (Exception ex) { if (db != null) { db.close_connection(); } return(-1); } } }
/// <summary> /// get info by id /// </summary> /// <param name="id"></param> /// <returns></returns> public push_server get_info_by_id(int id) { using (NpgsqlDBConnection db = new NpgsqlDBConnection()) { try { push_server obj = null; if (db.open_connection()) { string sql_command = "SELECT * FROM push_server WHERE id = " + id; sql_command += " LIMIT 1"; using (NpgsqlCommand cmd = db._conn.CreateCommand()) { cmd.CommandText = sql_command; NpgsqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { obj = new push_server(); obj = (push_server)_get_info(reader); break; } reader.Close(); db.close_connection(); return(obj); } } else { db.close_connection(); return(null); } } catch { if (db != null) { db.close_connection(); } return(null); } finally { db.close_connection(); } } }
public List <push_server> get_all() { List <push_server> listUser = new List <push_server>(); using (NpgsqlDBConnection db = new NpgsqlDBConnection()) { try { if (db.open_connection()) { string sql_command = "SELECT * FROM push_server"; using (NpgsqlCommand cmd = db._conn.CreateCommand()) { cmd.CommandText = sql_command; NpgsqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { push_server obj = new push_server(); obj = (push_server)_get_info(reader); listUser.Add(obj); } reader.Close(); db.close_connection(); return(listUser); } } else { db.close_connection(); return(null); } } catch { if (db != null) { db.close_connection(); } return(null); } finally { db.close_connection(); } } }
private push_server _get_info(NpgsqlDataReader dataReader) { push_server obj = new push_server(); try { if (!DBNull.Value.Equals(dataReader["id"])) { obj.id = Convert.ToInt32(dataReader["id"].ToString().Trim()); } else { obj.id = 0; } if (!DBNull.Value.Equals(dataReader["ftp_ip"])) { obj.ftp_ip = dataReader["ftp_ip"].ToString().Trim(); } else { obj.ftp_ip = ""; } if (!DBNull.Value.Equals(dataReader["ftp_username"])) { obj.ftp_username = dataReader["ftp_username"].ToString().Trim(); } else { obj.ftp_username = ""; } if (!DBNull.Value.Equals(dataReader["ftp_pwd"])) { obj.ftp_pwd = dataReader["ftp_pwd"].ToString().Trim(); } else { obj.ftp_pwd = ""; } if (!DBNull.Value.Equals(dataReader["ftp_folder"])) { obj.ftp_folder = dataReader["ftp_folder"].ToString().Trim(); } else { obj.ftp_folder = ""; } if (!DBNull.Value.Equals(dataReader["ftp_flag"])) { obj.ftp_flag = Convert.ToInt32(dataReader["ftp_flag"].ToString().Trim()); } else { obj.ftp_flag = -1; } if (!DBNull.Value.Equals(dataReader["ftp_lasted"])) { obj.ftp_lasted = Convert.ToDateTime(dataReader["ftp_lasted"].ToString().Trim()); } else { obj.ftp_lasted = new DateTime(); } if (!DBNull.Value.Equals(dataReader["ftp_lasted_manual"])) { obj.ftp_lasted_manual = Convert.ToDateTime(dataReader["ftp_lasted_manual"].ToString().Trim()); } else { obj.ftp_lasted_manual = new DateTime(); } } catch (Exception ex) { throw ex; } return(obj); }