using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
namespace QuartzTaskScheduler
{
class SQLiteHelper
{
#region SQlite数据操作
/// <summary>
/// ConnectionString样例：Datasource=Test.db3;Pooling=true;FailIfMissing=false
/// </summary>
public static string ConnectionString { get; set; }
private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params object[] p)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Parameters.Clear();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 30;
if (p != null)
{
foreach (object parm in p)
cmd.Parameters.AddWithValue(string.Empty, parm);
}
}
public static DataSet ExecuteQuery(string cmdText, params object[] p)
{
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
using (SQLiteCommand command = new SQLiteCommand())
{
DataSet ds = new DataSet();
PrepareCommand(command, conn, cmdText, p);
SQLiteDataAdapter da = new SQLiteDataAdapter(command);
da.Fill(ds);
return ds;
}
}
}
public static int ExecuteNonQuery(string cmdText, params object[] p)
{
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
using (SQLiteCommand command = new SQLiteCommand())
{
PrepareCommand(command, conn, cmdText, p);
return command.ExecuteNonQuery();
}
}
}
public static SQLiteDataReader ExecuteReader(string cmdText, params object[] p)
{
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
using (SQLiteCommand command = new SQLiteCommand())
{
PrepareCommand(command, conn, cmdText, p);
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
}
}
public static object ExecuteScalar(string cmdText, params object[] p)
{
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
using (SQLiteCommand command = new SQLiteCommand())
{
PrepareCommand(command, conn, cmdText, p);
return command.ExecuteScalar();
}
}
}
/// <summary>
/// 执行SQl语句 返回执行的行数
/// </summary>
/// <param name="str">链接数据库的字符串</param>
/// <param name="SqlString">sql执行语句</param>
/// <param name="Param">参数</param>
public static int SQLiteCommand(string str, string SqlString, params SQLiteParameter[] Param)
{
try
{
SQLiteConnection slcn = new SQLiteConnection(str);
SQLiteCommand slcmd = slcn.CreateCommand();
PrepareCommand(slcmd, slcn, null, SqlString, Param);
return slcmd.ExecuteNonQuery();
}
catch (System.Data.SQLite.SQLiteException ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 执行SQl语句 返回DataSet
/// </summary>
/// <param name="str">链接数据库的字符串</param>
/// <param name="SqlString">sql执行语句</param>
/// <param name="Param">参数</param>
/// <returns>DataSet</returns>
public static DataSet SQLiteCommandDataSet(string str, string SqlString, params SQLiteParameter[] Param)
{
try
{
DataSet ds = new DataSet();
SQLiteConnection slcn = new SQLiteConnection(str);
SQLiteCommand slcmd = slcn.CreateCommand();
PrepareCommand(slcmd, slcn, null, SqlString, Param);
SQLiteDataAdapter sda = new SQLiteDataAdapter(slcmd);
slcmd.Parameters.Clear();
sda.Fill(ds);
sda.Dispose();
return ds;
}
catch (System.Data.SQLite.SQLiteException ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 执行SQl语句 返回数据集SQLiteDataReader
/// </summary>
/// <param name="str">链接数据库的字符串</param>
/// <param name="SqlString">sql执行语句</param>
/// <param name="Param">参数</param>
/// <returns>SQLiteDataReader</returns>
public static SQLiteDataReader SQLiteCommandDataReader(string str, string SqlString, params SQLiteParameter[] Param)
{
try
{
SQLiteConnection slcn = new SQLiteConnection(str);
SQLiteCommand slcmd = slcn.CreateCommand();
PrepareCommand(slcmd, slcn, null, SqlString, Param);
SQLiteDataReader myReader = slcmd.ExecuteReader();
slcmd.Parameters.Clear();
return myReader;
}
catch (System.Data.SQLite.SQLiteException ex)
{
throw new Exception(ex.Message);
}
}
public static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;//cmdType;
if (cmdParms != null)
{
foreach (SQLiteParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
#endregion
#region Commit To Database
public static void CommitToDataBase(string connectionString, DataSet ds, string sql)
{
try
{
if (!ds.HasChanges()) { return; }
using (SQLiteConnection conn = new SQLiteConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
using (SQLiteDataAdapter da = new SQLiteDataAdapter(sql, conn))
{
SQLiteCommand b = new SQLiteCommand();
da.Update(ds.Tables[0]);
ds.AcceptChanges();
}
}
}
catch (Exception) { throw; }
}
#endregion
#region Get Data Set
public static DataSet GetDataSet(string connectionString, string sql)
{
DataSet ds = new DataSet();
try
{
using (SQLiteConnection conn = new SQLiteConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
using (SQLiteDataAdapter da = new SQLiteDataAdapter(sql, conn))
{
da.Fill(ds);
}
}
}
catch (Exception) { throw; }
return ds;
}
#endregion
}
}
通用类：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
namespace QuartzTaskScheduler
{
class TreeViewUtils
{
//创建根节点
public static void BindTreeVew(DataSet ds, TreeView treeView, bool isExpanded, string sSelectedData)
{
//将获取的数据转换为 一个DataTable
DataTable dt = ds.Tables[0];
treeView.Nodes.Clear();//清空 树的所有节点
//获取与排序顺序中的筛选器以及指定的状态相匹配的所有System.Data.DataRow 数组
DataRow[] rowlist = dt.Select("ParentID='0'");
if (rowlist.Length <= 0) return;
//创建跟节点
TreeNode rootNode = new TreeNode();
//设置根节点的属性
rootNode.Text = rowlist[0]["Name"].ToString();
//设置根节点的值
rootNode.Name = rowlist[0]["ID"].ToString();
//rootNode.Expanded = isExpanded;
//rootNode.Selected = true;
//添加跟节点
treeView.Nodes.Add(rootNode);
CreateChildNode(rootNode, dt, isExpanded, sSelectedData);
}
public static void CreateChildNode(TreeNode parentNode, DataTable dt, bool isExpanded, string sSelecedData)
{
//选择数据，添加排序表达市
DataRow[] rowList = dt.Select("ParentID=" + Convert.ToInt32(parentNode.Name));
foreach (DataRow row in rowList)
{
//创建一个新的节点了
TreeNode node = new TreeNode();
node.Text = row["Name"].ToString();
node.Name = row["ID"].ToString();
//node.Expand = isExpanded;
//if (node.Name == sSelecedData)
//{
// node.Selected = true;
//}
//parentNode.ChildNodes.Add(node);
parentNode.Nodes.Add(node);
//递归调用，创建其他节点
CreateChildNode(node, dt, isExpanded, sSelecedData);
}
}
}
}
调用：
//获取SQlite数据可定义的连接字符串
string DBConStr = string.Format("Data Source={0}", Application.ExecutablePath.Substring(0, Application.ExecutablePath.LastIndexOf("\\")) + "\\SQLiteDB.db");
private void initTree()
{
DataSet ds = new DataSet();
string sqlStr = string.Format("select * from TaskScheduler");
ds = SQLiteHelper.SQLiteCommandDataSet(DBConStr, sqlStr, null);
TreeViewUtils.BindTreeVew(ds, treeView1,true,"0");
}
SQLiteHelper类：