Get data with Webpage and store in database

[lubblig]

I'm not sure what language this should be written in but to your knowledge, I'm familiar with C#, JS (and basic knowledge in some other languages) and willing to learn more about another language that might be more appropriate.

 

Basically, I'm making a c# application to monitor temperature and load on an offsite computer/server that I have at a friends house. I would like this data to be sent, via the application to a webpage that gets the input and adds it to a database (any suggestions for what Database to use, please tell me. I'm using Ubuntu Server and Apache to host the webserver) that I can then use to read this data and make graphs and similar. I got the inspiration from Pushover, which I'm using to push notifications of more important things like very high temperature/load on the offsite computer or if a harddrive's SMART values aren't what they should be etc.

 

They seem to use JSON (which I've never used but, again, I'm willing to learn it) on a webserver (which I have the ability to host on my webserver) that gets the input from a C# application with the following code:

 

var parameters = new NameValueCollection {
    { "token", "APP_TOKEN" },
    { "user", "USER_KEY" },
    { "message", "hello world" }
};

using (var client = new WebClient())
{
    client.UploadValues("https://api.pushover.net/1/messages.json", parameters);
}

(for more info: https://pushover.net/faq#library )

 

This seemed like a good solution as I've been messing around with sending the data directly in the C# app to another C# app but that involved opening ports etc (which isn't ideal since I don't want to have to open ports on their network unless absolutely necessary) and I would love it to be able to make like dashboard-style monitor of temps and load available on a website to get easy access to it.

 

Does anyone know how to do something like this or know anything that could help me get started with this (for example, a good youtube tutorial on JSON, if JSON is something you'd recommend using for something like this).

[Mr_KoKa]

You can send data directly to database, since you're the owner of both, application and database, no third party will get access to your passwords.

[lubblig]

Just now, Mr_KoKa said:

You can send data directly to database.

Ok, how? I've never really worked with databases before. Do you have any recommendations for a good lightweight database that runs on Linux? And any tutorials on how to actually send the data (mostly strings and integers) from a windows based C# program to an external database on Linux. I've never done anything like this before so I don't really know what to Google as I don't know the proper terminology etc.

[Mr_KoKa]

You would need to connect from your C# with mysql server. I see you are concern about ports opening, do you host that website at home pc? Or that ubuntu system you're talking about is remote? What I mean is, even if you would send data to website (so using http) you would need to open port 80. If machine is remote like vps or dedicated server, then there is no worries.

 

What you need to acknowleadge is:

 - mysql server installation on your ubuntu.

 - connecting to mysql server from C#

 

I would recommend install phpmyadmin after you install mysql server. You would probably need to instal php, but it would probably do it with phpmyadmin as it probably depends on it. You will make use of php anyway to build your website. For charts I woudl recomend some js lib like Chart.js

 

Are you familiar with apt-get and such? There are tutorials how to install apache2 php and mysql to make environment to host a webpage.

 

 

[lubblig]

18 minutes ago, Mr_KoKa said:

You would need to connect from your C# with mysql server. I see you are concern about ports opening, do you host that website at home pc? Or that ubuntu system you're talking about is remote? What I mean is, even if you would send data to website (so using http) you would need to open port 80. If machine is remote like vps or dedicated server, then there is no worries.

 

What you need to acknowleadge is:

 - mysql server installation on your ubuntu.

 - connecting to mysql server from C#

 

I would recommend install phpmyadmin after you install mysql server. You would probably need to instal php, but it would probably do it with phpmyadmin as it probably depends on it. You will make use of php anyway to build your website. For charts I woudl recomend some js lib like Chart.js

 

Are you familiar with apt-get and such? There are tutorials how to install apache2 php and mysql to make environment to host a webpage.

 

 

The website will be hosted on an apache2 webserver on a Linux Ubuntu Server I currently run at home on a dedicated machine (always on and only for that). Port 80 is already open at my house for the webserver. It's the server/computer I have at a friends house I'd like to avoid opening ports on. So the webserver has port 80 open, the machine I want to retrieve statistics from I would like to avoid opening ports for.

 

When I previously ran a webserver at home, I did have phpmyadmin installed since I installed the LAMP package. But I've since then reinstalled and manually installed all the packages I need. So basic Linux knowledge is no problem.

 

But what would I need to install to get this running? Do I need PHP if I connect the C# application directly to the mySQL server? Or what is the PHP for? PHPMyAdmin is mainly used for managing the database right?

[Mr_KoKa]

Yes, phpmyadmin is for managing database and it uses PHP to work. PHP would be used to connect your webpage with your database, to build html that will display charts and what not.

 

If you don't what to open ports on your friend machine then that machine has to be the sender (I haven't thought about doing it other way around so we are ok there).

 

If connecting C# app with mysql will be too much of a problem then you can use http, no problem.

[lubblig]

11 minutes ago, Mr_KoKa said:

Yes, phpmyadmin is for managing database and it uses PHP to work. PHP would be used to connect your webpage with your database, to build html that will display charts and what not.

 

If you don't what to open ports on your friend machine then that machine has to be the sender (I haven't thought about doing it other way around so we are ok there).

 

If connecting C# app with mysql will be too much of a problem then you can use http, no problem.

Oh ok. Is PHP necessary to get the database info or would it be possible to use Javascript? I will have to do some reading about this but if I get any further questions during the process, would it be ok to send messages to you about this?

 

About this:

Quote

If you don't what to open ports on your friend machine then that machine has to be the sender (I haven't thought about doing it other way around so we are ok there).

The C# application I want to use for sending the statistics to the mysql database does not have ports open. Is this what you meant? And is it ok for it not to have open ports?

[Mr_KoKa]

Yup you don't need to open ports to send data, just to receive.

 

By open ports I mean firewall but also forward on router.

 

I guess it will be just fine if you will ask anything in this thread, so maybe someone who is doing similar thing will find it helpful.

 

I'm working on code that will connect to database and add some data to table and then print table contents. I will update this post. But on my expieirence so far, you need to right click on your project that is under solution, click Manage NuGet Packages and search for MySql.Data, it is driver made by Oracle. 

Edited July 26, 2016 by Mr_KoKa
Changed image from external lin to internal upload.

[lubblig]

9 minutes ago, Mr_KoKa said:

Yup you don't need to open ports to send data, just to receive.

 

By open ports I mean firewall but also forward on router.

 

I guess it will be just fine if you will ask anything in this thread, so maybe someone who is doing similar thing will find it helpful.

 

I'm working on code that will connect to database and add some data to table and then print table contents. I will update this post. But on my expieirence so far, you need to right click on your project that is under solution, click Manage NuGet Packages and search for MySql.Data, it is driver made by Oracle. 

Great, thanks!

 

That's true, didn't think about that!

[Mr_KoKa]

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient; 

namespace CSharpConsoleApp1 {
    class Program {
        static void Main(string[] args) {
            string cs = @"server=localhost;userid=root;database=test";

            MySqlConnection conn = null;

            try {
                conn = new MySqlConnection(cs);
                conn.Open();
                Console.WriteLine("MySQL version : {0}", conn.ServerVersion);

                MySqlCommand insertKey = new MySqlCommand("INSERT INTO `data` (`key`, `value`) VALUES (@key, @value)", conn);
                insertKey.Prepare();

                insertKey.Parameters.AddWithValue("key", "key2");
                insertKey.Parameters.AddWithValue("value", "value2");
                insertKey.ExecuteNonQuery();

                //Reusing prepared statement to insert another row.
                insertKey.Parameters.Clear();
                insertKey.Parameters.AddWithValue("key", "key3");
                insertKey.Parameters.AddWithValue("value", "value3");
                insertKey.ExecuteNonQuery();

                MySqlCommand selectKey = new MySqlCommand("SELECT * FROM `data`", conn);
                MySqlDataReader reader = selectKey.ExecuteReader();

                bool header = false;

                while(reader.Read()) {
                    if(!header) {
                        header = true;
                        Console.WriteLine(reader.FieldCount.ToString());
                        for(var i = 0; i < reader.FieldCount; i++) {
                            Console.Write(reader.GetName(i));
                            Console.Write(" | ");
                        }
                        Console.WriteLine();
                    }


                    for(var i = 0; i < reader.FieldCount; i++) {
                        Console.Write(reader.GetString(i) + " | ");
                    }
                    Console.WriteLine();
                }


            } catch(MySqlException ex) {
                Console.WriteLine("Error: {0}", ex.ToString());

            } finally {
                if(conn != null) {
                    conn.Close();
                }
            }

            Console.ReadKey();
        }
    }
}

The output looks like:

MySQL version : 5.6.24
id | key | value |
1 | key2 | value2 |
2 | key3 | value3 |

 

I used this as reference http://zetcode.com/db/mysqlcsharptutorial/

 

It works, but I don't know. I wish I could just set key and value for reusing statement to insert another row, but I didn't find such function in Parameters. Maybe I didn't look carefully enough, so I just Clear end added new ones. Usually, when you have many row s to insert (eg. 10 temp readouts) you prepare statement before loop and then just change Parameters inside loop and execute statement.

 

About reading from database, you probably don't need it. But it easier in php where you get associative as mysql query result. Here you need to specify column index to get data from.

PHP is easier because of that associative array where you can pick column by its name. You still could convert this result into key value array, but since you need just send from C# I wouldn't worry about it. And even if you need read something then indexes are fine too, as they won't change if you ask for them in specified order like:

SELECT `key`, `value` FROM `data`;

then index 0 will be always key and index 1 is always value

even if you SELECT * FROM indexes will remain in order (`id`, `key`, `value`), but it may change if you remove or add column before another (which isn't default behavior and adding column by default adds it at the end).