Editing data tables

Many possibilities are available to edit table data. This sample demostrates to edit data (insert and update records) via a datagridview (cell value event) events.
Postgres and C#
  • Insert new records to a certain table
  • 
      void insertNewMap(string fileName) // 
            {
                using (NpgsqlConnection cnn = new NpgsqlConnection(cnsb.ConnectionString))
                {
                    cnn.Open();
                    using (NpgsqlCommand cmd = new NpgsqlCommand())
                    {
                        string seq_mapid;
                        string gr_id = cmbGroups.SelectedValue.ToString();
                        string grformat = System.IO.Path.GetExtension(fileName).Replace(".","");
                        NpgsqlDataAdapter da = new NpgsqlDataAdapter("select nextval('seq_mapid')", cnn);       // select nextval('seq_mapid') command generates the next PK value
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        seq_mapid = dt.Rows[0][0].ToString();
                        cmd.Connection = cnn;
                        string sqlCommand = "INSERT INTO " + tableName + " (title, map_id, group_id, grformat) VALUES ('" + System.IO.Path.GetFileName(fileName) + "'," + seq_mapid.ToString() + ",'" + gr_id + "','" + grformat + "')";
                        cmd.CommandText = sqlCommand;
                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception error)
                        {                        
                            MessageBox.Show("Error in INSERT command: " + error.Message,"Error in INSERT",MessageBoxButtons.OK,MessageBoxIcon.Error);
                        }
                        System.IO.File.Copy(fileName, mapPath + seq_mapid + System.IO.Path.GetExtension(fileName));
                    }
                }
            }
    
    
  • Update a field of a certain record and table
  • 
            private void dgv_CellValueChanged(object sender, DataGridViewCellEventArgs e)       //if a cell value of datagrid has changed, this event occurs
            {
                using (NpgsqlConnection cnn = new NpgsqlConnection(cnsb.ConnectionString))
                {
                    cnn.Open();
                    using (NpgsqlCommand cmd = new NpgsqlCommand())
                    {
                        cmd.Connection = cnn;
                        string updateColumnName = dgv.Columns[e.ColumnIndex].Name;
                        string updateColumnValue = dgv.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
    
                        string updateCommand="";
                        updateCommand = "UPDATE " + tableName + " SET " + updateColumnName + "='" + updateColumnValue + "' WHERE map_id=" + dgv.Rows[selectedMapID].Cells["map_id"].Value; //mapId is the PK
                        cmd.CommandText = updateCommand;
                        cmd.ExecuteNonQuery();
                    }
                }
            }