C# Why do I get duplicate records back when I return data from SQL to a dataset?

Question

Im new to C#, so bear with me. Im using C#, a Form, and SQL Server 2012.

I have written some code that uses a Background Worker (from the form designer) and a progress bar to illustrate to the user the percentage of data that needs to be loaded from a SQL query until all records are completely read.

The problem Im having is that when the Worker is completed, I get many duplicate records listed in the datagridview. I should be getting only 14 returned records from my query, but I get many more (which are duplicates).

Im not sure why? Im sure there is a bug here (and that my beginnner code can be improved).

Can someone help my understand why Im getting duplicate records once the worker completes? My code is below

private void button2_Click(object sender, EventArgs e) {

        bgw.WorkerReportsProgress = true;
        bgw.WorkerSupportsCancellation = false;
        bgw.DoWork += new DoWorkEventHandler(bgw_DoWork);
        bgw.ProgressChanged += new ProgressChangedEventHandler(bgw_ProgressChanged);
        bgw.RunWorkerCompleted += new RunWorkerCompletedEventHandler(bgw_RunWorkerCompleted);

        System.Threading.Thread.Sleep(10);

        bgw.RunWorkerAsync();

    }

    DataSet ds = new DataSet();

    private void bgw_DoWork(object sender, DoWorkEventArgs e)
    {

        string query = "Select * from Stars_Pillars";

        string connetionString = null;
        SqlConnection cnn;

        connetionString = "Server=xxx\\xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx;";

        cnn = new SqlConnection(connetionString);

        SqlDataAdapter da = new SqlDataAdapter(query, cnn);


        //DataSet ds = new DataSet();



        try
        {

            cnn.Open();


            int resda, startrow, pagesize, pageno;

            pagesize = getRowCount();
            progressBar1.Maximum = pagesize;
            pageno = 1;

            ds.Tables.Clear();
            dataGridView1.Rows.Clear();


            while (true)

            {
                if (!bgw.CancellationPending) 
                {
                    startrow = pageno;

                    resda = da.Fill(ds, startrow, pagesize, "Stars_Pillars");
                    if (resda == 0)
                    {
                        e.Cancel = true;
                        break;
                    }
                    else
                    {

                        System.Threading.Thread.Sleep(10);
                        int percents = (pageno * 100 / pagesize);
                        bgw.ReportProgress(percents, pageno);
                        pageno++;

                    }
                }
                else
                {
                    e.Cancel = true;
                    break;
                }

            }


            da.Dispose();
            cnn.Close();
            cnn.Dispose();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Can not open connection ! ");
        }

    }



    private void bgw_ProgressChanged(object sender, ProgressChangedEventArgs e)
    {

        progressBar1.PerformStep();

    }

    private void bgw_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
    {

        progressBar1.Value = progressBar1.Maximum;
        progressBar1.Visible = false;

       dataGridView1.DataSource = ds.Tables[0];

    }


    private int getRowCount()
    {
        int count = 0;
        string connetionString;

        connetionString = "Server=xxxx\\xxxx;Initial Catalog=xxxx;User ID=xxxx;Password=xxxx;";
        SqlConnection conn = new SqlConnection(connetionString);
        string sql = "SELECT COUNT(Serial_Number) FROM [Stars_Pillars]";
        SqlCommand cmd = new SqlCommand(sql, conn);
        try
        {
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                count = (int)dr[0];
            }
        }
        catch { }
        finally
        {
            conn.Close();
        }
        return count;
    }

jonathana jonathana · Accepted Answer · 2017-05-15T18:01:13

there is a lot of improvements to make in your code...you should never use while (true)
it is extremely bad practice and you can end with infinite loop.
also, there is no way to measure and show progress of filling a dataset with dataAdapter. if the operation takes time show a Marquee style progress bar. for start try this code to load data from database to the DataSet object:

 private void bgw_DoWork(object sender, DoWorkEventArgs e)
 {
    try 

    {
    string query = "Select * from Stars_Pillars";
    connetionString = "Server=xxx\\xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx;";
    using(cnn = new SqlConnection(connetionString))
    {
            cnn.Open();
            using(SqlDataAdapter da = new SqlDataAdapter(query, cnn))
            {
                    adapter.Fill(ds);
                    cnn.Close();
            }
    }

    } catch (Exception ex) {

    // handle errors here and close connetion if its open
    }

}

C# Why do I get duplicate records back when I return data from SQL to a dataset?

2 Answers