asp.net+存储过程做个简单的注册
三层搭建就不说了:
1.实体类不多说 UserInfoModel
2.公共数据连接类
using
System; using
System.Collections.Generic; using
System.Linq; using
System.Text; using
System.Data; using
System.Data.SqlClient; namespace
Comment { /// <summary> /// 数据连接公共类 /// </summary> public
static class SqlHelper { //数据库连接字符串 windes身份验证 public
static string ConnStr = "Data Source=(local);Initial Catalog=DBTest;Integrated Security=True" ; /// <summary> /// 通用查询 支持存储过程 参数可有可无 /// </summary> /// <param name="sql"></param> /// <param name="ct"></param> /// <param name="Paramster"></param> /// <returns></returns> public
static int ExecuteNonQuery( string
sql,CommandType ct, params
SqlParameter[] Paramster) { //创建SqlConnection对象传入Connsrt连接字符串 using (SqlConnection conn= new
SqlConnection(ConnStr)) { conn.Open(); //打开连接 //创建SqlCommand对象 using (SqlCommand cmd=conn.CreateCommand()) { int
requset = 0; cmd.CommandText = sql; cmd.CommandType = ct; cmd.Parameters.AddRange(Paramster); requset = cmd.ExecuteNonQuery(); return
requset; } } } /// <summary> /// 通用增删改 支持存储过程 参数可有可无 /// </summary> /// <param name="sql"></param> /// <param name="ct"></param> /// <param name="Paramester"></param> /// <returns></returns> public
static DataSet ExecuteNonQueryDataSet( string
sql, CommandType ct, params
SqlParameter[] Paramester) { using (SqlConnection conn= new
SqlConnection(ConnStr)) { conn.Open(); using
(SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.CommandType = ct; cmd.Parameters.AddRange(Paramester); SqlDataAdapter dat = new
SqlDataAdapter(cmd); DataSet ds = new
DataSet(); dat.Fill(ds); return
ds; } } } } } |
3.DAL UserInfoDAL
using
System; using
System.Collections.Generic; using
System.Linq; using
System.Text; using
System.Data.SqlClient; using
System.Data; namespace
DAL { public
class UserInfoDAL { //Add public
static int getAdd(Model.UserInfoModel us) { int
requset=0; //string sql = "INSERT INTO UserInfo(UserName,UserPwd,age,sex,emel,adddate) VALUES (@UserName,@UserPwd,@age,@sex,@emel,@adddate)"; //使用存储过程 string
sql = "UserInfo_Add" ; var
pa = new
SqlParameter[] { new
SqlParameter( "@username" ,SqlDbType.VarChar,50), new
SqlParameter( "@userpwd" ,SqlDbType.VarChar,50), new
SqlParameter( "@age" ,SqlDbType.Int), new
SqlParameter( "@sex" ,SqlDbType.VarChar,50), new
SqlParameter( "@emel" ,SqlDbType.VarChar,200), new
SqlParameter( "@adddate" ,SqlDbType.DateTime,50), //返回参数 new
SqlParameter( "@userid" ,SqlDbType.Int), }; pa[0].Value = us.username; pa[1].Value = us.userpwd; pa[2].Value = us.age; pa[3].Value = us.sex; pa[4].Value = us.emel; pa[5].Value = us.adddate; pa[6].Value =0; //存储过程返回参数默认给个0 //int requset = Comment.SqlHelper.ExecuteNonQuery(sql, CommandType.Text, pa); requset = Comment.SqlHelper.ExecuteNonQuery(sql, CommandType.StoredProcedure, pa); return
requset; } } } |
4.UI
using
System; using
System.Collections.Generic; using
System.Linq; using
System.Web; using
System.Web.UI; using
System.Web.UI.WebControls; namespace
MyWeb { public
partial class Add : System.Web.UI.Page { protected
void Page_Load( object
sender, EventArgs e) { } //注册 protected
void btnAdd_Click( object
sender, EventArgs e) { string
name =Request.Form[ "txtName" ].ToString(); string
pwd = Request.Form[ "txtpwd" ].ToString(); int
age=20; string
sex= "男" ; Model.UserInfoModel us = new
Model.UserInfoModel(); us.username = name; us.userpwd = pwd; us.age = age; //为了方便直接给值了下面同理 上面2个也没获取 us.sex = sex; us.emel = emel; us.adddate = DateTime.Now; //获取当前时间 int
str = BLL.UserInfoBLL.getAdd(us); if
(str>0) { Response.Write( "~~~OK" ); } else
if (str == -1) { Response.Write( "该用户已存在!!" ); } else { Response.Write( "~~~注册失败!!" ); } } } } |
6.最后贴上存储过程
alter PROCEDURE UserInfo_Add ( @UserName varchar (50), @UserPwd varchar (50), @age int , @sex varchar (50), @emel varchar (200), @adddate datetime, @userid int
output --返回参数验证是否已存在 ) as IF EXISTS( SELECT
* FROM UserInfo WHERE
UserName=@username) --先判断用户是否存在 begin SELECT
@userid=-1 --如果存在则返回-1 end ELSE begin INSERT
INTO UserInfo(UserName,UserPwd,age,sex,emel,adddate) VALUES
(@UserName,@UserPwd,@age,@sex,@emel,@adddate); SELECT
@userid =SCOPE_IDENTITY() --获取刚注册分配的用户id FROM
UserInfo end |
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。