三层搭建就不说了:
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