WPF access mysql and pass data from datagrid to mysql

//sql
drop database if exists mydb;
create database mydb;
use mydb;
drop table if exists mt;
create table mt
(id int auto_increment primary key,
name varchar(50) not null default '',
isbn varchar(50) not null default '',
author varchar(50) not null default '',
comment varchar(50) not null default '',
content varchar(50) not null default '',
summary varbinary(50) not null default '',
title varchar(50) not null default '',
topic varchar(50) not null default '',
Congest blob not null
);

 

 

//<Window x:Class="WpfApp319.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:WpfApp319"
        mc:Ignorable="d" WindowState="Maximized"
        Title="MainWindow" Height="450" Width="800">
    <Window.Resources>
        <Style TargetType="{x:Type Button}" x:Key="btnStyle">
            <Setter Property="FontSize" Value="30"/>
            <Setter Property="Width" Value="150"/>
        </Style>
    </Window.Resources>
    <Window.DataContext>
        <local:BookVM/>
    </Window.DataContext>
    <Grid>
        <Grid.RowDefinitions>
            <RowDefinition Height="50"/>
            <RowDefinition/>
        </Grid.RowDefinitions>
        <ToolBar Grid.Row="0">
            <Button Content="New" Command="{Binding NewCmd}" Style="{StaticResource  btnStyle}"/>
            <GridSplitter/>
            <Button Content="Save" Command="{Binding SaveCmd}" Style="{StaticResource  btnStyle}"/>
            <GridSplitter/>
            <Button Content="Load" Command="{Binding LoadCmd}" Style="{StaticResource  btnStyle}"/>
            <GridSplitter/>
            <Button Content="Import" Command="{Binding ImportCmd}" Style="{StaticResource  btnStyle}"/>
            <GridSplitter/>
            <Button Content="Export" Command="{Binding ExportCmd}" Style="{StaticResource  btnStyle}"/>
            <GridSplitter/>
        </ToolBar>
        <DataGrid Grid.Row="1" 
                  ItemsSource="{Binding BooksCollection,Mode=TwoWay,UpdateSourceTrigger=PropertyChanged}"
                  AutoGenerateColumns="False" CanUserAddRows="False" 
                  EnableColumnVirtualization="True"
                  EnableRowVirtualization="True"
                  VirtualizingPanel.CacheLengthUnit="Item"
                  VirtualizingPanel.IsVirtualizing="True"
                  VirtualizingPanel.VirtualizationMode="Recycling" >
            <DataGrid.Columns>
                <DataGridTextColumn Header="Id" Binding="{Binding Id}" IsReadOnly="True"/>
                <DataGridTextColumn Header="Name" Binding="{Binding Name}" IsReadOnly="True"/>
                <DataGridTextColumn Header="Author" Binding="{Binding Author}" IsReadOnly="True"/>
                <DataGridTextColumn Header="Comment" Binding="{Binding Comment}" IsReadOnly="True"/>
                <DataGridTextColumn Header="Content" Binding="{Binding Content}" IsReadOnly="True"/>
                <DataGridTextColumn Header="ISBN" Binding="{Binding ISBN}" IsReadOnly="True"/>
                <DataGridTextColumn Header="Summary" Binding="{Binding Summary}" IsReadOnly="True"/>
                <DataGridTextColumn Header="Title" Binding="{Binding Title}" IsReadOnly="True"/>
                <DataGridTextColumn Header="Topic" Binding="{Binding Topic}" IsReadOnly="True"/>
                <DataGridTemplateColumn Header="ImgUrl" IsReadOnly="True">
                    <DataGridTemplateColumn.CellTemplate>
                        <DataTemplate>
                            <Image Source="{Binding ImgUrl}" Width="200" Height="500"/>
                        </DataTemplate>
                    </DataGridTemplateColumn.CellTemplate>
                </DataGridTemplateColumn>
            </DataGrid.Columns>
        </DataGrid>
    </Grid>
</Window>


//cs
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace WpfApp319
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
            //DBHelper.InsertInto("");
        }
    }

    public class BookVM : INotifyPropertyChanged
    {
        public BookVM()
        {
            InitCommands();
            InitData();
        }

        private void InitData()
        {
            imgsList = System.IO.Directory.GetFiles(@"../../Images")?.ToList();
            imgsCount = imgsList.Count;
        }

        private void InitCommands()
        {
            NewCmd = new DelCmd(NewCmdExecuted);
            SaveCmd = new DelCmd(SaveCmdExecuted);
            LoadCmd = new DelCmd(LoadCmdExecuted);
            ExportCmd = new DelCmd(ExportCmdExecuted);
            ImportCmd = new DelCmd(ImportCmdExecuted);
        }

        private void SaveCmdExecuted(object obj)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("insert into mt(author,name,isbn,comment,content,summary,title,topic,imgurl,congest) values ");
            if(BooksCollection!=null && BooksCollection.Any())
            {
                int booksCnt = BooksCollection.Count;
                for(int i=0;i<booksCnt;i++)
                {
                    var bk= BooksCollection[i];
                    sqlBuilder.Append($"('{bk.Author}','{bk.Name}','{bk.ISBN}','{bk.Comment}','{bk.Content}'," +
                        $"'{bk.Summary}','{bk.Title}','{bk.Topic}','{bk.ImgUrl}','{bk.ToString()}'),");
                }
            }

            string sqlStr = sqlBuilder.ToString().Substring(0,sqlBuilder.Length - 1);
            int insertedResult = DBHelper.InsertInto(sqlStr);
            MessageBox.Show($"Inserted :{insertedResult} successfully!");
        }

        private void ImportCmdExecuted(object obj)
        {

        }

        private void ExportCmdExecuted(object obj)
        {

        }

        private void LoadCmdExecuted(object obj)
        {

        }

        private void NewCmdExecuted(object obj)
        {
            BooksCollection = new ObservableCollection<Book>();
            for (int i = 1; i <= 100000; i++)
            {
                BooksCollection.Add(new Book()
                {
                    Id = i,
                    Name = $"Name_{i}",
                    Author = $"Author_{i}",
                    ISBN = $"ISBN_{i}",
                    Comment = $"Comment_{i}",
                    Content = $"Content_{i}",
                    Summary = $"Summary_{i}",
                    Title = $"Title_{i}",
                    Topic = $"Topic_{i}",
                    ImgUrl = $"{imgsList[i % imgsCount]}",
                    Congest = this.ToString()
                });
            }
        }

        public event PropertyChangedEventHandler PropertyChanged;
        private void OnPropertyChanged(string propName)
        {
            var handler = PropertyChanged;
            if (handler != null)
            {
                handler?.Invoke(this, new PropertyChangedEventArgs(propName));
            }
        }

        #region Properties

        private ObservableCollection<Book> booksCollection;
        public ObservableCollection<Book> BooksCollection
        {
            get
            {
                return booksCollection;
            }
            set
            {
                if (value != booksCollection)
                {
                    booksCollection = value;
                    OnPropertyChanged(nameof(BooksCollection));
                }
            }
        }

        private List<string> imgsList;
        private int imgsCount = 0;

        #endregion

        #region Commands
        public DelCmd NewCmd { get; set; }

        public DelCmd SaveCmd { get; set; }

        public DelCmd LoadCmd { get; set; }

        public DelCmd ExportCmd { get; set; }

        public DelCmd ImportCmd { get; set; }

        #endregion

    }

    public class DBHelper
    {
        static string connStr = @"Server=localhost;database=mydb;user=usernamevalue;password=passwordvalue;";
        static MySqlConnection conn { get; set; }

        static DBHelper()
        {
            conn = new MySqlConnection(connStr);
            conn.Open();
        }


        public static int InsertInto(string sqlStr)
        {
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }

            using (MySqlCommand cmd = new MySqlCommand(sqlStr, conn))
            {
                int insertedRows = cmd.ExecuteNonQuery();
                return insertedRows;
            }
        }


        ~DBHelper()
        {
            if (conn.State == System.Data.ConnectionState.Open)
            {
                conn.Close();
                conn = null;
            }
            else if (conn != null)
            {
                conn = null;
            }
        }
    }

    public class Book
    {
        public int Id { get; set; }

        public string Author { get; set; }

        public string Comment { get; set; }

        public string Content { get; set; }

        public string Name { get; set; }

        public string ISBN { get; set; }

        public string Summary { get; set; }

        public string Title { get; set; }

        public string Topic { get; set; }

        public string Congest { get; set; }

        public string ImgUrl { get; set; }

        public override string ToString()
        {
            return $"{Id},{Author},{Comment},{Content},{Name},{ISBN},{Summary},{Title},{Topic}";
        }
    }

    public class DelCmd : ICommand
    {
        public event EventHandler CanExecuteChanged
        {
            add
            {
                CommandManager.RequerySuggested += value;
            }
            remove
            {
                CommandManager.RequerySuggested -= value;
            }
        }

        private Action<object> execute;
        private Predicate<object> canExecute;

        public DelCmd(Action<object> executeValue, Predicate<object> canExecuteValue)
        {
            execute = executeValue;
            canExecute = canExecuteValue;
        }

        public DelCmd(Action<object> executeValue) : this(executeValue, null)
        {
        }


        public bool CanExecute(object parameter)
        {
            if (parameter == null)
            {
                return true;
            }
            return canExecute(parameter);
        }

        public void Execute(object parameter)
        {
            execute(parameter);
        }
    }
}