针对System.Data.SQLite的数据库常规操作封装的通用类

csharp

浏览数:222

2019-1-7


SQLiteDBHelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Windwos_API
{
    class SQLiteDBHelper
    {
        private string connectionString = string.Empty;
        ///

        /// 构造函数
        ///

        ///
        ///SQLite数据库文件路径
        public SQLiteDBHelper(string dbPath)
        {
            this.connectionString = "Data Source=" + dbPath;
        }

        /// 创建SQLite数据库文件
        ///
        /// 要创建的SQLite数据库文件路径
        public static void CreateDB(string dbPath)
        {
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = "CREATE TABLE Demo(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE)";
                    command.ExecuteNonQuery();
                    command.CommandText = "DROP TABLE Demo";
                    command.ExecuteNonQuery();
                }
            }
        }
        ///

        /// 对SQLite数据库执行增删改操作,返回受影响的行数。
        ///

        /// 要执行的增删改的SQL语句
        /// 执行增删改语句所需要的参数,参数必须以它们在SQL语句中的顺序为准
        ///
        public int ExecuteNonQuery(string sql, SQLiteParameter[] parameters)
        {
            int affectedRows = 0;
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (DbTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = new SQLiteCommand(connection))
                    {
                        command.CommandText = sql;
                        if (parameters != null)
                        {
                            command.Parameters.AddRange(parameters);
                        }
                        affectedRows = command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
            }
            return affectedRows;
        }
        ///

        /// 执行一个查询语句,返回一个关联的SQLiteDataReader实例
        ///

        /// 要执行的查询语句
        /// 执行SQL查询语句所需要的参数,参数必须以它们在SQL语句中的顺序为准
        ///
        public SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] parameters)
        {
            SQLiteConnection connection = new SQLiteConnection(connectionString);
            SQLiteCommand command = new SQLiteCommand(sql, connection);
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }
            connection.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        ///

        /// 执行一个查询语句,返回一个包含查询结果的DataTable
        ///

        /// 要执行的查询语句
        /// 执行SQL查询语句所需要的参数,参数必须以它们在SQL语句中的顺序为准
        ///
        public DataTable ExecuteDataTable(string sql, SQLiteParameter[] parameters)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    return data;
                }
            }

        }
        ///

        /// 执行一个查询语句,返回查询结果的第一行第一列
        ///

        /// 要执行的查询语句
        /// 执行SQL查询语句所需要的参数,参数必须以它们在SQL语句中的顺序为准
        ///
        public Object ExecuteScalar(string sql, SQLiteParameter[] parameters)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    return data;
                }
            }
        }
        ///

        /// 查询数据库中的所有数据类型信息
        ///

        ///
        public DataTable GetSchema()
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                DataTable data = connection.GetSchema("TABLES");
                connection.Close();
                //foreach (DataColumn column in data.Columns)
                //{
                //        Console.WriteLine(column.ColumnName);
                //}
                return data;
            }
        }
    }
}