none
How can I search in a DataSet? RRS feed

  • Question

  • Hi,

    I have a normal XML file, something like this:

    <?xml version="1.0" encoding="UTF-8"?>

    <users>

    <user type="admin">
    <userid>7</userid>
    <username>t</username>
    <datejoined>t</datejoined>
    <blablabla>1195841296</blablabla>
    </user>

    <user type="mod">
    <userid>7</userid>
    <username>t</username>
    <datejoined>t</datejoined>
    <blablabla>1195841296</blablabla>
    </user>

    </users>

    I loaded the XML file above into a DataSet:

    DataSet dsUsers = new DataSet();
    dsUsers.ReadXml("data.xml", XmlReadMode.InferSchema);

    So now I reach my rows as follow:

    DataRow dr = dsUsers.Tables["tblname"].Rows[recNum];

    dr["type"]
    dr["title"]

    The question is, how can I filter the records?

    1. Get the users where their "type" (which is an attribute) is "admin" (<user type="admin">)

    2.  Get the users where their "username" is "t"

    Please help.
    Monday, December 3, 2007 11:17 PM

Answers

  •  

    Hi there,

     

    In .NET 3.0, you can even simply what you need even further... You can query the XML file directly using LINQ.

     

    Code Block

    XElement xElem = XElement.Load(xmlPath);

     

    var query = from c in xElem.Descendants("user")

                select c;

     

    foreach (var q in query)

        Console.WriteLine("UserID: {0} Access Level: {1}",

                           q.Element("userid").Value,

                           q.Attribute("type").Value);

     

     

    What your doing above is querying the XML document for what you need.. You can query the DataSet as well... by using the DataSet object instead of xElem.Descendants("user")

     

    The output would be:

     

    UserID: User1 Access Level: admin

    UserID: User7 Access Level: mod

     

    After you queried your XML, you can set it to a Binding Source in your DataSource by doing

     

    Code Block

    mybindingsource.DataSource = query;

     

     

    That is all... Easy as cake Stick out tongue

     

    BUT you requested DataSet, I explained how you can query DataSet with LINQ , or XML with LINQ, but those require .NET 3.x, but maybe you are using .NET 2.0. The only way to do it (as far as I know) is the following:

     

    Code Block

    DataSet dsUsers = new DataSet();

    dsUsers.ReadXml(xmlPath, XmlReadMode.InferSchema);

     

    Console.WriteLine("----------------------------");

    Console.WriteLine("TABLE NAMES (which is users)");

    Console.WriteLine("----------------------------");

    // Which is equivalent to the XML Node ...

    // (at most 1 table since we are dealing with XML)

    for (int i = 0; i < dsUsers.Tables.Count; i++)

    {

        DataTable table = dsUsers.Tables[i];

        Console.WriteLine("Table Name: {0}",table.TableName);

    }

     

    Console.WriteLine("----------------------------");

    Console.WriteLine("ALL ROWS (which is user)");

    Console.WriteLine("----------------------------");

    // Lets see what that table has...

    for (int i = 0; i < dsUsers.Tables[0].Rows.Count; i++)

    {

        DataRow row = dsUsers.Tables[0].Rows[i];

        foreach (object o in row.ItemArray)

        {

            string oStr = o as string;

            Console.Write(" {0} |", oStr);

        }

        Console.WriteLine("");

    }

     

    Console.WriteLine("----------------------------");

    Console.WriteLine("ALL USERS (who are admin)");

    Console.WriteLine("----------------------------");

    // So if you want to query for your type, you do the following ... which is long ..

    // Lets see what that table has...

    for (int i = 0; i < dsUsers.Tables["user"].Rows.Count; i++)

    {

        DataRow row = dsUsers.Tables["user"].Rows[i];

        Console.WriteLine(" {0} ", (string)row["type"]);

        if ((string)row["type"] == "admin")

        {

            foreach (object o in row.ItemArray)

            {

                string oStr = o as string;

                Console.Write(" {0} |", oStr);

            }

            Console.WriteLine("");

        }
    }

     

     

    Sure, a person could make an object of Users and fill that object up in a List<Users> to make it more readable and usable within your code!

     

    But notice the difference between LINQ and DATASET ? The XML Search approach is even bigger... I could post that as well if you want..

     

    I hope this helped...

     

    Enjoy

    Tuesday, December 4, 2007 3:40 AM
  • LINQ to would be the simplest solution, if you are able to target .NET 3.5.

     

    1.

    Code Block

    var q = from user in dsUsers

            where user.type = "admin"

            select user;

    foreach (var user in q)

       // do something

     

     

     

    2.

    Code Block

    var q = from user in dsUsers

            where user.username = "t"

            select user;

    foreach (var user in q)

       // do something

     

     

     

    Check out LINQ in the MSDN library, there is a ton of info out there now (it's getting very popular).

    Tuesday, December 4, 2007 3:41 AM
  • Hi,

     

    If it is not applicable to change the target framework version (which is often the case), you can use the DataView class to filter, sort, search, edit and navigate a DataTable, just like a view in SQL Server or other databases.

     

    To filter the rows of the table, you can set the RowFilter property of the DataView to your search condition:

        DataSet ds = new DataSet();

        //...

     

        DataView view1 = new DataView();

        view1.Table = ds.Tables[0];

        view1.RowFilter = "type='admin'";

       

        foreach(DataRow row in ((DataTable)view1).Rows)

        {

            //...

        }

     

        DataView view2 = new DataView();

        view2.Table = ds.Tables[0];

        view2.RowFilter = "username='t'";

       

        //...

     

    In addition, the dataView can be bound to various data controls such as GridView conveniently.

     

    Best Regards

    Chunsheng Tang

     

     

    Wednesday, December 5, 2007 7:15 AM

All replies

  •  

    Hi there,

     

    In .NET 3.0, you can even simply what you need even further... You can query the XML file directly using LINQ.

     

    Code Block

    XElement xElem = XElement.Load(xmlPath);

     

    var query = from c in xElem.Descendants("user")

                select c;

     

    foreach (var q in query)

        Console.WriteLine("UserID: {0} Access Level: {1}",

                           q.Element("userid").Value,

                           q.Attribute("type").Value);

     

     

    What your doing above is querying the XML document for what you need.. You can query the DataSet as well... by using the DataSet object instead of xElem.Descendants("user")

     

    The output would be:

     

    UserID: User1 Access Level: admin

    UserID: User7 Access Level: mod

     

    After you queried your XML, you can set it to a Binding Source in your DataSource by doing

     

    Code Block

    mybindingsource.DataSource = query;

     

     

    That is all... Easy as cake Stick out tongue

     

    BUT you requested DataSet, I explained how you can query DataSet with LINQ , or XML with LINQ, but those require .NET 3.x, but maybe you are using .NET 2.0. The only way to do it (as far as I know) is the following:

     

    Code Block

    DataSet dsUsers = new DataSet();

    dsUsers.ReadXml(xmlPath, XmlReadMode.InferSchema);

     

    Console.WriteLine("----------------------------");

    Console.WriteLine("TABLE NAMES (which is users)");

    Console.WriteLine("----------------------------");

    // Which is equivalent to the XML Node ...

    // (at most 1 table since we are dealing with XML)

    for (int i = 0; i < dsUsers.Tables.Count; i++)

    {

        DataTable table = dsUsers.Tables[i];

        Console.WriteLine("Table Name: {0}",table.TableName);

    }

     

    Console.WriteLine("----------------------------");

    Console.WriteLine("ALL ROWS (which is user)");

    Console.WriteLine("----------------------------");

    // Lets see what that table has...

    for (int i = 0; i < dsUsers.Tables[0].Rows.Count; i++)

    {

        DataRow row = dsUsers.Tables[0].Rows[i];

        foreach (object o in row.ItemArray)

        {

            string oStr = o as string;

            Console.Write(" {0} |", oStr);

        }

        Console.WriteLine("");

    }

     

    Console.WriteLine("----------------------------");

    Console.WriteLine("ALL USERS (who are admin)");

    Console.WriteLine("----------------------------");

    // So if you want to query for your type, you do the following ... which is long ..

    // Lets see what that table has...

    for (int i = 0; i < dsUsers.Tables["user"].Rows.Count; i++)

    {

        DataRow row = dsUsers.Tables["user"].Rows[i];

        Console.WriteLine(" {0} ", (string)row["type"]);

        if ((string)row["type"] == "admin")

        {

            foreach (object o in row.ItemArray)

            {

                string oStr = o as string;

                Console.Write(" {0} |", oStr);

            }

            Console.WriteLine("");

        }
    }

     

     

    Sure, a person could make an object of Users and fill that object up in a List<Users> to make it more readable and usable within your code!

     

    But notice the difference between LINQ and DATASET ? The XML Search approach is even bigger... I could post that as well if you want..

     

    I hope this helped...

     

    Enjoy

    Tuesday, December 4, 2007 3:40 AM
  • LINQ to would be the simplest solution, if you are able to target .NET 3.5.

     

    1.

    Code Block

    var q = from user in dsUsers

            where user.type = "admin"

            select user;

    foreach (var user in q)

       // do something

     

     

     

    2.

    Code Block

    var q = from user in dsUsers

            where user.username = "t"

            select user;

    foreach (var user in q)

       // do something

     

     

     

    Check out LINQ in the MSDN library, there is a ton of info out there now (it's getting very popular).

    Tuesday, December 4, 2007 3:41 AM
  • Hi,

     

    If it is not applicable to change the target framework version (which is often the case), you can use the DataView class to filter, sort, search, edit and navigate a DataTable, just like a view in SQL Server or other databases.

     

    To filter the rows of the table, you can set the RowFilter property of the DataView to your search condition:

        DataSet ds = new DataSet();

        //...

     

        DataView view1 = new DataView();

        view1.Table = ds.Tables[0];

        view1.RowFilter = "type='admin'";

       

        foreach(DataRow row in ((DataTable)view1).Rows)

        {

            //...

        }

     

        DataView view2 = new DataView();

        view2.Table = ds.Tables[0];

        view2.RowFilter = "username='t'";

       

        //...

     

    In addition, the dataView can be bound to various data controls such as GridView conveniently.

     

    Best Regards

    Chunsheng Tang

     

     

    Wednesday, December 5, 2007 7:15 AM