public List <UserDataCollection> GetAllUsers() { List <UserDataCollection> Users = new List <UserDataCollection>(); //string cs = "Host=localhost; Username=postgres; Password=Rupom@223456; Database=postgres"; var Connection = new NpgsqlConnection(connectionString); Connection.Open(); try { // request a function to execute against PostgreeSql var command = new NpgsqlCommand(); // get the sql connection command.Connection = Connection; command.CommandText = "SELECT * From \"userdata\";"; command.ExecuteNonQuery(); NpgsqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { UserDataCollection getAllUsers = new UserDataCollection(); getAllUsers.age = dr[0].ToString(); getAllUsers.id = dr[2].ToString(); getAllUsers.name = dr[1].ToString(); Users.Add(getAllUsers); } dr.Close(); } catch { MessageBox.Show("Unable to fetch the value"); } finally { Connection.Close(); } return(Users); #endregion #region TO DELETE DATA FROM DATABASE /*public ServerResponse DeleteData(UserDataCollection userid) * { * }*/ #endregion }
public List <UserDataCollection> GetUser(string userid) { List <UserDataCollection> User = new List <UserDataCollection>(); //string cs = "Host=localhost; Username=postgres; Password=Rupom@223456; Database=postgres"; var Connection = new NpgsqlConnection(connectionString); Connection.Open(); try { // request a function to execute against PostgreeSql var command = new NpgsqlCommand(); // get the sql connection command.Connection = Connection; command.CommandText = "SELECT * FROM \"userdata\" WHERE name = '" + userid + "'; "; command.ExecuteNonQuery(); NpgsqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { UserDataCollection userInfo = new UserDataCollection(); userInfo.age = dr[0].ToString(); userInfo.id = dr[2].ToString(); userInfo.name = dr[1].ToString(); User.Add(userInfo); } dr.Close(); } catch { MessageBox.Show("unable to fetch the value"); } finally { Connection.Close(); } return(User); }
public ServerResponse PutData(UserDataCollection userDetails) { // connection string //string ConnectionString = "Host=localhost; Username=postgres; Password=Rupom@223456; Database=postgres"; // initializing a npgsqlConnection with the connection string var Connection = new NpgsqlConnection(connectionString); // initiate the connection Connection.Open(); // assigning the datamembers of the UserDataCollection to local varibale string id = userDetails.id; string name = userDetails.name; string age = userDetails.age; try { // request a function to execute against postgresql var command = new NpgsqlCommand(); // get the sql connection command.Connection = Connection; // setting the sql query to be executed command.CommandText = "UPDATE \"userdata\" SET \"id\" = '" + id + "', \"name\" = '" + name + "', \"age\" = '" + age + "' WHERE \"id\" = '" + id + "';"; // after executing the query against connection return the no of affected rows in the postgre table command.ExecuteNonQuery(); } catch { // for any error during try block this message will appear MessageBox.Show("Error encountered during UPDATE operation."); } finally { // close the conection after executig the process Connection.Close(); } // return the following perameters if the method executed fuccesfully return(new ServerResponse(200, "Value updated in the database")); }
public ServerResponse PostData(UserDataCollection userDetails) { // initializing a new instance of NpgSqlConnection with connection string var Connection = new NpgsqlConnection(connectionString); Connection.Open(); // assigning members of DataCollection to local variable string id = userDetails.id; string name = userDetails.name; string age = userDetails.age; try { // request a function to execute against PostgreeSql var command = new NpgsqlCommand(); // get the sql connection command.Connection = Connection; // setting the sql statement or query which need to executed command.CommandText = "INSERT INTO \"userdata\" VALUES('" + id + "', '" + name + "', ' " + age + "');"; // execute the SQL query against the connection and returns the no of row affected command.ExecuteNonQuery(); } catch { // this massage will appears if some error encountered during the try block execution MessageBox.Show("Error encountered during INSERT operation."); } finally { // once the process of posting is done we will close the connection Connection.Close(); } // Server will send this response to the client once this method executed succesfully return(new ServerResponse(200, "Value stored into the database")); }