Exemple #1
0
        public static int Retrieve(Entities ens, string sql, Paras paras, string[] fullAttrs)
        {
            DataTable dt = null;

            switch (ens.GetNewEntity.EnMap.EnDBUrl.DBUrlType)
            {
            case DBUrlType.AppCenterDSN:
                dt = DBAccess.RunSQLReturnTable(sql, paras);
                break;

            case DBUrlType.DBAccessOfMSSQL1:
                dt = DBAccessOfMSSQL1.RunSQLReturnTable(sql);
                break;

            case DBUrlType.DBAccessOfMSSQL2:
                dt = DBAccessOfMSSQL2.RunSQLReturnTable(sql);
                break;

            case DBUrlType.DBAccessOfOracle1:
                dt = DBAccessOfOracle1.RunSQLReturnTable(sql);
                break;

            case DBUrlType.DBAccessOfOracle2:
                dt = DBAccessOfOracle2.RunSQLReturnTable(sql);
                break;

            case DBUrlType.DBAccessOfOLE:
                dt = DBAccessOfOLE.RunSQLReturnTable(sql);
                break;

            default:
                throw new Exception("@没有设置DB类型。");
            }

            if (dt.Rows.Count == 0)
            {
                return(0);
            }

            //设置查询.
            QueryObject.InitEntitiesByDataTable(ens, dt, fullAttrs);

            int i = dt.Rows.Count;

            dt.Dispose();
            return(i);
            //return dt.Rows.Count;
        }
Exemple #2
0
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="en">实体</param>
        /// <param name="sql">组织的查询语句</param>
        /// <returns></returns>
        public static int Retrieve(Entity en, string sql)
        {
            try
            {
                DataTable dt = new DataTable();
                switch (en.EnMap.EnDBUrl.DBUrlType)
                {
                case DBUrlType.AppCenterDSN:
                    dt = DBAccess.RunSQLReturnTable(sql);
                    break;

                case DBUrlType.DBAccessOfMSSQL1:
                    dt = DBAccessOfMSSQL1.RunSQLReturnTable(sql);
                    break;

                case DBUrlType.DBAccessOfMSSQL2:
                    dt = DBAccessOfMSSQL2.RunSQLReturnTable(sql);
                    break;

                case DBUrlType.DBAccessOfOracle1:
                    dt = DBAccessOfOracle1.RunSQLReturnTable(sql);
                    break;

                case DBUrlType.DBAccessOfOracle2:
                    dt = DBAccessOfOracle2.RunSQLReturnTable(sql);
                    break;

                default:
                    throw new Exception("@没有设置DB类型。");
                }

                if (dt.Rows.Count == 0)
                {
                    return(0);
                }
                Attrs attrs = en.EnMap.Attrs;
                EntityDBAccess.fullDate(dt, en, attrs);
                int i = dt.Rows.Count;
                dt.Dispose();
                return(i);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
Exemple #3
0
        /// <summary>
        /// 得到数据源。
        /// </summary>
        /// <returns></returns>
        public DataTable GetToDataTable()
        {
            string    sql           = "SELECT * FROM " + this.ToTable;
            DataTable FromDataTable = new DataTable();

            switch (this.ToDBUrl)
            {
            case DA.DBUrlType.AppCenterDSN:
                FromDataTable = DBAccess.RunSQLReturnTable(sql);
                break;

            case DA.DBUrlType.DBAccessOfMSSQL2:
                FromDataTable = DBAccessOfMSSQL2.RunSQLReturnTable(sql);
                break;

            case DA.DBUrlType.DBAccessOfMSSQL1:
                FromDataTable = DBAccessOfMSSQL1.RunSQLReturnTable(sql);
                break;

            case DA.DBUrlType.DBAccessOfOLE:
                FromDataTable = DBAccessOfOLE.RunSQLReturnTable(sql);
                break;

            case DA.DBUrlType.DBAccessOfOracle1:
                FromDataTable = DBAccessOfOracle1.RunSQLReturnTable(sql);
                break;

            case DA.DBUrlType.DBAccessOfOracle2:
                FromDataTable = DBAccessOfOracle2.RunSQLReturnTable(sql);
                break;

            case DA.DBUrlType.DBAccessOfODBC:
                FromDataTable = DBAccessOfODBC.RunSQLReturnTable(sql);
                break;

            default:
                throw new Exception("the to dburl error DBUrlType ");
            }

            return(FromDataTable);
        }
Exemple #4
0
        /// <summary>
        /// 数据源 run sql ,返回table .
        /// </summary>
        /// <param name="selectSql"></param>
        /// <returns></returns>
        public DataTable FromDBUrlRunSQLReturnTable(string selectSql)
        {
            // 得到数据源.
            DataTable dt = new DataTable();

            switch (this.FromDBUrl)
            {
            case DBUrlType.AppCenterDSN:
                dt = DBAccess.RunSQLReturnTable(selectSql);
                break;

            case DBUrlType.DBAccessOfMSSQL1:
                dt = DBAccessOfMSSQL1.RunSQLReturnTable(selectSql);
                break;

            case DBUrlType.DBAccessOfMSSQL2:
                dt = DBAccessOfMSSQL2.RunSQLReturnTable(selectSql);
                break;

            case DBUrlType.DBAccessOfODBC:
                dt = DBAccessOfODBC.RunSQLReturnTable(selectSql);
                break;

            case DBUrlType.DBAccessOfOLE:
                dt = DBAccessOfOLE.RunSQLReturnTable(selectSql);
                break;

            case DBUrlType.DBAccessOfOracle1:
                dt = DBAccessOfOracle1.RunSQLReturnTable(selectSql);
                break;

            case DBUrlType.DBAccessOfOracle2:
                dt = DBAccessOfOracle2.RunSQLReturnTable(selectSql);
                break;

            default:
                break;
            }
            return(dt);
        }
Exemple #5
0
        /// <summary>
        /// 同步更新.
        /// </summary>
        public void Inphase()
        {
            #region 得到源表
            this.DoBefore();

            string FromSQL = "SELECT ";
            foreach (FF ff in this.FFs)
            {
                //对日期型的判断
                if (ff.DataType == DataType.AppDateTime)
                {
                    FromSQL += " CASE  " +
                               " when datalength( CONVERT(VARCHAR,datepart(month," + ff.FromField + " )))=1 then datename(year," + ff.FromField + " )+'-'+('0'+CONVERT(NVARCHAR,datepart(month," + ff.FromField + " ))) " +
                               " else " +
                               " datename(year," + ff.FromField + " )+'-'+CONVERT(VARCHAR,datepart(month," + ff.FromField + " )) " +
                               " END " +
                               " AS " + ff.FromField + " , ";
                }
                else
                {
                    FromSQL += ff.FromField + ",";
                }
            }
            FromSQL  = FromSQL.Substring(0, FromSQL.Length - 1);
            FromSQL += " from " + this.FromTable;
            FromSQL += this.FromWhere;
            DataTable FromDataTable = new DataTable();
            switch (this.FromDBUrl)
            {
            case DA.DBUrlType.AppCenterDSN:
                FromDataTable = DBAccess.RunSQLReturnTable(FromSQL);
                break;

            case DA.DBUrlType.DBAccessOfMSSQL1:
                FromDataTable = DBAccessOfMSSQL1.RunSQLReturnTable(FromSQL);
                break;

            case DA.DBUrlType.DBAccessOfMSSQL2:
                FromDataTable = DBAccessOfMSSQL2.RunSQLReturnTable(FromSQL);
                break;

            case DA.DBUrlType.DBAccessOfOLE:
                FromDataTable = DBAccessOfOLE.RunSQLReturnTable(FromSQL);
                break;

            case DA.DBUrlType.DBAccessOfOracle2:
                FromDataTable = DBAccessOfOracle2.RunSQLReturnTable(FromSQL);
                break;

            case DA.DBUrlType.DBAccessOfOracle1:
                FromDataTable = DBAccessOfOracle1.RunSQLReturnTable(FromSQL);
                break;

            case DA.DBUrlType.DBAccessOfODBC:
                FromDataTable = DBAccessOfODBC.RunSQLReturnTable(FromSQL);
                break;

            default:
                break;
            }
            #endregion

            #region 得到目的表(字段只包含主键)
            string ToSQL = "SELECT ";
            foreach (FF ff in this.FFs)
            {
                if (ff.IsPK == false)
                {
                    continue;
                }
                ToSQL += ff.ToField + ",";
            }
            ToSQL  = ToSQL.Substring(0, ToSQL.Length - 1);
            ToSQL += " FROM " + this.ToTable;
            DataTable ToDataTable = new DataTable();
            switch (this.ToDBUrl)
            {
            case DA.DBUrlType.AppCenterDSN:
                ToDataTable = DBAccess.RunSQLReturnTable(ToSQL);
                break;

            case DA.DBUrlType.DBAccessOfMSSQL1:
                ToDataTable = DBAccessOfMSSQL1.RunSQLReturnTable(ToSQL);
                break;

            case DA.DBUrlType.DBAccessOfMSSQL2:
                ToDataTable = DBAccessOfMSSQL2.RunSQLReturnTable(ToSQL);
                break;

            case DA.DBUrlType.DBAccessOfOLE:
                ToDataTable = DBAccessOfOLE.RunSQLReturnTable(ToSQL);
                break;

            case DA.DBUrlType.DBAccessOfOracle1:
                ToDataTable = DBAccessOfOracle1.RunSQLReturnTable(ToSQL);
                break;

            case DA.DBUrlType.DBAccessOfOracle2:
                ToDataTable = DBAccessOfOracle2.RunSQLReturnTable(ToSQL);
                break;

            case DA.DBUrlType.DBAccessOfODBC:
                ToDataTable = DBAccessOfODBC.RunSQLReturnTable(ToSQL);
                break;

            default:
                break;
            }
            #endregion

            string SELECTSQL = "";
            string InsertSQL = "";
            string UpdateSQL = "";
            string DeleteSQL = "";
            //int i=0;
            //int j=0;
            int result = 0;

            #region  遍历源表
            foreach (DataRow FromDR in FromDataTable.Rows)
            {
                UpdateSQL = "UPDATE  " + this.ToTable + " SET ";
                foreach (FF ff in this.FFs)
                {
                    switch (ff.DataType)
                    {
                    case DataType.AppDateTime:
                    case DataType.AppString:
                        UpdateSQL += ff.ToField + "='" + FromDR[ff.FromField].ToString() + "',";
                        break;

                    case DataType.AppFloat:
                    case DataType.AppInt:
                    case DataType.AppMoney:
                    case DataType.AppDate:
                    case DataType.AppDouble:
                        UpdateSQL += ff.ToField + "=" + FromDR[ff.FromField].ToString() + ",";
                        break;

                    default:
                        throw new Exception("没有涉及到的数据类型.");
                    }
                }
                UpdateSQL  = UpdateSQL.Substring(0, UpdateSQL.Length - 1);
                UpdateSQL += " WHERE ";
                foreach (FF ff in this.FFs)
                {
                    if (ff.IsPK == false)
                    {
                        continue;
                    }
                    UpdateSQL += ff.ToField + "='" + FromDR[ff.FromField] + "' AND ";
                }

                UpdateSQL = UpdateSQL.Substring(0, UpdateSQL.Length - 5);
                switch (this.ToDBUrl)
                {
                case DA.DBUrlType.AppCenterDSN:
                    result = DBAccess.RunSQL(UpdateSQL);
                    break;

                case DA.DBUrlType.DBAccessOfMSSQL1:
                    string a = UpdateSQL;
                    result = DBAccessOfMSSQL1.RunSQL(UpdateSQL);
                    break;

                case DA.DBUrlType.DBAccessOfMSSQL2:
                    string b = UpdateSQL;
                    result = DBAccessOfMSSQL2.RunSQL(UpdateSQL);
                    break;

                case DA.DBUrlType.DBAccessOfOLE:
                    result = DBAccessOfOLE.RunSQL(UpdateSQL);
                    break;

                case DA.DBUrlType.DBAccessOfOracle1:
                    result = DBAccessOfOracle1.RunSQL(UpdateSQL);
                    break;

                case DA.DBUrlType.DBAccessOfOracle2:
                    result = DBAccessOfOracle2.RunSQL(UpdateSQL);
                    break;

                case DA.DBUrlType.DBAccessOfODBC:
                    result = DBAccessOfODBC.RunSQL(UpdateSQL);
                    break;

                default:
                    break;
                }
                if (result == 0)
                {
                    //插入操作
                    InsertSQL = "INSERT INTO " + this.ToTable + "(";
                    foreach (FF ff in this.FFs)
                    {
                        InsertSQL += ff.ToField.ToString() + ",";
                    }
                    InsertSQL  = InsertSQL.Substring(0, InsertSQL.Length - 1);
                    InsertSQL += ") values(";
                    foreach (FF ff in this.FFs)
                    {
                        if (ff.DataType == DataType.AppString || ff.DataType == DataType.AppDateTime)
                        {
                            InsertSQL += "'" + FromDR[ff.FromField].ToString() + "',";
                        }
                        else
                        {
                            InsertSQL += FromDR[ff.FromField].ToString() + ",";
                        }
                    }
                    InsertSQL  = InsertSQL.Substring(0, InsertSQL.Length - 1);
                    InsertSQL += ")";
                    switch (this.ToDBUrl)
                    {
                    case DA.DBUrlType.AppCenterDSN:
                        DBAccess.RunSQL(InsertSQL);
                        break;

                    case DA.DBUrlType.DBAccessOfMSSQL1:
                        DBAccessOfMSSQL1.RunSQL(InsertSQL);
                        break;

                    case DA.DBUrlType.DBAccessOfMSSQL2:
                        DBAccessOfMSSQL2.RunSQL(InsertSQL);
                        break;

                    case DA.DBUrlType.DBAccessOfOLE:
                        DBAccessOfOLE.RunSQL(InsertSQL);
                        break;

                    case DA.DBUrlType.DBAccessOfOracle1:
                        DBAccessOfOracle1.RunSQL(InsertSQL);
                        break;

                    case DA.DBUrlType.DBAccessOfOracle2:
                        DBAccessOfOracle2.RunSQL(InsertSQL);
                        break;

                    case DA.DBUrlType.DBAccessOfODBC:
                        DBAccessOfODBC.RunSQL(InsertSQL);
                        break;

                    default:
                        break;
                    }
                }
            }
            #endregion

            #region 遍历目的表 如果该条记录存在,continue,如果该条记录不存在,则根据主键删除目的表的对应数据
            foreach (DataRow ToDR in ToDataTable.Rows)
            {
                SELECTSQL = "SELECT ";
                foreach (FF ff in this.FFs)
                {
                    if (ff.IsPK == false)
                    {
                        continue;
                    }
                    SELECTSQL += ff.FromField + ",";
                }
                SELECTSQL  = SELECTSQL.Substring(0, SELECTSQL.Length - 1);
                SELECTSQL += " FROM " + this.FromTable + " WHERE ";
                foreach (FF ff in this.FFs)
                {
                    if (ff.IsPK == false)
                    {
                        continue;
                    }
                    if (ff.DataType == DataType.AppDateTime)
                    {
                        SELECTSQL += " case " +
                                     " when datalength( CONVERT(VARCHAR,datepart(month," + ff.FromField + " )))=1 then datename(year," + ff.FromField + " )+'-'+('0'+CONVERT(VARCHAR,datepart(month," + ff.FromField + " ))) " +
                                     " else " +
                                     " datename(year," + ff.FromField + " )+'-'+CONVERT(VARCHAR,datepart(month," + ff.FromField + " )) " +
                                     " END " +
                                     "='" + ToDR[ff.ToField].ToString() + "' AND ";
                    }
                    else
                    {
                        if (ff.DataType == DataType.AppString)
                        {
                            SELECTSQL += ff.FromField + "='" + ToDR[ff.ToField].ToString() + "' AND ";
                        }
                        else
                        {
                            SELECTSQL += ff.FromField + "=" + ToDR[ff.ToField].ToString() + " AND ";
                        }
                    }
                }
                SELECTSQL = SELECTSQL.Substring(0, SELECTSQL.Length - 5);
                //SELECTSQL+=this.FromWhere;
                result = 0;
                switch (this.FromDBUrl)
                {
                case DA.DBUrlType.AppCenterDSN:
                    result = DBAccess.RunSQLReturnCOUNT(SELECTSQL);
                    break;

                case DA.DBUrlType.DBAccessOfMSSQL1:
                    result = DBAccessOfMSSQL1.RunSQLReturnCOUNT(SELECTSQL);
                    break;

                case DA.DBUrlType.DBAccessOfMSSQL2:
                    result = DBAccessOfMSSQL2.RunSQLReturnCOUNT(SELECTSQL);
                    break;

                case DA.DBUrlType.DBAccessOfOLE:
                    result = DBAccessOfOLE.RunSQLReturnCOUNT(SELECTSQL);
                    break;

                case DA.DBUrlType.DBAccessOfOracle1:
                    result = DBAccessOfOracle1.RunSQL(SELECTSQL);
                    break;

                case DA.DBUrlType.DBAccessOfOracle2:
                    result = DBAccessOfOracle2.RunSQL(SELECTSQL);
                    break;

                case DA.DBUrlType.DBAccessOfODBC:
                    result = DBAccessOfODBC.RunSQLReturnCOUNT(SELECTSQL);
                    break;

                default:
                    break;
                }

                if (result != 1)
                {
                    //delete
                    DeleteSQL = "delete FROM  " + this.ToTable + " WHERE ";
                    foreach (FF ff in this.FFs)
                    {
                        if (ff.IsPK == false)
                        {
                            continue;
                        }
                        if (ff.DataType == DataType.AppString)
                        {
                            DeleteSQL += ff.ToField + "='" + ToDR[ff.ToField].ToString() + "' AND ";
                        }
                        else
                        {
                            DeleteSQL += ff.ToField + "=" + ToDR[ff.ToField].ToString() + " AND ";
                        }
                    }
                    DeleteSQL = DeleteSQL.Substring(0, DeleteSQL.Length - 5);
                    switch (this.ToDBUrl)
                    {
                    case DA.DBUrlType.AppCenterDSN:
                        DBAccess.RunSQL(DeleteSQL);
                        break;

                    case DA.DBUrlType.DBAccessOfMSSQL1:
                        DBAccessOfMSSQL1.RunSQL(DeleteSQL);
                        break;

                    case DA.DBUrlType.DBAccessOfMSSQL2:
                        DBAccessOfMSSQL2.RunSQL(DeleteSQL);
                        break;

                    case DA.DBUrlType.DBAccessOfOLE:
                        DBAccessOfOLE.RunSQL(DeleteSQL);
                        break;

                    case DA.DBUrlType.DBAccessOfOracle1:
                        DBAccessOfOracle1.RunSQL(DeleteSQL);
                        break;

                    case DA.DBUrlType.DBAccessOfOracle2:
                        DBAccessOfOracle2.RunSQL(DeleteSQL);
                        break;

                    case DA.DBUrlType.DBAccessOfODBC:
                        DBAccessOfODBC.RunSQL(DeleteSQL);
                        break;

                    default:
                        break;
                    }
                    continue;
                }
                else if (result > 1)
                {
                    throw new Exception("目的数据异常错误+表名;关键字" + this.ToTable + "关键字" + ToDR[0].ToString());
                }
            }
            #endregion

            if (this.UPDATEsql != null)
            {
                switch (this.ToDBUrl)
                {
                case DA.DBUrlType.AppCenterDSN:
                    DBAccess.RunSQL(UPDATEsql);
                    break;

                //case DA.DBUrlType.DBAccessOfMSSQL:
                //    DBAccess.RunSQL(UPDATEsql);
                //    break;
                case DA.DBUrlType.DBAccessOfOLE:
                    DBAccessOfOLE.RunSQL(UPDATEsql);
                    break;

                //case DA.DBUrlType.DBAccessOfOracle:
                //    DBAccessOfOracle.RunSQL(UPDATEsql);
                //    break;
                case DA.DBUrlType.DBAccessOfODBC:
                    DBAccessOfODBC.RunSQL(UPDATEsql);
                    break;

                default:
                    break;
                }
            }
            this.DoAfter();
        }
Exemple #6
0
        /// <summary>
        /// 得到数据源。
        /// </summary>
        /// <returns>数据源</returns>
        public DataTable GetFromDataTable()
        {
            string FromSQL = "SELECT ";

            foreach (FF ff in this.FFs)
            {
                //对日期型的判断
                if (ff.DataType == DataType.AppDateTime)
                {
                    FromSQL += " CASE  " +
                               " when datalength( CONVERT(VARCHAR,datepart(month," + ff.FromField + " )))=1 then datename(year," + ff.FromField + " )+'-'+('0'+CONVERT(NVARCHAR,datepart(month," + ff.FromField + " ))) " +
                               " else " +
                               " datename(year," + ff.FromField + " )+'-'+CONVERT(VARCHAR,datepart(month," + ff.FromField + " )) " +
                               " END " +
                               " AS " + ff.FromField + " , ";
                }
                else
                {
                    FromSQL += ff.FromField + ",";
                }
            }

            FromSQL  = FromSQL.Substring(0, FromSQL.Length - 1);
            FromSQL += " from " + this.FromTable;
            FromSQL += this.FromWhere;
            DataTable FromDataTable = new DataTable();

            switch (this.FromDBUrl)
            {
            case DA.DBUrlType.AppCenterDSN:
                FromDataTable = DBAccess.RunSQLReturnTable(FromSQL);
                break;

            case DA.DBUrlType.DBAccessOfMSSQL1:
                FromDataTable = DBAccessOfMSSQL1.RunSQLReturnTable(FromSQL);
                break;

            case DA.DBUrlType.DBAccessOfMSSQL2:
                FromDataTable = DBAccessOfMSSQL2.RunSQLReturnTable(FromSQL);
                break;

            case DA.DBUrlType.DBAccessOfOLE:
                FromDataTable = DBAccessOfOLE.RunSQLReturnTable(FromSQL);
                break;

            case DA.DBUrlType.DBAccessOfOracle1:
                FromDataTable = DBAccessOfOracle1.RunSQLReturnTable(FromSQL);
                break;

            case DA.DBUrlType.DBAccessOfOracle2:
                FromDataTable = DBAccessOfOracle2.RunSQLReturnTable(FromSQL);
                break;

            case DA.DBUrlType.DBAccessOfODBC:
                FromDataTable = DBAccessOfODBC.RunSQLReturnTable(FromSQL);
                break;

            default:
                throw new Exception("the from dburl error DBUrlType ");
            }
            return(FromDataTable);
        }
Exemple #7
0
        public static int Retrieve(Entities ens, string sql)
        {
            try
            {
                DataTable dt = new DataTable();
                switch (ens.GetNewEntity.EnMap.EnDBUrl.DBUrlType)
                {
                case DBUrlType.AppCenterDSN:
                    dt = DBAccess.RunSQLReturnTable(sql);
                    break;

                case DBUrlType.DBAccessOfMSSQL1:
                    dt = DBAccessOfMSSQL1.RunSQLReturnTable(sql);
                    break;

                case DBUrlType.DBAccessOfMSSQL2:
                    dt = DBAccessOfMSSQL2.RunSQLReturnTable(sql);
                    break;

                case DBUrlType.DBAccessOfOracle1:
                    dt = DBAccessOfOracle1.RunSQLReturnTable(sql);
                    break;

                case DBUrlType.DBAccessOfOracle2:
                    dt = DBAccessOfOracle2.RunSQLReturnTable(sql);
                    break;

                case DBUrlType.DBAccessOfOLE:
                    dt = DBAccessOfOLE.RunSQLReturnTable(sql);
                    break;

                default:
                    throw new Exception("@没有设置DB类型。");
                }

                if (dt.Rows.Count == 0)
                {
                    return(0);
                }

                Map   enMap = ens.GetNewEntity.EnMap;
                Attrs attrs = enMap.Attrs;

                //Entity  en1 = ens.GetNewEntity;
                foreach (DataRow dr in dt.Rows)
                {
                    Entity en = ens.GetNewEntity;
                    //Entity  en = en1.CreateInstance();
                    foreach (Attr attr in attrs)
                    {
                        en.Row.SetValByKey(attr.Key, dr[attr.Key]);
                    }
                    ens.AddEntity(en);
                }
                int i = dt.Rows.Count;
                dt.Dispose();
                return(i);
                //return dt.Rows.Count;
            }
            catch (System.Exception ex)
            {
                // ens.GetNewEntity.CheckPhysicsTable();
                throw new Exception("@在[" + ens.GetNewEntity.EnDesc + "]查询时出现错误:" + ex.Message);
            }
        }