《数据库原理及应用》课程设计报告
商品销售管理系统
学 院： 信息工程学院
班 级：
学 号：
姓 名：
完成时间： 201
课程设计的任务
数据库原理及应用是计算机及其相关学科的一门重要的学科基础课程，也是计算机软件科学与技术、信息科学与技术的重要学科分支。本课程设计旨在通过对一个小型数据库管理系统（DBMS）的综合设计过程，强化学生对计算机系统软件的设计能力，提高学生的综合素质，并通过课程设计进一步加强学生对所学知识的理解，以及对数据库的全面、深刻认识。具体要求如下：
1) 了解DBMS的设计过程；
2) 掌握关系型DBMS的结构及实现；
3) 掌握系统程序设计的基础知识；
4) 深化理解并掌握《数据库原理及应用》课程的相关内容；
5) 强化软件开发的团队意识，提高合作能力。
系统需求分析与设计
数据流图
E－R图
数据结构描述
商品数据结构：
公司数据结构:
销售数据结构：
软件设计流程图和功能模块图
程序模块汇总
1 商品信息录入；
2 商品信息查询；
3 公司信息录入；
4 公司信息查询；
5 销售信息录入；
6 销售信息查询；
源程序清单
主目录：
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
Response.Redirect("~/Default2.aspx");
}
protected void Button3_Click(object sender, EventArgs e)
{
Response.Redirect("~/Default3.aspx");
}
protected void Button2_Click(object sender, EventArgs e)
{
Response.Redirect("~/Default4.aspx");
}
protected void Button4_Click(object sender, EventArgs e)
{
Response.Redirect("~/Default6.aspx");
}
protected void Button5_Click(object sender, EventArgs e)
{
Response.Redirect("~/Default4.aspx");
}
protected void Button6_Click(object sender, EventArgs e)
{
Response.Redirect("~/Default5.aspx");
}
protected void Button7_Click(object sender, EventArgs e)
{
Response.Redirect("~/Default7.aspx");
}
}
○1 商品信息录入；
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button3_Click(object sender, EventArgs e)
{
if ((ShangID.Text.Trim()).Length < 1)
{
Response.Write("<script>window.alert('没有要删除的项！')</script>");
return;
}
SqlConnection con = new SqlConnection("server=localhost;user id=sa;pwd=123456;database=studb");
con.Open();
string select = "select count(*) as total from S where ShanID="+"'"+ ShangID.Text.Trim()+ "'";
SqlCommand cmdsel = new SqlCommand(select, con);
SqlDataReader dr = cmdsel.ExecuteReader();
if (dr.Read())
{
if (int.Parse(dr["total"].ToString()) == 0)
{
Response.Write("<script>window.alert('要删除的记录不存在！')</script>");
return;
}
}
dr.Close();
string str = "delete from S where ShanID=" + "'" + ShangID.Text.Trim() + "'";
SqlCommand cmd = new SqlCommand(str, con);
cmd.ExecuteNonQuery();
con.Close();
}
protected void Exit_Click(object sender, EventArgs e)
{
Response.Redirect ("~/Default.aspx");
}
protected void Insert_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("server=localhost;uid=sa;pwd=123456;database=studb");
con.Open();
string insert = "insert into S(ShanID,ShanName,ShanCount,ShanPrice,FactoryID) values("
+ "'" + ShangID.Text.Trim() + "'" + "," + "'" + ShangName.Text.Trim() +
"'" + "," + ShangCount.Text.Trim() + "," + "'" + ShangPrice.Text.Trim() +
"'" + "," + "'" + ShangAddr.Text.Trim() + "'" + ")";
Response.Write(insert);
SqlCommand cmd1 = new SqlCommand(insert, con);
cmd1.ExecuteNonQuery();
con.Close();
}
}
○2 商品信息查询；
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class Default3 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void MoveToFirst_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("server=localhost;user id=sa;pwd=123456;database=studb");
con.Open();
string str = "select ShanID from S order by ShanID asc";
SqlCommand cmd = new SqlCommand(str, con);
SqlDataReader sr = cmd.ExecuteReader();
if (sr.Read())
{
string Student = sr["ShanID"].ToString();
Refresh(Student);
}
sr.Close();
}
private void Refresh(string shangId)
{
SqlConnection con = new SqlConnection("server=localhost;user id=sa;pwd=123456;database=studb");
con.Open();
string str = "select * from S where ShanID=" + "'" + shangId.ToString() + "'";
SqlCommand cmd = new SqlCommand(str, con);
SqlDataReader sr = cmd.ExecuteReader();
if (sr.Read())
{
ShangID.Text = shangId.ToString();
ShangName.Text = sr["ShanName"].ToString();
ShangCount.Text = sr["ShanCount"].ToString();
ShangPrice.Text = sr["ShanPrice"].ToString();
ShangAddr.Text = sr["FactoryID"].ToString();
}
}
protected void MoveToPre_Click(object sender, EventArgs e)
{
if ((ShangID.Text.Trim()).Length < 1)
{
Response.Write("<script>window.alert('请选择一个当前项！')</script>");
return;
}
string shangid = "";
SqlConnection con = new SqlConnection("server=localhost;user id=sa;pwd=123456;database=studb");
con.Open();
string str = "select ShanID from S order by ShanID asc";
SqlCommand cmd = new SqlCommand(str, con);
SqlDataReader sr = cmd.ExecuteReader();
if (sr.Read())
{
shangid = sr["ShanID"].ToString();
}
sr.Close();
if (shangid == ShangID.Text.Trim())
{
Response.Write("<script>window.alert('当前数据项已经是第一个了！')</script>");
return;
}
else
{
string tempstr = "select max(ShanID) as maxid from S where ShanID<" + "'" + ShangID.Text.Trim() + "'";
cmd.CommandText = tempstr;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
string shId = dr["maxid"].ToString();
Refresh(shId);
}
}
con.Close();
}
protected void MoveToNext_Click(object sender, EventArgs e)
{
if ((ShangID.Text.Trim()).Length < 1)
{
Response.Write("<script>window.alert('请选择一个当前项！')</script>");
return;
}
string shangid = "";
SqlConnection con = new SqlConnection("server=localhost;user id=sa;pwd=123456;database=studb");
con.Open();
string str = "select ShanID from S order by ShanID desc";
SqlCommand cmd = new SqlCommand(str, con);
SqlDataReader sr = cmd.ExecuteReader();
if (sr.Read())
{
shangid = sr["ShanID"].ToString();
}
sr.Close();
if (shangid == ShangID.Text.Trim())
{
Response.Write("<script>window.alert('当前数据项已经是最后一个了！')</script>");
return;
}
else
{
string tempstr = "select min(ShanID) as maxid from S where ShanID>" + "'" + ShangID.Text.Trim() + "'";
cmd.CommandText = tempstr;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
string shId = dr["maxid"].ToString();
Refresh(shId);
}
}
con.Close();
}
protected void MoveToLast_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("server=localhost;user id=sa;pwd=123456;database=studb");
con.Open();
string str = "select ShanID from S order by ShanID desc";
SqlCommand cmd = new SqlCommand(str, con);
SqlDataReader sr = cmd.ExecuteReader();
if (sr.Read())
{
string Shang = sr["ShanID"].ToString();
Refresh(Shang);
}
sr.Close();
con.Close();
}
protected void KeySelect_Click(object sender, EventArgs e)
{
bool find = false;
SqlConnection con = new SqlConnection("server=localhost;user id=sa;pwd=123456;database=studb");
con.Open();
string cmdstr = "select * from S";
SqlDataAdapter da = new SqlDataAdapter(cmdstr, con);
DataSet ds = new DataSet();
da.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
string data = (ds.Tables[0].Rows[i][j].ToString()).Trim();
if (data == Select.Text.Trim())
{
ShangID.Text = ds.Tables[0].Rows[i]["ShanID"].ToString();
ShangName.Text = ds.Tables[0].Rows[i]["ShanName"].ToString();
ShangCount.Text = ds.Tables[0].Rows[i]["ShanCount"].ToString();
ShangPrice.Text = ds.Tables[0].Rows[i]["ShanPrice"].ToString();
ShangAddr.Text = ds.Tables[0].Rows[i]["FactoryID"].ToString();
find = true;
}
}
}
if (find == false)
{
Response.Write("<script>window.alert('没有相关记录！')</script>");
}
con.Close();
}
protected void Exit_Click(object sender, EventArgs e)
{
Response.Redirect("~/Default.aspx");
}
}
○3 公司信息录入；
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class Default4 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Insert_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("server=localhost;uid=sa;pwd=123456;database=studb");
con.Open();
string insert = "insert into Factory(FactorID,FactorName,FactorAddr,FactorPhone) values("
+ "'" + FactoryID.Text.Trim() + "'" + "," + "'" + FactoryName.Text.Trim() +
"'" + "," + "'" + FactoryAddr.Text.Trim() + "'" + "," + "'" + FactoryPhone.Text.Trim() +
"'" + ")";
Response.Write(insert);
SqlCommand cmd1 = new SqlCommand(insert, con);
cmd1.ExecuteNonQuery();
con.Close();
}
protected void Delete_Click(object sender, EventArgs e)
{
if ((FactoryID.Text.Trim()).Length < 1)
{
Response.Write("<script>window.alert('没有要删除的项！')</script>");
return;
}
SqlConnection con = new SqlConnection("server=localhost;user id=sa;pwd=123456;database=studb");
con.Open();
string select = "select count(*) as total from Factory where FactorID=" + "'" + FactoryID.Text.Trim() + "'";
SqlCommand cmdsel = new SqlCommand(select, con);
SqlDataReader dr = cmdsel.ExecuteReader();
if (dr.Read())
{
if (int.Parse(dr["total"].ToString()) == 0)
{
Response.Write("<script>window.alert('要删除的记录不存在！')</script>");
return;
}
}
dr.Close();
string str = "delete from Factory where FactorID=" + "'" + FactoryID.Text.Trim() + "'";
SqlCommand cmd = new SqlCommand(str, con);
cmd.ExecuteNonQuery();
con.Close();
}
protected void Exit_Click(object sender, EventArgs e)
{
Response.Redirect("~/Default.aspx");
}
}
○4 公司信息查询；
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class Default5 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void MoveToFirst_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("server=localhost;user id=sa;pwd=123456;database=studb");
con.Open();
string str = "select FactorID from Factory order by FactorID asc";
SqlCommand cmd = new SqlCommand(str, con);
SqlDataReader sr = cmd.ExecuteReader();
if (sr.Read())
{
string Student = sr["FactorID"].ToString();
Refresh(Student);
}
sr.Close();
}
private void Refresh(string factorId)
{
SqlConnection con = new SqlConnection("server=localhost;user id=sa;pwd=123456;database=studb");
con.Open();
string str = "select * from Factory where FactorID=" + "'" + factorId.ToString() + "'";
SqlCommand cmd = new SqlCommand(str, con);
SqlDataReader sr = cmd.ExecuteReader();
if (sr.Read())
{
FactoryID.Text = factorId.ToString();
FactoryName.Text = sr["FactorName"].ToString();
FactoryAddr.Text = sr["FactorAddr"].ToString();
FactoryPhone.Text = sr["FactorPhone"].ToString();
}
}
protected void MoveToPre_Click(object sender, EventArgs e)
{
if ((FactoryID.Text.Trim()).Length < 1)
{
Response.Write("<script>window.alert('请选择一个当前项！')</script>");
return;
}
string factoryid = "";
SqlConnection con = new SqlConnection("server=localhost;user id=sa;pwd=123456;database=studb");
con.Open();
string str = "select FactorID from Factory order by FactorID asc";
SqlCommand cmd = new SqlCommand(str, con);
SqlDataReader sr = cmd.ExecuteReader();
if (sr.Read())
{
factoryid = sr["FactorID"].ToString();
}
sr.Close();
if (factoryid == FactoryID.Text.Trim())
{
Response.Write("<script>window.alert('当前数据项已经是第一个了！')</script>");
return;
}
else
{
string tempstr = "select max(FactorID) as maxid from Factory where FactorID<" + "'" + FactoryID.Text.Trim() + "'";
cmd.CommandText = tempstr;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
string faId = dr["maxid"].ToString();
Refresh(faId);
}
}
con.Close();
}
protected void MoveToNext_Click(object sender, EventArgs e)
{
if ((FactoryID.Text.Trim()).Length < 1)
{
Response.Write("<script>window.alert('请选择一个当前项！')</script>");
return;
}
string factoryid = "";
SqlConnection con = new SqlConnection("server=localhost;user id=sa;pwd=123456;database=studb");
con.Open();
string str = "select FactorID from Factory order by FactorID desc";
SqlCommand cmd = new SqlCommand(str, con);
SqlDataReader sr = cmd.ExecuteReader();
if (sr.Read())
{
factoryid = sr["FactorID"].ToString();
}
sr.Close();
if (factoryid == FactoryID.Text.Trim())
{
Response.Write("<script>window.alert('当前数据项已经是最后一个了！')</script>");
return;
}
else
{
string tempstr = "select min(FactorID) as maxid from Factory where FactorID>" + "'" + FactoryID.Text.Trim() + "'";
cmd.CommandText = tempstr;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
string faId = dr["maxid"].ToString();
Refresh(faId);
}
}
con.Close();
}
protected void MoveToLast_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("server=localhost;user id=sa;pwd=123456;database=studb");
con.Open();
string str = "select FactorID from Factory order by FactorID desc";
SqlCommand cmd = new SqlCommand(str, con);
SqlDataReader sr = cmd.ExecuteReader();
if (sr.Read())
{
string Factory = sr["FactorID"].ToString();
Refresh(Factory);
}
sr.Close();
con.Close();
}
protected void Selecte_Click(object sender, EventArgs e)
{
bool find = false;
SqlConnection con = new SqlConnection("server=localhost;user id=sa;pwd=123456;database=studb");
con.Open();
string cmdstr = "select * from Factory";
SqlDataAdapter da = new SqlDataAdapter(cmdstr, con);
DataSet ds = new DataSet();
da.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
string data = (ds.Tables[0].Rows[i][j].ToString()).Trim();
if (data ==select.Text.Trim())
{
FactoryID.Text = ds.Tables[0].Rows[i]["FactorID"].ToString();
FactoryName.Text = ds.Tables[0].Rows[i]["FactorName"].ToString();
FactoryAddr.Text = ds.Tables[0].Rows[i]["FactorAddr"].ToString();
FactoryPhone.Text = ds.Tables[0].Rows[i]["FactorPhone"].ToString();
find = true;
}
}
}
if (find == false)
{
Response.Write("<script>window.alert('没有相关记录！')</script>");
}
con.Close();
}
protected void Exit_Click(object sender, EventArgs e)
{
Response.Redirect("~/Default.aspx");
}
}
○5 销售信息录入；
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class Default7 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Insert_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("server=localhost;uid=sa;pwd=123456;database=studb");
con.Open();
string insert = "insert into Salse (ShanID,FactorID,SalseCount) values("
+ "'" + ShangI.Text.Trim() + "'" + "," + "'" + FactoryI.Text.Trim() +
"'" + "," + "'" + COUNT.Text.Trim() + "'" + ")";
Response.Write(insert);
SqlCommand cmd1 = new SqlCommand(insert, con);
cmd1.ExecuteNonQuery();
con.Close();
}
protected void Delect_Click(object sender, EventArgs e)
{
if ((ShangI.Text.Trim()).Length < 1)
{
Response.Write("<script>window.alert('没有要删除的项！')</script>");
return;
}
SqlConnection con = new SqlConnection("server=localhost;user id=sa;pwd=123456;database=studb");
con.Open();
string select = "select count(*) as total from Salse where ShanID=" + "'" + ShangI.Text.Trim() + "'";
SqlCommand cmdsel = new SqlCommand(select, con);
SqlDataReader dr = cmdsel.ExecuteReader();
if (dr.Read())
{
if (int.Parse(dr["total"].ToString()) == 0)
{
Response.Write("<script>window.alert('要删除的记录不存在！')</script>");
return;
}
}
dr.Close();
string str = "delete from Salse where ShanID=" + "'" + ShangI.Text.Trim() + "'";
SqlCommand cmd = new SqlCommand(str, con);
cmd.ExecuteNonQuery();
con.Close();
}
protected void Exit_Click(object sender, EventArgs e)
{
Response.Redirect ("~/Default.aspx");
}
}
○6 销售信息查询；
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class Default6 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Selecte_Click(object sender, EventArgs e)
{
bool find = false;
SqlConnection con = new SqlConnection("server=localhost;user id=sa;pwd=123456;database=studb");
con.Open();
string cmdstr = "select * from Salse";
SqlDataAdapter da = new SqlDataAdapter(cmdstr, con);
DataSet ds = new DataSet();
da.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
string data = (ds.Tables[0].Rows[i][j].ToString()).Trim();
if (data == select.Text.Trim())
{
ShangI.Text = ds.Tables[0].Rows[i]["ShanID"].ToString();
FactoryI.Text = ds.Tables[0].Rows[i]["FactorID"].ToString();
COUNT.Text = ds.Tables[0].Rows[i]["SalseCount"].ToString();
find = true;
}
}
}
if (find == false)
{
Response.Write("<script>window.alert('没有相关记录！')</script>");
}
con.Close();
}
protected void Exit_Click(object sender, EventArgs e)
{
Response.Redirect("~/Default.aspx");
}
}
本软件所能实现的功能综述及分析
① 数据插入 实现数据插入功能。
② 数据删除 实现数据删除功能
③ 数据查询 实现按条件进行数据查询
④ 数据浏览 实现数据向前、向后、第一条、最后一条的方式浏览显示。
课程设计体会和总结
通过这次课程设计发现这其中需要的很多知识我们没有接触过，上网查找资料的时候发现我们以前所学到的仅仅是皮毛，还有很多需要我们掌握的东西我们根本不知道。同时也发现有很多已经学过的东西我们没有理解到位，不能灵活运用于实际，不能很好的用来解决问题，这就需要自己不断的大量的实践，通过不断的自学，不断地发现问题，思考问题，进而解决问题。在这个过程中我们将深刻理解所学知识，同时也可以学到不少很实用的东西。通过本次课程设计，对SQL语言，数据库的创建、修改、删除方法有了一定的了解，通过导入表和删除表、更改表，学会了数据库的基本操作。通过这次课程设计的完成，使我对自己所学知识进一步的了解，同时通过实践发现自己的不足，对知识的掌握及运用存在诸多的不完善。本系统也存在着很多不足，如系统十分简单，实现功能也不够完善，安全性问题也没有很好的解决。所以在今后的学习中我会继续努力，完善自我，同学的帮助使这个课题能够顺利进行。