asp中实现sql事务登录_增删改查

来源：互联网发布：人工智能开发入门编辑：程序博客网时间：2024/04/27 17:47

web.config

<?xml version="1.0" encoding="utf-8"?>

</connectionStrings>

</configuration>

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="login.aspx.cs" Inherits="webado.login" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace webado
{
    public partial class login : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

}

        protected void Button1_Click(object sender, EventArgs e)
        {
            string connstr=ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType=CommandType.StoredProcedure;
                    cmd.CommandText = "usp_login";
                   SqlParameter p1= new SqlParameter("@uname",TextBox1.Text);
                   SqlParameter p2= new SqlParameter("@password", TextBox2.Text);
                   SqlParameter p3= new SqlParameter("@result", System.Data.SqlDbType.Bit);
                   p3.Direction = ParameterDirection.Output;
                   cmd.Parameters.Add(p1);
                   cmd.Parameters.Add(p2);
                   cmd.Parameters.Add(p3);
                   cmd.ExecuteNonQuery();
                   bool a= Convert.ToBoolean( p3.Value);
                   if (a == true)
                   {
                       Response.Write("登录成功");
                   }
                   else
                   {
                       Response.Write("登录失败");
                   }

                }
            }
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "usp_chkLogin";
                    SqlParameter p1 = new SqlParameter("@uname", TextBox1.Text);
                    SqlParameter p2 = new SqlParameter("@password", TextBox2.Text);
                    cmd.Parameters.Add(p1);
                    cmd.Parameters.Add(p2);
                    object cmdes = cmd.ExecuteScalar();
                    int r = Convert.ToInt32(cmdes);
                    if (r > 0)
                    {
                        Response.Write("登录成功");
                    }
                    else
                    {
                        Response.Write("登录失败");
                    }

                }
            }
        }

        protected void Button3_Click(object sender, EventArgs e)
        {
            string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "usp_insert";
                    SqlParameter p1 = new SqlParameter("@uname", TextBox1.Text);
                    SqlParameter p2 = new SqlParameter("@password", TextBox2.Text);
                    SqlParameter p3 = new SqlParameter("@message", System.Data.SqlDbType.Int);
                    p3.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(p1);
                    cmd.Parameters.Add(p2);
                    cmd.Parameters.Add(p3);
                    cmd.ExecuteNonQuery();
                    if (Convert.ToInt32(p3.Value) ==1)
                    {
                        Response.Write("成功注册");
                    }
                    else
                    {
                        Response.Write("当前用户名已存在，换一个吧");
                    }
                }
            }
        }

        protected void Button4_Click(object sender, EventArgs e)
        {
            string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "usp_delect";
                    SqlParameter p1 = new SqlParameter("@name", TextBox1.Text);
                    SqlParameter p2 = new SqlParameter("@message", System.Data.SqlDbType.Bit);
                    p2.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(p1);
                    cmd.Parameters.Add(p2);
                    cmd.ExecuteNonQuery();
                    if (Convert.ToBoolean(p2.Value))
                    {
                        Response.Write("删除成功");
                    }
                    else
                    {
                        Response.Write("无此记录");
                    }
                }
            }
        }

        protected void Button5_Click(object sender, EventArgs e)
        {
            string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "usp_select";
                    SqlParameter p1 = new SqlParameter("@name", TextBox1.Text);
                    cmd.Parameters.Add(p1);
                    cmd.ExecuteNonQuery();
                    DataSet ds = new DataSet();
                    SqlDataAdapter ad = new SqlDataAdapter(cmd);
                    ad.Fill(ds);
                    GridView1.DataSource = ds;
                    GridView1.DataBind();
                }

            }
        }
    }
}

sql:

--1 存储过程复用代码案例——如果english不及格的人超过半数，则给每个人增加2分,循环加，直到不及格的人数少于一半。
--usp_promoteGrade有一个参数，及格分数线@passline。
select * from Score
create proc usp_promoteGrade @a int
as
begin
declare @a1 int
set @a1=(select COUNT(*) from Score where english<@a)
while(@a1>(select COUNT(*) from Score)/2)
begin
update Score set english=english+2
set @a1=(select COUNT(*) from Score where english<@a)
end
end

exec usp_promoteGrade 60
--2ADO中使用存储过程
--1-1写usp_Login存储过程,参数有三个，其中第三个参数为output，用来返回登陆是否成功，
--成功1，失败0（ @uname varchar(50), @password varchar(50), @result bit output）
use test1

create proc usp_login @uname varchar(50),@password varchar(50),@result bit output
as
begin
declare @t_count int
set @t_count= (select COUNT(*) from T_denglu where f_user=@uname and f_password=@password)
if(@t_count>0)
begin
set @result=1
end
else
begin
set @result=0
end
end
------------------------------
create proc usp_chkLogin @uname varchar(50),@password varchar(50)
as
begin
select COUNT(*) from T_denglu where f_user=@uname and f_password=@password
end
----------------------------------
create proc usp_insert @uname varchar(50),@password varchar(50),@message int output
as
begin
declare @fcount int
set @fcount=(select COUNT(*) from T_denglu where f_user=@uname)
if(@fcount>0)
begin
set @message=0;
end
else
begin
set @message=1;
insert into T_denglu (f_user,f_password) values (@uname,@password);
end
end

--------------------------------
create proc usp_delect @name varchar(50),@message bit output
as
begin
declare @fcount int=(select COUNT(*) from T_denglu where f_user=@name)
if(@fcount>0)
begin
set @message=1;
delete from T_denglu where f_user=@name
end
else
begin
set @message=0;
end
end
------------------------------
create proc usp_select @name varchar(50)
as
begin
select * from T_denglu where f_user=@name;
end