Example #1
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 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++;
				Collection<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 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;
		}
Example #2
0
        /// <summary>
        /// Builds from the given provider.
        /// </summary>
        /// <param name="provider">
        /// The base <see cref="SharpMap.Data.Providers.IProvider"/> 
		/// from witch initialize the <see cref="NtsProvider"/> instance.
        /// </param>
        private void BuildFromProvider(SharpMap.Data.Providers.IProvider provider)
        {            
            // Features list initialization
            features = new List<GisSharpBlog.NetTopologySuite.Features.Feature>(provider.GetFeatureCount());

            try
            {
                // Load all features from the given provider
                provider.Open();                
                Collection<uint> ids = provider.GetObjectIDsInView(provider.GetExtents());             
                foreach (uint id in ids)
                {
                    SharpMap.Data.FeatureDataRow dataRow = provider.GetFeature(id);
                    GisSharpBlog.NetTopologySuite.Geometries.Geometry geometry = GeometryConverter.ToNTSGeometry(dataRow.Geometry, geometryFactory);
                    GisSharpBlog.NetTopologySuite.Features.AttributesTable attributes = new GisSharpBlog.NetTopologySuite.Features.AttributesTable();
                    foreach (DataColumn column in dataRow.Table.Columns)
                    {                        
                        if (dataRow[column] == null || dataRow[column].GetType() == typeof(System.DBNull))
                            throw new ApplicationException("Null values not supported");
                        attributes.AddAttribute(column.ColumnName, dataRow[column]);
                    }
                    features.Add(new GisSharpBlog.NetTopologySuite.Features.Feature(geometry, attributes));
                }
            }
            finally
            {
                if (provider.IsOpen)
                    provider.Close();
            }
        }
Example #3
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 datasource, string tablename, string connstr)
        {
            datasource.Open();
            FeatureDataRow geom = datasource.GetFeature(0);
            DataColumnCollection columns = geom.Table.Columns;
            int counter = -1;
            using (SQLiteConnection conn = new SQLiteConnection(connstr))
            {
                SQLiteCommand command = new SQLiteCommand();
                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 + " (fid INTEGER PRIMARY KEY, geom TEXT, " +
                    "minx REAL, miny REAL, maxx REAL, maxy REAL, oid INTEGER";
                foreach (DataColumn col in columns)
                    if (col.DataType != typeof(String))
                        sql += ", " + col.ColumnName + " " + Type2SqlLiteTypeString(col.DataType);
                    else
                        sql += ", " + col.ColumnName + " TEXT";
                command.CommandText = sql + ");";
                //command.CommandText = sql;
                command.ExecuteNonQuery();
                counter++;
                Collection<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 feature = datasource.GetFeature(idx);
                    if (counter == 0)
                    {

                        //Create insert script
                        string strSQL = " (";
                        foreach (DataColumn col in feature.Table.Columns)
                            strSQL += "@" + col.ColumnName + ",";

                        strSQL += "@geom,@minx,@miny, " +
                                    "@maxx,@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("@geom", DbType.Binary);
                        command.Parameters.Add("@minx", DbType.Double);
                        command.Parameters.Add("@miny", DbType.Double);
                        command.Parameters.Add("@maxx", DbType.Double);
                        command.Parameters.Add("@maxy", DbType.Double);
                    }
                    //Set values
                    foreach (DataColumn col in feature.Table.Columns)
                        command.Parameters["@" + col.ColumnName].Value = feature[col];
                    if (feature.Geometry != null)
                    {
                        System.Console.WriteLine(feature.Geometry.AsBinary().Length.ToString());
                        command.Parameters.AddWithValue("@geom", feature.Geometry.AsText()); //.AsBinary());
                        //command.Parameters["@geom"].Value = "X'" + ToHexString(feature.Geometry.AsBinary()) + "'"; //Add the geometry as Well-Known Binary
                        IEnvelope box = feature.Geometry.EnvelopeInternal;
                        command.Parameters["@minx"].Value = box.MinX;
                        command.Parameters["@miny"].Value = box.MinY;
                        command.Parameters["@maxx"].Value = box.MaxX;
                        command.Parameters["@maxy"].Value = box.MaxY;
                    }
                    else
                    {
                        command.Parameters["@geom"].Value = DBNull.Value;
                        command.Parameters["@minx"].Value = DBNull.Value;
                        command.Parameters["@miny"].Value = DBNull.Value;
                        command.Parameters["@maxx"].Value = DBNull.Value;
                        command.Parameters["@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;
        }