using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
using System.Data.SQLite;
|
using System.Windows.Forms;
|
using WindowsFormsApp1.entity.tb;
|
using WindowsFormsApp1.entity;
|
|
namespace WindowsFormsApp1.utils
|
{
|
class SQLiteDataBaseUtil
|
{
|
|
public SQLiteConnection getConnection() {
|
return new SQLiteConnection("Data Source=BKZ;Version=3;");
|
}
|
|
SQLiteConnection con;
|
private static SQLiteDataBaseUtil instance;
|
public static SQLiteDataBaseUtil getInstance() {
|
if (instance == null)
|
{
|
SQLiteConnection.CreateFile("BKZ");//创建数据库
|
instance = new SQLiteDataBaseUtil();
|
instance.con = new SQLiteConnection("Data Source=BKZ;Version=3;");
|
instance.init();
|
}
|
return instance;
|
}
|
|
|
//初始化数据库
|
private void init() {
|
CreateTBAccountTable();
|
CreateLoginTable();
|
CreateConfigTable();
|
}
|
|
|
public void execUpdate(String sql) {
|
con.Open();
|
SQLiteCommand command = new SQLiteCommand(sql, con);
|
command.ExecuteNonQuery();
|
con.Close();
|
}
|
|
|
|
|
|
/*
|
*
|
* 淘宝账号管理
|
*
|
*/
|
|
private void CreateTBAccountTable() {
|
SQLiteConnection con = getConnection();
|
con.Open();
|
string sql = "CREATE TABLE IF NOT EXISTS tb_account_info (`index` int, accountName varchar(256), tbUid varchar(128), loginState int)";
|
SQLiteCommand command = new SQLiteCommand(sql, con);
|
command.ExecuteNonQuery();
|
con.Close();
|
}
|
|
//添加本地淘宝账号
|
public void AddTaoBaoAccount(TBAccountLogin account) {
|
List<TBAccountLogin> list= GetByTBUid(account.TbUid);
|
if (list != null && list.Count > 0)
|
return;
|
SQLiteConnection con = getConnection();
|
con.Open();
|
string sql = "insert into tb_account_info (`index`,accountName, tbUid,loginState) values ("+ account .Index+ ", '"+ account .NickName+ "',"+account.TbUid+", "+(account.Login?1:0)+")";
|
SQLiteCommand command = new SQLiteCommand(sql, con);
|
command.ExecuteNonQuery();
|
con.Close();
|
}
|
|
//掉线
|
public void TBOffLine(int index) {
|
SQLiteConnection con= getConnection();
|
con.Open();
|
string sql = "update tb_account_info set loginState=0 where `index`=" + index;
|
SQLiteCommand command = new SQLiteCommand(sql, con);
|
command.ExecuteNonQuery();
|
con.Close();
|
}
|
|
|
public void TBDelete(int index)
|
{
|
SQLiteConnection con = getConnection();
|
con.Open();
|
string sql = "delete from tb_account_info where `index`=" + index;
|
SQLiteCommand command = new SQLiteCommand(sql, con);
|
command.ExecuteNonQuery();
|
con.Close();
|
}
|
|
//上线
|
public void TBOnLine(TBAccountLogin account) {
|
TBDelete(account.Index);
|
AddTaoBaoAccount(account);
|
}
|
|
|
//根据淘宝UID查询
|
public List<TBAccountLogin> GetByTBUid(String tbUid) {
|
List<TBAccountLogin> list = new List<TBAccountLogin>();
|
SQLiteConnection con = getConnection();
|
con.Open();
|
string sql = "select * from tb_account_info where tbUid="+tbUid;
|
if (tbUid == null) {
|
sql = "select * from tb_account_info";
|
}
|
SQLiteCommand command = new SQLiteCommand(sql, con);
|
SQLiteDataReader reader = command.ExecuteReader();
|
while (reader.Read()) {
|
TBAccountLogin account = new TBAccountLogin();
|
account.NickName = reader["accountName"].ToString();
|
account.Login = Convert.ToInt32(reader["loginState"]) == 1 ? true : false;
|
account.TbUid = reader["tbUid"].ToString();
|
account.Index =Convert.ToInt32(reader["index"].ToString());
|
list.Add(account);
|
}
|
con.Close();
|
return list;
|
}
|
|
/**
|
*
|
* 登录账号管理
|
*
|
*/
|
|
|
private void CreateLoginTable() {
|
SQLiteConnection con = getConnection();
|
con.Open();
|
string sql = "CREATE TABLE IF NOT EXISTS account_info (`account` varchar(256) PRIMARY KEY, `token` varchar(256), expireTime varchar(64),login_state int)";
|
SQLiteCommand command = new SQLiteCommand(sql, con);
|
command.ExecuteNonQuery();
|
con.Close();
|
}
|
|
|
//添加账户
|
public void AddAccount(UserInfo userInfo) {
|
DeleteByAccount(userInfo.Account);
|
SQLiteConnection con = getConnection();
|
con.Open();
|
string sql = "insert into account_info(`account`,`token`,expireTime,login_state) values('" + userInfo.Account+ "','"+ userInfo.Token+ "','"+ TimeUtil.GetGeneralTime(userInfo.SdljShareExpireTime,"yyyyMMdd HH:mm:ss")+ "',1)";
|
SQLiteCommand command = new SQLiteCommand(sql, con);
|
command.ExecuteNonQuery();
|
con.Close();
|
}
|
|
//删除账户
|
public void DeleteByAccount(String account)
|
{
|
SQLiteConnection con = getConnection();
|
con.Open();
|
string sql = "delete from account_info where `account`='" + account + "'";
|
SQLiteCommand command = new SQLiteCommand(sql, con);
|
command.ExecuteNonQuery();
|
con.Close();
|
}
|
|
//查询账户
|
public UserInfo GetLoginAccount()
|
{
|
SQLiteConnection con = getConnection();
|
con.Open();
|
string sql = "select * from account_info where login_state=1 limit 1";
|
SQLiteCommand command = new SQLiteCommand(sql, con);
|
SQLiteDataReader reader = command.ExecuteReader();
|
UserInfo user = new UserInfo();
|
if (reader.Read())
|
{
|
user.Account = reader["account"].ToString();
|
user.Token = reader["token"].ToString();
|
// user.SdljShareExpireTime =new DateTime( reader["expireTime"].ToString());
|
}
|
else {
|
user = null;
|
}
|
reader.Close();
|
con.Close();
|
return user;
|
}
|
|
/*
|
*
|
*系统配置参数
|
*
|
*/
|
|
|
private void CreateConfigTable()
|
{
|
SQLiteConnection con = getConnection();
|
con.Open();
|
string sql = "CREATE TABLE IF NOT EXISTS config (`key` varchar(256) PRIMARY KEY, `value` varchar(256))";
|
SQLiteCommand command = new SQLiteCommand(sql, con);
|
command.ExecuteNonQuery();
|
con.Close();
|
}
|
|
|
//添加账户
|
public void AddConfig(Config config)
|
{
|
DeleteConfig(config.Key);
|
SQLiteConnection con = getConnection();
|
con.Open();
|
string sql = "insert into config(`key`,`value`) values('" + config.Key + "','" + config.Value + "')";
|
SQLiteCommand command = new SQLiteCommand(sql, con);
|
command.ExecuteNonQuery();
|
con.Close();
|
}
|
|
//删除账户
|
public void DeleteConfig(String key)
|
{
|
SQLiteConnection con = getConnection();
|
con.Open();
|
string sql = "delete from config where `key`='" + key + "'";
|
SQLiteCommand command = new SQLiteCommand(sql, con);
|
command.ExecuteNonQuery();
|
con.Close();
|
}
|
|
//查询账户
|
public Config GetConfig(String key)
|
{
|
SQLiteConnection con = getConnection();
|
con.Open();
|
string sql = "select * from config where `key`='"+ key + "'";
|
SQLiteCommand command = new SQLiteCommand(sql, con);
|
SQLiteDataReader reader = command.ExecuteReader();
|
Config config = new Config();
|
if (reader.Read())
|
{
|
config.Key = reader["key"].ToString();
|
config.Value = reader["value"].ToString();
|
}
|
else
|
{
|
config = null;
|
}
|
reader.Close();
|
con.Close();
|
return config;
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
}
|
}
|
