C#操作SQLite数据库

刺骨的言语ヽ痛彻心扉 2022-09-21 15:29 195阅读 0赞

一.SqLite介绍：

SQLite，是一款轻型的数据库，遵守ACID（原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability））的关系型数据库管理系统，它包含在一个相对小的C库中。它是D.RichardHipp建立的公有领域项目。它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C\#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲，它的处理速度比他们都快。SQLite第一个Alpha版本诞生于2000年5月。 目前最新的版本是SQLite3。

二.SQLiteStudio 数据库管理工具

下载SqlLiteStudio > [http://sqlitestudio.pl/?act=download][http_sqlitestudio.pl_act_download]

新建数据库Test,添加User表新增字段UserID,和UserName

![20160624154135697][]

三.新建项目控制台项目：

打开程序包管理控制台：安装 Install-Package System.Data.SQLite

using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SQLite;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace SqLiteDemo
    {
        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;
                }
            }
        } 
    }

找到SQLiteStudio基目录下的Test SqlLite数据库文件地址：这里是：C:\\Users\\xxx\\Downloads\\sqlitestudio-3.1.0\\SQLiteStudio\\Test

using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SQLite;
    using System.Linq;
    using System.Text;
    using System.Threading;
    using System.Threading.Tasks;
    
    namespace SqLiteDemo
    {
        class Program
        {
            //SELECT
            private static SQLiteDBHelper db = new SQLiteDBHelper(@"C:\Users\xxx\Downloads\sqlitestudio-3.1.0\SQLiteStudio\Test");
    
            static void Main(string[] args)
            {
                string userid = Guid.NewGuid().ToString();
    
                SELECT();
                Console.WriteLine("INSERT");
                INSERT(userid);
                SELECT();
    
                Console.WriteLine("UPDATE");
                UPDATE(userid);
                SELECT();
    
                Thread.Sleep(10000);
                Console.WriteLine("DELETE");
                DELETE();
                SELECT();
            }
    
            private static void SELECT(){
                DataTable dt= db.ExecuteDataTable("select * from User",null);
                foreach (DataRow item in dt.Rows)
                {
                    Console.WriteLine(item[0].ToString()+"\t"+item[1].ToString()+"\t");
                }
                if (dt.Rows.Count <= 0)
                {
                    Console.WriteLine("没有数据");
                    Console.WriteLine();
                }
            }
    
            private static void INSERT(string USERID)
            {
                SQLiteParameter[] parm = new SQLiteParameter[2];
                parm[0] = new SQLiteParameter("USERID", USERID);
                parm[1] = new SQLiteParameter("USERNAME", "何杨谊");
                int result = db.ExecuteNonQuery("INSERT INTO User(UserID,UserName) VALUES (@USERID,@USERNAME)", parm);
                if (result > 0)
                {
                    Console.WriteLine("INSERT TRUE");
                    Console.WriteLine();
                }
                else
                    Console.WriteLine("INSERT FALSE");
                
            }
    
            private static void UPDATE( string USERID)
            {
                SQLiteParameter[] parm = new SQLiteParameter[2];
                parm[0] = new SQLiteParameter("USERID", USERID);
                parm[1] = new SQLiteParameter("USERNAME", new Random().Next(0, 100));
                int result = db.ExecuteNonQuery("UPDATE User SET UserName=@USERNAME WHERE UserID=@USERID", parm);
                if (result > 0)
                {
                    Console.WriteLine("UPDATE 成功");
                    Console.WriteLine();
                }
                else
                    Console.WriteLine("UPDATE 失败");
            }
    
            private static void DELETE()
            {
                db.ExecuteNonQuery("DELETE FROM User", null);
                Console.WriteLine("删除成功");
                Console.WriteLine();
            }
        }
    }

![20160624155702159][]\\

![20160624155756050][]

[http_sqlitestudio.pl_act_download]: http://sqlitestudio.pl/?act=download
[20160624154135697]: /images/20220721/471ce07aa1244f24807ce7b1ca838606.png
[20160624155702159]: /images/20220721/f118c4ee320b4a04867ed57500d45fc4.png
[20160624155756050]: /images/20220721/750266290c16463d853eb0bea14a057e.png