示例#1
0
        /// <summary>
        /// GetSQLDataXAML
        ///     returns XAML MapLayer with results of SQL Server query
        /// </summary>
        /// <param name="Parameters">XAMLParameters required for query</param>
        public XAMLResponse GetSQLDataXAML(XAMLParameters Parameters)
        {
            DateTime queryStart = DateTime.Now;
            DateTime queryStop;
            

            XAMLResponse _XAMLResponse = new XAMLResponse();
            _XAMLResponse.ErrorCode = 0;
            _XAMLResponse.OutputMessage = "Success";
            _XAMLResponse.OutputFields = new List<XAMLFields>();

            StringBuilder xaml = new StringBuilder();
            string layerstr = "layer"+CultureInfo.CurrentCulture.TextInfo.ToTitleCase(Parameters.table);

            //XML MapLayer header string for return XAML
            xaml.Append("<m:MapLayer x:Name=\""+layerstr+"\"");
            xaml.Append(" xmlns:m=\"clr-namespace:Microsoft.Maps.MapControl;assembly=Microsoft.Maps.MapControl\"");
            xaml.Append(" xmlns=\"http://schemas.microsoft.com/winfx/2006/xaml/presentation\"");
            xaml.Append(" xmlns:x=\"http://schemas.microsoft.com/winfx/2006/xaml\"");
            xaml.Append(">");
            string connStr = ConfigurationManager.ConnectionStrings["DataConnectionString"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            SqlDataReader rdr = null;

            try
            {
                if (Parameters.querytype == null || Parameters.querytype.Length == 0) throw new ArgumentException("Invalid Parameters: querytype=\"" + Parameters.querytype + "\"");
                if (Parameters.querytype.ToLower().Equals("buffer") && Parameters.radius <= 0) throw new ArgumentException("Invalid Parameters: querytype buffer requires radius > 0 - \"" + Parameters.radius + "\"");
                int test = Parameters.points.Split(',').Length;
                if (Parameters.points.Length == 0 && Parameters.querytype.ToLower().Equals("buffer")) throw new ArgumentException("Invalid Parameters: points must contain at least one point for querytype buffer");
                if (Parameters.points.Split(',').Length != 5 && Parameters.querytype.ToLower().Equals("bbox")) throw new ArgumentException("Invalid Parameters: points must contain 5 points for a closed querytype bbox");

                StringBuilder query = new StringBuilder();
                if (Parameters.querytype.ToLower().Equals("bbox"))
                {   //BBox
                    query.Append("SELECT *," + geomField + ".Reduce(@reduce) as "+geomField+"XAML FROM [dbo].[" + Parameters.table + "] WITH(INDEX(the_geo_sidx)) WHERE ");
                    query.Append(geomField + ".STIntersects(geography::STGeomFromText('POLYGON(('+@points+'))', @srid))=1");
                }
                else  if (Parameters.querytype.ToLower().Equals("buffer"))
                {
                    query.Append("SELECT *," + geomField + ".Reduce(@reduce) as " + geomField + "XAML FROM [dbo].[" + Parameters.table + "] WITH(INDEX(the_geo_sidx)) WHERE ");
                    if (Parameters.points.Split(',').Length > 1)
                    {   //Polyline Buffer
                        query.Append(geomField + ".STIntersects(geography::STGeomFromText('LINESTRING('+@points+')', @srid).STBuffer(@radius))=1");
                    }
                    else
                    {   //Point Buffer
                        query.Append(geomField + ".STIntersects(geography::STGeomFromText('POINT('+@points+')', @srid).STBuffer(@radius))=1");
                    }
                }
                log.Info(query);

                queryStart = DateTime.Now;
                SqlCommand cmd = new SqlCommand(query.ToString(), conn);
                cmd.Parameters.Add(new SqlParameter("reduce", Parameters.reduce));
                cmd.Parameters.Add(new SqlParameter("radius", Parameters.radius));
                cmd.Parameters.Add(new SqlParameter("srid", srid));
                cmd.Parameters.Add(new SqlParameter("points", Parameters.points));

                conn.Open();
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    XAMLFields shp = new XAMLFields();
                    shp.Fields = new Dictionary<string, string>();
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        log.Debug(rdr[i].GetType() + "  " + rdr.GetName(i));
                        if (rdr[i].GetType().Equals(typeof(SqlGeometry))||
                            rdr.GetName(i).Equals(geomField))
                        { //skip
                            log.Debug("skip " + rdr[i].GetType());
                        }
                        else
                        {
                            string test2 = rdr.GetName(i) + "   " + geomField + "XAML";
                            if (rdr.GetName(i).Equals("ID"))
                            {
                                shp.ID = Parameters.table+"_"+rdr[i].ToString();
                                log.Debug(rdr[i].GetType() + "  " + rdr.GetName(i) + "  " + rdr[i].ToString());
                            }
                            else if (rdr.GetName(i).Equals(geomField+"XAML"))
                            {

                                log.Debug(rdr[i].GetType() + "  " + rdr.GetName(i) + "  " + rdr[i].ToString());
                                SqlGeography geo = (SqlGeography)rdr[geomField + "XAML"];
                                StringBuilder geoxaml = new StringBuilder();

                                log.Debug(geo.STGeometryType().ToString().ToUpper());
                                switch (geo.STGeometryType().ToString().ToUpper())
                                {
                                    case "POINT":
                                        {
                                            geoxaml.Append(XamlPoint(geo, Parameters.table,shp.ID));
                                            break;
                                        }
                                    case "LINESTRING":
                                        {
                                            geoxaml.Append(XamlLinestring(geo, Parameters.table, shp.ID));
                                            break;
                                        }
                                    case "POLYGON":
                                        {
                                            geoxaml.Append(XamlPolygon(geo, Parameters.table, shp.ID));
                                            break;
                                        }
                                    case "MULTILINESTRING":
                                        {
                                            geoxaml.Append(XamlMultiLinestring(geo, Parameters.table, shp.ID));
                                            break;
                                        }
                                    case "MULTIPOLYGON":
                                        {
                                            geoxaml.Append(XamlMultiPolygon(geo, Parameters.table, shp.ID));
                                            break;
                                        }
                                    case "GEOMETRYCOLLECTION":
                                        {
                                            geoxaml.Append(XamlGeometryCollection(geo, Parameters.table, shp.ID));
                                            break;
                                        }
                                }
                                xaml.Append(geoxaml.ToString());
                            }
                            else
                            {
                                shp.Fields.Add(rdr.GetName(i), rdr[i].ToString());
                                log.Debug(rdr.GetName(i) + " : " + rdr[i].ToString());
                            }
                        }
                    }
                    log.Debug(shp.ID + "  " + _XAMLResponse.XAML);
                    _XAMLResponse.OutputFields.Add(shp);
                }
                xaml.AppendLine("</m:MapLayer>");
                _XAMLResponse.XAML = xaml.ToString();
            }
            catch (ArithmeticException e)
            {
                ServiceException(_XAMLResponse, e.Message);
            }
            catch (ArgumentException e)
            {
                ServiceException(_XAMLResponse, e.Message);
            }
            catch (Exception e)
            {
                ServiceException(_XAMLResponse, e.Message);
            }
            finally
            {
                if (rdr != null) rdr.Close();
                if (conn != null) conn.Close();
            }
            queryStop = DateTime.Now;
            log.Debug(String.Format("Query Time: {0,0:0}ms", (queryStop - queryStart).TotalMilliseconds));
            _XAMLResponse.QueryTime = (queryStop - queryStart).TotalMilliseconds;
            _XAMLResponse.totalPoints = totalPoints;

            return _XAMLResponse;
        }
示例#2
0
        /// <summary>
        /// GetSQLDataXAML
        ///     returns XAML MapLayer with results of SQL Server query
        /// </summary>
        /// <param name="Parameters">XAMLParameters required for query</param>
        public XAMLResponse GetSQLDataXAML(XAMLParameters Parameters)
        {
            DateTime queryStart = DateTime.Now;
            DateTime queryStop;


            XAMLResponse _XAMLResponse = new XAMLResponse();

            _XAMLResponse.ErrorCode     = 0;
            _XAMLResponse.OutputMessage = "Success";
            _XAMLResponse.OutputFields  = new List <XAMLFields>();

            StringBuilder xaml     = new StringBuilder();
            string        layerstr = "layer" + CultureInfo.CurrentCulture.TextInfo.ToTitleCase(Parameters.table);

            //XML MapLayer header string for return XAML
            xaml.Append("<m:MapLayer x:Name=\"" + layerstr + "\"");
            xaml.Append(" xmlns:m=\"clr-namespace:Microsoft.Maps.MapControl;assembly=Microsoft.Maps.MapControl\"");
            xaml.Append(" xmlns=\"http://schemas.microsoft.com/winfx/2006/xaml/presentation\"");
            xaml.Append(" xmlns:x=\"http://schemas.microsoft.com/winfx/2006/xaml\"");
            xaml.Append(">");
            string        connStr = ConfigurationManager.ConnectionStrings["DataConnectionString"].ConnectionString;
            SqlConnection conn    = new SqlConnection(connStr);
            SqlDataReader rdr     = null;

            try
            {
                if (Parameters.querytype == null || Parameters.querytype.Length == 0)
                {
                    throw new ArgumentException("Invalid Parameters: querytype=\"" + Parameters.querytype + "\"");
                }
                if (Parameters.querytype.ToLower().Equals("buffer") && Parameters.radius <= 0)
                {
                    throw new ArgumentException("Invalid Parameters: querytype buffer requires radius > 0 - \"" + Parameters.radius + "\"");
                }
                int test = Parameters.points.Split(',').Length;
                if (Parameters.points.Length == 0 && Parameters.querytype.ToLower().Equals("buffer"))
                {
                    throw new ArgumentException("Invalid Parameters: points must contain at least one point for querytype buffer");
                }
                if (Parameters.points.Split(',').Length != 5 && Parameters.querytype.ToLower().Equals("bbox"))
                {
                    throw new ArgumentException("Invalid Parameters: points must contain 5 points for a closed querytype bbox");
                }

                StringBuilder query = new StringBuilder();
                if (Parameters.querytype.ToLower().Equals("bbox"))
                {   //BBox
                    query.Append("SELECT *," + geomField + ".Reduce(@reduce) as " + geomField + "XAML FROM [dbo].[" + Parameters.table + "] WITH(INDEX(the_geo_sidx)) WHERE ");
                    query.Append(geomField + ".STIntersects(geography::STGeomFromText('POLYGON(('+@points+'))', @srid))=1");
                }
                else if (Parameters.querytype.ToLower().Equals("buffer"))
                {
                    query.Append("SELECT *," + geomField + ".Reduce(@reduce) as " + geomField + "XAML FROM [dbo].[" + Parameters.table + "] WITH(INDEX(the_geo_sidx)) WHERE ");
                    if (Parameters.points.Split(',').Length > 1)
                    {   //Polyline Buffer
                        query.Append(geomField + ".STIntersects(geography::STGeomFromText('LINESTRING('+@points+')', @srid).STBuffer(@radius))=1");
                    }
                    else
                    {   //Point Buffer
                        query.Append(geomField + ".STIntersects(geography::STGeomFromText('POINT('+@points+')', @srid).STBuffer(@radius))=1");
                    }
                }
                log.Info(query);

                queryStart = DateTime.Now;
                SqlCommand cmd = new SqlCommand(query.ToString(), conn);
                cmd.Parameters.Add(new SqlParameter("reduce", Parameters.reduce));
                cmd.Parameters.Add(new SqlParameter("radius", Parameters.radius));
                cmd.Parameters.Add(new SqlParameter("srid", srid));
                cmd.Parameters.Add(new SqlParameter("points", Parameters.points));

                conn.Open();
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    XAMLFields shp = new XAMLFields();
                    shp.Fields = new Dictionary <string, string>();
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        log.Debug(rdr[i].GetType() + "  " + rdr.GetName(i));
                        if (rdr[i].GetType().Equals(typeof(SqlGeometry)) ||
                            rdr.GetName(i).Equals(geomField))
                        { //skip
                            log.Debug("skip " + rdr[i].GetType());
                        }
                        else
                        {
                            string test2 = rdr.GetName(i) + "   " + geomField + "XAML";
                            if (rdr.GetName(i).Equals("ID"))
                            {
                                shp.ID = Parameters.table + "_" + rdr[i].ToString();
                                log.Debug(rdr[i].GetType() + "  " + rdr.GetName(i) + "  " + rdr[i].ToString());
                            }
                            else if (rdr.GetName(i).Equals(geomField + "XAML"))
                            {
                                log.Debug(rdr[i].GetType() + "  " + rdr.GetName(i) + "  " + rdr[i].ToString());
                                SqlGeography  geo     = (SqlGeography)rdr[geomField + "XAML"];
                                StringBuilder geoxaml = new StringBuilder();

                                log.Debug(geo.STGeometryType().ToString().ToUpper());
                                switch (geo.STGeometryType().ToString().ToUpper())
                                {
                                case "POINT":
                                {
                                    geoxaml.Append(XamlPoint(geo, Parameters.table, shp.ID));
                                    break;
                                }

                                case "LINESTRING":
                                {
                                    geoxaml.Append(XamlLinestring(geo, Parameters.table, shp.ID));
                                    break;
                                }

                                case "POLYGON":
                                {
                                    geoxaml.Append(XamlPolygon(geo, Parameters.table, shp.ID));
                                    break;
                                }

                                case "MULTILINESTRING":
                                {
                                    geoxaml.Append(XamlMultiLinestring(geo, Parameters.table, shp.ID));
                                    break;
                                }

                                case "MULTIPOLYGON":
                                {
                                    geoxaml.Append(XamlMultiPolygon(geo, Parameters.table, shp.ID));
                                    break;
                                }

                                case "GEOMETRYCOLLECTION":
                                {
                                    geoxaml.Append(XamlGeometryCollection(geo, Parameters.table, shp.ID));
                                    break;
                                }
                                }
                                xaml.Append(geoxaml.ToString());
                            }
                            else
                            {
                                shp.Fields.Add(rdr.GetName(i), rdr[i].ToString());
                                log.Debug(rdr.GetName(i) + " : " + rdr[i].ToString());
                            }
                        }
                    }
                    log.Debug(shp.ID + "  " + _XAMLResponse.XAML);
                    _XAMLResponse.OutputFields.Add(shp);
                }
                xaml.AppendLine("</m:MapLayer>");
                _XAMLResponse.XAML = xaml.ToString();
            }
            catch (ArithmeticException e)
            {
                ServiceException(_XAMLResponse, e.Message);
            }
            catch (ArgumentException e)
            {
                ServiceException(_XAMLResponse, e.Message);
            }
            catch (Exception e)
            {
                ServiceException(_XAMLResponse, e.Message);
            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
            queryStop = DateTime.Now;
            log.Debug(String.Format("Query Time: {0,0:0}ms", (queryStop - queryStart).TotalMilliseconds));
            _XAMLResponse.QueryTime   = (queryStop - queryStart).TotalMilliseconds;
            _XAMLResponse.totalPoints = totalPoints;

            return(_XAMLResponse);
        }