[dhicks19]

Hi ,I am new to C#. I am currently working on a project which records water readings .

I have 3 tables created using SQL server:
AMREADING - - Will store AM reading,Date,Timestamp
PMREADING - Will store PM reading,Date,Timestamp
DAILYREADINGS - This will store yesterdays AM,PM & Todays AM reading along with dates and timestamps. It will also store the water usage totals.

I am using entity frameworks, so far I have figured out how to insert/delete a record in the tables. However I need to retrieve a record from the AMREADING table & PMREADING table so I can store in variables and then insert into DAILYREADINGSTABLE. From this I can work out how much water was used : night,day and the total water used.

I have read that I need to create a list which I have done below,

 public List<GLOBALVARIABLES> GetData(DateTime usedate)
        {
            using (Water.WaterEntities1 context = new Water.WaterEntities1())
            {
                var items = context.amreadings
                            .Where(a => a.datetaken == usedate)
                            .Select(row => new GLOBALVARIABLES { YDAYAMREADING = row.reading, YDAYTIME = row.timestamp, YDAYDATETAKEN = row.datetaken });

                return items.ToList<GLOBALVARIABLES>();




            }

I also have a global variables class :

  public class GLOBALVARIABLES
    {

        public static int thisreading;

        public static DateTime todaysdate = DateTime.Now.Date;
        public static string thistime = DateTime.Now.ToShortTimeString();
        public static int closeform = 0;





        public Int32 YDAYAMREADING { get; set; }
        public DateTime YDAYDATETAKEN { get; set; }
        public string YDAYTIME { get; set; }

     


    }
}

I just dont know how to access the data?
Any help is much appreciated.

[jhouns]

Without pushing anything, I would say you need to rethink your database structure somewhat, I'd rather help you deduce it on your own rather than outright tell you though. But aside from that googling "c# sql server query" gives a plethora of examples.
EDIT: Using entity framework, my answer is now irrelevant.

This post has been edited by jhouns: 02 December 2013 - 09:34 AM

[dhicks19]

jhouns, on 02 December 2013 - 08:22 AM, said:

Without pushing anything, I would say you need to rethink your database structure somewhat, I'd rather help you deduce it on your own rather than outright tell you though. But aside from that googling "c# sql server query" gives a plethora of examples.

Thanks for your reply. Could you elaborate a little on my database structure issue?

Thanks

[jhouns]

Well from my understanding you have three tables for the same data and some redundant columns. Timestamp typically is in the format "DD:MM:YYYY HH:MM:SS" if I'm not mistaken. So if you have the exact time of day the reading was taken, what can you calculate about the reading?

Say these were your imaginary readings (numbers made up):
5000 - 02/12/2013 08:32:33
6000 - 02/12/2013 17:24:15

[dhicks19]

From the time I can calculate the average water use per hour for example.

I have this project currently coded with visual fox pro.
I am basically just trying to re-write this in C# as its the simplest program I have.

Do you not have any input regarding the tolist? I have googled all day and thats all I can find, my problem is I dont know how to retrieve the data.

[jhouns]

Yes but you can also check whether it's in the morning or afternoon to determine if it's AM/PM, it means you could potentially replace those two columns with one.

Although you haven't actually asked about opinions on the structure, it was just a side observation. I had presumed that

Quote

my problem is I dont know how to retrieve the data

Meant that you don't know how to retrieve it from the database? Am I mistaken?

[dhicks19]

jhouns, on 02 December 2013 - 09:10 AM, said:

Yes but you can also check whether it's in the morning or afternoon to determine if it's AM/PM, it means you could potentially replace those two columns with one.

Although you haven't actually asked about opinions on the structure, it was just a side observation. I had presumed that

Quote

my problem is I dont know how to retrieve the data

Meant that you don't know how to retrieve it from the database? Am I mistaken?

Yes you are right , I could replace it with one column. Yes that is correct, I am not sure what the best way to retrieve the data from the database is.

Is using the tolist function the right way? If so how do I then access the list and assign the data to the variables?

Thanks

[jhouns]

I can't see any connections to the database in your code, did you leave them out or have you not done any database connection code so far?

See at a glance it looks okay, but that would only be if you were searching through a list.

It looks like you're creating an empty list of WaterEntities1 and then searching through it. Whereas you really need to connect to the database, run the SQL query against that database and then gather the results from that.

Unless the WaterEntities1 class automatically pulls the data from the database in its constructor.

EDIT: Using entity framework, my answer is now irrelevant.

This post has been edited by jhouns: 02 December 2013 - 09:33 AM

[dhicks19]

I have not done any database connection code as of yet.

Here is my code for adding a record to AMREADINGS tables and it works without adding connection code:

   int thisreading = Convert.ToInt32(GLOBALVARIABLES.thisreading);
                WaterEntities1 dbcontext = new WaterEntities1();
                amreading thisam1 = new amreading();
                thisam1.reading = thisreading;
                thisam1.datetaken = GLOBALVARIABLES.todaysdate;
                thisam1.timestamp = GLOBALVARIABLES.thistime;
                dbcontext.amreadings.Add(thisam1);
                dbcontext.SaveChanges();

ok I guess I will look up SQL connections, but my understanding was when using entity framework, you didnt need to use SQL connection code etc.

Thanks for your help

[jhouns]

OH! Sorry yeah wow my bad. I completely missed that bit, my mistake, sorry. I've not had any experience with entity. Please just ignore my responses I'll edit them. I thought you were doing a standard connect/query without a framework, this is entirely my fault.