C# Windows Form with SQL Memory Jump

Question

I'm working on a program that queries a local SQL Server database. The basic setup is a main form with buttons that take you to new forms for each task. The main window uses about 20MB or memory. The other forms that are using less than that. No big deal.

EXCEPT:

Whenever I open a form that contains to run a SQL script/connections, the memory jumps up 1-2GB per form. What I can't figure out is, I'm not actually opening the db connection until a button is clicked. On form initialization, the connection is not open. When I do click a button, the connection opens, the script is executed, and then the connection closes, but there is no noticeable change in memory usage during that process.

Moreover, the memory us not release when I close or dispose of the form. I tried running a GC.collect and that did nothing.

So my questions are: why the large memory jump, and why doesn't it release when I close the form.

I'm new to coding so there may be something I'm completely overlooking, but I can't figure it out. I've reached out to an instructor at my school and there were stumped as well. If there is any more information you need to answer the question, please let me know.

Edit: Here's the code. I know I need to add parameters, I'm getting there.

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 KingKit
{
    public partial class DatabaseRepairForm : Form
    {
        public DatabaseRepairForm()
        {
            InitializeComponent();
        }

        //DBCC BUTTON
        private void ResetSubmitButton_Click(object sender, EventArgs e)
        {
            try
            {                
                Ux_connectionFailed.Visible = false;

                //CONNECTION AND SCRIPT EXECUTION ATTEMPT


                //ESTABLISH CONNECTION
                string dbname = DBNameInput.Text;
                string connection = "server=localhost\\King;" + "Trusted_Connection=yes;" + "Database=" + dbname + "; connection timeout=10";

                SqlConnection conn = new SqlConnection(connection);
                SqlCommand dbcc = new SqlCommand("dbcc checkdb(" + dbname + ") with tableresults", conn);

                conn.Open();
                //CONNECTION CHECK
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    Ux_connectionCheck.Visible = true;
                    Ux_connectionLabel.Visible = true;
                }
                else
                {
                    Ux_connectionLabel.Visible = true;
                    Ux_connectionFailed.Visible = true;

                }

                //CODE EXECUTION
                SqlDataReader reader = dbcc.ExecuteReader();
                while (reader.Read())
                {
                    Ux_repairDisplay.Items.Add(reader["MessageText"].ToString());
                }
                conn.Close();
                }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        //EXIT BUTTON
        private void Ux_appExit_Click(object sender, EventArgs e)
        {
            Dispose();
        }
    }
}

Answer 1

There are things you'll need to do to the code on button click to ensure proper cleanup. But before we even get there, are you saying just constructing the form causes the memory surge?

1. Are you sure the main form (that spawns this form) doesn't call any of its methods, causing data to start being read?
2. Try putting a breakpoint on your InitializeComponent() and start stepping inside it, and monitor if any particular line triggers the surge.

Now on to the code itself. As you can see in MSDN, SqlConnection, SqlCommand, and SqlDataReader are all IDisposable. You should put disposables in a using() statement to ensure they get disposed properly when out of scope, or when Exceptions occur. Also see this thread.

When starting out with .NET it's probably not obvious what is Disposable and what isn't, but a general rule is that all external resources (databases, files, network connections) and graphical objects (Forms, Controls, Fonts) are most likely disposable. You don't normally need to dispose Controls on the form because the Form (acting as a Container) will dispose them when disposing itself. Every other IDisposables you create, you need to clean up yourself.

As for Ux_repairDisplay, there's probably nothing you can do there (except, as mentioned in one of the comments, limiting the noise). If you do need the noise, maybe also put a number limit in your while loop so that if you get a million messages, it doesn't get out of hand.