C# 封装SQLite实现

最新推荐文章于 2023-04-27 16:34:16 发布

原创最新推荐文章于 2023-04-27 16:34:16 发布 · 768 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sqlite #c# #command #string #dataset #object

编程语言专栏收录该内容

17 篇文章

订阅专栏

本文详细介绍了如何使用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"];

}
}