public static bool TableExists(string pTableName, string pSchemaName)
{
var mSql = String.Format("SELECT * FROM information_schema.tables WHERE table_schema = '{0}' AND table_name = '{1}' LIMIT 1;", pSchemaName, pTableName);
var mReader = MySQLLib.ExecuteReader(mSql);
if (mReader.HasRows)
{
return(true);
}
else
{
return(false);
}
}
public static int ExecuteNonQuery(string pSql)
{
var mCmd = MySQLLib.Cmd(pSql);
return(mCmd.ExecuteNonQuery());
}
public static object ExecuteScalar(string pSql)
{
var mCmd = MySQLLib.Cmd(pSql);
return(mCmd.ExecuteScalar());
}
public static OdbcDataReader ExecuteReader(string pSql)
{
var mCmd = MySQLLib.Cmd(pSql);
return(mCmd.ExecuteReader());
}
public static ReturnValue ExportFeatureLayerToMySQL(string pConnStr, IPointLayer pLayer, string pSchemaName, bool pTruncateTable, int pSrcEPSG = 32640, int pTgtEPSG = 4326)
{
var mRV = new ReturnValue();
var mSrcSRS = new SpatialReference(null);
mSrcSRS.ImportFromEPSG(pSrcEPSG);
var mTgtSRS = new SpatialReference(null);
mTgtSRS.ImportFromEPSG(pTgtEPSG);
var mTransformation = Osr.CreateCoordinateTransformation(mSrcSRS, mTgtSRS);
MySQLLib.Create(pConnStr);
// Check if table exists
if (!MySQLLib.TableExists("geoobjects", pSchemaName))
{
var mSql = "delimiter $$ CREATE TABLE `geoobjects` ( `id` int(11) NOT NULL AUTO_INCREMENT, `geom` geometry NOT NULL, `left` float(10,6) NOT NULL, `bottom` float(10,6) NOT NULL, `right` float(10,6) NOT NULL, `top` float(10,6) NOT NULL, `namepart` varchar(250) DEFAULT NULL, `numberpart` int(11) DEFAULT NULL, `title` varchar(250) DEFAULT NULL, `description` text, `gtype` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), SPATIAL KEY `idxSpatial` (`geom`), FULLTEXT KEY `idxFullText` (`title`,`description`)) ENGINE=MyISAM AUTO_INCREMENT=2585 DEFAULT CHARSET=utf8$$";
SLib.ExecuteNonQuery(mSql);
}
if (pTruncateTable)
{
var mSql = "DELETE FROM geoobjects";
MySQLLib.ExecuteNonQuery(mSql);
}
foreach (IFeature mFeature in pLayer.DataSet.Features)
{
var mGeom = OSGeo.OGR.Geometry.CreateFromWkb(mFeature.ToBinary());
if (pSrcEPSG != pTgtEPSG)
{
mGeom.Transform(mTransformation);
}
Envelope mEnvelope = new Envelope();
mGeom.GetEnvelope(mEnvelope);
string mWkt;
mGeom.ExportToWkt(out mWkt);
var mSql = String.Format("INSERT INTO `admadr`.`geoobjects` (`geom`, `left`, `bottom`, `right`, `top`, `namepart`, `numberpart`, `title`, `description`, `gtype`) VALUES (GeomFromText('{0}'),{1},{2},{3},{4},'{5}',{6}, '{7}', '{8}', '{9}')",
mWkt,
mEnvelope.MinX,
mEnvelope.MinY,
mEnvelope.MaxX,
mEnvelope.MaxY,
Utilities.NormalizeString(mFeature.DataRow["ROADNAME_EN"].ToString()),
mFeature.DataRow["ADDRESSUNITNR"], // Function to convert to number?
mFeature.DataRow["ADDRESSUNITNR"] + ", " + Utilities.NormalizeString(mFeature.DataRow["ROADNAME_EN"].ToString()),
Utilities.GetVariants(mFeature.DataRow["ROADNAME_EN"].ToString()),
"address"
);
MySQLLib.ExecuteNonQuery(mSql);
}
MySQLLib.Dbx.Close();
MySQLLib.Dbx.Dispose();
return(mRV);
}