Skip to content

Getting Started

Jump to bottom
2881099 edited this page Nov 22, 2023 · 21 revisions

中文 | English

FreeSql is a powerful .NET ORM that supports all .NET Standard runtime platforms like .NET Framework 4.0+, .NET Core 2.1+, Xamarin and AOT, etc.

FreeSql supports MySql, SqlServer, PostgreSQL, Oracle, Sqlite, Firebird, 达梦, 神通, 人大金仓, 翰高, 虚谷, 南大通用, ClickHouse, QuestDB and MsAccess.

QQ Groups：4336577(full)、8578575(full)、52508226(full)

Models

FreeSql uses a model to perform data access. The model is represented by an entity class to represent a database table or view for querying and saving data.

The entity model can be generated from an existing database, and FreeSql provides the IDbFirst interface to generate the entity model.

Or you can create the model manually, and then create or modify the database based on the model. FreeSql provides an API for the ICodeFirst synchronization structure (it can even be synchronized automatically during the development phase).

using FreeSql.DataAnnotations;
using System;

public class Blog {
    [Column(IsIdentity = true, IsPrimary = true)]
    public int BlogId { get; set; }
    public string Url { get; set; }
    public int Rating { get; set; }
}

Packages

FreeSql.Provider.XXX (Optional Providers)

dotnet add packages FreeSql
dotnet add packages FreeSql.Provider.Sqlite

or

Install-Package FreeSql
Install-Package FreeSql.Provider.Sqlite

Declaring

Note: IFreeSql should be declared as a singleton in the project, not created every time it is used.

  • .NET Core Singleton Startup.cs
services.AddSingleton<IFreeSql>(r =>
{
    IFreeSql fsql = new FreeSql.FreeSqlBuilder()
        .UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=db1.db")
        //Automatically synchronize the entity structure to the database.
        //FreeSql will not scan the assembly, and will generate a table if and only when the CRUD instruction is executed.
        .UseAutoSyncStructure(true)
        .Build();
    return fsql;
});
public class DB
{
    static Lazy<IFreeSql> sqliteLazy = new Lazy<IFreeSql>(() => {
        var fsql = new FreeSql.FreeSqlBuilder()
            .UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=db1.db")
            .UseAutoSyncStructure(true)
            .Build();
        return fsql;
    });
    public static IFreeSql Sqlite => sqliteLazy.Value;
}

Tips: class DB\<T\> Generic classes are not suitable for static singletons

Then when using it, use fsql directly through IFreeSql fsql = DB.Sqlite;.

IFreeSql is the top-level object of ORM, and all operations use its methods or properties:

fsql.Select<T>(); //Query
fsql.Insert<T>(); //Insert
fsql.Update<T>(); //Update
fsql.Delete<T>(); //Delete
fsql.InsertOrUpdate<T>()// Insert or Update
fsql.Transaction(..); //Transaction

fsql.CodeFirst; //CodeFirst Object
fsql.DbFirst; //DbFirst Object
fsql.Ado; //Ado Object
fsql.Aop; //Aop Object
fsql.GlobalFilter; //Gloabl Filter Object

Migration

When the program is running, FreeSql will check the AutoSyncStructure parameter, and use this condition to determine whether to compare the changes between the entity and the database structure to achieve the purpose of automatic migration. For more information, please refer to the CodeFirst Documentation.

Note: Use this feature in a production environment with caution.

Note: Use this feature in a production environment with caution.

Note: Use this feature in a production environment with caution.

Query Data

var blogs = fsql.Select<Blog>()
    .Where(b => b.Rating > 3)
    .OrderBy(b => b.Url)
    .Skip(100)
    .Limit(10) //Query the record from line 100 to line 110
    .ToList();

Insert Data

var blog = new Blog { Url = "http://sample.com" };
blog.BlogId = (int)fsql.Insert<Blog>()
    .AppendData(blog)
    .ExecuteIdentity();

Update Data

fsql.Update<Blog>()
    .Set(b => b.Url, "http://sample2222.com")
    .Where(b => b.Url == "http://sample.com")
    .ExecuteAffrows();

Delete Data

fsql.Delete<Blog>()
    .Where(b => b.Url == "http://sample.com")
    .ExecuteAffrows();

FreeSqlBuilder

Methods Return Description
UseConnectionString this Connection string
UseAdoConnectionPool this set up Ado connection pool (default false, remote true)
UseSlave this Set up the slave database, support multipleslace databases.
UseSlaveWeight this Set up the slave database weight.
UseConnectionFactory this Set up a custom database connection object (abandon the built-in object connection pool technology)
UseAutoSyncStructure this [Recommended development environment] Automatically synchronize the entity structure to the database, and check entity creation or modification table structure during program operation
UseNoneCommandParameter this Do not use command parameterized execution. for Insert/Update, you can also temporarily use IInsert/IUpdate.NoneParameter()
UseGenerateCommandParameterWithLambda this For lambda expression analysis, generate command parameterized execution
UseLazyLoading this Turn on the lazy loading function,
UseMonitorCommand this Monitor before and after global SQL execution.
UseMappingPriority this Specify mapping priority（default Aop < FluentApi < Attribute）
UseNameConvert this Automatic name conversion Entity -> Db
UseExitAutoDisposePool this Listen to the AppDomain.CurrentDomain.ProcessExit/Console.CancelKeyPress event to automatically release the connection pool (default true)
Build<T> IFreeSql<T> Create an IFreeSql object. Note: Singleton design, don’t repeat creation

ConnectionStrings

DataType ConnectionString
DataType.MySql Data Source=127.0.0.1;Port=3306;User ID=root;Password=root; Initial Catalog=cccddd;Charset=utf8; SslMode=none;Min pool size=1
DataType.PostgreSQL Host=192.168.164.10;Port=5432;Username=postgres;Password=123456; Database=tedb;Pooling=true;Minimum Pool Size=1
DataType.SqlServer Data Source=.;User Id=sa;Password=123456;Initial Catalog=freesqlTest;Encrypt=True; TrustServerCertificate=True;Pooling=true;Min Pool Size=1
DataType.Oracle user id=user1;password=123456; data source=//127.0.0.1:1521/XE;Pooling=true;Min Pool Size=1
DataType.Sqlite Data Source=|DataDirectory|\document.db; Attachs=xxxtb.db; Pooling=true;Min Pool Size=1
DataType.Firebird database=localhost:D:\fbdata\EXAMPLES.fdb;user=sysdba;password=123456
DataType.MsAccess Provider=Microsoft.Jet.OleDb.4.0;Data Source=d:/accdb/2003.mdb
DataType.MsAccess(accdb) Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:/accdb/2003.accdb;
DataType.MsAccess(encipher) Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:/accdb/2003.accdb;Jet OLEDB:Database Password=12341234
DataType.Dameng(达梦) server=127.0.0.1;port=5236;user=2user;password=123456789;database=2user;poolsize=5
DataType.ShenTong(神通) HOST=192.168.164.10;PORT=2003;DATABASE=OSRDB;USERNAME=SYSDBA;PASSWORD=szoscar55;MAXPOOLSIZE=2
DataType.KingbaseES(人大金仓) V008R003 Server=127.0.0.1;Port=54321;UID=USER2;PWD=123456789;database=TEST;MAXPOOLSIZE=2
DataType.Gbase(南大通用) Driver={GBase ODBC DRIVER (64-Bit)};Host=192.168.164.134;Service=9088;Server=gbase01;Database=testdb;Protocol=onsoctcp;Uid=gbasedbt;Pwd=GBase123;Db_locale=zh_CN.utf8;Client_locale=zh_CN.utf8
DataType.Xugu(虚谷) IP=127.0.0.1;DB=SYSTEM;User=SYSDBA;PWD=SYSDBA;Port=5138;AUTO_COMMIT=on;CHAR_SET=UTF8
DataType.OdbcMySql Driver={MySQL ODBC 8.0 Unicode Driver}; Server=127.0.0.1;Persist Security Info=False; Trusted_Connection=Yes;UID=root;PWD=root; DATABASE=cccddd_odbc;Charset=utf8; SslMode=none;Min Pool Size=1
DataType.OdbcSqlServer Driver={SQL Server};Data Source=.;User Id=sa;Password=123456;Initial Catalog=freesqlTest;Encrypt=True; TrustServerCertificate=True;Pooling=true;Min Pool Size=1
DataType.OdbcOracle Driver={Oracle in XE};Server=//127.0.0.1:1521/XE; Persist Security Info=False; Trusted_Connection=Yes;UID=odbc1;PWD=123456; Min Pool Size=1
DataType.OdbcPostgreSQL Driver={PostgreSQL Unicode(x64)};Server=192.168.164.10; Port=5432;UID=postgres;PWD=123456; Database=tedb_odbc;Pooling=true;Min Pool Size=1
DataType.OdbcDameng (达梦) Driver={DM8 ODBC DRIVER};Server=127.0.0.1:5236; Persist Security Info=False; Trusted_Connection=Yes; UID=USER1;PWD=123456789
DataType.OdbcKingbaseES (人大金仓) V008R003 Driver={KingbaseES 8.2 ODBC Driver ANSI};Server=127.0.0.1;Port=54321;UID=USER2;PWD=123456789;database=TEST
DataType.Odbc Driver={SQL Server};Server=.;Persist Security Info=False; Trusted_Connection=Yes;Integrated Security=True; DATABASE=freesqlTest_odbc; Pooling=true;Min pool size=1
DataType.Custom Custom connection string, access any database

Reference

Basic

使用指南

学习与交流

信息

Clone this wiki locally