用ADO调用存储过程实现一个登陆注册页面

最新推荐文章于 2017-12-28 16:33:12 发布

wangjingjing1014

最新推荐文章于 2017-12-28 16:33:12 发布

阅读量1.1k

点赞数

本文链接：https://blog.csdn.net/wangjingjing1014/article/details/8243234

版权

-------login.aspx--------
<body>
    <form id="form1" runat="server">
    <div>

        用户名：<asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
        <br />
        密  码：<asp:TextBox ID="txtPassword" runat="server"></asp:TextBox>
        <br />
        <asp:Button ID="BtnLogin" runat="server" οnclick="BtnLogin_Click" Text="登陆" />
        <br />
        <asp:Label ID="Label1" runat="server"></asp:Label>

        <br />
        <asp:Label ID="Label2" runat="server"></asp:Label>

    </div>
    </form>
</body>
----------login.aspx.cs----------
protected void BtnLogin_Click(object sender, EventArgs e)
        {
            string username = txtUserName.Text.Trim();
            string password = txtPassword.Text.Trim();
            //使用ADO连接数据库
            string UserConstr = ConfigurationManager.ConnectionStrings["UserDB1conStr"].ConnectionString;
           using( SqlConnection conn=new SqlConnection(UserConstr))
           {
               conn.Open();
               using(SqlCommand cmd=conn.CreateCommand())
               {
               //string sql="insert into T_Users(UserName,Password) values ('王菲','555')";
                   string sp_name = "usp_Login";
               // cmd.CommandText=sql;
                   //执行sql语句和执行存储过程最大的区别！需要设置一个commandType
                   cmd.CommandType = CommandType.StoredProcedure;
                   cmd.CommandText = sp_name;
                   SqlParameter prml = new SqlParameter("@uname", username);
                   SqlParameter prm2 = new SqlParameter("@password", password);
                   SqlParameter prm3 = new SqlParameter("@result", System.Data.SqlDbType.Bit);
                   cmd.Parameters.Add(prml);
                   cmd.Parameters.Add(prm2);
                   cmd.Parameters.Add(prm3);
                   prm3.Direction = ParameterDirection.Output;

//第一种
int r = cmd.ExecuteNonQuery();

//第二种
//object obj = cmd.ExecuteScalar();

                   //第三种
                   //using (SqlDataReader reader = cmd.ExecuteReader())
                   //{ }

                   bool b=Convert.ToBoolean(prm3.Value);
                   if (b)
                   {
                       Label2.Text = "登陆成功";

                   }
                   else
                   {
                       Label2.Text = "登录失败！";

                   }
                   Label1.Text = r.ToString();

               }
           }

}

------------数据库中的存储过程----------------
create proc usp_Login
@uname varchar(50),
@password varchar(50),
@result bit output   --这个布尔类型的参数,是用来返回值的,存储用户登陆是否成功。1-----true 0---false
as
begin
   declare @count int
   set @count=( select COUNT(*) from T_Users where UserName=@uname and Password=@password)
   if @count>0
    begin
  set @result=1
    end
   else
    begin
  set @result=0
    end
end
--------------login3.aspx-------------------
<body>
    <form id="form1" runat="server">
    <div>

        用户名：<asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
        <br />
        密  码：<asp:TextBox ID="txtPassword" runat="server"></asp:TextBox>
        <br />
        <asp:Button ID="BtnLogin" runat="server" οnclick="BtnLogin_Click" Text="登陆" />
        <br />
        <asp:Label ID="Label1" runat="server"></asp:Label>

        <br />
        <asp:Label ID="Label2" runat="server"></asp:Label>

        <br />

    </div>
    </form>
</body>
--------------login3.aspx.cs-------------------
protected void BtnLogin_Click(object sender, EventArgs e)
        {
            string username = txtUserName.Text.Trim();
            string password = txtPassword.Text.Trim();
            //使用ADO连接数据库
            string UserConstr = ConfigurationManager.ConnectionStrings["UserDB1conStr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(UserConstr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    //string sql="insert into T_Users(UserName,Password) values ('王菲','555')";
                    string sp_name = "usp_chkLogin";

                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = sp_name;
                    SqlParameter prml = new SqlParameter("@username", username);
                    SqlParameter prm2 = new SqlParameter("@pwd", password);

                    cmd.Parameters.Add(prml);
                    cmd.Parameters.Add(prm2);

//第一种
//int r = cmd.ExecuteNonQuery();

//第二种
object obj = cmd.ExecuteScalar();

                    //第三种
                    //using (SqlDataReader reader = cmd.ExecuteReader())
                    //{ }

                   int b = Convert.ToInt32(obj);
                    if (b>0)
                    {
                        Label2.Text = "登陆成功";

                    }
                    else
                    {
                        Label2.Text = "登录失败！";

}

                }
            }
        }
-----------------存储过程----------------
create proc usp_chkLogin
@username varchar(50),
@pwd varchar(50)
as
begin
select COUNT(*) from T_Users where UserName=@username and Password=@pwd
end

-------------registor.aspx---------------
<body>
    <form id="form1" runat="server">
    <div>

        用户名：<asp:TextBox ID="txtUseName" runat="server"></asp:TextBox>
        <br />
        密  码：<asp:TextBox ID="txtPassword" runat="server"></asp:TextBox>
        <br />
        邮  箱：<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
        <br />
        删除的Id:<asp:TextBox ID="txtDeleteId" runat="server"></asp:TextBox>
        <br />
        <asp:Button ID="btnLogin" runat="server" οnclick="btnLogin_Click" Text="注册" />

         <asp:Button ID="BtnDeleteId" runat="server" οnclick="BtnDeleteId_Click"
            Text="删除ID" />

         
        <asp:Button ID="Button1" runat="server" οnclick="Button1_Click"
            Text="ADOYU事物" />

        <br />
        <asp:Label ID="LabelMessage" runat="server"></asp:Label>
        <asp:Label ID="Label2" runat="server"></asp:Label>

        <asp:Label ID="Label3" runat="server" Text="Label"></asp:Label>

    </div>
    </form>
</body>
-------------registor.aspx.cs---------------
   protected void btnLogin_Click(object sender, EventArgs e)
        {
             string username = txtUseName.Text.Trim();
            string password = txtPassword.Text.Trim();
            string email = txtEmail.Text.Trim();

            //string UserConstr = ConfigurationManager.ConnectionStrings["UserDB1conStr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(UserConstr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    string sp_name = "usp_T_Users_Insert";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = sp_name;
                    SqlParameter prm1 = new SqlParameter("@username", username);
                    SqlParameter prm2 = new SqlParameter("@password", password);
                    SqlParameter prm3 = new SqlParameter("@email", email);
                    cmd.Parameters.Add(prm1);
                    cmd.Parameters.Add(prm2);
                    cmd.Parameters.Add(prm3);

                int r = cmd.ExecuteNonQuery();
                bool b = Convert.ToBoolean(prm3.Value);

                if (r > 0)
                {
                    LabelMessage.Text = "注册成功！";
                }
                else
                {
                    LabelMessage.Text = "注册失败！";
                }
                Label2.Text = r.ToString();
                }
            }
        }

        protected void BtnDeleteId_Click(object sender, EventArgs e)
        {
            using (SqlConnection conn = new SqlConnection(UserConstr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    string sp_name = "usp_T_Users_delete";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = sp_name;

                    SqlParameter prm1 = new SqlParameter("@Id", 8);
                    cmd.Parameters.Add(prm1);
                    int r = cmd.ExecuteNonQuery();
                    if (r > 0)
                    {
                        LabelMessage.Text = "删除成功："+r.ToString()+"条记录";
                    }
                    else
                    {
                        LabelMessage.Text = "删除失败！";
                    }

                }
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            using (SqlConnection conn = new SqlConnection(UserConstr))
            {
                conn.Open();
                SqlTransaction tran = conn.BeginTransaction();//打开事物
                string sql = "delete from T_Users where PersonId=17";
                using (SqlCommand cmd =new SqlCommand(sql,conn))
                {
                    cmd.Transaction = tran;

                    int r = cmd.ExecuteNonQuery();

                    tran.Commit();
                    Label2.Text = "删除成功了！" + "影响了" + r.ToString() + "条记录";
                    //tran.Rollback();
                    //Label2.Text = "回滚了了！" + "影响了" + r.ToString() + "条记录";

                }
            }

}
--创建一个向特定表中插入记录的存储过程
select * from T_Users
create proc usp_T_Users_Insert
@username varchar(50),
@password varchar(50),
@email varchar(50)
as
begin
insert into T_Users values(@username,@password,@email)
end

SELECT * from T_Users where PersonId=1
use UserDB1
select * from T_Users
create proc usp_T_Users_delete
@Id int
as
begin
delete from T_Users where PersonId=@Id
end