Shulou(Shulou.com)06/02 Report--

This article introduces how to use SqlSugar in C#, the content is very detailed, interested friends can refer to, hope to be helpful to you.

1. Fancy mapping

In the actual development, the entity class in the program is not completely consistent with the table name of the database, and there are many reasons, for example, the team has different requirements for the naming of the database and the program. The database is established first and the program is developed later, or the program only uses part of the tables in the database and so on.

This will run counter to the C# convention that is superior to configuration, but it is also in line with the design philosophy of C#, because configuration is also part of C#. How can we establish the relationship between tables and entity classes from a practical point of view?

Let me take you to see if SqlSugar can do this part gracefully:

1.1 Attribute Settings

SqlSugar prefabricates some Attribute that allow us to establish relationships between entity tables and database tables through Attribute:

SugarTable: data table used to define entity class mapping

Public SugarTable (string tableName)

Public SugarTable (string tableName, string tableDescription)

These are the two constructors of SugarTable that allow you to set the table name and data table description

SugarColumn: used to define the relationship between attributes and columns in the data table

The data type of public string ColumnDataType {get; set;} / / column. Enter the data type of SQL.

Public string OldColumnName {get; set;} / / is used to generate the database after the table is updated. Fill in the original column name here.

Public bool IsNullable {get; set;} / / sets whether the column is allowed to be NULL

Public int Length {get; set;} / / sets the data length of the column

Public string ColumnDescription {get; set;} / / set the description name of the column

Public bool IsIdentity {get; set;} / / sets whether the column is self-incrementing

Public bool IsPrimaryKey {get; set;} / / sets the column to be the primary key

Public bool IsIgnore {get; set;} / / does not operate on the database, and true will not query, add, etc.

Public string ColumnName {get; set;} / / set the corresponding column name

Public string DefaultValue {get; set;} / / set the default value for this column

The Attribute configuration of SqlSugar is very simple and only needs to be configured for class-to-table mapping and attribute-to-column mapping.

1.2 dynamic configuration

Like EF, SqlSugar also supports dynamic configuration, so follow me to see how to implement dynamic configuration:

SqlSugar supports few dynamic configuration functions, so it is best to design the database in advance and then use dynamic configuration to do the correlation.

Set up the dynamic configuration of the data table through SugarClient:

Client.MappingTables.Add

The methods are:

Public void Add (string entityName, string dbTableName)

Public void Add (string entityName, string dbTableName, string dbTableShortName)

Public void Add (MappingTable table)

Then set the dynamic configuration of the column through SugarClient:

Client.MmappingColumn.Add

The methods are:

Public void Add (string propertyName, string dbColumnName, string entityName)

Public void Add (MappingColumn col)

Obviously, dynamic configuration does not support setting the rest of the column. Of course, SugarClient can also configure ignore fields:

Client.IgnoreColumns.Add

The specific implementation methods are as follows:

Public void Add (string propertyName, string EntityName)

Publiv void Add (IgnoreColumn col)

1.3 As alias pattern

When adding, deleting, changing and querying, SqlSugar adds aliases to the data entity, and you can use the method As (string newName).

Queryable () .As ("newName") / / select * from newName

Insertable

Updateable

Deleteable

Alias query similar to SQL

two。 Foreign key association

Instead of setting the formal loading of navigation properties in SqlSugar, a Mapper method is added: when querying, Mapper is called to map foreign key relationships to achieve the function of loading navigation properties together.

The first thing to note is that the navigation properties in SqlSugar need to be configured to ignore to avoid being parsed directly to SQL, otherwise Sequence contains no elements will be prompted.

Add several sample classes:

[SugarTable ("P_Person")]

Public class Person

{

[SugarColumn (IsPrimaryKey = true, IsIdentity = true)]

Public int Id {get; set;}

Public string Name {get; set;}

Public int Age {get; set;}

/ / /

/ / ignore

/ / /

[SugarColumn (IsIgnore = true)]

Public Employee Employee {get; set;}

}

Public class Employee

{

[SugarColumn (IsPrimaryKey = true, IsIdentity = true)]

Public int Id {get; set;}

Public string Name {get; set;}

Public int PersonId {get; set;}

[SugarColumn (IsIgnore = true)]

Public Person Person {get; set;}

Public int DeptId {get;set;}

[SugarColumn (IsIgnore = true)]

Public Dept Dept {get;set;}

}

Public class Dept

{

[SugarColumn (IsPrimaryKey = true, IsIdentity = true)]

Public int Id {get; set;}

Public string Name {get; set;}

[SugarColumn (IsIgnore = true)]

Public List Employees {get;set;}

}

Use the Context class from the previous article:

Public class DefaultContext

{

Public SqlSugarClient Client {get;}

Public DefaultContext (string connectionString, DbType dbType)

{

Client = new SqlSugarClient (new ConnectionConfig

{

ConnectionString = connectionString,// "Data Source=./demo.db"

DbType = dbType

IsAutoCloseConnection = true

InitKeyType = InitKeyType.Attribute

});

/ / = added

Client.CodeFirst.InitTables ()

Client.Aop.OnLogExecuting = (sql, paramters) = >

{

Console.WriteLine (sql)

}

}

}

Give me a brief introduction

SqlSugar provides many overloaded versions of the InitTables method, but the following three are recommended:

Void InitTables (string entitiesNamespace)

Void InitTables (string [] entitiesNamespaces)

Void InitTables (params Type [] entityTypes)

For the first two, you can agree on the namespace of the entity class, and then initialize all entity classes at once. The third initializes the instance of the entity class type passed in, and you can also reflect and traverse the desired class according to certain rules.

OnLogExecuting is a listening event of SqlSugar (although it is not an event, but I personally think it is better to write in event mode). It is used to monitor the SQL statements executed by the framework, which can be used for debugging or log monitoring.

Use Mapper to query the one-to-one mapping type:

Var results = context.Client.Queryable () .Mapper (t = > t.Person, p = > p.PersonId) .ToList ()

Use Mapper to query for one-to-many mapping types:

Var results = context.Client.Queryable () .Mapper (p = > p.Employees, p = > p.Employees.First () .Deptid) .ToList ()

It should be noted that these two are written in a fixed way.

Among them, one-to-one requirements must start with the main object query. A master object is one that must hold a foreign key pointing to another table.

The one-to-many requirement starts with the section that owns the property of the collection (that is, the "one"), and the association is indicated as the collection. First (). Foreign key.

Another thing is that the navigation properties of SqlSugar must be loaded manually and not automatically, so there is no problem of deep recursion at all.

On how to use SqlSugar in C# to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.