wangguanjie
/
NDBGJK5.0
4
1
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
年度变更建库软件5.0版本
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1 Commit
4 Branches
0 Tags
121 MiB
 
 
Tag: Branch: Tree: Dev
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'Dev'
${ noResults }
NDBGJK5.0/Kingo.Plugins/内业变更/Kingo.Plugin.BGResultManager/Helper/BaseTableHelper.cs

575 lines
26 KiB
Raw Permalink Blame History

using KGIS.Framework.DBOperator;
using KGIS.Framework.Maps;
using KGIS.Framework.Utils;
using KGIS.Framework.Utils.Helper;
using Kingo.PluginServiceInterface;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
namespace Kingo.Plugin.BGResultManager.Helper
{
public class BaseTableHelper
{
/// <summary>
/// 获取基础统计表---速度较慢，所以暂时不用2020-8-25 沈超
/// </summary>
/// <returns></returns>
public static void GetBaseSumTable()
{
IRDBHelper rdbHelper = null;
try
{
// 链接数据库
string dbPath = $"{(MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).ProjDir}\\BGTJ.sqlite";
rdbHelper = RDBFactory.CreateDbHelper("Data Source=" + dbPath, DatabaseType.SQLite);
if (rdbHelper == null)
{
MessageHelper.ShowError("数据库链接错误!");
return;
}
// 得到 -准- 基础统计表(平方米)(此时的表空值和空字符串没有合并，而且相应的权属没有映射)
string sql = "SELECT " +
"BGQDLBM, BGQQSXZ, SUBSTR(BGQZLDWDM,1,12) AS BGQZLDWDM, BGQGDLX, BGQGDPDJB, BGQTBXHDM, BGQZZSXDM, BGQCZCSXM, BGQMSSM, " +
"BGHDLBM, BGHQSXZ, SUBSTR(BGHZLDWDM,1,12) AS BGHZLDWDM, BGHGDLX, BGHGDPDJB, BGHTBXHDM, BGHZZSXDM, BGHCZCSXM, BGHMSSM, " +
"SUM(BGMJ) AS BGMJ FROM BGYLB " +
"GROUP BY " +
"BGQDLBM, BGQQSXZ, SUBSTR(BGQZLDWDM,1,12), BGQGDLX, BGQGDPDJB, BGQTBXHDM, BGQZZSXDM, BGQCZCSXM, BGQMSSM, " +
"BGHDLBM, BGHQSXZ, SUBSTR(BGHZLDWDM,1,12), BGHGDLX, BGHGDPDJB, BGHTBXHDM, BGHZZSXDM, BGHCZCSXM, BGHMSSM";
DataTable dt = rdbHelper.ExecuteDatatable2("BASETABLE", sql, true);
string dropTableSql = "DROP TABLE JCTJB_Temp";
rdbHelper.ExecuteSQL(dropTableSql);
string createTableSql = "Create TABLE JCTJB_Temp ( " +
"BGQZLDWDM text(100)," +
"BGQQSXZ text(100)," +
"BGQGDLX text(100)," +
"BGQGDPDJB text(100)," +
"BGQCZCSXM text(100)," +
"BGQTBXHDM text(100)," +
"BGQZZSXDM text(100)," +
"BGQMSSM text(100)," +
"BGQDLBM text(100)," +
"BGHZLDWDM text(100)," +
"BGHQSXZ text(100)," +
"BGHGDLX text(100)," +
"BGHGDPDJB text(100)," +
"BGHCZCSXM text(100)," +
"BGHTBXHDM text(100)," +
"BGHZZSXDM text(100)," +
"BGHMSSM text(100)," +
"BGHDLBM text(100)," +
"BGMJ double(16,10) " +
") ";
rdbHelper.ExecuteSQL(createTableSql);
Dictionary<string, string> dic = new Dictionary<string, string>();
foreach (DataColumn item in dt.Columns)
{
dic.Add(item.ColumnName, "空");
}
string insertSql = string.Empty;
foreach (DataRow row in dt.Rows)
{
for (int i = 0; i < dic.Count; i++)
{
KeyValuePair<string, string> pair = dic.ElementAt(i);
dic[pair.Key] = "空";
if (row[pair.Key] == null || string.IsNullOrWhiteSpace(row[pair.Key].ToString())) continue;
if (pair.Key == "BGQQSXZ" || pair.Key == "BGHQSXZ")
{
dic[pair.Key] = row[pair.Key].ToString() == "10" || row[pair.Key].ToString() == "20" ? "国有" : "集体";
continue;
}
dic[pair.Key] = row[pair.Key].ToString();
}
insertSql = string.Format("INSERT INTO JCTJB_Temp ( " +
"BGQZLDWDM , " +
"BGQQSXZ , " +
"BGQGDLX , " +
"BGQGDPDJB , " +
"BGQCZCSXM , " +
"BGQTBXHDM , " +
"BGQZZSXDM , " +
"BGQMSSM , " +
"BGQDLBM , " +
"BGHZLDWDM , " +
"BGHQSXZ , " +
"BGHGDLX , " +
"BGHGDPDJB , " +
"BGHCZCSXM , " +
"BGHTBXHDM , " +
"BGHZZSXDM , " +
"BGHMSSM , " +
"BGHDLBM , " +
"BGMJ ) " +
"VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}' , '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}' , '{18}')",
dic["BGQZLDWDM"],
dic["BGQQSXZ"],
dic["BGQGDLX"],
dic["BGQGDPDJB"],
dic["BGQCZCSXM"],
dic["BGQTBXHDM"],
dic["BGQZZSXDM"],
dic["BGQMSSM"],
dic["BGQDLBM"],
dic["BGHZLDWDM"],
dic["BGHQSXZ"],
dic["BGHGDLX"],
dic["BGHGDPDJB"],
dic["BGHCZCSXM"],
dic["BGHTBXHDM"],
dic["BGHZZSXDM"],
dic["BGHMSSM"],
dic["BGHDLBM"],
dic["BGMJ"]);
int it = rdbHelper.ExecuteSQL(insertSql);
}
sql = "SELECT " +
"BGQDLBM, BGQQSXZ, BGQZLDWDM, BGQGDLX, BGQGDPDJB, BGQTBXHDM, BGQZZSXDM, BGQCZCSXM, BGQMSSM, " +
"BGHDLBM, BGHQSXZ, BGHZLDWDM, BGHGDLX, BGHGDPDJB, BGHTBXHDM, BGHZZSXDM, BGHCZCSXM, BGHMSSM, " +
"SUM(BGMJ) AS BGMJ FROM JCTJB_Temp " +
"GROUP BY " +
"BGQDLBM, BGQQSXZ, BGQZLDWDM, BGQGDLX, BGQGDPDJB, BGQTBXHDM, BGQZZSXDM, BGQCZCSXM, BGQMSSM, " +
"BGHDLBM, BGHQSXZ, BGHZLDWDM, BGHGDLX, BGHGDPDJB, BGHTBXHDM, BGHZZSXDM, BGHCZCSXM, BGHMSSM";
dt = rdbHelper.ExecuteDatatable2("BASETABLE", sql, true);
// 公顷表制作
dropTableSql = "DROP TABLE JCTJB_GQ";
rdbHelper.ExecuteSQL(dropTableSql);
createTableSql = "Create TABLE JCTJB_GQ ( " +
"BGQZLDWDM text(100)," +
"BGQQSXZ text(100)," +
"BGQGDLX text(100)," +
"BGQGDPDJB text(100)," +
"BGQCZCSXM text(100)," +
"BGQTBXHDM text(100)," +
"BGQZZSXDM text(100)," +
"BGQMSSM text(100)," +
"BGQDLBM text(100)," +
"BGHZLDWDM text(100)," +
"BGHQSXZ text(100)," +
"BGHGDLX text(100)," +
"BGHGDPDJB text(100)," +
"BGHCZCSXM text(100)," +
"BGHTBXHDM text(100)," +
"BGHZZSXDM text(100)," +
"BGHMSSM text(100)," +
"BGHDLBM text(100)," +
"BGMJ text(100) " +
") ";
rdbHelper.ExecuteSQL(createTableSql);
foreach (DataRow row in dt.Rows)
{
for (int i = 0; i < dic.Count; i++)
{
KeyValuePair<string, string> pair = dic.ElementAt(i);
dic[pair.Key] = "空";
if (row[pair.Key] == null || string.IsNullOrWhiteSpace(row[pair.Key].ToString())) continue;
if (pair.Key == "BGMJ")
{
dic[pair.Key] = Math.Round(Convert.ToDecimal(row[pair.Key].ToString()) * Convert.ToDecimal(0.0001), 2, MidpointRounding.AwayFromZero).ToString();
continue;
}
dic[pair.Key] = row[pair.Key].ToString();
}
insertSql = string.Format("INSERT INTO JCTJB_GQ ( " +
"BGQZLDWDM , " +
"BGQQSXZ , " +
"BGQGDLX , " +
"BGQGDPDJB , " +
"BGQCZCSXM , " +
"BGQTBXHDM , " +
"BGQZZSXDM , " +
"BGQMSSM , " +
"BGQDLBM , " +
"BGHZLDWDM , " +
"BGHQSXZ , " +
"BGHGDLX , " +
"BGHGDPDJB , " +
"BGHCZCSXM , " +
"BGHTBXHDM , " +
"BGHZZSXDM , " +
"BGHMSSM , " +
"BGHDLBM , " +
"BGMJ ) " +
"VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}' , '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}' , '{18}')",
dic["BGQZLDWDM"],
dic["BGQQSXZ"],
dic["BGQGDLX"],
dic["BGQGDPDJB"],
dic["BGQCZCSXM"],
dic["BGQTBXHDM"],
dic["BGQZZSXDM"],
dic["BGQMSSM"],
dic["BGQDLBM"],
dic["BGHZLDWDM"],
dic["BGHQSXZ"],
dic["BGHGDLX"],
dic["BGHGDPDJB"],
dic["BGHCZCSXM"],
dic["BGHTBXHDM"],
dic["BGHZZSXDM"],
dic["BGHMSSM"],
dic["BGHDLBM"],
dic["BGMJ"]);
int it = rdbHelper.ExecuteSQL(insertSql);
}
//海岛汇总
rdbHelper.ExecuteSQL(" DROP TABLE HDTDLYXZBHPHTJB_TEMP ");
rdbHelper.ExecuteSQL(@" CREATE TABLE HDTDLYXZBHPHTJB_TEMP as select substr(BGQDLBM,1,4) as BGQDLBM,substr(BGHDLBM,1,4) as BGHDLBM,Sum(BGMJ) as MJ from JCTJB_GQ where substr(BGQDLBM,1,4)<>substr(BGHDLBM,1,4) and BGQMSSM='01' and BGHMSSM='01' group by substr(BGQDLBM,1,4),substr(BGHDLBM,1,4) ");
//土地利用现状一级分类面积按权属性质汇总表
rdbHelper.ExecuteSQL(" DROP TABLE TDLYXZBHPHTJB_TEMP ");
rdbHelper.ExecuteSQL(@" CREATE TABLE TDLYXZBHPHTJB_TEMP as select substr(BGQDLBM,1,4) as BGQDLBM,substr(BGHDLBM,1,4) as BGHDLBM,Sum(BGMJ) as MJ from JCTJB_GQ where substr(BGQDLBM,1,4)<>substr(BGHDLBM,1,4) group by substr(BGQDLBM,1,4),substr(BGHDLBM,1,4) ");
return;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (rdbHelper != null && rdbHelper.State != System.Data.ConnectionState.Closed)
rdbHelper.DisConnect();
}
}
/// <summary>
/// 获取基础统计表
/// </summary>
/// <returns></returns>
public static void GetTheBaseSumTable()
{
IRDBHelper rdbHelper = null;
try
{
// 链接数据库
string dbPath = $"{(MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).ProjDir}\\BGTJ.sqlite";
rdbHelper = RDBFactory.CreateDbHelper("Data Source=" + dbPath, DatabaseType.SQLite);
if (rdbHelper == null)
{
MessageHelper.ShowError("数据库链接错误!");
return;
}
// 得到 -准- 基础统计表(平方米)(此时的表空值和空字符串没有合并，而且相应的权属没有映射)
//string sql = "SELECT " +
// "BGQDLBM, BGQQSXZ, SUBSTR(BGQZLDWDM,1,12) AS BGQZLDWDM, BGQGDLX, BGQGDPDJB, BGQTBXHDM, BGQZZSXDM, BGQCZCSXM, BGQMSSM, " +
// "BGHDLBM, BGHQSXZ, SUBSTR(BGHZLDWDM,1,12) AS BGHZLDWDM, BGHGDLX, BGHGDPDJB, BGHTBXHDM, BGHZZSXDM, BGHCZCSXM, BGHMSSM, " +
// "SUM(BGMJ) AS BGMJ,XZQTZLX FROM BGYLB " +
// "GROUP BY " +
// "BGQDLBM, BGQQSXZ, SUBSTR(BGQZLDWDM,1,12), BGQGDLX, BGQGDPDJB, BGQTBXHDM, BGQZZSXDM, BGQCZCSXM, BGQMSSM, " +
// "BGHDLBM, BGHQSXZ, SUBSTR(BGHZLDWDM,1,12), BGHGDLX, BGHGDPDJB, BGHTBXHDM, BGHZZSXDM, BGHCZCSXM, BGHMSSM,XZQTZLX";
string sql = "SELECT BGQDLBM, BGQQSXZ,BGQZLDWDM,BGQGDLX,BGQGDPDJB,BGQTBXHDM, BGQZZSXDM,BGQCZCSXM,BGQMSSM, BGHDLBM,BGHQSXZ,BGHZLDWDM,BGHGDLX,BGHGDPDJB,BGHTBXHDM,BGHZZSXDM,BGHCZCSXM,BGHMSSM, XZQTZLX,SUM(BGMJ)BGMJ FROM (SELECT BGQDLBM,case when BGQQSXZ = '10' or BGQQSXZ = '20' or BGQQSXZ = '21' then '国有' ELSE '集体' end BGQQSXZ,case when BGHQSXZ = '10' or BGHQSXZ = '20' or BGHQSXZ = '21' then '国有' ELSE '集体' end BGHQSXZ,SUBSTR(BGQZLDWDM, 1, 12) BGQZLDWDM,BGQGDLX,BGQGDPDJB,BGQTBXHDM,BGQZZSXDM, BGQCZCSXM,BGQMSSM,BGHDLBM,SUBSTR(BGHZLDWDM, 1, 12) BGHZLDWDM,BGHGDLX, BGHGDPDJB,BGHTBXHDM, BGHZZSXDM,BGHCZCSXM,TRIM(IFNULL(BGHMSSM, '00') ) BGHMSSM,XZQTZLX,BGMJ FROM BGYLB ) GROUP BY BGQDLBM,BGQQSXZ,BGQZLDWDM,BGQGDLX,BGQGDPDJB,BGQTBXHDM,BGQZZSXDM,BGQCZCSXM, BGQMSSM,BGHDLBM,BGHQSXZ,BGHZLDWDM,BGHGDLX,BGHGDPDJB,BGHTBXHDM,BGHZZSXDM,BGHCZCSXM,BGHMSSM,XZQTZLX";
DataTable dt = rdbHelper.ExecuteDatatable2("BASETABLE", sql, true);
string dropTableSql = "DROP TABLE JCTJB_Temp";
rdbHelper.ExecuteSQL(dropTableSql);
string createTableSql = "Create TABLE JCTJB_Temp ( " +
"BGQZLDWDM text(100)," +
"BGQQSXZ text(100)," +
"BGQGDLX text(100)," +
"BGQGDPDJB text(100)," +
"BGQCZCSXM text(100)," +
"BGQTBXHDM text(100)," +
"BGQZZSXDM text(100)," +
"BGQMSSM text(100)," +
"BGQDLBM text(100)," +
"BGHZLDWDM text(100)," +
"BGHQSXZ text(100)," +
"BGHGDLX text(100)," +
"BGHGDPDJB text(100)," +
"BGHCZCSXM text(100)," +
"BGHTBXHDM text(100)," +
"BGHZZSXDM text(100)," +
"BGHMSSM text(100)," +
"BGHDLBM text(100)," +
"BGMJ double(16,10), " +
"XZQTZLX text(100)" +
") ";
rdbHelper.ExecuteSQL(createTableSql);
Dictionary<string, string> dic = new Dictionary<string, string>();
foreach (DataColumn item in dt.Columns)
{
dic.Add(item.ColumnName, "空");
}
string insertSql = string.Empty;
List<string> lstSql_1 = new List<string>();
lstSql_1.Clear();
foreach (DataRow row in dt.Rows)
{
for (int i = 0; i < dic.Count; i++)
{
KeyValuePair<string, string> pair = dic.ElementAt(i);
dic[pair.Key] = "空";
if (row[pair.Key] == null || string.IsNullOrWhiteSpace(row[pair.Key].ToString())) continue;
//if (pair.Key == "BGQQSXZ" || pair.Key == "BGHQSXZ")
//{
// dic[pair.Key] = row[pair.Key].ToString() == "10" || row[pair.Key].ToString() == "20" ? "国有" : "集体";
// continue;
//}
dic[pair.Key] = row[pair.Key].ToString();
}
insertSql = string.Format("INSERT INTO JCTJB_Temp ( " +
"BGQZLDWDM , " +
"BGQQSXZ , " +
"BGQGDLX , " +
"BGQGDPDJB , " +
"BGQCZCSXM , " +
"BGQTBXHDM , " +
"BGQZZSXDM , " +
"BGQMSSM , " +
"BGQDLBM , " +
"BGHZLDWDM , " +
"BGHQSXZ , " +
"BGHGDLX , " +
"BGHGDPDJB , " +
"BGHCZCSXM , " +
"BGHTBXHDM , " +
"BGHZZSXDM , " +
"BGHMSSM , " +
"BGHDLBM , " +
"BGMJ,XZQTZLX ) " +
"VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}' , '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}' , '{18}','{19}')",
dic["BGQZLDWDM"],
dic["BGQQSXZ"],
dic["BGQGDLX"],
dic["BGQGDPDJB"],
dic["BGQCZCSXM"],
dic["BGQTBXHDM"],
dic["BGQZZSXDM"],
dic["BGQMSSM"],
dic["BGQDLBM"],
dic["BGHZLDWDM"],
dic["BGHQSXZ"],
dic["BGHGDLX"],
dic["BGHGDPDJB"],
dic["BGHCZCSXM"],
dic["BGHTBXHDM"],
dic["BGHZZSXDM"],
dic["BGHMSSM"],
dic["BGHDLBM"],
dic["BGMJ"],
dic["XZQTZLX"]
);
//int it = rdbHelper.ExecuteSQL(insertSql);
lstSql_1.Add(insertSql);
}
//ExcuteInsertSqlList(lstSql_1, rdbHelper);
InsertSQLiteData(dbPath, lstSql_1);
lstSql_1.Clear();
sql = "SELECT " +
"BGQDLBM, BGQQSXZ, BGQZLDWDM, BGQGDLX, BGQGDPDJB, BGQTBXHDM, BGQZZSXDM, BGQCZCSXM, BGQMSSM, " +
"BGHDLBM, BGHQSXZ, BGHZLDWDM, BGHGDLX, BGHGDPDJB, BGHTBXHDM, BGHZZSXDM, BGHCZCSXM, BGHMSSM, " +
"round(sum(ifnull(BGMJ/10000 + 0.0000001,0)),2) AS BGMJ,XZQTZLX FROM JCTJB_Temp " +
"GROUP BY " +
"BGQDLBM, BGQQSXZ, BGQZLDWDM, BGQGDLX, BGQGDPDJB, BGQTBXHDM, BGQZZSXDM, BGQCZCSXM, BGQMSSM, " +
"BGHDLBM, BGHQSXZ, BGHZLDWDM, BGHGDLX, BGHGDPDJB, BGHTBXHDM, BGHZZSXDM, BGHCZCSXM, BGHMSSM,XZQTZLX";
dt = rdbHelper.ExecuteDatatable2("BASETABLE", sql, true);
// 公顷表制作
dropTableSql = "DROP TABLE JCTJB_GQ";
rdbHelper.ExecuteSQL(dropTableSql);
createTableSql = "Create TABLE JCTJB_GQ ( " +
"ID INTEGER PRIMARY KEY AUTOINCREMENT," +
"BGQZLDWDM text(100)," +
"BGQQSXZ text(100)," +
"BGQGDLX text(100)," +
"BGQGDPDJB text(100)," +
"BGQCZCSXM text(100)," +
"BGQTBXHDM text(100)," +
"BGQZZSXDM text(100)," +
"BGQMSSM text(100)," +
"BGQDLBM text(100)," +
"BGHZLDWDM text(100)," +
"BGHQSXZ text(100)," +
"BGHGDLX text(100)," +
"BGHGDPDJB text(100)," +
"BGHCZCSXM text(100)," +
"BGHTBXHDM text(100)," +
"BGHZZSXDM text(100)," +
"BGHMSSM text(100)," +
"BGHDLBM text(100)," +
"BGMJ double(16,10), " +
"XZQTZLX text(100) " +
") ";
rdbHelper.ExecuteSQL(createTableSql);
List<string> lstSql_2 = new List<string>();
lstSql_2.Clear();
foreach (DataRow row in dt.Rows)
{
for (int i = 0; i < dic.Count; i++)
{
KeyValuePair<string, string> pair = dic.ElementAt(i);
dic[pair.Key] = "空";
if (row[pair.Key] == null || string.IsNullOrWhiteSpace(row[pair.Key].ToString())) continue;
//if (pair.Key == "BGMJ")
//{
// dic[pair.Key] = Math.Round(Convert.ToDecimal(row[pair.Key].ToString()) * Convert.ToDecimal(0.0001), 2, MidpointRounding.AwayFromZero).ToString();
// continue;
//}
dic[pair.Key] = row[pair.Key].ToString();
}
insertSql = string.Format("INSERT INTO JCTJB_GQ ( " +
"BGQZLDWDM , " +
"BGQQSXZ , " +
"BGQGDLX , " +
"BGQGDPDJB , " +
"BGQCZCSXM , " +
"BGQTBXHDM , " +
"BGQZZSXDM , " +
"BGQMSSM , " +
"BGQDLBM , " +
"BGHZLDWDM , " +
"BGHQSXZ , " +
"BGHGDLX , " +
"BGHGDPDJB , " +
"BGHCZCSXM , " +
"BGHTBXHDM , " +
"BGHZZSXDM , " +
"BGHMSSM , " +
"BGHDLBM , " +
"BGMJ,XZQTZLX ) " +
"VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}' , '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}' , '{18}','{19}')",
dic["BGQZLDWDM"],
dic["BGQQSXZ"],
dic["BGQGDLX"],
dic["BGQGDPDJB"],
dic["BGQCZCSXM"],
dic["BGQTBXHDM"],
dic["BGQZZSXDM"],
dic["BGQMSSM"],
dic["BGQDLBM"],
dic["BGHZLDWDM"],
dic["BGHQSXZ"],
dic["BGHGDLX"],
dic["BGHGDPDJB"],
dic["BGHCZCSXM"],
dic["BGHTBXHDM"],
dic["BGHZZSXDM"],
dic["BGHMSSM"],
dic["BGHDLBM"],
dic["BGMJ"],
dic["XZQTZLX"]
);
//int it = rdbHelper.ExecuteSQL(insertSql);
lstSql_2.Add(insertSql);
}
InsertSQLiteData(dbPath, lstSql_2);
//string sql_gq = "update JCTJB_GQ set bgmj=round(ifnull(bgmj/10000 + 0.0000001,0),2)";
//rdbHelper.ExecuteSQL(sql_gq);
//InsertSQLiteData(dbPath, lstSql_2);
lstSql_2.Clear();
//海岛汇总
rdbHelper.ExecuteSQL(" DROP TABLE HDTDLYXZBHPHTJB_TEMP ");
rdbHelper.ExecuteSQL(@" CREATE TABLE HDTDLYXZBHPHTJB_TEMP as select substr(BGQDLBM,1,4) as BGQDLBM,substr(BGHDLBM,1,4) as BGHDLBM,Sum(BGMJ) as MJ from JCTJB_GQ where substr(BGQDLBM,1,4)<>substr(BGHDLBM,1,4) and BGQMSSM='01' and BGHMSSM='01' group by substr(BGQDLBM,1,4),substr(BGHDLBM,1,4) ");
//土地利用现状一级分类面积按权属性质汇总表
rdbHelper.ExecuteSQL(" DROP TABLE TDLYXZBHPHTJB_TEMP ");
rdbHelper.ExecuteSQL(@" CREATE TABLE TDLYXZBHPHTJB_TEMP as select substr(BGQDLBM,1,4) as BGQDLBM,substr(BGHDLBM,1,4) as BGHDLBM,Sum(BGMJ) as MJ from JCTJB_GQ where substr(BGQDLBM,1,4)<>substr(BGHDLBM,1,4) group by substr(BGQDLBM,1,4),substr(BGHDLBM,1,4) ");
return;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (rdbHelper != null && rdbHelper.State != System.Data.ConnectionState.Closed)
rdbHelper.DisConnect();
}
}
public static void ExcuteInsertSqlList(List<string> lstSql, IRDBHelper rdbHelper)
{
int i = 0;
try
{
foreach (string sql in lstSql)
{
try
{
i = rdbHelper.ExecuteNonQueryWithException(sql, System.Data.CommandType.Text);
}
catch (Exception sqlEx)
{
LogAPI.Debug(sqlEx);
LogAPI.Debug("出错语句" + sql);
}
}
}
catch (Exception ex)
{
LogAPI.Debug("批量执行SQL语句时失败，异常原因: " + ex + " ; ");
//if (rdbHelper != null)
// rdbHelper.DisConnect();
}
finally
{
//LogAPI.Debug(lstSql.Count() + " 条数据写入完成 ;\r\n ");
//if (rdbHelper != null)
// rdbHelper.DisConnect();
}
}
/// <summary>
/// 批量插入SQLite数据
/// </summary>
/// <param name="dbPath"></param>
/// <param name="sqlList"></param>
public static void InsertSQLiteData(string dbPath, List<string> sqlList)
{
try
{
//using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=" + dbPath))
//{
// conn.Open();
// using (System.Data.SQLite.SQLiteTransaction pTrans = conn.BeginTransaction())
// {
// using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
// {
// for (int i = 0; i < sqlList.Count(); i++)
// {
// cmd.CommandText = sqlList[i];
// cmd.ExecuteNonQuery();
// }
// pTrans.Commit();
// }
// }
// conn.Close();
//}
}
catch (Exception ex)
{
LogAPI.Debug("批量插入SQLite数据(地址:" + dbPath + ") 执行失败,异常原因: " + ex + " ; ");
return;
}
}
}
}