• 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

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

  • 相关阅读:
    IP地址和进制转换
    Cisco交换机常见配置
    macOS上的autoreconf错误导致无法安装问题
    LG P5147 随机数生成器
    LG P1879 [USACO06NOV]Corn Fields G
    LG P5017 [NOIP2018]摆渡车
    mysql触发器trigger详解
    MybatisPuls中QueryWrapper的select、update的用法
    @Transactional各属性详解
    Linux如何查看进程、杀死进程、启动进程等常用命令(包括常用的命令,如查看文件,修改文件读写权限、寻找文件等)
  • 原文地址:https://www.cnblogs.com/landuo/p/5014248.html
Copyright © 2020-2023  润新知