AG-AIRS/usercontrol/Page_UserControl1.xaml.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace AIRS.usercontrol
{
    /// <summary>
    /// Page_UserControl1.xaml 的交互逻辑
    /// </summary>
    public partial class Page_UserControl1 : UserControl
    {
        public Page_UserControl1()
        {
            InitializeComponent();
        }
        #region 数据库连接对象
        /// <summary>
        /// 数据库连接对象
        /// </summary>
        SQLiteConnection conn = null;
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        SQLiteConnectionStringBuilder connstr = null;
        /// <summary>
        /// 当前工作目录
        /// </summary>
        private string path = Directory.GetCurrentDirectory(); 
        #endregion

        #region 分页变量
        /// <summary>
        /// 当前页码
        /// </summary>
        private int m_Page = 1;
        /// <summary>
        /// 总条数
        /// </summary>
        private int m_Total = 0;
        /// <summary>
        /// 总页数
        /// </summary>
        private int m_Count = 1;
        /// <summary>
        /// 每页条数
        /// </summary>
        private int m_Page_Count = 1; 
        #endregion
        
        #region 分页按钮事件

        /// <summary>
        /// 上一页
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void pro_btn_Click(object sender, RoutedEventArgs e)
        {
            if (m_Page == 1)
            {
                MessageBox.Show("已经是第一页！");
                return;
            }
            m_Page--;
            GetAllUser(m_Page, m_Page_Count);
        }

        /// <summary>
        /// 首页
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void first_btn_Click(object sender, RoutedEventArgs e)
        {
            if (m_Page == 1)
            {
                MessageBox.Show("已经是第一页！");
                return;
            }
            m_Page = 1;
            GetAllUser(m_Page, m_Page_Count);
        }

        /// <summary>
        /// 下一页
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>

        private void next_btn_Click(object sender, RoutedEventArgs e)
        {
            if (m_Page >= m_Count)
            {
                MessageBox.Show("已经是最后一页！");
                return;
            }
            m_Page++;
            GetAllUser(m_Page, m_Page_Count);
        }

        /// <summary>
        /// 尾页
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>

        private void last_btn_Click(object sender, RoutedEventArgs e)
        {
            if (m_Page >= m_Count)
            {
                MessageBox.Show("已经是最后一页！");
                return;
            }
            m_Page = m_Count;
            GetAllUser(m_Page,m_Page_Count);
        }

        #endregion

        #region 数据库操作

        /// <summary>
        /// 获取数据库连接对象
        /// </summary>
        /// <returns></returns>
        private SQLiteConnection GetConnection()
        {
            SQLiteConnection con = new SQLiteConnection();
            SQLiteConnectionStringBuilder constr = new SQLiteConnectionStringBuilder();
            constr.DataSource = path + "\\datas.db";
            con.ConnectionString = constr.ToString();
            con.Open();
            return con;
        }

        /// <summary>
        /// 获取所有用户
        /// </summary>
        /// <param name="page">当前页码</param>
        /// <param name="count">每页条数</param>
        private void GetAllUser(int page,int count)
        {
            try
            {
                conn = GetConnection();
                string strSql = "select * from t_username where username!='admin' limit " + ((page - 1) * count) + "," + count + "";
                SQLiteDataAdapter da = new SQLiteDataAdapter(strSql, conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                string strcountSql = "select count(*) as count from t_username where username!='admin' ";
                da = new SQLiteDataAdapter(strcountSql, conn);
                DataTable dtcount = new DataTable();
                da.Fill(dtcount);

                if (dtcount.Rows.Count>0)
                {
                    m_Total = Convert.ToInt32(dtcount.Rows[0][0]);//总条数
                    m_Count = (int)Math.Ceiling(((double)m_Total /(double) m_Page_Count));
                }

                List<RowData> list = new List<RowData>();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    RowData dar = new RowData();
                    dar.username = dt.Rows[i]["username"].ToString();
                    dar.password = dt.Rows[i]["password"].ToString();
                    list.Add(dar);
                }
                date_grid.DataContext = list;

                //this.pageuser.FontSize = 36;
                this.pageuser.Page = m_Page;
                //this.pageuser.PageCount = m_Count;
                this.pageuser.PageSize = m_Page_Count;
                this.pageuser.RecordCount = m_Total;

            }
            catch (Exception ex)
            {

            }
            finally
            {
                try
                {

                    conn.Close();
                }
                catch
                {
                }
            }
        }
        /// <summary>
        /// 删除指定用户
        /// </summary>
        /// <returns></returns>
        private bool DelUserByName(string username)
        {
            try
            {
                conn = GetConnection();
                SQLiteCommand cmd = conn.CreateCommand();
                cmd.CommandText = "delete from t_username where username=@username";
                cmd.Parameters.Add(new SQLiteParameter("@username"));
                cmd.Parameters["@username"].Value = username;
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {

            }
            finally
            {
                try
                {

                    conn.Close();
                }
                catch
                {
                }
            }
            return false;
        }

        #endregion

        /// <summary>
        /// 序号列
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void date_grid_LoadingRow(object sender, DataGridRowEventArgs e)
        {
            e.Row.Header = e.Row.GetIndex() + 1;
        }

        /// <summary>
        /// 查询按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_query_Copy_Click(object sender, RoutedEventArgs e)
        {
            m_Page = 1;
            GetAllUser(m_Page, m_Page_Count);
        }


        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_add_Click(object sender, RoutedEventArgs e)
        {
            UpUser us = new UpUser();
            us.BtnLogin.Content = "添加";
            us.titel.Content = "添加检察员";
            us.ShowInTaskbar = false;
            us.conn = GetConnection();
            us.ShowDialog();
            if (us.DialogResult == true)
            {
                MessageBox.Show("添加成功！");
                m_Page = 1;
                GetAllUser(m_Page, m_Page_Count);
                try
                {
                    us.conn.Close();
                }
                catch
                {
                }
            }
        }

        /// <summary>
        /// 修改用户
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_Click(object sender, RoutedEventArgs e)
        {
            string username = ((Button)sender).Tag == null ? "" : ((Button)sender).Tag.ToString();
            if (username.Equals("admin"))
            {
                MessageBox.Show("管理员不可以修改！");
                return;
            }
            UpUser us = new UpUser();
            us.BtnLogin.Content = "修改";
            us.titel.Content = "修改检察员";
            us.ShowInTaskbar = false;
            us.username = username;
            us.conn = GetConnection();
            us.cbbLoginname.Text = username;
            us.ShowDialog();
            if (us.DialogResult == true)
            {
                MessageBox.Show("修改成功！");
                m_Page = 1;
                GetAllUser(m_Page, m_Page_Count);
                try
                {
                    us.conn.Close();
                }
                catch
                {
                }
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_Click_1(object sender, RoutedEventArgs e)
        {
            string username = ((Button)sender).Tag == null ? "" : ((Button)sender).Tag.ToString();
            if (username.Equals("admin"))
            {
                MessageBox.Show("管理员不可以删除！");
                return;
            }

            if (DelUserByName(username))
            {
                MessageBox.Show("删除成功！");
                m_Page = 1;
                GetAllUser(m_Page, m_Page_Count);
            }
            else
            {
                MessageBox.Show("删除失败！");
            }

        }
        /// <summary>
        /// 窗体加载完后执行
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void UserControl_Loaded(object sender, RoutedEventArgs e)
        {
            this.pageuser.PageChanged += new EventHandler<PageChangedEventArgs>(Eventpage);
            GetAllUser(m_Page, m_Page_Count);
        }

        /// <summary>
        /// 自定义分页事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="a"></param>
        public void Eventpage(object sender, PageChangedEventArgs a)
        {
            m_Page = a.Page;
            GetAllUser(m_Page, m_Page_Count);
        }
    }
    #region 显示列类
    /// <summary>
    /// 显示列类
    /// </summary>
    public class RowData
    {
        /// <summary>
        /// 用户名
        /// </summary>
        public string username { get; set; }
        /// <summary>
        /// 密码
        /// </summary>
        public string password { get; set; }
    }

    #endregion
}