C# MySQL Dapper insert delete select update data from table

Install dapper 

 

 

 

using Dapper;
using MySql.Data.MySqlClient;

namespace ConsoleApp87
{
    internal class Program
    {
        static string connStr = @"Server=servernamevalue;user id=usernamevalue;password=passwordvalue;database=databasenamevalue;";
        static void Main(string[] args)
        {
            DapperDeleteData();
            Console.ReadLine();
        }

        static void DapperDeleteData()
        {
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                conn.Open();
                Console.WriteLine(conn.State);
                string selectStr = "select * from t1 order by id desc limit 1;";
                var bk = conn.Query<Book>(selectStr).FirstOrDefault();
                Console.WriteLine(bk.ToString());
                string deleteSQL = "delete from t1 where id=@id";
                int deleteResult = conn.Execute(deleteSQL, bk);
                Console.WriteLine($"DeleteResult:{deleteResult}");
                bk = conn.Query<Book>(selectStr).FirstOrDefault();
                Console.WriteLine(bk.ToString());
            }
        }

        static void DapperUpdateData()
        {
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                conn.Open();
                Console.WriteLine(conn.State);
                string selectStr = "select * from t1 order by id desc limit 1";
                Book bk = conn.Query<Book>(selectStr).FirstOrDefault();
                Console.WriteLine(bk.ToString());
                bk.FirstName = $"{bk.FirstName}_Max";
                bk.LastName = $"{bk.LastName}_Max";

                string updateSQL = "update t1 set firstname=@firstname,lastname=@lastname where id=@id";
                int updatedResult = conn.Execute(updateSQL, bk);
                Console.WriteLine($"UpdatedResult:{updatedResult}");
                bk = conn.Query<Book>(selectStr).FirstOrDefault();
                Console.WriteLine(bk.ToString());
            }
        }

        static void DapperInsertData()
        {
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                conn.Open();
                Console.WriteLine(conn.State);
                string selectStr = "select id from t1 order by id desc limit 1";
                int maxId = conn.Query<int>(selectStr).FirstOrDefault();
                Console.WriteLine($"Old maxId:{maxId}");
                int newId = maxId + 1;
                Book bk = new Book()
                {
                    FirstName=$"FirstName_{newId}",
                    LastName=$"LastName_{newId}"
                };
                string insertSql = "insert into t1 (firstname,lastname) values (@firstname,@lastname)";
                int insertedResult= conn.Execute(insertSql, bk);
                Console.WriteLine($"Inserted Result:{insertedResult}");
                maxId = conn.Query<int>(selectStr).FirstOrDefault();
                Console.WriteLine($"New MaxId:{maxId}");
            }
        }

        static void DapperReadData()
        {
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                conn.Open();
                Console.WriteLine(conn.State);
                string selectStr = "select * from t1 where id in (1,2,3);";
                List<Book> booksList = conn.Query<Book>(selectStr).ToList();
                if (booksList != null && booksList.Any())
                {
                    foreach (var bk in booksList)
                    {
                        Console.WriteLine(bk.ToString());
                    }
                    
                }
            }
        }

        public class Book
        {
            public int Id { get; set; }
            public string FirstName { get; set; }
            public string LastName { get; set; }

            public override string ToString()
            {
                return $"Id:{Id},FirstName:{FirstName},LastName:{LastName}";
            }
        }
    }
}

 

 

 

 

use mydb;
select * from t1 order by id desc limit 1;