C # Sqlite Sequence

Sqlite cannot directly create user-defined functions, and cannot be easily created and used as in SQL server. But we can still create it. After the creation is successful, we can still do what we like (such as batch update)

Sequence is a common operation in a database. It is quite simple to create other relational databases, but Sqlite is not very convenient because it cannot directly create user-defined functions.

1. Create a table that represents the sequence first:

 

Create table sequence (SEQ_NAME VARCHAR (50) not null, MIN_VAL DECIMAL (12,0) not null, CURRENT_VAL DECIMAL (12,0) not null, MAX_VAL DECIMAL (12,0) not null default 1, increment int not null default 1, primary key (SEQ_NAME ));View Code

Defines the minimum, maximum, step, sequence name, and current value of a sequence.

2. Create a trigger

 

Create trigger [SEQ_RESET_TRG] after updateon [SEQUENCE] for each ROWbegin update sequence set CURRENT_VAL = MIN_VAL WHERE CURRENT_VAL-INCREMENT> = MAX_VAL; end;View Code

When the current value is greater than the maximum value, it is reset to the minimum value to achieve the objective of repeating the serial number.

Use code to create a function in C #. SqliteHelper is a public class library for accessing Sqlite. It is introduced in my C # Sqlite help class.

3. Get the current Sequence Value

[SQLiteFunction (Name = "GetCurrentValue", Arguments = 1, FuncType = FunctionType. scalar)] public class GetCurrentValue: SQLiteFunction {public override object Invoke (object [] args) {Dictionary <String, String> data = new Dictionary <string, string> (); data. add ("V_SEQ_NAME", args [0]. toString (); string SQL = "SELECT CURRENT_VAL FROM SEQUENCE WHERE SEQ_NAME = @ V_SEQ_NAME;"; return SqliteHelper. executeScalar (SQL, data );}}View Code

4. Get the next Sequence Value

[SQLiteFunction (Name = "GetNextValue", Arguments = 1, FuncType = FunctionType. scalar)] public class GetNextValue: SQLiteFunction {public override object Invoke (object [] args) {Dictionary <String, String> data = new Dictionary <string, string> (); data. add ("V_SEQ_NAME", args [0]. toString (); string SQL = "UPDATE SEQUENCE SET CURRENT_VAL = CURRENT_VAL + INCREMENT WHERE SEQ_NAME = @ V_SEQ_NAME;"; SqliteHelper. executeNonQuery (SQL, data); return SqliteHelper. executeScalar (string. format ("SELECT GetCurrentValue ('{0}')", args [0]. toString (), null );}}View Code

5. Set the current Sequence Value

[SQLiteFunction (Name = "SetValue", Arguments = 2, FuncType = FunctionType. scalar)] public class SetValue: SQLiteFunction {public override object Invoke (object [] args) {Dictionary <String, String> data = new Dictionary <string, string> (); data. add ("V_SEQ_NAME", args [0]. toString (); data. add ("V_VALUE", args [1]. toString (); string SQL = "UPDATE SEQUENCE SET CURRENT_VAL = @ V_VALUE WHERE SEQ_NAME = @ V_SEQ_NAME;"; SqliteHelper. executeScalar (SQL, data); return SqliteHelper. executeScalar (string. format ("SELECT GetCurrentValue ('{0}')", args [0]. toString (), null );}}View Code

6. test:

Add a row of data to the SEQUENCE of the SEQUENCE table

Define the sequence name as PURCHASE_IN_ORDER. The minimum value is 2000, the current value is 2000, the maximum value is 9999, and the step size is 1.

Execute the statement:

String SQL = string. Format ("Select GetNextValue ('purchase _ IN_ORDER ')"); SqliteHelper. ExecuteNonQuery (SQL, null );
Check whether the current value is increased in the database.