public n_create_sql(string connStr)
{
sqlite = new AccessDB(connStr);
}
/// <summary>
/// 返回首行字符串
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static string ExecuteScalar(string sql)
{
AccessDB db = new AccessDB(connStr);
string str = string.Empty;
try
{
str = db.ExecuteQueryString(sql);
return str;
}
catch (Exception e)
{
SqlErr = e.Message;
return str;
}
finally
{
db.CloseSqlConnection();
}
}
public n_create_sql()
{
sqlite = new AccessDB();
}
/// <summary>
/// 返回受影响行数
/// </summary>
/// <param name="sql"></param>
/// <param name="valuse"></param>
/// <returns></returns>
public static int ExecuteNum(string sql, SqliteParameter[] valuse)
{
if (sql.ToLower().IndexOf("count(*)") > 0)
{
try
{
return int.Parse(ExecuteScalar(sql, valuse));
}
catch { }
}
AccessDB db = new AccessDB(connStr);
int count;
try
{
count = db.ExecuteQueryNum(sql, valuse);
return count;
}
catch
{
return -1;
}
finally
{
db.CloseSqlConnection();
}
}
/// <summary>
/// 返回受影响行数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int ExecuteNum(string sql)
{
if (sql.ToLower().IndexOf("count(*)") > 0)
{
try
{
return int.Parse(ExecuteScalar(sql));
}
catch { }
}
AccessDB db = new AccessDB(connStr);
int count;
try
{
count = db.ExecuteQueryNum(sql);
return count;
}
catch (Exception e)
{
SqlErr = e.Message;
return -1;
}
finally
{
db.CloseSqlConnection();
}
}
/// <summary>
/// 分页方法
/// </summary>
/// <param name="sql"></param>
/// <param name="ai_PageSize">每页行数</param>
/// <param name="ai_PageIndex">当前页数</param>
/// <param name="ai_totalrow">总行数</param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string sql, int ai_PageSize, int ai_PageIndex, ref int ai_totalrow)
{
if (ai_PageSize > 20000)
{
ai_PageSize = 20000;
}
DataTable dt = new DataTable();
AccessDB db = new AccessDB(connStr);
if (sql.IndexOf("limit") > 0)
{
sql = sql.Substring(0, sql.IndexOf("limit"));
}
try
{
ai_totalrow = db.ExecuteQueryNum(sql);
if (ai_PageIndex == 1)
{
sql += " limit 0," + ai_PageSize;
}
else
{
sql += " limit " + (ai_PageIndex * ai_PageSize - 1) + "," + ai_PageSize;
}
dt = db.ExecuteQueryDataTable(sql);
return dt;
}
catch (Exception e)
{
SqlErr = e.Message;
return dt;
}
finally
{
db.CloseSqlConnection();
}
}
/// <summary>
/// 根据查询语句返回DataTable
/// </summary>
/// <param name="sql"></param>
/// <param name="as_where1">如：@name=sa</param>
/// <param name="as_where2"></param>
/// <param name="as_where3"></param>
/// <param name="as_where4"></param>
/// <param name="as_where5"></param>
/// <param name="as_where6"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string sql, string as_where1 = "", string as_where2 = "", string as_where3 = "", string as_where4 = "", string as_where5 = "", string as_where6 = "")
{
sql = of_AllSql(sql, as_where1, as_where2, as_where3, as_where4, as_where5, as_where6);
DataTable dt = new DataTable();
AccessDB db = new AccessDB(connStr);
try
{
dt = db.ExecuteQueryDataTable(sql);
return dt;
}
catch (Exception e)
{
SqlErr = e.Message;
return dt;
}
finally
{
db.CloseSqlConnection();
}
}
/// <summary>
/// 根据查询语句返回DataTable
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string sql)
{
DataTable dt = new DataTable();
AccessDB db = new AccessDB(connStr);
try
{
dt = db.ExecuteQueryDataTable(sql);
return dt;
}
catch (Exception e)
{
SqlErr = e.Message;
return dt;
}
finally
{
db.CloseSqlConnection();
}
}
/// <summary>
/// 判断是否存在表
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
public static Boolean of_ExistTable(string tableName)
{
string sql = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='" + tableName + "'";
AccessDB db = new AccessDB(connStr);
try
{
using (SqliteDataReader reader = db.ExecuteQuery(sql))
{
while (reader.Read())
{
string row = reader[0].ToString();
if (row == "1")
{
return true;
}
else
{
return false;
}
}
}
}
catch (Exception e)
{
SqlErr = e.Message;
}
finally
{
db.CloseSqlConnection();
}
return false;
}
private void Dispose()
{
tableName = null;
Col = null;
Set = null;
if (sqlite != null)
{
sqlite.CloseSqlConnection();
}
sqlite = null;
if (reader != null)
{
reader.Dispose();
}
reader = null;
}
