Passing objects in ArrayList 2

[kav123]

Can anyone tell me the best method to pick a record from a database using ExecuteReader, and passing it into an arraylist or any collection. I basically want to store all the records into a collection, arraylist or dictionary and then use that to display data.
Currently i have a class, with properties corresponding to the fields, storing them into those properties, and then adding every instance into an ArrayList. Is it possible to access individual properties from the object added to the arraylist, or am i completely in the wrong direction

While objReader.Read
mcp.SurveyQuestion = objReader.GetString(2)
mcp.Option1 = IIf(IsDBNull(objReader.GetValue(3)), " ", objReader.GetString(3))
mcp.Option2 = IIf(IsDBNull(objReader.GetValue(4)), " ", objReader.GetValue(4).ToString)
mcp.Option3 = IIf(IsDBNull(objReader.GetValue(5)), " ", objReader.GetValue(5).ToString)
mcp.Option4 = IIf(IsDBNull(objReader.GetValue(6)), " ", objReader.GetValue(6).ToString)

mcp.Option5 = IIf(IsDBNull(objReader.GetValue(7)), " ", objReader.GetValue(7).ToString)


questionairre.Add(mcp)
End While
where mcp is the instance of the class and questionairre is the Arraylist where i am adding the instance of the object.

Is this a feasible way??

 

[jmeckley]

assuming your using .net 2.0 I would use some form of a generice list.
1st. create your domain object.
2nd. map your datareader to the domain object
3rd. use your domain object to preform system logic.

your domain object could be a simple datatable.

using (IDbCommand command = connection.CreateCommand())
{
   command.Text = "select * from foo;"
   return new DataTable().Load(command.ExecuteReader();
}

I prefer to create my own custom objects and map the datarow to a domain object

IMapper<DataRow, Foo> mapper = new DataRowToFooMapper();

public IEnumerable<Foo> GetAList()
{
  DataTable table = new DataTable();
  using (IDbCommand command = connection.CreateCommand())
  {
     command.Text = "select * from foo;"
     table.Load(command.ExecuteReader();
  }

  foreach(DataRow row in table.Rows)
  {
     yield return mapper.MapFrom(row);
  }
}

where mapper preforms the logic of mapping a datarow to a new Foo object. i can then load this enumerable into a List<Foo> if more functionality is needed.

my mapper would look like this

public class DataRowToFooMapper : IMapper<DataRow, Foo>
{
   public Foo MapFrom(DataRow item)
   {
      return new Foo(item["column1"],item["column2"]);
   }
}

if your not fimilar with the yield keyword it's specific to IEnumerable.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[kav123]

Thanks so much, this will definitely get me closer. But i dont think there is a yield keyword in VB.Net, similar like you have in C#.
What i can do is add each row to a list item and then use that list. That should be fine right??

 

[jmeckley]

yes, if vb doesn't have yield load a list and return that

IList<Foo> foos = new List<Foo>();
foreach(DataRow row in Table.Rows)
{
   foos.Add(mapper.MapFrom(row);
}
return foos;

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[kav123]

Thats what i am trying to do but i am getting stuck somewhere well before that.

For Each row In table.Rows
qMapper.MapData(row)
Next

The above code snippet does not seem to be working. Row object gives a Null Reference Exception, and when i do a:
dim row as New DataRow, it says that this object is protected in this context. I even tried importing the DataRow specifically but to no avail.

Can you help me with that??

 

[ca8msm]

For Each row [!]As DataRow[/!] In table.Rows

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[kav123]

No use tried that. Now i am getting a null reference error. If i try to instantiate it with the new keyword, then it says, that DataRow is not accessible, because it is protected in this context.

I am doing a Dim row as new DataRow

 

[kav123]

I must add that i am using ExecuteReader to get data out and then loading it into a datatable object. No datasets are involved, is that why i am getting this??

 

[ca8msm]

I am doing a Dim row as new DataRow

If you use the method I showed above, you do not need a separate declaration.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[kav123]

Tried that still getting Null Reference Exception

 

[ca8msm]

You are doing it wrong then. But we can't see how from here...


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[kav123]

Here you go....

Dim objReader As OleDbDataReader

Dim colquestion As Integer
Dim colopt1 As Integer
Dim colopt2 As Integer
Dim colopt3 As Integer
Dim colopt4 As Integer
Dim colopt5 As Integer
Dim qId As Integer
Dim dSet As New DataSet
Dim table As New DataTable

Dim i As Integer

Try
objReader = conn.GetSurveyQuestions
table.Load(objReader)

For Each row as datarow In table.Rows
qMapper.MapData(row)
Next

colquestion = objReader.GetOrdinal("QuestionText")
colopt1 = objReader.GetOrdinal("Option1")
colopt2 = objReader.GetOrdinal("Option2")
colopt3 = objReader.GetOrdinal("Option3")
colopt4 = objReader.GetOrdinal("Option4")
colopt5 = objReader.GetOrdinal("Option5")
qId = objReader.GetOrdinal("QuestionID")

table.Load(objReader)

conn.CloseConnection()

Catch ex As Exception
Throw ex
conn.CloseConnection()
End Try

 

[kav123]

Ok, i think the problem may be here. I am passing the row object to this function to add it to a list. I think this where it throws up the error.

Public Sub MapData(ByVal row As DataRow)
QuestionsList.Add(row)
End Sub

 

[kav123]

Is this the correct way to pass objects between methods

 

[jmeckley]

a couple things.
1. i'm assuming conn is the connection object. so what is the method GetSurveyQuestions?
2. your loading the same table with the reader multiple times. why?
3. your then assigning values to basic integer types. why?
4. your catching an exception only to throw it... before closing it... right idea, wrong implementation.
5. i don't see a command object anywhere... this is object which should be querying the database.

my methods above using mapper would look like this. (it's c#)

public class QuestionRepository
{

private readonly IMapper<DataRow, Question> mapper;

public QuestionRepository(IMapper<DataRow, Question> mapper)
{
   this.mapper = mapper;
}

public IEnumerable<Question> function GetSurveyQuestions()
{
   DataTable table = new DataTable();
   using(IDbConnection cnn = new SqlConnection())
   {
      using(IDbCommand cmd = cnn.CreateCommand())
      {
         cmd.CommandText = "select * from foo";
         table.Load(cmd.ExecuteReader());
      }
   }

   foreach(DataRow row in table.Rows)
   {
      yield return mapper.MapFrom(row);
   }
}

}

my mapper would look like this

public interface IMapper<Input, Output>
{
   Output MapFrom(Input item);
}

public class DataRowToQuestionMapper : IMapper<DataRow, Question>
{
   public Question MapFrom(DataRow item)
   {
      return new Question(row["column"],(int)row["column"],(DateTime)row["column"]);
   }
}

the client could could then access it like this

MyGridView.DataSource = new QuestionRepository(new DataRowToQuestionMapper()).GetSurveyQuestions();
MyGridView.DataBind();

note the use of the using blocks. this is a simpler syntax which compile to a try/finally block.

for future reference the try/catches are all the same

IDisposable something = new SomeDisposableObject();
try
{
}
catch
{
   throw;
}
finally
{
   something.Dispose();
}

IDisposable something = new SomeDisposableObject();
try
{
}
finally
{
   something.Dispose();
}

using (IDisposable something = new SomeDisposableObject())
{
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[kav123]

GetSurveyQuestions, is the function which connects to the database. I am using the command object there, and that function returns the reader object. The integer variables are storing the ordinal numbers of the different columns. I may remove it. The multiple use of objReader, sorry it was actually commented in the code, however, i removed it for some reasong before pasting it.

But i have tested it further, i think the FOr Each loop is working fine, the program crashes, when the row object is passed to the MapData function which is in a different class.

Dont know what to do???:-(

 

[ca8msm]

Why pass the row object, and not the values from the row?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[kav123]

You read my mind there, i'll try that

 

[kav123]

This is getting really frustrating now.... Still getting a null reference exception. Here is the code which reads the rows from the table and puts in the constructor of a class.
Then i am passing that object to be added to the questionmapper class. Here is the code:

For Each row As DataRow In table.Rows

mcp = New MultipleChoiceRadio(row.Item(colquestion), IIf(IsDBNull(row.Item(colopt1)), " ", row.Item(colopt1)), IIf(IsDBNull(row.Item(colopt2)), " ", row.Item(colopt2)), IIf(IsDBNull(row.Item(colopt3)), " ", row.Item(colopt3)), IIf(IsDBNull(row.Item(colopt4)), " ", row.Item(colopt4)), IIf(IsDBNull(row.Item(colopt5)), " ", row.Item(colopt5)), row.Item(qId))

qMapper = New QuestionMapper(mcp)
i = i + 1

Next

The constructor for the qmapper class is:
Public Sub New(ByVal q As MultipleChoiceRadio)
QuestionsList.Add(q)
End Sub

I get an error at QuestionsList.Add(q).
QuestionsList is an ArrayList

 

[ca8msm]

I get an error at QuestionsList.Add(q).

So which one is null? QuestionsList or q?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]