Helps with Datarelations with Data Access Application block. RRS feed

  • Question

  • User1510286622 posted

    Ok so i've downloaded the microsoft application blocks and so far I like what I see but I've come to a road block when I try to populate my dataset with with what I need to be two datatables.  Here's my attempt



    DataSet ds;
    ds = DatabaseFactory.CreateDatabase().ExecuteDataSet(CommandType.Text, "select * from countries");
    ds = DatabaseFactory.CreateDatabase().ExecuteDataSet(CommandType.Text, "select * from countries_states_cities");


    I am basically trying to have two tables created in my dataset each with the resultset of the above queries.  I want to then use a datarelation to join them.  Problem is, is that I dont think this code does what I want.  I suspect the latter line writes over the first and I only ever end up with one table.  Can someone point me in the right direction.

    Friday, June 10, 2005 7:39 AM

All replies

  • User-1027516314 posted
    I had tried something similar like what you did and ran into the same road block. I can for the time being growing my knowledge of the DAAB everyday, it's not possible to create a relationship with the code above. It frustrates me extensively of not being able to do such a common task the way we do with ADO.NET dataSet. After Google sometimes and still haven't found what I was looking for, it left me with no choice but to dive deep into their source code. To confirm my suspicion, the DAAB only deals with the SQL command you pass into it and return you either a dataSet in one table, xml, a big string, IDataReader as a result set. I hope someone would prove me wrong on this one. In order to build a DataRelation, I look for the overloaded db.ExecuteDataSet(....) that would take 2 specific parameters string[] TableNames and string[] SqlCommandText and they are not there. It doesn't make sense to return a dataSet and not allowing us to use its full ADO.NET feature. And what disappoints me even more is that we only need one dataTable but get the heavy duty dataSet in return.
    Tuesday, June 21, 2005 5:25 PM
  • User1510286622 posted

    Actually I found out how to do it,

    Some minor changes to the code.  I'm at work at the moment and don't have my laptop, but if you want the line change let me know and I'll be happy to send the solution on.

    Wednesday, June 22, 2005 12:31 AM
  • User1510286622 posted

    Heres the solution


    private DataTable getCities(int country_id)


    DataTable dt = DatabaseFactory.CreateDatabase().ExecuteDataSet(CommandType.Text, "select * from cities where country_id = "+ country_id).Tables[0];

    dt.TableName = "Cities";

    return dt;


    Wednesday, June 22, 2005 5:33 AM
  • User-1027516314 posted
    Thanks for the reply. The good thing is that we can get it to return a DataTable. But I still think the coding convention is very complicated and we still have to do a lot work to get where we want. My opinion tells me that any framework should be easy to use and not having us(consumers) running around and digging our head to figure out something simple and straighforward when we get it done with the current .NET library. This is the key point where many people will decide whether to adopt it or not. With all that said, I do give these folks a lot of credit by releasing the source code and I have learned a lot from reading it. 
    Wednesday, June 22, 2005 10:38 AM
  • User1510286622 posted
    Your correct, the solution is not inuitive nor conventional, i dont even know where i found it.
    Wednesday, June 22, 2005 11:21 PM
  • User915633546 posted
    I don't think the problem is with the framework, it's more so a lack of experience with the DS class.

    Try this instead.
    You can then add a relationship between the 2 tables if one should exists.  The .net documentation describes how to do this.  I've also seen it in many of the publications provided about .net
    DataSet ds = new DataSet();

    ds.Tables.Add("Country",  DatabaseFactory.CreateDatabase().ExecuteDataSet(CommandType.Text, "select * from countries"));
    ds.Tables.Add("Cities",   DatabaseFactory.CreateDatabase().ExecuteDataSet(CommandType.Text, "select * from countries_states_cities"));

    Thursday, June 23, 2005 3:07 PM
  • User-1027516314 posted
    Thank you. It's so obvious with DataSet. I had written so many lines like that in production code and it didn't even cross my mind in this situation. I guess that studying the framework blurs my mind in many ways.
    Thursday, June 23, 2005 8:59 PM
  • User915633546 posted

    I forgot to add in my message that inorder to access the tables you can do the following.



    or to access a row
    string something = ds.Tables["YourTableName"].Row[0].Column["City"].ToString();

    Friday, June 24, 2005 2:30 PM