I’m trying to fill DataSet which contains 2 tables with one to many relationship. I’m using DataReader to achieve this :
public DataSet SelectOne(int id) { DataSet result = new DataSet(); using (DbCommand command = Connection.CreateCommand()) { command.CommandText = "select * from table1"; var param = ParametersBuilder.CreateByKey(command, "ID", id, null); command.Parameters.Add(param); Connection.Open(); using (DbDataReader reader = command.ExecuteReader()) { result.MainTable.Load(reader); } Connection.Close(); } return result; }
But I’ve got only one table filled up. How do I achieve my goal – fill both tables?
I would like to use DataReader instead DataAdapter, if it possible.
Advertisement
Answer
If you are issuing a single command with several select statements, you might use NextResult method to move to next resultset within the datareader: http://msdn.microsoft.com/en-us/library/system.data.idatareader.nextresult.aspx
I show how it could look bellow:
public DataSet SelectOne(int id) { DataSet result = new DataSet(); using (DbCommand command = Connection.CreateCommand()) { command.CommandText = @" select * from table1 select * from table2 "; var param = ParametersBuilder.CreateByKey(command, "ID", id, null); command.Parameters.Add(param); Connection.Open(); using (DbDataReader reader = command.ExecuteReader()) { result.MainTable.Load(reader); reader.NextResult(); result.SecondTable.Load(reader); // ... } Connection.Close(); } return result; }