PDF.NET框架学习篇之SQL-MAP使用存储过程

最近一直在学习“深蓝医生”的PDF.NET框架,对Sql-Map使用存储过程有了点小小的体会。基础知识请到http://www.cnblogs.com/bluedoctor/archive/2010/07/03/1769890.html中查看。

本文通过一个简单的例子以说明通过Sql-Map使用存储过程,并返回一个实体类。

功能实现:通过存储过程验证登录用户的合法性。

1.创建数据库表:

//系统用户维护表
CREATE
TABLE T_USER ( [USER_ID] [int] PRIMARY KEY, [LOGIN_NAME] [nvarchar](50) NULL, [REAL_NAME] [nvarchar](50) NULL, [PASSWORD] [nvarchar](50) NULL, [USER_STATE] [int] NULL,  --0:未激活  1:激活 [REMARK] [nvarchar](200) NULL )

2.创建用户验证存储过程:

//功能:验证用户的合法性。如果验证通过则返回当前登录用户的全部信息
CREATE
PROCEDURE up_user_verify ( @login_name nvarchar(50), @password nvarchar(50), @error nvarchar(500) output  --如果验证未通过,输出错误提示信息。 ) AS declare @user_id int, @pwd nvarchar(50), @user_state int   --判断是否存在用户,此处对用户的登录名和登录密码进行大小写区分 select @user_id = [user_id], @pwd = [password], @user_state = [user_state]
  from t_user
  where cast([login_name] as varbinary) = cast(@login_name as varbinary)
--登录名验证 if @user_id is null begin set @error = '用户登录名:'+@login_name+'不存在' return null end --登录密码验证 if cast(@password as varbinary) != cast(@pwd as varbinary) begin set @error = '登录密码不正确,请重新输入' return null end --用户状态验证 if @user_state = 0 begin set @error = '用户:' +@login_name+'未激活' return null end
  --通过验证,返回用户的信息
select * from t_user where [login_name] = @login_name

3.生成表T_USER对应的实体类:

  [Serializable()]
    public partial class Tb_User : EntityBase
    {
        public Tb_User()
        {
            TableName = "T_USER";
            EntityMap=EntityMapType.Table;
            PrimaryKeys.Add("USER_ID");
        }
        protected override void SetFieldNames()
        {
            PropertyNames = new string[] { "USER_ID","LOGIN_NAME","REAL_NAME","PASSWORD","USER_STATE","REMARK" };
        }
        /// <summary>
        /// 用户ID
        /// </summary>
        public System.Int32 USER_ID
        {
            get{return getProperty<System.Int32>("USER_ID");}
            set{setProperty("USER_ID",value );}
        }
        /// <summary>
        /// 登录名称
        /// </summary>
        public System.String LOGIN_NAME
        {
            get{return getProperty<System.String>("LOGIN_NAME");}
            set{setProperty("LOGIN_NAME",value ,50);}
        }
        /// <summary>
        /// 真实姓名
        /// </summary>
        public System.String REAL_NAME
        {
            get{return getProperty<System.String>("REAL_NAME");}
            set{setProperty("REAL_NAME",value ,50);}
        }
        /// <summary>
        /// 登录密码
        /// </summary>
        public System.String PASSWORD
        {
            get{return getProperty<System.String>("PASSWORD");}
            set{setProperty("PASSWORD",value ,50);}
        }
        /// <summary>
        /// 用户状态
        /// </summary>
        public System.String USER_STATE
        {
            get{return getProperty<System.String>("USER_STATE");}
            set{setProperty("USER_STATE",value ,50);}
        }
        /// <summary>
        /// 备注信息
        /// </summary>
        public System.String REMARK
        {
            get{return getProperty<System.String>("REMARK");}
            set{setProperty("REMARK",value ,200);}
        }
    }

4.Sql-Map配置脚本:

<CommandClass Name="UserManage" Class="UserInfoManageDAL" Description="系统用户信息维护" Interface="">
   <Select CommandName="VerifyUser" CommandType="StoredProcedure" Method="" Description="验证用户的合法性" 
ResultClass
="EntityObject" ResultMap="DAL.Entitys.Tb_User"> <![CDATA[[up_user_verify] #login_name:String,String,50,Input# #password:String,String,50,Input# #error:String,String,500,Output#]]> </Select>
</CommandClass>

5.SQL-MAP的DAL程序:

public partial class UserInfoManageDAL : DBMapper 
    {
        /// <summary>
        /// 默认构造函数
        /// </summary>
        public UserInfoManageDAL()
        {
            Mapper.CommandClassName = "UserManage";
            Mapper.EmbedAssemblySource = "DAL,DAL.SqlMap.config";
        }/// <summary>
        /// 用户合法性验证
        /// </summary>
        /// <param name="loginName">登录名</param>
        /// <param name="password">登录密码</param>
        /// <param name="error">验证失败的错误信息</param>
        /// <returns>验证通过,返回用户实体类实例</returns>
        public Tb_User VerifyUser(string loginName, string password, ref string error)
        {
            CommandInfo cmdInfo = Mapper.GetCommandInfo("VerifyUser");
            cmdInfo.DataParameters[0].Value = loginName;
            cmdInfo.DataParameters[1].Value = password;
            cmdInfo.DataParameters[2].Value = error;
            Tb_User user = EntityQuery<Tb_User>.QueryObject(CurrentDataBase.ExecuteDataReader(cmdInfo.CommandText, cmdInfo.CommandType, cmdInfo.DataParameters));        
            error = user == null ? cmdInfo.DataParameters[2].Value.ToString() : string.Empty;
            return user;
        }
        /// <summary>
        /// 获取全部用户信息
        /// </summary>
        /// <returns>用户实体列表</returns>
        public List<Tb_User> GetAllUser()
        {
            return OQL.From<Tb_User>().Select().END.ToList<Tb_User>();
        }
        /// <summary>
        /// 通过用户ID获取用户信息
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <returns></returns>
        public Tb_User GetUserById(int userId)
        {
            Tb_User user = new Tb_User();
            user.USER_ID = userId;
            OQL q = OQL.From(user).Select().Where(user.USER_ID).END;
            return EntityQuery<Tb_User>.QueryObject(q);
        }
    }

6.测试程序

private void button1_Click(object sender, EventArgs e)
{
  UserInfoManageDAL dal = new UserInfoManageDAL();
  string name = "admin";
  string pwd = "abc";
  string error = string.Empty;
  Tb_User user = dal.VerifyUser(name, pwd, ref error);
  if (user == null)
    MessageBox.Show(error);
  else
    MessageBox.Show(user.USER_ID.ToString() + ":" user.LOGIN_NAME + ":" + user.REAL_NAME); 
}

 

posted @ 2013-11-24 20:35  jack_mjl  阅读(361)  评论(2编辑  收藏  举报