我可以使用什么强类型数据结构来保存具有不同形状的多个RecordSet的集合 [英] What Strongly-Typed Data-Structure can I use to hold a collection of multiple RecordSets with different shape
问题描述
应我原始问题的回答者的要求我被要求重新写出这个问题，以求达到实际需求的最低水平。
我可以使用什么强类型数据结构来保存多个 RecordSet 的集合，其中每个 RecordSet 将包含可能是与其他 RecordSets 形状不同？
此需求是由处理从存储的数据返回的数据驱动的通过 DbDataReader 进行操作。 存储过程可能具有多个 RecordSets ，每个 RecordSet 返回不同的列和列数。
将有 DTO 类代表相应数据集的每个行。这些在编译时是已知的。我需要一个可以容纳多个 RecordSets 的数据结构，并且对于每个 RecordSet 都容纳一个 DTO ，它们表示从返回的行RecordSet 。
示例存储过程：
创建程序[dbo]。[MultipleRecordSetStoredProcedure]
@Id INT
，@Name VARCHAR（20）
，@Active BIT
，@Price DECIMAL（10，4）
，@UniqueIdentifier UNIQUEIDENTIFIER
，@Count TINYINT
AS
BEGIN
/ *第一个记录集* /
选择
@Id AS Id
，@Name AS名称
UNION
SELECT
17 AS ID
，'Bill'AS名称;
/ *第二记录集* /
SELECT
@Name AS Name，
@ Active as Active
UNION
SELECT
'Bill'AS名称
，CAST（0 AS BIT）为有效；
/ *第三记录集* /
SELECT
@UniqueIdentifier AS UNIQUEIDENTIFIER
，@Count AS Count
UNION
SELECT
NEWID（）作为唯一身份
，CAST（10作为TINYINT）作为计数；
END
示例调用代码：
DbConnection连接= CreateConnection（）;
CommandBehavior commandBehavior = CommandBehavior.Default;
//使用（DbCommand命令= connection.CreateStoredProcedureCommand（
procedureName，
procedureParameters，
commandTimeout，
transaction））
{
//使用（DbDataReader reader = command.ExecuteReader（commandBehavior））调用命令
来填充DataReder /遍历每个结果集...
do
{
//逐行处理结果集
而（reader.Read（））
{
//将数据读入DataStructure
}
} while（reader.NextResult（））;
}
}
此处的DTO示例：
内部类MultipleRecordSetStoredProcedureReturnType1
{
public int Id {get;组; }
公共字符串Name {get;组; }
}
内部类MultipleRecordSetStoredProcedureReturnType2
{
public bool Active {get;组; }
公用十进制小数{get;组; }
}
内部类MultipleRecordSetStoredProcedureReturnType3
{
public Guid UniqueIdentifier {get;组; }
public Byte Count {get;组; }
}
理想情况下，我不需要对象列表或动力学，但列出了记录集内容的DTO。希望这会更好地阐明我的原始问题。
我不确定返回
公共类RecordSetContainer
{
public RecordSetContainer（）
{
RecordSet1 = new List< MultipleRecordSetStoredProcedureReturnType1>（）;
RecordSet2 = new List< MultipleRecordSetStoredProcedureReturnType2>（）;
RecordSet3 = new List< MultipleRecordSetStoredProcedureReturnType3>（）;
}
public List< MultipleRecordSetStoredProcedureReturnType1> RecordSet1 {获取；组; }
public List< MultipleRecordSetStoredProcedureReturnType2> RecordSet2 {获取;组; }
public List< MultipleRecordSetStoredProcedureReturnType3> RecordSet3 {获取;组; }
}
如果您想进一步提高数据库代码的通用性您可以按照以下方式做些事情。 IList>> [] recordSetPropertiesInOrder）
其中T：class，new（）
{
var outputType = typeof（T）;
var output = new T（）;
// DbConnection&隐藏命令设置
var recordSetNumber = 0;
do
{
var outputRecordSetSetPropertyName =
（（（MemberExpression）recordSetPropertiesInOrder [recordSetNumber] .Body）.Member.Name;
var dtoList =（IList）outputType.GetProperty（outputRecordSetPropertyName）.GetValue（output）;
var dtoListType = dtoList.GetType（）。GetGenericArguments（）[0];
而（reader.Read（））
{
var item = Activator.CreateInstance（dtoListType）;
var propertiesToWrite = dtoListType.GetProperties（）。Where（p => p.CanWrite）;
foreach（propertiesToWrite中的var属性）
{
property.SetValue（
item，
Convert.ChangeType（reader [property.Name]，属性。财产类型））;
}
dtoList.Add（item）;
}
recordSetNumber ++;
} while（reader.NextResult（））;
返回输出；
}
我承认的不是最漂亮或可读的代码，但它应该允许您调用任何多记录集存储过程，只要属性名称与列名称匹配即可。此代码的缺点是，尽管编译器将强制您仅在表达式中选择 IList 属性，但无法确保选择通用列表（该方法本身实际上是
对于您的示例情况，该方法将按以下方式调用
CallMultipleRecordSetStoredProcedure< RecordSetContainer>（
rsc => rsc.RecordSet1，
rsc => rsc.RecordSet2，
rsc => rsc.RecordSet3）;
At the request of a responder to my original question here, I have been asked to reword the question in an effort to get to the bottom of the actual requirement.
What Strongly-Typed Data-Structure can I use to hold a collection of multiple RecordSets where each RecordSet will contain rows that may be of a different shape to the other RecordSets?
This need is driven by the need to handle the data coming back from a Stored Procedure via a DbDataReader. The Stored Procedure may have multiple RecordSets, with each RecordSet returning different columns and number of columns.
There will be DTO classes to represent each row of the respective data sets. These are known at compile time. What I need is a data structure that can hold the multiple RecordSets, and for each RecordSet hold the DTOs representing rows returned from the RecordSet.
Example Stored procedure:
CREATE PROCEDURE [dbo].[MultipleRecordSetStoredProcedure]
@Id INT
, @Name VARCHAR(20)
, @Active BIT
, @Price DECIMAL(10, 4)
, @UniqueIdentifier UNIQUEIDENTIFIER
, @Count TINYINT
AS
BEGIN
/* First Record Set */
SELECT
@Id AS Id
, @Name AS Name
UNION
SELECT
17 AS Id
, 'Bill' AS Name;
/* Second Record Set */
SELECT
@Name AS Name,
@Active as Active
UNION
SELECT
'Bill' AS Name
, CAST(0 AS BIT) AS Active;
/* Third Record Set */
SELECT
@UniqueIdentifier AS UNIQUEIDENTIFIER
, @Count AS Count
UNION
SELECT
NEWID() AS UNIQUEIDENTIFIER
, CAST(10 AS TINYINT) AS Count;
END
Example calling code:
DbConnection connection = CreateConnection();
CommandBehavior commandBehavior = CommandBehavior.Default;
// Create a command to execute the stored storedProcedure
using (DbCommand command = connection.CreateStoredProcedureCommand(
procedureName,
procedureParameters,
commandTimeout,
transaction))
{
// Populate a DataReder by calling the command
using (DbDataReader reader = command.ExecuteReader(commandBehavior))
{
// Iterate through each result set...
do
{
// Process the result set line by line
while (reader.Read())
{
// Read data into DataStructure
}
} while (reader.NextResult());
}
}
Example DTOs here:
internal class MultipleRecordSetStoredProcedureReturnType1
{
public int Id { get; set; }
public string Name { get; set; }
}
internal class MultipleRecordSetStoredProcedureReturnType2
{
public bool Active { get; set; }
public decimal Decimal { get; set; }
}
internal class MultipleRecordSetStoredProcedureReturnType3
{
public Guid UniqueIdentifier { get; set; }
public Byte Count { get; set; }
}
Ideally I do not want a list of objects or dynamics but a list of my DTOs for the recordset contents. Hopefully this will better clarify my original question.
I'm not sure it makes sense to return a List containing disparate results. I'd probably create a wrapper class to group your dto objects like below.
public class RecordSetContainer
{
public RecordSetContainer()
{
RecordSet1 = new List<MultipleRecordSetStoredProcedureReturnType1>();
RecordSet2 = new List<MultipleRecordSetStoredProcedureReturnType2>();
RecordSet3 = new List<MultipleRecordSetStoredProcedureReturnType3>();
}
public List<MultipleRecordSetStoredProcedureReturnType1> RecordSet1 { get; set; }
public List<MultipleRecordSetStoredProcedureReturnType2> RecordSet2 { get; set; }
public List<MultipleRecordSetStoredProcedureReturnType3> RecordSet3 { get; set; }
}
If you wanted to take the db code a step further to make it generic you could do something along these lines.
public T CallMultipleRecordSetStoredProcedure<T>(
params Expression<Func<T, IList>>[] recordSetPropertiesInOrder)
where T : class, new()
{
var outputType = typeof (T);
var output = new T();
// DbConnection & Command setup hidden
var recordSetNumber = 0;
do
{
var outputRecordSetPropertyName =
((MemberExpression) recordSetPropertiesInOrder[recordSetNumber].Body).Member.Name;
var dtoList = (IList) outputType.GetProperty(outputRecordSetPropertyName).GetValue(output);
var dtoListType = dtoList.GetType().GetGenericArguments()[0];
while (reader.Read())
{
var item = Activator.CreateInstance(dtoListType);
var propertiesToWrite = dtoListType.GetProperties().Where(p => p.CanWrite);
foreach (var property in propertiesToWrite)
{
property.SetValue(
item,
Convert.ChangeType(reader[property.Name], property.PropertyType));
}
dtoList.Add(item);
}
recordSetNumber++;
} while (reader.NextResult());
return output;
}
Not the prettiest or readable code I admit but it should allow you to call any multi record set stored procedure as long as the property names match the column names. One downside with this code is that while the compiler will enforce you to only select IList properties in you expressions it is unable to ensure you pick a generic list (which the method itself actually requires).
For your example case the method would be called as below
CallMultipleRecordSetStoredProcedure<RecordSetContainer>(
rsc => rsc.RecordSet1,
rsc => rsc.RecordSet2,
rsc => rsc.RecordSet3);
这篇关于我可以使用什么强类型数据结构来保存具有不同形状的多个RecordSet的集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
