Skip to content
Archive of posts filed under the Data category.

VS Live Orlando

I will be speaking at VS Live conference in Orlando, FL on 12/08/2011. I will be presenting the following two talks.

Working with Data on Windows Phone 7
I will talk about various approaches to work with data on Windows Phone 7. I will cover all major concepts, such as local storage, OData/WCF Data Services and custom WCF services. I will demonstrate how to retrieve data and save changes locally or to a remove web server. I will build demos for all technologies step-by-step.

You will learn:
• About options to persist the data on Windows Phone 7
• About pros and cons of each technologies
• Knowledge to build a Windows Phone application that publishes and/or consumes data

You can download the materials for this presentation here.

Using Code First (Code Only) Approach with Entity Framework
Session will include high level overview of Entity Framework and how various approaches to use it fit into application development lifecycle. Then I will build POCO classes that entity framework can use to create database as well as perform CRUD operations against the database. Various attributes that are included out of the box will be covered. This will include columns constraints, relationships, etc. I will show you how ASP.ENT MVC 3 can utilize entity framework code first metadata to build automatic validation of user input.

You will learn:
• How to build data access layer with Entity Framework Code First
• About validation approaches using POCO classes
• Fluent API and attribute based configuration option

I hope to see you there. 

Thanks.

Post to Twitter

Update to WinRT Database project

If you follow my posts, you noticed that I started up WinRT database project on CodePlex.  I just published as small update to it that now support data binding to list based controls in WinRT.  It took actually a significant amount of pain and research, including some posts on WinRT forums.

You can read my post here on details of data binding to list objects in WinRT.  I used the code I posted there in my WinRT database projects.  I also started working on support for thread safety when working with file asynchronously.  This is not an issue for you if you have a Save button, but my goal is to eventually support transparent save, where data is saved automatically when it changes.

Comments are welcomed.

Thanks.

Post to Twitter

Quick Start for WinRT Database

 

I have a CodePlex project for file based database for WinRT / Windows 8. You can check out this project at http://winrtdatabase.codeplex.com/

First step is to download the project and build it on your computer. Visit the download page for the latest recommended download. Unzip the source code on your machine, open it up in Visual Studio 11 (Developer preview) and compile to create a DLL.

Next, create new Windows Metro style application project using C# and add a reference to the DLL from step 1.  You will end up with a XAML / C# based project.

Next, in your View Model (or elsewhere in your application) check for database existence and create if it does not exist along with tables. See sample code below

public async void Initialise()
        {

            var exists = await Database.DoesDatabaseExistsAsync(DatabaseName);
            if (!exists)
            {
                _database = await Database.CreateDatabaseAsync(DatabaseName);
                _database.CreateTable<Person>();
                var table = await _database.Table<Person>();
                table.AddRange(new Person[]
                {
                    new Person()
                    {
                        PersonID = Guid.NewGuid(),
                        FirstName = "Sergey",
                        LastName = "Barskiy",
                        Age=19
                    },
                    new Person()
                    {
                        PersonID = Guid.NewGuid(),
                        FirstName = "Michelle",
                        LastName = "Barskiy",
                        Age=18
                    }
                });
                await _database.SaveAsync();
                People = table;
            }
            else
            {
                _database = await Database.OpenDatabaseAsync(DatabaseName, true);
                var table = await _database.Table<Person>();
                People = table;
            }
        }

In the sample above I am testing if database exists, then create it if it does not, Then I am adding a table to it based on POCO class Person. I am adding two people to the tables.  You can add a single instance by calling Add instead of AddRange.  For example, Person class would look like

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Windows.UI.Xaml.Data;

namespace WinRTDbQuickStart
{
    public class Person : BaseObject
    {
        private Guid personID;

        public Guid PersonID
        {
            get { return personID; }
            set { personID = value; OnPropertyChanged("PersonID"); }
        }

        private string firstName;

        public string FirstName
        {
            get { return firstName; }
            set { firstName = value; OnPropertyChanged("FirstName"); }
        }

        private string lastName;

        public string LastName
        {
            get { return lastName; }
            set { lastName = value; OnPropertyChanged("LastName"); }
        }

        private int age;

        public int Age
        {
            get { return age; }
            set { age = value; OnPropertyChanged("Age"); }
        }
       
       
    }
}

To remove, just issue remove command:

public void OnDelete(Person parameter)
      {
          if (parameter != null)
          {
              People.Remove(parameter);
              People.SaveAsync();
          }
      }

You can also remove and add a range of items based on condition.

People.RemoveRange((person) => { return (person.Salary >= 2); });

Make sure to call SaveAsync() on either database or a specific table to commit your changes.

You can also look in unit test project and quick start project that is distributed as part of the source code download for other API samples. There are a number of issue with the sample app due to differences between WinRT and .NET.  For example,ListBox is not listening to Observable Collection changes, instead there is IObservableVector<T> interface.  I am planning to implement those changes next.

Update 11/25/2011 – Issue with binding to ListBox has been fixed.  All tables are now bindable to list based controls.

Post to Twitter

VS Live Redmond 2011

As I mentioned before, I presented at VS Live conference in Redmond, WA yesterday, 10/20/2011.  I presented the following two talks

Working with Data on Windows Phone 7
I will talk about various approaches to work with data on Windows Phone 7.  I will cover all major concepts, such as local storage, OData/WCF Data Services and custom WCF services.  I will demonstrate how to retrieve data and save changes locally or to a remove web server.  I will build demos for all technologies step-by-step.

You will learn:
• About options to persist the data on Windows Phone 7
• About pros and cons of each technologies
• Knowledge to build a Windows Phone application that publishes and/or consumes data

You can download the materials for this presentation here.

Using Code First (Code Only) Approach with Entity Framework
Session will include high level overview of Entity Framework and how various approaches to use it fit into application development lifecycle.  Then I will build POCO classes that entity framework can use to create database as well as perform CRUD operations against the database.  Various attributes that are included out of the box will be covered.  This will include columns constraints, relationships, etc. I will show you how ASP.ENT MVC 3 can utilize entity framework code first metadata to build automatic validation of user input.
 
You will learn:
• How to build data access layer with Entity Framework Code First
• About validation approaches using POCO classes
• Fluent API and attribute based configuration option

You can download the materials for this presentation here.

Post to Twitter

Entity Framework Migrations

I recently posted on my intentions to create a migration solution for Entity Framework Code First using Red Gate tools.  The initial alpha version 0.9 is now live on CodePlex.  Here is how you would use the product. 

You have to obtain a license to Red Gate SQL Comparison SDK.  If you simply would like to try my solution, just get 14 days trial and check out what it does for you.  In my opinion, $700 dollars is not that much money for the functionality you get.  But of course, if you are giving your product away, that is a lot of cash.

Next you have to create some classes to use.  Your table classes are the same as in any other code first project.  You context however, now needs to inherit from ExtendedDbContext from my project.

    public class ProductsContext : ExtendedDbContext
    {
       
public ProductsContext(string connectionString)
            :
base
(connectionString)
        {
 
        }
 
       
public DbSet<Product> Products { get; set
; }
 
       
public DbSet<DbVersion> Version { get; set
; }
 
       
protected override void OnModelCreating(DbModelBuilder
modelBuilder)
        {
           
            modelBuilder.Conventions.Remove<
IncludeMetadataConvention
>();
           
base
.OnModelCreating(modelBuilder);
            
        }

 

There is one requirement, you must have a constructor that takes connection string.  You would typically have something like that anyway, so this is a minor adjustment to make.

Next you have to decide how you are going to use version comparison.  I have an interface that you must implement INewVersionProvider<TContext>.  I provide two of those out of the box, which should be enough for you to get started.  I have AssemblyBasedNewVersionProvider and ModelBasedNewVersionProvider.  Frist one stores version of the assembly that contains your DbContext and uses it for comparison.  To trigger migration you have to bump up a version of your assembly before running or deploying.  Second one uses the same mechanism as Entity Framework, creating a model hash based on actual context definition and its tables.  There is one requirement you have to use to support versioning of migrations.,  You have to include DbVersion table which is part of my framework in your context.  You can see that above in this blog in my context definition. 

Next you have to implement MigratingInitializer.  I am including base class, making this process a breeze. 


using EFUtil.Migrations.SqlServer;
using
EFUtil.Migrations;
 

namespace
EFUtil.Tests.Source
{
   
public class ProductInitializer : MigratingInitializer<ProductsContext
>
    {
       
public ProductInitializer(INewVersionProvider<ProductsContext
> versionProivider)
            :
base
(versionProivider)
        {
 
        }
 
       
protected override void BeforeMigration(ProductsContext
context)
        {
 
        }
 
       
protected override void AfterMigration(ProductsContext
context)
        {
 
        }
    }
}

 

As you can see, I provide two hooks, before and after that will allow you to massage the data before the migration or after, including adding seed data.  You can use either context or manual queries. 

using EFUtil.Migrations;
using
EFUtil.Migrations.SqlServer;
using
System.Data.Entity.Infrastructure;
 

namespace
EFUtil.TestApplication
{
   
public class ProductInitializer : MigratingInitializer<ProductsContext
>
    {
       
public ProductInitializer(INewVersionProvider<ProductsContext
> versionProivider)
            :
base
(versionProivider)
        {
 
        }
 
       
protected override void BeforeMigration(ProductsContext
context)
        {
           
var adapter = context as IObjectContextAdapter
;
           
if
(context.Database.Exists())
                adapter.ObjectContext.ExecuteStoreCommand(
"Update Products Set ProductNumber = ProductNumber + ’1′"
);
        }
 
       
protected override void AfterMigration(ProductsContext
context)
        {
           
var adapter = context as IObjectContextAdapter
;
            adapter.ObjectContext.ExecuteStoreCommand(
"Update Products Set ProductName = ProductNumber + ‘ name’"
);
        }
    }
}

 

In the example above I am firing SQL Queries, but I could have added rows to context’s tables directly and fired Save().

I am also including conventions, just like I promised.  You can read more about conventions in my posts on global conventions and attribute conventions.  I also created a follow up post here.  All the code to support conventions is now part of CodePlex project.  I felt it is necessary to formalize my efforts in order to make the functionality cleaner and include tests for it.

If you download source code, you will see unit test project.  Note: you have to run one test at a time because there is a timing issue related to dropping databases.  There is also a quick test bed project you could use to play with code.  it is called EFUtil.TestApplication.  You can just keep altering Project class and re-running the project to observe migrations in action.

This is initial version, and I already have plans to further develop the functionality.

Here is my road map for the project:

  • Publish beta releases once I have some feedback and a number of downloads without bugs reported.
  • I am planning to add
    • Index support
    • Default values support

Please let me know what you think about my efforts and provide some feedback.

Post to Twitter

Entity Framework Thoughts

I have been thinking for a little while about the future of Entity Framework, feature set on the horizon, and trying to ascertain where I would like to concentrate my next “free time” project.

If you have not been keeping up with Entity Framework, please read the last few posts on the Entity Framework design blog about the present and the future of the product.

The more I have been thinking the more I see a benefit in creating a complementary solution that would enhance features available in Entity Framework, and possibly gather some community input as to what features are most needed.  Just like a number of folks, I looked at the migrations feature outline that will likely be using existing database project API.  I worked with database projects for quite some time, and I am not a giant fan of the product.  I did submit a handful of suggested enhancements, but I am not sure now if any of them will see a light of day.  API that the project exposes is different from Visual Studio UI of course, and I am sure that API will be good to write migration code against.  On the other hand, I worked with Red Gate product called SQL Compare and related .NET SDK since about 2005 on and off.  When I used that product last, our company saved countless amounts of money by using Red Gate API to synchronize our product database with existing databases in the field.  You can read more about SQL Compare here and SDK here.  You can also buy both products as a bundle.  If you look at the price of SDK, it is about $700 per developer, and it includes 10 distribution licenses.  I encourage you to read more about licensing process and maybe even call Red Gate, if you decide to use the product.  There is also a competing product from APEX.  You can read more about that product here.

But back to Entity Framework.  I confess, I have been a fan of the product since 1.0 beta.  I used it on production projects, and I saved countless hours not having to code stored procedures and DAL layers.  I became quite excited when Code First came out on top of 4.0 version.  It offered very clean programming interface with a number of options to handle many use cases that developers might have.  It did lack a few features, namely migrations, pluggable conventions, indexes, and default values.  I have been thinking about how to address this, given that I am not a Microsoft employee.   After thinking about it for quite some time, I decided to start a CodePlex project in attempt to address these issue on my own.  I am going to rely on Red Gate based on my expedience with the product and the fact that the price is right in my opinion.

Stay tuned for the announcement.  I suspect that the initial alpha version that would include migration story will be out in a few weeks.  I am going to “productize” my blog posts on pluggable conventions and roll them into the same CodePlex project next.

I am welcoming any suggestions, including your thoughts on buying $700 product.

Thank you.

Post to Twitter

Update for WP7 Database

I have had a small number of requests to enhance the functionality of my Windows Phone 7 database project on CodePlex.  In light of Mango release for WP 7 that will contain SQL CE database, I think that my project will likely be used less in just a few months.  The reason being is that once the users update, the reason to use Isolated storage to persist data becomes less appealing.  I am not knocking on my own project, I am just recognizing the reality.  Having said that, the database will likely still be in use for a while, since we have to wait for the actual phone users to update the version, which cannot be done via 3G, you have to use Zune to update.  Because of that, I think, I decided to implement the most request feature, specifically named tables.  In other words, right now you can create a table in my database and give it a type of data to store, such as :

db.CreateTable<Person>();

 

What named tables allow you to do, is horizontally partition your data by allowing multi0ple tables to store the same type, such as:

string name2 = "p2";
Database db = Database.CreateDatabase("test"
);
db.CreateTable<
Person
>(name1);

 

So, the syntax pretty much remains the same, so anywhere you used to refer to db.Table<T>, you can now also use db.Table<T>(tableName).  This includes all functions, such as adding of rows and querying.

db.Table<Person>(name1).Add(NewRandomPerson());

 

Nothing else changes.  One thing to notice, that this is pretty deep change that required me to touch a lot of code and test legacy data to ensure it opens properly.  As always, I did my due diligence, as you can gather from the tiny number of reported issues in over a year of usage.  Nevertheless, I encourage everyone to thoroughly test your applications.  You can open up my test project and see how I tested legacy data by looking at the method NamedTablesLegacyTest in my test page in test project.  As a result, I am going to publish this release under alpha version until I get some feedback that is sufficient enough to warrant beta status.

I made another small change, making DoesTableExist method public.

Enjoy.

Thank you.

Post to Twitter

SQL CE in Mango–Updating the Schema

In my previous post I talked about general usage of SQL CE database in Mango.  In this example I am going to talk about keeping the database schema updated.

For example, I want to add a column to Person table from previous post.  I would like to add Notes column with the following definition:

        [Column(DbType = "NVarChar(300) NULL")]

        public string Notes { get; set; }

 

Now I am going to use DatabaseSchemaUpdater class to accomplish this.  First of all, I have to add using statement to my program in order to invoke extension method on my data context:

using Microsoft.Phone.Data.Linq;

 

Now, I am going to add some code after the database exists check and add the column as follows:

                DatabaseSchemaUpdater db = App.CurrentApp.DB.CreateDatabaseSchemaUpdater();

                if (db.DatabaseSchemaVersion == 0)

                {

                    db.AddColumn<Person>("Notes");

                    db.DatabaseSchemaVersion = 1;

                    db.Execute();

                }

 

Here is what I am doing here.  I am checking for DB version, which I am going to assume responsibility for updating from now on.  I am adding notes column called Notes to Person table.  Then I am bumping version number for subsequent updates.  Lastly, I am firing Execute method to commit my changes.  Pretty easy all-and-all.  I only wish that Entity Framework code first had similar functionality.

Post to Twitter

SQL CE on Windows Phone 7.1 (Mango)

In the past I have written about dealing with data on Windows Phone 7, including WCF Services, WCF Data Services and Isolated Storage.  One thing that was missing in the initial release is native RDBMS, such as SQL CE.  Luckily, Mango update that should be released later this year finally exposes SQL CE to developers.

Even I admit that Isolated Storage DB was a useful tool for me in absence if SQL CE.  That is why I wrote my project on CodePlex.  Having said that, I would likely not take on this task if SQL CE was available in Windows Phone 7.  As a good little developer, I wanted to spend some time and educate myself on how to use SQL CE in Mango.  This post describes my adventures on the subject.

First of all, you have download and install Mango (7.1) update.  Current update version is beta 2.  You can download it here.

Now, let’s create new project and select 7.1 as target framework.  Now, let’s get acquainted with Linq to SQL API for Windows Phone 7.  Yes, interestingly enough ORM framework that is available on the phone is L2S not Entity Framework.  I will not be commenting on this subject, let’s just look at what is available.

We are using code only approach with L2S, so we are going to define some tables in code.  I want to document some relationships, so I will use the following logical structure: Person with a Title and a number of Interests.  Let’s look at the simplest table – PersonTitle.

using System.Data.Linq.Mapping;

 

namespace MangoDataApp

{

    [Table]

    public class PersonTitle

    {

        [Column(DbType = "INT NOT NULL IDENTITY", IsDbGenerated = true,

            IsPrimaryKey = true, AutoSync = AutoSync.OnInsert)]

        public int PersonTitleID { get; set; }

 

        [Column(DbType = "NVarChar(30) NOT NULL")]

        public string Title { get; set; }

    }

}

 

Let’s look at the code in details.  You will notice that I use a couple of attributes to designate this class as Table and define columns.  I find Table attribute pretty easy.  Column attribute is a lot more annoying because I have to remember the column definition and type it as a magic string.  I do not like this, but what can one do?  I designate my primary key as Identity column and force to sync up the newly generated ID after the insert is committed.  So far so good.  Now I am going to take a closer look at defining an one-to-many relationship.  I will use Person with a Title for this.  Here is what my Person class looks like:

    [Table]

    public class Person

    {

        [Column(DbType = "INT NOT NULL IDENTITY", IsDbGenerated = true,

            IsPrimaryKey = true, AutoSync = AutoSync.OnInsert)]

        public int PersonID { get; set; }

 

        [Column(DbType = "NVarChar(30) NOT NULL")]

        public string FirstName { get; set; }

 

        [Column(DbType = "NVarChar(50) NOT NULL")]

        public string LastName { get; set; }

 

        [Column()]

        public int PersonTitleID { get; set; }

 

        private EntityRef<PersonTitle> personTitle;

        [Association(ThisKey = "PersonTitleID", OtherKey = "PersonTitleID",

            Storage = "personTitle")]

        public PersonTitle PersonTitle

        {

            get { return personTitle.Entity; }

            set { personTitle.Entity = value; }

        }

 

 

As you can see above, unlike simple API of Entity Framework Code First I have to jump through a few hoops.  One thing is that I have to create an explicit backing field of type EntityRef.  I am hinting L2S to use this backing field by using Storage parameter of the column attribute.  I also specify keys in both Person and PersonTitle tables.  All-in-all not so bad.  Now let’s look at many-to-many relationship between Person and Person interests.  In this case I have to actually create a class corresponding to my junction table (unlike Entity Framework where I do not need to do this).  Here is my junction table:

using System.Data.Linq;

using System.Data.Linq.Mapping;

 

namespace MangoDataApp

{

    [Table]

    public class PersonInterest

    {

        [Column(IsPrimaryKey = true)]

        public int PersonID { get; set; }

 

        [Column(IsPrimaryKey = true)]

        public int InterestID { get; set; }

 

        private EntityRef<Interest> interest;

        [Association(ThisKey = "InterestID", OtherKey = "InterestID", Storage = "interest")]

        public Interest Interest { get { return interest.Entity; } set { interest.Entity = value; } }

 

        private EntityRef<Person> person;

        [Association(ThisKey = "PersonID", OtherKey = "PersonID", Storage = "person")]

        public Person Person { get { return person.Entity; } set { person.Entity = value; } }

    }

}

 

Again, I am using EntityRef class just like in example above.  Now, how does my Person table take use of that?

using System.Data.Linq;

using System.Data.Linq.Mapping;

 

namespace MangoDataApp

{

    [Table]

    public class Person

    {

        [Column(DbType = "INT NOT NULL IDENTITY", IsDbGenerated = true,

            IsPrimaryKey = true, AutoSync = AutoSync.OnInsert)]

        public int PersonID { get; set; }

 

        [Column(DbType = "NVarChar(30) NOT NULL")]

        public string FirstName { get; set; }

 

        [Column(DbType = "NVarChar(50) NOT NULL")]

        public string LastName { get; set; }

 

        [Association(ThisKey = "PersonID", OtherKey = "PersonID")]

        public EntitySet<PersonInterest> PersonInterests { get; set; }

 

        [Column()]

        public int PersonTitleID { get; set; }

 

        private EntityRef<PersonTitle> personTitle;

        [Association(ThisKey = "PersonTitleID", OtherKey = "PersonTitleID",

            Storage = "personTitle")]

        public PersonTitle PersonTitle

        {

            get { return personTitle.Entity; }

            set { personTitle.Entity = value; }

        }

 

        public Person()

        {

            PersonInterests = new EntitySet<PersonInterest>();

        }

    }

}

 

Pretty easy again – I define Person Interest property as EntitySet this time, not EntityRef.

Let’s take a look at our context.  This class is very simple.  Please notice that I use fields, not properties for tables.  If you use properties you will get an ugly non-descriptive exception.

using System.Data.Linq;

 

namespace MangoDataApp

{

    public class PersonContext : DataContext

    {

        public  PersonContext()

            : base("isostore:/PersonDB.sdf")

        {

 

        }

        public Table<Person> People;

        public Table<Interest> Interests;

        public Table<PersonInterest> PersonInterests;

        public Table<PersonTitle> PersonTitles;

    }

}

 

And that is all there is to it.  Now, let me see how I can pump some data into my database.  Frist of, I am going to expose my database through property of my application class:

    public partial class App : Application

    {

        private static App app;

 

        public static App CurrentApp

        {

            get { return app; }

        }

 

 

        public PersonContext DB { get; private set; }

 

        /// <summary>

        /// Constructor for the Application object.

        /// </summary>

        public App()

        {

            app = this;

            DB = new PersonContext();

 

I have a static property to make the code that uses DB easier:

            if (!App.CurrentApp.DB.DatabaseExists())

            {

                App.CurrentApp.DB.CreateDatabase();

 

Cool.  Now, let’s pump some data into my four tables:

                var int1 = new Interest() { InterestName = "Phone" };

                App.CurrentApp.DB.Interests.InsertOnSubmit(int1);

 

                var int2 = new Interest() { InterestName = "Desktop" };

                App.CurrentApp.DB.Interests.InsertOnSubmit(int2);

 

                var title = new PersonTitle() { Title = "Mr." };

                App.CurrentApp.DB.PersonTitles

                    .InsertAllOnSubmit(new[] { title, new PersonTitle() { Title = "Mrs." } });

 

                App.CurrentApp.DB.SubmitChanges();

 

                var person = new Person() { FirstName = "Sergey", LastName = "Barskiy" };

                person.PersonTitleID = title.PersonTitleID;

 

 

                App.CurrentApp.DB.People.InsertOnSubmit(person);

                App.CurrentApp.DB.SubmitChanges();

 

                PersonInterest pi1 = new PersonInterest()

{ InterestID = int1.InterestID, PersonID = person.PersonID };

                App.CurrentApp.DB.PersonInterests.InsertOnSubmit(pi1);

 

                PersonInterest pi2 = new PersonInterest()

{ InterestID = int2.InterestID, PersonID = person.PersonID };

                App.CurrentApp.DB.PersonInterests.InsertOnSubmit(pi2);

 

                person.PersonInterests.Add(pi1);

                person.PersonInterests.Add(pi2);

                App.CurrentApp.DB.SubmitChanges();

 

The last thing I would like to talk about is lazy / eager loading.  By default L2S will use lazy loading, meaning that your related properties are not retrieved until they are accessed.  Most of that time this is good, but what if I always want to load the title when I get the data?  I just use data load options object to achieve that.

                DataLoadOptions options = new DataLoadOptions();

                options.LoadWith<Person>(c => c.PersonTitle);

                App.CurrentApp.DB.LoadOptions = options;

                var people = App.CurrentApp.DB.People.ToList();

 

You can download sample project here.

Here are some useful links on MSDN:

Column attribute

Linq to SQL Overview

SQL CE Overview for Windows Phone 7.1

SQL CE Connection Strings for the Phone

Post to Twitter

Atlanta Code Camp

Atlanta Code Camp 2011 took place yesterday, Saturday, June 25 2011. I was one of the organizers of the event.  The event was a success based on some preliminary feedback we gathered during the vent itself.  We have over 250 people attending 54 different sessions.

I did one talk – Using Code First (Code Only) approach with Entity Framework

Since I would like to share my slides and sample code, you can download the zip file by clicking on the title above.

Thank you.

Post to Twitter

Global Conventions in Entity Framework Code First v 4.1

In my previous post I showed how to implement custom conventions in absence of the same feature in Entity Framework 4.1.  Today I am going to expand on the same topic and try to create some global contentions.

Let me elaborate a bit on a problem I am trying to resolve.  For example, if I create a property of type decimal on a class that is used to create a set of entities in DbContext, it would result in the decimal(18,2) column definition in the database.  What if I would like my default to be (8,4) unless specified otherwise?  You can see my problem.  If I have 100 tables in my database all with 10 decimal fields, I would have to type in 1000 decimal column definitions using fluent API.   This is way too much typing to my taste. 

Here is the solution I would like to purse.  I am going to expand on my previous attribute based conventions and create a global, attribute-less convention.

First I am going to refactor my previous code and convert attribute specific conventions to generic conventions based on IConvention

Here is my new code to process all conventions and add one convention:

        /// <summary>

        /// Add one convention

        /// </summary>

        /// <param name="convention">Convention to add</param>

        protected void AddConvention(IConvention convention)

        {

            conventions.Add(convention);

        }

 

        /// <summary>

        /// Process conventions

        /// </summary>

        /// <param name="modelBuilder">Model builder</param>

        protected virtual void ProcessAddedConventions(DbModelBuilder modelBuilder)

        {

            if (conventions.Count > 0)

            {

                // poulate reflection data

                PopulateSetMetadata();

                // run through all added conventions

                conventions.ForEach(convention =>

                {

                    if (convention is IAttributeConvention)

                    {

                        ProcessAttributeBasedConvention(modelBuilder, convention as IAttributeConvention);

                    }

                   

                });

            }

        }

 

Now, I am going to write a new method to process global conventions.  I am going to extensively comment the code below:

    /// <summary>

    /// Process global conventions

    /// </summary>

    /// <param name="modelBuilder">Model builder</param>

    /// <param name="convention">One global convention to process</param>

    private void ProcessGlobalConvention(DbModelBuilder modelBuilder, IGlobalConvention convention)

    {

      var setMetadata = dbSetMetadata[this.GetType().AssemblyQualifiedName];

      // run through DbSets in current context

      setMetadata.ForEach(set =>

      {

        //run through properties in each DbSet<T> for class of type T

        set.DbSetItemProperties.ToList().ForEach(prop =>

        {

          // get type of property that matches current convention

          Type targetType = GetMatchingTypeForConfiguration(convention.PropertyConfigurationType);

          // make sure this type matched property type

          if (prop.PropertyInfo.PropertyType == targetType)

          {

            // Get entity method in ModuleBuilder

            // we are trying to get to the point of expressing the following

            //modelBuilder.Entity<Person>().Property(a => a.Name).IsMaxLength() for example

            var setMethod = modelBuilder.GetType()

                .GetMethod("Entity", BindingFlags.Public | BindingFlags.Instance | BindingFlags.FlattenHierarchy);

            // one we have Entity method, we have to add generic parameters to get to Entity<T>

            var genericSetMethod = setMethod.MakeGenericMethod(new Type[] { set.ItemType });

            // Get an instance of EntityTypeConfiguration<T>

            var entityInstance = genericSetMethod.Invoke(modelBuilder, null);

 

            //Get methods of EntityTypeConfiguration<T>

            var propertyAccessors = entityInstance.GetType().GetMethods(

                BindingFlags.Public | BindingFlags.Instance | BindingFlags.FlattenHierarchy).ToList();

 

            // we are looking for Property method that returns PropertyConfiguration

            // that is used in current convention

            var propertyMethod =

                propertyAccessors.Where(oneProperty =>

                    oneProperty.ReturnType == convention.PropertyConfigurationType).FirstOrDefault();

 

            //Get method handle in order to build the expression

            // example: (a => a.Name)

            var expressionGetMethod = GetPropertyExpressionMethodHandle();

 

            //Create lamda expression by making expression method that takes two generic parameters

            // one for class, the other for property type

            var genericExpressionMethod = expressionGetMethod

                .MakeGenericMethod(new Type[] { prop.PropertyInfo.DeclaringType, prop.PropertyInfo.PropertyType });

 

            //FInally, get lamda expression it self

            // example: (a => a.Name)

            var propertyExpression = genericExpressionMethod.Invoke(null, new object[] { prop.PropertyInfo });

 

            //Not get an instance of PrimitivePropertyConfiguration by 
            // infoking EntityTypeConfiguration<T>’s

            // Property() method

            var config = propertyMethod

                .Invoke(entityInstance, new object[] { propertyExpression }) as PrimitivePropertyConfiguration;

 

            //Finally, pass this configuration and attribute into the convention

            convention.ApplyConfiguration(prop.PropertyInfo, config);

          }

        });

      });

    }

 

Just like in attribute based convention method, I am running through all the properties for all the entities.  However, first step I am doing is making sure that the convention I am applying is matching the property type.  I am using the following method to get property type based on convention.

    /// <summary>

    /// Determine what property type should be used for a specific convention

    /// </summary>

    /// <param name="propertyConfigurationType">

    /// Type of PrimitivePropertyConfiguration to process

    /// </param>

    /// <returns>

    /// Property type that should be used with current convention

    /// </returns>

    private Type GetMatchingTypeForConfiguration(Type propertyConfigurationType)

    {

      if (propertyConfigurationType == typeof(DecimalPropertyConfiguration))

      {

        return typeof(decimal);

      }

      if (propertyConfigurationType == typeof(StringPropertyConfiguration))

      {

        return typeof(string);

      }

      if (propertyConfigurationType == typeof(DateTimePropertyConfiguration))

      {

        return typeof(DateTime);

      }

      if (propertyConfigurationType == typeof(BinaryPropertyConfiguration))

      {

        return typeof(byte[]);

      }

      else

      {

        return typeof(object);

      }

    }

 

For example, I only want to apply string based configuration to string properties.  The rest of the code on apply global conventions methods is just reflection code aimed to obtain property configuration and call Apply method of the global convention.  Here is a sample implementation for this convention, making all decimal properties decimal(8,4):

using System;

using System.Data.Entity.ModelConfiguration.Configuration;

using System.Reflection;

using EFCodeFirstConventions;

 

namespace EFCodeFirstConventionsConsole

{

  public class GenericDecimalConvention :

    GlobalConfigurationConvention<MemberInfo, DecimalPropertyConfiguration>

  {

    protected override void Apply(

      MemberInfo memberInfo,

      DecimalPropertyConfiguration propertyConfiguration)

    {

      propertyConfiguration.HasPrecision(8, 4);

    }

  }

}

 

If I add this convention to my extended context, I will make my decimals size (8,4) unless specified otherwise.  In order to do so, I am processing global conventions prior to attribute based conventions:

    /// <summary>

    /// Process conventions

    /// </summary>

    /// <param name="modelBuilder">Model builder</param>

    protected virtual void ProcessAddedConventions(DbModelBuilder modelBuilder)

    {

      if (conventions.Count > 0)

      {

        // poulate reflection data

        PopulateSetMetadata();

        // run through all global added conventions

        conventions.ForEach(convention =>

        {

          if (convention is IGlobalConvention)

          {

            ProcessGlobalConvention(modelBuilder, convention as IGlobalConvention);

          }

        });

        // run through attribute based conventions

        conventions.ForEach(convention =>

        {

          if (convention is IAttributeConvention)

          {

            ProcessAttributeBasedConvention(modelBuilder, convention as IAttributeConvention);

          }

        });

      }

    }

 

Now, let’s do another convention that is based on property names.  For example, I want to have all properties that have word Percent in them to be set as decimal(4,2).  I am just going to write a global convention for that:

using System.Data.Entity.ModelConfiguration.Configuration;

using System.Reflection;

using EFCodeFirstConventions;

 

namespace EFCodeFirstConventionsConsole

{

  public class PercentConvention :

    GlobalConfigurationConvention<MemberInfo, DecimalPropertyConfiguration>

  {

    protected override void Apply(

      MemberInfo memberInfo,

      DecimalPropertyConfiguration propertyConfiguration)

    {

      if (memberInfo.Name.ToUpper().Contains("PERCENT"))

      {

        propertyConfiguration.HasPrecision(4, 2);

      }

    }

  }

}

 

 

Super easy, now that I have my framework setup.  In this convention I am using the fact that I have MethodInfo as a parameter, so I can apply name based global conventions!  This cuts down on amount of fluent API code I have to write tremendously.  One thing to remember, I have to add conventions to my extended context in certain order to ensure they do not overwrite each other.

 

    public class CustomContext : ExtendedDbContext

    {

      public IDbSet<Person> Perosns { get; set; }

 

      protected override void AddConventions()

      {

        AddConvention(new EtendedStringConvention());

        AddConvention(new GenericDecimalConvention());

        AddConvention(new PercentConvention());

      }

 

    }

 

Here is my test Person class I am using:

using System.ComponentModel.DataAnnotations;

 

namespace EFCodeFirstConventionsConsole

{

  public class Person

  {

    [Key]

    public int PersonID { get; set; }

 

    [ExtendedString(10, 200, false)]

    public string Name { get; set; }

 

    public bool IsActive { get; set; }

 

    public decimal GenericDecimal { get; set; }

 

    public decimal Percent { get; set; }

  }

}

 

You can download updated project here.

 

Thank you.

Post to Twitter

Custom Conventions in Entity Framework Code First v 4.1

As you know, in preview version of Entity Framework code first existed concept of custom pluggable conventions that would allow the developer to avoid using large amounts of code in OnModelCreating method.  Typically, if one would like to keep entity classes free of entity framework references and possibly of Data Annotations references, fluent API available in ModelBuilder class can be used to configure entire model.  However, if you have reasonably large model, you will end up with thousands of lined of code in OnModelCreating  method of you custom DbContext.

Custom conventions would allow you to avoid this situation.  You would combine custom attributes with pluggable conventions that would pass the attribute value into your convention along with appropriate instance PropertyConfiguration, such as StringPropertyConfiguration.

Unfortunately, this functionality was removed from release candidate build and also will not be available in final build of EF 4.1 sometimes later this year.  I heard some complaints regarding this issue, and decided to write something compatible.

In this post I will describe how I went about this process, illustrating how to support primitive properties, such as string properties. 

I borrowed the concept from CTP 5.  My base class of custom convention is very similar to Microsoft one.

I started with the following interface:

using System;

using System.Data.Entity.ModelConfiguration.Configuration;

using System.Data.Entity.ModelConfiguration.Conventions;

using System.Reflection;

 

namespace EFCodeFirstConventions

{

    public interface IAttributeConvention : IConvention

    {

        void ApplyConfiguration(

            MemberInfo memberInfo,

            PrimitivePropertyConfiguration propertyConfiguration,

            Attribute attrribute);

 

        Type PropertyConfigurationType { get; }

        Type AttributeType { get; }

    }

}

 

 

IConvention class still exists in release candidate.  The idea behind the interface is as following.  I will call ApplyConfiguration method, passing the instance of attribute that my property inside entity class is decorated with along with PropertyInfo object (to provide additional information to the convention class).  Here is my implementation in an abstract class:

using System;

using System.Data.Entity.ModelConfiguration.Configuration;

using System.Reflection;

 

namespace EFCodeFirstConventions

{

    public abstract class AttributeConfigurationConvention<TMemberInfo, TPropertyConfiguration, TAttribute>

        : IAttributeConvention

        where TMemberInfo : MemberInfo

        where TPropertyConfiguration : PrimitivePropertyConfiguration

        where TAttribute : Attribute

    {

 

        public void ApplyConfiguration(

            MemberInfo memberInfo,

            PrimitivePropertyConfiguration propertyConfiguration,

            Attribute attribute)

        {

            Apply((TMemberInfo)memberInfo, (TPropertyConfiguration)propertyConfiguration, (TAttribute)attribute);

        }

 

        protected abstract void Apply(

            TMemberInfo memberInfo,

            TPropertyConfiguration propertyConfiguration,

            TAttribute attrribute);

 

 

        public Type PropertyConfigurationType

        {

            get { return typeof(TPropertyConfiguration); }

        }

 

        public Type AttributeType

        {

            get { return typeof(TAttribute); }

        }

 

    }

 

 

 

}

 

I introduced Apply method in my base convention class in order to make cleaner, strongly typed API in derived classes.  I also make convention strongly typed via generics, again in order to easy the pain of implementation.

Here is a sample implementation:

using System.Data.Entity.ModelConfiguration.Configuration;

using System.Reflection;

using EFCodeFirstConventions;

 

namespace EFCodeFirstConventionsConsole

{

    public class EtendedStringConvention :

        AttributeConfigurationConvention<MemberInfo, StringPropertyConfiguration, ExtendedStringAttribute>

    {

 

        protected override void Apply(

            MemberInfo memberInfo,

            StringPropertyConfiguration propertyConfiguration,

            ExtendedStringAttribute attrribute)

        {

            propertyConfiguration.IsUnicode(attrribute.IsUnicode);

            if (attrribute.MaxLength == int.MaxValue || attrribute.MaxLength == -1)

            {

                propertyConfiguration.IsMaxLength();

            }

            else if (attrribute.MaxLength == attrribute.MinLength && attrribute.MinLength > 0)

            {

                propertyConfiguration.IsMaxLength();

                propertyConfiguration.IsFixedLength();

                propertyConfiguration.HasMaxLength(attrribute.MaxLength);

            }

            else

            {

                propertyConfiguration.HasMaxLength(attrribute.MaxLength);

            }

        }

    }

}

 

 

As you can see, once you are in your Apply method if you have clean code that allows you to configure StringPropertyConfiguration based on ExtendedStringAttribute value.

using System;

 

namespace EFCodeFirstConventionsConsole

{

    [AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]

    public class ExtendedStringAttribute : Attribute

    {

        public ExtendedStringAttribute()

            : this(isUnicode: true)

        {

 

        }

 

        public ExtendedStringAttribute(

            int minLength = 0, int maxLength = int.MaxValue, bool isUnicode = true)

        {

            MinLength = minLength;

            MaxLength = maxLength;

            IsUnicode = isUnicode;

        }

 

        public int MinLength { get; private set; }

        public int MaxLength { get; private set; }

        public bool IsUnicode { get; private set; }

    }

}

 

Code above is my attribute.  I am using it as follows:

using System.ComponentModel.DataAnnotations;

 

namespace EFCodeFirstConventionsConsole

{

    public class Person

    {

        [Key]

        public int PersonID { get; set; }

 

        [ExtendedString(10, 200, false)]

 

        public string Name { get; set; }

 

        public bool IsActive { get; set; }

 

    }

}

 

Easy as pie.  Now, here is the ugly code with plenty of reflection that supports this nice feature.  I documented the class itself very thoroughly to ensure that one can understand what I wrote.

using System;

using System.Collections.Generic;

using System.Data.Entity;

using System.Data.Entity.ModelConfiguration.Configuration;

using System.Linq;

using System.Linq.Expressions;

using System.Reflection;

using EFCodeFirstConventions.Reflection;

 

namespace EFCodeFirstConventions

{

    public abstract class ExtendedDbContext : DbContext

    {

        protected override void OnModelCreating(DbModelBuilder modelBuilder)

        {

            base.OnModelCreating(modelBuilder);

            // call derived class to add conventions

            AddConventions();

            // now process conventions

            ProcessAddedConventions(modelBuilder);

        }

 

        /// <summary>

        /// Force implementation via astract class

        /// </summary>

        protected abstract void AddConventions();

 

        // conventsions saved here

        private List<IAttributeConvention> conventions = new List<IAttributeConvention>();

 

        //reflrecion data about DbContext, its sets, properties and attributes

        private static Dictionary<string, List<DbSetMetadata>> dbSetMetadata =

            new Dictionary<string, List<DbSetMetadata>>();

 

        private static object locker = new object();

 

        /// <summary>

        /// Add one convention

        /// </summary>

        /// <param name="convention">Convention to add</param>

        protected void AddConvention(IAttributeConvention convention)

        {

            conventions.Add(convention);

        }

 

        /// <summary>

        /// Process conventions

        /// </summary>

        /// <param name="modelBuilder">Model builder</param>

        protected virtual void ProcessAddedConventions(DbModelBuilder modelBuilder)

        {

            if (conventions.Count > 0)

            {

                // poulate reflection data

                PopulateSetMetadata();

                // run through all added conventions

                conventions.ForEach(convention =>

                {

                    var setMetadata = dbSetMetadata[this.GetType().AssemblyQualifiedName];

                    // run through DbSets in current context

                    setMetadata.ForEach(set =>

                        {

                            //run through properties in each DbSet<T> for class of type T

                            set.DbSetItemProperties.ToList().ForEach(prop =>

                                {

                                    // get attribute that matches convention

                                    var data = prop.DbSetItemAttributes

                                        .Where(attr => attr.Attribute.GetType() == convention.AttributeType).FirstOrDefault();

 

                                    // this class’s property has the attribute

                                    if (data != null)

                                    {

                                        // Get entity method in ModuleBuilder

                                        // we are trying to get to the point of expressing the following

                                        //modelBuilder.Entity<Person>().Property
                                        // (a => a.Name).IsMaxLength() for example

                                        var setMethod = modelBuilder.GetType()

                                            .GetMethod("Entity", BindingFlags.Public | BindingFlags.Instance | BindingFlags.FlattenHierarchy);

                                        // one we have Entity method, we have to add generic parameters
                                        // to get to Entity<T>

                                        var genericSetMethod = setMethod
.MakeGenericMethod(
new Type
[] { set.ItemType });

                                        // Get an instance of EntityTypeConfiguration<T>

                                        var entityInstance = genericSetMethod
Invoke(modelBuilder,
null
);

 

                                        //Get methods of EntityTypeConfiguration<T>

                                        var propertyAccessors = entityInstance.GetType().GetMethods(

                                            BindingFlags.Public | BindingFlags.Instance 
|
BindingFlags
.FlattenHierarchy).ToList();

 

                                        // we are looking for Property method that returns 
                                        // PropertyConfiguration

                                        // that is used in current convention

                                        var propertyMethod =

                                            propertyAccessors.Where(oneProperty =>

                                                oneProperty.ReturnType == 
.PropertyConfigurationType).FirstOrDefault();

 

                                        //Get method handle in order to build the expression

                                        // example: (a => a.Name)

                                        var expressionGetMethod = 
GetPropertyExpressionMethodHandle();

 

                                        //Create lamda expression by making expression method that takes two generic parameters

                                        // one for class, the other for property type

                                        var genericExpressionMethod = expressionGetMethod

                                            .MakeGenericMethod(new Type[] { prop.PropertyInfo.DeclaringType, prop.PropertyInfo.PropertyType });

 

                                        //FInally, get lamda expression it self

                                        // example: (a => a.Name)

                                        var propertyExpression = 
genericExpressionMethod.Invoke(
null, new object
[] { prop.PropertyInfo });

 

                                        //Not get an instance of PrimitivePropertyConfiguration by 
                                        //infoking EntityTypeConfiguration<T>’s

                                        // Property() method

                                        var config = propertyMethod

                                            .Invoke(entityInstance, 
new object[] { propertyExpression }) as PrimitivePropertyConfiguration;

 

                                        //Finally, pass this configuration and attribute into the 
                                        // convention

                                        convention.ApplyConfiguration(prop.PropertyInfo, config, data.Attribute);

                                    }

 

                                });

                        });

                });

            }

        }

 

        /// <summary>

        /// Locate member info handle for GetPropertyExpression method by iterating through

        /// class hierarchy

        /// </summary>

        /// <returns>MemberInfo handle for GetPropertyExpression method</returns>

        private MethodInfo GetPropertyExpressionMethodHandle()

        {

            MethodInfo returnValue = null;

            Type currentType = this.GetType();

            while (returnValue == null)

            {

                returnValue = currentType

                                .GetMethod("GetPropertyExpression",

                                BindingFlags.NonPublic | BindingFlags.FlattenHierarchy 
|
BindingFlags
.Static);

                if (returnValue == null)

                {

                    currentType = currentType.BaseType;

                    if (currentType == null)

                    {

                        break;

                    }

                }

            }

            return returnValue;

        }

 

        /// <summary>

        /// Create Expression that can access property on a class.  You would typically write it as

        /// (p=>p.Name)

        /// In our case we are using Expression to build the same expression

        /// </summary>

        /// <typeparam name="TClass">Class type that is owning the property in question</typeparam>

        /// <typeparam name="TProperty">Property type</typeparam>

        /// <param name="property">PropertyInfo object for property in question</param>

        /// <returns>Expression that returns the property, such as (p=>p.Name)</returns>

        private static Expression<Func<TClass, TProperty>> GetPropertyExpression<TClass, TProperty>(PropertyInfo property)

        {

            //  Create {p=> portion of the Epxression in example (p=>p.Name)

            var objectExpression = Expression.Parameter(property.DeclaringType, "param");

            // create property expression – .Name for example

            var propertyExpression = Expression.Property(objectExpression, property);

            //Create lambda expression from two parts

            var returnValue = Expression.Lambda<Func<TClass, TProperty>>(propertyExpression, objectExpression);

            return returnValue;

        }

 

        /// <summary>

        /// RUn through DbContnxt sets and save reflection data in a dictionary

        /// </summary>

        private void PopulateSetMetadata()

        {

            if (!dbSetMetadata.ContainsKey(this.GetType().AssemblyQualifiedName))

            {

                lock (locker)

                {

                    if (!dbSetMetadata.ContainsKey(this.GetType().AssemblyQualifiedName))

                    {

                        var props = this.GetType().GetProperties(

                            BindingFlags.Public | BindingFlags.Instance | 
BindingFlags
.FlattenHierarchy).ToList();

                        List<DbSetMetadata> sets = new List<DbSetMetadata>();

                        props.ForEach(one =>

                        {

                            //Filter out db sets

                            if (one.PropertyType.IsGenericType &&

                                (one.PropertyType.GetGenericTypeDefinition() == typeof(DbSet<>) ||

                                one.PropertyType.GetGenericTypeDefinition() == typeof(IDbSet<>)))

                            {

                                sets.Add(new DbSetMetadata(one.PropertyType.GetGenericArguments().First(), one));

                            }

                        });

                        // add this context to diutionary

                        dbSetMetadata.Add(this.GetType().AssemblyQualifiedName, sets);

                    }

                }

            }

        }

    }

}

 

That is all.  Feel free to use the code.  Please contact me (use Contact page) if you would like me to create CodePlex project for this sample of extend it further.

Here is what my inherited sample DbContext looks like:

        public class CustomContext: ExtendedDbContext

        {

            public IDbSet<Person> Perosns { get; set; }

 

            protected override void AddConventions()

            {

                AddConvention(new EtendedStringConvention());

            }

 

        }

 

 

You can download full project here.

Thanks.

Post to Twitter

Entity Framework Code First Caching

As I was playing around with Entity Framework code first and ASP.NET MVC, it became pretty obvious that caching of some of the EF query results would be pretty handy.  For example, if you look at my post that includes a simple ASP.NET MVC blogging application, you could see that I have to keep fetching the list of categories every time I render the blog entry screen.  This is because I want to keep my application stateless.  This is not very efficient.

As a result, I looked around for some caching options.  There is a second-level cache project that was published on Microsoft code.  Here is the link to it.  This is very involved and handy project, but currently does not support EF 4.1 (official release name for a number of features, including Code First).  You can download release candidate here or get it from NuGet inside Visual Studio 2010.

Also, I wanted something more explicit, such as extension method to specify that I want to cache results of a specific query.  Here is how I wrote a simple extension method:

        public static IEnumerable<T> AsCacheable<T>(this IQueryable<T> query)

        {

            if (cacheProvider == null)

            {

                throw new InvalidOperationException("Please set cache provider (call SetCacheProvider) before using caching");

            }

            return cacheProvider.GetOrCreateCache<T>(query);

        }

 

I envision my final usage of new functionality will look a following:

 

EFCacheExtensions.SetCacheProvider(MemoryCacheProvider.GetInstance());

using (ProductContext context = new ProductContext())

{

  var query = context.Products.OrderBy(one => one.ProductNumber).
    Where(one => one.IsActive).AsCacheable();

 

}

 

In the example above, I am using in-memory cache provider I wrote.  This provider is using static memory variable to cache data.  This is poor man’s caching solution every day.  To elaborate, this approach works in any environment.  If you are using think client, such as WPF or Windows forms, the cache will stay in memory as long as the application is running.  This is because of the rule of garbage collector.  Static variables are never garbage collected.  In case of web application, such as WCF, the static variables will leave as long as application pool lives.  They will be lost when application pool is recycled.  Default time for app pool recycling in IIS 7 is 20 minutes.  This means that your cache will not live very long.  So, I am also going to implement a provider that is using AppFabric Caching (formerly known as project Velocity).

Since I would like to have an abstraction over both, so that I do not have to change the code going from Velocity to In-Memory provider, I will create an interface to deal with that.

    public interface IEFCacheProvider

    {

        IEnumerable<T> GetOrCreateCache<T>(IQueryable<T> query);

        IEnumerable<T> GetOrCreateCache<T>(IQueryable<T> query, TimeSpan cacheDuration);

        bool RemoveFromCache<T>(IQueryable<T> query);

    }

 

Now, the key code is to implement both providers.  I am going to explain memory provider first.  Here is the class

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Collections.Concurrent;

 

namespace EFCodeFirstCacheExtensions

{

    public class MemoryCacheProvider : IEFCacheProvider

    {

        private MemoryCacheProvider() { }

 

        public static MemoryCacheProvider GetInstance()

        {

            lock (locker)

            {

                if (dictionary == null)

                {

                    dictionary = new ConcurrentDictionary<string, CacheItem>();

                }

 

                if (instance == null)

                {

                    instance = new MemoryCacheProvider();

                }

            }

            return instance;

        }

 

        private static ConcurrentDictionary<string, CacheItem> dictionary;

        private static MemoryCacheProvider instance;

        private static object locker = new object();

 

        public IEnumerable<T> GetOrCreateCache<T>(IQueryable<T> query, TimeSpan cacheDuration)

        {

            string key = GetKey<T>(query);

 

            CacheItem item = dictionary.GetOrAdd(

                key,

                (keyToFind) => { return new CacheItem()

                    { Item = query.ToList(), AdditionTime = DateTime.Now }; });

 

            if (DateTime.Now.Subtract(item.AdditionTime) > cacheDuration)

            {

                item = dictionary.AddOrUpdate(

                    key,

                    new CacheItem() { Item = item.Item, AdditionTime = DateTime.Now },

                    (keyToFind, oldItem) => { return new CacheItem()

                        { Item = query.ToList(), AdditionTime = DateTime.Now }; });

            }

            foreach (var oneItem in ((List<T>)item.Item))

            {

                yield return oneItem;

            }

        }

 

        public IEnumerable<T> GetOrCreateCache<T>(IQueryable<T> query)

        {

            string key = GetKey<T>(query);

 

            CacheItem item = dictionary.GetOrAdd(

                key,

                (keyToFind) => { return new CacheItem()

                    { Item = query.ToList(), AdditionTime = DateTime.Now }; });

 

            foreach (var oneItem in ((List<T>)item.Item))

            {

                yield return oneItem;

            }

        }

 

        public bool RemoveFromCache<T>(IQueryable<T> query)

        {

            string key = GetKey<T>(query);

            CacheItem item = null;

            return dictionary.TryRemove(key, out item);

        }

 

        private static string GetKey<T>(IQueryable<T> query)

        {

            string key = string.Concat(query.ToString(), "\n\r",

                typeof(T).AssemblyQualifiedName);

            return key;

        }

    }

}

 

 

As you can see, I am implementing the original provider interface.  I am supporting self-expiring as well as non-expiring cache.  I am using a neat feature of EF Code Frist.  If I issue to .ToString() of IQueryable, I will get the actual T-SQL query that will be executed.  I am using it as a key for caching in conjunction with class name (T is the type that is returned by they query).  I have to issue ToList prior to caching because of deferred execution in Entity Framework – query is not executed on the back end until I access at least one result.  So, am caching the results of the query as List<T>, but returning IEnumerable<T> to be more generic.

Next, comes AppFabric provider.  The only difference is that I am using AppFabric caching features.  To write and to test, I have to install AppFabric locally and add references to client DLLs to my project.  Those DLLs are:

Microsoft.ApplicationServer.Caching.Client

Microsoft.ApplicationServer.Caching.Core

Once those are done, I just need to replace any chunks of code that refer to memory to refer to cache object from AppFabric.  Here is the full class:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using EFCodeFirstCacheExtensions;

using Microsoft.ApplicationServer.Caching;

 

namespace AppFabricCacheProvider

{

    public class AppFabricCacheProvider : IEFCacheProvider

    {

        private AppFabricCacheProvider() { }

 

        private static object locker = new object();

        private static AppFabricCacheProvider instance;

        private static DataCache cache;

 

        public static AppFabricCacheProvider GetInstance()

        {

            lock (locker)

            {

                if (instance == null)

                {

                    instance = new AppFabricCacheProvider();

                    DataCacheFactory factory = new DataCacheFactory();

                    cache = factory.GetCache("Default");

                }

            }

            return instance;

        }

        public IEnumerable<T> GetOrCreateCache<T>(IQueryable<T> query, TimeSpan cacheDuration)

        {

            string key = GetKey<T>(query);

 

            var cacheItem = cache.Get(key);

            if (cacheItem == null)

            {

                cache.Put(key, query.ToList(), cacheDuration);

                foreach (var oneItem in query)

                {

                    yield return oneItem;

                }

            }

            else

            {

                foreach (var oneItem in ((List<T>)cacheItem))

                {

                    yield return oneItem;

                }

            }

        }

 

        public IEnumerable<T> GetOrCreateCache<T>(IQueryable<T> query)

        {

            string key = GetKey<T>(query);

 

            var cacheItem = cache.Get(key);

            if (cacheItem == null)

            {

                cache.Put(key, query.ToList());

                foreach (var oneItem in query)

                {

                    yield return oneItem;

                }

            }

            else

            {

                foreach (var oneItem in ((List<T>)cacheItem))

                {

                    yield return oneItem;

                }

            }

        }

 

        public bool RemoveFromCache<T>(IQueryable<T> query)

        {

            string key = GetKey<T>(query);

            CacheItem item = null;

            return cache.Remove(key);

        }

 

        private static string GetKey<T>(IQueryable<T> query)

        {

            string key = string.Concat(query.ToString(), "\n\r",

                typeof(T).AssemblyQualifiedName);

            return key;

        }

    }

}

 

II am using built-in expiration as well instead of computing the expiration myself.  I am using DataCacheFactory to create an instance of cache named Default.  Here is my app.config from my unit test project that supports this code:

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

    <!–configSections must be the FIRST element –>

    <configSections>

        <section name="dataCacheClient"

           type="Microsoft.ApplicationServer.Caching.DataCacheClientSection, 
Microsoft.ApplicationServer.Caching.Core, Version=1.0.0.0, 
Culture=neutral, PublicKeyToken=31bf3856ad364e35
"

           allowLocation="true"

           allowDefinition="Everywhere"/>

    </configSections>

 

    <dataCacheClient>

        <hosts>

            <host

               name="SERGEYB-PC1"

               cachePort="22233"/>

        </hosts>

        <localCache

             isEnabled="true"

             sync="TimeoutBased"

             objectCount="100000"

             ttlValue="300" />

 

    </dataCacheClient>

 

    <connectionStrings>

        <add name="ProductConnection"

             connectionString="Server=(local);Database=Products;Trusted_Connection=True;"

             providerName="System.Data.SqlClient"/>

    </connectionStrings>

 

</configuration>

 

Also, I used AppFabric console to create cache named Default.

Here is my unit test code that test usage of my new classes:

        [TestMethod]

        public void MemoryCacheProviderGetOrCreateCacheUsageTest()

        {

            EFCacheExtensions.SetCacheProvider(MemoryCacheProvider.GetInstance());

            using (ProductContext context = new ProductContext())

            {

                var query = context.Products

                    .OrderBy(one => one.ProductNumber)

                    .Where(one => one.IsActive).AsCacheable();

 

                Assert.AreEqual(2, query.Count(), "Should have 2 rows");

 

                SQLCommandHelper.ExecuteNonQuery("Update Products Set IsActive = 0");

 

                query = context.Products

                    .OrderBy(one => one.ProductNumber)

                    .Where(one => one.IsActive).AsCacheable();

                Assert.AreEqual(2, query.Count(), "Should have 2 rows");

 

 

                IQueryable<Product> cleanupQuery = context.Products

                    .OrderBy(one => one.ProductNumber)

                    .Where(one => one.IsActive);

 

                EFCacheExtensions.RemoveFromCache<Product>(cleanupQuery);

 

                query = context.Products

                    .OrderBy(one => one.ProductNumber)

                    .Where(one => one.IsActive).AsCacheable();

                Assert.AreEqual(0, query.Count(), "Should have 0 rows");

 

                EFCacheExtensions.RemoveFromCache<Product>(cleanupQuery);

            }

 

        }

 

This demonstrates my intended use of caching in c – simply add AsCacheable to explicitly cache results of a query.  You can also supply timespan for which the cache will live.

You can download entire solution with unit tests and In-Memory and AppFabric implementations here.

Thanks.

Post to Twitter

Repository Pattern with Entity Framework

Repository pattern is a popular architecture pattern that is used to create data access code / layer for an application.  Basic principal behind the pattern is that business object in your application never talks to database directly, but instead talks to repository that takes POCO classes and also returns POCO classes as results of a query.  This way your business layer does not take on a dependency on a specific database or data access layer implementation.  As we see from experience, data access technology does not see to be handing around for very long.

As I was working on entity framework code first, I decided to take a few minutes to develop a number of  convenience classes that would make it easier to repositories that internally use entity framework code first.  Typically, you would create a repository for a set of classes, and this repository will be responsible for querying some data as well as CUD operations.  For example, if you had a product table, you might have repository with methods such as CreateProduct(Product product), UpdateProduct(Product product), DeleteProduct(Product product), GetProductsOrderedByName(int pageNumber, int rowsPerPage), GetProductsOrderedByNumber(int pageNumber, int rowsPerPage), etc…  As you develop more of your application, you might end up with a whole number of repositories.  A lot of them might have similar or the same methods.

I would like to simplify this approach by creating a generic repository that works with DbContext.  So, I am going to create a generic repository for  context type.

  /// <summary>

    /// Repository base class used with DbContext

    /// </summary>

    /// <typeparam name="TContext">Type of DdContext that this repositiory operates on</typeparam>

    public class EFRepository<TContext> : IDisposable

        where TContext : DbContext, IObjectContextAdapter, new()

    {

        private TContext context;

 

        private EFRepository()

        {

 

        }

        /// <summary>

        /// Create new instance of repository

        /// </summary>

        /// <param name="connecstionStringName">Connection string name from .config file</param>

        public EFRepository(string connecstionStringName)

        {

            context = new TContext();

            context.Database.Connection.ConnectionString =

                ConfigurationManager.ConnectionStrings[connecstionStringName].ConnectionString;

        }

 

        /// <summary>

        /// Dipose repository

        /// </summary>

        public void Dispose()

        {

            if (context != null)

            {

                context.Dispose();

                context = null;

            }

        }

 

As you can see, I am using DbContext as a parameter.  I also force to user to supply connection string.  In constructor I am creating new instance of the context.  As a good little developer, I am using IDisposable interface to dispose of the context.  So far pretty simple.  Next, I would like to write generic query mechanism via Select method.

 

 

        /// <summary>

        /// Select data from database

        /// </summary>

        /// <typeparam name="TItem">Type of data to select</typeparam>

        /// <returns></returns>

        public IQueryable<TItem> Select<TItem>()

           where TItem : class, new()

        {

            PropertyInfo property = GetDbSet(typeof(TItem));

 

            DbSet<TItem> set = property.GetValue(context, null) as DbSet<TItem>;

 

            return set;

        }

 
        private PropertyInfo GetDbSet(Type itemType)
        {
            var properties = typeof(TContext).GetProperties().

Where(item => item.PropertyType.Equals(typeof(DbSet<>).MakeGenericType(itemType)));
 
            return properties.First();
        }

 

My select method returns IQueryable.  This way the user of the repository can supply strongly types where and order by clauses as well as Top() and Skip() methods for paging.  Here is an example:

using (EFRepository<ProductContext> repository = new EFRepository<ProductContext>("ProductConnection"))

            {

 

                var ordered = repository.Select<Product>().Where(one => one.IsActive == true).OrderBy(one => one.ProducNumber).ToList();

 

This way I can avoid a whole slew of Select methods and the only dependency I take on is IQueryable, which is a generic interface.in System.Linq namespace located in System.Core.  This to me is a small price to pay for code reduction in repository. 

Next, insert code.  This is super simple of course. 

        /// <summary>

        /// Insert new item into database

        /// </summary>

        /// <typeparam name="TItem">Type of item to insert</typeparam>

        /// <param name="item">Item to insert</param>

        /// <returns>Inserted item</returns>

        public TItem Insert<TItem>(TItem item)

            where TItem : class, new()

        {

            DbSet<TItem> set = GetDbSet(typeof(TItem)).GetValue(context, null) as DbSet<TItem>;

            set.Add(item);

            context.SaveChanges();

            return item;

        }

 

Again, I am using a bit of reflection to find the property DbSet property in the context.  Once I find the set, I add new item to it, fire Save and return new item.  I am using Identity field, and conveniently enough returned item will have the value populated.

Here is an update method:

        /// <summary>

        /// Update na item

        /// </summary>

        /// <typeparam name="TItem">Type of item to update</typeparam>

        /// <param name="item">Item to update</param>

        /// <returns>Updated item</returns>

        public TItem Update<TItem>(TItem item)

            where TItem : class, new()

        {

            DbSet<TItem> set = GetDbSet(typeof(TItem)).GetValue(context, null) as DbSet<TItem>;

            set.Attach(item);

            context.Entry(item).State = System.Data.EntityState.Modified;

            context.SaveChanges();

            return item;

        }

 

Update code is only slightly more complicated.  I am simply setting the state to modified after I am attaching the item in order to force the update.  Delete statement is about the same:

 

 

 

        /// <summary>

        /// Delete an item

        /// </summary>

        /// <typeparam name="TItem">Type of item to delete</typeparam>

        /// <param name="item">Item to delete</param>

        public void Delete<TItem>(TItem item)

           where TItem : class, new()

        {

            DbSet<TItem> set = GetDbSet(typeof(TItem)).GetValue(context, null) as DbSet<TItem>;

            var entry = context.Entry(item);

            if (entry != null)

            {

                entry.State = System.Data.EntityState.Deleted;

            }

            else

            {

                set.Attach(item);

            }

            context.Entry(item).State = System.Data.EntityState.Deleted;

            context.SaveChanges();

        }

The only difference is that I am testing for entity not being in the context, which is really should not be necessary ordinarily, but I ran into an issue like this in my test project, so I added this code to it.

That is all there is to it.  Here is the full class code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.Entity;

using System.Linq.Expressions;

using System.Reflection;

using System.Configuration;

using System.Data.Entity.Infrastructure;

using System.Data.Objects;

 

namespace RepositoryEFCodeFirst

{

    /// <summary>

    /// Repository base class used with DbContext

    /// </summary>

    /// <typeparam name="TContext">Type of DdContext that this repositiory operates on</typeparam>

    public class EFRepository<TContext> : IDisposable

        where TContext : DbContext, IObjectContextAdapter, new()

    {

        private TContext context;

 

        private EFRepository()

        {

 

        }

        /// <summary>

        /// Create new instance of repository

        /// </summary>

        /// <param name="connecstionStringName">Connection string name from .config file</param>

        public EFRepository(string connecstionStringName)

        {

            context = new TContext();

            context.Database.Connection.ConnectionString =

                ConfigurationManager.ConnectionStrings[connecstionStringName].ConnectionString;

        }

 

        /// <summary>

        /// Dipose repository

        /// </summary>

        public void Dispose()

        {

            if (context != null)

            {

                context.Dispose();

                context = null;

            }

        }

 

 

        /// <summary>

        /// Select data from database

        /// </summary>

        /// <typeparam name="TItem">Type of data to select</typeparam>

        /// <returns></returns>

        public IQueryable<TItem> Select<TItem>()

           where TItem : class, new()

        {

            PropertyInfo property = GetDbSet(typeof(TItem));

 

            DbSet<TItem> set = property.GetValue(context, null) as DbSet<TItem>;

 

            return set;

        }

 

        /// <summary>

        /// Insert new item into database

        /// </summary>

        /// <typeparam name="TItem">Type of item to insert</typeparam>

        /// <param name="item">Item to insert</param>

        /// <returns>Inserted item</returns>

        public TItem Insert<TItem>(TItem item)

            where TItem : class, new()

        {

            DbSet<TItem> set = GetDbSet(typeof(TItem)).GetValue(context, null) as DbSet<TItem>;

            set.Add(item);

            context.SaveChanges();

            return item;

        }

 

        /// <summary>

        /// Update na item

        /// </summary>

        /// <typeparam name="TItem">Type of item to update</typeparam>

        /// <param name="item">Item to update</param>

        /// <returns>Updated item</returns>

        public TItem Update<TItem>(TItem item)

            where TItem : class, new()

        {

            DbSet<TItem> set = GetDbSet(typeof(TItem)).GetValue(context, null) as DbSet<TItem>;

            set.Attach(item);

            context.Entry(item).State = System.Data.EntityState.Modified;

            context.SaveChanges();

            return item;

        }

 

        /// <summary>

        /// Delete an item

        /// </summary>

        /// <typeparam name="TItem">Type of item to delete</typeparam>

        /// <param name="item">Item to delete</param>

        public void Delete<TItem>(TItem item)

           where TItem : class, new()

        {

            DbSet<TItem> set = GetDbSet(typeof(TItem)).GetValue(context, null) as DbSet<TItem>;

            var entry = context.Entry(item);

            if (entry != null)

            {

                entry.State = System.Data.EntityState.Deleted;

            }

            else

            {

                set.Attach(item);

            }

            context.Entry(item).State = System.Data.EntityState.Deleted;

            context.SaveChanges();

        }

 

        private PropertyInfo GetDbSet(Type itemType)

        {

            var properties = typeof(TContext).GetProperties().Where(item => item.PropertyType.Equals(typeof(DbSet<>).MakeGenericType(itemType)));

 

            return properties.First();

        }

 

    }

}

 

Here is sample code that uses this repository:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

namespace RepositoryEFCodeFirst

{

    class Program

    {

        static void Main(string[] args)

        {

            using (EFRepository<ProductContext> repository = new EFRepository<ProductContext>("ProductConnection"))

            {

                Console.WriteLine("Total products in DB : " +

                    repository.Select<Product>().Count().ToString());

 

                Product newProduct = new Product();

                newProduct.ProducName = "Plates";

                newProduct.ProducNumber = "001";

                newProduct.Notes = "SOme notes for plates";

                newProduct.IsActive = true;

 

                newProduct = repository.Insert<Product>(newProduct);

 

                var ordered = repository.Select<Product>().Where(one => one.IsActive == true).OrderBy(one => one.ProducNumber).ToList();

 

                Console.WriteLine("New id is " + newProduct.ProductId.ToString());

 

 

                var aProduct = repository.Select<Product>().Where(one => one.IsActive).First();

                aProduct.ProducName = "Updated Plates";

 

                aProduct = repository.Update<Product>(aProduct);

 

                aProduct = repository.Select<Product>().Where(one => one.ProductId == newProduct.ProductId).First();

                Console.WriteLine("Update name is: " + aProduct.ProducName.ToString());

 

                repository.Delete<Product>(aProduct);

 

            }

        }

    }

}

 

I captured the result of the query (in bold) in profiler of my select with where and order by to ensure the operation takes place on the server.  Here is the query.

SELECT

[Extent1].[ProductId] AS [ProductId],

[Extent1].[ProducNumber] AS [ProducNumber],

[Extent1].[ProducName] AS [ProducName],

[Extent1].[Notes] AS [Notes],

[Extent1].[IsActive] AS [IsActive]

FROM [dbo].[Products] AS [Extent1]

WHERE 1 = [Extent1].[IsActive]

ORDER BY [Extent1].[ProducNumber] ASC

This concludes sample repository pattern implementation for Entity Framework Code First.  On a side note, I have not been blogging quite as much.  As I found out, spending 2-3 hours in the car every day going to/from work is not very conducive to blogging Smile.

Thanks.

Post to Twitter

Hosting WCF Data Services on Third Party Hosting Provider

Today I am going to talk about how to create a WCF Data Service and host it using a hosting provider.  My specific example will be using GoDaddy (www.GoDaddy.com).  This is the site where I host my blog.  I already posted on this topic, but was using Azure instead.  If you have a subscription to SQL Azure, you can sign up for WCF Data Service option with a single click (https://www.sqlazurelabs.com/).

The reason I decided to write this post was the fact that I got a question from one of the users of my Windows Phone database CodePlex project.  This developer was trying to speed up a query that was doing full-text search on 30,000 + records on Windows Phone.  My suggestion was to host this data in the cloud instead because then you can use full power of the SQL Server (or other RDBMS) to speed up the data retrieval.

First step is to sign up for the database on GoDaddy.  Most packages they sell offer a database.  My package came up with 1 SQL Server database and 10 MySql databases.  Also, I configured my web site on GoDaddy to use ASP.NET 4.0.  I need to use .NET 4 to get support for Data Services.  I could use either SQL Server of MySql, but will illustrate SQL Server in this post.  Once I created database, I was able to use SSQM (SQL Server Management Studio) to connect to the DB server and add tables to my database. Then, I am going to start on WCF Data Service. 

First of all, I am going to create new empty web application project by going to New Project in VS 2010.

image

Then, I am going to use Entity Framework to create a new model.  To do so I am going to add new item and select ADO.NET Entity Data model.

image

I am going to choose the option to generate from the existing database in the wizard, and I am going to select only the tables I need in the mode.  To connect to SQL Server on GoDaddy I needed to write down server name in the control panel on hosting option on GoDaddy.  I input all the settings into the data model wizard after clicking on New Connection button.

image

Then I am presented with my brand spanking new entity model.

image

Now I need to create WCF Data Service.  To do so, I am going to add new item to my web project and select WCF Data Service this time.  Then I put my simple rule into the service definition, allowing all access only to the tables in my model of course.

namespace CompanyWeb
{
   
public class CompanyWcfDataService : DataService<CompanyEntities
>
    {
       
public static void InitializeService(DataServiceConfiguration
config)
        {
            config.SetEntitySetAccessRule(
"*", EntitySetRights
.All);
            config.SetServiceOperationAccessRule(
"*", ServiceOperationRights
.All);
            config.DataServiceBehavior.MaxProtocolVersion =
DataServiceProtocolVersion.V2;
        }
    }
}

 

So far so good.  Now am I am going to test my setup locally by browsing to /CompanyWcfDataService.svc/ file in my project.  Just right click on it and select Browse.  Then I am going to add ContactRoles to the end of the Url and test that.I am getting the data.  I will get Atom Pub page similar to any RSS feed.  I am just looking for number of records those, since I

Now I need to deploy the web project.  First, use content –> IIS menu to create new folder and mark it as application root as well as enable Anonymous authentication. I am going then to use file manager in GoDaddy control panel to create a folder on my web site and copy all the files for my web project there.  Alternatively you can use FTP.  All I would need is to transfer bin folder, .SVC file and web.config.  Here is the final version of web.config:

<?xml version="1.0" encoding="utf-8"?>
<!–

  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
 
–>
<
configuration
>
    <
system.web
>
        <
customErrors mode="Off"
/>
        <
compilation debug="true"
/>
    </
system.web
>
    <
system.webServer
>
        <
httpErrors errorMode="Detailed"
/>
        <
asp scriptErrorSentToBrowser="true"
/>
    </
system.webServer
>
    <
connectionStrings
>
        <
add name="CompanyEntities" connectionString="metadata=res://*/CompanyModel.csdl|res://*/CompanyModel.ssdl|res://*/CompanyModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=serverIP;Initial Catalog=DBName;Persist Security Info=True;User ID=UserID;Password=Password;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient"
/>
    </
connectionStrings
>
    <
system.serviceModel
>
        <
serviceHostingEnvironment aspNetCompatibilityEnabled="true"
>
            <
baseAddressPrefixFilters
>
                <
clear
/>
                <
add prefix="http://www.yoursite.com/yourservicefolder/"/>
            </
baseAddressPrefixFilters
>
        </
serviceHostingEnvironment
>
        <
behaviors
>
            <
serviceBehaviors
>
                <
behavior
>
                    <
serviceDebug includeExceptionDetailInFaults="true"
/>
                </
behavior
>
            </
serviceBehaviors
>
        </
behaviors
>
    </
system.serviceModel
>
</
configuration
>

 

A couple of things to point out.  I am using configuration under system.web to troubleshoot errors.  I have to update service hosting environment to configure prefix to filter out requests I need.  Behavior is also setup to easy in troubleshooting. 

When all said and done, I am testing the service in IQ using the address of my service http://www.yoursite.com/yourservicefolder/CompanyWcfDataService.svc/ContactRoles

Then I got an error – IIS specified authentication schemes ‘Basic, Anonymous’, but the binding only supports specification of exactly one authentication scheme. Valid authentication schemes are Digest, Negotiate, NTLM, Basic, or Anonymous. Change the IIS settings so that only a single authentication scheme is used. To solve this I had to call GoDaddy support to ask them to turn off basic authentication on my site.  That was the last step.

Now, you can simply add a reference to the new service in your Silverlight or phone application.  See my previous posts for details on the process.

A couple of final thoughts.

  • If you are transferring sensitive data, consider buying SSL certificate.
  • Use error configuration to troubleshoot deployment issues.
  • You can use MySQL database with entity framework, you just need to download appropriate provider.
  • Almost exact same process applies to traditional WCF Service.

Please let me know if you have any questions.

Post to Twitter

Enterprise Library Database Logging and TransactionScope

I have recently worked on a project that was using enterprise library logging capabilities.  Enterprise Libiary information can be viewed on pattern and practices web site.  These library includes a number of useful components,  The one I am going to cover here is database logging capabilities.

Logging features allows an application to be configured to log information about itself into a destination such as Windows event log, text file or database.  We decided to use database logging because of easy of access, less possibility of contention than text file and ability to easy report off this information.  To install logging  you just have to run a script to create and configure the database.  If you installed Enterprise Library, you will find the script in \Blocks\Logging\Src\DatabaseTraceListener\Scripts folder under the source code location which you will need to install as well.  Then, you can use configuration tool that ships with the library to configure it.  If you have application configuration file already part of your solution, you can right-click on it and choose Edit Enterprise Library Configuration menu.  Once, we completed those steps, we noticed that some errors where not logged.  What was strange is that I saw gaps in identity column values in log table.  This led me to believe that errors were logged, but not committed.  Upon further investigation, I found out that logging transactions where rolled back when application errors occurred.   Main reason for that was that our data access code was wrapped inside TransactionScope block.  This happen to be a feature that logging to database has – it does not opt out of ambient transaction scope.  This can be done by using overloaded constructor:

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
{

 

 

This setup allows the code to opt out of ambient transaction established with TransactionScope.

Next I am going to create a couple of my own classes to replace the ones that come with Enterprise Library database logging: FormattedDatabaseTraceListener and FormattedDatabaseTraceListenerData.  You need to replace both because they work in conjunction.  The only difference in FormattedDatabaseTraceListenerData is the creation of trace listener based on our new type:

 

using System;
using System.Configuration;
using System.Diagnostics;
using System.Linq.Expressions;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ContainerModel;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration.Design;
using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Logging.Configuration;
using
Microsoft.Practices.EnterpriseLibrary.Logging.Formatters;
 

namespace
Logging
{
   
/// <summary>
    /// Configuration object for a <see cref="FormattedDatabaseTraceListener"/>.
    /// </summary>
    [AddSateliteProviderCommand("connectionStrings", typeof(DatabaseSettings), "DefaultDatabase", "DatabaseInstanceName"
)]
   
public class FormattedDatabaseTraceListenerData : TraceListenerData
    {
       
private const string addCategoryStoredProcNameProperty = "addCategoryStoredProcName"
;
       
private const string databaseInstanceNameProperty = "databaseInstanceName"
;
       
private const string formatterNameProperty = "formatter"
;
       
private const string writeLogStoredProcNameProperty = "writeLogStoredProcName"
;
 
       
/// <summary>
        /// Initializes a <see cref="FormattedDatabaseTraceListenerData"/>.
        /// </summary>
        public
FormattedDatabaseTraceListenerData()
            :
base(typeof(FormattedDatabaseTraceListener
))
        {
           
this.ListenerDataType = typeof(FormattedDatabaseTraceListenerData
);
        }
 
       
/// <summary>
        /// Initializes a named instance of <see cref="FormattedDatabaseTraceListenerData"/> with
        /// name, stored procedure name, databse instance name, and formatter name.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <param name="writeLogStoredProcName">The stored procedure name for writing the log.</param>
        /// <param name="addCategoryStoredProcName">The stored procedure name for adding a category for this log.</param>
        /// <param name="databaseInstanceName">The database instance name.</param>
        /// <param name="formatterName">The formatter name.</param>       
        public FormattedDatabaseTraceListenerData(string
name,
                                                 
string
writeLogStoredProcName,
                                                 
string
addCategoryStoredProcName,
                                                 
string
databaseInstanceName,
                                                 
string
formatterName)
            :
this
(
                name,
                writeLogStoredProcName,
                addCategoryStoredProcName,
                databaseInstanceName,
                formatterName,
               
TraceOptions
.None,
               
SourceLevels
.All)
        {
        }
 
       
/// <summary>
        /// Initializes a named instance of <see cref="FormattedDatabaseTraceListenerData"/> with
        /// name, stored procedure name, databse instance name, and formatter name.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <param name="writeLogStoredProcName">The stored procedure name for writing the log.</param>
        /// <param name="addCategoryStoredProcName">The stored procedure name for adding a category for this log.</param>
        /// <param name="databaseInstanceName">The database instance name.</param>
        /// <param name="formatterName">The formatter name.</param>
        /// <param name="traceOutputOptions">The trace options.</param>
        /// <param name="filter">The filter to be applied</param>
        public FormattedDatabaseTraceListenerData(string
name,
                                                 
string
writeLogStoredProcName,
                                                 
string
addCategoryStoredProcName,
                                                 
string
databaseInstanceName,
                                                 
string
formatterName,
                                                 
TraceOptions
traceOutputOptions,
                                                 
SourceLevels
filter)
            :
base(name, typeof(FormattedDatabaseTraceListener
), traceOutputOptions, filter)
        {
            DatabaseInstanceName = databaseInstanceName;
            WriteLogStoredProcName = writeLogStoredProcName;
            AddCategoryStoredProcName = addCategoryStoredProcName;
            Formatter = formatterName;
        }
 
       
/// <summary>
        /// Gets and sets the database instance name.
        /// </summary>
        [ConfigurationProperty(databaseInstanceNameProperty, IsRequired = true
)]
       
public string
DatabaseInstanceName
        {
           
get { return (string)base
[databaseInstanceNameProperty]; }
           
set { base[databaseInstanceNameProperty] = value
; }
        }
 
       
/// <summary>
        /// Gets and sets the stored procedure name for writing the log.
        /// </summary>
        [ConfigurationProperty(writeLogStoredProcNameProperty, IsRequired = true, DefaultValue = "WriteLog"
)]
       
public string
WriteLogStoredProcName
        {
           
get { return (string)base
[writeLogStoredProcNameProperty]; }
           
set { base[writeLogStoredProcNameProperty] = value
; }
        }
 
       
/// <summary>
        /// Gets and sets the stored procedure name for adding a category for this log.
        /// </summary>
        [ConfigurationProperty(addCategoryStoredProcNameProperty, IsRequired = true, DefaultValue = "AddCategory"
)]
       
public string
AddCategoryStoredProcName
        {
           
get { return (string)base
[addCategoryStoredProcNameProperty]; }
           
set { base[addCategoryStoredProcNameProperty] = value
; }
        }
 
       
/// <summary>
        /// Gets and sets the formatter name.
        /// </summary>
        [ConfigurationProperty(formatterNameProperty, IsRequired = false
)]
       
public string
Formatter
        {
           
get { return (string)base
[formatterNameProperty]; }
           
set { base[formatterNameProperty] = value
; }
        }
 
       
/// <summary>
        /// Returns a lambda expression that represents the creation of the trace listener described by this
        /// configuration object.
        /// </summary>
        /// <returns>A lambda expression to create a trace listener.</returns>
        protected override Expression<Func<TraceListener
>> GetCreationExpression()
        {
           
return
() =>
                  
new FormattedDatabaseTraceListener
(
                      
Container.Resolved<Microsoft.Practices.EnterpriseLibrary.Data.Database
>(DatabaseInstanceName),
                       WriteLogStoredProcName,
                       AddCategoryStoredProcName,
                      
Container.ResolvedIfNotNull<ILogFormatter>(Formatter));
        }
    }
}

 

I kept the rest of the code the same.  Listener itself has more changes, specifically, I am wrapping all database access code with new transaction scope:

using System;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Globalization;
using System.Transactions;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Logging;
using Microsoft.Practices.EnterpriseLibrary.Logging.Formatters;
using
Microsoft.Practices.EnterpriseLibrary.Logging.TraceListeners;
 

namespace
Logging
{
   
/// <summary>
    /// A <see cref="System.Diagnostics.TraceListener"/> that writes to a database, formatting the output with an <see cref="ILogFormatter"/>.
    /// </summary>
    [ConfigurationElementType(typeof(FormattedDatabaseTraceListenerData
))]
   
public class FormattedDatabaseTraceListener : FormattedTraceListenerBase
    {
       
string writeLogStoredProcName = String
.Empty;
       
string addCategoryStoredProcName = String
.Empty;
        Microsoft.Practices.EnterpriseLibrary.Data.
Database
database;
 
       
/// <summary>
        /// Initializes a new instance of <see cref="FormattedDatabaseTraceListener"/>.
        /// </summary>
        /// <param name="database">The database for writing the log.</param>
        /// <param name="writeLogStoredProcName">The stored procedure name for writing the log.</param>
        /// <param name="addCategoryStoredProcName">The stored procedure name for adding a category for this log.</param>
        /// <param name="formatter">The formatter.</param>       
        public
FormattedDatabaseTraceListener(
            Microsoft.Practices.EnterpriseLibrary.Data.
Database
database,
           
string
writeLogStoredProcName,
           
string
addCategoryStoredProcName,
           
ILogFormatter
formatter)
            :
base
(formatter)
        {
           
this
.writeLogStoredProcName = writeLogStoredProcName;
           
this
.addCategoryStoredProcName = addCategoryStoredProcName;
           
this
.database = database;
        }
 
       
/// <summary>
        /// The Write method
        /// </summary>
        /// <param name="message">The message to log</param>
        public override void Write(string
message)
        {
           
using (TransactionScope scope = new TransactionScope(TransactionScopeOption
.Suppress))
            {
               
using (DbConnection
connection = database.CreateConnection())
                {
                    connection.Open();
                   
try
                    {
                       
using (DbTransaction
transaction = connection.BeginTransaction())
                        {
                           
try
                            {
                                ExecuteWriteLogStoredProcedure(0, 5,
TraceEventType.Information, string.Empty, DateTime.Now, string
.Empty,
                                           
string.Empty, string.Empty, string.Empty, null, null
, message, database);
                                transaction.Commit();
                            }
                           
catch
                            {
                                transaction.Rollback();
                               
throw
;
                            }
                        }
                    }
                   
finally
                    {
                        connection.Close();
                    }
                }
                scope.Complete();
            }
           
        }
 
       
/// <summary>
        /// The WriteLine method.
        /// </summary>
        /// <param name="message">The message to log</param>
        public override void WriteLine(string
message)
        {
            Write(message);
        }
 
 
       
/// <summary>
        /// Delivers the trace data to the underlying database.
        /// </summary>
        /// <param name="eventCache">The context information provided by <see cref="System.Diagnostics"/>.</param>
        /// <param name="source">The name of the trace source that delivered the trace data.</param>
        /// <param name="eventType">The type of event.</param>
        /// <param name="id">The id of the event.</param>
        /// <param name="data">The data to trace.</param>
        public override void TraceData(TraceEventCache eventCache, string source, TraceEventType eventType, int id, object
data)
        {
           
if ((this.Filter == null) || this.Filter.ShouldTrace(eventCache, source, eventType, id, null, null, data, null
))
            {
               
if (data is LogEntry
)
                {
                   
LogEntry logEntry = data as LogEntry
;
                   
if
(ValidateParameters(logEntry))
                        ExecuteStoredProcedure(logEntry);
                }
               
else if (data is string
)
                {
                    Write(data
as string
);
                }
               
else
                {
                   
base
.TraceData(eventCache, source, eventType, id, data);
                }
            }
        }
 
       
/// <summary>
        /// Declare the supported attributes for <see cref="FormattedDatabaseTraceListener"/>
        /// </summary>
        protected override string
[] GetSupportedAttributes()
        {
           
return new string[4] { "formatter", "writeLogStoredProcName", "addCategoryStoredProcName", "databaseInstanceName"
};
        }
 
       
/// <summary>
        /// Validates that enough information exists to attempt executing the stored procedures
        /// </summary>
        /// <param name="logEntry">The LogEntry to validate.</param>
        /// <returns>A Boolean indicating whether the parameters for the LogEntry configuration are valid.</returns>
        private bool ValidateParameters(LogEntry
logEntry)
        {
           
bool valid = true
;
 
           
if (writeLogStoredProcName == null
||
                writeLogStoredProcName.Length == 0)
            {
               
return false
;
            }
 
           
if (addCategoryStoredProcName == null
||
                addCategoryStoredProcName.Length == 0)
            {
               
return false
;
            }
 
           
return
valid;
        }
 
       
/// <summary>
        /// Executes the stored procedures
        /// </summary>
        /// <param name="logEntry">The LogEntry to store in the database</param>
        private void ExecuteStoredProcedure(LogEntry
logEntry)
        {
           
using (TransactionScope scope = new TransactionScope(TransactionScopeOption
.Suppress))
            {
               
using (DbConnection
connection = database.CreateConnection())
                {
                    connection.Open();
                   
try
                    {
                       
using (DbTransaction
transaction = connection.BeginTransaction())
                        {
                           
try
                            {
                               
int logID = Convert
.ToInt32(ExecuteWriteLogStoredProcedure(logEntry, database, transaction));
                                ExecuteAddCategoryStoredProcedure(logEntry, logID, database, transaction);
                                transaction.Commit();
                            }
                           
catch
                            {
                                transaction.Rollback();
                               
throw
;
                            }
 
                        }
                    }
                   
finally
                    {
                        connection.Close();
                    }
                }
                scope.Complete();
            }
        }
 
       
/// <summary>
        /// Executes the WriteLog stored procedure
        /// </summary>
        /// <param name="eventId">The event id for this LogEntry.</param>
        /// <param name="priority">The priority for this LogEntry.</param>
        /// <param name="severity">The severity for this LogEntry.</param>
        /// <param name="title">The title for this LogEntry.</param>
        /// <param name="timeStamp">The timestamp for this LogEntry.</param>
        /// <param name="machineName">The machine name for this LogEntry.</param>
        /// <param name="appDomainName">The appDomainName for this LogEntry.</param>
        /// <param name="processId">The process id for this LogEntry.</param>
        /// <param name="processName">The processName for this LogEntry.</param>
        /// <param name="managedThreadName">The managedthreadName for this LogEntry.</param>
        /// <param name="win32ThreadId">The win32threadID for this LogEntry.</param>
        /// <param name="message">The message for this LogEntry.</param>
        /// <param name="db">An instance of the database class to use for storing the LogEntry</param>
        /// <returns>An integer for the LogEntry Id</returns>
        private int ExecuteWriteLogStoredProcedure(int eventId, int priority, TraceEventType severity, string title, DateTime
timeStamp,
                                                   
string machineName, string appDomainName, string processId, string
processName,
                                                   
string managedThreadName, string win32ThreadId, string message, Microsoft.Practices.EnterpriseLibrary.Data.Database
db)
        {
           
using (TransactionScope scope = new TransactionScope(TransactionScopeOption
.Suppress))
            {
               
DbCommand
cmd = db.GetStoredProcCommand(writeLogStoredProcName);
 
                db.AddInParameter(cmd,
"eventID", DbType
.Int32, eventId);
                db.AddInParameter(cmd,
"priority", DbType
.Int32, priority);
                db.AddParameter(cmd,
"severity", DbType.String, 32, ParameterDirection.Input, false, 0, 0, null, DataRowVersion
.Default, severity.ToString());
                db.AddParameter(cmd,
"title", DbType.String, 256, ParameterDirection.Input, false, 0, 0, null, DataRowVersion
.Default, title);
                db.AddInParameter(cmd,
"timestamp", DbType
.DateTime, timeStamp);
                db.AddParameter(cmd,
"machineName", DbType.String, 32, ParameterDirection.Input, false, 0, 0, null, DataRowVersion
.Default, machineName);
                db.AddParameter(cmd,
"AppDomainName", DbType.String, 512, ParameterDirection.Input, false, 0, 0, null, DataRowVersion
.Default, appDomainName);
                db.AddParameter(cmd,
"ProcessID", DbType.String, 256, ParameterDirection.Input, false, 0, 0, null, DataRowVersion
.Default, processId);
                db.AddParameter(cmd,
"ProcessName", DbType.String, 512, ParameterDirection.Input, false, 0, 0, null, DataRowVersion
.Default, processName);
                db.AddParameter(cmd,
"ThreadName", DbType.String, 512, ParameterDirection.Input, false, 0, 0, null, DataRowVersion
.Default, managedThreadName);
                db.AddParameter(cmd,
"Win32ThreadId", DbType.String, 128, ParameterDirection.Input, false, 0, 0, null, DataRowVersion
.Default, win32ThreadId);
                db.AddParameter(cmd,
"message", DbType.String, 1500, ParameterDirection.Input, false, 0, 0, null, DataRowVersion
.Default, message);
                db.AddInParameter(cmd,
"formattedmessage", DbType
.String, message);
 
                db.AddOutParameter(cmd,
"LogId", DbType
.Int32, 4);
 
                db.ExecuteNonQuery(cmd);
               
int logId = Convert.ToInt32(cmd.Parameters[cmd.Parameters.Count - 1].Value, CultureInfo
.InvariantCulture);
                scope.Complete();
               
return
logId;
            }
        }
 
       
/// <summary>
        /// Executes the WriteLog stored procedure
        /// </summary>
        /// <param name="logEntry">The LogEntry to store in the database.</param>
        /// <param name="db">An instance of the database class to use for storing the LogEntry</param>
        /// <param name="transaction">The transaction that wraps around the execution calls for storing the LogEntry</param>
        /// <returns>An integer for the LogEntry Id</returns>
        private int ExecuteWriteLogStoredProcedure(LogEntry logEntry, Microsoft.Practices.EnterpriseLibrary.Data.Database db, DbTransaction
transaction)
        {
           
using (TransactionScope scope = new TransactionScope(TransactionScopeOption
.Suppress))
            {
               
DbCommand
cmd = db.GetStoredProcCommand(writeLogStoredProcName);
 
 
                db.AddInParameter(cmd,
"eventID", DbType
.Int32, logEntry.EventId);
                db.AddInParameter(cmd,
"priority", DbType
.Int32, logEntry.Priority);
                db.AddParameter(cmd,
"severity", DbType.String, 32, ParameterDirection.Input, false, 0, 0, null, DataRowVersion
.Default, logEntry.Severity.ToString());
                db.AddParameter(cmd,
"title", DbType.String, 256, ParameterDirection.Input, false, 0, 0, null, DataRowVersion
.Default, logEntry.Title);
                db.AddInParameter(cmd,
"timestamp", DbType
.DateTime, logEntry.TimeStamp);
                db.AddParameter(cmd,
"machineName", DbType.String, 32, ParameterDirection.Input, false, 0, 0, null, DataRowVersion
.Default, logEntry.MachineName);
                db.AddParameter(cmd,
"AppDomainName", DbType.String, 512, ParameterDirection.Input, false, 0, 0, null, DataRowVersion
.Default, logEntry.AppDomainName);
                db.AddParameter(cmd,
"ProcessID", DbType.String, 256, ParameterDirection.Input, false, 0, 0, null, DataRowVersion
.Default, logEntry.ProcessId);
                db.AddParameter(cmd,
"ProcessName", DbType.String, 512, ParameterDirection.Input, false, 0, 0, null, DataRowVersion
.Default, logEntry.ProcessName);
                db.AddParameter(cmd,
"ThreadName", DbType.String, 512, ParameterDirection.Input, false, 0, 0, null, DataRowVersion
.Default, logEntry.ManagedThreadName);
                db.AddParameter(cmd,
"Win32ThreadId", DbType.String, 128, ParameterDirection.Input, false, 0, 0, null, DataRowVersion
.Default, logEntry.Win32ThreadId);
                db.AddParameter(cmd,
"message", DbType.String, 1500, ParameterDirection.Input, false, 0, 0, null, DataRowVersion
.Default, logEntry.Message);
 
               
if (Formatter != null
)
                    db.AddInParameter(cmd,
"formattedmessage", DbType
.String, Formatter.Format(logEntry));
               
else
                    db.AddInParameter(cmd, "formattedmessage", DbType
.String, logEntry.Message);
 
                db.AddOutParameter(cmd,
"LogId", DbType
.Int32, 4);
 
                db.ExecuteNonQuery(cmd, transaction);
               
int logId = Convert.ToInt32(cmd.Parameters[cmd.Parameters.Count - 1].Value, CultureInfo
.InvariantCulture);
                scope.Complete();
               
return
logId;
               
            }
        }
 
       
/// <summary>
        /// Executes the AddCategory stored procedure
        /// </summary>
        /// <param name="logEntry">The LogEntry to store in the database.</param>
        /// <param name="logID">The unique identifer for the LogEntry as obtained from the WriteLog Stored procedure.</param>
        /// <param name="db">An instance of the database class to use for storing the LogEntry</param>
        /// <param name="transaction">The transaction that wraps around the execution calls for storing the LogEntry</param>
        private void ExecuteAddCategoryStoredProcedure(LogEntry logEntry, int logID, Microsoft.Practices.EnterpriseLibrary.Data.Database db, DbTransaction
transaction)
        {
           
using (TransactionScope scope = new TransactionScope(TransactionScopeOption
.Suppress))
            {
               
foreach (string category in
logEntry.Categories)
                {
                   
DbCommand
cmd = db.GetStoredProcCommand(addCategoryStoredProcName);
                    db.AddInParameter(cmd,
"categoryName", DbType
.String, category);
                    db.AddInParameter(cmd,
"logID", DbType.Int32, logID);
                    db.ExecuteNonQuery(cmd, transaction);
                }
                scope.Complete();
            }
        }
    }
}

 

The last step is to replace preloaded listener types with our own as in below:

<?xml version="1.0" encoding="utf-8" ?>
<
configuration
>
    <
configSections
>
        <
section name="loggingConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.LoggingSettings, Microsoft.Practices.EnterpriseLibrary.Logging, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="true"
/>
        <
section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="true"
/>
    </
configSections
>
    <
loggingConfiguration name="" tracingEnabled="true" defaultCategory="General"
>
        <
listeners
>
            <
add name="Database Trace Listener"
                type="Logging.FormattedDatabaseTraceListener, Logging, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"
                istenerDataType="Logging.FormattedDatabaseTraceListenerData, Logging, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"
                databaseInstanceName="Logging"
                writeLogStoredProcName="WriteLog"
                addCategoryStoredProcName="AddCategory"
/>
        </
listeners
>
        <
formatters
>
            <
add type="Microsoft.Practices.EnterpriseLibrary.Logging.Formatters.TextFormatter, Microsoft.Practices.EnterpriseLibrary.Logging, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
                template="Timestamp: {timestamp}{newline}&#xA;Message: {message}{newline}&#xA;Category: {category}{newline}&#xA;Priority: {priority}{newline}&#xA;EventId: {eventid}{newline}&#xA;Severity: {severity}{newline}&#xA;Title:{title}{newline}&#xA;Machine: {localMachine}{newline}&#xA;App Domain: {localAppDomain}{newline}&#xA;ProcessId: {localProcessId}{newline}&#xA;Process Name: {localProcessName}{newline}&#xA;Thread Name: {threadName}{newline}&#xA;Win32 ThreadId:{win32ThreadId}{newline}&#xA;Extended Properties: {dictionary({key} – {value}{newline})}"
                name="Text Formatter"
/>
        </
formatters
>
        <
categorySources
>
            <
add switchValue="All" name="General"
>
                <
listeners
>
                    <
add name="Database Trace Listener"
/>
                </
listeners
>
            </
add
>
        </
categorySources
>
        <
specialSources
>
            <
allEvents switchValue="All" name="All Events"
/>
            <
notProcessed switchValue="All" name="Unprocessed Category"
/>
            <
errors switchValue="All" name="Logging Errors &amp; Warnings"
>
                <
listeners
>
                    <
add name="Database Trace Listener"
/>
                </
listeners
>
            </
errors
>
        </
specialSources
>
    </
loggingConfiguration
>
    <
dataConfiguration defaultDatabase="Logging"
/>
    <
connectionStrings
>
        <
add name="Logging" connectionString="Logging" providerName="System.Data.SqlClient"
/>
    </
connectionStrings
>
</
configuration
>

 

That is all there is to using Enterprise Library database logging within existing transaction scope.  You can download the project here.  You will need to download and install Enterprise Library to build it.

Post to Twitter

Working with Data on Windows Phone 7 – Part 2

In this part I am going to use WCF Data Services (formerly known as project Astoria and ADO.NET Data Services) to accomplish the same couple of tasks I did in WCF custom service.  I am going to get a list of people and add a person.

I will use exact same project as I did the last time.  As you recall, I created a database with one table using EF Code First.  This is where I am going to start.  In first step I am going to create an Entity Framework model from that database.  To do so, I am going to add new item to my web project and select Entity Framework model.

image

I will then walk through EF model wizard to generate the model from my database:

image

image

 

image

This will generate my model with my person class.

WCF Data Service will expose my model via web RESTful interfaces.  To add the service, I am going to add new item again, but now select WCF Data Service as item type:

 

image

Next step is to configure WCF Data Service to use my entity model.  This step is pretty easy, since service is a generic type with a parameter that takes entity model

    public class PersonWcfDataService : DataService<PersonsDbEntities>
    {
       
public static void InitializeService(DataServiceConfiguration
config)
        {
            config.SetEntitySetAccessRule(
"*", EntitySetRights
.All);
            config.DataServiceBehavior.MaxProtocolVersion =
DataServiceProtocolVersion.V2;
        }
    }

 

In the code above I am setting rights to all entities to everyone.  In production I would not do this, but it works as illustration.  In my next step I am going to test the service.  I am going to right click on PersonWcfDataService.svc file that was generated in the step above and select View In Browser.

image

Next, I want to make sure I can get the list of people.  I am going to change my Url and test that.  You can see the address and result below:

image

Of course the results look like Atom feed since OData that WCF Data Service implement extends AtomPub protocol.  I can look at the data by selecting view source.  The data looks as following:

image

Next I need to generate proxy for my Phone application.  Unfortunately, add service reference does not work for phone Silverlight applications.  So, I am going to download necessary files from CodePlex to proceed.

Visit http://odata.codeplex.com and download ODataClient_BinariesAndCodeGenToolForWinPhone.zip.  Once it is on your local machine, unblock the zip file in windows properties, otherwise you will get compilation errors later.  Unzip it into a blank folder to make it easy to find later.

Here is the content of the folder:

image

Add a reference to System.Data.Services.Client.dll to your windows phone project.  Now, we need to generate proxy and related classes.  To do so we will use DataSvcUtil.exe from the same folder.  Here is the command line I am using:

 

datasvcutil /uri:http://localhost:50406/PersonWcfDataService.svc/ /out:.\PersonModel.cs /DataServiceCollection /Version:2.0

As you can see I am providing my svc file’s url.  I am using version 2 of the data services.  I am outputting the proxy into PersonModel.cs file.  DataServiceCollection parameter instructs the utility to generate collections that derive from DataSericeCollection.  Next I am include this file in my project.

Next I am going to create an instance of the client in my view model as following:

PersonsDbEntities odataClient;

odataClient =

         new PersonsDbEntities(new Uri(@"http://localhost:50406/PersonWcfDataService.svc"));

 

Of course in production you need to give correct Url.  Next, I am going to query the data.  First I am going to define a property to hold my collection


        private DataServiceCollection<PersonsDbModel.Person> oDataPeople;
 
       
public DataServiceCollection<PersonsDbModel.Person
> ODataPeople
        {
           
get { return
oDataPeople; }
           
set
            {
                oDataPeople =
value
;
                NotifyPropertyChanged(
"ODataPeople");
            }
        }

 

Then I am going to query the data as following

            ODataPeople = new DataServiceCollection<PersonsDbModel.Person>(odataClient);
            ODataPeople.LoadCompleted += ODataPeople_LoadCompleted;
 
           
try
            {
                ODataPeople.LoadAsync(peopleUri);
            }
           
catch (DataServiceClientException
ex)
            {
               
MessageBox.Show(ex.ToString());
            }

 

So, first I create a collection and tie it to my client.  Without that I can query the collection.  In the callback I check for errors:

        void ODataPeople_LoadCompleted(object sender, LoadCompletedEventArgs e)
        {
           
if (e.QueryOperationResponse.Error != null
)
            {
               
MessageBox.Show(e.QueryOperationResponse.Error.ToString());
            }
        }

 

That I it.  Now I just add a listbox to my UI and bind it to this collection:

            <controls:PivotItem Header="oData">
                <!–Double line list with text wrapping–>
                <ListBox x:Name="ODataListBox" Margin="0,0,-12,0"
 
                       
ItemsSource="{Binding ODataPeople}">
                    <ListBox.ItemTemplate>
                        <DataTemplate>
                            <StackPanel Margin="0,0,0,17" Width="432">
                                <TextBlock
 
                                
Text="{Binding FirstName}"
 
                                
TextWrapping="Wrap"
 
                                
Style="{StaticResource PhoneTextExtraLargeStyle}"/>
                                <TextBlock
 
                                
Text="{Binding LastName}"
 
                                
TextWrapping="Wrap"
 
                                
Margin="12,-6,12,0"
 
                                
Style="{StaticResource PhoneTextSubtleStyle}"/>
                            </StackPanel>
                        </DataTemplate>
                    </ListBox.ItemTemplate>
                </ListBox>
            </controls:PivotItem
>

 

Next, adding a person via OData.  It is really simple.  I just add new person to my collection and start the save process.  DataSericeCollection  objects are smart to keep track of the changes that occur during operation on the server.


        public void InsertPersonOdata()
        {
            ODataPeople.Add(
new PersonsDbModel.Person() { FirstName = "James", LastName = "Homer", PersonID = Guid
.NewGuid() });
            odataClient.BeginSaveChanges(
SaveChangesOptions
.Batch, (ar) =>
            {
               
Deployment
.Current.Dispatcher.BeginInvoke(() =>
                {
                    odataClient = ar.AsyncState
as PersonsDbModel.PersonsDbEntities
;
                   
DataServiceResponse
response = odataClient.EndSaveChanges(ar);
                   
if
(response.IsBatchResponse)
                    {
                       
StringBuilder errors = new StringBuilder
();
                       
foreach (ChangeOperationResponse change in
response)
                        {
                           
if (change.Error != null
)
                            {
                                errors.Append(change.Error.ToString());
                            }
                        }
                       
if
(errors.Length == 0)
                        {
                           
MessageBox.Show("Success"
);
                        }
                       
else
                        {
                           
MessageBox.Show(errors.ToString());
                        }
                    }
                });
            }, odataClient);
        }

 

You can download the entire solution for both parts 1 and 2 here.  There does seem to be some flakiness in running both services from one solution, so you may have to comment WCF Service or OData service calls to test each portion.

Post to Twitter

Quick Start Guide for Windows Phone 7 Database

I have a CodePlex project for Isolated Storage based database for Windows phone 7.  You can check out this project at http://winphone7db.codeplex.com/

 

First step is to download the project and build it on your copter.  Visit the download page for the latest recommended download.  Unzip the source code on your machine, open it up in Visual Studio 2010 and compile to create a DLL.

Next, create new Windows Phone 7 project and add a reference to the DLL from step 1.

Next, in your View Model (or elsewhere in your application) check for database existence and create if it does not exist along with tables.  See sample code below

            if (!Database.DoesDatabaseExists(DatabaseName))
            {
                database =
Database
.CreateDatabase(DatabaseName);
                database.CreateTable<
Person
>();
                database.CreateTable<
PersonDetail
>();
                database.Save();
            }
           
else
            {
                database =
Database.OpenDatabase(DatabaseName, string.Empty, true);
            }

In the sample above I am testing if database exists, then create it if it does not,  Then I am adding two tables to it based on POCO classes.  For example, Person class would look like

    public class Person
    {
       
public Guid PersonID { get; set
; }
       
public string FirstName { get; set
; }
       
public string LasstName { get; set; }
    }

 

If you would like to add a row to a table, just issue Add command:

db.Table<Person>().Add(NewPerson());

 

New Person routine just returns Person instance.

To remove, just issue remove command:

 

db.Table<Person>().Remove(person2);

 

You can also remove and add a range of items based on condition.

db.Table<Person>().RemoveRange((person) => { return (person.Salary >= 2); });

 

You can also add a range of items, specifically IEnumerable<T>

db.Table<Person>().AddRange(list);

 

Make sure to call Save() on either database or a specific table to commit your changes.

You can also look in unit test project that is distributed as part of the source code download for other API samples.

Post to Twitter

Building Windows Phone 7 Silverlight Application

It this post I want to describe the process I went though building and publishing my first Silverlight application for Windows Phone 7.  It is currently being tested, and I hope I will pass the test this time.  I went through a couple of iterations of testing already, learning in the process from my mistakes.  I want to describe the process so that I myself can remember the steps, but hopefully help other folks with some tips and tricks.

In order to be able to publish an application for Windows Phone 7, you must sign up as a developer on App Hub – http://create.msdn.com.  This process was pretty painless actually, and only took a few minutes.  A couple of important things to remember.  Once you sign up, you will get an email from GeoTrust.  You have to follow the link in the email and answer a few questions confirming your identity.  The questions for me came from my credit report.  This tells me that Microsoft really wants to ensure there is a real person behind the signee.  Once you complete this step, you have to go back to App Hub and fill out your bank account information.  Once you are done with paperwork, you are ready to start writing your application.

To create an app, you have to have some tools installed on your machine.  You have to have at lest Visual Studio Express for Windows Phone, but I would also recommend Expression Blend for Windows Phone 7.  You can find the links to the software on main page of App Hub.

Once you have basic tools, you will likely need some secondary software to help you write your applications.  Here is what I would recommend.

  • Windows Phone 7 Toolkit from CodePlex.  Controls in the toolkit are written by Microsoft folks just like Silverlight toolkit.  You can download the toolkit here  To help you get started, check out Tim Heuer’s post on the toolkit here.
  • Another good tool is version of Prism for Windows Phone 7.  This article on MSDN describes the content of Prism for the phone.  You can download binaries here.  Once you build Prism 4, you will find Phone folder under Bin with all the binaries.  There is a lot of cool functionality there.
  • I had some troubles getting commanding to work using blend using event to command behavior.  As a result, I also downloaded and used MVVM Light by Laurent Bugnion.  You can download binaries and find some quick start links on CodePlex here.
  • If you want to save some data on the phone, I would recommend using a database abstraction by your truly.  You can download Database for Windows Phone 7 here.

Now, some sample code.

Application bar is the portion of the screen that shows the menu.  I am using Prism to implement commands to react to user interactions.  Here is XAML portion of the application bar.

<phone:PhoneApplicationPage
   xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
   xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
   xmlns:phone="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone"
   xmlns:shell="clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone"
   xmlns:controls="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone.Controls"
   xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
   xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
 
  
xmlns:wi="clr-namespace:System.Windows.Interactivity;assembly=System.Windows.Interactivity"
    
  
xmlns:i="clr-namespace:Microsoft.Practices.Prism.Interactivity;assembly=Microsoft.Practices.Prism.Interactivity"
 
  
xmlns:toolkit="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone.Controls.Toolkit"
   xmlns:mvvmLight="clr-namespace:GalaSoft.MvvmLight.Command;assembly=GalaSoft.MvvmLight.Extras.WP7"
   xmlns:charting="clr-namespace:System.Windows.Controls.DataVisualization.Charting;

assembly=System.Windows.Controls.DataVisualization.Toolkit"
   x:Class="SportTimer.MainPage"
 
  
mc:Ignorable="d" d:DesignWidth="480" d:DesignHeight="696"
 
  
FontFamily="{StaticResource PhoneFontFamilyNormal}"
   FontSize="{StaticResource PhoneFontSizeNormal}"
   Foreground="{StaticResource PhoneForegroundBrush}"
   SupportedOrientations="Portrait"  Orientation="Portrait"
   shell:SystemTray.IsVisible="True">
 
    <!–Sample code showing usage of ApplicationBar–>
    <phone:PhoneApplicationPage.ApplicationBar>
        <shell:ApplicationBar IsVisible="True" IsMenuEnabled="True">
            <shell:ApplicationBarIconButton IconUri="/icons/clock.png" Text="time" />
            <shell:ApplicationBarIconButton IconUri="/icons/person.png" Text="people"/>
            <shell:ApplicationBarIconButton IconUri="/icons/event.png" Text="events"/>
            <shell:ApplicationBarIconButton IconUri="/icons/graph.png" Text="history"/>
        </shell:ApplicationBar>
    </phone:PhoneApplicationPage.ApplicationBar>
 
    <wi:Interaction.Behaviors>
        <i:ApplicationBarButtonCommand ButtonText="time" 

CommandBinding="{Binding ChangeIndexCommand, Mode=OneWay}" 

CommandParameterBinding="{Binding TimeIndex}"/>
        <i:ApplicationBarButtonCommand ButtonText="people" 

CommandBinding="{Binding ChangeIndexCommand, Mode=OneWay}" 

CommandParameterBinding="{Binding PeopleIndex}"/>
        <i:ApplicationBarButtonCommand ButtonText="events" 

CommandBinding="{Binding ChangeIndexCommand, Mode=OneWay}" 

CommandParameterBinding="{Binding EventIndex}"/>
        <i:ApplicationBarButtonCommand ButtonText="history" 

CommandBinding="{Binding ChangeIndexCommand, Mode=OneWay}" 

CommandParameterBinding="{Binding HisotryIndex}"/>
    </wi:Interaction.Behaviors>
    <!–LayoutRoot is the root grid where all page content is placed–>
    <Grid x:Name="LayoutRoot" Background
="Transparent">

 

Let me comment a bot on the code.  Application bar can have at most 4 buttons, and I am using all of them.  I am declaring application bar button command separately which is how Prism wants the implementation done, then bind commands to buttons via Text property of the button. Commands are declared in my view model as follows

 

        public DelegateCommand<object> ChangeIndexCommand { get; set; }        public void OnChangeIndex(object parameter)        {            SelectedPivotIndex = int.Parse(parameter.ToString());        }

 

ChangeIndexCommand = new DelegateCommand<object>(OnChangeIndex);

I am creating an instance of the command in view model constructor.  The command simply changes selected index of the pivot control,

I also have some button controls in my application.  I use MVVM light for those, again because I could not get Prism behavior to work.

<Button Content="Stop" Grid.Row="3" Grid.Column="2">    <wi:Interaction.Triggers>        <wi:EventTrigger EventName="Click">            <mvvmLight:EventToCommand Command="{Binding Path=StopTimerCommand}" />        </wi:EventTrigger>    </wi:Interaction.Triggers></Button>

 

Another interesting control I use is ContextMenu from toolkit.  I am using it to delete items from ListBox:

<ListBox x:Name="FilteredHistoryListBox" Grid.Row="3" Grid.ColumnSpan="3"

           ItemsSource="{Binding Path=FilteredHistory}">

    <ListBox.ItemTemplate>

        <DataTemplate>

            <Border Margin="0" BorderThickness="0" Background="Transparent" Padding="0">

                <toolkit:ContextMenuService.ContextMenu>

                    <toolkit:ContextMenu>

                        <toolkit:MenuItem Header="Delete">

                            <wi:Interaction.Triggers>

                                <wi:EventTrigger EventName="Click">

                                    <mvvmLight:EventToCommand 

Command="{Binding ElementName=EventsItem, Path=DataContext.DeleteHistoryCommand, Mode=OneWay}" 

CommandParameter="{Binding}" />

                                </wi:EventTrigger>

                            </wi:Interaction.Triggers>

                        </toolkit:MenuItem>

                    </toolkit:ContextMenu>

                </toolkit:ContextMenuService.ContextMenu>

                <StackPanel Margin="0,0,0,7">

                    <TextBlock Text="{Binding Path=TimeString}"  

TextWrapping="NoWrap" Margin="12,0,0,0" Style="{StaticResource PhoneTextExtraLargeStyle}"/>

                    <TextBlock Text="{Binding Path=HistoryDateString}" TextWrapping="NoWrap" Margin="12,-6,0,0" Style="{StaticResource PhoneTextSubtleStyle}"/>

                </StackPanel>

            </Border>

        </DataTemplate>

    </ListBox.ItemTemplate>

 

 

Now, about application bar icons.  I was trying to use the icons from Visual Studio, and I failed the test.  Then, I actually read the documentation Smile

The application bar icons must be 48×48 pixels with transparent background, with the icon itself (image or text) white 24×24 pixels.  I found this very good post by Brad Tutterow, and I used it to create custom icons for my application.  Nothing fancy, but my skill were good enough to create four icons for my Sport Timer application.   Make sure to test your application in light theme either on the phone or emulator.  Here is my person icon and graph icon.

Persongraph

I wanted to incorporate graphs info my application, and I found this great port of chart library from Silverlight toolkit.  At least I think that is what it is.  Here is the link to the post about it that includes download link.

Another tip that I have is that for some reason virtual keyboard did not work on one of my screens.  Come to find out through trial and error that you must inherit for PhoneApplicationPage for that to work.  I used UserControl, following typical Silverlight development pattern.  Once I changed the inheritance in XAML and .cs files, everything worked like magic.

Also, you have to test your icons on the phone.  Make sure to pin your application to start up to ensure it looks good.  You can setup icons and test for start up page on the phone and regular list on the phone in properties of your application page.  Make sure to check these settings.

image

You can use the same image or different ones.  I followed the default application and used different sizes.  You can see the image size from the icon name.  All of them are images in png format.

When you submit your application, you are also required to submit a number of image in specific format.  I used Paint.NET to resize my images and set DPI.  You can do both through Resize command window.  Also, make sure the screenshots of your application do not include the emulator itself, only the application.  You will fail testing because of the this as well.  Again, follow the descriptions in submission page to verify the DPI and sizes of all your images.

I hope this post may save a few folks a few extra minutes.

Post to Twitter

Developing ASP.NET MVC 3 Application with EF CTP (Part 1)

I this post I will walk through steps of creating an ASP.NET MVC 3 application using new code first entity framework CTP as data access and business layer.  Just a few upfront comments.  Both technologies are in preview state right now, but will likely be released in first half of next year.

To get started we have to download and install them.  To make this simpler, I will use NuGet (formerly  NuPack) which is package management software for .NET applications.  You can find it here.  Visual Studio 2010 is required to follow along with the demo, by the way.  You can download ASP.NET MVC 3.0 beta here.

Now that it has been downloaded and installed, let’s create new project.

image

Next step to pick the view engine you want to use.  You can select either ASPX (ASP.NET) or new Razor engine that will ship with MVC 3.0 and new product – Web Matrix.

image

We will go ahead and also select to create Unit Test project.

What the wizard builds for us is empty site with home view and registration service that uses membership provider.  Let’s run the app to see if everything worked correctly.  We can open web.config and verify that the system is using ASP.NET SQL Membership provider for login:

<configuration>
  <
connectionStrings
>
    <
add name=ApplicationServices

         connectionString=data source=.\SQLEXPRESS;Integrated Security=SSPI;
AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true

         providerName=System.Data.SqlClient
/>
  </
connectionStrings>
 
  <appSettings
>
    <
add key=enableSimpleMembership value=false
/>
    <
add key=ClientValidationEnabled value=false
/>
    <
add key=UnobtrusiveJavaScriptEnabled value=true
/>
  </
appSettings>
 
  <system.web
>
 
    <authentication mode=Forms
>
      <
forms loginUrl=~/Account/LogOn timeout=2880
/>
    </
authentication>
 
    <membership
>
      <
providers
>
        <
clear
/>
        <
add name=AspNetSqlMembershipProvider
 
            
type=System.Web.Security.SqlMembershipProvider
 
            
connectionStringName=ApplicationServices
             enablePasswordRetrieval=false
 
            
enablePasswordReset=true
 
            
requiresQuestionAndAnswer=false
 
            
requiresUniqueEmail=false
             maxInvalidPasswordAttempts=5
 
            
minRequiredPasswordLength=6
             minRequiredNonalphanumericCharacters=0
 
            
passwordAttemptWindow=10
             applicationName=/
/>
      </
providers
>
    </
membership
>

Now, let’s add our code.  Since I absolutely lack any imagination, I will create blogging application.  Each blog will contain title, text, posted date and category.  Let’s create entities for blogs and categories using EF code first CTP.  First, let’s install CTP 4 for entity framework.  To do, we will use NuGet functionality.  Right click on references node in solution and select add package, search for EF and Select EFCTP 4:

image

 

image

Now, let’s create our two classes – categories and blog posts.  We will add them to the Model folder. Blog category class is very simple:

    public class BlogCategory
    {
        [
Key
]
       
public int CategoryId { get; private set
; }
 
        [
Display(Name = "Category Name"
)]
       
public string CategoryName { get; internal set; }
 
    }

 

I made blog entries property virtual to enable lazy loading.  Blog entry class is only slightly mode complicated:

    public class BlogEntry
    {
        [
Key
]
       
public int BlogEntryId { get; private set
; }
 
        [
Display(Name = "Title"
)]
        [
StringLength
(30, MinimumLength = 5)]
        [
Required
]
       
public string Title { get; set
; }
 
        [
Display(Name = "Post Text"
)]
        [
StringLength
(30, MinimumLength = 5)]
        [
Required
]
       
public string PostText { get; set
; }
 
        [
Display(Name = "Category"
)]
       
public BlogCategory Category { get; set
; }
 
        [
Display(Name = "Post Date"
)]
        [
Range(typeof(DateTime), "1/1/1900", "12/31/2099"
)]
       
public DateTime? PostedOn { get; set; }
    }

 

As you can see, I am using attributes from DataAnnotations namespace to describe the properties of my classes.  Next class I need is DbContext, which essentially is my database abstraction:

    public class BlogContext : DbContext
    {
       
public DbSet<BlogCategory> Categories { get; set
; }
       
public DbSet<BlogEntry> Entries { get; set
; }
 
       
protected override void OnModelCreating(

          System.Data.Entity.ModelConfiguration.ModelBuilder modelBuilder)
        {
           
base
.OnModelCreating(modelBuilder);
 
            modelBuilder.Entity<
BlogEntry
>().HasKey(a => a.BlogEntryId);
            modelBuilder.Entity<
BlogCategory>().HasKey(a => a.CategoryId);
 
        }
    }

 

 

As you can see I have two properties – for blog entries and categories.  I also modify default configuration of my database by overriding OnModelCreating and specifying my primary key columns and table names.  One more class I would like to add to customize database creation behavior that is very useful for development:

    public class BlogDatabase :

       System.Data.Entity.Infrastructure.RecreateDatabaseIfModelChanges<BlogContext>
    {
       
protected override void Seed(BlogContext
context)
        {
            context.Categories.Add(
new BlogCategory()

             { CategoryName = “General” });
            context.Categories.Add(
new BlogCategory()

             { CategoryName = “Entity Framework” });
            context.Categories.Add(
new BlogCategory()

             { CategoryName = “Silverlight” });
        }
    }

 

This class inherits for recreate database behavior.  I am overriding Seed method to prepopulate my database with a handful of categories.  Now, I have to register my class in global.asax.  I am doing so by setting initializer in static method on database class.

        protected void Application_Start()
        {
           
AreaRegistration
.RegisterAllAreas();
           
Database.SetInitializer<BlogContext>(new BlogDatabase
());
 
            RegisterGlobalFilters(
GlobalFilters
.Filters);
            RegisterRoutes(
RouteTable.Routes);
        }

In the next post I will add some web pages to use these classes. 

Post to Twitter

Using Entity Framework 4.0 CTP

In this post I will take a look at the new features in the next release of entity framework.

Primary feature set is centered around what is commonly referred to as “Code First” approach to Entity Framework.  With code first approach you write the code first, then entity framework runtime dynamically figures out how to persist your classes to database.

So, you start with plain ol’ LCR classes (POCO classes).  For my small example I will have list of companies with their contacts.  Here is what my classes look like:

        public Company()
        {
            Contacts = new ObservableCollection<CompanyContact>();
            Contacts.CollectionChanged += (o, e) =>
                {
                    if (e.Action == System.Collections.Specialized.NotifyCollectionChangedAction.Add)
                    {
                        foreach (var item in e.NewItems)
                        {
                            (item as CompanyContact).ParentCompany = this;
                        }
                    }
                };
        }
        public int CompanyId { get; set; }
        public string CompanyName { get; set; }
        public DateTime DateAdded { get; set; }
        public virtual ObservableCollection<CompanyContact> Contacts { get; private set; }
    }
 
    public class CompanyContact
    {
        public int CompanyContactId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public Company ParentCompany { get; set; }
    }

 

Now we need to take a look at key classes that enable POCO persistence in Entity Framework CTP : DbSet and DbContext.

DbSet is a set of POCO classes that is pretty much a simplified version of ObjectSet.  It gives developers ability to add and remove items to.  DbContext is really at heart of CTP version, and it is a simplified version of ObjectContext.  It may contain any number of properties that are of type DbSet<T> where T is any POCO class.

Here is what this class looks like for my Rolodex mini-application:

    public class CompanyContext : DbContext
    {
        public CompanyContext()
            : base()
        {
        }
        public DbSet<Company> Companies { get; set; }
 
        public DbSet<CompanyContact> Contacts { get; set; }

 

That is all we need to do at a high level, dealing with out POCO classes.  Now, the key part – let’s define our rules and persistence details.  There is a number of ways this can be done, I am picking virtual method OnModelCreating on DbContext that allows me to configure rules and persistence details.  Here is what I am doing for my couple of classes:

        protected override void OnModelCreating(System.Data.Entity.ModelConfiguration.ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
 
            // configure primary key
            modelBuilder.Entity<Company>()
                .HasKey(c => c.CompanyId);
 
            // company id is identity
            modelBuilder.Entity<Company>().Property(c => c.CompanyId)
                .StoreGeneratedPattern = System.Data.Metadata.Edm.StoreGeneratedPattern.Identity;
 
            //maximum length for company name
            modelBuilder.Entity<Company>()
               .Property(c => c.CompanyName).MaxLength = 40;
 
            //name required
            modelBuilder.Entity<Company>()
                .Property(c => c.CompanyName).Optional = false;
 
            // One to many relationship
            modelBuilder.Entity<Company>()
                .HasMany(c => c.Contacts);
 
            //contact id is identity
            modelBuilder.Entity<CompanyContact>().Property(c => c.CompanyContactId)
                .StoreGeneratedPattern = System.Data.Metadata.Edm.StoreGeneratedPattern.Identity;
 
            // primary key for contacts
            // company cannot be null
            modelBuilder.Entity<CompanyContact>()
                .HasKey(c => c.CompanyContactId)
                .HasRequired(c => c.ParentCompany);
 
            //last name required
            modelBuilder.Entity<CompanyContact>()
                .Property(c => c.LastName).Optional = false;
 
            //maximum length for last name
            modelBuilder.Entity<CompanyContact>()
                .Property(c => c.LastName).MaxLength = 50;
 
            //first name required
            modelBuilder.Entity<CompanyContact>()
               .Property(c => c.FirstName).Optional = false;
 
            //maximum length for first name
            modelBuilder.Entity<CompanyContact>()
              .Property(c => c.FirstName).MaxLength = 30;
 
 
        }

 

As you can see from my comments I am configuring nullable / non nullable properties via Optional properties off Property method.  I am also configuring Identity properties via StoreGeneratedPattern.  I am also configuring maximum length for my fields at the same time.

The next step is to create a database and add data.  Creating database is super easy with DbContext:

            using (var context = new CompanyContext())
            {
                context.Database.Connection.ConnectionString = @"Server=(local);Database=RolodexEFCtp;Trusted_Connection=True;";
                context.Database.CreateIfNotExists();

 

That is all that takes.  At the time my context is created, OnModelCreating will fire, thus configuring all my persistence options.  Now, let’s populate DB with data:

                var company = new Company()
                    {
                        CompanyName = "Mine",
                        DateAdded = DateTime.Now
                    };
                context.Companies.Add(company);
 
                company.Contacts.Add(new CompanyContact()
                {
                    FirstName = "Sergey",
                    LastName = "Basrskiy"
                });
 
                company.Contacts.Add(new CompanyContact()
                {
                    FirstName = "John",
                    LastName = "Doe"
                });
 
                company = new Company()
                {
                    CompanyName = "Yours",
                    DateAdded = DateTime.Now
                };
                context.Companies.Add(company);
 
                company.Contacts.Add(new CompanyContact()
                {
                    FirstName = "Jane",
                    LastName = "Doe"
                });
 
                company.Contacts.Add(new CompanyContact()
                {
                    FirstName = "John",
                    LastName = "James"
                });
                context.SaveChanges();

 

As before, the last line of dealing with saving is calling SaveChanges() method. 

Now, let’s take a look at how to get the data back from database:

            using (var context = new CompanyContext())
            {
                context.Database.Connection.ConnectionString = @"Server=(local);Database=RolodexEFCtp;Trusted_Connection=True;";
                var companies = context.Set<Company>().Include("Contacts").OrderBy(c => c.CompanyName).ToList();
            }

 

The last line will get me the list of companies, each populated with the list of corresponding contacts.  At this point I am doing everything that Entity Framework is designed for – get me the data from database and save data to database, except now I am configuring everything in code and creating storage on the fly.  Most importantly, I use POCO classes instead of Entity classes.

Now we just have to wait and see when new version of Entity Framework is released.

You can download CTP here: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=4e094902-aeff-4ee2-a12d-5881d4b0dd3e&displaylang=en

You can find more details on CTP on entity framework team blog: http://blogs.msdn.com/b/adonet/archive/2010/07/14/ctp4codefirstwalkthrough.aspx

Thanks

Post to Twitter

Back to Windows Phone 7 Database

Since Microsoft now released RTM version of their tools for Windows Phone 7 development, I updated my phone database project to the latest version as well.  You can find this project here : http://winphone7db.codeplex.com/.

I added also a new feature – ability to create tables with data on desktop, then load them into the database when application runs for the first time.  In order to optimize the process, I am setting this table content (XML files) as Content in my sample application.  If you set your additional files as resources, they will be immediately loaded into memory, while this is not the same for files marked as content.  Here is a screenshot that illustrates the settings.

image

Next step is to read content in Windows phone 7 application.  Here is how I am doing it:

string

content = string.Empty;
Uri uri = new Uri(“test.xml”, UriKind.Relative);
StreamResourceInfo info = Application.GetResourceStream(uri);
using (StreamReader reader = new StreamReader(info.Stream))
{
    content = reader.ReadToEnd();
}

 

 

Pretty easy, hah?

Please look at downloaded application at http://winphone7db.codeplex.com/. for sample use of my database.

Thanks.

Post to Twitter

ElementName Binding Inside Silverlight DataGrid

If you ever used ComboBox inside a templated columns inside DataGrid in Silverlight, you probably encountered the following issue. 

If you use ElementName to bind ItemsSource for a ComboBox in this situation, you will find that ElementName cannot be resolved.  Typically, you have to put your items source inside Resources collection on your use control, and use binding with StaticResource.  This is quite a pain, especially if you are using MVVM pattern, such as Prism.  This problems has been a sore spot for me for a while, but I did not have time to come up with a good solution.

Today I gave it some thought, and it hit me.  I should use a behavior to solve the problem.  The approach I took is manually look for the bound element one the ComboBox is part of visual tree.  I can do this inside Loaded event.  Here is how I got started:

    public class ResolveElementName : TargetedTriggerAction<FrameworkElement>

    {

        protected override void OnAttached()

        {

            base.OnAttached();

            Target.Loaded += Target_Loaded;

        }

 

        protected override void OnDetaching()

        {

            base.OnDetaching();

            Target.Loaded -= Target_Loaded;

        }

 

        private void Target_Loaded(object sender, RoutedEventArgs e)

        {

I also added a Dependency Property:

        public string PropertyName

        {

            get { return (string)GetValue(PropertyNameProperty); }

            set { SetValue(PropertyNameProperty, value); }

        }

 

        public static readonly DependencyProperty PropertyNameProperty =

            DependencyProperty.Register("PropertyName", typeof(string), typeof(ResolveElementName), new PropertyMetadata(string.Empty));

 

        protected override void Invoke(object parameter)

        {

            // do nothing

        }

 

The next step is to look how I typically setup ComboBox inside data gird.  I would like to keep the code as close to usual setup as possible to minimize the learning of new functionality.  Here is XAML from my test program.  This also shows how behavior is setup.

        <grid:DataGrid

            ItemsSource="{Binding People}"

            x:Name="PoepleBox"

            Grid.Row="3"

            AutoGenerateColumns="False" >

            <grid:DataGrid.Columns>

                <grid:DataGridTemplateColumn>

                    <grid:DataGridTemplateColumn.CellTemplate>

                        <DataTemplate>

                            <TextBlock Text="{Binding Path=Name}" Margin="7"/>

                        </DataTemplate>

                    </grid:DataGridTemplateColumn.CellTemplate>

 

                </grid:DataGridTemplateColumn>

                <grid:DataGridTemplateColumn>

                    <grid:DataGridTemplateColumn.CellTemplate>

                        <DataTemplate>

                            <TextBlock

                                Text="{Binding Path=StatusID}"

                                Margin="7"/>

                        </DataTemplate>

                    </grid:DataGridTemplateColumn.CellTemplate>

 

                </grid:DataGridTemplateColumn>

                <grid:DataGridTemplateColumn>

                    <grid:DataGridTemplateColumn.CellTemplate>

                        <DataTemplate>

                            <ComboBox

                                SelectedValue="{Binding Path=StatusID, Mode=TwoWay}"

                                DisplayMemberPath="StatusName"

                                SelectedValuePath="StatusID"

                                ItemsSource="{Binding ElementName=LayoutRoot, Path=DataContext.Statuses}" 

                                Margin="7"

                                Width="200" >

                                <i:Interaction.Triggers>

                                    <i:EventTrigger>

                                        <behavior:ResolveElementName PropertyName="ItemsSource" />

                                    </i:EventTrigger>

                                </i:Interaction.Triggers>

                            </ComboBox>

                        </DataTemplate>

                    </grid:DataGridTemplateColumn.CellTemplate>

 

                </grid:DataGridTemplateColumn>

            </grid:DataGrid.Columns>

        </grid:DataGrid>

Also, you will need to import name spaces to get this to work as following:

<UserControl x:Class="ResolveElementNameBehaviorDemo.MainPage"

    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"

    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"

    xmlns:local="clr-namespace:ResolveElementNameBehaviorDemo"

    xmlns:grid="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data"

    xmlns:behavior="clr-namespace:ResolveElementNameBehavior;assembly=ResolveElementNameBehavior"

    xmlns:i="clr-namespace:System.Windows.Interactivity;assembly=System.Windows.Interactivity"  

 

Now, I would like to show you how I am updating ItemsSource binding by replacing element name binding with regular source based binding:

        private void Target_Loaded(object sender, RoutedEventArgs e)

        {

 

            if (Target != null)

            {

                var fields = Target.GetType().GetFields(

                    System.Reflection.BindingFlags.Public |

                    System.Reflection.BindingFlags.FlattenHierarchy |

                    System.Reflection.BindingFlags.Static);

                foreach (var field in fields)

                {

                    if (field.FieldType == typeof(DependencyProperty) &&

                        (field.Name == PropertyName ||

                        field.Name == string.Concat(PropertyName, "Property")))

                    {

                        DependencyProperty dp = field.GetValue(Target) as DependencyProperty;

                        var binding = Target.GetBindingExpression(dp);

                        string elementName = binding.ParentBinding.ElementName;

                        if (!string.IsNullOrEmpty(elementName))

                        {

                            DependencyObject boundToElement = GetElementBasedOnName(Target, elementName);

                            if (boundToElement != null)

                            {

                                Binding newBinding = new Binding();

                                Binding oldBinding = binding.ParentBinding;

                                newBinding.BindsDirectlyToSource = oldBinding.BindsDirectlyToSource;

                                newBinding.Converter = oldBinding.Converter;

                                newBinding.ConverterCulture = oldBinding.ConverterCulture;

                                newBinding.ConverterParameter = oldBinding.ConverterParameter;

                                newBinding.FallbackValue = oldBinding.FallbackValue;

                                newBinding.Mode = oldBinding.Mode;

                                newBinding.NotifyOnValidationError = oldBinding.NotifyOnValidationError;

                                newBinding.Path = oldBinding.Path;

                                newBinding.Source = boundToElement;

                                newBinding.StringFormat = oldBinding.StringFormat;

                                newBinding.TargetNullValue = oldBinding.TargetNullValue;

                                newBinding.UpdateSourceTrigger = oldBinding.UpdateSourceTrigger;

                                newBinding.ValidatesOnDataErrors = oldBinding.ValidatesOnDataErrors;

                                newBinding.ValidatesOnExceptions = oldBinding.ValidatesOnExceptions;

                                newBinding.ValidatesOnNotifyDataErrors = oldBinding.ValidatesOnNotifyDataErrors;

                                if (Target is ComboBox)

                                {

                                    ComboBox combo = Target as ComboBox;

                                    combo.SetBinding(dp, newBinding);

                                    combo.SetBinding(ComboBox.SelectedValueProperty,

                                        combo.GetBindingExpression(ComboBox.SelectedValueProperty).ParentBinding);

                                }

                                else

                                {

                                    Target.SetBinding(dp, newBinding);

                                }

                               

                            }

                        }

                    }

                }

            }

        }

 

Last step, let me show you how to look for an element with a specific name inside visual tree.  I will use a recursive function that will use VisualTreeHelper class:

        private static DependencyObject GetElementBasedOnName(DependencyObject startPoint, string elementName)

        {

            DependencyObject returnValue = null;

            if (startPoint != null)

            {

                DependencyObject parent = VisualTreeHelper.GetParent(startPoint);

                if (parent != null)

                {

                    FrameworkElement fe = parent as FrameworkElement;

                    if (fe != null)

                    {

                        if (fe.Name == elementName)

                        {

                            returnValue = fe;

                        }

                        else

                        {

                            returnValue = GetElementBasedOnName(fe, elementName);

                        }

                    }

                    else

                    {

                        returnValue = GetElementBasedOnName(fe, elementName);

                    }

                }

            }

            return returnValue;

        }

 

This is it.  There as couple of downsides of this approach.  First of all, there is a small flash when combo is bound.  It is hard to notice though.  The other downside is the overhead of looking for a parent.  However, this is not visibly noticeable.  I tested with a data grid with 100 items, and I do not see any hesitation when scrolling.

You can download the entire solution with behavior and test application here.

I also published it to Microsoft Expression Gallery.

Please let me know if you have any questions.. 

Post to Twitter

Windows Phone 7 Database

I just uploaded and published new CodePlex project I have been working on.  It is an Isolated Storage based database for Windows Phone 7.  I converted my existing Silverlight database project I published a long time ago and adapted it to Windows Phone 7. It supports multiple strongly typed tables, basic Linq based operations, addition and deletion of ranges of items, deletion of items based on condition.  You can also directly bind data to UI in Silverlight because base table inherits from ObservableCollection.  It supports encryption along with lazy loading of table. 

Please take a look at http://winphone7db.codeplex.com/

 

Here are key classes of the project.  ITable interface makes it easy to write generic code in Database class.  It is a simple interface as you can see:

using System;
using System.IO;

namespace SilverlightPhoneDatabase.Core
{
    /// <summary>
    /// Interface that is used for tables in a database
    /// </summary>
    public interface ITable
    {
        /// <summary>
        /// Type of object that database contains
        /// </summary>
        Type RowType { get; }

        /// <summary>
        /// Save table le to the Isolated Storage
        /// </summary>
        void Save();

        /// <summary>
        /// Write content of a table to a file stream
        /// </summary>
        /// <param name="stream">Stream to write table to</param>
        void WriteDTableToStream(Stream stream);

        /// <summary>
        /// Set internal variables data when a table is de-serialized
        /// </summary>
        /// <param name="databaseName">Database name that table belongs to</param>
        /// <param name="password">Password to use for encryption</param>
        void SetTableDefinition(string databaseName, string password);
    }
}

I use XML Serializer to store table to Isolated Storage:

using (IsolatedStorageFile store = IsolatedStorageFile.GetUserStoreForApplication())
                    {
                        string fileName = string.Concat(_databaseName, ".", typeof(T).FullName);
#if SILVERLIGHT
                        if (store.FileExists(fileName))
                        {
                            store.DeleteFile(fileName);
                        }
#else
                        if (store.GetFileNames(fileName).Length > 0)
                        {
                            store.DeleteFile(fileName);
                        }
#endif
                        using (IsolatedStorageFileStream stream = new IsolatedStorageFileStream(fileName, FileMode.OpenOrCreate, store))
                        {
                            WriteDTableToStream(stream);
                        }
                    }

 

Writing data to stream is very easy:

 /// <summary>
        /// Write content of a table to a stream
        /// </summary>
        /// <param name="stream">Stream to write table to</param>
        public void WriteDTableToStream(Stream stream)
        {
            string content = string.Empty;
            using (StringWriter stringWriter = new StringWriter())
            {
                XmlSerializer serializer = new XmlSerializer(this.GetType());

                serializer.Serialize(stringWriter, this);
                stringWriter.Flush();
                content = stringWriter.GetStringBuilder().ToString();
                if (!string.IsNullOrEmpty(_password))
                {
                    content = Cryptography.Encrypt(content, _password);
                }
                stringWriter.Close();
            }

            using (StreamWriter writer = new StreamWriter(stream))
            {
                writer.Write(content);
                writer.Flush();
                writer.Close();
            }
        }

The most interesting part of Database class is probably table loading.  This is done via generic function Table<T>:

/// <summary>
        /// FInd instance of a table that contains specific row type
        /// </summary>
        /// <typeparam name="T">Type of object that this table contains</typeparam>
        /// <returns></returns>
        public Table<T> Table<T>()
        {
            ITable returnValue = (from oneTable in Tables
                                  where oneTable.RowType == typeof(T)
                                  select oneTable).FirstOrDefault();
            if (_useLazyLoading && !_loadedTables[typeof(T)])
            {
                Type tableType = typeof(Table<>).MakeGenericType(new Type[] { typeof(T) });
                string fileName = string.Concat(_databaseName, ".", typeof(T).FullName);
                using (IsolatedStorageFile store = IsolatedStorageFile.GetUserStoreForApplication())
                {
                    using (IsolatedStorageFileStream tableStream = new IsolatedStorageFileStream(fileName, FileMode.Open, store))
                    {
                        LoadTable(tableStream, tableType);
                        tableStream.Close();
                    }
                }
                _loadedTables[typeof(T)] = true;
                returnValue = (from oneTable in Tables
                               where oneTable.RowType == typeof(T)
                               select oneTable).FirstOrDefault();
            }
            return (Table<T>)returnValue;

        }

 

I would very much appreciate comments and feature requests related to this project.

Thanks in advance for the feedback.

Post to Twitter

Windows Phone 7 Application with OData

In this post I will continue exploring Windows Phone 7 development.  Almost any application needs a data source.  There are a few options available for Silverlight applications on Windows Phone.  You can use plain WCF service.  You can optionally put it on Azure.  You can also use Isolated Storage.  Essentially it becomes your custom database.  Of course, you have to write all the API in this case.  Or, you can use OData.  OData approach is the one I am exploring in this post.

First things first, you need to download CTP for OData on Windows Phone.  Here is the link for this download:

http://www.microsoft.com/downloads/details.aspx?FamilyID=b251b247-70ca-4887-bab6-dccdec192f8d&displaylang=en

Download and unzip the files into a folder.  You will need a DLL that is contained in this download – System.Data.Services.Client.Dll

You can follow my previous post and create new Silverlight application for Windows Phone.  There is small change you will need to make.  You need to select the following project template:

image

Now we need to pick an OData source.  I am going to look at www.OData.Org to find one.  I am picking Mix 2010, but you can select any other.  Here is the link I found on OData site – http://api.visitmix.com/OData.svc/

You can always browse the service.  In my case I am going to generate a proxy for this service by running the following command from Microsoft.NET/v4 directory on your local machine:

DataSvcutil.exe /uri:http://api.visitmix.com/OData.svc /DataServiceCollection /Version:2.0 /out:VisitMixClientTypes.cs

DataSvcuil is the .NET utility that generates proxies for ADO.NET Data Services or OData.

Once this is done, copy generated file (VisitMixClientTypes.cs) into the project folder and include it in the project.  What we have inside the file is the proxy for OData service.

Nest step is to update the XAML to match our proxy.  In my simple example I will use the list of sessions and show it on the front page.  Here is what my XAML looks like for my main page (MainPage.xaml)

 

 

<!--ContentGrid contains ListBox. Place additional content here-->
<Grid x:Name="ContentGrid" Grid.Row="1">
    <Grid.Projection>
        <PlaneProjection/>
    </Grid.Projection>
    <ListBox x:Name="ListBoxOne" MouseLeftButtonUp="ListBoxOne_MouseLeftButtonUp"
             Style="{StaticResource PhoneListBox}">
        <ListBox.ItemTemplate>
            <DataTemplate>
                <StackPanel x:Name="DataTemplateStackPanel" Orientation="Horizontal">
                    <Border x:Name="DataTemplateBorder" Height="44" Width="44" BorderBrush="White"
                            BorderThickness="2.5" CornerRadius="100" Margin="10,16,0,0"
                            VerticalAlignment="Top">
                        <Path x:Name="DataTemplatePath" Height="16" Width="11" Fill="White"
                              Stretch="Fill" Margin="4,0,0,0" HorizontalAlignment="Center"
                              VerticalAlignment="Center" UseLayoutRounding="False"
                              Data="M337.59924,129.61948 L337.59924,141.51501 L345.5704,135.87381 z"/>
                    </Border>
                    <mpc:ListViewItem Layout="TextAndDetailsWithIcon" Text="{Binding Title}"
                                      Details="{Binding Body}" Style="{StaticResource PhoneListBoxItemLayout}"/>
                </StackPanel>
            </DataTemplate>
        </ListBox.ItemTemplate>
    </ListBox>
</Grid>

I am only copying the main portion of UI, not entire page here.

I am also going to update the details page that is generated by the project wizard to match my data as well.  Here is what it looks like:

<Grid x:Name="LayoutRoot"
      Background="{StaticResource PhoneBackgroundBrush}"
      d:DataContext="{Binding Items[0]}">
    <Grid.RowDefinitions>
        <RowDefinition Height="Auto"/>
        <RowDefinition Height="*"/>
    </Grid.RowDefinitions>
    <!--TitleGrid is the name of the application and page title-->
    <Grid x:Name="TitleGrid" Grid.Row="0">
        <Grid.Projection>
            <PlaneProjection/>
        </Grid.Projection>
        <TextBlock Text="{Binding Title}" Style="{StaticResource PhoneTextPageTitle2Style}"/>
    </Grid>
    <!--ContentGrid contains the details-->
    <Grid x:Name="ContentGrid" Grid.Row="1">
        <Grid.Projection>
            <PlaneProjection/>
        </Grid.Projection>
        <TextBlock TextWrapping="Wrap" Text="{Binding Body}"
                   Style="{StaticResource PhoneTextBodyTextStyle}"/>
    </Grid>
</Grid>

Now, let’s work on code behind to call our OData service.  Here is entire code behind for MainPage.xaml.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using Microsoft.Phone.Controls;
using System.Windows.Navigation;
using EventModel;
using System.Collections.ObjectModel;
using System.Data.Services.Client;
using System.Windows.Input;
namespace WindowsPhoneListApplication1
{
    public partial class MainPage : PhoneApplicationPage
    {
        object _selectedItem;
        EventEntities _entities = new EventEntities(new Uri("http://api.visitmix.com/OData.svc/"));
        public MainPage()
        {
            InitializeComponent();
            SupportedOrientations = SupportedPageOrientation.Portrait;
            Loaded += new RoutedEventHandler(MainPage_Loaded);
            PageTransitionList.Completed += new EventHandler(PageTransitionList_Completed);
            var query = _entities.CreateQuery<Session>("Sessions");
            query.BeginExecute(ar =>
            {
                DataLoaded(ar);
            }, query);
        }
        private void DataLoaded(IAsyncResult result)
        {
            try
            {
                DataServiceQuery<Session> query = result.AsyncState as DataServiceQuery<Session>;
                List<Session> sessions = query.EndExecute(result).ToList();
                Dispatcher.BeginInvoke(() =>
                    {
                        this.ListBoxOne.ItemsSource = sessions;
                    });
            }
            catch (Exception ex)
            {
                Exception thing = ex;
            }
        }
        private void MainPage_Loaded(object sender, RoutedEventArgs e)
        {
            // Reset page transition
            ResetPageTransitionList.Begin();
        }
        private void ListBoxOne_MouseLeftButtonUp(object sender, MouseButtonEventArgs e)
        {
            // Capture selected item data
            _selectedItem = (sender as ListBox).SelectedItem;
            // Start page transition animation
            PageTransitionList.Begin();
        }
        private void PageTransitionList_Completed(object sender, EventArgs e)
        {
            // Set datacontext of details page to selected listbox item
            NavigationService.Navigate(new Uri("/DetailsPage.xaml", UriKind.Relative));
            FrameworkElement root = Application.Current.RootVisual as FrameworkElement;
            root.DataContext = _selectedItem;
        }
    }
}

The code above is fairly easy.  First, I create a DataSericeQuery of type Session.  Session type is part of our generated proxy.  It then call BeginExecute and pass it a handler.  Inside the handler (DataLoaded) I fire EndExecute, then set the items source for my ListBox.  I have to make sure to use Dispatcher for this call, otherwise I will get cross thread access exception because handler is not firing on UI thread.

That is it.  You can now run the application and see the list of sessions at the MIX 2010.  You can also click on an item to see the details.

Please post your questions here.

Thanks.

Post to Twitter

Getting Started with PowerPivot

Last week I took a stab at creating my first PowerPivot spreadsheet, and I wanted to document the steps.  Maybe this post will help someone, but it will help me remember, that is for sure!

Before you get started, you will need to download all the software required.  Here is my list:

Of course, you will need and instance of SQL Server 2008.  You can just use Express edition if you do not have one installed.

Now that I have everything installed and configured, I can start with PowerPivot.  One note – look for Hand shaped cursor in the screens below.s

Step 1 – Start up Excel 2010 and look for PowerPivot menu, then click on PowerPivot Window to launch pivot.

image

Step 2 – click on From Database in the window, then Select from SQL Server

image

Step 3 – Fill in SQL Server details.  I am choosing to use Data Warehouse DB.  Click Next when done.  On the next screen select an option to Select for a list of tables and views.

image

Step 4 – Select dimension tables and fact table to analyze.  Here is my list in alphabetical order

  • DimCustomer
  • DimDate
  • DimProductCategory
  • DimSalesTerritory
  • FactInternetSales

You can select any tables you like of course.  Wait while data is imported into PowerPivot, then click Close.  PowerPivot window will now have five tabs with data and columns directly imported from the database.  Here is what it looks like.

image

Now click on PivotTable menu and select  Chart and Table – Vertical option.  We will simply create a chart and an analysis table to go with it.  Click on New Worksheet when prompted.  This step will return you back to main Excel window.  You will see chart, table and on the left hand side Gemini task pane – listing of imported data.  Gemini used to be Microsoft code name for this product/functionality.

image

Step 5 – Building a chart.  We are going to analyze sales based on customer marital status by linking it to sales amount.  Click on the chart first, then in Gemini pane expand DimCustomer table and select MaritalStatus.  Then, expand FactInternetSales and select SalesAmount.  PowerPivot will guess what we are trying to do and MaritalStatus as Axis field and Sum(SalesAmount) as Values.  Here is what it looks like:

image

 

Step 6 – building table with filters. 

Click on table first, then check the following tables/fields in Gemini pane:

  • DimCustomer – MaritalStatus and Gender
  • DimDte – CalendarYear
  • DimProductCategory – EnglishProductCategoryName
  • DimSalesTerritory – SalesTerritoryCountry
  • FactInternetSales – SalesAmount

At this point your bottom of Gemini pane should look like this:

image

if you have anything else in Values, click on that item and select “Move to Row labels”.  What you will see also is that your have a report built for you in table area that uses all the analysis points.  You could leave it as is, but I will do more.

 

Step 7 Now I can analyze the data by “slicing it”.  To do this we will create two groups of slices – horizontal and vertical.  We will split our analysis categories as follows:

  • Horizontal Slices: Marital Status and Gender
  • Vertical Slices –English Product Category and Calendar Year.

I will leave Sales Territory country as a column in my table. To perform this I will click on each of my slices and select “Move to Slices Horizontal (or Vertical) as appropriate.  Then I click on each slice and choose Move Up or down until my pane looks like this:

image

Now here is what my table looks:

image

I will go ahead and move things around to make spreadsheet look better:

image

 

Step 8 – Use slices for analysis.  This process is super simple – just click on a desired slice(s) to enact filter on rows of a table.

image

To clear the filter just click on a funnel picture with x across it:

image

As you do so, you will see the chart and table both updating with your filter values.  One thing I did notice at the end – Catergory is lon linked to internet sales, so category filter has no effect on data.  If you see similar behavior, your data has the same issue.  You can always manually build relationships in PowerPivot by click on Table tab in pivot and choosing Manage Relationships menu:

image

 

As you can see, using PowerPivot is extremely easy and the results are very powerful.  You can save your spreadsheet, open it later and refresh the data by clicking on a filter!  You also have an option to show or hide Gemini panels in Excel under PowerPivot menu.  In the same menu you also have an option to show or hide Gemini panels.

Thanks.

Post to Twitter

Silverlight 4 + COM + SQL Server = Cool!

Today is the day to talk about COM possibilities in Silverlight 4.  One would question the titles of my post.  COM and Cool in the same sentence?  Let me proof this title to you.

First, let me take a closer look at COM.  In order to access COM, you must install Silverlight application on a local machine.  The access to COM is not enabled when a Silverlight application is run in the browser.  So, first of all you have to enable this by checking “Enable running application out of browser” checkbox in project properties.  Second of all, you have to check “Require elevated trust…” checkbox in “Out-of-browser settings” area in project properties.  Now, you are ready to install you application and test COM support.  How about Word automation:

Microsoft.Office.Interop.Word.Application word = new Microsoft.Office.Interop.Word.Application();
var doc = word.Documents.Add();
var paragraph = doc.Paragraphs.Add();
paragraph.Range.Text = "Some text";

Now, let’s talk about database access.  Parts of System.Data.SqlClient namespace are not exposed to COM by default.  So, to enable database access we must write an assembly that is exposed to COM that wraps database access.  To do so, check the property “Register for COM Interop” in project properties for the .NET (NOT Silverlight) based project that will fire off database queries for us.  Once the assembly is built, I can just use RegAsm to register my DLL with COM on a machine.  Of course, this would be a prerequisite to use Silverlight application for local data access.  The other prerequisite is to have .NET Runtime installed on that machine as well.  I could write an install project of course to make this process easier.

The next step is to write a Silverlight assembly that would use COM Interop similar to the one above for Word to talk to my database access .NET based assembly.

dynamic sqlDB = ComAutomationFactory.CreateObject("COMSQLClient.COMSqlDatabase");

Now the most exciting part.  I created and published the project on CodePlex that does exactly what I just talked about.  Check out the project and let me know what you think.  You can download the source code and look into the implementation details.  Here is the link to it:

http://silverlight4sqllib.codeplex.com/

Thanks.

Post to Twitter

Entity Framework 4.0 and Multiple Data Contexts

In the EF version 1.0, there was a problem when one would try to update multiple contexts as part of a single transaction.  For example, I can use Transaction Scope, the update one context, then second context.  Here is sample code to illustrate:

 

using (TransactionScope scope = new TransactionScope())
{
    try
    {
        using (RolodexEntities context = new RolodexEntities())
        {
            context.Connection.Open();

            Company company = new Company();
            company.CompanyName = "Test 1";
            company.DateAdded = DateTime.Today;
            context.AddToCompanies(company);
            context.SaveChanges();

            using (RolodexEntities context2 = new RolodexEntities(context.Connection as EntityConnection))
            {
                Company company2 = new Company();
                company2.CompanyName = "Test 1";
                company2.DateAdded = DateTime.Today;
                context2.AddToCompanies(company2);
                context2.SaveChanges();
            }
        }

        scope.Complete();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

This code in version 1.0 would throw an exception when second context’s constructor was called.  The exception was quite obscure and was referring to the fact that the connection was already open.  This was actually correct, but that was the whole reason I was passing in the connection.  I was trying to make sure that my transaction would not get promoted to Distributed Transactions Coordinator.  The transaction would always get promoted as soon as there are multiple SQL Server connections used within using scope/.end using scope code block.  So, in version 1.0 MSDTC would be required in the code above.

This issue still exists in EF 4.0.  You can vote to fix the issue here : https://connect.microsoft.com/data/feedback/details/533240/cannot-share-a-store-connection-between-multiple-contexts-in-order-to-avoid-dtc-promotion

Here is the code that confirms the problem:

using (TransactionScope scope = new TransactionScope())
{
    try
    {
        using (RolodexEntities context = new RolodexEntities())
        {
            context.Connection.Open();

            Company company = new Company();
            company.CompanyName = "Test 11";
            company.DateAdded = DateTime.Today;
            context.AddToCompanies(company);
            context.SaveChanges();

            RolodexEntities1 contextTemp = new RolodexEntities1();
            MetadataWorkspace space = (contextTemp.Connection as EntityConnection).GetMetadataWorkspace();
            EntityConnection connection = new EntityConnection(space, (context.Connection as EntityConnection).StoreConnection);

            using (RolodexEntities1 context2 = new RolodexEntities1(connection))
            {
                User user = new User();
                user.UserName = "test";
                user.Role = "User";
                context2.AddToUsers(user);
                context2.SaveChanges();
            }
        }

        scope.Complete();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Please feel free to ask any questions on this subject.

Post to Twitter

RIA Services (Cont.)

In this post I will cover the update process.

I am going to recap where I left off in the last post on RIA Services.  The last step was to retrieved based on ID from a read only company object.  Once this object is obtained in the VIewModel, we can create a screen to edit a company.

Here is how I am getting the company by ID in the ViewModel class:

var

companyQuery = _context.GetCompanyQuery(_companyID);

 

_context.Load<Companies>(companyQuery, (o1) =>

 

{

 

    HidePleaseWaitMessage();

 

    if (o.Error != null)

 

    {

 

        ErrorHandler.HandleException(o.Error);

 

    }

 

    else

 

    {

 

        this.Model = o1.Entities.First() as Companies;

 

    }

 

}, null);

Now that I have my Model property set, I can create a user control to edit the company.  Here is the part of the XAML for the edit form that shows the binding:

<UserControl x:Class

=”Rolodex.Silverlight.Views.CompanyEditView”

 

    xmlns=”http://schemas.microsoft.com/winfx/2006/xaml/presentation”

 

    xmlns:x=”http://schemas.microsoft.com/winfx/2006/xaml”

 

    xmlns:controls=”clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data”

 

    xmlns:input=”clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls”

 

    xmlns:cal=”clr-namespace:Microsoft.Practices.Composite.Presentation.Commands;assembly=Microsoft.Practices.Composite.Presentation”

 

    xmlns:core=”clr-namespace:Rolodex.Silverlight.Core”

 

    >

 

    <UserControl.Resources

>

 

       

 

    </UserControl.Resources

>

 

    <Grid x:Name

=”LayoutRoot”>

 

        <Grid.RowDefinitions

>

 

            <RowDefinition Height

=”*”/>

 

            <RowDefinition Height

=”Auto”/>

 

        </Grid.RowDefinitions

>

 

        <Grid x:Name=”EditGrid” DataContext=”{Binding Model

}”>

 

            <Grid.RowDefinitions

>

 

                <RowDefinition Height

=”35″/>

 

                <RowDefinition Height

=”35″/>

 

                <RowDefinition Height

=”1*”/>

 

                <RowDefinition Height

=”35″/>

 

                <RowDefinition Height

=”1*”/>

 

                <RowDefinition Height

=”35″/>

 

            </Grid.RowDefinitions

>

 

            <Grid.ColumnDefinitions

>

 

                <ColumnDefinition Width

=”Auto”/>

 

                <ColumnDefinition Width

=”200″/>

 

                <ColumnDefinition Width

=”*”/>

 

            </Grid.ColumnDefinitions

>

 

            <TextBlock Text=”Company Name:” TextAlignment=”Right” HorizontalAlignment=”Right” Grid.Column=”0″ Grid.Row=”0″ Margin=”6,6,6,6″ VerticalAlignment

=”Center”/>

 

            <TextBox x:Name=”CompanyNameTextbox” Grid.Column=”1″ Grid.Row=”0″ HorizontalAlignment=”Stretch” Margin=”6,6,6,6″ Text=”{Binding Path=CompanyName, Mode=TwoWay}” VerticalAlignment

=”Center”/>

 

            <TextBlock Text=”Date Added:” TextAlignment=”Right” HorizontalAlignment=”Right” Grid.Column=”0″ Grid.Row=”1″ Margin=”6,6,6,6″ VerticalAlignment

=”Center”/>

 

            <input:DatePicker x:Name=”DateAddedTextbox” SelectedDate=”{Binding DateAdded, Mode=TwoWay}” Grid.Column=”1″ Grid.Row=”1″ HorizontalAlignment=”Stretch” Margin=”6,6,6,6″ VerticalAlignment=”Center”/>

So far, I have an instance of a Companies object that is bound to the data context of my user control.  At this point a user can edit company name and date added fields.  Logically, the next thing a user would want to do is to save his changed – the nerve of him :-)

So, let’s take a look at the Save command in our view model object.

 

protected

override void BeginSave()

 

{

 

    ShowPleaseWaitMessage();

 

    _context.SubmitChanges(HandleSave, null);

 

}

 

 

 

private

void HandleSave(SubmitOperation operation)

 

{

 

    HidePleaseWaitMessage();

 

    if (operation.Error != null)

 

    {

 

        ErrorHandler.HandleException(operation.Error);

 

    }

 

}

Save is a two step process because all communication in Silverlight is asynchronous.  We are starting the save process in BeginSave, and we are checking for errors in HandleSave methods respectively.  The key part to this process is client side context of RIA Services that keeps track of all the changes that the user is making after the object has been initially retrieved via a call to the server.  The key work is done by the domain service object – LinqToEntitiesDomainService<RolodexEntities> in our case.  We can look at this code generated by the RIA Services wizard by looking at RolodexDomainService.cs class in my case.  There is a bug in current CTP that causes update to parent and child objects not being flushed properly.  In my case I have Companies object that contains a list of ComanyContacts objects.  Generated code has check for EntityState in UpdateCompanyContacts method that I had to remove to get the process to work.  Here is the final version of key methods of the Domain service class:

public

void UpdateCompanies(Companies currentCompanies)

 

{

 

    this.ObjectContext.AttachAsModified(currentCompanies, this.ChangeSet.GetOriginal(currentCompanies));

 

}

public

void UpdateCompanyContacts(CompanyContacts currentCompanyContacts)

 

{

 

    this.ObjectContext.AttachAsModified(currentCompanyContacts, this.ChangeSet.GetOriginal(currentCompanyContacts));

 

}

So, after this step all parts of the process are working – I can update companies and contacts.

Please email me any questions,

Post to Twitter

Velocity Presentation

As I mentioned previously, I presented on the topic of Velocity at the Atlanta Leading Edge Microsoft Users Group on Tuesday, January 19th.

The talk went pretty well, we had a lot of technical questions.  I really enjoyed this presentation, as I am always excited to speak on a new topic that I have not covered previously. 

You can download materials here.

 

Please let me know if you have any questions.

Post to Twitter

RIA Services (Cont.)

I am going to continue exploring the topic of RIA services and Silverlight.  I thought I was going to get into updates in this post, but there are a few topics I would like to cover first.  I am going to try to get to updates in the next post.

Today, I am going to explore two more topics.  I am going to cover extending RIA Services DomainContext with custom classes and working on including child objects when fetching a parent object using Entity Framework and RIA Services.

 

First, I am going to extend my DomainContext with a custom class.  In my case I have Company Object/Table with a number of columns.  In my Silverlight application I would like to have a list of companies that shows only company name.  When a user selected one company, I would like to fetch full company object with company contacts child object.  If I were to use the method that the wizard built for me – public IQueryable<Companies> GetCompanies(),I would get the full object with all columns.  So, I am going to build a custom class that only has company ID and name:

    public class ReadOnlyCompany

    {

        [Key]

        public int CompanyId { internal set; get; }

        public string CompanyName { internal set; get; }

    }

 

I am going to add this class to RIA Service project on .NET side (SilverlightRIAServicesLibrary.Web project in my case).  As you notice I did not have to decorate my class with DataContract attribute or decorate properties with DataMember attribute.  We do not actually need to do this, as RIA Services will take care of all that for us.  I do however need to use Key attribute or I would get a compile time error.  Each object is required to have a key (primary key) property.  Next step is to write a method in DomainService (RoldexDomainService in my case) class.  Here is what this would look like:

        public List<ReadOnlyCompany> GetReadOnlyCompanies()

        {

            return (from oneCompany in this.ObjectContext.Companies

                    orderby oneCompany.CompanyName

                    select new ReadOnlyCompany()

                    {

                        CompanyId = oneCompany.CompanyId,

                        CompanyName = oneCompany.CompanyName

                    }).ToList<ReadOnlyCompany>();

        }

 

Even though I return List object here, but RIA Services will actually return ReadOnlyObservableCollection<ReadOnlyCompany> on Silverlight side.  You have to remember that when you cast return value for RIA Services call to a specific object.  Here is what this call would look like on Silverlight side:

        private void GetCompaniesList()

        {

            ShowPleaseWaitMessage();

            var query = _context.GetReadOnlyCompaniesQuery();

 

            _context.Load<ReadOnlyCompany>(query, LoadBehavior.RefreshCurrent, (o) =>

            {

                HidePleaseWaitMessage();

                if (o.Error != null)

                    ErrorHandler.HandleException(o.Error);

                else

                {

                    this.Model = o.Entities as ReadOnlyObservableCollection<ReadOnlyCompany>;

                }

                    HidePleaseWaitMessage();

            }, null); ;

 

        }

Here I am putting up a please wait window, fire a query and interpret the results.  As I mention before, my next step is to get full Company object based on selected ID.  Again, I am adding a new method to DomainService class on .NET side:

        public Companies GetCompany(int companyID)

        {

            var returnValue = this.ObjectContext.Companies

                .Include("CompanyContacts")

                .Include("CompanyContacts.CompanyContactPhones")

                .Where(one => one.CompanyId == companyID).FirstOrDefault();

            return returnValue;

        }

Now, let’s see what this call looks like on Silverlight side.  This also demonstrates the use of parameters:

var companyQuery = _context.GetCompanyQuery(_companyID);

_context.Load<Companies>(companyQuery, (o1) =>

{

    HidePleaseWaitMessage();

    if (o.Error != null)

    {

        ErrorHandler.HandleException(o.Error);

    }

    else

    {

        this.Model = o1.Entities.First() as Companies;

    }

}, null);

As you can see, RIA services build us a query that already has the same company ID parameter for us.  Pretty cool!  However, if I look at return value (Companies object), I will notice that contacts property is actually null even though I did add .Include statement to my custom Entity Framework based method.  To get this to work, I have to modify the metadata class that RIA Services generated(RolodexDomainService.metadata.cs in my case).  I need to open this class file and look for Companies object.  I will find public EntityCollection<CompanyContactPhones> CompanyContactPhones property in it.  To get RIA Services to include child collection, I need to add Include attribute here as well:

[Include]

public EntityCollection<CompanyContactPhones> CompanyContactPhones;

I would have to take the same step to include phones collection for each contact.  If I run my code again, I will not get Company object with a list of Contacts, each containing a list of Phones.

I will try to cover update in my next post.

Thank you and do not hesitate to ask questions.

Post to Twitter

Talk at GGMUG

As always I had fun presenting at Gwinnett Georgia Microsoft Users Group on Thursday.  The subject of my talk was “Getting started with SQL Azure”.  I documented all the steps in this blog post.  There are also useful links that I mentioned in this post to help you get started.

There are many advantages to use SQL Azure.  Number of reason is probably the fact that one can eliminate the need to house expensive hardware and software on premises.  As long as you are fine with living with certain limitations of SQL Azure compared to SQL Server 2008, you can take advantage of this great technology.  The primary limitation is probably the size of the database.  You cannot have a database bigger than 10 GB.

Feel free to ask any questions on the topic.

Post to Twitter

AppFabric Caching (Velocity) and Versioning

To continue on the topic of Velocity, I will explore versioning today.

As I showed previously, you can use DataCache.Add method to add an item to cache.  This, of course, requires that this item’s key was not to be found in the cache already, or an exception will be thrown.  This puts a damper on versioning,  So, let’s look at a different method – Put.

You can use Put method to add/replace an item in cache.  If a key already exists, the item will be replaced, otherwise it will be added.

// add an item to cache

CachablePerson person1 = new CachablePerson();

person1.FirstName = "Version1";

DataCacheItemVersion version1 = _defaultCache.Put("personwithversion", person1);

 

 

Now, I will explore versioning of items.  In order to control versioning, I will use GetAndLock method to get an item from cache while locking it to ensure safe updates.  I will update a property of an object I got from cache, then I will put it back into cache.

// get an item and lock it

DataCacheLockHandle handle;

CachablePerson temp = _defaultCache.GetAndLock("personwithversion", TimeSpan.FromSeconds(1), out handle, true) as CachablePerson;

/update an item

temp.FirstName = "Version2";

//put it back and get the new version

DataCacheItemVersion version2 = _defaultCache.PutAndUnlock("personwithversion", temp, handle);

Now I will version that versioning actually works by getting both initial and new items

// get initial item

CachablePerson ver1 = (CachablePerson)_defaultCache.Get("personwithversion");

// get the item with version 2

CachablePerson ver2 = (CachablePerson)_defaultCache.GetIfNewer("personwithversion", ref version1);

To proof that the process worked, I will output the results to a window or console.  In my case ver1.FirstName is Version1 and ver2.FirstName is Version2 which is what is expected.

In this post I looked at the Put method that makes it easier to put items into cache without worrying if an item with the same key already exists.  I also looked at the way to maintain many versioned items with the same key in cache in case versioning is important to the consuming application.

You can download the updated initial sample here.

Post to Twitter

Getting Started with Velocity (AppFabric Caching Library)

I have worked through my first Velocity project today, and I would like to blog about  the steps necessary to use caching.

First, you should read my previous post and install AppFabric on your machine.

I used Visual Studio 2010 beta 2 to create the solution.  You can download the solution here.  I create WPF project in order to prove that caching is not limited to web applications, although web farm would probably be an ideal application for caching.  Once project is created, I added references to DLLs for Velocity.  On my machine they were located in C:\Windows\System32\ApplicationServerExtensions.  Two DLLs I needed were CacheBaseLibrary.dll and  ClientLibrary.dll.  They contain key classes that can be used to utilize caching.  I could not add them directly from that folder, I was getting an error from Add Reference dialog.  So, I copied them into a folder under my Solution (“Bin” folder).

Then I added a complex class that I intended to cache.  I wanted to prove to myself that complex object graphs are OK since all the demos I saw just used strings.  Here is the class I used:

 

    public class CachablePerson

    {

        public string FirstName { get; set; }

        public string LastName { get; set; }

        public List<CachableAddress> Addresses { get; set; }

        public CachablePerson()

        {

            FirstName = "Sergey";

            LastName = "Barskiy";

            Addresses = new List<CachableAddress>();

            Addresses.Add(new CachableAddress() { Street = "Main Street", City = "Atlanta" });

            Addresses.Add(new CachableAddress() { Street = "Second Street", City = "Lilburn" });

        }

    }

 

    public class CachableAddress

    {

        public string Street { get; set; }

        public string City { get; set; }

    }

 

I put the class into its own project and referenced this project from two separate WPF application projects.

First step in using caching is to configure cache object.  Here is how we can configure cache object using cache factory:

        private void SetupCache()

        {

            DataCacheServerEndpoint[] servers = new DataCacheServerEndpoint[1];

            servers[0] = new DataCacheServerEndpoint("Sergey-Laptop", 22233, "DistributedCacheService");

            _cacheFactory = new DataCacheFactory(servers, false, true);

            _defaultCache = _cacheFactory.GetCache("default");

        }

 

Now we are ready to add object to cache.  We will do this in steps.  First we check to see if an object is already in cache, and if so, we will remove it from cache.  Cache basically is using a string as the object key.  In addition to that you can also version objects, thus having multiple copies of the same object with the same key existing in cache at any given time.

            if (_defaultCache.Get("person") != null)

            {

                _defaultCache.Remove("person");

            }

            _defaultCache.Add("person", _person);

 

 

Now I am going to get the object from cache.  Just as simple as adding it:

_person = (CachablePerson)_defaultCache.Get("person");

 

Simple and easy.  I think Velocity is super powerful yet simple to use.  Event though it would be an abuse, but one could possibly use caching to communicate between different applications. 

You can download my sample solution here

Post to Twitter

Windows AppFabric and Velocity

I downloaded Windows AppFabric Beta 1 version this week.  You can find this download here. It took a few hours to install it.  I could surely tell I was dealing with beta 1 of version 1 of the product.  I finally installed it using new cluster option and XML configuration option.  I tried SQL Server configuration  option a few times, but it never installed without errors.  I think at the end I ran the install about a dozen times before it succeeded.

Anyway, three hours later or so I was done.  Next I tried to run a downloaded sample.   No matter what I tried, I could not get the sample to work.  It looked pretty obvious that I still need to configure something.  Finally after another 30 minutes I found an answer – I had to start the cache cluster.  To do so, I went to Programs –> Microsoft Distributed Cache –> Administration tool.  I was sort of surprised that this launched PowerShell window.  I guess we need to wait until release to get real nice configuration software with GUI.  In the mean time, I had to type “start-cachecluster”.  Once I ran that and saw that the server was started, I went back to run the samples.  They worked perfectly well this time.

Once I write my own sample, I will post source code on this blog.

Post to Twitter

Windows Azure Application

This is purely a bragging post :-)

I just deployed a test application to the cloud (Microsoft Azure): http://rolodex.cloudapp.net/

Here is the technology stack for it:

  1. Windows Azure
  2. SQL Azure
  3. CSLA 3.8.1
  4. Silverlight
  5. Entity Framework
  6. WCF
  7. Prism (Composite Application Guidance)
  8. Silverlight Toolkit

I promise to write a blog entry in the near future, the steps one has to follow through to create an Azure application.

Post to Twitter

Getting Started with SQL Azure

I setup an account with SQL Azure last week, and I would like to document each step I took in order. 

There is a lot of information on SQL Azure FAQ and Zack Owens’ blog.  I still ran into some issues though, and I wanted to provide detailed instruction on how to work through them.

Step 1 – Get SQL Azure token.  You need to Microsoft Azure site and scroll down the page to request tokens.

Step 2 – Wait for the email you will receive that will contain the token (invitation code) and instructions on how to activate it. Follow those step to setup an account.

Step 3 – Create new project in SQL Azure.  You will setup user ID and password as part of that process.

Step 4 – Create a database with a name you choose and setup firewall

image

 

image 

 

image

You can see your own IP address on this window above as well.  You can just copy that address and paste it into both IP ranges – low and high,

Step 5 – Start SSMS (SQL Server Management Studio).  Hit Cancel on initial login screen.

image

Step 6 – Click New Query button

image

Step 7 – Enter your server information, but do NOT click Connect

image

Step 8 – Click Options and enter your database name

image

Step 9 – Click “Connect”.  You should not get any errors and now you can see a query window.  You are ready to add tables.

Step 10 – Type in script to create database structure.  Important: not all standard SQL commands are supported.  If you are scripting an existing database, you will need to remove all file group references, such as “ON PRIMARY”. 

image

Step 11 – Create a standard .NET application.  You can get exact connection string on your SQL Azure management page:

image

You are done!

Find more information on SQL Azure FAQ and Zack Owens’ blog.

Post to Twitter

Entity Framework 4.0 CTP

I just installed CTP of entity framework 4.0 (2.0 technically).  I decided to test the most advertized feature – ability to have actual foreign keys (IDs) in addition to navigation properties.  The lack of this feature has been the biggest annoyance for me while working with Entity Framework for many months now.

Here is the model I created

image

As you can see Companies have Contacts, and each contact object now has CompanyID in addition to Company navigation property.  I had to select this option while generating the model from the database.  The checkbox for option to include foreign key values was available on one of the wizard screens.

Here is the code I wrote for testing:

 

            using (RolodexEntities context = new RolodexEntities())

            {

                CompanyContact newConact = new CompanyContact();

                newConact.CompanyId = 2;

                newConact.BaseSalary = 1;

                newConact.Birthday = DateTime.Today;

                newConact.FirstName = "Sergey";

                newConact.LastName = "Barskiy";

                newConact.RankId = 1;

                context.AddToCompanyContacts(newConact);

                context.SaveChanges();

            }

Hurray!  It worked!  New contact was added to the database and linked to Company with ID of 2 and Rank with ID of 1.

This feature to me is invaluable in n-Tier development when a developer has to replay the changes on the server after allowing the user to edit the data on the client.  Now, we can just transmit all properties of a Contact to the server, (probably with IsNew, IsUpdated, IsDeleted flags), set the foreign keys via IDs, then save.

Post to Twitter

Silverlight And N-Tier Data Access using Entity Framework

As I mentioned before I talked at GGMUG last Thursday.  I talked about Entity Framework in general and specifically in N-Tier applications, such as Silverlight applications.  I think I got a few folks excited about using Entity Framework.  The key thing to remember when using EF in N-Tier applications is that EF keeps track of changes in the context object.  Unfortunately, this object does not survive the trips between client and the server.  As a result, my recommendation is to use EF in conjunction with a business layer framework, such as CSLA. You can download slides and sample project here.  Please feel free to post comments or questions.

Post to Twitter

I will be talking at GGMUG on Thursday, September 10th

I will be presenting at the next Gwinnett Georgia Microsoft Users Group this coming Thursday.  The topic of my presentation will be Entity Framework in general and its applications to Silverlight development.  I will be posting the zip file with slides and sample application immediately after the presentation.

I hope to see you there!

Post to Twitter

Entity Framework and multiple relationships between two tables

Here is a scenario – I have two tables – Users and Phones.  Users table has two columns that relate to Phones table – PrimaryPhone and SecondaryPhone.  If you build an EF model with these two tables, you will end up with Users entity with two navigation properties that relate to Phones entity – Phones and Phones1.  Yes, I hope this excellent :-( naming convention will be fixed in EF 2.0.  Our goal – retrieve the data for both phones with each user.  If you do the following:

var query = (from oneUser in Users.Include(“Phones”)

you will end up with only secondary (or primary) navigation property in Users object populated.  So, either Users.Phones or Users.Phones1 will have data, the other one will be null.  Important thing to realize when working with Entity Framework is that “.Includes”  statement works off navigation property names, not table names.  So, if you would like to populate both primary and secondary phone, you need to use the following query:

var query = (from oneUser in Users.Include(“Phones”).Include(“Phones1”)

Post to Twitter

Using Entity Framework as Data Access Layer in n-Tier applications

I have been using Entity framework as DAL for a CSLA for Silverlight based application.  I have player a role in uncovering a number of issues that make it very hard to use in these circumstances.  Here is my (surely incomplete) list.

1. Foreign keys handling.  The actual IDs that are used as foreign keys are not built as properties when you generate a model from a database.  Instead you get navigation properties.  For example, you have User and UserTypes table.  Users table has UserTypeID in it.  When you build a mode for this, you will have UserTypes property in Users entity, but not access to UserTypeID.  As a result, when you are rehydrating a users object in n-Tier environment, you have to have an instance of UserTypes entity to attach a user to.  Your options are to retrieve it from database (inefficient) or write a helper class that creates one on the fly.  To do so, you create an instance of UserTypes entity, then set entity key on it, then attach it to context.  To do all this, you will need to know ID in advance.  You can do this by using UserTypesReference property in Users entity – it will contain UserTypesID when you retrieve an instance of Users from DB.  You can store it in a property of the user object (CSLA for example) that you will use in UI.

2.  Another foreign key annoyance is an instance when you have multiple columns from one table relating to another table.  For example, Users table has columns PrimaryType and SecondaryType, both relating to UseTypes table.  As a result you will get navigation properties UserTypes1 and UserTypes2 in Users entity.  This is pretty much useless because as you write code against that, you will need to constantly have your model open to figure out what ID you need to use.  On top of that, if you use .Include(“UserTypes”) syntax to get the user with both primary and secondary columns, only one of the UserTypes properties will be populated with an instance of UserTypes if you have different IDs in property and secondary columns.  What do you do then?  Your only option is to use UserTypes1(2)Reference property to either get an ID or retrieve an instance of UserTypes table

3.  Third foreign key issue is concurrency handling.  Typically, for all properties you can indicate if a column is to participate in concurrency handling.  For some reason, you cannot do the same for navigation properties – they always participate in concurrency handling if they are changing.  As a result, you have to always keep track of old and new value for each navigation property,  You would set its old value before attaching it to the context, then set it to the new value.  If you capture the SQL query that is sent to the DB when the values are not the same, you will find navigation properties are part of the where clause / concurrency checking.  Sad but true.

4.  Re-using context for Oracle and SQL server (or any multiple databases).  Currently Microsoft ships entity framework only with SQL Server provider.  So, you are stuck buying some third party Oracle provider and hope that the model it generates will be the same as SQL server model, so that you only need to change connection string when switching databases.  There is a promise we might get there one day, but today is not that day.

Post to Twitter

Linq to SQL

I just read this blog post on ADO.NET team blog.  I have been wondering about the future on Linq to SQL since the release of Entity Framework.  There is signification overlap in capabilities of both technologies, and one must wonder about the overhead Microsoft is willing to incur in maintaining both.  My guess is, they will not do this forever.  So, if you are starting a new application, would you use Linq to SQL today.  My answer is no.

Post to Twitter