kiss.data的简单记录-.net-火龙果软件工程

分享到

kiss.data的简单记录

作者 Jonathan Allen，火龙果软件发布于 2014-03-14

kiss.data是golang的数据库访问类库kdb的c#版本，最初是先有c#版本的，后来根据golang的设计重构了。

刚写完主干部分，还在测试中。

特性

1.支持主流数据库

2.支持常见ORM操作

3.智能数据转换

4....

基本类型

DbContent类

封装对db的一些基本操作。

IDataObjectAdapter接口

对象字段访问的接口，包含四个方法

1.public interface IDataObjectAdapter
2.{
3.    void Set(string field, object value); //设置字段的值
4.    object Get(string field); //获取字段的值
5.    IEnumerable Fields(); //获取所有字段
6.    bool Contains(string field); //判断字段是否存在, 可能多余，因为根据Fields的结果也可以判断字段是否存在
7.}

ISqlExpression接口

Sql表达式的接口，定义如下

2.public interface ISqlExpression
3.{
4.    NodeType NodeType();
5.}

环境

假设数据库连接字符串配置如下

表结构如下

1.CREATE TABLE [ttable](
2.    [pk]        [bigint] IDENTITY(1,1) NOT NULL,
3.    [cbool]     [bit] NULL,
4.    [cint]      [int] NULL,
5.    [cfloat]    [float] NULL,
6.    [cnumeric]  [numeric](10, 4) NULL,
7.    [cstring]   [nvarchar](100) NULL,
8.    [cdatetime] [datetime] NULL,
9.    [cguid]     [uniqueidentifier] NULL,
10.    [cbytes]    [binary](100) NULL,
11.
12.    CONSTRAINT [PK_ttable] PRIMARY KEY CLUSTERED 
13.    (
14.        [pk] ASC
15.    )
16.)

代码中的对象定义如下

2.[DbTable(Name = "ttable")]
3.public class CEntity
4.{
5.    [DbColumn(IsKey = true, UpdateAble = false, InsertAble = false)]
6.    public int PK { get; set; }
7.
8.    public bool CBool { get; set; }
9.
10.    public int CInt { get; set; }
11.
12.    public float CFloat { get; set; }
13.
14.    [DbColumn(Name = "CNumeric")]
15.    public decimal ColNumeric { get; set; }
16.
17.    public string CString { get; set; }
18.
19.    public DateTime CDateTime;
20.
21.    public Guid CGuid;
22.
23.}

直接执行sql 脚本

用IDataObjectAdapter传参数

1.using (DbContent db = new DbContent("mssql"))
2.{
3.    var data = Kiss.Core.Adapter.Dictionary();
4.    data.Set("cint", 101);
5.    data.Set("pk", 11606);
6.    db.TextNonQuery("update TTABLE set cint = @cint where pk = @pk", data);
7.}

用params object[]传参数

1.using (DbContent db = new DbContent("mssql"))
2.{
3.    var i = db.TextNonQuery("update TTABLE set cint = @cint where pk = @pk", 102, 11606);
4.}

创建Text对象

1.using (DbContent db = new DbContent("mssql"))
2.{
3.    var exp = new Kiss.Data.Expression.Text("update TTABLE set cint = @cint where pk = @pk")
4.        .Set("cint", 103)
5.        .Set("pk", 11606);
6.
7.    db.ExecuteNonQuery(exp);
8.}

执行存储过程

测试存储过程定义如下

2.create procedure [usp_query](@cint int)
3.as
4.begin
5.        select * from ttable where cint > @cint;
6.end;
7.
8.create procedure [usp_exec](@cint int)
9.as
10.begin
11.         delete from ttable where cint = @cint;  
12.end;
13.
14.create procedure [usp_inout](@x int, @y int output, @sum int output)
15.as
16.begin
17.        set @sum = @x + @y;
18.        set @y = 2 * @y
19.end;

用IDataObjectAdapter传参数

1.using (DbContent db = new DbContent("mssql"))
2.{
3.    Dictionary<string, object> data = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);
4.    data["cint"] = 101;
5.    var table = db.ProcedureReader("usp_query", Kiss.Core.Adapter.Dictionary(data)).ToTable();
6.}

用params object[]传参数

1.using (DbContent db = new DbContent("mssql"))
2.{
3.    db.ProcedureNonQuery("usp_exec", 11606);
4.}

返回output参数

1.using (DbContent db = new DbContent("mssql"))
2.{
3.    var data = Kiss.Core.Adapter.Dictionary();
4.    data.Set("x", 2);
5.    data.Set("y", 7);
6.    IExecuteResult r;
7.    db.ProcedureNonQuery("usp_inout", data, out r);
8.    var output = r.Output();
9.    Console.WriteLine("y:{0},sum:{1}", output["y"], output["sum"]);
10.}

通过Procedure对象执行

1.using (DbContent db = new DbContent("mssql"))
2.{
3.    var exp = new Kiss.Data.Expression.Procedure("usp_exec")
4.        .Set("cint", 64);
5.
6.    db.ExecuteNonQuery(exp);               
7.}

如果定义了存储过程对应的接口，可以通过动态代理执行，比如接口定义如下

1.public interface IPorxyTest
2.{
3.    IDataReader usp_query(int cint);
4.
5.    [DbProcedure(Name = "usp_exec")]
6.    IDataReader Exec([DbParameter(Name = "cint")] int c);
7.
8.    IDataReader usp_inout(int x, ref int y, out int sum);
9.}

可以这么执行

1.using (DbContent db = new DbContent("mssql"))
2.{
3.    IPorxyTest proxy = FunctionProxy.Create(db);
4.    var reader = proxy.usp_query(101);
5.    reader.Dispose();
6.}
7.
8.using (DbContent db = new DbContent("mssql"))
9.{
10.    IPorxyTest proxy = FunctionProxy.Create(db);
11.    var reader = proxy.Exec(101);
12.    Console.WriteLine(reader.RecordsAffected);
13.    reader.Dispose();
14.}
15.
16.using (DbContent db = new DbContent("mssql"))
17.{
18.    int x = 3;
19.    int y = 7;
20.    int sum;
21.
22.    IPorxyTest proxy = FunctionProxy.Create(db);
23.    proxy.usp_inout(x, ref y, out sum);
24.    Console.WriteLine("y:{0},sum:{1}", y, sum);
25.}

Insert

通过insert对象执行

1.using (DbContent db = new DbContent("mssql"))
2.{
3.    var insert = new Kiss.Data.Expression.Insert("ttable")
4.        .Set("cbool", true)
5.        .Set("cint", 42)
6.        .Set("cfloat", 3.14)
7.        .Set("cnumeric", 1.1)
8.        .Set("cstring", "string")
9.        .Set("cdatetime", "2004-07-24");
10.
11.    db.ExecuteNonQuery(insert);
12.}

通过IDataObjectAdapter执行

1.using (DbContent db = new DbContent("mssql"))
2.{
3.    var data = Kiss.Core.Adapter.Dictionary();
4.    data.Set("A_cbool", true);
5.    data.Set("A_cint", 42);
6.    data.Set("A_cfloat", 3.14);
7.    data.Set("A_cnumeric", 1.1);
8.    data.Set("A_cstring", "string");
9.    data.Set("A_cdatetime", "2004-07-24");
10.
11.    db.Table("ttable").Insert(data, (x)=> "A_" + x, null, new string[]{"A_cint"});
12.}

通过ActiveEntity执行

1.using (DbContent db = new DbContent("mssql"))
2.{
3.    var data = CEntity.NewEntity();
4.    ActiveEntity ae = new ActiveEntity(db);
5.    var pk = ae.Add(data);
6.    Console.WriteLine(pk);
7.}

Update

通过Update对象执行

1.using (DbContent db = new DbContent("mssql"))
2.{
3.    var update = new Kiss.Data.Expression.Update("ttable");
4.    update
5.        .Set("cstring", "new string")
6.        .Set("cdatetime", DateTime.Now)
7.    .Limit(10)
8.    .Where
9.        .EqualsTo("cint", 101);
10.
11.    db.ExecuteNonQuery(update);
12.}

通过IDataObjectAdapter执行

1.using (DbContent db = new DbContent("mssql"))
2.{
3.    var data = Kiss.Core.Adapter.Dictionary();
4.    data.Set("cint", 420);
5.    data.Set("cfloat", 3.141);
6.    data.Set("cnumeric", 1.12);
7.
8.    db.Table("ttable").Update(data, "cint", 101);
9.}

更新字段

2.using (DbContent db = new DbContent("mssql"))
3.{
4.    var where = new Where()
5.        .EqualsTo("cint", 101);
6.
7.    db.Table("ttable").UpdateColumn("cstring", "a string", where);
8.}

通过主键更新

2.using (DbContent db = new DbContent("mssql"))
3.{
4.    CEntity entity = CEntity.NewEntity();
5.    ActiveEntity ae = new ActiveEntity(db);
6.    ae.UpdateByKey(entity, 11606);
7.}

根据字段更新

1.using (DbContent db = new DbContent("mssql"))
2.{
3.    CEntity entity = CEntity.NewEntity();
4.    ActiveEntity ae = new ActiveEntity(db);
5.    ae.UpdateByFields(entity, (x) => x.CInt, 101);
6.}

根据lamda条件更新

2.using (DbContent db = new DbContent("mssql"))
3.{
4.    var data = new Dictionary<string, object>();
5.    data["cstring"] = "some string";
6.    data["cfloat"] = 3.14 * 3.14;
7.    ActiveEntity ae = new ActiveEntity(db);
8.    ae.UpdateFields((x) => x.CInt > 101 && x.CInt < 202, data);
9.}

Query

通过Query对象执行

1.using (DbContent db = new DbContent("mssql"))
2.{
3.    var query= new Data.Expression.Query("ttable");
4.    query.Where
5.        .EqualsTo("cint" , 10100)
6.        .EqualsTo("cint", 20200);
7.
8.    var reader = db.ExecuteReader(query);
9.    reader.Dispose();
10.}

根据列查询

1.using (DbContent db = new DbContent("mssql"))
2.{
3.    var reader = db.Table("ttable").Read("cint", 10100, "cint", 20200);
4.    reader.Dispose();
5.}

读取某个字段的值

2.using (DbContent db = new DbContent("mssql"))
3.{
4.    var i = db.Table("ttable").ReadCell("cint", "pk", "11606");
5.}

查询某一列

2.using (DbContent db = new DbContent("mssql"))
3.{
4.    var list = db.Table("ttable").ReadColumn("cstring", false, "cint", SqlOperator.GreaterThan, 202);
5.}

通过ActiveEntity执行

2.using (DbContent db = new DbContent("mssql"))
3.{                
4.    ActiveEntity ae = new ActiveEntity(db);
5.    var list = ae.QueryByFields((x)=>x.PK, 11606);
6.}

通过lamda执行

1.using (DbContent db = new DbContent("mssql"))
2.{
3.    ActiveEntity ae = new ActiveEntity(db);
4.    var list = ae.Query((x) => x.CInt > 101 && x.CInt < 20200);
5.}

注意

要确保关闭DbConnection，特别是返回IDataReader时(此时DbContent不会自动关闭DbConnection)

FAQ

默认实体类型名字段名要和表名列名一致:

故意这么设计的，取不一样的名字有什么好处？当然也可以自定义映射关系

不追求性能:

不追求某一项的性能指标，加班到半夜和凌晨起来改bug和性能基本上没什么关系

不支持多表查询:

故意这么设计的，如果有复杂的多表关联还是改掉为宜，或者用view

不支持缓存:

缓存不应该在数据层处理

不支持代码生成: