SQL Compact Query Analyzer

I’ve been working extensively on enterprise mobility projects lately. These applications integrate into large SAP based systems and when testing the system it can get very tedious to set up some temporary data from the backend. I’m also working with some not-so-technical testers that get intimidated by the Visual Studio or the SQL Server Management Studio. This led me to writing an open source project called SQL Compact Query Analyzer

Here’s some details I pulled directly off the CodePlex site

Project Description
SQL Server Compact Edition Database Query Analyzer

Features:

– Create new database
– Automatically refresh database upon executing create/alter/drop table queries
– Displays database information (database version, filename, size, creation date)
– Displays schema summary (number of tables, columns, primary keys, identity fields, nullable fields)
– Displays the information schema views
– Displays column information (database type, clr type, max length, allows null, etc)
– Displays index information (column name, is unique, is clustered)
– Execute SQL Queries against a SQL Server Compact Edition database
– Execute multiple SQL queries (delimited by a semi colon 😉
– Easily edit the contents of the database
– Display query result as XML
– Shrink and Compact Databases
– SDF file association with SQL Compact Query Analyzer for launching directly by opening the SDF in Windows Explorer
– Displays thumbnails for IMAGE fields
– Generates Schema and Data Scripts
– Supports password protected databases
– Supports SQLCE 3.0, 3.1, 3.5 and 4.0

Coming Soon:
– Purge database content
– Create, edit, and drop tables UI
– Create, edit, and delete table references and indexes UI
– Support for SQL Server Compact Edition 2.0

Screenshots


– Displays database and schema information and executes multiple SQL queries directly


– Edit the table data directly


– Display the contents of IMAGE fields


– Performance numbers for queries


– Query errors


– Output result set as XML


– Create new database


– Shrink, compact, script database

Prerequisites:
– .NET Framework 4.0

Check it out! You might find it useful!

SQL Compact Code Generator

More than a year ago, I published a project on CodePlex called SQL Compact Code Generator. Unfortunately, I never managed to find the time to do some work on it and the project was set on a very long hold. A year after I suddenly really needed such a tool and decided that I should put in some hours on the project.

I’m currently working on a large enterprise project where changes to the database schema is done rather frequently, to avoid the pain of updating my data layer after every change I decided to use my code generator.

Here’s some details I pulled directly off the CodePlex site.

Project Description
Contains a stand alone GUI application and a Visual Studio Custom Tool for automatically generating a .NET data access layer code for objects in a SQL Server Compact Edition database.

Features:

  • Visual Studio 2008 and 2010 Custom Tool Support
  • Creates entity classes for each table in the database
  • Generates data access code that implements the Repository Pattern
  • Generates methods for Create, Read, Update and Delete operations
  • Generates SelectBy and DeleteBy methods for every column in every table
  • Generates a Purge method for every table to delete all records
  • Generates Count() method for retrieving the number of records in each table
  • Generates CreateDatabase() method for re-creating the database
  • Generates xml-doc code comments for entities and data access methods
  • Generates Entity Unit Tests
  • Generates Data Access Unit Tests
  • Generates .NET Compact and Full Framework compatible code
  • Support for SQL Compact Edition version 4.0
  • Multiple test framework code generation (MSTest, NUnit, xUnit)
  • Transaction support per DataRepository instance (Begin, Commit, Rollback)
  • Code generation options to enable/disable unit test code generation
  • Windows Phone 7 “Mango” support for generating a LINQ to SQL DataContext
Coming Soon:
  • Generate database maintenance code (clear database, shrink/compress database)
  • Support for multiple versions of SQL Server Compact Edition
  • VB.NET Code Support
  • Visual Studio Add-in

Screenshots:


NEW: Custom Tool for Windows Phone 7 “Mango”

Custom Tool


Generating Entity Classes


Generating Data Access methods that implement the Repository Pattern


Generating Entity Unit Tests


Generating Data Access Unit Tests to validate the integrity between the data layer and the actual database

Check it out! You might find it useful too…

SQLCE Code Generator

Some time ago, I started a hobby project for generating data access layer code for SQL Compact Edition databases. I managed to get as far as creating a custom tool and generating a code behind data access layer file (currently only in C#) for every table in the database.

I’m having a bit of a hard time finding the time to work on the project so I published it on CodePlex. If you’re interested in the source code or if you want to contribute to the project then check it out at http://sqlcecodegen.codeplex.com/

SqlCeEngineEx – Extending the SqlCeEngine class

I use System.Data.SqlServer.SqlCeEngine() quite a lot in all my projects. I normally create the database on the fly when the application is launched for the first time and then I populate the initial data via a web service.

I often check if database objects exist before I create them. You can do this by querying the INFORMATION_SCHEMA views. I created a helper class called SqlCeEngineEx that contains the following methods for querying the INFORMATION_SCHEMA:

1) bool DoesTableExist(string table) – Checks if a table exists in the database
2) string[] GetTables() – Returns a string array of all the tables in the database
3) string[] GetTableConstraints(string table) – Returns a string array of all the constraints for a table
4) string[] GetTableConstraints() – Returns a string array of all the constraints in the database

And here is the full code:

public class SqlCeEngineEx : IDisposable
{
  private SqlCeEngine engine;

  public SqlCeEngineEx()
  {
   engine = new SqlCeEngine();
  }

  public SqlCeEngineEx(string connectionString)
  {
   engine = new SqlCeEngine(connectionString);
  }

  public bool DoesTableExist(string tablename)
  {
   bool result = false;

   using (SqlCeConnection conn = new SqlCeConnection(LocalConnectionString)) {
    conn.Open();
    using (SqlCeCommand cmd = conn.CreateCommand()) {
     cmd.CommandText =
      @”SELECT COUNT(TABLE_NAME)
       FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_NAME=@Name”;
     cmd.Parameters.AddWithValue(“@Name”, tablename);
     result = Convert.ToBoolean(cmd.ExecuteScalar());
    }
   }

   return result;
  }

  private string[] PopulateStringList(SqlCeCommand cmd)
  {
   List list = new List();

   using (SqlCeDataReader reader = cmd.ExecuteReader()) {
    while (reader.Read()) {
     list.Add(reader.GetString(0));
    }
   }

   return list.ToArray();
  }

  public string[] GetTables()
  {
   string[] tables;

   using (SqlCeConnection conn = new SqlCeConnection(LocalConnectionString)) {
    conn.Open();
    using (SqlCeCommand cmd = conn.CreateCommand()) {
     cmd.CommandText = “SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES”;
     tables = PopulateStringList(cmd);
    }
   }

   return tables;
  }

  public string[] GetTableConstraints()
  {
   string[] constraints;

   using (SqlCeConnection conn = new SqlCeConnection(LocalConnectionString)) {
    conn.Open();
    using (SqlCeCommand cmd = conn.CreateCommand()) {
     cmd.CommandText = “SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS”;
     constraints = PopulateStringList(cmd);
    }
   }

   return constraints;
  }

  public string[] GetTableConstraints(string tablename)
  {
   string[] constraints;

   using (SqlCeConnection conn = new SqlCeConnection(LocalConnectionString)) {
    conn.Open();
    using (SqlCeCommand cmd = conn.CreateCommand()) {
     cmd.CommandText =
      @”SELECT CONSTRAINT_NAME
       FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
       WHERE TABLE_NAME=@Name”;
     cmd.Parameters.AddWithValue(“@Name”, tablename);
     constraints = PopulateStringList(cmd);
    }
   }

   return constraints;
  }

  public string LocalConnectionString
  {
   get { return engine.LocalConnectionString; }
   set { engine.LocalConnectionString = value; }
  }

  public void Compact()
  {
   engine.Compact(null);
  }

  public void Compact(string connectionString)
  {
   engine.Compact(connectionString);
  }

  public void CreateDatabase()
  {
   engine.CreateDatabase();
  }

  public void Repair(string connectionString, RepairOption options)
  {
   engine.Repair(connectionString, options);
  }

  public void Shrink()
  {
   engine.Shrink();
  }

  public bool Verify()
  {
   return engine.Verify();
  }

  public void Dispose()
  {
   engine.Dispose();
   engine = null;
  }
}