public static void CreateTable()
{
string dbPath = testDatabaseName;
//如果不存在改数据库文件，则创建该数据库文件
if (!System.IO.File.Exists(dbPath))
{
SQLiteDBHelper.CreateDB(testDatabaseName);
}
bool tableExist = false;
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath))
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = "select count(*) from sqlite_master where type = 'table' and name = 'Test3'";
int num = Convert.ToInt32(command.ExecuteScalar());
if (num > 0)
{
tableExist = true;
}
}
}
if (!tableExist)
{
SQLiteDBHelper db = new SQLiteDBHelper(testDatabaseName);
string sql = "CREATE TABLE Test3(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,Name char(3),TypeName varchar(50),addDate datetime,UpdateTime Date,Time time,Comments blob)";
db.ExecuteNonQuery(sql, null);
}
}
static public bool SentToDB()
{
if (!System.IO.File.Exists(dbPath))
{
SQLiteDBHelper.CreateDB(dbPath);
}
bool tableExist = false;
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath))
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = "select count(*) from sqlite_master where type = 'table' and name = '" + existFilessName + "'";
int num = Convert.ToInt32(command.ExecuteScalar());
if (num > 0)
{
tableExist = true;
}
}
}
SQLiteDBHelper db = null;
string sql = null;
if (tableExist)
{
db = new SQLiteDBHelper(dbPath);
sql = "DROP TABLE " + existFilessName;
db.ExecuteNonQuery(sql, null);
}
db = new SQLiteDBHelper(dbPath);
sql = "CREATE TABLE " + existFilessName + @"(
name TEXT,
filePath TEXT )";
db.ExecuteNonQuery(sql, null);
sql = "INSERT INTO " + existFilessName + "(name,filePath)values(@name, @filePath)";
db = new SQLiteDBHelper(dbPath);
for (int i = 0; i < g_FileInfoCollector.Count; i++)
{
SQLiteParameter[] parameters = new SQLiteParameter[] {
new SQLiteParameter("@name", ((FileInformation)g_FileInfoCollector[i]).FileName),
new SQLiteParameter("@filePath", ((FileInformation)g_FileInfoCollector[i]).FullName)
};
db.ExecuteNonQuery(sql, parameters);
}
return(true);
}
public static void ShowData()
{
//查询从50条起的20条记录
string sql = "select * from test3 order by id desc limit 50 offset 20";
SQLiteDBHelper db = new SQLiteDBHelper(testDatabaseName);
using (SQLiteDataReader reader = db.ExecuteReader(sql, null))
{
while (reader.Read())
{
Console.WriteLine("ID:{0},TypeName{1}", reader.GetInt64(0), reader.GetString(1));
}
}
}
protected override void UpdateUI()
{
SetCol();
dataGridView.Rows.Clear();
SQLiteDBHelper db = new SQLiteDBHelper(datebaseName);
string sql = "select * from " + existFilessName;
using (SQLiteDataReader reader = db.ExecuteReader(sql, null))
{
int index = 0;
while (reader.Read())
{
dataGridView.Rows.Add();
dataGridView.Rows[index].Cells[GetColIndex("序号")].Value = index.ToString();
dataGridView.Rows[index].Cells[GetColIndex("文件名")].Value = reader["name"].ToString();
++index;
}
}
}
public static void InsertData()
{
string sql = "INSERT INTO Test3(Name,TypeName,addDate,UpdateTime,Time,Comments)values(@Name,@TypeName,@addDate,@UpdateTime,@Time,@Comments)";
SQLiteDBHelper db = new SQLiteDBHelper(testDatabaseName);
for (char c = 'A'; c <= 'Z'; c++)
{
for (int i = 0; i < 5; i++)
{
SQLiteParameter[] parameters = new SQLiteParameter[] {
new SQLiteParameter("@Name", c + i.ToString()),
new SQLiteParameter("@TypeName", c.ToString()),
new SQLiteParameter("@addDate", DateTime.Now),
new SQLiteParameter("@UpdateTime", DateTime.Now.Date),
new SQLiteParameter("@Time", DateTime.Now.ToShortTimeString()),
new SQLiteParameter("@Comments", "Just a Test" + i)
};
db.ExecuteNonQuery(sql, parameters);
}
}
}
public void UpdataGridView(ArrayList FileInfoCollector)
{
SetCol();
dataGridView1.Rows.Clear();
dataGridView1.Rows.Insert(0, 100);
int serialIndex = FileUtilty.GetDataGridViewIndex(dataGridView1, "序号");
int fileNameIndex = FileUtilty.GetDataGridViewIndex(dataGridView1, "文件名");
int pathIndex = FileUtilty.GetDataGridViewIndex(dataGridView1, "路径");
const string dbPath = ExistFiles.dbPath;
if (!System.IO.File.Exists(dbPath))
{
return;
}
SQLiteDBHelper db = new SQLiteDBHelper(dbPath);
string sql = "select * from " + ExistFiles.existFilessName;
using (SQLiteDataReader reader = db.ExecuteReader(sql, null))
{
int index = 0;
while (reader.Read())
{
if (index > dataGridView1.Rows.Count - 2)
{
dataGridView1.Rows.Add();
}
dataGridView1.Rows[index].Cells[serialIndex].Value = index.ToString();
dataGridView1.Rows[index].Cells[fileNameIndex].Value = reader["name"].ToString();
dataGridView1.Rows[index].Cells[pathIndex].Value = reader["filePath"].ToString();
++index;
}
}
}
protected override bool CreateTable()
{
try
{
SQLiteDBHelper db = null;
string sql = null;
db = new SQLiteDBHelper(datebaseName);
if (db.IsTableExist(tableName))
{
db.DeleteTable(tableName);
}
sql = @"CREATE TABLE file_name(
name TEXT,
property1 TEXT,
property2 TEXT,
property3 TEXT,
property4 TEXT,
property5 TEXT,
property6 TEXT,
property7 TEXT,
property8 TEXT,
property9 TEXT,
property10 TEXT)";
db.ExecuteNonQuery(sql, null);
}
catch (Exception)
{
return(false);
}
return(true);
}