Search results matching tag 'SQL Server 2008'

SQLSaturday #216 Krasnodar Event Recap

mmateev — Sun, 19 May 2013 10:45:28 GMT

Infragistics Inc. was a gold sponsor of BI conference SQLSaturday # 216 Krasnodar. Company was also presented at SQLSaturday Krasnodar by me by me as a speaker.
The event was held on Saturday, May 18th 2013 at Business Center "Caucasus" in Krasnodar.

The event was organized from PASS and supported from the Krasnodar SQL user group, Microsoft Russia, Infragistics Inc, Red Gate Software and other companies.

It was an very well organized event involving many good SQL, BI and. Net professionals from Russia, Ukraine and Bulgaria. Special Thanks to Andrey Korshikov, the Russian PASS community lead and RM, who did everything to be this conference successful.

There was 9 presentations in 2 tracks: Development, BI, DBA, Azure and other areas. Event schedule is available here.

Infragistics participation in the event:

There was 2 technical presentations from Infragistics Inc.:

Spatial Data with SQL Server Reporting Services : Speaker – Mihail Mateev

Windows Azure SQL Reporting for App developers : Speaker – Mihail Mateev

At the conference I presented the products of Infragistics Inc on behalf of the company as a sponsor

There was also an interest in the Infragistics solutions, related to Data Visualization and Business Solutions. Infragistics presentations included samples solutions with WPF and ASP.Net MVC implemented with Infragistics components (NetAdvantage and Ignite UI). At the conference were showcased and the mobile Infragistics applications - SharePlus and ReportPlus

Presentations could be downloaded here:

Follow news from Infragistics for more information about new Infragistics events.

As always, you can follow us on Twitter @mihailmateev and @Infragistics and stay in touch on Facebook, Google+ andLinkedIn!

How to Integrate Ignite UI Pivot Grid in ASP.Net MVC Applications

mmateev — Fri, 17 May 2013 08:26:00 GMT

Ignite UI Pivot Grid is a WEB client (HTML5/jQuery/jQuery.UI based ) implementation of the very successful Infragistics Pivot Grid (xamPivotGrid) , well known from XAML platforms like WPF and Silverlight. Pivot Grid is a component that provides the same functionalities like Pivot Table and developers could embed this control in their applications and get pivot table functionalities out of the box.

Introduction

A pivot table is a program tool that allows you to reorganize and summarize selected columns and rows of data in a spreadsheet or database table to obtain a desired report. A pivot table doesn't actually change the spreadsheet or database itself.

The term pivot table is a generic phrase used by multiple vendors. In the United States, Microsoft Corporation has trademarked the specific form PivotTable. Pivot tables can be seen as a simplification of the more complete and complex OLAP concepts.

On-Line Analytical Processing (OLAP) is a category of software technology that enables analysts, managers and executives to gain insight into data through fast, consistent, interactive access to a wide variety of possible views of information that has been transformed from raw data to reflect the real dimensionality of the enterprise as understood by the user.
OLAP functionality is characterized by dynamic, multi-dimensional analysis of consolidated enterprise data supporting end user analytical and navigational activities including:

calculations and modeling applied across dimensions, through hierarchies and/or across members
trend analysis over sequential time periods
slicing subsets for on-screen viewing
drill-down to deeper levels of consolidation
reach-through to underlying detail data
rotation to new dimensional comparisons in the viewing area

Microsoft SQL Server Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by letting you design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases.

XMLA

XML for Analysis (XMLA) is a SOAP-based XML protocol, designed specifically for universal data access to any standard multidimensional data source that can be accessed over an HTTP connection. Analysis Services uses XMLA as its only protocol when communicating with client applications.

As a developer, you can use XMLA to integrate a client application with Analysis Services. Developers can use XMLA to integrate a client application with Analysis Services, without any dependencies on the .NET Framework or COM interfaces. Applications require XMLA and an HTTP connection to Analysis Services.

In this study we will not talk about how to set XMLA http access for SQL Server Analytics Services (SASS).

You could learn how to set up an SQL server that provides analysis services and XMLA HTTP Access from the great article “How to set up XMLA HTTP Access for SQL Server Analysis Service 2008 and access the Adventure Works 2008 from an Infragistics PivotGrid application”.

XMLA and Ignite UI Pivot Grid

Pivot Grid to use XMLA via Infragistics Data Source (igDataSource) using serverUrl option

There are two main options:

Direct XMLA provider via SQL Server HTTP Access
Remote XMLA provider (using XMLA via controller / WEB service)

Often requirements could be to use a different format to transfer data between WEB service and WEB client (for example JSON, which is very popular last years)

In this study we will demonstrate direct and remote XMLA access and also how can implement service with a different format (JSON)

Sample Demo

Sample demo is an ASP.Net MVC 4 project, generated with Visual Studio 2012

We have 2 projects in the solution:

1. WEB client – ASP.Net MVC 4

2. ASMX service (used for JSON serialization of the XMLA data)

In the WEB client (ASP.Net MVC 4 ) application there are :

3. PivotGridController (contains controllers implementations for all views (direct and remote XMLA, remote JSON)

4. Views :

DirectXmla (Direct XMLA implementation)
Index (Remote JSON via XMLA )
RemoteXmlaProvider (Remote XMLA implementation)

5. OlabWebService (ASMX service, used for JSON serialization )

6. WEB config (you need to allow Cross-origin resource sharing for ASMX service)

Demo project structure

Web Service

Landing page layout

From the landing page there are added three links that calls controllers / respectively return views for the each case that the sample covers:

Remote JSON provider
Remote XMLA provider
Direct XMLA

Direct XMLA Access

The Controller (In this case controller just returns a view)

   1: public ActionResult DirectXmla()   2: {   3:     return View();   4: }

Data source definition.

Here the Url points to the address for SQL Server HTTP Access for OLAP

   1: dataSource = new $.ig.OlapXmlaDataSource({   2:     serverUrl: 'http://sampledata.infragistics.com/olap/msmdpump.dll',   3:     catalog: 'Adventure Works DW Standard Edition',   4:     cube: 'Adventure Works',   5:     rows: '[Date].[Calendar]',   6:     columns: '[Product].[Product Categories]',   7:     measures: '[Measures].[Internet Order Count]'   8: });

Ignite UI PivotGrid definition – direct XMLA (igPivotGrid just uses data source – there are no specific settings)

   1: $directGrid.igPivotGrid({   2:     dataSource: dataSource,   3:     width: "915px",   4:     height: "450px",   5:     hideFiltersDropArea: true,   6:     disableColumnsDropArea: true,   7:     disableRowsDropArea: true,   8:     disableMeasuresDropArea: true,   9:     tupleMemberExpanding: function (evt, ui) {  10:         toggleTupleMember(true, $directGrid, ui.axisName, ui.tupleIndex, ui.memberIndex);  11:     },  12:     tupleMemberCollapsing: function (evt, ui) {  13:         toggleTupleMember(false, $directGrid, ui.axisName, ui.tupleIndex, ui.memberIndex);  14:     }  15: });

Remote XMLA Provider

Controller implementation. In this case you need to add a specific attribute “remote-xmla-provider-endpoint” for your action in the controller. XmlaDataSourceModel is a class implemented in Ignite UI .Net libraries and you need to use it as a context for your view. To initialize the model you need to add a server Url for OLAP HTTP access.

   1: [XmlaDataSourceAction]   2:  [ActionName("remote-xmla-provider-endpoint")]   3:  public ActionResult RemoteXmlaProviderEndpoint()   4:  {   5:      return View(new XmlaDataSourceModel { ServerUrl = "http://sampledata.infragistics.com/olap/msmdpump.dll" });   6:  }

Remote DataSource. In this case for serverUrl you need to use the action in the MVC controller: '@Url.Action("remote-xmla-provider-endpoint")'

   1: remoteDataSource = new $.ig.OlapXmlaDataSource({   2:      isRemote: true,   3:      serverUrl: '@Url.Action("remote-xmla-provider-endpoint")',   4:      catalog: 'Adventure Works DW Standard Edition',   5:      cube: 'Adventure Works',   6:      rows: '[Date].[Calendar]',   7:      columns: '[Product].[Product Categories]',   8:      measures: '[Measures].[Internet Order Count]'   9:  });

Pivot Grid implementation (there is nothing specific – all differences are in igDataSource).

   1: $remoteGrid.igPivotGrid({   2:     dataSource: remoteDataSource,   3:     width: "915px",   4:     height: "450px",   5:     hideFiltersDropArea: true,   6:     disableColumnsDropArea: true,   7:     disableRowsDropArea: true,   8:     disableMeasuresDropArea: true,   9:     tupleMemberExpanding: function (evt, ui) {  10:         toggleTupleMember(true, $remoteGrid, ui.axisName, ui.tupleIndex, ui.memberIndex);  11:     },  12:     tupleMemberCollapsing: function (evt, ui) {  13:         toggleTupleMember(false, $remoteGrid, ui.axisName, ui.tupleIndex, ui.memberIndex);  14:     }  15: });

Remote JSON (using WEB service *.asmx)

The Controller

When you have own serialization controller just returns the view

   1: public ActionResult Index()   2: {   3:     return View();   4: }

Remote JSON WEB service (ASMX service)

In your implementation you need to create XmlaDataSourceModel and XmlaDataSourceManager that should be used to create a query.

Result is serialized as JSON – You can use for the your web method an attribute [ScriptMethod(ResponseFormat = ResponseFormat.Json)].

   1: /// <summary>   2: /// Summary description for OlapWebService   3: /// </summary>   4: [ScriptService]    5: [WebService(Namespace = "http://tempuri.org/")]   6: [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]   7: [System.ComponentModel.ToolboxItem(false)]   8: public class OlapWebService : System.Web.Services.WebService   9: {  10:    11:     [WebMethod]  12:     [ScriptMethod(ResponseFormat = ResponseFormat.Json)]  13:     public string GetAdventureWorks()  14:     {  15:         const string serverUrl = "http://sampledata.infragistics.com/olap/msmdpump.dll";  16:    17:         var model = new XmlaDataSourceModel { ServerUrl = serverUrl };  18:         var mng = new XmlaDataSourceManager();  19:         string result = mng.DoQuery(model, this.Context.Request);  20:    21:         return result;  22:     }  23: }

Remote JSON view implementation

Remote Data Source using JSON (you need to use for serverUrl the Url of the your web method in the asmx service)

   1: remoteDataSource = new $.ig.OlapXmlaDataSource({   2:     isRemote: true,   3:     serverUrl: 'http://localhost:14889/OlapWebService.asmx/GetAdventureWorks',   4:     catalog: 'Adventure Works DW Standard Edition',   5:     cube: 'Adventure Works',   6:     rows: '[Date].[Calendar]',   7:     columns: '[Product].[Product Categories]',   8:     measures: '[Measures].[Internet Order Count]'   9: });

Ignite UI Pivot Grid implementation (it is the same like in Direct XAML and Remote XMLA implementations)

   1: $remoteGrid.igPivotGrid({   2:     dataSource: remoteDataSource,   3:     width: "915px",   4:     height: "450px",   5:     hideFiltersDropArea: true,   6:     disableColumnsDropArea: true,   7:     disableRowsDropArea: true,   8:     disableMeasuresDropArea: true,   9:     tupleMemberExpanding: function (evt, ui) {  10:         toggleTupleMember(true, $remoteGrid, ui.axisName, ui.tupleIndex, ui.memberIndex);  11:     },  12:     tupleMemberCollapsing: function (evt, ui) {  13:         toggleTupleMember(false, $remoteGrid, ui.axisName, ui.tupleIndex, ui.memberIndex);  14:     }  15: });

WEB config file

You need to add the lines below to your WEB.config file (you need to allow Cross-origin resource sharing for ASMX service). IE now 10 doesn’t require this settings but you need to add it for other browsers.

   1: <system.webServer>   2:   ....   3:   <httpProtocol>   4:     <customHeaders>   5:       <add name="Access-Control-Allow-Origin" value="*" />   6:       <add name="Access-Control-Allow-Headers" value="Origin, X-Requested-With, Content-Type, Accept" />   7:     </customHeaders>   8:   </httpProtocol>   9: </system.webServer>

Running Pivot using JSON via WEB service

Remote JSON implementation (this is the first option)

Select Remote XMLA link to open the view with Remote XMLA implementation. In this case you will see that the data traffic from the remote WEB server to your client is more than 5 times more larger.

Select Direct XMLA link to redirect to Direct XMLA view. In this case you will see the pivot grid with the same data

Conclusions:

Infragistics Ignite UI supports XAML out of the box via Data Source (igDataSource) option “serverUrl”

You could use remote and direct XMLA providers. It is easy to implement own WEB service where to serialize data to JSON and Data Source could use this format.

Remote access leads to less data transfer and it will be better to use this approach (doesn’t matter which format you are using: XMLA or JSON)

Sample source code with all referenced scripts and libraries you could download here:

You are probably thinking, how do I get my hands on Ignite UI Pivot Grid? It’s easy.

Click on this image to get a fully support trial version of Infragistics Ignite UI controls:

To view all the samples and code for HTML, MVC & ASP.NET, click here: .

http://www.infragistics.com/products/jquery/samples

Follow news from Infragistics for more information about new Infragistics products.

As always, you can follow us on Twitter @mihailmateev and @Infragistics and stay in touch on Facebook, Google+andLinkedIn!

Plovdiv UG Spring 2013 Meeting - Event Recap

mmateev — Mon, 29 Apr 2013 12:02:31 GMT

On April 26 was held Plovdiv UG Spring 2013 Meeting. The event is organized by Microsoft Bulgaria and Plovdiv. Net User Group featuring two speakers from Infragistics Friends / Bulgarian BI PASS Chapter: Ivan Donev and Mihail Mateev

The event was held in the new building of the University of Plovdiv in Bulgaria Blvd 236, Room No. 422, 4th floor in the central university block.

Plovdiv UG Spring 2013 Meeting was the largest community event, organized in Plovdiv by now

Event Schedule:

14:30 - 15:30:
Subject: SQL Server Performance tuning basics
The session will cover the basics for SQL Server troubleshooting and performance optimization - what we can use to discover where the issue is, what we can use to help us and what are the free tools that can help us in the investigation.
Speaker: Ivan Donev.

Presentation could ne downloaded here:

15:45 - 16:45:
Subject: SharePoint 2013 Apps
Speaker: Radi Atanassov

17:00 - 18:00:
Optimizing WPF Application Performance
Short description: This presentation is for WPF application developers who are looking for ways to improve the performance of their applications. The design of your WPF application can impact its performance by creating unnecessary overhead in calculating layout and validating object references. There are different areas where you can improve your apps performance: Application Startup Time, Application Resources, Data Binding, Optimal usage of hardware, Optimizing the layout and design. In this talk you will see useful examples and best practices when you need to improve your WPF apps performance.
Speaker: Mihail Mateev

Slides could be downloaded here:

Ivan Donev is presenting about SQL Server performance

The event demonstrated a good relationship between Microsoft Bulgaria and different user groups in the cities of Sofia and Plovdiv. Thanks to Infragistics for supporting our user groups (Infragistics Friends and Bulgarian PASS BI Chapter) that are actively involved in the local IT community.

Follow news from Infragistics for more information about new Infragistics events.

As always, you can follow us on Twitter @mihailmateev and @Infragistics and stay in touch on Facebook, Google+ andLinkedIn!

SQLSaturday #196 Denmark Event Recap

mmateev — Mon, 29 Apr 2013 10:08:00 GMT

Infragistics was presented at SQLSaturday Denmark by me by me as a speaker.
The event was held on Saturday, April 20th 2013 at Microsoft Denmark office, Copenhagen.

The event was organized from PASS and supported from the Denmark SQL user group, Microsoft, SolidQ, Redgate and many other sponsors. It was an extremely well organized event involving many good SQL, BI and. Net professionals.
There was also an interest in the Infragistics solutions, related to Data Visualization and Business Solutions. Infragistics presentation samples about Spatial Data and Windows Azure SQL Database includes solutions with WPF and ASP.Net MVC implemented with Infragistics components (NetAdvantage and Ignite UI). Many professionals from Scandinavia knows Infragistics brand and sent a very good feedback about Infragistics products .

Some of SQLSaturday Denmark presentations

Microsoft Denmark

There was 28 presentations in 4 tracks: Development, BI, DBA, Azure and other areas. Event schedule is available here.

Infragistics participation in the event:

There was 1 technical presentation from Infragistics Inc.:

Spatial Data and Windows Azure SQL Database : Speaker - Mihail Mateev

Presentation could be downloaded here:

Follow news from Infragistics for more information about new Infragistics events.

As always, you can follow us on Twitter @mihailmateev and @Infragistics and stay in touch on Facebook, Google+ andLinkedIn!

24 Hours of PASS. Russian Edition Event Recap

mmateev — Sat, 23 Mar 2013 10:39:30 GMT

On March 21 was held the second online conference "24 Hours of PASS. Russian Edition". On March 21 was held the second online conference "24 Hours of PASS. Russian Edition". The conference is organized by PASS (The Professional Association for SQL Server) and presents lectures on SQL Server, Windows Azure and BI with the help of the Russian SQL Server community,

Infragistics Inc was presented at the conference in me as a speaker. I presented the lecture "Spatial Data and Windows Azure SQL Database" in Russian. There was demonstrated also demos with Windows Azure WEB Sites, including ASP.Net MVC 4 applications with Ignite UI and Entity Framework 5. This was the first Infragistics presentation for Russian online conferences. Special thanks to Andrey Korshikov and Denis Reznik for the help and the amazing organization!

I can summarize that it was a great event with noticeable participation of Infragistics.

Live demos (Windows Azure WEB Sites, including ASP.Net MVC 4 applications with Ignite UI and Entity Framework 5) can be seen here.

More information about the conference can be found here. Event schedule is available here.

Follow news from Infragistics for more information about new Infragistics events.

As always, you can follow us on Twitter @mihailmateev and @Infragistics and stay in touch on Facebook, Google+andLinkedIn!

Infragistics will host a SQLSaturday Bulgaria for the second time

mmateev — Thu, 06 Dec 2012 14:58:51 GMT

For the second time in Bulgaria SQLSaturday will be held on 26 of October, 2013 at Infragistics Bulgaria office: 110 B, Simeonovsko Shosse Bul., Office Floors II and III, 1700 Sofia, Bulgaria. The event will be of interest to all specialists, using any version of SQL server and/or other BI related technologies from Microsoft - developers, administrators and analysts. The conference is supported by Microsoft Bulgaria and all Microsoft user groups in this region.

After the successful SQLSaturday # 152 Bulgaria 2012 is proud to announce that PASS entrusted Infragistics to host SQLSaturday # 199 Bulgaria 2013

What is SQL Saturday:

SQL Saturday is a conference of PASS (Professional Association for SQL Server) www.sqlpass.org ,www.sqlsaturday.com
The event was announced officially on PASS SQLSaturday site : http://www.sqlsaturday.com/199/eventhome.aspx
The conference shall be held on Saturday of pre-selected location.

SQLSaturday Bulgaria 2012 was the biggest SQL Server and BI related event ever organized in the country.

SQLSaturday #152 Bulgaria Event Summary:

219 registered for the event
About 150 participants
21 presentations in 4 tracks: Development, DBA, BI and Windows Azure SQL Database & Personal Development
15 speakers from 6 countries: USA, UK, Austria, Denmark, Poland and Bulgaria

The conference schedule could be found here:

SQLSaturday #199 Bulgaria 2013

We plan to offer 24 conference lectures. Presentations are in several tracks related to Microsoft SQL Server:

DBA (Database Administration)
Development
BI (Business Intelligence)
Windows Azure & Windows Azure SQL Database
Personal Development

It is expected next year SQLSaturday Bulgaria to has more than 200 participants.

The conference will be interesting for those of you who wish to learn more about SQL Server, SQL Reporting Services, Windows Azure, SharePoint, NoSQL databases and data visualization in the mentioned above areas. You can register here.

If you are speaker and have appropriate topics you can submit your session by using the call for speakers page.

Sponsors who are interested in the event can register here.

To see all news about SQLSaturday Bulgaria 2013 follow #sqlsat199 on Twitter.

Follow news from Infragistics for more information about new Infragistics events.

As always, you can follow us on Twitter @mihailmateev and @Infragistics and stay in touch on Facebook, Google+ andLinkedIn!

SQLSaturday #179 Kharkov Event Recap

mmateev — Mon, 26 Nov 2012 06:00:00 GMT

Infragistics was presented at SQLSaturday Kharkov by me by me as a speaker.
The event was held on Saturday, November 24th at pr. Moskovsky, 199-b, Conference Hall «Sun City Premium», Kharkov, Ukraine.

Administrators at the conference was Andrey Korshikov – PASS Regional Mentor for Central Eastern Europe and Denis Reznik – Kharkov SQL User group lead. Special guest was

Niko Carvalho Neugebauer – PASS Community Evangelist.

This is the first Infragistics event in Ukraina. Infragistics Inc. was the only one component vendor with a speaker at the conference. Participants gave a good feedback about Infragistics presentations. There was also an interest in the Infragistics solutions, related to Data Visualization and Business Solutions. Infragistics presentation samples for SQL Spatial data include solutions with WPF, Silverlight and jQuery implemented with Infragistics components (NetAdvantage and Ignite UI). Hope in the near feature to have more closer contacts with professionals, community members and companies from this region.

Registration:

Niko Neugebauer opens the conference:

Denis Reznik in action:

Attendees:

Coffee break:

Summary:

There was 14 presentations in 3 tracks: Development, BI and DBA.

Infragistics participation in the event:

There was 2 technical presentation from Infragistics Inc.:

Windows Azure SQL Reporting for App developers : Mihail Mateev

Spatial Data and Windows Azure SQL Database : Mihail Mateev

Follow news from Infragistics for more information about new Infragistics events.

As always, you can follow us on Twitter @mihailmateev and @Infragistics and stay in touch on Facebook, Google+ and LinkedIn!

SQLSaturday #178 Moscow Event Recap

mmateev — Thu, 22 Nov 2012 11:45:00 GMT

Infragistics had involvement SQLSaturday # 178 Moscow impersonated by me as a speaker.
The event was held on Saturday, November 17th at Microsoft Russia office, Moscow.

Administrators at the conference was Andrey Korshikov – PASS Regional Mentor for Central Eastern Europe.

This is the first Infragistics event in Russia. Infragistics Inc. was the only one component vendor with a speaker at the conference. Participants expressed interest in the Infragistics solutions, related to Data Visualization and Business Solutions. Hope in the near feature to have more closer contacts with professionals, community members and companies from this region.

Registration

Microsoft Russia Office

Coffee break between sessions

Summary:

There was 15 presentations in 3 tracks: Development, BI and DBA.

Infragistics participation in the event:

There was 2 technical presentation from Infragistics Inc.:

Windows Azure SQL Reporting for App developers : Mihail Mateev

Spatial Data and Windows Azure SQL Database : Mihail Mateev

Follow news from Infragistics for more information about new Infragistics events.

As always, you can follow us on Twitter @mihailmateev and @Infragistics and stay in touch on Facebook, Google+ and LinkedIn!

Infragistics XamGantt Control - Using Entity Framework Code First to Persist Projects in a Database

mmateev — Sun, 21 Oct 2012 12:27:39 GMT

Infragistics Gantt Control helps to create own project planning applications, that can contain most of the Microsoft Project features. One of the most common scenario is to store project data in a database (Microsoft SQL Server or other).

Developers are always looking for the easiest way for the implementation of the solution. In this case, it is reasonable to use an ORM framework. This article will demonstrate how to use Entity Framework with Code First approach.

Entity Framework 4.x and 5.x contains both the Code First approach and the DbContext API. This API provides a more productive surface for working with the Entity Framework and can be used with the Code First, Database First, and Model First approaches.

Entity Framework Code First allows you to create data model classes prior to creating a database. When you run the application for the first time, a database is created for you on the fly based on the classes you created. Database initializers allow you to decide a strategy of database creation and seed data generation. Code First provides its own set of database initializer classes and also allows you to create your own. This approach is a very convenient when you need to distribute your application. Customers need only to have a SQL Server installed.

Let’s create a simple WPF application and add XamGantt control instance inside it. You can start with the samples from my previous post: “How to Start Using Infragistics XamGantt Control” , "Data Exchange with Infragistics XamGantt Control - Using XML (Part 1)" or “Data Exchange with Infragistics XamGantt Control - Import/Export to Microsoft Project (Part 2)”. Add buttons to call import from and export to project. If you want to create a Silverlight simple you can use the same snippets, mentioned for WPF application.

What you need in advance:

To have some experience in the use of Entity Framework 4.x or 5.x
To install NetAdvantage for WPF vol. 12.2 or NetAdvantage for Silverlight Vol. 12.2 (these products are also included in NetAdvantage for .NET and NetAdvantage Ultimate)
To install EntityFramework, run the following command in the Package Manager Console PM> Install-Package EntityFramework

Create the Model

Let’s define a very simple model using classes. You can start with the models samples from my previous post “Data Exchange with Infragistics XamGantt Control - Import/Export to Microsoft Project (Part 2)”.

Create a Context

The simplest way to start using the classes for data access is to define a context that derives from System.Data.Entity.DbContext and exposes a typed DbSet<TEntity> for each class in my model.

You need to add a reference to the EF assembly

Project -> Add Reference…
Select the “.NET” tab
Select “EntityFramework” from the list
Click “OK”

You’ll also need a reference to the System.Data.Entity assembly:

Project -> Add Reference…
Select the “.NET” tab
Select “System.Data.Entity” from the list
Click “OK”

Let’s create a derived context:

Add a derived context below the existing classes that we’ve defined. You need to inherit System.Data.Entity.DbContext. By default when you run the application your database should be created if it doesn’t exist.

   1: public class DbProjectViewModel : DbContext, INotifyPropertyChanged   2:  {   3:     4:      #region SelectedTask   5:      private TaskModel _selectedTask;   6:      public TaskModel SelectedTask   7:      {   8:          get   9:          {  10:              return _selectedTask;  11:          }  12:          set  13:          {  14:              if (value != null)  15:              {  16:                  _selectedTask = value;  17:              }  18:    19:              NotifyPropertyChanged("SelectedTask");  20:          }  21:      }  22:      #endregion SelectedTask  23:    24:      #region Tasks  25:      private DbSet<TaskModel> _tasks;  26:      public DbSet<TaskModel> Tasks {get; set;}  27:        28:      #endregion //Tasks  29:    30:      #region NotifyPropertyChanged  31:      public event PropertyChangedEventHandler PropertyChanged;  32:      protected void NotifyPropertyChanged(String info)  33:      {  34:          if (PropertyChanged != null)  35:          {  36:              PropertyChanged(this, new PropertyChangedEventArgs(info));  37:          }  38:      }  39:      #endregion NotifyPropertyChanged  40:    41:  }

If you do not want to generate a database at all, as you already have a database, you can add the following to the DbProjectViewModel constructor:

   1: #region Constructor   2: public DbProjectViewModel()   3:    : base()   4: {   5:    Database.SetInitializer<DbProjectViewModel>(null);   6: }   7:     8: public DbProjectViewModel(String connection)   9:    : base(connection)  10: {  11:    Database.SetInitializer<DbProjectViewModel>(null);  12: }  13: #endregion Constructor

Your task model will be the same as in previous posts.

   1: public class TaskModel : ObservableModel   2:  {   3:      public TaskModel():base()   4:      {   5:      }   6:     7:      public TaskModel(TaskModel oldTask)   8:          : base()   9:      {  10:          ConstraintType = oldTask.ConstraintType;  11:          ConstraintDate = oldTask.ConstraintDate;  12:          Id = oldTask.Id;  13:          DeadlineDate = oldTask.DeadlineDate;  14:          TaskName = oldTask.TaskName;  15:          Index = oldTask.Index;  16:          Id = oldTask.Id;  17:          DurationTicks = oldTask.DurationTicks;  18:          Start = oldTask.Start;  19:          IsMilestone = oldTask.IsMilestone;  20:          IsInProgress = oldTask.IsInProgress;              21:      }  22:    23:    24:      #region ConstraintType  25:      private ProjectTaskConstraintType _constraintType;  26:      public ProjectTaskConstraintType ConstraintType  27:      {  28:          get  29:          {  30:              return _constraintType;  31:          }  32:          set  33:          {  34:              if (_constraintType != value)  35:              {  36:                  _constraintType = value;  37:                  this.NotifyPropertyChanged("ConstraintType");  38:              }  39:          }  40:      }  41:      #endregion ConstraintType  42:    43:      #region ConstraintDate  44:      private DateTime? _constraintDate;  45:      public DateTime? ConstraintDate  46:      {  47:          get  48:          {  49:              return _constraintDate;  50:          }  51:          set  52:          {  53:              if (_constraintDate != value)  54:              {  55:                  _constraintDate = value;  56:                  this.NotifyPropertyChanged("ConstraintDate");  57:              }  58:          }  59:      }  60:      #endregion ConstraintDate  61:    62:      #region DeadlineDate  63:      private DateTime? _deadlineDate;  64:      public DateTime? DeadlineDate  65:      {  66:          get  67:          {  68:              return _deadlineDate;  69:          }  70:          set  71:          {  72:              if (_deadlineDate != value)  73:              {  74:                  _deadlineDate = value;  75:                  this.NotifyPropertyChanged("DeadlineDate");  76:              }  77:          }  78:      }  79:      #endregion //DeadlineDate  80:    81:      #region DurationFormat  82:      private ProjectDurationFormat _durationFormat;  83:      public ProjectDurationFormat DurationFormat  84:      {  85:          get  86:          {  87:              return _durationFormat;  88:          }  89:          set  90:          {  91:              if (_durationFormat != value)  92:              {  93:                  _durationFormat = value;  94:                  this.NotifyPropertyChanged("DurationFormat");  95:              }  96:          }  97:      }  98:      #endregion DurationFormat  99:   100:      #region Duration 101:      private TimeSpan _duration; 102:   103:      //[XmlIgnore] 104:      [NotMapped] 105:      public TimeSpan Duration 106:      { 107:          get 108:          { 109:              return _duration; 110:          } 111:          set 112:          { 113:              if (_duration != value) 114:              { 115:                  _duration = value; 116:                  this.NotifyPropertyChanged("Duration"); 117:              } 118:          } 119:      } 120:      #endregion Duration 121:   122:      #region DurationTicks 123:   124:      // Pretend property for serialization 125:      [XmlElement("Duration")] 126:      public long DurationTicks 127:      { 128:          get { return _duration.Ticks; } 129:          set { _duration = new TimeSpan(value); } 130:      } 131:   132:      #endregion DurationTicks 133:   134:      #region Id 135:      private Int32 _id; 136:   137:   138:      public Int32 Id 139:      { 140:          get 141:          { 142:              return _id; 143:          } 144:          set 145:          { 146:              if (_id != value) 147:              { 148:                  _id = value; 149:                  this.NotifyPropertyChanged("Id"); 150:              } 151:          } 152:      } 153:      #endregion Id 154:   155:      #region Index 156:      private TimeSpan _index; 157:   158:   159:      public TimeSpan Index 160:      { 161:          get 162:          { 163:              return _index; 164:          } 165:          set 166:          { 167:              if (_index != value) 168:              { 169:                  _index = value; 170:                  this.NotifyPropertyChanged("Index"); 171:              } 172:          } 173:      } 174:      #endregion Index 175:   176:      #region IsMilestone 177:      private bool _isMilestone = false; 178:      public bool IsMilestone 179:      { 180:          get 181:          { 182:              return _isMilestone; 183:          } 184:          set 185:          { 186:              if (_isMilestone != value) 187:              { 188:                  _isMilestone = value; 189:                  this.NotifyPropertyChanged("IsMilestone"); 190:              } 191:          } 192:      } 193:      #endregion IsMilestone 194:   195:      #region IsInProgress 196:      private bool _isInProgress = true; 197:      public bool IsInProgress 198:      { 199:          get 200:          { 201:              return _isInProgress; 202:          } 203:          set 204:          { 205:              if (_isInProgress != value) 206:              { 207:                  _isInProgress = value; 208:                  this.NotifyPropertyChanged("IsInProgress"); 209:              } 210:          } 211:      } 212:      #endregion IsInProgress 213:   214:      #region IsUndetermined 215:      private bool _isUndetermined = false; 216:      public bool IsUndetermined 217:      { 218:          get 219:          { 220:              return _isUndetermined; 221:          } 222:          set 223:          { 224:              if (_isUndetermined != value) 225:              { 226:                  _isUndetermined = value; 227:                  this.NotifyPropertyChanged("IsUndetermined"); 228:              } 229:          } 230:      } 231:      #endregion //IsUndetermined 232:   233:      #region Predecessors 234:      private string _predecesors; 235:      public string Predecessors 236:      { 237:          get { return _predecesors; } 238:          set 239:          { 240:              if (value != _predecesors) 241:              { 242:                  _predecesors = value; 243:                  this.NotifyPropertyChanged("Predecessors"); 244:              } 245:          } 246:      } 247:      #endregion //Predecessors 248:   249:      #region PredecessorsIdText 250:      private string _predecessorsIdText; 251:      public string PredecessorsIdText 252:      { 253:          get { return _predecessorsIdText; } 254:          set 255:          { 256:              if (value != _predecessorsIdText) 257:              { 258:                  _predecessorsIdText = value; 259:                  this.NotifyPropertyChanged("PredecessorsIdText"); 260:              } 261:          } 262:      } 263:      #endregion //PredecessorsIdText 264:   265:      #region Successors 266:      private string _successors; 267:      public string Successors 268:      { 269:          get { return _successors; } 270:          set 271:          { 272:              if (value != _successors) 273:              { 274:                  _successors = value; 275:                  this.NotifyPropertyChanged("Successors"); 276:              } 277:          } 278:      } 279:      #endregion //Successors 280:   281:      #region Start 282:      private DateTime? _start; 283:      public DateTime? Start 284:      { 285:          get 286:          { 287:              return _start; 288:          } 289:          set 290:          { 291:              if (_start != value) 292:              { 293:                  _start = value; 294:                  this.NotifyPropertyChanged("Start"); 295:              } 296:          } 297:      } 298:      #endregion Start 299:   300:      #region Finish 301:      private DateTime? _finish; 302:      public DateTime? Finish 303:      { 304:          get 305:          { 306:              return _finish; 307:          } 308:          set 309:          { 310:              if (_finish != value) 311:              { 312:                  _finish = value; 313:                  this.NotifyPropertyChanged("Finish"); 314:              } 315:          } 316:      } 317:      #endregion Finish 318:   319:   320:      #region TaskID 321:      private string _taskId; 322:      public string TaskID 323:      { 324:          get 325:          { 326:              return _taskId; 327:          } 328:          set 329:          { 330:              if (_taskId != value) 331:              { 332:                  _taskId = value; 333:                  this.NotifyPropertyChanged("TaskID"); 334:              } 335:          } 336:      } 337:      #endregion TaskID 338:   339:      #region Tasks 340:      private string _tasks; 341:      public string Tasks 342:      { 343:          get 344:          { 345:              return _tasks; 346:          } 347:          set 348:          { 349:              if (_tasks != value) 350:              { 351:                  _tasks = value; 352:                  this.NotifyPropertyChanged("Tasks"); 353:              } 354:          } 355:      } 356:      #endregion Tasks 357:   358:      #region TaskName 359:      private string _name; 360:      [Key] 361:      public string TaskName 362:      { 363:          get 364:          { 365:              return _name; 366:          } 367:          set 368:          { 369:              if (_name != value) 370:              { 371:                  _name = value; 372:                  this.NotifyPropertyChanged("TaskName"); 373:              } 374:          } 375:      } 376:      #endregion TaskName 377:   378:      #region ResourceName 379:      private string _resourceName; 380:      public string ResourceName 381:      { 382:          get 383:          { 384:              return _resourceName; 385:          } 386:          set 387:          { 388:              if (_resourceName != value) 389:              { 390:                  _resourceName = value; 391:                  this.NotifyPropertyChanged("ResourceName"); 392:              } 393:          } 394:      } 395:      #endregion ResourceName 396:   397:  } 398: }

Add an instance of your derived context in the sample application. You can use it in the same way like you will bind XamGantt control to a model with custom tasks, demonstrated in the blog "How to Start Using Infragistics XamGantt Control".

   1: <Grid.Resources>   2:     <models:DbProjectViewModel x:Key="viewmodel" />   3: </Grid.Resources>

You need to cerate a ListBackedProject and a Gantt control instances. ListBackedProject.TaskItemsSource is bound to Tasks.Local that returns ObservableCollection<TaskModel>.

   1: <ig:ListBackedProject x:Name="dataProvider" TaskItemsSource="{Binding Tasks.Local}">   2:      <ig:ListBackedProject.TaskPropertyMappings>   3:          <ig:ProjectTaskPropertyMappingCollection UseDefaultMappings="True">   4:     5:              <!--Mandatory mappings -->   6:              <ig:ProjectTaskPropertyMapping TaskProperty="DataItemId"    7:                                 DataObjectProperty="TaskID" />   8:              <ig:ProjectTaskPropertyMapping TaskProperty="ConstraintType"    9:                                 DataObjectProperty="ConstraintType" />  10:              <ig:ProjectTaskPropertyMapping TaskProperty="ConstraintDate"   11:                                 DataObjectProperty="ConstraintDate" />  12:              <ig:ProjectTaskPropertyMapping TaskProperty="DurationFormat"   13:                                 DataObjectProperty="DurationFormat" />  14:              <ig:ProjectTaskPropertyMapping TaskProperty="Tasks"   15:                                 DataObjectProperty="Tasks" />  16:              <!--End of mandatory mappings -->  17:    18:              <!--Manual tasks mapping -->  19:    20:              <ig:ProjectTaskPropertyMapping TaskProperty="IsManual"   21:                                 DataObjectProperty="IsUndetermined" />  22:    23:              <!--End of maual tasks mapping -->  24:    25:              <!--Task link mapping -->  26:    27:              <ig:ProjectTaskPropertyMapping TaskProperty="Predecessors" DataObjectProperty="Predecessors" />  28:    29:              <!--End of task link mapping -->  30:    31:              <!--Other tasks mapping -->  32:    33:              <ig:ProjectTaskPropertyMapping TaskProperty="IsActive"   34:                                 DataObjectProperty="IsInProgress" />  35:    36:              <ig:ProjectTaskPropertyMapping TaskProperty="Finish"   37:                                         DataObjectProperty="Finish" />  38:    39:              <ig:ProjectTaskPropertyMapping TaskProperty="DurationFormat"   40:                                         DataObjectProperty="DurationFormat" />                                 41:    42:    43:              <ig:ProjectTaskPropertyMapping TaskProperty="TaskName"   44:                                 DataObjectProperty="TaskName" />  45:    46:              <ig:ProjectTaskPropertyMapping TaskProperty="Start"   47:                                 DataObjectProperty="Start" />  48:    49:    50:              <ig:ProjectTaskPropertyMapping TaskProperty="Duration"   51:                                 DataObjectProperty="Duration" />  52:    53:    54:              <ig:ProjectTaskPropertyMapping TaskProperty="IsMilestone"   55:                                 DataObjectProperty="IsMilestone" />  56:    57:              <ig:ProjectTaskPropertyMapping TaskProperty="Deadline"   58:                                 DataObjectProperty="DeadlineDate" />  59:    60:              <!--End of other tasks mapping -->  61:    62:    63:          </ig:ProjectTaskPropertyMappingCollection>  64:      </ig:ListBackedProject.TaskPropertyMappings>  65:      <ig:ListBackedProject.TaskSettings>  66:          <ig:ProjectTaskSettings AllowChangeIndentation="True"   67:                      AllowDelete="True"  68:                      AllowDragDeadline="True"  69:                      AllowDragMilestone="True"  70:                      AllowDragPercentComplete="True"  71:                      AllowDragSummary="Always"  72:                      AllowDragTask="True"                                73:                      AllowInsert="True"   74:                      AllowResizeSummary="Always"                                          75:                      AllowResizeTask="True"/>  76:      </ig:ListBackedProject.TaskSettings>  77:    78:  </ig:ListBackedProject>

   1: <ig:XamGantt Margin="10" x:Name="xamGantt" VerticalAlignment="Top" ActiveCellChanged="xamGantt_ActiveCellChanged"  Project="{Binding ElementName=dataProvider}">   2:                        <ig:XamGantt.DefaultColumnSettings>   3:                            <ig:ProjectColumnSettings AllowHide="True"   4:                                          AllowMove="True"   5:                                          AllowResize="True"   6:                                          AllowShow="True"  />   7:                        </ig:XamGantt.DefaultColumnSettings>   8:                    </ig:XamGantt>

Save project to database

Let’s first try to save Gantt project to Microsoft SQL Server database. Create a project with tasks in your XamGantt appellation.

To be possible to save tasks with correct data about links you need to add an additional code – here it is implemented in UpdatePredecessorsIdText method.

   1: #region btnSave_Click   2: private void btnSave_Click(object sender, RoutedEventArgs e)   3: {   4:         var model = this.Root.Resources["viewmodel"] as DbProjectViewModel;   5:         if (model != null)   6:         {   7:             UpdatePredecessorsIdText(xamGantt);   8:             SaveGanttProject(model);   9:         }  10: }  11: #endregion btnSave_Click

Unlike previous blogs here should keep not only information about PredecessorsIdText property but also information about the task sequence number is stored in a property named id. Add this property in your custom task model.

When you save and read the model tasks could not be saved/read in the proper number. This property will keep the original task sequence in the Infragistics Gantt project.

   1: #region Id   2: private Int32 _id;   3:     4: public Int32 Id   5: {   6:     get   7:     {   8:         return _id;   9:     }  10:     set  11:     {  12:         if (_id != value)  13:         {  14:             _id = value;  15:             this.NotifyPropertyChanged("Id");  16:         }  17:     }  18: }  19: #endregion Id

Add in the UpdatePredecessorsIdText method code that copy the value from the readonly property ProjectTask.Id to your custom tasks before to save the model.

   1: #region UpdatePredecessorsIdText   2: private void UpdatePredecessorsIdText(XamGantt gantt)   3: {   4:     foreach (ProjectTask task in gantt.Project.RootTask.Tasks)   5:     {   6:         var customTask = task.DataItem as TaskModel;   7:         customTask.PredecessorsIdText = task.PredecessorsIdText;   8:         customTask.Id = task.Id;   9:     }  10: }  11: #endregion UpdatePredecessorsIdText

The real save to your database is a very simple just call SaveChanges() method of your derived context.

   1: #region SaveGanttProject   2: private void SaveGanttProject(DbProjectViewModel model)   3: {   4:     model.SaveChanges();   5: }   6: #endregion SaveGanttProject

Database settings

Connection strings

You can tell DbContext to use this connection by passing the connection string name to the DbContext constructor. For example:

   1: public class DbProjectViewModel : DbContext, INotifyPropertyChanged   2: {   3:         #region Constructor   4:         public DbProjectViewModel()   5:             : base("name=Northwind_Entities")   6:         {   7:             Database.SetInitializer<DbProjectViewModel>(null);   8:         }   9:         //implementation...  10: }

Code First Default Connection Factory

Connection strings go in the standard connectionStrings element and do not require the new entityFramework section.

This configuration section allows you to specify a default connection factory that Code First should use to locate a database to use for a context. The default connection factory is only used when no connection string has been added to the configuration file for a context.

In the sample application is used the approach when you have no connection string and use default connection factory settings.

When you use default connection factory without changes you will use a .\SQLEXPRESS instance.

Where’s My Data?

DbContext by convention created a database for you on localhost\SQLEXPRESS. The database is named after the fully qualified name of your derived context, in our case that is “WPfIgGantImportExport.Models.DbProjectViewModel”. We’ll look at ways to change this later in the walkthrough.

   1: <entityFramework>   2:   <defaultConnectionFactory    3:         type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />   4: </entityFramework>

If you don’t set a default connection factory, Code First uses the SqlConnectionFactory, pointing to .\SQLEXPRESS.SqlConnectionFactory also has a constructor that allows you to override parts of the connection string. If you want to use a SQL Server instance other than .\SQLEXPRESS you can use this constructor to set the server.

   1: <entityFramework>   2:   <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">   3:     <parameters>   4:       <parameter value="Data Source=(local);integrated security=True;multipleactiveresultsets=True"/>   5:     </parameters>   6:   </defaultConnectionFactory>   7: </entityFramework>

Entity Framework Code First now uses local Microsoft SQL Server 2012 instance.

Read project from database

Write a LINQ expression that gets your tasks from data base. place this code in a separate method, named in this sample “ReadGanttProject”.

   1: #region btnRead_Click   2:  private void btnRead_Click(object sender, RoutedEventArgs e)   3:  {   4:          var model = this.Root.Resources["viewmodel"] as DbProjectViewModel;   5:          if (model != null)   6:          {   7:              ReadGanttProject(ref model);   8:          }   9:  }  10:  #endregion btnRead_Click

Get all tasks in a separate list, ordered by Id. Clear the tasks from your model and add tasks from your list one by one from the smallest Id to the largest.

   1: #region ReadGanttProject   2: private void ReadGanttProject(ref DbProjectViewModel model)   3: {   4:     5:     var mm = model.Tasks.Where(a => a.TaskName != "").OrderBy(a=>a.Id).ToList();   6:        7:     foreach (var entity in model.Tasks)   8:     {   9:         model.Tasks.Remove(entity);  10:     }  11:    12:     //Clear the read tasks  13:     model.SaveChanges();  14:    15:     xamGantt.Project.RootTask.Tasks.Clear();  16:    17:     for (int i = 0; i < mm.Count(); i++)  18:     {  19:         model.Tasks.Add(mm[i]);  20:     }  21:    22:     foreach (ProjectTask prTask in xamGantt.Project.RootTask.Tasks)  23:     {  24:         var taskModel = prTask.DataItem as TaskModel;  25:         prTask.PredecessorsIdText = taskModel.PredecessorsIdText;  26:     }  27:    28:     //Update database again   29:     model.SaveChanges();  30:    31: }  32: #endregion ReadGanttProject

Now you have all tasks from database in your XamGantt application.

Try to delete a specified task, add a new task and save the project. Database is updated without any issues.

The ListBackedProject.TaskItemsSource is not bound Tasks (Tasks is from DbSet type and doesn’t support property changed notifications) . It is bound to Tasks.Local that returns ObservableCollection<TaskModel>.

<ig:ListBackedProject x:Name="dataProvider" TaskItemsSource="{Binding Tasks.Local}">

In this case all property changed notifications updates the tasks inside the XamGantt control.

Conclusions

It is very easy to a database (Microsoft SQL Server or other to persist your project data from an application with an Infragistics XamGantt control. This scenario could be used for multi-user systems for project management and planning. The sample application could be modified with a few small changes to support many different projects as a real life application.

Using Infragistics NetAdvantage for XAML (WPF and Silverlight) Vol. 12.2 you can create software for project-management teams providing the benefit from the next NetAdvantage updates of and features .

Follow news from Infragistics for more information about new Infragistics products.

Source code is available here. Sample project is created with Visual Studio 2012, but you can use NetAdvantage 12.2 controls with both: Visual Studio 2012 and Visual Studio 2012. As always, you can follow us on Twitter @mihailmateev and @Infragistics and stay in touch on Facebook, Google+ and LinkedIn!

Using Spatial Data in ASP.Net MVC with SQL Server 2012, Entity Framework 5 and Infragistics jQuery Map

mmateev — Wed, 11 Jul 2012 16:03:00 GMT

One of the highly-anticipated features in the Entity Framework 5 is a Spatial support.

CodeProject

Many developers have been asking since the release of SQL 2008 for support of Spatial data types in the Entity Framework. It was a dream for the Microsoft ORM users to create .NET business applications quickly, using spatial data. In May of this year the release candidate for Entity Framework 5 (EF5) was announced.This version has increased performance when compared to earlier EF version and also has support for spatial types. The Spatial functionality in EF5 requires .NET 4.5.

The Spatial functionality in EF5 requires .NET 4.5. This means you will need Visual Studios 2012 installed. You can download the release candidate for VS 2012 here: http://www.microsoft.com/visualstudio/en-us

Spatial Data in the Entity Framework

Prior to Entity Framework 5.0 on .NET 4.5 consuming of the data above required using stored procedures or raw SQL commands to access the spatial data. In Entity Framework 5 however, Microsoft introduced the new DbGeometry andDbGeography types. These immutable location types provide a bunch of functionality for manipulating spatial points using geometry functions which in turn can be used to do common spatial queries like I described in the SQL syntax above.

The DbGeography/DbGeometry types are immutable, meaning that you can't write to them once they've been created. They are a bit odd in that you need to use factory methods in order to instantiate them - they have no constructor() and you can't assign to properties like Latitude and Longitude.

It is important to mention that these types are defined in System.Data.Entity assembly in System.Data.Spatial namespace. By now you have probably used types SqlGeometry and SqlGeography types, defined in Microsoft.SqlServer.Types namespace

Creating a Model with Spatial Data

Let's start by creating a simple Entity Framework model that includes entities from sample databases Northwind and SpatialDemo. The entity named world contains a geom property of type DbGeometry. Sample is using SQL Server 2012, but you could run it with SQL Server 2008.

[EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]

[DataMemberAttribute()]

public global::System.Data.Spatial.DbGeometry geom

{

    get

    {

        return _geom;

    }

    set

    {

        OngeomChanging(value);

        ReportPropertyChanging("geom");

        _geom = StructuralObject.SetValidValue(value, true, "geom");

        ReportPropertyChanged("geom");

        OngeomChanged();

    }

}

private global::System.Data.Spatial.DbGeometry _geom;

partial void OngeomChanging(global::System.Data.Spatial.DbGeometry value);

partial void OngeomChanged();

Northwind Entities

Sample SpatialDemo database, including spatial data. Table world a filed from Geometry type, named “geom”. This field contains the contours of countries as polygons

Entity world, generated from SpatialDemo database contains a field “geom” from Geometry type.

ASP.Net MVC 4 Application with Entity Framework 5 RC and Spatial data

Now it is a pretty easy to use spatial data in ASP.Net MVC applications.

Controller

Controller returns a view that contains a dashboard with Infragistics jQuery controls.

#region DashboardJs

public ActionResult DashboardJs()

{

    ViewBag.Message = "Spatial Data Dashboard";

  

    return View();

}

#endregion //DashboardJs

Spatial Data Maintenance

When you have a data from DbGeometry / DbGeography type you can’t serialize it. There are two options:

To convert spatial data type to WKT (Well Known Text) and send it to the client (view) as part of JSON or XML
To use your own classes that could be serialized

This sample is demonstrates the second approach

CountryByName method serializes results to JSON to be possible to use it in the view

#region CountryByName

[OutputCache(VaryByParam = "countryName", Duration = 120)]

public JsonResult CountryByName(string countryName)

{

    switch (countryName)

    {

        case "UK":

            countryName = "United Kingdom";

            break;

        case "USA":

            countryName = "United States";

            break;

    }

    var results = spDemo.worlds.Where(x => x.CNTRY_NAME == countryName);

  

  

    List ret = new List();

    foreach (world country in results)

    {

        CountryInfo info = new CountryInfo

        {

            Id = country.ID,

            Code = country.CODE,

            CountryName = country.CNTRY_NAME,

            Population = country.POP_CNTRY,

            Extend = GetGeometryBoundary(country)

        };

        ret.Add(info);

    }

  

    var retVal = Json(ret, JsonRequestBehavior.AllowGet);

    return retVal;

}

#endregion //CountryByName

GetGeometryBoundary is a helper method used to get a list of points, representing an envelope of a DbGeometry instance. Don't forget that DbGeometry/DbGeography point indexes start from 1 !.

#region GetGeometryBoundary

public static SpatialRect GetGeometryBoundary(world country)

{

    List multiPoints = new List();

    var numPoints = country.geom.Envelope.ElementAt(1).PointCount;

    for (int i = 1; i <= numpoints="" i="" pre="">
    {
        SpatialPoint pnt = new SpatialPoint((double)(country.geom.Envelope.ElementAt(1).PointAt(i).XCoordinate), (double)(country.geom.Envelope.ElementAt(1).PointAt(i).YCoordinate));
        multiPoints.Add(pnt);
  
    }
    SpatialRect rect = multiPoints.GetBounds();
    return rect;
}
#endregion //GetGeometryBoundary

ContryInfo is a helper class used to serialize data

#region CountryInfo

  

public class CountryInfo

{

    public int Id { get; set; }

    public string Code { get; set; }

    public string CountryName { get; set; }

    public long? Population { get; set; }

    public SpatialRect Extend { get; set; }

 

}

#endregion //CountryInfo

SpatialPoint is a helper class to keep a point data. You could use

#region SpatialPoint

  

public class SpatialPoint

{

    public SpatialPoint(double x, double y)

    {

        this.X = x;

        this.Y = y;

    }

  

    public double X { get; set; }

    public double Y { get; set; }

}

  

#endregion //SpatialPoint

SpatialRect is a helper class to keep an extend of the country

#region SpatialRect

  

public struct SpatialRect

{

    public SpatialRect(double pLeft, double pTop, double pWidth, double pHeight)

    {

        left = pLeft;

        top = pTop;

        width = pWidth;

        height = pHeight;

    }

  

    public double left; 

    public double top; 

    public double width; 

    public double height; 

}

  

#endregion //SpatialRect

GetBounds is an extension method used to get a boundary of the list of points.

#region Extensions

public static class Extensions

{

  

    #region GetBounds

    public static SpatialRect GetBounds(this IList points)

    {

        double xmin = Double.PositiveInfinity;

        double ymin = Double.PositiveInfinity;

        double xmax = Double.NegativeInfinity;

        double ymax = Double.NegativeInfinity;

  

        SpatialPoint p;

        for (var i = 0; i < points.Count; i++)

        {

            p = points[i];

            xmin = Math.Min(xmin, p.X);

            ymin = Math.Min(ymin, p.Y);

  

            xmax = Math.Max(xmax, p.X);

            ymax = Math.Max(ymax, p.Y);

        }

  

        if (Double.IsInfinity(xmin) || Double.IsInfinity(ymin) || Double.IsInfinity(ymin) || Double.IsInfinity(ymax))

        {

            return new SpatialRect(0.0, 0.0, 0.0, 0.0);

        }

  

        return new SpatialRect(xmin, ymin, xmax - xmin, ymax - ymin);

    }

    #endregion //GetBounds

} 

#endregion //Extensions

View

The view presents a dashboard from Infragistics jQuery Grid, Chart and Map.

The most important part in the sample is how to query the controller’s method that returns spatial data (the country extend in this case).

var countryUrl = "/Home/CountryByName?countryName=" + args.row.element[0].cells[1].textContent

  

...

  

$.getJSON(countryUrl,

  function (json, text) {

      $.each(json, function (index, value) {

          var country = value;

          var extend = country["Extend"];

  

          var zoom = $("#map").igMap("getZoomFromGeographic", extend);

          $("#map").igMap("option", "windowRect", zoom);

  

      });

});

Infragistics jQuery Map instance definition.

$("#map").igMap({

     width: "500px",

     height: "500px",

     panModifier: "control",

     horizontalZoomable: true,

     verticalZoomable: true,

     windowResponse: "immediate",

     overviewPlusDetailPaneVisibility: "visible",

     seriesMouseLeftButtonUp: function (ui, args) {

         var tets = args;

     }

 });

Infragistics jQuery Grid with zoom around the selected customer’s country.

$('#grid').igGrid({

     virtualization: false, height: 280, width: 650,

     dataSource: "/Home/Customers",

     autoGenerateColumns: false,

     columns: [

         { headerText: "Customer ID", key: "CustomerID", width: "120px", dataType: "string" },

         { headerText: "Country", key: "Country", width: "150px", dataType: "string" },

         { headerText: "City", key: "City", dataType: "string" },

         { headerText: "Contact Name", key: "ContactName", dataType: "string" },

         {headerText: "Phone", key: "Phone", dataType: "string" }

     ],

     features: [

  

      {

          name: 'Selection',

          mode: 'row',

          multipleSelection: false,

          rowSelectionChanged: function (ui, args) {

              $("#chart").igDataChart({

                  dataSource: "/Home/Orders?userID=" + args.row.element[0].cells[0].textContent

              });

  

              selected = args.row.element[0].cells[0].textContent; //keep track of selected user

              var countryUrl = "/Home/CountryByName?countryName=" + args.row.element[0].cells[1].textContent

  

  

             $.getJSON(countryUrl,

                function (json, text) {

                    $.each(json, function (index, value) {

                        var country = value;

                        var extend = country["Extend"];

  

                        var zoom = $("#map").igMap("getZoomFromGeographic", extend);

                        $("#map").igMap("option", "windowRect", zoom);

  

                    });

              });

  

          }

      }

     ,

  

     {

         name: 'Sorting',

         type: "remote"

     },

     {

         name: 'Paging',

         type: "local",

         pageSize: 10

     }]

  

 })

Spatial Data in Action

Run the application and select the “Spatial Data Dashboard” from the menu.

Choose a customer from the igGrid and see how the map shows the country from which the client

You can download source code of the sample here.

To run this sample you could download Northwind and SpatialDemo sample databases

As always, you can follow us on Twitter: @mihailmateev and @Infragistics , all tweets with hashtag #infragistcs and stay in touch on Facebook, Google+ , LinkedIn and Infragistics Friends User Group !