Geek Answers Handbook

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

Back when I had only one table in my .sdffile, 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 if the database file ( Platypus.sdf) does not exist , but if there is a specific table (e.g. Platydudes) in this table / file. Is there any way to determine this?

UPDATE

The "IF NOT EXISTS" clause in the query raises an exception at run time. 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();
    }
}

... throws an exception to this exception: an error occurred parsing the request. [Token line number = 1, token line offset = 1, error token = IF]

, , "IF" . , ? , ? IOW, :

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

?

2

: NOPE! , " " .ExecuteNonQuery().

3

:

( ) ( " " ):

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();
}
+4
4

SQL Compact SQL, , , . , . :

TableExists SQL Compact ORN OpenNETCF:

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

, , . CurrentTransaction . GetNewCommandObject SqlCeCommand. GetConnection SqlCeConnection. DoneWithConnection NOP. Basiocally , ORM . , , - SQL, , true/false return.

( ) :

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);
    }
}
+11

:

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
}
+2

If you want to check if a table exists, you should use TABLE_SCHEMA

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

Based on Shiva's answer, I will do something like this:

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 ="IF NOT EXISTS( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Platydudes') " 
                           + "CREATE TABLE Platydudes (Id int NOT NULL, BillSize smallint NOT NULL, Description nvarchar(255) )";
        command.ExecuteNonQuery();
    }
}

... repeat "using partitions (var connection = ..." as necessary for each table (Platypups, Platypops, and c)

UPDATE

Using the ctacke code, I now have this:

public static void ConditionallyCreateTables()
{
    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;

            if (!TableExists(connection, "InventoryItems"))
            {
                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();
            }

            if (!TableExists(connection, "Departments"))
            {
                command.CommandText = "CREATE TABLE Departments (Id int NOT NULL, DeptNum int NOT NULL, DepartmentName nvarchar(255))";
                command.ExecuteNonQuery();
            }

            if (!TableExists(connection, "Subdepartments"))
            {
                command.CommandText = "CREATE TABLE Subdepartments (Id int NOT NULL, DeptId int NOT NULL, SubdeptId int NOT NULL, DepartmentName nvarchar(255))";
                command.ExecuteNonQuery();
            }

            if (!TableExists(connection, "Redemptions"))
            {
                command.CommandText = "CREATE TABLE Redemptions (Id int NOT NULL, RedemptionId nvarchar(50), RedemptionItemId nvarchar(50), RedemptionName nvarchar(255), RedemptionAmount numeric, RedemptionDept nvarchar(50), RedemptionSubdept nvarchar(50))";
                command.ExecuteNonQuery();
            }
        }
    }
}

UPDATE 2

Now I changed it in this format:

if (TableExists(connection, "InventoryItems"))
{
    command.CommandText = "DROP TABLE InventoryItems";
    command.ExecuteNonQuery();
}
command.CommandText = "CREATE TABLE InventoryItems (Id nvarchar(50) NOT NULL, PackSize smallint NOT NULL, Description nvarchar(255), DeptDotSubdept float, UnitCost float, UnitList float, UPCCode nvarchar(50), UPCPackSize smallint, CRVId int);";
command.ExecuteNonQuery();
-1
source

More articles:


All Articles