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

 

转载于:https://www.cnblogs.com/yangxinghua/p/3681670.html