CODEWITHSUNDEEP
c#sqldatabasesql-server-ce
Tom
Tom

Reputation: 154

Check if table name exists SQL

How can I check if a table already exists before creating a new one?

Updated Code:

    private void checkTable()
            {

                string tableName = quotenameTxt.Text + "_" + firstTxt.Text + "_" + surenameTxt.Text;
                string connStr = @"Data Source=|DataDirectory|\LWADataBase.sdf";
             //   SqlCeConnection conn = new SqlCeConnection(connStr);
            //    if (conn.State == ConnectionState.Closed) { conn.Open(); }
                using (SqlCeConnection conn = new SqlCeConnection(connStr))
    {
        conn.Open();    
        SqlCeCommand cmd = new SqlCeCommand(@"SELECT * 
                                              FROM INFORMATION_SCHEMA.TABLES 
                                              WHERE TABLE_NAME = @tname", conn);
        cmd.Parameters.AddWithValue("@tname", tableName);
        SqlCeDataReader reader = cmd.ExecuteReader();
        if(reader.Read()){
            MessageBox.Show("Table exists");}
        else{
            MessageBox.Show("Table doesn't exist");
createtable();}

Upvotes: 0

Views: 2408

Answers (2)

Steve
Steve

Reputation: 216293

Sql Server Compact supports the INFORMATION_SCHEMA views 

using (SqlCeConnection conn = new SqlCeConnection(connStr))
{
    conn.Open();    
    SqlCeCommand cmd = new SqlCeCommand(@"SELECT TOP 1 * 
                                          FROM INFORMATION_SCHEMA.TABLES 
                                          WHERE TABLE_NAME = @tname", conn);
    cmd.Parameters.AddWithValue("@tname", tableName)
    SqlCeDataReader reader = cmd.ExecuteReader();
    if(reader.Read())
        Console.WriteLine("Table exists");
    else
        Console.WriteLine("Table doesn't exist");

}

EDIT In version 3.5 it seems that the TOP 1 instruction is not accepted. However, given the WHERE clause it should make no difference using it or not so, to make it work just change the query to 

SqlCeCommand cmd = new SqlCeCommand(@"SELECT * FROM INFORMATION_SCHEMA.TABLES 
                                      WHERE TABLE_NAME = @tname", conn);

SECOND EDIT Looking at the code that creates the table.
(It is In chat, I suggest to add it to the question for completeness)

using (SqlCeCommand command = new SqlCeCommand( 
        "CREATE TABLE ['" + tableName + "'] " + 
        "(Weight INT, Name NVARCHAR, Breed NVARCHAR)", con))

The single quotes around the tableName variables becomes part of the name of the table. But the check for table exists doesn't use the quotes. And your code fall through the path that tries to create again the table with the quotes. Just remove the quotes around the name. They are not needed.

Upvotes: 1

dovid
dovid

Reputation: 6462

You can use the SqlClientConnection to get list of all objects in the db.

private void checkTable()
{
    string tableName = quotenameTxt.Text + "-" + firstTxt.Text + "-" + surenameTxt.Text;
    string connStr = @"Data Source=|DataDirectory|\LWADataBase.sdf";

    using (SqlCeConnection conn = new SqlCeConnection(connStr))
    {
        bool isTableExist = conn.GetSchema("Tables")
                                .AsEnumerable()
                                .Any(row => row[2] == tableName);
    }

    if (!isTableExist)
    {
        MessageBox.Show("No such data table exists!");
    }
    else
    {
        MessageBox.Show("Such data table exists!");
    }
}

Source: https://stackoverflow.com/a/3005157/1271037

Upvotes: 0

Related Questions