1
\$\begingroup\$

Lets say I have one method, which extracts property name and value:

public TModel Get (Expression<Func<object>> param)
{
        using (OracleConnection connection = new OracleConnection(GetConnectionString()))
        {
            connection.Open();

            var propertyName= ((MemberExpression)param.Body).Member.Name;

            var value = param.Compile()();

            // GetTableName() returns table name of TModel
            var query = $"SELECT * FROM {GetTableName()} WHERE {propertyName}='{value}'";

            var output = connection.Query<TModel>(query);
            connection.Dispose();
            return output.FirstOrDefault();

        }
}

and using it as:

var model = Get(() => foo.FirstProperty);

However, if I want to get name and value from the unknown number of properties, I do this:

public TModel Get(params Expression<Func<object>>[] param)
        using (OracleConnection connection = new OracleConnection(GetConnectionString()))
        {
            connection.Open();

            var query = new StringBuilder();

            query.Append($"SELECT * FROM {GetTableName()} WHERE ");
            for (int i = 0; i < param.Length; i++)
            {
                var propertyName = ((MemberExpression)param[i].Body).Member.Name;

                var value = param[i].Compile()();

                query.Append($"{propertyName} = '{value}'");
                if (i + 1 < param.Length) query.Append(" AND ");
            }

            var output = connection.Query<TModel>(query.ToString());
            connection.Dispose();
            return output.FirstOrDefault();
        }
    }

But, implementation looks a bit 'ugly' and verbose:

var model = Get(() => foo.FirstProperty, () => foo.SecondProperty); // and so on

I could send manualy name of the property + value, however, I would like to keep the implementation as simple as possible and less verbose.

Is there any way to simplify this?

\$\endgroup\$
  • 2
    \$\begingroup\$ Pseudocode, stub code, hypothetical code, obfuscated code, and generic best practices are outside the scope of this site. Please follow the tour, and read "What topics can I ask about here?", "How do I ask a good question?" and "What types of questions should I avoid asking?". \$\endgroup\$ – BCdotWEB Feb 24 at 16:09
  • \$\begingroup\$ Also, what is even the point of this code? In what scenario do you know so little of your DB that you're trying to guess what fields a table has? Surely you're not building your own ORM? If so: lostechies.com/jimmybogard/2012/07/24/dont-write-your-own-orm . \$\endgroup\$ – BCdotWEB Feb 24 at 16:13
  • \$\begingroup\$ @BCdotWEB This is not hypothetical, obfuscated code and I'm not looking for best practices. Main point of this question is how to simplify method to get name/value pairs of multiple properties. And building a dynamic sql statement is not the same as creating own ORM. Reason behind: I'm using dapper.Contrib extention and Oracle database. However, these two are not very 'friendly', therefore, I have a method which writes sql statement based on the given object and then pass it to the dapper, which does all the ORM job. \$\endgroup\$ – Eduardas Šlutas Feb 24 at 21:45
  • \$\begingroup\$ how about passing object[] which would be something like Get(() => new {foo.FirstProperty, foo.SecondProperty}) then just loop over the object array. \$\endgroup\$ – iSR5 Feb 24 at 22:43
  • \$\begingroup\$ aside from other comments, why do you invoke " connection.Dispose()" if you're enclosing in "using"? \$\endgroup\$ – yob Feb 25 at 0:31
2
\$\begingroup\$

If you want to iterate through all of the properties then reflection will help you to do that. You can find more at MS docs Something like this:

public TModel Get<TParam>(TParam param)
{
        var allParams = typeof(TParam).GetProperties().Select(p => $"{p.Name} ='{p.GetValue(param)}'");
        var condition = string.Join(" AND ", allParams);

        using (OracleConnection connection = new OracleConnection(GetConnectionString()))
        {
            connection.Open();

            // GetTableName() returns table name of TModel
            var query = $"SELECT * FROM {GetTableName()} WHERE {condition}";

            var output = connection.Query<TModel>(query);
            return output.FirstOrDefault();
        }
}

But you have to think how to escape SQL injections. Because the value of query variable in current implementation could have potential vulnerabilities.

| improve this answer | |
\$\endgroup\$

Not the answer you're looking for? Browse other questions tagged or ask your own question.