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.Tasks;
namespace FormSQLite
{
/// <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 Demo(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE)";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE Demo";
command.ExecuteNonQuery();
}
}
}
/// <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
}
}
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace FormSQLite
{
/// <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 Demo(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE)";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE Demo";
command.ExecuteNonQuery();
}
}
}
/// <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
}
}