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");
/// <summary>
/// 查询所有
/// </summary>
/// <returns></returns>
public List<Student> 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<Student>>(resd);
return list;
}
/// <summary>
/// 添加
/// </summary>
/// <param name="s"></param>
/// <returns></returns>
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);
}
/// <summary>
/// 事务添加
/// </summary>
/// <param name="s"></param>
/// <returns></returns>
public int AddList(List<Student> 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;
}
}
}
/// <summary>
/// 删除
/// </summary>
/// <param name="s"></param>
/// <returns></returns>
public int DelStu(string id)
{
string sql = "delete Student where Id in '" + id + "'";
return DBHelper.ExecuteNonQuery(sql);
}
/// <summary>
/// 存储过程删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
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);
}
/// <summary>
/// 修改
/// </summary>
/// <param name="s"></param>
/// <returns></returns>
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);
}
/// <summary>
/// 查询单条数据
/// </summary>
/// <returns></returns>
public List<Student> 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<Student>>(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<Student> list = new List<Student>();
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;
}
//修改的数据
$(document).on("click", ".btnUpd", function () {
if ($(this).val() == "修改") {
var id = $(this).parent().siblings().eq(0).html();
var num = $(this).parent().siblings().eq(1).html();
var name = $(this).parent().siblings().eq(2).html();
var age = $(this).parent().siblings().eq(3).html();
$(this).parent().siblings().eq(1).html("<input name=\"Text2\" type=\"text\" value='" + num + "'/>");
$(this).parent().siblings().eq(2).html("<input name=\"Text3\" type=\"text\" value='" + name + "'/>");
$(this).parent().siblings().eq(3).html("<input name=\"Text4\" type=\"text\" value='" + age + "'/>");
$(this).val("确定");
}
else {
var id = $(this).parent().siblings().eq(0).html();
var num = $(this).parent().parent().find("input[name='Text2']").val();
var name = $(this).parent().parent().find("input[name='Text3']").val();
var age = $(this).parent().parent().find("input[name='Text4']").val();
$.ajax({
url: "/Stu/UpdStu",
type: "post",
dataType: "json",
data: { id: id,num:num,name:name,age:age },
success: function (Data) {
if (Data.code == 1) {
alert(Data.message);
show();
}
else {
alert(Data.message);
}
}
})
}
})
//$(".btndel").parent().siblings().eq(0).text();
})
//删除
function dell(id)
{
$.ajax({
url: "/Stu/DelShow",
type: "post",
dataType: "json",
data: { id: id },
success: function (Data) {
if (Data.code == 1) {
alert(Data.message);
show();
}
else {
alert(Data.message);
}
}
})
}
</script>