Creating a class that handles postgresql queries i

Question

My question is: What's the most efficient way to handle bunch of postgresql queries inside a new class/method that have different SQL queries, parameters and return values?

For example:

NpgsqlCommand ukupno_sati = new NpgsqlCommand("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;",conn);
                    ukupno_sati.Parameters.AddWithValue("@zaposlenik_id", dtIme_prezime_odjel_bolovanje_godisnji.Rows[i][0]);
                    ukupno_sati.Parameters.AddWithValue("@mjesec_broj", mjesec_broj);
                    ukupno_sati.Parameters.AddWithValue("@godina", godina);
                    NpgsqlDataAdapter dz = new NpgsqlDataAdapter(ukupno_sati);
                    DataTable UkupnoSati = new DataTable();
                    dz.Fill(UkupnoSati);

or

NpgsqlCommand godisnji = new NpgsqlCommand("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;",conn);
                    godisnji.Parameters.AddWithValue("@mjesec_broj", mjesec_broj.ToString());
                    godisnji.Parameters.AddWithValue("@godina", godina.ToString());
                    godisnji.Parameters.AddWithValue("@zaposlenik_id", dtIme_prezime_odjel_bolovanje_godisnji.Rows[i][0]);
                    int iskoristeni_godisnji = Convert.ToInt32(godisnji.ExecuteScalar());

So my point is: Can I make a method that handles different queries with different return values and different number of parameters depending on a queries like shown in examples?

Answer 1

You can use the same technique I've used for writing ADONETHelper.
Also, check out the code on the project that's called HowToUseADONETHelper.

The main method is this:

T Execute<T>(string sql, CommandType commandType, Func<IDbCommand, T> function, params IDbDataParameter[] parameters)
{
    using (var con = new TConnection()) // replace TConnection with your connection object
    {
        con.ConnectionString = _ConnectionString;
        using (var cmd = new NpgsqlCommand()) 
        {
            cmd.CommandText = sql;
            cmd.Connection = con;
            cmd.CommandType = commandType;
            if (parameters.Length > 0)
            {
                foreach (var parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }
            }
            con.Open();
            return function(cmd);
        }
    }
}

Note that the Execute method accepts an argument of type Func<IDbCommand, T> - that fact allows you to do all kind of interesting things:

Then you can use some other execute overloads to simplify your code:

Execute non query:

public int ExecuteNonQuery(string sql, CommandType commandType, params IDbDataParameter[] parameters)
{
    return Execute<int>(sql, commandType, c => c.ExecuteNonQuery(), parameters);
}

That's pretty simple - the function you send to the Execute method is simply c => c.ExecuteNonQuery() - and as you know, the ExecuteNonQuery in the IDbCommand interface is returning an int, so Execute will also return int.

Execute Scalar:

public T ExecuteScalar<T>(string sql, CommandType commandType, params IDbDataParameter[] parameters)
{
    return Execute<T>(sql, commandType, c =>
    {
        var returnValue = c.ExecuteScalar();
        return (returnValue != null && returnValue != DBNull.Value && returnValue is T)
         ? (T)returnValue
         : default(T);
    }, parameters);
}

Here the function is a little more complicated. The ExecuteScalar() of IDbCommand returns an Object. That Object might be null, it might be DBNull, or it might be a string, int, or whatever datatype supported by your database. So what I do is use a condition that returns the default of T when the value returned by ExecuteScalar is null or DBNull, or simply cast the object to T and return it.

Execute Reader:

// Here, you do the actuall reading from the dataReader in the populate function
public bool ExecuteReader(string sql, CommandType commandType, Func<IDataReader, bool> populate, params IDbDataParameter[] parameters)
{
    return Execute<bool>(sql, commandType, c => populate(c.ExecuteReader()), parameters);
}

Pretty simple, right? the populate function might look something like this:

bool Populate(IDataReader reader)
{
     var obj = new MyClass();
     while(reader.Read())
     {
         obj.PropertyA = reader.GetValueOrDefault("A"); // That method is written in the IDataReaderExtestion class in ADONETHelper
     }

    return true;
}

And fill a dataset:

public DataSet FillDataSet(string sql, CommandType commandType, params IDbDataParameter[] parameters)
{
    return Execute<DataSet>(sql, commandType, c => FillDataSet(c), parameters);
}

private DataSet FillDataSet(IDbCommand command)
{
    var dataSet = new DataSet();
    using (var adapter = new TAdapter())
    {
        adapter.SelectCommand = command;
        adapter.Fill(dataSet);
    }
    return dataSet;
}

Basically, I could have done the same thing here as with the execute reader, and write the FillDataSet method as a lambda expression, I don't remember why I've decided to write the FillDataSet as a different method. However, this is the method I pass as the function argument to the Execute method. It simply use a DataAdapter to fill the dataset. Note that you should change the TAdapter to NpgsqlDataAdapter in your case.

Answer 2

If you are looking to return Data Table and integer from a method then its possible in single method by using data table as return type and integer as out parameter(you can read here)