Mvc+三层(批量添加、删除、修改)

DAL层

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Data;
using Newtonsoft.Json;
using System.Data.SqlClient;

namespace DAL
{
    
    public class StuDal
    {
        public static SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Day15;Integrated Security=True");
        ///


        /// 查询所有
        ///

        ///
        public List GetStu(string name)
        {
            string sql = "select * from Student where 1=1";
            if (!string.IsNullOrEmpty(name))
            {
                sql += "and StuName like '%"+name+"%'";
            }
            var res=DBHelper.GetDataSet(sql).Tables[0];
            var resd=JsonConvert.SerializeObject(res);
            var list=JsonConvert.DeserializeObject<List>(resd);
            return list;
        }

        ///


        /// 添加
        ///

        ///
        ///
        public int AddStu(Student s)
        {
            string sql = string.Format("insert into Student values('{0}','{1}',{2})",s.StuNum,s.StuName,s.Age);
            return DBHelper.ExecuteNonQuery(sql);
        }

        ///


        /// 事务添加
        ///

        ///
        ///
        public int AddList(List list)
        {
            conn.Open();
            using (SqlTransaction tran=conn.BeginTransaction())
            {
                try
                {
                    int res=0;
                    foreach (var item in list)
                    {
                        string sql = string.Format("insert into Student values('{0}','{1}',{2})",item.StuNum,item.StuName,item.Age);
                        SqlCommand command = new SqlCommand(sql, conn);
                        command.Transaction = tran;
                        int result = command.ExecuteNonQuery();
                        res += result;
                    }
                    tran.Commit();
                    conn.Close();
                    return res;
                }
                catch (Exception)
                {
                    tran.Rollback();
                    conn.Close();
                    return 0;
                }
            }
        }

        ///


        /// 删除
        ///

        ///
        ///
        public int DelStu(string  id)
        {
            string sql = "delete Student where Id in '" + id + "'";
            return DBHelper.ExecuteNonQuery(sql);
        }

        ///


        /// 存储过程删除
        ///

        ///
        ///
        public int Del(int id)
        {
            conn.Open();
            SqlCommand com = new SqlCommand("P_Shanchu",conn);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@id", SqlDbType.Int).Value = id;

            com.Parameters.AddWithValue("@return_value", SqlDbType.Int);
            com.Parameters["@return_value"].Direction = ParameterDirection.ReturnValue;
            //执行
            com.ExecuteNonQuery();
            conn.Close();
            return Convert.ToInt32(com.Parameters["@return_value"].Direction);

           // return Convert.ToInt32(com.Parameters.AddWithValue("@return_value", SqlDbType.Int).Value);
            
        }

        ///


        /// 修改
        ///

        ///
        ///
        public int UpdStu(Student s)
        {
            string sql =string.Format("update Student set StuNum='{0}',StuName='{1}',Age={2} where ID={3}", s.StuNum, s.StuName, s.Age,s.ID);
            return DBHelper.ExecuteNonQuery(sql);
        }

        ///


        /// 查询单条数据
        ///

        ///
        public List GetStuByID(int id)
        {
            string sql = "select * from Student where ID="+id;
            var res = DBHelper.GetDataSet(sql).Tables[0];
            var resd = JsonConvert.SerializeObject(res);
            var list = JsonConvert.DeserializeObject<List>(resd);
            return list;
        }
    }
}

controller层

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using BLL;
using Model;
using Newtonsoft.Json;

namespace Day15Mvc.Controllers
{
    public class StuController : Controller
    {
        //
        // GET: /Stu/

        //对象实例化
        StuBll bll = new StuBll();

        public ActionResult Index()
        {
         
            return View();
        }
        
        public ActionResult Show()
        {
            return View();
            
        }
        [HttpPost]
        public JsonResult DelShow(int id)
        {
            int q = bll.Del(id);
            if (q > 0)
            {
                return Json(new { code = 1, message = "成功" });
            }
            else
            {
                return Json(new { code = 0, message = "失败" });
            }

        }

        [HttpPost]
        public JsonResult ShowStu(string name)
        {
            var res = bll.GetStu(name);         
            return Json(res);
        }
        
        public JsonResult AddStu(string str)
        {
            str= str.TrimEnd('|');
            string[] st = str.Split('|');
            //判断是否有值
            if (st == null || st.Length == 0)
            {
                //匿名类
                return Json(new { code = 0, message = "失败" });
            }
            else
            {
                List list = new List();
                foreach (var item in st)
                {
                    string[] s = item.Split(',');
                    if (s == null || s.Length == 0)
                    {
                        break;
                       // return Json(new { code = 0, message = "失败" });                      
                    }
                    else
                    {
                        Student student = new Student();
                        student.StuNum = s[0];
                        student.StuName = s[1];
                        student.Age = Convert.ToInt32(s[2]);
                        list.Add(student);                     
                    }
                }

                if (bll.AddList(list) > 0)
                {
                    return Json(new { code = 1, message = "添加成功" });
                }
                else
                {
                    return Json(new { code = 0, message = "添加失败" });
                }
            }
           
            
        }

       
        [HttpPost]
        public JsonResult UpdStu(string id,string num,string name,string age)
        {
            Student stu = new Student()
            {
                ID = Convert.ToInt32(id),
                StuNum = num,
                StuName = name,
                Age = Convert.ToInt32(age)
            };

            if (bll.UpdStu(stu) > 0)
            {
                return Json(new { code = 1, message = "修改成功" });
            }
            else
            {
                return Json(new { code = 0, message = "修改失败" });
            }
        }
    }
}

前台

@{
    Layout = null;
}

                   

   
                                                                                                                                                                                                                       
编号学号姓名年龄操作
   

评论

暂无评论