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)));
}
/// <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);
}
}
}
}
/// <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);
}
public override int GetHashCode()
{
unchecked
{
int res = 0;
if (m_alias != null)
{
res += m_alias.GetHashCode();
}
if (TableOrView != null)
{
res += TableOrView.GetHashCode();
}
return(res);
}
}
/// <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);
}
}
}
}
/// <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);
}
}
}
}
internal override TableStatistics LoadTableStatistics(TableOrView tableOrView)
{
throw new NotImplementedException();
}
public String CsdlEntityTypeRef(TableOrView dbt)
{
return(String.Format("{0}.{1}", CsdlNs(), CsdlEntityType(dbt)));
}
public String CsdlEntityType(TableOrView dbt)
{
return(TSimpleIdentifier(String.Format("{0}", dbt.Name)));
}
public String CsdlEntitySet(TableOrView dbt)
{
return(TSimpleIdentifier(dbt.Name));
}
public String SsdlEntityType(TableOrView dbt)
{
return(String.Format("{0}", dbt.Name));
}
public String SsdlEntitySet(TableOrView dbt)
{
return(dbt.Name);
}
// 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);
