/// <summary>
        /// Reads the complete feature set from the database
        /// </summary>
        /// <param name="connString">sqlite db connection string</param>
        /// <param name="featureSetInfo">information about the table</param>
        /// <param name="sql">the sql query</param>
        /// <returns>the resulting feature set</returns>
        public IFeatureSet ReadFeatureSet(string connString, GeometryColumnInfo featureSetInfo, string sql)
        {
            DataTable   tab   = new DataTable();
            FeatureType fType = GetGeometryType(featureSetInfo.GeometryType);
            FeatureSet  fs    = new FeatureSet(fType);

            fs.IndexMode = false; //setting the initial index mode..

            using (SQLiteCommand cmd = CreateCommand(connString, sql))
            {
                cmd.Connection.Open();

                RunInitialCommands(cmd.Connection);

                //DotSpatial.Topology.Utilities.WkbReader wkbr = new DotSpatial.Topology.Utilities.WkbReader();
                SpatiaLiteWkbReader wkbr = new SpatiaLiteWkbReader();

                SQLiteDataReader rdr = cmd.ExecuteReader();

                string[] columnNames = PopulateTableSchema(fs, featureSetInfo.GeometryColumnName, rdr);
                int      numColumns  = fs.DataTable.Columns.Count;

                while (rdr.Read())
                {
                    byte[]    wkb  = rdr[featureSetInfo.GeometryColumnName] as byte[];
                    IGeometry geom = wkbr.Read(wkb);

                    IFeature newFeature = fs.AddFeature(geom);

                    //populate the attributes
                    foreach (string colName in columnNames)
                    {
                        newFeature.DataRow[colName] = rdr[colName];
                    }
                }
                cmd.Connection.Close();
                fs.Name = featureSetInfo.TableName;

                //HACK required for selection to work properly
                fs.IndexMode = true;

                //assign projection
                ProjectionInfo proj = ProjectionInfo.FromEpsgCode(featureSetInfo.SRID);
                fs.Projection = proj;

                return(fs);
            }
        }
        // finds out the geometry column information..
        private GeometryColumnInfo FindGeometryColumnInfo(string connString, string sqlQuery)
        {
            GeometryColumnInfo result = null;

            using (var cmd = CreateCommand(connString, sqlQuery))
            {
                cmd.Connection.Open();

                RunInitialCommands(cmd.Connection);

                var wkbr = new SpatiaLiteWkbReader();

                var rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);

                var schemaTable = rdr.GetSchemaTable();
                if (schemaTable == null)
                {
                    return(null);
                }

                foreach (DataRow r in schemaTable.Rows)
                {
                    var colName     = Convert.ToString(r["ColumnName"]);
                    var colDataType = Convert.ToString(r["DataType"]);

                    // if BLOB, then assume geometry column
                    if (Type.GetType(colDataType) == typeof(byte[]))
                    {
                        result = new GeometryColumnInfo {
                            GeometryColumnName = colName
                        };
                        break;
                    }
                }

                if (result != null && rdr.HasRows)
                {
                    rdr.Read();
                    var blob = rdr[result.GeometryColumnName] as byte[];
                    var geom = wkbr.Read(blob);
                    result.GeometryType = geom.GeometryType;
                }

                cmd.Connection.Close();
                return(result);
            }
        }
        /// <summary>
        /// Reads the complete feature set from the database
        /// </summary>
        /// <param name="featureSetInfo">information about the table</param>
        /// <param name="sql">the sql query</param>
        /// <returns>the resulting feature set</returns>
        public IFeatureSet ReadFeatureSet(GeometryColumnInfo featureSetInfo, string sql)
        {
            var fType = GetGeometryType(featureSetInfo.GeometryType);
            SpatiaLiteFeatureSet fs = new SpatiaLiteFeatureSet(fType)
            {
                IndexMode = true, // setting the initial index mode..
                Name      = featureSetInfo.TableName,
                Filename  = SqLiteHelper.GetSqLiteFileName(ConnectionString),
                LayerName = featureSetInfo.TableName
            };

            using (var cmd = CreateCommand(ConnectionString, sql))
            {
                cmd.Connection.Open();

                var wkbr = new SpatiaLiteWkbReader();

                var rdr = cmd.ExecuteReader();

                var columnNames = PopulateTableSchema(fs, featureSetInfo.GeometryColumnName, rdr);
                while (rdr.Read())
                {
                    var wkb  = rdr[featureSetInfo.GeometryColumnName] as byte[];
                    var geom = wkbr.Read(wkb);

                    var newFeature = fs.AddFeature(geom);

                    // populate the attributes
                    foreach (var colName in columnNames)
                    {
                        newFeature.DataRow[colName] = rdr[colName];
                    }
                }

                cmd.Connection.Close();

                // assign projection
                if (featureSetInfo.Srid > 0)
                {
                    var proj = ProjectionInfo.FromEpsgCode(featureSetInfo.Srid);
                    fs.Projection = proj;
                }

                return(fs);
            }
        }
        //finds out the geometry column information..
        private GeometryColumnInfo FindGeometryColumnInfo(string connString, string sqlQuery)
        {
            GeometryColumnInfo result = null;

            using (SQLiteCommand cmd = CreateCommand(connString, sqlQuery))
            {
                cmd.Connection.Open();

                RunInitialCommands(cmd.Connection);

                SpatiaLiteWkbReader wkbr = new SpatiaLiteWkbReader();

                SQLiteDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);

                DataTable schemaTable = rdr.GetSchemaTable();
                foreach (DataRow r in schemaTable.Rows)
                {
                    string colName = Convert.ToString(r["ColumnName"]);
                    string colDataType = Convert.ToString(r["DataType"]);
                    //if BLOB, then assume geometry column
                    if (Type.GetType(colDataType) == typeof(byte[]))
                    {
                        result = new GeometryColumnInfo();
                        result.GeometryColumnName = colName;
                        break;
                    }
                }

                if (result != null && rdr.HasRows)
                {
                    rdr.Read();
                    byte[] blob = rdr[result.GeometryColumnName] as byte[];
                    IGeometry geom = wkbr.Read(blob);
                    result.GeometryType = geom.GeometryType;
                }

                cmd.Connection.Close();
                return result;
            }
        }
        /// <summary>
        /// Reads the complete feature set from the database
        /// </summary>
        /// <param name="connString">sqlite db connection string</param>
        /// <param name="featureSetInfo">information about the table</param>
        /// <param name="sql">the sql query</param>
        /// <returns>the resulting feature set</returns>
        public IFeatureSet ReadFeatureSet(string connString, GeometryColumnInfo featureSetInfo, string sql)
        {
            DataTable tab = new DataTable();
            FeatureType fType = GetGeometryType(featureSetInfo.GeometryType);
            FeatureSet fs = new FeatureSet(fType);
            fs.IndexMode = false; //setting the initial index mode..

            using (SQLiteCommand cmd = CreateCommand(connString, sql))
            {
                cmd.Connection.Open();

                RunInitialCommands(cmd.Connection);

                //DotSpatial.Topology.Utilities.WkbReader wkbr = new DotSpatial.Topology.Utilities.WkbReader();
                SpatiaLiteWkbReader wkbr = new SpatiaLiteWkbReader();

                SQLiteDataReader rdr = cmd.ExecuteReader();

                string[] columnNames = PopulateTableSchema(fs, featureSetInfo.GeometryColumnName, rdr);
                int numColumns = fs.DataTable.Columns.Count;

                while (rdr.Read())
                {
                    byte[] wkb = rdr[featureSetInfo.GeometryColumnName] as byte[];
                    IGeometry geom = wkbr.Read(wkb);

                    IFeature newFeature = fs.AddFeature(geom);

                    //populate the attributes
                    foreach (string colName in columnNames)
                    {
                        newFeature.DataRow[colName] = rdr[colName];
                    }
                }
                cmd.Connection.Close();
                fs.Name = featureSetInfo.TableName;

                //HACK required for selection to work properly
                fs.IndexMode = true;

                //assign projection
                ProjectionInfo proj = ProjectionInfo.FromEpsgCode(featureSetInfo.SRID);
                fs.Projection = proj;

                return fs;
            }
        }