web.config
<?xml version="1.0" encoding="utf-8"?>
<!--
有关如何配置 ASP.NET 应用程序的详细消息,请访问
http://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<system.web>
<compilation debug="true" targetFramework="4.0" />
</system.web>
<connectionStrings>
<add name="connstr" connectionString ="Data Source=PC-20120110JFDT;Initial Catalog=test1;Persist Security Info=True;User ID=sa;Password=yhb"/>
</connectionStrings>
</configuration>
login.aspx:
<%@ 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">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
用户名<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
密码<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<br />
<asp:Button ID="Button1" runat="server" Text="登录" οnclick="Button1_Click" />
<asp:Button ID="Button2" runat="server" Text="登录2" οnclick="Button2_Click" />
<asp:Button ID="Button3" runat="server" Text="注册" οnclick="Button3_Click" />
<asp:Button ID="Button4" runat="server" Text="删除" οnclick="Button4_Click" />
<asp:Button ID="Button5" runat="server" Text="查询" οnclick="Button5_Click" />
</div>
<div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
login.aspx.cs:
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