How can I programmatically determine if a table exists within a SQL Server CE database?

17,799

Solution 1

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:

public override bool TableExists(string tableName)
{
    var connection = GetConnection(true);
    try
    {
        using (var command = GetNewCommandObject())
        {
            command.Transaction = CurrentTransaction as SqlCeTransaction;
            command.Connection = connection;
            var sql = string.Format(
                    "SELECT COUNT(*) FROM information_schema.tables WHERE table_name = '{0}'", 
                     tableName);
            command.CommandText = sql;
            var count = Convert.ToInt32(command.ExecuteScalar());

            return (count > 0);
        }
    }
    finally
    {
        DoneWithConnection(connection, true);
    }
}

Obviously it's not complete for your case, but it's pretty easy to understand. CurrentTransaction could easily be null. GetNewCommandObject simply returns a new SqlCeCommand instance. GetConnection simply could return a new SqlCeConnection 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:

public bool TableExists(SqlCeConnection connection, string tableName)
{
    using (var command = new SqlCeCommand())
    {
        command.Connection = connection;
        var sql = string.Format(
                "SELECT COUNT(*) FROM information_schema.tables WHERE table_name = '{0}'", 
                 tableName);
        command.CommandText = sql;
        var count = Convert.ToInt32(command.ExecuteScalar());
        return (count > 0);
    }
}

Solution 2

If you want check if a Table exist you must use TABLE_SCHEMA

IF (EXISTS (SELECT * 
             FROM INFORMATION_SCHEMA.TABLES 
             WHERE TABLE_SCHEMA = 'TheSchema' 
             AND  TABLE_NAME = 'TheTable'))
BEGIN
--Do Stuff
END

Solution 3

Using Database helper:

var db = Database.Open("MyDatabase");
var sql = @"SELECT Count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MyTable'"
var count = db.QueryValue(sql);
if(count.Equals(1)){
    //table exists
}
Share:
17,799

Related videos on Youtube

B. Clay Shannon-B. Crow Raven
Author by

B. Clay Shannon-B. Crow Raven

My novel about climate change and social justice featuring talking animals traveling through time and space to prevent disasters is now available on amazon, in three formats: Taterskin & The Eco Defenders Kindle eBook; Taterskin & The Eco Defenders Paperback; Taterskin & The Eco Defenders Hardcover Taterskin & The Eco Defenders, told in “first canine” by the titular character, a Labrador Retriever, is the story of a few humans and several talking animals who travel through time and space to make the past—and thus the future—a better place. The improvements effected by the Eco Defenders benefit not just the earth itself, but also mistreated humans and animals. In Book 1 (“Wonders Never Cease”), The Eco Defenders travel 150 million years into the past, to meet a Pterodactyl and make plans to “nip Nazism in the bud.” After that, it's on to 1787 Australia to protect the indigenous people and the environment there. The Eco Defenders next go to India, where they assemble animals from all over that country to put an end to Thuggee and fights to the death between Cobras and Mongooses. Their final stop is 1885 Africa, where the Eco Defenders band together with the local animals to prevent King Leopold of Belgium from taking control of the Congo, following which they put an end to the poaching of animals throughout the continent. Book 2 (“Tell it to Future Generations”) takes up with the Eco Defenders following up on their earlier adventures by 1) Preventing the American Civil War in 1861, after which a slave they free joins them; 2) Saving the Indians from being massacred at Wounded Knee in 1890, following which Chapawee, a Sioux Indian, joins the Eco Defenders; 3) Putting an end to the practice of vivisection (experimentation on live animals) in 1903; 4) Coming to the aid of exploited workers in 1911 Manhattan, saving hundreds from the Triangle Shirtwaist Fire; and 5) Traveling to the Amazon Basin in 1978 to protect and preserve the Amazon rainforest. @@@@@@@@@@@@@@@@@@@@@@@ I have lived in eight states; besides my native California (where I was born and where I now again reside), in chronological order I have infested: New York (Brooklyn), Montana (Helena), Alaska (Anchorage), Oklahoma (Bethany), Wisconsin (New Berlin and Oconomowoc), Idaho (Coeur d'Alene), and Missouri (Piedmont). I am a writer of both fiction (for which I use the nom de guerre "Blackbird Crow Raven", as a nod to my Native American heritage - I am "½ Cowboy, ½ Indian") and nonfiction, including a two-volume social and cultural history of the U.S. which covers important events from 1620-2006 and can be downloaded gratis here.

Updated on September 22, 2022

Comments

  • B. Clay Shannon-B. Crow Raven
    B. Clay Shannon-B. Crow Raven less than a minute

    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();
    }
    
    • ctacke
      ctacke almost 9 years
      DELETE InventoryItems does not delete the table, it just deletes all rows. DROP TABLE InventoryItems would delete the table itself.
    • B. Clay Shannon-B. Crow Raven
      B. Clay Shannon-B. Crow Raven almost 9 years
      Oh, yeah, I knew that; brain freeze!
  • Alex Jolig
    Alex Jolig almost 8 years
    I think you should add connection.Open(); before var count = Convert.ToInt32(command.ExecuteScalar());. Mine worked that way.