iojsioi 发表于 2016-11-30 08:11:22

C#操作SQLite增删改模板<转载>

using System;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
namespace SQLiteQueryBrowser
{
    /// <summary>
    /// 说明:这是一个针对System.Data.SQLite的数据库常规操作封装的通用类。
    /// 作者:zhoufoxcn(周公)
    /// 日期:2010-04-01
    /// Blog:http://zhoufoxcn.blog.iyunv.com or http://blog.csdn.net/zhoufoxcn
    /// Version:0.1
    /// </summary>
    public class SQLiteDBHelper
    {
        private string connectionString = string.Empty;
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="dbPath">SQLite数据库文件路径</param>
        public SQLiteDBHelper(string dbPath)
        {
            this.connectionString = "Data Source=" + dbPath;
        }
        /// <summary>
        /// 创建SQLite数据库文件
        /// </summary>
        /// <param name="dbPath">要创建的SQLite数据库文件路径</param>
        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();
                }
            }
        }
        /// <summary>
        /// 对SQLite数据库执行增删改操作,返回受影响的行数。
        /// </summary>
        /// <param name="sql">要执行的增删改的SQL语句</param>
        /// <param name="parameters">执行增删改语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param>
        /// <returns></returns>
        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;
        }
        /// <summary>
        /// 执行一个查询语句,返回一个关联的SQLiteDataReader实例
        /// </summary>
        /// <param name="sql">要执行的查询语句</param>
        /// <param name="parameters">执行SQL查询语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param>
        /// <returns></returns>
        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);
        }
        /// <summary>
        /// 执行一个查询语句,返回一个包含查询结果的DataTable
        /// </summary>
        /// <param name="sql">要执行的查询语句</param>
        /// <param name="parameters">执行SQL查询语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param>
        /// <returns></returns>
        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;
                }
            }
 
        }
        /// <summary>
        /// 执行一个查询语句,返回查询结果的第一行第一列
        /// </summary>
        /// <param name="sql">要执行的查询语句</param>
        /// <param name="parameters">执行SQL查询语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param>
        /// <returns></returns>
        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;
                }
            }
        }
        /// <summary>
        /// 查询数据库中的所有数据类型信息
        /// </summary>
        /// <returns></returns>
        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;
            }
        }
    }
}
 

完整的程序例子:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using SQLiteQueryBrowser;
namespace SQLiteDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            //CreateTable();
            //InsertData();
            ShowData();
            Console.ReadLine();
        }
        public static void CreateTable()
        {
            string dbPath = "D:\\Demo.db3";
            //如果不存在改数据库文件,则创建该数据库文件
            if (!System.IO.File.Exists(dbPath))
            {
                SQLiteDBHelper.CreateDB("D:\\Demo.db3");
            }
            SQLiteDBHelper db = new SQLiteDBHelper("D:\\Demo.db3");
            string sql = "CREATE TABLE Test3(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,Name char(3),TypeName varchar(50),addDate datetime,UpdateTime Date,Time time,Comments blob)";
            db.ExecuteNonQuery(sql, null);
        }
        public static void InsertData()
        {
            string sql = "INSERT INTO Test3(Name,TypeName,addDate,UpdateTime,Time,Comments)values(@Name,@TypeName,@addDate,@UpdateTime,@Time,@Comments)";
            SQLiteDBHelper db = new SQLiteDBHelper("D:\\Demo.db3");
            for (char c = "A"; c <= "Z"; c++)
            {
                for (int i = 0; i < 100; i++)
                {
                    SQLiteParameter[] parameters = new SQLiteParameter[]{
                                                 new SQLiteParameter("@Name",c+i.ToString()),
                                         new SQLiteParameter("@TypeName",c.ToString()),
                                         new SQLiteParameter("@addDate",DateTime.Now),
                                         new SQLiteParameter("@UpdateTime",DateTime.Now.Date),
                                         new SQLiteParameter("@Time",DateTime.Now.ToShortTimeString()),
                                         new SQLiteParameter("@Comments","Just a Test"+i)
                                         };
                    db.ExecuteNonQuery(sql, parameters);
                }
            }
        }
        public static void ShowData()
        {
            //查询从50条起的20条记录
            string sql = "select * from test3 order by id desc limit 50 offset 20";
            SQLiteDBHelper db = new SQLiteDBHelper("D:\\Demo.db3");
            using (SQLiteDataReader reader = db.ExecuteReader(sql, null))
            {
                while (reader.Read())
                {
                    Console.WriteLine("ID:{0},TypeName{1}", reader.GetInt64(0), reader.GetString(1));
                }
            }
        }
 
    }
}
 

在实际情况中,采用通用类大批量插入数据会有些慢,这是因为在System.Data.SQLite中的操作如果没有指定操作,则会被当做一个事物,如果需要一次性写入大量记录,则建议显式创建一个事物,在这个事务中完成所有的操作比较好,这样的话比每次操作创建一个事物的效率要提升很多。
最终利用VS2008提供的功能,可以看到里面的数据如下: 
需要说明的是在System.Data.SQLite中数据类型的规定不是很严格,从创建Test3表的SQL语句来看,表中addDate、UpdateTime、Time分别是DateTime、Date、Time类型字段,但实际上我们插入的时候没有按照这个规定,最终显示的结果也是尽量遵循数据库字段的定义。
总结
System.Data.SQLite确实是一个非常小巧精悍的数据库,作为对SQLite的封装(SQLite可以在Android等类型的手机上利用Java访问),它依然是体较小,同比性能高、内存消耗小、无需安装仅需一个dll就可以运行的优点(如果在Mobile手机上则需要两个文件),唯一的一个缺点是没有比较的GUI(图形用户界面),不过正因为如此它才得以体积小。
在实际开发中没有图形用户界面可能有些不便,我们可以使用VS来查看和操作数据,我自己也做了一个小东东,便于管理和维护数据,界面如下: 
如果你要开发数据量在10万条以下的应用,我建议你尝试使用一下System.Data.SQLite,它或许是一个不错的选择。
 
页: [1]
查看完整版本: C#操作SQLite增删改模板<转载>