数据库课程设计---百货进销存信息管理系统.doc

南通大学数据库课程设计报告 百货进销存信息管理系统班级： 计101班姓名： 刘春晖 学号： 1013023002 一、课题名称题目：企业进销存管理信息系统开发工具：Visual Studio 2010，SQL Server 2005数据库：SQL Server 2005操作系统：windows7二、设计目的和要求、任务2.1目的和要求通过本次课程设计，加深对数据库原理以及相关程序设计语言的理解和应用水平。通过设计实际的数据库系统应用课题，进一步熟悉数据库管理系统的操作技术，提高动手能力，提高分析问题和解决问题的能力。2.2任务 自选一种程序设计语言以及一种数据库管理系统开发一个小型的数据库应用系统。2.3主要内容2.3.1系统分析与数据库设计阶段（1）进行系统需求分析和系统设计。（2）设计数据模型并进行优化，确定数据库结构、功能结构和系统安全性、完整性要求。2.3.2应用程序设计阶段（1）完成数据库定义工作，实现系统数据的处理和录入。（2）实现应用程序的设计、编程、优化功能。2.3.3系统集成调试阶段 对系统的各个应用程序进行集成和调试，进一步优化系统性能，改善系统用户界面。三、需求分析3.1 用户需求说明3.1.1 数据需求商品信息：商品号，商品名，商品属性入库信息：仓库号，商品名，经办人，数量出库信息：仓库号，商品名，经办人，数量调价信息：商品号，调后价格3.1.2 事务需求查询商品信息添加商品删除商品修改商品信息商品入库商品出库添加职工信息添加仓库信息3.2 系统需求说明 1) 软件环境软件环境需支持windows操作系统，sql server数据库，Visual Studio 20102) 初始数据库大小大约有7部库存商品大约有4个员工至少有一名管理员其它初始信息可为零3) 记录查找的类型和平均数量查询库存商品信息 每天大约20次查询出库记录 每天大约5-10次查询销售记录 每天大约20-30次查询调价记录 每天大约3-10次4) 性能单个记录查询时间少于一秒多个记录查询时间少于三秒添加、删除、更新记录时间少于五秒四、总体功能设计五、数据库设计 数据库结构设计管理员登陆表 employee员工表 C 商品表 P 仓库表 S 进货，出库表 SPC 六、应用程序设计功能模块及详细代码1.登陆界面实现用户管理员的登陆（Admin_Login.aspx）Admin_Login.cs 关键代码protected void Button1_Click(object sender, System.EventArgs e)string dsn=ConfigurationSettings.AppSettingsdsn;SqlConnection con=new SqlConnection(dsn);con.Open();SqlCommand cmd=new SqlCommand(select * from employee where employeename=+this.txtUserName.Text.Trim()+,con);SqlDataReader sdr=cmd.ExecuteReader();if(!sdr.Read()con.Close();Response.Write(alert(对不起没有该管理员存在你的物理地址和将被记录);this.txtUserName.Text=;this.txtUserPwd.Text=;return;elseif(this.txtUserPwd.Text.Trim()!=sdrpwd.ToString()con.Close();Response.Write(alert(密码错误你的物理地址和将记录!);this.txtUserPwd.Text=;this.txtUserName.Text=;return;elsecon.Close();Sessionemployeename=this.txtUserName.Text.Trim();Response.Redirect(Admin_index.aspx);sdr.Close();con.Close();protected void Button2_Click(object sender, System.EventArgs e)this.txtUserPwd.Text=;this.txtUserName.Text=; 2.主界面成功登陆显示欢迎界面（Admin_Index.aspx）3.商品、员工、仓库的添加页面来用实现商品、员工、仓库的添加（Admin_PAdd.aspx Admin_CAdd.aspx Admin_SAdd.aspx）Admin_PAdd.CS关键代码protected void btnSubmit_Click(object sender, System.EventArgs e)string dsn=ConfigurationSettings.AppSettingsdsn;SqlConnection con=new SqlConnection(dsn);con.Open(); string T1 = this.Textbox1.Text; int i1 = Convert.ToInt16(T1); string T2 = this.Textbox2.Text; int i2 = Convert.ToInt16(T2); string str = insert into P(Pno,Pname,Im,Om,Number)values( + this.txtTitle.Text.Trim().Replace(, ) + , + this.txtSource.Text + , + i1+ , + i2 + ,0); SqlCommand cmd = new SqlCommand(str, con); cmd.ExecuteNonQuery(); cmd.Dispose(); con.Close(); Response.Redirect(Admin_PL.aspx);Admin_CAdd.CS 关键代码protected void btnSubmit_Click(object sender, System.EventArgs e)string dsn=ConfigurationSettings.AppSettingsdsn;SqlConnection con=new SqlConnection(dsn);con.Open(); string str = insert into C(Cno,Cname,Csex)values( + this.txtTitle.Text.Trim().Replace(, ) + , + this.txtSource.Text + , + this.Textbox1.Text + ); SqlCommand cmd = new SqlCommand(str, con); cmd.ExecuteNonQuery(); cmd.Dispose(); con.Close(); Response.Redirect(Admin_CL.aspx);Admin_SAdd.CS关键代码protected void btnSubmit_Click(object sender, System.EventArgs e)string dsn=ConfigurationSettings.AppSettingsdsn;SqlConnection con=new SqlConnection(dsn);con.Open(); string T1 = this.Textbox1.Text; int i1 = Convert.ToInt16(T1); string str = insert into S(Sno,Sname,Ssize)values( + this.txtTitle.Text.Trim().Replace(, ) + , + this.txtSource.Text + , + i1+ ); SqlCommand cmd = new SqlCommand(str, con); cmd.ExecuteNonQuery(); cmd.Dispose(); con.Close(); Response.Redirect(Admin_SL.aspx);4. 商品、员工、仓库的显示页面实现商品、员工、仓库的显示（Admin_PL.aspx Admin_CL.aspx Admin_SL.aspx）Admin_PL.CS 关键代码public partial class rscadm_Admin_ArticleAdd : System.Web.UI.Page string Pno = ; protected void Page_Load(object sender, EventArgs e) if (Sessionemployeename = null) Response.Redirect(admin_login.aspx); string dsn = ConfigurationSettings.AppSettingsdsn; SqlConnection con = new SqlConnection(dsn); con.Open(); string str = select Pno,Pname,Im,Om,Number from P; SqlDataAdapter sda = new SqlDataAdapter(str, con); DataSet ds = new DataSet(); sda.Fill(ds, PList); this.dgTopic.DataSource = ds.TablesPList.DefaultView; this.dgTopic.DataBind(); ds.Clear(); sda.Dispose(); con.Close(); Admin_CL.CS 关键代码public partial class rscadm_Admin_ArticleAdd : System.Web.UI.Page string Pno = ; protected void Page_Load(object sender, EventArgs e) if (Sessionemployeename = null) Response.Redirect(admin_login.aspx); string dsn = ConfigurationSettings.AppSettingsdsn; SqlConnection con = new SqlConnection(dsn); con.Open(); string str = select Cno,Cname,Csex from C; SqlDataAdapter sda = new SqlDataAdapter(str, con); DataSet ds = new DataSet(); sda.Fill(ds, PList); this.dgTopic.DataSource = ds.TablesPList.DefaultView; this.dgTopic.DataBind(); ds.Clear(); sda.Dispose(); con.Close(); Admin_SL.CS 关键代码public partial class rscadm_Admin_ArticleAdd : System.Web.UI.Page string Pno = ; protected void Page_Load(object sender, EventArgs e) if (Sessionemployeename = null) Response.Redirect(admin_login.aspx); string dsn = ConfigurationSettings.AppSettingsdsn; SqlConnection con = new SqlConnection(dsn); con.Open(); string str = select Sno,Sname from S; SqlDataAdapter sda = new SqlDataAdapter(str, con); DataSet ds = new DataSet(); sda.Fill(ds, PList); this.dgTopic.DataSource = ds.TablesPList.DefaultView; this.dgTopic.DataBind(); ds.Clear(); sda.Dispose(); con.Close(); 5.进货系统实现商品进货（Admin_SPCIAdd.aspx）Admin_SPCIAdd.CS关键代码protected void btnSubmit_Click(object sender, System.EventArgs e)string dsn=ConfigurationSettings.AppSettingsdsn;SqlConnection con=new SqlConnection(dsn);con.Open(); string T1 = this.Textbox2.Text; int i1 = Convert.ToInt16(T1); string str = update P set Number=Number + +i1+ where Pno=+this.txtTitle.Text.Trim().Replace(, )+ insert into SPC(Pno,Sno,Cno,Number,times)values( + this.txtTitle.Text.Trim().Replace(, ) + , + this.txtSource.Text + , + this.Textbox1.Text + , + i1 + , + this.DropDownCalendar1.Value + ); SqlCommand cmd = new SqlCommand(str, con); cmd.ExecuteNonQuery(); cmd.Dispose(); con.Close(); Response.Redirect(Admin_PL.aspx);6.出库系统实现商品出库（Admin_SPCOAdd.aspx）Admin_SPCOAdd.CS关键代码protected void btnSubmit_Click(object sender, System.EventArgs e)string dsn=ConfigurationSettings.AppSettingsdsn;SqlConnection con=new SqlConnection(dsn);con.Open(); string T1 = this.Textbox2.Text; int i1 = Convert.ToInt16(T1)*(-1); string str = update P set Number=Number + + i1 + where Pno= + this.txtTitle.Text.Trim().Replace(, ) + insert into SPC(Pno,Sno,Cno,Number,times)values( + this.txtTitle.Text.Trim().Replace(, ) + , + this.txtSource.Text + , + this.Textbox1.Text + , + i1 + , + this.DropDownCalendar1.Value + ); SqlCommand cmd = new SqlCommand(str, con); cmd.ExecuteNonQuery(); cmd.Dispose(); con.Close(); Response.Redirect(Admin_PL.aspx);7.商品信息管理对已添加的商品进行名称、进价、售价的修改（Admin_PX.aspx）Admin_PX.CS关键代码public partial class rscadm_Admin_ArticleAdd : System.Web.UI.Page protected void Page_Load(object sender, System.EventArgs e)if(Sessionemployeename=null)Response.Redirect(admin_login.aspx);if(!this.IsPostBack)BindData();private void BindData()string dsn=ConfigurationSettings.AppSettingsdsn;SqlConnection con=new SqlConnection(dsn);con.Open();SqlDataAdapter sda=new SqlDataAdapter(select Pno,Pname,Im,Om from P ,con);DataSet ds=new DataSet();sda.Fill(ds,PList);this.dgTopic.DataKeyField=Pno;this.dgTopic.DataSource=ds.TablesPList.DefaultView;this.dgTopic.DataBind(); ds.Clear(); sda.Dispose();con.Close();private void dgTopic_CancelCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)this.dgTopic.EditItemIndex=-1;this.BindData();private void dgTopic_DeleteCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)string dsn=ConfigurationSettings.AppSettingsdsn;SqlConnection con=new SqlConnection(dsn);con.Open();SqlCommand cmd=new SqlCommand(delete from P where Pno=+this.dgTopic.DataKeyse.Item.ItemIndex.ToString()+,con);cmd.ExecuteNonQuery(); cmd.Dispose();con.Close();BindData();private void dgTopic_EditCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)this.dgTopic.EditItemIndex=e.Item.ItemIndex;this.BindData();private void dgTopic_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)this.dgTopic.CurrentPageIndex=e.NewPageIndex;this.BindData();private void dgTopic_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)string Pno=this.dgTopic.DataKeyse.Item.ItemIndex.ToString();string Pname=(TextBox)(e.Item.Cells1.Controls0).Text;string T1=(TextBox)(e.Item.Cells2.Controls0).Text; int Im = Convert.ToInt16(T1);string T2=(TextBox)(e.Item.Cells3.Controls0).Text; int Om = Convert.ToInt16(T2);string dsn=ConfigurationSettings.AppSettingsdsn;SqlConnection con=new SqlConnection(dsn);con.Open();SqlCommand cmd=new SqlCommand(update P set Pname=+Pname+,Im=+Im+,Om=+Om+ where Pno=+Pno+,con);cmd.ExecuteNonQuery(); cmd.Dispose(); con.Close();this.dgTopic.EditItemIndex=-1