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类。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[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; }
标记 DataContract 特性是为了日后能将对象序列化为json。
继承 INotifyPropertyChanged 接口是为了能通知UI绑定对象的属性值发生变动。
支持 BaseModel<T> 泛型是为了父类的方法更好的返回子类型。
添加 ClientID 是为以后的子类准备个通用的本地自增长主键,区别于数据库主键。
标记 NoRemeberProperty 特性是为了让 本地sqlite在生成类型的map时略过一些不必要存储的属性。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public class NoRemeberProperty : Attribute { }
添加UsercAccount类。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[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"); } } }
标记 DataMember 特性是为了日后能将属性序列化为json。
集成 BaseModel 类是为了能省化部分代码。
添加Sss类到Services文件夹
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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); } } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public static string DBPath { get { //return "connectionDrive.sqlite"; return System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "connectionDrive.sqlite"); } }
并添加一个用于初始化数据库的静态方法,将其放置于app.xaml.cs 里的第53行,OnLaunched方法中。
--代码弄错了,多创建了几个表,请删除。
添加StatusCode枚举
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[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, }
标记 EnumMember 特性是为了让枚举能够序列化为json。
添加Local类到Services 文件夹
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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; } }
并添加一个用于添加和更新数据的静态方法。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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; } }
添加一个业务逻辑的代码。
添加WB类到Services文件夹
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[DataContract] public enum UserWork { [EnumMember] Login, } public enum WorkStatus { PostBegin, PostEnd, PostPause }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[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; } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 + "..." ; }
添加 请求方法。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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)) { //登录成功; } } }
登录按钮
网页Ashx文件的处理代码
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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; } }
终于写完了,不知道合不合适,先发出来看看。