1. 项目结构
image.png注意:添加引用的时候从下往上添加
1)DAL添加Models的引用
2) BLL添加DAL和Models引用
3)UI添加BLL和Models引用
- Common类设计
image.png
添加Common类库时候遇到的问题using System.Windows.Forms;
报错
解决:在Common类库里面添加引用
image.png
2.项目编写
2.1 SqlHelper编写
2.2 管理员登陆
SysAdminService.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Models;
using DBUtility;
namespace DAL
{
/// <summary>
/// 管理员数据访问类
/// </summary>
public class SysAdminService
{
//传入管理员对象和返回管理员对象都是为了以后使用这个管理员方便
public SysAdmin AdminLogin(SysAdmin objAdmin)
{
//定义登陆的sql语句,使用带参数的sql语句
string sql = "select AdminName,StatusId,RoleId from SysAdmins where";
sql += "AdminId=@AdminId and LoginPwd=@LoginPwd";
//封装参数
SqlParameter[] sqlParam = new SqlParameter[]
{
new SqlParameter("@AdminId",objAdmin.AdminId),
new SqlParameter("@LoginPwd",objAdmin.LoginPwd),
};
//执行查询
SqlDataReader ojbReader = SQLHelper.GetReader(sql, sqlParam);
//处理查询结果
if (ojbReader.Read())
{
objAdmin.AdminName = ojbReader["AdminName"].ToString();
objAdmin.StatusId =Convert.ToInt32(ojbReader["StatusId"]);
objAdmin.RoleId =Convert.ToInt32(ojbReader["RoleId"]);
}
else
{
objAdmin = null;//登录失败,清空当前对象
}
ojbReader.Close(); //关闭读取器
return objAdmin; //返回结果
}
}
}
1.对于需要用到一个类里面的很多值的时候,使用实例化的类作为传值,返回值
2. 注意带参数的sql调用方法,必须熟练
3. 登录失败之类的,要清空类
- 管理员登陆BLL层
BLL-SysAdminManager
using DAL;
using Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace BLL
{
public class SysAdminManager
{
//首先实例化数据访问对象
private SysAdminService objSysAdminService = new SysAdminService();
public SysAdmin AdminLogin(SysAdmin ojbAdmin)
{
return objSysAdminService.AdminLogin(ojbAdmin);
}
}
}
UI层的FrmAdminLogin.cs
//去BLL层里找到需要的类
private SysAdminManager objAdminManager = new SysAdminManager();
public FrmAdminLogin()
{
InitializeComponent();
}
//点击登陆
private void btnLogin_Click(object sender, EventArgs e)
{
//数据验证
if (this.txtAdminId.Text.Trim().Length == 0)
{
MessageBox.Show("不合法用户ID");
this.txtAdminId.Focus();
return;
}
//封装用户输入
SysAdmin objAdmin = new SysAdmin()
{
AdminId = Convert.ToInt32(this.txtAdminId.Text.Trim()),
LoginPwd = this.txtLoginPwd.Text.Trim()
};
try
{
//调用BLL业务逻辑,完成用户对象验证
objAdmin = objAdminManager.AdminLogin(objAdmin);
//判断登陆是否成功
if (objAdmin != null)
{
if (objAdmin.StatusId == 1)
{
Program.objSysAdmin = objAdmin;
this.DialogResult = DialogResult.OK;
this.Close();
}
else
{
MessageBox.Show("账号异常");
}
}
else
{
MessageBox.Show("登陆失败");
objAdmin = null;
}
}
catch (Exception)
{
MessageBox.Show("程序发生异常");
this.Close();
}
}
2.3 实现图书对象的添加
image.png1.DAL中的
BookService.cs
注意:在编写DAL的时候,需要明确项目中需要什么类型的数据,如果是数据集,那就需要返回List,如果只是需要一个查询结果就int,这里需要将所有的书籍种类和出版社列出来,所以,两个都需要返回的是一个列表
namespace DAL
{
public class BookService
{
/// <summary>
/// 获取图书分类
/// </summary>
/// <returns></returns>
public List<Category> GetAllCategoryData()
{
string sql = "select CategoryId,CategoryName from Categories";
List<Category> list = new List<Category>();
SqlDataReader objReader = SQLHelper.GetReader(sql);
//循环读取并且封装对象
while (objReader.Read())
{
list.Add(
new Category()
{
CategoryId = Convert.ToInt32(objReader["CategoryId"]),
CategoryName = objReader["CategoryName"].ToString()
}
);
}
objReader.Close();
return list;
}
/// <summary>
/// 获取所有出版社
/// </summary>
/// <returns></returns>
public List<Publisher> GetAllPublisherData()
{
string sql = "select PublisherId,PublisherName from Publishers";
List<Publisher> list = new List<Publisher>();
SqlDataReader objReader = SQLHelper.GetReader(sql);
//循环读取并且封装对象
while (objReader.Read())
{
list.Add(
new Publisher()
{
PublisherId = Convert.ToInt32(objReader["PublisherId"]),
PublisherName = objReader["PublisherName"].ToString()
}
);
}
objReader.Close();
return list;
}
}
}
- BLL层的
BookManager.cs
namespace BLL
{
public class BookManager
{
private BookService objBookService = new BookService();
public List<Category> GetAllCategory()
{
return objBookService.GetAllCategoryData();
}
public List<Publisher> GetAllPublisher()
{
return objBookService.GetAllPublisherData();
}
}
}
注意:该层编写,主要是拿到DAL层的数据,之后进行一系列的处理,这个项目中,只是需要返回对象的NAME和ID所以,直接返回就可以
- UI层
FrmAddBook.cs
//实例化BLL层BookManager
private BookManager objBookManager = new BookManager();
public FrmAddBook()
{
InitializeComponent();
//获取所有图书分类
this.cboBookCategory.DataSource = objBookManager.GetAllCategory();
this.cboBookCategory.DisplayMember = "CategoryName";
this.cboBookCategory.ValueMember = "CategoryId";
//设置默认值
this.cboBookCategory.SelectedIndex = -1;//默认不选中
this.cboPublisher.DataSource = objBookManager.GetAllPublisher();
this.cboPublisher.DisplayMember = "PublisherName";
this.cboPublisher.ValueMember = "PublisherId";
//设置默认值
this.cboPublisher.SelectedIndex = -1;//默认不选中
}
注意:UI层与业务层的关键就在于实例化BLL层的管理器
2.4 带参数的存储过程添加图书
sql编写存储过程
USE [LibraryDB]
GO
if exists(select * from sysobjects where name = 'up_AddBooks')
drop prodecure up_AddBooks
go
create procedure up_AddBooks
@BarCode varchar(20),
@BookName varchar(100),
@Author varchar(50),
@PublisherId int ,
@PublishDate smalldatetime,
@BookCategory int,
@UnitPrice numeric(18,2),
@BookImage text,
@BookCount int,
@Remainder int,
@BookPosition varchar(20)
as
insert into Books(BarCode, BookName, Author, PublisherId, PublishDate, BookCategory, UnitPrice, BookImage, BookCount, Remainder, BookPosition) values
(@BarCode, @BookName, @Author, @PublisherId, @PublishDate, @BookCategory, @UnitPrice,@BookImage, @BookCount, @Remainder, @BookPosition)
go
DAL层BookService.cs
public int GetCountByCarCode(string barCode)
{
string sql = "select count(1) from Books where BarCode = @BarCode";
SqlParameter[] param = new SqlParameter[] { new SqlParameter("BarCode", barCode) };
return Convert.ToInt32(SQLHelper.GetSingleResult(sql, param));
}
/// <summary>
/// 带参数的存储过程,添加图书对象
/// </summary>
/// <param name="objBooks"></param>
/// <returns></returns>
public int AddBook(Books objBooks)
{
//封装参数
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@BarCode",objBooks.BarCode),
new SqlParameter("@BookName",objBooks.BookName),
new SqlParameter("@Author",objBooks.Author),
new SqlParameter("@PublisherId",objBooks.PublisherId),
new SqlParameter("@PublishDate",objBooks.PublishDate),
new SqlParameter("@BookCategory",objBooks.BookCategory),
new SqlParameter("@UnitPrice",objBooks.UnitPrice),
new SqlParameter("@BookImage",objBooks.BookImage),
new SqlParameter("@BookCount",objBooks.BookCount),
new SqlParameter("@Remainder",objBooks.Remainder),
new SqlParameter("@BookPosition",objBooks.BookPosition),
};
//调用通用数据访问类,提交对象
return SQLHelper.UpdateByProdecure("up_AddBooks", param);
}
2.5 通过条码查询图书信息
DAL层BookService.cs
/// <summary>
/// 根据条码返回一个图书对象
/// </summary>
/// <param name="BarCode"></param>
/// <returns></returns>
public Books GetBookByBarCode(string BarCode)
{
string sql = "select BookId,BarCode,BookName,Author,PublisherId,PublishDate,BookCategory,UnitPrice," +
"BookImage,BookCount,Remainder,BookPosition,RegTime,PublisherName,CategoryName from Books" +
"inner join Publisers on Publishers.PublisherId = Books.PublisherId" +
"inner join Categoryies on Books.BookCategory = Categories.CategoryId";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@BarCode",BarCode)
};
SqlDataReader objReader = SQLHelper.GetReader(sql, param);
Books objBook = null;
if (objReader !=null)
{
objBook = new Books()
{
Author = objReader["Author"].ToString(),
BarCode = objReader["BarCode"].ToString(),
CategoryName = objReader["CategoryName"].ToString(),
BookName = objReader["BookName"].ToString(),
PublisherName = objReader["PublisherName"].ToString(),
BookImage = objReader["BookImage"] is DBNull?"" : objReader["BookImage"].ToString()
};
}
return objBook;
}
-
BookImage = objReader["BookImage"] is DBNull?"" : objReader["BookImage"].ToString()
三元表达式 前面是真,后面是假,DBNull是数据库空
网友评论