mercredi 14 octobre 2015

c# windows10 sqlite async and speed

I'm new in SQLite and I'm wondering if SQLite is that slow and how I could fasten up the Things. I would like to go async but running into Troubles.

  1. I'm doing a Windows10 Universal App
  2. I have SQLitePCL from Microsoft Open Technologies v3.8.7.2 installed (Nuget)
  3. I have the VSIX package for Universal App Platform development using Visual Studio 2015 SQLIte-Package installed
  4. I took some sample Code from channel9 to get an idea how to program SQLite

First Question: I have the Impression that SQLite is very slow. To read approx 340 entries it takes up to 2-4 seconds. Can you let me know if that's oky so far?! Here my Code:

public static SQLiteConnection sqlite_connection;
sqlite_connection = new SQLiteConnection("teacherscal.db");

public abstract class DB_ModelBase<TItemType, TKeyType>
{
public ObservableCollection<TItemType> GetAllSelectedItems(object[] key)
{
    var items = new ObservableCollection<TItemType>();
    using (var statement = sqlConnection.Prepare(GetSelectSelectionSql(key)))
    {
        FillSelectSelectionStatement(statement, key);
        while (statement.Step() == SQLiteResult.ROW)
        {
            var item = CreateItem(statement);
            items.Add(item);
        }
    }
    return items;
}
}

protected override PRGD010_Tag CreateItem(ISQLiteStatement statement)
{
     PRGD010_Tag obj = new PRGD010_Tag(
            DateTime.Parse((string)statement[0]),
            (string)statement[1],
            (Category)statement[2],
            (Classification)statement[3],
            (long)statement[4]
      );
     return obj;
}

public void load_days(object[] key)
{
    string sql = @"BEGIN TRANSACTION";
    using (var statement = App.sqlite_connection.Prepare(sql))
    {
        statement.Step();
    }

    var prgd010 = App.sqlite_tage.GetAllSelectedItems(new object[] { selected_recid, (DateTime)key[0], (DateTime)key[1] });
    var prgd020 = App.sqlite_stunden.GetAllSelectedItems(new object[] { selected_recid, (DateTime)key[0], (DateTime)key[1] });

    sql = @"END TRANSACTION";
    using (var statement = App.sqlite_connection.Prepare(sql))
    {
        statement.Step();
    }
}

2nd question: I've tried to make the method async, but get an error execption: RPC_E_WRONG_THREAD Exception. Can you give me some hints how to solve this. The class which is created with method CreateItem contains = new SolidColorbrush(). This seems to make Troubles.

public class PRGD010_Tag : ModelBase
{
    private DateTime _dat_day = new DateTime();
    public DateTime dat_day
    {
        get { return _dat_day; }
        set { _dat_day = value; OnPropertyChanged(); }
    }


    public string dat_day_string {get {return _dat_day.ToString("yyyy-MM-dd");}}

    private string _notes = string.Empty;
    public string notes
    {
        get { return _notes; }
        set { if (OnPropertyChanged(ref _notes, value)) ismodified = true; }
    }

    public Category ref_cat_id
    {
        get { return _ref_cat.category; }
        set
        {
            if (App.main_viewmodel.setd010.Count != 0 && value >= 0 && (int)value <= App.main_viewmodel.setd010.Count - 1)
            {
                _ref_cat = App.main_viewmodel.setd010[(int)value];
            }
            else
            {
                _ref_cat = new SETD010_Category();
            }
        }
    }
    private SETD010_Category _ref_cat = new SETD010_Category();
    public SETD010_Category ref_cat
    {
        get { return _ref_cat; }
        set { if (OnPropertyChanged(ref _ref_cat, value)) ismodified = true; }
    }

    public Classification ref_class_id
    {
        get { return _ref_class.classification; }
        set
        {
            if (App.main_viewmodel.setd020.Count != 0 && value >= 0 && (int)value <= App.main_viewmodel.setd020.Count - 1)
            {
                _ref_class = App.main_viewmodel.setd020[(int)value];
            }
            else
            {
                _ref_class = new SETD020_Classification();
            }
        }
    }
    private SETD020_Classification _ref_class = new SETD020_Classification();
    public SETD020_Classification ref_class
    {
        get { return _ref_class; }
        set { if (OnPropertyChanged(ref _ref_class, value)) ismodified = true; }
    }

    public long ref_recid { get; set; }

    private ObservableCollection<PRGD020_Stunde> _stunden = new ObservableCollection<PRGD020_Stunde>();
    public ObservableCollection<PRGD020_Stunde> stunden
    {
        get { return _stunden; }
        set { _stunden = value; OnPropertyChanged(); }
    }


    private bool _ismodified = false;
    public bool ismodified
    {
        get { return _ismodified; }
        set
        {
            if (!value) { return; }
            else
            {
                OnPropertyChanged();
                if (App.sqlite_tage != null) { App.sqlite_tage.UpdateItem(new object[] { ref_recid, dat_day}, this); }
            }
        }
    }

    internal PRGD010_Tag() { }
    internal PRGD010_Tag(DateTime dat_day, string notes, Category cat_id, Classification class_id, long recid)
    {
        _dat_day = dat_day;
        _notes = notes;
        ref_cat_id = cat_id;
        ref_class_id = class_id;
        ref_recid = recid;
    }

    public override string ToString()
    {
        return string.Format("RECID: {0}; DATE: {1:yyyy-MM-dd}", ref_recid, dat_day);
    }
}

public class SETD010_Category : ModelBase
{
    private string _header = string.Empty;
    public string header
    {
        get { return _header; }
        set { if (OnPropertyChanged(ref _header, value)) ismodified = true; }
    }

    private string _notes = string.Empty;
    public string notes
    {
        get { return _notes; }
        set { if (OnPropertyChanged(ref _notes, value)) ismodified = true; }
    }

    private SolidColorBrush _color = new SolidColorBrush(Colors.Transparent);
    public SolidColorBrush color
    {
        get { return _color; }
        set { _color = value; OnPropertyChanged(); }
    }

    public string colorcode
    {
        get { return _color.Color.ToString(); }
        set { _color.Color = ColorHelper.StringToColor(value); }
    }

    private Category _category = Category.none;
    public Category category
    {
        get { return _category; }
        set { _category = value; OnPropertyChanged(); }
    }

    private bool _ismodified = false;
    public bool ismodified
    {
        get { return _ismodified; }
        set
        {
            if (!value) { return; }
            else
            {
                OnPropertyChanged();
                if (App.sqlite_categories != null) { App.sqlite_categories.UpdateItem((long)category, this); }
            }
        }
    }

    internal SETD010_Category() { }
    internal SETD010_Category(string header, string notes, string colorcode, Category category)
    {
        _header = header;
        _notes = notes;
        this.colorcode = colorcode;
        _category = category;
    }

    public override string ToString()
    {
        return String.Format("CAT_ID: {0}; HEADER: {1}", this.category, this.header);
    }
}

public class ModelBase : INotifyPropertyChanged
{
    public event PropertyChangedEventHandler PropertyChanged;

    protected void OnPropertyChanged([CallerMemberName] string propertyName = "")
    {
        OnPropertyChangedExplicit(propertyName);
    }

    protected bool OnPropertyChanged<T>(ref T current, T value, [CallerMemberName] string propertyName = "")
    {
        if (object.Equals(current, value))
            return false;

        current = value;
        OnPropertyChangedExplicit(propertyName);
        return true;
    }

    void OnPropertyChangedExplicit(string propertyName)
    {
        PropertyChangedEventHandler handler = this.PropertyChanged;
        if (handler != null)
        {
            var e = new PropertyChangedEventArgs(propertyName);
            handler(this, e);
        }
    }

    private object _collection;
    public object collection
    {   get { return _collection; }
        set { _collection = value; }
    }

    private object _sqlite;
    public object sqlite
    {
        get { return _sqlite; }
        set { _sqlite = value; }
    }

    virtual public bool add_item(object[] param)
    {
        return false;
    }

    virtual public bool del_item(object[] param)
    {
        return false;
    }
}

Aucun commentaire:

Enregistrer un commentaire