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)
);

CREATE TRIGGER [SEQ_RESET_TRG]
AFTER UPDATE
ON [SEQUENCE]
FOR EACH ROW
begin
    UPDATE SEQUENCE SET CURRENT_VAL=MIN_VAL WHERE CURRENT_VAL-INCREMENT>=MAX_VAL;
end;

[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);
    }
}

[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);
    }
}

[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);
    }
}

string sql = string.Format("Select GetNextValue('PURCHASE_IN_ORDER')");
SqliteHelper.ExecuteNonQuery(sql,null);