http://www.ikende.com/smarkdata.aspx
Smark.Data是基于Ado.net实现的数据访问组件,提供基于强类型的查询表达式进行灵活的数据查询,统计,修改和删除等操作;采用基于条件驱动的操作模式,使数据操作更简单轻松;内部通过标准SQL92实现对不同数据的支持,包括MSSQL,ORACLE,SQLITE,ACCESS等。
1
2
3
4
5
6
7
8
9
10
11
12
13
|
var emp = 3.Load();
emp.Notes =
"smark.data"
;
emp.Save();
(Employee.employeeID == 111).Edit(o => { o.Notes =
"text"
; });
SQL sql =
"delete from employees where employeeid=@p1"
;
sql[
"p1"
, 11].Execute();
sql =
"select * from orders where employeeid=@p1 and orderdate >@p2"
;
var items= sql[
"p1"
, 1][
"p2"
,
"1996-1-1"
].List();
var items = (Order.employeeID == 1 & Order.orderDate >
"1996-1-1"
).List();
|
主要功能
- 提供强类型的查询表达式,并提供基于条件表达式的数据据查询,汇总,修改,删除和编辑。
- 提供多数库支持,运行期提供实体可对应多个数据库操作。
- 提供基于线程的事务机制,可在不传递事务对象的情况下可以支持跨方法事务。
- 支持表,视图和查询汇总实体描述。
- 可在运行期动态调整实体对应的表名。
- 支持自定义业务对象填充。
- 支持存储过程调用描述和存储过程数据对象填充。
- 支持自定义实体成员数据转换器,并自动应用到相应查询条件中。
- 支持自定义实体成员数据验证,并可能通过数据查询的方式来验证成员数据有效性.
- 支持自定义值描述,可以根据实际情况定义不同的成员默认值,包括从mssql获取自增值,oracle序列或系统的guid值等。
设置访问数据类型和连接信息
- 配置文件配置
1
2
3
4
5
6
7
8
9
10
11
12
|
<
configSections
>
<
section
name
=
"smarkdata"
type
=
"Smark.Data.SmarkDataSection,Smark.Data"
/>
</
configSections
>
<
smarkdata
>
<
Connection
>
<
add
name
=
"0"
type
=
"Smark.Data.MSSQL,Smark.Data"
connectionstring
=
"Data Source=192.168.0.x;Initial Catalog=ProductDB;uid=xxx;pwd=xxx"
/>
</
Connection
>
<
Assembly
>
<
clear
/>
</
Assembly
>
</
smarkdata
>
|
- 代码配置
1
2
3
|
string
dbpath =
@"Data Source=..\\..\\..\\lib\\northwind.db;Pooling=true;FailIfMissing=false;"
;
DBContext.SetConnectionDriver<SqliteDriver>(ConnectionType.Context1);
DBContext.SetConnectionString(ConnectionType.Context1, dbpath);
|
实体定义
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
[Table(
"customers"
)]
public
interface
ICustomer
{
[ID]
string
CustomerID {
get
;
set
; }
[Column]
string
CompanyName {
get
;
set
; }
[Column]
string
ContactName {
get
;
set
; }
[Column]
string
Address {
get
;
set
; }
[Column]
string
City {
get
;
set
; }
[Column]
string
Region {
get
;
set
; }
[Column]
string
PostalCode {
get
;
set
; }
[Column]
string
Country {
get
;
set
; }
[Column]
string
Phone {
get
;
set
; }
[Column]
string
Fax {
get
;
set
; }
}
|
下载实体插件支持vs2010,vs2008(win2003,winxp,win7,win2008)
数据增,删,改
- 增加
12345
Employee emp =
new
Employee();
emp.FirstName =
"fan"
;
emp.LastName =
"henry"
;
emp.City =
"guangzhou"
;
DBContext.Save(emp);
组件会根据设置成员值的情况来更新相关字段,当没有更改值的属性并不会生成相关SQL添加到数据库中
- 删除
12
emp = DBContext.Load<Employee>(3);
DBContext.Delete(emp);
- 修改
123
emp = DBContext.Load<Employee>(3);
emp.Notes =
"test,remark"
;
DBContext.Save(emp);
条件表达式
- 简单查询
12
Expression exp = Customer.country ==
"USA"
& Customer.city ==
"OR"
;
var items = exp.List<Customer>();
- 组合查询
12345678910
Expression exp =
new
Expression();
if
(
string
.IsNullOrEmpty(employeeid))
exp &= Order.employeeID == employeeid;
if
(
string
.IsNullOrEmpty(customerid))
exp &= Order.customerID == customerid;
if
(from !=
null
)
exp &= Order.orderDate > from;
if
(to !=
null
)
exp &= Order.orderDate <to;
var items = exp.List<Order>();
- 条件复用
12345
exp = Employee.employeeID == 6;
var employees = exp.List<Employee>();
var orders = exp.List<Order>();
exp.Delete<Employee>();
exp.Delete<Order>();
- 修改数据
1
(Customer.country ==
"USA"
).Edit<Customer>(d => { d.City =
"abs"
; });
- 删除数据
1
(Customer.country ==
new
[] {
"USA"
,
"UK"
}).Delete<Customer>();
- 数据统计
12
exp.Count<Customer>();
exp.Sum<
double
, Product>(Product.unitPrice.Name);
关联,统计查询
- 单一对象
12345678910
[Table(
"employees"
)]
public
interface
IEmployeeView
{
[ID]
string
EmployeeID {
get
;
set
; }
[Column]
string
FirstName {
get
;
set
; }
[Column]
string
LastName {
get
;
set
; }
}
12Expression exp =
new
Expression();
var empviews = exp.List<EmployeeView>();
- 制定表关联查询
1234567891011121314151617181920212223242526
[Table(
"customers inner join orders on customers.customerid=orders.customerid"
)]
public
interface
ICustomerOrders
{
[Column]
string
CompanyName {
get
;
set
; }
[Column]
string
ContactName {
get
;
set
; }
[Column]
[DateTimToLong]
DateTime OrderDate {
get
;
set
; }
[Column]
[DateTimToLong]
DateTime RequiredDate {
get
;
set
; }
[Column]
string
ShipName {
get
;
set
; }
[Column]
string
ShipAddress {
get
;
set
; }
[Column]
string
ShipCity {
get
;
set
; }
[Column]
string
ShipRegion {
get
;
set
; }
[Column]
string
ShipPostalCode {
get
;
set
; }
[Column]
string
ShipCountry {
get
;
set
; }
}
12Expression exp =
new
Expression();
var empviews = exp.List<CustomerOrders>();
- 数据汇总统计
数据统计需要制定相关统计对象,以下是统计各城市客户订单数量12345678910[Table(
"customers inner join orders on customers.customerid=orders.customerid"
)]
public
interface
ICustOrderGroupByCity
{
[Column]
string
Country {
get
;
set
; }
[Column]
string
City {
get
;
set
; }
[Count]
int
Count {
get
;
set
; }
}
12Expression exp =
new
Expression();
var empviews = exp.List<CustOrderGroupByCity>();
自定义对象填充
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
[Table(
"Employees"
)]
public
interface
IEmployee
{
[ID]
int
EmployeeID {
get
;
set
; }
[Column]
string
LastName {
get
;
set
; }
[Column]
string
FirstName {
get
;
set
; }
[Column]
string
Title {
get
;
set
; }
[Column]
string
TitleOfCourtesy {
get
;
set
; }
[Column]
DateTime BirthDate {
get
;
set
; }
[Column]
DateTime HireDate {
get
;
set
; }
[Column]
string
Address {
get
;
set
; }
[Column]
string
City {
get
;
set
; }
[Column]
string
Region {
get
;
set
; }
[Column]
string
PostalCode {
get
;
set
; }
[Column]
string
Country {
get
;
set
; }
[Column]
string
HomePhone {
get
;
set
; }
[Column]
string
Extension {
get
;
set
; }
}
|
以上是一个简单的雇员信息描述。
1
2
|
Expression exp =
new
Expression();
var items = exp.List<Employee>();
|
以上操作是获取所有雇员信息,而产生的SQL如下:
1
2
3
4
5
|
Select (EmployeeID)
as
p_EmployeeID,(LastName)
as
p_LastName,(FirstName)
as
p_FirstName,
(Title)
as
p_Title,(TitleOfCourtesy)
as
p_TitleOfCourtesy,(BirthDate)
as
p_BirthDate,
(HireDate)
as
p_HireDate,(Address)
as
p_Address,(City)
as
p_City,(Region)
as
p_Region,
(PostalCode)
as
p_PostalCode,(Country)
as
p_Country,(HomePhone)
as
p_HomePhone,
(Extension)
as
p_Extension from Employees
|
1
2
3
4
5
6
7
8
9
10
11
|
public
class
EmployeeContact
{
public
string
LastName {
get
;
set
; }
public
string
FirstName {
get
;
set
; }
public
string
Address {
get
;
set
; }
public
string
City {
get
;
set
; }
public
string
Region {
get
;
set
; }
public
string
PostalCode {
get
;
set
; }
public
string
Country {
get
;
set
; }
public
string
HomePhone {
get
;
set
; }
}
|
在查询的时候只需要,调用List方法的另一版本即可.
1
2
|
Expression exp =
new
Expression();
var items = exp.List<Employee, EmployeeContact>();
|
以上生成的SQL并不会获取所有字段,而是根据EmployeeContact和Employee相匹配的属性进行生成查询字段.
1
2
3
|
Select (LastName)
as
p_LastName,(FirstName)
as
p_FirstName,(Address)
as
p_Address,
(City)
as
p_City,(Region)
as
p_Region,(PostalCode)
as
p_PostalCode,(Country)
as
p_Country,(HomePhone)
as
p_HomePhone from Employees
|
存储过程描述
可以通过以下代码来描述一个存储过程:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
[Proc]
public
class
CustOrderHist
{
[ProcParameter]
public
string
CustomerID
{
get
;
set
;
}
[ProcParameter(Direction= System.Data.ParameterDirection.ReturnValue)]
public
int
Result
{
get
;
set
;
}
}
|
通过一个Proc属性来描述对象是一个存储过程描述对象,如果属性不指定名称则用对象名称作为存储过程名称调用;通过PorcParameter来描述一个属性对应的存储过程参数。这样描述后就可以进行一个存储过程执行
1
2
3
|
CustOrderHist p =
new
CustOrderHist();
p.CustomerID =
"ALFKI"
;
DBContext.ExecProc(p);
|
如果有输出类型参数,执行完成后组件会自动把输出参数值填充到对应的属性上.
当需执行存储过程返回一个对象列表的时候可以,通过以下方法调用即可:
1
2
3
|
CustOrderHist p =
new
CustOrderHist();
p.CustomerID =
"ALFKI"
;
var items = DBContext.ExecProcToObjects<OrderHist>(p);
|
其属性对应关系是属性名和字段一致即可,对象成员不需要添加任何属性描述。
事务
- 普通事务
1234567891011121314
Expression exp =
new
Expression();
exp = Employee.employeeID == 5;
using
(IConnectinContext cc = DBContext.Context1)
{
cc.BeginTransaction();
exp.Delete<Order>();
exp.Delete<Employee>();
cc.Commit();
}
DBContext.TransactionExecute(c => {
exp.Delete<Order>();
exp.Delete<Employee>();
});
- 跨方法事务