C # Sqlite Sequence

SQLiteYou cannot create custom functions directly, not asSQL Servereasy to create and use. But we can still create it, and once we create it, we can do whatever we want (likebatch updates, etc.)

A sequence is a very common operation in a database, and in other relational database creation is fairly straightforward, but SQLite is not very convenient because it cannot directly create custom functions

1. First create a table that represents the sequence:

 create TABLE SEQUENCE (seq_name VARCHAR ( 50   "not Null,min_val DECIMAL ( 12 , 0  ) not NULL, Current_ VAL DECIMAL ( 12 , 0
     12 , 0 ) not NULL DEFAULT 1  ,increment INT not NULL DEFAULT  1   

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

Resets to the minimum value when the current value is greater than the maximum value, to achieve the purpose of the ordinal loop.

Using code creation functions in C #,Sqlitehelper is a public class library that accesses SQLite, as described in my C # SQLite help class.

3. Get the current sequence value

[Sqlitefunction (Name ="GetCurrentValue", Arguments =1, Functype =functiontype.scalar)] Public classgetcurrentvalue:sqlitefunction { Public Override ObjectInvoke (Object[] args) {Dictionary<string, string> data =Newdictionary<string,string>(); Data. ADD ("V_seq_name", args[0].            ToString ()); stringsql ="SELECT current_val from SEQUENCE WHERE seq_name = @V_SEQ_NAME;"; returnsqlitehelper.executescalar (Sql,data); }    }

View Code

4. Get the next sequence value

[Sqlitefunction (Name ="Getnextvalue", Arguments =1, Functype =functiontype.scalar)] Public classgetnextvalue:sqlitefunction { Public Override ObjectInvoke (Object[] args) {Dictionary<string, string> data =Newdictionary<string,string>(); Data. ADD ("V_seq_name", args[0].            ToString ()); stringsql ="UPDATE SEQUENCE SET current_val = current_val + INCREMENT WHERE seq_name = @V_SEQ_NAME;";            Sqlitehelper.executenonquery (SQL, data); returnSqlitehelper.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 classsetvalue:sqlitefunction { Public Override ObjectInvoke (Object[] args) {Dictionary<string, string> data =Newdictionary<string,string>(); Data. ADD ("V_seq_name", args[0].            ToString ()); Data. ADD ("V_value", args[1].            ToString ()); stringsql ="UPDATE SEQUENCE SET current_val = @V_VALUE WHERE seq_name= @V_SEQ_NAME;";            Sqlitehelper.executescalar (SQL, data); returnSqlitehelper.executescalar (string. Format ("SELECT getcurrentvalue (' {0} ')", args[0]. ToString ()),NULL); }    }

View Code

6. Test:

Add a row of data to the sequence table sequence

The definition sequence name is Purchase_in_order, the minimum value is 2000, the current value is 2000, the maximum value is 9999, and the step is 1.

Execute statement:

string sql = string. Format ("Select Getnextvalue (' Purchase_in_order ')"); Sqlitehelper. ExecuteNonQuery (SQL,null);
Go to the database to see if the current value is increased