net core 中使用Microsoft.Data.Sqlite 来操作sqlite数据库

2021-03-18 乐帮网

sqlite netcore

下面直接上代码，以官方提供的Microsoft.Data.Sqlite来构建一个小的跨平台控制台应用程序，以演示SQLite库的用法。以对表“User”实现“CRUD”操作。示例代码使用的是net core 3.1控制台程序。所以你得首先创建一个项目基于net core 3.1的控制台程序。文章最后会提供源码下载

（1）新一个User实体类：

public class User
    {
        public int Id
        {
            get;
            set;
        }
        public string Username
        {
            get;
            set;
        }
        public string Email
        {
            get;
            set;
        }
        public string Password
        {
            get;
            set;
        }
    }

（2）在项目中通过NuGet添加Microsoft.Data.Sqlite包，并且写一个简单的扩展方法如下:

 /// <summary>
    /// https://lebang2020.cn/details/210318gea541rv.html
    /// </summary>
 public static class DbConnectionExtensions
    {
        public static int ExecuteNonQuery(this DbConnection connection, string commandText,IList<SqliteParameter> parameters=null, int timeout = 30)
        {
            var command = connection.CreateCommand();
            command.CommandTimeout = timeout;
            command.CommandText = commandText;
            if (parameters != null)
            {
                foreach(var p in parameters)
                {
                    command.Parameters.Add(p);
                }
            }
            return command.ExecuteNonQuery();
        }

        public static T ExecuteScalar<T>(this DbConnection connection, string commandText, IList<SqliteParameter> parameters = null, int timeout = 30) =>
            (T)connection.ExecuteScalar(commandText, parameters, timeout);

        private static object ExecuteScalar(this DbConnection connection, string commandText, IList<SqliteParameter> parameters, int timeout)
        {
            var command = connection.CreateCommand();
            command.CommandTimeout = timeout;
            command.CommandText = commandText;
            return command.ExecuteScalar();
        }

        public static DbDataReader ExecuteReader(this DbConnection connection, string commandText, IList<SqliteParameter> parameters = null)
        {
            var command = connection.CreateCommand();
            command.CommandText = commandText;
            if (parameters != null)
            {
                foreach (var p in parameters)
                {
                    command.Parameters.Add(p);
                }
            }
            return command.ExecuteReader();
        }


    }

（3）新建一个sqlite数据库操作类 UserRepository

public class UserRepository : IDisposable
    {
        DbConnection connection;

        public UserRepository(string connectionString)
        {
            connection = new SqliteConnection(connectionString);
        }

        private void OpenConnection()
        {
            if (connection.State != System.Data.ConnectionState.Open)
                connection.Open();
        }

        private void CloseConnection()
        {
            if (connection.State != System.Data.ConnectionState.Closed)
                connection.Close();
        }

        public void SelectFromUserTable()
        {

            string sql = @"SELECT UserName,Email from Users;";
            OpenConnection();
            var result = connection.ExecuteReader(sql);
            while (result.Read())
            {
                Console.WriteLine(string.Format("UserName: {0}", result.GetString(0)));
                Console.WriteLine(string.Format("Email: {0}", result.GetString(1)));
            }
        }

        public void DeleteFromUserTable()
        {
            string sql = @"DELETE FROM Users;";
            OpenConnection();
            connection.ExecuteNonQuery(sql);
        }

        public void InsertIntoUserTable(User user)
        {
            string sql = @"INSERT INTO Users
                            (Username, Email, Password)
                        VALUES
                            (@Username, @Email,@Password)";
            OpenConnection();
            connection.ExecuteNonQuery(sql, new List<SqliteParameter>
            {
                new SqliteParameter("@Username",user.Username),
                new SqliteParameter("@Email",user.Email),
                new SqliteParameter("@Password",user.Password),
            });
        }

        public void CreateUserTable()
        {
            string sql = @"
                        CREATE TABLE IF NOT EXISTS Users(
                            [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                            [Username] NVARCHAR(64) NOT NULL,
                            [Email] NVARCHAR(128) NOT NULL,
                            [Password] NVARCHAR(128) NOT NULL
                        )";
            OpenConnection();
            connection.ExecuteNonQuery(sql);
        }

        public User GetUserdById(int userId)
        {
            try
            {
               string sql = "SELECT * From Users WHERE Id = @UserId;";
                OpenConnection();
                var result = connection.ExecuteReader(sql,new List<SqliteParameter>
                {
                    new SqliteParameter("@UserId",userId)
                });
                if (result.Read())
                {
                    var user = new User
                    {
                        Id = result.GetInt32(0),
                        Username = result.GetString(1),
                        Email = result.GetString(2),
                        Password = result.GetString(3)
                    };
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }

            return null;
        }

        // Transaction Demo
        public void InsertMultipleWithTransaction()
        {
            OpenConnection();
            var transaction = connection.BeginTransaction();

            try
            {
                connection.ExecuteNonQuery(@"INSERT INTO Users(Username, Email, Password)
                            VALUES('admin1', 'testing1@gmail.com', 'test1')");
                connection.ExecuteNonQuery(@"INSERT INTO Users(Username, Email, Password)
                            VALUES('admin2', 'testing2@gmail.com', 'test2')");
                transaction.Commit();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
                transaction.Rollback();
            }
            finally
            {
                transaction.Dispose();
            }
        }

        public void Dispose()
        {
            CloseConnection();
        }
    }

4、在Program中调用操作类测试我们实现的功能

 Console.WriteLine("Sqlite Demo Begin!");

            string connectionString = "Data Source=./test.db";

            using (UserRepository userRepository =
                new UserRepository(connectionString))
            {
                Console.WriteLine("Creating user table\n");
                userRepository.CreateUserTable();

                Console.WriteLine("Inserting data to user table\n");
                userRepository.InsertIntoUserTable(new models.User() { Username="lebang2020.cn",Email="lebang@gmail.com",Password="123456"});

                Console.WriteLine("Selecting data\n");
                userRepository.SelectFromUserTable();

                Console.WriteLine("\nGet User By ID: 1\n");
                var user = userRepository.GetUserdById(1);
                if (user != null)
                {
                    Console.WriteLine("User Name: {0}", user.Username);
                    Console.WriteLine("Email: {0}", user.Email);
                }

                Console.WriteLine("\nDeleting data\n");
                userRepository.DeleteFromUserTable();

                Console.WriteLine("Selecting data\n");
                userRepository.SelectFromUserTable();

                Console.WriteLine("Inserting multiple data to user table\n");
                userRepository.InsertMultipleWithTransaction();

                Console.WriteLine("Selecting data\n");
                userRepository.SelectFromUserTable();
            }

            Console.ReadLine();

最后的效果图：

result

参考文章：https://www.c-sharpcorner.com/UploadFile/ranjancse/net-co-introduction-to-microsoft-data-sqlite/

源码地址：链接: https://pan.baidu.com/s/1LAOf_Mq3TD7nIOzvFQg7UA

提取码请点击最下方按钮

关注我的微信公众号
在公众号里留言交流
投稿邮箱：1052839972@qq.com

庭院深深深几许？杨柳堆烟，帘幕无重数。
玉勒雕鞍游冶处，楼高不见章台路。
雨横风狂三月暮。门掩黄昏，无计留春住。
泪眼问花花不语，乱红飞过秋千去。

如果感觉对您有帮助
欢迎向作者提供捐赠
这将是创作的最大动力