# SqlRepoEx2.0StartGuid

**Repository Path**: azthinker/SqlRepoEx2.0StartGuid

## Basic Information

- **Project Name**: SqlRepoEx2.0StartGuid
- **Description**: Getting Start SqlRepoEx2.2.1
- **Primary Language**: C#
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 1
- **Forks**: 0
- **Created**: 2018-10-21
- **Last Updated**: 2022-01-18

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# SqlRepoEx2.0StartGuid
Getting Start SqlRepoEx2.2.1


``` C#
  public static void QueryOnly(bool go = false)
        {
            // [Column("ProductName")]
            // public string ProductName2 { get; set; }
            // Shoud Show  [dbo].[Products].[ProductName] as [ProductName2]
            var repository = MsSqlRepoFactory.Create<AzProducts>();
            var result = repository.Query();
            Console.WriteLine(result.Sql());
            Console.WriteLine();
            if (go)
            {
                var resultgo = result.Go();
                foreach (var item in resultgo)
                {
                    Console.WriteLine($"{item.ProductID}\t{item.Supplier}");
                }
            }
            // SELECT [dbo].[Products].[ProductID]
            // , [dbo].[Products].[ProductName] as [ProductName2]
            // , [dbo].[Products].[SupplierID]
            // , [dbo].[Products].[CategoryID]
            // , [dbo].[Products].[QuantityPerUnit]
            // , [dbo].[Products].[UnitPrice]
            // , [dbo].[Products].[UnitsInStock]
            // , [dbo].[Products].[UnitsOnOrder]
            // , [dbo].[Products].[ReorderLevel]
            // , [dbo].[Products].[Discontinued]
            // FROM [dbo].[Products];
        }

        public static void DoInnerJoin()
        {
            var repository = MsSqlRepoFactory.Create<AzProducts>();
            var result = repository.Query()
                                   .InnerJoin<AzSuppliers>()
                                   .On<AzSuppliers>((l, r) => l.SupplierID == r.SupplierID, r => r.CompanyName);
            Console.WriteLine(result.Sql());
            Console.WriteLine();
            // SELECT [dbo].[Products].[ProductID]
            // , [dbo].[Products].[ProductName] as [ProductName2]
            // , [dbo].[Products].[SupplierID]
            // , [dbo].[Products].[CategoryID]
            // , [dbo].[Products].[QuantityPerUnit]
            // , [dbo].[Products].[UnitPrice]
            // , [dbo].[Products].[UnitsInStock]
            // , [dbo].[Products].[UnitsOnOrder]
            // , [dbo].[Products].[ReorderLevel]
            // , [dbo].[Products].[Discontinued]
            // , [dbo].[Suppliers].[CompanyName] as [Supplier]
            // FROM [dbo].[Products]
            // INNER JOIN [dbo].[Suppliers]
            // ON [dbo].[Products].[SupplierID] = [dbo].[Suppliers].[SupplierID];
        }


        public static void LeftOuterJoin()
        {
            var repository = MsSqlRepoFactory.Create<AzProducts>();
            var result = repository.Query()
                                   .LeftOuterJoin<AzSuppliers>()
                                   .On<AzSuppliers>((l, r) => l.SupplierID == r.SupplierID, r => r.CompanyName);
            Console.WriteLine(result.Sql());
            Console.WriteLine();
            //	SELECT [dbo].[Products].[ProductID]
            //	, [dbo].[Products].[ProductName] as [ProductName2]
            //	, [dbo].[Products].[SupplierID]
            //	, [dbo].[Products].[CategoryID]
            //	, [dbo].[Products].[QuantityPerUnit]
            //	, [dbo].[Products].[UnitPrice]
            //	, [dbo].[Products].[UnitsInStock]
            //	, [dbo].[Products].[UnitsOnOrder]
            //	, [dbo].[Products].[ReorderLevel]
            //	, [dbo].[Products].[Discontinued]
            //	, [dbo].[Suppliers].[CompanyName] as [Supplier]
            //	FROM [dbo].[Products]
            //	LEFT OUTER JOIN [dbo].[Suppliers]
            //	ON [dbo].[Products].[SupplierID] = [dbo].[Suppliers].[SupplierID];
        }
```
      
# For Dapper
      
```
      public static class DapperTest
    {
        private static IDbConnection dbConnection = MsSqlRepoFactory.DbConnection;
        public static void QueryOnly()
        {
            var repository = MsSqlRepoFactory.Create<AzProducts>();
            var result = repository.Query().Top(10);
            Console.WriteLine(result.Sql());

            IEnumerable<AzProducts> azProducts = dbConnection.Query<AzProducts>(result.Sql());

            foreach (var item in azProducts)
            {
                Console.WriteLine($"{item.ProductID}\t{item.ProductName2}");
            }
        }

        public static void DoInnerJoin()
        {
            var repository = MsSqlRepoFactory.Create<AzProducts>();
            var result = repository.Query()
                                   .InnerJoin<AzSuppliers>()
                                   .On<AzSuppliers>((l, r) => l.SupplierID == r.SupplierID, r => r.CompanyName)
                                   .Top(10);
            Console.WriteLine(result.Sql());
            Console.WriteLine();

            IEnumerable<AzProducts> azProducts = dbConnection.Query<AzProducts>(result.Sql());

            foreach (var item in azProducts)
            {
                Console.WriteLine($"{item.ProductID}\t{item.ProductName2}\t{item.Supplier}");
            }

        }

        public static void LeftOuterJoin()
        {
            var repository = MsSqlRepoFactory.Create<AzProducts>();
            var result = repository.Query()
                                   .LeftOuterJoin<AzSuppliers>()
                                   .On<AzSuppliers>((l, r) => l.SupplierID == r.SupplierID, r => r.CompanyName)
                                   .Top(10);
            Console.WriteLine(result.Sql());
            Console.WriteLine();

            IEnumerable<AzProducts> azProducts = dbConnection.Query<AzProducts>(result.Sql());

            foreach (var item in azProducts)
            {
                Console.WriteLine($"{item.ProductID}\t{item.ProductName2}\t{item.Supplier}");
            }
        }

        public static void QueryWhere()
        {
            var repository = MsSqlRepoFactory.Create<AzProducts>();
            var result = repository.Query()
                                    .Where(p => p.ProductName2.Contains("t") && p.ProductID < 100)
                                    .Top(10);
            Console.WriteLine(result.Sql());
            Console.WriteLine();

            IEnumerable<AzProducts> azProducts = dbConnection.Query<AzProducts>(result.Sql());

            foreach (var item in azProducts)
            {
                Console.WriteLine($"{item.ProductID}\t{item.ProductName2}");
            }
        }

        public static void QueryWhereIn(bool go = false)
        {
            var repository = MsSqlRepoFactory.Create<AzProducts>();
            var result = repository.Query()
                                    .WhereIn(p => p.ProductName2, new string[] { "Konbu", "Chang", "Tunnbröd", "Geitost" });
            Console.WriteLine(result.Sql());
            Console.WriteLine();
            IEnumerable<AzProducts> azProducts = dbConnection.Query<AzProducts>(result.Sql());

            foreach (var item in azProducts)
            {
                Console.WriteLine($"{item.ProductID}\t{item.ProductName2}");
            }
        }

        public static void QueryAvg()
        {
            var repository = MsSqlRepoFactory.Create<AzOrder_Details>();
            var result = repository.Query().Select(p => p.ProductID, p => p.ProductName)
                                   .Avg(c => c.Quantity, c => c.QuantityAvg)
                                   .Avg(c => c.UnitPrice)
                                   .GroupBy(c => c.ProductID)
                                   .InnerJoin<AzProducts>()
                                   .On<AzProducts>((r, l) => r.ProductID == l.ProductID, l => l.ProductName2)
                                   .GroupBy<AzProducts>(p => p.ProductName2)
                                   .Top(10);

            Console.WriteLine(result.Sql());
            Console.WriteLine();
            IEnumerable<AzOrder_Details> azOrder_Details = dbConnection.Query<AzOrder_Details>(result.Sql());

            foreach (var item in azOrder_Details)
            {
                Console.WriteLine($"{item.ProductID}\t{item.ProductName}\t{item.QuantityAvg}\t{item.UnitPrice}");
            }
        }
        public static void QueryUnion()
        {
            var repository = MsSqlRepoFactory.Create<AzCustomers>();

            // 此语句不会参与数据查询，只是作为Union的包裹
            // 如果此语句本身也是数据查询，请增加到new List<UnionSql>中
            var result = repository.Query()
                                   .Select(c => c.CustomerID, c => c.CompanyName);


            var result01 = repository.Query()
                                    .Select(c => c.CustomerID, c => c.CompanyName)
                                    .Where(c => c.CustomerID == "ANATR");

            var result02 = repository.Query()
                                    .Select(c => c.CustomerID, c => c.CompanyName)
                                    .Where(c => c.CustomerID == "FRANK");


            var result03 = repository.Query()
                                    .Select(c => c.CustomerID, c => c.CompanyName)
                                    .Where(c => c.CustomerID == "TRADH");



            var resultAllSql = result.UnionSql(new List<UnionSql>  {
                UnionSql.New(  result01,UnionType.Union ),
                UnionSql.New(  result02,UnionType.Union ),
                UnionSql.New(  result03,UnionType.Union ), });

            Console.WriteLine(resultAllSql);
            Console.WriteLine();

            IEnumerable<AzCustomers> azCustomers = dbConnection.Query<AzCustomers>(resultAllSql);

            foreach (var item in azCustomers)
            {
                Console.WriteLine($"{item.CustomerID}\t{item.CompanyName}");
            }
        }

        public static void DoInsertEntityParam()
        {
            var repository = MsSqlRepoFactory.Create<AzProducts>();
            AzProducts azProduct = new AzProducts { ProductName2 = "testvalue" };
            var resultinsert = repository
                                    .Insert();



            Console.WriteLine(resultinsert.ParamSql());
            Console.WriteLine();

            // 需返回自增字段，所以用Query
            IEnumerable<AzProducts> azProducts = dbConnection.Query<AzProducts>(resultinsert.ParamSql(), azProduct);

            foreach (var item in azProducts)
            {
                Console.WriteLine($"{item.ProductID}\t{item.ProductName2}");
            }

        }

        public static void DoInsertEntityParamBatch()
        {
            var repository = MsSqlRepoFactory.Create<AzProducts>();
            List<AzProducts> azProductList = new List<AzProducts>{
              new AzProducts { ProductName2 = "testvalue1" ,CategoryID=1,UnitPrice=123},
              new AzProducts { ProductName2 = "testvalue2" ,CategoryID=1,UnitPrice=123},
              new AzProducts { ProductName2 = "testvalue3" ,CategoryID=1,UnitPrice=123},
              new AzProducts { ProductName2 = "testvalue4" ,CategoryID=1,UnitPrice=123 },
              new AzProducts { ProductName2 = "testvalue5" ,CategoryID=1,UnitPrice=123},
              new AzProducts { ProductName2 = "testvalue6" ,CategoryID=1,UnitPrice=123},
            };
            var resultinsert = repository
                                    .Insert().ParamWith(c => c.ProductName2, c => c.UnitPrice, c => c.CategoryID);



            Console.WriteLine(resultinsert.ParamSql());
            Console.WriteLine();

            // 需返回自增字段，所以用Query
            dbConnection.Execute(resultinsert.ParamSql(), azProductList);



        }

        public static void DoUpdateEntityParam()
        {
            var repository = MsSqlRepoFactory.Create<AzProducts>();
            var resultUpdate = repository
                                    .Update()
                                    .ParamSet(p => p.ProductName2, p => p.CategoryID)
                                    .Where(p => p.ProductID == p.ProductID);

            Console.WriteLine(resultUpdate.ParamSql());
            Console.WriteLine();

            AzProducts products = new AzProducts() { ProductID = 84, ProductName2 = "testvalue100", CategoryID = 7 };

            int result = dbConnection.Execute(resultUpdate.ParamSql(), products);


            Console.WriteLine($"{result}");
        }


        public static void DoDeleteEntity(bool go = false)
        {
            var repository = MsSqlRepoFactory.Create<AzProducts>();
            AzProducts azProducts = new AzProducts { ProductName2 = "testvalue", ProductID = 81 };
            var resultUpdate = repository.Delete().For(azProducts);
            Console.WriteLine(resultUpdate.Sql());
            Console.WriteLine();
            int result = dbConnection.Execute(resultUpdate.Sql());
            Console.WriteLine($"{result}");

        }


        public static void DoDeleteBatch()
        {
            var repository = MsSqlRepoFactory.Create<AzProducts>();

            var resultUpdate = repository.Delete().Where(p => p.ProductID == p.ProductID);
            List<AzProducts> azProductList = new List<AzProducts>
            {
                new AzProducts{ProductID=88},
                new AzProducts{ProductID=89},
                new AzProducts{ProductID=90},
                new AzProducts{ProductID=91},

            };
            Console.WriteLine(resultUpdate.Sql());
            Console.WriteLine();
            int result = dbConnection.Execute(resultUpdate.Sql(), azProductList);
            Console.WriteLine($"{result}");
        }

        public static void DoDelete()
        {
            var repository = MsSqlRepoFactory.Create<AzProducts>();

            var resultUpdate = repository.Delete().Where(p => p.ProductID == 90);

            Console.WriteLine(resultUpdate.Sql());
            Console.WriteLine();
            int result = dbConnection.Execute(resultUpdate.Sql());
            Console.WriteLine($"{result}");
        }


        public static void DoDeleteTransaction()
        {
            var repository = MsSqlRepoFactory.Create<AzProducts>();

            var resultUpdate = repository.Delete().Where(p => p.ProductID == p.ProductID);
            List<AzProducts> azProductList = new List<AzProducts>
            {
                new AzProducts{ProductID=92},
                new AzProducts{ProductID=93},
                new AzProducts{ProductID=94},
                new AzProducts{ProductID=91},
            };
            Console.WriteLine(resultUpdate.Sql());
            Console.WriteLine();
            using (var transaction = dbConnection.BeginTransaction())
            {
                dbConnection.Execute(resultUpdate.Sql(), azProductList, transaction: transaction);
                transaction.Rollback();
            }

        }
    }
```