162 lines
4.6 KiB
C#
162 lines
4.6 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
using System.Data.Common;
|
||
using System.Data.SQLite;
|
||
using System.Linq;
|
||
using System.Text;
|
||
using System.Threading;
|
||
|
||
|
||
namespace JJServer
|
||
{
|
||
/// <summary>
|
||
/// 说明：这是一个针对System.Data.SQLite的数据库常规操作封装的通用类。
|
||
/// </summary>
|
||
public class SQLiteDBHelper
|
||
{
|
||
/// <summary>
|
||
///
|
||
/// </summary>
|
||
public string connectionString = string.Empty;
|
||
|
||
|
||
/// <summary>
|
||
///
|
||
/// </summary>
|
||
public static SQLiteConnection connection = null;
|
||
|
||
|
||
/// <summary>
|
||
/// 构造函数
|
||
/// </summary>
|
||
/// <param name="dbPath">数据库文件路径</param>
|
||
public SQLiteDBHelper(string dbPath)
|
||
{
|
||
this.connectionString = "Data Source=" + dbPath;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 创建数据库文件
|
||
/// </summary>
|
||
/// <param name="dbPath"></param>
|
||
public static void CreateDB(string dbPath)
|
||
{
|
||
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath))
|
||
{
|
||
connection.Open();
|
||
using (SQLiteCommand command = new SQLiteCommand(connection))
|
||
{
|
||
//command.CommandText = "CREATE TABLE Tranlist(ID INTEGER PRIMARY KEY,Name TEXT,IDNo TEXT,Sex TEXT,Nation TEXT,Phone TEXT,Address TEXT,Remarks TEXT,Time TEXT)";
|
||
//command.ExecuteNonQuery();
|
||
//command.CommandText = "DROP TABLE Tranlist";
|
||
//command.ExecuteNonQuery();
|
||
bool ret = ExecuteCommand("CREATE TABLE Tranlist(ID INTEGER PRIMARY KEY,Name TEXT,IDNo TEXT,Sex TEXT,Nation TEXT,Phone TEXT,Address TEXT,Remarks TEXT,Time TEXT)");
|
||
|
||
}
|
||
}
|
||
}
|
||
|
||
|
||
/// <summary>
|
||
/// 打开连接
|
||
/// </summary>
|
||
/// <param name="dataSource"></param>
|
||
/// <param name="password"></param>
|
||
public void SetDataSource(string dataSource, string password)
|
||
{
|
||
connectionString = string.Format("Data Source={0};Pooling=true;FailIfMissing=false", dataSource);
|
||
|
||
|
||
connection = new SQLiteConnection(connectionString);
|
||
|
||
|
||
if (!string.IsNullOrEmpty(password))
|
||
{
|
||
//connection.SetPassword(password);
|
||
}
|
||
connection.Open();
|
||
}
|
||
|
||
|
||
#region 通用方法
|
||
/// <summary>
|
||
/// 查询表
|
||
/// </summary>
|
||
/// <param name="sqlStr"></param>
|
||
/// <returns></returns>
|
||
public static DataTable ExecQuery(string sqlStr)
|
||
{
|
||
DataTable dt = new DataTable();
|
||
try
|
||
{
|
||
SQLiteDataAdapter OraDa = new SQLiteDataAdapter(sqlStr, connection);
|
||
OraDa.Fill(dt);
|
||
return dt;
|
||
}
|
||
catch (SQLiteException e)
|
||
{
|
||
string s = e.Message;
|
||
return null;
|
||
}
|
||
}
|
||
|
||
|
||
/// <summary>
|
||
/// 增、删、改操作
|
||
/// </summary>
|
||
/// <param name="commandStr">sql语句</param>
|
||
/// <returns>是否成功</returns>
|
||
public static bool ExecuteCommand(string sqlStr)
|
||
{
|
||
using (SQLiteCommand cmd = new SQLiteCommand(sqlStr, connection))
|
||
{
|
||
try
|
||
{
|
||
if (cmd.ExecuteNonQuery() > 0)
|
||
{
|
||
return true;
|
||
}
|
||
else
|
||
{
|
||
return false;
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
ex.ToString();
|
||
return false;
|
||
}
|
||
}
|
||
}
|
||
|
||
|
||
/// <summary>
|
||
/// 根据查询语句，获取表中记录的条数 select count(*) from t_Developer
|
||
/// </summary>
|
||
/// <param name="sqlStr"></param>
|
||
/// <returns></returns>
|
||
public static int GetRecordCount(string sqlStr)
|
||
{
|
||
using (SQLiteCommand cmd = new SQLiteCommand(sqlStr, connection))
|
||
{
|
||
try
|
||
{
|
||
cmd.CommandText = sqlStr;
|
||
SQLiteDataReader dr = cmd.ExecuteReader();
|
||
if (dr.Read())
|
||
{
|
||
return dr.FieldCount;
|
||
}
|
||
return 0;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
e.ToString();
|
||
return 0;
|
||
}
|
||
}
|
||
}
|
||
#endregion
|
||
}
|
||
} |