.Net 6 + WebApi Basics 6 - Database

2023-01-10 22:27:56 .Net >> .Net6 >> WebApi #C# #.Net6 #WebApi #SqlSugar #Database

Use SqlSugar to connect with DB

SQLSugar is relatively simple, less configuration, easy to get started, and more friendly to novices.

SQLSugar

Other ORM: EF Core, ADO.NET

Insall SqlSugarCore

You can directly install SqlSugar in Nuget by searching SqlSugarCore.

Dependency Injection

Use .NET IOC to launch.

Create a new class, named SqlsugarSetup.cs.

public static class SqlsugarSetup
    {
        public static void AddSqlsugarSetup(this IServiceCollection services, IConfiguration configuration,
        string dbName = "db_master")
        {
            SqlSugarScope sqlSugar = new SqlSugarScope(new ConnectionConfig()
            {
                DbType = SqlSugar.DbType.SqlServer,
                ConnectionString = configuration[dbName],
                IsAutoCloseConnection = true,
            },
                db =>
                {
                    //Configure Singleton parameters
                    db.Aop.OnLogExecuting = (sql, pars) =>
                    {
                        //Console.WriteLine(sql);//Output sql
                    };

                    //services.GetService<注入对象>();
                });
            services.AddSingleton<ISqlSugarClient>(sqlSugar);//SqlSugarScope uses AddSingleton
        }
    }

string dbName = "db_master" will get database’s connection string from appsettings.json.
The database name we use here is SQLSUGAR4XTEST。

1	"db_master": "server=localhost\\SQL_INSTANCE;uid=sa;pwd=aaa111;database=SQLSUGAR4XTEST"

DbType = SqlSugar.DbType.SqlServer will configure database. We use Microsoft SQL Server here。
ConnectionString = configuration[dbName] will acquire SqlServer’s Connection String。
Use builder.Services in program.cs to add sqlsugar service.

1	builder.Services.AddSqlsugarSetup(builder.Configuration);

CodeFirst

Code-First is mainly useful in Domain Driven Design. In the Code-First approach, you focus on the domain of your application and start creating classes for your domain entity rather than design your database first and then create the classes which match your database design. See here.

As its name, Code First, first establish the entity structure to be mapped to the database in the code, and then generate the corresponding database according to the entity structure. Simply put, use code to manage database,rather than operating database system directly.

Create Database

Right-click and create a new Controller file, named DbTestController.

[Route("api/[controller]")]
    [ApiController]
    public class DbTestController : ControllerBase
    {
        public DbTestController(ISqlSugarClient db)
        {
            Db = db;
        }

        private ISqlSugarClient Db { get; }

        /// <summary>
        /// Create the Database
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public bool CreateDb()
        {
            return Db.DbMaintenance.CreateDatabase();
        }
    }

ISqlSugarClient gets sqlsugar management parameters.
Db.DbMaintenance.CreateDatabase() creates the database.

Result

Run project.

In Swagger page, a DBTest interface is added.

DBTest

Click Try it out–>Execute

Try

Open SQL Server, SQLSUGAR4XTEST has been added.

Create Table

/// <summary>
/// Create Table
/// </summary>
/// <returns></returns>
[HttpPost("table")]
public void CreateTable()
{
    Db.CodeFirst.InitTables(typeof(UserClass));
}

Setup Keys

public class UserClass
{
    [SugarColumn(IsIdentity = true, IsPrimaryKey = true)]
    public int ID { get; set; }
    public string UserName { get; set; }
    public int Age { get; set; }
}

[SugarTable("Table_User")] Set table name Table_User.
IsPrimaryKey Set primary key.

CreateTable

Edit Table Name

Change the UserName to User. Only modify it in the database, and the code keeps unchanged.

1 2	[SugarColumn(ColumnName = "User")] public string UserName { get; set; }

Query

/// <summary>
/// Get data from DB
/// </summary>
/// <returns></returns>
[HttpGet("AllDB")]
public List<UserClass> GetList()
{
    return Db.Queryable<UserClass>().ToList();
}