话不多说了,直接上代码。
SQLite有一个明显的确定就是不支持并发。
public static class SQLiteHelper
{
//连接字符串
private static string connectStr = "Data Source={0};Version=3"; //链接字符串
private static string exceptionMsg = "";
public static void SetConnectStr(string str)
{
connectStr = string.Format(connectStr, str);
}
/// <summary>
/// 测试是否能够连接数据库
/// </summary>
/// <param name="msg"></param>
/// <returns></returns>
public static bool CheckConnectionDatabase(out string msg)
{
try
{
SQLiteConnection conn = new SQLiteConnection(connectStr);
conn.Open();
if (ConnectionState.Open == conn.State)
{
msg = "数据库已打开";
return true;
}
else
{
msg = "数据库已关闭!请检查数据库是否有打开";
return false;
}
}
catch (Exception ex)
{
msg = ex.ToString() + "\r\n请检查数据库是否有打开!!!!";
return false;
}
}
//方法
/// <summary>
/// 增删改 都可以
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="ps">sql语句中的参数</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQuery(string sql, params SQLiteParameter[] ps)
{
using (SQLiteConnection con = new SQLiteConnection(connectStr))
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
{
con.Open();
cmd.Parameters.AddRange(ps);
return cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 查询首行首列
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="ps">参数</param>
/// <returns>首行首列object</returns>
public static object ExecuteScalar(string sql, params SQLiteParameter[] ps)
{
using (SQLiteConnection con = new SQLiteConnection(connectStr))
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
{
con.Open();
cmd.Parameters.AddRange(ps);
return cmd.ExecuteScalar();
}
}
}
/// <summary>
/// 查询的
/// </summary>
/// <param name="sql"></param>
/// <param name="ps"></param>
/// <returns></returns>
public static SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] ps)
{
SQLiteConnection con = new SQLiteConnection(connectStr);
using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
{
if (ps != null)
{
cmd.Parameters.AddRange(ps);
}
try
{
con.Open();
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
exceptionMsg = ex.ToString();
con.Close();
con.Dispose();
throw ex;
}
}
}
/// <summary>
/// 查询的是一个表
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="ps">sql语句中的参数</param>
/// <returns>一个表</returns>
public static DataTable ExecuteTable(string sql, params SQLiteParameter[] ps)
{
DataTable dt = new DataTable();
using (SQLiteDataAdapter sda = new SQLiteDataAdapter(sql, connectStr))
{
if (ps != null)
{
sda.SelectCommand.Parameters.AddRange(ps);
}
try
{
sda.Fill(dt);
}
catch (Exception ex)
{
exceptionMsg = ex.ToString();
sda.Dispose();
}
}
return dt;
}
/// <summary>
/// 获取异常信息
/// </summary>
/// <returns></returns>
public static string GetExceptionMessage()
{
return exceptionMsg;
}
/// <summary>
/// 获取个表的最新ID
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public static int GetLastestId(string name)
{
string sql = string.Format("select IDENT_CURRENT('{0}');", name);
return Convert.ToInt32(ExecuteScalar(sql));
}
}
以上是sqlite 的增删改查需要用到的基础代码。希望对大家有帮助。