tag:blogger.com,1999:blog-15293989030680910232024-03-14T15:04:33.350+08:00皮尼網前走吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.comBlogger27125tag:blogger.com,1999:blog-1529398903068091023.post-83932436144711315632017-03-06T19:52:00.002+08:002017-03-06T19:52:51.954+08:00[Line Backup] Line 對話紀錄免Root無痛轉移 v7.1.1參考了 電腦王阿達分享的<a href="https://www.kocpc.com.tw/archives/2441" target="_blank"> [Android]LINE推出最新功能,讓您備份/還原聊天紀錄對話訊息</a><br />
<br />
此版本在我手機(Device:HTC M9+ Line v7.1.1)並不work<br />
<br />
於是參考了ASUS論壇,https://www.asus.com/zentalk/tw/thread-164392-1-1.html,走到降版的步驟就出現錯誤訊息[INSTALL_FAILED_PERMISSION_MODEL_DOWNGRADE]<br />
<br />
終於翻到論壇的第十頁,貌似找到解答了,我把成功的command貼出:<br />
<br />
First,一定要先依照阿達的分享將手機與電腦連結。<br />
Second,就照ASUS論壇把ADB搞定吧~<br />
<br />
【原手機步驟】<br />
1. adb devices<br />
--> 出現以連線的手機序號<br />
<br />
2. adb shell content query --uri content://settings/secure --where "name=\'android_id\'"<br />
-->Row: 0 _id=25, name=android_id, value=xxxxxxxxxxxxxxxx(請好好保存android_id)<br />
<br />
3. adb shell pm uninstall -k jp.naver.line.android<br />
-->Success<br />
(移除原手機的line ?! 此時我的手機 ES應用分析器跑出來問要不要刪除line的相關檔案,別理他)<br />
<br />
4. adb push LINEold.apk /sdcard/<br />
--> 複製舊版apk到手機<br />
<br />
5. adb shell pm install /sdcard/LINEold.apk<br />
-->Success (Line降版,裝好後不要開)<br />
<br />
6. adb backup -apk jp.naver.line.android<br />
-->Now unlock your device and confirm the backup operation.<br />
(手機畫面點"備份我的資料",等一下直接檢查電腦檔案夾的backup.ab檔案大小,1K就是失敗了,至少要來個10MB)<br />
<br />
【新手機步驟(先移除任何版本LINE)】<br />
1. adb install LINEold.apk<br />
2. adb shell settings put secure android_id xxxxxxxxxxxxxxxx(第2步驟取的android_id共16碼)<br />
3. adb restore backup.ab<br />
(手機畫面點"還原我的資料")<br />
<br />
馬上開新手機的Line絕對是不work的,此時不用擔心,到Play Store更新Line版本。<br />
<br />
<br />吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com0tag:blogger.com,1999:blog-1529398903068091023.post-50209914168790481492011-08-24T22:29:00.000+08:002011-08-24T22:34:09.319+08:00[ASP.NET] Import Excel with linqtoexcel<a href="http://wupinny.blogspot.com/2011/07/aspnet-export-excel-with-npoi-and.html">上篇文章</a>比較了以NPOI and EPPlus匯出Excel的效能,接著就是要介紹Import Excel了,雖然NPOI也可以Import Excel2003/2007,但我認識了<a href="http://code.google.com/p/linqtoexcel/">linqtoexcel</a>後,就毅然決然的選用LinqToExcel為我的Import利器了。<br />
LinqToExcel為32位元的dll,所以如果在64位元遇到complier的問題時,請參閱轉載自網路的<a href="http://cloudolphinstudy.blogspot.com/2011/02/could-not-load-file-or-assembly.html">排除方法</a>。 還有<a href="http://code.google.com/p/linqtoexcel/wiki/UsingLinqToExcel">注意事項</a>,轉載自官網: <br />
<blockquote>
<b>Target x86 Platform</b> Linq to Excel requires any projects referencing it to be built against the x86 platform target. See <a href="http://blog.yodersolutions.com/2008/11/microsoftjetoledb-and-x64-machines/">this link</a> for detailed information on setting the platform target to x86. Note this only applies to compiling the project on x64 computers. <br />
<b>.Net 4</b> When using Linq to Excel in a .Net 4 app, make sure to change the target framework from the default client profile to the full .Net 4 framework. (Properties -> Application -> Target framework) The client profile cannot compile .Net 3.5 dlls. </blockquote>
<b>檢而言之,只能跑在x64,跟.Net framework 3.5。</b> 接著用官網的sample code介紹這好用的工具。 <br />
<pre class="brush:csharp">var excel = new ExcelQueryFactory("excelFileName");
var oldCompanies = from c in repo.Worksheet<company>("US Companies") //worksheet name = 'US Companies'
where c.LaunchDate < new DateTime(1900, 0, 0)
select c;
</company></pre>
首先讀取名字為"US Companies"的工作表,如果無法確定上傳的檔案的名稱,最好傳入index即可。<br />
接著<span class="pun"><</span><span class="typ">Company</span><span class="pun">></span><span class="str"></span>則是有一個名叫Company的Class與Excel 的欄位都一模一樣, 這樣檔案就會乖乖的資料吃到Company的集合物件, 並可以用linq的手法去做資料篩選,一整個就是美妙。<br />
<br />
不過如果Excel欄位與Class無法設定成一樣的,那還是有法可解: <br />
<pre class="brush:csharp">var excel = new ExcelQueryFactory("excelFileName");
excel.AddMapping<company>(x => x.State, "Providence"); //maps the "State" property to the "Providence" column
var indianaCompanies = from c in excel.Worksheet<company>()
where c.State == "IN" && c.Employees > 500
select c;
</company></company></pre>
使用AddMapping去指定欄位間的關係,Excel欄位 "Providence"去指定對應到Company的State的欄位。吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com0tag:blogger.com,1999:blog-1529398903068091023.post-11708743244901745502011-07-26T08:20:00.004+08:002011-07-26T08:24:25.780+08:00[ASP.NET] Export Excel with NPOI and EPPlus<span style="font-size: large;"><b>NPOI:</b>使用於(Excel2003及之前的版本),可寫出Excel2003(xls),可讀Excel2003 (xls)/ Excel2007(xlsx)</span><br />
<br />
官網:<br />
<ul>
<li><a href="http://npoi.codeplex.com/">http://npoi.codeplex.com/</a> (~1.2.3)</li>
<li><a href="http://code.google.com/p/npoi/">http://code.google.com/p/npoi/</a> (1.2.4)</li>
</ul>
System Requirement:<br />
<ul>
<li>VS2005 or VS2008 with .NET 2.0 Runtime (SP1)</li>
<li>vs2003 with .NET 1.1</li>
<li>medium trust environment in ASP.NET</li>
</ul>
<br />
<span style="font-size: large;"><b>EPPlus:</b>EPPlus is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx).</span><br />
<br />
官網:<a href="http://epplus.codeplex.com/">http://epplus.codeplex.com/</a><br />
System Requirements:<br />
<ul>
<li>.Net Framwork 3.5 or higher</li>
</ul>
<ul></ul>
<br />
<span style="font-size: large;">NPOI vs. EPPlus:比較匯出大量資料的時間</span><br />
<br />
<a name='more'></a><br />
<br />
<b><span style="font-size: small;">NPOI: </span></b><br />
<pre class="brush:csharp">private void ExportNPOI()
{
logger.Info("begin npoi");
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = workbook.CreateSheet();
for (int i = 0; i < 65535; i++)
{
HSSFRow row = sheet.CreateRow(i);
row.CreateCell(0).SetCellValue("npoiformats");
}
workbook.Write(ms);
logger.Info("end npoi");
Response.ContentType = "application/download";
Response.AddHeader("Content-Disposition", "attachment; filename=file.xls");
Response.Clear();
Response.BinaryWrite(ms.GetBuffer());
ms.Close();
ms.Dispose();
}
</pre>
<b><span style="font-size: small;">EPPlus: </span></b>
<br />
<pre class="brush:csharp">private void ExportEPPlus()
{
logger.Info("begin epplus");
ExcelPackage pck = new ExcelPackage();
var ws = pck.Workbook.Worksheets.Add("Sample2");
for (int i = 1; i < 65999; i++)
{
ws.Cells[i, 1].Value = "openxmlformats"; //Set the value of cell A1 to 1
ws.Cells[i, 2].Value = "openxmlformats";
ws.Cells[i, 3].Value = "openxmlformats";
ws.Cells[i, 4].Value = "openxmlformats";
ws.Cells[i, 5].Value = "openxmlformats";
}
logger.Info("end epplus");
Response.Clear();
Response.AddHeader("content-disposition", "attachment; filename=file.xlsx");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.BinaryWrite(pck.GetAsByteArray());
Response.End();
}
</pre>
Excel2007可會出超過65535筆資料。<br />
使用NPOI產出65535筆資料花費41 sec,我測試的最佳紀錄為25秒。<br />
使用EPPlus產出65999筆花費2 sec。<br />
<br />
<b>Log File: </b><br />
<pre class="brush:text">2011-07-21 15:09:18.8851|INFO|ExcelCompare._Default|begin npoi
2011-07-21 15:09:59.2133|INFO|ExcelCompare._Default|end npoi
2011-07-21 15:10:02.4633|INFO|ExcelCompare._Default|begin epplus
2011-07-21 15:10:04.7758|INFO|ExcelCompare._Default|end epplus
</pre>
吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com2tag:blogger.com,1999:blog-1529398903068091023.post-1726844576635667812011-07-19T23:16:00.001+08:002011-07-26T08:24:09.182+08:00[ASP.NET] 解決Oracle IN clause 超過1000個參數不管在SQL Plus或其他Tool,甚至ASP.NET中,只要出現以下錯誤訊息:<br />
<span id="_ctl5_ctlTopic"><span id="_ctl5_ctlTopic_ctlPanelBar"><span id="_ctl5_ctlTopic_ctlPanelBar_ctlTopicsRepeater__ctl4_lblFullMessage"><b>ORA-01795 maximum number of expressions in a list is 1000</b></span></span></span><br />
<br />
<span id="_ctl5_ctlTopic"><span id="_ctl5_ctlTopic_ctlPanelBar"><span id="_ctl5_ctlTopic_ctlPanelBar_ctlTopicsRepeater__ctl4_lblFullMessage">不用懷疑</span></span></span>,就是你的SQL中,IN的參數超過1000個了。<br />
<br />
最快的解決方法有兩種:<br />
1. OR<br />
2. Union<br />
<br />
<b>OR </b>Sample如下:<br />
<pre class="brush:sql">select * from TestTable where ID in (1,2,3,4,...,1000)
union all
select * from TestTable where ID in (1001,1002,...)
</pre>
<br />
<b>Union </b>Sample如下:<br />
<pre class="brush:sql">select * from TestTable where ID in (1,2,3,4,...,1000) or ID in (1001,1002,...,2000)
</pre>
<br />
<br />
在ASP.NET中,Sample如下(使用OR):<br />
<br />
<pre class="brush:csharp">strSQL = "select * from TestTable where {0} ";
int limitCount = 900;
double dLoopCycle = (IDs.Count / limitCount);
int loopCycle = Convert.ToInt32(Math.Ceiling(dLoopCycle));
loopCycle = loopCycle == 0 ? 1 : loopCycle;
string strIDs = string.Empty;
string strTempSQL = string.Empty;
//more than 1000 parameter would raise error
for (int i = 0; i < loopCycle; i++)
{
int rangeIndex = (i * limitCount);
List<string> tempList = IDs.GetRange(rangeIndex, Math.Min(limitCount, IDs.Count - rangeIndex));
strIDs = string.Join(",", tempList.ToArray());
if (i == 0)
strTempSQL = string.Format("ID in ({0})", strIDs);
else
strTempSQL += string.Format("or ID in ({0}) ", strIDs);
}
strSQL = string.Format(strSQL, strTempSQL);
</string></pre>
<br />吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com0tag:blogger.com,1999:blog-1529398903068091023.post-41003066749927313232011-07-19T22:48:00.001+08:002011-07-26T08:23:03.927+08:00[ASP.NET] 解決Linq Contain 參數超過2100的錯誤在Linq使用Contain時,必須注意參數的個數,限制為2100為上限,錯誤訊息如下:<br />
<b>The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.</b><br />
<br />
表示你踩到IN Parameter最多2100個的限制。<br />
<br />
以下為我的解決之道:<br />
<br />
<pre class="brush:csharp">double dLoopCycle = (containList.Count / 2000);
int loopCycle = Convert.ToInt32(Math.Ceiling(dLoopCycle));
loopCycle = loopCycle == 0 ? 1 : loopCycle;
List<test> result = new List<test>();
List<test> tempResult = new List<test>();
//more than 2100 parameter would raise error
for (int i = 0; i < loopCycle; i++)
{
int rangeIndex = (i * 2000);
var tempList = containList.GetRange(rangeIndex, Math.Min(2000, containList.Count - rangeIndex));
tempResult = (from r in DataContext.Test where tempList.Contain(r.ID) select r).ToList();
result = result.Union(tempResult );
}
</test></test></test></test></pre>
<br />
另外oracle的SQL也有IN Parameter不得超過1000個的限制,這個就會在後續的文章分享我的解決方式囉~~<br />
<br />
<a href="http://wupinny.blogspot.com/2011/07/aspnet-oracle-in-clause-1000.html">[ASP.NET] 解決Oracle IN clause 超過1000個參數</a>吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com0tag:blogger.com,1999:blog-1529398903068091023.post-58602561560517284932011-07-13T22:24:00.006+08:002011-07-19T22:52:25.117+08:00[ASP.NET] Nlog:記Log必備的好東西要瞭解Nlog初步認識一定必須先拜讀保哥的的大作,<span style="font-size: small;"><a class="taggedlink" href="http://blog.miniasp.com/post/2010/07/18/Useful-Library-NLog-Advanced-NET-Logging.aspx">介紹好用函式庫:NLog - Advanced .NET Logging</a></span><br />
<br />
<span style="font-size: small;">當然師傅引進門,詳細的設定就是要靠自己啦...</span><br />
<span style="font-size: small;">就如保哥所說的,install之後就有完整的API文件跟範例,</span><br />
<span style="font-size: small;">其實不外乎只要把 Nlog.config 設定好,就完成大半的工作了!!!</span><br />
<br />
<span style="font-size: small;">我的設定主要:</span><br />
<span style="font-size: small;">1.存成File的部分,七天回滾一次,自動刪除過期的Log,讓Disk不會被擠爆。</span><br />
<span style="font-size: small;">2.寄出Mail的部分,集滿五個拉環才寄出,不會一個Message寄發信,讓我的信箱不被塞爆。</span><br />
<br />
<span style="font-size: small;">首先附上我的Nlog.config</span><br />
<span style="font-size: small;"> </span><br />
<pre class="brush:xml"><nlog autoreload="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.nlog-project.org/schemas/NLog.xsd">
<targets>
<target archiveevery="Day" archivefilename="${basedir}/archives/log.{#}.txt" archivenumbering="Rolling" concurrentwrites="true" encoding="iso-8859-2" filename="${basedir}/logs/logfile.txt" keepfileopen="false" layout="${longdate} ${logger} ${message}" maxarchivefiles="7" name="file" xsi:type="File">
<target buffersize="5" name="mail" xsi:type="BufferingWrapper">
<target from="email@xxx.com" layout="${longdate}|${level:uppercase=true}|${logger}|${message}${newline}" smtpserver="smtpserver" subject="[Alert]subject" to="pinny@xxx.com.tw" xsi:type="Mail">
</target>
</target></target></targets>
<rules>
<logger minlevel="Debug" name="*" writeto="file">
<logger minlevel="Warn" name="*" writeto="mail">
</logger></logger></rules>
</nlog>
</pre>
<br />
<span style="font-size: small;"></span><br />
<a name='more'></a><span style="font-size: small;">Config檔案分成兩大塊,targets 與rules</span><br />
targets主要是設定Log要紀錄的方式<br />
rules主要則是決定紀錄Log的level與輸出的方式<br />
<br />
我的targets的設定針對兩個紀錄方式:File / Mail<br />
針對File的部分,奧妙的地方:<br />
1. layout => 寫入Log的內容 (時間+程式名稱+Message)<br />
2. archive相關 => 以Day切Log檔,最多存7個檔案,超過一週就會被覆蓋<br />
詳細可看<a href="http://nlog-project.org/wiki/File_target#Time-based_file_archival">官網範例</a><br />
針對Mail的部分,奧妙的地方:<br />
1.設定SMTP等mail setting是一定要的<br />
2.其中bufferSize則是集滿拉環才寄出,不然將會一個log message就丟出一封信來,當然如果只有兩則message在程式結束後也會mail出來<br />
<br />
針對rules就相對簡單易懂了,minlevel就是比你設定的level嚴重的才記log<br />
log level依序越來越嚴重:<br />
<ul>
<li> Trace.log
</li>
<li> Debug.log
</li>
<li> Info.log
</li>
<li> Warn.log
</li>
<li> Error.log
</li>
<li> Fatal.log
</li>
</ul>
我的設定就是Debug嚴重度以上的都要紀錄在File中<br />
Warm嚴重度以上的才寄信通知我!!!!<br />
<br />
<br />吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com2tag:blogger.com,1999:blog-1529398903068091023.post-37617375591632959532010-12-20T21:35:00.001+08:002010-12-20T21:39:22.400+08:00[Oracle]Update data from another TableWeb系統不免會有由Excel Upload的資料去更新資料表的需求,當然如果Excel資料一筆一筆的對Table做更新是不符合效益,這將造成對database的transaction過多,而且因為目前需Update的資料表資料量相當大,所以採用的方法如下:<br />
1.Insert data in db temp table from excel data<br />
2.update data from temp table<br />
<br />
上述的從Temp table取用資料Update資料表,就切入本篇的主題,可用的方法有三:<br />
1.Update by sub-query<br />
2.Update table view<br />
3.Merge<br />
<br />
<div style="color: red;"><b>方法一:</b></div><pre class="brush:sql">UPDATE bigTable b
SET (col1,col2,col3) = (
SELECT t.col1,t.col2,t.col3
FROM tempTable t
WHERE b.col = t.col)
where exists(
SELECT t.col1,t.col2,t.col3
FROM tempTable t
WHERE b.col = t.col);
</pre><div style="color: red;"><b>方法二:</b></div><pre class="brush:sql">UPDATE (
SELECT b.col1 as old_col1,
b.col2 as old_col2,
b.col3 as old_col3,
t.col1 as new_col1,
t.col2 as new_col2,
t.col3 as new_col3
FROM bigTable b, tempTable t
WHERE b.col = t.col)
SET old_col1 = new_col1,
old_col2 = new_col2,
old_col3 = new_col3;
</pre><br />
<div style="color: red;"><b>方法三:</b></div><pre class="brush:sql">MERGE INTO bigTable b
USING (SELECT col1 , col2 , col3 , col
tempTable t ) t
ON ( b.col = t.col)
WHEN matched THEN
UPDATE
SET old_col1 = new_col1,
old_col2 = new_col2,
old_col3 = new_col3;
</pre><br />
測試結果:<br />
方法一:140 sec<br />
<div style="color: red;"><b>方法二:1 sec</b></div>方法三:未測<br />
<br />
透過方法二 明顯是效能最佳的解法,但若會被update的bigTable並非符合UK或PK的條件,將會產生<b>"ORA-01779: cannot modify a column which maps to a non-key-preserved table"</b>的錯誤,若你的table不適合建立UK或PK時,可透過hint的方式<b style="color: red;">/*+ BYPASS_UJVC */</b>來忽略UK的檢查。<br />
<pre class="brush:sql">UPDATE (
SELECT /*+ BYPASS_UJVC */ b.col1 as old_col1,
b.col2 as old_col2,
b.col3 as old_col3,
t.col1 as new_col1,
t.col2 as new_col2,
t.col3 as new_col3
FROM bigTable b, tempTable t
WHERE b.col = t.col)
SET old_col1 = new_col1,
old_col2 = new_col2,
old_col3 = new_col3;
</pre><br />
參考網址:<a href="http://blog.csdn.net/yuhua3272004/archive/2008/08/06/2776121.aspx">http://blog.csdn.net/yuhua3272004/archive/2008/08/06/2776121.aspx</a>吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com1tag:blogger.com,1999:blog-1529398903068091023.post-5091994078438996612010-11-17T23:28:00.000+08:002010-11-17T23:28:12.013+08:00[Oracle]處理中文亂碼 -- NLS_LANG設定開發連結oracle資料庫不可不注意中文的處理問題<br />
<br />
首先必須確認DB Server的編碼設定<br />
<br />
<pre class="brush:sql">select userenv('language') from dual;
</pre>取得結果為TRADITIONAL_CHINESE.TAIWAN.ZHT16BIG5<br />
<br />
接下來就是設定client端的NLS_LANG,只要與DB Server相同,中文顯示就會正確了<br />
<br />
<b>1. 開始 -> 執行 -> regedit</b><br />
<b>2. 找出 HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE</b><br />
<b>3. NLS_LANG改為TRADITIONAL_CHINESE.TAIWAN.ZHT16BIG5</b><br />
<br />
這樣由這台client連到TRADITIONAL_CHINESE.TAIWAN.ZHT16BIG5 的DB Server中文顯示就會是正確的。<br />
<br />
另一個議題,單一client要連結的DB Server有多種NLS_LANG,擔任Web Server的client常有這種狀況!!!<br />
<br />
以ASP.NET為例:<br />
<br />
<pre class="brush:csharp">public void ReadMyData(string connectionString)
{
//設定Oracle NLS_LANG,在connect db前設定環境變數
System.Environment.SetEnvironmentVariable("NLS_LANG", "AMERICAN_AMERICA.ZHT32EUC");
string queryString = "SELECT OrderID, CustomerID FROM Orders";
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
OleDbCommand command = new OleDbCommand(queryString, connection);
connection.Open();
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.GetInt32(0) + ", " + reader.GetString(1));
}
// always call Close when done reading.
reader.Close();
}
}
</pre><br />
其關鍵就是<br />
<pre style="color: red;"><b><span lang="EN-US">System.Environment.<wbr></wbr>SetEnvironmentVariable("NLS_<wbr></wbr>LANG","AMERICAN_AMERICA.ZHT32EUC");</span></b></pre><pre style="color: red;"><b><span lang="EN-US"> </span></b></pre><pre style="color: black;">針對<span lang="EN-US">單一Web Site設定連線到DB NLS_LANG,</span><span lang="EN-US">就可以一勞永逸的解決中文問題,不管程式被Deploy到哪裡,
都不受那台Web Server的NLS_Lang影響。</span></pre>吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com4tag:blogger.com,1999:blog-1529398903068091023.post-91998969131138537672010-10-11T21:15:00.004+08:002010-10-11T21:19:49.825+08:00[ASP.NET]Visual Studio建立N-Tier的Solution於Visual Studio實現N-Tier,目前將程式分為三層:<br />
1.UI (Web Form)<br />
2.BLL (Bussiness Logic Layer)<br />
3.DAL (Data Access Layer)<br />
<br />
要建立起這樣的架構,首先必須建立一個空白專案,我們可用此專案開啟我們的N-Tier全部檔案來檢視。<br />
<b>1.建立空白專案 </b><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzWu6rcACQvkkrFlpbfz8h00jU-kqluChL4-aEs4hY_3pKWt6vMjgFk8vv4pdb2G0H09HO_7ZyzaKvkS3aE2VsJaOrNIPZL2yKUXgz6uULb8qnBJoxvSMALrVbW2Wq7y0VMbAtjHNkC1iw/s1600/2010-10-11_135649.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzWu6rcACQvkkrFlpbfz8h00jU-kqluChL4-aEs4hY_3pKWt6vMjgFk8vv4pdb2G0H09HO_7ZyzaKvkS3aE2VsJaOrNIPZL2yKUXgz6uULb8qnBJoxvSMALrVbW2Wq7y0VMbAtjHNkC1iw/s1600/2010-10-11_135649.png" /></a></div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<b> 2.建立Client檔案夾,加入Web Application 的Project</b><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgq_-sN99xQbonOq5CaXxwxC3gRnDsvYz3v4s0EyYoEGPZFjqLaH3-u_zPeHhloxmE0AG3xLufWK4LAe7lum1qj2GzC7GkjPTHc1jSpglfO4xmmqaqKrpMNftty2jpBQrfq28OCH_2ohUMB/s1600/2010-10-11_140415.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgq_-sN99xQbonOq5CaXxwxC3gRnDsvYz3v4s0EyYoEGPZFjqLaH3-u_zPeHhloxmE0AG3xLufWK4LAe7lum1qj2GzC7GkjPTHc1jSpglfO4xmmqaqKrpMNftty2jpBQrfq28OCH_2ohUMB/s320/2010-10-11_140415.png" width="280" /></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYT6b6IYE2DG97WRkzQLdByWYUqfYWM6X_Ob5vHaum6vWt3VjM_YcJZq2IKQuW2RbzrBUqI4Vnbb8Is2WBuXs_O3koTjHD-BgASd5VxB1vAL_mb7dqEjErVtyb_g6lKmDQkN-tXCMQbJmA/s1600/2010-10-11_140509.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="189" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYT6b6IYE2DG97WRkzQLdByWYUqfYWM6X_Ob5vHaum6vWt3VjM_YcJZq2IKQuW2RbzrBUqI4Vnbb8Is2WBuXs_O3koTjHD-BgASd5VxB1vAL_mb7dqEjErVtyb_g6lKmDQkN-tXCMQbJmA/s320/2010-10-11_140509.png" width="320" /></a></div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<b><br />
</b><br />
<b>3.建立General檔案夾,建立BLL與DAL的Project</b><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj6fuHdGKLGkKXcE4WL6bX546ff0SW7EtGHSLQ4lrerDHqMw38YeyCVRYWnltdaqCkA66eIEulLAZVTiMyMx42psxuuLPCb_VWIw_2sGf2fOuao_-AX6msr2-F1DsuPtl88UGb4hURFNtxk/s1600/2010-10-11_140550.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj6fuHdGKLGkKXcE4WL6bX546ff0SW7EtGHSLQ4lrerDHqMw38YeyCVRYWnltdaqCkA66eIEulLAZVTiMyMx42psxuuLPCb_VWIw_2sGf2fOuao_-AX6msr2-F1DsuPtl88UGb4hURFNtxk/s320/2010-10-11_140550.png" width="222" /></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUn4EVGZG38CuPSrmxv5aG1eah3EGZuaLl_puaWIWHHptF9Vx2QuAIfHkJttp2nzD6l06-p_vfEghb0ko10-c7Ja8riaaMcZb_c-4gpAQ8wvQIAeWjBk1VFpovrQyM0-lkyATa5xJ7aAqT/s1600/2010-10-11_140618.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="238" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUn4EVGZG38CuPSrmxv5aG1eah3EGZuaLl_puaWIWHHptF9Vx2QuAIfHkJttp2nzD6l06-p_vfEghb0ko10-c7Ja8riaaMcZb_c-4gpAQ8wvQIAeWjBk1VFpovrQyM0-lkyATa5xJ7aAqT/s400/2010-10-11_140618.png" width="400" /></a></div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<b>4.檢視建立完畢的Solution</b><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0ws85FHOsfTB-oc1drMopqtWoueGDZR_P6wv5CnwtY7sKqRbX6CJrVTbQM-TTAztSZeDJNYQoXOwGpULBRDsP93GdW980U3dBFBJHtMlnJYpxAFepj_PGWt6_ogSoPMbYvBZz77KTu0Qy/s1600/2010-10-11_140725.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0ws85FHOsfTB-oc1drMopqtWoueGDZR_P6wv5CnwtY7sKqRbX6CJrVTbQM-TTAztSZeDJNYQoXOwGpULBRDsP93GdW980U3dBFBJHtMlnJYpxAFepj_PGWt6_ogSoPMbYvBZz77KTu0Qy/s1600/2010-10-11_140725.png" /></a></div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<b>5.各Layer間要加入Reference,這樣就可以把整個Solution關係都拉起來了~</b><br />
首先,BLL將DAL加入Reference,<br />
接著就是在Web Application加入BLL與DAL。<br />
<br />
<div class="separator" style="clear: both; text-align: center;"></div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-WYYvvhgqJLn1phyF0yGzx7ToMJmatJ49R5XqSTj8nydfJ3tXz5AfPbR3OQcQh9fJ2AiQ91mGuXDpHFRlLtloARSSKQd0mKqJ58YUSrxt9GEkC_3Uvc7402Kz4wC6HSyELqkVwOsPgAWH/s1600/2010-10-11_140824.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-WYYvvhgqJLn1phyF0yGzx7ToMJmatJ49R5XqSTj8nydfJ3tXz5AfPbR3OQcQh9fJ2AiQ91mGuXDpHFRlLtloARSSKQd0mKqJ58YUSrxt9GEkC_3Uvc7402Kz4wC6HSyELqkVwOsPgAWH/s400/2010-10-11_140824.png" width="224" /></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2lGSoowjN37YW3auTK6xiN_vr3vLVsu-TnMYBIeKukZ-bbh80vhX3K1gVn5GmtQeis8jktFc5bBbUnywpREaJDzp1UIX8HSePG39DoxxEWXOSg3h9H3p0N7Q-Ui-xafGxJzn0J4iQxOeh/s1600/2010-10-11_140910.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="284" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2lGSoowjN37YW3auTK6xiN_vr3vLVsu-TnMYBIeKukZ-bbh80vhX3K1gVn5GmtQeis8jktFc5bBbUnywpREaJDzp1UIX8HSePG39DoxxEWXOSg3h9H3p0N7Q-Ui-xafGxJzn0J4iQxOeh/s400/2010-10-11_140910.png" width="400" /></a></div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<b>6.檢視整個Solution加入Reference後的結果</b><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYpo9nH2wm0I8d4HHWZSQ1igZzh2Fb4qGa8033q8KaNdIuJ9QBm-GAEOUDQg7vdpb2JCO5nYKYsGj0smd1Y6QBAZbGKJwvjaldBVRIBkqQVDBYc2Z_RhCI9BMYELpWrXlecPMcXx6w64zA/s1600/2010-10-11_140957.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYpo9nH2wm0I8d4HHWZSQ1igZzh2Fb4qGa8033q8KaNdIuJ9QBm-GAEOUDQg7vdpb2JCO5nYKYsGj0smd1Y6QBAZbGKJwvjaldBVRIBkqQVDBYc2Z_RhCI9BMYELpWrXlecPMcXx6w64zA/s1600/2010-10-11_140957.png" /></a></div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
這樣就大致的把3-Tier切出來了,<br />
透過如此的切法,<br />
DAL主要只負責用來連結資料庫,對資料庫做資料處理。<br />
BLL則是扮演DAL與UI之前的橋樑,對於取出資料庫取出的資料作邏輯處理,回覆資料給UI。<br />
UI則是單純的扮演呼叫BLL與呈現資料,不做邏輯處理與判斷。吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com1tag:blogger.com,1999:blog-1529398903068091023.post-87885599820226040962010-06-07T21:45:00.004+08:002010-06-07T21:48:09.243+08:00[Oracle]箱型圖(BoxPlot)統計值運算繪製<a href="http://zh.wikipedia.org/zh-tw/%E7%AE%B1%E5%BD%A2%E5%9C%96">箱型圖</a>顯示一組數據的分散情況,而若從資料庫中的Raw Data,來繪製箱型圖,則需結算出六個必要數值:最大值、最小值、中位數、平均值、Q1下<a href="http://zh.wikipedia.org/zh-hant/%E5%9B%9B%E5%88%86%E4%BD%8D%E6%95%B0">四分位數</a>、Q3下<a href="http://zh.wikipedia.org/zh-hant/%E5%9B%9B%E5%88%86%E4%BD%8D%E6%95%B0">四分位數</a><br />
<br />
以Oracle範例資料庫中的employees資料表為例,計算每個部門的箱型圖數值:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCWy8xihQ6_OPujiN7hpuvigqhmqiYxGSz62hV4a7I8P36uHtjrgASNx6JTyvpraeJP3phqIQsW5PNTzr-VsR_1HHt1skJ-hiKMbG8TYO-dwnjDCm9npuUCQE2vQBoNrDxriS3agkZ46e8/s1600/boxplot_data.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="211" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCWy8xihQ6_OPujiN7hpuvigqhmqiYxGSz62hV4a7I8P36uHtjrgASNx6JTyvpraeJP3phqIQsW5PNTzr-VsR_1HHt1skJ-hiKMbG8TYO-dwnjDCm9npuUCQE2vQBoNrDxriS3agkZ46e8/s640/boxplot_data.jpg" width="640" /></a></div><br />
<pre class="brush:sql">select department_id,
max(salary) Upper_whisker,
min(salary) Lower_whisker,
round(avg(salary),3) Average,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary ASC) Q1,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary ASC) Median,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary ASC) Q3
from employees t
group by department_id</pre><a name='more'></a><br />
<b>PERCENTILE_CONT為Oracle中的特殊Analytic Function,用於計算群組數值中分佈的百分比</b>,<br />
以中位數為例,為該群組數值中,從小到大排列的第50%的數字,<br />
PERCENTILE_CONT(<b><span style="color: red;">0.5</span></b>)的0.5則代表第50%,<br />
WITHIN GROUP (<b style="color: red;">ORDER BY salary ASC</b>)則代表以薪水salary做從小到大的排列。<br />
<br />
下圖將部門代號為100的數值以Excel驗算中位數、Q1、Q3的數值<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh5zyPTrJhkGOWrHzJW-e-3pOgFzvL9YEl-Kf44191Gjl9_q3Zw1o4OGDgsg1JEKKK0IA-igbl1JT3ZCpqH3EH4PhLyxf0VETGWrO5nS55kt_N1fy_gJmv5kTPr9Jd5F5D3tjBPwVFgdNFc/s1600/boxplot_excel.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="203" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh5zyPTrJhkGOWrHzJW-e-3pOgFzvL9YEl-Kf44191Gjl9_q3Zw1o4OGDgsg1JEKKK0IA-igbl1JT3ZCpqH3EH4PhLyxf0VETGWrO5nS55kt_N1fy_gJmv5kTPr9Jd5F5D3tjBPwVFgdNFc/s640/boxplot_excel.jpg" width="640" /></a></div>參考資源:<br />
<a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions110.htm#SQLRF00687">OracleR Database SQL Reference - PERCENTILE_CONT</a><br />
<a href="http://psoug.org/reference/analytic_functions.html">Oracle Analytic Functions</a>吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com0tag:blogger.com,1999:blog-1529398903068091023.post-46105429229074137292010-05-27T23:20:00.007+08:002010-06-07T21:59:20.091+08:00[ASP.NET]左右ListBox選單(3) -- 非同步PostBack Trigger 承<a href="http://wupinny.blogspot.com/2010/05/aspnetlistbox1-trigger.html">[ASP.NET] 左右ListBox選單(1) -- 按鈕Trigger</a> 與<a href="http://wupinny.blogspot.com/2010/05/aspnet-listbox2-javascript-trigger.html">[ASP.NET] 左右ListBox選單(2) -- Javascript Trigger</a>的主題,有介紹以雙擊(double click)去觸發PostBack,然而以javascript送出則會有整頁往後送的效果,網頁會閃爍。<br />
本文介紹將Control放入Update Panel來避免頁面閃爍的現象,另以非同步的javascript去做PostBack,同時可達到雙擊的效果與簡短了程式碼的撰寫。<br />
<br />
首先,承<a href="http://wupinny.blogspot.com/2010/05/aspnetlistbox1-trigger.html">上文</a>的Code:<br />
<ol><li>Control放入UpdatePanel中</li>
<li>ListBox的Control內加入Double Click事件的Function</li>
<li>__doPostBack('ButtonRight','') 為非同步送出觸發ButtonRight 的事件<br />
<a name='more'></a> </li>
</ol><pre ;="" class="brush:xml" collapse:="" true;=""><asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<table align="center" class="style1" style="width: 400px">
<tr>
<td align="left" style="font-weight: bold; font-size: 13px; font-family: Arial" width="150px">
可選擇的ProductID </td>
<td>
&nbsp; </td>
<td align="left" style="font-weight: bold; font-size: 13px; font-family: Arial">
已選擇的ProductID </td>
</tr>
<tr>
<td align="left">
<asp:ListBox ID="ListBoxLeft" ondblclick="__doPostBack('ButtonRight','')" runat="server"
Rows="5" SelectionMode="Multiple" Width="150px"></asp:ListBox>
</td>
<td align="center">
<asp:Button ID="ButtonRight" runat="server" OnClick="ButtonRight_Click" Text=" > "
Width="40px" CssClass="barkbutton" />
<br />
<asp:Button ID="ButtonRightAll" runat="server" OnClick="ButtonRightAll_Click" Text=">>"
Width="40px" CssClass="barkbutton" />
<br />
<asp:Button ID="ButtonLeft" runat="server" OnClick="ButtonLeft_Click" Text=" < "
Width="40px" CssClass="barkbutton" />
<br />
<asp:Button ID="ButtonLeftAll" runat="server" OnClick="ButtonLeftAll_Click" Text="<<"
Width="40px" CssClass="barkbutton" />
</td>
<td align="left">
<asp:ListBox ID="ListBoxRight" ondblclick="__doPostBack('ButtonLeft','');" runat="server" Rows="5" SelectionMode="Multiple" Width="150px">
</asp:ListBox>
</td>
</tr>
</table>
</ContentTemplate>
</asp:UpdatePanel></pre><br />
<a href="http://wupinny.blogspot.com/2010/05/aspnetlistbox1-trigger.html">[ASP.NET] 左右ListBox選單(1) -- 按鈕Trigger</a><br />
<a href="http://wupinny.blogspot.com/2010/05/aspnet-listbox2-javascript-trigger.html">[ASP.NET] 左右ListBox選單(2) -- Javascript Trigger</a><br />
<a href="http://wupinny.blogspot.com/2010/05/aspnetlistbox3-postback-trigger.html">[ASP.NET]左右ListBox選單(3) -- 非同步PostBack Trigger </a>吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com0tag:blogger.com,1999:blog-1529398903068091023.post-2548626182067335602010-05-26T23:47:00.004+08:002010-06-07T21:59:41.411+08:00[ASP.NET]左右ListBox選單(2) -- Javascript Trigger承<a href="http://wupinny.blogspot.com/2010/05/aspnetlistbox1-trigger.html">[ASP.NET] 左右ListBox選單(1) -- 按鈕Trigger</a> 的主題,選取Value後按下中間的四個按鈕觸發PostBack,本文將雙擊(double click)的動作加入觸發PostBack的行列,讓選取的時候更加方便與直覺。<br />
<br />
首先,承上文的Code:<br />
<ol><li><head></head>之間加入javascript,雙擊時將送出form</li>
<li>ListBox的Control內加入Double Click事件的Function,並加入一個hidden欄位</li>
<li>CS頁面內加入接收雙擊ListBox時操作<br />
<a name='more'></a> </li>
</ol><b style="color: red;">加入Javascript</b> <br />
<pre class="brush:xml"><script language="javascript">
function ListBox1_DoubleClick() {
/* we will change value of this hidden field so that inpage load event we can identify event.
*/
document.forms[0].ListBox1Hidden.value = "doubleclicked";
document.forms[0].submit();
}
</script></pre><br />
<div style="color: red;"><b>ListBox加入事件並加hidden 欄位</b></div><pre class="brush:xml"><asp:ListBox ID="ListBoxLeft" ondblclick="ListBox1_DoubleClick()" runat="server"
Rows="5" SelectionMode="Multiple" Width="150px"></asp:ListBox>
<input type="hidden" name="ListBox1Hidden" />
</pre><br />
<div style="color: red;"><b>cs頁面加入ListBox Double Click Code</b></div><pre class="brush:c-sharp">protected void Page_Load(object sender, EventArgs e)
{
if (Request.Params["ListBox1Hidden"] != null && (string)Request.Params["ListBox1Hidden"] == "doubleclicked")
{
ButtonRight_Click(null,null);
}
}</pre>雙擊左方的ListBox則當成第一個向右按鈕被觸發。<br />
<br />
<a href="http://wupinny.blogspot.com/2010/05/aspnetlistbox1-trigger.html">[ASP.NET]左右ListBox選單(1) -- 按鈕Trigger</a><br />
<a href="http://wupinny.blogspot.com/2010/05/aspnet-listbox2-javascript-trigger.html">[ASP.NET]左右ListBox選單(2) -- Javascript Trigger</a><br />
<a href="http:///#">[ASP.NET]左右ListBox選單(3) -- 非同步PostBack Trigger </a>吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com0tag:blogger.com,1999:blog-1529398903068091023.post-22768099502643240202010-05-25T21:29:00.010+08:002010-05-26T23:49:30.968+08:00[ASP.NET]左右ListBox選單(1) -- 按鈕Trigger以ListBox實做左方ListBox為可選擇之項目,右方ListBox為已選擇之項目,中間放置四個按鈕,分別為向右鈕/全選向右鈕/向左鈕/全選向左鈕。<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8iEPCxITb_mZGUM05_oWw_9nYAmiGbeti4xGivXs6u2UwrKfDCemmwMao0abI31uhMo-mqedD2VoXbJdy4NQpZmuw_j6V1cEhqImoflJBAdLqphcixhxF7JSgOJkap7GPmMjDd8Ot9_0c/" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8iEPCxITb_mZGUM05_oWw_9nYAmiGbeti4xGivXs6u2UwrKfDCemmwMao0abI31uhMo-mqedD2VoXbJdy4NQpZmuw_j6V1cEhqImoflJBAdLqphcixhxF7JSgOJkap7GPmMjDd8Ot9_0c/" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8iEPCxITb_mZGUM05_oWw_9nYAmiGbeti4xGivXs6u2UwrKfDCemmwMao0abI31uhMo-mqedD2VoXbJdy4NQpZmuw_j6V1cEhqImoflJBAdLqphcixhxF7JSgOJkap7GPmMjDd8Ot9_0c/" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="138" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8iEPCxITb_mZGUM05_oWw_9nYAmiGbeti4xGivXs6u2UwrKfDCemmwMao0abI31uhMo-mqedD2VoXbJdy4NQpZmuw_j6V1cEhqImoflJBAdLqphcixhxF7JSgOJkap7GPmMjDd8Ot9_0c/" width="400" /></a></div><br />
<a name='more'></a><br />
<br />
Sample.aspx<br />
<pre class="brush:xhtml"><table align="center" class="style1" style="width: 400px">
<tr>
<td align="left" style="font-weight: bold; font-size: 13px; font-family: Arial" width="150px">
可選擇的ProductID </td>
<td>
&nbsp; </td>
<td align="left" style="font-weight: bold; font-size: 13px; font-family: Arial">
已選擇的ProductID </td>
</tr>
<tr>
<td align="left">
<asp:ListBox ID="ListBoxLeft" runat="server" Rows="5" SelectionMode="Multiple" Width="150px">
<asp:ListItem>AAAA</asp:ListItem>
<asp:ListItem>BBBB</asp:ListItem>
<asp:ListItem>CCCC</asp:ListItem>
<asp:ListItem>DDDD</asp:ListItem>
</asp:ListBox>
</td>
<td align="center">
<asp:Button ID="ButtonRight" runat="server" OnClick="ButtonRight_Click" Text="Right"
Width="70px" CssClass="barkbutton" />
<br />
<asp:Button ID="ButtonRightAll" runat="server" OnClick="ButtonRightAll_Click" Text="ALL Right"
Width="70px" CssClass="barkbutton" />
<br />
<asp:Button ID="ButtonLeft" runat="server" OnClick="ButtonLeft_Click" Text="Left"
Width="70px" CssClass="barkbutton" />
<br />
<asp:Button ID="ButtonLeftAll" runat="server" OnClick="ButtonLeftAll_Click" Text="ALL Left"
Width="70px" CssClass="barkbutton" />
</td>
<td align="left">
<asp:ListBox ID="ListBoxRight" ondblclick="__doPostBack('ButtonLeft','');" runat="server"
Rows="5" SelectionMode="Multiple" Width="150px"></asp:ListBox>
</td>
</tr>
</table>
</pre>Sample.aspx.cs<br />
<br />
<pre class="brush:c-sharp">protected void ButtonRight_Click(object sender, EventArgs e)
{
if (ListBoxLeft.SelectedItem != null)
{
foreach (ListItem item in ListBoxLeft.Items)
{
if (item.Selected == true)
{
ListBoxRight.Items.Add(item);
}
}
foreach (ListItem item in ListBoxRight.Items)
{
if (item.Selected == true)
{
ListBoxLeft.Items.Remove(item);
}
}
}
}
protected void ButtonRightAll_Click(object sender, EventArgs e)
{
foreach (ListItem item in ListBoxLeft.Items)
{
ListBoxRight.Items.Add(item);
}
ListBoxLeft.Items.Clear();
}
protected void ButtonLeft_Click(object sender, EventArgs e)
{
if (ListBoxRight.SelectedItem != null)
{
foreach (ListItem item in ListBoxRight.Items)
{
if (item.Selected == true)
{
ListBoxLeft.Items.Add(item);
}
}
foreach (ListItem item in ListBoxLeft.Items)
{
if (item.Selected == true)
{
ListBoxRight.Items.Remove(item);
}
}
}
}
protected void ButtonLeftAll_Click(object sender, EventArgs e)
{
foreach (ListItem item in ListBoxRight.Items)
{
ListBoxLeft.Items.Add(item);
}
ListBoxRight.Items.Clear();
} </pre><a href="http://wupinny.blogspot.com/2010/05/aspnetlistbox1-trigger.html">[ASP.NET]左右ListBox選單(1) -- 按鈕Trigger</a><br />
<a href="http://wupinny.blogspot.com/2010/05/aspnet-listbox2-javascript-trigger.html">[ASP.NET]左右ListBox選單(2) -- Javascript Trigger</a><br />
<a href="http:///#">[ASP.NET]左右ListBox選單(3) -- 非同步PostBack Trigger </a>吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com0tag:blogger.com,1999:blog-1529398903068091023.post-621768581414769712010-05-25T20:08:00.001+08:002010-05-25T20:08:32.838+08:00Blogger加上表格--放入HTML TABLE在Blogger寫程式分享的文章,若有插入表格的需求,<br />
表格編寫需要用HTML的方式,如:<table><tr><td>XXX</td></tr><br />
<br />
寫入的原始碼需用CSS的方式包住,才可避免有多餘的空白出現。<br />
<br />
<pre class="brush: xml"><style type="text/css">.nobrtable br {display:none}</style>
<div class="nobrtable">
[Table 原始碼]
</div></pre>吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com0tag:blogger.com,1999:blog-1529398903068091023.post-81864029969928383662010-05-25T20:01:00.004+08:002010-05-26T22:52:33.359+08:00[ASP.NET]LinkButton + ImageLinkButton只能秀文字,ImageButton只能秀圖片,<br />
那當有需求是文字與圖片要一起在同一個按鈕時,目前的解法就是將圖片放入LinkButton中即可。<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6V6oBQ38Hvf2N4kB7pwl6O4zf8PwkWiVMB5OqxqeBJme4oXcB44EDiawO4navdbF91OOIEgZAJXs_0_L7yVMgUs00r2J_ejDI1nCSGLRGwwJ_pJOmUONo6h4yef0m2o4UdP9Jb_SrEVKQ/s1600/2010-05-26_083653.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6V6oBQ38Hvf2N4kB7pwl6O4zf8PwkWiVMB5OqxqeBJme4oXcB44EDiawO4navdbF91OOIEgZAJXs_0_L7yVMgUs00r2J_ejDI1nCSGLRGwwJ_pJOmUONo6h4yef0m2o4UdP9Jb_SrEVKQ/s320/2010-05-26_083653.png" /></a></div><br />
<br />
<br />
<pre class="brush:xml"><asp:LinkButton runat="server" ID="LinkButton1" BorderColor="Gray"
Font-Names="Arial" ForeColor="Navy" BorderWidth="1px" BackColor="LightGray">
<img src="images/filter/funnel.png" style="border:0; vertical-align:bottom" />Data Filter
</asp:LinkButton>
</pre>吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com0tag:blogger.com,1999:blog-1529398903068091023.post-8112966751912304632010-05-13T00:23:00.005+08:002010-05-25T19:56:20.109+08:00Blogger加上程式碼 -- 高亮度顯示程式(SyntaxHighlighter)在Blogger寫程式分享的文章,必備的套用工具SyntaxHighlighter這類的高亮度顯示程式。<br />
套用方法:<br />
<ol><li> 進入管理介面<b>[<span class="content"></span>版面配置]</b></li>
<li><b>[修改 HTML] </b></li>
<li>備份範本<b></b></li>
<li><b>[修改範本</b><b>],</b>加入以下code至<head>與</head>之間<b><br />
</b></li>
</ol><pre class="brush: js"><link type="text/css" rel="stylesheet" href="http://alexgorbatchev.com/pub/sh/2.0.320/styles/shCore.css"/>
<link type="text/css" rel="stylesheet" href="http://alexgorbatchev.com/pub/sh/2.0.320/styles/shThemeDefault.css" id="shTheme"/>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/2.0.320/scripts/shCore.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/2.0.320/scripts/shLegacy.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/2.0.320/scripts/shBrushBash.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/2.0.320/scripts/shBrushCpp.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/2.0.320/scripts/shBrushCSharp.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/2.0.320/scripts/shBrushCss.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/2.0.320/scripts/shBrushDelphi.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/2.0.320/scripts/shBrushDiff.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/2.0.320/scripts/shBrushGroovy.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/2.0.320/scripts/shBrushJava.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/2.0.320/scripts/shBrushJScript.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/2.0.320/scripts/shBrushPhp.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/2.0.320/scripts/shBrushPlain.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/2.0.320/scripts/shBrushPython.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/2.0.320/scripts/shBrushRuby.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/2.0.320/scripts/shBrushScala.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/2.0.320/scripts/shBrushSql.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/2.0.320/scripts/shBrushVb.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/2.0.320/scripts/shBrushXml.js"></script>
<script type='text/javascript'>
SyntaxHighlighter.config.bloggerMode = true;
SyntaxHighlighter.ClipboardSwf = 'http://http://alexgorbatchev.com/pub/sh/2.0.320/scripts/clipboard.swf';
SyntaxHighlighter.all();
</script>
</pre>後續只要在code以<pre class="brush:xml"></pre>框住即可<br />
<br />
<div style="color: red;"><b>brushes 分類如下:</b></div><br />
<style type="text/css">
.nobrtable br {display:none}
</style><br />
<div class="nobrtable"><br />
<table #a4d1ff="" bgcolor:="" border="1" cellpadding="0" cellspacing="0"><tbody>
<tr> <th>Brush name</th><th>Brush aliases</th><th>File name</th></tr>
<tr> <td>ActionScript3</td><td>as3, actionscript3</td><td>shBrushAS3.js</td></tr>
<tr> <td>Bash/shell</td><td>bash, shell</td><td>shBrushBash.js</td></tr>
<tr> <td>ColdFusion</td><td>cf, coldfusion</td><td>shBrushColdFusion.js</td></tr>
<tr> <td>C#</td><td>c-sharp, csharp</td><td>shBrushCSharp.js</td></tr>
<tr> <td>C++</td><td>cpp, c</td><td>shBrushCpp.js<br />
<br />
</td></tr>
<tr> <td>CSS</td><td>css</td><td>shBrushCss.js</td></tr>
<tr> <td>Delphi</td><td>delphi, pas, pascal</td><td>shBrushDelphi.js<br />
<br />
</td></tr>
<tr> <td>Diff</td><td>diff, patch</td><td>shBrushDiff.js</td></tr>
<tr> <td>Erlang</td><td>erl, erlang</td><td>shBrushErlang.js<br />
<br />
</td></tr>
<tr> <td>Groovy</td><td>groovy</td><td>shBrushGroovy.js</td></tr>
<tr> <td>JavaScript</td><td>js, jscript, javascript</td><td>shBrushJScript.js<br />
<br />
</td></tr>
<tr> <td>Java</td><td>java</td><td>shBrushJava.js</td></tr>
<tr> <td>JavaFX</td><td>jfx, javafx</td><td>shBrushJavaFX.js<br />
<br />
</td></tr>
<tr> <td>Perl</td><td>perl, pl</td><td>shBrushPerl.js</td></tr>
<tr> <td>PHP</td><td>php</td><td>shBrushPhp.js<br />
<br />
</td></tr>
<tr> <td>Plain Text</td><td>plain, text</td><td>shBrushPlain.js</td></tr>
<tr> <td>PowerShell</td><td>ps, powershell</td><td>shBrushPowerShell.js<br />
<br />
</td></tr>
<tr> <td>Python</td><td>py, python</td><td>shBrushPython.js</td></tr>
<tr> <td>Ruby</td><td>rails, ror, ruby</td><td>shBrushRuby.js<br />
<br />
</td></tr>
<tr> <td>Scala</td><td>scala</td><td>shBrushScala.js</td></tr>
<tr> <td>SQL</td><td>sql</td><td>shBrushSql.js<br />
<br />
</td></tr>
<tr> <td>Visual Basic</td><td>vb, vbnet</td><td>shBrushVb.js</td></tr>
<tr> <td>XML</td><td>xml, xhtml, xslt, html, xhtml</td><td>shBrushXml.js<br />
<br />
</td></tr>
</tbody></table><br />
<br />
<br />
</div>吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com0tag:blogger.com,1999:blog-1529398903068091023.post-33274716902490736642010-05-12T23:48:00.011+08:002010-05-26T23:06:02.462+08:00[Linq]LINQ to Object (ListBox)可複選的ListBox取出被Selected的值。<br />
解法一:使用LINQ取出Selected == true<br />
解法二:使用for迴圈尋訪ListBox的每一個ListItem,並判斷ListItem.Selected == true<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8iEPCxITb_mZGUM05_oWw_9nYAmiGbeti4xGivXs6u2UwrKfDCemmwMao0abI31uhMo-mqedD2VoXbJdy4NQpZmuw_j6V1cEhqImoflJBAdLqphcixhxF7JSgOJkap7GPmMjDd8Ot9_0c/s1600/2010-05-26_084742.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"></a></div><br />
Sample.aspx<br />
<pre class="brush: xml"><asp:ListBox ID="ListBox1" runat="server" Rows="5" SelectionMode="Multiple" Width="150px">
<asp:ListItem>AAAA</asp:ListItem>
<asp:ListItem>BBBB</asp:ListItem>
<asp:ListItem>CCCC</asp:ListItem>
<asp:ListItem>DDDD</asp:ListItem>
</asp:ListBox></pre><br />
<div style="color: red;"><b>解法一:</b></div>Sample.aspx.cs<br />
<pre class="brush:c-sharp">var selItems = from ListItem li in ListBox1.Items
where li.Selected == true
select li.Text;
Response.Write("Selected Item(s): ");
foreach (var item in selItems)
{
Response.Write(item.ToString() + "");
}
</pre><br />
<div style="color: red;"><b>解法二:</b></div>Sample.aspx.cs<br />
<pre class="brush:c-sharp">ListBox1.SelectionMode = ListSelectionMode.Multiple;
for (int i = 0; i < ListBox1.Items.Count; i++)
{
if(ListBox1.Items[i].Selected == true)
{
Response.Write( ListBox1.Items[i].ToString() + "");
}
}
</pre>吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com0tag:blogger.com,1999:blog-1529398903068091023.post-49719049541240921832010-05-11T23:02:00.031+08:002010-05-26T22:58:21.322+08:00[Linq]LINQ to DataSet針對DataTable的資料,若有加工或篩選的需求,Linq 是目前我選用要熟悉的技術。<br />
<br />
<a href="http://msdn.microsoft.com/zh-tw/library/bb552415%28v=VS.90%29.aspx">LINQ to DataSet 中的查詢</a><br />
<br />
其中範例中,大致分成兩種,一為查詢運算式語法,二為以方法為基礎的查詢語法<br />
其實對我而言,就是一個有from,另一個沒有~<br />
當我遇到要做Order By時,似乎只有看到有from那種語法有範例。<br />
<a name='more'></a><br />
<div style="color: blue;"><b>查詢運算式語法</b></div><pre class="brush: c-sharp">DataTable products = ds.Tables["Product"];
IEnumerable<datarow> query =
from product in products.AsEnumerable()
select product;
Console.WriteLine("Product Names:");
foreach (DataRow p in query)
{
Console.WriteLine(p.Field<string>("Name"));
}</pre><pre class="brush: c-sharp">DataTable products = ds.Tables["Product"];
IEnumerable<datarow> query =
from product in products.AsEnumerable()
orderby product.Field<decimal>("ListPrice") descending
select product;
// Force immediate execution of the query.
IEnumerable<datarow> productsArray = query.ToArray();
Console.WriteLine("Every price from highest to lowest:");
foreach (DataRow prod in productsArray)
{
Console.WriteLine(prod.Field<decimal>("ListPrice"));
}
</pre><div style="color: blue;"><b>以方法為基礎的查詢語法</b></div><pre class="brush: c-sharp">DataTable products = ds.Tables["Product"];
var query = products.AsEnumerable().
Select(product => new
{
ProductName = product.Field<string>("Name"),
ProductNumber = product.Field<string>("ProductNumber"),
Price = product.Field<decimal>("ListPrice")
});
Console.WriteLine("Product Info:");
foreach (var productInfo in query)
{
Console.WriteLine("Product name: {0} Product number: {1} List price: ${2} ",
productInfo.ProductName, productInfo.ProductNumber, productInfo.Price);
}</pre>吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com1tag:blogger.com,1999:blog-1529398903068091023.post-38374907517893884522010-01-25T22:24:00.002+08:002010-01-25T22:28:55.657+08:00[Oracle]PL/SQL與T-SQL的語法比較文件今日從企圖找出PL/SQL中continue的相似語法,無意間找到了一份PL/SQL與T-SQL的語法用法比較的word文件。也同時也試用google共用文件的功能。<br />
<br />
<a href="http://docs.google.com/View?id=drj5cdm_1497sz5vf4">PL/SQL與T-SQL比較Word文件</a><br />
<br />
稍後讀完文件後,再做補充說明吧~~今天雖然是文章發表日,就讓我先偷懶一下下吧!!!吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com0tag:blogger.com,1999:blog-1529398903068091023.post-88056700163376777572010-01-20T11:09:00.002+08:002010-05-12T07:48:16.515+08:00[Oracle]Week的時間區間日前有一個需求為呈現從今日往前推53週的日期,列出每週的週別與開始與結束日期。每週的開始日期為週二。<br />
<br />
呈現如下: <br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRsyyWLBD730WeXHGpcuiLUGH7I57zISKW4mCMxc4Ki2cmm7-dzHtjsZdyhNVsJXQBuJ9lxaaD37Gym-QroUh1GHV7yHEAdmqhVfhyDVFc8x_5OERUhq75c_gUEh3vZLDVYpZ-JH7K2iri/s1600-h/weekno.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRsyyWLBD730WeXHGpcuiLUGH7I57zISKW4mCMxc4Ki2cmm7-dzHtjsZdyhNVsJXQBuJ9lxaaD37Gym-QroUh1GHV7yHEAdmqhVfhyDVFc8x_5OERUhq75c_gUEh3vZLDVYpZ-JH7K2iri/s400/weekno.png" /></a><br />
</div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<a name='more'></a><b><span style="color: red;">解法一:</span></b> <br />
<pre class="brush: sql" name="code">select to_char(start_date, 'iyyy') || 'W' || to_char(start_date, 'iw') week_no,
to_char(start_date, 'yyyy/mm/dd') start_date,
to_char(end_date, 'yyyy/mm/dd') end_date
from (select next_day(sysdate, 3) - rn * 7 start_date,
(next_day(sysdate, 3) - (rn - 1) * 7) - 1 end_date
from (select rownum rn from dual connect by level <= 53)) </pre>重點說明:<br />
<ol><li>使用connect by level <= 53 去生出53筆資料 ,rownum依序呈現1,2,3,4....等數據。</li>
<li>使用next_day去指定週二為一週的起始日期。</li>
<li>使用to_char()參數選用iyyy與iw去指定使用iso的週別定義方式轉換週別。<br />
</li>
</ol>吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com2tag:blogger.com,1999:blog-1529398903068091023.post-57451829095464094742010-01-11T22:40:00.002+08:002010-05-12T07:47:51.501+08:00[Oracle]Mutil Row To One Row應用(二)本次範例應用北風資料For Oracle中<b> orders </b>的訂單資料Table做為Sample。本次範例想要取得訂單資料中每天訂單中每個貨運公司的最大運費,並且有優先次序的選擇呈現貨運公司的資料,例如:如果當天有托運公司1,2,3呈現資料的次序為<b>1 > 2 > 3</b> 。<br />
<br />
<pre class="brush: sql" name="code">select orderid, orderdate, shipvia, freight from orders t
</pre><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdeDYeZf_JWW7KgskrVl4ezaFuyCh2Xcc5xonExJsxEZSxNW14lSQzLdcmpHDJ5hAlaNHegA79aoyoCgZN_R-zkrvjNEiFZOi7OAu5_K4cAAJ6gDdodcVXJOfuf5UO7P0Qiy0vfCweBha3/s1600-h/order1.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdeDYeZf_JWW7KgskrVl4ezaFuyCh2Xcc5xonExJsxEZSxNW14lSQzLdcmpHDJ5hAlaNHegA79aoyoCgZN_R-zkrvjNEiFZOi7OAu5_K4cAAJ6gDdodcVXJOfuf5UO7P0Qiy0vfCweBha3/s320/order1.png" /></a><br />
</div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
結果如下:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3dCpS6m63svlXq7u1vbrsewrp6vw5DAh_ZxpFaw_UkYfFI-eZC_eZ15N97MKTWdlzjhbNGrpruJkzrSPNLL4ZuyGLQiFmCAtBhc-y6lhMySil6iIdAjhfL8uMJAtdxj8WV46bYERO9esG/s1600-h/order2.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3dCpS6m63svlXq7u1vbrsewrp6vw5DAh_ZxpFaw_UkYfFI-eZC_eZ15N97MKTWdlzjhbNGrpruJkzrSPNLL4ZuyGLQiFmCAtBhc-y6lhMySil6iIdAjhfL8uMJAtdxj8WV46bYERO9esG/s320/order2.png" /></a><br />
</div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<div style="color: red;"><b><a name='more'></a></b><br />
<b>解法一:</b><br />
</div><br />
<pre class="brush: sql" name="code">select orderdate,
shippers,
coalesce(shippers1_freight, shippers2_freight, shippers3_freight) shippers_freight
from (select orderdate,
min(shipvia) shippers,
max(decode(shipvia, 1, freight)) shippers1_freight,
max(decode(shipvia, 2, freight)) shippers2_freight,
max(decode(shipvia, 3, freight)) shippers3_freight
from (select to_char(t.orderdate, 'yyyy/mm/dd') orderdate,
shipvia,
max(t.freight) freight
from orders t
group by to_char(t.orderdate, 'yyyy/mm/dd'), shipvia)
group by orderdate
order by orderdate)
</pre><br />
接著就要把SQL分三層一一抽絲剝繭,想當然爾的從最內層開始解說:<br />
<ol><li> group by to_char(t.orderdate, 'yyyy/mm/dd'), shipvia -- 依據每日每個貨運公司取得最大運費 max(t.freight)。</li>
<li> Mutil Row To One Row:利用group搭配max函數與decode將三個貨運公司分列為三個欄位。</li>
<li> 利用<b>coalesce</b>函數依優先次數呈現貨運公司的運費。</li>
</ol>[註] :<a href="http://www.techonthenet.com/oracle/functions/coalesce.php">coalesce</a> 為<a href="http://www.techonthenet.com/oracle/functions/nvl.php"> nvl</a> 的加強版。<br />
nvl( string1, string2 ) =>判斷 string1 如果為 null 則呈現 string2。nvl 無法應付兩個以上null的判斷。<br />
coalesce(string1, string2, string3) =>判斷 string1 為null則呈現 string2,若 string2 也是null則呈現 string3。<br />
吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com0tag:blogger.com,1999:blog-1529398903068091023.post-79895889916960230702010-01-04T22:46:00.000+08:002010-01-04T22:46:17.849+08:00[Oracle]PL/SQL Developer Tool提到Oracle的相關開發工具,就非得提到我每天都一定會使用的<a href="http://www.allroundautomations.com/bodyplsqldev.html">PL/SQL Developer</a>,用他來做什麼呢??<br />
<br />
當然不外乎就是寫SQL Statement,會用到就是<a href="http://www.allroundautomations.com/plssql.html">SQL Window</a> ,同一個SQL Window裡面可以下多個Statement,只要用分號隔開,SQL Window下方的結果集就會依照Statement順序分頁籤。<br />
<br />
<a name='more'></a><br />
<br />
<div style="color: red;"><b>SQL Window:</b><span style="color: black;">(File -> New -> SQL Window)</span><br />
</div><div class="separator" style="clear: both; text-align: center;"><a href="http://www.allroundautomations.com/images/plssql.gif" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="390" src="http://www.allroundautomations.com/images/plssql.gif" width="640" /></a><br />
</div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
撰寫PL/SQL更是好用的工具,這時候用到就是<a href="http://www.allroundautomations.com/plseditor.html">Program Window</a>,我常用來撰寫Procedure,Function與Package,撰寫PL/SQL的會有Code Assistant,例如在cursor後<span style="font-size: large;"><b><span style="background-color: #ffd966; color: red;"> . </span></b></span>就會帶出相關的欄位,大大的提升寫程式的速度與正確性。<br />
<br />
<b style="color: red;">Program Window:</b> <span style="color: black;">(File -> New -> Program Window)</span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.allroundautomations.com/images/plseditor.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="416" src="http://www.allroundautomations.com/images/plseditor.png" width="640" /></a><br />
</div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
寫完Program總要試跑看看,這時候就一定要介紹<a href="http://www.allroundautomations.com/plsdebugger.html">Test Window</a>了,執行Test Window前先在Program Window設定中斷點,然後就可以一步一步的Debug了,當然可以設定查看變數,又有另一個頁籤可以查看dbms_output.put_line的結果,Debug利用這個工具可謂是相當完整了。<br />
<br />
<b><span style="color: red;">Test Window:</span></b><span style="color: black;">(File -> New -> Test Window)</span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.allroundautomations.com/images/plsdebugger.gif" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="426" src="http://www.allroundautomations.com/images/plsdebugger.gif" width="640" /></a><br />
</div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
另外有一個一定要介紹的就是<a href="http://www.allroundautomations.com/plsbeautifier.html">PL/SQL Beautifier</a>,只要SQL或是PL/SQL寫的正確,寫的再醜的格式都一定會幫你排的漂漂亮亮的,如果排的格式不是你預期的,還使用 PL/SQL Beautifier Option去設定自己喜愛的格式。<br />
<br />
<b style="color: red;">PL/SQL Beautifier</b><b><span style="color: red;">:</span></b>(Edit -> PL/SQL Beautifier)<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.allroundautomations.com/images/plsbeautifier.gif" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="498" src="http://www.allroundautomations.com/images/plsbeautifier.gif" width="640" /></a><br />
</div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
以上圖片皆引用於<a href="http://www.allroundautomations.com/">http://www.allroundautomations.com</a>,其他項目請參閱<a href="http://www.allroundautomations.com/bodyplsqldev.html">http://www.allroundautomations.com/bodyplsqldev.html</a>。<br />
<br />
接下來介紹我喜歡的Layout,如果畫面內一塊一塊的功能視窗不小心按掉了,可以進入Tools中的第三個Section勾勾看囉~ Layout調整完就可以使用(Window -> Save Layout)將畫面配置儲存。<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWrY8HDD79cA8z2nqoQpLA9dx8AJqYohiC1mAsmhsZxZdqa8j91ozR8qIJQ6H-b82XO_be1rdvBFsO398C4K-_yLAuXr7S1tBF_muOCJijoON-xBWunjvzAeE36YkzOCidAPsB34lWLoYD/s1600-h/plsql.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWrY8HDD79cA8z2nqoQpLA9dx8AJqYohiC1mAsmhsZxZdqa8j91ozR8qIJQ6H-b82XO_be1rdvBFsO398C4K-_yLAuXr7S1tBF_muOCJijoON-xBWunjvzAeE36YkzOCidAPsB34lWLoYD/s640/plsql.png" /></a><br />
</div>吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com2tag:blogger.com,1999:blog-1529398903068091023.post-6429256781484718692009-12-23T21:33:00.006+08:002010-05-12T07:48:53.633+08:00[Oracle]Mutil Row To One Row應用(一)我有一張Log Table,會紀錄著procedure開始執行與結束的時間,該Log Table的紀錄方式為procedure Begin時寫入一筆資料,procedure End時寫入一筆資料,所以每次procedure執行只要是成功的狀態,就會有雙雙成對的Begin/End的資料,我期許可以算出每次procedure執行所需要的時間,於是就有把每兩筆Log Data變成一筆後去做時間運算的需求。<br />
<br />
<a name='more'></a><pre class="brush: sql" name="code">select * from log_table t
</pre><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjU1KnB4_ypDHeqQDBUTqqEI1AMabPzQEQRbQfumzlucb6zHelcfjFv1kGFvVytrqJmfGxBmL1QSIrSvjh48VMtvipUq-jA40cJy9OZg7rikOeW7JGHiR70PXQz5Q_qFtiDnCMGqMO7WDXQ/s1600-h/log_table.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjU1KnB4_ypDHeqQDBUTqqEI1AMabPzQEQRbQfumzlucb6zHelcfjFv1kGFvVytrqJmfGxBmL1QSIrSvjh48VMtvipUq-jA40cJy9OZg7rikOeW7JGHiR70PXQz5Q_qFtiDnCMGqMO7WDXQ/s320/log_table.png" /></a><br />
</div><br />
<div class="separator" style="clear: both; text-align: center;"><br />
</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjA7QXieH_8XRznjZ-TO9CuSknaSg4aquB4nR9voUusrbraoMnrqhp7zJ7lqHxN5OuWO_YO0kBdyVqMPbrlGvnrt7dWUGOZaBKKk0Pm3FU_0-AC1-QhNYyY-HxXLF4X5kwEiBoeYbKlkK2W/s1600-h/log_table2.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjA7QXieH_8XRznjZ-TO9CuSknaSg4aquB4nR9voUusrbraoMnrqhp7zJ7lqHxN5OuWO_YO0kBdyVqMPbrlGvnrt7dWUGOZaBKKk0Pm3FU_0-AC1-QhNYyY-HxXLF4X5kwEiBoeYbKlkK2W/s320/log_table2.png" /></a><br />
</div><br />
<br />
<br />
<br />
<br />
<br />
<br />
呈現上圖結果,利用兩個欄位相減去運算出每次Loader所執行花費的時間。<br />
<br />
以上這個題目拋出請同事們一起動動腦,加上我個人的解法共募集了三種解法。<br />
<br />
<div style="color: red;"><b>解法一:</b><br />
</div><pre class="brush: sql" name="code">select a.execute_time begin_time,
b.execute_time end_time,
round((b.execute_time - a.execute_time) * 24 * 60, 2) cost_time
from (select rownum begin_rn, execute_time
from (select *
from log_table t
where t.procedure_name = 'Loader1'
and t.code = 'Begin'
order by t.execute_time)) a,
(select rownum end_rn, execute_time
from (select *
from log_table t
where t.procedure_name = 'Loader1'
and t.code = 'End'
order by t.execute_time)) b
where a.begin_rn = b.end_rn
</pre>上列解法一運用兩份sub query的join去做資料結合,<br />
將Begin的sub query與End的sub query 利用rownum來join。 <br />
<br />
不過解法一對log_table做了兩次資料處理,在大量資料的狀況下,想必效能應該會稍差。<br />
<br />
<div style="color: red;"><b>解法二:</b><br />
</div><pre class="brush: sql" name="code">select max(decode(code, 'Begin', execute_time)) begin_date,
max(decode(code, 'End', execute_time)) end_date,
round((max(decode(code, 'End', execute_time)) -
max(decode(code, 'Begin', execute_time))) * 24 * 60,
2) cost_time
from (select rn,
mod(rn, 2) rn_1,
ceil(rn / 2) rn_2,
b.code,
b.execute_time
from (select rownum rn, a.code, a.execute_time
from (select *
from log_table t
where t.procedure_name = 'Loader1'
order by t.execute_time) a) b)
group by rn_2
</pre>上列解法二運用rownum的數學運算後,再執行group by去做資料結合,<br />
先利用rownum將每一筆資料依照時間排序編列流水號,<br />
mod為取餘數,目前在分組運算中並沒利用到,<br />
ceil為無條件進入,解法二中就是將每一筆資料除以2後取無條件進入,這樣就將每兩筆變成兩兩一組,有同樣的編號(rn_2),<br />
當我們已經順利的將資料分成兩兩一組後,再利用group by rn_2,搭配decode與群組函數max就可以轉秩資料(row to column)。<br />
<br />
<div style="color: red;"><b>解法三:</b><br />
</div><pre class="brush: sql" name="code">select b.begin_time,
b.end_time,
round((b.end_time - b.begin_time) * 24 * 60, 2) cost_time
from (select a.*, rownum rn
from (select t.execute_time begin_time,
lead(execute_time, 1) over(ORDER BY execute_time) AS end_time
from log_table t
where t.procedure_name = 'Loader1'
order by t.execute_time) a) b
where mod(rn, 2) = 1
</pre>解法三主要是應用oracle特有的function lead去做資料結合,<br />
lead主要是把每一筆資料的下一筆資料的某的欄位抓上去擺在隔壁當鄰居欄位,<br />
所以在a的sub query中Begin的execute_time就會有一個鄰居欄位為End 的execute_time<br />
當然End 的execute_time也會出現一個鄰居欄位為Begin的execute_time,<br />
所以我們就應用解法二裡面的mod餘數,只去取Begin的那一筆來計算,Begin那筆的rownum 取2的餘數都是1,End那筆的rownum可被2整除,所以餘數都是0。<br />
<br />
解法二、三針對來源Table:log_table都只做一次資料處理,所以可想而知的效能應該會比較好才是,相關那個解法的效能較佳,數據會說話,等我的後續報導啦~~吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com1tag:blogger.com,1999:blog-1529398903068091023.post-7608579788222914312009-12-07T23:02:00.009+08:002010-05-12T07:47:10.531+08:00[Oracle]無中生有的月曆本日課題是無中生有的產出一個月份天數的筆數,並且在今天的日期標示Today的字串於第二個欄位。<br />
<br />
第一種解法為利用Oracle內建的all_object的sys table去做無中生有的筆數生成。<br />
第二種解法是同事爬出文來的,用於沒有all_object的select的權限時,利用connect by level去生成所需筆數 。<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWZPkHNXouIKXj7FgR_it4YqGoD_KXgBfhFr6a93vIux6wmJHZACvmUegab-W_wrO3wWkTwONmXgvlL1x79cdrg9bTcbNJAKtgaUwhE7QI4mG50cbn6s4zAXqLwDQ5ere_pbX-CfcTCfOV/s1600-h/calendar.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWZPkHNXouIKXj7FgR_it4YqGoD_KXgBfhFr6a93vIux6wmJHZACvmUegab-W_wrO3wWkTwONmXgvlL1x79cdrg9bTcbNJAKtgaUwhE7QI4mG50cbn6s4zAXqLwDQ5ere_pbX-CfcTCfOV/s320/calendar.png" /></a><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<a name='more'></a><br />
<br />
<div style="color: red;"><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<b>解法一:</b><br />
</div><pre class="brush: sql" name="code">select trunc(sysdate, 'MM') + rn - 1 Calendar,
decode(trunc(sysdate, 'MM') + rn - 1, trunc(sysdate, 'DD'), 'Today') high_Today
from (select rownum rn
from all_objects t
where rownum <=
trunc(LAST_DAY(sysdate), 'DD') - trunc(sysdate, 'MM') + 1)
</pre><div style="color: red;"><b>解法二:</b><br />
</div><pre class="brush: sql" name="code">select trunc(sysdate, 'MM') + rn - 1 Calendar,
decode(trunc(sysdate, 'MM') + rn - 1, trunc(sysdate, 'DD'), 'Today') high_Today
from (select rownum rn
FROM dual
CONNECT BY LEVEL <=
trunc(LAST_DAY(sysdate), 'DD') - trunc(sysdate, 'MM') + 1)
</pre><br />
<b>connect by</b> 為階層相關的函數,詳細Functon使用可看<a href="http://www.psoug.org/reference/connectby.html">Link</a>吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com0tag:blogger.com,1999:blog-1529398903068091023.post-19808876716977934982009-11-18T00:02:00.004+08:002010-05-12T07:46:50.306+08:00[Oracle]Not 的妙用最近遇到了一個需求,讓我驚覺Not 的妙用~~<br />
<br />
欄位一等於A,且欄位二等於B,不列入計算<br />
欄位一等於A,且欄位二等於C,不列入計算<br />
<br />
這個看似簡單的條件,可讓我小小思考了一下,才寫出自己滿意的寫法。 <br />
<br />
<a name='more'></a> 以employees table為範例,假設條件為呈現所有 employees的資料,但濾除以下兩個條件:<br />
<br />
job_id = SA_REP 且 manager_id = 145<br />
<br />
job_id = SA_REP 且 manager_id = 146<br />
<br />
<b style="color: red;">解法一: </b><br />
<pre class="brush: sql" name="code">select *
from employees t
where not (t.job_id = 'SA_REP' and t.manager_id in (145, 146));
</pre><br />
<br />
<b><span style="color: red;">解法二:</span></b><br />
<pre class="brush: sql" name="code">select *
from employees t
where not exists (select 'X'
from employees
where t.job_id = 'SA_REP'
and t.manager_id in (145, 146));
</pre><br />
<b><span style="color: red;">解法三:</span></b><br />
<pre class="brush: sql" name="code">select *
from employees t
where (t.job_id, t.manager_id) not in (select 'SA_REP', 145 from dual)
and (t.job_id, t.manager_id) not in (select 'SA_REP', 146 from dual);
</pre><br />
<b style="color: red;">解法四:</b><br />
<pre class="brush: sql" name="code">select *
from employees t
where (t.job_id, t.manager_id) not in
(select 'SA_REP', 145
from dual
union
select 'SA_REP', 146 from dual)
</pre>吳皮尼http://www.blogger.com/profile/00001893416779601316noreply@blogger.com0