一、使用sql及存储过程1)使用List<Dictionary<string, MObject>> 1.使用sql ,体验原生态的感觉 string sql="select * from Class where ClassName = @";
string sql2="select * from Class where DateTimem = @"; List<Dictionary<string, MObject>> mylist=db.ExecuteSqlToDictionaryList(sql,"boy'"); List<Dictionary<string, MObject>> mylist2=db.ExecuteSqlToDictionaryList(sql2,DateTime.Parse("2013-10-10 14:40:08")); foreach(var oneClass in mylist){
string className=oneClass["className"].To<string>(); long id=oneClass["Classid"].To<long>(); DateTime datetimem=oneClass["datetimem"].To<DateTime>();//不用区分大小写 Console.WriteLine(className+" "+id+" "+datetimem); } 2.使用mql,智能感知带来的优雅体验 var list=db.GetDictionaryList(ClassSet.SelectAll().Where(ClassSet.ClassID.BiggerThan(0)))
2)MQL 全面接触
2.1 MQL的标准查询
var mm=ClassSet.Select(ClassSet.ClassID,ClassSet.ClassName).
Where(ClassSet.ClassName.Contains("s").And(ClassSet.ClassID.BiggerThan(9)));
SELECT [Class].[ClassID],[Class].[ClassName] FROM [Class] WHERE [Class].[ClassName] LIKE @p1 AND [Class].[ClassID]>@p2
@p1=%s% @p2=9 2.2 MQL的嵌套查询(含有Top查询:支持mysql、oracle、postgreSQL、sqlserver、sqlite)var qiantao=ScoreSet.SelectAll().Where(
SELECT TOP 1 [Score].* FROM [Score] WHERE [Score].[UserID] IN (SELECT [User].[UserID] FROM [User] WHERE [User].[ClassID] IN (SELECT [Class].[ClassID] FROM [Class] WHERE [Class].[ClassName]=@p1 AND [Class].[ClassID]>@p2 ) ) ScoreSet.UserID.In(UserSet.Select(UserSet.UserID).Where( UserSet.ClassID.In( ClassSet.Select(ClassSet.ClassID).Where( ClassSet.ClassName.Equal(c.ClassName).And(ClassSet.ClassID.BiggerThan(0)) ) ) ) ) ).Top(1); @p1=综合测试ClassName2 @p2=0 2.3 MQL的分组查询var mql=ScoreSet.Select(ScoreSet.ScoreM.Sum().AS("sum"),ScoreSet.TypeName).
Where(ScoreSet.ScoreM.BiggerThanOrEqual(100)). GroupBy(ScoreSet.TypeName). Having(ScoreSet.ScoreM.Sum().BiggerThan(300));
SELECT SUM([Score].[ScoreM]) AS 'sum',[Score].[TypeName] FROM [Score] WHERE [Score].[ScoreM]>=@p1 GROUP BY [Score].[TypeName] HAVING SUM([Score].[ScoreM])>@p2
@p1=100 @p2=300 2.4 MQL的连接查询var m1=ClassSet.Select(ClassSet.ClassID,ClassSet.ClassName)
.LeftJoin( UserSet.Select(UserSet.UserID)) .ON(ClassSet.ClassID.Equal(UserSet.UserID)) .Where(UserSet.UserID.BiggerThan(9)); SELECT [Class].[ClassID],[Class].[ClassName],[User].[UserID] FROM [Class] LEFT JOIN [User] ON [Class].[ClassID]=[User].[UserID] WHERE [User].[UserID]>@p1
@p1=9 2.5 MQL的Union查询var mql=ClassSet.SelectAll().Where(ClassSet.ClassID.BiggerThan(1))
.Union(ClassSet.SelectAll().Where(ClassSet.ClassID.BiggerThan(2))); var mql=ClassSet.SelectAll().Where(ClassSet.ClassID.BiggerThan(1))
.UnionAll(ClassSet.SelectAll().Where(ClassSet.ClassID.BiggerThan(2)));
SELECT [Class].* FROM [Class] WHERE [Class].[ClassID]>@p1 UNION SELECT [Class].* FROM [Class] WHERE [Class].[ClassID]>@p2
@p1=1 @p2=2 SELECT [Class].* FROM [Class] WHERE [Class].[ClassID]>@p1 UNION ALL SELECT [Class].* FROM [Class] WHERE [Class].[ClassID]>@p2
@p1=1 @p2=2
2.6 MQL的使用预览public static void Main(string[] args)
{ using (var db=Db.CreateDefaultDb()) { db.TransactionEnabled=true; db.DebugEnabled=true; Console.WriteLine("---------------嵌套查询---------------------"); var qiantao=ScoreSet.SelectAll().Where( ScoreSet.UserID.In(UserSet.Select(UserSet.UserID).Where( UserSet.ClassID.In( ClassSet.Select(ClassSet.ClassID).Where( ClassSet.ClassName.Equal(c.ClassName).And(ClassSet.ClassID.BiggerThan(0)) ) ) ) ) ).Top(1); Console.WriteLine("---------------分组查询---------------------"); var mql=ScoreSet.Select(ScoreSet.ScoreM.Sum().AS("sum"),ScoreSet.TypeName).Where(ScoreSet.ScoreM.BiggerThanOrEqual(100)).GroupBy(ScoreSet.TypeName).Having(ScoreSet.ScoreM.Sum().BiggerThan(300)); Console.WriteLine("---------------连接查询---------------------"); var m1=ClassSet.Select(ClassSet.ClassID,ClassSet.ClassName) .LeftJoin( UserSet.Select(UserSet.UserID)) .ON(ClassSet.ClassID.Equal(UserSet.UserID)) .Where(UserSet.UserID.BiggerThan(9)); } }
3)使用DataSet
1.使用存储过程DataSet dataset=db.ExecuteProToDataSet("存储过程名",参数一,参数二);
2.使用sqlDataSet dataset=db.ExecuteSqlToDataSet(sql,"boy");
3.使用mqlDataSet dataset=db.GetDataSet(ClassSet.SelectAll().Where(ClassSet.ClassID.BiggerThan(0)));
4)使用xml配置sql查询1.配置config节点
<appSettings>
<add key="SQL_XML_FILE_NAME" value="C:\Moon\Moon.Orm\sql.xml"></add>//如果不是全路径,则默认在dll生成目录 </appSettings>
2.配置xml(sql.xml)
<?xml version="1.0"?>
3.使用id进行查询
<sqls> <sqlxml id="getname"> <sql>select name from user where id>@</Sql> <description>查询用户名(描述信息)</Description> </sqlxml> </sqls> var list=db.GetDictionaryList(XmlHelper.GetSqlXmlByID("getname"),"boy");
5)sql之王者归来
使用GetDynamicList ,让你体验另一种自由
object,但在.net 4.0下面,您可以用dynamic直接取值.
string sql22="select * from Score";
dynamic list22=db.GetDynamicList(sql22,"Score"); foreach(var a in list22){ Console.WriteLine(a.ID+"--"+a.ScoreM+"--"+a.UserID+"--"+a.TypeName);//都是强类型 } 以下是体验强类型:)
请加入第二个群 225656797
|
|
来自: 昵称10504424 > 《工作》