//查询数据库
public void ExecuteQuery(string sql)
{
if (HistoryListView.Items.Count > 0)
{
this.HistoryListView.ItemsSource = null;
HistoryList.Clear();
}
this.HistoryListView.ItemsSource = HistoryList;
PublicFunction.Open(PublicFunction.SqLiteConnection);
using (var tr = PublicFunction.SqLiteConnection.BeginTransaction())
{
using (var command = PublicFunction.SqLiteConnection.CreateCommand())
{
command.CommandText = sql;
//执行查询语句返回SQLiteDataReader对象
var reader = command.ExecuteReader();
//reader.Read()方法会从读出一行匹配的数据到reader中。注意:是一行数据。
while (reader.Read())
{
// 有一系列的Get方法，方法的参数是列数。意思是获取第n列的数据，转成Type返回。
// 比如这里的语句，意思就是：获取第0列的数据，转成int值返回。
// var temp = reader.GetInt32(0);
string id = reader.GetInt32(0).ToString();
string type = reader.GetString(1);
dynamic tempBytes;
try
{
tempBytes = reader.GetString(2);
}
catch (Exception e)
{
tempBytes = "";
}
string data;
switch (type)
{
case "TEXT":
data = PublicFunction.SqlRebuild(tempBytes);
if (data.Length > ViewTextNumber)
{
data = data.Substring(0, ViewTextNumber);
}
break;
case "IMG":
data = PublicFunction.SqlRebuild(tempBytes);
break;
case "FILE":
data = PublicFunction.SqlRebuild(tempBytes);
break;
default:
data = "Byte|" + tempBytes.GetType().ToString();
break;
}
string time = Convert.ToDateTime(reader.GetString(3)).ToString();
// dynamic temp = reader.GetInt32(4);
// MessageBox.Show(temp.ToString());
string locked = reader.GetString(4);
string note = reader.GetString(5);
HistoryList.Add(new HistoryInfo(id, type, data, time, locked, note));
// MessageBox.Show(note);
// ListView myListView = new ListView();
}
}
tr.Commit();
}
}
//检测到剪切板变化后的操作
private void MyClipboard_ClipboardChanged(object sender, SharpClipboard.ClipboardChangedEventArgs e)
{
//播放提示音
try
{
SoundPlayer spPlayer = new SoundPlayer(ClipBoardSet.Default.VoicePath);
spPlayer.Play();
spPlayer.Dispose();
}
catch (Exception exception)
{
}
if (MyVariable.Variable.SetClip != true)
{
//实例化数据库操作命令
SQLiteCommand cmdCommand = PublicFunction.SqLiteConnection.CreateCommand();
//取系统时间
DateTime datestart = new DateTime(DateTime.Now.Year, 1, 1, 0, 0, 0);
TimeSpan interval = DateTime.Now - datestart;
string id, type, time, data, sql, note;
//生成ID
id = interval.TotalSeconds.ToString().Replace(".", "").Substring(0, 8);//取秒时间差作为索引ID
//取现行时间
time = DateTime.Now.ToString();
switch (e.ContentType)
{
case SharpClipboard.ContentTypes.Text:
PublicFunction.Open(PublicFunction.SqLiteConnection);
sql = string.Format("SELECT COUNT(*) FROM history WHERE id={0}", id);
cmdCommand.CommandText = sql;
var reader = cmdCommand.ExecuteScalar();
if (Convert.ToUInt32(reader) != 1)
{
type = "TEXT";
data = PublicFunction.SqlClean(myClipboard.ClipboardText);
note = e.SourceApplication.Title + "\n" + data.Length + "\n" + time;
sql = string.Format(
"INSERT INTO history (id,type,data,date,lock,note) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}')",
id, type, data, time, 0, note);
cmdCommand.CommandText = sql;
cmdCommand.ExecuteNonQuery();
}
break;
case SharpClipboard.ContentTypes.Image:
PublicFunction.Open(PublicFunction.SqLiteConnection);
type = "IMG";
BitmapSource tempImage = Clipboard.GetImage();
string temppath = string.Format(PublicFunction.ImgPath + "{0}.jpg", id);
PublicFunction.SaveImageToJpeg(tempImage, 100, temppath);
data = temppath;
note = e.SourceApplication.Title + "\n" + tempImage.PixelWidth.ToString() + "x" + tempImage.PixelHeight.ToString() + "\n" + time;
sql = string.Format("INSERT INTO history (id,type,data,date,lock,note) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}')", id, type, data, time, 0, note);
cmdCommand.CommandText = sql;
cmdCommand.ExecuteNonQuery();
break;
case SharpClipboard.ContentTypes.Files:
PublicFunction.Open(PublicFunction.SqLiteConnection);
type = "FILE";
data = myClipboard.ClipboardFile;
note = e.SourceApplication.Title + "\n" + time;
sql = string.Format("INSERT INTO history (id,type,data,date,lock,note) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}')", id, type, data, time, 0, note);
cmdCommand.CommandText = sql;
cmdCommand.ExecuteNonQuery();
break;
}
}
}