0

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?

DevDave
  • 6,700
  • 12
  • 65
  • 99

3 Answers3

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);
kevev22
  • 3,737
  • 21
  • 32
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.

jmacinnes
  • 1,589
  • 1
  • 11
  • 21
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';
Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
  • 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? – DevDave Dec 08 '11 at 17:47
  • As kevev22 says, use `ExecuteQuery` 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. – Olivier Jacot-Descombes Dec 08 '11 at 17:57