示例#1
0
        /// <summary>
        /// Returns a datarow based on an object id (OID).
        /// </summary>
        /// <param name="oid"></param>
        /// <returns>datarow</returns>
        public SharpMap.Data.FeatureDataRow <uint> GetFeature(uint oid)
        {
            using (SqlConnection conn = new SqlConnection(_ConnectionString))
            {
                string strSQL = "SELECT *, " + this.GeometryColumn + " AS sharpmap_tempgeometry FROM " + this.Table + " WHERE " + this.ObjectIdColumn + "='" + oid.ToString() + "'";
                using (SqlDataAdapter adapter = new SqlDataAdapter(strSQL, conn))
                {
                    DataSet ds = new DataSet();
                    conn.Open();
                    adapter.Fill(ds);
                    conn.Close();

                    if (ds.Tables.Count > 0)
                    {
                        FeatureDataTable <uint> fdt = new FeatureDataTable <uint>(ds.Tables[0], "OID");

                        foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
                        {
                            if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry" && !col.ColumnName.StartsWith("Envelope_"))
                            {
                                fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
                            }
                        }

                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            System.Data.DataRow dr = ds.Tables[0].Rows[0];
                            SharpMap.Data.FeatureDataRow <uint> fdr = fdt.NewRow(oid);

                            foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
                            {
                                if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry" &&
                                    !col.ColumnName.StartsWith("Envelope_") && String.Compare(col.ColumnName, "oid", StringComparison.CurrentCultureIgnoreCase) != 0)
                                {
                                    fdr[col.ColumnName] = dr[col];
                                }
                            }

                            if (dr["sharpmap_tempgeometry"] != DBNull.Value)
                            {
                                fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
                            }

                            return(fdr);
                        }
                        else
                        {
                            return(null);
                        }
                    }
                    else
                    {
                        return(null);
                    }
                }
            }
        }
示例#2
0
        /// <summary>
        /// Creates a new table in a Microsoft SQL Server database and copies rows from an existing datasource.
        /// </summary>
        /// <remarks>
        /// <para>The datatable created will contain six extra columns besides the attribute data: "OID" (Object ID row),
        /// "WKB_Geometry" (Geometry stored as WKB), and Envelope_MinX, Envelope_MinY, Envelope_MaxX, Envelope_MaxY
        /// for geometry bounding box.</para>
        /// <para>
        /// <example>
        /// Upload a ShapeFile to a database:
        /// <code>
        /// public void CreateDatabase(string shapeFile)
        /// {
        ///		if (!System.IO.File.Exists(shapeFile))
        ///		{
        ///			MessageBox.Show("File not found");
        ///			return;
        ///		}
        ///		ShapeFile shp = new ShapeFile(shapeFile, false);
        ///		//Create tablename from filename
        ///		string tablename = shapeFile.Substring(shapeFile.LastIndexOf('\\') + 1,
        ///			shapeFile.LastIndexOf('.') - shapeFile.LastIndexOf('\\') - 1);
        ///		//Create connectionstring
        ///		string connstr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|GeoDatabase.mdf;Integrated Security=True;User Instance=True";
        ///		int count = SharpMap.Data.Providers.MsSql.CreateDataTable(shp, tablename, connstr);
        ///		MessageBox.Show("Uploaded " + count.ToString() + " features to datatable '" + tablename + "'");
        ///	}
        /// </code>
        /// </example>
        /// </para>
        /// </remarks>
        /// <param name="datasource">Datasource to upload</param>
        /// <param name="tablename">Name of table to create (existing table will be overwritten!)</param>
        /// <param name="connstr">Connection string to database</param>
        /// <returns>Number or rows inserted, -1 if failed and 0 if table created but no rows inserted.</returns>
        public static int CreateDataTable(SharpMap.Data.Providers.IProvider <uint> datasource, string tablename, string connstr)
        {
            datasource.Open();
            FeatureDataRow       geom    = datasource.GetFeature(0);
            DataColumnCollection columns = geom.Table.Columns;
            int counter = -1;

            using (SqlConnection conn = new SqlConnection(connstr))
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;

                conn.Open();
                //Try to drop table if it exists
                try
                {
                    command.CommandText = "DROP TABLE \"" + tablename + "\";";
                    command.ExecuteNonQuery();
                }
                catch { }
                //Create new table for storing the datasource
                string sql = "CREATE TABLE " + tablename + " (oid INTEGER IDENTITY PRIMARY KEY, WKB_Geometry Image, " +
                             "Envelope_MinX real, Envelope_MinY real, Envelope_MaxX real, Envelope_MaxY real";
                foreach (DataColumn col in columns)
                {
                    if (col.DataType != typeof(String))
                    {
                        sql += ", " + col.ColumnName + " " + Type2SqlType(col.DataType).ToString();
                    }
                    else
                    {
                        sql += ", " + col.ColumnName + " VARCHAR(256)";
                    }
                }
                command.CommandText = sql + ");";
                command.ExecuteNonQuery();
                counter++;

                ReadOnlyCollection <uint> indexes = datasource.GetObjectIdsInView(datasource.GetExtents());
                //Select all indexes in shapefile, loop through each feature and insert them one-by-one
                foreach (uint idx in indexes)
                {
                    //Get feature from shapefile
                    SharpMap.Data.FeatureDataRow <uint> feature = datasource.GetFeature(idx);
                    if (counter == 0)
                    {
                        //Create insert script
                        string strSQL = " (";
                        foreach (DataColumn col in feature.Table.Columns)
                        {
                            strSQL += "@" + col.ColumnName + ",";
                        }

                        strSQL += "@WKB_Geometry,@Envelope_MinX,@Envelope_MinY, " +
                                  "@Envelope_MaxX,@Envelope_MaxY)";
                        strSQL = "INSERT INTO " + tablename + strSQL.Replace("@", "") + " VALUES" + strSQL;

                        command.CommandText = strSQL;
                        command.Parameters.Clear();
                        //Add datacolumn parameters
                        foreach (DataColumn col in feature.Table.Columns)
                        {
                            command.Parameters.Add("@" + col.ColumnName, Type2SqlType(col.DataType));
                        }

                        //Add geometry parameters
                        command.Parameters.Add("@WKB_Geometry", SqlDbType.VarBinary);
                        command.Parameters.Add("@Envelope_MinX", SqlDbType.Real);
                        command.Parameters.Add("@Envelope_MinY", SqlDbType.Real);
                        command.Parameters.Add("@Envelope_MaxX", SqlDbType.Real);
                        command.Parameters.Add("@Envelope_MaxY", SqlDbType.Real);
                    }
                    //Set values
                    foreach (DataColumn col in feature.Table.Columns)
                    {
                        command.Parameters["@" + col.ColumnName].Value = feature[col];
                    }
                    if (feature.Geometry != null)
                    {
                        command.Parameters["@WKB_Geometry"].Value = feature.Geometry.AsBinary();                         //Add the geometry as Well-Known Binary
                        SharpMap.Geometries.BoundingBox box = feature.Geometry.GetBoundingBox();
                        command.Parameters["@Envelope_MinX"].Value = box.Left;
                        command.Parameters["@Envelope_MinY"].Value = box.Bottom;
                        command.Parameters["@Envelope_MaxX"].Value = box.Right;
                        command.Parameters["@Envelope_MaxY"].Value = box.Top;
                    }
                    else
                    {
                        command.Parameters["@WKB_Geometry"].Value  = DBNull.Value;
                        command.Parameters["@Envelope_MinX"].Value = DBNull.Value;
                        command.Parameters["@Envelope_MinY"].Value = DBNull.Value;
                        command.Parameters["@Envelope_MaxX"].Value = DBNull.Value;
                        command.Parameters["@Envelope_MaxY"].Value = DBNull.Value;
                    }
                    //Insert row
                    command.ExecuteNonQuery();
                    counter++;
                }
                //Create indexes
                command.Parameters.Clear();
                command.CommandText = "CREATE INDEX [IDX_Envelope_MinX] ON " + tablename + " (Envelope_MinX)";
                command.ExecuteNonQuery();
                command.CommandText = "CREATE INDEX [IDX_Envelope_MinY] ON " + tablename + " (Envelope_MinY)";
                command.ExecuteNonQuery();
                command.CommandText = "CREATE INDEX [IDX_Envelope_MaxX] ON " + tablename + " (Envelope_MaxX)";
                command.ExecuteNonQuery();
                command.CommandText = "CREATE INDEX [IDX_Envelope_MaxY] ON " + tablename + " (Envelope_MaxY)";
                command.ExecuteNonQuery();

                conn.Close();
            }
            datasource.Close();
            return(counter);
        }