spacer1
spacer2 1_1 1_2
2_1
 Subscribe
 The MP2K Update!
 
 
 
 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

Map Visitors

  ARTICLES  


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

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:

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.

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.

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:

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.



Google
 
MP2Kmag Internet


 Recent Discussion
-GCPowerStation v17.2
-GCPowerStation v17.2
-Gaussian 09W v8.0 Rev B.01 SMP
-Gaussian 09 E.01 Linux64
-Atmel Studio v.7.0.1931
-Steinberg Cubase Elements v9.5.40 WiN
-Nero Platinum 2019 Suite 20.0.05900 Multilingual
-Serif DrawPlus X8 14.0.0.19
-PROMT Master 19 Final + PROMT 19 Dictionary Collection
-Apple Logic Pro X v.10.4.2 Multilanguage (MacOSX)
-PROMT Expert 19 Final + PROMT 19 Dictionary Collection
-Waves Complete v2018.10.16 WiN by R2R
-VMware Fusion Pro 10.1.1 MacOSX
-Foxit Phantompdf Business v.9.3.0.10826 Multilanguage
-Bluestacks v4.31.56.2510 Multilingual
-Xara Web Designer Premium 16.0.0.55162 (x64) Portable
-Adobe Bridge CC 6.1.1.10 Update 3 by m0nkrus
-Garmin MapSource v5.4
-Alien Skin Exposure X4 Bundle 4.0.2.43 | macOS
-Unity Asset Cubebrush - Scrap City Assets 1.0 x64
-Magix Samplitude Pro X3 Suite v.14.4.0.518 Multilanguage
-Waves Complete v2018.10.16
-Oxygen XML Editor 18.0 (Win/Mac/Lnx)
-Steinberg Cubase Elements v9.5.40 WiN
-The Foundry Nuke Studio 11.2v4 Win x64
 Resources
Browse GIS books and periodicals
Find a MapPoint Partner or Consultant
Real Estate Columbia Homes for Sale by Owner


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.