public void UsingSafeConnection(string sqlStr, Action <SQLiteDataReader> callBack)
{
var sql = new SQLiteString(sqlStr);
using (SQLiteConnection con = new SQLiteConnection(DataSource))
{
con.Open();
using (SQLiteCommand com = new SQLiteCommand(con))
{
try
{
com.CommandText = sql.SqlText;
sql.SetCommand(com);
var reader = com.ExecuteReader();
callBack(reader);
reader.Close();
}
catch (Exception ex)
{
throw new Exception(string.Format("SQLite database [{0}] occurse errors", DataSource), ex);
}
finally
{
con.Close();
con.Dispose();
}
}
}
}
public void Find(string sql, Action <dynamic> callback)
{
try
{
var s = new SQLiteString(sql);
using (SQLiteConnection con = new SQLiteConnection(DataSource))
{
con.Open();
SQLiteCommand com = new SQLiteCommand(con);
s.SetCommand(com);
using (var reader = com.ExecuteReader())
{
while (reader.Read())
{
callback(reader.ToDynamic());
}
reader.Close();
}
con.Close();
}
}
catch
{
}
}
public void UsingSafeConnection(string sqlStr, Action <DBDataReader> callBack)
{
var sql = new SQLiteString(sqlStr);
using (SQLiteConnection con = new SQLiteConnection(DataSource))
{
con.Open();
using (SQLiteCommand com = new SQLiteCommand(con))
{
try
{
com.CommandText = sql.SqlText;
sql.SetCommand(com);
var reader = com.ExecuteReader();
callBack(new DBDataReader(reader));
reader.Close();
}
catch (Exception ex)
{
Log4NetService.LoggerManagerSingle.Instance.Error(ex, string.Format("SQLite database [{0}] occurse errors", DataSource));
}
finally
{
con.Close();
con.Dispose();
}
}
}
}
/// <summary>
/// 执行sql，返回首行首列的值
/// </summary>
public string ExecuteScalar(SQLiteString sql)
{
string result = string.Empty;
UsingSafeConnection(com =>
{
com.CommandText = sql.SqlText;
sql.SetCommand(com);
result = com.ExecuteScalar().ToSafeString();
});
return(result);
}
/// <summary>
/// 执行Sql，返回受影响的行数
/// </summary>
public int ExecuteNonQuery(SQLiteString sql)
{
int result = 0;
UsingSafeConnection(com =>
{
com.CommandText = sql.SqlText;
sql.SetCommand(com);
result = com.ExecuteNonQuery();
});
return(result);
}
/// <summary>
/// 执行sql，获取动态类型数据集合
/// </summary>
public List <dynamic> FindByExistingConnection(SQLiteString sql)
{
List <dynamic> items = new List <dynamic>();
UsingExistingConnection(sql, (reader) =>
{
while (reader.Read())
{
items.Add(reader.ToDynamic());
}
});
return(items);
}
public void UsingExistingConnection(SQLiteString sql, Action <SQLiteDataReader> callBack)
{
using (SQLiteCommand com = new SQLiteCommand(ExistingConnection))
{
try
{
com.CommandText = sql.SqlText;
sql.SetCommand(com);
var reader = com.ExecuteReader();
callBack(reader);
}
catch (Exception ex)
{
throw new Exception(string.Format("SQLite database [{0}] occurse errors", DataSource), ex);
}
}
}
/// <summary>
/// 外部不能调用该方法！！！
/// 否则可能在迭代的时候报错！
/// 请使用安全的 public IEnumerable<dynamic> Find(string sql) 和 public IEnumerable<dynamic> Find(SQLiteString sql)
/// 暂时不用该方法，因为数据库连接的释放问题，可能会导致文件长期呗占用
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
//private IEnumerable<dynamic> FindData(SQLiteString sql)
//{
// SQLiteConnection con = new SQLiteConnection(DataSource);
// con.Open();
// SQLiteCommand com = new SQLiteCommand(con);
// sql.SetCommand(com);
// var reader = com.ExecuteReader();
// while (reader.Read())
// {
// yield return reader.ToDynamic();
// }
// reader.Close();
// con.Close();
//}
public List <dynamic> FindData(SQLiteString sql)
{
try
{
List <dynamic> items = new List <dynamic>();
UsingSafeConnection(sql, (reader) =>
{
while (reader.Read())
{
items.Add(reader.ToDynamic());
}
});
return(items);
}
catch
{
return(new List <dynamic>());
}
}
public IEnumerable <dynamic> Find(SQLiteString sql)
{
try
{
var items = FindData(sql);
if (items.Any())
{
return(items);
}
else
{
return(new List <dynamic>());
}
}
catch
{
return(new List <dynamic>());
}
}