由于业务需要,写了个C#处理sqlite的工具程序。
using System;
using System.Collections.Generic;using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
//调用sqlite相关,依赖System.Data.SQLite.dll+SQLite.Interop.dll
using System.Data;
using System.Data.SQLite;
//调用外部程序
using System.Diagnostics;
//调用遍历目录中的文件
using System.Drawing;
using System.IO;
public struct tabStruct
{
public string link_id;
public string mesh_id;
public int verify_flag;
//public string verify_flag;
public string src;
};
namespace WpfApplication1
{
/// <summary>
/// MainWindow.xaml 的交互逻辑
/// </summary>
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
//需求1
private void Button_Click(object sender, RoutedEventArgs e)
{
//tab转sqlite
mycall("-f \"SQLite\" tab\\test.db -dsco SPATIALITE=NO -lco FORMAT=WKT tab\\nav_link_Out.TAB -lco GEOMETRY_NAME=geom");
//读入已经被转换的tab文件
var basefolder = AppDomain.CurrentDomain.BaseDirectory;
var testdbPath = System.IO.Path.Combine(basefolder, "tab", "test.db");
using (SQLiteConnection conn = new SQLiteConnection("Data Source=" + testdbPath + ";Pooling=true;FailIfMissing=false"))
{
conn.Open();
using (SQLiteCommand cmd = conn.CreateCommand())
{
//创建临时表 used_tab
cmd.CommandText = "drop table if exists used_tab";
cmd.ExecuteNonQuery();
cmd.CommandText = "create table used_tab as select * from nav_link_out where verify_flag_new <> verify_flag and verify_flag_new = 2";
cmd.ExecuteNonQuery();
//为临时表创建索引
cmd.CommandText = "drop index if exists index_tab_hzq;create index index_tab_hzq on used_tab(link_id)";
cmd.ExecuteNonQuery();
//附加库
string path = System.AppDomain.CurrentDomain.BaseDirectory;
path = path + "ttfa\\";
DirectoryInfo theFolder = new DirectoryInfo(path);
FileInfo[] fileInfo = theFolder.GetFiles();
//处理每个ttfa文件
foreach (FileInfo NextFile in fileInfo)
{
string ttfa_path = path + NextFile.Name;
string ttfa_name = NextFile.Name;
string alias_num = ttfa_name.Substring(0, 6);
string alias = "ttfa" + alias_num;
//增加库引用
cmd.CommandText = "attach \"" + ttfa_path + "\" as "+alias;
cmd.ExecuteNonQuery();
//进行匹配,修改alias ttfa,删除test tab
//待修改数据
cmd.CommandText = "drop table if exists revise_link_id";
cmd.ExecuteNonQuery();
cmd.CommandText = "create table revise_link_id as "
+ "select link_id from " + alias + ".nav_link "
+ "where exists (select 1 from used_tab where link_id = " + alias + ".nav_link.link_id) "
+ "and mesh_id = \'" + alias_num + "\'";
cmd.ExecuteNonQuery();
//更新ttf部分数据
cmd.CommandText = "update " + alias + ".nav_link set verify_flag = 2, src = '1' where exists (select 1 from used_tab where link_id = " + alias + ".nav_link.link_id)";
cmd.ExecuteNonQuery();
//删除tab部分数据
cmd.CommandText = "delete from nav_link_out where exists (select 1 from revise_link_id where link_id = nav_link_out.link_id)";
cmd.ExecuteNonQuery();
//清空修改表
cmd.CommandText = "drop table if exists revise_link_id; ";
cmd.ExecuteNonQuery();
//分离库引用
cmd.CommandText = "detach " + alias;
cmd.ExecuteNonQuery();
}
//删除辅助表和索引
cmd.CommandText = "drop index if exists index_tab_hzq";
cmd.ExecuteNonQuery();
cmd.CommandText = "drop table if exists uesd_tab";
cmd.ExecuteNonQuery();
//db 转 tab
mycall("-f \"MapInfo File\" tab\\nav_link_out_new.tab tab\\test.db -a_srs WGS84 -lco GEOMETRY_NAME=geometry -lco GEOM_TYPE=geometry");
MessageBox.Show("处理完毕");
}
conn.Close();
}
}
//需求2
private void Button_Click_1(object sender, RoutedEventArgs e)
{
//MessageBox.Show("功能2");
mycall("-f \"SQLite\" yanzhen_tab\\test2.db -dsco SPATIALITE=NO -lco FORMAT=WKT yanzhen_tab\\test2.TAB -lco GEOMETRY_NAME=geom");
//return;
//tab转sqlite
//读入已经被转换的tab文件
var basefolder = AppDomain.CurrentDomain.BaseDirectory;
var testdbPath = System.IO.Path.Combine(basefolder, "yanzhen_tab", "test2.db");
using (SQLiteConnection conn = new SQLiteConnection("Data Source=" + testdbPath + ";Pooling=true;FailIfMissing=false"))
{
conn.Open();
using (SQLiteCommand cmd = conn.CreateCommand())
{
//创建临时表 used_tab
cmd.CommandText = "drop table if exists mlid_tab";
cmd.ExecuteNonQuery();
cmd.CommandText = "create table mlid_tab as select link_id,mesh_id from test2";
cmd.ExecuteNonQuery();
//为临时表创建索引
cmd.CommandText = "drop index if exists index_tab_hzq;create index index_tab_hzq on mlid_tab(link_id)";
cmd.ExecuteNonQuery();
//挑出对应的mesh_id
string[] e_mesh_id;
e_mesh_id = new string[30000];
int num_mesh = 0;
cmd.CommandText = "select distinct mesh_id from mlid_tab";
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
e_mesh_id[num_mesh] = reader.GetString(0);
num_mesh++;
}
}
//附加库
string path = System.AppDomain.CurrentDomain.BaseDirectory;
path = path + "yanzhen_ttfa\\";
DirectoryInfo theFolder = new DirectoryInfo(path);
FileInfo[] fileInfo = theFolder.GetFiles();
//处理每个ttfa文件
foreach (FileInfo NextFile in fileInfo)
{
string ttfa_path = path + NextFile.Name;
string ttfa_name = NextFile.Name;
string alias_num = ttfa_name.Substring(0, 6);
string alias = "ttfa" + alias_num;
bool e_flag = false;
for (int i = 0; i <= num_mesh; i++)
{
if (alias_num == e_mesh_id[i])
{
e_flag = true;
break;
}
else
{
continue;
}
}
if (e_flag == false)
{ continue; }
//增加库引用
cmd.CommandText = "attach \"" + ttfa_path + "\" as " + alias;
cmd.ExecuteNonQuery();
//进行匹配,修改alias ttfa,删除test tab
//待修改数据
cmd.CommandText = "drop table if exists revise_link_id";
cmd.ExecuteNonQuery();
cmd.CommandText = "create table revise_link_id as "
+ "select link_id from " + alias + ".nav_link "
+ "where exists (select 1 from mlid_tab where link_id = " + alias + ".nav_link.link_id) "
+ "and mesh_id = \'" + alias_num + "\'";
cmd.ExecuteNonQuery();
//更新ttf部分数据
cmd.CommandText = "update " + alias + ".nav_link set verify_flag = 0, src = '1,4' where exists (select 1 from mlid_tab where link_id = " + alias + ".nav_link.link_id)";
cmd.ExecuteNonQuery();
//删除tab部分数据
cmd.CommandText = "delete from test2 where exists (select 1 from revise_link_id where link_id = test2.link_id)";
cmd.ExecuteNonQuery();
//清空修改表
cmd.CommandText = "drop table if exists revise_link_id; ";
cmd.ExecuteNonQuery();
//分离库引用
cmd.CommandText = "detach " + alias;
cmd.ExecuteNonQuery();
}
//删除辅助表和索引
cmd.CommandText = "drop index if exists index_tab_hzq";
cmd.ExecuteNonQuery();
cmd.CommandText = "drop table if exists mlid_tab";
cmd.ExecuteNonQuery();
//db 转 tab
mycall("-f \"MapInfo File\" yanzhen_tab\\test2_new.tab yanzhen_tab\\test2.db -a_srs WGS84 -lco GEOMETRY_NAME=geometry -lco GEOM_TYPE=geometry");
MessageBox.Show("处理完毕");
}
conn.Close();
}
}
//ogr2ogr调用
private bool mycall(string ogr2ogr_cmd_str)
{
//tab转sqlite
System.Diagnostics.ProcessStartInfo p = null;
System.Diagnostics.Process Proc;
p = new ProcessStartInfo("gdal1100\\bin\\ogr2ogr.exe",ogr2ogr_cmd_str);// "-f \"MapInfo File\" tab\\nav_link_out_new.tab tab\\test.db -a_srs WGS84 -lco GEOMETRY_NAME=geometry -lco GEOM_TYPE=geometry");
p.WindowStyle = ProcessWindowStyle.Hidden;//在调用外部exe程序的时候,控制台窗口不弹出
Proc = System.Diagnostics.Process.Start(p);//调用外部程序
return true;
}
}
}