import Access databae to a list

[rubenrav]

I'm writing a program has access to a database (a database where an other software stores data that users type). This database has several colums, but in my code, i need to use the two columns that describes where the data is from, and the the date when the data where added. And then my intensjions is that the user type in on place (where the data is from) and, then the program goes trough the database and looks for the oldest dates in that perticular place and places around.

Here is an example of my database:

Area, Section, MeasureDate
1 12 12.05.2009
1 13 15.05.2009
1 14 17.05.2009
1 15 18.05.2009
1 16 19.05.2009

I know how to add the database as a dataset, but not sure how to continue.
If the user now types inn 12 in of my textBoxes and press calculate.

the code first make a list (that contains 12,13,14,15) and then i want it to look for this numbers in the dataset and then which section have the oldest measurment and need "most" to be measured now.

so of this example, my form should give out:

Section 13 at 1 was last measured 12.05.2009 and needs to be measured now.

I'm using Microsoft visual studio 2010, my dataset have the name car_mosDataSet.xsd in the solution explorer.

I would be very grateful for any help, and if i should give more information, just give me a hint.

[thines01]

Are you familiar with LINQ?

[rubenrav]

Well i've read about it in this guide:

http://www.codeproject.com/KB/linq/LINQSQLCS.aspx

So i don't need to add local database? Just a linq to sql class?

[rubenrav]

I've tried to make a linq to sql class, but i can't drag my table from server explorer. I just get the message "The selected object(s) use an unsopported data provider". Is this because i'm having a MS access database?

[thines01]

I'm not talking about Linq to SQL (that's for SQL Server). I mean Linq to Objects. I should have specified.
If you extract this data from the DB into a custom object, you can use Linq (to Objects) to find your data quickly.

[thines01]

Here's a partial example:
If you have your Area/Section/MeasureDate (ASM) in a collection (datatable, list, etc.)
and your section could be represented as an integer,
and your MeasureDate could be represented as a DateTime
you could do something like this:

// get max date in target range
         DateTime maxDate =
         (
            from a in m_lstASM 
            where a.intSection >= intUserSelectedSection
            select a.dtMeasureDate
         ).Max(); // << returns ONE value

         // CAsm represents a class or row returning the chosen ASM
         // get record in target range that matches target date
         CAsm asmFound =
         (
            from asm in m_lstASM
            where
               asm.intSection >= intUserSelectedSection
               && asm.dtMeasureDate.Equals(maxDate)
            select asm
         ).FirstOrDefault(); // << returns ONE value

[rubenrav]

Here's a partial example:
If you have your Area/Section/MeasureDate (ASM) in a collection (datatable, list, etc.)
and your section could be represented as an integer,
and your MeasureDate could be represented as a DateTime
you could do something like this:

// get max date in target range
         DateTime maxDate =
         (
            from a in m_lstASM 
            where a.intSection >= intUserSelectedSection
            select a.dtMeasureDate
         ).Max(); // << returns ONE value

         // CAsm represents a class or row returning the chosen ASM
         // get record in target range that matches target date
         CAsm asmFound =
         (
            from asm in m_lstASM
            where
               asm.intSection >= intUserSelectedSection
               && asm.dtMeasureDate.Equals(maxDate)
            select asm
         ).FirstOrDefault(); // << returns ONE value

Well yes thank you so much. I think this is just what i needed. Now i first need to work on how to get my database as data this code can work whit. I've got some help other place and i have wrote thise code:

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\*fileLocation*\car_mos.accdb;Persist Security Info=False";
            string queryString =
                "Select dbo_SectionMeasure.BakingFurnaceNo, dbo_SectionMeasure.SectionNo, dbo_SectionMeasure.MeasureDate, dbo_SectionMeasure.IsLastValue  from dbo_SectionMeasure WHERE (((dbo_SectionMeasure.IsLastValue)=True)); ";
            OleDbConnection connection = new OleDbConnection(connectionString);
            OleDbCommand command = connection.CreateCommand();
            command.CommandText = queryString;
            try
            {
                connection.Open();
                OleDbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    string date = reader[2].ToString();
                    DateTime dt = Convert.ToDateTime(date);
                    System.TimeSpan diff1 = DateTime.Today.Subtract(dt);



                    richTextBox1.Text = richTextBox1.Text + " FurnaceNo " + reader[0].ToString() + "      Section: " + reader[1].ToString() + "       Mmeasurdate " + reader[2].ToString() + "       Days since last measurment: " + diff1.Days + "\n";
                }

                reader.Close();
            }
            catch (Exception ee)
            {
                richTextBox1.Text = ee.ToString();
                throw;
            }

with this code i get my listbox filled with all the data from the database with some text between.

But thanks a lot for the help and with this reply you gave me and the message i think i can figure out something!

[thines01]

I sent you a code sample that shows how I would load data from ANY database into (first) a collection. Once it's in the "master", you can decide where you want it to go or how it will be used.
I don't tie the data gathering to the display because at some point, I might want that data again in a different type of application.

The pattern I use has:
1) The object
2) The "master" -- collection of those objects
3) The Loader (that loads that collection)
4) (optional) The Archiver that Loads and Stores the collection

This might seem like overkill (at first), but I've never needed another pattern.

Let me know what you think of the sample.

I use an interface to ensure the classes adhere to the same functionality.
If I need to load multiple data sources, I can put empty masters in a list and call the .Load() method in a "ForEach" loop.

In another class (not used in this example), I even go a step further for loading multiple masters.

[thines01]

The code I sent assumes your Area and Section are integers and that MeasureDate is a DateTime.