Insert geometry on spatialite always insert null C#

Question

I'm trying to insert a geometry (POINT, LINE, POLYGON) into a table in a sqlite DB (spatialite), all the code run OK but at time to see the rows, the column of geometry always is NULL.

The info in the DB after INSERT.

How it should be:

The result of string SQL is OK, I'm using

INSERT INTO Table1( Codigo, Nombre, Geometry) VALUES('1','Andalucia',GeomFromText('POLYGON((-75.8396284742612 8.39962954808519, ..., -75.8396284742612 8.39962954808519))', 4326));

INSERT INTO Table2( Codigo, Nombre, Geometry) VALUES('1','Andalucia',GeomFromText('POINT(-75.8361480385065 8.40883548152381)', 4326));

This is my code on C#:

public SQLiteConnection create_Open_SpatilaDB(string dbName)
{
    try
    {
        dbName += DateTime.Now.ToString("ddMMyyyyHHmmssff") + ".sqlite";

        string ruta = HttpContext.Current.Server.MapPath("/Uploads/" + dbName);

        string strConexion = "Data Source=" + ruta + ";Version=3;";

        SQLiteConnection conexion = null;

        if (!File.Exists(ruta))
        {
            // create the connection
            conexion = new SQLiteConnection(strConexion);

            //Abrir la conexión
            conexion.Open();

            //Load the lib
            conexion.EnableExtensions(true);
            conexion.LoadExtension("libspatialite-2.dll");                    

            //Create tables
            string SQL = "CREATE TABLE AppVersion ("
                        + " versionCode INTEGER NOT NULL PRIMARY KEY,"
                        + " state INTEGER NOT NULL);";

            SQLiteCommand cmd = new SQLiteCommand(SQL, conexion);
            cmd.ExecuteNonQuery();

            SQL = "CREATE TABLE Table1 ("                        
                + " Codigo TEXT NOT NULL PRIMARY KEY,"
                + " Nombre TEXT,"
                + " Geometry POLYGON);";

            cmd = new SQLiteCommand(SQL, conexion);
            cmd.ExecuteNonQuery();

            SQL = "CREATE TABLE Table2("
                    + " Codigo TEXT NOT NULL PRIMARY KEY,"
                    + " Nombre TEXT NOT NULL,"
                    + " Geometry POIN);";

            cmd = new SQLiteCommand(SQL, conexion);
            cmd.ExecuteNonQuery();
            .
            .
            .
        }
        else
        {
            conexion = new SQLiteConnection(strConexion);
            conexion.Open();
            conexion.EnableExtensions(true);
            conexion.LoadExtension("libspatialite-2.dll");
        }

        return conexion;

    }
    catch (Exception)
    {
        throw;
    }
}

And here i'm inserting the info:

public void insertInfo(SQLiteConnection conexion)
{
    try
    {
        string SQL = "BEGIN";
        SQLiteCommand cmd = new SQLiteCommand(SQL, conexion);
        cmd.ExecuteNonQuery();

        //Insert info table1        
        List<Table1> list = getInfoTable1();

        foreach (Table1 item in list)
        {
            SQL = "INSERT INTO Table1("
                    + " Codigo,"
                    + " Nombre,"
                    + " Geometry) VALUES("
                    + "'" + item.Codigo + "',"
                    + "'" + item.Nombre + "',"
                    + "GeomFromText(" + item.geom + "));";

            cmd = new SQLiteCommand(SQL, conexion);
            cmd.ExecuteNonQuery();
        }

        //Insert info table1        
        List<Table2> list = getInfoTable2();

        foreach (Table2 item in list)
        {
            SQL = "INSERT INTO Table2("
                    + " Codigo,"
                    + " Nombre,"
                    + " Geometry) VALUES("
                    + "'" + item.Codigo + "',"
                    + "'" + item.Nombre + "',"
                    + "GeomFromText(" + item.geom + "));";

            cmd = new SQLiteCommand(SQL, conexion);
            cmd.ExecuteNonQuery();
        }
        .
        .
        .
        SQL = "COMMIT";
        cmd = new SQLiteCommand(SQL, conexion);
        cmd.ExecuteNonQuery();
    }
    catch (Exception)
    {
        throw;
    }
}

score 1 · Answer 1 · answered May 31 '17 at 09:32

I think you have to mark the column of your table as the geometry column; otherwise it is not recognized as the geometry column.

For this you should use the RecoverGeometryColumn-Function in order to transform you normal column to a geometry column. Otherwise you could also use the AddGeometryColumn-Function Have a look on this link:
https://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/new-geom.html

Furthermore if you really want to use C# you should consider Spatialite > 4.2.0 with the System.Data.SQLite ADO.NET provider for SQLite.
Maybe you can have a look in this discussion here:
https://groups.google.com/forum/#!topic/spatialite-users/u2QZpQL_6ek

Insert geometry on spatialite always insert null C#

1 Answers1