public async Task <OptionSetValueMetadata> QueryById(Guid id)
{
OptionSetValueMetadata metadata = null;
//获取只读连接字符串
var strConn = _entityMetadataConnectionFactory.CreateReadForEntityMetadata();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, strConn, 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} from OptionSetValueMetadata where [id]=@id", StoreHelper.GetOptionSetValueMetadataSelectFields(string.Empty))
})
{
var parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = id
};
commond.Parameters.Add(parameter);
await commond.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await commond.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
metadata = new OptionSetValueMetadata();
StoreHelper.SetOptionSetValueMetadataSelectFields(metadata, reader, string.Empty);
}
reader.Close();
}
}
});
return(metadata);
}
/// <summary>
/// 根据实体元数据ID查询该实体元数据关联的全部记录
/// </summary>
/// <param name="infoId"></param>
/// <param name="callback"></param>
/// <returns></returns>
public async Task QueryAllByEntityInfoId(Guid infoId, Func <EntityInfoKeyRelation, Task> callback)
{
List <EntityInfoKeyRelation> itemList = new List <EntityInfoKeyRelation>();
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}
FROM [EntityInfoKeyRelation] AS keyrelation INNER JOIN [EntityInfo] AS info ON keyrelation.[entityinfoid] = info.[id]
INNER JOIN [EntityAttributeInfo] AS attribute ON keyrelation.[entityattributeinfoid] = attribute.[id]
WHERE info.[id] = @id ORDER BY keyrelation.[order];",
StoreHelper.GetEntityInfoKeyRelationSelectFields("keyrelation"),
StoreHelper.GetEntityInfoSelectFields("info"),
StoreHelper.GetEntityAttributeInfoSelectFields("attribute"));
}
else
{
commond.CommandText = string.Format(@"SELECT TOP (@pagesize) {0},{1},{2}
FROM [EntityInfoKeyRelation] AS keyrelation INNER JOIN [EntityInfo] AS info ON keyrelation.[entityinfoid] = info.[id]
INNER JOIN [EntityAttributeInfo] AS attribute ON keyrelation.[entityattributeinfoid] = attribute.[id]
WHERE info.[id] = @id AND keyrelation.[order] > @sequence ORDER BY keyrelation.[order];",
StoreHelper.GetEntityInfoKeyRelationSelectFields("keyrelation"),
StoreHelper.GetEntityInfoSelectFields("info"),
StoreHelper.GetEntityAttributeInfoSelectFields("attribute"));
}
var parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = infoId
};
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 EntityInfoKeyRelation();
StoreHelper.SetEntityInfoKeyRelationSelectFields(item, reader, "keyrelation");
sequence = (int)reader["keyrelationorder"];
item.EntityInfo = new EntityInfo();
StoreHelper.SetEntityInfoSelectFields(item.EntityInfo, reader, "info");
item.EntityAttributeInfo = new EntityAttributeInfo();
StoreHelper.SetEntityAttributeInfoSelectFields(item.EntityAttributeInfo, reader, "attribute");
itemList.Add(item);
}
await reader.CloseAsync();
}
}
foreach (var item in itemList)
{
await callback(item);
}
if (itemList.Count != pageSize)
{
break;
}
}
});
}
/// <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>
/// 根据实体类型查询
/// </summary>
/// <param name="entityType"></param>
/// <returns></returns>
public async Task <EntityInfo> QueryByEntityType(string entityType)
{
EntityInfo 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} FROM [EntityInfo] WHERE [entitytype] = @entitytype;", StoreHelper.GetEntityInfoSelectFields(string.Empty))
})
{
var parameter = new SqlParameter("@entitytype", SqlDbType.NVarChar, 256)
{
Value = entityType
};
commond.Parameters.Add(parameter);
await commond.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await commond.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
info = new EntityInfo();
StoreHelper.SetEntityInfoSelectFields(info, reader, string.Empty);
}
await reader.CloseAsync();
}
}
});
return(info);
}
public async Task QueryAll(Guid optionSetValueId, Func <OptionSetValueItem, Task> callback)
{
//获取只读连接字符串
var strConn = _entityMetadataConnectionFactory.CreateReadForEntityMetadata();
List <OptionSetValueItem> itemList = new List <OptionSetValueItem>();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, strConn, async (conn, transaction) =>
{
Int64?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} from OptionSetValueItem as item join OptionSetValueMetadata as metadata on item.optionsetvalueid=metadata.id where metadata.id=@optionsetvalueid order by item.[sequence]", StoreHelper.GetOptionSetValueItemSelectFields("item"), StoreHelper.GetOptionSetValueMetadataSelectFields("metadata"));
}
else
{
commond.CommandText = string.Format(@"select top (@pagesize) {0},{1} from OptionSetValueItem as item join OptionSetValueMetadata as metadata on listener.optionsetvalueid=metadata.id where metadata.id=@optionsetvalueid and item.[sequence]>@sequence order by item.[sequence]", StoreHelper.GetOptionSetValueItemSelectFields("item"), StoreHelper.GetOptionSetValueMetadataSelectFields("metadata"));
}
var parameter = new SqlParameter("@optionsetvalueid", SqlDbType.UniqueIdentifier)
{
Value = optionSetValueId
};
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 OptionSetValueItem();
StoreHelper.SetOptionSetValueItemSelectFields(item, reader, "item");
sequence = (Int64)reader["itemsequence"];
item.OptionSetValue = new OptionSetValueMetadata();
StoreHelper.SetOptionSetValueMetadataSelectFields(item.OptionSetValue, reader, "metadata");
itemList.Add(item);
}
await reader.CloseAsync();
}
}
foreach (var item in itemList)
{
await callback(item);
}
if (itemList.Count != pageSize)
{
break;
}
}
});
}