void IMySqlValue.WriteValue(MySqlPacket packet, bool binary, object val, int length)
{
byte[] array = null;
try
{
array = ((MySqlGeometry)val)._valBinary;
}
catch
{
array = (val as byte[]);
}
if (array == null)
{
MySqlGeometry mySqlGeometry = new MySqlGeometry(0.0, 0.0);
MySqlGeometry.TryParse(val.ToString(), out mySqlGeometry);
array = mySqlGeometry._valBinary;
}
byte[] array2 = new byte[25];
for (int i = 0; i < array.Length; i++)
{
if (array.Length < 25)
{
array2[i + 4] = array[i];
}
else
{
array2[i] = array[i];
}
}
packet.WriteStringNoNull("_binary ");
packet.WriteByte(39);
MySqlGeometry.EscapeByteArray(array2, 25, packet);
packet.WriteByte(39);
}
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 static bool TryParse(string value, out MySqlGeometry mySqlGeometryValue)
{
string[] array = new string[0];
string text = string.Empty;
bool flag = false;
bool flag2 = false;
double xValue = 0.0;
double yValue = 0.0;
int srid = 0;
try
{
if (value.Contains(";"))
{
array = value.Split(new char[]
{
';'
});
}
else
{
text = value;
}
if (array.Length > 1 || text != string.Empty)
{
string text2 = (text != string.Empty) ? text : array[1];
text2 = text2.Replace("POINT (", "").Replace("POINT(", "").Replace(")", "");
string[] array2 = text2.Split(new char[]
{
' '
});
if (array2.Length > 1)
{
flag = double.TryParse(array2[0], out xValue);
flag2 = double.TryParse(array2[1], out yValue);
}
if (array.Length >= 1)
{
int.TryParse(array[0].Replace("SRID=", ""), out srid);
}
}
if (flag && flag2)
{
mySqlGeometryValue = new MySqlGeometry(xValue, yValue, srid);
return(true);
}
}
catch
{
}
mySqlGeometryValue = new MySqlGeometry(true);
return(false);
}
public static MySqlGeometry Parse(string value)
{
if (string.IsNullOrEmpty(value))
{
throw new ArgumentNullException("value");
}
if (!value.Contains("SRID") && !value.Contains("POINT(") && !value.Contains("POINT ("))
{
throw new FormatException("String does not contain a valid geometry value");
}
MySqlGeometry result = new MySqlGeometry(0.0, 0.0);
MySqlGeometry.TryParse(value, out result);
return(result);
}
/// <summary>
/// Try to get value from WKT format
/// SRID=0;POINT (x y) or POINT (x y)
/// </summary>
/// <param name="value">WKT string format</param>
public static bool TryParse(string value, out MySqlGeometry mySqlGeometryValue)
{
string[] arrayResult = new string[0];
string strResult = string.Empty;
bool hasX = false;
bool hasY = false;
Double xVal = 0;
Double yVal = 0;
int sridValue = 0;
try
{
if (value.Contains(";"))
{
arrayResult = value.Split(';');
}
else
{
strResult = value;
}
if (arrayResult.Length > 1 || strResult != String.Empty)
{
string point = strResult != String.Empty ? strResult : arrayResult[1];
point = point.Replace("POINT (", "").Replace("POINT(", "").Replace(")", "");
var coord = point.Split(' ');
if (coord.Length > 1)
{
hasX = Double.TryParse(coord[0], out xVal);
hasY = Double.TryParse(coord[1], out yVal);
}
if (arrayResult.Length >= 1)
{
Int32.TryParse(arrayResult[0].Replace("SRID=", ""), out sridValue);
}
}
if (hasX && hasY)
{
mySqlGeometryValue = new MySqlGeometry(xVal, yVal, sridValue);
return(true);
}
}
catch
{ }
mySqlGeometryValue = new MySqlGeometry(true);
return(false);
}
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);
}
IMySqlValue IMySqlValue.ReadValue(MySqlPacket packet, long length, bool nullVal)
{
MySqlGeometry mySqlGeometry;
if (nullVal)
{
mySqlGeometry = new MySqlGeometry(this._type, true);
}
else
{
if (length == -1L)
{
length = packet.ReadFieldLength();
}
byte[] array = new byte[length];
packet.Read(array, 0, (int)length);
mySqlGeometry = new MySqlGeometry(this._type, array);
}
return(mySqlGeometry);
}
IMySqlValue IMySqlValue.ReadValue(MySqlPacket packet, long length, bool nullVal)
{
MySqlGeometry g;
if (nullVal)
{
g = new MySqlGeometry(_type, true);
}
else
{
if (length == -1)
{
length = (long)packet.ReadFieldLength();
}
byte[] newBuff = new byte[length];
packet.Read(newBuff, 0, (int)length);
g = new MySqlGeometry(_type, newBuff);
}
return(g);
}
void IMySqlValue.WriteValue(MySqlPacket packet, bool binary, object val, int length)
{
byte[] buffToWrite = null;
try
{
buffToWrite = ((MySqlGeometry)val)._valBinary;
}
catch
{
buffToWrite = val as Byte[];
}
if (buffToWrite == null)
{
MySqlGeometry v = new MySqlGeometry(0, 0);
MySqlGeometry.TryParse(val.ToString(), out v);
buffToWrite = v._valBinary;
}
byte[] result = new byte[GEOMETRY_LENGTH];
for (int i = 0; i < buffToWrite.Length; i++)
{
if (buffToWrite.Length < GEOMETRY_LENGTH)
{
result[i + 4] = buffToWrite[i];
}
else
{
result[i] = buffToWrite[i];
}
}
packet.WriteStringNoNull("_binary ");
packet.WriteByte((byte)'\'');
EscapeByteArray(result, GEOMETRY_LENGTH, packet);
packet.WriteByte((byte)'\'');
}
/// <summary>
/// Try to get value from WKT format
/// SRID=0;POINT (x y) or POINT (x y)
/// </summary>
/// <param name="value">WKT string format</param>
public static bool TryParse(string value, out MySqlGeometry mySqlGeometryValue)
{
string[] arrayResult = new string[0];
string strResult = string.Empty;
bool hasX = false;
bool hasY = false;
Double xVal = 0;
Double yVal = 0;
int sridValue = 0;
try
{
if (value.Contains(";"))
arrayResult = value.Split(';');
else
strResult = value;
if (arrayResult.Length > 1 || strResult != String.Empty)
{
string point = strResult != String.Empty ? strResult : arrayResult[1];
point = point.Replace("POINT (", "").Replace("POINT(", "").Replace(")", "");
var coord = point.Split(' ');
if (coord.Length > 1)
{
hasX = Double.TryParse(coord[0], out xVal);
hasY = Double.TryParse(coord[1], out yVal);
}
if (arrayResult.Length >= 1)
Int32.TryParse(arrayResult[0].Replace("SRID=", ""), out sridValue);
}
if (hasX && hasY)
{
mySqlGeometryValue = new MySqlGeometry(xVal, yVal, sridValue);
return true;
}
}
catch
{ }
mySqlGeometryValue = new MySqlGeometry(true);
return false;
}
public MySqlGeometry(double xValue, double yValue)
{
this = new MySqlGeometry(MySqlDbType.Geometry, xValue, yValue, 0);
}
public MySqlGeometry(double xValue, double yValue, int srid)
{
this = new MySqlGeometry(MySqlDbType.Geometry, xValue, yValue, srid);
}
public void CanSaveSridValueOnGeometry()
{
if (version.Major < 5) return;
execSQL("DROP TABLE IF EXISTS Test");
execSQL("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)", conn);
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.AreEqual("101", val);
}
}
public MySqlGeometry(bool isNull)
{
this = new MySqlGeometry(MySqlDbType.Geometry, isNull);
}
public void CanTryParseGeometryValueStringWithSRIDValue()
{
var mysqlGeometryResult = new MySqlGeometry(0, 0);
MySqlGeometry.TryParse("SRID=101;POINT (47.37 -122.21)", out mysqlGeometryResult);
Assert.AreEqual("SRID=101;POINT(47.37 -122.21)", mysqlGeometryResult.ToString());
}
public void CanTryParseGeometryValueString()
{
MySqlGeometry v = new MySqlGeometry(0, 0);
MySqlGeometry.TryParse("POINT (47.37 -122.21)", out v);
Assert.AreEqual("POINT(47.37 -122.21)", v.ToString());
}
void IMySqlValue.WriteValue(MySqlPacket packet, bool binary, object val, int length)
{
byte[] buffToWrite = null;
try
{
buffToWrite = ((MySqlGeometry)val)._valBinary;
}
catch
{
buffToWrite = val as Byte[];
}
if (buffToWrite == null)
{
MySqlGeometry v = new MySqlGeometry(0, 0);
MySqlGeometry.TryParse(val.ToString(), out v);
buffToWrite = v._valBinary;
}
byte[] result = new byte[GEOMETRY_LENGTH];
for (int i = 0; i < buffToWrite.Length; i++)
{
if (buffToWrite.Length < GEOMETRY_LENGTH)
result[i + 4] = buffToWrite[i];
else
result[i] = buffToWrite[i];
}
packet.WriteStringNoNull("_binary ");
packet.WriteByte((byte)'\'');
EscapeByteArray(result, GEOMETRY_LENGTH, packet);
packet.WriteByte((byte)'\'');
}
public void CanUseReaderGetMySqlGeometry()
{
if (version.Major < 5) return;
execSQL("DROP TABLE IF EXISTS Test");
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)", conn);
cmd.Parameters.Add(par);
cmd.ExecuteNonQuery();
// reading as binary
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.AreEqual("POINT(47.37 -122.21)", val.ToString());
Assert.AreEqual("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.AreEqual("POINT(47.37 -122.21)", val.ToString());
Assert.AreEqual("POINT(47.37 -122.21)", valWithName.ToString());
}
}
public void CanGetToStringFromMySqlGeometry()
{
MySqlGeometry v = new MySqlGeometry(47.37, -122.21);
var valToString = v.ToString();
Assert.AreEqual("POINT(47.37 -122.21)", valToString);
}
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;
}
/// <summary>
/// Get value from WKT format
/// SRID=0;POINT (x y) or POINT (x y)
/// </summary>
/// <param name="value">WKT string format</param>
public static MySqlGeometry Parse(string value)
{
if (String.IsNullOrEmpty(value))
throw new ArgumentNullException("value");
if (!(value.Contains("SRID") || value.Contains("POINT(") || value.Contains("POINT (")))
throw new FormatException("String does not contain a valid geometry value");
MySqlGeometry result = new MySqlGeometry(0,0);
MySqlGeometry.TryParse(value, out result);
return result;
}
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 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;
}
IMySqlValue IMySqlValue.ReadValue(MySqlPacket packet, long length, bool nullVal)
{
MySqlGeometry g;
if (nullVal)
g = new MySqlGeometry(_type, true);
else
{
if (length == -1)
length = (long)packet.ReadFieldLength();
byte[] newBuff = new byte[length];
packet.Read(newBuff, 0, (int)length);
g = new MySqlGeometry(_type, newBuff);
}
return g;
}
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
}
