System Views in SQL Server Compact Edition: Tables

First off, let me say a few thank you’s. Thanks to the Lower Alabama .Net User Group for putting on a great code camp. And thanks to all of you who attended my presentation, I felt honored, especially considering some of the others who were speaking at the same time. Now, on to the views.

While SQL Server Compact Edition does not support the creation of views, it does come with several views built in. For those of you familiar with full blown SQL Server, these will be familiar as they are all part of the INFORMATION_SCHEMA.

There are seven, Columns, Indexes, Key_Column_Usage, Tables, Table_Constraints, Provider_Types, and Referential_Constraints. When SSCE was created, these views were setup to mimic many of the views of “big” SQL Server. But because SSCE only supports a limited set of features, many of the fields wind up being NULL. Like in the INFORMATION_SCHEMA.TABLES view.

For this example, I’m creating a couple of very simple tables. Start by creating a brand new SSCE database. Next, here’s the SQL needed to create my example tables, you should be able to use either Visual Studio Database Explorer or SQL Server Management Studio.

create table employee (
empid uniqueidentifier not null,
last   nvarchar(100),
first nvarchar(100),
birth  datetime,
constraint pk_empid primary key (empid)
);
go
create table inventory (
partid uniqueidentifier not null,
partname nvarchar(100) not null,
qtyonhand int,
location nvarchar(50),
constraint pk_partid primary key (partid)
)

OK, now that you have a few tables created, let’s issue the following command:

select * from information_schema.tables;

What gets brought back is:

You’ll note all the NULLs, as I said most of this was jettisoned in order to put the Compact in Compact Edition. As such, we have a lot of unnecessary fields in the output. Let’s refine the query a little:

select table_name, table_type from information_schema.tables;

Produces this output:

At first glance you may think the TABLE_TYPE of TABLE is redundant. And with the state of SSCE today you’d be right. But for the time being if you want to write queries that will survive into the future, I’d suggest writing your SQL as:

select table_name from information_schema.tables where table_type = ‘TABLE’;

This will give you this output:

Now you have a nice, safe query that will return all table names in your database, and you can rest assured that it will survive into the future should Microsoft decide to add other object types to the Tables view.

So what could you do with this? Well I setup a little test harness similar to the one I did in the last series. I created a simple windows form with one button named Tables and a label we’ll call lblResults. I also made a few other minor changes from my previous code base. Here’s the code, so you can see:

    // Class level to hold connection
    SqlCeConnection _cn = new SqlCeConnection(ConnectString()); 
 
    public Form1()
    {
      InitializeComponent();
    }
    #region ConnectString
    // This became static so it could be called 
    // during the constructor, so we could set the
    // class level variable.
    private static string ConnectString()
    {
      string connectionString;
      string fileName = “SSCE_View_Test.sdf”;
 
      connectionString = string.Format(
        “DataSource=\”{0}\” “, fileName) ; 
 
      return connectionString;
    }
    #endregion
 
    #region OpenConnection
    private void OpenConnection()
    {
      if (_cn.State==ConnectionState.Closed)
      {
        _cn.Open();
      }
 
    }
    #endregion
 
    #region btnTables_Click
    private void btnTables_Click(object sender, EventArgs e)
    {
      // In case it’s not already open
      // Calling it too much won’t hurt, 
      // as it checks first before trying
      OpenConnection();
 
      SqlCeCommand cmd;
      string sql = “select table_name “
      + “from information_schema.tables “
      + “where table_type = ‘TABLE'”;
 
      try
      {
        cmd = new SqlCeCommand(sql, _cn);
        cmd.CommandType = CommandType.Text;
        SqlCeResultSet rs = 
          cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
 
        if (rs.HasRows)
        {
          int ordTable = rs.GetOrdinal(“table_name”);
          StringBuilder output = new StringBuilder();
          rs.ReadFirst();
          output.AppendLine(rs.GetString(ordTable));
          while (rs.Read())
          {
            output.AppendLine(rs.GetString(ordTable));
          }
          lblResults.Text = output.ToString();
        }
        else
          lblResults.Text = “No tables found.”;
      }
      catch (SqlCeException sqlexception)
      {
        MessageBox.Show(sqlexception.Message, “Oh Crap.”,
          MessageBoxButtons.OK, MessageBoxIcon.Error);
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.Message, “Oh Crap.”,
          MessageBoxButtons.OK, MessageBoxIcon.Error);
      }
 
    }
    #endregion
  }
 
Here’s the output:
 
 
You could use this to build your own SQL Queries, reports, or to do code generation. 
The rest of the examples in this series will fall into the same test harness, so be sure to get a good look and understanding. The rest of the week we’ll spend exploring some of the more useful SSCE Views. 

System Views in SQL Server Compact Edition: Tables

Published by arcanecode

Leave a Reply Cancel reply

Share this:

Like this:

Related

Published by arcanecode

Leave a Reply Cancel reply