发现还写了SqLite的类，，但是主要都写了读，我的数据库里有时间，所以主要就是读一段时间内的
using System;
using System.Data;
using System.Data.SQLite;
namespace ChartDemo
{
/// <summary>
/// SQLite 操作类
/// </summary>
public class SqLiteHelper
{
private string _dbName = "";
private SQLiteConnection _SQLiteConn = null; //连接对象
private SQLiteTransaction _SQLiteTrans = null; //事务对象
private bool _IsRunTrans = false; //事务运行标识
private string _SQLiteConnString = null; //连接字符串
private bool _AutoCommit = false; //事务自动提交标识
static public string dbPath;
static public string table_Path;
public string SQLiteConnString
{
set { this._SQLiteConnString = value; }
get { return this._SQLiteConnString; }
}
public SqLiteHelper(string dbPath)
{
this._dbName = dbPath;
this._SQLiteConnString = "Data Source=" + dbPath;
}
/// <summary>
/// 新建数据库文件
/// </summary>
/// <param name="dbPath">数据库文件路径及名称</param>
/// <returns>新建成功，返回true，否则返回false</returns>
static public Boolean NewDbFile(string dbPath)
{
try
{
SQLiteConnection.CreateFile(dbPath);
return true;
}
catch (Exception ex)
{
throw new Exception("新建数据库文件" + dbPath + "失败：" + ex.Message);
}
}
//读取该name的所有数据
static public DataSet ReadPart(string name)
{
DataSet ds = new DataSet();
try
{
//打开要连接的数据链接
SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
if (sqliteConn.State != System.Data.ConnectionState.Open)
{
int t = 1;
sqliteConn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = sqliteConn;
//sqlite的between.and语句包括between不包括and
cmd.CommandText = "SELECT * FROM " + table_Path + " WHERE "+name+"= '" + t + "' ";
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
adapter.Fill(ds);
return ds;
}
else
{
return null;
}
}
catch (Exception ex)
{
return null;
}
}
//查询不合格产品
static public DataSet Readfail(DateTime dt1, DateTime dt2, int dec_type)
{
DataSet ds = new DataSet();
try
{
//打开要连接的数据链接
SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
if (sqliteConn.State != System.Data.ConnectionState.Open)
{
string str1 = dt1.ToString("yyyy - MM - dd HH: mm: ss");
string str2 = dt2.ToString("yyyy - MM - dd HH: mm: ss");
sqliteConn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = sqliteConn;
//sqlite的between.and语句包括between不包括and
cmd.CommandText = "SELECT* FROM " + table_Path + " WHERE Flag = '" + 0 + "' AND Time >='" + str1 + "' and Time <= '" + str2 + "' and Type='" + dec_type + "'";
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
adapter.Fill(ds);
return ds;
}
else
{
return null;
}
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 查询指定时间内的数据信息
/// </summary>
/// <param name="dt1"></param>
/// <param name="dt2"></param>
/// <returns></returns>
static public DataSet ReadSelected(DateTime dt1, DateTime dt2, int dec_type)
{
DataSet ds = new DataSet();
try
{
//打开要连接的数据链接
SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
if (sqliteConn.State != System.Data.ConnectionState.Open)
{
string str1 = dt1.ToString("yyyy - MM - dd HH: mm: ss");
string str2 = dt2.ToString("yyyy - MM - dd HH: mm: ss");
sqliteConn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = sqliteConn;
//sqlite的between.and语句包括between不包括and
cmd.CommandText = "SELECT* FROM " + table_Path + " WHERE Time >='" + str1 + "' and Time <= '" + str2 + "' and Type='" + dec_type + "' ";
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
adapter.Fill(ds);
return ds;
}
else
{
return null;
}
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 创建表
/// </summary>
/// <param name="dbPath">指定数据库文件</param>
/// <param name="tableName">表名称</param>
static public int NewTable(string tableName)
{
//打开要连接的数据链接
SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
try
{
if (sqliteConn.State != System.Data.ConnectionState.Open)
{
sqliteConn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = sqliteConn;
cmd.CommandText = "CREATE TABLE " + tableName + "(Id int,Data char,Flag int,Time Text,Type int)";
cmd.ExecuteNonQuery();
}
sqliteConn.Close();
return 1;
}
catch (Exception ex)
{
return 0;
}
}
/// <summary>
/// 打开当前数据库的连接
/// </summary>
/// <returns></returns>
public Boolean OpenDb()
{
try
{
this._SQLiteConn = new SQLiteConnection(this._SQLiteConnString);
this._SQLiteConn.Open();
return true;
}
catch (Exception ex)
{
throw new Exception("打开数据库：" + _dbName + "的连接失败：" + ex.Message);
}
}
/// <summary>
/// 打开指定数据库的连接
/// </summary>
/// <param name="dbPath">数据库路径</param>
/// <returns></returns>
public Boolean OpenDb(string dbPath)
{
try
{
string sqliteConnString = "Data Source=" + dbPath;
this._SQLiteConn = new SQLiteConnection(sqliteConnString);
this._dbName = dbPath;
this._SQLiteConnString = sqliteConnString;
this._SQLiteConn.Open();
return true;
}
catch (Exception ex)
{
throw new Exception("打开数据库：" + dbPath + "的连接失败：" + ex.Message);
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void CloseDb()
{
if (this._SQLiteConn != null && this._SQLiteConn.State != ConnectionState.Closed)
{
if (this._IsRunTrans && this._AutoCommit)
{
this.Commit();
}
this._SQLiteConn.Close();
this._SQLiteConn = null;
}
}
/// <summary>
/// 开始数据库事务
/// </summary>
public void BeginTransaction()
{
this._SQLiteConn.BeginTransaction();
this._IsRunTrans = true;
}
/// <summary>
/// 开始数据库事务
/// </summary>
/// <param name="isoLevel">事务锁级别</param>
public void BeginTransaction(IsolationLevel isoLevel)
{
this._SQLiteConn.BeginTransaction(isoLevel);
this._IsRunTrans = true;
}
/// <summary>
/// 添加数据
/// </summary>
/// <param name="sqlData"></param>
static public void Add(SqlData sqlData)
{
//打开要连接的数据链接
SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
if (sqliteConn.State != System.Data.ConnectionState.Open)
{
sqliteConn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = sqliteConn;
string str1 = sqlData.Time.ToString("yyyy - MM - dd HH: mm: ss");
cmd.CommandText = "insert into " + table_Path + "(Id,Data,Flag,Time,Type) values('" + sqlData.id + " ', '" + sqlData.data + "','" + sqlData.flag + "' ,'" + str1 + "' ,'" + sqlData.dect_type + "')";
cmd.ExecuteNonQuery();
}
sqliteConn.Close();
}
/// <summary>
/// 读取一段id之间的数据
/// </summary>
/// <param name="ini"></param>
/// <param name="end"></param>
/// <returns></returns>
static public DataSet ReadData(int ini, int end)
{
DataSet ds = new DataSet();
try
{
//打开要连接的数据链接
SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
if (sqliteConn.State != System.Data.ConnectionState.Open)
{
string poi = ini.ToString();
string mo = end.ToString();
sqliteConn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = sqliteConn;
//sqlite的between.and语句包括between不包括and
cmd.CommandText = "SELECT* FROM" + table_Path + "WHERE Id >= '" + ini + "' and Id <= '" + end + "'";
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
adapter.Fill(ds);
return ds;
}
else
{
return null;
}
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 读取一段时间内的数据
/// </summary>
/// <param name="dt1"></param>
/// <param name="dt2"></param>
/// <returns></returns>
static public DataSet ReadOneday(DateTime dt1, DateTime dt2)
{
DataSet ds = new DataSet();
try
{
//打开要连接的数据链接
SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
if (sqliteConn.State != System.Data.ConnectionState.Open)
{
string str1 = dt1.ToString("yyyy - MM - dd HH: mm: ss");
string str2 = dt2.ToString("yyyy - MM - dd HH: mm: ss");
sqliteConn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = sqliteConn;
cmd.CommandText = "SELECT* FROM" + table_Path + "WHERE Time >='" + str2 + "' and Time <= '" + str1 + "'";
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
adapter.Fill(ds);
return ds;
}
else
{
return null;
}
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 读取数据库中的表名
/// </summary>
/// <returns></returns>
static public DataSet ReadTableName()
{
DataSet ds = new DataSet();
try
{
//打开要连接的数据链接
SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
if (sqliteConn.State != System.Data.ConnectionState.Open)
{
sqliteConn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = sqliteConn;
cmd.CommandText = "SELECT * FROM sqlite_master WHERE type='table' order by name";
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
adapter.Fill(ds);
return ds;
}
else
{
return null;
}
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 读取数据
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
static public string ReadDate(int id)
{
DataSet ds = new DataSet();
try
{
//打开要连接的数据链接
SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
if (sqliteConn.State != System.Data.ConnectionState.Open)
{
sqliteConn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = sqliteConn;
cmd.CommandText = "SELECT* FROM" + table_Path + "WHERE Id = '" + id + "' ";
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
adapter.Fill(ds);
return (ds.Tables[0].Rows[0][3]).ToString();
}
else
{
return null;
}
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 读取表的长度
/// </summary>
/// <returns></returns>
static public int DataLength()
{
DataSet ds = new DataSet();
try
{
//打开要连接的数据链接
SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
if (sqliteConn.State != System.Data.ConnectionState.Open)
{
sqliteConn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = sqliteConn;
cmd.CommandText = "SELECT * FROM " + table_Path;
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
adapter.Fill(ds);
return ds.Tables[0].Rows.Count;
}
else
{
return 0;
}
}
catch (Exception ex)
{
return 0;
}
}
/// <summary>
/// 删除表
/// </summary>
static public void dropTable(string str)
{
try
{
//打开要连接的数据链接
SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
if (sqliteConn.State != System.Data.ConnectionState.Open)
{
sqliteConn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = sqliteConn;
cmd.CommandText = "drop table " + str;
int I = cmd.ExecuteNonQuery();
}
else
{
return;
}
}
catch (Exception ex)
{
return;
}
}
/// <summary>
/// 提交当前挂起的事务
/// </summary>
public void Commit()
{
if (this._IsRunTrans)
{
this._SQLiteTrans.Commit();
this._IsRunTrans = false;
}
}
}
}
版权声明：本文为qq_42729780原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。