项目中一些常用操作记录

2016-12-30 09:55:33来源:oschina作者:深圳大道人点击

第七城市
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Threading.Tasks;
using JianKu.Dal;
using JianKu.Model;
using Dos.ORM;
namespace LiteORM
{
class Program
{
static void Main(string[] args)
{
//ORM测试
var str = "";
/*SELECT TOP 10 * FROM [TestTable]*/
//Console.WriteLine("==================================");
//str = "";
//var list = DataRootBase.Context.From().Top(10).ToList();
//foreach (var item in list)
//{
//str += string.Format("{0}---{1}---{2}---{3}---{4}/n", item.D_Id, item.D_Name, item.D_Password, item.D_Else, item.D_Amount);
//}
//Console.WriteLine(str); /*===============================================================================================*/
/* exec sp_executesql N' SELECT count(*) as r_cnt FROM [TestTable] WHERE [TestTable].[D_Name] LIKE @D_Name1',N'@D_Name1 nvarchar(4)',@D_Name1=N'名称1%' */
//Console.WriteLine("==================================");
//str = "";
//var count = DataRootBase.Context.From()
//.Where(d => d.D_Name.StartsWith("名称1"))
//.Count();
//Console.WriteLine(count); /*===============================================================================================*/
/*SELECT [TestTable].[D_Id],[TestTable].[D_Name] FROM [TestTable]*/
//Console.WriteLine("==================================");
//str = "";
//var list1 = DataRootBase.Context.From().Select(d => new { d.D_Id, d.D_Name }).Top(5).ToList();
//foreach (var item in list1)
//{
//str += string.Format("{0}---{1}/n", item.D_Id, item.D_Name);
//}
//Console.WriteLine(str); /*===============================================================================================*/
///*SELECT TOP 5 [TestTable].[D_Id] AS [ID],[TestTable].[D_Name] AS [UserName] FROM [TestTable] */
//Console.WriteLine("==================================");
//str = "";
//var list2 = DataRootBase.Context.From().Select(d => new { ID = d.D_Id, UserName = d.D_Name }).Top(5).ToDataTable();
//foreach (DataRow item in list2.Rows)
//{
//str += string.Format("{0}---{1}/n", item["ID"], item["UserName"]);
//}
//Console.WriteLine(str);
/*======================================视图分页=================================================*/
//强类型
//str = "";
//var list2 = DataRootBase.Context.From().Page(10,10).ToList();
//foreach (var item in list2)
//{
//str += string.Format("{0}---{1}/n", item.D_Id, item.D_Name);
//}
//Console.WriteLine(str);
//Console.ReadKey();
//弱类型
//str = "";
//var list2 = DataRootBase.Context.From("vw_test").Page(10, 10).OrderBy(new OrderByClip("D_Id", Dos.ORM.OrderByOperater.ASC)).ToDataTable();
//foreach (DataRow item in list2.Rows)
//{
//str += string.Format("{0}---{1}/n", item["D_Id"], item["D_Name"]);
//}
//Console.WriteLine(str);
//Console.ReadKey(); /*======================================执行存储过程 带输入输出参数 且有多个结果集=================================================*/
/*存储过程
ALTER PROCEDURE [dbo].[sp_test]
@userId INT
AS
BEGIN
SELECT TOP 5 * FROM dbo.TestTable
SELECT para1='张三',para2='男',para3='21',userCount=15662
END
*/
//DataSet ds = DataRootBase.Context.FromProc("sp_test")
//.AddInParameter("@userId", DbType.Int32,100)
//.ToDataSet();
//str += "==============datatable1数据====================/n";
//foreach (DataRow item in ds.Tables[0].Rows)
//{
//str += string.Format("{0}---{1}/n", item["D_Id"], item["D_Name"]);
//}
//str += "==============datatable2数据====================/n";
//foreach (DataRow item in ds.Tables[1].Rows)
//{
//str += string.Format("{0}---{1}---{2}---{3}/n", item["para1"], item["para2"], item["para3"], item["userCount"]);
//}
//Console.WriteLine(str);
//Console.ReadKey(); /*===============================================================================================*/
/* exec sp_executesql N'INSERT INTO [TestTable] ([D_Amount],[D_Name]) VALUES (@D_Amount2,@D_Name3);select scope_identity()',
* N'@D_Amount2 decimal(4,0),@D_Name3 nvarchar(4)',@D_Amount2=5811,@D_Name3=N'我是李四' */
//Console.WriteLine("==================================");
//str = "";
//var newModel = new TestTable()
//{
//D_Name="我是李四",
//D_Amount = 5811
//};
//var count1 = DataRootBase.Context.Insert(newModel);
//Console.WriteLine(count1); /*===============================================================================================*/
/*=====批量插入方法,ORM内部存在事务,不用担心某些数据插入成功,某些数据插入失败。===============*/
//var newList = new List();
//newList.Add(new TestTable()
//{
//D_Name = "200我是李四",
//D_Amount = 5811
//});
//newList.Add(new TestTable()
//{
//D_Name = "201我是李四",
//D_Amount = 5888
//});
////
//var count = DataRootBase.Context.Insert(newList);
//Console.WriteLine(count);
/*===============================================================================================*/
/* exec sp_executesql N'UPDATE [TestTable] SET [D_Name]=@D_Name2 WHERE [TestTable].[D_Id] = @D_Id1',
* N'@D_Name2 nvarchar(6),@D_Id1 int',@D_Name2=N'我是修改后的',@D_Id1=10006*/
//var uptModel = new TestTable();
//uptModel.D_Name = "我是修改后的";//修改表中所有数据 使用UpdateAll
//var count = DataRootBase.Context.Update(uptModel, d => d.D_Id == 10006);
//Console.WriteLine(count);
//Console.ReadKey();
/*===============================================================================================*/
/*=======================================子查询条件修改==========================================*/
/*
//SQL语句:UPDATE table SET sex='man' WHERE id IN
//(SELECT id FROM table WHERE name='ITdos')
var uptModel = new TestTable();
uptModel.sex = "man";
var count2 = DB.Content.Update(uptModel,table._.id.SubQueryIn(
DB.Content.From
().Select(d => d.id).Where(d => d.name == "ITdos")
));
*/
/*=======================================删除操作==========================================*/
/*=======================================删除操作==========================================*/
/*
根据主键值删除单条数据:
//SQL语句:DELETE FROM table WHERE id=1
var count = DB.Context.Delete
(d => d.id == 1);
//或者简写:
var count = DB.Context.Delete
(1);
//同样也适用于Guid主键类型
var count = DB.Context.Delete
(d => d.id == Guid.Parse("..."));
//简写:
var count = DB.Context.Delete
(Guid.Parse("..."));
*
根据主键值批量删除数据:
//SQL语句:DELETE FROM table WHERE id IN(1,2,3)
var ids = new List();
ids.Add(1);
ids.Add(2);
ids.Add(3);
var count = DB.Context.Delete
(d => d.id.In(ids));
//也可以这样写:
var count = DB.Context.Delete
(d => d.id.In(1,2,3));
*
根据实体删除单条数据:
//SQL语句:DELETE FROM table WHERE id=1
var model = DB.Context.From
().First();
if(model == null)
{
return "不存在要删除的数据";
}
//会根据主键自动添加where条件:WHERE id=model.id
var count = DB.Context.Delete
(model);
//同以下写法:
var count = DB.Context.Delete
(d => d.id == model.id);
*
根据实体批量删除数据:
//SQL语句:DELETE FROM table WHERE id IN(1,2,3)
var list = DB.Context.From
().ToList();
//批量删除方法内部有事务,会自动添加where条件:WHERE id IN(list主键数据)
var count = DB.Context.Delete
(list);
//同以下写法:
var ids = list.Select(d => d.id).ToList();
var count = DB.Context.Delete
(d => d.id.In(ids));
*
子查询条件删除:
//SQL语句:DELETE FROM table WHERE id IN
//(SELECT id FROM table WHERE name='ITdos')
var count2 = DB.Content.Delete
(table._.id.SubQueryIn(
DB.Content.From
().Select(d => d.id).Where(d => d.name == "ITdos")
));
*/ /*=======================================直接执行SQL语句==========================================*/
/*=======================================直接执行SQL语句==========================================*/
/*
直接执行SQL语句:
//返回List
数据
var list = DB.Context.FromSql("SELECT * FROM table").ToList
();
//也可以指定任意类映射返回:
public class ViewTable
{
public string name {get;set;}
public string sex {get;set;}
}
//返回List,将成功映射name,sex。id无法映射,因为没有为ViewTable定义id属性。
var list = DB.Context.FromSql("SELECT id,name,sex FROM table").ToList();
*
执行带参SQL:
var list = DB.Context.FromSql("SELECT * FROM table WHERE name=@name AND id=@id")
.AddInParameter("@name", DbType.String, "ITdos")
.AddInParameter("@id", DbType.Int32, "1")
.ToList
();
//也可以先拼接好参数,再一次性传入
var params = new DbParameter[2];
params[0] = DbSession.Default.Db.DbProviderFactory.CreateParameter();
params[0].DbType = DbType.String;
params[0].ParameterName = "@name";
params[0].Value = "ITdos";
params[1] = DB.Context.Db.DbProviderFactory.CreateParameter();
params[1].DbType = DbType.Int32;
params[1].ParameterName = "@id";
params[1].Value = 1;
DB.Context.FromSql("SELECT * FROM table WHERE name=@name AND id=@id")
.AddParameter(params)
.ToDataTable();
*
返回类型还可以如下:
//返回DataReader
IDataReader ToDataReader()
//返回DataSet
DataSet ToDataSet()
//返回受影响的条数
int ExecuteNonQuery()
//返回单个值,第一行第一列
object ToScalar()
//返回执行类型的值
TResult ToScalar()
//返回第一条实体
TEntity ToFirst()
*/ /*=======================================执行存储过程==========================================*/
/*=======================================执行存储过程==========================================*/
/*
执行存储过程是通过FromProc方法来完成的。
执行无参数存储过程如下:
DbSession.Default.FromProc("Ten Most Expensive Products").ToDataTable();
"Ten Most Expensive Products"就是存储过程名称。
*
执行带参数的存储过程:
DbSession.Default.FromProc("Sales by Year")
.AddInParameter("Beginning_Date", DbType.DateTime, "1995-01-01")
.AddInParameter("Ending_Date", DbType.DateTime, "1996-12-01")
.ToDataTable();
数据库中该存储过程
create procedure "Sales by Year"
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year
FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date
GO
有两个参数,分别是Beginning_Date和Ending_Date。
* 存储过程和sql语句的执行类似,不过存储过程多了参数,就是会有输入输出参数。通过
AddInputOutputParameter方法添加输入输出参数
AddOutParameter方法添加输出参数
AddReturnValueParameter方法添加返回参数返回参数值,示例如下:
ProcSection proc = DbSession.Default.FromProc("testoutstore")
.AddInParameter("in1", System.Data.DbType.Int32, 1)
.AddOutParameter("out1", System.Data.DbType.Int32)
.AddOutParameter("out2", System.Data.DbType.String,100);
proc.ExecuteNonQuery();
Dictionary returnValue = proc.GetReturnValues();
foreach (KeyValuePair kv in returnValue)
{
Response.Write("ParameterName:" + kv.Key + ";ReturnValue:" + Convert.ToString(kv.Value));
Response.Write("
");
}
其中GetReturnValues()方法就是回去返回值。
*/ /*=======================================Where条件查询==========================================*/
/*=======================================Where条件查询==========================================*/
/*
普通的Where条件写法:
//SQL语句:SELECT * FROM table WHERE name='ITdos' AND (id=1 OR sex='man')
var list = DB.Context.From
()
.Where(d => d.name == "ITdos" && (d.id == 1 || d.sex == 'man'))
.ToList();
*
Like模糊查询:
//SQL语句:SELECT * FROM table WHERE name LIKE '%ITdos%'
// AND name LIKE 'dos%' AND name LIKE '%IT'
var list = DB.Context.From
()
.Where(d => d.name.Like("ITdos")
&& d.name.StartsWith("dos")
&& d.name.EndsWith("IT"))
.ToList();
*
In、Not In查询:
//SQL:SELECT * FROm table WHERE id IN(1,2,3) AND name NOT IN('dos','IT')
var list = DB.Context.From
()
.Where(d => d.id.In(1,2,3) && d.name.NotIn("dos","IT"))
.ToList();
//也可以指定一个数据集做为条件
var listParam = new List();
listParam.Add(1);
listParam.Add(2);
listParam.Add(3);
var list = DB.Context.From
()
.Where(d => d.id.In(listParam))
.ToList();
*
Where条件拼接使用Where类:
//sql语句:SELECT * FROM table WHERE name='ITdos' AND id=1
var where = new Where
();
where.And(d => d.name == "ITdos");
where.And(d => d.id == 1);
var list = DB.Context.From
()
.Where(where)
.ToList();
*
多表条件拼接:
//SQL语句:SELECT * FROM table a
// INNER JOIN table2 b ON a.id=b.aid
// INNER JOIN table3 c ON a.id=c.aid
// WHERE a.id=1 AND b.id=2 AND c.id=3
var where = new Where
();
where.And(a => a.id == 1);
where.And((a,b) => b.id == 2);
where.And((a,c) => c.id == 3);
var list = DB.Context.From
()
.InnerJoin((a,b) => a.id == b.aid)
.InnerJoin((a,c) => a.id == c.aid)
.Where(where)
.ToList();
//上面的where还可以这样写:
var where = new Where
();
where.And((a,b,c) => a.id == 1 && b.id == 2 && c.id == 3);
*/ /*=======================================多表联合查询==========================================*/
/*=======================================多表联合查询==========================================*/
/*
//SQL语句:SELECT a.*,b.*,c.name FROM table a
// INNER JOIN table2 b ON a.id=b.aid
// LEFT JOIN table3 c ON a.id=c.aid
var list = DB.Context.From
()
.Select(table._.All,
table2._.All,
table3._.name)
.InnerJoin((a,b) => a.id == b.aid)
.LeftJoin((a,c) => a.id == c.aid)
.ToList();
//由于返回的数据是List
数据,如果table实体类不包含table3的name字段
//那么将无法映射这些数据,所以需要为table扩展属性
//新建带有partial修饰的table扩展类,与table实体类在同一命名空间下:
using Model;
public partial class table
{
public string name { get; set;}
//可以继续扩展table2中的字段
}
//这样.ToList()返回的List
数据就包含table3.name的数据了。
//如果您觉得关联表太多,字段太多,扩展麻烦,可以直接返回.ToDataTable()。
*
方法对照表:
方法 SQL
.InnerJoin() inner join
.LeftJoin() left join
.RightJoin() right join
.CrossJoin() cross join
.FullJoin(0full join
*/ /*=======================================分组和排序==========================================*/
/*=======================================分组和排序==========================================*/
/*
* 自己指定排序
DbSession.Default.From()
.Page(10, 2)
.Where(Products._.CategoryID.SelectIn(1, 2, 3))
.OrderBy(Products._.CategoryID.Asc)
.ToList();
* 多个字段排序则如下操作
DbSession.Default.From()
.OrderBy(Products._.CategoryID.Asc && Products._.ProductID.Asc)
.ToList();
* 分组
通过方法GroupBy来设置按照哪些字段分组。
例如:
DbSession.Default.From()
.GroupBy(Products._.ProductName.GroupBy)
.Select(Products._.ProductName)
.ToDataTable();
生成的sql:
Text:
SELECT [Products].[ProductName] FROM [Products] GROUP BY [Products].[ProductName]
* 按照多个字段分组和多个排序类似。
DbSession.Default.From()
.GroupBy(Products._.ProductName.GroupBy && Products._.ProductID.GroupBy)
.Select(Products._.ProductName,Products._.ProductID)
.ToDataTable();
sql:
Text:
SELECT [Products].[ProductName],[Products].[ProductID] FROM [Products] GROUP BY [Products].[ProductName],[Products].[ProductID]
*/ /*=======================================数据分页==========================================*/
/*=======================================数据分页==========================================*/
/*
top方法:
DbSession.Default.From()
.Top(10)
.ToList();
该查询时查询products表中的前10条记录,生成的sql语句如下:(如何输出查看组件生成的sql)
Text:
SELECT TOP 10 * FROM [Products]
from方法:
DbSession.Default.From()
.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()
.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()
.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()
.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()
.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()。
*/ /*=======================================子查询==========================================*/
/*=======================================子查询==========================================*/
/*
查询条件的值来自另外一次查询。
例如sqlserver sql:
select * from products where categoryid=(select top 1 categoryid from categories where categoryname=’produce’)
这条sql的实现代码如下:
DbSession.Default.From()
.Where(Products._.CategoryID
.SubQueryEqual(DbSession.Default.From().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()
.Where(Products._.CategoryID
.SubQueryNotIn(DbSession.Default.From().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
子查询是不是也变的很简单的呢了。
*/ /*=======================================事务==========================================*/
/*=======================================事务==========================================*/
/*
组件提供了简单的事务,并没有过多的封装。
先上个例子:
using (DbTrans trans = DbSession.Default.BeginTransaction())
{
DbSession.Default.Update(Products._.ProductName, "apple", Products._.ProductID == 1, trans);
DbSession.Default.Update(Products._.ProductName, "egg", Products._.ProductID == 2, trans);
trans.Commit();
}
trans.Commit(); 必须提交,不然就执行不成功了。如果使用try catch的写法如下:
DbTrans trans = DbSession.Default.BeginTransaction();
try
{
DbSession.Default.Update(Products._.ProductName, "apple", Products._.ProductID == 1, trans);
DbSession.Default.Update(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);
*/ /*=======================================批处理==========================================*/
/*=======================================批处理==========================================*/
/*
批处理就是提交的脚本不是马上执行,而是到一定数量才提交。
还是先上例子
using (DbBatch batch = DbSession.Default.BeginBatchConnection())
{
batch.Update(Products._.ProductName, "apple", Products._.ProductID == 1);
batch.Update(Products._.ProductName, "pear", Products._.ProductID == 2);
batch.Update(Products._.ProductName, "orange", Products._.ProductID == 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._.ProductName, "apple", Products._.ProductID == 1);
batch.Update(Products._.ProductName, "pear", Products._.ProductID == 2);
batch.Execute();
batch.Update(Products._.ProductName, "orange", Products._.ProductID == 3);
}
执行batch.Execute(),就会将之前的sql脚本先提交。
try catch的写法如下:
DbBatch batch = DbSession.Default.BeginBatchConnection();
try
{
batch.Update(Products._.ProductName, "apple1", Products._.ProductID == 1);
batch.Update(Products._.ProductName, "pear1", Products._.ProductID == 2);
batch.Update(Products._.ProductName, "orange1", Products._.ProductID == 3);
}
catch
{
//do something
}
finally
{
batch.Close();
}
效果和第一个例子是一样的。批处理也是比较简单的。
*/ /*=======================================自定义缓存==========================================*/
/*=======================================自定义缓存==========================================*/
/*
配置的缓存并不能满足我们的需求,例如有时候需要刷新缓存,或者某次查询缓存时间或者缓存依赖不一样等。
刷新缓存
例如:
DbSession.Default.From().Where(Products._.ProductID == 1).Refresh().ToFirst();
Refresh()方法设置了刷新缓存,即不从缓存中读取,直接从数据库读取最新数据,并重新缓存。
设置查询的缓存有效期。
例如:
DbSession.Default.From().Where(Products._.ProductID == 1).SetCacheTimeOut(180).ToFirst();
设置该查询的缓存时间为180秒。
该设置会覆盖默认缓存配置,并且缓存配置中没有实体设置,也会缓存。
当下次执行:
DbSession.Default.From().Where(Products._.ProductID == 1).ToFirst();
在180秒内缓存有效。* 设置缓存依赖。
例如:
System.Web.Caching.CacheDependency cacheDep = new System.Web.Caching.CacheDependency(Server.MapPath("~/2.txt"));
DbSession.Default.From().Where(Products._.ProductID == 1).SetCacheDependency(cacheDep).ToFirst();
设置该查询缓存的缓存依赖为2.txt文件。
该设置会覆盖默认缓存配置,并且缓存配置中没有实体设置,也会缓存。
当再次执行:
DbSession.Default.From().Where(Products._.ProductID == 1).ToFirst();
如果2.txt文件没有改变,缓存是不会失效的。
当然这几个方法也可以同时使用。
缓存是根据生成的sql作为缓存依据的,所以写法确保一致,特别是条件的先后顺序,才能有效利用缓存,否则就要浪费内存了。
*/
}
}
}
第七城市

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台