Using a DSN-less Connection to a SQL Server Database to Get Data for Maps

Subscribe

Magazine

Front Cover
What's New
Articles
News
Sample Data
Gallery
Advertise
About

Features

MapPoint 2013
Press Releases
MapPoint Forums
Companies
Link to MP2Kmag
Wish List
MapPoint Trial
Authors

Earlier Content

Past News Items
Past What's New Announcements

Sponsors

Order

MapPoint 2013

Programming MapPoint in .NET

MapPoint Book

Spatial Community

SVG Tutorials
MapPoint


	ARTICLES

Whether you are building a COM add-in or using the MapPoint ActiveX Control, you may need to access SQL Server data. Here are some ideas on doing it efficiently and flexibly.

Article assumptions:

You have a basic understanding of data access.
You need to get at SQL Server data to do something with MapPoint.

(Let me apologize in advance about how I have mixed VB code with SQL Server table, view and stored procedure definitions in this article. My goal is to give you all of the elements you need to dynamically grab SQL data and make it available in MapPoint. I hope I have succeeded.)

MapPoint provides import and linking support for SQL Server data, but that is limited by the need for using Universal Data Link as the mechanism to get at SQL Server data. You can't execute dynamically created SQL or SQL Server stored procedures. Therefore, we need a way to get data onto maps in a more flexible way. We can use a DSN-less connection in our code to get at SQL Server data and create points from that data.

Why go DSN-less?

In an MSDN article called Improving ASP Application Performance, there are several interesting data access performance tips that are not specific to ASP.

In general, DSN-less connections are faster than System DSNs (data source names), which are faster than File DSNs.
Microsoft Access is a file-based database, so don't expect it to perform well with concurrent users under IIS [or otherwise].
For SQL Server, SQLOLEDB, the SQL provider, is recommended over MSDASQL, the OLEDB provider for ODBC for performance and reliability.
It is always good practice to explicitly close your object variables.

The code below shows a number of ways to make connections and create recordsets:

 
' DSN-less connection to a database.
' Author: Walt Cygan
' (wcygan@macrogroup.net)
'
' This code is copyrighted freeware - use freely, but please leave this
' header intact. Suggestions and comments welcome.
'
Dim objConn as ADODB.Connection
Dim objRS as ADODB.Recordset
Dim strConn as String
Dim strSQL as String
Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset

'Create a connection string.
strConn = "Provider=SQLOLEDB;Data Source=MyServer;" & _
     "Initial Catalog=Northwind;User Id=MyId;Password=123aBc;"

'If you have to use MS Access, build a connection string this way.
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\Data\MappingDatabase.mdb"

'You can find OLEDB providers and connection syntax for all kinds of 
'other data sources including text files.

'The following string is equivalent to the first used for access to
'SQL Server:
strConn = "Provider=SQLOLEDB;Server=MyServer;" & _
     "Database=Northwind;User Id=MyId;Password=123aBc;"

objConn.Open strConn 'will open the database connection.

'An alternate way to open the connection is:
With objConn
    .ConnectionString = strConn
    .ConnectionTimeout = 120
    .CommandTimeout = 120
    .Open
End With

'The ability to change connection properties is
'a major reason to explicitly create a Connection object.

'This can also be done without the connection string as:
With objConn
    .Provider = "SQLOLEDB"
    .DefaultDatabase = "Northwind"
    .Properties("Data Source") = "MyServer"

    .Properties("User Id") = "MyId"
    .Properties("Password") = "123aBc"
    .Open
End With

'Lets create the simplest possible SQL we want to execute.
strSQL = "Select * from tbAddresses "

'You can now open the recordset.
With objRS
    .Open strSQL, objConn
End With

'or
objRS.Open strSQL, objConn

'or without using the connection object
objRS.Open strSQL, strConn, adOpenForwardOnly

'See documentation on ADO to understand the cursor type options 
'(such as adOpenForwardOnly).
'You can now use the data in objRS.
'=====
'Be sure you close and destroy your objects.
objRS.Close
objConn.Close
Set objConn = Nothing
Set objRS = Nothing

The table I use in SQL Server 2000 for holding locations looks like this:

Once you have a recordset, you can start creating MapPoint objects.

' Create MapPoint objects from SQL data
' Author: Walt Cygan
' (wcygan@macrogroup.net)
'
' This code is copyrighted freeware - use freely, but please leave this
' header intact. Suggestions and comments welcome.
'
'''''
'Use previous code to get SQL data into the recordset called rs.
'''''
    'Create a new pushpin set.
    objMap.DataSets.AddPushpinSet "Locations from DB"
    'Get the DataSet object from the pushpin set
    Set objDS = objMap.DataSets("Locations from DB")
    objDS.Select
    'Let's loop through the SQL data...
    Do While Not rs.EOF
        'Create a location object from the latitude and longitude on the database.
        Set objLocTst = objMap.GetLocation(rs!Latitude, rs!Longitude)
        'Add a pushpin at that location with a name from the "LocationName" field on the database
        Set objPin = objMap.AddPushpin(objLocTst, rs!LocationName)
        'Make sure the balloon is closed.
        objPin.BalloonState = geoDisplayNone
        'Put additional data in the note field of the pin.
        strNote = rs!Address1 & vbCrLf & _
                    IIf(IsNull(rs!Address2), "", rs!Address2 & vbCrLf) & _
                    rs!City & ", " & rs!StateCode & "   " & rs!ZipCode & vbCrLf & _
                    "Location ID = " & rs!LocationID
        objPin.Note = strNote
        'Cut the pin out of "My Pushpins"...
        objPin.Cut
        '... and paste into "Locations from DB"
        objDS.Paste
        'Get the next database record.
        rs.MoveNext
    Loop

A more advanced way to make use of SQL Server would be to use stored procedures. See the following examples for ideas. This works best in an environment that uses integrated SQL security. Instead of providing the User ID and password in the connection string, it looks like this: "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=MyServer". This passes the NT/2000 Domain login information already available from the person logged in on the workstation, and provides it to SQL Server as the login information for the database.

If you use integrated security, then you can use the SQL Server function "suser_sname()" to get the DOMAIN\UserID of the person running the application that is accessing SQL Server.

' Use stored procedures to get data to map.
' Author: Walt Cygan
' (wcygan@macrogroup.net)
'
' This code is copyrighted freeware - use freely, but please leave this
' header intact. Suggestions and comments welcome.
'
===> SQL table definition
'The table used for gathering data to map looks like:
CREATE TABLE [dbo].[tbMapDisplay] (
	[DisplayID] [int] IDENTITY (1, 1) NOT NULL ,
	[LocationID] [int] NOT NULL ,
	[LocationName] [varchar] (100) NULL ,
	[Address1] [varchar] (100) NULL ,
	[Address2] [varchar] (100) NULL ,
	[City] [varchar] (50) NULL ,
	[StateCode] [char] (2) NULL ,
	[ZipCode] [char] (10) NULL ,
	[Latitude] [decimal](18, 7) NULL ,
	[Longitude] [decimal](18, 7) NULL ,
	[GeoCodeStatus] [smallint] NULL ,
	[UserID] [varchar] (100) NOT NULL ,
	[KeepRecord] [smallint] NOT NULL 
) ON [PRIMARY]
GO
where the [UserID] field has a default of "(suser_sname())"
and the [KeepRecord] field has a defaul of 1

===> Visual Basic
Dim strConn as String

    strConn = "Provider=SQLOLEDB;Integrated Security=SSPI;" & _
                "Persist Security Info=False;" & _ 
                "Initial Catalog=Northwind;Data Source=MyServer"
    Set cn = New ADODB.Connection
    cn.ConnectionTimeout = 120
    cn.CommandTimeout = 120
    cn.Open strConn

    'Execute the stored procedure to delete any rows I had previously
    'added to the table.
    sSQL = "upMapDisplayDelete"
    cn.Execute sSQL

===> SQL Server
'The "upMapDisplayDelete" procedure is defined as
Create Procedure upMapDisplayDelete
As
	set nocount on
	Delete from tbMapDisplay where UserID = SUSER_SNAME()
	return 

GO

===> Visual Basic
'Next add the records you want displayed:
    sSQL = "upMapDisplayAdd '" & txtStateCode & "', '" & txtCity & "'"
    cn.Execute sSQL

===> SQL Server
'The "upMapDisplayadd" procedure is defined as:
CREATE PROCEDURE upMapDisplayAdd
@StateCode char(2),
@City varchar(50) = 'ALL'
AS
	set nocount on
	INSERT INTO tbMapDisplay
		(
		LocationID, LocationName, Address1, Address2, City, StateCode, ZipCode, Latitude, Longitude, GeoCodeStatus
		)
	SELECT 
		LocationID, LocationName, Address1, Address2, City, StateCode, ZipCode, Latitude, Longitude, GeoCodeStatus 
	FROM tbAddresses
	WHERE
		GeoCodeStatus = 1
		And (CASE 
			WHEN @StateCode = 'XX' THEN (SELECT 1)
			WHEN @StateCode = 'UM' THEN (SELECT 1 WHERE StateCode IN ('MN', 'ND', 'SD', 'IA', 'WI'))
			ELSE (SELECT 1 WHERE StateCode = @StateCode )
			END) =1
		And (CASE 
			WHEN UPPER(@City) = 'ALL' THEN (SELECT 1)
			ELSE (SELECT 1 WHERE City = @City )
			END) =1
GO

'Because the tbMapDisplay table has a UserID field that is set to the Windows user id, 
'the stored proc adds rows that will be unique to the current user. This procedure will 
'add rows based on the StateCode and City (if provided) passed to it. If no city is 
'provided, all cities will be added to the display table. A state code of 'UM' (standing 
'for Upper Midwest) will result in addresses in Minnesota, North Dakota, South Dakota, 
'Iowa and Wisconsin being copied to the display table.

'I think you can appreciate how much more complicated this logic could become.

Now create a view to allow each user to get the data tahe he or she added [call it "vwMapDisplay"]:

This view can now be used to import selected data into MapPoint using:

 
Dim objDS As MapPoint.DataSet
Dim objMap As MapPoint.Map
Dim arrFields(1 To 9, 1 To 2)
    
    arrFields(1, 1) = "Longitude": arrFields(1, 2) = geoFieldLongitude
    arrFields(2, 1) = "Latitude": arrFields(2, 2) = geoFieldLatitude
    arrFields(3, 1) = "LocationID": arrFields(3, 2) = geoFieldInformation
    arrFields(4, 1) = "LocationName": arrFields(4, 2) = geoFieldName
    arrFields(5, 1) = "Address1": arrFields(5, 2) = geoFieldAddress1
    arrFields(6, 1) = "Address2": arrFields(6, 2) = geoFieldAddress2
    arrFields(7, 1) = "City": arrFields(7, 2) = geoFieldCity
    arrFields(8, 1) = "StateCode": arrFields(8, 2) = geoFieldRegion1
    arrFields(9, 1) = "ZipCode": arrFields(9, 2) = geoFieldPostal1

    With objMap.DataSets
        'UDL's are DSN-less too.
        sConnection = "C:\UDLLocation\DBConnection.udl!vwMapDisplay" 
        Set objDS = .ImportData(sConnection, arrFields, geoCountryUnitedStates, geoDelimiterDefault)
    End With

What is the use of all of this... Addins and VB programs using the MapPoint ActiveX Control can be configured to apply very sophisticated logic to the display of map data drawn from the underlying SQL Server database. How far you want to take this becomes completely up to you.

Clearly the one enhancement to MapPoint that would simplify this whole thing would be the ability to import an ADO recordset directly into a MapPoint Dataset. I am looking forward to seeing the object model for the next version of MapPoint to see what data handling improvements are added.

Discuss this story in the forum.

Author: Walt Cygan
Email: wcygan(AT)macrogroup.net
URL: http://www.macrogroup.net
Walt is a consultant for The MACRO GROUP, Inc. in Minneapolis, Minnesota. The MACRO GROUP, Inc. is an information systems consulting firm whose customers include public, private and non-profit organizations of all sizes.

Recent Discussion

Resources

Browse GIS books and periodicals
Find a MapPoint Partner or Consultant
Real Estate Columbia, MO Real Estate

Want Your Site To Appear Here?

© 1999-2012 MP2K. Questions and comments to: website@mp2kmag.com
Microsoft and MapPoint 2002/2004/2006/2009/2010/2011/2013 are either trademarks or registered trademarks of Microsoft.