Dapper的操作

    public class Users
    {
        public int UserID { get; set; }
        public virtual string UserName { get; set; }
        public virtual string Email { get; set; }
        public virtual string Address { get; set; }

    }

 public class Product
    {
        public int ProductID { get; set; }
        public virtual string ProductName { get; set; }
        public virtual string ProductDesc { get; set; }
        public virtual int UserID { get; set; }
        public virtual DateTime CreateTime { get; set; }


        public Users User { get; set; }
    }

  static void Main(string[] args)
        {


            System.Data.IDbConnection connection = new SqlConnection(
                "server=.;database=ScoreDB;uid=sa;pwd=qqqqq");//初始化


            新增操作
            // var result = connection.Execute("Insert into Users values (@UserName, @Email, @Address)",
            //                        new { UserName = "jack", Email = "380234234@qq.com", Address = "上海" });
            //int i = connection.Execute("insert into Users(UserName,Email,Address) values(@UserName,@Email,@Address)", new { UserName = "tomase", Email = "12@qq.com", Address = "loudi" });
            // Console.WriteLine(i);




            /*InsertBulk批量操作*/
            // var userList = Enumerable.Range(0, 10).Select(u => new
            // {
            //     Email = $"{u}qq.com",
            //     Address = $"HuNan",
            //     UserName = $"{u}jack"
            // });
            //var resultLine = connection.Execute(
            //     "insert into Users(UserName,Email,Address)values(@UserName,@Email,@Address)",
            //     userList);
            // Console.WriteLine(resultLine);


            查询
            //IEnumerable<Users> list = connection.Query<Users>("select * from Users where UserName=@name", new { name = "jack" });
            //foreach (Users item in list)
            //{
            //    Console.WriteLine($"{item.UserID}/{item.UserName}/{item.Email}");
            //}


            更新操作
            //int i = connection.Execute("update dbo.Users set UserName=@userName, Email=@email,Address=@address where UserID=@userId", new { userName = "johon", email = "222@qq.com", address = "Loudi", userId = "4" });
            //Console.WriteLine(i>=0);


            // //删除
            //Task<int> t = connection.ExecuteAsync("delete from Users where UserID=@UserID",
            //     new { UserID = 4 });
            // Console.WriteLine(t.Result >= 0);


            in操作
            //Task<IEnumerable<Users>> t = connection.QueryAsync<Users>("select * from Users where UserID in (@userId1,@userId2)",
            //     new { userId1 = 2, userId2 = 6 });
            //foreach (Users item in t.Result)
            //{
            //    Console.WriteLine($"{item.UserID}//{item.UserName}//{item.Email}");
            //}
            //string insertSql = "insert into Product(ProductName) values(@productName)";
            //connection.Execute(insertSql, new { productName = "鸡蛋" });




            多表的查询
            //string sql = "select * from Users;select * from Product;";


            //GridReader multiple = connection.QueryMultiple(sql);
            //IEnumerable<Users> userList = multiple.Read<Users>();
            //IEnumerable<Product> productList = multiple.Read<Product>();


            //foreach (Users u in userList)
            //{
            //    Console.WriteLine(u.UserName+" "+u.UserID );
            //}


            //foreach (var p in productList)
            //{
            //   Console.WriteLine("p_U_ID"  + "产品Id:" + p.ProductID + " UserID号" + p.UserID );
            //}
            //multiple.Dispose();


            //连接查询
            //string sql = "select ProductID,ProductName,p.UserID,UserName,Email,[Address] from Product p left join Users u on p.UserID = u.UserID";
            //IEnumerable<Product> userList = connection.Query<Product, Users, Product>(sql,
            //    (p, u) => { p.User = u; return p; }, splitOn: "UserName");//设置一个从右边至左边的停止点 一般设置除了ID的第一个属性
            //foreach (Product item in userList)
            //{
            //    Console.WriteLine(item.UserID + " / " + item.User.UserName);
            //}




            //调用 存储过程
            /*
             create proc sp_GetUsers
@id int
as 
begin
select * from Users where UserID=@id
end
             */
            /*向存储过程塞入一个@id参数，返回具体的Users EntityList，*/
            IEnumerable<Users> lists = connection.Query<Users>("sp_GetUsers", new { id = 5 },
                commandType: System.Data.CommandType.StoredProcedure);
            foreach (Users u in lists)
            {
                Console.WriteLine(u.UserID + "  " + u.UserName + " " + u.Email);
            }
            Console.ReadKey();
        }

/*参考文章        http://blog.csdn.net/huangxinchen520/article/details/52505516          */