.NET Core與Vue3組合開發技_第02天_如何安裝SqlSugar資料存取層套件_配置MySQL資料庫

 
安裝配置SqlSugarCore框架(也是更新的一套類似EFCore的ORM框架)



註冊SqlSugar相應服務
這邊藉由Singleton將服務給註冊進來

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
using SqlSugar;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddCors(options =>
{
    options.AddDefaultPolicy(
        builder =>
        {
            builder.WithOrigins("http://localhost:9090").AllowAnyHeader().AllowAnyMethod();
        });
});

builder.Services.AddSingleton<ISqlSugarClient>(config =>
{
    var client = new SqlSugarClient(new ConnectionConfig()
    {
        ConnectionString = builder.Configuration.GetConnectionString("MySQLDbConn"),
        DbType = DbType.MySql,
        IsAutoCloseConnection = true,
        InitKeyType = InitKeyType.Attribute
    });
    return client;
});

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

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseCors();

app.UseAuthorization();

app.MapControllers();

app.Run();


配置連線字串與資料庫種類
appsettings.json中配置MySQL資料庫連接字串

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "MySQLDbConn" : "server=localhost;port=3307;database=vuedb;uid=root;pwd=;charset=utf-8"
  }
}


在SqlSugar中的DbType就有提供如下這幾種DB類別

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
namespace SqlSugar
{
    public enum DbType
    {
        MySql = 0,
        SqlServer = 1,
        Sqlite = 2,
        Oracle = 3,
        PostgreSQL = 4,
        Dm = 5,
        Kdbndp = 6,
        Oscar = 7,
        [Obsolete("使用DbType.MySql,已经全部统一用MySqlConnector取代 MySql.Data 原因.NET7下面差了几倍性能")]
        MySqlConnector = 8,
        Access = 9,
        OpenGauss = 10,
        QuestDB = 11,
        HG = 12,
        ClickHouse = 13,
        GBase = 14,
        Odbc = 0xF,
        Custom = 900
    }
}

建立一個Student類別作為一個學生資料表的測試


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
using SqlSugar;

namespace vueprojapi.Models
{
    [SugarTable("Students")]
    public class Student
    {

        [SugarColumn(IsPrimaryKey = true,IsIdentity = true)]
        public int Id { get; set; }

        [SugarColumn(ColumnDataType = "varchar" , Length = 200)]
        public string? Name { get; set; }
        
        //年齡
        public int Age { get; set; }

        //性別, true:男,false:女
        public bool Sex { get; set; }

        //年資
        public int Year { get; set; }

        //備註
        [SugarColumn(IsNullable = true , ColumnName ="Memo")]
        public string? Remark { get; set; }
    }
}


接著就可在資料庫生成對應的table
SqlSugar也可藉由code first模式來做開發 底層也是依賴ado.net微軟的套件


Program.cs中去做SqlSugar物件與相應資料庫與表結構生成的程式碼


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
using SqlSugar;
using vueprojapi.Models;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddCors(options =>
{
    options.AddDefaultPolicy(
        builder =>
        {
            builder.WithOrigins("http://localhost:9090").AllowAnyHeader().AllowAnyMethod();
        });
});

builder.Services.AddSingleton<ISqlSugarClient>(config =>
{
    var client = new SqlSugarClient(new ConnectionConfig()
    {
        ConnectionString = builder.Configuration.GetConnectionString("MySQLDbConn"),
        DbType = DbType.MySql,
        IsAutoCloseConnection = true,
        InitKeyType = InitKeyType.Attribute
    });
    return client;
});

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

var app = builder.Build();

var sqlSugar = app.Services.CreateScope().ServiceProvider.GetRequiredService<ISqlSugarClient>();

sqlSugar.DbMaintenance.CreateDatabase();

sqlSugar.CodeFirst.InitTables(typeof(Student));

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseCors();

app.UseAuthorization();

app.MapControllers();

app.Run();

產生資料庫由於寫在每次程式重新運行的程式進入點當中
而每次重新運行
都可確保資料庫一些相關data表結構都不會被覆蓋掉
但如果是有欄位異動或增刪結構則可自動同步







留言

這個網誌中的熱門文章

何謂淨重(Net Weight)、皮重(Tare Weight)與毛重(Gross Weight)

Architecture(架構) 和 Framework(框架) 有何不同?_軟體設計前的事前規劃的藍圖概念

(2021年度)駕訓學科筆試準備題庫歸納分析_法規是非題