SECLAB技术与学术社区

 找回密码
 立即注册
12
返回列表 发新帖
楼主: limingzuo

DOS.ORM基础教程

[复制链接]

13

主题

34

帖子

117

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
117
 楼主| 发表于 2016-11-29 16:23:02 | 显示全部楼层
Dos.ORM使用方法 - 子查询
数据库组件 Hxj.Data (十三) (子查询)
查询条件的值来自另外一次查询。

例如  sqlserver sql:   

select * from products where categoryid=(select top 1 categoryid from categories where categoryname=’produce’)

这条sql的实现代码如下:

DbSession.Default.From<Products>()
                .Where(Products._.CategoryID
                .SubQueryEqual(DbSession.Default.From<Categories>().Where(Categories._.CategoryName == "Produce").Select(Categories._.CategoryID).Top(1)))
                .ToList();对比一下组件生成的sql

Text: SELECT * FROM [Products] WHERE [Products].[CategoryID] = ( SELECT TOP 1 [Categories].[CategoryID] FROM [Categories] WHERE [Categories].[CategoryName] = @174b5c8999e2480594cdc08ab4d8e5bd) Parameters: @174b5c8999e2480594cdc08ab4d8e5bd[String] = Produce



子查询方法对应sql表如下:

方法名称 sql
SubQueryEqual  =
SubQueryNotEqual <>
SubQueryLess <
SubQueryLessOrEqual <=
SubQueryGreater >
SubQueryGreaterOrEqual >=
SubQueryIn in
SubQueryNotIn not in



再写一个例子

DbSession.Default.From<Products>()
                .Where(Products._.CategoryID
                .SubQueryNotIn(DbSession.Default.From<Categories>().Where(Categories._.CategoryName == "Produce").Select(Categories._.CategoryID)))
                .ToList();生成的sql如下

Text: SELECT * FROM [Products] WHERE [Products].[CategoryID] NOT IN ( SELECT [Categories].[CategoryID] FROM [Categories] WHERE [Categories].[CategoryName] = @32365a219b864e5fbeb7959a6071d4c8) Parameters: @32365a219b864e5fbeb7959a6071d4c8[String] = Produce

子查询是不是也变的很简单的呢了。



下一节将讲述联合查询。

13

主题

34

帖子

117

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
117
 楼主| 发表于 2016-11-29 16:23:25 | 显示全部楼层
数据库组件 Hxj.Data (二十) (分页)
分页在前面好几节中的列子中已经出现,现在在详细讲解一下。

有三种方法去实现:

1、Top(int topCount) 方法

在sqlserver和msaccess中其实就是top , 在oracle通过rownum实现。



2、From(int startIndex, int endIndex)

从startIndex条到endIndex条之间的记录数。



3、Page(int pageSize, int pageIndex)

每页pageSize条,第pageIndex页



下面就依次对这三个方法举例说明。



top方法:

DbSession.Default.From<Products>()
                .Top(10)
                .ToList();该查询时查询products表中的前10条记录,生成的sql语句如下:(如何输出查看组件生成的sql)

Text: SELECT TOP 10 * FROM [Products]  

from方法:
DbSession.Default.From<Products>()
                .From(3, 8)
                .ToList();查找第3条到第8条的数据(包括第3条和第8条),生成的sql语句如下:

Text: SELECT * FROM ( SELECT TOP 6 * FROM ( SELECT TOP 8 * FROM [Products] ORDER BY [Products].[ProductID] ASC) AS tempIntable ORDER BY [ProductID] DESC) AS tempOuttable ORDER BY [ProductID] ASC  

page方法:
DbSession.Default.From<Products>()
                .Page(10, 2)
                .ToList();查询每页10条的第2页数据,sql语句如下:
Text: SELECT * FROM ( SELECT TOP 10 * FROM ( SELECT TOP 20 * FROM [Products] ORDER BY [Products].[ProductID] ASC) AS tempIntable ORDER BY [ProductID] DESC) AS tempOuttable ORDER BY [ProductID] ASC

可以看出from方法和page方法生成的sql语句是格式是一样的,其实page方法最终还是调用from方法,
如果from的startIndex参数等于1就会调用top方法来查询。

默认情况下我们并没有设置排序,组件则会自动添加一个排序,如果有主键则选主键排序,不然选一个其他列排序。

当然在很多情况下我们是需要设置哪些列排序的。
例如:
DbSession.Default.From<Products>()
                .Page(10, 2)
                .OrderBy(Products._.UnitPrice.Desc)
                .Where(Products._.CategoryID == 2)
                .ToList();查询条件是categoryid等于2,按照unitprice倒叙排序,每页10条的第2页数据。
生成的sql如下:
Text: SELECT * FROM ( SELECT TOP 2 * FROM [Products] WHERE [Products].[CategoryID] = @bee7551993404c8592f07f9b01710bb5 ORDER BY [Products].[UnitPrice] ASC) AS temp_table ORDER BY [UnitPrice] DESC Parameters: @bee7551993404c8592f07f9b01710bb5[Int32] = 2 这样的sql语句是不是出乎意料啊,原来符合条件的查询第二页只有2条数据,所以查询的时候就直接unitprice正序top 2就完结了。

我们把条件去掉再看看:
DbSession.Default.From<Products>()
                .Page(10, 2)
                .OrderBy(Products._.UnitPrice.Desc)
                //.Where(Products._.CategoryID == 2)
                .ToList();生成的sql如下:
Text: SELECT * FROM ( SELECT TOP 10 * FROM ( SELECT TOP 20 * FROM [Products] ORDER BY [Products].[UnitPrice] DESC) AS tempIntable ORDER BY [UnitPrice] ASC) AS tempOuttable ORDER BY [UnitPrice] DESC 这样算是正常的生成格式了。

以上的sql脚本都是在sql server2000下生成的。

其实在查询过程中组建还会自动查询了count()符合条件的记录数,所以在大数据的情况下,效率不会很好。

如果你设置组建的数据库是sql server2005则不会去查询 count,而是直接通过row_number()来查询,来获得更好的效率。(oracle则是rownum实现)

下面来sql server2005例子:
DbSession.Default.From<Products>()
                .Page(10, 2)
                .OrderBy(Products._.UnitPrice.Desc)
                //.Where(Products._.CategoryID == 2)
                .ToList();代码还是上面的例子的,生成的sql:
Text: SELECT * FROM ( SELECT *,row_number() over( ORDER BY [Products].[UnitPrice] DESC) AS tmp__rowid FROM [Products] ) AS tmp_table WHERE (tmp__rowid BETWEEN 11 AND 20) 方法的调用还是一样的,所以如果from的参数startIndex等于1,还是优先使用top,并没有使用row_numer()。


当然你觉得分页还不够理想,可以自己写sql或者存储过程。

13

主题

34

帖子

117

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
117
 楼主| 发表于 2016-11-29 16:24:43 | 显示全部楼层
Dos.ORM使用方法 - 事务
数据库组件 Hxj.Data (十七) (事务)
首先对事务进行了升级,所以又要重新去下载一下新版本。



组件提供了简单的事务,并没有过多的封装。

先上个例子:

using (DbTrans trans = DbSession.Default.BeginTransaction())
{
    DbSession.Default.Update<Products>(Products._.ProductName, "apple", Products._.ProductID == 1, trans);
    DbSession.Default.Update<Products>(Products._.ProductName, "egg", Products._.ProductID == 2, trans);
    trans.Commit();
}trans.Commit(); 必须提交,不然就执行不成功了。 如果使用try catch的写法如下:DbTrans trans = DbSession.Default.BeginTransaction();
try
{
    DbSession.Default.Update<Products>(Products._.ProductName, "apple", Products._.ProductID == 1, trans);
    DbSession.Default.Update<Products>(Products._.ProductName, "egg", Products._.ProductID == 2, trans);
    trans.Commit();
}
catch
{
    trans.Rollback();
}
finally
{
    trans.Close();
}insert、update、delete方法都提供了DbTransaction参数所以也只是添加,修改,删除可以提交事务。

存储过程和直接sql执行也是可以添加事务的。

例如:

DbTrans trans = DbSession.Default.BeginTransaction();
DbSession.Default.FromProc("Ten Most Expensive Products").SetDbTransaction(trans);通过SetDbTransaction方法来添加事务。

FromSql也是一样。



也可以设置事务的级别,如下:

DbTrans trans = DbSession.Default.BeginTransaction(IsolationLevel.ReadCommitted);

总的来说组件对事务封装的比较简单,写起来似乎不是特别方便,有待进一步的改进。



在使用过程中觉得不方便或者需要改进的希望大家能提出来,谢谢。



下一节将讲述批处理。

数据库组件 Hxj.Data (十七) (事务)
首先对事务进行了升级,所以又要重新去下载一下新版本。



组件提供了简单的事务,并没有过多的封装。

先上个例子:

using (DbTrans trans = DbSession.Default.BeginTransaction())
{
    DbSession.Default.Update<Products>(Products._.ProductName, "apple", Products._.ProductID == 1, trans);
    DbSession.Default.Update<Products>(Products._.ProductName, "egg", Products._.ProductID == 2, trans);
    trans.Commit();
}trans.Commit(); 必须提交,不然就执行不成功了。 如果使用try catch的写法如下:DbTrans trans = DbSession.Default.BeginTransaction();
try
{
    DbSession.Default.Update<Products>(Products._.ProductName, "apple", Products._.ProductID == 1, trans);
    DbSession.Default.Update<Products>(Products._.ProductName, "egg", Products._.ProductID == 2, trans);
    trans.Commit();
}
catch
{
    trans.Rollback();
}
finally
{
    trans.Close();
}insert、update、delete方法都提供了DbTransaction参数所以也只是添加,修改,删除可以提交事务。

存储过程和直接sql执行也是可以添加事务的。

例如:

DbTrans trans = DbSession.Default.BeginTransaction();
DbSession.Default.FromProc("Ten Most Expensive Products").SetDbTransaction(trans);通过SetDbTransaction方法来添加事务。

FromSql也是一样。



也可以设置事务的级别,如下:

DbTrans trans = DbSession.Default.BeginTransaction(IsolationLevel.ReadCommitted);

总的来说组件对事务封装的比较简单,写起来似乎不是特别方便,有待进一步的改进。



在使用过程中觉得不方便或者需要改进的希望大家能提出来,谢谢。



下一节将讲述批处理。

13

主题

34

帖子

117

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
117
 楼主| 发表于 2016-11-29 16:25:41 | 显示全部楼层
Dos.ORM使用方法 - 批处理
批处理就是提交的脚本不是马上执行,而是到一定数量才提交。还是先上例子using (DbBatch batch = DbSession.Default.BeginBatchConnection())
{
    batch.Update<Products>(Products._.ProductName, "apple", Products._.ProductID == 1);
    batch.Update<Products>(Products._.ProductName, "pear", Products._.ProductID == 2);
    batch.Update<Products>(Products._.ProductName, "orange", Products._.ProductID == 3);
}生成的sql:

Text: UPDATE [Products] SET [ProductName]=@3d40ca14f83644f18269874c99e621a5 WHERE [Products].[ProductID] = @50999b135fbf4f068f89bda7ab341ac3;UPDATE [Products] SET [ProductName]=@fe1ea91dd6e14260813ea19abbc245eb WHERE [Products].[ProductID] = @6bef4d78041549fa817c3363bd847d41;UPDATE [Products] SET [ProductName]=@075f1b4639c446be88bfc33638b1f754 WHERE [Products].[ProductID] = @588a5481ff3d4ff58fc304293173b6ab; Parameters: @3d40ca14f83644f18269874c99e621a5[String] = apple @50999b135fbf4f068f89bda7ab341ac3[Int32] = 1 @fe1ea91dd6e14260813ea19abbc245eb[String] = pear @6bef4d78041549fa817c3363bd847d41[Int32] = 2 @075f1b4639c446be88bfc33638b1f754[String] = orange @588a5481ff3d4ff58fc304293173b6ab[Int32] = 3

默认是10条sql执行一次。也可以自定义。



DbBatch batch = DbSession.Default.BeginBatchConnection(20)这样就设置了20条sql执行一次。

并可以设置内部事务级别.

DbBatchbatch = DbSession.Default.BeginBatchConnection(20, IsolationLevel.ReadCommitted)

也可强制性执行:using (DbBatch batch = DbSession.Default.BeginBatchConnection())
{
    batch.Update<Products>(Products._.ProductName, "apple", Products._.ProductID == 1);
    batch.Update<Products>(Products._.ProductName, "pear", Products._.ProductID == 2);
    batch.Execute();
    batch.Update<Products>(Products._.ProductName, "orange", Products._.ProductID == 3);
}执行batch.Execute(),就会将之前的sql脚本先提交。

生成的sql:

Text: UPDATE [Products] SET [ProductName]=@1ae514d0db3247acb196b8d55110a8e8 WHERE [Products].[ProductID] = @c694d46594324eec9db0d1e26a3ea499;UPDATE [Products] SET [ProductName]=@4b1d4e07b6cc45d994e65c5eeac12619 WHERE [Products].[ProductID] = @58fa8ceaede745f0ac541d5c45574309; Parameters: @1ae514d0db3247acb196b8d55110a8e8[String] = apple @c694d46594324eec9db0d1e26a3ea499[Int32] = 1 @4b1d4e07b6cc45d994e65c5eeac12619[String] = pear @58fa8ceaede745f0ac541d5c45574309[Int32] = 2 Text: UPDATE [Products] SET [ProductName]=@c3a36b7a94754cd884ee27d4e42199a5 WHERE [Products].[ProductID] = @550ebc4788f54105ababe5d6bdc9cf2a; Parameters: @c3a36b7a94754cd884ee27d4e42199a5[String] = orange @550ebc4788f54105ababe5d6bdc9cf2a[Int32] = 3

看输出的脚本分两次执行的。



try catch的写法如下:

DbBatch batch = DbSession.Default.BeginBatchConnection();
try
{
    batch.Update<Products>(Products._.ProductName, "apple1", Products._.ProductID == 1);
    batch.Update<Products>(Products._.ProductName, "pear1", Products._.ProductID == 2);
    batch.Update<Products>(Products._.ProductName, "orange1", Products._.ProductID == 3);
}
catch
{
    //do something
}
finally
{
    batch.Close();
}效果和第一个例子是一样的。

批处理也是比较简单的。



作者:steven hu
出处:http://www.cnblogs.com/huxj
MSN: cn_huxj@hotmail.com
交流QQ群:60831381
版权声明:欢迎任何网络媒体和网站转载本人博客的内容,只需注明作者和主博客文章地址的链接。另有约定者除外。
此声明随时可能修改,不另行通知。

13

主题

34

帖子

117

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
117
 楼主| 发表于 2016-11-29 16:26:33 | 显示全部楼层
Dos.ORM使用方法 - 缓存
数据库组件 Hxj.Data (二十二) (缓存)
首先我是做web开发的,所以很多会偏向web的,组件使用的缓存是System.Web.HttpRuntime.Cache。



组件在默认情况下是关闭缓存的。

所以要开启缓存查询要做一下配置。

先看一下例子配置:

<configSections>
    <section name="HxjCacheConfig" type="Hxj.Data.CacheConfiguration,Hxj.Data"/>        
</configSections>
<HxjCacheConfig enable="true">
    <entities>
        <add key="NorthwindConnectionString.Products" value="60"></add>
    </entities>
</HxjCacheConfig>
<connectionStrings>
    <add name="NorthwindConnectionString" connectionString="Data Source=ricci\hu;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

HxjCacheConfig 节点的 enable 表示是否开启缓存,默认是关闭状态,除非显式开启 enable="true"。



entities 节点下的配置就是对实体查询的缓存配置。

<add key="NorthwindConnectionString.Products" value="60"></add>

表示connectionStrings中的节点name="NorthwindConnectionString"的连接下的Products表配置.

value="60" 表示缓存60秒。



所以一定要connectionStrings的name加上实体名,不然配置无效。




当然value也可以是缓存依赖的文件。

<add key="NorthwindConnectionString.Products" value="1.txt"></add>表示Products表的查询缓存依赖程序根目录下的1.txt文件。组件会判断该文件是否存在,不存在则该配置无效。



那测试一下缓存配置。

List<Products> list = new List<Products>();
for (int i = 0; i < 2; i++)
{
    list.Add(DbSession.Default.From<Products>().ToFirst());
}


for (int i = 0; i < 3; i++)
{
    list.Add(DbSession.Default.From<Products>().ToFirst());
}我们设置 enable="false",关闭缓存。

看一下组件执行的sql:

Text: SELECT TOP 1 * FROM [Products] WHERE ([Products].[ProductID] = @1a6b9589aa23444c9e6e13049220c19d) Parameters: @1a6b9589aa23444c9e6e13049220c19d[Int32] = 1 Text: SELECT TOP 1 * FROM [Products] WHERE ([Products].[ProductID] = @61ce100cf8074e76abc8d205b3ec0a2e) Parameters: @61ce100cf8074e76abc8d205b3ec0a2e[Int32] = 1 Text: SELECT TOP 1 * FROM [Products] WHERE ([Products].[ProductID] = @c99aa7541e1543aca08c28f65c7b1d4d) Parameters: @c99aa7541e1543aca08c28f65c7b1d4d[Int32] = 1 Text: SELECT TOP 1 * FROM [Products] WHERE ([Products].[ProductID] = @c8f9ce2df63f451aa09cbc4fa62acd9c) Parameters: @c8f9ce2df63f451aa09cbc4fa62acd9c[Int32] = 1 Text: SELECT TOP 1 * FROM [Products] WHERE ([Products].[ProductID] = @d786265aa9bd40bfbc253814d6c16b13) Parameters: @d786265aa9bd40bfbc253814d6c16b13[Int32] = 1



执行了5次查询。

再设置 enable="true",开启缓存。

再查看一下执行的sql:

Text: SELECT TOP 1 * FROM [Products] WHERE ([Products].[ProductID] = @075ce07012884a21878604c197ddb7cc)Parameters: @075ce07012884a21878604c197ddb7cc[Int32] = 1

只执行了一次sql连接。

后面的四次都是直接从缓存中读取的。

再次执行,所有的查询都是直接从缓存中返回回来,没有连接数据库。



测试缓存文件依赖:

List<Products> list = new List<Products>();
for (int i = 0; i < 2; i++)
{
    list.Add(DbSession.Default.From<Products>().Where(Products._.ProductID == 3).ToFirst());
}

FileHelper.AppendText(Server.MapPath("~/1.txt"), "abc");System.Threading.Thread.Sleep(2000);for (int i = 0; i < 3; i++)
{
    list.Add(DbSession.Default.From<Products>().Where(Products._.ProductID == 3).ToFirst());
}查看sql:

Text: SELECT TOP 1 * FROM [Products] WHERE ([Products].[ProductID] = @aa4964dae36c479792186ea95ce10b6e) Parameters: @aa4964dae36c479792186ea95ce10b6e[Int32] = 3 Text: SELECT TOP 1 * FROM [Products] WHERE ([Products].[ProductID] = @e614d6bbc84c4603b159e30644665e07) Parameters: @e614d6bbc84c4603b159e30644665e07[Int32] = 3



执行了两次,当修改文件后,缓存失效重新从数据库查询数据。



其实测试的有时候输出一条sql,可能是执行的太快,缓存失效来不及。

所以后来才加了System.Threading.Thread.Sleep(2000);

再次刷新,就只输出一条sql,只执行一次,也就是修改1.txt文件后才重新查询数据库。



查询判断是否有缓存配置是根据 From<Products>()这里的Products来判断是否存在缓存配置的。



对于ToDataReader()查询,是不会缓存的。

当然用到ToDataReader的查询的其他方法也不会缓存,例如上一节中的Exists<TEntity>(WhereClip where)方法。



下一节将讲述自定义缓存。

数据库组件 Hxj.Data (二十三) (自定义缓存)
配置的缓存并不能满足我们的需求,例如有时候需要刷新缓存,或者某次查询缓存时间或者缓存依赖不一样等。



刷新缓存

例如:

DbSession.Default.From<Products>().Where(Products._.ProductID == 1).Refresh().ToFirst();Refresh()方法设置了刷新缓存,即不从缓存中读取,直接从数据库读取最新数据,并重新缓存。



设置查询的缓存有效期。

例如:

DbSession.Default.From<Products>().Where(Products._.ProductID == 1).SetCacheTimeOut(180).ToFirst();设置该查询的缓存时间为180秒。

该设置会覆盖默认缓存配置,并且缓存配置中没有实体设置,也会缓存。



当下次执行:

DbSession.Default.From<Products>().Where(Products._.ProductID == 1).ToFirst();在180秒内缓存有效。



设置缓存依赖。

例如:

System.Web.Caching.CacheDependency cacheDep = new System.Web.Caching.CacheDependency(Server.MapPath("~/2.txt"));

DbSession.Default.From<Products>().Where(Products._.ProductID == 1).SetCacheDependency(cacheDep).ToFirst();设置该查询缓存的缓存依赖为2.txt文件。

该设置会覆盖默认缓存配置,并且缓存配置中没有实体设置,也会缓存。



当再次执行:

DbSession.Default.From<Products>().Where(Products._.ProductID == 1).ToFirst();

如果2.txt文件没有改变,缓存是不会失效的。



当然这几个方法也可以同时使用。



缓存是根据生成的sql作为缓存依据的,所以写法确保一致,特别是条件的先后顺序,才能有效利用缓存,否则就要浪费内存了。

13

主题

34

帖子

117

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
117
 楼主| 发表于 2016-11-29 16:27:23 | 显示全部楼层
Dos.ORM使用方法 - 跟踪记录Sql执行日志
数据库组件 Hxj.Data (十) (输出组件执行的sql)
前面有TX留言问分页的sql是怎么样的,看完这篇你也就知道了。

组件可以输出执行的sql,方便查看sql生成的语句是否有问题。

通过注册事件来输出sql

DbSession.Default.RegisterSqlLogger(database_OnLog);
private string sql;void database_OnLog(string logMsg){    //保存执行的DbCommand (sql语句和参数)    sql += "<br />" + logMsg;}
然后通过执行
DbSession.Default.UnregisterSqlLogger(database_OnLog);来注销注册的事件。

这里是例子是asp.net


DbSession.Default.From<Products>()                .InnerJoin<Suppliers>(Suppliers._.SupplierID == Products._.SupplierID)                .Page(10, 2)                .Select(Products._.ProductID, Products._.ProductName)                .ToDataTable();

执行上面的语句输出的sql语句如下:

SELECT   * FROM  ( SELECT   TOP 10 * FROM  ( SELECT   TOP 20 [Products].[ProductID],[Products].[ProductName] FROM [Products] INNER JOIN [Suppliers] ON ([Suppliers].[SupplierID] = [Products].[SupplierID])  ORDER BY [Products].[ProductID] ASC) AS tempIntable   ORDER BY [ProductID] DESC) AS tempOuttable   ORDER BY [ProductID] ASC     


再来一个
DbSession.Default.From<Products>().Where(Products._.CategoryID == 2).ToFirst();

生成的sql语句如下
Text: SELECT TOP 1 * FROM [Products] WHERE [Products].[CategoryID] = @ae2b9c6a112545e5b56fa6dc70f32ac1 Parameters: @ae2b9c6a112545e5b56fa6dc70f32ac1[Int32] = 2

这下组件生成的sql语句可以一目了然了,sql语句的输出也大大方便了调试。


下一节将讲述WhereClip(条件)的生成。






您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

SEC LAB ( 赣ICP备15007304号 )

GMT+8, 2017-5-26 17:13 , Processed in 1.296908 second(s), 17 queries .

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表