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:

	MP2Kmag Internet