增删改查 Dapper的使用+ajax基础操作

DapperAPI中的DAL

//先写连接字符串
string connstr = “Data Source=.;Initial Catalog=Lianxi1;Integrated Security=True”;

    //添加
    public int AddStu(StudentInfo s)
    {
        using (SqlConnection conn = new SqlConnection(connstr))
        {
            return conn.Execute(string.Format("insert into StudentInfo values('{0}','{1}','{2}')", s.SName, s.Age, s.Sex));
        }
    }
    //显示
    public List<StudentInfo> Show()
    {
        using (SqlConnection conn = new SqlConnection(connstr))
        {
            return conn.Query<StudentInfo>("select * from StudentInfo").ToList();
        }
    }
    //删除
    public int DelStu(int sid)
    {
        using (SqlConnection conn=new SqlConnection(connstr))
        {
            return conn.Execute(string.Format("delete from StudentInfo where SId='{0}'", sid));
        }
    }      
    //修改
    public int UptStu(StudentInfo s)
    {
        using (SqlConnection conn = new SqlConnection(connstr))
        {
        
            return conn.Execute($"update StudentInfo set SName='{s.SName}',Age='{s.Age}',Sex='{s.Sex}' where SId='{s.SId}'");
        }
    }
    //反填
    public StudentInfo Fan(int sid)
    {
        using (SqlConnection conn = new SqlConnection(connstr))
        {
            return conn.Query<StudentInfo>($"select * from StudentInfo where SId={sid}").FirstOrDefault();
        }
    }

在控制器中中有的调用是要加参数
StudentDAL dal = new StudentDAL();
[HttpPost]
public int AddStu(StudentInfo s)
{
return dal.AddStu(s);
}
[HttpGet]
public List Show()
{
return dal.Show();
}
[HttpPost]
public int DelStu(int sid)
{
return dal.DelStu(sid);
}
[HttpPost]
public int UptStu(StudentInfo s)
{
return dal.UptStu(s);
}
[HttpGet]
public StudentInfo Fan(int sid)
{
return dal.Fan(sid);
}

    在反填修改时要传修改的Id 在MVC控制器中
    public ActionResult Upt(int sid)
    {
        ViewBag.sid = sid;
        return View();
    }

下面是ajax显示页面
$(function () {
Show();
})

function Show() {
    $.ajax({
        url: "http://localhost:2363/api/Stu/Show",
        dataType: "json",
        type: "get",
        success: function (d) {
            $("#tb").empty();
            $(d).each(function () {
                $("#tb").append("<tr>" +
                    "<td>" + this.SId + "</td>" +
                    "<td>" + this.SName + "</td>" +
                    "<td>" + this.Age + "</td>" +
                    "<td>" + this.Sex + "</td>" +
                    "<td>" + "<input id='Button1' type='button' value='删除' onclick='Del(" + this.SId + ")'/>" + 
                    "<input id='Button1' type='button' value='修改' onclick='Upt(" + this.SId + ")'/>"+ "</td>" +
                    "</tr>");          
            });
        }
    })
}
function Del(sid) {
    $.ajax({
        url: "http://localhost:2363/api/Stu/DelStu?sid="+sid,
        dataType: "json",
        type: "post",
        success: function (d) {
            if (d>0) {
                alert("删除成功");
                Show();
            }
            else {
                alert("删除失败");
            }
        }
    })
}
//修改跳转
function Upt(sid) {
    location.href = 'Upt?sid=' + sid;
}
添加页面
function Add() {
    var obj = {
        SName: $("#SName").val(),
        Age: $("#Age").val(),
        Sex: $("input[name='Sex']:checked").val()
    };
    $.ajax({
        url: "http://localhost:2363/api/Stu/AddStu",
        dataType: "json",
        type: "post",
        data:obj,
        success: function (d) {
            if (d>0) {
                alert("添加成功");
                location.href = "Index";
            }
            else {
                alert("添加失败");
            }
        }
    })
}
修改反填页面

            <td>姓名:</td>
            <td>
                <input id="SName" type="text" />
                <input id="Hidden1" type="hidden" value="@ViewBag.sid" />
            </td>

反填

   $(function () {
    Fan();
})
function Fan() {
    $.ajax({
        url: "http://localhost:2363/api/Stu/Fan?sid=" + $("#Hidden1").val(),
        dataType: "json",
        type: "get",
        success: function (d) {
            $("#SName").val(d.SName);
            $("#Age").val(d.Age);
            $('input[name="Sex"][value="' + d.Sex + '"]').prop("checked", "true");
        }
    })
}
//修改
function Upt() {
    var obj = {
        SName: $("#SName").val(),
        Age: $("#Age").val(),
        Sex: $("input[name='Sex']:checked").val(),
        SId: $("#Hidden1").val()
    };
    $.ajax({
        url: "http://localhost:2363/api/Stu/UptStu",
        dataType: "json",
        type: "post",
        data: obj,
        success: function (d) {
            if (d > 0) {
                alert("修改成功");
                location.href = "Index";
            }
            else {
                alert("修改失败");
            }
        }
    })
}