mssql.cs

C# 的地图开发例子（sharp map)

		}

		#region IProvider Members

		/// <summary>
		/// Gets a collection of columns in the dataset
		/// </summary>
		public System.Data.DataColumnCollection Columns
		{
			get
			{
				throw new NotImplementedException();
			}
		}

		private int _srid = -2;

		/// <summary>
		/// Spacial Reference ID
		/// </summary>
		public int SRID
		{
			get { return _srid; }
			set { _srid = value; }
		}

		/// <summary>
		/// Returns a datarow based on a RowID
		/// </summary>
		/// <param name="RowID"></param>
		/// <returns>datarow</returns>
		public SharpMap.Data.FeatureDataRow GetFeature(uint RowID)
		{
			using (SqlConnection conn = new SqlConnection(_ConnectionString))
			{
				string strSQL = "SELECT *, " + this.GeometryColumn + " AS sharpmap_tempgeometry FROM " + this.Table + " WHERE " + this.ObjectIdColumn + "='" + RowID.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 fdt = new FeatureDataTable(ds.Tables[0]);
						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 fdr = fdt.NewRow();
							foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
								if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry" && !col.ColumnName.StartsWith("Envelope_"))
									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;
				}
			}
		}

		/// <summary>
		/// Boundingbox of dataset
		/// </summary>
		/// <returns>boundingbox</returns>
		public SharpMap.Geometries.BoundingBox GetExtents()
		{
			SharpMap.Geometries.BoundingBox box = null;
			using (SqlConnection conn = new SqlConnection(_ConnectionString))
			{
				string strSQL = "SELECT Min(Envelope_MinX) AS MinX, Min(Envelope_MinY) AS MinY, Max(Envelope_MaxX) AS MaxX, Max(Envelope_MaxY) AS MaxY FROM " + this.Table;
				if (!String.IsNullOrEmpty(_defintionQuery))
					strSQL += " WHERE " + this.DefinitionQuery;
				using (SqlCommand command = new SqlCommand(strSQL, conn))
				{
					conn.Open();
					using (SqlDataReader dr = command.ExecuteReader())
						if (dr.Read())
						{
							box = new SharpMap.Geometries.BoundingBox((float)dr[0], (float)dr[1], (float)dr[2], (float)dr[3]);
						}
					conn.Close();
				}
				return box;
			}
		}

		/// <summary>
		/// Gets the connection ID of the datasource
		/// </summary>
		public string ConnectionID
		{
			get { return _ConnectionString; }
		}

		private string GetBoxClause(SharpMap.Geometries.BoundingBox bbox)
		{
			return String.Format(SharpMap.Map.numberFormat_EnUS,
				"(Envelope_MinX < {0} AND Envelope_MaxX > {1} AND Envelope_MinY < {2} AND Envelope_MaxY > {3})",
				bbox.Max.X, bbox.Min.X, bbox.Max.Y, bbox.Min.Y);
		}

		/// <summary>
		/// Returns all features with the view box
		/// </summary>
		/// <param name="bbox">view box</param>
		/// <param name="ds">FeatureDataSet to fill data into</param>
		public void ExecuteIntersectionQuery(SharpMap.Geometries.BoundingBox bbox, SharpMap.Data.FeatureDataSet ds)
		{
			//List<Geometries.Geometry> features = new List<SharpMap.Geometries.Geometry>();
			using (SqlConnection conn = new SqlConnection(_ConnectionString))
			{
				string strSQL = "SELECT *, " + this.GeometryColumn + " AS sharpmap_tempgeometry ";
				strSQL += "FROM " + this.Table + " WHERE ";
				strSQL += GetBoxClause(bbox);

				if (!String.IsNullOrEmpty(_defintionQuery))
					strSQL += " AND " + this.DefinitionQuery;

				using (SqlDataAdapter adapter = new SqlDataAdapter(strSQL, conn))
				{
					conn.Open();
					System.Data.DataSet ds2 = new System.Data.DataSet();
					adapter.Fill(ds2);
					conn.Close();
					if (ds2.Tables.Count > 0)
					{
						FeatureDataTable fdt = new FeatureDataTable(ds2.Tables[0]);
						foreach (System.Data.DataColumn col in ds2.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);
						foreach (System.Data.DataRow dr in ds2.Tables[0].Rows)
						{
							SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
							foreach (System.Data.DataColumn col in ds2.Tables[0].Columns)
								if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry" && !col.ColumnName.StartsWith("Envelope_"))
									fdr[col.ColumnName] = dr[col];
							if (dr["sharpmap_tempgeometry"] != DBNull.Value)
								fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
							fdt.AddRow(fdr);
						}
						ds.Tables.Add(fdt);
					}
				}
			}
		}
		#endregion

		/// <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;
		}
		/// <summary>
		/// Returns the name of the SqlServer datatype based on a .NET datatype
		/// </summary>
		/// <param name="t"></param>
		/// <returns></returns>
		private static SqlDbType Type2SqlType(Type t)
		{
			switch (t.ToString())
			{
				case "System.Boolean": return System.Data.SqlDbType.Bit;
				case "System.Single": return System.Data.SqlDbType.Real;
				case "System.Double": return System.Data.SqlDbType.Float;
				case "System.Int16": return System.Data.SqlDbType.SmallInt;
				case "System.Int32": return System.Data.SqlDbType.Int;
				case "System.Int64": return System.Data.SqlDbType.BigInt;
				case "System.DateTime": return System.Data.SqlDbType.DateTime;
				case "System.Byte[]": return System.Data.SqlDbType.Image;
				case "System.String": return System.Data.SqlDbType.VarChar;
				default:
					throw (new NotSupportedException("Unsupported datatype '" + t.Name + "' found in datasource"));
			}
		}
	}
}