Query SQL Server Table

3691
14
07-26-2011 07:22 AM
BrandonCales
New Contributor III
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?
0 Kudos
14 Replies
HugoCardenas
New Contributor
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.
0 Kudos
IgressT
New Contributor II
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
0 Kudos
BrandonCales
New Contributor III
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
0 Kudos
BrandonCales
New Contributor III
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.
0 Kudos
HugoCardenas
New Contributor
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.
0 Kudos
BrandonCales
New Contributor III
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.
0 Kudos
SheldonKitzul
New Contributor
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! 🙂
0 Kudos
MellissaLasslo
New Contributor III
When you say map service are you talking about an interactive map service online? Are you using ArcGIS Server to create the map service?
0 Kudos
SheldonKitzul
New Contributor
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.
0 Kudos