ASP.NET MVC 学习(一) ado.net 调用存储过程

最新推荐文章于 2023-04-25 00:13:19 发布

隶属感

最新推荐文章于 2023-04-25 00:13:19 发布

阅读量3.8k

点赞数

本文链接：https://blog.csdn.net/qq_36445227/article/details/78923712

版权

ASP.NET MVC 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

ASP.NET MVC 学习(一) ado.net 调用存储过程

见证我的菜鸟历史:

想学一学存储过程,自己写了一下简单的例子,发现了一些问题这里记录一下

以下是存储过程:

create procedure [dbo].[UserAll]
 @loginname nvarchar(200),
 @password nvarchar(200),
 @issuccess int output
as
begin
print @loginname
print @password
declare @isexist int
declare  @userid  uniqueidentifier
declare  @roleid uniqueidentifier
if  exists(select 1 from Tuser where LoginName=@loginname and PassWord=@password)
begin
 set @issuccess=1
  set @userid=(select UserId from Tuser where LoginName=@loginname and PassWord=@password)
  set @roleid=(select top 1 RoleId from  UserRole where UserId=@userid);
  select * from  Trole where RoleId= @roleid
  end
  else
  begin
 set @issuccess=0
  end
end

.net mvc 代码如下:

public ActionResult Index()
        {
            ViewBag.issuccess = false;
            if (Request.Form["loginname"]==null|| Request.Form["loginname"]=="")
            {
                return View();
            }
            string loginname = Request.Form["loginname"];
            string password = Request.Form["password"];
            //存储过程
            SqlConnection con = new SqlConnection("data source=192.168.124.93;initial catalog=PadOrder;persist security info=True;user id=sa;password=Jxasfw2017;");
            SqlCommand cmd = new SqlCommand("UserAll", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@loginname", loginname);//添加参数
            cmd.Parameters.AddWithValue("@password", password);//添加参数
            SqlParameter issuccess=  cmd.Parameters.AddWithValue("@issuccess", 0);//添加参数
            cmd.Parameters["@issuccess"].Direction = ParameterDirection.Output;//设置参数类型
            con.Open();
            SqlDataReader yh = cmd.ExecuteReader();
            DataReaderHelper helper = new DataReaderHelper();
            DataSet dataset= helper.ConvertDataReaderToDataSet(yh);
            con.Close();//
            if (int.Parse(issuccess.Value.ToString()) != 0)
            {
                ViewBag.issuccess = true;
                var datahelper = new DataHelper<Trole>();
                var res = datahelper.FillModel(dataset);
                ViewBag.userdata = res;
            }
            return View();
        }

在代码中发现存储过程的 output 类型的参数只有在连接关闭以后才能获取到值也就是 con.Close()之后

读取到的数据库的一列显示到view:

view 页如下:

@{
    Layout = null;
}
<!DOCTYPE html>
<title>hhda</title>
<html>
<head></head>
<body>
    <div class="jumbotron">
        <h1>ASP.NET</h1>
    <form action="@Url.Action("Index","Home")" enctype="multipart/form-data" method="post">
        <div><label>登录名</label><input id="loginname" name="loginname"/></div>
        <div><label>密--码</label><input id="password" name="password" /></div>
        <br />
        <br />
        <input type="submit" value="登陆"/>
    </form>
        @if (ViewBag.issuccess)
        {
            <div>
                <div><label>你的角色信息如下:</label></div>
                <table>
                    @foreach (var item in ViewBag.userdata)
                    {
                        <tr>
                            <td><label>Id</label><input type="text" value="@item.Id" /></td>
                            <td><label>URL</label><input type="text" value="@item.name" /></td>
                            <td><label>SortId</label><input type="text" value="@item.context" /></td>
                            <td><label>ParentId</label><input type="text" value="@item.OrgStationID" /></td>
                            <td><label>Ischange</label><input type="text" value="@item.RoleId" /></td>
                        </tr>
                    }
                </table>
            </div>
        }
        else
        {
           <div><label style="color:red">密码或账号错误</label></div>
        }


        <table></table>
    </div>
</body>
</html>

三个表的情况: