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

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