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="男"; string emel="sqlcomm@163.com"; 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