Get value of first column without knowing name

Question

Similar to Column Number rather than Column Name but I believe it's different because that was for unnamed columns, whereas my columns are named, I just don't know the names ahead of time.

First, some background. I'm creating a method that takes a query and its arguments as parameters, then runs the query (using Dapper) and checks if the first row, first column value is a 1 or a 0. It's intended to work as a generic validator for databases.

For example, the user could enter a query that returns a 1 in the only row's only column if some value exists in some table, and a 0 otherwise. The use of this method is intentionally very broad and most of the effort is put on the user writing the query (I should also clarify that the "user" in this case is always another developer and the use is done within code calling this method, not on, say, form input).

I want to query my database and get the value of the first row, first column without knowing the name of the column ahead of time. Ideally (though I'm not sure if this is possible or not within the Dapper call), I'd also like to require that there only be one row and one column in the result. When I was searching around, I based my initial solution on this post's first answer, but found that for that to work I needed to know the name of the column:

var dict = connection.Query(query, parameters).ToDictionary(row => (string)row.VALUE);
if(dict.ElementAt(0).Value != 1){
    //do stuff
}

I thought of just adding a requirement that the user add an alias that names the first column some constant value (in this case, VALUE), but I'd prefer not to put the burden on my user if at all possible.

Ultimately my core issue is: how can I get the value of a named column in a DB using Dapper without knowing the name of the column? Does anybody have any ideas?

How do you define the "first" row in a result set? Are all your queries guaranteed to have a deterministic ORDER BY clause? — , Jul 07 '16 at 16:54
@mathguy Ultimately I want the query to only return one row; this method is intentionally very broad. As I said, this method is intended to be a generic validator for database data; so when properly used the query will only have a single result row. If there is more than one row, the query was invalid and I can check that outside of what is covered in the scope of this question. The burden of effort of this method is on the user writing the query, and that is intentional. I just don't want to require them to name their column, if possible. — Chris H., Jul 07 '16 at 16:59

score 3 · Accepted Answer · answered Jul 07 '16 at 17:26

3

I've done this successfully by just using int as the type in the generic Query method as such:

int value = conn.Query<int>(sql,
commandType: CommandType.StoredProcedure).FirstOrDefault();

Notice the use of the FirstOrDefault method. So you get 0 or whatever the value is from the DB.

answered Jul 07 '16 at 17:26

richinator38

165
1
18

2

This is the way to do it, indeed. Or ExecuteScalar. – Marc Gravell Jul 07 '16 at 17:30
Just to make sure I understand, in this case, if for whatever reason the query returns a result in which the first row, first column value is not an integer `value` will be set to 0? – Chris H. Jul 07 '16 at 17:31
1

Yes, I believe so. Of course if you don't know the type being returned, you can return an object and check in code for type. Not the best idea though. – richinator38 Jul 07 '16 at 17:35
`ExecuteScalar` is probably faster, but you lose the ability to ensure that you only have one row/column. – Jonathan Allen Jul 07 '16 at 21:37
As an update to Chris H.'s question above, when I get a 'null' value back, Dapper threw an "Object reference not set" error. So I had to change the int to int? and make sure it had a value before setting it. – richinator38 Jul 08 '16 at 18:24

score 0 · Answer 2 · answered Jul 07 '16 at 16:46

0

The easiest thing to do would be to make sure the SQL command ALWAYS returns the same column name, by way of an alias. Then your C# code can just use that alias.

answered Jul 07 '16 at 16:46

Xavier J

4,326
1
14
25

The problem is that I want to take a generic SQL command input by the user, so I can't guarantee that an alias will be used. I mentioned that just after my code block. – Chris H. Jul 07 '16 at 16:47
Then you really have no business using Dapper, or any other ORM! An ORM is designed to abstract your code AWAY from human-generated SQL statements. Great tool, but you are misapplying it. – Xavier J Jul 07 '16 at 16:50
This method will be used only internally by other developers who are most familiar with dapper and may want to use its parameter mapping features in their queries for validation (which this method supports). Could you elaborate on why I shouldn't be using Dapper for this? I'm a little confused. – Chris H. Jul 07 '16 at 17:13
ORMs are generally used to populate data into POCO classes with fixed property names, from SQL statements that are pre-determined *before* run-time. By trying to use Dapper to populate from a SQL statement generated *during* run-time, you're subverting the whole point of having the ORM in the first place. – Xavier J Jul 07 '16 at 17:23
Okay; I think part of the confusion here is that when I say "users write the SQL" I really mean "other developers write SQL", meaning that the queries are written before run-time into their code which uses my library/method. – Chris H. Jul 07 '16 at 17:25
@codenoir Your statement doesn't make any sense. Dapper doesn't have the ability to generation SQL. You have to provide it with either hand-written SQL or a stored procedure name. – Jonathan Allen Jul 07 '16 at 21:46
Dapper doesn't generate SQL. – Jonathan Allen Jul 07 '16 at 22:32

score 0 · Answer 3 · answered Jul 07 '16 at 21:42

Using Tortuga Chain

var result = DataSource.Sql(query, parameters).ToInt32().Execute();

I'd also like to require that there only be one row and one column in the result

By default, Chain uses DbCommand.ExecuteScalar. This returns the first column of the first row, ignoring everything else. To add that restriction I would do this:

var result = DataSource.Sql(query, parameters).ToInt32List().Execute().Single;

Another option is to change how you generate the SQL. For example,

var filterObject = new {ColumnA = 1, ColumnB = "X", ColumnC = 17};
var result = DataSource.From(tableName, filterObject).ToInt32(columnName).Execute();

This has the advantage of being immune to SQL inject attacks (which is probably a concern for you) and ensures that you SQL only returns one column before it is executed.

Get value of first column without knowing name

3 Answers3