none
How can I filter data retrieved from the database based on data held in a dataset RRS feed

  • Question

  • I have retrieved some data from our database that identifies vacant positions and hold this data in a dataset. The table is defined like this:
      <xs:element name="VacDataSet">
    <xs:complexType>
    <xs:choice minOccurs="0" maxOccurs="unbounded">
    <xs:element name="VacantPositions">
    <xs:complexType>
    <xs:sequence>
    <xs:element name="PositionID"></xs:element>
    <xs:element name="PositionName"></xs:element>
    <xs:element name="PositionStatus"></xs:element>
    <xs:element name="VacantFrom"></xs:element>
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    </xs:choice>
    </xs:complexType>
    </xs:element>

    Each of the positions identified and held in this table then needs additional information based on the position ID  and the vacant from date.

    To get the data I'm after I could retrieve the data for all positions and use a relation in the dataset to filter it or execute a query for each row of position data in my table. These will both work but seem inefficient.


    Is there correct/better way of getting the additional data using the contents of the data table as a filter?
    There are only 10 types of people. Those who understand binary and those who don't
    Wednesday, December 16, 2009 2:08 PM

All replies

  • Not sure if this is what you're after, but you can select out from a filled datatable.

            static void Main(string[] args)
            {
                
                DataTable dt = GetTable();
                Console.WriteLine("Unfiltered");
                foreach (DataRow r in dt.Rows)
                {
                    Console.WriteLine("{0} {1} {2}", r[0].ToString(), r[1].ToString(), r[2].ToString());
                }
    
                DataRow[] rows = dt.Select("PID > 2 AND VF = '2009-12-20'");
                Console.WriteLine("\nFiltered");
                foreach (DataRow r in rows)
                {
                    Console.WriteLine("{0} {1} {2}", r[0].ToString(), r[1].ToString(), r[2].ToString());
                }
            }
    
            private static DataTable GetTable()
            {
                DataTable dt = new DataTable("MyTable");
                
                dt.Columns.Add("PID", typeof(int));
                dt.Columns.Add("PN", typeof(string));
                dt.Columns.Add("VF", typeof(DateTime));
    
                dt.Rows.Add(new object[]{1, "Mike", DateTime.Today.AddDays(1)});
                dt.Rows.Add(new object[]{2, "John", DateTime.Today.AddDays(2)});
                dt.Rows.Add(new object[]{3, "Mary", DateTime.Today.AddDays(3)});
                dt.Rows.Add(new object[]{4, "Todd", DateTime.Today.AddDays(4)});
    
                return dt;
            }

    This should give the following output (if running it today (the datetime is causing this)):

    Unfiltered
    1 Mike 2009-12-17
    2 John 2009-12-18
    3 Mary 2009-12-19
    4 Todd 2009-12-20

    Filtered
    4 Todd 2009-12-20


    This posting is provided "AS IS" with no warranties.
    Wednesday, December 16, 2009 4:33 PM
  • Thanks for the response but it's not quite what I'm after as this filters data already in the datatable. I'm trying to get new data from the database based on the data held in columns in my datatable.

    I'm currently investigating this http://msdn.microsoft.com/en-us/library/bbw6zyha.aspx

    If I get it working I'll post my findings.

    There are only 10 types of people. Those who understand binary and those who don't
    Wednesday, December 16, 2009 4:53 PM

  • I've been trying to get this working but I'm not having any joy...
    private void RetrieveVacancyAdditionalPosData(VacDataSet TrentData)
    {
        try
        {
            // Ensure that we have some data to use as parameters
            if( TrentData.VacantPositions != null && TrentData.VacantPositions.Count > 0 )
            {
                // Create the connection
                using (IDbConnection conn = m_factory.GetConnection())
                {
                    // Create the command object
                    using (IDbCommand command = m_factory.GetCommand( this.m_cmdBuilder.PositionDetails , conn))
                    {
                        command.CommandTimeout = 300;
    
                        // Get the data adapter
                        using (DbDataAdapter da = m_factory.GetDataAdapter(command))
                        {
                            // Add the parameters from the vacant position table
                            command.Parameters.Add(m_factory.GetParameter("PositionID", "NVarChar", 8, "VacantPositions.PositionID"));
    
                            // Fill the datatable
                            da.Fill(TrentData, "PosDetails");
                        }
                    }
                }
            }
            else
            {
                Log.WriteLine(Category.Debug, "No vacant positions identified for data retrieval");
            }
        }
        catch (Exception ex)
        {
            Log.WriteLine(Category.Warning, "Failed to retrieve vacant position data", ex);
        }
    }
    

    To get the job done I've had to resort to using a for loop

    private void RetrieveVacancyAdditionalPosData(VacDataSet TrentData)
    {
        try
        {
            // Ensure that we have some data to use as parameters
            if( TrentData.VacantPositions != null && TrentData.VacantPositions.Count > 0 )
            {
                // Create the connection
                using (IDbConnection conn = m_factory.GetConnection())
                {
                    // Create the command object
                    using (IDbCommand command = m_factory.GetCommand( this.m_cmdBuilder.PositionDetails , conn))
                    {
                        command.CommandTimeout = 300;
    
                        // Get the data adapter
                        using (DbDataAdapter da = m_factory.GetDataAdapter(command))
                        {
                            // Iterate through the vacant positions getting details for each
                            foreach (VacDataSet.VacantPositionsRow vacantPos in TrentData.VacantPositions)
                            {
                                // Add the parameters from the vacant position table
                                command.Parameters.Add(m_factory.GetParameter("PositionID", vacantPos.PositionID));
    
                                // Fill the datatable
                                da.Fill(TrentData, "PosDetails");
    
                                // now remove the parameters ready for the next itteration
                                command.Parameters.Remove(":PositionID");
                            }
                        }
                    }
                }
            }
            else
            {
                Log.WriteLine(Category.Debug, "No vacant positions identified for data retrieval");
            }
        }
        catch (Exception ex)
        {
            Log.WriteLine(Category.Warning, "Failed to retrieve vacant position data", ex);
        }
    }
    

    There are only 10 types of people. Those who understand binary and those who don't
    Thursday, December 17, 2009 12:19 PM
  • Hi TeamWild,
    Iterating each row in the datatable then opening connection and closing on each iterated row is not efficient way to go. 

    What you can do is send your datatable data (entire datatable) to a #TempTable in your database http://www.sqlteam.com/article/temporary-tables Then create your select sql statement joined by by the #TempTable this will be treated as "Filter"


    Let me know if this works for you
     
    John
    Tuesday, January 5, 2010 11:13 AM