Exemplo n.º 1
0
        /// <summary>
        /// Imports the provided shapefile into a sqlite database using the VirtualShape extension
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="client"></param>
        /// <param name="filename"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static bool ImportShapefile(DbConnection conn, IDataClient client, string filename, string tableName, int srid)
        {
            try
            {
                if (!File.Exists(filename))
                {
                    _log.ErrorFormat("ImportShapefile Failed!: File does not exist {0}", filename);
                    return(false);
                }

                if (DataClient.HasTable(conn, client, tableName))
                {
                    client.GetCommand(string.Format("DROP TABLE \"{0}\"", tableName)).ExecuteNonQuery();
                }

                //trim off the '.shp' from the end
                filename = Path.Combine(Path.GetDirectoryName(filename), Path.GetFileNameWithoutExtension(filename));
                string sql = string.Format("CREATE VIRTUAL TABLE " + tableName + " USING VirtualShape('{0}', CP1252, {1});", filename, srid);
                client.GetCommand(sql, conn).ExecuteNonQuery();

                _log.DebugFormat("Imported Shapefile {0} into table {1}",
                                 Path.GetFileNameWithoutExtension(filename),
                                 tableName);

                return(true);
            }
            catch (Exception ex)
            {
                _log.Error("ImportShapefile failed: Error while loading shapefile ", ex);
            }
            return(false);
        }
Exemplo n.º 2
0
        /// <summary>
        /// Uses a select statement and the ADO.NET CommandBuilder
        /// to generate Insert,Update, and Delete statements, and load them onto an adapter
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="client"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DbDataAdapter GetMagicAdapter(DbConnection conn, IDataClient client, string sql)
        {
            var cmd     = client.GetCommand(sql, conn);
            var dba     = client.GetDataAdapter(cmd);
            var builder = client.GetCommandBuilder(dba);

            dba.InsertCommand = builder.GetInsertCommand(true);
            dba.DeleteCommand = builder.GetDeleteCommand(true);
            dba.UpdateCommand = builder.GetUpdateCommand(true);

            if (dba.InsertCommand != null)
            {
                dba.InsertCommand.CommandTimeout = client.QueryTimeout;
            }

            if (dba.DeleteCommand != null)
            {
                dba.DeleteCommand.CommandTimeout = client.QueryTimeout;
            }

            if (dba.UpdateCommand != null)
            {
                dba.UpdateCommand.CommandTimeout = client.QueryTimeout;
            }

            return(dba);
        }
Exemplo n.º 3
0
        protected DataTable SetupTable(DbConnection conn, IDataClient client, string tablename)
        {
            tempTableName = tablename;

            client.GetCommand(string.Format(CreateTableSQL, tablename), conn).ExecuteNonQuery();
            string selectAllSQL = string.Format("select * from \"{0}\"", tablename);

            return(DataClient.GetMagicTable(conn, client, selectAllSQL));
        }
Exemplo n.º 4
0
 public static int RowCount(DbConnection conn, IDataClient client, string tablename)
 {
     try
     {
         string sql = string.Format("select count(1) from {0}", tablename);
         var    cmd = client.GetCommand(sql, conn);
         return(Utilities.GetAs <int>(cmd.ExecuteScalar(), -1));
     }
     catch (Exception ex)
     {
         _log.Error("Error while looking for table", ex);
     }
     return(-1);
 }
Exemplo n.º 5
0
        /// <summary>
        /// Returns true if a particular table has a particular column
        /// </summary>
        /// <param name="columnname"></param>
        /// <param name="tablename"></param>
        /// <param name="conn"></param>
        /// <param name="client"></param>
        /// <returns></returns>
        public static bool HasColumn(string columnname, string tablename, DbConnection conn, IDataClient client)
        {
            string sql = string.Format("select * from {0} WHERE 0 = 1", tablename);

            var dt = GetMagicTable(conn, client, sql);

            var cmd = client.GetCommand(sql, conn);
            var dba = client.GetDataAdapter(cmd);

            dba.Fill(dt);

            if (dt != null)
            {
                return(dt.Columns.Contains(columnname));
            }
            return(false);
        }
Exemplo n.º 6
0
        /// <summary>
        /// 'Magically' populates a DataTable object with the contents of your sql query
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="client"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable GetMagicTable(DbConnection conn, IDataClient client, string sql)
        {
            DataTable dt = new DataTable();

            try
            {
                var cmd = client.GetCommand(sql, conn);
                var dba = client.GetDataAdapter(cmd);

                dba.Fill(dt);

                return(dt);
            }
            catch (Exception ex)
            {
                _log.Error("error during select", ex);
            }

            return(null);
        }
Exemplo n.º 7
0
        /// <summary>
        /// Uses a select statement and the ADO.NET CommandBuilder 
        /// to generate Insert,Update, and Delete statements, and load them onto an adapter
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="client"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DbDataAdapter GetMagicAdapter(DbConnection conn, IDataClient client, string sql)
        {
            var cmd = client.GetCommand(sql, conn);
            var dba = client.GetDataAdapter(cmd);
            var builder = client.GetCommandBuilder(dba);

            dba.InsertCommand = builder.GetInsertCommand(true);
            dba.DeleteCommand = builder.GetDeleteCommand(true);
            dba.UpdateCommand = builder.GetUpdateCommand(true);

            if (dba.InsertCommand != null)
                dba.InsertCommand.CommandTimeout = client.QueryTimeout;

            if (dba.DeleteCommand != null)
                dba.DeleteCommand.CommandTimeout = client.QueryTimeout;

            if (dba.UpdateCommand != null)
                dba.UpdateCommand.CommandTimeout = client.QueryTimeout;

            return dba;
        }
Exemplo n.º 8
0
        /// <summary>
        /// Imports the provided shapefile into a sqlite database using the VirtualShape extension
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="client"></param>
        /// <param name="filename"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static bool ImportShapefile(DbConnection conn, IDataClient client, string filename, string tableName, int srid)
        {
            try
            {
                if (!File.Exists(filename))
                {
                    _log.ErrorFormat("ImportShapefile Failed!: File does not exist {0}", filename);
                    return false;
                }

                if (DataClient.HasTable(conn, client, tableName))
                {
                    client.GetCommand(string.Format("DROP TABLE \"{0}\"", tableName)).ExecuteNonQuery();
                }

                //trim off the '.shp' from the end
                filename = Path.Combine(Path.GetDirectoryName(filename), Path.GetFileNameWithoutExtension(filename));
                string sql = string.Format("CREATE VIRTUAL TABLE " + tableName + " USING VirtualShape('{0}', CP1252, {1});", filename, srid);
                client.GetCommand(sql, conn).ExecuteNonQuery();

                _log.DebugFormat("Imported Shapefile {0} into table {1}",
                    Path.GetFileNameWithoutExtension(filename),
                    tableName);

                return true;
            }
            catch (Exception ex)
            {
                _log.Error("ImportShapefile failed: Error while loading shapefile ", ex);
            }
            return false;
        }
Exemplo n.º 9
0
 public void RemoveTemporaryTable(DbConnection conn, IDataClient client)
 {
     client.GetCommand(string.Format(DropTableSQL, tempTableName), conn).ExecuteNonQuery();
 }
Exemplo n.º 10
0
        protected DataTable SetupTable(DbConnection conn, IDataClient client, string tablename)
        {
            tempTableName = tablename;

            client.GetCommand(string.Format(CreateTableSQL, tablename), conn).ExecuteNonQuery();
            string selectAllSQL = string.Format("select * from \"{0}\"", tablename);
            return DataClient.GetMagicTable(conn, client, selectAllSQL);
        }
Exemplo n.º 11
0
 public void RemoveTemporaryTable(DbConnection conn, IDataClient client)
 {
     client.GetCommand(string.Format(DropTableSQL, tempTableName), conn).ExecuteNonQuery();
 }
Exemplo n.º 12
0
 public static int RowCount(DbConnection conn, IDataClient client, string tablename)
 {
     try
     {
         string sql = string.Format("select count(1) from {0}", tablename);
         var cmd = client.GetCommand(sql, conn);
         return Utilities.GetAs<int>(cmd.ExecuteScalar(), -1);
     }
     catch (Exception ex)
     {
         _log.Error("Error while looking for table", ex);
     }
     return -1;
 }
Exemplo n.º 13
0
        /// <summary>
        /// Returns true if a particular table has a particular column
        /// </summary>
        /// <param name="columnname"></param>
        /// <param name="tablename"></param>
        /// <param name="conn"></param>
        /// <param name="client"></param>
        /// <returns></returns>
        public static bool HasColumn(string columnname, string tablename, DbConnection conn, IDataClient client)
        {
            string sql = string.Format("select * from {0} WHERE 0 = 1", tablename);

            var dt = GetMagicTable(conn, client, sql);

            var cmd = client.GetCommand(sql, conn);
            var dba = client.GetDataAdapter(cmd);

            dba.Fill(dt);

            if (dt != null)
            {
                return dt.Columns.Contains(columnname);
            }
            return false;
        }
Exemplo n.º 14
0
        /// <summary>
        /// 'Magically' populates a DataTable object with the contents of your sql query 
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="client"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable GetMagicTable(DbConnection conn, IDataClient client, string sql)
        {
            DataTable dt = new DataTable();

            try
            {
                var cmd = client.GetCommand(sql, conn);
                var dba = client.GetDataAdapter(cmd);

                dba.Fill(dt);

                return dt;
            }
            catch (Exception ex)
            {

                _log.Error("error during select", ex);
            }

            return null;
        }