基于三层构架的桌面软件开发
三层架构分为：表现层（UI）、业务逻辑层（BLL）、数据访问层（DAL）、实体类库（Model）
分层次的目的：为了“高内聚低耦合”的思想
- 表示层（UI）：主要对用户的请求接受，以及数据的返回，为客户端提供应用程序的访问。
- 业务逻辑层（BLL）：主要负责对数据层的操作。也就是说把一些数据层的操作进行组合。
- 数据访问层（DAL）：主要看数据层里面有没有包含逻辑处理，实际上它的各个函数主要完成各个对数据文件的操作。
各层之间的关系
搭建三层
建立项目，按顺序添加层，每添加一层删除 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;
}
}至此基本功能完成，下面介绍一个简单的练习数据库的增删改查
增删改查
点击会员管理，实现对会员的增删改查
点击会员管理，出现会员管理窗体
1加载
数据库中会员表，表名为MemberInfo
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;
}
自此三层框架中的增删改查介绍完毕。
679
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
