桌面软件架构模版桌面程序架构

转载

langrisser 2023-12-25 08:59:46

文章标签 桌面软件架构模版三层 winform SQL sql 文章分类 架构后端开发

基于三层构架的桌面软件开发

三层架构分为：表现层（UI）、业务逻辑层（BLL）、数据访问层（DAL）、实体类库（Model）
分层次的目的：为了“高内聚低耦合”的思想

表示层（UI）：主要对用户的请求接受，以及数据的返回，为客户端提供应用程序的访问。
业务逻辑层（BLL）：主要负责对数据层的操作。也就是说把一些数据层的操作进行组合。
数据访问层（DAL）：主要看数据层里面有没有包含逻辑处理，实际上它的各个函数主要完成各个对数据文件的操作。

各层之间的关系

桌面软件架构模版桌面程序架构_SQL

搭建三层

建立项目，按顺序添加层，每添加一层删除 Programe.cs。

建立其他项目类型-空白解决方法TestFloor
添加TestFloorModel项目类库，右击属性-创建名称空间修改为TestFloor.Model
依次添加TestFloorDAL，TestFloorBLL，UI层即为TestFloor不需要修改
创建联系，TestFloorDAL引用项目TestFloorModel，TestFloorBLL引用项目TestFloorDAL和TestFloorModel，TestFloor引用项目TestFloorModel和TestFloorBLL，其中修改Forms类，直接重命名
在文件夹目录新建DLL文件夹，将外部dll复制过来
在UI中添加应用配置文件App.config用于连接数据库，修改文件内容，将数据库参数内容写入configuration中

<connectionStrings> 
    <add connectionString="Data Source=cater.db;Version=3;" name="conStr"/>
</connectionStrings>

将数据库文件.db放入到项目UI层，BIN-DEBUG文件夹中
在DAL项目添加引用，SQLite.dll，添加类SqliteHelper，在类头添加using System.Data.SQLite;using System.Data
编写SqliteHelper类

public class SqliteHelper
    {
        //连接字符串
        private static readonly string str = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        /// <summary>
        /// 增删该
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(string sql, params SQLiteParameter[] param)
        {
            using(SQLiteConnection con= new SQLiteConnection(str))//使用完自动释放资源
            {
                using (SQLiteCommand cmd=new SQLiteCommand(sql,con))
                {
                    con.Open();
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns>首行首列</returns>
        public static object ExecuteScalar(string sql, params SQLiteParameter[] param)
        {
            using (SQLiteConnection con = new SQLiteConnection(str))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
                {
                    con.Open();
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    return cmd.ExecuteScalar();
                }
            }
        }
        /// <summary>
        /// 多行查询
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns>SQLiteDataReader</returns>
        public static SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] param)
        {
            SQLiteConnection con = new SQLiteConnection(str);
            using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
            {
                if (param != null)
                {
                    cmd.Parameters.AddRange(param);
                }
                try
                {
                    con.Open();
                    return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                }
                catch (Exception ex)
                {
                    con.Close();
                    con.Dispose();
                    throw ex;
                }
            }
        }
        /// <summary>
        /// 查询多行数据
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns>一个表À</returns>
        public static DataTable ExecuteTable(string sql,params SQLiteParameter[] param)
        {
            DataTable dt = new DataTable();
            using (SQLiteDataAdapter sda = new SQLiteDataAdapter(sql,str))
            {
                if (param != null)
                {
                    sda.SelectCommand.Parameters.AddRange(param);
                }
                sda.Fill(dt);
            }
            return dt;
        }
    }

至此基本功能完成，下面介绍一个简单的练习数据库的增删改查

增删改查

点击会员管理，实现对会员的增删改查

桌面软件架构模版桌面程序架构_三层_02

点击会员管理，出现会员管理窗体

桌面软件架构模版桌面程序架构_winform_03

1加载

数据库中会员表，表名为MemberInfo

桌面软件架构模版桌面程序架构_桌面软件架构模版_04

private void btnMember_Click(object sender, EventArgs e)
{
    FrmMmemberInfo fm =new FrmMmemberInfo();
    fm.ShowDialog();
}

在窗口加载时需要将现有会员显示出来，若要将所有会员显示出来，传入标识符，返回会员列表

private void FrmMmemberInfo_Load(object sender, EventArgs e)
{
    //加载会员
    LoadMemberInfoByDelflag(0);
}

下面要去MODEL中根据表名新建一个model类，MemmberInfo类，该类中成员都为列名

public class MemmberInfo
    {
        //MemberId MemName MemMobilePhone MemAdress MemType MemNum MemGender MemDiscount MemMoney DelFlag SubTime MemIntegral MemEndServerTime MemBirthdaty
        #region Model
        private int _memmberid;
        private string _memname;
        private string _memphone;
        private string _memmobilephone;
        private string _memaddress;
        private int _memtype;
        private string _memnum;
        private string _memgender;
        private decimal? _memdiscount = 1.00M;
        private decimal? _memmoney = 0M;
        private int? _delflag;
        private DateTime? _subtime;
        private int? _memintegral;
        private DateTime? _memendservertime;
        private DateTime? _membirthdaty;

        //冗余属性
        public string MemTpName
        {
            get;
            set;
        }
        /// <summary>
        /// 会员主键id
        /// </summary>
        public int MemmberId
        {
            set { _memmberid = value; }
            get { return _memmberid; }
        }
        /// <summary>
        /// 会员名字
        /// </summary>
        public string MemName
        {
            set { _memname = value; }
            get { return _memname; }
        }
        /// <summary>
        /// 会员电话
        /// </summary>
        public string MemPhone
        {
            set { _memphone = value; }
            get { return _memphone; }
        }
        /// <summary>
        /// 会员手机
        /// </summary>
        public string MemMobilePhone
        {
            set { _memmobilephone = value; }
            get { return _memmobilephone; }
        }
        /// <summary>
        /// 会员地址
        /// </summary>
        public string MemAddress
        {
            set { _memaddress = value; }
            get { return _memaddress; }
        }
        /// <summary>
        /// 会员类型
        /// </summary>
        public int MemType
        {
            set { _memtype = value; }
            get { return _memtype; }
        }
        /// <summary>
        /// 会员编号
        /// </summary>
        public string MemNum
        {
            set { _memnum = value; }
            get { return _memnum; }
        }
        /// <summary>
        /// 会员性别
        /// </summary>
        public string MemGender
        {
            set { _memgender = value; }
            get { return _memgender; }
        }
        /// <summary>
        /// 会员折扣
        /// </summary>
        public decimal? MemDiscount
        {
            set { _memdiscount = value; }
            get { return _memdiscount; }
        }
        /// <summary>
        /// 会员余额
        /// </summary>
        public decimal? MemMoney
        {
            set { _memmoney = value; }
            get { return _memmoney; }
        }
        /// <summary>
        /// 会员删除标识
        /// </summary>
        public int? DelFlag
        {
            set { _delflag = value; }
            get { return _delflag; }
        }
        /// <summary>
        /// 会员提交时间
        /// </summary>
        public DateTime? SubTime
        {
            set { _subtime = value; }
            get { return _subtime; }
        }
        /// <summary>
        /// 会员积分
        /// </summary>
        public int? MemIntegral
        {
            set { _memintegral = value; }
            get { return _memintegral; }
        }
        /// <summary>
        /// 会员结束时间
        /// </summary>
        public DateTime? MemEndServerTime
        {
            set { _memendservertime = value; }
            get { return _memendservertime; }
        }
        /// <summary>
        /// 会员生日
        /// </summary>
        public DateTime? MemBirthdaty
        {
            set { _membirthdaty = value; }
            get { return _membirthdaty; }
        }
        #endregion Model
    }

在DAL层同样的方式新建MemmberInfoDAL类，将private改成public，创建公有方法GetAllMemmberInfoDelflag（）同时还涉及到一个行转对象的问题

public List<MemmberInfo> GetAllMemmberInfoDelflag(int delflag)
{
    string sql="select * from MemmberInfo where DelFlag=@DelFlag";
    DataTable dt=SqliteHelper.ExecuteTable(sql,new SQLiteParameter("@DelFlag",delflag));
    List<MemmberInfo> list=new List<MemmberInfo>();
    if(dt.Rows.Count>0)
    {
        foreach (DataRow item in dt.Rows)
        {
            MemmberInfo member=RowToMemberInfo(item); 
            if(list!=null)
            {
                list.Add(member);
            }
        }
    }
    return list;
}
/// <summary>
/// 成员转类
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
private MemmberInfo RowToMemberInfo(DataRow dr)
{
    MemmberInfo member=new MemmberInfo();
    member.DelFlag=Convert.ToInt32( dr["DelFlag"]);
    member.MemAddress=dr["MemAddress"].ToString();
    member.MemmberId=Convert.ToInt32(dr["MemmberId"]);
    //member.MemBirthdaty=Convert.ToDateTime(dr["MemBirthdaty"]);
    member.MemDiscount=Convert.ToDecimal(dr["MemDiscount"]);
    //member.MemEndServerTime=Convert.ToDateTime(dr["MemEndServerTime"]);
    member.MemGender=dr["MemGender"].ToString();
    //member.MemIntegral=Convert.ToInt32(dr["MemIntegral"]);
    member.MemMobilePhone=dr["MemMobilePhone"].ToString();
    member.MemMoney=Convert.ToDecimal(dr["MemMoney"]);;
    member.MemName=dr["MemName"].ToString();
    member.MemNum=dr["MemNum"].ToString();
    member.MemType=Convert.ToInt32(dr["MemType"]);
    member.SubTime=Convert.ToDateTime(dr["SubTime"]);  
    return member;
}

在BLL层同样的方式新建MemmberInfoBLL类，将private改成public，创建公有方法GetAllMemmberInfoDelflag（）

public List<MemmberInfo> GetAllMemmberInfoDelflag(int delflag)
{
    return dal.GetAllMemmberInfoDelflag(delflag);
}

在UI层中，加载数据

private void LoadMemberInfoByDelflag(int p)
{
    MemmberInfoBLL bll = new MemmberInfoBLL();
    dgvmember.AutoGenerateColumns = false;//静止自动生成列
    dgvmember.DataSource = bll.GetAllMemmberInfoDelflag(p);
    dgvmember.SelectedRows[0].Selected = false;//默认不选中
}

删除

删除首先要判断是否选中行，然后通过标识进行删除操作，UI层

private void btnDelete_Click(object sender, EventArgs e)
{
    //判断是否选中某行
    if (dgvmember.SelectedRows.Count > 0)
    {
        MemmberInfoBLL bll = new MemmberInfoBLL();
        int id = Convert.ToInt32(dgvmember.SelectedRows[0].Cells[0].Value);
        if (bll.DeleteMemberInfoByMemberID(id))
        {
            MessageBox.Show("操作成功");
            LoadMemberInfoByDelflag(0);
        }
        else
        {
            MessageBox.Show("操作失败");
        }
    }
    else 
    {
        MessageBox.Show("请先选中要删除的会员");
    }
}

同样到DAL和BLL层中编写DeleteMemberInfoByMemberID()方法
在DAL的MemmberInfoDAL类中

/// <summary>
/// 根据id删除会员
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int DeleteMemberInfoByMemberID(int id)
{
    //用户传的需要写参数，不是可以不用写参数
    string sql = "update MemmberInfo set DelFlag=1 where MemmberId=@MemmberId";
   return   SqliteHelper.ExecuteNonQuery(sql,new SQLiteParameter("@MemmberId",id));
}

在BLL的MemmberInfoBLL类中

/// <summary>
/// 根据ID删值
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool DeleteMemberInfoByMemberID(int id)
{
    return dal.DeleteMemberInfoByMemberID(id)>0 ? true:false;
}

增加和修改

增加和修改窗体

这两个功能可以放到一起做，通过事件向窗体传值FrmChanfeMember窗体

//为了传值在UI层新建消息类
//窗体传值用的类
public class FrmEventArgs : EventArgs
{
    /// <summary>
    /// 标识
    /// </summary>
    public int Temp { set; get; }
    /// <summary>
    /// 对象
    /// </summary>
    public Object obj { set; get; }
}
/// <summary>
/// 窗体传值事件
/// </summary>
public event EventHandler evt;
/// <summary>
/// 事件参数类
/// </summary>
FrmEventArgs fea = new FrmEventArgs();
//点击增加
private void btnAddMember_Click(object sender, EventArgs e)
{
//ShowFrmChangeMember(2);
fea.Temp = 2;
ShowFrmChangeMember();
}
//点击修改
private void btnUpdeMember_Click(object sender, EventArgs e)
{
    if (dgvmember.SelectedRows.Count > 0)
    {
        int id = Convert.ToInt32(dgvmember.SelectedRows[0].Cells[0].Value);
        //不能从表中直接拿值，要从数据库中查询
        MemmberInfoBLL bll = new MemmberInfoBLL();
        fea.Temp = 1;
        fea.obj = bll.GetMemberInfoByID(id);//从数据库中根据ID取值
        ShowFrmChangeMember();
    }
    else
    {
        MessageBox.Show("请选中要修改的会员");
    }
    //ShowFrmChangeMember(1);

}
private void ShowFrmChangeMember()
{
    FrmChanfeMember fcm = new FrmChanfeMember();
    this.evt += new EventHandler(fcm.SetText);

    //fea.Temp = p;//新增或修改的表示
    if (this.evt != null)//执行事件之前不能为空
    {
        this.evt(this, fea);//执行事件

    }
    //fcm.ShowDialog();他关闭将子窗口中值传递给父类窗口
    fcm.FormClosed += new FormClosedEventHandler(fcmFrmClosed);
    fcm.ShowDialog();
}
//窗口关闭更新界面
private  void fcmFrmClosed(object sender,EventArgs e)
{
    LoadMemberInfoByDelflag(0);
 }

在FrmChanfeMember中传值函数

public void SetText(object sender,EventArgs e)
{
    loadMemmberType();
    FrmEventArgs fea = e as FrmEventArgs;
    temp = fea.Temp;
    //if (fea.Temp == 2)//新增修改
    //{

    foreach (var item in this.Controls)
    {
        if (item is TextBox)
        {
            TextBox tb = item as TextBox;
            tb.Text = "";
        }
    }
    //}
    if(fea.Temp==1)
    {
        MemmberInfo member=fea.obj as MemmberInfo;
        if (member != null)
        {
            txtMemNum.Text = member.MemNum.ToString();
            txtAddress.Text = member.MemAddress.ToString();
            txtMemDiscount.Text = member.MemDiscount.ToString();
            txtMemIntegral.Text = member.MemIntegral.ToString();
            txtmemMoney.Text = member.MemMoney.ToString();
            txtMemName.Text = member.MemName.ToString();
            txtMemPhone.Text = member.MemMobilePhone.ToString();
            txtBirs.Text = member.MemBirthdaty.ToString();
            cmbMemType.SelectedIndex = Convert.ToInt32(member.MemType);
            rdoMan.Checked = member.MemGender=="男"?true:false;
            rdoWomen.Checked=member.MemGender=="女"?true:false;
            labId.Text = member.MemmberId.ToString();//将ID存起来
        }


    }
    else
    {
        txtMemIntegral.Text = "0";
    }

}
public void loadMemmberType()
{
    MemmberTypeBLL bll = new MemmberTypeBLL();
    List<MemmberType> list = bll.GetMemmberType();
    list.Insert(0, new MemmberType() { MemType = -1, MemTpName = "请选择" });
    cmbMemType.DataSource = list;
    cmbMemType.DisplayMember = "MemTpName";
    cmbMemType.ValueMember = "MemType";
}
/// <summary>
/// 判断是新增还是修改
/// </summary>
private int temp{set;get;}
private void btnOk_Click(object sender, EventArgs e)
{
    //获取会员信息
    //每个文本框不能为空
    //判断性别
    MemmberInfo mem=new MemmberInfo();
    if(CheckMemmberTextEmpty())
    {
        mem.MemAddress = txtAddress.Text;
        mem.MemBirthdaty = Convert.ToDateTime(txtBirs.Text);
        mem.MemDiscount = Convert.ToDecimal(txtMemDiscount.Text);
        mem.MemEndServerTime = dtEndServerTime.Value;
        mem.MemGender = CheckGender();
        mem.MemIntegral = Convert.ToInt32(txtMemIntegral.Text);
        mem.MemMobilePhone = txtMemPhone.Text;
        mem.MemMoney = Convert.ToDecimal(txtmemMoney.Text);
        mem.MemName = txtMemName.Text;
        mem.MemNum = txtMemNum.Text;
        mem.MemType = Convert.ToInt32(cmbMemType.SelectedIndex);
    }
    MemmberInfoBLL bll = new MemmberInfoBLL();
    if (temp == 2)
    {
        mem.DelFlag = 0;
        mem.SubTime = System.DateTime.Now; 
    }
    if (temp == 1)
    {
        mem.MemmberId = Convert.ToInt32(labId.Text);
    }
    string str = bll.SaveMember(mem, this.temp) ? "操作成功" : "操作失败";
    MessageBox.Show(str);
    this.Close();
}
public bool CheckMemmberTextEmpty()
{
    if (string.IsNullOrEmpty(txtBirs.Text))
    {
        MessageBox.Show("生日不能为空");
        return false;
    }
    if (string.IsNullOrEmpty(txtMemDiscount.Text))
    {
        MessageBox.Show("折扣不能为空");
        return false;
    }
    if (string.IsNullOrEmpty(txtMemIntegral.Text))
    {
        MessageBox.Show("积分不能为空");
        return false;
    }
    if (string.IsNullOrEmpty(txtmemMoney.Text))
    {
        MessageBox.Show("余额不能为空");
        return false;
    }
    if (string.IsNullOrEmpty(txtMemName.Text))
    {
        MessageBox.Show("名字不能为空");
        return false;

    }
    if (string.IsNullOrEmpty(txtMemNum.Text))
    {
        MessageBox.Show("编号不能为空");
        return false;
    }
    if (string.IsNullOrEmpty(txtMemPhone.Text))
    {
        MessageBox.Show("电话不能为空");
        return false;
    }
    if (string.IsNullOrEmpty(dtEndServerTime.Text))
    {
        MessageBox.Show("有效期不能为空");
        return false;
    }
    return true;
}
public string CheckGender()
{
    string str="";
    if (rdoMan.Checked)
    {
         str = "男";
    }
    if(rdoWomen.Checked)
    {
         str = "女";
    }
    return str;
}

点击OK将数据保存数据库，BLL层在该层中要进行逻辑判断是新增还是修改

public bool SaveMember(MemmberInfo memmber, int temp)
{
    int flag = -1;
    if (temp == 2)
    {
       flag= dal.AddMemmberInfo(memmber);
    }
    if (temp == 1)
    {
        flag= dal.UpdataAddMemberInfo(memmber);
    }
    return flag>0?true:false;
}
/// <summary>
/// 根据id查值
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public MemmberInfo GetMemberInfoByID(int id)
{
    return dal.GetMemberInfoByID(id);
}

DAL层

public int AddMemmberInfo(MemmberInfo memmber)
{
    string sql = "insert into MemmberInfo(MemName,MemMobilePhone,MemAddress,MemType,MemNum,MemGender,MemDiscount,MemMoney,DelFlag,SubTime,MemIntegral,MemEndServerTime,MemBirthdaty)values(@MemName,@MemMobilePhone,@MemAddress,@MemType,@MemNum,@MemGender,@MemDiscount,@MemMoney,@DelFlag,@SubTime,@MemIntegral,@MemEndServerTime,@MemBirthdaty)";
    return AddAndUpdate(memmber, sql, 1);
}
public int UpdataAddMemberInfo(MemmberInfo memmber)
{
    string sql = "update MemmberInfo set MemName=@MemName,MemMobilePhone=@MemMobilePhone,MemAddress=@MemAddress,MemType=@MemType,MemNum=@MemNum,MemGender=@MemGender,MemDiscount=@MemDiscount,MemMoney=@MemMoney,MemIntegral=@MemIntegral,MemEndServerTime=@MemEndServerTime,MemBirthdaty=@MemBirthdaty where MemmberId=@MemmberId";
    return AddAndUpdate(memmber, sql, 2);
}
//新增和修改的合并方法
private int AddAndUpdate(MemmberInfo memmber, string sql, int temp)
{
    SQLiteParameter[] param = { 
          new SQLiteParameter("@MemName",memmber.MemName),
          new SQLiteParameter("@MemMobilePhone",memmber.MemMobilePhone),
           new SQLiteParameter("@MemAddress",memmber.MemAddress),
            new SQLiteParameter("@MemType",memmber.MemType),
             new SQLiteParameter("@MemNum",memmber.MemNum),
              new SQLiteParameter("@MemGender",memmber.MemGender),
               new SQLiteParameter("@MemDiscount",memmber.MemDiscount),
                new SQLiteParameter("@MemMoney",memmber.MemMoney),
                 // new SQLiteParameter("@MemmberId",memmber.MemmberId),
                      new SQLiteParameter("@MemIntegral",memmber.MemIntegral),
                      new SQLiteParameter("@MemEndServerTime",memmber.MemEndServerTime),
                      new SQLiteParameter("@MemBirthdaty",memmber.MemBirthdaty)

                              };
    List<SQLiteParameter> list = new List<SQLiteParameter>();
    list.AddRange(param);
    if (temp == 1)//新增
    {
        list.Add(new SQLiteParameter("@SubTime", memmber.SubTime));
        list.Add(new SQLiteParameter("@DelFlag", memmber.DelFlag));
    }
    else if (temp == 2)//修改
    {
        list.Add(new SQLiteParameter("@MemmberId", memmber.MemmberId));
    }

    return SqliteHelper.ExecuteNonQuery(sql, list.ToArray());
}
public MemmberInfo GetMemberInfoByID(int id)
{
    MemmberInfo member = null;
    string sql = "select * from MemmberInfo where MemmberId=@MemmberId";
    DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@MemmberId", id));
    if (dt.Rows.Count > 0)
    {
        member = RowToMemberInfo(dt.Rows[0]);
    }
    return member;
}

自此三层框架中的增删改查介绍完毕。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。