Appcrash after try to insert a datagrid

Question

After try to create a DataGrid with a sqlcommand, The app crash. I have read many code to have a succes with the datagrid, but no way :/

Someone can help me ?

using System;using System.Windows;using System.Windows.Controls;using Microsoft.Data.SqlClient;

namespace SUS { public partial class MainWindow : Window { SqlConnection Cnx = new SqlConnection(); SqlCommand Cmd = new SqlCommand(); SqlDataReader dr;

    public MainWindow()
    {
        InitializeComponent();
        Title = "Stop User Sage";
        
        refresh.IsEnabled = false;

        if (txtServerName.Text == "")
        {
            txtServerName.Text = string.Concat(Environment.MachineName, @"\SAGE_ERP");
        }
    }

    private void txtServerName_Initialized(object sender, EventArgs e)
    {
        txtServerName.Text = Properties.Settings.Default.sa_ServerName;
    }

    private void txtServerName_SelectionChanged(object sender, RoutedEventArgs e)
    {
        Properties.Settings.Default.sa_ServerName = txtServerName.Text;
        Properties.Settings.Default.Save();
    }

    private void cmbAuthentification_SelectionChanged(object sender, SelectionChangedEventArgs e)
    {
        if (!cmbAuthentification.IsLoaded)
        {
            cmbAuthentification.Loaded += (ss, ee) => cmbAuthentification_SelectionChanged(sender, e);
            return;
        }

        if (cmbAuthentification.SelectedIndex == 0)
        {
            txtUserID.IsEnabled = false;
            txtUserID.Clear();
            txtUserPwd.IsEnabled = false;
            txtUserPwd.Clear();
        }

        if (cmbAuthentification.SelectedIndex == 1)
        {
            txtUserID.IsEnabled = true;
            txtUserPwd.IsEnabled = true;
        }
    }

    private void cmbDatabase_DropDownOpened(object sender, EventArgs e)
    {

        cmbDatabase.Items.Clear();

        try
        {
            if (Cnx.State == System.Data.ConnectionState.Open)
            {
                Cnx.Close();
            }
            if (cmbAuthentification.Text.Equals("Windows"))
            {
                SQL_Connection.ConnectionString = @"Server = " + txtServerName.Text + "; Integrated Security = SSPI;";
                Cnx.ConnectionString = SQL_Connection.ConnectionString;
            }
            else if (cmbAuthentification.Text.Equals("SQL Server"))
            {
                SQL_Connection.ConnectionString = @"Server = " + txtServerName.Text + "; User ID =" + txtUserID.Text + "; Password=" + txtUserPwd.Text + ";";
                Cnx.ConnectionString = SQL_Connection.ConnectionString;
            }
            Cnx.Open();
            Cmd.Connection = Cnx;
            Cmd.CommandText = "SELECT [name] AS [database],database_id FROM sys.databases WHERE case WHEN state_desc = 'ONLINE' THEN object_id(quotename([name]) +'.[dbo].[P_DOSSIER]', 'U') END IS NOT NULL ORDER BY 1;";
            dr = Cmd.ExecuteReader();
            while (dr.Read())
            {
                cmbDatabase.Items.Add(dr["Database"].ToString());
            }
            Cnx.Close();
        }
        catch (Exception exception_connection)
        {
            MessageBox.Show(exception_connection.Message, "OOoopssss !!!", MessageBoxButton.OK, MessageBoxImage.Error);
        }
    }

    private void cmbDatabase_SelectionChanged(object sender, SelectionChangedEventArgs e)
    {
        if (cmbDatabase.SelectedItem == null)
        {
            refresh.IsEnabled = false;
            return;
        }
        else refresh.IsEnabled = true;
    }

    private void refresh_Click(object sender, RoutedEventArgs e)
    {
        try
        {
            if (Cnx.State == System.Data.ConnectionState.Open)
            {
                Cnx.Close();
            }
            if (cmbAuthentification.Text.Equals("Windows"))
            {
                SQL_Connection.ConnectionString = @"Server = " + txtServerName.Text + "; Integrated Security = SSPI;";
                Cnx.ConnectionString = SQL_Connection.ConnectionString;
            }
            else if (cmbAuthentification.Text.Equals("SQL Server"))
            {
                SQL_Connection.ConnectionString = @"Server = " + txtServerName.Text + "; User ID =" + txtUserID.Text + "; Password=" + txtUserPwd.Text + ";";
                Cnx.ConnectionString = SQL_Connection.ConnectionString;
            }
            Cnx.Open();
            Cmd.Connection = Cnx;
            Cmd.CommandText = ("USE " +cmbDatabase.Text+ ";SELECT a.cbSession AS [UID_ID_Session],b.nt_username AS [UID_UserName],b.hostname AS [UID_ComputerName] FROM cbUserSession a INNER JOIN master..sysprocesses b ON a.cbSession = b.spid;");
            dr = Cmd.ExecuteReader();
            while (dr.Read())
            {
                dgConnected_Users.ItemsSource = dr;

            }
            Cnx.Close();
        }
        catch (Exception exception)
        {
            MessageBox.Show("Erreur de chargement des données pour le motif suivant : \n" + exception.Message, "OOoopssss !!!", MessageBoxButton.OK, MessageBoxImage.Error);
        }
    }
}

}

Answer 1

I think your problem is with how you are handling the data returned from the data reader. This code right here:

Cmd.CommandText = "SELECT [name] AS [database],database_id FROM sys.databases WHERE case WHEN state_desc = 'ONLINE' THEN object_id(quotename([name]) +'.[dbo].[P_DOSSIER]', 'U') END IS NOT NULL ORDER BY 1;";
dr = Cmd.ExecuteReader();
while (dr.Read())
{
   cmbDatabase.Items.Add(dr["Database"].ToString());
}

Wrap the while statement with an if statement to protect against an empty result set and change the way you use the reader like this:

    if (reader.HasRows)
    {
        while (reader.Read())
        {
             cmbDatabase.Items.Add(reader.GetString(0));
        }
    }