轻量ORM-SqlRepoEx （四）INSERT、UPDATE、DELETE 语句

*本文中所用类声明见上一篇博文《轻量ORM-SqlRepoEx （三）Select语句》中Customers类

一、增加记录

1、工厂一个实例仓储

  var repository = RepoFactory.Create<Customers>();

2、使用实例增加

Customers customers = new Customers { CustomerID = "YOUR1", CompanyName = "你的公司名1", Phone = "13900000000" };

repository.Insert().For(customers).Go();

当前增加的SQL语句：

INSERT[dbo].[Customers]([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax])

VALUES('YOUR1', '你的公司名1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '13900000000', NULL);

3、指定列值增加

    repository.Insert().With(m => m.CustomerID, "YOUR1")

                .With(m => m.CompanyName, "你的公司名1")

                .With(m => m.Phone, "13900000001").Go();

当前增加的SQL语句：

        INSERT[dbo].[Customers]

        ([CustomerID], [CompanyName], [Phone])

       VALUES('YOUR1', '你的公司名1', '13900000001');

4、注意事项：

（1）、如果有自增加字段，需要在对应属性增加[IdentityFiled] 特性

如： [IdentityFiled]

     public string CustomerID { get; set; }

（2）则生成时有 IdentityFiled 特性是的SQL语句

INSERT [dbo].[Customers]([CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax])

VALUES('你的公司名', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1390000000', NULL);

SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]

FROM [dbo].[Customers]

WHERE [CustomerID] = SCOPE_IDENTITY();

（3）指定列值增加时有 IdentityFiled 特性是的SQL语句

INSERT [dbo].[Customers]([CustomerID], [CompanyName], [Phone])

VALUES('YOUR1', '你的公司名1', '13900000001');

SELECT [CustomerID], [CompanyName], [Phone]

FROM [dbo].[Customers]

WHERE [CustomerID] = SCOPE_IDENTITY();

关于相关特性会在后续博文中介绍

二、更新记录

1、工厂一个实例仓储 

  var repository = RepoFactory.Create<Customers>();

2、使用实例更新

Customers customers = new Customers { CustomerID = "YOURC", CompanyName = "你的公司名", Phone = "1390000000" };

repository.Update().For(customers).Go();

生成的SQL语句

UPDATE [dbo].[Customers]

SET [CustomerID] = 'YOURC', [CompanyName] = '你的公司名', [ContactName] = NULL, [ContactTitle] = NULL, [Address] = NULL, [City] = NULL, [Region] = NULL, [PostalCode] = NULL, [Country] = NULL, [Phone] = '1390000000', [Fax] = NULL WHERE  [CustomerID] = 'YOURC',  [CompanyName] = '你的公司名';

3、指定列值更新

repository.Update()

                .Set(m => m.CompanyName, "你的公司名100")

                .Set(m => m.Phone, "13900000023").Where(m => m.CustomerID == "YOUR1").Go())

生成的SQL语句

UPDATE [dbo].[Customers]

SET [CompanyName] = '你的公司名100', [Phone] = '13900000023'

WHERE ([dbo].[Customers].[CustomerID] = 'YOUR1');

三、删除记录

1、工厂一个实例仓储

  var repository = RepoFactory.Create<Customers>();

2、使用实例删除

 Customers customers = new Customers { CustomerID = "YOURC", CompanyName = "你的公司名", Phone = "1390000000" };

repository.Delete().For(customers).Go();

生成的SQL语句

DELETE [dbo].[Customers] WHERE  [CustomerID] = 'YOURC',  [CompanyName] = '你的公司名';

3、指定条件删除

repository.Delete().Where(m => m.CustomerID == "YOUR1").Go();

生成的SQL语句

DELETE [dbo].[Customers]

WHERE ([dbo].[Customers].[CustomerID] = 'YOUR1');