SQLiteHelpers.cs
using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Data;
namespace WaveAnalysisToolCS.SQLConn
{
class SQLiteHelpers
{
public string ConnectionString { get; set; }
public SQLiteHelpers(String conStr)
{
ConnectionString = conStr;
}
private void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params object[] p)
{
cmd.Parameters.Clear();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 30;
if (p != null)
{
foreach (object parm in p)
cmd.Parameters.AddWithValue("@vibdata", parm);//@vibdata
}
}
public System.Data.DataTable ExecuteQueryDT(string cmdText, params object[] p)
{
DataTable dt = new DataTable();
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
using (SQLiteCommand command = new SQLiteCommand())
{
DataSet ds = new DataSet();
PrepareCommand(command, conn, cmdText, p);
SQLiteDataAdapter da = new SQLiteDataAdapter(command);
da.Fill(ds);
if(ds.Tables.Count > 0)
dt = ds.Tables[0];
}
conn.Close();
conn.Dispose();
}
return dt;
}
public System.Data.DataSet ExecuteQuery(string cmdText, params object[] p)
{
DataSet ds = new DataSet();
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
using (SQLiteCommand command = new SQLiteCommand())
{
PrepareCommand(command, conn, cmdText, p);
SQLiteDataAdapter da = new SQLiteDataAdapter(command);
da.Fill(ds);
}
conn.Close();
conn.Dispose();
}
return ds;
}
public int ExecuteNonQuery(string cmdText, params object[] p)
{
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
int ret;
using (SQLiteCommand command = new SQLiteCommand())
{
PrepareCommand(command, conn, cmdText, p);
ret = command.ExecuteNonQuery();
}
conn.Close();
conn.Dispose();
return ret;
}
}
/**
本方法不能使用 因为连接关闭后,SQLiteDataReader自动失效
* */
public SQLiteDataReader ExecuteReader(string cmdText, params object[] p)
{
SQLiteDataReader dr;
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
using (SQLiteCommand command = new SQLiteCommand())
{
PrepareCommand(command, conn, cmdText, p);
dr = command.ExecuteReader(CommandBehavior.CloseConnection);
}
conn.Close();
conn.Dispose();
}
return dr;
}
public object ExecuteScalar(string cmdText, params object[] p)
{
object objScalar = new object();
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
using (SQLiteCommand command = new SQLiteCommand())
{
PrepareCommand(command, conn, cmdText, p);
objScalar = command.ExecuteScalar();
}
conn.Close();
conn.Dispose();
}
return objScalar;
}
}
}
DBConn.cs
using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Windows.Forms;
using System.Data;
namespace WaveAnalysisToolCS.SQLConn
{
class DBConn
{
public SQLiteHelpers GetDBConn(string DBName)
{
SQLiteHelpers connDB = new SQLiteHelpers(GetConnectionString(DBName));
return connDB;
}
private String GetConnectionString(String dbPath)
{
return "Data Source=" + dbPath + ";" + "Version=3;New=False;Compress=True;";
}
public float[] GetVibDataFromDB(string dbname,string sql)
{
SQLiteHelpers sqlite = new SQLiteHelpers(GetConnectionString(dbname));
DataTable DTSample = sqlite.ExecuteQueryDT(sql, null);
if (DTSample.Rows.Count > 0)
{
byte[] testb = (byte[])DTSample.Rows[0]["sData"];
int floatlength = testb.Length / 4;
float[] vibSampleF = new float[floatlength];
vibSampleF = Const.ConvertToFloatArray(testb);
return vibSampleF;
}
else
return null;
}
public float[] GetVibDataFromDBTemp(string dbname, string sql)
{
SQLiteHelpers sqlite = new SQLiteHelpers(GetConnectionString(dbname));
DataTable DTSample = sqlite.ExecuteQueryDT(sql, null);
if (DTSample.Rows.Count > 0)
{
byte[] testb = (byte[])DTSample.Rows[0]["vibdata"];
int floatlength = testb.Length / 4;
float[] vibSampleF = new float[floatlength];
vibSampleF = Const.ConvertToFloatArray(testb);
return vibSampleF;
}
else
return null;
}
}
}