Walkthrough: Creating a Map Report with SQL Server Spatial Queries

Article
08/02/2012

Using spatial data from a SQL Server query in a map report involves three major steps:

Create a data object for the spatial data query
Design the map report using the data object
Supply the query data to the ReportViewer control

This walkthrough takes you through all the steps required to successfully design and display your first map report using the SQL Server spatial query option in a Windows Forms application project.

Note

You cannot use the dataset designer in Visual Studio for queries that return SQL Server spatial types. The dataset designer does not support user-defined types (UDTs), to which category the SQL Server spatial types (SqlGeometry and SqlGeography) belong.

Prerequisites

Visual Studio 2012 Professional or Ultimate
Access to the AdventureWorks2008R2 database (see Walkthrough: Installing the AdventureWorks Database).
Internet access (for Bing maps)

Create a data object for the spatial data query

Make a copy of the Microsoft.SqlServer.Types.dll (version 11.0.0.0) from the GAC to a directory of your choice. To do this, execute the following command in a command prompt:
```
Copy C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Types\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Types.dll <destination_folder>
```
Note

By default, folders under C:\Windows\assembly are not visible in File Explorer. They are visible only in the command prompt.
In the File menu in Visual Studio, point to New and select Project.
In the New Project dialog box, select Windows Forms Application, give it the name “MapWithSqlQuery”, and then click OK.
In Solution Explorer, right-click your project’s References node and select Add Reference.
Click the Browse button and navigate to the directory to which you copied Microsoft.SqlServer.Types.dll (version 11.0.0.0), select the file, click Open, and then click Add.

You are now able use the SqlGeometry and SqlGeography types in your data object.
In Solution Explorer, right-click your project, point to Add, and then select Class.
Give the file the name “StoreSalesAndLocations.cs” and click Add.

Open the StoreSalesAndLocations.cs file and define the following public properties inside the curly braces for the StoreSalesAndLocations class:

public double TotalDue { get; set; }
public string Store { get; set; }
public Microsoft.SqlServer.Types.SqlGeography SpatialLocation { get; set; }

Copy the following code below the property definitions and change the <DBServerName> parameter to the name of your database server:

// Returns the IEnumerable needed by the Report Designer to create a business object dataset.
public static IEnumerable<StoreSalesAndLocations> ReadFromDatabase()
{
    string connectionString = "Data Source=<DBServerName>;Initial Catalog=AdventureWorks2008R2;Integrated Security=True";
    using (var connection = new System.Data.SqlClient.SqlConnection(connectionString))
    {
        // The following query returns all sales orders from US physical stores, with the 
        // individual order total, store where the order is placed, and the store location.
        string queryText = @"
            SELECT soh.TotalDue, st.Name AS Store, ad.SpatialLocation
            FROM Sales.SalesOrderHeader AS soh INNER JOIN
              Sales.Customer AS c ON soh.CustomerID = c.CustomerID INNER JOIN
              Person.BusinessEntity AS b ON b.BusinessEntityID = c.StoreID INNER JOIN
              Sales.Store AS st ON st.BusinessEntityID 
                 = b.BusinessEntityID INNER JOIN
              Person.BusinessEntityAddress AS a 
                 ON a.BusinessEntityID = b.BusinessEntityID INNER JOIN
              Person.Address AS ad ON ad.AddressID = a.AddressID INNER JOIN
              Person.AddressType AS at ON at.AddressTypeID = a.AddressTypeID
                 INNER JOIN Person.StateProvince AS sp 
                 ON sp.StateProvinceID = ad.StateProvinceID
            WHERE (c.StoreID IS NOT NULL) 
               AND (at.Name = N'Main Office') AND (sp.CountryRegionCode = N'US')
        ";
        var command = new System.Data.SqlClient.SqlCommand(queryText, connection);
        command.CommandType = System.Data.CommandType.Text;
        connection.Open();
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                var next = new StoreSalesAndLocations() 
                {
                    TotalDue = Double.Parse(reader["TotalDue"].ToString()),
                    Store = reader["Store"].ToString(),
                    SpatialLocation = ReadSqlGeography(reader, "SpatialLocation")
                };

                yield return next;
            }
        }
    }
}

// Parses a geography column from a T-SQL query into the CLR SqlGeography type.
private static Microsoft.SqlServer.Types.SqlGeography ReadSqlGeography(System.Data.SqlClient.SqlDataReader reader, string columnName)
{
    var dataInBytes = reader.GetSqlBytes(reader.GetOrdinal(columnName));
    var newGeography = new Microsoft.SqlServer.Types.SqlGeography();
    using (var dataStream = dataInBytes.Stream)
    {
        newGeography.Read(new System.IO.BinaryReader(dataStream));
    }
    return newGeography;
}

Note

To know whether you should convert SQL Server spatial data into SqlGeometry or SqlGeography, inspect the table definition for the column in question. If the column type is geometry, then you should convert it into SqlGeometry. If the column type is geography, you should convert it into SqlGeography.

Build your solution. This ensures Data Source Configuration Wizard can see the new class when it’s launched from Report Designer.

In the next section, you design a map report using the data object you created. Before you begin, make sure you have already built your solution with your data object code. Otherwise, your data object will not show up later in the Data Source Configuration Wizard and you cannot use it in your report.

Design the map report using the data object

In Solution Explorer, right-click your project, point to Add, and select New Item.
In the Add New Item dialog box, select the Report item type, accept the default file name, and then click OK. The report is automatically opened in Report Designer.
Drag a Map report item from the toolbox to the design surface to launch the New Map Layer wizard.
In the Choose a source of spatial data page, select SQL Server spatial query and click Next.
If the Data Source Configuration Wizard is not automatically launched, click New to create a new data source.
In the Data Source Configuration Wizard, select Object and click Next.
Select your new data class from the tree view as shown below and click Finish, and then click Next in the Dataset Properties page.
Click OK to dismiss the pop-up dialog.
In the Layer type list, select Point. Also, select Add a Bing Maps layer. Then, click Next.
In Choose map visualization, select Bubble Map and click Next. The Dataset Properties page is opened again for you to specify data for the bubble map.
In the Data source list, select MapWithSqlQuery, click Next, and then click Next again.

Tip

Here, the wizard creates a second dataset in your report. Since your dataset already contains both spatial data and analytical data, strictly speaking you do not need two datasets. However, for simplicity you will keep both datasets, “DataSet1” and “DataSet2”.
In the Choose color theme and data visualization, make sure that Use bubble sizes to visualize data is selected, and select [Sum(TotalDue)] in the Data field list. This step configures the bubble map to show bubble size according to the sales total of each store.
Click Finish.
Note

You may notice the message “No spatial data available. The map display contains sample spatial data.” This is because you are using an object data source and no data is available at design time. This means the following:
- At run time, the map’s viewport will be automatically centered and zoomed to fit the data points in your dataset.
- At design time, the map’s viewport cannot show you the actual center and zoom that will be shown at run time.
- It is very difficult for you to customize the map’s center and zoom at design time due to the above design time limitation.

In the section, you make sure that the ReportViewer has access to the query data for the two datasets (“DataSet1” and “DataSet2”) defined in your report. Since they are really the same dataset, you only need to get the data once and adds it to the DataSources property of LocalReport for both of the dataset names.

Supply the query data to the ReportViewer control

In Solution Explorer, open Form1.cs in the form designer. From the Toolbox, in the Reporting category, drag a ReportViewer control on the form.
In the ReportViewer Tasks smart tag, select the report you created, and then select Dock in Parent Container.
In Solution Explorer, right-click Form1.cs and select View Code to open it in the code editor.

In the Form1_Load method, copy the following code above the RefreshReport method call:

IEnumerable<StoreSalesAndLocations> reportData = StoreSalesAndLocations.ReadFromDatabase();
reportViewer1.LocalReport.DataSources.Add(new ReportDataSource("DataSet1", reportData));
reportViewer1.LocalReport.DataSources.Add(new ReportDataSource("DataSet2", reportData));

In the Debug menu, select Start Debugging to run the project.

Walkthrough: Creating a Map Report with SQL Server Spatial Queries

Prerequisites

Create a data object for the spatial data query

Design the map report using the data object

Supply the query data to the ReportViewer control

See Also

Tasks

Other Resources

Additional resources