Generic database select method for within my application I want to implement a generic Get method for my ASP MVC application, which gets data from an MS Access database.

This is what I have so far, where I am testing it on the Pallet object.

Access

Get

public List&lt;T&gt; Get&lt;T&gt;(List&lt;string&gt; parameters)
{
    query = DatabaseConstants.Select&lt;T&gt;();
    argNames = DatabaseConstants.SelectParameters&lt;T&gt;();
    args = new DynamicParameters();
    foreach (var p in parameters)
    {
        args.Add(argNames[parameters.IndexOf(p)], p);
    }

    return NewSelect&lt;T&gt;(query, args);
}


Select

public List&lt;T&gt; NewSelect&lt;T&gt;(string query, DynamicParameters args = null)
{
    using (var db = new OleDbConnection(connectionString))
    {
        return (List&lt;T&gt;)db.Query&lt;T&gt;(query, args);
    }
}


DatabaseConstants

SelectQuery

public static string SelectQuery&lt;T&gt;()
{
    if (typeof(T) == typeof(Pallet))
    {
        return "SELECT * FROM subGRVReceived WHERE GRVNo=@grv";
    }
    else if (typeof(T) == typeof(Sample))
    {
        return "SELECT * FROM subReceivingQC WHERE GRV=@grv AND PalletSeq=@pallet"; 
    }
    else if (typeof(T) == typeof(QCItem))
    {
        return "SELECT * FROM subQCItems WHERE GRV=@grv AND PalletSeq=@pallet AND SampleNo=@sampleno";
    }
    else
    {
        return string.Empty;
    }
}


SelectParameters

public static List&lt;string&gt; SelectParameter&lt;T&gt;()
{
    if (typeof(T) == typeof(Pallet))
    {
        return new List&lt;string&gt;() { "@GRV" };
    }
    else if (typeof(T) == typeof(Sample))
    {
        return new List&lt;string&gt;() { "@grv", "@pallet" };
    }
    else if (typeof(T) == typeof(QCItem))
    {
        return new List&lt;string&gt;() { "@grv", "@pallet", "sampleno" };
    }
    else
    {
        return new List&lt;string&gt;();
    }
}


The method can be called like this:

Pallets = access.Get&lt;Pallet&gt;(new List&lt;string&gt;() { grvNumber });


Once I have completed this I will also look into creating generic Insert, Update etc methods. my main goal here is to just reduce the amount of code in the project, as I currently have a seperate method for each database query. 

Does anyone have any thoughts or suggestionss on this? Many thanks for taking the time to look!

___________-

I initially wanted to implement an IDatabaseObject containing Query and ParameterNames, where Pallet can inherit from this and store the values inside itself, but I got an error, as the property Pallets is of type List&lt;Pallet&gt;, and C# does not seem to allow me to assign the output of Get&lt;T&gt; to this value when I included a where T : IDatabaseObject.
First things first: your current code has a huge optimization to be made.


foreach (var p in parameters)
{
    args.Add(argNames[parameters.IndexOf(p)], p);
}



This is wrong, because argNames has an indexer and parameters is a List&lt;string&gt;. The IndexOf(T) method you call on parameters to get the index increases the amount of effort, work and time that your program has to perform in order to give you a result when all you really need to do is index the list.

for (int i = 0; i &lt; parameters.Count; i++) 
{
    args.Add(argNames[i], parameters[i]);
}


Much simpler. Of course, if the parameters list is larger than argNames, it will throw an exception. You can either keep it that way, or throw and explicit exception (which you should do to inform the user why their code failed).

public List&lt;T&gt; Get&lt;T&gt;(List&lt;string&gt; parameters)
{
    argNames = DatabaseConstants.SelectParameters&lt;T&gt;();
    if (argNames.Length != parameters.Count) // Is argNames a List? If so replace `Length` with `Count`
    {
        throw new ArgumentException($"The argument provided for '{nameof(parameters)}' was not the same length as the required number of parameters for this query.");
    }

    query = DatabaseConstants.Select&lt;T&gt;();
    args = new DynamicParameters();
    foreach (var p in parameters)
    {
        args.Add(argNames[parameters.IndexOf(p)], p);
    }

    return NewSelect&lt;T&gt;(query, args);
}




Next, let's rethink your process.


public static string SelectQuery&lt;T&gt;()
{
    if (typeof(T) == typeof(Pallet))
    {
        return "SELECT * FROM subGRVReceived WHERE GRVNo=@grv";
    }
    else if (typeof(T) == typeof(Sample))
    {
        return "SELECT * FROM subReceivingQC WHERE GRV=@grv AND PalletSeq=@pallet"; 
    }
    else if (typeof(T) == typeof(QCItem))
    {
        return "SELECT * FROM subQCItems WHERE GRV=@grv AND PalletSeq=@pallet AND SampleNo=@sampleno";
    }
    else
    {
        return string.Empty;
    }
}



This screams for a proper ORM, or at least a fake implementation of one. You also mention in the comments:


  I added some examples, but while doing so I realised that I'm actually going to need to query the same table but with an extra parameter at some point, so this probably wont work .. However as it stands it could be fairly useful (although not as reusable or generic as I might like!)


What if I told you that not only could we make this generic (and generate the query dynamically), but we could solve that problem at the same time?

I'm going to solve them one-at-a-time, but it won't be too difficult.

First, we need two pieces of data. We need the table name and property name, assuming that they're not defined by the property name itself. (As it appears.)

If you have access to the System.ComponentModel.DataAnnotations library, and the System.ComponentModel.DataAnnotations and System.ComponentModel.DataAnnotations.Schema namespaces, then this is significantly easer, otherwise you need to define the three attributes we're going to use. I'm going to use built-in's.

First, we're going to define our keys on the object with the System.ComponentModel.DataAnnotations.Key attribute, then we'll define the System.ComponentModel.DataAnnotations.Schema.Table attribute, and the System.ComponentModel.DataAnnotations.Schema.Column attribute.

[Table("TestTable")]
public class Foo
{
    [Key]
    [Column("fooId")]
    public int Id { get; set; }

    public string Name { get; set; }
}

[Table("TableBar")]
public class Bar
{
    [Key]
    [Column("barId")]
    public int BarId { get; set; }

    [Column("barName")]
    public string Name { get; set; }
}

public class Baz
{
    [Key]
    public int Id { get; set; }

    public int Order { get; set; }
}


The result is going to be something like:

SELECT * FROM TestTable WHERE fooId = @fooId
SELECT * FROM TableBar WHERE barId = @barId AND Name = @Name
SELECT * FROM Baz WHERE Id = @Id


So, how do we get there? A lot of reflection.

public static List&lt;string&gt; GetParameters&lt;T&gt;(IEnumerable&lt;string&gt; additionalConstraints = null)
{
    var type = typeof(T);

    var result = new List&lt;string&gt;();
    var keys = type.GetProperties().Where(x =&gt; x.IsDefined(typeof(KeyAttribute), true));
    if (keys.Any())
    {
        foreach (var key in keys)
        {
            if (key.IsDefined(typeof(ColumnAttribute), true))
            {
                var nameAttr = (ColumnAttribute)key.GetCustomAttributes(typeof(ColumnAttribute), true).FirstOrDefault();
                result.Add($"@{nameAttr.Name}");
            }
            else
            {
                result.Add($"@{key.Name}");
            }
        }
    }

    if (additionalConstraints?.Any() == true)
    {
        foreach (var constraint in additionalConstraints)
        {
            var prop = typeof(TableAttribute).GetProperties().Where(x =&gt; x.Name == constraint).First();
            if (prop.IsDefined(typeof(ColumnAttribute), true))
            {
                var nameAttr = (ColumnAttribute)prop.GetCustomAttributes(typeof(ColumnAttribute), true).FirstOrDefault();
                result.Add($"@{nameAttr.Name}");
            }
            else
            {
                result.Add($"@{prop.Name}");
            }
        }
    }

    return result;
}

public static string BuildSelect&lt;T&gt;(IEnumerable&lt;string&gt; additionalConstraints = null)
{
    var type = typeof(T);

    var sb = new StringBuilder();
    sb.Append("SELECT * FROM ");

    var tableAttribute = type.GetCustomAttributes(typeof(TableAttribute), true).FirstOrDefault();
    if (tableAttribute != null)
    {
        sb.Append(((TableAttribute)tableAttribute).Name);
    }
    else
    {
        sb.Append(type.Name);
    }

    var appendedClause = false;
    var parameters = GetParameters&lt;T&gt;(additionalConstraints);
    if (parameters.Count &gt; 0)
    {
        sb.Append(" WHERE ");
        foreach (var p in parameters)
        {
            if (appendedClause)
            {
                sb.Append(" AND ");
            }

            sb.Append($"{p.Substring(1)} = {p}");
            appendedClause = true;
        }
    }

    return sb.ToString();
}


Advantages? Your code doesn't need modified, at all, to add support for a new type. Simply add the type, define [Table(name)], [Key] and [Column(name)] if necessary.

Downsides? Calling it frequently can become expensive, it would not be difficult to build a cache to hold all the queries and select the relevant one from, so that if you call BuildSelect&lt;Foo&gt;() it would return the result from the cache.

Now the point we're at is great if you only need select queries, if you need more than that we hit a slight issue in that we have a lot of code to reproduce to build a new query. Now there is a fix for that.

public class QueryProperty
{
    public PropertyInfo Property { get; set; }
    public string Name { get; set; }
}

public class QueryData
{
    public string TableName { get; set; }
    public List&lt;QueryProperty&gt; KeyProperties { get; set; } = new List&lt;QueryProperty&gt;();
    public List&lt;QueryProperty&gt; NonKeyProperties { get; set; } = new List&lt;QueryProperty&gt;();
    public List&lt;QueryProperty&gt; AllProperties { get; set; } = new List&lt;QueryProperty&gt;();

    public string AsSelect(bool explicitColumns = true, bool includeKey = true)
    {
        var sb = new StringBuilder();
        sb.Append("SELECT ");

        if (explicitColumns)
        {
            sb.Append(string.Join(", ", AllProperties.Select(x =&gt; x.Name)));
        }
        else
        {
            sb.Append('*');
        }

        sb.Append(" FROM ").Append(TableName);

        if (includeKey)
        {
            sb.Append(" WHERE ");
            sb.Append(string.Join(" AND ", KeyProperties.Select(x =&gt; $"{x.Name} = @{x.Name}")));
        }

        return sb.ToString();
    }

    public string AsInsert(bool explicitColumns = true)
    {
        var sb = new StringBuilder();
        sb.Append("INSERT INTO ").Append(TableName).Append(' ');

        if (explicitColumns)
        {
            sb.Append('(');
            sb.Append(string.Join(", ", AllProperties.Select(x =&gt; x.Name)));
            sb.Append(')');
        }

        sb.Append(" VALUES (");
        sb.Append(string.Join(", ", AllProperties.Select(x =&gt; $"@{x.Name}")));
        sb.Append(')');

        return sb.ToString();
    }

    public string AsUpdate(bool explicitColumns = true, bool includeKey = true)
    {
        var sb = new StringBuilder();
        sb.Append("UPDATE ").Append(TableName).Append(' ');

        sb.Append("SET ");
        sb.Append(string.Join(", ", NonKeyProperties.Select(x =&gt; $"{x.Name} = @{x.Name}")));

        if (includeKey)
        {
            sb.Append(" WHERE ");
            sb.Append(string.Join(" AND ", KeyProperties.Select(x =&gt; $"{x.Name} = @{x.Name}")));
        }

        return sb.ToString();
    }

    public static QueryData Build&lt;T&gt;(IEnumerable&lt;string&gt; additionalConstraints = null)
    {
        var result = new QueryData();

        var type = typeof(T);
        if (type.IsDefined(typeof(TableAttribute)))
        {
            var attr = (TableAttribute)type.GetCustomAttribute(typeof(TableAttribute), true);
            result.TableName = attr.Name;
        }
        else
        {
            result.TableName = type.Name;
        }

        foreach (var prop in type.GetProperties())
        {
            if (prop.IsDefined(typeof(ColumnAttribute), true))
            {
                var nameAttr = (ColumnAttribute)prop.GetCustomAttributes(typeof(ColumnAttribute), true).FirstOrDefault();
                result.AllProperties.Add(new QueryProperty { Property = prop, Name = nameAttr.Name });
            }
            else
            {
                result.AllProperties.Add(new QueryProperty { Property = prop, Name = prop.Name });
            }
        }

        var keys = result.AllProperties.Where(x =&gt; x.Property.IsDefined(typeof(KeyAttribute), true));
        if (keys.Any())
        {
            foreach (var key in keys)
            {
                result.KeyProperties.Add(key);
            }
        }

        var nonKeys = result.AllProperties.Where(x =&gt; !x.Property.IsDefined(typeof(KeyAttribute), true));
        if (nonKeys.Any())
        {
            foreach (var nonKey in nonKeys)
            {
                result.NonKeyProperties.Add(nonKey);
            }
        }

        if (additionalConstraints?.Any() == true)
        {
            foreach (var keyName in additionalConstraints)
            {
                var key = result.AllProperties.Where(x =&gt; x.Property.Name == keyName).FirstOrDefault();
                if (key == null)
                {
                    throw new ArgumentException($"The value '{key}' provided for '{nameof(key)}' was not a property of the type '{type.FullName}'.");
                }

                result.KeyProperties.Add(key);
            }
        }

        return result;
    }
}


Now the call becomes:

var queryData = QueryData.Build&lt;Foo&gt;(new string[] { nameof(Foo.Name) });
var selectQuery = queryData.AsSelect();


I leave building a DELETE query up to you, once you have it I recommend posting a new Code Review question. :)