/// <summary>
/// 根据实体元数据Id和备用关键字名称查询备用关键字
/// </summary>
/// <param name="infoId"></param>
/// <param name="name"></param>
/// <returns></returns>
public async Task <EntityInfoAlternateKey> QueryByEntityInfoIdAndName(Guid infoId, string name)
{
EntityInfoAlternateKey info = null;
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, _entityMetadataConnectionFactory.CreateReadForEntityMetadata(), async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = string.Format(@"SELECT {0},{1}
FROM [EntityInfoAlternateKey] AS ekey
INNER JOIN [EntityInfo] AS info
ON ekey.[entityinfoid] = info.[id]
WHERE info.[id] = @infoid
AND ekey.[name] = @name;", StoreHelper.GetEntityInfoAlternateKeySelectFields("ekey"), StoreHelper.GetEntityInfoSelectFields("info"))
})
{
var parameter = new SqlParameter("@infoid", SqlDbType.UniqueIdentifier)
{
Value = infoId
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@name", SqlDbType.NVarChar, 256)
{
Value = name
};
commond.Parameters.Add(parameter);
commond.Prepare();
SqlDataReader reader = null;
await using (reader = await commond.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
info = new EntityInfoAlternateKey();
StoreHelper.SetEntityInfoAlternateKeySelectFields(info, reader, "ekey");
info.EntityInfo = new EntityInfo();
StoreHelper.SetEntityInfoSelectFields(info.EntityInfo, reader, "info");
}
await reader.CloseAsync();
}
}
});
return(info);
}
/// <summary>
/// 根据实体元树备用关键字Id查询全部记录
/// </summary>
/// <param name="alternateKeyId"></param>
/// <param name="callback"></param>
/// <returns></returns>
public async Task QueryAllByAlternateKeyId(Guid alternateKeyId, Func <EntityInfoAlternateKeyRelation, Task> callback)
{
List <EntityInfoAlternateKeyRelation> itemList = new List <EntityInfoAlternateKeyRelation>();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, _entityMetadataConnectionFactory.CreateReadForEntityMetadata(), async (conn, transaction) =>
{
int?sequence = null;
int pageSize = 500;
while (true)
{
itemList.Clear();
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran
})
{
if (!sequence.HasValue)
{
commond.CommandText = string.Format(@"SELECT TOP (@pagesize) {0},{1},{2},{3}
FROM [EntityInfoAlternateKeyRelation] AS relation
INNER JOIN [EntityInfoAlternateKey] AS ekey ON relation.[entityinfoAlternatekeyid] = ekey.[id]
INNER JOIN [EntityAttributeInfo] AS attribute ON relation.[entityattributeinfoid] = attribute.[id]
INNER JOIN [EntityInfo] AS info ON attribute.[entityinfoid] = info.[id]
WHERE ekey.[id] = @alternateKeyId ORDER BY relation.[order];",
StoreHelper.GetEntityInfoAlternateKeyRelationSelectFields("relation"),
StoreHelper.GetEntityInfoAlternateKeySelectFields("ekey"),
StoreHelper.GetEntityAttributeInfoSelectFields("attribute"),
StoreHelper.GetEntityInfoSelectFields("info"));
}
else
{
commond.CommandText = string.Format(@"SELECT TOP (@pagesize) {0},{1},{2},{3}
FROM [EntityInfoAlternateKeyRelation] AS relation
INNER JOIN [EntityInfoAlternateKey] AS ekey ON relation.[entityinfoAlternatekeyid] = ekey.[id]
INNER JOIN [EntityAttributeInfo] AS attribute ON relation.[entityattributeinfoid] = attribute.[id]
INNER JOIN [EntityInfo] AS info ON attribute.[entityinfoid] = info.[id]
WHERE ekey.[id] = @alternateKeyId AND relation.[order] > @sequence ORDER BY relation.[order];",
StoreHelper.GetEntityInfoAlternateKeyRelationSelectFields("relation"),
StoreHelper.GetEntityInfoAlternateKeySelectFields("ekey"),
StoreHelper.GetEntityAttributeInfoSelectFields("attribute"),
StoreHelper.GetEntityInfoSelectFields("info"));
}
var parameter = new SqlParameter("@alternateKeyId", SqlDbType.UniqueIdentifier)
{
Value = alternateKeyId
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@pagesize", SqlDbType.Int)
{
Value = pageSize
};
commond.Parameters.Add(parameter);
if (sequence.HasValue)
{
parameter = new SqlParameter("@sequence", SqlDbType.BigInt)
{
Value = sequence
};
commond.Parameters.Add(parameter);
}
await commond.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await commond.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var item = new EntityInfoAlternateKeyRelation();
StoreHelper.SetEntityInfoAlternateKeyRelationSelectFields(item, reader, "relation");
sequence = (int)reader["relationorder"];
item.EntityInfoAlternateKey = new EntityInfoAlternateKey();
StoreHelper.SetEntityInfoAlternateKeySelectFields(item.EntityInfoAlternateKey, reader, "ekey");
item.EntityAttributeInfo = new EntityAttributeInfo();
StoreHelper.SetEntityAttributeInfoSelectFields(item.EntityAttributeInfo, reader, "attribute");
item.EntityAttributeInfo.EntityInfo = new EntityInfo();
StoreHelper.SetEntityInfoSelectFields(item.EntityAttributeInfo.EntityInfo, reader, "info");
itemList.Add(item);
}
await reader.CloseAsync();
}
}
foreach (var item in itemList)
{
await callback(item);
}
if (itemList.Count != pageSize)
{
break;
}
}
});
}