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
{
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
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
{
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
{
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
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
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;
}
| 编号 | 学号 | 姓名 | 年龄 | 操作 |
