sqldataprovider.cs

其主要的功能就是,进行后台管理模块整合. 1)其可以支持多个应用,多个模块的权限管理. 2)支持Access和MsSql200/2005. 3)采用角色来进行权限的授权,每个用户可以属于多个角色

        public override int sys_GroupInsertUpdate(sys_GroupTable fam)
        {
            int rInt = 0;
            using (SqlConnection Conn = GetSqlConnection())
            {
                SqlCommand cmd = new SqlCommand("sys_GroupInsertUpdateDelete", Conn);
                cmd.CommandType = CommandType.StoredProcedure;
                //设置参数
                cmd.Parameters.Add("@DB_Option_Action_", SqlDbType.NVarChar).Value = fam.DB_Option_Action_; //操作方法 Insert:增加 Update:修改 Delete:删除 Disp:显示单笔记录
                cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = fam.GroupID;  //分类ID号
                cmd.Parameters.Add("@G_CName", SqlDbType.NVarChar).Value = fam.G_CName;  //分类中文说明
                cmd.Parameters.Add("@G_ParentID", SqlDbType.Int).Value = fam.G_ParentID;  //上级分类ID0:为最高级
                cmd.Parameters.Add("@G_ShowOrder", SqlDbType.Int).Value = fam.G_ShowOrder;  //显示顺序
                cmd.Parameters.Add("@G_Level", SqlDbType.Int).Value = fam.G_Level;  //当前分类所在层数
                cmd.Parameters.Add("@G_ChildCount", SqlDbType.Int).Value = fam.G_ChildCount;  //当前分类子分类数
                cmd.Parameters.Add("@G_Delete", SqlDbType.Int).Value = fam.G_Delete;  //是否删除1:是0:否
                Conn.Open();
                rInt = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Dispose();
                Conn.Dispose();
                Conn.Close();
            }
            return rInt;
        }

        /// <summary>
        /// 返回sys_GroupTable实体类的ArrayList对象
        /// </summary>
        /// <param name="qp">查询类</param>
        /// <param name="RecordCount">返回记录总数</param>
        /// <returns>sys_GroupTable实体类的ArrayList对象</returns>
        public override ArrayList sys_GroupList(QueryParam qp, out int RecordCount)
        {
            PopulateDelegate mypd = new PopulateDelegate(base.Populatesys_Group);
            return this.GetObjectList(mypd, qp, out RecordCount);
        }

        #endregion		

        #region "sys_Module - SQLDataProvider"
        /// <summary>
        /// 新增/删除/修改 sys_Module
        /// </summary>
        /// <param name="fam">sys_ModuleTable实体类</param>
        /// <returns>返回0操正常</returns>
        public override int sys_ModuleInsertUpdate(sys_ModuleTable fam)
        {
            int rInt = 0;
            using (SqlConnection Conn = GetSqlConnection())
            {
                SqlCommand cmd = new SqlCommand("sys_ModuleInsertUpdateDelete", Conn);
                cmd.CommandType = CommandType.StoredProcedure;
                //设置参数
                cmd.Parameters.Add("@DB_Option_Action_", SqlDbType.NVarChar).Value = fam.DB_Option_Action_; //操作方法 Insert:增加 Update:修改 Delete:删除 Disp:显示单笔记录
                cmd.Parameters.Add("@ModuleID", SqlDbType.Int).Value = fam.ModuleID;  //功能模块ID号
                cmd.Parameters.Add("@M_ApplicationID", SqlDbType.Int).Value = fam.M_ApplicationID;  //所属应用程序ID
                cmd.Parameters.Add("@M_ParentID", SqlDbType.Int).Value = fam.M_ParentID;  //所属父级模块ID与ModuleID关联,0为顶级	
                cmd.Parameters.Add("@M_PageCode", SqlDbType.VarChar).Value = fam.M_PageCode;  //模块编码Parent为0,则为S00(xx),否则为S00M00(xx)
                cmd.Parameters.Add("@M_CName", SqlDbType.NVarChar).Value = fam.M_CName;  //模块/栏目名称当ParentID为0为模块名称
                cmd.Parameters.Add("@M_Directory", SqlDbType.NVarChar).Value = fam.M_Directory;  //模块/栏目目录名	
                cmd.Parameters.Add("@M_OrderLevel", SqlDbType.VarChar).Value = fam.M_OrderLevel;  //当前所在排序级别支持双层99级菜单
                cmd.Parameters.Add("@M_IsSystem", SqlDbType.Int).Value = fam.M_IsSystem;  //是否为系统模块1:是0:否如为系统则无法修改
                cmd.Parameters.Add("@M_Close", SqlDbType.Int).Value = fam.M_Close;  //是否关闭1:是0:否
                Conn.Open();
                rInt = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Dispose();
                Conn.Dispose();
                Conn.Close();
            }
            return rInt;
        }

        /// <summary>
        /// 返回sys_ModuleTable实体类的ArrayList对象
        /// </summary>
        /// <param name="qp">查询类</param>
        /// <param name="RecordCount">返回记录总数</param>
        /// <returns>sys_ModuleTable实体类的ArrayList对象</returns>
        public override ArrayList sys_ModuleList(QueryParam qp, out int RecordCount)
        {
            PopulateDelegate mypd = new PopulateDelegate(base.Populatesys_Module);
            return this.GetObjectList(mypd, qp, out RecordCount);
        }

        #endregion		

        #region "sys_RolePermission - SQLDataProvider"
        /// <summary>
        /// 新增/删除/修改 sys_RolePermission
        /// </summary>
        /// <param name="fam">sys_RolePermissionTable实体类</param>
        /// <returns>返回0操正常</returns>
        public override int sys_RolePermissionInsertUpdate(sys_RolePermissionTable fam)
        {
            int rInt = 0;
            using (SqlConnection Conn = GetSqlConnection())
            {
                SqlCommand cmd = new SqlCommand("sys_RolePermissionInsertUpdateDelete", Conn);
                cmd.CommandType = CommandType.StoredProcedure;
                //设置参数
                cmd.Parameters.Add("@DB_Option_Action_", SqlDbType.NVarChar).Value = fam.DB_Option_Action_; //操作方法 Insert:增加 Update:修改 Delete:删除 Disp:显示单笔记录
                cmd.Parameters.Add("@PermissionID", SqlDbType.Int).Value = fam.PermissionID;  //角色应用权限自动ID
                cmd.Parameters.Add("@P_RoleID", SqlDbType.Int).Value = fam.P_RoleID;  //角色ID与sys_Roles表中RoleID相
                cmd.Parameters.Add("@P_ApplicationID", SqlDbType.Int).Value = fam.P_ApplicationID;  //角色所属应用ID与sys_Applicatio	
                cmd.Parameters.Add("@P_PageCode", SqlDbType.VarChar).Value = fam.P_PageCode;  //角色应用中页面权限代码
                cmd.Parameters.Add("@P_Value", SqlDbType.Int).Value = fam.P_Value;  //权限值
                Conn.Open();
                rInt = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Dispose();
                Conn.Dispose();
                Conn.Close();
            }
            return rInt;
        }

        /// <summary>
        /// 返回sys_RolePermissionTable实体类的ArrayList对象
        /// </summary>
        /// <param name="qp">查询类</param>
        /// <param name="RecordCount">返回记录总数</param>
        /// <returns>sys_RolePermissionTable实体类的ArrayList对象</returns>
        public override ArrayList sys_RolePermissionList(QueryParam qp, out int RecordCount)
        {
            PopulateDelegate mypd = new PopulateDelegate(base.Populatesys_RolePermission);
            return this.GetObjectList(mypd, qp, out RecordCount);
        }

        #endregion		

        #region "sys_Roles - SQLDataProvider"
        /// <summary>
        /// 新增/删除/修改 sys_Roles
        /// </summary>
        /// <param name="fam">sys_RolesTable实体类</param>
        /// <returns>返回0操正常</returns>
        public override int sys_RolesInsertUpdate(sys_RolesTable fam)
        {
            int rInt = 0;
            using (SqlConnection Conn = GetSqlConnection())
            {
                SqlCommand cmd = new SqlCommand("sys_RolesInsertUpdateDelete", Conn);
                cmd.CommandType = CommandType.StoredProcedure;
                //设置参数
                cmd.Parameters.Add("@DB_Option_Action_", SqlDbType.NVarChar).Value = fam.DB_Option_Action_; //操作方法 Insert:增加 Update:修改 Delete:删除 Disp:显示单笔记录
                cmd.Parameters.Add("@RoleID", SqlDbType.Int).Value = fam.RoleID;  //角色ID自动ID
                cmd.Parameters.Add("@R_RoleName", SqlDbType.NVarChar).Value = fam.R_RoleName;  //角色名称
                cmd.Parameters.Add("@R_Description", SqlDbType.NVarChar).Value = fam.R_Description;  //角色介绍
                Conn.Open();
                rInt = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Dispose();
                Conn.Dispose();
                Conn.Close();
            }
            return rInt;
        }

        /// <summary>
        /// 返回sys_RolesTable实体类的ArrayList对象
        /// </summary>
        /// <param name="qp">查询类</param>
        /// <param name="RecordCount">返回记录总数</param>
        /// <returns>sys_RolesTable实体类的ArrayList对象</returns>
        public override ArrayList sys_RolesList(QueryParam qp, out int RecordCount)
        {
            PopulateDelegate mypd = new PopulateDelegate(base.Populatesys_Roles);
            return this.GetObjectList(mypd, qp, out RecordCount);
        }

        #endregion		

        #region "sys_User - SQLDataProvider"
        /// <summary>
        /// 新增/删除/修改 sys_User
        /// </summary>
        /// <param name="fam">sys_UserTable实体类</param>
        /// <returns>返回0操正常</returns>
        public override int sys_UserInsertUpdate(sys_UserTable fam)
        {
            int rInt = 0;
            using (SqlConnection Conn = GetSqlConnection())
            {
                SqlCommand cmd = new SqlCommand("sys_UserInsertUpdateDelete", Conn);
                cmd.CommandType = CommandType.StoredProcedure;
                //设置参数
                cmd.Parameters.Add("@DB_Option_Action_", SqlDbType.NVarChar).Value = fam.DB_Option_Action_; //操作方法 Insert:增加 Update:修改 Delete:删除 Disp:显示单笔记录
                cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = fam.UserID;  //用户ID号
                cmd.Parameters.Add("@U_LoginName", SqlDbType.NVarChar).Value = fam.U_LoginName;  //登陆名	
                cmd.Parameters.Add("@U_Password", SqlDbType.VarChar).Value = fam.U_Password;  //密码md5加密字符
                cmd.Parameters.Add("@U_CName", SqlDbType.NVarChar).Value = fam.U_CName;  //中文姓名	
                cmd.Parameters.Add("@U_EName", SqlDbType.VarChar).Value = fam.U_EName;  //英文名
                cmd.Parameters.Add("@U_GroupID", SqlDbType.Int).Value = fam.U_GroupID;  //部门ID号与sys_Group表中GroupID关联	
                cmd.Parameters.Add("@U_Email", SqlDbType.VarChar).Value = fam.U_Email;  //电子邮件
                cmd.Parameters.Add("@U_Type", SqlDbType.Int).Value = fam.U_Type;  //用户类型0:超级用户1:普通用户
                cmd.Parameters.Add("@U_Status", SqlDbType.Int).Value = fam.U_Status;  //当前状态0:正常 1:禁止	
                cmd.Parameters.Add("@U_Licence", SqlDbType.VarChar).Value = fam.U_Licence;  //用户序列号	
                cmd.Parameters.Add("@U_Mac", SqlDbType.VarChar).Value = fam.U_Mac;  //锁定机器硬件地址
                cmd.Parameters.Add("@U_Remark", SqlDbType.NVarChar).Value = fam.U_Remark;  //备注说明	
                cmd.Parameters.Add("@U_IDCard", SqlDbType.VarChar).Value = fam.U_IDCard;  //身份证号码
                cmd.Parameters.Add("@U_Sex", SqlDbType.Int).Value = fam.U_Sex;  //性别1:男0:女
                cmd.Parameters.Add("@U_BirthDay", SqlDbType.DateTime).Value = fam.U_BirthDay;  //出生日期	
                cmd.Parameters.Add("@U_MobileNo", SqlDbType.VarChar).Value = fam.U_MobileNo;  //手机号	
                cmd.Parameters.Add("@U_UserNO", SqlDbType.VarChar).Value = fam.U_UserNO;  //员工编号
                cmd.Parameters.Add("@U_WorkStartDate", SqlDbType.DateTime).Value = fam.U_WorkStartDate;  //到职日期
                cmd.Parameters.Add("@U_WorkEndDate", SqlDbType.DateTime).Value = fam.U_WorkEndDate;  //离职日期	
                cmd.Parameters.Add("@U_CompanyMail", SqlDbType.VarChar).Value = fam.U_CompanyMail;  //公司邮件地址
                cmd.Parameters.Add("@U_Title", SqlDbType.Int).Value = fam.U_Title;  //职称与应用字段关联	
                cmd.Parameters.Add("@U_Extension", SqlDbType.VarChar).Value = fam.U_Extension;  //分机号	
                cmd.Parameters.Add("@U_HomeTel", SqlDbType.VarChar).Value = fam.U_HomeTel;  //家中电话
                cmd.Parameters.Add("@U_PhotoUrl", SqlDbType.NVarChar).Value = fam.U_PhotoUrl;  //用户照片网址
                cmd.Parameters.Add("@U_DateTime", SqlDbType.DateTime).Value = fam.U_DateTime;  //操作时间	
                cmd.Parameters.Add("@U_LastIP", SqlDbType.VarChar).Value = fam.U_LastIP;  //最后访问IP
                cmd.Parameters.Add("@U_LastDateTime", SqlDbType.DateTime).Value = fam.U_LastDateTime;  //最后访问时间	
                cmd.Parameters.Add("@U_ExtendField", SqlDbType.NText).Value = fam.U_ExtendField;  //扩展字段
                Conn.Open();
                rInt = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Dispose();
                Conn.Dispose();
                Conn.Close();
            }
            return rInt;
        }

        /// <summary>
        /// 返回sys_UserTable实体类的ArrayList对象
        /// </summary>
        /// <param name="qp">查询类</param>
        /// <param name="RecordCount">返回记录总数</param>
        /// <returns>sys_UserTable实体类的ArrayList对象</returns>
        public override ArrayList sys_UserList(QueryParam qp, out int RecordCount)
        {
            PopulateDelegate mypd = new PopulateDelegate(base.Populatesys_User);
            return this.GetObjectList(mypd, qp, out RecordCount);
        }

        #endregion	

        #region "sys_RoleApplication - SQLDataProvider"
        /// <summary>
        /// 新增/删除/修改 sys_RoleApplication
        /// </summary>
        /// <param name="fam">sys_RoleApplicationTable实体类</param>
        /// <returns>返回0操正常</returns>
        public override int sys_RoleApplicationInsertUpdate(sys_RoleApplicationTable fam)
        {
            int rInt = 0;
            using (SqlConnection Conn = GetSqlConnection())
            {
                SqlCommand cmd = new SqlCommand("sys_RoleApplicationInsertUpdateDelete", Conn);
                cmd.CommandType = CommandType.StoredProcedure;
                //设置参数
                cmd.Parameters.Add("@DB_Option_Action_", SqlDbType.NVarChar).Value = fam.DB_Option_Action_; //操作方法 Insert:增加 Update:修改 Delete:删除 Disp:显示单笔记录
                cmd.Parameters.Add("@A_RoleID", SqlDbType.Int).Value = fam.A_RoleID;  //角色ID与sys_Roles中RoleID相关
                cmd.Parameters.Add("@A_ApplicationID", SqlDbType.Int).Value = fam.A_ApplicationID;  //应用ID与sys_Applications中Appl
                Conn.Open();
                rInt = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Dispose();
                Conn.Dispose();
                Conn.Close();
            }