2
\$\begingroup\$

Since C# 8 and .Net Core 3 are going to be released sometime this year, I wanted to try and convert the library I made to make querying databases easier, implementing the already accepted features (using declarations, patterns, ranges, ...).

I'm going to pre-emptively say that parameters aren't here yet, because first I have to find a way to make them in a generic way (IDataParameter) and not have to add them one at a time (IDbCommand.Parameters.AddRange). I have some ideas, but those are for another thread.

If something isn't clear about the code below, tell me and I'll amend it.

// Enum to indicate what kind of query it is.
public enum QueryType
{
    Sql = 0,
    Odbc = 1
}

public class Queries
{
    /*Legend*/
    // Retrieve = # of columns to consider or which column to pick.
    // SplitBy = what to use to split the results.
    /*******/

    private readonly string _connectionString;
    private readonly QueryType _type;
    private readonly int _timeOut;

    // Initialize a new instance of the class.
    public Queries(
        QueryType type, 
        string connectionString, 
        int timeOut = 60)
    {
        if (string.IsNullOrEmpty(connectionString))
            throw new System.NullReferenceException(
                "The connection string can't be empty");

        _type = type;
        _connectionString = connectionString;
        _timeOut = timeOut;
        // Test to see if connectionString is valid.
        GetConnection();
    }

    private IDbConnection GetConnection()
    {
        IDbConnection dbConnection = null;

        return _type switch
        {
            QueryType.Odbc => new OdbcConnection(_connectionString),
            QueryType.Sql => new SqlConnection(_connectionString),
            _ => dbConnection
        };
    }

    private IDbCommand GetCommand(
        string query, 
        IDbConnection connection)
    {
        IDbCommand idbCommand = null;

        return _type switch
        {
            QueryType.Odbc => new OdbcCommand(
                query, 
                (OdbcConnection)connection)
                { CommandTimeout = _timeOut },
            QueryType.Sql => new SqlCommand(
                query, 
                (SqlConnection)connection)
                { CommandTimeout = _timeOut },
            _ => idbCommand
        };
    }

    private IDbDataAdapter GetDataAdapter(
        IDbCommand command)
    {
        IDbDataAdapter idbAdapter = null;

        return _type switch
        {
            QueryType.Odbc => new OdbcDataAdapter((OdbcCommand)command),
            QueryType.Sql => new SqlDataAdapter((SqlCommand)command),
            _ => idbAdapter
        };
    }

    // Execute commands, get number of affected rows in return.
    public int Execute(
        string query)
    {
        using var connection = GetConnection();
        using var command = GetCommand(query, connection);
        connection.Open();
        return command.ExecuteNonQuery();
    }

    public IEnumerable<int> Execute(
        List<string> query) => 
        query.Select(item => Execute(item));

    // Return only the first row in a single string.
    public string SingleRowReader(
        string query, 
        int retrieve, 
        string splitBy = "§")
    {
        using var connection = GetConnection();
        using var command = GetCommand(query, connection);
        connection.Open();
        using var reader = command.ExecuteReader(CommandBehavior.SingleRow);

        string result = null;

        while (reader.Read())
            for (var i = 0; i < retrieve; i++)
                result += reader[i].ToString().Trim() + splitBy;

        return result?[..^1];
    }

    public IEnumerable<string> SingleRowReader(
        List<string> query, 
        int retrieve, 
        string splitBy = "§") => 
        query.Select(item => SingleRowReader(item, retrieve, splitBy));

    // Create a list out of the result of the query.
    public List<string> ListReader(
        string query, 
        int retrieve, 
        string splitBy = "§")
    {
        using var connection = GetConnection();
        using var command = GetCommand(query, connection);
        connection.Open();
        using var reader = command.ExecuteReader();

        var res = new List<string>();

        while (reader.Read())
        {
            var row = "";

            for (var y = 0; y < retrieve; y++)
                row += reader[y].ToString().Trim() + splitBy;

            res.Add(row?[..^1]);
        }

        return res;
    }

    public IEnumerable<List<string>> ListReader(
        List<string> query, 
        int retrieve, 
        string splitBy = "§") => 
        query.Select(item => ListReader(item, retrieve, splitBy));

    // Create a dictionary with the row as key and the results as values.
    public Dictionary<int, string> DictionaryReader(
        string query, 
        int retrieve = 0,
        string splitBy = "§")
    {
        var res = new Dictionary<int, string>();
        var att = ListReader(query, retrieve, splitBy);

        for (var i = 0; i < att.Count; i++)            
            res.Add(i, att[i]);

        return res;
    }

    // Create a dictionary with the list of columns 
    // as keys and the results divided by row as values.
    public Dictionary<string, List<string>> DictionaryReader(
        string query, 
        List<string> retrieve)
    {
        using var connection = GetConnection();
        using var command = GetCommand(query, connection);
        connection.Open();
        using var reader = command.ExecuteReader();

        var res = new Dictionary<string, List<string>>();

        foreach (var item in retrieve)
            res.Add(item, new List<string>());

        var row = 0;

        while (reader.Read())
        {
            for (var y = 0; y < retrieve.Count; y++)
            {
                var value = reader[retrieve[y]].ToString().Trim();

                res[retrieve[y]].Insert(row, value);
            }

            row++;
        }

        return res;
    }

    // Returns a table with the results of the query.
    public DataTable Table(
        string query)
    {
        using var connection = GetConnection();
        using var command = GetCommand(query, connection);
        connection.Open();
        var dataAdapter = GetDataAdapter(command);            
        var dt = new DataTable() { Locale = 
            System.Globalization.CultureInfo.InvariantCulture };

        switch (_type)
        {
            case QueryType.Odbc:
                ((OdbcDataAdapter)dataAdapter).Fill(dt);
                break;
            case QueryType.Sql:
                ((SqlDataAdapter)dataAdapter).Fill(dt);
                break;
            default:
                break;
        }

        return dt;
    }
}

// The class for SQL queries.
public class SQLQueries : Queries
{
    public SQLQueries(
        string connectionString,
        int timeOut = 60) :
        base(
            QueryType.Sql,
            connectionString,
            timeOut)
    { }
}

// The class for ODBC queries.
public class ODBCQueries : Queries
{
    public ODBCQueries(
        string connectionString, 
        int timeOut = 60) : 
        base(
            QueryType.Odbc, 
            connectionString, 
            timeOut)
    { }
}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.