95992828九五至尊2

C882828九五至尊手机版#操作SQLite数据库

三月 5th, 2019  |  882828九五至尊手机版

一.SqLite介绍:

一.SqLite介绍:

SQLite,
是一款轻型的数据库,遵从ACID(原子性(Atomicity)、一致性(Consistency)、隔断性(Isolation)、持久性
(Durability))的关系型数据库管理类别,它含有在三个相对小的C库中。它是D.RichardHipp建立的公有领域项目。它的统一筹划目标是嵌
入式的,而且最近一度在重重嵌入式产品中动用了它,它占用财富丰裕的低,在嵌入式设备中,恐怕只须求几百K的内部存款和储蓄器就够了。它亦可帮衬Windows/Linux/Unix等等主流的操作系统,同时能够跟很多程序语言相结合,比如
Tcl、C#、PHP、Java等,还有ODBC接口,同样比起Mysql、PostgreSQL那七款开源的社会风气名牌数据库管理体系来讲,它的处理速度
比她们都快。SQLite第三个Alpha版本诞生于3000年八月。
近年来风靡的本子是SQLite3。

SQLite,
是一款轻型的数据库,遵从ACID(原子性(Atomicity)、一致性(Consistency)、隔断性(Isolation)、持久性
(Durability))的关系型数据库管理种类,它富含在二个周旋小的C库中。它是D.RichardHipp建立的国有领域项目。它的宏图指标是嵌
入式的,而且近来曾经在重重嵌入式产品中选择了它,它占用财富相当低,在嵌入式设备中,恐怕只须求几百K的内部存款和储蓄器就够了。它能够补助Windows/Linux/Unix等等主流的操作系统,同时能够跟很多程序语言相结合,比如
Tcl、C#、PHP、Java等,还有ODBC接口,同样比起Mysql、PostgreSQL那七款开源的社会风气有名数据库管理种类来讲,它的处理速度
比她们都快。SQLite第一个Alpha版本诞生于三千年一月。
方今风行的版本是SQLite3。

二.SQLiteStudio 数据库管理工科具

二.SQLiteStudio 数据库管理工科具

下载SqlLiteStudio > http://sqlitestudio.pl/?act=download

下载SqlLiteStudio > http://sqlitestudio.pl/?act=download

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

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

882828九五至尊手机版 1

882828九五至尊手机版 2

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

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

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

开拓程序包管控台:安装 Install-Package System.Data.SQLite

882828九五至尊手机版 3882828九五至尊手机版 4

882828九五至尊手机版 5882828九五至尊手机版 6

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

View Code

View Code

找到SQLiteStudio基目录下的Test
SqlLite数据库文件位置:这里是:C:\882828九五至尊手机版,Users\xxx\Downloads\sqlitestudio-3.1.0\SQLiteStudio\Test

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

882828九五至尊手机版 7882828九五至尊手机版 8

882828九五至尊手机版 9882828九五至尊手机版 10

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

View Code

View Code

882828九五至尊手机版 11\
882828九五至尊手机版 12

882828九五至尊手机版 13\
882828九五至尊手机版 14

相关文章

Your Comments

近期评论

    功能


    网站地图xml地图