Mysql Database forms slow

[Derekgirven]

Hi,
I have a Mysql database ,and have built a C# front end in Visual Studio . it works great , just some of my forms pull a bit of info from separate tables using combo boxes .I am using a connection string to retrieve info but it seems to slow and freezes at times .
Is C# and window forms the best way to go or make it using PHP or something similar ?
Any advise ?

 

[Skydiver]

Without seeing your code, I only have to assume that you are doing things the wrong way (either code wise or design wise), because it should not matter to MySQL if you are requesting data from it via WinForms, a web service, PHP, GTK, C++, Java, or some other language/framework.

Are you loading too much data at once? Are you throwing away data that you only needs to loaded once? Are you loading data too soon? Are you loading data inefficiently? Are you doing all your data in the UI thread using blocking calls?

 

[Derekgirven]

Hi Skydiver,
I would probably go with both as I am still pretty new to C#
I made a dataset and pulled in the controls from there (if that makes sense ) .but I have like 12 combo boxes pulling info from tables.
Been a bit cheeky if you can check would be great ha ha

 

[Skydiver]

Hard to check if we can't see your code.

 

[cjard]

Probably you used the default dataset-based data access behavior, which is to load everything out of the db into the the set

 

[Derekgirven]

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;

namespace DgsData_b
{
    public partial class DGSOrderForm : Form
    {
        SqlConnection con = new SqlConnection(@"Data Source = C:\Users\User\source\repos\DgsData_b.sn\DgsData_b\phone_book_searchDataSet.xsd");
        public DGSOrderForm()
        {
            InitializeComponent();
        }

        private void DGSOrderForm_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'phone_book_searchDataSet.orderforms_data' table. You can move, or remove it, as needed.
            this.orderforms_dataTableAdapter.Fill(this.phone_book_searchDataSet.orderforms_data);
            //

        }

        private void modelComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void lengthComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void colourComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void finishComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void extrasComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void groupBox2_Enter(object sender, EventArgs e)
        {

        }

        private void panel1_Paint(object sender, PaintEventArgs e)
        {

        }

        private void groupBox5_Enter(object sender, EventArgs e)
        {

        }

        private void custiDLabel_Click(object sender, EventArgs e)
        {

        }

        private void groupBox1_Enter(object sender, EventArgs e)
        {

        }

        private void groupBox7_Enter(object sender, EventArgs e)
        {

        }

        private void notesLabel_Click(object sender, EventArgs e)
        {

        }

        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void orderforms_dataBindingNavigator_RefreshItems(object sender, EventArgs e)
        {

        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

            SqlConnection con = new SqlConnection(@"C:\Users\User\source\repos\DgsData_b.sn\DgsData_b\phone_book_searchDataSet.xsd");
            string sql = "SELECT  concat(FirstName,'  ',Surname) as name FROM phone_book_search.tblcustomers";
            SqlCommand cmd = new SqlCommand(sql, con);
            con.Open();

            SqlDataReader rd = cmd.ExecuteReader();


            while (rd.Read())
            {

                string FirstName = rd.GetString(1);
                string Surname = rd.GetString(2);

                Name = FirstName + " " + Surname;
                con.Close();
            }
        }
    }
}

Probably you used the default dataset-based data access behavior, which is to load everything out of the db into the the se

I made the dataset using two related tables ,the fill in details come from combo boxes pulling info from other tables this seems to be my problem. I am pretty new to C# (coming from Access ) so a lot is going over my head sorry!!

 

[jmcilhinney]

Please think about what code you're posting. We are interested in code relevant to the issue and not anything else. It's not always easy to work out exactly what's relevant and what isn't but it should be obvious that all those empty methods are not. Including all that noise just makes it harder for us to isolate the problem.

 

[jmcilhinney]

Firstly, you should decide whether you're going to use a typed DataSet or not. If you are, use it. If you're not, don't. Don't do half and half. You're using a typed DataSet in your Load event handler and then you're not in your comboBox1_SelectedIndexChanged method. Choose one and stick to it.

 

[jmcilhinney]

Your comboBox1_SelectedIndexChanged makes no sense. Your query is getting every record from a table but then you close the connection after reading the first record. Do you want every record or not? Also, your query result set has one column containing the FirstName and Surname concatenated, then your code tries to read the second and third columns - they don't exist - as though they contain the FirstName and Surname and concatenate them. Either use the SQL or the C# to process the data, not both.

 

[jmcilhinney]

As for the issue, you need to be specific about what you do and what happens when you do it. When the issue occurs, you need to determine what code is being executed at the time. You then need to profile and debug that code to see exactly what it's doing and whether that's what you expected. You can use the Stopwatch class to time sections of code and VS has various debugging tools. If you don't know how to debug, you should stop what you're doing and learn now. It's an essential skill.

 

[cjard]

in your dataset, the table adapters that load values used in combo boxes (lookup values) should probably be a SELECT * with no where clause. Tables that you're going to add new records to forever should be based on a SELECT * WHERE ...
I typically advocate that the main query in a table adapter be `WHERE id = @id` and then the other/additional queries on it be more useful , such as searching multiple records by some other columns. Name all queries properly, using ByXxx as a name suffix when you have a query with a where clause. Don't end up with a dataset whose tabkeadapter methods are named like "Fill", "FillBy", "FillBy1", "FillBy2"

-

When you work with related lookup data, such as having a Person table with a GenderId column, and separately a Gender table with an Id column, and GenderDesc columns (the latter being a descriptive text like Male, Female, Jedi, Other), and you want to show the person records in a DataGridView but have the gender column in the grid be full of drop downs then you-

* drag a Person grid onto the form from the data sources window
* drag a Gender grid to the form but delete it again; we will use the other things doing so generates but not the grid
* edit the colunns
* change the Gender column to be a datagridviewcomboboxcolumn type
* set the DataSource of the combo to be the gender bindingsource
* set the DisplayMember of the column to be the GenderDesc column
* set the ValueMember of the column to be the Id column (of the gender table)
* set the DataPropertyName of the column to be the GenderId column (it's of the person table)
* ensure the code has a Fill that fills all genders into the dataset gender table upon form load. You should never clear the dataset or these genders will be lost

Now your grid is set up to load person records and when it finds eg Id 1 in the person table, to be able to look that up as Id 1 in the genders table and show the associated description (eg Female). The combo not only looks up 1 to a string Female when showing the record, but it also performs the reverse action of setting Person.GenderId to 2 if the user chooses Male from the dropdown

There isn't a limit to how many you can have in this way, just have a table in your dataset per lookup factor you want (MaritalStatus, EmploymentStatus etc etc) and fill them with records that you leave alone

In case it wasn't made clear already, you should not load every hundreds of thousands of people into your dataset when the form loads. If you ensure your SELECT * FROM Person WHERE ... query on your person table adapter does actually have a WHERE clause then this won't happen. Provide a search to the user where they can look up eg last name being smith, at the push of a button. Load the lookup values automatically- there aren't many of them, and then wait for the user to load 100 people all called smith

If you want to show related data (eg click the person on the main grid and see a list of all the addresses they ever lived at) in another grid that is a separate process, not like the above

 

[Derekgirven]

Please think about what code you're posting. We are interested in code relevant to the issue and not anything else. It's not always easy to work out exactly what's relevant and what isn't but it should be obvious that all those empty methods are not. Including all that noise just makes it harder for us to isolate the problem.

Sorry i think you giving me more credit then I deserve , I thought I was !!!

 

[Derekgirven]

in your dataset, the table adapters that load values used in combo boxes (lookup values) should probably be a SELECT * with no where clause. Tables that you're going to add new records to forever should be based on a SELECT * WHERE ...
I typically advocate that the main query in a table adapter be `WHERE id = @id` and then the other/additional queries on it be more useful , such as searching multiple records by some other columns. Name all queries properly, using ByXxx as a name suffix when you have a query with a where clause. Don't end up with a dataset whose tabkeadapter methods are named like "Fill", "FillBy", "FillBy1", "FillBy2"

-

When you work with related lookup data, such as having a Person table with a GenderId column, and separately a Gender table with an Id column, and GenderDesc columns (the latter being a descriptive text like Male, Female, Jedi, Other), and you want to show the person records in a DataGridView but have the gender column in the grid be full of drop downs then you-

* drag a Person grid onto the form from the data sources window
* drag a Gender grid to the form but delete it again; we will use the other things doing so generates but not the grid
* edit the colunns
* change the Gender column to be a datagridviewcomboboxcolumn type
* set the DataSource of the combo to be the gender bindingsource
* set the DisplayMember of the column to be the GenderDesc column
* set the ValueMember of the column to be the Id column (of the gender table)
* set the DataPropertyName of the column to be the GenderId column (it's of the person table)
* ensure the code has a Fill that fills all genders into the dataset gender table upon form load. You should never clear the dataset or these genders will be lost

Now your grid is set up to load person records and when it finds eg Id 1 in the person table, to be able to look that up as Id 1 in the genders table and show the associated description (eg Female). The combo not only looks up 1 to a string Female when showing the record, but it also performs the reverse action of setting Person.GenderId to 2 if the user chooses Male from the dropdown

There isn't a limit to how many you can have in this way, just have a table in your dataset per lookup factor you want (MaritalStatus, EmploymentStatus etc etc) and fill them with records that you leave alone

In case it wasn't made clear already, you should not load every hundreds of thousands of people into your dataset when the form loads. If you ensure your SELECT * FROM Person WHERE ... query on your person table adapter does actually have a WHERE clause then this won't happen. Provide a search to the user where they can look up eg last name being smith, at the push of a button. Load the lookup values automatically- there aren't many of them, and then wait for the user to load 100 people all called smith

If you want to show related data (eg click the person on the main grid and see a list of all the addresses they ever lived at) in another grid that is a separate process, not like the above

Shew I will need to try figure all that out, thanks for the reply,I am working from My Udemy lessons only ,so need a heap of help I see .Might enlist some help then

 

[cjard]

Forum etiquette: please don't entirely quote a massive post and write a couple of sentences under it. Also don't quote a post at all in this case; you're the next person to post after me, so you don't need to repeat my entire post immediately underneath my entire post. You can either use @cjard to refer to me, or hit quote and replace all the txt with ... if you're after making a single post that talks to multiple people

-

Aim to use quote selectively only, to pick out sentences here and there and respond to them in some way. The quote system in this forum works quite logically; you can hit quote and then edit the words to remove some of them, and hit return a couple of times to break the quote block into two so you can write a bit, quote a bit, write a bit..

 

[gulshan212]

Hello, this is Gulshan Negi.
Well, for a database-driven application, the choice between C# and Windows Forms or PHP depends on the application's complexity, user interface requirements, and development team skill level. Both C# and PHP have great help for working with information bases, and the decision to innovate will at last depend upon your particular requirements and inclinations. If your C# application freezes or runs slowly when retrieving data from the database, you may be able to improve performance by optimizing your code or queries. Looking for guidance from specialists or different designers can likewise assist you in settling on the most ideal choice for your task.
Thanks