See SqlDataReader.NextResult (an SqlDataReader is returned from calling SqlCommand.ExecuteReader ): Advances the data reader to the next result [set], when reading the results of batch Transact-SQL statements.
Example:
string commandText = @"SELECT Id, ContactId
FROM dbo.Subscriptions;
SELECT Id, [Name]
FROM dbo.Contacts;";
List<Subscription> subscriptions = new List<Subscription>();
List<Contact> contacts = new List<Contact>();
using (SqlConnection dbConnection = new SqlConnection(@"Data Source=server;Database=database;Integrated Security=true;"))
{
dbConnection.Open();
using (SqlCommand dbCommand = dbConnection.CreateCommand())
{
dbCommand.CommandText = commandText;
using(SqlDataReader reader = dbCommand.ExecuteReader())
{
while(reader.Read())
{
subscriptions.Add(new Subscription()
{
Id = (int)reader["Id"],
ContactId = (int)reader["ContactId"]
});
}
// this advances to the next resultset
reader.NextResult();
while(reader.Read())
{
contacts.Add(new Contact()
{
Id = (int)reader["Id"],
Name = (string)reader["Name"]
});
}
}
}
}
Tools like "dapper" make this very easy, regardless of whether you use ad-hoc text queries or stored procedures; for example:
using(var multi = conn.QueryMultiple(sql, args))
{
var customers = multi.Read<Customer>().AsList(); // first grid
var regionName = multi.ReadFirstOrDefault<string>(); // second grid
var addresses = multi.Read<Address>().AsList(); // third grid
// todo: use those things
}
Individual grids can also be read without buffering (as an open IEnumerable<T> over the reader itself) via the optional parameters to Read[<T>] .
I am using a dictionary approach. You can use Newton Json to convert it to json. This way you are not tied to types and IDataRecord
public List<List<Dictionary<string, object>>> ExecuteSqlReader(string cmd, int commandTimeout = 30, CommandType commandType = CommandType.Text)
{
var sqlCmd = new SqlCommand(cmd);
var allRecord = new List<List<Dictionary<string, object>>>();
using (var connection = GetSqlConnection(commandTimeout, commandType, ref sqlCmd))
{
using (var reader = sqlCmd.ExecuteReader())
{
if (reader.HasRows)
{
var result = new List<Dictionary<string, object>>();
while (reader.Read())
{
result = GetTableRowData(reader);
}
allRecord.Add(result);
}
while (reader.NextResult())
{
if (reader.HasRows)
{
var result = new List<Dictionary<string, object>>();
while (reader.Read())
{
result = GetTableRowData(reader);
}
allRecord.Add(result);
}
}
}
}
return allRecord;
}
I call a sproc and get multiple result sets with object, so you end up with a
List<List<Dictionary<string, object>>>
In the multiResultsSet each results set is then
List<Dictionary<string, object>>
They can be cast to their type s and transformed into the models as needed.
After you set up the sproc command with everything needed, pass it to this:
private static List<List<Dictionary<string, object>>> ProcessReader(SqlCommand command)
{
var tables = new List<List<Dictionary<string, object>>>();
using (var reader = command.ExecuteReader())
{
do
{
var table = new List<Dictionary<string, object>>();
while (reader.Read())
table.Add(Read(reader));
tables.Add(table);
} while (reader.NextResult());
}
return tables;
}
and Read() is fairly straight forward.
private static Dictionary<string, object> Read(IDataRecord reader)
{
var row = new Dictionary<string, object>();
for (var i = 0; i < reader.FieldCount; i++)
{
var val = reader[i];
row[reader.GetName(i)] = val == DBNull.Value ? null : val;
}
return row;
}
Dim dt1, dt2, dt3As New DataTable
Dim command As SqlCommand
Dim adapter As New SqlDataAdapter
Dim ds As New DataSet
Dim Sql1, Sql2, Sql3 As String
Sql1 = "select id, CurName from Table1 where IS_Deleted=0 order by id"
Sql2 = "select id ,Item from Table2 order by id"
Sql3 = "select id ,SellPrice from Table3 order by id"
Try
conn1.Open()
command = New SqlCommand(Sql1, conn1)
command.CommandType = CommandType.Text
adapter.SelectCommand = command
adapter.Fill(ds, "dt1")
adapter.SelectCommand.CommandText = Sql2
adapter.Fill(ds, "dt2")
adapter.SelectCommand.CommandText = Sql3
adapter.Fill(ds, "dt3")
adapter.Dispose()
command.Dispose()
conn1.Close()
cmbCurrency.DataSource = ds.Tables("dt1")
cmbCurrency.DisplayMember = "CurName"
cmbCurrency.ValueMember = "id"
cmbCurrency.SelectedIndex = -1
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''|
cmbGroups.DataSource = ds.Tables("dt2")
cmbGroups.DisplayMember = "Item"
cmbGroups.ValueMember = "id"
cmbGroups.SelectedIndex = -1
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''|
cmbUnits.DataSource = ds.Tables("dt3")
cmbUnits.DisplayMember = "SellPrice"
cmbUnits.ValueMember = "id"
cmbUnits.SelectedIndex = -1
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''|
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
8条答案
按热度按时间93ze6v8z1#
See SqlDataReader.NextResult (an SqlDataReader is returned from calling SqlCommand.ExecuteReader ):
Advances the data reader to the next result [set], when reading the results of batch Transact-SQL statements.
Example:
Other examples:
vhmi4jdf2#
Create a
Stored Procedure
that has multiple selects, and fill theDataSet
.The returned dataset will have a
DataTable
in it's Tables array for each select in the stored procedure.llycmphe3#
Tools like "dapper" make this very easy, regardless of whether you use ad-hoc text queries or stored procedures; for example:
Individual grids can also be read without buffering (as an open
IEnumerable<T>
over the reader itself) via the optional parameters toRead[<T>]
.m2xkgtsf4#
I am using a dictionary approach. You can use Newton Json to convert it to json. This way you are not tied to types and IDataRecord
yshpjwxd5#
I call a sproc and get multiple result sets with object, so you end up with a
In the multiResultsSet each results set is then
They can be cast to their type s and transformed into the models as needed.
After you set up the sproc command with everything needed, pass it to this:
and Read() is fairly straight forward.
uurv41yg6#
This is what i have been using for returning multiple result sets.
Then I just inherit it like so:
vsmadaxz7#
Try this
For More Help http://vb.net-informations.com/dataset/dataset-multiple-tables-sqlserver.htm
axzmvihb8#