CODEWITHSUNDEEP
sqllinqexecutequery
DevDave
DevDave

Reputation: 6908

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

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?

Upvotes: 0

Views: 342

Answers (3)

kevev22
kevev22

Reputation: 3775

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);

Upvotes: 1

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112612

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';

Upvotes: 0

jmacinnes
jmacinnes

Reputation: 1609

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.

Upvotes: 0

Related Questions