How can I check if a table exists?
-
can anyone help? I tried to google out the solution but none of the answers I found was non proper for C#. I've a MS SQL Database (Compact Edition(this is not an issue here)) I want to check if a table exists, what should I do? I found an SQL statement for this
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND type in (N'U'))but I could not figure out how to use this in C# My first idea is=string command2 = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND type in (N'U'))"
SqlCeCommand com2 = new SqlCeCommand(command2, conn);Where should I look if it returned 1 or 0?
-
can anyone help? I tried to google out the solution but none of the answers I found was non proper for C#. I've a MS SQL Database (Compact Edition(this is not an issue here)) I want to check if a table exists, what should I do? I found an SQL statement for this
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND type in (N'U'))but I could not figure out how to use this in C# My first idea is=string command2 = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND type in (N'U'))"
SqlCeCommand com2 = new SqlCeCommand(command2, conn);Where should I look if it returned 1 or 0?
SQLCe will not support [dbo]. at the head of a table name. I don't know if it supports this sort of call ( I've never tried it ). Remove the [dbo]. and then complete the statement ( such as if EXISTS... select 1 ELSE select 0 ) then you can execute scalar and see if you get 1 or 0 back. You can experiment inside SQL Server management console to establish exactly what SQL works. Your alternative is to say select * from tablename and do it in a try catch to see if it blows up ( presumably b/c the table does not exist )
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
-
SQLCe will not support [dbo]. at the head of a table name. I don't know if it supports this sort of call ( I've never tried it ). Remove the [dbo]. and then complete the statement ( such as if EXISTS... select 1 ELSE select 0 ) then you can execute scalar and see if you get 1 or 0 back. You can experiment inside SQL Server management console to establish exactly what SQL works. Your alternative is to say select * from tablename and do it in a try catch to see if it blows up ( presumably b/c the table does not exist )
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
thanks I'll try furthermore, just to know Can I use this "if -> else" format like below?
IF (blahblahblah) >0 BEGIN CREATE TABLE MyTable (...etc) END" -
can anyone help? I tried to google out the solution but none of the answers I found was non proper for C#. I've a MS SQL Database (Compact Edition(this is not an issue here)) I want to check if a table exists, what should I do? I found an SQL statement for this
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND type in (N'U'))but I could not figure out how to use this in C# My first idea is=string command2 = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND type in (N'U'))"
SqlCeCommand com2 = new SqlCeCommand(command2, conn);Where should I look if it returned 1 or 0?
Just execute the command, but a better command would be
string command2 = "Select Case OBJECT_ID('TableName') when null then 0 else 1 end;";
SqlCeCommand com2 = new SqlCeCommand(command2, conn);
bool tableExists = (bool)com2.ExecuteScalar();If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) www.JacksonSoft.co.uk
-
Just execute the command, but a better command would be
string command2 = "Select Case OBJECT_ID('TableName') when null then 0 else 1 end;";
SqlCeCommand com2 = new SqlCeCommand(command2, conn);
bool tableExists = (bool)com2.ExecuteScalar();If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) www.JacksonSoft.co.uk
oh this works? I'll try right now :D
-
thanks I'll try furthermore, just to know Can I use this "if -> else" format like below?
IF (blahblahblah) >0 BEGIN CREATE TABLE MyTable (...etc) END"I think so. I've never executed SQL that complex outside of a proc, but I don't see why you shouldn't be able to.
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
-
can anyone help? I tried to google out the solution but none of the answers I found was non proper for C#. I've a MS SQL Database (Compact Edition(this is not an issue here)) I want to check if a table exists, what should I do? I found an SQL statement for this
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND type in (N'U'))but I could not figure out how to use this in C# My first idea is=string command2 = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND type in (N'U'))"
SqlCeCommand com2 = new SqlCeCommand(command2, conn);Where should I look if it returned 1 or 0?
I would likely use
SELECT COUNT(*) FROM sys.objects ...to see how many tables have that name and type. -
Just execute the command, but a better command would be
string command2 = "Select Case OBJECT_ID('TableName') when null then 0 else 1 end;";
SqlCeCommand com2 = new SqlCeCommand(command2, conn);
bool tableExists = (bool)com2.ExecuteScalar();If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) www.JacksonSoft.co.uk
The function is not recognized by SQL Server Compact Edition. [ Name of function = OBJECT_ID,Data type (if known) = ] ..it says pointing out the
bool tableExists = (bool)com2.ExecuteScalar();line :/ -
I think so. I've never executed SQL that complex outside of a proc, but I don't see why you shouldn't be able to.
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
according to compiler Compact Edition does not support the function OBJECT_ID() I guess try-catch is the only solution is it?
-
according to compiler Compact Edition does not support the function OBJECT_ID() I guess try-catch is the only solution is it?
I would presume so. I am not surprised, CE is a very cut down version, designed to be portable, not complete.
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
-
The function is not recognized by SQL Server Compact Edition. [ Name of function = OBJECT_ID,Data type (if known) = ] ..it says pointing out the
bool tableExists = (bool)com2.ExecuteScalar();line :/SQL CE. silly me.
string command2 = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'your table name'";
SqlCeCommand com2 = new SqlCeCommand(command2, conn);
bool tableExists = false;
If ((Integer)com2.ExecuteNonQuery() > 0){
tableExists = true;
}ExecuteNonQuery returns the number of rows selected and if the table exists the return value will be 1.
If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) www.JacksonSoft.co.uk
-
SQL CE. silly me.
string command2 = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'your table name'";
SqlCeCommand com2 = new SqlCeCommand(command2, conn);
bool tableExists = false;
If ((Integer)com2.ExecuteNonQuery() > 0){
tableExists = true;
}ExecuteNonQuery returns the number of rows selected and if the table exists the return value will be 1.
If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) www.JacksonSoft.co.uk
oh thanks =)