一、使用sql及存儲(chǔ)過程
創(chuàng)新互聯(lián)建站成立于2013年，我們提供高端重慶網(wǎng)站建設(shè)公司、成都網(wǎng)站制作公司、成都網(wǎng)站設(shè)計(jì)、網(wǎng)站定制、全網(wǎng)營(yíng)銷推廣、微信平臺(tái)小程序開發(fā)、微信公眾號(hào)開發(fā)、seo優(yōu)化服務(wù)，提供專業(yè)營(yíng)銷思路、內(nèi)容策劃、視覺設(shè)計(jì)、程序開發(fā)來(lái)完成項(xiàng)目落地，為汽車玻璃修復(fù)企業(yè)提供源源不斷的流量和訂單咨詢。
1)使用List>
1.使用sql ,體驗(yàn)原生態(tài)的感覺
- string sql="select * from Class where ClassName = @";
- string sql2="select * from Class where DateTimem = @";
- List> mylist=db.ExecuteSqlToDictionaryList(sql,"boy'");
- List> mylist2=db.ExecuteSqlToDictionaryList(sql2,DateTime.Parse("2013-10-10 14:40:08"));
- foreach(var oneClass in mylist){
- string className=oneClass["className"].To();
- long id=oneClass["Classid"].To();
- DateTime datetimem=oneClass["datetimem"].To();//不用區(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ǔ)過程
- DataSet dataset=db.ExecuteProToDataSet("存儲(chǔ)過程名",參數(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)
-
- //如果不是全路徑,則默認(rèn)在dll生成目錄
-
2.配置xml(sql.xml)
-
-
-
- select name from user where id>@
- 查詢用戶名(描述信息)
-
-
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
分享文章：欣賞另一種Orm的設(shè)計(jì)風(fēng)格：大道至簡(jiǎn)
網(wǎng)頁(yè)地址：
http://uogjgqi.cn/article/coghhpp.html
