Hi,

I did some work on adding support for storing MapWinGIS shapefiles in databases.
ShapefileDataClient class is implemented in MapWindow.Controls. I added suport
of several providers, which should implement IDataProvider interface and
be passed to ShapefileDataClient Constructor.

Shape data stored as array of bytes (BLOB) in the same format it's done inside shapefile.
Two methods are added to pass data between MapWinGIS.Shape and it's binary representation:
Shape.ExportToBinary and Shape.ImportFromBinary. The particular data type for storing
BLOBs differs between databases so it should be defined by IDataProvider implementation.
Here is sample imlementation of the provider.

    /// <summary>
    /// SQLite provider for shapefile conveter
    /// </summary>
    public class SQLiteProvider: IDataProvider
    {
        public DbConnection CreateConnection()
        {
            return new SQLiteConnection();
        }
        public DbCommand CreateCommand()
        {
            return new SQLiteCommand();
        }
        public DbParameter CreateParameter()
        {
            return new SQLiteParameter();
        }
        public DbParameter CreateBinaryParameter()
        {
            SQLiteParameter param = new SQLiteParameter();
            param.DbType = DbType.Object;
            return param;
        }
        public DbDataAdapter CreateDataAdapter()
        {
            return new SQLiteDataAdapter();
        }
        public string GetBinaryTypeName()
        {
            return "BLOB";
        }
        public string GetIntergerTypeName()
        {
            return "INTEGER";
        }
        public string GetDoubleTypeName()
        {
            return "REAL";
        }
        public string GetStringTypeName(out bool fixedLength)
        {
            fixedLength = false;
            return "TEXT";
        }
    }

And the examples of the client code for saving shapefile in the database and
loading from it to create in-memory shapefile object.

/// <summary>
    /// Saves shapefile in the database
    /// </summary>
    private void button1_Click(object sender, EventArgs e)
    {
        DatabaseType dbType = DatabaseType.MsAccess;
       
        OpenFileDialog dlg = new OpenFileDialog();
        MapWinGIS.Shapefile sf = new Shapefile();
        dlg.Filter = sf.CdlgFilter;
        if (dlg.ShowDialog() == DialogResult.OK)
        {
            if (sf.Open(dlg.FileName, null))
            {
                dlg = new OpenFileDialog();
                dlg.Filter = this.GetDialogFilter(dbType);
                if (dlg.ShowDialog() == DialogResult.OK)
                {
                    string conn = ShapefileDataClient.CreateConnectionString(dbType, dlg.FileName);
                    ShapefileDataClient client = new ShapefileDataClient(this.GetDataProvider(dbType), conn);
                    client.SaveShapefile(sf, "Test");
                }
            }
        }
    }

    /// <summary>
    /// Loads shapefile from the database
    /// </summary>
    private void button2_Click(object sender, EventArgs e)
    {
        DatabaseType dbType = DatabaseType.MsAccess;
        
        OpenFileDialog dlg = new OpenFileDialog();
        dlg.Filter = this.GetDialogFilter(dbType);
        if (dlg.ShowDialog() == DialogResult.OK)
        {
            string conn = ShapefileDataClient.CreateConnectionString(dbType, dlg.FileName);
            ShapefileDataClient client = new ShapefileDataClient(this.GetDataProvider(dbType), conn);
            MapWinGIS.Shapefile sf = client.LoadShapefile("test");
            if (sf != null)
            {
                int handle = axMap1.AddLayer(sf, true);
                axMap1.ZoomToLayer(handle);
                axMap1.Redraw();
            }
        }
    }

    /// <summary>
    /// Gets dialog filter for specified database
    /// </summary>
    private string GetDialogFilter(DatabaseType dbType)
    {
        switch (dbType)
        {
            case DatabaseType.MsAccess:
                return "Microsoft Access (*.mdb)|*.mdb";
            case DatabaseType.SQLite:
                return "SQLite (*.db)|*.db";
            case DatabaseType.SqlServerCE:
                return "SQL Server CE (*.sdf)|*.sdf";
            default:
                return "";
        }
    }

    /// <summary>
    /// Gets provider for specified database
    /// </summary>
    private IDataProvider GetDataProvider(DatabaseType dbType)
    {
        switch (dbType)
        {
            case DatabaseType.MsAccess:
                return new OleDbProvider();
            case DatabaseType.SQLite:
                return new SQLiteProvider();
            case DatabaseType.SqlServerCE:
                return new SqlCeProvider();
            default:
                return null;
        }
    }

In theory such data storage will work for any database that has provider for .NET
and suitable type for long binary values.

Below are performance results. I was interested in the local databases (not client-server).
So MS Access, SQL Server CE 3.5 and SQLite were tested. Test data - USA counties shapefile
~3000 shapes, ~60 fields, total size - 2.82 MB.

	    saving, sec   loading, sec      size of database, MB
MS Access    10.98 *          2.17             2.96
SS CE 3.5     2.97	      2.18             9.89**
SQLite        1.83            0.92             2.65

* Access can load data a bit faster (~8 sec) using DataAdapter. But my code is based on direct
queries. It seems that Access takes no performance benefits from transactions.
** SQL Server CE can use less disk space infact (~3MB) if Geometry field is defined
as VARBINARY(size). But such fields are limited to 8000 bytes. Image type that was used
can stored much larger arrays but memory management is poor. VARBINARY(max) data type
isn't suported for CE.

From 3 databases SQLite is definitely the best choice: faster, good memory management,
good support in Visual Studio, easy to use. It's the only database that I was able to define
correct data types in the query from the first attempt.

So further steps:
1. Implement ShapefileDataClient.UpdateTable function - to commit changes from shapefile
to the database.
2. Support for SpatiaLite. Methods like Shape.ExportToSpatialLite, Shape.ImportFromSpatialLite
can be added. They should work with byte arrays. Then it will be possible to
use SpatiaLite functions in queries, or even to write a COM wrapper for it. But that's more
distant perspective.

If you got further ideas on the topiс - let me know.

Regards,
Sergei



[Edit:] Made image inline



Edited 2 time(s). Last edit at 08/15/2011 01:43PM by pmeems.

Sergei,

This is really great and thanks for sharing your code.
This will give a lot of opportunities for MapWindow but for plug-in builders as well.

Thanks,

Paul

--
Don't forget to read the new documentation: www.mapwindow.org/documentation/mapwingis4.8
Join us Google+: MapWindow GIS Google+ Community
Join the MapWindow Group on LinkedIn! LinkedIn - MapWindow Group

Download the latest beta installer at:
tinyurl.com/mwMonthly 32-Bit
tinyurl.com/mwMonthlyx64 64-Bit
Follow me on Twitter MapWindow_nl to read when a new installer is published.

---
Paul Meems
The Netherlands
[www.bontepaarden.nl]
Release manager, configuration manager and
forum moderator of MapWindow GIS

Owner of MapWindow.nl - Support for
Dutch speaking users: www.mapwindow.nl

*******
Everything I say or write is my personal opinion and
not the opinion of the company I work for.
*******
View my profile on LinkedIn