System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Reflection;
using TrustDevKit.Accident_WebService;
using TrustDevKit.localhost;
using TrustDevKit.Misc;
using System.Windows;
using System.Data.SQLite;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Configuration;
using System;
namespace TrustDevKit
{
namespace Accident
{
public partial class DataService
{
/// <summary>
/// 更新本地字典、文书模板库
/// </summary>
/// <returns>返回更新表数,失败出错返回-1</returns>
public static int Upate_Code_TemplateDB()
{
int gxs = -
1;
try
{
using (SQLiteConnection connection =
new SQLiteConnection(SQLiteConnectionString_Code_TemplateDB))
{
connection.Open();
SQLiteCommand command =
new SQLiteCommand(connection);
#region 检查更新记录
string sql =
"create table if not exists Update_Info(gxxm varchar2(64),gxsj date);";
command.CommandText =
sql;
command.ExecuteNonQuery();
sql =
"select gxsj from Update_Info where gxxm=‘Code_TemplateDB‘;";
command.CommandText =
sql;
var result =
command.ExecuteScalar();
DateTime last_update =
new DateTime(); ;
if (result ==
null)
{
sql =
"insert into update_info (gxxm,gxsj) values (‘Code_TemplateDB‘,datetime(‘2001-01-01‘));";
command.CommandText =
sql;
command.ExecuteNonQuery();
}
else
{
last_update =
(DateTime)result;
}
gxs++
;
//查找最近更新的表，以服务器更新时间与config文件记录做对比
DataTable table_updateinfo = taps_WebService.Get_UpdateInfo();
//服务器更新信息表
bool frm_codetype_updated = (DateTime)table_updateinfo.Select(
"DM=‘Frm_Code‘")[
0][
"GXSJ"] >
last_update;
bool frm_code_updated = (DateTime)table_updateinfo.Select(
"DM=‘Taps_CodeType‘")[
0][
"GXSJ"] >
last_update;
bool taps_codetype_updated = (DateTime)table_updateinfo.Select(
"DM=‘Taps_Code‘")[
0][
"GXSJ"] >
last_update;
bool taps_code_updated = (DateTime)table_updateinfo.Select(
"DM=‘Taps_Code‘")[
0][
"GXSJ"] >
last_update;
bool doctemplatetype_updated = (DateTime)table_updateinfo.Select(
"DM=‘Taps_DocTemplateType‘")[
0][
"GXSJ"] >
last_update;
bool doctemplate_updated = (DateTime)table_updateinfo.Select(
"DM=‘Taps_DocTemplate‘")[
0][
"GXSJ"] >
last_update;
#endregion
//开始事务
using (DbTransaction transaction =
connection.BeginTransaction())
{
try
{
#region 更新Frm_CodeType表
if (frm_code_updated)
{
string drop_From_CodeType =
"drop table if exists Frm_codeType;";
string create_Frm_CodeType =
" CREATE TABLE if not exists FRM_CODETYPE( xtlb VARCHAR2(2) not null, dmlb VARCHAR2(6) not null, lbsm VARCHAR2(256) not null, dmcd NUMBER(2), lbsx CHAR(1),"
+
" dmsx NUMBER(4), bz VARCHAR2(256), jznc CHAR(1), dmlx CHAR(1));";
string create_Frm_CodeType_Idx =
"CREATE UNIQUE INDEX if not exists Frm_CodeType_Idx on Frm_CodeType (XTLB,DMLB);";
command.CommandText = drop_From_CodeType + create_Frm_CodeType +
create_Frm_CodeType_Idx;
command.ExecuteNonQuery();
DataTable frmcodetype = DataService.taps_WebService.Get_CodeTable_by_name(
"Frm_CodeType");
foreach (
var item
in frmcodetype.Rows)
{
DataRow dr = item
as DataRow;
sql =
"INSERT INTO FRM_CODETYPE(xtlb,dmlb,lbsm,dmcd,lbsx,dmsx,bz,jznc,dmlx)values(@xtlb,@dmlb,@lbsm,@dmcd,@lbsx,@dmsx,@bz,@jznc,@dmlx)";
SQLiteParameter[] parameters =
new SQLiteParameter[]{
new SQLiteParameter(
"@xtlb",dr[
"XTLB"]),
new SQLiteParameter(
"@dmlb",dr[
"DMLB"]),
new SQLiteParameter(
"@lbsm",dr[
"LBSM"]),
new SQLiteParameter(
"@dmcd",dr[
"DMCD"]),
new SQLiteParameter(
"@lbsx",dr[
"LBSX"]),
new SQLiteParameter(
"@dmsx",dr[
"DMSX"]) ,
new SQLiteParameter(
"@bz",dr[
"BZ"]),
new SQLiteParameter(
"@jznc",dr[
"JZNC"]),
new SQLiteParameter(
"@dmlx",dr[
"DMLX"])
};
command.CommandText =
sql;
command.Parameters.AddRange(parameters);
command.ExecuteNonQuery();
}
gxs++
;
}
#endregion
#region 更新Frm_Code
if (frm_codetype_updated)
{
string drop_From_Code =
"drop table if exists Frm_code;";
string create_Frm_Code =
" CREATE TABLE if not exists FRM_CODE( xtlb CHAR(2) not null, dmlb VARCHAR2(6) not null, dmz VARCHAR2(30) not null, "
+
"dmsm1 VARCHAR2(256) not null, dmsm2 VARCHAR2(256), dmsm3 VARCHAR2(256), dmsm4 VARCHAR2(256), dmsx CHAR(1) default 0, sxh NUMBER(4), ywdx VARCHAR2(128), zt CHAR(1) default 1);";
string create_Frm_Code_Idx =
"CREATE UNIQUE INDEX if not exists Frm_Code_Idx on Frm_Code (XTLB,DMLB,DMZ);";
command.CommandText = drop_From_Code + create_Frm_Code +
create_Frm_Code_Idx;
command.ExecuteNonQuery();
DataTable frmcode = DataService.taps_WebService.Get_CodeTable_by_name(
"Frm_Code");
foreach (
var item
in frmcode.Rows)
{
DataRow dr = item
as DataRow;
sql =
"INSERT INTO FRM_CODE(xtlb,dmlb,dmz,dmsm1,dmsm2,dmsm3,dmsm4,dmsx,sxh,ywdx,zt)values(@xtlb,@dmlb,@dmz,@dmsm1,@dmsm2,@dmsm3,@dmsm4,@dmsx,@sxh,@ywdx,@zt)";
SQLiteParameter[] parameters =
new SQLiteParameter[]{
new SQLiteParameter(
"@xtlb",dr[
"XTLB"]),
new SQLiteParameter(
"@dmlb",dr[
"DMLB"]),
new SQLiteParameter(
"@dmz",dr[
"DMZ"]),
new SQLiteParameter(
"@dmsm1",dr[
"DMSM1"]),
new SQLiteParameter(
"@dmsm2",dr[
"DMSM2"]),
new SQLiteParameter(
"@dmsm3",dr[
"DMSM3"]) ,
new SQLiteParameter(
"@dmsm4",dr[
"DMSM4"]),
new SQLiteParameter(
"@dmsx",dr[
"DMSX"]),
new SQLiteParameter(
"@sxh",dr[
"SXH"]),
new SQLiteParameter(
"@ywdx",dr[
"YWDX"]),
new SQLiteParameter(
"@zt",dr[
"ZT"])
};
command.CommandText =
sql;
command.Parameters.AddRange(parameters);
command.ExecuteNonQuery();
}
gxs++
;
}
#endregion
#region 更新Taps_CodeType
if (taps_codetype_updated)
{
string drop_Taps_CodeType =
"drop table if exists Taps_codeType;";
string create_TAPS_CODETYPE =
"CREATE TABLE if not exists TAPS_CODETYPE( dmlb VARCHAR2(6) not null, lbsm VARCHAR2(256) not null, dmcd NUMBER(2), "
+
"lbsx CHAR(1), dmsx NUMBER(4), bz VARCHAR2(256), jznc CHAR(1), dmlx CHAR(1));";
string create_Taps_CodeType_Idx =
"CREATE UNIQUE INDEX if not exists Taps_CodeType_Idx on Taps_CodeType (DMLB);";
command.CommandText = drop_Taps_CodeType + create_TAPS_CODETYPE +
create_Taps_CodeType_Idx;
command.ExecuteNonQuery();
DataTable tapscodetype = DataService.taps_WebService.Get_CodeTable_by_name(
"Taps_CodeType");
foreach (
var item
in tapscodetype.Rows)
{
DataRow dr = item
as DataRow;
sql =
"INSERT INTO Taps_CODETYPE(dmlb,lbsm,dmcd,lbsx,dmsx,bz,jznc,dmlx)values(@dmlb,@lbsm,@dmcd,@lbsx,@dmsx,@bz,@jznc,@dmlx)";
SQLiteParameter[] parameters =
new SQLiteParameter[]{
new SQLiteParameter(
"@dmlb",dr[
"DMLB"]),
new SQLiteParameter(
"@lbsm",dr[
"LBSM"]),
new SQLiteParameter(
"@dmcd",dr[
"DMCD"]),
new SQLiteParameter(
"@lbsx",dr[
"LBSX"]),
new SQLiteParameter(
"@dmsx",dr[
"DMSX"]) ,
new SQLiteParameter(
"@bz",dr[
"BZ"]),
new SQLiteParameter(
"@jznc",dr[
"JZNC"]),
new SQLiteParameter(
"@dmlx",dr[
"DMLX"])
};
command =
new SQLiteCommand(connection);
command.CommandText =
sql;
command.Parameters.AddRange(parameters);
command.ExecuteNonQuery();
}
gxs++
;
}
#endregion
#region 更新Taps_Code
if (taps_code_updated)
{
string drop_Taps_Code =
"drop table if exists Taps_code;";
string create_TAPS_CODE =
"CREATE TABLE if not exists TAPS_CODE( dmlb VARCHAR2(6) not null, dmz VARCHAR2(30) not null, dmsm1 VARCHAR2(256) not null, dmsm2 VARCHAR2(256), dmsm3 VARCHAR2(256),"
+
" dmsm4 VARCHAR2(256), dmsx CHAR(1) default 0, sxh NUMBER(4), ywdx VARCHAR2(128), zt CHAR(1) default 1);";
string create_Taps_Code_Idx =
"CREATE UNIQUE INDEX if not exists Taps_Code_Idx on Taps_Code (DMLB,DMZ);";
command.CommandText = drop_Taps_Code + create_TAPS_CODE +
create_Taps_Code_Idx;
command.ExecuteNonQuery();
DataTable tapscode = DataService.taps_WebService.Get_CodeTable_by_name(
"Taps_Code");
foreach (
var item
in tapscode.Rows)
{
DataRow dr = item
as DataRow;
sql =
"INSERT INTO Taps_CODE(dmlb,dmz,dmsm1,dmsm2,dmsm3,dmsm4,dmsx,sxh,ywdx,zt)values(@dmlb,@dmz,@dmsm1,@dmsm2,@dmsm3,@dmsm4,@dmsx,@sxh,@ywdx,@zt)";
SQLiteParameter[] parameters =
new SQLiteParameter[]{
new SQLiteParameter(
"@dmlb",dr[
"DMLB"]),
new SQLiteParameter(
"@dmz",dr[
"DMZ"]),
new SQLiteParameter(
"@dmsm1",dr[
"DMSM1"]),
new SQLiteParameter(
"@dmsm2",dr[
"DMSM2"]),
new SQLiteParameter(
"@dmsm3",dr[
"DMSM3"]) ,
new SQLiteParameter(
"@dmsm4",dr[
"DMSM4"]),
new SQLiteParameter(
"@dmsx",dr[
"DMSX"]),
new SQLiteParameter(
"@sxh",dr[
"SXH"]),
new SQLiteParameter(
"@ywdx",dr[
"YWDX"]),
new SQLiteParameter(
"@zt",dr[
"ZT"])
};
command =
new SQLiteCommand(connection);
command.CommandText =
sql;
command.Parameters.AddRange(parameters);
command.ExecuteNonQuery();
}
gxs++
;
}
#endregion
#region 更新DocTemplateType
if (doctemplatetype_updated)
{
string drop_DocTemplateType =
"drop table if exists DocTemplateType;";
string create_DocTemplateType =
" CREATE TABLE if not exists DocTemplateType(XTLB varchar(2),MBLB varchar(6),LBSM varchar(256),MBSX int(4),BZ varchar(256));";
string create_DocTemplateType_Idx =
"CREATE UNIQUE INDEX if not exists DocTemplateType_Idx on DocTemplateType (XTLB,MBLB);";
command.CommandText = drop_DocTemplateType + create_DocTemplateType +
create_DocTemplateType_Idx;
command.ExecuteNonQuery();
DataTable doctemplatetype = DataService.taps_WebService.Get_CodeTable_by_name(
"Taps_DocTemplateType");
foreach (
var item
in doctemplatetype.Rows)
{
DataRow dr = item
as DataRow;
sql =
"INSERT INTO DocTemplateType(xtlb,mblb,lbsm,mbsx,bz)values(@xtlb,@mblb,@lbsm,@mbsx,@bz)";
SQLiteParameter[] parameters =
new SQLiteParameter[]{
new SQLiteParameter(
"@xtlb",dr[
"XTLB"]),
new SQLiteParameter(
"@mblb",dr[
"MBLB"]),
new SQLiteParameter(
"@lbsm",dr[
"LBSM"]),
new SQLiteParameter(
"@mbsx",dr[
"MBSX"]),
new SQLiteParameter(
"@bz",dr[
"BZ"])
};
command =
new SQLiteCommand(connection);
command.CommandText =
sql;
command.Parameters.AddRange(parameters);
command.ExecuteNonQuery();
}
gxs++
;
}
#endregion
#region 更新DocTemplate
if (doctemplate_updated)
{
string drop_DocTemplate =
"drop table if exists DocTemplate;";
string create_DocTemplate =
"CREATE TABLE if not exists DocTemplate( xtlb CHAR(2) not null, mblb VARCHAR2(6) not null, mbdm VARCHAR2(30) not null, "
+
"mbmc VARCHAR2(256) not null, sxh NUMBER(4), zt CHAR(1) default 1, gxsj datatime, mb Blob);";
string create_DocTemplate_Idx =
"CREATE UNIQUE INDEX if not exists DocTemplate_Idx on DocTemplate (XTLB,MBLB,MBDM);";
command.CommandText = drop_DocTemplate + create_DocTemplate +
create_DocTemplate_Idx;
command.ExecuteNonQuery();
DataTable doctemplate = DataService.taps_WebService.Get_CodeTable_by_name(
"Taps_DocTemplate");
foreach (
var item
in doctemplate.Rows)
{
DataRow dr = item
as DataRow;
string mb_file = dr[
"XTLB"] +
"_" + dr[
"MBLB"] +
"_" + dr[
"MBDM"];
byte[] mb_buffer =
DataService.taps_WebService.Get_DocTemplate(mb_file);
sql =
"INSERT INTO DocTemplate(xtlb,mblb,mbdm,mbmc,sxh,zt,gxsj,mb)values(@xtlb,@mblb,@mbdm,@mbmc,@sxh,@zt,@gxsj,@mb)";
SQLiteParameter[] parameters =
new SQLiteParameter[]{
new SQLiteParameter(
"@xtlb",dr[
"XTLB"]),
new SQLiteParameter(
"@mblb",dr[
"MBLB"]),
new SQLiteParameter(
"@mbdm",dr[
"MBDM"]),
new SQLiteParameter(
"@mbmc",dr[
"MBMC"]),
new SQLiteParameter(
"@sxh",dr[
"SXH"]),
new SQLiteParameter(
"@zt",dr[
"ZT"]),
new SQLiteParameter(
"@gxsj",dr[
"GXSJ"]) ,
new SQLiteParameter(
"@mb",mb_buffer) };
command.CommandText =
sql;
command.Parameters.AddRange(parameters);
command.ExecuteNonQuery();
}
gxs++
;
}
#endregion
#region 记录本次更新时间
if (gxs >
0)
{
sql =
"update update_info set gxsj=datetime(‘" + DateTime.Now.ToString(
"yyyy-MM-dd HH:mm:ss") +
"‘);";
command.CommandText =
sql;
command.ExecuteNonQuery();
}
#endregion
transaction.Commit(); //提交事务，写入数据文件
}
catch (Exception)
{
transaction.Rollback(); //出错回滚
return -
1;
}
}
connection.Close();
}
return gxs;
}
catch
{
return -
1;
}
}
}
}
}
废弃sqlite代码，备查
标签：
