样例完整代码
一、管理员管理模块
1、创建BLL层、DAL层、UI层、Model层
2、数据查询
(1)在UI层的app.config文件中添加连接数据库的字符串
<connectionStrings>
<add name="constr" connectionString="Server=.;
Database=Res_Manage;User Id=sa;Password=1234;"
providerName="System.Data.SqlClient"/>
</connectionStrings>
(2)在DAL层创建SQLHepler类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
namespace Res_Man_DAL
{
internal class SQLHelper
{
private static string conStr =
ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
//查询数据
public static DataTable ExecuteDataTable(string sql,params SqlParameter[] parameters )
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection( conStr ))
{
//conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
adapter.SelectCommand.Parameters.AddRange( parameters );
adapter.Fill(dt);
}
return dt;
}
}
}
(3)在DAL层创建ManagerInfoDAL类
需要一个方法 调用sqlHepler获取 dataTable,将dataTable中的数据转化成对象
using Res_Man_Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Res_Man_DAL
{
public class ManagerInfoDAL
{
//需要一个方法 调用sqlHepler获取 dataTable
//将dataTable中的数据转化成对象
public List<ManagerInfo> GetManager()
{
string sql = "select * from ManagerInfo";
//创建一个空对象集合
List<ManagerInfo> list = new List<ManagerInfo>();
//调用sqlHepler获取dataTable
DataTable dt = SQLHelper.ExecuteDataTable(sql);
//把DataTable转换成模型实体(表->对象)
foreach (DataRow dr in dt.Rows)
{
list.Add(new ManagerInfo()
{
MId = Convert.ToInt32(dr["MId"]),
MName = dr["MName"].ToString(),
MPwd = dr["MPwd"].ToString(),
MType = Convert.ToInt32(dr["MType"])
});
}
return list;
}
}
}
(4)在Model层创建对应的对象类ManagerInfo类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Res_Man_Model
{
public class ManagerInfo
{
public int MId { get; set; }
public string MName { get; set; }
public string MPwd { get; set; }
public int MType { get; set; }
}
}
(5)在BLL层创建ManagerInfoBLL类
创建方法供UI层访问数据
using Res_Man_DAL;
using Res_Man_Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace BLL
{
public class ManagerInfoBLL
{
public List<ManagerInfo> GetManagers()
{
ManagerInfoDAL managerInfoDAL = new ManagerInfoDAL();
return managerInfoDAL.GetManager();
}
}
}
(6)在UI层的主界面创建Form1_load()方法
获取从BLL层传递过来的数据
using Res_Man_Model;
using BLL;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace RestaurantManagerment
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
Form1_load();
}
private void Form1_load()
{
//获取从BLL层传递过来的数据
ManagerInfoBLL managerInfoBLL = new ManagerInfoBLL();
List<ManagerInfo> managerInfos = managerInfoBLL.GetManagers();
dgv_load.DataSource = managerInfos;
}
}
}
(7)查询展示
3、搭建UI
(1)设置dataGridView的列
修改数据源和显示文字。
(2)设置禁用自动生成列、自适应宽度
在UI层的Form1_load方法中添加:禁用dataGridView自动生成列,自适应宽度。
private void Form1_load()
{
//把自动生成列设置为false
dgv_load.AutoGenerateColumns = false;
//自动适应表宽度
dgv_load.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
//获取从BLL层传递过来的数据
ManagerInfoBLL managerInfoBLL = new ManagerInfoBLL();
List<ManagerInfo> managerInfos = managerInfoBLL.GetManagers();
dgv_load.DataSource = managerInfos;
}
(3)添加对应组件,设计UI
添加两个groupbox,以及内部其余小组件
4、添加数据
(1)将managerInfoBLL设为全局变量
ManagerInfoBLL managerInfoBLL = new ManagerInfoBLL();
Form1:放到Form1_load()方法外。
BLL层中的ManagerInfoDAL也设为全局变量。
UI层中的Form1的ManagerInfoBLL也设为全局变量。
(2)UI层设置添加按钮事件
为了方便操作后自动刷新,将查询进行封装。
public void InitalData()
{
List<ManagerInfo> managerInfos = managerInfoBLL.GetManagers();
dgv_load.DataSource = managerInfos;
}
添加点击事件
private void btn_add_Click(object sender, EventArgs e)
{
//添加数据
string MName = tb_UserName.Text.Trim();
string MPwd = tb_UserPwd.Text.Trim();
int MType = rbtn_jingli.Checked ? 1 : 0;
//构建对象
ManagerInfo addManagerInfo = new ManagerInfo()
{
MName = MName,
MPwd = MPwd,
MType = MType,
};
managerInfoBLL.Insert(addManagerInfo);
}
(3)在BLL层调用Insert插入函数
public bool Insert(ManagerInfo managerInfo)
{
//调用DAL层对象,进行数据库插入
return managerInfoDAL.Insert(managerInfo)>0;
}
(4)ManagerInfoDAL中实现Insert插入函数
public int Insert(ManagerInfo managerInfo)
{
string sql = "insert into ManagerInfo(MName,MPwd,MType) values(@MName,@MPwd,@MType)";
//构建参数
SqlParameter[] sqlParameters = new SqlParameter[]
{
new SqlParameter("@MName",managerInfo.MName),
new SqlParameter("@MPwd",managerInfo.MPwd),
new SqlParameter("@MType",managerInfo.MType)
};
int r = -1;
r = SQLHelper.ExecuteNonQuery(sql, sqlParameters);
return r;
}
(5)SqlHelper中添加函数实现查找和修改语句sql执行
public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters )
{
int r = -1;
using (SqlConnection conn = new SqlConnection(conStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
//添加参数
cmd.Parameters.AddRange ( parameters );
//打开链接
conn.Open();
r = cmd.ExecuteNonQuery();
}
}
return r;
}
5、修改数据
(1)UI层
From1:
private void btn_update_Click(object sender, EventArgs e)
{
//获取想修改的id
int mid =Convert.ToInt32 (dgv_load.SelectedRows[0].Cells[0].Value);
//拿到要修改的数据
string MName = tb_UserName.Text.Trim();
string MPwd = tb_UserPwd.Text.Trim();
int MType = rbtn_jingli.Checked ? 1 : 0;
//构建对象
ManagerInfo mi = new ManagerInfo()
{
MId = mid,
MName = MName,
MPwd = MPwd,
MType = MType,
};
//对象传递给BLL
bool b = managerInfoBLL.UpdateManagerInfo(mi);
if (b)
{
MessageBox.Show("修改成功");
}
else
{
MessageBox.Show("修改失败");
}
InitalData();
}
(2)BLL层
ManagerInfoBLL:
public bool UpdateManagerInfo(ManagerInfo mi)
{
return managerInfoDAL.UpdateManagerInfo(mi)>0;
}
(3)DAL层的ManagerInfoDAL
ManagerInfoDAL:
public int UpdateManagerInfo(ManagerInfo mi)
{
int r = -1;
//准备sql
string sql = "update ManagerInfo set MName=@MName,MPwd=@MPwd,MType=@MType where MId=@MId";
//准备参数
SqlParameter[] sqlParameters = new SqlParameter[]
{
new SqlParameter("@MName",mi.MName),
new SqlParameter("@MPwd",mi.MPwd),
new SqlParameter("@MType",mi.MType),
new SqlParameter("@MId",mi.MId)
};
//执行sqlhelper
r = SQLHelper.ExecuteNonQuery(sql, sqlParameters);
//返回结果
return r;
}
(4)效果图
(5)后续实现:点击选中dataGridView1某一行,在旁边的textbox里显示选中行的名字等属性信息
6、删除数据
(1)DAL层实现
ManagerDAL:
//删除数据
public int DeleteManagerInfo(int id)
{
string sql = "delete from ManagerInfo where MId=" + id;
return SQLHelper.ExecuteNonQuery(sql);
}
(2)BLL层
ManagerInfoBLL:
public bool DeleteManagerInfo(int id)
{
return managerInfoDAL.DeleteManagerInfo(id)>0 ;
}
(3)UI层
Form1:
private void btn_delete_Click(object sender, EventArgs e)
{
//获取想修改的id
int mid = Convert.ToInt32(dgv_load.SelectedRows[0].Cells["编号"].Value);
bool b = managerInfoBLL.DeleteManagerInfo(mid);
if (b)
{
MessageBox.Show("删除成功");
}
else
{
MessageBox.Show("删除失败");
}
InitalData();
}