wangguanjie
/
LLFX
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
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.
3 Commits
1 Branch
0 Tags
73 MiB
Tag: Branch: Tree: main
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'main'
${ noResults }
LLFX/Helper/StatisticalReportClass.cs

409 lines
23 KiB
Raw Permalink Normal View History Unescape

初始化
1 month ago
using ExcelDataReader;
using KGIS.Framework.DBOperator;
using KGIS.Framework.Utils;
using KGIS.Framework.Utils.ExtensionMethod;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using WpfApp1.Model;
namespace WpfApp1.Helper
{
public class StatisticalReportClass
{
public bool ExportTDLYXZBGB(string saveCurrentPath, AnalysisExport analysisExport, string temppath)
{
IRDBHelper dbHelper = null;
string ncsjExcelPath = string.Empty;
FileInfo fileInfo = new FileInfo(analysisExport.JCSJExcelName);
string fileName = "土地利用现状分类面积汇总表";//analysisExport.JCSJExcelName.Replace("(", "").Replace(")", "").Replace(analysisExport.XZQDM, "").Replace(fileInfo.Extension, "");
#region 数据赋值
string tempPath = AppDomain.CurrentDomain.BaseDirectory + "Template\\土地利用现状变更表.xlsx";
File.Copy(tempPath, saveCurrentPath, true);
DataTable resultdataTable = new DataTable();
dbHelper = RDBFactory.CreateDbHelper("Data Source=" + $"{temppath}\\BGTJ.sqlite", DatabaseType.SQLite);
DataTable dt = dbHelper.ExecuteDatatable("TDLYXZBGB", @" select Cells_X,Cells_Y,BGMJ from
(
select bgq,bgh,SUM(BGMJ)BGMJ from (
select bgq,bgh,SUM(BGMJ)BGMJ from
(
select
case when bgq in('1001', '1002', '1003', '1007', '1008', '1009', '1109' )or substr(bgq, 1, 2)='20' THEN 'JSYD' ELSE bgq END bgq,
case when bgh in('1001', '1002', '1003', '1007', '1008', '1009', '1109')or substr(bgh, 1, 2)='20' THEN 'JSYD' ELSE bgh END bgh , BGMJ from (select case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ from (select substr(BGQDLBM, 1, 4) BGQDLBM,substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ, XZQTZLX from JCTJB_GQ )A )A
)A WHERE bgq<>bgh and (bgq='JSYD' OR bgh='JSYD' ) GROUP BY bgq,bgh
union ALL
select bgq,bgh,SUM(BGMJ)BGMJ from
(
select case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ from (select substr(BGQDLBM, 1, 4) BGQDLBM,substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ, XZQTZLX from JCTJB_GQ )A
)A WHERE bgq<>bgh GROUP BY bgq,bgh
)A GROUP BY bgq,bgh
)A left JOIN TDLYXZBGB B ON a.bgh = B.bgh and a.bgq = B.bgq where Cells_X NOTNULL and Cells_Y NOTNULL ", true);
if (dt != null && dt.Rows.Count > 0)
{
resultdataTable.Merge(dt);
}
#endregion
#region 上年末面积
DataTable table = dbHelper.ExecuteDatatable("tab", string.Format(@"select case WHEN column1='{0}' THEN'column1' ELSE 'column2' END ColumnName from {1} WHERE column1='{0}' or column2='{0}' ", analysisExport.XZQDM, fileName), true);
if (table == null || table.Rows.Count == 0)
{
return false;
}
string ColumnName = table.Rows[0][0].ToTrim();
if (ColumnName == "column1")
{
dt = dbHelper.ExecuteDatatable("SNMMJ", string.Format(@"
select '4' Cells_X, '2' Cells_Y, round(column11,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '3' Cells_Y, round(column12,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '4' Cells_Y, round(column13,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '5' Cells_Y, round(column14,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '6' Cells_Y, round(column15,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '7' Cells_Y, round(column16,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '8' Cells_Y, round(column17,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '9' Cells_Y, round(column18,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '10' Cells_Y, round(column19,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '11' Cells_Y, round(column20,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '12' Cells_Y, round(column21,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '13' Cells_Y, round(column22,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '14' Cells_Y, round(column23,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '15' Cells_Y, round(column24,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '16' Cells_Y, round(column25,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '17' Cells_Y, round(column26,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '18' Cells_Y, round(column27,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '19' Cells_Y, round(column28,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
SELECT '4' Cells_X, '20' Cells_Y, round(column3,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
SELECT '4' Cells_X, '21' Cells_Y, round(column4,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
SELECT '4' Cells_X, '22' Cells_Y, round(column5,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
SELECT '4' Cells_X, '23' Cells_Y, round(column6,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
SELECT '4' Cells_X, '24' Cells_Y, round(column7,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '25' Cells_Y, round(column8,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '26' Cells_Y, round(column9,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '27' Cells_Y, round(column10,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '28' Cells_Y, round(column43,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '29' Cells_Y, round(column44,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '30' Cells_Y, round(column45,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '31' Cells_Y, round(column46,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '32' Cells_Y, round(column47,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '33' Cells_Y, round(column48,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '34' Cells_Y, round(column49,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '36' Cells_Y, round(column29,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '37' Cells_Y, round(column30,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '38' Cells_Y, round(column31,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '39' Cells_Y, round(column32,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '40' Cells_Y, round(column33,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '41' Cells_Y, round(column34,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '42' Cells_Y, round(column35,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '43' Cells_Y, round(column36,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '44' Cells_Y, round(column37,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '45' Cells_Y, round(column38,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '46' Cells_Y, round(column39,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '47' Cells_Y, round(column40,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '48' Cells_Y, round(column41,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '49' Cells_Y, round(column42,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '50' Cells_Y, round(column50,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '51' Cells_Y, round(column51,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '52' Cells_Y, round(column52,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '53' Cells_Y, round(column53,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '54' Cells_Y, round(column54,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '55' Cells_Y, round(column55,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '56' Cells_Y, round(column56,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '57' Cells_Y, round(column57,4) BGMJ FROM {2} WHERE {1}='{0}' ", analysisExport.XZQDM, ColumnName, fileName), true);
}
else if (ColumnName == "column2")
{
dt = dbHelper.ExecuteDatatable("SNMMJ", string.Format(@"
select '4' Cells_X, '2' Cells_Y, round(column12,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '3' Cells_Y, round(column13,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '4' Cells_Y, round(column14,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '5' Cells_Y, round(column15,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '6' Cells_Y, round(column16,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '7' Cells_Y, round(column17,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '8' Cells_Y, round(column18,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '9' Cells_Y, round(column19,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '10' Cells_Y, round(column20,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '11' Cells_Y, round(column21,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '12' Cells_Y, round(column22,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '13' Cells_Y, round(column23,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '14' Cells_Y, round(column24,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '15' Cells_Y, round(column25,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '16' Cells_Y, round(column26,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '17' Cells_Y, round(column27,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '18' Cells_Y, round(column28,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '19' Cells_Y, round(column29,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
SELECT '4' Cells_X, '20' Cells_Y, round(column4,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
SELECT '4' Cells_X, '21' Cells_Y, round(column5,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
SELECT '4' Cells_X, '22' Cells_Y, round(column6,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
SELECT '4' Cells_X, '23' Cells_Y, round(column7,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
SELECT '4' Cells_X, '24' Cells_Y, round(column8,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '25' Cells_Y, round(column9,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '26' Cells_Y, round(column10,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '27' Cells_Y, round(column11,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '28' Cells_Y, round(column44,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '29' Cells_Y, round(column45,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '30' Cells_Y, round(column46,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '31' Cells_Y, round(column47,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '32' Cells_Y, round(column48,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '33' Cells_Y, round(column49,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '34' Cells_Y, round(column50,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '36' Cells_Y, round(column30,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '37' Cells_Y, round(column31,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '38' Cells_Y, round(column32,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '39' Cells_Y, round(column33,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '40' Cells_Y, round(column34,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '41' Cells_Y, round(column35,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '42' Cells_Y, round(column36,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '43' Cells_Y, round(column37,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '44' Cells_Y, round(column38,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '45' Cells_Y, round(column39,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '46' Cells_Y, round(column40,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '47' Cells_Y, round(column41,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '48' Cells_Y, round(column42,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '49' Cells_Y, round(column43,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '50' Cells_Y, round(column51,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '51' Cells_Y, round(column52,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '52' Cells_Y, round(column53,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '53' Cells_Y, round(column54,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '54' Cells_Y, round(column55,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '55' Cells_Y, round(column56,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '56' Cells_Y, round(column57,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL
select '4' Cells_X, '57' Cells_Y, round(column58,4) BGMJ FROM {2} WHERE {1}='{0}' ", analysisExport.XZQDM, ColumnName, fileName), true);
}
if (dt != null && dt.Rows.Count > 0)
{
resultdataTable.Merge(dt);
}
#endregion
#region 行政区调入
dt = dbHelper.ExecuteDatatable("XZQDR", string.Format(@" select '5' Cells_X,Cells_Y,BGMJ from
(
select case when XZQTZLX='1' then bgq ELSE bgh end dlbm, SUM(BGMJ) BGMJ from
(
select case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,XZQTZLX
from
(
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ, XZQTZLX from JCTJB_GQ
)A WHERE XZQTZLX in ('1','3')
)A GROUP BY dlbm
)a left join TDLYXZBGB B on a.DLBM= B.bgh where b.Cells_X='12' "), true);
if (dt != null && dt.Rows.Count > 0)
{
resultdataTable.Merge(dt);
}
#endregion
#region 行政区调出
dt = dbHelper.ExecuteDatatable("XZQDC", string.Format(@" select '6' Cells_X,Cells_Y,BGMJ from
(
select bgq dlbm, SUM(BGMJ) BGMJ from
(
select case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,XZQTZLX
from
(
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ, XZQTZLX from JCTJB_GQ
)A WHERE XZQTZLX in ('2','4')
)A GROUP BY dlbm
)a left join TDLYXZBGB B on a.DLBM= B.bgh where b.Cells_X='12' "), true);
if (dt != null && dt.Rows.Count > 0)
{
resultdataTable.Merge(dt);
}
CellHelper.SetFileToDisk(resultdataTable, saveCurrentPath, 0);
#endregion
CellHelper.Import(saveCurrentPath);
return true;
}
#region 导入表格数据
public void ExcelToDataTable(string temppath, string NCDataDir, string xzqdm)
{
DataTable dataTable = null;
IRDBHelper rdbHelper = null;
try
{
string dbPath = temppath + @"\BGTJ.sqlite";
rdbHelper = RDBFactory.CreateDbHelper("Data Source=" + dbPath, DatabaseType.SQLite);
FileInfo file = new FileInfo(NCDataDir);
var tableName = "土地利用现状分类面积汇总表";
using (var streamData = File.Open(file.FullName, FileMode.Open, FileAccess.Read))
{
using (var readerData = ExcelReaderFactory.CreateReader(streamData))
{
var result = readerData.AsDataSet();
dataTable = result.Tables[0];
Dictionary<string, string> dics = Get_Colunms(dataTable);
CreateTableSql(rdbHelper, tableName, dics);
if (dataTable != null && dataTable.Rows.Count > 0)
{
if (file.Name.StartsWith("土地利用现状分类面积汇总表"))
{
DataTable table = dataTable.Clone();
var dataRow = dataTable.Select($" column2='{xzqdm}' ");
if (dataRow != null && dataRow.Length > 0)
table.ImportRow(dataRow[0]);
dataTable = table;
}
else
{
dataTable = DtSelectTop(10, dataTable);
}
Insert_sql(dataTable, dics, rdbHelper, tableName);
}
}
}
}
catch (Exception ex)
{
LogAPI.Debug("读取Excel数据错误(成果输出)：" + ex.Message);
LogAPI.Debug("读取Excel数据错误(成果输出)：" + ex.StackTrace);
}
finally
{
if (dataTable != null)
{
dataTable.Clear();
dataTable = null;
}
}
}
public static DataTable DtSelectTop(int TopItem, DataTable oDT)
{
if (oDT.Rows.Count < TopItem) return oDT;
DataTable NewTable = oDT.Clone();
DataRow[] rows = oDT.Select("1=1");
for (int i = 0; i < TopItem; i++)
{
NewTable.ImportRow((DataRow)rows[i]);
}
return NewTable;
}
/// <summary>
/// 获取列
/// </summary>
/// <param name="dic"></param>
/// <returns></returns>
public static Dictionary<string, string> Get_Colunms(DataTable dic)
{
Dictionary<string, string> dics = new Dictionary<string, string>();
foreach (DataColumn item in dic.Columns)
{
dics.Add(item.ColumnName, "Varchar");
}
return dics;
}
/// <summary>
/// 创建表
/// </summary>
/// <param name="rdbHelper"></param>
/// <param name="tableName"></param>
/// <param name="table"></param>
protected void CreateTableSql(IRDBHelper rdbHelper, string tableName, Dictionary<string, string> table)
{
string result = string.Empty;
try
{
string temp1 = "CREATE TABLE if not exists " + tableName + "(";
foreach (var item in table)
{
temp1 = temp1 + item.Key.Trim().ToLower() + " " + item.Value + ",";
}
result = temp1.TrimEnd(',') + ");";
rdbHelper.ExecuteNonQueryWithException($" DROP TABLE if exists {tableName}");
rdbHelper.ExecuteNonQueryWithException(result);
}
catch (Exception ex)
{
throw ex;
}
}
public void Insert_sql(DataTable dic, Dictionary<string, string> dics, IRDBHelper SqlHelper, string Table)
{
#region 获取所有字段
StringBuilder columns = new StringBuilder();
foreach (var item in dics)
{
columns.Append("," + item.Key);
}
#endregion
#region 数据入库
int CycleIndex = 0;
StringBuilder insertsql = new StringBuilder();
StringBuilder strsql = new StringBuilder();
StringBuilder stringBuilder = new StringBuilder();
var count = dic.Rows.Count;
foreach (DataRow item0 in dic.Rows)
{
CycleIndex++;
StringBuilder data = new StringBuilder();//insert语句插入字段
foreach (var item1 in dics)
{
if (item0[item1.Key].ToString().Trim() == "")
{
data.Append(",null");
}
else
{
data.Append((",'" + item0[item1.Key].ToString().Trim().Replace("'", "''").Replace("\n", "").Replace(" ", "").Replace("\t", "").Replace("\r", "") + "'"));
}
}
insertsql.Append(string.Format("insert into " + Table + "({0})values({1});", columns.ToString().Substring(1), data.ToString().Substring(1)));
#region 提交数据
if (count < 10000)
{
if (CycleIndex < count)
{
stringBuilder.Append(insertsql.ToString());
insertsql.Clear();
}
else if (CycleIndex == count)
{
stringBuilder.Append(insertsql.ToString());
var num = SqlHelper.ExecuteNonQuery(stringBuilder.ToString(), CommandType.Text);
if (num < 0 && stringBuilder.ToString() != "")
return;
}
}
else
{
strsql.Append(insertsql.ToString());
insertsql.Clear();
}
if (CycleIndex == 10000)
{
try
{
var num = SqlHelper.ExecuteNonQuery(strsql.ToString(), CommandType.Text);
if (num < 0 && strsql.ToString() != "")
return;
insertsql.Clear();
strsql.Clear();
count = count - CycleIndex;
CycleIndex = 0;
}
catch (Exception exc)
{
throw new Exception(exc.Message);
}
finally
{
GC.Collect();
}
}
#endregion
}
#endregion
}
#endregion
}
}