Return a sql value indicating whether a file exists, using c# and ExecuteCommand

Question

Apologies if the question has already been answered elsewhere but I look around already with no luck. I am trying to establish whether or not a database table already exists using mvc3 and c#.

And I am putting the following sql into a resx file to call:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[%tableName%]') AND type in (N'U'))
SELECT 1
ELSE 
SELECT 0

This works fine in Management Studio but I am having trouble getting it to work in my c# project, where I try to call it like this:

public bool GetIsInstalled(string tableName)
        {
            return _db.ExecuteCommand(Scripts.CheckIfInstalled.Replace("%tableName%", tableName))==1;
        }

When I debug the method the value returned is actually -1, so I guess there's something wrong somewhere? I was thinking that it might be that I need to execute a query rather than a command? Could someone help me with this and please provide a code example if possible?

kevev22 · Accepted Answer · 2011-12-08 17:48:16Z

1

The ExecuteCommand method returns the number of rows that were modified by the executed command. That is not what you want in this case. Instead you should be using the ExecuteQuery method:

return db.ExecuteQuery<int>(
    Scripts.CheckIfInstalled.Replace("%tableName%", tableName)
).Single().Equals(1);

answered Dec 8, 2011 at 17:48

kevev22

3,76524 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jmacinnes · Accepted Answer · 2011-12-08 17:40:19Z

0

Try this simpler query:

SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[%tableName%]') AND type in (N'U')

Also, check that the user specified in your connection string has access to systables.

answered Dec 8, 2011 at 17:40

jmacinnes

1,6091 gold badge12 silver badges21 bronze badges

1 Comment

DevDave Over a year ago

thanks for your response but that is also returning a -1 error

Olivier Jacot-Descombes · Accepted Answer · 2011-12-08 17:45:41Z

0

I don't know which kind of db interface you are using, but if you were using System.Data.SqlClient.SqlCommand for instance you would do something like

object result = cmd.ExecuteScalar();

By the way: You could just use:

SELECT COUNT(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[%tableName%]') AND type=N'U';

answered Dec 8, 2011 at 17:45

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

2 Comments

DevDave Over a year ago

Sorry I am using Linq to SQL. I'll give your sql a try but I think its just going to return -1 again for some reason. Wondered if it was the way I was calling it from c# was what was wrong?

Olivier Jacot-Descombes Over a year ago

As kevev22 says, use ExecuteQuery<T> in Linq to Sql, in order to return results from a select query. ExecuteCommand would be used for queries that don't return results, like UPDATE or INSERT queries.

Collectives™ on Stack Overflow

Return a sql value indicating whether a file exists, using c# and ExecuteCommand

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related