topic Re: Query SQL Server Table in ArcGIS API for Silverlight Questions

Query SQL Server Table

BrandonCales — Tue, 26 Jul 2011 14:22:33 GMT

I have a view on another system (Permits), that I want to query based on the selected Parcel. Do I have to setup a relationship class in ArcGIS Server or is there another way?

Re: Query SQL Server Table

HugoCardenas — Tue, 26 Jul 2011 16:29:20 GMT

I hate repying like this. I wish I knew a more specific answer. I will be doing the same thing as you and this is how I am doing it:

1. Create a WCF service that runs on the same data server or in another web server which has tcpip access to it. The WCF service will serve your data.

2. Consume (create an instance) of the WCF in your Silverlight application.
3. Call the method in the WCF service that exposes (retrieves) the data you need.

Upon creating the WCF, create it an independent library (standard dll), separated from Silverlight.
Do not return primitive data types in your WCF service, but rather complex data types that apply to your business domain; that is, if you are returning permits, create a custom business object (cbo) that exposes a permit. For example, I retrieve water meters info, so I have a Meter cbo returned:

/* request */
public class Meter
{
public long DcomId { get; set; }
public long DeviceId { get; set; }
public short DeviceType { get; set; }
...
}

The WCF fills this object and this is the one returned to you in the Silverlight application.

Also, when you request, do not use primitive values either; use a request class:

/* Result */
public class PermitRequest
{
   public string Name { get; set; }
   public int ID { get; set; }
   public DateTime { get; set; }
}

Now, your class that exposes the service in the WCF service has a method like this:

public PermitResult GetPermit(PermitRequest request)
{
}

From your Silverlight application:

Meter myMeter = (Meter)objWCF.GetPermit(objPermitRequest);

Sorry about this....
As I said, I wish I knew a shorter option.
Hugo.

Re: Query SQL Server Table

IgressT — Tue, 26 Jul 2011 17:55:31 GMT

Not entirely sure if this is what you are trying to do but...
How about trying to use the "Add Query Layer" functionality in ArcGIS desktop and then publishing it as a service. Then use a query task to get the data... but you need to have access to ArcGIS desktop to create and publish the service

Re: Query SQL Server Table

BrandonCales — Tue, 26 Jul 2011 18:41:40 GMT

I'm thinking more of an ad-hoc query against SQL. I have it working in VB.NET ADF for ArcGIS 9.3, but moving to Silverlight API changes a lot of that...or maybe removes it. I will try these and post back. Thanks

Re: Query SQL Server Table

BrandonCales — Wed, 27 Jul 2011 17:22:18 GMT

Does the service have to be created outside my ArcGIS VS Project, or can I just add it to it? I'll have to read up more on this - new area.

I hate repying like this. I wish I knew a more specific answer. I will be doing the same thing as you and this is how I am doing it:

1. Create a WCF service that runs on the same data server or in another web server which has tcpip access to it. The WCF service will serve your data.

2. Consume (create an instance) of the WCF in your Silverlight application.
3. Call the method in the WCF service that exposes (retrieves) the data you need.

Upon creating the WCF, create it an independent library (standard dll), separated from Silverlight.
Do not return primitive data types in your WCF service, but rather complex data types that apply to your business domain; that is, if you are returning permits, create a custom business object (cbo) that exposes a permit. For example, I retrieve water meters info, so I have a Meter cbo returned:

/* request */
public class Meter
{
public long DcomId { get; set; }
public long DeviceId { get; set; }
public short DeviceType { get; set; }
...
}

The WCF fills this object and this is the one returned to you in the Silverlight application.

Also, when you request, do not use primitive values either; use a request class:

/* Result */
public class PermitRequest
{
   public string Name { get; set; }
   public int ID { get; set; }
   public DateTime { get; set; }
}

Now, your class that exposes the service in the WCF service has a method like this:

public PermitResult GetPermit(PermitRequest request)
{
}

From your Silverlight application:

Meter myMeter = (Meter)objWCF.GetPermit(objPermitRequest);

Sorry about this....
As I said, I wish I knew a shorter option.
Hugo.

Re: Query SQL Server Table

HugoCardenas — Tue, 02 Aug 2011 20:29:02 GMT

Yes. It is better to create the WCF service as regular .NET class library (.dll); then create a standard web site of type "Web Service" to deploy the .dll. This way you get all the power of .NET that Silverlight lacks. I have done it already and works great.

My scenario may be somewhat different from yours or any other. I have my Silverlight application sitting on the Cloud server, getting all map services from the ArcGIS Server on the Cloud as well. My Silverlight application connects to non-GIS servers (standard MSSQL servers) outside the Cloud to retrieve dynamic data (always changing information [massive water meter readings that change every 2 to 4 hours]). Static information (meter id, address, etc.) comes from the ArcGIS Server in the Cloud, but other dynamic attributes are retrieved from those other servers outside the Cloud.

I can save and open files (somehting very unsual in Silverlight with the browser option on), for my WCF service does it all.

I can share some code and the .dll if anyone needs it.

Hugo.

Re: Query SQL Server Table

BrandonCales — Tue, 02 Aug 2011 20:55:17 GMT

I was actually able to work around it by the following...it works great.

...since you have a common field name between the Parcel feature class and the stand alone table 'permit'; I would suggest to perform a relationship operation between them and then publish the map service.

For example:
http://sampleserver3.arcgisonline.com/ArcGIS/rest/services/Petroleum/KSPetro/MapServer

In your Silverlight application, you may then use the queryTask.ExecuteRelationshipQueryAsync() method to perform the query on the Related table.

Re: Query SQL Server Table

SheldonKitzul — Fri, 02 Dec 2011 13:48:03 GMT

First off, I apologize for my lack of technical expertise. I am a product manager looking to understand how to link a map service with a dynamic SQL database. In short, the goal is that the map service should reflect real-time data any time it is accessed. There will be a single attribute in the map service that determines how information will be displayed by the map service. This single attribute is updated based on information contained in the SQL database.

My concerns are being able to do this in a manner that does not negatively impact performance and that is as close to real-time as possible.

Our company is also in the process of creating several WCF services that could be used as well.

What alternatives are there for us to consider? PLEASE help! 🙂

Re: Query SQL Server Table

MellissaLasslo — Wed, 07 Dec 2011 19:10:43 GMT

When you say map service are you talking about an interactive map service online? Are you using ArcGIS Server to create the map service?

Re: Query SQL Server Table

SheldonKitzul — Fri, 09 Dec 2011 15:30:16 GMT

When you say map service are you talking about an interactive map service online? Are you using ArcGIS Server to create the map service?

Thanks for your reply. Much appreciated.

I am talking about an interactive map service online that would be created using ArcGIS Server, correct.

In essence, according to my understanding, the map service displays information based on a table. One of the attributes in this table will change based on updates coming in from the SQL side. To meet the needs of our application, the map service needs to display as close-to-real-time information as possible without significant impacts to performance.

Thus, the table needs to somehow dynamically update so that anytime the user of the application moves around in the map and generates a view, the view reflects current information.

Re: Query SQL Server Table

MellissaLasslo — Fri, 09 Dec 2011 15:42:05 GMT

What version of ArcGIS are you using?

Re: Query SQL Server Table

SheldonKitzul — Mon, 12 Dec 2011 13:31:52 GMT

What version of ArcGIS are you using?

We will be using ArcGIS 9.3.1 with potential to go to 10 or 10.1 if there is significant benefit.

Re: Query SQL Server Table

MellissaLasslo — Mon, 12 Dec 2011 21:24:55 GMT

We will be using ArcGIS 9.3.1 with potential to go to 10 or 10.1 if there is significant benefit.

I'm not sure how to do it in 9.3.1, but we were just able to do it in 10. Here are some links to threads that may help:

http://forums.arcgis.com/threads/2381-Trying-to-create-map-service-with-non-spatial-SQL-Server-database

http://forums.arcgis.com/threads/24893-ArcGIS-Conecction-OLE-DB-Publishing-MapService-Problem?highlight=odc

http://forums.arcgis.com/threads/42972-adding-layer-to-.mxd-from-OLE-database-connection-produces-invalid-layer-error

You can also email me at mellissa.lasslo[@]carbon[dot]utah[dot]gov if you have any more questions or need help.

Re: Query SQL Server Table

SheldonKitzul — Mon, 19 Dec 2011 13:24:13 GMT

I'm not sure how to do it in 9.3.1, but we were just able to do it in 10. Here are some links to threads that may help:

http://forums.arcgis.com/threads/2381-Trying-to-create-map-service-with-non-spatial-SQL-Server-database

http://forums.arcgis.com/threads/24893-ArcGIS-Conecction-OLE-DB-Publishing-MapService-Problem?highlight=odc

http://forums.arcgis.com/threads/42972-adding-layer-to-.mxd-from-OLE-database-connection-produces-invalid-layer-error

You can also email me at mellissa.lasslo[@]carbon[dot]utah[dot]gov if you have any more questions or need help.

Thanks Melissa! I will check into the links. I GREATLY appreciate your help!

Re: Query SQL Server Table

Abul_Kashem_Md_Hasan — Mon, 08 Apr 2013 02:48:47 GMT

Yes. It is better to create the WCF service as regular .NET class library (.dll); then create a standard web site of type "Web Service" to deploy the .dll. This way you get all the power of .NET that Silverlight lacks. I have done it already and works great.

My scenario may be somewhat different from yours or any other. I have my Silverlight application sitting on the Cloud server, getting all map services from the ArcGIS Server on the Cloud as well. My Silverlight application connects to non-GIS servers (standard MSSQL servers) outside the Cloud to retrieve dynamic data (always changing information [massive water meter readings that change every 2 to 4 hours]). Static information (meter id, address, etc.) comes from the ArcGIS Server in the Cloud, but other dynamic attributes are retrieved from those other servers outside the Cloud.

I can save and open files (somehting very unsual in Silverlight with the browser option on), for my WCF service does it all.

I can share some code and the .dll if anyone needs it.

Hugo.

Dear Hugo,
We are using silverlight. we need to join a table from oracle with map service on the fly. I think you code will be very helpful to us. Could you please send your code to me. My email address is ahasan@cegisbd.com.

Thank you very much.