Generating a SQL Server CE database schema from a SQL Server database using Entity Framework

In a previous entry I described how to programmatically create (& destroy) a SQL CE dB for integration testing using NUnit.  Since getting that working I ran into a couple of other problems which I've more or less solved so I thought I'd write those up.  To begin with though this is a prequel post describing how to obtain the SQL script to create the SQL CE dB.

If you happen to be working exclusively with CE then you'll already have your schema file.  In my case I'm using SQLExpress and as this is experimental work I created my dB by hand.  However, using the EF it's pretty easy to obtain the schema and have the EF wizard generate the CE schema.  This is important as there are differences in the dialect of SQL used by SQL Express and SQL CE and its easier to have a tool handle those, though it doesn't do all of them.

The basic flow is to generate an EF model (EDMX) file from the existing SQL Express database and then use the 'Generate database from model' functionality.  It is at this point that the target SQL dB can be chosen, i.e. SQL Server, SQL Server CE or some others.

To create a model requires adding a 'New Item' of type 'ADO.Net Entity Data Model' to a VS project so first a new dummy project needs creating.  This is where it gets a little complicated as not any type of project will do.  I'm working with CE 4 and require a schema for that version of the dB (though creating one for 3.5 works but I like to things as close to ideal as possible).  Due to this constraint it is necessary to chose a Web type project as for some reason the VS2010 integration provided by EF only supports the generation of CE 4 dBs for Web projects.  If a simple C# Windows Console project is selected then you're limited to CE 3.5.  Thus the simplest project type is the 'ASP.Net Empty Web Application' as shown below.

Having done this, next add a new item of type ADO.Net Entity Data Model as below. NOTE: The project will have to reference the Entity Framework assemblies.  The easiest way to do this (& the one most people are probably using) is to use the NuGet package.

Then follow the wizard.

Selecting "Generate from database".

Choose your SQLExpress (or SQL Server) dB but uncheck the "Save entity connection settings in Web.Config as:" as we're converting to SQLCE so want to minimize anything related to other types of SQL Server.

Finally select the SQL elements you require.  In this example only the existing tables were selected.  As this is generating the EF model from an existing database no SQL file is generated just the model for which the diagram is shown, i.e.

The next phase is to generate the SQL from the model (which was generated from the hand crafted db) but to make sure the SQL that's generated is compliant with SQL CE.

To generate the schema right click and select "Generate model from database..."

This brings up the "Generate database" wizard which is very similar to the previously used "Entity Data Model" wizard used to create the model.  From here choose the "New Connection" option which pops up another set of dialogs.  On the first choose the type of data source as "Microsoft SQL Server Compact 4.0".

Clicking on continue then leads to the next dialog where you need to create a dB.

Ok-ing this leads back to the "Generate database wizard".

This time check the "Save entity connection settings in Web.Config" checkbox.  This information will be useful later (to be covered in a different post).  Clicking "Next" the SQL is generated and present in the wizard.

This can be copied & pasted directly from here or pressing "Finish" will save the SQL to the file indicated at the top of the dialog box.  This file is added to the project.  The following prompt will appear when "Finish" is pressed.

 

This doesn't really matter as this is a throw away project but having the updated schemas maybe useful so go with "Yes".

The SQL can now be used to configure an empty SQL CE 4.0 database.  The easiest way is to open the SQL file and right-click selecting the "Execute SQL" menu item.

This brings up the SQL Server dialog from which if "New Database" is selected an CE 4 one can be specified.

Having specified a location and pressed "Ok" the SQL script is executed.  As can be seen below this is not without errors.  However, this isn't anything to worry about as the errors are to do with dropping tables and indices that currently don't exist as it's a newly created dB.  Performing the same steps but missing out the creation of the dB file as it already exists sees the SQL script execute flawlessly.

The final picture shows the newly created database in VS2010's Server Explorer demonstrating that the tables were indeed created.

The basis for this post is my experimentation on using NUnit to programmatically test some dB based functionality.  If a single instance of a database suffices for all your tests and you can execute the SQL by hand as above and then you can follow these steps.  In may case I want to a fresh database per test so I need to automate the running of the SQL Script combined the with the creation and destruction of the underlying database.  The creation and deletion aspect were covered in a previous post but the next step will have to wait until a later one.

More computing humor

A few more computer/programming related jokes.  This time courtesy of the ACCU general mailing list:

• I heard a good concurrency joke recently, about 10,000 threads. It was a bit contentious... (Ewan Milne)

• A programmer walked into a Bar.  I say a Bar, it was actually a Foo. (Frances Buontempo)

• Two programmers are sitting in a bar discussing static and dynamic languages. The next day they get a visit from the RSPCA. When they ask what it's concerning, the RSPCA man says "We got a tip off that you were using ducks for typing". (Anthony Williams)

• An SEO expert walks into a bar, bars, pub, public house, Irish pub, drinks, beer…. (Anders Knatten) 
• Q: WHY DID THE HACKER GO TO THE BEACH? A: EASY ACCESS TO SHELLS (@yeahok)

Unit Testing when the success case is compilation failure (with C++)

There is an issue with the use of Window’s COM types being used with STL containers in versions of Visual Studio prior to 2010. The crux of the matter is that various COM types define the address-of operator and instead of returning the real address of the object they return a logical address. If the STL container re-arranges its contents this can lead to corruption as it can unwittingly manipulate an object at the incorrect address.

The prescribed solution to this problem is to not place the COM types directly into the STL container but instead wrap them with the ATL CAdapt class.  When adopting this approach there are two important aspects to consider:

• Firstly, is there a way to prevent incorrect usage of the containers?
• Secondly, assuming this is possible, is there a way to make sure it covers the various permutations of STL containers and COM types that are being used?

It turns out that the first issue is easily solved using template specialization.  The hard part though is creating  a set of unit tests that ensure specializations have been created for all permutations and that they are active.  This is made even more difficult by the fact a successful use of the specialization results in compilation failure.

This too can be solved by combing various template mechanisms and techniques: SFINAE & Template Meta-programming etc.  In the April issue of the ACCU Overload journal I have written article that describes this problem along with the solution.  Please follow the link below (and then jump to page 24 in the PDF if it doesn't open there automatically).

http://accu.org/var/uploads/journals/overload108.pdf#page=24

The complete source code the article is also available on GitHut: https://github.com/petebarber/TemplateArticleExamples

Using NUnit and Entity Framework DbContext to programmatically create SQL Server CE databases & specify the databse directory

NOTE: An update to the method used here is provided in this newer post: Integration Testing with NUnit and Entity Framework.

I won't go into the why but I wanted to write what probably amounts to an integration test of an Entity Framework model.  I decided to use NUnit for this.  Even though it's predominately for Unit Testing there's no reason why it can't be used for other forms of testing.  In fact I think the SpecFlow BDD framework eventually performs its tests using NUnit: It's not what you've got but what you do with it!

The application I'm writing uses SQL Express but for my tests I wanted to create & destroy a database frequently and locally so I opted to use SQL Server Compact Edition.  This is pretty easy to do with EF's DbContext: just create an instance of it, pass in either the name of the database or a Connection String, e.g.


[Test]
public void TestWithConnectionString()
{
  var db = new DbContext("Name=Foo");


  db.Database.Create();
}

which references the following Connection String in the .config file


<connectionStrings>
  <add name="Foo"
    providerName="System.Data.SqlServerCe.4.0"
    connectionString="Data Source=foo.sdf"/>
</connectionStrings>



As I'm going to be potentially creating many of these I wanted precise control over their name and location.  This meant I didn't really want to use a Connection String as this is more or less hard-coded in the .config file.

Instead I opted to create it programmatically.  By default DbContext expects to be using SQL Server in particular an instance of SQLExpress named .\SQLExpress.  This is easily changed by replacing the DefaultConnectionFactory with the factory class for SQL Server CE: SqlCeConnectionFactory.

The first parameter to the constructor is the type of the factory class which allows instances of different versions to be created. This is simply the providerName as per the Connection String.  The second parameter is the directory to create any new databases in.  The third parameter (unused in the example below) are options to append to the generated Connection String.  The code below will result in the creation of C:\UsersPete\TestDBs\bar.sdf assuming the directory exists and the user has appropriate permissions and bar.sdf does not already exist.


[Test]
public void TestWithFactory()
{
  Database.DefaultConnectionFactory = 
    new SqlCeConnectionFactory(
      "System.Data.SqlServerCe.4.0", 
      @"C:\Users\Pete\TestDBs\", "");


  var db = new DbContext("bar.sdf");

  db.Database.Create();
}

That's mainly it:  A simple way to programmatically create databases.  This code should really be moved to the SetUp method and a corresponding TearDown method added to delete the database; oh, and some tests!

I haven't got that far yet and the actual reason for writing the entry was for a short while despite specifying the directory for the databases, i.e. C:\Users\Pete\TestDBs they were being created in the current working directory.  This was because rather than just specifying the name of the database as the argument to DbContext I was passing the string "DataSource=bar.sdf" of which the name part appears to be an absolute path and thus override that specified in the SqlCeConnectionFactory.  As only a file name is present it uses the current working directory for the path element. If you've got the same issue (whether integration testing or not) this might save you a little time.

A C#/.NET Attributes Based Command Line Argument Parser

I've been meaning to add a blog entry for this for a while.  It's just a link to my latest Code Project article that went live last week.  It's a long piece describing a small library for handling the parsing of command line arguments in C# programs, either Console or GUI (there's a WPF example).

This is the first project I created using Test Driven Development so as well as the technical details of the library there's also a description of how I found TDD.

You can find it here: http://www.codeproject.com/KB/cs/NAttrArgs.aspx

Open Source C# Network File System (NFS) Server (for Windows)

Happy New Year!

Not a proper post but just a quick link to my latest Code Project article.  A few years ago I wrote an NFS Server (in C#).  I had reason to revisit this lately so I thought I might as well write it up as a Code Project article and Open Source it.  You can grab it from GitHub.

Links:

Article: http://www.codeproject.com/KB/IP/NFSServer.aspx
Source: https://github.com/petebarber/NFS

Obtaining the size of a C++ array using templates and other techniques (from C++11) - Part 2

Welcome back!  In Part 1 we saw a nifty technique to obtain the size of an array via function template argument deduction.  However, this ended with the problem of how to use this value at compile time, e.g. as the size of another array.  This proves hard in C++98 but C++11 changes things.

The simplest way to enable compile time use is to make use of the new constexpr keyword.  This tells the compiler that the function can be completely evaluated (to a constant value) at compile.  As such C++11 allows this function to be called at compile time though instead the evaluated constant value is used.  Adding this to the definition of GetNumberOfElements as below

template<typename T, size_t sizeOfArray>
constexpr size_t GetNmberOfElements(T (&)[sizeOfArray])
{
  return sizeOfArray;
}

now allows the following to compile

char a[100];
char b[GetNumberOfElements(a)];


Is this the best that we can do?  Using raw array types isn't that good.  Even though the size of the array is known and can now be discovered at compile time using it in std::for_each is somewhat burdensome as the length must be obtained in order to obtain the end iterator, i.e.

std::for_each(&a[0], &a[GetNumberOfElements(a)], SomeFunctor());

Due to the raw array not being an object it doesn't have an end method which means the code must rely on the programmer specifying the correct index for the end of an array or using the technique above.

This can be remedied by moving from raw array types to std::array (first made available in TR1), e.g.

std::array<char, 100> a;
std::for_each(std::begin(a), std::end(a), SomeFunctor());

(Note the use of the C+11 non-member versions begin and end functions)

The downside of using std::array is that even though it can be initialized when defined, e.g.

std::array<int, 4> a = { 0, 1, 2, 3 };

It effectively requires the length of the array specifying twice.  Firstly as the template parameter and then again implicitly by the number of elements in the initializer list.

Given the new initializer list feature of C++11 it could be expected that this would allow initialization without the length parameter.  However it seems this is not supported "out of the box" though there are some interesting techniques to allow this.

All is not lost though.  Taking a step back and returning to the raw array the size issue can be circumvented by use of C++11's range-for statement.

char a[100];
SomeFunctor sf;
for (auto x : a)
sf(x);

To backtrack once again, the use of the non-member versions of begin and end can also be used on raw arrays so the example above can also be written as:

char a[100];
std::for_each(std::begin(a), std::end(a), SomeFunctor());

Which if the purpose of the loop is to invoke a functor then std::for_each is more succinct than range-for as it doesn't require an explicit instance of the functor.

I suspect the same technique for finding the length of a naked array along with constexpr is used by the non-member version of end to obtain the length.  In fact one version of end is probably a function template overloaded to accept a raw array as its container parameter, e.g.

template<typename T, size_t>
constexpr T* example_begin(T (&array)[])
{
  return &array[0];
}


template<typename T, size_t sizeOfArray>
constexpr T* example_end(T (&array)[sizeOfArray])
{
  return &array[sizeOfArray];
}


char a[100];
std::for_each(example_begin(a), example_end(a), SomeFunctor());

However, any looping mechanism that requires both the start and end to be specified rather than just the container does allow for an error to occur by having begin and end of different containers specified whereas range-for eliminates this possibility!

In summary, it's possible to find the size of a raw array using function template deduction and using C++11 features that value can be used at compile time.  However, if the reason that the size needs obtaining is to obtain the end iterator then C++11 makes this redundant through the use of range-for and the non-member versions of begin and more accurately end.