Hi Everyone,

Well, it’s nearly the end of the year 2011, and hasn’t it been a doozy?  I don’t know about you, but my year has been haywire, to say the least.  I started the year living in China, moved back to Australia, set up in Sydney and then finished the year in Canberra (the capital of Australia).

With all that going on, you’d think I’d be hard pressed to find time to write anything substantiative.  Of course, you’d be right.  Therefore, I’m aiming to write at least one more article of value before the year is out.  Since the bulk of the work I did this month was BizTalk related, I’m going to write a little bit about some problems I had to solve with BizTalk 2010.

I realise that I had previous written about a few other topics I wanted to cover off.. and I will.. but for now, BizTalk is the most convenient topic, so please bear with me Smile

There’s also quite a lot to look forward to in 2012.  For one thing, we’re expecting the next version of SQL Server (codename Denali) to drop – that should be a biggie – and there are the usual soft spots (WCF, Web Services, IIS) and so on.

Hopefully, you’ll see my BizTalk article before the new year, but in the meantime.. thanks for reading this year and all the best for 2012!

/R

 

Continuing on from Part 1, our objective in this article is to create a new Visual Studio solution, and then populate it with some existing projects.  To facilitate this, it would be beneficial if you had a few projects already created.  For this example, I have defined the following structure:

C:\UnitTesting – Root Folder

C:\UnitTesting\TestSolution – An existing folder which contains subdirectories which hold project files.

The sample structure is as follows:

image

So basically, there are three projects and two reference each other.  Our goal is to create a new solution file in the root directory which is then populated with references to the existing project files.

Please bear in mind that this is all demo code, and lacks the normal hardening and checks which production code should have.  Please use wisely, as there are no refunds.. ha!

I’ve reworked the previous example to be a little more robust, as follows:

  1. Encapsulated the functionality to create a new blank solution file (a method called CreateSolution())
  2. Second, I’ve added a new function called AddProjects which will take a path, plus details about the target solution file.

I’ve moved the DTE2 object to be a static object, and refactored CreateSolution thus:

private static DTE2 _dte2 = (DTE2)Microsoft.VisualBasic.Interaction.CreateObject("VisualStudio.DTE.10.0", "");

public static void CreateSolution(string path, string solutionName)
{
    Solution4 solutionObject = (Solution4)_dte2.Solution;
    solutionObject.Create(path, solutionName);
    solutionObject.Close(true);
}

Unit testing for this was easy enough:

[TestMethod]
public void CreateEmptySolution()
{
    SolutionHelper.CreateSolution(@"C:\UnitTesting\", "UnitTestSolution");
}

[TestMethod]
public void AddProjects()
{
    File.Delete(@"C:\UnitTesting\UnitTestSolution.sln");
    SolutionHelper.AddProjects(@"C:\UnitTesting\UnitTestSolution.sln", @"C:\UnitTesting\TestSolution", "*.*proj");
}

As you can see, all pretty straightforward.  So reviewing our CreateSolution function, it now just takes a path and solution name (omit the “.sln” extension) and creates the empty solution.

The next step is to iterate and find projects, and finally – add them to a solution file.  I’ve added some logic which creates the target Solution File if it does not exist.

The AddProjects function is pretty easy to understand:

public static void AddProjects(string solutionFile, string projectPath, string projectWildCardMatch)
{
    if (!File.Exists(solutionFile))
    {
        CreateSolution(Path.GetDirectoryName(solutionFile), Path.GetFileName(solutionFile));
    }

    Solution4 solutionObject = (Solution4)_dte2.Solution;
    solutionObject.Open(solutionFile);

    var fileNames = Directory.GetFiles(projectPath, projectWildCardMatch, SearchOption.AllDirectories);

    foreach (string projectFile in fileNames)
    {
        solutionObject.AddFromFile(projectFile, false);
    }

    solutionObject.Close(true);
}

Hopefully you can see how easy it is to add projects to the solution.  The boolean value in the call Solution.AddFromFile is important, as it elects to use the currently loaded solution.  If omitted, it is either attached to the project’s original solution (in my experience).  Make sure you save the solution when closing it.

If we open the generated solution file in Visual Studio 2010, you’ll see that not only are the projects added, but they also show the references:

image

Here is a complete copy of the source file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EnvDTE80;
using EnvDTE100;
using System.IO;
using EnvDTE;
using System.Diagnostics;

namespace VSAutomation.Toolkit
{
    public static class SolutionHelper
    {
        private static DTE2 _dte2 = (DTE2)Microsoft.VisualBasic.Interaction.CreateObject("VisualStudio.DTE.10.0", "");

        public static void CreateSolution(string path, string solutionName)
        {
            Solution4 solutionObject = (Solution4)_dte2.Solution;
            solutionObject.Create(path, solutionName);
            solutionObject.Close(true);
        }

        public static void AddProjects(string solutionFile, string projectPath)
        {
            if (!File.Exists(solutionFile))
            {
                CreateSolution(Path.GetDirectoryName(solutionFile), Path.GetFileName(solutionFile));
            }

            Solution4 solutionObject = (Solution4)_dte2.Solution;
            solutionObject.Open(solutionFile);

            var fileNames = Directory.GetFiles(projectPath, "*.*proj", SearchOption.AllDirectories);

            foreach (string projectFile in fileNames)
            {
                solutionObject.AddFromFile(projectFile, false);
            }

            solutionObject.Close(true);
        }
    }
}
 

Hi there and happy new year.  2011 promises to be quite an interesting year, and I hope that I can continue to contribute here at Sanders Technology.  To kick off the new year, I decided to revisit the Windows Workflow article I started late last year.

A little while ago I wrote a post at entitled “A quick and dirty Rules Engine using Windows Workflow (Part 1)” which has evidently been fairly popular.  Unfortunately, it seems that I forgot to follow it up with a part 2!  Now, welcoming in the new year, I’m putting together the second part.

Honestly though, folks, this could easily be a multi part mini project, because the uses of this Windows Workflow Foundation (WF) rules engine are immense!

I’ve managed to extend the scope of the code displayed in part 1 to include some dummy data items and I’ve crafted some more reusable and general purpose code (for example purposes), but you really ought to be able to see for yourselves how powerful and multi-purpose this really is.

I was going to write a quick and dirty WinForms UI, but I ended up ditching it in favour of a bunch of unit tests instead.  You really should be able to see the potential here, I don’t want to spoil the magic by adding an inept user interface.

Let’s take a look at the sample solution.  I’ve added some terribly (and perhaps insultingly) simple “objects” which, of course, you would substitute for your own DTOs/Entities/BusinessObjects.  It’s a basic class with some public properties, nothing terribly complex (it’s a demo after all).  You can see it uses an Enum just for fun on one of the properties.  I’ve also included a screenshot of the Solution structure – nothing too scary here.

imageimage

The Class View for the “BusinessObjects” | The Solution Structure

Basically the entire solution consists of two class libraries and a Unit Test project.  I’m trying to keep this very simple.  You could plug a WinForms UI or a website or a WCF Web Service Application underneath this very easily!

The main fun is in the “RuleManager” class, which is basically just a wrapper for the main WF workflow engine parts.  I’ve put in an extremely vanilla implementation which allows a few interesting parts of functionality.  I think if you use your imagination, you’ll be able to come up with some much more interesting ways to play with the options.

So why don’t we have a look at the RuleManager class?  It is defined to take a generic type, so you can work upon different source object types.

For the purpose of this post, we have just the one main object defined “Employee”.  It also doesn’t so anything to ensure the rules loaded are explicit for the data type – I’ll do an expanded implementation later to show how we can account for this.

My sincere apologies for the crappiness of the format of the posted code!  I’m having a bit of a fight with my copy of Live Writer and the plugins for inserting code snippets are not working very well with the site layout theme.  I’ll try and get it looking right.. soon.

#region Using Directives
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Workflow.Activities.Rules.Design;
using System.Workflow.Activities.Rules;
using System.Windows.Forms;
using System.Workflow.ComponentModel.Serialization;
using System.Xml;
using System.IO;
using BusinessObjects;
using System.Collections.ObjectModel;
#endregion

namespace WorkFlowProvider
{
    /// Implements a wrapper around the Windows Workflow Foundation Rules Engine
    /// A Data Object type to process
    public static class RulesManager<T> where T : new()
    {
        #region Rules Editor Support

        /// Launch the Rules Form to create a new rule
        public static RuleSet LaunchNewRulesDialog(string ruleName, string outputPath)
        {
            return LaunchRulesDialog(null, ruleName, outputPath);
        }

        /// Launch the Rules Editor with an existing rule (for editing),
        /// or to create a new rule (pass NULL to create a new rule)
        ///
The rule name (for the file name)
        ///
The path to save rules to
        /// A rule (if one is saved/edited)
        public static RuleSet LaunchRulesDialog(RuleSet ruleSet, string ruleName, string outputPath)
        {
            // You could pass in an existing ruleset object for editing if you 


            // wanted to, we're creating a new rule, so it's set to null
            RuleSetDialog ruleSetDialog = new RuleSetDialog(typeof(T), null, ruleSet);

            if (ruleSetDialog.ShowDialog() == DialogResult.OK)
            {
                // grab the ruleset
                ruleSet = ruleSetDialog.RuleSet;

                // We're going to serialize it to disk so it can be reloaded 


                WorkflowMarkupSerializer serializer = new WorkflowMarkupSerializer();

                string fileName = String.Format("{0}.rules", ruleName);
                string fullName = Path.Combine(outputPath, fileName);

                if (File.Exists(fullName))
                {
                    File.Delete(fullName); //delete existing rule
                }

                using (XmlWriter rulesWriter = XmlWriter.Create(fullName))
                {
                    serializer.Serialize(rulesWriter, ruleSet);
                    rulesWriter.Close();
                }
            }
            return ruleSet;
        }

        #endregion

        #region Rule Processing

        /// Applies a set of rules to a specified data object
        public static T ProcessRules(T objectToProcess, ReadOnlyCollection rules)
        {
            RuleValidation validation = new RuleValidation(typeof(T), null);
            RuleExecution execution = new RuleExecution(validation, objectToProcess);

            foreach (RuleSet rule in rules)
            {
                rule.Execute(execution);
            }

            return objectToProcess;
        }

        /// Execute a single rule on a single data object
        public static T ProcessRule(T objectToProcess, RuleSet rule)
        {
            RuleValidation validation = new RuleValidation(typeof(T), null);
            RuleExecution execution = new RuleExecution(validation, objectToProcess);
            rule.Execute(execution);
            return objectToProcess;
        }

        #endregion

        #region Rules Management

        /// Loads a single rule given a path and file name
        public static RuleSet LoadRule(string rulesLocation, string fileName)
        {
            RuleSet ruleSet = null;

            // Deserialize from a .rules file.
            using (XmlTextReader rulesReader = new XmlTextReader(Path.Combine(rulesLocation, fileName)))
            {
                WorkflowMarkupSerializer serializer = new WorkflowMarkupSerializer();
                ruleSet = (RuleSet)serializer.Deserialize(rulesReader);
            }

            return ruleSet;
        }

        /// Loads a set of rules from disk
        public static ReadOnlyCollection LoadRules(string rulesLocation)
        {
            RuleSet ruleSet = null;
            List rules = new List();

            foreach (string fileName in Directory.GetFiles(rulesLocation, "*.rules"))
            {
                // Deserialize from a .rules file.
                using (XmlTextReader rulesReader = new XmlTextReader(fileName))
                {
                    WorkflowMarkupSerializer serializer = new WorkflowMarkupSerializer();
                    ruleSet = (RuleSet)serializer.Deserialize(rulesReader);
                    rules.Add(ruleSet);
                    rulesReader.Close();
                }
            }
            return rules.AsReadOnly();
        }

        #endregion
    }
}

This one class pretty much gives you all you need to create, load and save rules.  It’s a bit basic at this point in time, I will try to create a more robust and tolerant class in subsequent posts on this topic.  For now though, I think it adequately demonstrates the sort of functionality which can be gleaned from the Rules Engine.

You can create or edit a rule by using the LaunchNewRulesDialog or LaunchRulesDialog methods with minimal user input.  I’ve written a very basic Unit Test which proves how efficient this can be, but I’m sure you’ll be able to have some fun with it.

Next up, there are some functions to load existing rule files from disk, the aptly named LoadRule and LoadRules methods.  They are pretty self explanatory, I don’t think we need to go into too much detail about the loading of rules files.

Finally, there are some functions which can be called to execute rules against data objects.  At this stage I’m supporting the execution of a single rule against a single data object, or a collection of rules against a single data object.  Obviously you could easily expand upon this.  You may wish to consider a multi-threaded approach, I may be persuaded to implement a more robust solution which allows for concurrent multiple item/multiple rule processing if you leave a comment for me.

Finally, here’s the Unit Test which allows you to create a new rule and apply it to the test data defined in the test:

[TestMethod]
public void CreateNewRule()
{
    Employee testEmployee = new Employee();
    testEmployee.FirstName = "Joe";
    testEmployee.Surname = "Smith";
    testEmployee.Location = StateEnum.ACT;
    testEmployee.Manager = null;
    testEmployee.DateHired = DateTime.Now.AddYears(-1);
    testEmployee.EmployeeNumber = 99;

    string ruleName = String.Format("{0}UnitTestRule", DateTime.Now.Millisecond);
    string path = Assembly.GetExecutingAssembly().Location.Replace(Assembly.GetExecutingAssembly().ManifestModule.Name, String.Empty);

    RuleSet newRule = RulesManager.LaunchNewRulesDialog(ruleName, path);
    testEmployee = RulesManager.ProcessRule(testEmployee, newRule);

    Trace.WriteLine(testEmployee.FirstName);
    Trace.WriteLine(testEmployee.Surname);
    Trace.WriteLine(testEmployee.DateHired);
}

So, in this post we’ve had a look at a very basic solution structure which demonstrates a reusable rules design.  At the moment it is as close to useless as a demo usually starts off looking like.  I’m only getting started, once you are familiar with the ‘RulesManager’ wrapper concept, we’ll be ready to expand upon it significantly.

This is part 2 of a multi-part series.  I’ll be expanding upon the concepts shown here in subsequent posts.

Check back soon!

Solution Files

 

Hi Everyone,

Well, I said I’d do it, and I will be – I’ve spent several hours putting together a sample DB and solution which demonstrates how to support Entity Framework (v4) POCO objects serialized in and out of WCF Services.

In a word, it’s going to be a lengthy article – maybe even several articles – as there are many things to address.  I was stuck on the last part of the scenario – sending objects back to be updated, but in the last 30 minutes I remembered what had to be done.

The sample solution will contain a test DB schema, a WCF Service project, a test client project (a console application) and some additional projects (class libraries) as well as an Entity Framework (v4) EDMX model which has been converted into POCO objects using the POCO Template Generator which you can install via Visual Studio 2010.

So now that the project is working in an end-to-end scenario, all I have to do is write up notes, capture some screenshots and also make notes of potential pitfalls and errors.  This is tricky stuff, but it is quite possible to accomplish.  Stay tuned, as the first post will be coming out this week!  If you want to be prepared in advance, I strongly suggest you have a play at generating POCO objects for your existing Entity Framework (v4) model.

Here’s a quick sample of things to come..

DB-Schema EDMX

  • This is a screenshot from SQL Management Studio’s SQL Diagrams, showing the raw schema
  • Next we have the EDMX (Entity Framework Entity Model) in all its glory

Poco-1 Poco-2

  • A screenshot of how to access the Code Generation template options from the EDMX designer
  • Next, we have a screenshot of the Online Templates – note the ADO.Net C# POCO Entity Generator is the first item

/R

 

There’s going to be more to this in coming posts, but I thought I’d add all the articles I’ve been reading ahead of schedule.  The long and short of the story is this –

I’ve managed to implement round trip CRUD operations using POCO (Plain Old CLR Objects) using a WCF Service & Client in conjunction with the ADO Entity Framework v4 (and Visual Studio 2010 RC).

In doing so, I’ve implemented all the “fun stuff” (plumbing, configuration, attributes, etc) but I’m currently investigating the options for supporting non-lazy loading queries which return a partial object graph.  Part of the problem I’m facing is with the POCO Proxies (which are unknown types at runtime) though I hope to have a solution soon.

Enjoy the reading, or please be patient, and I’ll write a post going into more detail soon!

POCO Templates for Entity Framework v4 (applies to RC as well):

http://blogs.rev-net.com/ddewinter/2010/01/25/poco-templates-for-entity-framework-v4-beta-2-released/

How to set up POCO Entities using the template:

http://blogs.msdn.com/adonet/pages/walkthrough-poco-template-for-the-entity-framework.aspx

ADO Team – Creating and Serializing POCO Proxies:

http://blogs.msdn.com/adonet/archive/2009/12/22/poco-proxies-part-1.aspx
http://blogs.msdn.com/adonet/archive/2010/01/05/poco-proxies-part-2-serializing-poco-proxies.aspx

MSDN – Serializing POCO Proxies:

http://msdn.microsoft.com/en-us/library/ee705457%28VS.100%29.aspx

Exposing POCO Entities via WCF (forum):

http://social.msdn.microsoft.com/Forums/en/adonetefx/thread/1c37447a-e303-4947-a3ee-d2e6592aac0a

Debugging/Trace tips and tricks:

If you find yourself with the dreaded “Object graph for type ‘X.Y.Z’ contains cycles and cannot be serialized if reference tracking is disabled error”
then check out this outstanding article:  http://chabster.blogspot.com/2008/02/wcf-cyclic-references-support.html

 

This is part of a series of entries written about Microsoft’s new SQL Azure database service and the Entity Framework v4.

Following on from my previous posts (check them out before continuing) – this article assumes you have followed steps outlined in the  previous posts to create various models and accounts etc.

Continuing along..

Our next step is to create a Dynamic Data website.  If you haven’t come across this yet, it’s most likely because you haven’t been using Visual Studio 2010 or the .Net Framework 4.0.  Recently introduced and compatible with both LINQ-to-SQL and the Entity Framework, this nice site template makes use of the dynamic nature of both LINQ-to-SQL [.dbml] (SqlMetal) and Entity Framework [.edmx] data models.

Continuing within the solution we created in Part 3, we shall now add a Dynamic Data website to our solution.  Open the solution in Visual Studio 2010 Beta 2 and then Right click the solution, “Add –> New Project..”.

Please note: this portion of the solution will also work in conjunction with any other database provider supported by the Entity Framework, not just SQL Azure.  To use a Dynamic Data site all you need is a LINQ-To-SQL or Entity Framework Data Context!  For details, read on..

image

I’ve called the new project “SQLAzure.Application.Web” and it sits in a subfolder off the root folder level for the solution.  Once you click the OK button, you’ll be presented with the Global.asax.cs (or .vb) which, at first, I found a little unusual.  We’ll explore why shortly.

First things, let’s add a project reference to our DataAccess project.  Right click the References solution folder and add the reference.  You might notice that the project already has the appropriate references for the Entity Data objects.  At this point if you compile, it will build without any errors (and if not, you are off to a bad start indeed!).

Configuration Bliss

There are some configuration options which will need to be set.  First, double click on the web.config and copy your <connectionString> values from the app.config located in the DataAccess project.

image

Once you’ve updated the Web.Config, save and close it.  You should now be looking at the aforementioned Global.asax.cs (or vb).  To get us up and running, with the very minimum of configuration (and hassle), we’ll keep the default settings (using the ASP Development Server a.k.a Cassini).  Right click the Web project in the Solution Explorer and “Set as StartUp project”.

Web Scaffolding and other treats

Next, in the Global.asax.cs (or .vb) in the method “RegisterRoutes” uncomment all the code lines, remembering to substitute the value (line 31) “YourDataContextType” with the name of the actual data model context (in our case, SqlDataModelContainer) and you’ll also need to add “using SQLAzure.Application.DataAccess;” at the top of the file.

In the first line, change { ScaffoldAllTables = false }); to { ScaffoldAllTables = true });
We can always customize the data model later to only scaffold specific tables (if we want to).

Once this is all done, you should be able to cleanly compile the project/solution.  Assuming you’ve followed all the steps I’ve outlined, your Global.asax.cs should look like the following:

image

Compile and run the solution (without debugging).  You should notice the ASP Development Server load into the System Tray, and then your default web browser should open and browse to your nice new site.  If you get the following error message:

Server Error in ‘/’ Application.


There are no accessible tables. Make sure that at least one data model is registered in Global.asax and scaffolding is enabled or implement custom pages.

It means you haven’t changed the value on Line 31 from false to true:

image

You *should*, assuming everything has been correctly set, see the following in your web browser, a list of tables in your SQL Azure database!  If you click on the “Albums” link, you should drill into a paginated view of the first 15 records in the Albums table in your nice SQL Azure database in the cloud..

Prepare to be amazed!

image image

Notice the combo boxes in the top left hand corner?  Yes, the site has automatically created those so you can filter the results according to foreign keys in the entiry relationship model – very nice.  Go ahead, select an artist.  I’ve selected “Bob Dylan”:

image

…but wait, there’s more..

That was pretty effortless, wasn’t it?  You want more functionality?  Of course – click on the “Edit” button for the first result.  You’ll be treated to this “details view” of the record:

image

You’ll notice, of course, that again the site has detected all the relationships (including many-to-many) and created combo boxes/checkboxes according to the cardinality of the relationships.  You may edit any of these settings and hit the “Update” button – you are now updating your data in the cloud!

Another nice touch is that you can navigate the relationships (hyperlinked) as well.  All the tables are inter-related (where applicable).  I’ll leave you now to play with your shiny new website, backed with a SQL Azure data store.

The big picture

This was, by far, the easiest website-to-database project I’ve ever wired up, period.  The functionality is uncanny for an out-of-the-box template driven site, and what’s more, if you hook it up to a Server instance (as opposed to SQL Azure) you can run SQL Profiler and see that the queries (T-SQL) themselves aren’t terrible, either.

This is amazing value – foreign key filters, paginated data views, complete with full edit/delete functionality.

However, it also has some additional work which should be addressed in any decent implementation, for example, security and brevity (reducing the footprint of the tables).  We could also do with some optimization and customization!  For example, the screen (1) below could obviously be truncated as the results run right off the screen, and clearly look better in the “Details” view (2).

image image

There are many more things which a “production” quality implementation would require.  I may (or may not) go into details in future posts, but off the top of my head you would need to address any of the following:

- Move from Cassini to IIS (should be done early for serious multi-access websites)
- Add a custom App Pool and Identity
- Strong name the assemblies
- Edit all the templates with a scheme
- Restrictions/reductions in the data displayed in “grid views”
- Optimization of some T-SQL to suit the data model design
- Implementing a security model to restrict read/write/deletion of data
- Auditing changes
- Create an automated build and deploy script

However, this is a great start and would probably suit a number of “intranet” style applications.

Looking ahead

In my next entry, we’ll look at some customizations as well as the technology underpinning this great functionality.  I do hope you are enjoying this series of entries, please leave comments!

 

Happy Australia Day to you!

Following on from my previous posts (check them out before continuing) – this article assumes you have followed steps outlined in the  previous posts to create various models and accounts etc.

Now we should have a working data model which has been created in your SQL Azure (Cloud) database.  Taking lessons learned from Part 2, we’re going to create an Entity Framework (v4) model of the SQL Azure tables using Visual Studio 2010 Beta 2.

This entry is sort of a rehash of the first and second entries, but the outcome should provide you with a fully functioning Entity Framework (v4) data model, and a Unit Test to prove it works!

Creating the Model

Fire up Visual Studio 2010 (Beta 2).  Next use “File –> Project..” and choose a new Class Library.  You can name the new Library whatever you like, but I will be calling it SQLAzure.Application.DataAccess as you can see in the following screen shot:

image

Now, the first thing I do when I create a new solution or project is to add a corresponding Unit Test project, so we can code out some Unit Tests as we implement new functionality.  Let’s do that now.

If you can’t see it, the Solution node in the Solution Explorer can be set to “Always Visible” by going to “Tools –> Options”, “Projects and Solutions” and check the checkbox next to “Always show solution”.

image image

Now, right click the “Solution” in Solution Explorer and select “Add New Project”.  You want to select a new Test Project (select the Test node under the tree list on the left hand side).  I’ve called my test project SQLAzure.Application.DataAccess.UnitTests.  Once added, right click on the “References” solution folder and select the DataAccess project.  You’ll also need to add a reference to System.Data.Entity.

Now, let’s delete those default files added to the projects (Class1.cs and UnitTest1.cs) which should leave us with a fairly vanilla solution, like so:

image

Now let’s configure that Data Model!  In the DataAccess project, right click and “Add –> New Item..”.  Select “Application Configuration File” then press “Add”.
Note that the file opens in the Text Editor after it has been added.  Copy and paste the following code between the <configuration> tags, then replace the items in bold with the proper values for your SQL Azure database:

<connectionStrings>
    <add name="SqlDataModelContainer"         connectionString="metadata=res://*/SqlDataModel.csdl|res://*/SqlDataModel.ssdl|res://*/SqlDataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=[servername].database.windows.net;Database=Vinyl;Uid=[user]@[servername];Pwd=[password];&quot;"
         providerName="System.Data.EntityClient" />
  </connectionStrings>

Next, on the DataAccess project, right click “Add –> New Item..” and select “Data” from the left hand tree.  Name the file “SqlDataModel.edmx” and click “Add”.  On the next screen, select “Empty Model” and then click “Finish”.

image

A blank model will open in the editor.  Now, assuming you’ve followed all these steps correctly, you should be able to right click the model and select “Update Model from Database…”.

You should find yourself looking at the “Update Wizard”’s “Choose Your Database Objects” window.  If you are not, it means your App.Config probably has incorrect values.  Refer to my first post for how to fix any issues you may encounter.

image image

Select all the Tables in this dialog and click “Finish”.  Barring any unexpected errors, you should now be looking at a fully articulated Entity Framework model of the schema we previously created and populated in SQL Azure.

image

Save the file and close it.

Verifying it all works!

Now, right click on the UnitTests project and select “Add –> New Test..” and select a “Unit Test”.  I typically add a “Smoke Test” set of tests which look for basic connectivity and authentication.  You can see from the following screen capture:

image

Next, remove all the template generated plumbing (you won’t need it for these basic tests, and rename the initial test to be now called “BasicDataAccessTest”, as follows:

image

Now, we’re going to need to add a config setting.  We’re going to add a link to the App.Config file you created under the DataAccess project.  Right click the UnitTests project and select “Add –> Existing Item..”.  Browse to the App.Config file under the DataAccess project (you may have to change the file filter to “All Files (*.*)” to see it).  Instead of clicking “Add”, click on the little downwards arrow on the Add button and select “Add As Link”.

image

Now, a reference to the DataAccess’ App.Config exists, thus no need to duplicate the connectionString settings across multiple projects, just one config for all.
Next, copy the following code into the body of the BasicDataAccessTest function (you will need to add “using System.Diagnostics;” at the top of the file): (apologies for a lack of colouring)

[TestMethod]
public void BasicDataAccessTest()
{
    using (SqlDataModelContainer e = new SqlDataModelContainer())
    {
        var albumsCount = (from a in e.Albums
                                   select a).Count();

Trace.WriteLine(String.Format("Total records found: {0}", albumsCount));
    }
}

Now, click on the Test menu and select “Windows –> Test View”.  You might need to click the little “Refresh” button to see the renamed Test Method.

imageimage

The proof is in the pudding

Assuming you’ve followed all the steps thus far, you should be able to successfully run this test now.  If you double click on the test result, you should see the following:

image

If you haven’t managed to get this test to pass, then you must have some problems with your configuration.  R
e-read this entry and you should manage to have it all wired up correctly.  Please drop me an email (rob.sanders [at] gmail.com) if you encounter any issues, I’m more than happy to try and help.

Conclusion

Now, assuming you have achieved success in wiring up this simple Unit Test, you have a working data model and working SQL Azure database instance.  Congratulations!

In my next article, I’ll show you some of the more complex queries we can perform, as well as begin to introduce you to some of the amazing functionality which is now available to use, because you have a working Entity Framework (v4) data model.

Check back soon for the next entry.

 

Following on from my previous posts (check them out before continuing) – this article assumes you have followed steps outlined in the  previous posts to create various models and accounts etc.

Introduction

So now that we’ve had a look at some of the basics around SQL Azure, Microsoft’s Database in the cloud, let us take a look at something more practical.  For our next example, I’m going to refer to a schema I designed and populated last year – my vinyl record collection.  You can have a look at the details of the schema in a previous entry – Project Sneak Peek.

The project was previously defined in this entry – New Project Announcement: SqlAzure Database and Azure Application (though we may skip the Azure application part, depending on time).

Given the schema is for SQL Server 2008, one of our first tasks will be to script the database and existing data, and then convert it to be conform with SQL Azure’s T-SQL restrictions.

y1pmSAI-g2FjfcovuNuDqJaNmfLmX6296F-F60WDvVnlxaHpuFlXK-OyVCg2uqdJSn2yjJgO67nwXVvSDY39SKwuQ[1]
Schema from the Previous Post

Generating Scripts with SQL Management Studio 2008 R2 (November 2009 CTP)

Let us take a quick look at the first step – scripting the existing database. 
You may skip this step and use the script I’ve attached (at the end of this post) if you wish, or you can download the pre-migration script and follow along. 
There’s nothing tricky here except that some of the options have changed.

Once you’ve created the Database on a SQL Server 2008 instance (using the script provided, at the end of this post) right click the Database and select “Generate Scripts..”.

image

When the “Generate and Publish Scripts” wizard loads, you’ll notice that the initial screen has from the 2008 RTM version.
This is a “cut down” version which heavily simplifies the process of generating a script.  Click “Next”.

From the radio buttons, change it from the default setting, select “Select specific database objects”, and then check the checkbox next to the Tables item in the tree view, below.  Note that the “Script Data” option is no longer here, it has been merged into “Types of data to script”.

image image

From the next screen – do not click “Next” just yet.  Click the “Advanced” button first.
Change the defaults to the values specified in the screen below.

image image

Once everything is set, you may click OK and then proceed to generate the script.
Once the script has been generated, you’ll need to make some changes to the T-SQL, as it will not be compatible with SQL Azure by default.

Making a SQL Azure Compatible Script

For this purpose, I recommend downloading the following tool from CodePlex – SQL Azure Migration Wizard v3.1.4.

The other option is to connect to S
QL Azure via SQL Management Studio R2 (Beta) and parse the script whilst connected to SQL Azure.  The tool is the better option IMHO.

If you use the tool, once it is running, select the “Analyze and Migrate” option, “TSQL File”.  Note, you could also migrate an existing schema (but where would be the fun in that?).

image
SQL Migration Assistant

Select the source file and click “Next”.  you’ll see a very busy window and a lot of T-SQL generated.  Once it is finished, you can select the contents and save to a new file or simply hit the save button.  Either way, you should now have a valid T-SQL syntax for SQL Azure.

Following the steps from the previous post, you can now connect to SQL Azure and run the script to generate the database.  A quick short list of steps follows:

  • Through the Azure Developer Portal, create a new Database
  • Open SQL Management Studio 2008 R2 and connect to the SQL Azure Database
  • Open the script with the active connection
  • Parse the script (to check for any issues)
  • Execute the script (ensure that the first statement is Use [Vinyl])
  • Verify no errors, and that all data is added

Stay tuned for the next article, as we’ll discover what we can do with our new Database in the Cloud.

Database Scripts

SQL 2008 T-SQL Script

SQL Azure T-SQL Script

Further Reading

Tools and References

 

This post is the first in a series of posts on this topic. 
The following is a summary of the various parts.

Introduction

Well, it’s been “out there” for a while, and now it is time I gave you a taste of how to combine some of the “Next Generation” data access technologies, today.  SQL Azure is Microsoft’s “Database in the Cloud” and represents a complete makeover (replacement) of the first attempt, SQL Data Services (SDS).  SDS is no longer, SQL Azure is the future.

One of the biggest knocks on SDS was the lack of a relational database.  Everything was done according to vertical data “silos” and cross-silo querying was painful (and almost impossible).  Unfortunately, administration was also tricky (well, worse) and it veered quite severely from the established SQL world which we know and love (or submit to).

Now, I’m going to walk you through the process of establishing a working Entity Framework (v4) model which runs against a SQL Azure database – a database in the “Cloud”.  This is not, by any means, a definitive guide.  In fact, I only just got this working myself this morning.  It relies on a few creative “workarounds”, which I will explain in due course.

This example is only intended as a “Proof of Concept” and has not exhaustively been tested. 
Attempt at your own peril (usual conditions and waivers apply)!

Prerequisites

What you will need prior to following along:

All of the above can be obtained for free, from the links above.  You may have to wait for a SQL Azure invitation or token.  My tokens were obtained a little while ago.
I’ll consider writing a separate article on how to set up SQL Azure, so if you are interested send me an email or leave a comment here.

Starting Off

This article assumes you are familiar with Visual Studio basics.

Once all the tools are installed, and you have configured SQL Azure for the first time (this involves setting up a master user account and password) log into the Administration console and create a database – just click “Create Database” and give it a name of “Sample”.

image

You can now get connection strings by clicking on the “Connection Strings” button.  Your server name is the key thing here.  Make a note of it.

Next, you may need to add your IP address to the Firewall settings.  To do this, click on the “FirewallSettings” tab, and add a new Rule using your IP address (your IP address is listed on the screen).

Now, fire up SQL Management Studio R2 (Beta).  On the initial dialog, enter the full name of your SQL Azure database, e.g. [server].database.windows.net and for the Username, use the one you configured initially (use SQL Authentication, not Windows Authentication), in the form of [user]@[servername] and, of course, your password, which you used to configure SQL Azure initially.

image

Now, you should be able to successfully connect to SQL Azure from SQL Management Studio.  If you can not connect, recheck your config values, it’s really just servername, username@servername and the correct password.  You may also need to add a firewall rule to allow your IP address to SQL Azure (see above).

Ensure you are using SQL Management Studio 2008 R2 Beta (November CTP) – earlier versions are not supported, so this will not work with the Server 2008 RTM version or prior.

Creating a Test Database

Please note: The following parts in this post introduce you to concepts which will be used in later articles.  You may safely skip the rest of this post if you are only interested in establishing the application and data model for the project.  Otherwise, keep reading and you’ll get a full understanding of how we intend to map a SQL Azure database to a working Entity Framework model.

image

Everything here is done via T-SQL script (unfortunately) so to create a new table we’ll have to create it.  Here’s a sample T-SQL script you can use.  We’ll create a table called “DailyStats” which has an Identity PK, a text field and a date field.

– =========================================
– Create table template SQL Azure Database
– =========================================

IF OBJECT_ID(‘DailyStats’, ‘U’) IS NOT NULL
  DROP TABLE DailyStats
GO

CREATE TABLE DailyStats
(
    StatId int IDENTITY NOT NULL,
    Data nvarchar(150) NULL,
    Created datetime
NULL
    CONSTRAINT PK_sample_table PRIMARY KEY (StatId)
)
GO

Next, we can populate the table with a row of test data, so we can do some simple selects later.

INSERT INTO [Sample].[dbo].[DailyStats]
           ([Data]
           ,[Created])
     VALUES
           (‘Blah Blah Blah’,
           GETDATE())
GO

Now, we’re pretty much done with SQL Azure for the time being.  Close SQL Management Studio, and fire up Visual Studio 2010 (Beta 2).

Creating the Entity Model

Create a new Console Application.  Once you are ready, in the Solution Explorer, add an “App.Config” (right click under the project in Solution Explorer, “Add” –> “New Item”, “Application Configuration File”) file to your solution.  Once you have done so, open it in the editor and add the following:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <add name="SampleEntities"         connectionString="metadata=res://*/SqlAzureModel.csdl|res://*/SqlAzureModel.ssdl|res://*/SqlAzureModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=[servername].database.windows.net;Database=Sample;Uid=[user]@[servername];Pwd=[password];&quot;"
         providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

Make sure you change the items in bold to their correct values.  For the .csdl, .ssdl and .msl entries (the first three values in bold, above after the name value), use the name of the Model (file name) you are planning to use, e.g. I am using the name “SqlAzureModel.edmx”.

Now, in Solution Explorer, right click and “Add” –> “Add New Item”, from the Tree, select “Data” and select “ADO.NET Entity Data Model”.  If you can’t find this, make sure your project is a .Net Framework 4.0 Project!  This can be found in the project’s “Application” tab, under “Target framework”.

Now, just create an Empty Data Model.  You can specify your own name for the model, but you will need to edit the app.config.  Once you have added your new Model, expand it in the Solution Explorer and double click the associated “.Designer.” file.

image

image

Make note of the value of the Constructor in the Contexts region (mine is as follows):

public SampleEntities() : base("name=SampleEntities", "SampleEntities")
{
    this.ContextOptions.LazyLoadingEnabled = true;
    OnContextCreated();
}

You need to ensure your ConnectionString in the App.Config has the same name as the text in bold (whatever your Context is called).  This is the “hack” workaround.

image

Now, assuming this is all configured properly, you can double click the .edmx file and you should see an empty model.  Right click on the model and select “Update Model from Database…”.

image

Assuming you have configured the connection string properly, you should see the following dialog (note we can see the table we created in SSMS, earlier).  If not, just ensure that the key in the App.Config file matches the Connection String expected by the .Designer. file (see above).

image

Once you see the above dialog, ensure you have selected the table, and we can update the model.

Once created and saved, open the Program.cs (or vb) file and add the following code:

static void Main(string[] args)
{
    using (SampleEntities e = new SampleEntities())
    {
        Console.Write(e.DailyStats.Count());
        foreach (DailyStat stat in e.DailyStats)
        {
            Console.Write(stat.Data);
        }
    }
}

You should be able to execute this code now, and it should print the value (1)  representing the one row in the DailyStats table and the value (blah blah blah) which is the value found in the single row under the “Data” column.

I do realise this is a very basic example, but it does introduce you to a number of fundamental ways to interact with your SQL Azure database.  You have no successfully used SQL Management Studio 2008 R2 and Visual Studio 2010 (Beta 2) to contact SQL Azure.  To top it all off, we have (hopefully) managed to use the Entity Framework to query real dat
a in the database.

This is an important first step.  In the next article we will delve deeper into the supported functionality.  For now, it may help to read ahead on the limitations of SQL Azure:

MSDN: Guidelines and Limitations (SQL Azure)

That’s it for now – you have now successfully (hopefully) connected to your SQL Azure database using the Entity Framework v4.  Check back for my next entry on this topic soon.

 

If you’ve been following the blog for a while now, you’ll know there are two main database schemas I tend to work with for Proof of Concept (PoC) applications, movies and records (vinyl LPs).

Last year I was building an Entity Framework based PoC using a schema made up of information about my LPs, I’ve decided to reuse this schema for my next Proof of Concept with SQL Azure.

The database is not exceptionally complex, but does offer the opportunity to design somewhat large complex cross queries, binary data and multiple relationships.  It’s also a great candidate to have an ORM applied to it (like LINQ to SQL, NHibernate, Entity Framework).

For this proof of concept, I’m (most likely) going to use the Entity Framework v4 which is part of the .Net Framework 4.0 Beta (you can find it with the existing Visual Studio 2010 Betas).  EF v4 also contains proper POCO (Plain Old CLR Objects) support and a HIGHLY improved SQL provider, many, many times better than v1.

As I develop the application (which is likely to be backed by an Azure application – TBD), I’ll continue to post entries here on the progress made as well as any tips, pointers and other interesting information.  Usually, I’d plan this out well in advance, but I’ve decided to chalk out a solution by exploring the capabilities of the early CTP.  It is hard to draw up an architecture without fully understanding the capabilities and limitations, so be it :)

Here are some of the goals I am aiming to complete (some of these are “the normal” requirements):

  • Port existing data model and data to SQLAzure
  • Web or Windows Client application
  • ORM solution using LINQ (EF v4 as planned)
  • Multiple search parameters and support for wild cards
  • Grid based “search results panel”, support sorting results, and supports pagination
  • “Item Views” for viewing records (drill down)
  • All data stored in “the cloud” (SQL Azure)
  • A maintenance plan/utility to manage and administer data (in the cloud)

Some stretch goals:

  • Security – support authentication and filtering data by roles
  • Failover support
  • Mirroring

This forms the high level basis of the PoC.  The main aim here is to establish the reliability, performance and maintainability of Azure apps and SQLAzure databases.  Heavy emphasis will be on maintenance of SQLAzure and the data access interfaces and security.

Stay tuned for more information, we start looking into this starting from next week.

Aussie Wine Guy


© 2012 Rob Sanders: Sanders Technology Suffusion theme by Sayontan Sinha
WordPress SEO fine-tune by Meta SEO Pack from Poradnik Webmastera