一、使用sql及存儲(chǔ)過(guò)程

1)使用List<Dictionary<string, MObject>>

1.使用sql ,體驗(yàn)原生態(tài)的感覺(jué)

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>();//不用區(qū)分大小寫  
                    Console.WriteLine(className+"  "+id+"  "+datetimem);  
} 

2.使用mql,智能感知帶來(lái)的優(yōu)雅體驗(yàn)

var  list=db.GetDictionaryList(ClassSet.SelectAll().Where(ClassSet.ClassID.BiggerThan(0))) 

2)MQL 全面接觸

2.1 MQL的標(biāo)準(zhǔn)查詢

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(  
                    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);  
  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 ) )  
@p1=綜合測(cè)試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的使用預(yù)覽

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));  
 
            }  
            Console.WriteLine("---------------Union測(cè)試---------------------");  
            using (var db=Db.CreateDefaultDb()) {  
                db.TransactionEnabled=true;  
                db.DebugEnabled=true;  
                  
                var mql=ClassSet.SelectAll().Where(ClassSet.ClassID.BiggerThan(1))  
                    .Union(ClassSet.SelectAll().Where(ClassSet.ClassID.BiggerThan(2)));   
            }  
        } 

3)使用DataSet

1.使用存儲(chǔ)過(guò)程

DataSet dataset=db.ExecuteProToDataSet("存儲(chǔ)過(guò)程名",參數(shù)一,參數(shù)二); 

2.使用sql

DataSet dataset=db.ExecuteSqlToDataSet(sql,"boy"); 

3.使用mql

DataSet dataset=db.GetDataSet(ClassSet.SelectAll().Where(ClassSet.ClassID.BiggerThan(0))); 

4)使用xml配置sql查詢

1.配置config節(jié)點(diǎn)

<appSettings> 
<add key="SQL_XML_FILE_NAME" value="C:\Moon\Moon.Orm\sql.xml"></add>//如果不是全路徑,則默認(rèn)在dll生成目錄  
</appSettings> 

2.配置xml(sql.xml)

<?xml version="1.0"?> 
<sqls> 
<sqlxml id="getname"> 
<sql>select name from user where id>@</Sql> 
<description>查詢用戶名(描述信息)</Description> 
</sqlxml> 
</sqls> 

3.使用id進(jìn)行查詢

var list=db.GetDictionaryList(XmlHelper.GetSqlXmlByID("getname"),"boy"); 

5)sql之王者歸來(lái)

使用GetDynamicList ,讓你體驗(yàn)另一種自由

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);//都是強(qiáng)類型  
} 

以下是體驗(yàn)強(qiáng)類型:)

原文鏈接：http://www.cnblogs.com/humble/p/3380065.html