Return to Question

After I found out who is logged in and gave proper query for it and fill the datatable dt with return value of query:

for (int i = 0; i < dt.Rows.Count; i++) //dt is the datatable that was filled with data depending on the role of user that is logged in
                {
                    //query that returns how many times an employee logged in in that month of a year
                    komanda = "begin;select count(*) from radni_sati where zaposlenik_id=@zaposlenik_id and " +
                              " extract(month from datum)=@mjesec_broj and extract(year from datum)=@godina ;commit;";
                    listaParametara.Add(new NpgsqlParameter { ParameterName = "@mjesec_broj", Value = mjesec_broj }); //month we are making report on 
                    listaParametara.Add(new NpgsqlParameter { ParameterName = "@godina", Value = godina }); //year we are making report on
                    listaParametara.Add(new NpgsqlParameter { ParameterName = "@zaposlenik_id", Value = dt.Rows[i][0] }); //employee id
                    long ZapisUMjesecu = instanca.ExecuteScalar<long>(komanda, CommandType.Text, listaParametara);
                    listaParametara.Clear();

                    //returns the department_id from current employee 
                    komanda = "begin;select odjel_id from zaposlenici where zaposlenik_id=@zaposlenik_id ;commit;";
                    listaParametara.Add(new NpgsqlParameter { ParameterName = "@zaposlenik_id", Value = dt.Rows[i][0] });
                    int OdjelID = instanca.ExecuteScalar<int>(komanda, CommandType.Text, listaParametara);
                    listaParametara.Clear();

                    //if that employee has atlest 1 day of work
                    if (ZapisUMjesecu > 0)
                    {
                        //return number of sick days he had
                        komanda = "begin;select count(*) from bolovanje where extract(month from bolovanje.datum)" +
                                  "=@mjesec_broj and extract(year from bolovanje.datum)=@godina and zaposlenik_id=@zaposlenik_id ;commit;";
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@mjesec_broj", Value = mjesec_broj });
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@godina", Value = godina });
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@zaposlenik_id", Value = dt.Rows[i][0] });
                        long iskoristeno_bolovanje = instanca.ExecuteScalar<long>(komanda, CommandType.Text, listaParametara);
                        listaParametara.Clear();

                        //returns all the dates that employee was sick 
                        komanda = "begin;select datum from bolovanje where extract(month from bolovanje.datum)" +
                                  "=@mjesec_broj and extract(year from bolovanje.datum)=@godina and zaposlenik_id=@zaposlenik_id ;commit;";
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@zaposlenik_id", Value = dt.Rows[i][0] });
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@mjesec_broj", Value = mjesec_broj });
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@godina", Value = godina });
                        DataTable DatumiBolovanja = instanca.FillDataTable(komanda, CommandType.Text, listaParametara);
                        listaParametara.Clear();

                        //return number of holidays he had
                        komanda = "begin;select count(*) from godisnji where extract(month from godisnji.datum)" +
                                  "=@mjesec_broj and extract(year from godisnji.datum)=@godina and zaposlenik_id=@zaposlenik_id;commit;";
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@mjesec_broj", Value = mjesec_broj });
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@godina", Value = godina });
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@zaposlenik_id", Value = dt.Rows[i][0] });
                        long iskoristeni_godisnji = instanca.ExecuteScalar<long>(komanda, CommandType.Text, listaParametara);
                        listaParametara.Clear();

                        //returns all the dates that employee was on holiday
                        komanda = "begin;select datum from godisnji where extract(month from godisnji.datum)" +
                                  "=@mjesec_broj and extract(year from godisnji.datum)=@godina and zaposlenik_id=@zaposlenik_id ;commit;";
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@zaposlenik_id", Value = dt.Rows[i][0] });
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@mjesec_broj", Value = mjesec_broj });
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@godina", Value = godina });
                        DataTable DatumiGodisnjeg = instanca.FillDataTable(komanda, CommandType.Text, listaParametara);
                        listaParametara.Clear();



                        //returns a number of days that employee was working
                        komanda = "begin;select count(*) from radni_sati where zaposlenik_id=@zaposlenik_id" +
                                  " and (dolazak is not null and odlazak is not null and sati_rada is not null) and extract(month from radni_sati.datum)" +
                                  "=@mjesec_broj and extract(year from radni_sati.datum)=@godina group by zaposlenik_id order by zaposlenik_id;commit;";
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@mjesec_broj", Value = mjesec_broj });
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@godina", Value = godina });
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@zaposlenik_id", Value = dt.Rows[i][0] });
                        long redovi = instanca.ExecuteScalar<long>(komanda, CommandType.Text, listaParametara);
                        listaParametara.Clear();

                        
                        //calculate number of hours that employee was working in that month                 
                         komanda = "begin;select cast(radni_sati.sati_rada as time) from radni_sati where zaposlenik_id=@zaposlenik_id " +
                                  " and extract(month from radni_sati.datum)=@mjesec_broj and extract(year from radni_sati.datum)=@godina;commit;";
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@zaposlenik_id", Value = dt.Rows[i][0] });
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@mjesec_broj", Value = mjesec_broj });
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@godina", Value = godina });
                        DataTable UkupnoSati = instanca.FillDataTable(komanda, CommandType.Text, listaParametara);
                        listaParametara.Clear();

After I found out who is logged in and gave proper query for it and fill the datatable dt with return value of query. (Edited by idea of Derek Smyth) :

for (int i = 0; i < dt.Rows.Count; i++) //dt is the datatable that was filled with data depending on the role of user that is logged in
                {
                    //query that returns how many times an employee logged in in that month of a year
                    komanda = "begin;select count(*) from radni_sati where zaposlenik_id=@zaposlenik_id and " +
                              " extract(month from datum)=@mjesec_broj and extract(year from datum)=@godina ;commit;";
                    listaParametara.Add(new NpgsqlParameter { ParameterName = "@mjesec_broj", Value = mjesec_broj }); //month we are making report on 
                    listaParametara.Add(new NpgsqlParameter { ParameterName = "@godina", Value = godina }); //year we are making report on
                    listaParametara.Add(new NpgsqlParameter { ParameterName = "@zaposlenik_id", Value = dt.Rows[i][0] }); //employee id
                    long ZapisUMjesecu = instanca.ExecuteScalar<long>(komanda, CommandType.Text, listaParametara);
                    listaParametara.Clear();

                    //returns the department_id from current employee 
                    komanda = "begin;select odjel_id from zaposlenici where zaposlenik_id=@zaposlenik_id ;commit;";
                    listaParametara.Add(new NpgsqlParameter { ParameterName = "@zaposlenik_id", Value = dt.Rows[i][0] });
                    int OdjelID = instanca.ExecuteScalar<int>(komanda, CommandType.Text, listaParametara);
                    listaParametara.Clear();

                    //if that employee has atlest 1 day of work
                    bool radio = ZapisUMjesecu > 0; //ako postoje zapisi, tj. ako se zaposlenik bar jednom prijavio u mjesecu
                    if (radio)
                    {
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@mjesec_broj", Value = mjesec_broj });
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@godina", Value = godina });
                        listaParametara.Add(new NpgsqlParameter { ParameterName = "@zaposlenik_id", Value = dt.Rows[i][0] });
 
                        Task<long> iskoristeno_bolovanje_tsk = Task.Run(() => instanca.ExecuteScalar<long>( 
                        "begin;select count(*) from bolovanje where extract(month from bolovanje.datum)" +
                        "=@mjesec_broj and extract(year from bolovanje.datum)=@godina and zaposlenik_id=@zaposlenik_id ;commit;",
                        CommandType.Text, listaParametara));

                        //returns all the dates that employee was sick 
                        Task<DataTable> DatumiBolovanja_tsk = Task.Run(() => instanca.FillDataTable(
                            "begin;select datum from bolovanje where extract(month from bolovanje.datum)" +
                            "=@mjesec_broj and extract(year from bolovanje.datum)=@godina and zaposlenik_id=@zaposlenik_id ;commit;",
                            CommandType.Text, listaParametara));


                        //return number of holidays employee had
                        Task<long> iskoristeni_godisnji_tsk = Task.Run(() => instanca.ExecuteScalar<long>(
                            "begin;select count(*) from godisnji where extract(month from godisnji.datum)" +
                            "=@mjesec_broj and extract(year from godisnji.datum)=@godina and zaposlenik_id=@zaposlenik_id;commit;",
                            CommandType.Text, listaParametara));

                        //returns all the dates that employee was on holiday
                        Task<DataTable> DatumiGodisnjeg_tsk = Task.Run(() => instanca.FillDataTable(
                            "begin;select datum from godisnji where extract(month from godisnji.datum)" +
                            "=@mjesec_broj and extract(year from godisnji.datum)=@godina and zaposlenik_id=@zaposlenik_id ;commit;",
                            CommandType.Text, listaParametara));


                        //returns a number of days that employee was working
                        Task<long> redovi_tsk = Task.Run(() => instanca.ExecuteScalar<long>(
                            "begin;select count(*) from radni_sati where zaposlenik_id=@zaposlenik_id" +
                            " and (dolazak is not null and odlazak is not null and sati_rada is not null) and extract(month from radni_sati.datum)" +
                            "=@mjesec_broj and extract(year from radni_sati.datum)=@godina group by zaposlenik_id order by zaposlenik_id;commit;",
                            CommandType.Text, listaParametara));

                        //calculate number of hours that employee was working in that month   
                        Task<DataTable> UkupnoSati_tsk = Task.Run(() => instanca.FillDataTable(
                            "begin;select cast(radni_sati.sati_rada as time) from radni_sati where zaposlenik_id=@zaposlenik_id " +
                            " and extract(month from radni_sati.datum)=@mjesec_broj and extract(year from radni_sati.datum)=@godina;commit;",
                            CommandType.Text, listaParametara));

                        // wait for all the tasks above to complete
                        Task.WaitAll( 
                            iskoristeno_bolovanje_tsk, DatumiBolovanja_tsk,
                            iskoristeni_godisnji_tsk, DatumiGodisnjeg_tsk,
                            redovi_tsk, UkupnoSati_tsk); 

                        // get the values after each of these return
                        long iskoristeno_bolovanje = iskoristeno_bolovanje_tsk.Result;
                        DataTable DatumiBolovanja = DatumiBolovanja_tsk.Result;

                        long iskoristeni_godisnji = iskoristeni_godisnji_tsk.Result;
                        DataTable DatumiGodisnjeg = DatumiGodisnjeg_tsk.Result;

                        long redovi = redovi_tsk.Result;
                        DataTable UkupnoSati = UkupnoSati_tsk.Result;


                        listaParametara.Clear();

 class Query_builder
{
    public T Execute<T>(string sql, CommandType commandType, Func<NpgsqlCommand, T> function, List<NpgsqlParameter> parameters)
    {
        
        using (NpgsqlConnection conn = KonekcijaNaServer.Spajanje(true))  //created a connection with postgresql database
        {
            using (var cmd = new NpgsqlCommand(sql, conn) { CommandType = commandType }) //using a NpgsqlCommand with query as "sql", "conn" as connection and commandType as a type of query.
            {
                if (parameters.Count > 0 ) //if there are any parameters in the list
                {
                    foreach (var parameter in parameters)
                    {
                        cmd.Parameters.AddWithValue(parameter.ParameterName,parameter.Value);  //add parameter to the referenced place 
                    }
                }                    
                return function(cmd); 
            }
        }            
    }
    
    public T ExecuteScalar<T>(string sql, CommandType commandType, List<NpgsqlParameter> parameters)
    {
       return Execute<T>(sql, commandType, c => { //return to Execute<T> with parameters: "sql","commandType",lamba expression as function and parameters.
            var returnValue =c.ExecuteScalar();  //execute ExecuteScalar and get the return value from Execute<T>
            return (returnValue != null && returnValue != DBNull.Value && returnValue is T) //return if not null or DBNull
            ? (T)returnValue : default(T); //if condition is true, return "returnValue", if not return default of declared type
        }, parameters);          
    }

    public DataTable FillDataTable(string sql, CommandType commandType, List<NpgsqlParameter> parameters)
    {
        return Execute<DataTable>(sql, commandType, c => FillDataTable(c), parameters);                                                                                          
    }

    private DataTable FillDataTable(NpgsqlCommand command)
    {
        using (NpgsqlConnection conn = Konekcija_na_server.Spajanje("spoji")) //using the connection to postgresql database
        {
            DataTable dataTable = new DataTable(); //create a new datatable
            using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command)) //create an adapted with sql command as "command"
            {
                command.Connection = conn; //connection for executing is "conn"
                adapter.Fill(dataTable); //fill the adapted with result DataTable from sql query
            }
            return dataTable;                 
        }          
    }  
}

My connection class Konekcija_na_server (Edited by idea of RobH):

 class Query_builder
{
    public T Execute<T>(string sql, CommandType commandType, Func<NpgsqlCommand, T> function, List<NpgsqlParameter> parameters)
    {
        
        using (NpgsqlConnection conn = KonekcijaNaServer.Spajanje(true))  //created a connection with postgresql database
        {
            using (var cmd = new NpgsqlCommand(sql, conn) { CommandType = commandType }) //using a NpgsqlCommand with query as "sql", "conn" as connection and commandType as a type of query.
            {
                if (parameters.Count > 0 ) //if there are any parameters in the list
                {
                    foreach (var parameter in parameters)
                    {
                        cmd.Parameters.AddWithValue(parameter.ParameterName,parameter.Value);  //add parameter to the referenced place 
                    }
                }                    
                return function(cmd); 
            }
        }            
    }
    
    public T ExecuteScalar<T>(string sql, CommandType commandType, List<NpgsqlParameter> parameters)
    {
       return Execute<T>(sql, commandType, c => { //return to Execute<T> with parameters: "sql","commandType",lamba expression as function and parameters.
            var returnValue =c.ExecuteScalar();  //execute ExecuteScalar and get the return value from Execute<T>
            return (returnValue != null && returnValue != DBNull.Value && returnValue is T) //return if not null or DBNull
            ? (T)returnValue : default(T); //if condition is true, return "returnValue", if not return default of declared type
        }, parameters);          
    }

    public DataTable FillDataTable(string sql, CommandType commandType, List<NpgsqlParameter> parameters)
    {
        return Execute<DataTable>(sql, commandType, c => FillDataTable(c), parameters);                                                                                          
    }

    private DataTable FillDataTable(NpgsqlCommand command)
    {
        using (NpgsqlConnection conn = KonekcijaNaServer.Spajanje(true)) //using the connection to postgresql database
        {
            DataTable dataTable = new DataTable(); //create a new datatable
            using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command)) //create an adapted with sql command as "command"
            {
                command.Connection = conn; //connection for executing is "conn"
                adapter.Fill(dataTable); //fill the adapted with result DataTable from sql query
            }
            return dataTable;                 
        }          
    }  
}

My connection class KonekcijaNaServer (Edited by idea of RobH):

 class Query_builder
{
    public T Execute<T>(string sql, CommandType commandType, Func<NpgsqlCommand, T> function, List<NpgsqlParameter> parameters)
    {
        
        using (NpgsqlConnection conn = Konekcija_na_server.Spajanje("spoji"))  //created a connection with postgresql database
        {
            using (var cmd = new NpgsqlCommand(sql, conn) { CommandType = commandType }) //using a NpgsqlCommand with query as "sql", "conn" as connection and commandType as a type of query.
            {
                if (parameters.Count > 0 ) //if there are any parameters in the list
                {
                    foreach (var parameter in parameters)
                    {
                        cmd.Parameters.AddWithValue(parameter.ParameterName,parameter.Value);  //add parameter to the referenced place 
                    }
                }                    
                return function(cmd); 
            }
        }            
    }
    
    public T ExecuteScalar<T>(string sql, CommandType commandType, List<NpgsqlParameter> parameters)
    {
       return Execute<T>(sql, commandType, c => { //return to Execute<T> with parameters: "sql","commandType",lamba expression as function and parameters.
            var returnValue =c.ExecuteScalar();  //execute ExecuteScalar and get the return value from Execute<T>
            return (returnValue != null && returnValue != DBNull.Value && returnValue is T) //return if not null or DBNull
            ? (T)returnValue : default(T); //if condition is true, return "returnValue", if not return default of declared type
        }, parameters);          
    }

    public DataTable FillDataTable(string sql, CommandType commandType, List<NpgsqlParameter> parameters)
    {
        return Execute<DataTable>(sql, commandType, c => FillDataTable(c), parameters);                                                                                          
    }

    private DataTable FillDataTable(NpgsqlCommand command)
    {
        using (NpgsqlConnection conn = Konekcija_na_server.Spajanje("spoji")) //using the connection to postgresql database
        {
            DataTable dataTable = new DataTable(); //create a new datatable
            using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command)) //create an adapted with sql command as "command"
            {
                command.Connection = conn; //connection for executing is "conn"
                adapter.Fill(dataTable); //fill the adapted with result DataTable from sql query
            }
            return dataTable;                 
        }          
    }  
}

My connection class Konekcija_na_server:

class Konekcija_na_server
{
    public static string Connectionstring = "Server=127.0.0.1;Port=5433;User Id=postgres;" +
    "Password=*********;Database=postgres;Pooling=false;";

    public static NpgsqlConnection Spajanje(string status)
    {
        bool spajanje = false;

        NpgsqlConnection conn = new NpgsqlConnection(Connectionstring);
        if (status == "spoji")
        {             
            conn.Open();
            spajanje = true;
        }
        else if (status == "prekini")
        {
            conn.Close();              
        }

        if (spajanje == true)
        {
            return conn;                
        }
        else return null;

    }
}

 class Query_builder
{
    public T Execute<T>(string sql, CommandType commandType, Func<NpgsqlCommand, T> function, List<NpgsqlParameter> parameters)
    {
        
        using (NpgsqlConnection conn = KonekcijaNaServer.Spajanje(true))  //created a connection with postgresql database
        {
            using (var cmd = new NpgsqlCommand(sql, conn) { CommandType = commandType }) //using a NpgsqlCommand with query as "sql", "conn" as connection and commandType as a type of query.
            {
                if (parameters.Count > 0 ) //if there are any parameters in the list
                {
                    foreach (var parameter in parameters)
                    {
                        cmd.Parameters.AddWithValue(parameter.ParameterName,parameter.Value);  //add parameter to the referenced place 
                    }
                }                    
                return function(cmd); 
            }
        }            
    }
    
    public T ExecuteScalar<T>(string sql, CommandType commandType, List<NpgsqlParameter> parameters)
    {
       return Execute<T>(sql, commandType, c => { //return to Execute<T> with parameters: "sql","commandType",lamba expression as function and parameters.
            var returnValue =c.ExecuteScalar();  //execute ExecuteScalar and get the return value from Execute<T>
            return (returnValue != null && returnValue != DBNull.Value && returnValue is T) //return if not null or DBNull
            ? (T)returnValue : default(T); //if condition is true, return "returnValue", if not return default of declared type
        }, parameters);          
    }

    public DataTable FillDataTable(string sql, CommandType commandType, List<NpgsqlParameter> parameters)
    {
        return Execute<DataTable>(sql, commandType, c => FillDataTable(c), parameters);                                                                                          
    }

    private DataTable FillDataTable(NpgsqlCommand command)
    {
        using (NpgsqlConnection conn = Konekcija_na_server.Spajanje("spoji")) //using the connection to postgresql database
        {
            DataTable dataTable = new DataTable(); //create a new datatable
            using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command)) //create an adapted with sql command as "command"
            {
                command.Connection = conn; //connection for executing is "conn"
                adapter.Fill(dataTable); //fill the adapted with result DataTable from sql query
            }
            return dataTable;                 
        }          
    }  
}

My connection class Konekcija_na_server (Edited by idea of RobH):

public static class KonekcijaNaServer
{
    public static string Connectionstring = "Server=192.168.130.240;Port=5433;User Id=postgres;" +
                                           "Password=*********;Database=postgres;Pooling=false;";

    public static NpgsqlConnection Spajanje(bool openConnection)
    {
        NpgsqlConnection conn = new NpgsqlConnection(Connectionstring);
        if (openConnection)
        {
            conn.Open();
        }
        return conn;
    }
 }