资产信息管理MVC版

一.SQL语句

create database AssetsDB
go
use AssetsDB
go
create table userinfo
(
uid int identity(1,1) primary key,
uname varchar(50) unique,
upwd varchar(50) not null,
realname varchar(50) not null
)
go
insert into userinfo values('admin','123456','**'),('admin1','admin','**'),('admin2','125145ds4560','**')
go
create table assetsType
(
tid int identity(1,1) primary key,
tname varchar(50) unique,
)
go
insert into assetsType values('书籍类'),('电器类'),('水果类')
go
create table assetsInfo
(
aid  int identity(1,1) primary key,
tid int references assetsType(tid),
aname varchar(100) not null,
acount int not null,
aunit char(2) not null,
amoney decimal(10,2) not null,
apurchaseTime date  not null,
aregTime datetime default getdate(),
astate bit,
auser varchar(60) not null,
)
go
insert into assetsInfo values(1,'java',100,'本',133,'2022-12-1',default,1,'**'),(2,'电视',1020,'本',1000,'2021-12-1',default,1,'**'),(3,'苹果',120,'本',50,'2021-12-1',default,0,'**')
go

二.MVC查询

1.查询页面

  <div>
        <table align="center" border="1" cellspacing="0" width="1200">
            <tr>
                <td align="center" colspan="13"><h1>资产信息查询</h1></td>
            </tr>
            <tr>
                <td align="center" colspan="13">
                    <form action="~/Home/Index" method="post">
                        资产名称:<input type="text" name="Aname" value="@ViewBag.Aname" />
                        资产类型:<select name="tid">
                            <option value="0">--请选择--</option>
                            @foreach (var item in ViewBag.AssetsDBinfo)
                            {
                                <option value="@item.tid"@(item.tid==ViewBag.tid?"selected":"")>@item.tname</option>
                            }
                        </select>
                        <input type="submit" value="查询" />
                    </form>
                </td>
            </tr>
            <tr>
                <td colspan="13" align="center"><a href="AddAssetsDBFrom">添加资产</a></td>
            </tr>
            <tr>
                <th>资产编号</th>
                <th>资产类型</th>
                <th>资产名称</th>
                <th>数量</th>
                <th>单位</th>
                <th>单价</th>
                <th>总计</th>
                <th>采购日期</th>
                <th>登记日期</th>
                <th>使用状态</th>
                <th>使用者</th>
                <th colspan="2">操作</th>
            </tr>
            @foreach (var item in ViewBag.AssetsDB)
            {
                <tr>
                    <th>@item.Aid</th>
                    <th>@item.Tname</th>
                    <th>@item.Aname</th>
                    <th>@item.Acount</th>
                    <th>@item.Aunit</th>
                    <th>@item.Amoney</th>
                    <th>@(item.Acount * item.Amoney)</th>
                    <th>@item.ApurchaseTime</th>
                    <th>@item.AregTime</th>
                    <th>@(item.Astate == true ? "正常使用" : "报废")</th>
                    <th>@item.Auser</th>
                    <th><a href="~/Home/DeleteAssetsDB?aid=@item.Aid" onclick="return confirm('确定删除吗？')">删除</a></th>
                    <th><a href="~/Home/ExitAssetsDBFrom?aid=@item.Aid">编辑</a></th>
                </tr>
            }
        </table>
    </div>

2.在Models文件夹里面创建AssetsDBViewFrom类和连接数据库模型

AssetsDBViewFrom类代码如下：

//资产编号
        public int Aid { get; set; }
//资产类型
        public string Tname { get; set; }
//资产名称
        public string Aname { get; set; }
//数量
        public int Acount { get; set; }
//单位
        public string Aunit { get; set; }
//单价
        public decimal Amoney { get; set; }
//采购日期
        public System.DateTime ApurchaseTime { get; set; }
//登记日期
        public Nullable<System.DateTime> AregTime { get; set; }
//使用状态
        public Nullable<bool> Astate { get; set; }
//使用者
        public string Auser { get; set; }

3.在Controllers文件夹里面的Get请求的Index方法里面写联合查询的Linq查询语句并在HomeController控制器下调要Models类里的数据库模型类。

 AssetsDBEntities db = new AssetsDBEntities();
        // GET: Home
        public ActionResult Index()
        {
            ViewBag.AssetsDB = from i in db.assetsInfo
                               join t in db.assetsType
                               on i.tid equals t.tid
                               select new AssetsDBViewFrom
                               {
                                   Aid = i.aid,
                                   Tname = t.tname,
                                   Aname = i.aname,
                                   Acount = i.acount,
                                   Aunit = i.aunit,
                                   Amoney = i.amoney,
                                   ApurchaseTime = i.apurchaseTime,
                                   AregTime = i.aregTime,
                                   Astate = i.astate,
                                   Auser = i.auser
                               };
            ViewBag.AssetsDBinfo = db.assetsType;
            return View();
        }

4.条件查询，用资产名称和资产类型查询，

在Controllers文件夹里面的Post请求的Index方法里面写条件查询的联合查询的Linq查询语句

[HttpPost]
        public ActionResult Index(string Aname, int tid)
        {
            if (Aname != "" && tid != 0)
            {
                ViewBag.AssetsDB = from i in db.assetsInfo
                                   join t in db.assetsType
                                   on i.tid equals t.tid
                                   where i.aname.Contains(Aname)
                                   where i.tid == tid
                                   select new AssetsDBViewFrom
                                   {
                                       Aid = i.aid,
                                       Tname = t.tname,
                                       Aname = i.aname,
                                       Acount = i.acount,
                                       Aunit = i.aunit,
                                       Amoney = i.amoney,
                                       ApurchaseTime = i.apurchaseTime,
                                       AregTime = i.aregTime,
                                       Astate = i.astate,
                                       Auser = i.auser
                                   };
            }
            else if (Aname == "" && tid != 0)
            {
                ViewBag.AssetsDB = from i in db.assetsInfo
                                   join t in db.assetsType
                                   on i.tid equals t.tid
                                   where i.tid == tid
                                   select new AssetsDBViewFrom
                                   {
                                       Aid = i.aid,
                                       Tname = t.tname,
                                       Aname = i.aname,
                                       Acount = i.acount,
                                       Aunit = i.aunit,
                                       Amoney = i.amoney,
                                       ApurchaseTime = i.apurchaseTime,
                                       AregTime = i.aregTime,
                                       Astate = i.astate,
                                       Auser = i.auser
                                   };
            }
            else if (Aname != "" && tid == 0)
            {
                ViewBag.AssetsDB = from i in db.assetsInfo
                                   join t in db.assetsType
                                   on i.tid equals t.tid
                                   where i.aname.Contains(Aname)
                                   select new AssetsDBViewFrom
                                   {
                                       Aid = i.aid,
                                       Tname = t.tname,
                                       Aname = i.aname,
                                       Acount = i.acount,
                                       Aunit = i.aunit,
                                       Amoney = i.amoney,
                                       ApurchaseTime = i.apurchaseTime,
                                       AregTime = i.aregTime,
                                       Astate = i.astate,
                                       Auser = i.auser
                                   };
            }
            else
            {
                ViewBag.AssetsDB = from i in db.assetsInfo
                                   join t in db.assetsType
                                   on i.tid equals t.tid
                                   select new AssetsDBViewFrom
                                   {
                                       Aid = i.aid,
                                       Tname = t.tname,
                                       Aname = i.aname,
                                       Acount = i.acount,
                                       Aunit = i.aunit,
                                       Amoney = i.amoney,
                                       ApurchaseTime = i.apurchaseTime,
                                       AregTime = i.aregTime,
                                       Astate = i.astate,
                                       Auser = i.auser
                                   };
            }

            ViewBag.AssetsDBinfo = db.assetsType;
            ViewBag.tid = tid;
            ViewBag.Aname = Aname;
            return View();
        }

5.运行结果

 

三.添加

1.添加页面，首先在文件夹Controllers下面的Home控制器里面添加AddAssetsDBFrom方法用于写添加页面

 <form action="AddAssetsDB" method="post">
        <table align="center" border="1" cellspacing="0" width="500">
            <tr>
                <td colspan="2" align="center"><h1>添加资产信息</h1></td>
            </tr>
            <tr>
                <th>资产类型</th>
                <td>
                    <select name="tid">
                        <option value="0">--请选择--</option>
                        @foreach (var item in ViewBag.AssetsDB)
                        {
                            <option value="@item.tid">@item.tname</option>
                        }
                    </select>
                </td>
            </tr>
            <tr>
                <th>资产名称</th>
                <td><input type="text" name="aname" value="" /></td>
            </tr>
            <tr>
                <th>数量</th>
                <td><input type="text" name="acount" value="" /></td>
            </tr>
            <tr>
                <th>单位</th>
                <td><input type="text" name="aunit" value="" /></td>
            </tr>
            <tr>
                <th>单价</th>
                <td><input type="text" name="amoney" value="" /></td>
            </tr>
            <tr>
                <th>采购日期</th>
                <td><input type="text" name="apurchaseTime" value="" /></td>
            </tr>
            <tr>
                <th>当前状态</th>
                <td>
                    <input type="radio" name="astate" value="1" checked/>正常使用
                    <input type="radio" name="astate" value="0" />报废

                </td>
            </tr>
            <tr>
                <th>使用者</th>
                <td><input type="text" name="auser" value="" /></td>
            </tr>
            <tr>
                <th colspan="2" align="center"><input type="submit" value="添加" /></th>
            </tr>
        </table>
    </form>

2.在文件夹Controllers下面的Home控制器里面添加AddAssetsDBFrom方法用于写添加页面，

添加AddAssetsDB方法用于写添加的逻辑

 public ActionResult AddAssetsDBFrom()
        {
            ViewBag.AssetsDB = db.assetsType;
            return View();
        }
        public ActionResult AddAssetsDB()
        {
            assetsInfo assets = new assetsInfo()
            {
                tid = int.Parse(Request["tid"]),
                aname = Request["aname"],
                acount = int.Parse(Request["acount"]),
                aunit = Request["aunit"],
                amoney = decimal.Parse(Request["amoney"]),
                apurchaseTime = DateTime.Parse(Request["apurchaseTime"]),
                astate = (Request["astate"] == "1" ? true : false),
//给数据库里的登记时间哪个默认约束字段赋值。
                aregTime = DateTime.Now,
                auser = Request["auser"],
            };
            db.assetsInfo.Add(assets);
            int i = db.SaveChanges();
            if (i > 0)
            {
                return Content(" <script>alert('添加成功');window.location.href = 'Index';</script>");
            }
            else
            {
                return Content(" <script>alert('添加失败');window.location.href = 'AddAssetsDBFrom';</script>");
            }
        }

3.运行结果

 四.删除

1.在查询页面Index里面的删除按钮哪里设置主键方便在后端Home控制器里面利用主键发现一行数据并删除它。并在Home控制器里面添加DeleteAssetsDB方法写删除的逻辑，代码如下：

        public ActionResult DeleteAssetsDB()
        {
            int aid = int.Parse(Request["aid"]);
            var e = db.assetsInfo.Find(aid);
            db.assetsInfo.Remove(e);
            int i = db.SaveChanges();
            if (i > 0)
            {
                return Content(" <script>alert('删除成功');window.location.href = 'Index';</script>");
            }
            else
            {
                return Content(" <script>alert('删除失败');window.location.href = 'Index';</script>");
            }
        }

2.运行结果

 五.修改

1.修改页面，首先在文件夹Controllers下面的Home控制器里面添加ExitAssetsDBFrom方法用于写修改页面并在查询页面Index里面的修改按钮哪里设置主键方便在后端Home控制器里面利用主键发现一行数据并修改页面赋值。

修改页面里面需要添加一个隐藏文本域用来获取数据库里面的主键。便于在Home控制器里面的ExitAssetsDB方法获取一行数据去编辑它

<form action="ExitAssetsDB" method="post">
        <input type="hidden" name="aid" value="@ViewBag.AssetsDBinfo.aid" />
        <table align="center" border="1" cellspacing="0" width="500">
            <tr>
                <td colspan="2" align="center"><h1>修改资产信息</h1></td>
            </tr>
            <tr>
                <th>资产类型</th>
                <td>
                    <select name="tid">
                        <option value="0">--请选择--</option>
                        @foreach (var item in ViewBag.AssetsDB)
                        {
                            <option value="@item.tid" @(item.tid == ViewBag.AssetsDBinfo.tid ? "selected" : "")>@item.tname</option>
                        }
                    </select>
                </td>
            </tr>
            <tr>
                <th>资产名称</th>
                <td><input type="text" name="aname" value="@ViewBag.AssetsDBinfo.aname" /></td>
            </tr>
            <tr>
                <th>数量</th>
                <td><input type="text" name="acount" value="@ViewBag.AssetsDBinfo.acount" /></td>
            </tr>
            <tr>
                <th>单位</th>
                <td><input type="text" name="aunit" value="@ViewBag.AssetsDBinfo.aunit" /></td>
            </tr>
            <tr>
                <th>单价</th>
                <td><input type="text" name="amoney" value="@ViewBag.AssetsDBinfo.amoney" /></td>
            </tr>
            <tr>
                <th>采购日期</th>
                <td><input type="text" name="apurchaseTime" value="@ViewBag.AssetsDBinfo.apurchaseTime" /></td>
            </tr>
            <tr>
            <tr>
                <th>当前状态</th>
                <td>
                    <input type="radio" name="astate" value="1" @(ViewBag.AssetsDBinfo.astate?"checked":"")/>正常使用
                    <input type="radio" name="astate" value="0" @(!ViewBag.AssetsDBinfo.astate?"checked":"")/>报废

                </td>
            </tr>
            </tr>
            <tr>
                <th>使用者</th>
                <td><input type="text" name="auser" value="@ViewBag.AssetsDBinfo.auser" /></td>
            </tr>
            <tr>
                <th colspan="2" align="center"><input type="submit" value="修改" /></th>
            </tr>
        </table>
    </form>

2.在文件夹Controllers下面的Home控制器里面添加AddAssetsDBFrom方法用于写修改页面，

添加ExitAssetsDB方法用于写修改的逻辑

代码如下：

  public ActionResult ExitAssetsDBFrom()
        {
            ViewBag.AssetsDB = db.assetsType;
            int aid = int.Parse(Request["aid"]);
            ViewBag.AssetsDBinfo = db.assetsInfo.Find(aid);
            return View();
        }
        public ActionResult ExitAssetsDB()
        {
            int aid = int.Parse(Request["aid"]);
            var e = db.assetsInfo.Find(aid);
            e.tid = int.Parse(Request["tid"]);
            e.aname = Request["aname"];
            e.acount = int.Parse(Request["acount"]);
            e.aunit = Request["aunit"];
            e.amoney = decimal.Parse(Request["amoney"]);
            e.apurchaseTime = DateTime.Parse(Request["apurchaseTime"]);
            e.astate = (Request["astate"] == "1" ? true : false);

            e.aregTime = DateTime.Now;
            e.auser = Request["auser"];
            int i = db.SaveChanges();
            if (i > 0)
            {
                return Content(" <script>alert('编辑成功');window.location.href = 'Index';</script>");
            }
            else
            {
                return Content(" <script>alert('编辑失败');window.location.href = 'Index';</script>");
            }
        }

3.运行结果