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
-AutoColor
-SolidThinking Inspire v2016.1.5559 x64-BEAN (17/8)
-Auto FX PhotoGraphic Edges Ultimate Bundle Gen2 v9.6.0 (17/8)
-Mentor Graphics FloEFD 14.2-SSQ (17/8)
-Dlubal RFEM 5.06 (x64) Multilanguage (17/8)
-Bluebeam Revu eXtreme 2018.1 v18.x Multilingual (17/8)
-Autodesk Maya 2018.4 (17/8)
-Adobe Bridge CC 2018 (v8.1) x86-x64 Multilingual Update 2 (17/8)
-Simufact.Welding 4.0.1 (17/8)
-ANSYS Discovery Enterprise 19.1.1 (17/8)
-Aurora SPARK3D v2018
-ANSYS Electromagnetics Suite 17.1 (17/8)
-Solidthinking Inspire v2018.3.0.10526 Multilingual (x64) (17/8)
-Adobe Acrobat Pro DC 2018.011.20058 Multilingual (17/8)
-ANSYS Electromagnetics Suite 17.0 (x64) (17/8)
-GibbsCAM 12.0.29.0 (x64) Multilingual (17/8)
-Autodesk Autocad Mechanical 2019.1 by m0nkrus (17/8)
-Adobe Bridge CC 2018 v8.1 (x86/x64) Update 2 Multilingual (17/8)
-Autodesk AutoCAD Electrical 2019.1 (17/8)
-DS V5R22 CATIA-DELMIA-ENOVIA last hotfixes for all SP (17/8)
-VMware Fusion Pro 10.1.3 Build 9472307 (17/8)
-MAGIX Fotostory easy 2.0.0.35 Germany + English (17/8)
-Ds Simulia Abaqus v6.14-4 (17/8)
-Paragon Drive Copy 15 Professional 10.1.25.779 (x86/x64) (17/8)
-ARCHICAD 21 Build 3005 with Addons
 Resources
Browse GIS books and periodicals
Find a MapPoint Partner or Consultant
Real Estate Columbia MO Homes for Sale


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.