using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Collections;
using PBCXKZT.mode;
namespace PBCXKZT.cs
{
//SQLLite数据库管理类
public class SQLiteManage
{
/// <summary>
/// 获取数据库类型
/// </summary>
/// <param name="objInfo"></param>
/// <returns></returns>
private static DbType GetDbType(object pValue)
{
DbType Mytype = new DbType();
switch (pValue.GetType().ToString())
{
case "System.String":
Mytype = DbType.String;
break;
case "System.Int32":
Mytype = DbType.Int32;
break;
case "System.Int64":
Mytype = DbType.Int64;
break;
case "System.DateTime":
Mytype = DbType.DateTime;
break;
case "System.Decimal":
Mytype = DbType.Decimal;
break;
case "System.Double":
Mytype = DbType.Double;
break;
case "System.Byte[]":
Mytype = DbType.Binary;
break;
case "System.Byte":
Mytype = DbType.Binary;
break;
default:
Mytype = DbType.String;
break;
}
return Mytype;
}
/// <summary>
/// Connection
/// </summary>
public SQLiteConnection MySQLiteConnection = null;
/// <summary>
/// 数据库存储路径
/// </summary>
public string SQLiteDBPath
{
get
{
return _SQLiteDBPath;
}
}
public string _SQLiteDBPath = string.Empty;
/// <summary>
/// 数据库标志
/// </summary>
public string SQLiteFlag = string.Empty;
/// <summary>
/// 构造函数 .sqlite文件全路径
/// </summary>
public SQLiteManage(string pPath)
{
_SQLiteDBPath = pPath;
InitializeSQLite();
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="pPath">完整的Conn连接串</param>
public SQLiteManage(string pPath)
{
InitializeSQLite(pPath);
}
private void InitializeSQLite()
{
try
{
string strConn = "Data Source=" + SQLiteDBPath + ";";
MySQLiteConnection = new SQLiteConnection(strConn);
}
catch (Exception ex)
{
LogManage L = new LogManage();
L.Save(ex, 1);
throw ex;
}
}
private void InitializeSQLite(string pPath)
{
try
{
MySQLiteConnection = new SQLiteConnection(pPath);
}
catch (Exception ex)
{
LogManage L = new LogManage();
L.Save(ex, 1);
throw ex;
}
}
/// <summary>
/// 创建sqlite文件
/// </summary>
/// <param name="pPath"></param>
/// <returns></returns>
public static bool CreateSQLiteFile(string pPath)
{
try
{
if (File.Exists(pPath))
{
File.Delete(pPath);
}
SQLiteConnection.CreateFile(pPath);
return true;
}
catch (Exception ex)
{
LogManage L = new LogManage();
L.Save(ex, 1);
throw ex;
}
}
/// <summary>
/// 打开连接
/// </summary>
public void Open()
{
try
{
if (MySQLiteConnection.State == ConnectionState.Closed)
{
MySQLiteConnection.Open();
}
}
catch (Exception ex)
{
LogManage L = new LogManage();
L.Save(ex, 1);
throw ex;
}
}
/// <summary>
/// 关闭连接
/// </summary>
public void Close()
{
try
{
if (MySQLiteConnection.State == ConnectionState.Open)
{
MySQLiteConnection.Close();
}
}
catch (Exception ex)
{
LogManage L = new LogManage();
L.Save(ex, 1);
throw ex;
}
}
/// <summary>
/// 检测表是否存在于库中
/// </summary>
/// <param name="pTableName">表名</param>
/// <returns></returns>
public bool HasTable(string pTableName)
{
bool booRet = false;
string strSQL = "";
try
{
strSQL = "SELECT * FROM " + pTableName + " WHERE 1=2";
using (SQLiteDataAdapter apt = new SQLiteDataAdapter(strSQL, MySQLiteConnection))
{
DataTable DT = new DataTable();
apt.Fill(DT);
DT.Dispose();
apt.Dispose();
booRet = true;
}
}
catch
{
booRet = false;
}
return booRet;
}
/// <summary>
/// 查询返回DataTable
/// </summary>
/// <param name="pSQL"></param>
/// <returns>DataTable</returns>
public DataTable GetDataTable(string pSQL)
{
try
{
DataTable DT = new DataTable();
SQLiteDataAdapter apt = new SQLiteDataAdapter(pSQL,MySQLiteConnection);
apt.Fill(DT);
return DT;
}
catch (Exception ex)
{
LogManage L = new LogManage();
L.Save(ex, 1);
throw ex;
}
}
/// <summary>
/// 查询返回DataSet
/// </summary>
/// <param name="pSQL"></param>
/// <returns>DataTable</returns>
public DataSet GetDataSet(string pSQL)
{
try
{
DataSet DS = new DataSet();
SQLiteDataAdapter apt = new SQLiteDataAdapter(pSQL, MySQLiteConnection);
apt.Fill(DS);
return DS;
}
catch (Exception ex)
{
LogManage L = new LogManage();
L.Save(ex, 1);
throw ex;
}
}
/// <summary>
/// 返回第一行第一列
/// </summary>
/// <param name="pSQL"></param>
/// <returns></returns>
public object GetSingle(string pSQL)
{
object objRet = "";
try
{
//DataTable DT = new DataTable();
//SQLiteDataAdapter apt = new SQLiteDataAdapter(pSQL, MySQLiteConnection);
//apt.Fill(DT);
//if (DT != null && DT.Rows.Count > 0)
//{
// return DT.Rows[0][0] == DBNull.Value ? "" : DT.Rows[0][0].ToString();
//}
SQLiteCommand comm = new SQLiteCommand(pSQL, MySQLiteConnection);