Resolved sql data to chart

miladel

Member
Joined
Aug 16, 2020
Messages
14
Programming Experience
Beginner
Hi all,
I have some problems to add a chart with sql data.
to define the problem, I need to collect data from a sensor, store it in database and use it as a chart and data grid by time descending format.
the code below is a simple example part of the project, the problem is that whenever I start the application, data shows on data grid view and chart but the chart data just like a zigzag line.
every new line just a slop of Sensor's end & start values (see attached picture: here [67,43],[65,43],[63,43],...)
another problem is that no data stores in database and all fields are null.
project attached as a zip file.
thanks for helping as ever.

Annotation 2020-08-18 235008.jpg
Annotation 2020-08-18 235009.jpg

C#:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Web.UI.DataVisualization.Charting;

namespace p1
{
    public partial class Form1 : Form
    {
        SqlConnection conn = new SqlConnection();
        SqlCommand cmd1 = new SqlCommand();
        SqlDataAdapter da = new SqlDataAdapter();
        DataSet ds = new DataSet();

        int inputData;
        public Form1()
        {
            InitializeComponent();
        }

        private void BtnStart_Click(object sender, EventArgs e)
        {
            timer1.Enabled = true;
        }

        private void Timer1_Tick(object sender, EventArgs e)
        {
            serialPort1.Open();
            serialPort1.Write("1");
            txtData.Text = serialPort1.ReadLine();
            serialPort1.Close();
            SqlCommand c1 = new SqlCommand();
            c1.CommandText = "insert into tblCount values (@p1,@p2)";
            inputData = Int32.Parse(txtData.Text);
            c1.Parameters.AddWithValue("p1",DateTime.Now.ToString());
            c1.Parameters.AddWithValue("p2", inputData);
            c1.Connection = conn;
            c1.ExecuteNonQuery();
            fillgrid();
        }

        private void BtnStop_Click(object sender, EventArgs e)
        {
            timer1.Enabled = false;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            conn.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True";
            conn.Open();
            fillgrid();
        }
        void fillgrid(string s="select * from tblCount")
        {
            cmd1.CommandText = s;
            cmd1.Connection = conn;
            da.SelectCommand = cmd1;
            ds.Clear();
            da.Fill(ds, "T1");
            dataGridView1.DataBindings.Clear();
            dataGridView1.DataBindings.Add("datasource", ds, "T1");
            dataGridView1.Sort(dataGridView1.Columns[0], ListSortDirection.Descending);
            txtData.DataBindings.Clear();

            //===================================================Chart1
            SqlConnection conn2 = new SqlConnection();
            conn2.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True";
            conn2.Open();
            SqlCommand cmd2 = new SqlCommand("select sensorValue from tblCount order by dateTime desc", conn2);
            SqlDataReader reader = cmd2.ExecuteReader();
            Series sr = new Series();
            while (reader.Read())
                chart1.Series[0].Points.AddY(reader.GetInt32(0));
        }
    }
}
 

Attachments

  • p.zip
    739.4 KB · Views: 32
You neglected to ask a question. And I'm not one for making assumption.

What is your problem?
What is your expectation?
What is your actual current result?

As aside, to point out some things in your code. It's generally not a good model to do things like opening database connections in your form load event. Such issues as the database being unreachable will play havoc with how your form loads. It's also a good idea to wrap connections attempts in try catch blocks. (Link in my signature.) You also shouldn't use your UI as a model to handle business logic; such that of database interactions etc. Generally you would use a background worker or at least a new thread which isn't the one of that used by your UI.

Next. What's this about :
C#:
void fillgrid(string s="select * from tblCount")
Should be :
C#:
void fillgrid(string s)
However, I'd recommend more suitable names for parameters than s. You would then call that method with :
C#:
fillgrid("select * from tblCount");

It's also a good idea to wrap disposable objects in using statements, as these then become disposed when they are used. SqlCommands, SqlConnections, SqlDataReaders can all use using statements. Lastly, win forms is EOL, and you could make much more stylish windows with something like its successor; WPF. You should also check your insert commands parameters names and use proper names, and not names that match your namespace or any other already named object. And add your @ where your parameters are defined without the symbols. You can find links to using parameters in my signature.

As for the rest, you need to be more specific, as i asked above.
 
another problem is that no data stores in database and all fields are null.
Let me stop you right there. A chart is about displaying data so the fact that no data is saved to a database is COMPLETELY unrelated. Please keep each thread to a single topic and each topic to a single thread. If you have issues displaying data and saving data then create two separate threads for the two separate issues.
 
You neglected to ask a question. And I'm not one for making assumption.

What is your problem?
What is your expectation?
What is your actual current result?

As aside, to point out some things in your code. It's generally not a good model to do things like opening database connections in your form load event. Such issues as the database being unreachable will play havoc with how your form loads. It's also a good idea to wrap connections attempts in try catch blocks. (Link in my signature.) You also shouldn't use your UI as a model to handle business logic; such that of database interactions etc. Generally you would use a background worker or at least a new thread which isn't the one of that used by your UI.

Next. What's this about :
C#:
void fillgrid(string s="select * from tblCount")
Should be :
C#:
void fillgrid(string s)
However, I'd recommend more suitable names for parameters than s. You would then call that method with :
C#:
fillgrid("select * from tblCount");

It's also a good idea to wrap disposable objects in using statements, as these then become disposed when they are used. SqlCommands, SqlConnections, SqlDataReaders can all use using statements. Lastly, win forms is EOL, and you could make much more stylish windows with something like its successor; WPF. You should also check your insert commands parameters names and use proper names, and not names that match your namespace or any other already named object. And add your @ where your parameters are defined without the symbols. You can find links to using parameters in my signature.

As for the rest, you need to be more specific, as i asked above.


Dear Sheepings,
Thanks for your good points.
I did a search for proper successor, as you had truly mentioned win forms are EOL. I think UWP is a better option than WPF. so now i am working on UWP to learn it. Next I will rewrite my program and I hope to do it correctly, not jut like this one. this was an example code and I know I needs lots of modofications.
thanks for your answer
regards
 
Let me stop you right there. A chart is about displaying data so the fact that no data is saved to a database is COMPLETELY unrelated. Please keep each thread to a single topic and each topic to a single thread. If you have issues displaying data and saving data then create two separate threads for the two separate issues.


Dear jmcilhinney,
yeah that's a better idea to separate every issue in a single thread. I need to change the whole program. Let's see how it will be going on.
thanks
Regards
 
Good to see you are going to do some revisions and move away from old Winforms. WPF will be fine for what you're doing, unless you will require a mobile version for windows devices. In which case UWP would be the better option. But if you only need a desktop version, I'd sway with WPF personally.

If you get stuck, you know where we are if you have any questions.
 
Back
Top Bottom