.net5操作Sqlit进行CURD

.net5操作Sqlit进行CURD

/// <summary>
        /// sqlite查询多行
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="pms">SQL参数</param>
        /// <returns>返回SqlDataReader对象</returns>
        public static async Task<DataTable> SqliteExcuteReader(string conStr, string sql, params SqliteParameter[] pms)
        {
            //这里不能用using，不然在返回SqlDataReader时候会报错，因为返回时候已经在using中关闭了。
            //事实上，在使用数据库相关类中，SqlConnection是必须关闭的，但是其他可以选择关闭，因为CG回自动回收
            SqliteConnection conn = new SqliteConnection(conStr);
            DataTable dt = new DataTable();
            using (SqliteCommand cmd = new SqliteCommand(sql, conn))
            {
                if (pms != null)
                {
                    cmd.Parameters.AddRange(pms);
                }
                try
                {
                    conn.Open();
                    //传入System.Data.CommandBehavior.CloseConnection枚举是为了让在外面使用完毕SqlDataReader后，只要关闭了SqlDataReader就会关闭对应的SqlConnection
                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        dt.Load(reader);
                        reader.Close();
                    }
                    SqliteConnection.ClearAllPools();
                    conn.Close();
                    conn.Dispose();
                    return dt;
                }
                catch
                {
                    SqliteConnection.ClearAllPools();
                    conn.Close();
                    conn.Dispose();
                    return null;
                }
            }
        }

        /// <summary>
        /// sqlite 增删改
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="pms">SQL参数</param>
        public static async Task SqliteExcuteCUD(string conStr, string sql, params SqliteParameter[] pms)
        {
            //这里不能用using，不然在返回SqlDataReader时候会报错，因为返回时候已经在using中关闭了。
            //事实上，在使用数据库相关类中，SqlConnection是必须关闭的，但是其他可以选择关闭，因为CG回自动回收
            SqliteConnection conn = new SqliteConnection(conStr);
            DataTable dt = new DataTable();
            using (SqliteCommand cmd = new SqliteCommand(sql, conn))
            {
                if (pms != null)
                {
                    cmd.Parameters.AddRange(pms);
                }
                try
                {
                    conn.Open();
                    //传入System.Data.CommandBehavior.CloseConnection枚举是为了让在外面使用完毕SqlDataReader后，只要关闭了SqlDataReader就会关闭对应的SqlConnection
                    cmd.CommandText = sql;
                    await cmd.ExecuteNonQueryAsync();
                    SqliteConnection.ClearAllPools();
                    conn.Close();
                    conn.Dispose();
                }
                catch
                {
                    SqliteConnection.ClearAllPools();
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

方法调用 CUD

                //string contentRootPath = _hostingEnvironment.ContentRootPath;
                string path = _hostingEnvironment.WebRootPath + @"/file/";
                string fileName = "xxx.db";
                await SqliteExcuteCUD($"Data Source={path}{fileName};", "DELETE FROM table;", null);
                var drug = await _drug.FindAllAsync();
                foreach (var item in drug)
                {
                    string sql = $@"INSERT INTO ""table""(""DERS"", ""createTime"") VALUES ( @DERS, @createTime);";
                    SqliteParameter[] array = new SqliteParameter[]
                    {new SqliteParameter("@DERS", item.DERS),
                        new SqliteParameter("@createTime", item.CreateTime),
                    };

                    await SqliteExcuteCUD($"Data Source={path}{fileName};", sql, array);

方法调用Read

 　　　　　　　　　string path = _hostingEnvironment.WebRootPath + @"/file/";
                string fileName = "xxx.db";

　　　　　　　　   using (var reader = await SqliteExcuteReader($"Data Source={path}{fileName};", "SELECT * FROM main.Info"))
                {
                    //首先判断是否有数据
                    foreach (System.Data.DataRow it in reader.Rows)
                    {
                        try
                        {
                            Info Info = new Info()
                            {
                                DateTime = DateTime.Parse(it["Date"].ToString() + " " + it["Time"].ToString()),
                                Claw = it["Claw"].ToString(),
                                Key = key,
                                Event = it["Event"].ToString(),
                                Id = Guid.NewGuid(),
                                State = it["State"].ToString(),
                                Count = double.Parse(it["Count"].ToString() ?? "0"),
                                Mode = it["Mode"].ToString(),
     
                            };
                            await Repository.AddAsync(Info);
                            await Repository.Context.CommitAsync();
                        }
                        catch (Exception ex)
                        {
                            await Task.FromResult(Utils.Log("xxxx", "xxxx", $"失败：{ex.Message}", true, ""));
                        }
                    }
                    reader.Dispose();
                }