hisql.net official website (document writing)

Hisql source code (github) https://github.com/tansar/HiSql

git clone https://github.com/tansar/HiSql.git

HiSql query statement tutorial

Data insertion

HiSql provides a variety of data insertion methods. Through extreme performance optimization, it is currently among the best in common ORM

Single table data insertion

Performance test results

Number of records inserted	Hisql (time consuming)	Sqlsugar (time consuming)	FreeSQL (time consuming)
Article 5	0.0107 seconds	0.0312 seconds	0.02675 seconds
Article 10	0.0111 seconds	0.0307 seconds	0.0271 seconds
Article 50	0.0174 seconds	0.0364 seconds	0.0430 seconds
100 articles	0.0281 seconds	0.0472 seconds	0.0922 seconds
200 articles	0.0310 seconds	0.0584 seconds	0.2319 seconds
500 articles	0.0712 seconds	0.1127 seconds	0.5996 seconds
1000 articles	0.1112 seconds	0.1871 seconds	0.8145 seconds
10000	1.0440 seconds	1.6415 seconds	5.3671 seconds
100000 articles	10.3279 seconds	15.8685 seconds	46.0261 seconds

Insert via anonymous class

The biggest advantage of anonymous classes is that they do not depend on entity classes and do not need to create a separate class for the table. They are still very useful for inserting data quickly and conveniently. If the return value is greater than 0 after insertion, it indicates success,

::: tip

The field column name of the hidden sentence class should correspond to at least one field in the database, otherwise HiSql will throw an exception

HiSql allows the inserted object column set to be larger than the column set in the database

Field names ignore case
:::

Example HiSql anonymous class writes to table data

int _effect= sqlClient.Insert("HTest01", new { SID =123456, UName ="tansar", Age =25, Salary =1999.9, Descript ="hello world"}).ExecCommand();

If the inserted data set is larger than the column in the data, it can also be inserted successfully, as shown below

//TestColumn does not exist in table HTest01. If HiSql does not exist, it will be automatically ignored when checking the data

int _effect= sqlClient.Insert("HTest01", new { SID =123456, UName ="tansar", Age =25, Salary =1999.9, Descript ="hello world",TestColumn="test"}).ExecCommand();

If the case of the column is inconsistent, it can also be recognized automatically for writing

int _effect= sqlClient.Insert("HTest01", new { sid =123456, uname ="tansar", age =25, Salary =1999.9, descript ="hello world" }).ExecCommand();

Batch insert anonymous classes

If you need to insert more than one piece of data into the table, use the following method

There is no limit to how much data can be written in batch. At the bottom of HiSql, the data size and column size will be automatically calculated for different databases to implement the optimization scheme of packet size

List<object> lstdata = new List<object>()
{
    new{ sid =123456, UName ="tansar", Age =25, Salary =1999.9, Descript ="hello world"},
    new{ sid =123457, UName ="tansar", Age =25, Salary =1999.9, Descript ="hello world"}
};
// A return greater than 0 indicates success
int _effect4 = sqlClient.Insert("HTest01", lstdata).ExecCommand();

Write through dictionary object

::: tip

The field column name of the field object must correspond to at least one field in the database, otherwise HiSql will throw an exception

HiSql allows the inserted object column set to be larger than the column set in the database

Field names ignore case
When dictionary < string, string > is used, you need to add new dictionary < string, string > (stringcomparer. Ordinalignorecase) to ignore case
:::

//Dictionary < string, Object > object writing
int _effect = sqlClient.Insert("HTest01", 
    new Dictionary<string, object> { { "SID", 123456 },{ "UName","tansar" },{ "Age",25 },{ "Salary",1999.9 },{ "descript","hello world" } }
    ).ExecCommand();


//Dictionary < string, string > object write
int _effect2 = sqlClient.Insert("HTest01",
    new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase) { { "SID", "123456" }, { "UName", "tansar" }, { "Age", "25" }, { "Salary", "1999.9" }, { "descript", "hello world" } }
    ).ExecCommand();

How to write the JSON object passed in from the front end? Here's a demonstration

In this way, the JSON data passed in from the front end can be written directly to the data table

string jsondata= "{\"SID\":123456,\"UName\":\"tansar\",\"Age\":25,\"Salary\":1999.9,\"descript\":\"hello world\"}";
var _dicobj = JsonConvert.DeserializeObject<Dictionary<string, object>>(jsondata);

int _effect3 = sqlClient.Insert("HTest01", _dicobj).ExecCommand();

Batch dictionary object write

Batch writing through dictionary object data

There is no limit to how much data can be written in batch. At the bottom of HiSql, the data size and column size will be automatically calculated for different databases to implement the optimization scheme of packet size

List<Dictionary<string, object>> lstdata = new List<Dictionary<string, object>> {
    new Dictionary<string, object> { { "SID", 123456 }, { "UName", "tansar" }, { "Age", 25 }, { "Salary", 1999.9 }, { "descript", "hello world" } },
    new Dictionary<string, object> { { "SID", 123457 }, { "UName", "tansar" }, { "Age", 25 }, { "Salary", 1999.9 }, { "descript", "hello world" } }
};

int _effect4 = sqlClient.Insert("HTest01", lstdata).ExecCommand();

Write through entity class

Entity class write
::: tip

The field column name of the entity class must correspond to at least one field in the database, otherwise HiSql will throw an exception

HiSql allows the inserted object column set to be larger than the column set in the database

Field names ignore case
:::

int _effect1 = sqlClient.Insert("HTest01", new HTest01 { SID = 123456, UName = "tansar", Age = 25, Salary = 1999, Descript = "hello world" }).ExecCommand();

Batch entity data writing

As shown in the following code

List<HTest01> lstdata = new List<HTest01>()
{
    new HTest01 { SID = 123456, UName = "tansar", Age = 25, Salary = 1999, Descript = "hello world" },
    new HTest01 { SID = 123457, UName = "tansar", Age = 25, Salary = 1999, Descript = "hello world" }
};

int _effect4 = sqlClient.Insert("HTest01", lstdata).ExecCommand();

Insert multiple tables at the same time

Insert multiple tables without transactions

Platform in our business development process, a business often needs to write two tables at the same time. In addition, if a table fails in transaction control, it will roll back at the same time. HiSql provides a simple operation method that can write two tables at the same time, as shown in the following table

//Simultaneously to table Hi_Domain and Hi_DataElement insert data
sqlClient.Insert("Hi_Domain", new { Domain = "TST", DomainDesc = "Test data type" })
    .Insert("Hi_DataElement", new List<object> {
    new { Domain = "TST", ElementValue = "A" } ,
    new { Domain = "TST", ElementValue = "B" }
    }).ExecCommand();

Insert with transaction

When inserting multiple tables, as long as one table fails, the transaction rolls back, as shown below

sqlClient.BeginTran();
try
{
    sqlClient.Insert("Hi_Domain", new { Domain = "TST", DomainDesc = "Test data type" })
        .Insert("Hi_DataElement", new List<object> {
        new { Domain = "TST", ElementValue = "A" } ,
        new { Domain = "TST", ElementValue = "B" }
        }).ExecCommand();
    sqlClient.CommitTran();
}
catch (Exception E)
{
    sqlClient.RollBackTran();
}

Insert check

Table test data verification

When designing the second paradigm database, it is often necessary to associate the possible duplicate data into a separate table, but when writing the table, it is necessary to verify whether the value exists in another table for the sake of data rigor
If a user table (HTest01) is used, the table structure is as follows

The value of field UTYP is in another table H_UType is maintained. The table structure is as follows

To demonstrate the use of H_ Write three pieces of data in Utype as follows

//Adding a user type Modi method to the table means updating if it exists and inserting if it does not
            sqlClient.Modi("H_UType", new List<object> {
                new { UTYP = "U1", UTypeName = "Ordinary users" },
                new { UTYP = "U2", UTypeName = "Intermediate user" },
                new { UTYP = "U3", UTypeName = "Advanced user" }
            }).ExecCommand();

Normally, when we write data to table HTest01, the value of field UTYP must exist in table H_ Otherwise, this data is illogical in Utype. It can also be considered as dirty data. If some verification is not done when writing code, this data can be successfully saved to the database

HiSql authors often encounter this problem in daily development, especially in multi business scenarios and multi person team development. Some development considerations are verified at all, and some are ignored, which brings hidden dangers of system bugs. Is there a way to eliminate this basic logic error?

HiSql provides this function. Users who have used HiSql must know that four standard tables will be generated after HiSql is initialized, as shown below

Hi_TabModel
Hi_FieldModel
Hi_Domain
Hi_DataElement

However, we do not focus on introducing the functions of each table one by one. We need to realize the data detection described in this article. We only need to modify the configuration table hi_ The fieldmodel code is as follows

sqlClient.Update("Hi_FieldModel", new { TabName = "HTest01", FieldName = "UTYP", IsRefTab=true,RefTab= "H_UType",RefField="UTYP", RefFields = "UTYP,UTypeName",RefFieldDesc= "Type code,Type name",RefWhere="UTYP<>''" }).ExecCommand();

Executing the above update code is to configure the field UTYP of table HTest01. When inserting data into the table, the value of the field UTYP must be in table H_ If the field in Utype is in UTYP, the underlying HiSql will automatically verify when writing data to table HTest01
You may be worried about whether each data will be checked in the table and whether there will be performance problems. In fact, you don't have to worry about HiSql's special handling of these. Basically, you don't have to worry. If you are interested, you can check the HiSql source code

So let's try inserting data into table HTest01

sqlClient.Insert("HTest01", new { SID = "0", UTYP = "U4", UName = "hisql", Age = 36, Salary = 11, Descript = "hisql" }).ExecCommand();

What happens when you execute the above code? Yes, an exception will be thrown, as shown below

This is an exception thrown after the bottom detection of HiSql. The field UTYP value U4 inserted in the above code is not in the table h just configured_ Utype, so this is illegal data that does not conform to normal business logic

Modify the code

sqlClient.Insert("HTest01", new { SID = "0", UTYP = "U3", UName = "hisql", Age = 36, Salary = 11, Descript = "hisql" }).ExecCommand();

In this way, it can be executed normally and can be verified through HiSql