模型层
private int userId;
public int UserId
{
get { return userId; }
set { userId = value; }
}
private string userName;
public string UserName
{
get { return userName; }
set { userName = value; }
}
private string passWord;
public string PassWord
{
get { return passWord; }
set { passWord = value; }
}
数据层
public class UserService
{
public List<User> GetAllUserDynamic(string whereCondition)
{
List<User> users=new List<User>();
string strSQL = "spSelect";
SqlParameter[] parameter = new SqlParameter[]
{
new SqlParameter("@TableCondition","User"),
new SqlParameter("@WhereCondition",whereCondition),
new SqlParameter("@OrderCondition",null)
};
using (SqlDataReader dataReader=SqlHelper.GetDataReader(strSQL,parameter))
{
while (dataReader.Read())
{
User user = new User();
user.UserId = (int)dataReader["UserId"];
user.UserName = dataReader["UserName"].ToString();
user.PassWord = dataReader["PassWord"].ToString();
// user.TypeId = (int)dataReader["TypeId"];
users.Add(user);
}
}
return users;
}
public object AddUser(User user)
{
string strSQL = "spInsert";
string valueCondition = string.Format("'{0}','{1}',{2}",user.UserName,user.PassWord,user.TypeId);
SqlParameter[] parameter = new SqlParameter[]
{
new SqlParameter("@TableCondition","User"),
new SqlParameter("@ValueCondition",valueCondition)
};
return SqlHelper.ExecuteSalar(strSQL, parameter);
}
public int UpdateUser(User user)
{
string strSQL = "spUpdate";
string setCondition = string.Format("[UserName]='{0}',[PassWord]='{1}',[TypeId]={2}", user.UserName, user.PassWord, user.TypeId);
string whereCondition = string.Format("[UserId]={0}",user.UserId);
SqlParameter[] parameter = new SqlParameter[]
{
new SqlParameter("@TableCondition","User"),
new SqlParameter("@SetCondition",setCondition),
new SqlParameter("@WhereCondition",whereCondition)
};
return SqlHelper.ExecuteCommand(strSQL, parameter);
}
public int DeleteUser(User user)
{
string strSQL = "spDelete";
string whereCondition = string.Format("[UserId]={0}", user.UserId);
SqlParameter[] parameter = new SqlParameter[]
{
new SqlParameter("@TableCondition","User"),
new SqlParameter("@WhereCondition",whereCondition)
};
return SqlHelper.ExecuteCommand(strSQL, parameter);
}
}
业务层
public class UserManager
{
UserService userService=new UserService();
[DataObjectMethod(DataObjectMethodType.Select)]
public List<User> GetAllUser()
{
return userService.GetAllUserDynamic(null);
}
[DataObjectMethod(DataObjectMethodType.Select)]
public User GetAllUserByUserId(int userId)
{
string whereCondition = string.Format("[UserId]={0}",userId);
List<User> users = userService.GetAllUserDynamic(whereCondition);
return users.Count > 0 ? users[0] : null;
}
[DataObjectMethod(DataObjectMethodType.Insert)]
public object AddUser(User user)
{
return userService.AddUser(user);
}
[DataObjectMethod(DataObjectMethodType.Update)]
public int UpdateUser(User user)
{
return userService.UpdateUser(user);
}
[DataObjectMethod(DataObjectMethodType.Delete)]
public int DeleteUser(User user)
{
return userService.DeleteUser(user);
}
[DataObjectMethod(DataObjectMethodType.Select)]
public User LoginUser(object userName,object passWord)
{
string whereCondition = string.Format("[UserName]='{0}'AND [PassWord]='{1}'",userName,passWord);
List<User> users = userService.GetAllUserDynamic(whereCondition);
return users.Count > 0 ? users[0] : null;
}
}
表示层 随便拖个拖个gridview 绑定下数据源
我用的通用存储过程
CREATE PROCEDURE dbo.spSelect
(
@TableCondition nvarchar(255),
@WhereCondition nvarchar(255)=null,
@OrderCondition nvarchar(255)=null
)
AS
DECLARE @SQL NVARCHAR(255)
SET @SQL='SELECT * FROM['+@TableCondition+']'
IF @WhereCondition IS NOT NULL AND LEN (@WhereCondition)>0
BEGIN
SET @SQL=@SQL+'WHERE'+@WhereCondition
END
IF @OrderCondition IS NOT NULL AND LEN (@OrderCondition)>0
BEGIN
SET @SQL=@SQL+'ORDER BY'+@OrderCondition
END
EXEC sp_executesql @SQL
RETURN
GO
CREATE PROCEDURE dbo.spInsert
(
@TableCondition nvarchar(255),
@ValueCondition nvarchar(255)=null
)
AS
DECLARE @SQL NVARCHAR(255)
SET @SQL='INSERT INTO['+@TableCondition+']'
IF @ValueCondition IS NOT NULL AND LEN (@ValueCondition)>0
BEGIN
SET @SQL=@SQL+'VALUES('+@ValueCondition+')'
END
SET @SQL=@SQL+'SELECT SCOPE_IDENTITY()'
EXEC sp_executesql @sql
RETURN
GO
CREATE PROCEDURE dbo.spUpdate
(
@TableCondition nvarchar(255),
@SetCondition nvarchar(255)=null,
@WhereCondition nvarchar(255)=null
)
AS
DECLARE @SQL NVARCHAR(255)
SET @SQL='UPDATE ['+@TableCondition+']'
IF @SetCondition IS NOT NULL AND LEN (@SetCondition)>0
BEGIN
SET @SQL=@SQL+'SET'+@SetCondition
END
IF @WhereCondition IS NOT NULL AND LEN (@WhereCondition)>0
BEGIN
SET @SQL=@SQL+'WHERE'+@WhereCondition
END
EXEC sp_executesql @sql
RETURN
GO
CREATE PROCEDURE dbo.spDelete
(
@TableCondition nvarchar(255),
@WhereCondition nvarchar(255)=null
)
AS
DECLARE @SQL NVARCHAR(255)
SET @SQL='DELETE FROM ['+@TableCondition+']'
IF @WhereCondition IS NOT NULL AND LEN (@WhereCondition)>0
BEGIN
SET @SQL=@SQL+'WHERE'+@WhereCondition
END
EXEC sp_executesql @sql
RETURN