你的位置:首页 > ASP.net教程

[ASP.net教程]Windows UWP应用使用本地Sqlite和远程Sql(一)


贫猿注册博客园有三年多了,第一次写博客,版式尽量控制的简单点。

本系列文章是简单的记录一下《账簿》本身所运用到的操作本地sqlite和远程sql的代码和结构。

首先的准备工作

安装Sqlite for UWP扩展

从菜单栏找到工具-扩展和更新。在搜索框填写sqlite,在结果里找到“sqlite for Universal App Platform”并安装它。

新建一个8.1的windows 应用,并添加sqlite for windows runtime(8.1)的支持。这时会自动生成SQLiteAsync.cs 和SQLite.cs俩文件,把他们复制到windows10 UWP项目里即。

 

准备解决方案和项目

新建一个通用的空白应用,添加一个名叫Models的文件夹。并为这个项目添加sqlite for Universal App Platform 的引用。

添加一个名叫Services的文件夹,添加现有项SQLiteAsync.cs 和SQLite.cs。

新建一个8.1的windows 应用,并添加sqlite for windows runtime(8.1)的支持。这时会自动生成SQLiteAsync.cs 和SQLite.cs俩文件,把他们复制到windows10 UWP项目里即。

添加BaseModel类。

[DataContract]  public partial class BaseModel<T> : INotifyPropertyChanged where T : class  {    public event PropertyChangedEventHandler PropertyChanged;    public void OnPro(string pName)    {      if (this.PropertyChanged != null)        this.PropertyChanged(this, new PropertyChangedEventArgs(pName));    }    public static T FromJson(string json)    {      using (var ms = new MemoryStream(Encoding.UTF8.GetBytes(json)))      {        var t = JSON.ReadObject(ms);        return t as T;      }    }    public override string ToString()    {      return ToJson();    }    public string ToJson()    {      var t = this;      string json = "";      using (var ms = new MemoryStream())      {        JSON.WriteObject(ms, t);        var array = ms.ToArray();        json = Encoding.UTF8.GetString(array, 0, array.Length);      }      return json;    }    public static DataContractJsonSerializer JSON = new DataContractJsonSerializer(typeof(T));    [NoRemeberProperty]    public bool IsSelected    {      get      {        return _IsSelected;      }      set      {        _IsSelected = value;        OnPro("IsSelected");      }    }    [AutoIncrement, PrimaryKey]    public int ClientId    {      get      {        return _ClientId;      }      set      {        _ClientId = value;      }    }    private bool _IsSelected;    private int _ClientId;  }

View Code

标记 DataContract 特性是为了日后能将对象序列化为json。

继承 INotifyPropertyChanged 接口是为了能通知UI绑定对象的属性值发生变动。

支持 BaseModel<T> 泛型是为了父类的方法更好的返回子类型。

添加 ClientID 是为以后的子类准备个通用的本地自增长主键,区别于数据库主键。

标记 NoRemeberProperty 特性是为了让 本地sqlite在生成类型的map时略过一些不必要存储的属性。

 public class NoRemeberProperty : Attribute  {  }

View Code

添加UsercAccount类。

[DataContract]  public class UserAccount : BaseModel<UserAccount>  {    private string _Name;    private string _Email;    private string _Password;    [DataMember]    public string Name    {      get      {        return _Name;      }      set      {        _Name = value; OnPro("Name");      }    }    [DataMember]    public string Email    {      get      {        return _Email;      }      set      {        _Email = value; OnPro("Email");      }    }    [DataMember]    public string Password    {      get      {        return _Password;      }      set      {        _Password = value; OnPro("Password");      }    }  }

View Code

标记 DataMember 特性是为了日后能将属性序列化为json。

集成 BaseModel 类是为了能省化部分代码。

添加Sss类到Services文件夹

public async static void InitDataBase()    {      bool isNeedCreate = false;      StorageFile sf = null;      try      {        sf = await StorageFile.GetFileFromPathAsync(DBPath);      }      catch (FileNotFoundException ex) //文件不存在      {        isNeedCreate = true;      }      if (isNeedCreate)      {        try        {          Setting.Values.Clear();          var db = new SQLiteAsyncConnection(DBPath);          await db.CreateTableAsync<RoundTask>();          await db.CreateTableAsync<AssetChanges>();          await db.CreateTableAsync<UserAccount>();          await db.CreateTableAsync<MoneyInfo>();        }        catch (Exception er) //试图加载格式不正确的程序        {          sf.DeleteAsync();          OnException("初始化数据库失败", er);          Sss.WriteException("sss.initdatebase", er);        }      }    }

View Code
public static string DBPath    {      get      {        //return "connectionDrive.sqlite";        return System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "connectionDrive.sqlite");      }    }

View Code

并添加一个用于初始化数据库的静态方法,将其放置于app.xaml.cs 里的第53行,OnLaunched方法中。

--代码弄错了,多创建了几个表,请删除。

添加StatusCode枚举

[DataContract]  public enum StatusCode  {    /// <summary>    /// 账户不存在    /// </summary>    [EnumMember]    UserAccount_NotExists,    /// <summary>    /// 密码错误    /// </summary>    [EnumMember]    Password_Error,    /// <summary>    /// 用户账户已存在    /// </summary>    [EnumMember]    UserAccount_Exists,    /// <summary>    /// 网络错误致使数据传输出错或失败    /// </summary>    [EnumMember]    Network_Error,    /// <summary>    /// 未初始化请求    /// </summary>    [EnumMember]    None,    /// <summary>    /// 已成功提交并执行请求    /// </summary>    [EnumMember]    Completed,    /// <summary>    /// 操作失败    /// </summary>    [EnumMember]    Error,    /// <summary>    /// 已停止支持该版本,请更新你的程序    /// </summary>    [EnumMember]    Application_Stop,  }

View Code

 

标记 EnumMember 特性是为了让枚举能够序列化为json。

添加Local类到Services 文件夹

public static SQLiteAsyncConnection db = new SQLiteAsyncConnection(Sss.DBPath);    public static async Task<StatusCode> AddObject(object obj)    {      try      {        await db.InsertAsync(obj);        OnAddedObject(obj);        return StatusCode.Completed;      }      catch (Exception e)      {        Sss.WriteException("local.addobject", e);        return StatusCode.Error;      }    }    public static async Task<StatusCode> UpdateObject(object obj)    {      try      {        await db.UpdateAsync(obj);        OnUpdatedObject(obj);        return StatusCode.Completed;      }      catch (Exception e)      {        Sss.WriteException("local.updateobject", e);        return StatusCode.Error;      }    }

View Code

 

并添加一个用于添加和更新数据的静态方法。

public static async Task<List<string>> GetEmails()    {      try      {        var sql = "select * from UserAccount";        var rs = await db.QueryAsync<UserAccount>(sql);        return rs.Select(c => c.Email).ToList();      }      catch (Exception e)      {        return null;      }    }    public static async Task<bool> CanLogin(string email, string pwd)    {      try      {        var sql = "select * from UserAccount where email = '" + email + "' and password = '" + pwd + "'";        var rs = await db.QueryAsync<UserAccount>(sql);        return rs.Count == 1;      }      catch (Exception e)      {        return false;      }    }

View Code

添加一个业务逻辑的代码。

添加WB类到Services文件夹

[DataContract]  public enum UserWork  {    [EnumMember]    Login,}public enum WorkStatus  {    PostBegin,    PostEnd,    PostPause  }

View Code
 [DataContract]  public class HR  {    private Dictionary<string, object> Values = new Dictionary<string, object>();    [DataMember]    public string Source    {      get      {        var s = "";        foreach (var t in Values)        {          s += t.Key + "=" + t.Value + "&";        }        if (s.EndsWith("&"))        {          s = s.Substring(0, s.Length - 1);        }        return s;      }      set      {        Values.Clear();        foreach (var t in value.Split('&'))        {          var s = t.Split('=');          Values.Add(s[0], s[1]);        }      }    }    public StatusCode Status    {      get      {        if (this.Values.ContainsKey("Status"))          return (StatusCode)Enum.Parse(typeof(StatusCode), this["Status"].ToString());        else          return StatusCode.None;      }      set { this["Status"] = value.ToString(); }    }    public object this[string key]    {      get      {        if (this.Values.ContainsKey(key))          return this.Values[key];        else          return string.Empty;      }      set      {        if (!this.Values.ContainsKey(key))          this.Values.Add(key, String.Empty);        this.Values[key] = value;      }    }    public T Get<T>(string key) where T : class    {      return this.Values[key] as T;    }  }

View Code

 

private static string _workUri = "http://localhost:9009/work.ashx";    private static string _version = "1";    public static event EventHandler<WorkStatus> WorkStatusChanged;    public static string Version    {      get { return WB._version; }      set { WB._version = value; }    }    public static string WorkUri    {      get      {        if (Sss.WorkUir != null)          _workUri = Sss.WorkUir;        return _workUri;      }      set      {        _workUri = value;        Sss.WorkUir = _workUri;      }    }    private static void OnWorkStatusChanged(UserWork work, WorkStatus status)    {      if (WorkStatusChanged != null)      {        WorkStatusChanged(work, status);      }    }    private async static Task<String> Post(string uri, HttpFormUrlEncodedContent args)    {      HttpClient hc = new HttpClient();      var r = await hc.PostAsync(new Uri(WorkUri), args) as HttpResponseMessage;      return await r.Content.ReadAsStringAsync();    }    public async static Task<HR> Post(UserWork type, params object[] args)    {      HR hr = null;      try      {        OnWorkStatusChanged(type, WorkStatus.PostBegin);        var pd = GetData(type, args);        hr = new HR() { Source = await Post(WorkUri, GetData(type, args)) };        return hr;      }      catch (Exception er)      {        return new HR() { Status = StatusCode.Network_Error };      }      finally      {        //if (hr["msg"].ToString().Length > 0)        //  await Sss.Show("", hr["msg"].ToString(), Sss.OkCmd);        OnWorkStatusChanged(type, WorkStatus.PostEnd);      }    }    public static HttpFormUrlEncodedContent GetData(UserWork type, params object[] args)    {      var lst = new List<KeyValuePair<string, string>>();      lst.Add(new KeyValuePair<string, string>("type", type.ToString()));      lst.Add(new KeyValuePair<string, string>("version", Version.ToString()));      if (type == UserWork.Login)      {        lst.Add(new KeyValuePair<string, string>("UserAccount", args.Where(c => c is UserAccount).First().ToString()));      }      return new HttpFormUrlEncodedContent(lst);     }public static string GetStatusText(UserWork type)    {      string status = "";      switch (type)      {        case UserWork.Login:          status += "正在登录";          break;       }   return status + "..." ;}

View Code

 

 添加 请求方法。

    public bool 是否有网 { get; set; }    private async void button_Click(object sender, RoutedEventArgs e)    {      if (是否有网)      {        var hr = await WB.Post(UserWork.Login, new UserAccount() { Email = txtEmail.Text, Password = pwd.Password });         if (hr.Status == StatusCode.Completed)        {          //登录成功;        }      }      else      {        if (Local.CanLogin(txtEmail.Text, pwd.Password))        {          //登录成功;        }      }    }

View Code

登录按钮

网页Ashx文件的处理代码

public void ProcessRequest(HttpContext context)    {      var h = new HR();      h.Status = StatusCode.None;      try      {var type = (UserWork)Enum.Parse(typeof(UserWork), context.Request["type"]);switch (type)      {        case UserWork.Login:          Login(context, h);          break;}       }      catch (Exception e)      {        h.Status = StatusCode.Error;        h["msg"] = e.Message + (e.InnerException == null ? "" : e.InnerException.Message);      }      finally      {        context.Response.Write(h.Source);        context.Response.End();      }    }private static StatusCode Login(HttpContext context, HR h)    {      var ut = Sss.FromJson<UserAccount>(context.Request["UserAccount"]);      if (udao.IsExists(ut.Email))      {        if (udao.Login(ut))        {          h.Status = StatusCode.Completed;          h["UserAccount"] = ut.ToString();        }        else        {          h.Status = StatusCode.Password_Error;        }      }      else      {        h.Status = StatusCode.UserAccount_NotExists;      }    }

View Code

 

 

终于写完了,不知道合不合适,先发出来看看。