CODEWITHSUNDEEP
c#winformssql-server-ce
user153923
user153923

Reputation:

C# SqlCeException: One Line SQL to determine table exists

Following the guidelines presented in SO Question #167576, I constructed the following SQL query string in my C# WinForm application to determine if table RMCoil exists:

using (SqlCeCommand cmd = new SqlCeCommand(null, new SqlCeConnection(Connection))) {
  cmd.Connection.Open();
  cmd.CommandText =
    "IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='RMCoil') " +
    "SELECT 1 ELSE SELECT 0";
  if (cmd.ExecuteNonQuery() < 1) {
    // code to create table
  }
}

Executing the non query throws this SqlCeException:

There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]

I'm fairly new to SqlCeServer. Could someone enlighten me on how to execute this query all in one line?

Upvotes: 2

Views: 1165

Answers (2)

user153923
user153923

Reputation:

This actually made a better test:

SELECT CASE WHEN EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='RMCoil') THEN 1 ELSE 0 END

Upvotes: 0

Mr47
Mr47

Reputation: 2655

SELECT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='RMCoil') might do the trick?
You would also have to do ExecuteScalar() instead of ExecuteNonQuery() I think to retrieve your value...

As Paul Sasik very wisely pointed out, the following query is even better:
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='RMCoil'

Upvotes: 3

Related Questions