Back when I only had one table in my .sdf
file, this code worked fine:
const string sdfPath = @"\Program Files\duckbilled\Platypus.sdf";
string dataSource = string.Format("Data Source={0}", sdfPath);
if (!File.Exists(sdfPath))
{
using (var engine = new SqlCeEngine(dataSource))
{
engine.CreateDatabase();
}
using (var connection = new SqlCeConnection(dataSource))
{
connection.Open();
using (var command = new SqlCeCommand())
{
command.Connection = connection;
command.CommandText =
"CREATE TABLE Platydudes (Id int NOT NULL, BillSize smallint NOT NULL, Description nvarchar(255)";
command.ExecuteNonQuery();
}
}
}
…but now I need to know, not whether the database file (Platypus.sdf
) exists, but whether a particular table (such as Platydudes
) exists in that table/file. Is there a way to determine that?
UPDATE
The 'IF NOT EXISTS' clause in the query causes a runtime exception. This code:
using (var connection = new SqlCeConnection(dataSource))
{
connection.Open();
using (var command = new SqlCeCommand())
{
command.Connection = connection;
command.CommandText = "IF NOT EXISTS( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'InventoryItems') " +
"CREATE TABLE InventoryItems (Id nvarchar(50) NOT NULL, PackSize smallint NOT NULL, Description nvarchar(255), DeptDotSubdept numeric, UnitCost numeric, UnitList numeric, UPCCode nvarchar(50), UPCPackSize smallint, CRVId int);";
command.ExecuteNonQuery();
}
}
…causes this exception to be thrown: There was an error parsing the query. [ Token line number = 1, Token line offset = 1, Token in error = IF ]
So obviously the "IF" business is unwanted by the query parser. Is there another way to only create the table if it doesn't already exist? Or should I, each time, first delete the table then recreate it? IOW, should I do this:
using (var connection = new SqlCeConnection(dataSource))
{
connection.Open();
using (var command = new SqlCeCommand())
{
command.Connection = connection;
command.CommandText = "DELETE InventoryItems";
command.ExecuteNonQuery();
}
using (var command = new SqlCeCommand())
{
command.Connection = connection;
command.CommandText = "CREATE TABLE InventoryItems (Id nvarchar(50) NOT NULL, PackSize smallint NOT NULL, Description nvarchar(255), DeptDotSubdept numeric, UnitCost numeric, UnitList numeric, UPCCode nvarchar(50), UPCPackSize smallint, CRVId int);";
command.ExecuteNonQuery();
}
}
?
UPDATE 2
To answer my question above in the first update: NOPE! If I do that, I get "The specified table already exists" on the second call to .ExecuteNonQuery().
UPDATE 3
In response to Shiva's comment to my answer:
This (reusing the command object) fails the same way ("table already exists"):
using (var command = new SqlCeCommand())
{
command.Connection = connection;
command.CommandText = "DELETE InventoryItems";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE InventoryItems (Id nvarchar(50) NOT NULL, PackSize smallint NOT NULL, Description nvarchar(255), DeptDotSubdept numeric, UnitCost numeric, UnitList numeric, UPCCode nvarchar(50), UPCPackSize smallint, CRVId int);";
command.ExecuteNonQuery();
}
Best Answer
SQL Compact doesn't like much for logic inside a SQL statement, so Shiva's answer probably won't pass the parser. He's on the right course, though. You can to do it in two steps:
Here's the
TableExists
method from the SQL Compact implementation of the OpenNETCF ORM:Obviously it's not complete for your case, but it's pretty easy to understand.
CurrentTransaction
could easily be null.GetNewCommandObject
simply returns a newSqlCeCommand
instance.GetConnection
simply could return a newSqlCeConnection
instance. DoneWithConnection could be a NOP. Basiocally these are all handling the fact that the ORM supports a plethora of backing stores. The kernel of info you need is the SQL that I pass in and how I determine the true/false return.My (untested in a compiler) guess to the resulting method would be something like this: