accessdataprovider.cs

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

/************************************************************************************
 *      Copyright (C) 2008 supesoft.com,All Rights Reserved						    *
 *      File:																		*
 *				AccessDataProvider.cs                     	                		*
 *      Description:																*
 *				 Access数据库访问类    	   							        	    *
 *      Author:																		*
 *				Lzppcc														        *
 *				[e-mail]													*
 *				http://www.supesoft.com												*
 *      Finish DateTime:															*
 *				2007年8月6日														*
 *      History:																	*
 ***********************************************************************************/
using System;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Text;

using FrameWork.Components;


namespace FrameWork.Data
{
    /// <summary>
    /// Access数据库访问类
    /// </summary>
    public class AccessDataProvider : DataProvider
    {
        #region "AccessDataProvider"
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private string ConnString = string.Empty;
        /// <summary>
        /// 构造函数
        /// </summary>
        public AccessDataProvider()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
            ConnString = string.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source={0}{1};Persist Security Info=True;", AppDomain.CurrentDomain.BaseDirectory, Common.GetConnString);

            
        }
        /// <summary>
        /// 获取数据连接
        /// </summary>
        /// <returns></returns>
        private OleDbConnection GetSqlConnection()
        {
            try
            {
                return new OleDbConnection(ConnString);
            }
            catch
            {
                throw new Exception("没有提供数据庫连接字符串Access！");
            }
        }
        #endregion
        
        #region "sys_Roles - AccessDataProvider"
        /// <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 (OleDbConnection Conn = GetSqlConnection())
            {
                string CommTxt;
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = Conn;
                if (fam.DB_Option_Action_ == "Insert")
                {
                    CommTxt = "Insert into 	sys_Roles(R_RoleName,R_Description)VALUES(@R_RoleName,@R_Description)";
                    cmd.CommandText = CommTxt;

                    cmd.Parameters.Add("@R_RoleName", OleDbType.VarWChar).Value = fam.R_RoleName + ""; //角色名称
                    cmd.Parameters.Add("@R_Description", OleDbType.VarWChar).Value = fam.R_Description + ""; //角色介绍
                }
                else if (fam.DB_Option_Action_ == "Update")
                {

                    CommTxt = "UPDATE sys_Roles SET	R_RoleName = @R_RoleName,R_Description = @R_Description WHERE (RoleID = @RoleID)";
                    cmd.CommandText = CommTxt;

                    cmd.Parameters.Add("@R_RoleName", OleDbType.VarWChar).Value = fam.R_RoleName + ""; //角色名称
                    cmd.Parameters.Add("@R_Description", OleDbType.VarWChar).Value = fam.R_Description + ""; //角色介绍
                    cmd.Parameters.Add("@RoleID", OleDbType.Integer).Value = fam.RoleID;
                }
                else if (fam.DB_Option_Action_ == "Delete")
                {
                    CommTxt = "Delete from  sys_Roles  WHERE (RoleID = @RoleID)";
                    cmd.CommandText = CommTxt;
                    cmd.Parameters.Add("@RoleID", OleDbType.Integer).Value = fam.RoleID;
                }
                else
                    throw new ApplicationException("无法识别的操作命令!");
                Conn.Open();
                rInt = cmd.ExecuteNonQuery();
                if (fam.DB_Option_Action_ == "Insert")
                {
                    cmd.CommandText = "SELECT @@identity";
                    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 - AccessDataProvider"
        /// <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 (OleDbConnection Conn = GetSqlConnection())
            {
                string CommTxt;
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = Conn;
                if (fam.DB_Option_Action_ == "Insert")
                {
                    CommTxt = "Insert into 	sys_User(U_LoginName,U_Password,U_CName,U_EName,U_GroupID,U_Email,U_Type,U_Status,U_Licence,U_Mac,U_Remark,U_IDCard,U_Sex,U_BirthDay,U_MobileNo,U_UserNO,U_WorkStartDate,U_WorkEndDate,U_CompanyMail,U_Title,U_Extension,U_HomeTel,U_PhotoUrl,U_DateTime,U_LastIP,U_LastDateTime,U_ExtendField)VALUES(@U_LoginName,@U_Password,@U_CName,@U_EName,@U_GroupID,@U_Email,@U_Type,@U_Status,@U_Licence,@U_Mac,@U_Remark,@U_IDCard,@U_Sex,@U_BirthDay,@U_MobileNo,@U_UserNO,@U_WorkStartDate,@U_WorkEndDate,@U_CompanyMail,@U_Title,@U_Extension,@U_HomeTel,@U_PhotoUrl,@U_DateTime,@U_LastIP,@U_LastDateTime,@U_ExtendField)";
                    cmd.CommandText = CommTxt;

                    cmd.Parameters.Add("@U_LoginName", OleDbType.VarWChar).Value = fam.U_LoginName + ""; //登陆名	
                    cmd.Parameters.Add("@U_Password", OleDbType.VarWChar).Value = fam.U_Password + ""; //密码md5加密字符
                    cmd.Parameters.Add("@U_CName", OleDbType.VarWChar).Value = fam.U_CName + ""; //中文姓名	
                    cmd.Parameters.Add("@U_EName", OleDbType.VarWChar).Value = fam.U_EName + ""; //英文名	
                    cmd.Parameters.Add("@U_GroupID", OleDbType.Integer).Value = fam.U_GroupID; //部门ID号与sys_Group表中GroupID关联	
                    cmd.Parameters.Add("@U_Email", OleDbType.VarWChar).Value = fam.U_Email + ""; //电子邮件
                    cmd.Parameters.Add("@U_Type", OleDbType.LongVarWChar).Value = fam.U_Type + "";  //用户类型0:超级用户1:普通用户
                    cmd.Parameters.Add("@U_Status", OleDbType.LongVarWChar).Value = fam.U_Status + "";  //当前状态0:正常 1:禁止	
                    cmd.Parameters.Add("@U_Licence", OleDbType.VarWChar).Value = fam.U_Licence + ""; //用户序列号	
                    cmd.Parameters.Add("@U_Mac", OleDbType.VarWChar).Value = fam.U_Mac + ""; //锁定机器硬件地址
                    cmd.Parameters.Add("@U_Remark", OleDbType.VarWChar).Value = fam.U_Remark + ""; //备注说明	
                    cmd.Parameters.Add("@U_IDCard", OleDbType.VarWChar).Value = fam.U_IDCard + ""; //身份证号码
                    cmd.Parameters.Add("@U_Sex", OleDbType.LongVarWChar).Value = fam.U_Sex + "";  //性别1:男0:女	
                    cmd.Parameters.Add("@U_BirthDay", OleDbType.Date).Value = fam.U_BirthDay; //出生日期	
                    cmd.Parameters.Add("@U_MobileNo", OleDbType.VarWChar).Value = fam.U_MobileNo + ""; //手机号	
                    cmd.Parameters.Add("@U_UserNO", OleDbType.VarWChar).Value = fam.U_UserNO + ""; //员工编号	
                    cmd.Parameters.Add("@U_WorkStartDate", OleDbType.Date).Value = fam.U_WorkStartDate; //到职日期	
                    cmd.Parameters.Add("@U_WorkEndDate", OleDbType.Date).Value = fam.U_WorkEndDate; //离职日期	
                    cmd.Parameters.Add("@U_CompanyMail", OleDbType.VarWChar).Value = fam.U_CompanyMail + ""; //公司邮件地址	
                    cmd.Parameters.Add("@U_Title", OleDbType.Integer).Value = fam.U_Title; //职称与应用字段关联	
                    cmd.Parameters.Add("@U_Extension", OleDbType.VarWChar).Value = fam.U_Extension + ""; //分机号	
                    cmd.Parameters.Add("@U_HomeTel", OleDbType.VarWChar).Value = fam.U_HomeTel + ""; //家中电话
                    cmd.Parameters.Add("@U_PhotoUrl", OleDbType.VarWChar).Value = fam.U_PhotoUrl + ""; //用户照片网址	
                    cmd.Parameters.Add("@U_DateTime", OleDbType.Date).Value = fam.U_DateTime; //操作时间	
                    cmd.Parameters.Add("@U_LastIP", OleDbType.VarWChar).Value = fam.U_LastIP + ""; //最后访问IP	
                    cmd.Parameters.Add("@U_LastDateTime", OleDbType.Date).Value = fam.U_LastDateTime; //最后访问时间
                    cmd.Parameters.Add("@U_ExtendField", OleDbType.LongVarWChar).Value = fam.U_ExtendField + "";  //扩展字段
                }
                else if (fam.DB_Option_Action_ == "Update")
                {

                    CommTxt = "UPDATE sys_User SET	U_LoginName = @U_LoginName,U_Password = @U_Password,U_CName = @U_CName,U_EName = @U_EName,U_GroupID = @U_GroupID,U_Email = @U_Email,U_Type = @U_Type,U_Status = @U_Status,U_Licence = @U_Licence,U_Mac = @U_Mac,U_Remark = @U_Remark,U_IDCard = @U_IDCard,U_Sex = @U_Sex,U_BirthDay = @U_BirthDay,U_MobileNo = @U_MobileNo,U_UserNO = @U_UserNO,U_WorkStartDate = @U_WorkStartDate,U_WorkEndDate = @U_WorkEndDate,U_CompanyMail = @U_CompanyMail,U_Title = @U_Title,U_Extension = @U_Extension,U_HomeTel = @U_HomeTel,U_PhotoUrl = @U_PhotoUrl,U_DateTime = @U_DateTime,U_LastIP = @U_LastIP,U_LastDateTime = @U_LastDateTime,U_ExtendField = @U_ExtendField WHERE (UserID = @UserID)";
                    cmd.CommandText = CommTxt;

                    cmd.Parameters.Add("@U_LoginName", OleDbType.VarWChar).Value = fam.U_LoginName + ""; //登陆名	
                    cmd.Parameters.Add("@U_Password", OleDbType.VarWChar).Value = fam.U_Password + ""; //密码md5加密字符
                    cmd.Parameters.Add("@U_CName", OleDbType.VarWChar).Value = fam.U_CName + ""; //中文姓名	
                    cmd.Parameters.Add("@U_EName", OleDbType.VarWChar).Value = fam.U_EName + ""; //英文名	
                    cmd.Parameters.Add("@U_GroupID", OleDbType.Integer).Value = fam.U_GroupID; //部门ID号与sys_Group表中GroupID关联	
                    cmd.Parameters.Add("@U_Email", OleDbType.VarWChar).Value = fam.U_Email + ""; //电子邮件
                    cmd.Parameters.Add("@U_Type", OleDbType.LongVarWChar).Value = fam.U_Type + "";  //用户类型0:超级用户1:普通用户
                    cmd.Parameters.Add("@U_Status", OleDbType.LongVarWChar).Value = fam.U_Status + "";  //当前状态0:正常 1:禁止	
                    cmd.Parameters.Add("@U_Licence", OleDbType.VarWChar).Value = fam.U_Licence + ""; //用户序列号	
                    cmd.Parameters.Add("@U_Mac", OleDbType.VarWChar).Value = fam.U_Mac + ""; //锁定机器硬件地址
                    cmd.Parameters.Add("@U_Remark", OleDbType.VarWChar).Value = fam.U_Remark + ""; //备注说明	
                    cmd.Parameters.Add("@U_IDCard", OleDbType.VarWChar).Value = fam.U_IDCard + ""; //身份证号码
                    cmd.Parameters.Add("@U_Sex", OleDbType.LongVarWChar).Value = fam.U_Sex + "";  //性别1:男0:女	
                    cmd.Parameters.Add("@U_BirthDay", OleDbType.Date).Value = fam.U_BirthDay; //出生日期	
                    cmd.Parameters.Add("@U_MobileNo", OleDbType.VarWChar).Value = fam.U_MobileNo + ""; //手机号	
                    cmd.Parameters.Add("@U_UserNO", OleDbType.VarWChar).Value = fam.U_UserNO + ""; //员工编号	
                    cmd.Parameters.Add("@U_WorkStartDate", OleDbType.Date).Value = fam.U_WorkStartDate; //到职日期	
                    cmd.Parameters.Add("@U_WorkEndDate", OleDbType.Date).Value = fam.U_WorkEndDate; //离职日期	
                    cmd.Parameters.Add("@U_CompanyMail", OleDbType.VarWChar).Value = fam.U_CompanyMail + ""; //公司邮件地址	
                    cmd.Parameters.Add("@U_Title", OleDbType.Integer).Value = fam.U_Title; //职称与应用字段关联	
                    cmd.Parameters.Add("@U_Extension", OleDbType.VarWChar).Value = fam.U_Extension + ""; //分机号	
                    cmd.Parameters.Add("@U_HomeTel", OleDbType.VarWChar).Value = fam.U_HomeTel + ""; //家中电话
                    cmd.Parameters.Add("@U_PhotoUrl", OleDbType.VarWChar).Value = fam.U_PhotoUrl + ""; //用户照片网址	
                    cmd.Parameters.Add("@U_DateTime", OleDbType.Date).Value = fam.U_DateTime; //操作时间	
                    cmd.Parameters.Add("@U_LastIP", OleDbType.VarWChar).Value = fam.U_LastIP + ""; //最后访问IP	
                    cmd.Parameters.Add("@U_LastDateTime", OleDbType.Date).Value = fam.U_LastDateTime; //最后访问时间
                    cmd.Parameters.Add("@U_ExtendField", OleDbType.LongVarWChar).Value = fam.U_ExtendField + "";  //扩展字段
                    cmd.Parameters.Add("@UserID", OleDbType.Integer).Value = fam.UserID;
                }
                else if (fam.DB_Option_Action_ == "Delete")
                {
                    CommTxt = "Delete from  sys_User  WHERE (UserID = @UserID)";
                    cmd.CommandText = CommTxt;
                    cmd.Parameters.Add("@UserID", OleDbType.Integer).Value = fam.UserID;
                }
                else
                    throw new ApplicationException("无法识别的操作命令!");
                Conn.Open();
                rInt = cmd.ExecuteNonQuery();
                if (fam.DB_Option_Action_ == "Insert")
                {
                    cmd.CommandText = "SELECT @@identity";
                    rInt = Convert.ToInt32(cmd.ExecuteScalar());
                }
                cmd.Dispose();
                Conn.Dispose();
                Conn.Close();
            }
            return rInt;
        }