一、查詢 1 )簡單查詢 ? 一種方式(分解構造式) ? SubSonic. Query query =? TPjWorkday .CreateQuery(); ???????? query.SelectList =? TPjWorkday . Columns .Year+? ", " ?+? TPjWorkday . Columns .Yearmonth +? "," ?+? TPjWorkday . Columns .Workdays; ???????? query.AddWhere( TPjWorkday . Columns .Year,yearNum); ???????? query.OrderBy = SubSonic. OrderBy .Asc( TPjWorkday . Columns .Yearmonth); ???????? DataTable dt = query.ExecuteDataSet().Tables[0]; ???????? return dt; 另外一種方式( LINQ 式) SqlQuery? query=? new Select ( TPjWorkday . Columns .Year,? TPjWorkday . Columns .Yearmonth,? TPjWorkday . Columns .Workdays).From( TPjWorkday .Schema ? ).Where( TPjWorkday . Columns .Year).IsEqualTo(yearNum).OrderAsc( TPjWorkday . Columns .Yearmonth); ?? DataTable dt = query.ExecuteDataSet().Tables[0]; ? 2 ) LIKE 查詢 queryBuilder.AddWhere( TBaseAddressbook . Columns .Name,? Comparison .Like, "%" + this .tbName.Text+ "%" ); ? 3 )查詢單值 SqlQuery ?query =? new Select ( TBaseDepartment . Columns .Deptname).From( TBaseDepartment .Schema ??????????????? ? ).Where( TBaseDepartment . Columns .Deptid).IsEqualTo(deptid); return ?query.ExecuteScalar().ToString(); ? 4 )多表連接查詢(連接查詢的表的數量超過 3 個最好使用視圖) 根據單位 ID 獲取用戶 SqlQuery ?query= new Select ( TBaseUser .UseridColumn, TBaseUser .UsernameColumn,? TBaseUser .PersonnameColumn,? TBaseUser .PhonecodeColumn,? TBaseUser .EmailColumn) ???????? .From( TBaseUser .Schema).InnerJoin( TLstUserindepartment .UseridColumn,? TBaseUser .UseridColumn) ???????? .Where( TBaseUser .EnabledColumn).IsEqualTo(1) ???????? .And( TLstUserindepartment .DeptidColumn).IsEqualTo(deptid) ???????? .OrderAsc( TBaseUser . Columns .Userid); ????????? DataTable dt = query.ExecuteDataSet().Tables[0]; 注意 InnerJoin ( f2,f1 )的用法。也就是說當兩個表連接時, From 語句里面的表要放在后面。 ? 5 )分頁查詢 (方式一, Query ) //Query 方式分頁 , 序號列使用 rowindex, ???? oracle 數據庫 Query ?query1 =? new Query ( TBaseUser .Schema); ???????? query1.SelectList =? TBaseUser . Columns .Userid+? "," ?+? TBaseUser . Columns .Personname +? "," +? TBaseUser . Columns .Phonecode+ "," +? TBaseUser . Columns .Email +? "," +? TBaseUser . Columns .Userindex; ???????? query1.AddWhere( TBaseUser . Columns .Issignedin, Comparison .Equals, 1); // 登錄狀態 ???????? query1.AND( TBaseUser . Columns .Enabled, Comparison .Equals, 1); // 啟用 ???????? query1.OrderBy =? OrderBy .Asc( TBaseUser . Columns .Userindex); ???????? int ?sum= query1.GetRecordCount(); // 用于計算總頁數 query1.PageSize = 12; // 分頁大小 ???????? query1.PageIndex = pagenum -1; // 當前頁 ???????? DataTable ViewData = query1.ExecuteDataSet().Tables[0]; ???????? if (sum%12==0) ???????????? this .lbPageCount.Text= (sum / 12).ToString(); ???????? else ???????????? this .lbPageCount.Text= (sum / 12+1).ToString(); ???????? this .lbPageNum.Text= pagenum.ToString(); ? (方式二、 SqlQuery ) //SqlQuery 方式分頁,序號列使用 row_number ??? oracle 數據庫 SqlQuery ?query =? new Select ().From( VWfSupervisor .Schema).Where( VWfSupervisor . Columns .Userid).IsEqualTo(CreateUserInfo().userid).OrderAsc( VWfSupervisor . Columns .Userid); int ?sum = query.GetRecordCount(); // 未分頁前,總記錄數 query.Paged(pagenum - 1, 12); DataTable ?ViewData = query.ExecuteDataSet().Tables[0]; if (sum%12==0) ?????? this .lbPageCount.Text= (sum / 12).ToString(); ? else ??????? this .lbPageCount.Text= (sum / 12+1).ToString(); ? this .lbPageNum.Text= pagenum.ToString(); ? ? 二、插入,更新和刪除 插入和更新操作都是通過實體的 Save() 方法來實現的,判斷更新與插入操作主要體現在實體的 IsNew 屬性,如果 IsNew==true ,則執行的是插入操作,反之執行的是更新操作。 當實例化一個實體時,如 TPjWorkday ?newworkday =? new TPjWorkday () ,此時的 newworkday.IsNew=true; 當實例化傳入參數時, TPjAttendancetime ?attendanceTime =? new TPjAttendancetime ( Convert .ToInt32(dt.Rows[0][ "flowid" ])); 此時的 newworkday.IsNew=false 。 ? 1. 插入 TPjWorkday ?newworkday =? new TPjWorkday (); newworkday.Year =? Convert .ToInt32(lbYear.Text); newworkday.Yearmonth=? Convert .ToInt32( this .lbYear.Text)* 100 + (i + 1); newworkday.Workdays = workStr; newworkday.Save(); ? new Insert ( TLstUserinrole .Schema, false ).Value( TLstUserinrole .UseridColumn,2).Value( TLstUserinrole .RoleidColumn, 2) ???????????? .Value( TLstUserinrole .CreatebyColumn,3).Value( TLstUserinrole .CreatetimeColumn,? DateTime .Now).Execute(); 其中 false 表示不是全部字段。默認為 true ? 2. 更新 單字段逐漸更新方式,傳入主鍵字段值 TPjAttendancetime ?attendanceTime =? new TPjAttendancetime ( Convert .ToInt32(dt.Rows[0][ "flowid" ])); attendanceTime.Begintime =? Convert .ToDateTime(tbBeginTime.Value); attendanceTime.Endtime =? Convert .ToDateTime(tbEndTime.Value); attendanceTime.AM1 =? Convert .ToDateTime(dlAm1.SelectedValue).ToShortTimeString(); attendanceTime.AM2 =? Convert .ToDateTime(dlAm2.SelectedValue).ToShortTimeString(); attendanceTime.PM1 =? Convert .ToDateTime(dlPm1.SelectedValue).ToShortTimeString(); attendanceTime.PM2 =? Convert .ToDateTime(dlPm2.SelectedValue).ToShortTimeString(); attendanceTime.Modifyby =? Convert .ToInt32(Request.QueryString[ "userid" ]); attendanceTime.Modifytime = System. DateTime .Now; attendanceTime.Save(); ? new Update ( TPjInnernews .Schema).Set( TPjInnernews .NewstitleColumn).EqualTo( "123" ).Where( TPjInnernews .NewsidColumn).IsEqualTo(3); ? 復合主鍵更新方式,( userid , ascxid ) new Update ( TLstUserhomepage .Schema).Set( TLstUserhomepage . Columns .Modifytime).EqualTo(System. DateTime .Now).Set( TLstUserhomepage . Columns .Modifyby).EqualTo(3).Where( TLstUserhomepage .UseridColumn) ???????????? .IsEqualTo(409).And( TLstUserhomepage .AscxidColumn).IsEqualTo(11).Execute(); ? // 修改記錄 ,? 先取得對象,然后再更新 ???????????? TBaseStatistic type= new Select ().From( TBaseStatistic .Schema).Where( TBaseStatistic . Columns .Typeid).IsEqualTo( int .Parse(GetEQString( "Typeid" ))) ???????????????? .And( TBaseStatistic . Columns .Statid).IsEqualTo( int .Parse( this .Label3.Text)).ExecuteScalar(); ???????????? type.Statname =? this .TextBox1.Text.Trim(); ???????????? type.Stattabname =? this .Label1.Text; ???????????? type.Statcolname =? this .Label12.Text; ???????????? type.Statcolid =? this .Label7.Text; ???????????? type.Datecolname=? this .Label14.Text; ???????????? type.Wherepart=? this .Label8.Text; type.Save(); ? UPDATE table set column=column+ 1 int ?records =? new Update ( Product .Schema) ???????????????? .SetExpression( "UnitPrice" ).EqualTo( "UnitPrice * 3" ) ???????????????? .Where( "productid" ).IsEqualTo(1) ???????????????? .Execute(); ? 3. 刪除 Query ?q =? TPjWorkday .CreateQuery(); q.WHERE( TPjWorkday . Columns .Year,? Convert .ToInt32(lbYear.Text)); q.QueryType =? QueryType .Delete; q.Execute(); ? TPjWorkday .Delete( TPjWorkday . Columns .Yearmonth,? "200901" ); ? ? ? 三、事務處理 using ?( SharedDbConnectionScope sp =? new SharedDbConnectionScope ()) { ?? using ?( TransactionScope scope =? new TransactionScope ()) ?? { ????????? // 數據庫操作 ????????? // ? 不能使用平臺的自增操作,否則會報錯: {" 無法加載 ?DLL “ oramts.dll ” :? 找不到指定的模塊。 ?( 異常來自 HRESULT:0x8007007E) 。 "} //CACA.BusinessLogic.Util.CounterUtil.increment("EntityInnernews") ????????? scope.Complete(); } } ? List < SqlQuery >list =? new List < SqlQuery >(); list.Add( new Delete ().From( TBaseAddressbook .Schema).Where( TBaseAddressbook . Columns .Serialno).IsEqualTo(lbl.Text)); list.Add( new Delete ().From( TBaseAddressbookusual .Schema).Where( TBaseAddressbookusual . Columns .Serialno).IsEqualTo(lbl.Text)); SqlQuery .ExecuteTransaction(list); ? List < Insert >list=new? List < Insert >(); ? 存儲過程: //StoredProcedure spd = newStoredProcedure("GetDataTable"); ???????? //spd.Command.AddParameter("@userid",userid); ???????? //spd.Command.AddOutputParameter("@dataresult"); ??????? //spd.Execute(); ? ? ? 四、 SubSonic 直接執行 SQL 語句 ( 后門,當遇到無法解決的問題時,直接寫 SQL 語句 ) SubSonic 直接執行 SQL 語句可以使用以下方式: ? // 執行 sql 語句,返回 datatable QueryCommand qc = newQueryCommand(strSql.ToString(), null); ? ???????????????? dt =DataService.GetDataSet(qc).Tables[0]; ? ? public void Inline_Simple() { ????? QueryCommandcmd = new InlineQuery().GetCommand("SELECT productID from products"); ????? Assert.IsTrue(cmd.CommandSql==? ??????????????? "SELECT productID fromproducts"); } ? public void Inline_WithCommands() { ????? QueryCommandcmd = new InlineQuery() ?????????????? .GetCommand(@"SELECTproductID from products? ?????????????????? WHEREproductid=@productid", 1); ? ????? Assert.IsTrue(cmd.Parameters[0].ParameterName== "@productid"); ????? Assert.IsTrue((int)cmd.Parameters[0].ParameterValue== 1); } ? public void Inline_AsCollection() { ????? ProductCollectionproducts = ????????? newInlineQuery() ?????????????? .ExecuteAsCollection<ProductCollection>( ????????????????????????? @"SELECTproductID from products? ?????????????????????????? WHEREproductid=@productid", 1); } ? ? 五、常用方法的返回值 Execute(), 返回受影響的記錄條數 //ExecuteScalar() ,返回一個 object ,( System.Decimal 類型的值) ExecuteSingle< T >() ,返回 T 類型的對象,例如可以用這個方法獲得滿足要求的實體對象。 TDicItem ?item=? new Select ().From( TDicItem .Schema).Where( TDicItem . Columns .Dicitemid).IsEqualTo(2) ???????????? .And( TDicItem . Columns .Dictypeid).IsEqualTo(1030).ExecuteSingle< TDicItem >(); ? ExecuteDataSet(), 返回一個記錄集 DataSet ExecuteTypedList<T>(); 返回 List<T>, 對象的列表,例如 List< Product >products = new? Select ().From< Product >() .Where( Product .ProductIDColumn).IsEqualTo(4) .Paged(1, 30) .ExecuteTypedList< Product >(); 轉自他人文章! |
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
