PgSQL

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Npgsql;
using System.Windows.Forms;

namespace student_progress
{
    class PgSQL
    {

        string connParam = null;

        public PgSQL(string connectionString)
        {
            connParam = connectionString;
        }

        public DataTable OutTable(string query)
        {
            NpgsqlConnection dbConnection = null;
            NpgsqlCommand command = null;
            DataTable dt = new DataTable();
            try
            {
                dbConnection = new NpgsqlConnection(connParam);
                dbConnection.Open();
                command = new NpgsqlCommand();
                command.Connection = dbConnection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = query;
                NpgsqlDataAdapter dbDataAdapter = new NpgsqlDataAdapter(command);
                DataSet ds = new DataSet();
                dbDataAdapter.Fill(ds);
                dt = ds.Tables[0];
            }
            catch (NpgsqlException p)
            {
                throw new Exception (p.Message);
            }
            finally
            {
                dbConnection.Close();
            }
            return dt;
        }

        public void Del(string procName, int id)
        {
            NpgsqlConnection dbConnection = null;
            NpgsqlCommand command = null;
            try
            {
                dbConnection = new NpgsqlConnection(connParam);
                command = new NpgsqlCommand();
                command.Connection = dbConnection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = procName;

                command.Parameters.AddWithValue("i", id);

                dbConnection.Open();
                command.Prepare();
                command.ExecuteNonQuery();
            }
            catch (Exception p)
            {
                throw new Exception(p.Message);
            }
            finally
            {
                dbConnection.Close();
            }
        }

        public void AddEditStat(string procName, int st, int tch, int d, string r, string dt, int tr, int gr, string att, int id = -1)
        {
            NpgsqlConnection dbConnection = null;
            NpgsqlCommand command = null;
            try
            {
                dbConnection = new NpgsqlConnection(connParam);
                command = new NpgsqlCommand();
                command.Connection = dbConnection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = procName;

                command.Parameters.AddWithValue("st", st);
                command.Parameters.AddWithValue("tch", tch);
                command.Parameters.AddWithValue("d", d);
                command.Parameters.AddWithValue("r", NpgsqlTypes.NpgsqlDbType.Varchar, r);
                command.Parameters.AddWithValue("dt", NpgsqlTypes.NpgsqlDbType.Date, Convert.ToDateTime(dt));
                command.Parameters.AddWithValue("tr", tr);
                command.Parameters.AddWithValue("gr", gr);
                command.Parameters.AddWithValue("att", NpgsqlTypes.NpgsqlDbType.Varchar, att);
                if (id != -1)
                    command.Parameters.AddWithValue("i", id);

                dbConnection.Open();
                command.Prepare();
                command.ExecuteNonQuery();
            }
            catch (Exception p)
            {
                throw new Exception(p.Message);
            }
            finally
            {
                dbConnection.Close();
            }
        }

        public void AddEditStudent(string procName, string n, string ph, string ay, int f, int gr, string rb, int id = -1)
        {
            NpgsqlConnection dbConnection = null;
            NpgsqlCommand command = null;
            try
            {
                dbConnection = new NpgsqlConnection(connParam);
                command = new NpgsqlCommand();
                command.Connection = dbConnection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = procName;

                command.Parameters.AddWithValue("n", NpgsqlTypes.NpgsqlDbType.Varchar, n);
                command.Parameters.AddWithValue("ph", NpgsqlTypes.NpgsqlDbType.Varchar, ph);
                command.Parameters.AddWithValue("ay", NpgsqlTypes.NpgsqlDbType.Varchar, ay);
                command.Parameters.AddWithValue("f", NpgsqlTypes.NpgsqlDbType.Integer, f);
                command.Parameters.AddWithValue("gr", NpgsqlTypes.NpgsqlDbType.Integer, gr);
                command.Parameters.AddWithValue("rb", NpgsqlTypes.NpgsqlDbType.Varchar, rb);
                if (id != -1)
                    command.Parameters.AddWithValue("i", id);

                dbConnection.Open();
                command.Prepare();
                command.ExecuteNonQuery();
            }
            catch (Exception p)
            {
                throw new Exception(p.Message);
            }
            finally
            {
                dbConnection.Close();
            }
        }

        public void AddEditDisc(string procName, string n, int id = -1)
        {
            NpgsqlConnection dbConnection = null;
            NpgsqlCommand command = null;
            try
            {
                dbConnection = new NpgsqlConnection(connParam);
                command = new NpgsqlCommand();
                command.Connection = dbConnection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = procName;

                command.Parameters.AddWithValue("n", NpgsqlTypes.NpgsqlDbType.Varchar, n);
                if (id != -1)
                    command.Parameters.AddWithValue("i", id);

                dbConnection.Open();
                command.Prepare();
                command.ExecuteNonQuery();
            }
            catch (Exception p)
            {
                throw new Exception(p.Message);
            }
            finally
            {
                dbConnection.Close();
            }
        }

        public void AddEditTeacher(string procName, string n, string ph, int ct, int id = -1)
        {
            NpgsqlConnection dbConnection = null;
            NpgsqlCommand command = null;
            try
            {
                dbConnection = new NpgsqlConnection(connParam);
                command = new NpgsqlCommand();
                command.Connection = dbConnection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = procName;

                command.Parameters.AddWithValue("n", NpgsqlTypes.NpgsqlDbType.Varchar, n);
                command.Parameters.AddWithValue("ph", NpgsqlTypes.NpgsqlDbType.Varchar, ph);
                command.Parameters.AddWithValue("ct", NpgsqlTypes.NpgsqlDbType.Integer, ct);
                if (id != -1)
                    command.Parameters.AddWithValue("i", id);

                dbConnection.Open();
                command.Prepare();
                command.ExecuteNonQuery();
            }
            catch (Exception p)
            {
                throw new Exception(p.Message);
            }
            finally
            {
                dbConnection.Close();
            }
        }

        public void AddEditFaculty(string procName, string n, int id = -1)
        {
            NpgsqlConnection dbConnection = null;
            NpgsqlCommand command = null;
            try
            {
                dbConnection = new NpgsqlConnection(connParam);
                command = new NpgsqlCommand();
                command.Connection = dbConnection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = procName;

                command.Parameters.AddWithValue("n", NpgsqlTypes.NpgsqlDbType.Varchar, n);
                if (id != -1)
                    command.Parameters.AddWithValue("i", id);

                dbConnection.Open();
                command.Prepare();
                command.ExecuteNonQuery();
            }
            catch (Exception p)
            {
                throw new Exception(p.Message);
            }
            finally
            {
                dbConnection.Close();
            }
        }

        public void AddEditCathedra(string procName, string n, int f, string ph, string mrg, int id = -1)
        {
            NpgsqlConnection dbConnection = null;
            NpgsqlCommand command = null;
            try
            {
                dbConnection = new NpgsqlConnection(connParam);
                command = new NpgsqlCommand();
                command.Connection = dbConnection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = procName;

                command.Parameters.AddWithValue("n", NpgsqlTypes.NpgsqlDbType.Varchar, n);
                command.Parameters.AddWithValue("f", NpgsqlTypes.NpgsqlDbType.Integer, f);
                command.Parameters.AddWithValue("ph", NpgsqlTypes.NpgsqlDbType.Varchar, ph);
                command.Parameters.AddWithValue("mrg", NpgsqlTypes.NpgsqlDbType.Varchar, mrg);
                if (id != -1)
                    command.Parameters.AddWithValue("i", id);

                dbConnection.Open();
                command.Prepare();
                command.ExecuteNonQuery();
            }
            catch (Exception p)
            {
                throw new Exception(p.Message);
            }
            finally
            {
                dbConnection.Close();
            }
        }

        public void AddEditSpec(string procName, string n, string cd, int ct, int id = -1)
        {
            NpgsqlConnection dbConnection = null;
            NpgsqlCommand command = null;
            try
            {
                dbConnection = new NpgsqlConnection(connParam);
                command = new NpgsqlCommand();
                command.Connection = dbConnection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = procName;

                command.Parameters.AddWithValue("n", NpgsqlTypes.NpgsqlDbType.Varchar, n);
                command.Parameters.AddWithValue("cd", NpgsqlTypes.NpgsqlDbType.Varchar, cd);
                command.Parameters.AddWithValue("ct", NpgsqlTypes.NpgsqlDbType.Integer, ct);
                if (id != -1)
                    command.Parameters.AddWithValue("i", id);

                dbConnection.Open();
                command.Prepare();
                command.ExecuteNonQuery();
            }
            catch (Exception p)
            {
                throw new Exception(p.Message);
            }
            finally
            {
                dbConnection.Close();
            }
        }

        public void AddEditGroup(string procName, string grn, int sp, string hm, int ppl, int id = -1)
        {
            NpgsqlConnection dbConnection = null;
            NpgsqlCommand command = null;
            try
            {
                dbConnection = new NpgsqlConnection(connParam);
                command = new NpgsqlCommand();
                command.Connection = dbConnection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = procName;

                command.Parameters.AddWithValue("grn", NpgsqlTypes.NpgsqlDbType.Varchar, grn);
                command.Parameters.AddWithValue("sp", NpgsqlTypes.NpgsqlDbType.Integer, sp);
                command.Parameters.AddWithValue("hm", NpgsqlTypes.NpgsqlDbType.Varchar, hm);
                command.Parameters.AddWithValue("ppl", NpgsqlTypes.NpgsqlDbType.Integer, ppl);
                if (id != -1)
                    command.Parameters.AddWithValue("i", id);

                dbConnection.Open();
                command.Prepare();
                command.ExecuteNonQuery();
            }
            catch (Exception p)
            {
                throw new Exception(p.Message);
            }
            finally
            {
                dbConnection.Close();
            }
        }
    }
}