How can I check whether a table exists in SQL Server CE 3.5
I have a database user.sdf
in asp.net, I want to create the tables for that I need check it check it first exist are not if exist then need not create the tables, else create t开发者_开发百科he new table how can I check it that please help me to resolve this.
You can query the schema views in SQL CE 3.5 take a look here.
Here is a simple extension method that you could use.
public static class SqlCeExtentions
{
public static bool TableExists(this SqlCeConnection connection, string tableName)
{
if (tableName == null) throw new ArgumentNullException("tableName");
if (string.IsNullOrWhiteSpace(tableName)) throw new ArgumentException("Invalid table name");
if (connection == null) throw new ArgumentNullException("connection");
if (connection.State != ConnectionState.Open)
{
throw new InvalidOperationException("TableExists requires an open and available Connection. The connection's current state is " + connection.State);
}
using (SqlCeCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT 1 FROM Information_Schema.Tables WHERE TABLE_NAME = @tableName";
command.Parameters.AddWithValue("tableName", tableName);
object result = command.ExecuteScalar();
return result != null;
}
}
}
You can use the above as follows
using (SqlCeConnection connection = new SqlCeConnection(@"Data Source=MyDatabase1.sdf"))
{
connection.Open();
if (connection.TableExists("MyTable"))
{
// The table exists
}
else
{
// The table does not exist
}
}
As an alternative you can Query the Table and catch the Exception thrown. If there is an Exception, the Table wasn't found, else the Table exists.
SELECT TOP 1 1 FROM TableName;
A little and simple Performance Test had better Results than the Query against INFORMATION_SCHEMA. Although I would consider a Query against INFORMATION_SCHEMA as cleaner.
精彩评论