コード例 #1
0
 public override string GenerateSelectStarQuery(TableOrView tableOrView, int top)
 {
     return(String.Format(
                "SELECT t.* FROM {1} AS t {0}",
                GenerateTopExpression(top),
                GetResolvedTableName(tableOrView.DatabaseName, tableOrView.SchemaName, tableOrView.ObjectName)));
 }
コード例 #2
0
        /// <summary>
        /// Loads statistics of a table.
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        internal override TableStatistics LoadTableStatistics(TableOrView obj)
        {
            var sql = @"
-- Data space
SELECT SUM(a.total_pages), SUM(a.used_pages), SUM(a.data_pages)
FROM sys.objects o
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
INNER JOIN sys.indexes i ON i.object_id = o.object_id AND i.type IN (0, 1)  -- heap and clustered
INNER JOIN sys.partitions p	ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.allocation_units a ON a.container_id = p.hobt_id
WHERE s.name = @schemaName AND o.name = @objectName

-- Index space
SELECT SUM(a.total_pages), SUM(a.used_pages), SUM(a.data_pages)
FROM sys.objects o
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
INNER JOIN sys.indexes i ON i.object_id = o.object_id AND i.type IN (2)  -- non-clustered
INNER JOIN sys.partitions p	ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.allocation_units a ON a.container_id = p.hobt_id
WHERE s.name = @schemaName AND o.name = @objectName

-- Row count
SELECT SUM(p.rows)
FROM sys.objects o
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
INNER JOIN sys.indexes i ON i.object_id = o.object_id AND i.type IN (0, 1)
INNER JOIN sys.partitions p	ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE s.name = @schemaName AND o.name = @objectName
";

            using (var cn = OpenConnection())
            {
                using (var cmd = new SqlCommand(sql, cn))
                {
                    cmd.Parameters.Add("@schemaName", SqlDbType.NVarChar, 128).Value = String.IsNullOrWhiteSpace(obj.SchemaName) ? (object)DBNull.Value : (object)obj.SchemaName;
                    cmd.Parameters.Add("@objectName", SqlDbType.NVarChar, 128).Value = obj.ObjectName;

                    using (var dr = cmd.ExecuteReader())
                    {
                        var stat = new TableStatistics();

                        // Data space
                        dr.Read();
                        stat.DataSpace = dr.IsDBNull(0) ? 0L : dr.GetInt64(0) * 0x2000;    // 8K pages

                        // Index space
                        dr.NextResult();
                        dr.Read();
                        stat.IndexSpace = dr.IsDBNull(0) ? 0L : dr.GetInt64(0) * 0x2000;    // 8K pages

                        // Row count
                        dr.NextResult();
                        dr.Read();
                        stat.RowCount = dr.IsDBNull(0) ? 0L : dr.GetInt64(0);

                        return(stat);
                    }
                }
            }
        }
コード例 #3
0
ファイル: UploadXmlHelper.cs プロジェクト: radtek/Applet_NXQL
        /// <summary>
        /// 从XML加载配置对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="xmlPath"></param>
        /// <returns></returns>
        public List <TableOrView> LoadConfig()
        {
            string             xmlPath = "UploadData.AppConfig.xml";
            List <TableOrView> result  = new List <TableOrView>();

            if (File.Exists(xmlPath))
            {
                XmlDocument xdoc = new XmlDocument();
                xdoc.LoadXml(File.ReadAllText(xmlPath));

                XmlNodeList list = xdoc.GetElementsByTagName("TableOrView");

                var tables = (new TableOrView()).GetType().GetProperties();
                var props  = (new PropertySet()).GetType().GetProperties();

                foreach (XmlNode item in list)
                {
                    TableOrView entity = new TableOrView()
                    {
                        PropertySetDetails = new List <PropertySet>()
                    };

                    foreach (XmlAttribute attr in item.Attributes)
                    {
                        if (tables.Count(a => a.Name.ToLower() == attr.Name.ToLower()) <= 0)
                        {
                            continue;
                        }
                        var pi = tables.First(a => a.Name.ToLower() == attr.Name.ToLower());
                        pi.SetValue(entity, attr.Value, null);
                    }

                    foreach (XmlNode node in item.ChildNodes)
                    {
                        PropertySet detail = new PropertySet();

                        foreach (XmlAttribute attr in node.Attributes)
                        {
                            if (props.Count(a => a.Name.ToLower() == attr.Name.ToLower()) <= 0)
                            {
                                continue;
                            }
                            var pi = props.First(a => a.Name.ToLower() == attr.Name.ToLower());
                            pi.SetValue(detail, attr.Value, null);
                        }
                        entity.PropertySetDetails.Add(detail);
                    }
                    result.Add(entity);
                }
            }
            return(result);
        }
コード例 #4
0
ファイル: DmlfRelation.cs プロジェクト: timothydodd/dbshell
 public override int GetHashCode()
 {
     unchecked
     {
         int res = 0;
         if (m_alias != null)
         {
             res += m_alias.GetHashCode();
         }
         if (TableOrView != null)
         {
             res += TableOrView.GetHashCode();
         }
         return(res);
     }
 }
コード例 #5
0
ファイル: UploadDataDAO.cs プロジェクト: radtek/Applet_NXQL
        /// <summary>
        /// 同步集团数据
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="thirdDber"></param>
        /// <param name="output"></param>
        private void DownLoadData(TableOrView entity, OracleDapperDber thirdDber, Action <string, eOutputType> output)
        {
            //同步集团数据
            string sbSearch = "select ";

            foreach (var item in entity.PropertySetDetails)
            {
                sbSearch += string.Format("{0},", item.Source);
            }

            sbSearch  = sbSearch.Trim(',');
            sbSearch += string.Format(" from {0} ", entity.Source);

            if (!string.IsNullOrWhiteSpace(entity.TimeIntervalProperty))
            {
                int    intervalValue = 7;
                string configValue   = commonDAO.GetAppletConfigString("数据同步智仁接口", "获取集团数据时间间隔(天)");
                if (!string.IsNullOrWhiteSpace(configValue))
                {
                    Int32.TryParse(configValue, out intervalValue);
                }
                DateTime startTime = DateTime.Now.AddDays(-intervalValue);

                sbSearch += string.Format(" where {0}>=to_date('{1}','yyyy-MM-dd hh24:mi:ss')", entity.TimeIntervalProperty, startTime.ToString("yyyy-MM-dd HH:mm:ss"));
            }

            DataTable dt = thirdDber.ExecuteDataTable(sbSearch);

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

            Boolean ishavepk = entity.PropertySetDetails.Count(a => a.DesPrimaryKey != null && a.DesPrimaryKey.ToLower() == "true") > 0;

            foreach (DataRow item in dt.Rows)
            {
                //只有更新和新增操作
                string strChaXun = string.Format("select * from {0} where 1=1 ", entity.Destination);
                if (ishavepk)
                {
                    foreach (var pk in entity.PropertySetDetails.Where(a => a.DesPrimaryKey != null && a.DesPrimaryKey.ToLower() == "true"))
                    {
                        strChaXun += string.Format("and {0}='{1}' ", pk.Destination, item[pk.Source] == null ? "" : item[pk.Source].ToString());
                    }
                }
                else
                {
                    foreach (var pk in entity.PropertySetDetails)
                    {
                        strChaXun += string.Format("and {0}='{1}' ", pk.Destination, item[pk.Source] == null ? "" : item[pk.Source].ToString());
                    }
                }

                DataTable dtHaveData = SelfDber.ExecuteDataTable(strChaXun);
                if (dtHaveData == null || dtHaveData.Rows.Count <= 0)
                {
                    //新增
                    string insertSql = string.Format(@"insert into {0} (", entity.Destination);
                    string names     = "ID, CREATIONTIME, CREATORUSERID,LASTMODIFICATIONTIME,";
                    string values    = string.Format("'{0}', sysdate,1,sysdate,", Guid.NewGuid().ToString());

                    if (entity.Description == "矿点同步")
                    {
                        string code = commonDAO.GetMineNewChildCode("000");
                        if (!string.IsNullOrEmpty(code))
                        {
                            names  += "Code,";
                            values += "'" + code + "',";
                        }
                        names  += "Sort,";
                        values += commonDAO.GetMineSort() + ",";
                    }
                    else if (entity.Description == "煤种同步")
                    {
                        string code = commonDAO.GetFuelKindNewChildCode("000");
                        if (!string.IsNullOrEmpty(code))
                        {
                            names  += "Code,";
                            values += "'" + code + "',";
                        }
                        names  += "Sort,";
                        values += commonDAO.GetFuelKindSort() + ",";
                    }

                    if (!string.IsNullOrWhiteSpace(entity.IsSoftDelete) && entity.IsSoftDelete.ToLower() == "true")
                    {
                        names  += "ISDELETED,";
                        values += "0,";
                    }

                    if (!string.IsNullOrWhiteSpace(entity.TreeParentId))
                    {
                        names  += "parentid,";
                        values += string.Format("'{0}',", entity.TreeParentId);
                    }

                    foreach (var detail in entity.PropertySetDetails)
                    {
                        names  += string.Format("{0},", detail.Destination);
                        values += string.Format("'{0}',", item[detail.Source] == null ? "" : item[detail.Source].ToString());
                    }

                    if (!string.IsNullOrWhiteSpace(entity.IsHaveSyncTime) && entity.IsHaveSyncTime.ToLower() == "true")
                    {
                        names  += "SYNCTIME,";
                        values += "sysdate,";
                    }

                    insertSql += names.Trim(',') + ") values (" + values.Trim(',') + ")";
                    if (SelfDber.Execute(insertSql) > 0)
                    {
                        output(string.Format("接口取数【{0}】已同步,操作:新增", entity.Description), eOutputType.Normal);
                    }
                }
                else
                {
                    //更新
                    string updateSql = string.Format("update {0} set ", entity.Destination);

                    foreach (var detail in entity.PropertySetDetails)
                    {
                        updateSql += string.Format("{0}='{1}',", detail.Destination, item[detail.Source] == null ? "" : item[detail.Source].ToString());
                    }

                    if (!string.IsNullOrWhiteSpace(entity.IsHaveSyncTime) && entity.IsHaveSyncTime.ToLower() == "true")
                    {
                        updateSql += "SYNCTIME=sysdate,";
                    }

                    updateSql = updateSql.Trim(',') + string.Format(" where id='{0}'", dtHaveData.Rows[0]["ID"].ToString());
                    if (SelfDber.Execute(updateSql) > 0)
                    {
                        output(string.Format("接口取数【{0}】已同步,操作:更新", entity.Description), eOutputType.Normal);
                    }
                }
            }
        }
コード例 #6
0
ファイル: UploadDataDAO.cs プロジェクト: radtek/Applet_NXQL
        /// <summary>
        /// 上报数据
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="thirdDber"></param>
        /// <param name="output"></param>
        private void UpLoadData(TableOrView entity, OracleDapperDber thirdDber, Action <string, eOutputType> output)
        {
            //查询本地是否有待上报数据
            string sbSearch = "select ";

            foreach (var item in entity.PropertySetDetails)
            {
                sbSearch += string.Format("{0},", item.Source);
            }

            sbSearch  = sbSearch.Trim(',');
            sbSearch += string.Format(" from {0} ", entity.Source);


            if (!string.IsNullOrWhiteSpace(entity.TimeIntervalProperty))
            {
                int    intervalValue = 7;
                string configValue   = commonDAO.GetAppletConfigString("数据同步智仁接口", "数据上报时间间隔(天)");
                if (!string.IsNullOrWhiteSpace(configValue))
                {
                    Int32.TryParse(configValue, out intervalValue);
                }
                DateTime startTime = DateTime.Now.AddDays(-intervalValue);

                sbSearch += string.Format(" where {0}>=to_date('{1}','yyyy-MM-dd hh24:mi:ss')", entity.TimeIntervalProperty, startTime);
            }

            DataTable dt = SelfDber.ExecuteDataTable(sbSearch);

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

            Boolean ishavepk = entity.PropertySetDetails.Count(a => a.DesPrimaryKey != null && a.DesPrimaryKey.ToLower() == "true") > 0;

            foreach (DataRow item in dt.Rows)
            {
                //只有更新和新增操作
                string strChaXun = string.Format("select * from {0} where 1=1 ", entity.Destination);
                if (ishavepk)
                {
                    foreach (var pk in entity.PropertySetDetails.Where(a => a.DesPrimaryKey != null && a.DesPrimaryKey.ToLower() == "true"))
                    {
                        if (!string.IsNullOrWhiteSpace(pk.DesType) && pk.DesType.ToLower() == "datetime")
                        {
                            pk.Format  = "yyyy-MM-dd hh24:mi:ss";
                            strChaXun += string.Format("and {0}=to_date('{1}','{2}') ", pk.Destination, item[pk.Source] == DBNull.Value ? "" : item[pk.Source].ToString(), pk.Format);
                        }
                        else
                        {
                            strChaXun += string.Format("and {0}='{1}' ", pk.Destination, item[pk.Source] == DBNull.Value ? "" : item[pk.Source].ToString());
                        }
                    }
                }
                else
                {
                    foreach (var pk in entity.PropertySetDetails)
                    {
                        if (!string.IsNullOrWhiteSpace(pk.DesType) && pk.DesType.ToLower() == "datetime")
                        {
                            pk.Format  = "yyyy-MM-dd hh24:mi:ss";
                            strChaXun += string.Format("and {0}=to_date('{1}','{2}') ", pk.Destination, item[pk.Source] == DBNull.Value ? "" : item[pk.Source].ToString(), pk.Format);
                        }
                        else
                        {
                            strChaXun += string.Format("and {0}='{1}' ", pk.Destination, item[pk.Source] == DBNull.Value ? "" : item[pk.Source].ToString());
                        }
                    }
                }

                DataTable dtHaveData = thirdDber.ExecuteDataTable(strChaXun);
                if (dtHaveData == null || dtHaveData.Rows.Count <= 0)
                {
                    //新增
                    string insertSql = string.Format(@"insert into {0} (", entity.Destination);
                    string names     = "";
                    string values    = "";


                    foreach (var detail in entity.PropertySetDetails)
                    {
                        names += string.Format("{0},", detail.Destination);

                        if (detail.DesType != null && detail.DesType.ToLower() == "datetime" && !string.IsNullOrWhiteSpace(detail.Format))
                        {
                            values += string.Format("{0},", item[detail.Source] == DBNull.Value ? "''" : ("'" + DateTime.Parse(item[detail.Source].ToString()).ToString(detail.Format) + "'"));
                        }
                        else
                        {
                            values += string.Format("'{0}',", item[detail.Source] == DBNull.Value ? "" : item[detail.Source].ToString());
                        }
                    }

                    insertSql += names.Trim(',') + ") values (" + values.Trim(',') + ")";
                    if (thirdDber.Execute(insertSql) > 0)
                    {
                        output(string.Format("接口上报数据【{0}】已同步,操作:新增", entity.Description), eOutputType.Normal);
                    }
                }
                else
                {
                    //更新
                    string updateSql = string.Format("update {0} set ", entity.Destination);

                    foreach (var detail in entity.PropertySetDetails)
                    {
                        if (detail.DesType != null && detail.DesType.ToLower() == "datetime" && !string.IsNullOrWhiteSpace(detail.Format))
                        {
                            updateSql += string.Format("{0}={1},", detail.Destination, item[detail.Source] == DBNull.Value ? "''" : ("'" + DateTime.Parse(item[detail.Source].ToString()).ToString(detail.Format) + "'"));
                        }
                        else
                        {
                            updateSql += string.Format("{0}='{1}',", detail.Destination, item[detail.Source] == DBNull.Value ? "" : item[detail.Source].ToString());
                        }
                    }
                    //if (entity.Description == "编码接口表")
                    //    updateSql = updateSql.Trim(',') + string.Format(" where id='{0}'", dtHaveData.Rows[0]["TRUCKENTERID"].ToString());
                    //else

                    updateSql = updateSql.Trim(',');
                    string updateWhere = "";
                    if (ishavepk)
                    {
                        int index = 0;
                        foreach (var pk in entity.PropertySetDetails.Where(a => a.DesPrimaryKey != null && a.DesPrimaryKey.ToLower() == "true"))
                        {
                            if (!string.IsNullOrWhiteSpace(pk.DesType) && pk.DesType.ToLower() == "datetime")
                            {
                                pk.Format    = "yyyy-MM-dd hh24:mi:ss";
                                updateWhere += string.Format("{3} {0}=to_date('{1}','{2}') ", pk.Destination, item[pk.Source] == DBNull.Value ? "" : item[pk.Source].ToString(), pk.Format, (index == 0 ? "" : "and"));
                            }
                            else
                            {
                                updateWhere += string.Format("{2} {0}='{1}' ", pk.Destination, item[pk.Source] == DBNull.Value ? "" : item[pk.Source].ToString(), (index == 0 ? "" : "and"));
                            }
                            index++;
                        }

                        if (!string.IsNullOrWhiteSpace(updateWhere))
                        {
                            updateWhere = " where " + updateWhere;
                        }
                    }

                    if (string.IsNullOrWhiteSpace(updateWhere))
                    {
                        updateWhere = string.Format(" where {0}='{1}'", entity.PropertySetDetails.First().Destination, dtHaveData.Rows[0][entity.PropertySetDetails.First().Destination].ToString());
                    }

                    updateSql = updateSql + " " + updateWhere;
                    //updateSql = updateSql.Trim(',') + string.Format(" where {0}='{1}'", entity.PropertySetDetails.First().Destination, dtHaveData.Rows[0][entity.PropertySetDetails.First().Destination].ToString());
                    if (thirdDber.Execute(updateSql) > 0)
                    {
                        output(string.Format("接口取数【{0}】已同步,操作:更新", entity.Description), eOutputType.Normal);
                    }
                }
            }
        }
コード例 #7
0
 internal override TableStatistics LoadTableStatistics(TableOrView tableOrView)
 {
     throw new NotImplementedException();
 }
コード例 #8
0
 public String CsdlEntityTypeRef(TableOrView dbt)
 {
     return(String.Format("{0}.{1}", CsdlNs(), CsdlEntityType(dbt)));
 }
コード例 #9
0
 public String CsdlEntityType(TableOrView dbt)
 {
     return(TSimpleIdentifier(String.Format("{0}", dbt.Name)));
 }
コード例 #10
0
 public String CsdlEntitySet(TableOrView dbt)
 {
     return(TSimpleIdentifier(dbt.Name));
 }
コード例 #11
0
 public String SsdlEntityType(TableOrView dbt)
 {
     return(String.Format("{0}", dbt.Name));
 }
コード例 #12
0
 public String SsdlEntitySet(TableOrView dbt)
 {
     return(dbt.Name);
 }
コード例 #13
0
        // These functions don't use the parsing tree, they generate certain
        // types of queries.

        /// <summary>
        ///
        /// </summary>
        /// <param name="linkedServerName"></param>
        /// <param name="databaseName"></param>
        /// <param name="schemaName"></param>
        /// <param name="tableName"></param>
        /// <param name="top"></param>
        /// <returns></returns>
        /// <remarks>This is used by the web interface's 'peek' function</remarks>
        public abstract string GenerateSelectStarQuery(TableOrView tableOrView, int top);