C# 封装SQLite实现

本文详细介绍了如何使用C#结合SQLite数据库进行数据查询、更新、删除等操作,并通过SQL查询实现数据提取与分析,提供了高效的数据处理解决方案。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 

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;
        }


    }
}


 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值