数据库：
create database wuzi
on
(
name=wuzi_dat,
filename='D:peixunzuoyeVS201020150506sqlwuzi.mdf',
size=10,
maxsize=50,
filegrowth=10%
)
log on
(
name=wuzi_log,
filename='D:peixunzuoyeVS201020150506sqlwuzi.ldf',
size=10,
maxsize=50,
filegrowth=10%
)
create table kc
(
WzName varchar(20) primary key,
Num int not null,
Unit varchar(20) not null,
Price Decimal(7,2) not null
)
create table fafang
(
Id int primary key identity(0,1) not null,
WzName varchar(20) not null,
faNum int not null,
Unit varchar(20) not null,
faPrice Decimal(7,2) not null,
dadate datetime not null,
receiver varchar(100) not null
)
insert into kc values ('帐篷',500,'顶',2000);
insert into kc values ('应急灯',400,'台',600);
insert into kc values ('棉被',300,'套',300);
insert into kc values ('水桶',600,'个',5);
C#：
主函数：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace wuzifafang
{
class Program
{
// public const string AB = "server=.;database=wuzi;uid=sa;pwd=123";//不用
static void Main(string[] args)
{
// Show();
while (true)
{
Console.WriteLine("赈灾物资发放登记系统：");
Console.WriteLine("1.物资库存维护");
Console.WriteLine("2.物资库存列表");
Console.WriteLine("3.物资发放");
Console.WriteLine("4.物资发放统计");
Console.WriteLine("5.退出系统");
Console.Write("请输入您的选择：");
string shu = Console.ReadLine();
if (shu == "1")
{
//功能1
Weihutianjia a1 = new Weihutianjia();
a1.Tianjia();
}
else if (shu == "2")
{
//功能2
Wuziku b1 = new Wuziku();
b1.Wuzi();
}
else if (shu == "3")
{
//功能3
Wuzifafang c1 = new Wuzifafang();
c1.Fafang();
}
else if (shu == "4")
{
//功能4
Fafangbiao d1 = new Fafangbiao();
d1.Ffbiao();
}
else if (shu == "5")
{ break; }
else
{
Console.WriteLine("请重新输入您的选择：");
Chongxie();
}
}
Console.ReadLine();
}//刚开始的显示目录
public static void Chongxie()
{
for (int i = 0; i < 2; i++)
{
string shu = Console.ReadLine();
if (shu == "1")
{
//功能1
Weihutianjia a1 = new Weihutianjia();
a1.Tianjia();
}
else if (shu == "2")
{
//功能2
Wuziku b1 = new Wuziku();
b1.Wuzi();
}
else if (shu == "3")
{
//功能3
Wuzifafang c1 = new Wuzifafang();
c1.Fafang();
}
else if (shu == "4")
{
//功能4
Fafangbiao d1 = new Fafangbiao();
d1.Ffbiao();
}
else if (shu == "5")
{ break; }
else
{
if (i == 1)
{
Console.WriteLine("您已输入3次，系统将重新打开！");
break;
}
else
{
Console.Write("请重新输入您的选择：");
}
}
}
}//验证输入正确与否，可重写3次
//public static void Show()
//{
// SqlConnection con = new SqlConnection(AB);
// try
// {
// con.Open();
// SqlCommand cmd = con.CreateCommand();
// cmd.CommandText = "select * from kc";
// SqlDataReader dr = cmd.ExecuteReader();
// while (dr.Read())
// {
// string name = dr["WzName"].ToString();
// int num = (int)dr["Num"];
// string unit = dr["Unit"].ToString();
// decimal price = (decimal)dr["Price"];
// Console.WriteLine(name + " " + num + " " + unit + " " + price);
// }
// }
// catch
// { Console.WriteLine("文件错误！"); }
// finally
// {
// con.Close();
// }
//}
//显示所有物资状况//显示物资表的
}
}
添加物资：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace wuzifafang
{
class Weihutianjia
{
public const string AB = "server=.;database=wuzi;uid=sa;pwd=123";
public void Tianjia()
{
Console.WriteLine("1.物资库存维护（添加）：");
Console.Write("请输入物质名称：");
string Wzname = Console.ReadLine();
Console.Write("请输入计量单位：");
string Wzunit = Console.ReadLine();
Console.Write("请输入库存数量：");
int Wznum =Convert.ToInt32( Console.ReadLine());
Console.Write("请输入物质单价：");
decimal Wzprice =Convert.ToDecimal( Console.ReadLine());
Yan(Wzname,Wzunit,Wznum,Wzprice);
}//添加的主要函数，调用其它附庸函数，输入相应的添加信息
public static void Yan(string name,string unit,int num,decimal price)
{
string you = "";
you=Panduan(name);
if (you == "wu")
{
Charu(name,unit,num,price);
}
else
{
Gaidong(name, unit, num, price);
}
}
//验证填入信息的物资名称是否已存在--调用Panduan()，
//不存在(you=="wu")--调用Charu();存在--调用Gaidong(),改动该物资数量
public static string Panduan(string name)
{
string cun = "wu";
SqlConnection con = new SqlConnection(AB);
try
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "select * from kc";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr["WzName"].ToString() == name)
{ cun = "bu"; break; }
else { cun = "wu"; }
}
}
catch
{ Console.WriteLine("文件错误！"); }
finally
{
con.Close();
}
return cun;
}
//验证填入信息的物资名称是否已存在，存在返回"bu"，不存在返回"wu"
public static void Charu(string name, string unit, int num, decimal price)
{
SqlConnection con = new SqlConnection(AB);
try
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "insert into kc values ('" + name + "'," + num + ",'" + unit + "'," + price + ")";
cmd.ExecuteNonQuery();
Console.WriteLine("数据添加成功！");
}
catch
{
Console.WriteLine("数据添加失败！");
}
finally
{
con.Close();
}
}
//向物资表中插入相应信息
public static void Gaidong(string name, string unit, int num, decimal price)
{
int num1 = Getnum(name); ;
int num2 = num+num1;
SqlConnection con = new SqlConnection(AB);
try
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "update kc set Num='"+num2+"' where WzName='"+name+"' ";
cmd.ExecuteNonQuery();
Console.WriteLine("数据添加成功！");
}
catch
{
Console.WriteLine("数据添加失败！");
}
finally
{
con.Close();
}
}
//调用Getnum(),得到原来物资表中该物资的数量，再累加新添入的数量，改动原本的数量
public static int Getnum(string name)
{
int newnum = 0;
SqlConnection con = new SqlConnection(AB);
try
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "select * from kc where WzName='" + name + "'";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
newnum = (int)dr["Num"];
}
}
catch
{
Console.WriteLine("文件错误！");
}
finally
{
con.Close();
}
return newnum;
}//得到原来物资表中该物资的数量
}
}
物资库表：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace wuzifafang
{
class Wuziku
{
public const string AB = "server=.;database=wuzi;uid=sa;pwd=123";
public void Wuzi()
{
Console.WriteLine("2.物资库存列表如下");
Console.WriteLine("********************************************************************");
Console.WriteLine("物资名称"+" "+"库存数量"+" "+"单价（元）");
SqlConnection con = new SqlConnection(AB);
try
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "select * from kc";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string name = dr["WzName"].ToString();
int num = (int)dr["Num"];
string unit = dr["Unit"].ToString();
decimal price = (decimal)dr["Price"];
Console.WriteLine(name + " " + num + unit + " " + price);
}
}
catch
{
Console.WriteLine("文件错误！");
}
finally
{
con.Close();
}
Console.WriteLine("********************************************************************");
}//select语句查找物资表中所有数据，并显示出来
}
}
物资发放：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace wuzifafang
{
class Wuzifafang
{
public const string AB = "server=.;database=wuzi;uid=sa;pwd=123";
public static decimal jiage ;//物资表中物资的当前价格
public static string danwei;//物资表中物资的当前单位
public void Fafang()
{
string wuzi = "cun";
Console.WriteLine("3.物资发放：");
Console.Write("请输入发放物资：");
string name= Console.ReadLine();
wuzi=Yanname(name);
if (wuzi == "cun")
{
Xianshuliang(name);
Console.Write("请输入发放数量：");
int num1 = Convert.ToInt32(Console.ReadLine());
Console.Write("接受人：");
string ren = Console.ReadLine();
string xing = Kcjian(name, num1);
if (xing == "ke")
{
Zhixing(name, num1, danwei, jiage, ren);
}
else
{
Console.WriteLine("您输入的物资数量已超过库存数量！");
}
}
else
{
Console.WriteLine("您输入的物资不存在！");
}
}
//根据要求填写要发放的物资名称，调用Yanname()，若不存在，直接输出；若存在--调用Xianshuliang()函数
//再填写发放的数量和接受人，调用Kcjian（），如果可行调用Zhixing（），否则输出数量超出物资存在数量
public static void Xianshuliang(string na)
{
int shuliang=0;
SqlConnection con = new SqlConnection(AB);
try
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "select * from kc where WzName='" + na + "'";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
jiage = (decimal)dr["Price"];
shuliang = (int)dr["Num"];
danwei = dr["Unit"].ToString();
}
Console.WriteLine("当前单价：" + jiage + "元");
Console.WriteLine("当前库存：" + shuliang + danwei);
}
catch
{
Console.WriteLine("文件错误！");
}
finally
{
con.Close();
}
}
//根据名称，select语句查出该物资的当前价格和当前库存
public static string Kcjian(string na, int num1)
{
string xing = "ke";
int num = 0;
SqlConnection con = new SqlConnection(AB);
try
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "select * from kc where WzName='" + na + "'";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
num = (int)dr["Num"];
}
if (num >= num1)
{
int newnum = num - num1;
Gainum(na, newnum);
xing = "ke";
}
else
{ xing = "bu"; }
}
catch
{
Console.WriteLine("文件错误！");
}
finally
{
con.Close();
}
return xing;
}
//根据名称，查出该物资的库存数量，与输入的发放数量对比，超过发放数量就调用Gainum（），否则返回不行
public static string Yanname(string name)
{
string Ok = "cun";
SqlConnection con = new SqlConnection(AB);
try
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "select * from kc";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr["WzName"].ToString() == name)
{ Ok = "cun"; break; }
else
{ Ok = "no"; }
}
}
catch
{
Console.WriteLine("文件错误！");
}
finally
{
con.Close();
}
return Ok;
}//验证是否存在该物资
//public static string Jian(string na, int num1)
//{
// string xing = "ke";
// int num = 0;
// SqlConnection con = new SqlConnection(AB);
// con.Open();
// SqlCommand cmd = con.CreateCommand();
// cmd.CommandText = "select * from kc where WzName='" + na + "'";
// SqlDataReader dr = cmd.ExecuteReader();
// while (dr.Read())
// {
// num = (int)dr["Num"];
// }
// if (num >= num1)
// {
// int newnum = num - num1;
// Gainum(na, newnum);
// xing = "ke";
// }
// else
// { xing = "bu"; }
// con.Close();
// return xing;
//}//与Kcjian（）功能相同
public static void Gainum(string name,int nownum)
{
SqlConnection con = new SqlConnection(AB);
try
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "update kc set Num='" + nownum + "' where WzName='" + name + "' ";
cmd.ExecuteNonQuery();
Console.WriteLine("数据已从物资库中减除成功！");
}
catch
{
Console.WriteLine("数据未从物资库中减除！");
}
finally
{
con.Close();
}
}
//在物资表中改动（即减去）要发放的物资数量
public static void Zhixing(string name, int num, string unit, decimal price, string ren)
{
DateTime da =DateTime.Now;
SqlConnection con = new SqlConnection(AB);
try
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "insert into fafang values ('" + name + "'," + num + ",'" + unit + "'," + price + ",'" + da + "','" + ren + "')";
cmd.ExecuteNonQuery();
Console.WriteLine("数据填入发放表中成功！");
}
catch
{
Console.WriteLine("数据未填入发放表中！");
}
finally
{
con.Close();
}
}
//在发放物资表中，插入要发放的物资的相应信息
}
}
物资发放表：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace wuzifafang
{
class Fafangbiao
{
public const string AB = "server=.;database=wuzi;uid=sa;pwd=123";
public void Ffbiao()
{
Console.WriteLine("4.物资发放列表如下");
Console.WriteLine("********************************************************************");
Console.WriteLine("物资名称" + " " + "发放数量" + " " + "单价（元）" + " " + "接受人");
SqlConnection con = new SqlConnection(AB);
try
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "select * from fafang";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string name = dr["WzName"].ToString();
int num = (int)dr["faNum"];
string unit = dr["Unit"].ToString();
decimal price = (decimal)dr["faPrice"];
string ren = dr["receiver"].ToString();
Console.WriteLine(name + " " + num + unit + " " + price + " " + ren);
}
}
catch
{
Console.WriteLine("文件错误！");
}
finally
{
con.Close();
}
Console.WriteLine("********************************************************************");
}//select语句查找发放物资表中所有数据，并显示出来
}
}