C# ORM框架（Dapper）

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;//引入ORM框架
using System.Data.SqlClient;
using System.Data;

namespace Dapper_Demo_01
{
    class Program
    {
        private static string connStr = "server=.;uid=sa;pwd=sa;database=taobaodb";
        static void Main(string[] args)
        {
            //Dapper生成模型，下载插件
            //Dapper 添加、删除、修改、查询、联表查询  聚合函数、存储过程、事务
            //Add();//默认不带事务处理机制
            //Update();
            //Delete();

            //GetModel();
            //GetList();
            //GetFunction();
            //GetListJoin();
            //ExecuteTranscation();//事务
            ExecProc();//存储过程
        }

        private static void Add()
        {
            string sql = @"insert into producttype(typeid,typename) 
                           values(@typeid,@typename)";
            //ProductType productType = new ProductType()
            //{
            //    TypeId = 4,
            //    TypeName = "测试类型"
            //};
            //可以支持批量添加，不带事务
            List<ProductType> list = new List<ProductType>()
            {
                new ProductType(){TypeId = 4,TypeName = "测试类型5"},
                new ProductType(){TypeId = 5,TypeName = "测试类型6"},
                new ProductType(){TypeId =6,TypeName = "测试类型4"}
            };
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                int result = conn.Execute(sql, list);
                if (result > 0)
                {
                    Console.WriteLine("添加成功！");
                }
            }

        }

        private static void Update()//修改
        {
            string sql = @"update producttype set typename=@typename where typeid=@typeid";
            //ProductType productType = new ProductType()
            //{
            //    TypeId = 4,
            //    TypeName = "测试类型"
            //};
            //可以支持批量添加，不带事务
            List<ProductType> list = new List<ProductType>()
            {
                new ProductType(){TypeId = 4,TypeName = "测试类型7"},
                new ProductType(){TypeId = 5,TypeName = "测试类型8"},
                new ProductType(){TypeId =6,TypeName = "测试类型9"}
            };
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //添加、删除、修改 Execute  存储过程
                //update
                int result = conn.Execute(sql, list);
                if (result > 0)
                {
                    Console.WriteLine("修改成功！");
                }
            }
        }
        //ORM不是对应DBHelper 添加 删除 修改
        private static void Delete()//修改
        {
            string sql = @"delete producttype where typeid=@typeid";
            ProductType productType = new ProductType();
            productType.TypeId = 5;

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //添加、删除、修改 Execute  存储过程
                //update
                int result = conn.Execute(sql, productType);
                if (result > 0)
                {
                    Console.WriteLine("删除成功！");
                }
            }
        }

        private static void GetModel()//修改
        {
            string sql = @"select * from Product where productno=@productno";
            Product product = new Product();
            product.ProductNo = 1002;

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                Product _product = conn.Query<Product>(sql, product).Single();
                Console.WriteLine($"{_product.ProductNo}-{_product.ProductName}");
            }
        }

        private static void GetList()//修改
        {
            string sql = @"select * from Product";//ORM框架执行效率排序EF 7 8  Dapper执行效率 1 2
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                List<Product> productList = conn.Query<Product>(sql).ToList();
                foreach (Product _product in productList)
                {
                    Console.WriteLine($"{_product.ProductNo}-{_product.ProductName}");
                }
            }
        }

        private static void GetFunction()//修改
        {
            string sql = @"select * from Product";//ORM框架执行效率排序EF 7 8  Dapper执行效率 1 2
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                List<Product> productList = conn.Query<Product>(sql).ToList();
                int count = productList.Count();//获取记录条数
                decimal sumPrice = productList.Sum(p => p.Price);
                decimal avgPrice = productList.Average(p => p.Price);
                decimal maxPrice = productList.Max(p => p.Price);
                decimal minPrice = productList.Min(p => p.Price);
                Console.WriteLine(sumPrice);
                foreach (Product _product in productList)
                {
                    Console.WriteLine($"{_product.ProductNo}-{_product.ProductName}");
                }
            }
        }

        private static void GetListJoin()//修改
        {
            string sql = @"select * from Product a 
                            left join producttype b 
                            on(a.typeid=b.typeid)";
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                List<Product> productList = conn.Query<Product, ProductType, Product>
                    (sql, (p, t) =>
                         {
                             p.ProductType = t;//关联表
                             return p;
                         }, splitOn: "typeid"//建立关系的字段
                    ).ToList();
                foreach (Product _product in productList)
                {
                    Console.WriteLine($"{_product.ProductNo}-{_product.ProductName}-{_product.ProductType.TypeName}");
                }
            }
        }


        private static void ExecuteTranscation()//事务
        {
            string sql1 = @"insert into producttype(typeid,typename) 
                           values(@typeid,@typename)";
            string sql2 = @"insert into producttype(typeid,typename) 
                           values(@typeid,@typename)";
            ProductType productType1 = new ProductType()
            {
                TypeId = 7,
                TypeName = "测试7"
            };
            ProductType productType2 = new ProductType()
            {
                TypeId = 5,
                TypeName = "测试5"
            };
            using (SqlConnection conn = new SqlConnection(connStr))//打开数据库方式交给框架执行
            {
                SqlTransaction trans = null;
                try
                {
                    conn.Open();//手动打开数据库链接
                    //程序事务，建立在某个连接对象上
                    //开始事务
                    trans = conn.BeginTransaction();//连接对象开启事务
                    int result1 = conn.Execute(sql1, productType2, trans);
                    int result2 = conn.Execute(sql2, productType1, trans);
                    //提交事务(持久化数据)
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    //事务回滚
                    trans.Rollback();
                    Console.WriteLine(ex.Message);
                }
            }
        }

        private static void ExecProc()
        {
            using (SqlConnection conn = new SqlConnection(connStr))//打开数据库方式交给框架执行
            {
                DynamicParameters paras = new DynamicParameters();
                paras.Add("@typeid", 1);//输入参数
                paras.Add("@count", 0, DbType.Int32, ParameterDirection.Output);//输出参数

                List<Product> productList = conn.Query<Product>("PROC_GetProduct", paras, commandType: CommandType.StoredProcedure).ToList();//sql 存储过程

                int count = paras.Get<int>("@count");//获取输出参数

                Console.WriteLine($"共计:{count}");
                foreach (Product product in productList)
                {
                    Console.WriteLine($"{product.ProductNo} - {product.ProductName}");
                }
            }
        }
    }
}