private void Createdbtable()
{
ADbInteractive db = new ADbInteractive(AStatic.DbPath);
db.CreateDb(AStatic.DbPath);
string sql = "create table accounts(id integer primary key, tag text unique not null, category text, url text, user text, password text, phone text, mail text, notes text, lastmodified TimeStamp NOT NULL DEFAULT (datetime('now','localtime')), pyindex text, pyfspell text)";
db.ExecQuery(sql, null);
}
public void ReadCatalog()
{
AStatic.Catalog = new List<ALibModel>();
string sql = "select tag, category, pyindex, pyfspell from accounts order by pyindex asc";
ADbInteractive db = new ADbInteractive(AStatic.DbPath);
using (SQLiteDataReader reader = db.ExecReader(sql, null))
{
while (reader.Read())
{
AStatic.Catalog.Add(new ALibModel(reader.GetString(0),
reader.GetString(1), reader.GetString(2),
reader.GetString(3)));
}
reader.Close();
}
}
private int Update()
{
string sql = "update accounts set "
//+ "tag='" + tag + "', "
+ "category='" + category + "',"
+ "url='" + url + "', "
+ "user='******', "
+ "password='******', "
+ "phone='" + phone + "', "
+ "mail='" + mail + "', "
+ "notes='" + notes + "', "
+ "lastmodified=datetime('now','localtime') "
+ "where tag='" + tag + "'";
ADbInteractive db = new ADbInteractive(AStatic.DbPath);
return db.ExecQuery(sql, null);
}
private int InsertInto()
{
if (!ExistsInDb(tag))
{
APinYin pyer = new APinYin();
string sql = "insert into accounts (tag, category, url, user, password, phone, mail, notes, pyindex, pyfspell) "
+ "values('"
+ tag + "', '"
+ category + "', '"
+ url + "', '"
+ user + "', '"
+ password + "', '"
+ phone + "', '"
+ mail + "', '"
+ notes + "', '"
+ pyer.GetPyStr(tag, false).ToLower() + "', '"
+ pyer.GetPyStr(tag, true).ToLower() + "')";
ADbInteractive db = new ADbInteractive(AStatic.DbPath);
return db.ExecQuery(sql, null);
}
else
{
return -1;
}
}
public void ReadAccount(string t)
{
string sql = "select * from accounts where tag='" + t + "'";
ADbInteractive db = new ADbInteractive(AStatic.DbPath);
using (SQLiteDataReader reader = db.ExecReader(sql, null))
{
while (reader.Read())
{
tag = reader.GetString(1);
category = reader.GetString(2);
url = reader.GetString(3);
user = reader.GetString(4);
password = reader.GetString(5);
phone = reader.GetString(6);
mail = reader.GetString(7);
notes = reader.GetString(8);
}
reader.Close();
}
}
public void HtmlExport(string fname)
{
using (StreamReader fs = new StreamReader("share\\head.html"))
{
head = fs.ReadToEnd();
}
head += "<h2>Accounts</h2>\n";
DateTime dt = DateTime.Now;
head += "<p>" + dt.ToString() + "</p>\n";
body = "<table class=\"dataintable\">\n<tbody>\n";
body += "<tr>";
for (int i = 1; i < 10; i++)
{
body += "<th>"+title[i - 1] + "</th>\n";
}
body += "</tr>\n";
string sql = "select * from accounts order by pyindex asc";
ADbInteractive db = new ADbInteractive(AStatic.DbPath);
using (SQLiteDataReader reader = db.ExecReader(sql, null))
{
while (reader.Read())
{
body += "<tr>\n";
for (int i = 1; i < 10; i++)
{
body += "<td>" + reader.GetString(i) + "</td>\n";
}
body += "</tr>\n";
}
reader.Close();
}
using (StreamWriter fw = new StreamWriter(fname, false))
{
fw.Write(head + body + tail);
}
}
public void TxtExport(string fname)
{
string sql = "select * from accounts order by pyindex asc";
FileStream fs = new FileStream(fname, FileMode.Create);
StreamWriter sw = new StreamWriter(fs);
string result = "";
for (int i = 1; i < 10; i++)
{
result += title[i - 1] + ",";
}
sw.Write(result + "\r\n");
result = "";
ADbInteractive db = new ADbInteractive(AStatic.DbPath);
using (SQLiteDataReader reader = db.ExecReader(sql, null))
{
while (reader.Read())
{
for (int i = 1; i < 10; i++)
{
result += reader.GetString(i) + ",";
}
result += "\r\n";
}
reader.Close();
}
sw.Write(result);
sw.Close();
fs.Close();
}
public int DelFromDb(string t)
{
string sql = "delete from accounts where tag='" + t + "'";
ADbInteractive db = new ADbInteractive(AStatic.DbPath);
return db.ExecQuery(sql, null);
}