NetCore WebAPI通过SqlSugar读取数据库，并将数据库的内容传入wwwroot

页面展示，用到了ajax，sqlsugar，layui

 一、项目创建

如果启动错误大概率是连接字符串出错了，字符串内的是数据库名，而不是表名

1.创建项目使用ASP.NET Core Web API

 2.选择net6.0，把配置HTTPS取消

二、netcore后端

1.引入sqlsugarcore包

 2.创建SqlSugarHelper类来连接数据库

 public static class SqlSugarHelper
    {
        public static void Connection
            (this IServiceCollection services,
            IConfiguration configuration,
            string connect = "ConnectionString")
        {
            SqlSugarScope sqlSugarScope = new SqlSugarScope(new ConnectionConfig()
            {
                DbType = DbType.SqlServer,
                ConnectionString = configuration[connect],
                IsAutoCloseConnection = true
            },
            db =>
            {
                //单例参数配置，所有上下文生效
                db.Aop.OnLogExecuting = (sql, pars) =>
                {
                    Console.WriteLine(sql);//输出sql到控制台
                };
            });
            services.AddSingleton<ISqlSugarClient>(sqlSugarScope);
        }
    }

3.在appsettings.json内写入连接字符串，我把全文粘下来，文档数据库名为New_Demo

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionString": "Server=.;Database=sqldemo;Uid=sa;Pwd=123456;"
}

4.创建实体类ticket表

 public class ticket
    {
        /// <summary>
        /// id
        /// </summary>
        [SugarColumn(IsIdentity = true, IsPrimaryKey = true)]
        public int ID { get; set; }

        /// <summary>
        /// 对方账号
        /// </summary>

        [SugarColumn(ColumnDataType = "varchar(20)", IsNullable = true)]
        public string OtherId { get; set; }

        /// <summary>
        /// 存取
        /// </summary>

        [SugarColumn(ColumnDataType = "varchar(20)", IsNullable = true)]
        public string CunQU { get; set; }

        /// <summary>
        /// 发生额
        /// </summary>

        [SugarColumn(ColumnDataType = "varchar(20)", IsNullable = true)]
        public string Price { get; set; }

        /// <summary>
        /// 本次余额
        /// </summary>

        [SugarColumn(ColumnDataType = "varchar(20)", IsNullable = true)]
        public string Remove { get; set; }

        /// <summary>
        /// 交易日期
        /// </summary>

        [SugarColumn(ColumnDataType = "varchar(20)", IsNullable = true)]
        public string DealTime { get; set; }

        /// <summary>
        /// 星期
        /// </summary>

        [SugarColumn(ColumnDataType = "varchar(20)", IsNullable = true)]
        public string Day { get; set; }

        /// <summary>
        /// 交易类型
        /// </summary>

        [SugarColumn(ColumnDataType = "varchar(20)", IsNullable = true)]
        public string DealType { get; set; }

        /// <summary>
        /// 交易机构名称
        /// </summary>

        [SugarColumn(ColumnDataType = "varchar(50)", IsNullable = true)]
        public string DealName { get; set; }

        /// <summary>
        /// 摘要
        /// </summary>

        [SugarColumn(ColumnDataType = "varchar(20)", IsNullable = true)]
        public string txt { get; set; }

        /// <summary>
        /// 币种
        /// </summary>

        [SugarColumn(ColumnDataType = "varchar(20)", IsNullable = true)]
        public string Kind { get; set; }

    }

5.创建控制器写接口

[Route("api/[controller]/[action]")]//控制器的路由模板
    [ApiController]
    public class HomeController : Controller
    {
        private readonly ISqlSugarClient db;
        public HomeController(ISqlSugarClient db)
        {
            this.db = db;
        }

        /// <summary>
        /// 获取
        /// </summary>
        [HttpGet]
        public List<ticket> Get()
        {
            //使用db.Queryable<todos>()方法从数据库中查询所有的todos记录，
            //并将结果保存在list变量中
            var list = db.Queryable<ticket>().ToList();
            return list;
        }
    }

6.Program内

using TestDemo;

var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();


//应用程序构建后，不能修改ServiceCollection,所以这句话必须在build上面
builder.Services.Connection(builder.Configuration);


var app = builder.Build();

//1.注释掉Properties/launchsettings.json里的"launchUrl": "swagger",
//2.注释掉下面的if，这样默认打开的页面就是wwwroot的index.html
//注释这两个把下面的if取消注释，并取消launchsettings.json的注释，默认页面就是api页面
app.UseDefaultFiles();
app.UseStaticFiles();
// Configure the HTTP request pipeline.
//if (app.Environment.IsDevelopment())
//{
//    app.UseSwagger();
//    app.UseSwaggerUI();
//}



app.UseCors(t => t.AllowAnyOrigin().AllowAnyHeader().AllowAnyMethod());
app.UseHttpsRedirection();



app.UseAuthorization();
app.MapControllers();
app.Run();

7.创建wwwroot文件夹

下载layuiLayui - 经典开源模块化前端 UI 框架 (ilayuis.com)

将layui.css引入css文件

创建index.html,代码如下

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title></title>
    <link rel="stylesheet" href="./css/layui.css">
</head>
<body>
    <div>
        <h1>账单数据</h1>
        <table class="layui-table" id="data-table">
            <colgroup>
                <col width="150">
                <col width="150">
                <col width="150">
                <col width="150">
                <col width="150">
                <col width="150">
                <col width="150">
                <col width="150">
                <col width="150">
                <col width="150">
            </colgroup>
            <thead>
                <tr>
                    <th>对方账号</th>
                    <th>存取</th>
                    <th>发生额</th>
                    <th>本次余额</th>
                    <th>交易日期</th>
                    <th>星期</th>
                    <th>交易类型</th>
                    <th>交易机构名称</th>
                    <th>摘要</th>
                    <th>币种</th>
                </tr>
            </thead>
            <tbody>
            </tbody>
        </table>
        <div id="result"></div>
    </div>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script type="text/javascript">
        $.ajax({
            url: 'http://localhost:5147/api/Home/Get',
            type: 'GET',
            dataType: 'json',
            success: function (data) {
                // 将数据填充到元素中
                $('#result').text(JSON.stringify(data));

                var table = document.getElementById("data-table");

                for (var i = 0; i < data.length; i++) {
                    var row = document.createElement("tr");

                    row.innerHTML = `
                <td>${data[i].otherId}</td>
                <td>${data[i].cunQU}</td>
                <td>${data[i].price}</td>
                <td>${data[i].remove}</td>
                <td>${data[i].dealTime}</td>
                <td>${data[i].day}</td>
                <td>${data[i].dealType}</td>
                <td>${data[i].dealName}</td>
                <td>${data[i].txt}</td>
                <td>${data[i].kind}</td>
            `;

                    table.appendChild(row);
                }
            },
            error: function (error) {
                console.log(error);
            }
        });
    </script>

</body>
</html>