Удаление строки из БД DataGrid WPF. Нажатием на кнопку Delete

Question

Как реализовать удаление строки из таблицы в DataBase нажатием на кнопку DEL? Вывод таблицы:

public partial class Deportaments : Window
{
    public ObservableCollection<StudentsClass> StudentsList = new ObservableCollection<StudentsClass>(); 
    private const string _selectString = "SELECT * FROM Students";
    private readonly DataTable _dataTable;
    private const string _conString = @"Data Source=DESKTOP-TAH07PT; Initial Catalog=Karataev; Integrated Security=True";
    public ET DataBase = new ET();
    public Deportaments()
    {
        InitializeComponent();
        _dataTable = new DataTable("Students");
        binddatagrid();


    }

    public void binddatagrid()
    {
        //SqlConnection connection = new SqlConnection(@"Data Source=DESKTOP-TAH07PT; Initial Catalog=Karataev; Integrated Security=True");
        //connection.Open();
        //SqlCommand cmd = new SqlCommand();
        //cmd.CommandText = "select * From Students";
        //cmd.Connection = connection;
        //SqlDataAdapter da = new SqlDataAdapter(cmd);  
        //da.Fill(_dataTable);
        //DeportamentsGrid.ItemsSource = _dataTable.DefaultView;



        SqlConnection connection = new SqlConnection(@"Data Source=DESKTOP-TAH07PT; Initial Catalog=Karataev; Integrated Security=True");
        connection.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "select * From Students";
        cmd.Connection = connection;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(_dataTable);

        foreach (DataRow row in _dataTable.Rows)
        {
            StudentsList.Add(new StudentsClass
            {
                NumberStudents = Convert.ToString(row["Number_Students"]),
                FirstName = Convert.ToString(row["First_Name_Students"]),
                LastName = Convert.ToString(row["Last_Name_Students"]),
                MidName = Convert.ToString(row["Mid_Name_Students"]),
                Address = Convert.ToString(row["Address_Students"]),
                Course = Convert.ToString(row["Course_Students"]),
                BirthDay = Convert.ToString(row["Birthday_Students"])
            });
        }
        DeportamentsGrid.ItemsSource = StudentsList;
        removeButton.Click += removeButton_Click;
    }
private void removeButton_Click(object sender, RoutedEventArgs e)
    {

        var person = DeportamentsGrid.SelectedItem as StudentsClass;
        if (person == null)
            return;

        try
        {
            using (var cnn = new SqlConnection(_conString))
            using (var cmd = cnn.CreateCommand())
            {
                cmd.CommandText = "DELETE FROM Students WHERE NumberStudents = @id";
                cmd.Parameters.AddWithValue("@id", person.NumberStudents);
                cnn.Open();
                var deleted = cmd.ExecuteNonQuery();
                Trace.WriteLine($"Удалено {deleted}");
            }
            StudentsList.Remove(person);
        }
        catch (Exception ex)
        {
            Trace.WriteLine(ex.Message);
        }

    }

XAML:

<DataGrid   HorizontalAlignment="Left" Height="400" Margin="10,10,0,0" VerticalAlignment="Top" Width="601" AutoGenerateColumns="False" Name="DeportamentsGrid"  MouseDoubleClick="DeportamentsGrid_MouseDoubleClick" SelectionChanged="DeportamentsGrid_SelectionChanged_1" PreviewKeyDown="DeportamentsGrid_PreviewKeyDown">
        <DataGrid.Columns>
            <DataGridTextColumn Binding="{Binding NumberStudents}" Header="Номер студента" Width="100"/>
            <DataGridTextColumn Binding="{Binding FirstName}" Header="Имя" Width="100"/>
            <DataGridTextColumn Binding="{Binding MidName}" Header="Отчество" Width="100"/>
            <DataGridTextColumn Binding="{Binding LastName}" Header="Фамилия" Width="100"/>
            <DataGridTextColumn Binding="{Binding BirthDay}" Header="Дата рождения" Width="100"/>
            <DataGridTextColumn Binding="{Binding Address}" Header="Адрес" Width="100"/>
            <DataGridTextColumn Binding="{Binding Course}" Header="Курс" Width="100"/>
        </DataGrid.Columns>
    </DataGrid>

ClassStudents C#:

public class StudentsClass
{
    public string NumberStudents { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string MidName { get; set; }
    public string Address { get; set; }

    public string BirthDay { get; set; }
    public string Course { get; set; }


}

Answer 1

У вас студенты, а у меня люди.

public class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime Birthdate { get; set; }
}

public partial class MainWindow : Window
{
    private const string _conString = @"Data Source=(localdb)\MSSQLLocalDB;" +
                                      "Initial Catalog=DatabaseOneTable;" +
                                      "Integrated Security=True;";
    private readonly ObservableCollection<Person> _people;

    public MainWindow()
    {
        InitializeComponent();

        _people = new ObservableCollection<Person>();
        this.Loaded += MainWindow_Loaded;
    }

    private async void MainWindow_Loaded(object sender, RoutedEventArgs e)
    {
        try
        {
            using (var cnn = new SqlConnection(_conString))
            using (var cmd = cnn.CreateCommand())
            {
                cmd.CommandText = "SELECT * FROM People";
                await cnn.OpenAsync();
                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var person = new Person
                        {
                            Id = reader.GetInt32(reader.GetOrdinal("Id")),
                            FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
                            LastName = reader.GetString(reader.GetOrdinal("LastName")),
                            Birthdate = reader.GetDateTime(reader.GetOrdinal("Birthdate"))
                        };
                        _people.Add(person);
                    }
                }
            }

            _dataGrid.ItemsSource = _people;
        }
        catch (Exception ex)
        {
            Trace.WriteLine(ex.Message);
        }

        _buttonRemove.Click += ButtonRemove_Click;
    }

    private async void ButtonRemove_Click(object sender, RoutedEventArgs e)
    {
        var person = _dataGrid.SelectedItem as Person;
        if (person == null)
            return;

        try
        {
            using (var cnn = new SqlConnection(_conString))
            using (var cmd = cnn.CreateCommand())
            {
                cmd.CommandText = "DELETE FROM People WHERE Id = @id";
                cmd.Parameters.AddWithValue("@id", person.Id);
                await cnn.OpenAsync();
                var deleted =  await cmd.ExecuteNonQueryAsync();
                Trace.WriteLine($"Удалено {deleted}");
            }
            _people.Remove(person);
        }
        catch (Exception ex)
        {
            Trace.WriteLine(ex.Message);
        }
    }
}

P.S. XAML самый примитивный, это же демка.

Window x:Class="WpfApp1.MainWindow"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    xmlns:local="clr-namespace:WpfApp1"
    mc:Ignorable="d"
    Title="MainWindow" Height="450" Width="800" WindowStartupLocation="CenterScreen">
<Canvas>
    <DataGrid x:Name="_dataGrid" Height="220" Canvas.Left="20" Canvas.Top="23" Width="749"/>
    <Button x:Name="_buttonRemove" Content="Удалить" Canvas.Left="50" Canvas.Top="308" Width="75"/>
</Canvas>

БЛОГ НА HUSL

Удаление строки из БД DataGrid WPF. Нажатием на кнопку Delete

Ответы (1 шт):