通过一个完整的实例实现课程信息管理功能的操作，包括查询、修改、删除课程信息等操作。
1) 创建课程信息表
create table StuCourse
(
id int primary key identity(1,1),
name varchar(20),
credit numeric(3,1),
remark varchar(50)
);
INSERT INTO StuCourse (name,credit,remark) VALUES ('English',3.00,'Good Good Study');
INSERT INTO StuCourse (name,credit,remark) VALUES ('Math',2.56,'Good Good Study, Day');
INSERT INTO StuCourse (name,credit,remark) VALUES ('Chinese',4.04,'Good Good Study, Day Day');
INSERT INTO StuCourse (name,credit,remark) VALUES ('English1',3.00,'Good Good Study');
INSERT INTO StuCourse (name,credit,remark) VALUES ('Math1',2.56,'Good Good Study, Day');
INSERT INTO StuCourse (name,credit,remark) VALUES ('Chinese1',4.04,'Good Good Study, Day Day');
2) 课程信息管理界面的设计
DataGridView 控件用于显示课程信息，并提供了根据课程名称查找课程信息、修改以及删除的功能，另外提供增加课程功能（暂无）。
3) 具体代码
实体类：
/// <summary>
/// 课程实体类（实际应创建一实体类的项目，里面可能有多个实体类）
/// </summary>
public class Entities
{
/// <summary>
/// 编号（数据库中的自增主键）
/// </summary>
public int ID { get; set; }
/// <summary>
/// 课程名称
/// </summary>
public string Name { get; set; }
/// <summary>
/// 课程学分
/// </summary>
public float Credit { get; set; }
/// <summary>
/// 备注
/// </summary>
public string Remark { get; set; }
}
业务层数据层：
/// <summary>
/// 课程相关处理类（实际应将业务逻辑层BLL与数据库层DAL分开,各自创建一个项目）
/// </summary>
public class CourseInfo
{
static string conString = "Data Source=.;Initial Catalog=TestDB;Integrated Security=True;";
/// <summary>
/// 返回所有课程信息
/// </summary>
/// <returns> List<Entities></returns>
public static List<Entities> GetAllCourseInfo()
{
SqlDataReader reader = null;
try
{
List<Entities> entities = new List<Entities>();
string sqlStr = "SELECT * FROM StuCourse";
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
SqlCommand cmd = new SqlCommand(sqlStr, con);
reader = cmd.ExecuteReader();
while (reader.Read())
{
int id = reader["id"] == DBNull.Value ? -1 : Convert.ToInt32(reader["id"]);
string name = reader["name"] == DBNull.Value ? "" : reader["name"].ToString();
float credit = reader["credit"] == DBNull.Value ? 0f : Convert.ToSingle(reader["credit"]);
string remark = reader["remark"] == DBNull.Value ? "" : reader["remark"].ToString();
Entities entity = new Entities()
{
ID = id,
Name = name,
Credit = credit,
Remark = remark
};
entities.Add(entity);
}
}
return entities;
}
catch (Exception)
{
//打印log
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
/// <summary>
/// 返回查找的课程信息,模糊查询
/// </summary>
/// <param name="courseName"></param>
/// <returns>List<Entities></returns>
public static List<Entities> GetTheCourseInfo(string courseName)
{
SqlDataReader reader = null;
try
{
string sqlStr = "";
List <Entities> entities = new List<Entities>();
if (courseName == "All Course")
sqlStr = $"SELECT * FROM StuCourse";
else
sqlStr = $"SELECT * FROM StuCourse WHERE name like '%{courseName}%'";
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
SqlCommand cmd = new SqlCommand(sqlStr, con);
reader = cmd.ExecuteReader();
while (reader.Read())
{
int id = reader["id"] == DBNull.Value ? -1 : Convert.ToInt32(reader["id"]);
string name = reader["name"] == DBNull.Value ? "" : reader["name"].ToString();
float credit = reader["credit"] == DBNull.Value ? 0f : Convert.ToSingle(reader["credit"]);
string remark = reader["remark"] == DBNull.Value ? "" : reader["remark"].ToString();
Entities entity = new Entities()
{
ID = id,
Name = name,
Credit = credit,
Remark = remark
};
entities.Add(entity);
}
}
return entities;
}
catch (Exception)
{
//打印log
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
/// <summary>
/// 删除
/// </summary>
/// <param name="courseID"></param>
/// <returns>int</returns>
public static int DeleteTheCourseInfo(int courseID)
{
try
{
int res = -1;
List<Entities> entities = new List<Entities>();
string sqlStr = $"DELETE FROM StuCourse WHERE id = {courseID}";
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
SqlCommand cmd = new SqlCommand(sqlStr, con);
res = cmd.ExecuteNonQuery();
}
return res;
}
catch (Exception)
{
//打印log
throw;
}
}
/// <summary>
/// 更新
/// </summary>
/// <param name="entity"></param>
/// <returns>int</returns>
public static int UpdateTheCourseInfo(Entities entity)
{
try
{
int res = -1;
string sqlStr = $@"UPDATE StuCourse SET name = N'{entity.Name}',
credit = {entity.Credit}, remark = N'{entity.Remark}' WHERE id = {entity.ID}";
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
SqlCommand cmd = new SqlCommand(sqlStr, con);
res = cmd.ExecuteNonQuery();
}
return res;
}
catch (Exception)
{
//打印log
throw;
}
}
/// <summary>
/// 增加
/// </summary>
/// <param name="entity"></param>
/// <returns>int</returns>
public static int AddTheCourseInfo(Entities entity)
{
try
{
int res = -1;
string sqlStr = $@"INSERT INTO StuCourse (name,credit,remark) VALUES
('{entity.Name}', {entity.Credit}, '{entity.Remark}')";
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
SqlCommand cmd = new SqlCommand(sqlStr, con);
res = cmd.ExecuteNonQuery();
}
return res;
}
catch (Exception)
{
//打印log
throw;
}
}
}
界面代码：
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
Init();
}
private void Init()
{
List<Entities> allCourseInfo = CourseInfo.GetAllCourseInfo();
dgvCourseInfo.DataSource = allCourseInfo;
cbCourseName.Items.Add("All Course");
for (int i = 0; i < allCourseInfo.Count; i++)
{
cbCourseName.Items.Add(allCourseInfo[i].Name);
}
if (cbCourseName.Items.Count > 0)
{
cbCourseName.SelectedIndex = 0;
}
//dgvCourseInfo.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
//dgvCourseInfo.Columns[0].HeaderText = "id";
//dgvCourseInfo.Columns[1].HeaderText = "名称";
}
//private void cbCourseName_SelectedIndexChanged(object sender, EventArgs e)
//{
// if (!string.IsNullOrWhiteSpace(cbCourseName.SelectedItem.ToString()))
// {
// btnSearch_Click(sender, e);
// }
//}
//查询
private void btnSearch_Click(object sender, EventArgs e)
{
List<Entities> theCourseInfo = CourseInfo.GetTheCourseInfo(cbCourseName.SelectedItem.ToString().Trim());
if (theCourseInfo.Count > 0)
{
dgvCourseInfo.DataSource = null;
dgvCourseInfo.DataSource = theCourseInfo;
MessageBox.Show(theCourseInfo.Count.ToString() + "条数据", "Tips", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
dgvCourseInfo.DataSource = null;
MessageBox.Show("No Data","Tips", MessageBoxButtons.OK,MessageBoxIcon.Information);
}
}
//删除
private void btnDelete_Click(object sender, EventArgs e)
{
if (dgvCourseInfo.DataSource == null)
{
MessageBox.Show("Search first", "Tips", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
dgvCourseInfo.MultiSelect = false;
DataGridViewSelectedRowCollection src = dgvCourseInfo.SelectedRows;
if (src.Count == 0)
{
MessageBox.Show("Choose first", "Tips", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
int id = Convert.ToInt32(src[0].Cells[0].Value);
int res = CourseInfo.DeleteTheCourseInfo(id);
if (res > 0)
{
List<Entities> allCourseInfo = CourseInfo.GetAllCourseInfo();
dgvCourseInfo.DataSource = allCourseInfo;
}
else
{
MessageBox.Show("Delete failed", "Tips", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
//更新
private void btnUpdate_Click(object sender, EventArgs e)
{
if (dgvCourseInfo.DataSource == null)
{
MessageBox.Show("Search first", "Tips", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
dgvCourseInfo.MultiSelect = false;
DataGridViewSelectedRowCollection src = dgvCourseInfo.SelectedRows;
if (src.Count == 0)
{
MessageBox.Show("Choose first", "Tips", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
int id = Convert.ToInt32(src[0].Cells[0].Value);
string name = src[0].Cells[1].Value.ToString();
float credit = Convert.ToSingle(src[0].Cells[2].Value);
string remark = src[0].Cells[3].Value.ToString();
int res = CourseInfo.UpdateTheCourseInfo(new Entities() {
ID=id,
Name=name,
Credit=credit,
Remark=remark
});
if (res > 0)
{
List<Entities> allCourseInfo = CourseInfo.GetAllCourseInfo();
dgvCourseInfo.DataSource = allCourseInfo;
}
else
{
MessageBox.Show("Update failed", "Tips", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
}
不合理的地方有待改进。