oracledataprovider.cs

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

        /// 新增/删除/修改 sys_UserRoles
        /// </summary>
        /// <param name="fam">sys_UserRolesTable实体类</param>
        /// <returns>返回0操正常</returns>
        public override int sys_UserRolesInsertUpdate(sys_UserRolesTable fam)
        {
            int rInt = 0;

            using (OracleConnection Conn = GetSqlConnection())
            {
                string CommTxt;
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = Conn;
                if (fam.DB_Option_Action_ == "Insert")
                {
                    CommTxt = "Insert into 	sys_UserRoles(R_UserID,R_RoleID)VALUES(:R_UserID,:R_RoleID)";
                    cmd.CommandText = CommTxt;

                    cmd.Parameters.Add("R_UserID", OracleType.Int32).Value = fam.R_UserID; //用户ID与sys_User表中UserID相关	
                    cmd.Parameters.Add("R_RoleID", OracleType.Int32).Value = fam.R_RoleID; //用户所属角色ID与Sys_Roles关联
                }
                else if (fam.DB_Option_Action_ == "Update")
                {

                    CommTxt = "UPDATE sys_UserRoles SET	R_UserID = :R_UserID,R_RoleID = :R_RoleID WHERE (  R_UserID = :R_UserID and R_RoleID = :R_RoleID)";
                    cmd.CommandText = CommTxt;

                    cmd.Parameters.Add("R_UserID", OracleType.Int32).Value = fam.R_UserID; //用户ID与sys_User表中UserID相关	
                    cmd.Parameters.Add("R_RoleID", OracleType.Int32).Value = fam.R_RoleID; //用户所属角色ID与Sys_Roles关联

                }
                else if (fam.DB_Option_Action_ == "Delete")
                {
                    CommTxt = "Delete from  sys_UserRoles  WHERE ( R_UserID = :R_UserID and R_RoleID = :R_RoleID)";
                    cmd.CommandText = CommTxt;
                    cmd.Parameters.Add("R_UserID", OracleType.Int32).Value = fam.R_UserID; //用户ID与sys_User表中UserID相关	
                    cmd.Parameters.Add("R_RoleID", OracleType.Int32).Value = fam.R_RoleID; //用户所属角色ID与Sys_Roles关联
                }
                else
                    throw new ApplicationException("无法识别的操作命令!");
                Conn.Open();
                OracleTransaction Tran = Conn.BeginTransaction();
                cmd.Transaction = Tran;
                try
                {
                    rInt = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    Tran.Commit();
                }
                catch (Exception ex)
                {
                    Tran.Rollback();
                    throw new ApplicationException(ex.ToString());
                }
                finally
                {
                    Tran.Dispose();
                    cmd.Dispose();
                    Conn.Dispose();
                    Conn.Close();
                }
            }
            return rInt;
        }

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

        #endregion

        #region "sys_Applications - OracleDataProvider"
        /// <summary>
        /// 新增/删除/修改 sys_Applications
        /// </summary>
        /// <param name="fam">sys_ApplicationsTable实体类</param>
        /// <returns>返回0操正常</returns>
        public override int sys_ApplicationsInsertUpdate(sys_ApplicationsTable fam)
        {
            int rInt = 0;

            using (OracleConnection Conn = GetSqlConnection())
            {
                string CommTxt;
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = Conn;
                if (fam.DB_Option_Action_ == "Insert")
                {
                    CommTxt = "Insert into 	sys_Applications(ApplicationID,A_AppName,A_AppDescription,A_AppUrl)VALUES(SEQ_sys_Applications_ID.NEXTVAL,:A_AppName,:A_AppDescription,:A_AppUrl)";
                    cmd.CommandText = CommTxt;

                    cmd.Parameters.Add("A_AppName", OracleType.NVarChar).Value = fam.A_AppName + ""; //应用名称
                    cmd.Parameters.Add("A_AppDescription", OracleType.NVarChar).Value = fam.A_AppDescription + ""; //应用介绍	
                    cmd.Parameters.Add("A_AppUrl", OracleType.VarChar).Value = fam.A_AppUrl + ""; //应用Url地址
                }
                else if (fam.DB_Option_Action_ == "Update")
                {

                    CommTxt = "UPDATE sys_Applications SET	A_AppName = :A_AppName,A_AppDescription = :A_AppDescription,A_AppUrl = :A_AppUrl WHERE (ApplicationID = :ApplicationID)";
                    cmd.CommandText = CommTxt;

                    cmd.Parameters.Add("A_AppName", OracleType.NVarChar).Value = fam.A_AppName + ""; //应用名称
                    cmd.Parameters.Add("A_AppDescription", OracleType.NVarChar).Value = fam.A_AppDescription + ""; //应用介绍	
                    cmd.Parameters.Add("A_AppUrl", OracleType.VarChar).Value = fam.A_AppUrl + ""; //应用Url地址
                    cmd.Parameters.Add("ApplicationID", OracleType.Int32).Value = fam.ApplicationID;
                }
                else if (fam.DB_Option_Action_ == "Delete")
                {
                    CommTxt = "Delete from  sys_Applications  WHERE (ApplicationID = :ApplicationID)";
                    cmd.CommandText = CommTxt;
                    cmd.Parameters.Add("ApplicationID", OracleType.Int32).Value = fam.ApplicationID;
                }
                else
                    throw new ApplicationException("无法识别的操作命令!");
                Conn.Open();
                OracleTransaction Tran = Conn.BeginTransaction();
                cmd.Transaction = Tran;
                try
                {
                    rInt = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    if (fam.DB_Option_Action_ == "Insert")
                    {
                        cmd.CommandText = "select SEQ_sys_Applications_ID.CURRVAL from  dual";
                        rInt = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                    Tran.Commit();
                }
                catch (Exception ex)
                {
                    Tran.Rollback();
                    throw new ApplicationException(ex.ToString());
                }
                finally
                {
                    Tran.Dispose();
                    cmd.Dispose();
                    Conn.Dispose();
                    Conn.Close();
                }
            }
            return rInt;
        }

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

        #endregion

        #region "sys_Event - OracleDataProvider"
        /// <summary>
        /// 新增/删除/修改 sys_Event
        /// </summary>
        /// <param name="fam">sys_EventTable实体类</param>
        /// <returns>返回0操正常</returns>
        public override int sys_EventInsertUpdate(sys_EventTable fam)
        {
            int rInt = 0;

            using (OracleConnection Conn = GetSqlConnection())
            {
                string CommTxt;
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = Conn;
                if (fam.DB_Option_Action_ == "Insert")
                {
                    CommTxt = "Insert into 	sys_Event(EventID,E_U_LoginName,E_UserID,E_DateTime,E_ApplicationID,E_A_AppName,E_M_Name,E_M_PageCode,E_From,E_Type,E_IP,E_Record)VALUES(SEQ_sys_Event_ID.NEXTVAL,:E_U_LoginName,:E_UserID,:E_DateTime,:E_ApplicationID,:E_A_AppName,:E_M_Name,:E_M_PageCode,:E_From,:E_Type,:E_IP,:E_Record)";
                    cmd.CommandText = CommTxt;

                    cmd.Parameters.Add("E_U_LoginName", OracleType.NVarChar).Value = fam.E_U_LoginName + ""; //用户名	
                    cmd.Parameters.Add("E_UserID", OracleType.Int32).Value = fam.E_UserID; //操作时用户ID与sys_Users中UserID	
                    cmd.Parameters.Add("E_DateTime", OracleType.DateTime).Value = fam.E_DateTime; //事件发生的日期及时间	
                    cmd.Parameters.Add("E_ApplicationID", OracleType.Int32).Value = fam.E_ApplicationID; //所属应用程序ID与sys_Applicatio
                    cmd.Parameters.Add("E_A_AppName", OracleType.NVarChar).Value = fam.E_A_AppName + ""; //所属应用名称
                    cmd.Parameters.Add("E_M_Name", OracleType.NVarChar).Value = fam.E_M_Name + ""; //PageCode模块名称与sys_Module相同	
                    cmd.Parameters.Add("E_M_PageCode", OracleType.VarChar).Value = fam.E_M_PageCode + ""; //发生事件时模块名称
                    cmd.Parameters.Add("E_From", OracleType.NVarChar).Value = fam.E_From + ""; //来源
                    cmd.Parameters.Add("E_Type", OracleType.SByte).Value = fam.E_Type; //日记类型,1:操作日记2:安全日志3	
                    cmd.Parameters.Add("E_IP", OracleType.VarChar).Value = fam.E_IP + ""; //客户端IP地址
                    cmd.Parameters.Add("E_Record", OracleType.NVarChar).Value = fam.E_Record + ""; //详细描述
                }
                else if (fam.DB_Option_Action_ == "Update")
                {

                    CommTxt = "UPDATE sys_Event SET	E_U_LoginName = :E_U_LoginName,E_UserID = :E_UserID,E_DateTime = :E_DateTime,E_ApplicationID = :E_ApplicationID,E_A_AppName = :E_A_AppName,E_M_Name = :E_M_Name,E_M_PageCode = :E_M_PageCode,E_From = :E_From,E_Type = :E_Type,E_IP = :E_IP,E_Record = :E_Record WHERE (EventID = :EventID)";
                    cmd.CommandText = CommTxt;

                    cmd.Parameters.Add("E_U_LoginName", OracleType.NVarChar).Value = fam.E_U_LoginName + ""; //用户名	
                    cmd.Parameters.Add("E_UserID", OracleType.Int32).Value = fam.E_UserID; //操作时用户ID与sys_Users中UserID	
                    cmd.Parameters.Add("E_DateTime", OracleType.DateTime).Value = fam.E_DateTime; //事件发生的日期及时间	
                    cmd.Parameters.Add("E_ApplicationID", OracleType.Int32).Value = fam.E_ApplicationID; //所属应用程序ID与sys_Applicatio
                    cmd.Parameters.Add("E_A_AppName", OracleType.NVarChar).Value = fam.E_A_AppName + ""; //所属应用名称
                    cmd.Parameters.Add("E_M_Name", OracleType.NVarChar).Value = fam.E_M_Name + ""; //PageCode模块名称与sys_Module相同	
                    cmd.Parameters.Add("E_M_PageCode", OracleType.VarChar).Value = fam.E_M_PageCode + ""; //发生事件时模块名称
                    cmd.Parameters.Add("E_From", OracleType.NVarChar).Value = fam.E_From + ""; //来源
                    cmd.Parameters.Add("E_Type", OracleType.SByte).Value = fam.E_Type; //日记类型,1:操作日记2:安全日志3	
                    cmd.Parameters.Add("E_IP", OracleType.VarChar).Value = fam.E_IP + ""; //客户端IP地址
                    cmd.Parameters.Add("E_Record", OracleType.NVarChar).Value = fam.E_Record + ""; //详细描述
                    cmd.Parameters.Add("EventID", OracleType.Int32).Value = fam.EventID;
                }
                else if (fam.DB_Option_Action_ == "Delete")
                {
                    CommTxt = "Delete from  sys_Event  WHERE (EventID = :EventID)";
                    cmd.CommandText = CommTxt;
                    cmd.Parameters.Add("EventID", OracleType.Int32).Value = fam.EventID;
                }
                else
                    throw new ApplicationException("无法识别的操作命令!");
                Conn.Open();
                OracleTransaction Tran = Conn.BeginTransaction();
                cmd.Transaction = Tran;
                try
                {
                    rInt = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    if (fam.DB_Option_Action_ == "Insert")
                    {
                        cmd.CommandText = "select SEQ_sys_Event_ID.CURRVAL from  dual";
                        rInt = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                    Tran.Commit();
                }
                catch (Exception ex)
                {
                    Tran.Rollback();
                    throw new ApplicationException(ex.ToString());