Dapper return single value

Question

Dapper return single value

c# dapper tsql

Question

I want to perform this simple test to see whether a table exists. I thought it would be easy just to return an int value depending on whether the table exists.

Below is what I have tried and doesn't work:

result = connection.ExecuteScalar<int>(@"
IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = '@tableSchema' 
                 AND  TABLE_NAME = '@tableName'))
    RETURN 0;


RETURN 1;
", new { tableSchema, tableName });

Error Message:

A RETURN statement with a return value cannot be used in this context.

It's something really noddy...

Callum Linington

Popular Answer

Instead of using IF..ELSE statement you can use the following query and check whether the result is greater than zero.

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA = '@tableSchema' AND      
TABLE_NAME = '@tableName'

BTW your query has syntax error, that's why you are seeing that error.

Karthik AMR

View more on Stack Overflow

Dirk · Accepted Answer · 6/7/2016 11:02:09 AM

ExecuteScalar

executes the query, and returns the first column of the first row in the result set returned by the query.

It does not return the return value of your SQL statements. You could simply use

SELECT 1
FROM   INFORMATION_SCHEMA.TABLES 
WHERE  TABLE_SCHEMA = @tableSchema
AND    TABLE_NAME   = @tableName

Then you can use ExecuteScalar and it will return 1 if the table exists. Please note that I also corrected your parameters in the SQL statements. They must not be delimited by '.

Dapper return single value

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator