public void CanFetchGeometryAsText()
{
if (st.version.Major < 5)
{
return;
}
st.execSQL("DROP TABLE IF EXISTS Test");
st.execSQL("CREATE TABLE Test (v Geometry NOT NULL)");
MySqlGeometry v = new MySqlGeometry(47.37, -122.21);
var par = new MySqlParameter("?v", MySqlDbType.Geometry);
par.Value = v;
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?v)", st.conn);
cmd.Parameters.Add(par);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT AsText(v) FROM Test";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
var val = reader.GetString(0);
Assert.Equal("POINT(47.37 -122.21)", val);
}
}
public void CanCreateMySqlGeometryFromEmptyGeometryCollection()
{
var bytes = new byte[] { 0x00, 0x00, 0x00, 0x00, 0x01, 0x07, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00 };
MySqlGeometry v = new MySqlGeometry(MySqlDbType.Geometry, bytes);
Assert.Equal("POINT(3.45845952088873E-323 0)", v.ToString());
}
internal void Serialize(MySqlPacket packet, bool binary, MySqlConnectionStringBuilder settings)
{
if (!binary && (paramValue == null || paramValue == DBNull.Value))
{
packet.WriteStringNoNull("NULL");
}
else
{
if (ValueObject.MySqlDbType == MySqlDbType.Guid)
{
MySqlGuid g = (MySqlGuid)ValueObject;
g.OldGuids = settings.OldGuids;
ValueObject = g;
}
#if !CF
if (ValueObject.MySqlDbType == MySqlDbType.Geometry)
{
MySqlGeometry v = (MySqlGeometry)ValueObject;
if (v.IsNull && Value != null)
{
MySqlGeometry.TryParse(Value.ToString(), out v);
}
ValueObject = v;
}
#endif
ValueObject.WriteValue(packet, binary, paramValue, Size);
}
}
public void CanTryParseGeometryValueStringWithSRIDValue()
{
var mysqlGeometryResult = new MySqlGeometry(0, 0);
MySqlGeometry.TryParse("SRID=101;POINT (47.37 -122.21)", out mysqlGeometryResult);
Assert.Equal("SRID=101;POINT(47.37 -122.21)", mysqlGeometryResult.ToString());
}
public void CanFetchGeometryAsText()
{
executeSQL("DROP TABLE IF EXISTS Test");
executeSQL("CREATE TABLE Test (v Geometry NOT NULL)");
MySqlGeometry v = new MySqlGeometry(47.37, -122.21);
var par = new MySqlParameter("?v", MySqlDbType.Geometry);
par.Value = v;
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?v)", Connection);
cmd.Parameters.Add(par);
cmd.ExecuteNonQuery();
cmd.CommandText = Connection.driver.Version.isAtLeast(8, 0, 1) ?
"SELECT ST_AsText(v) FROM Test" :
"SELECT AsText(v) FROM Test";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
var val = reader.GetString(0);
Assert.Equal("POINT(47.37 -122.21)", val);
}
}
public void CanTryParseGeometryValueString()
{
MySqlGeometry v = new MySqlGeometry(0, 0);
MySqlGeometry.TryParse("POINT (47.37 -122.21)", out v);
Assert.Equal("POINT(47.37 -122.21)", v.ToString());
}
public void CanFetchGeometryAsBinary()
{
executeSQL("DROP TABLE IF EXISTS Test");
executeSQL("CREATE TABLE Test (v Geometry NOT NULL)");
MySqlGeometry v = new MySqlGeometry(47.37, -122.21);
var par = new MySqlParameter("?v", MySqlDbType.Geometry);
par.Value = v;
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?v)", Connection);
cmd.Parameters.Add(par);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT AsBinary(v) FROM Test";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
var val = reader.GetValue(0) as Byte[];
var MyGeometry = new MySqlGeometry(MySqlDbType.Geometry, val);
Assert.Equal("POINT(47.37 -122.21)", MyGeometry.ToString());
}
}
public void CanGetToStringFromMySqlGeometry()
{
MySqlGeometry v = new MySqlGeometry(47.37, -122.21);
var valToString = v.ToString();
Assert.Equal("POINT(47.37 -122.21)", valToString);
}
void CustomizingSqlCommands(BeanApi api, Bean bean)
{
/// ## Customizing SQL Commands
/// In some cases it is necessary to manually adjust parameters of a SQL command which is about to execute.
/// This can be done in the `QueryExecuting` event handler.
///
/// **Example 1.** Force `datetime2` type for all dates (SQL Server):
#if CODE
api.QueryExecuting += cmd => {
foreach (SqlParameter p in cmd.Parameters)
{
if (p.Value is DateTime)
{
p.SqlDbType = SqlDbType.DateTime2;
}
}
};
#endif
/// **Example 2.** Work with `MySqlGeometry` objects (MySQL/MariaDB):
#if CODE
api.QueryExecuting += cmd => {
foreach (MySqlParameter p in cmd.Parameters)
{
if (p.Value is MySqlGeometry)
{
p.MySqlDbType = MySqlDbType.Geometry;
}
}
};
bean["point"] = new MySqlGeometry(34.962, 34.066);
api.Store(bean);
#endif
}
public override object GetSqlGeometry(string wkt, int srid)
{
if (!MySqlGeometry.TryParse(wkt, out MySqlGeometry value))
{
return(null);
}
return(value);
}
public override double?GetYCoordinate(DbGeometry geometryValue)
{
if (geometryValue == null)
{
throw new ArgumentNullException("geometryValue");
}
var providerValue = new MySqlGeometry();
MySqlGeometry.TryParse(geometryValue.ProviderValue.ToString(), out providerValue);
return(providerValue.YCoordinate);
}
public override string AsText(DbGeometry geometryValue)
{
if (geometryValue == null)
{
throw new ArgumentNullException("geometryValue");
}
var providerValue = new MySqlGeometry();
MySqlGeometry.TryParse(geometryValue.ProviderValue.ToString(), out providerValue);
return(providerValue.ToString());
}
public override DbGeometry GetGeometry(int ordinal)
{
ReturnGeometryColumn(ordinal);
var geometryBytes = this.reader.GetValue(ordinal);
Type t = geometryBytes.GetType();
object geometry = Activator.CreateInstance(t);
var providerValue = new MySqlGeometry();
MySqlGeometry.TryParse(geometryBytes.ToString(), out providerValue);
return(MySqlSpatialServices.Instance.GeometryFromProviderValue(providerValue));
}
public void CanUseReaderGetMySqlGeometry()
{
executeSQL("DROP TABLE IF EXISTS Test");
executeSQL("CREATE TABLE Test (v Geometry NOT NULL)");
MySqlGeometry v = new MySqlGeometry(47.37, -122.21);
var par = new MySqlParameter("?v", MySqlDbType.Geometry);
par.Value = v;
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?v)", Connection);
cmd.Parameters.Add(par);
cmd.ExecuteNonQuery();
// reading as binary
if (Connection.driver.Version.isAtLeast(8, 0, 1))
{
cmd.CommandText = "SELECT ST_AsBinary(v) as v FROM Test";
}
else
{
cmd.CommandText = "SELECT AsBinary(v) as v FROM Test";
}
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
var val = reader.GetMySqlGeometry(0);
var valWithName = reader.GetMySqlGeometry("v");
Assert.Equal("POINT(47.37 -122.21)", val.ToString());
Assert.Equal("POINT(47.37 -122.21)", valWithName.ToString());
}
// reading as geometry
cmd.CommandText = "SELECT v as v FROM Test";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
var val = reader.GetMySqlGeometry(0);
var valWithName = reader.GetMySqlGeometry("v");
Assert.Equal("POINT(47.37 -122.21)", val.ToString());
Assert.Equal("POINT(47.37 -122.21)", valWithName.ToString());
}
}
public override DbGeometry GeometryFromProviderValue(object providerValue)
{
if (providerValue == null)
{
throw new ArgumentNullException("provider value");
}
var myGeom = new MySqlGeometry();
if (MySqlGeometry.TryParse(providerValue.ToString(), out myGeom))
{
return(DbGeometry.FromText(providerValue.ToString()));
}
else
{
return(null);
}
}
private static TGeometry ConvertFromProviderCore(MySqlGeometry v)
{
using var memoryStream = new MemoryStream(v.Value);
// MySQL starts it's spatial data with a 4 byte SRID, that is unexpected by WKBReader.
var biEndianBinaryReader = new BiEndianBinaryReader(memoryStream);
var srid = biEndianBinaryReader.ReadUInt32();
var geometryServices = _geometryServiceses.GetOrAdd(
srid,
b => new NtsGeometryServices(
NtsGeometryServices.Instance.DefaultCoordinateSequenceFactory,
NtsGeometryServices.Instance.DefaultPrecisionModel,
(int)b));
var reader = new WKBReader(geometryServices);
var geometry = reader.Read(memoryStream);
return((TGeometry)geometry);
}
internal void Serialize(MySqlPacket packet, bool binary, MySqlConnectionStringBuilder settings)
{
if (!binary && (this.paramValue == null || this.paramValue == DBNull.Value))
{
packet.WriteStringNoNull("NULL");
return;
}
if (this.ValueObject.MySqlDbType == MySqlDbType.Guid)
{
MySqlGuid mySqlGuid = (MySqlGuid)this.ValueObject;
mySqlGuid.OldGuids = settings.OldGuids;
this.ValueObject = mySqlGuid;
}
if (this.ValueObject.MySqlDbType == MySqlDbType.Geometry)
{
MySqlGeometry mySqlGeometry = (MySqlGeometry)this.ValueObject;
this.ValueObject = mySqlGeometry;
}
this.ValueObject.WriteValue(packet, binary, this.paramValue, this.Size);
}
public override object CreateProviderValue(DbGeometryWellKnownValue wellKnownValue)
{
if (wellKnownValue == null)
{
throw new ArgumentNullException("wellKnownValue");
}
if (wellKnownValue.WellKnownText != null)
{
var mysqlGeometry = new MySqlGeometry(true);
MySqlGeometry.TryParse(wellKnownValue.WellKnownText.ToString(), out mysqlGeometry);
return(mysqlGeometry);
}
else if (wellKnownValue.WellKnownBinary != null)
{
var mysqlGeometry = new MySqlGeometry(MySqlDbType.Geometry, wellKnownValue.WellKnownBinary);
return(mysqlGeometry);
}
return(null);
}
public void CanUseCreateProviderValueFunction()
{
using (DistribuitorsContext context = new DistribuitorsContext())
{
context.Database.Delete();
context.Database.Create();
context.Distributors.Add(new Distributor()
{
Name = "Graphic Design Institute",
point = DbGeometry.FromText("POINT(-122.336106 47.605049)"),
});
context.SaveChanges();
var point = (from u in context.Distributors
select u.point).First();
var geometryWellKnownValueWKT = new DbGeometryWellKnownValue()
{
CoordinateSystemId = 0,
WellKnownBinary = null,
WellKnownText = "POINT(1 2)"
};
MySqlGeometry providerValue = (MySqlGeometry)spatialServices.CreateProviderValue(geometryWellKnownValueWKT);
Assert.AreEqual("POINT(1 2)", providerValue.ToString());
var geometryWellKnownValueWKB = new DbGeometryWellKnownValue()
{
CoordinateSystemId = 0,
WellKnownBinary = providerValue.Value,
WellKnownText = null
};
MySqlGeometry providerValue_2 = (MySqlGeometry)spatialServices.CreateProviderValue(geometryWellKnownValueWKB);
Assert.AreEqual("POINT(1 2)", providerValue_2.ToString());
context.Database.Delete();
}
}
public void CanSaveSridValueOnGeometry()
{
executeSQL("DROP TABLE IF EXISTS Test");
executeSQL("CREATE TABLE Test (v Geometry NOT NULL)");
MySqlGeometry v = new MySqlGeometry(47.37, -122.21, 101);
var par = new MySqlParameter("?v", MySqlDbType.Geometry);
par.Value = v;
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?v)", Connection);
cmd.Parameters.Add(par);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT SRID(v) FROM Test";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
var val = reader.GetString(0);
Assert.Equal("101", val);
}
}
public override object GetSqlGeometry(string wkt, int srid)
{
if (!MySqlGeometry.TryParse(wkt, out MySqlGeometry value))
return null;
return value;
}
public override object FromStringValue(string xml)
{
return(MySqlGeometry.Parse(xml));
}
private static MySqlGeometry ConvertToProviderCore(TGeometry v) => MySqlGeometry.FromWkb(v.SRID, v.ToBinary());
private static void GetValuesByColumn(Object objClass, IDataReader reader, ClassMap classMap, bool bTryToLoadMemberClasses)
{
bool bFound = false;
for (int i = 0; i < reader.FieldCount; i++)
{
string strColumnName = reader.GetName(i);
PropertyInfo prop = null;
if (classMap.DBProperties.TryGetValue(strColumnName, out prop))
{
bFound = true;
if (reader.IsDBNull(i))
{
prop.SetValue(objClass, null, null);
}
else
{
try
{
if (prop.PropertyType.IsEnum)
{
Type fieldType = reader.GetFieldType(i);
Object value = reader[i];
if (fieldType == typeof(String))
{
value = Convert.ToInt32(((String)value)[0]);
}
prop.SetValue(objClass, Enum.ToObject(prop.PropertyType, value), null);
}
else if (prop.PropertyType == typeof(PointD) && reader.GetFieldType(i) == typeof(byte[]))
{
Byte[] value = reader[i] as Byte[];
MySqlGeometry point = new MySqlGeometry(MySql.Data.MySqlClient.MySqlDbType.Geometry, (byte[])value);
prop.SetValue(objClass, new PointD((Double)point.XCoordinate, (Double)point.YCoordinate));
}
else if (prop.PropertyType.IsClass &&
prop.PropertyType.IsPrimitive == false &&
(prop.PropertyType.Namespace == null || prop.PropertyType.Namespace.StartsWith("System") == false))
{
TypeConverter tc = TypeDescriptor.GetConverter(prop.PropertyType);
object obj = tc.ConvertFrom(reader[i]);
prop.SetValue(objClass, obj, null);
// prop.SetValue(objClass, Convert.ChangeType(tc.ConvertFrom(reader[i]), prop.PropertyType), null);
}
else
{
// Bug Fix: 250
Type type = reader[i].GetType();
if (prop.PropertyType == typeof(System.DateTime))
{
if (type == typeof(Decimal) || type == typeof(Double))
{
// Special Handling For Decimal(17,3) Database Timestamps
prop.SetValue(objClass, DBUtil.GetDateTime(reader[i]), null);
}
else
{
try
{
Object o = reader[i];
if (o is MySql.Data.Types.MySqlDateTime && ((MySql.Data.Types.MySqlDateTime)o).IsValidDateTime == false)
{
prop.SetValue(objClass, DateTime.MinValue, null);
}
else
{
prop.SetValue(objClass, Convert.ChangeType(reader[i], prop.PropertyType), null);
}
}
catch (Exception)
{
prop.SetValue(objClass, DateTime.MinValue, null);
}
}
}
else
{
Object o = reader[i];
/** special handling for boolean 0/1 */
if (prop.PropertyType == typeof(bool) && Char.IsDigit(o.ToString()[0]))
{
prop.SetValue(objClass, o.ToString()[0] == '0' ? false : true, null);
}
/** special handling for blob as string */
else if (prop.PropertyType == typeof(String) && o.GetType() == typeof(byte[]))
{
prop.SetValue(objClass, ASCIIEncoding.UTF8.GetString((byte[])o), null);
}
else
{
prop.SetValue(objClass, Convert.ChangeType(reader[i], prop.PropertyType), null);
}
}
}
}
catch (Exception e)
{
System.Console.WriteLine("Conversion Error " + e);
}
}
}
}
if (bFound && bTryToLoadMemberClasses)
{
// roll through subtypes
foreach (KeyValuePair <String, PropertyInfo> pair in classMap.ClassProperties)
{
ConstructorInfo constructor = pair.Value.PropertyType.GetConstructor(new Type[0]);
if (constructor == null)
{
throw new Exception(String.Format("No constructor exists for target object type {0}", pair.Value.ToString()));
}
/**
* construct the target object
*/
Object objNew = constructor.Invoke(new Object[0]);
pair.Value.SetValue(objClass, Convert.ChangeType(objNew, pair.Value.PropertyType), null);
LoadClassFromDataReader(objNew, reader);
}
}
}
public void CanParseGeometryValueString()
{
var v = MySqlGeometry.Parse("POINT (47.37 -122.21)");
Assert.Equal("POINT(47.37 -122.21)", v.ToString());
}
internal PointD(MySqlGeometry mySqlGeometry)
{
this.x = (double)mySqlGeometry.XCoordinate;
this.Y = (double)mySqlGeometry.YCoordinate;
}
