You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
393 lines
14 KiB
C#
393 lines
14 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Windows.Forms;
|
|
using System.Data.SQLite;
|
|
using System.Data;
|
|
using System.IO;
|
|
|
|
namespace demo
|
|
{
|
|
class MySqlite
|
|
{
|
|
public static string path = Application.StartupPath + "\\db\\远程省内邮寄信息.db";
|
|
|
|
public static string tableName = "yfj";
|
|
public static string jgh = "43001800";
|
|
public static string gh = "YFJ000001";
|
|
|
|
public static string strConn = "data source=" + path;
|
|
//yf_id VARCHAR(64),yf_OddNumbers varchar(13),yf_applicant_sfzid varchar(18),yf_applicant_name varchar(32),yf_receiver_name varchar(20),yf_address varchar(200),yf_phone_number varchar(13),yf_intime time,yf_state varchar(2),yf_read_time time,yf_jgh varchar(8),yf_gh varchar(9),ztc_state varchar(2));", tableName);
|
|
public static string[] colNames = new string[] { "yf_id", "yf_OddNumbers", "yf_applicant_sfzid", "yf_applicant_name", "yf_receiver_name", "yf_address", "yf_phone_number", "yf_intime", "yf_state", "yf_read_time", "yf_jgh", "yf_gh", "ztc_state" };
|
|
|
|
public static bool CreateDB(string path)
|
|
{
|
|
if (File.Exists(path))
|
|
{
|
|
return true;
|
|
}
|
|
bool ret = false;
|
|
SQLiteConnection cn = null;
|
|
try
|
|
{
|
|
cn = new SQLiteConnection("data source=" + path);
|
|
cn.Open();
|
|
ret = true;
|
|
}
|
|
catch (System.Exception ex)
|
|
{
|
|
}
|
|
finally
|
|
{
|
|
cn.Close();
|
|
}
|
|
return ret;
|
|
}
|
|
|
|
public static bool CreateTable(string path, string tableName)
|
|
{
|
|
bool ret = false;
|
|
SQLiteConnection cn = new SQLiteConnection("data source=" + path);
|
|
if (cn.State != System.Data.ConnectionState.Open)
|
|
{
|
|
try
|
|
{
|
|
cn.Open();
|
|
SQLiteCommand cmd = new SQLiteCommand();
|
|
cmd.Connection = cn;
|
|
string sql = "CREATE TABLE IF NOT EXISTS " + tableName +
|
|
"(yf_id VARCHAR(64),yf_OddNumbers varchar(13),yf_applicant_sfzid varchar(18),yf_applicant_name varchar(32),yf_receiver_name varchar(20),yf_address varchar(200),yf_phone_number varchar(13),yf_intime time,yf_state varchar(2),yf_read_time time,yf_jgh varchar(8),yf_gh varchar(9),ztc_state varchar(2))";
|
|
|
|
//cmd.CommandText = "CREATE TABLE t1(id varchar(4),score int)";
|
|
//cmd.CommandText = "CREATE TABLE IF NOT EXISTS t1(id varchar(4),score int)";
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
ret = true;
|
|
}
|
|
catch (System.Exception ex)
|
|
{
|
|
|
|
}
|
|
finally
|
|
{
|
|
cn.Close();
|
|
}
|
|
}
|
|
return ret;
|
|
}
|
|
|
|
public static bool UpdateDataFromRemote(DataTable dt)
|
|
{
|
|
lock (GlobalVariable.lockSqlite)
|
|
{
|
|
bool ret = false;
|
|
|
|
SQLiteConnection conn = new SQLiteConnection("data source=" + path);
|
|
SQLiteDataAdapter sda = new SQLiteDataAdapter();
|
|
sda.SelectCommand = new SQLiteCommand("select * from " + tableName, conn);
|
|
try
|
|
{
|
|
conn.Open();
|
|
SQLiteTransaction ts = conn.BeginTransaction();
|
|
SQLiteCommandBuilder scb = new SQLiteCommandBuilder(sda);
|
|
scb.QuotePrefix = "[";
|
|
scb.QuoteSuffix = "]";
|
|
DataSet ds = new DataSet();
|
|
sda.Fill(ds, tableName);
|
|
foreach (DataRow dtRow in dt.Rows)
|
|
{
|
|
DataRow dr = ds.Tables[tableName].NewRow();
|
|
dr.ItemArray = dtRow.ItemArray;//行复制
|
|
ds.Tables[tableName].Rows.Add(dr);
|
|
}
|
|
sda.Update(ds,tableName);
|
|
ts.Commit(); // 提交事务
|
|
ds.AcceptChanges();
|
|
ret = true;
|
|
}
|
|
catch (System.Exception ex)
|
|
{
|
|
|
|
}
|
|
return ret;
|
|
}
|
|
}
|
|
|
|
public static bool SelectPostInfoBySfzID(string sfzid, ref string yf_id, ref string recvAddress, ref string postNo, ref string recvName, ref string recvNo, ref string applicantName)
|
|
{
|
|
lock (GlobalVariable.lockSqlite)
|
|
{
|
|
bool flag = false;
|
|
SQLiteConnection conn = null;
|
|
SQLiteCommand cmd = null;
|
|
DataSet ds = new DataSet();
|
|
string sql = null;
|
|
sql = string.Format("select * from {0} where yf_applicant_sfzid = '{1}'", tableName, sfzid);
|
|
try
|
|
{
|
|
conn = new SQLiteConnection(strConn);
|
|
conn.Open();
|
|
cmd = new SQLiteCommand(sql, conn);
|
|
SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
|
|
sda.Fill(ds);
|
|
|
|
//DataTable dt = new DataTable();
|
|
//sda.Fill(dt);
|
|
//for (int i = 0; i < dt.Rows.Count; i++)
|
|
//{
|
|
// for (int j = 0; j < dt.Columns.Count; j++)
|
|
// {
|
|
// //Console.WriteLine(dt.Rows[i][j].ToString());
|
|
// string str = dt.Rows[i][j].ToString();
|
|
// }
|
|
//}
|
|
//Console.ReadKey();
|
|
|
|
if (ds.Tables[0].Rows.Count == 1)
|
|
{
|
|
yf_id = ds.Tables[0].Rows[0]["yf_id"].ToString();
|
|
|
|
recvAddress = ds.Tables[0].Rows[0]["yf_address"].ToString();
|
|
postNo = ds.Tables[0].Rows[0]["yf_OddNumbers"].ToString();
|
|
recvName = ds.Tables[0].Rows[0]["yf_receiver_name"].ToString();
|
|
recvNo = ds.Tables[0].Rows[0]["yf_phone_number"].ToString();
|
|
applicantName = ds.Tables[0].Rows[0]["yf_applicant_name"].ToString();
|
|
flag = true;
|
|
}
|
|
|
|
}
|
|
catch (Exception)
|
|
{
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
conn.Close();
|
|
}
|
|
return flag;
|
|
}
|
|
}
|
|
|
|
public static bool UpdateCheckState(string yfid, string state)
|
|
{
|
|
lock (GlobalVariable.lockSqlite)
|
|
{
|
|
bool flag = false;
|
|
SQLiteConnection conn = null;
|
|
SQLiteCommand cmd = null;
|
|
string readTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
|
|
string sql = string.Format("update {0} set yf_read_time = datetime('{1}'),yf_state = '{2}',yf_jgh = '{3}',yf_gh = '{4}' where yf_id = '{5}'",
|
|
tableName, readTime, state, jgh, gh, yfid);
|
|
try
|
|
{
|
|
conn = new SQLiteConnection(strConn);
|
|
conn.Open();
|
|
cmd = new SQLiteCommand(sql, conn);
|
|
int i = cmd.ExecuteNonQuery();
|
|
if (i > 0)
|
|
{
|
|
flag = true;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
string str = ex.ToString();
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
conn.Close();
|
|
}
|
|
return flag;
|
|
}
|
|
}
|
|
|
|
public static bool UpdateCheckStateBySfzid(string sfzid, string state)
|
|
{
|
|
lock (GlobalVariable.lockSqlite)
|
|
{
|
|
bool flag = false;
|
|
SQLiteConnection conn = null;
|
|
SQLiteCommand cmd = null;
|
|
string readTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
|
|
string sql = string.Format("update {0} set yf_read_time = datetime('{1}'),yf_state = '{2}',yf_jgh = '{3}',yf_gh = '{4}' where yf_applicant_sfzid = '{5}'",
|
|
tableName, readTime, state, jgh, gh, sfzid);
|
|
try
|
|
{
|
|
conn = new SQLiteConnection(strConn);
|
|
conn.Open();
|
|
cmd = new SQLiteCommand(sql, conn);
|
|
int i = cmd.ExecuteNonQuery();
|
|
if (i > 0)
|
|
{
|
|
flag = true;
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
string str = ex.ToString();
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
conn.Close();
|
|
}
|
|
return flag;
|
|
}
|
|
}
|
|
|
|
public static bool UpdateCheckStateToNoBySfzid(string sfzid)
|
|
{
|
|
lock (GlobalVariable.lockSqlite)
|
|
{
|
|
bool flag = false;
|
|
SQLiteConnection conn = null;
|
|
SQLiteCommand cmd = null;
|
|
//string readTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
|
|
string sql = string.Format("update {0} set yf_read_time = datetime('{1}'),yf_state = '{2}',yf_jgh = '{3}',yf_gh = '{4}' where yf_applicant_sfzid = '{5}'",
|
|
tableName, null, "否", null, null, sfzid);
|
|
try
|
|
{
|
|
conn = new SQLiteConnection(strConn);
|
|
conn.Open();
|
|
cmd = new SQLiteCommand(sql, conn);
|
|
int i = cmd.ExecuteNonQuery();
|
|
if (i>0)
|
|
{
|
|
flag = true;
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
string str = ex.ToString();
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
conn.Close();
|
|
}
|
|
return flag;
|
|
}
|
|
}
|
|
|
|
public static bool DeleteCheckOkData()
|
|
{
|
|
lock (GlobalVariable.lockSqlite)
|
|
{
|
|
bool flag = false;
|
|
SQLiteConnection conn = null;
|
|
SQLiteCommand cmd = null;
|
|
//string readTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
|
|
DateTime nowTime = DateTime.Now;
|
|
FileInfo fi = new FileInfo(MySqlite.path);
|
|
DateTime createTime = fi.CreationTime;
|
|
TimeSpan ts = nowTime - createTime;
|
|
double time = ts.TotalDays;
|
|
if (time <= 15)
|
|
{
|
|
return true;
|
|
}
|
|
string endTime = nowTime.AddDays(-15).ToString("yyyy-MM-dd HH:mm");
|
|
string sql = string.Format("delete from {0} where yf_state = '{1}' and datetime(yf_read_time) < datetime('{2}')",
|
|
tableName, "是", endTime);
|
|
try
|
|
{
|
|
conn = new SQLiteConnection(strConn);
|
|
conn.Open();
|
|
cmd = new SQLiteCommand(sql, conn);
|
|
cmd.ExecuteNonQuery();
|
|
flag = true;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
string str = ex.ToString();
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
conn.Close();
|
|
}
|
|
return flag;
|
|
}
|
|
}
|
|
|
|
public static bool DeleteBefore60DaysData()
|
|
{
|
|
lock (GlobalVariable.lockSqlite)
|
|
{
|
|
bool flag = false;
|
|
SQLiteConnection conn = null;
|
|
SQLiteCommand cmd = null;
|
|
//string readTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
|
|
DateTime nowTime = DateTime.Now;
|
|
FileInfo fi = new FileInfo(MySqlite.path);
|
|
DateTime createTime = fi.CreationTime;
|
|
TimeSpan ts = nowTime - createTime;
|
|
double time = ts.TotalDays;
|
|
if (time <= 15)
|
|
{
|
|
return true;
|
|
}
|
|
string startTime = nowTime.AddDays(-60).ToString("yyyy-MM-dd HH:mm:ss");
|
|
string endTime = nowTime.ToString("yyyy-MM-dd HH:mm:ss");
|
|
string sql = string.Format("delete from {0} where datetime(yf_intime) < datetime('{1}')",
|
|
tableName, startTime);
|
|
try
|
|
{
|
|
conn = new SQLiteConnection(strConn);
|
|
conn.Open();
|
|
cmd = new SQLiteCommand(sql, conn);
|
|
cmd.ExecuteNonQuery();
|
|
flag = true;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
string str = ex.ToString();
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
conn.Close();
|
|
}
|
|
return flag;
|
|
}
|
|
}
|
|
|
|
public static bool GetRowsCount(ref int count)
|
|
{
|
|
lock (GlobalVariable.lockSqlite)
|
|
{
|
|
bool flag = false;
|
|
SQLiteConnection conn = null;
|
|
SQLiteCommand cmd = null;
|
|
string sql = string.Format("select * from {0}",
|
|
tableName);
|
|
try
|
|
{
|
|
conn = new SQLiteConnection(strConn);
|
|
conn.Open();
|
|
cmd = new SQLiteCommand(sql, conn);
|
|
DataTable dt = new DataTable();
|
|
SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
|
|
sda.Fill(dt);
|
|
count = dt.Rows.Count;
|
|
flag = true;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
string str = ex.ToString();
|
|
}
|
|
finally
|
|
{
|
|
cmd.Dispose();
|
|
conn.Close();
|
|
}
|
|
return flag;
|
|
}
|
|
}
|
|
}
|
|
}
|