Thursday, July 26, 2012

Northwind-mania: Upgrading Northwind.mdf database file from SQL Server 2005/2008 to LocalDB\v11.0

Visual Studio 2012 comes with a new small light-weight database named LocalDB\v11.0. You can find out the default database used by Visual Studio 2012 by checking out TOOLS >> Options… >> Database Tools >> Data Connections:

image

If you have a SQL2005 or SQL2008 database and wish to use it in a Visual Studio 2012 application, you should upgrade it to LocalDB\v11.0 format. Here’s what I did when I came across this predicament. I used the usual Northwind.mdf SQL2008 database for this post.

- In Visual Studio 2012  “Server Explorer”, right-click on “Data Connections” and select “Add Connections…”

image

- Click the “Change…” button on the “Add Connection” dialog:

image

- Choose “Microsoft SQL Server Database File” then click OK. You will be returned to the “Add Connection” dialog.

image

- Navigate to the location of your Northwind.mdf file by clicking the “Browse…” button, then click OK.

image

- Visual Studio 2012 will detect that the database is not of LocalDB\v11.0 format and will display this message:

image

- Click “Yes” so that your SQL200x database gets upgraded to LocalDB\v11.0. Upon completion of the upgrade process you will be able to view all your database objects in Server Explorer:

image

Hope this helps.

Tuesday, July 24, 2012

Northwind-mania: Read Northwind oData into a Windows 8 Metro Style C# / XAML application

Once again I will use the well known Northwind database. This time I will demonstrate how easy it is to render an oData feed originating from http://services.odata.org/Northwind/Northwind.svc.

Pre-requisites:

  • Windows 8 Release Preview
  • Visual Studio 2012 RC

Step 1:

In Visual Studio 2012 RC, start a new project as follows:

FILE >> New Project … >> Visual C# >> Windows Metro Style >> Blank App (XAML)

Name the application “Win8ReadOdata” then click OK.

image

Step 2:

We will retrieve data from an oData source at odata.org. In “Solution Explorer”, right-click on References and select “Add Service Reference…”. Enter the address http://services.odata.org/Northwind/Northwind.svc then click on the “Go” button. After the service is found, give the Namespace the name “NorthwindServiceReference” then clock OK.

image

At this point a proxy to the service is created for us.

Step 3:

Open the “MainPage.xaml” document and paste the following XAML code inside of the “Grid” element:

<StackPanel Background="Transparent" VerticalAlignment="Center" x:Name="fullMode" >
        <ListBox Name="categoriesList" Margin="40" Foreground="Blue" Background="Transparent" Height="500">
            <ListBox.ItemTemplate>
                <DataTemplate>
                    <StackPanel Margin="100,0,0,10" VerticalAlignment="Center" Orientation="Horizontal" >
                        <TextBlock Text="{Binding CategoryID}" Margin="10,0,0,0" FontSize="26" VerticalAlignment="Center" Foreground="Green"/>
                        <TextBlock Text="{Binding CategoryName}" Margin="10,0,0,0" FontSize="22" VerticalAlignment="Center" Foreground="Red"/>
                        <TextBlock Text="{Binding Description}" Margin="10,0,0,0" FontSize="22" VerticalAlignment="Center"/>
                    </StackPanel>
                </DataTemplate>
            </ListBox.ItemTemplate>
        </ListBox>
</StackPanel>

The above markup produces a listbox control that will hold records from the “Categories” table in the Northwind database. We will display the CategoryID, CategoryName, and Description database columns in Green, Red, and Blue colors respectively just so that they are easily distinguishable.

Step 4:

Place the following declaration in MainPage.xaml.cs just before the constructor:

NorthwindEntities ctx = new NorthwindEntities(new Uri("http://services.odata.org/Northwind/Northwind.svc/"));

You may need to resolve the NorthwindEntities class.

Put the following code inside the constructor just after this.InitializeComponent().

LoadCategoriesThruOData();

Add these methods to the MainPage.xaml.cs:

private async void LoadCategoriesThruOData()
{
    var results = await Task<IEnumerable<Category>>.Factory.FromAsync(ctx.Categories.BeginExecute(AnotherEndContinuation, ctx), ContinuationDelegate);

    // This fires only after we have a response back.
    this.ParseAndBindData(results);
}

Func<IAsyncResult, IEnumerable<Category>> ContinuationDelegate = EndContinuation;
private static IEnumerable<Category> EndContinuation(IAsyncResult result)
{
    NorthwindServiceReference.NorthwindEntities client = (NorthwindServiceReference.NorthwindEntities)result.AsyncState;
    return client.Categories.EndExecute(result);
}

private static void AnotherEndContinuation(IAsyncResult result)
{
    // Do nothing here.
}

private void ParseAndBindData(IEnumerable<Category> results)
{
    categoriesList.ItemsSource = results;
}

The LoadCategoriesThruOData()  method is responsible for making an async call to the oData service. The ParseAndBindData() method is called once data is successfully received.

After resolving some missing namespaces, run the application by hitting F5. The result should look like this:

image

In future posts I will extend this application with abilities to insert, update, and delete data.

Friday, July 6, 2012

Registered Community Speaker with INETA

I am now a registered community speaker with INETA. If you are organizing a technology conference or a user group leader, you can make a request for me to come over and deliver a talk by clicking on the link below:

INETA Community Speakers Program

Tuesday, July 3, 2012

Northwind-mania: Simple example on working with Northwind oData using KnockoutJS

In my previous post I demonstrated how one can easily render data on the client side from the Categories table in the Northwind database using KnockoutJS. This post is a slight variation of my previous post whereby the source of the data is an oData feed originating from http://services.odata.org/Northwind/Northwind.svc.

Pre-requisites:

  • Visual Studio 2010
  • MVC 3.0
  • NuGet
  • KnockoutJS
  • DataJS

Step 1:

Start a new New Web Site project in Visual Studio 2010 using the ASP.NET Empty Web Site template.

Step 2:

In Solution Explorer, right-click on the top node and select “Manage NuGet Packages …”.

image

Step 3:

Enter “knockout” in the search field on the top right-hand-side of the next screen. Select “knockoutjs” then click on the “Install” button.

image

Step 4:

In addition to knockoutjs, we will be using other JavaScript libraries jQuery and DataJS. DataJS facilitates access to oData. Therefore, repeat Step-3 for jQuery and DataJs libraries.

image

image

Ensure that the jQuery, DataJs and KnockoutJs libraries are installed into your project under the Scripts folder as shown below:

image

Step 5:

Add a new page named “Default.htm” to your website.

Open the Default.htm file. Drag and drop the JavaScript files jquery-1.7.2.js, knockout-2.1.0.js and datajs-1.0.3.js from the Scripts folder into the Default.htm file just below the ending </title> tag. This produces the <script> tag pointing to the respective JavaScript files. The page should resemble the following:

<!DOCTYPE>
<html>
<head>
    <title></title>
    <script src="Scripts/jquery-1.7.2.js" type="text/javascript"></script>
    <script src="Scripts/knockout-2.1.0.js" type="text/javascript"></script>
    <script src="Scripts/datajs-1.0.3.js" type="text/javascript"></script>
</head>
<body>

</body>
</html>

 

Step 6:

Place the following HTML into the body section of the Default.htm file:

<ul data-bind="foreach: categories">    
     <li><span data-bind="text: $data.CategoryName"></span></li>
</ul>
<script type="text/javascript">
     var ServiceURL = "
http://services.odata.org/Northwind/Northwind.svc";

     OData.read(ServiceURL + "/Categories?$format=json", CallbackFunction);

     function CallbackFunction(data, request) {
         var viewModel = {
             categories: data.results
         };
         ko.applyBindings(viewModel);
     }
</script>

Let’s analyze the above code. The most important line is:

OData.read(url, CallbackFunction);

The OData.read() function comes from the “DataJS” library. Its first argument is an oData URL, and the second argument is a callback function that is called once a response is received from the server. Notice that the oData URL contains “$format=json” so that data returned from the service is in JSON format.

The callback function reads the JSON data encapsulated in property data.results into the categories property of the KnockoutJS viewModel. Finally, the viewModel is bound to the view with the function call ko.applyBindings(viewModel).

On the view side, data is rendered to the web page as follows:

<ul data-bind="foreach: categories">
<li><span data-bind="text: $data.CategoryName"></span></li>
</ul>

The final state of the web page is:

<!DOCTYPE>
<html>
<head>
    <title></title>
    <script src="Scripts/jquery-1.7.2.js" type="text/javascript"></script>
    <script src="Scripts/knockout-2.1.0.js" type="text/javascript"></script>
    <script src="Scripts/datajs-1.0.3.js" type="text/javascript"></script>
</head>
<body>
    <ul data-bind="foreach: categories">
        <li><span data-bind="text: $data.CategoryName"></span></li>
    </ul>
    <script type="text/javascript">
        var ServiceURL = "
http://services.odata.org/Northwind/Northwind.svc";

        OData.read(ServiceURL + "/Categories?$format=json", CallbackFunction);

        function CallbackFunction(data, request) {
            var viewModel = {
                categories: data.results
            };
            ko.applyBindings(viewModel);
        }
    </script>
</body>
</html>

This example, of course, a very simplistic. It does, however, illustrate how the various pieces fit together involving KnockoutJS and oData.

Hit F5 in Visual Studio 2010.

You may receive a browser warning because a request is being made to another server at http://www.odata.org. I received the following dialog, and clicked on the “Yes” button:

image

The output is identical to my previous post. The difference, this time, is that we are using plain HTML and all data access is being done using client-side technologies.

image