[分享]PHP版網頁原始碼處理函式

序言

這是我依VB.Net的網頁原始碼處理函式翻過來的PHP版程式,可以取得網頁後,再針對特定標簽進行切割處理。

函式原始碼

我將以下原始碼存在【myClass】目錄的【HTMLParser.php】檔
<?php
Class HTMLParser{
function getHTML($url,$method="GET",$param=NULL,$noCashe=False){
//$param = array("name" => 'tim',"content" => 'test');

if($noCashe){
$cashe="Cache-Control: no-cache\r\n";
}else{
$cashe="";
}
if($method=="GET" and $param!=NULL){
$data="";
foreach ($param as $k => $v) {
if($data!="") $data.="&";
$data.= "$k=".htmlentities($v);
}
$url.="?".$data;
}

if($method=="POST" and $param!=NULL){
$data = http_build_query($param);
$contentLength="Content-length:".strlen($data)."\r\n";
$opts = array(
'http'=>array(
'method'=>$method,
'header'=>"User-Agent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)\r\n"
. "Accept-language: zh-tw\r\n"
. "Content-Typ: application/x-www-form-urlencoded\r\n"
. $cashe . $contentLength,
'content' => $data
)
);
}else{
$opts = array(
'http'=>array(
'method'=>$method,
'header'=>"User-Agent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)\r\n"
. "Accept-language: zh-tw\r\n"
. "Content-Typ: application/x-www-form-urlencoded\r\n"
. $cashe
)
);

}
$context = stream_context_create($opts);
// Open the file using the HTTP headers set above
return file_get_contents($url, false, $context);
//return file_get_contents($url);
}
//取得網頁的Body區段
function getHTMLBody($url,$postValue=NULL,$noCashe= False){
$html=NULL;
$htmlBody=NULL;
if($postValue!=NULL){
$html = $this->getHTML($url,"POST",$postValue,$noCashe);
}else{
$html = $this->getHTML($url,"GET", $postValue,$noCashe);
}
If(html!=NULL) $this->getHTMLTagContain($html, "body", $htmlBody);

return $htmlBody;
}
//取得HTML中第一個符合的標籤內容的指標,並將取得的標籤內容寫入參數中
function getHTMLTagContain($html,$tag,&$contain=NULL,&$indexEnd= -1){
$tag = strtolower($tag);
$contain = NULL;
$indexBegin= -1;
$indexEnd = -1;
$indexbBegin= -1;
If($html !=NULL){
$indexBegin = strpos(strtolower($html),"<" . $tag);
if (gettype($indexBegin)!="integer") $indexBegin=-1;
If($indexBegin > -1){
$indexbBegin = strpos($html,">",$indexBegin);
if (gettype($indexbBegin)!="integer"){
$indexbBegin=-1;
}else{
$indexbBegin+=1;
}
$findTag=False;
$indexEnd=$indexbBegin;
$lastStart=$indexbBegin;
$stopLimit2 = 9999;
do{
$indexEnd=strpos(strtolower($html),"</" . $tag,$indexEnd);
if(gettype($indexEnd)!="integer") $indexEnd=-1;
If ($indexEnd > -1){
if(gettype(strpos(substr($html,$lastStart, $indexEnd - $lastStart),"<" . $tag))=="integer"){
$lastStart=$indexEnd;
$indexEnd=$indexEnd+strlen("</" . $tag);
$findTag=True;
}else{
$findTag=False;
}
}else{
$findTag=False;
}
$stopLimit2--;
}while($findTag AND $stopLimit2>0);
If ($indexEnd > -1){
$contain = substr($html,$indexbBegin, $indexEnd - $indexbBegin);

$indexEnd= strpos($html,">",$indexEnd);
if(gettype($indexEnd)!="integer"){
$indexEnd=-1;
}else{
$indexEnd+=1;
}
}
}
}
//echo $contain;
Return $indexbBegin;
}
//取得HTML中第一個符合的標籤屬性的指標,並將取得的標籤內容與該屬性內容寫入參數中
function getHTMLTagAtt($html,$tag,$attName,&$att=NULL,&$contain=NULL,&$indexEnd= -1){
$tag = strtolower($tag);
$attName = strtolower($attName);
$contain = NULL;
$att = NULL;
$indexEnd = -1;
$indexBegin= -1;
$indexaBegin= -1;
$indexbEnd = -1;
If($html !=NULL){
$indexBegin = 0;
$stopLimit1 = 9999;
do{
$indexbEnd = -1;
$indexBegin = strpos(strtolower($html),"<" . $tag,$indexBegin);
if (gettype($indexBegin)!="integer") $indexBegin=-1;
If($indexBegin > -1){
$indexbEnd= strpos($html,">",$indexBegin);
if (gettype($indexbEnd)!="integer") $indexbEnd=-1;
If($indexbEnd > -1){
$indexaBegin = strpos(strtolower(str_replace("\"", "'",substr($html,0, $indexbEnd))),$attName . "='",$indexBegin);
if(gettype($indexaBegin)!="integer") $indexaBegin=-1;
If($indexaBegin > -1){
$sign = substr($html,$indexaBegin + strlen($attName . "="), 1);
$indexaBegin+=strlen($attName . "='");
$indexaEnd= strpos(substr($html,0, $indexbEnd),$sign,$indexaBegin);
if(gettype($indexaEnd)!="integer") $indexaEnd=-1;
If($indexaEnd > -1){
$att = substr($html,$indexaBegin, $indexaEnd - $indexaBegin);
}
}else{
$indexBegin = $indexbEnd + 1;
continue;
}
$indexbEnd+=1;
}

$findTag=False;
$indexEnd=$indexbEnd;
$lastStart=$indexbEnd;
$stopLimit2 = 9999;
do{
$indexEnd=strpos(strtolower($html),"</" . $tag,$indexEnd);
if(gettype($indexEnd)!="integer") $indexEnd=-1;
If ($indexEnd > -1){
if(gettype(strpos(substr($html,$lastStart, $indexEnd - $lastStart),"<" . $tag))=="integer"){
$lastStart=$indexEnd;
$indexEnd=$indexEnd+strlen("</" . $tag);
$findTag=True;
}else{
$findTag=False;
}
}else{
$findTag=False;
}
$stopLimit2--;
}while($findTag==True AND $stopLimit2>0);
If ($indexEnd > -1){
$contain = substr($html,$indexbEnd, $indexEnd - $indexbEnd);
$indexEnd= strpos($html,">",$indexEnd);
if(gettype($indexEnd)!="integer"){
$indexEnd=-1;
}else{
$indexEnd+=1;
}
}
}else{
break;
}
$stopLimit1--;
}while($att == NULL AND $stopLimit1>0);
}
Return $indexbEnd;
}
}
?>

使用範例

<?php
    include 'myClass/HTMLParser.php';
    $htmlParser=new HTMLParser();
    $url = "http://allen080.blogspot.com/2009/05/vbnet20.html";
    //取得整個網頁的HTML
    $htmlAll = $htmlParser->getHTML($url);
    //取得網頁Body的部份
    $htmlBody = $htmlParser->getHTMLBody($url);
    If ($htmlBody!=NULL){
        $indexEnd = strpos($htmlBody,"<span class='item-control blog-admin'>");
        if(gettype($indexEnd)!="integer")    $indexEnd=-1;
        $valueAttribute = NULL;
        $valueContain = NULL;
        If($indexEnd > 0){
            $htmlBody = substr($htmlBody,$indexEnd);
            //取得a標籤1的內容
            $htmlParser->getHTMLTagContain($htmlBody, "span", $valueContain, $indexEnd);
            echo "span標籤的內容: " . $valueContain ."<BR>\n";
            //$htmlBody = substr($htmlBody,$indexEnd);
            //取得a標籤2的內容與href的屬性
            $htmlParser->getHTMLTagAtt($htmlBody, "a", "onclick", $valueAttribute, $valueContain, $indexEnd);
          echo "a標籤的內容與onclick的屬性: " . $valueAttribute ."\t" . ",內容:" . $valueContain;
        }
    }    
?>


執行結果

span標籤的內容:
<a class='quickedit' href='http://www.blogger.com/rearrange?blogID=2698062899592178296&widgetType=HTML&widgetId=HTML1&action=editWidget' onclick='return _WidgetManager._PopupConfig(document.getElementById("HTML1"));' target='configHTML1' title='編輯'>
<img alt='' height='18' src='http://img1.blogblog.com/img/icon18_wrench_allbkg.png' width='18'/>
</a>
<BR>
a標籤的內容與onclick的屬性: return _WidgetManager._PopupConfig(document.getElementById("HTML1"));    ,內容:
<img alt='' height='18' src='http://img1.blogblog.com/img/icon18_wrench_allbkg.png' width='18'/>


總結

這是我為了練習php時寫出的東西~
我個人是覺得滿好用的~
不過有部份邏輯滿複雜的~所以我也不敢保證完全正確或是適用於各種網頁~
如果有人用有發現更好的改進方式請跟我說。


[PHP]字串處理函式

序言

字串處理是常用的功能,大多數語言對這部份也有許多大同小異的函式,以下列出比較常用或我覺得特別需注意的函式。

尋找字串

strpos($string,$find[,$start]):尋找第一個在字串中出現的字詞,傳回0開始的數字位置。【$string】是被找的字串,【$find】是尋找的目標字詞,【$start】是代表尋找的起始位置。→官方文
這個函式有一個極需注意的部份,就在於它的回傳值,找到的話會回傳位置,也就是個0開始的數字;但沒找到的話就機車了,以VB為例的IndexOf函式是類似這個函式的功能,如果沒找到會回傳【-1】,但在PHP不同版本中,會回傳的值居然是False,那就算了,聽說這是4.0b3版以後的PHP是這樣,更早之前的PHP甚至聽說回傳的連False都不是。於是就有人想出了判斷型態的方法,具體實現方式如下

<?php
    //尋找第一個在字串中出現的字詞
    function str_indexof($string,$find,$start=0){
        $index=strpos($string,$find,$start);
        if(gettype($index)!="integer")    $index=-1;
        return $index;
    }
   
    $string='abcdefghijk';
    $find='l';
    echo str_indexof($string,$find) . "\n";    //-1
    $find='a';
    echo str_indexof($string,$find,3) . "\n";    //-1
    $find='i';
    echo str_indexof($string,$find,3) . "\n";    //8
?>


子字串

substr($string[[,$start],$length]):取得子字串,傳回子字串內容。【$string】是來源字串,【$start】是代表尋找的起始位置,【$length】是子字串長度。→官方文
這個函式需注意的部份在於它對中文字(UTF-8)會以每個字3字元的方式計算,所以切割時如果是中文字就要以3的倍數遞增。有另外的函式可對含中文的函式做計算找子字串,但如果是用上面談到的strpos函式來找指標,那就可以不用擔心這個問題。具體實現方式如下

<?php
    $string = '這是substr的測試';
    echo substr($string,0,13) . "\n";    //這是substr�
    $string = '這是mb_substr的測試';
    echo mb_substr($string,0,13,'UTF-8') . "\n";    //這是mb_substr的測
?>


字串長度

strlen($string):傳回字串長度。【$string】是來源字串。→官方文件
這個函式與上面一樣對中文字(UTF-8)會以每個字3字元的方式計算。

轉換大小寫

strtoupper($string):回傳轉換為英文大寫字串。strtolower($string):取得轉換為英文小寫字串。【$string】是來源字串。→官方文件
這個函式與上面一樣對中文字(UTF-8)會以每個字3字元的方式計算。

文字切割

split($pattern,$string[,$limit]):將字串依分割條件切割為陣列,回傳結果陣列。【$pattern】是分割條件,可為字串或是正規表達式。【$string】是來源字串。【$limit】最多切出幾個元素。
這個函式較直得注意的有兩點,一個是參數順序,居然不是來源字串...;第二個是分割條件【$pattern】可用正規表達式,有關於正規表達式可參考相關連結。→官方文件

文字驗證

ereg($pattern,$string[,&$arrResult]):依正規表達式驗證字串格式(比對字串時有大小寫之分),回傳True符合或False不符合。→官方文件
eregi($pattern,$string[,&$arrResult]):功能同上,但比對字串時沒有大小寫之分。
【$pattern】是驗證條件正規表達式。【$string】是來源字串。【$arrResult】符合的文字。
這個函式較直得注意的一樣有兩點,一個是參數順序;第二個是驗證條件【$pattern】為正規表達式,如【^[a-z0-9-]{3,8}$】如果要完整比對字串的話,要記得開頭用與結尾分別用【^】與【$】把條件框住,【[a-z0-9-]】代表小寫a~z與0~9及減號都是可用的字元,【{3,8}】代表上數字元可重複的次數為3~8次,可參考相關連結
相關連結
  • PHP官方開發手冊:英文開發手冊。
  • W3School:簡體中文的網站開發程式語言文件,雖然有些不夠完整,不過很適合當工具書或初學參考(除了PHP,其他還有許多網頁語言)。

[PHP]陣列、檔案讀寫

陣列

PHP的Array也是我覺得很特別的東西,感覺有點像VB中HashTable的機制。
從程式中可注意到,要取得Array中的元素個數可用【count($dataArr)】。
新增元素的方法也很多元,也能動態新增。→官方文件

<?php
    echo "\nArray 1<br>\n";
    $dataArr = array('abc','test');
    for($i=0;$i<count($dataArr);$i++){
        echo $i."\t".$dataArr[$i] . "<br>\n";
    }
    echo "\nArray 2<br>\n";
    $dataArr = array();
    $dataArr[]='abc';
    $dataArr[]='test';
    for($i=0;$i<count($dataArr);$i++){
        echo $i."\t".$dataArr[$i] . "<br>\n";
    }
    echo "\nArray 3<br>\n";
    $dataArr = array();
    $dataArr['name']='abc';
    $dataArr['content']='test';
    for($i=0;$i<count($dataArr);$i++){
        echo $i."\t".$dataArr[$i] . "<br>\n";
    }
    foreach($dataArr as $value){
        echo $value . "<br>\n";
    }
    foreach($dataArr as $key => $value){
        echo $key."\t".$value . "<br>\n";
    }
    echo "\nArray 4<br>\n";
    $dataArr = array('name' => 'abc','content' => 'test');
    foreach($dataArr as $key => $value){
        echo $key."\t".$value . "<br>\n";
    }
?>

結果:

Array 1<br>
0    abc<br>
1    test<br>

Array 2<br>
0    abc<br>
1    test<br>

Array 3<br>

0    <br>
1    <br>
abc<br>
test<br>
name    abc<br>
content    test<br>

Array 4<br>
name    abc<br>
content    test<br>

檔案存取

檔案存取是我常會用的功能,以下列出我的寫法與常用函式:官方文件

<?php
    //存文字檔,如果檔案不存在會建立檔案,如果存在會覆蓋檔案
    function saveText($filename,$fText){
        try{
            $fh = fopen($filename, "w");
            fwrite($fh, $fText);
            fclose($fh);
        }catch(Exception $e){
            echo 'Error addText: ' .$e->getMessage();
        }
    }
    //增加文字到文字檔最後面,如果檔案不存在會建立檔案
    function addText($filename,$fText){
        try{
            $fh = fopen($filename, "a");
            fwrite($fh, $fText);
            fclose($fh);
        }catch(Exception $e){
            echo 'Error addText: ' .$e->getMessage();
        }
    }
    //讀取文字檔內容
    function loadText($filename){
        $fText=NULL;
        try{
            if(file_exists($filename))    $fText=file_get_contents($filename);           
        }catch(Exception $e){
            echo 'Error loadText: ' .$e->getMessage();
        }
        return $fText;
    }
    //刪除檔案
    function deleteFile($filename){
        return unlink($filename);
    }
    //建立資料夾
    function createDir($dirname){
        return mkdir($dirname);
    }
    //刪除資料夾
    function deleteDir($dirname){
        return rmdir($dirname);
    }
?>

[初學]PHP入門心得

序言

雖然寫了許久的程式,但一直沒花時間去練習PHP。
最近花了點時間試試PHP,增長了些見聞,後續幾篇會一一記錄下我開發時注意到的事。

開發環境

  • Windows 2003
  • TWAMPs(Tiny Windows Apache MySQL PHP Portable Web Server)V1.3.3中文版
    我開發的時候雖然已經有V1.3.5版,但執行的時候不知道為什麼我的整個作業系統會出現很多衝突的現象,很多程式都受影響,所以就往回推用舊的版本。V1.3.4版無法下載,就改用V1.3.3。以下是這個免安裝的環境提供的版本:
    • Apache:2.2.11
    • PHP:5.2.10
    • MySQL:5.1.36-community
    • phpMyAdmin:3.2.0.1
    • Drupal:6.13
  • Notepad++ 5.4.4
    我沒有特地找PHP的編輯器,所以編輯起來比較費工,不過Notepad還是有用顏色標亮特定的語法,所以還是有點用的。

環境設定

  • 啟動前可以看一下根目錄的【README.txt】來找到對應的Port或其他值的更改地點,但有些部分【README.txt】沒有寫的很正確,所以可能還是要靠經驗與人工找找實際的目錄,如:
    •  Apache PORT更改,README.txt說【edit httpd.conf in TWAMP\Apache\conf】,但實際路徑會在【 TWAMPd\app\conf】下的httpd.conf,找到並更改【Listen 80】
    • php.ini的位置,README.txt說【Edit php.ini in TWAMPd\PHP】,但實際路徑會在【TWAMPd\app\bin】
    • 【TWAMPd\htdocs】目錄下會是所有網頁文件所在的地方。

Apache 目錄存取限制設定

【.htaccess】 是一個文字檔,可以自己建立後,放在要限制/保護的目錄下,Apache就會依裡面的設定內容對使用者的存取進行限制或保護。
例如我對目錄要完全不給人進來存取檔案,就在文字檔內寫入以下內容

order allow,deny
deny from all

基本上這只是最簡單的功能,但很常用。如果需要更多設定,其實這個檔也可以設定的很複雜,網路上還有人專門做出設定工具:htaccessEditor,在此就不詳述了。

PHP開發兩三事

  • 基本語法

    <?php echo "123"; ?>

    【<?php ... ?>】指的是裡面的code【...】是php,以副檔名為*.php的檔案放在【TWAMPd\htdocs】目錄下,就可以被編譯執行。【echo】會將後面帶的文字顯示在頁面這段程式所在的位置上。→官方文件-基本語法官方文件-echo
  • include

    <?php
    include 'myClass/HTMLParser.php';
    ?>

    include 的寫法,會將include的PHP程式如同嵌入在宣告的位置一樣,我想使用方法應該就如同C一樣,欲使用前必先宣告。→官方文件
  • 變數、單引號與雙引號

    <?php
        $value="123";
        echo "1: " . $value . "\r\n<br>";
        echo "2: " . "abc\t$valuedef\r\n<br>";
        echo "3: " . 'abc$valuedef\r\n<br>';
        echo "4: " . "abc\t$value def\r\n<br>";
        echo "5: " . 'abc$value def \r\n<br>';
        echo "6: " . 'abc' . $value . 'def\r\n<br>';
        echo "7: " . '"' . $value . "'\r\n<br>";
    ?>

    結果:

    1: 123
    <br>2: abc    
    <br>3: abc$valuedef\r\n<br>4: abc    123 def
    <br>5: abc$value def \r\n<br>6: abc123def\r\n<br>7: "123'
    <br>

    上面的程式中可以看到幾個現象:變數的前面用【$】來標示它是個變數,而且不用先行宣告,隨使用就會產生。以下逐行來看
    1. 字串的組合是用點【.】來標示兩字串要串聯起來。【雙引號】中的特殊文元【\】標示了後面帶了特殊字元【\r\n】,這都是換行符號,如果用VB來形容就是【vbCrLf】,這個特殊字元可以單獨使用,如【\n】。(PS:按「Enter」鍵實際產生兩個字元換行和回到開頭(ASCII編碼10:\n和13:\r))
    2. 【雙引號】中的變數被編譯成顯示,但因為英文字連再一起,所以編譯後是顯示【valuedef】變數的值。因為根本沒有宣告【valuedef】變數的值,所以最後產生的時候是空值,就什麼都沒有。
    3. 【單引號】直接輸出文字內容,所以特殊字元或變數都無效。
    4. 【雙引號】中的變數被編譯成顯示,此字串內容中,還有用到特殊字元【\t】來出現tab的字元。
    5. 【單引號】直接輸出文字內容,所以特殊字元或變數都無效。
    6. 【單引號】直接輸出文字內容,所以特殊字元或變數都無效。
    7. 雙引號內容中可以出現單引號,反過來說也行,如果雙引號內容中要出現雙引號,需要加上特殊字元\。
    PHP 會解譯【雙引號】內容中的變數,而【單引號】則視為純字串出,PHP 不會再處理單引號內的內容。所以單引號與雙引號在使用時會有時機上的差異。
  • 常數

    <?php
        define('myValue','1234');
        echo myValue;
    ?>

    常數的寫法與變數不同,定義後名稱前面不需要再加上【$】號。→官方文件
  • 脫逸 (Escape)字

    <?php
        //去除get_magic_quotes_gpc on 時 針對特殊字做脫逸 (Escape)的影響
        if (get_magic_quotes_gpc())
        {   
            $HTTP_GET_VARS = array_map('stripslashes', $HTTP_GET_VARS);
            $HTTP_POST_VARS = array_map('stripslashes', $HTTP_POST_VARS);
            $HTTP_COOKIE_VARS = array_map('stripslashes', $HTTP_COOKIE_VARS);
            $_GET = array_map('stripslashes', $_GET);
            $_POST = array_map('stripslashes', $_POST);
            $_COOKIE = array_map('stripslashes', $_COOKIE);
            $_REQUEST = array_map('stripslashes', $_REQUEST);
        }
    ?>

    這個部份是比較與執行環境設定有關的。在PHP.ini中有個設定【get_magic_quotes_gpc on】(官方文件),可以讓使用者輸入的字元中一些特殊的字元加上【\】,如單引號【'】、雙引號【"】、斜線【\】,以防程式處理時會造成意想不到的錯誤。但這樣的設定可能反而會讓我們在處理資料時更加棘手,所以這串程式能判斷當這個設定開啟時,讓這些特殊的字元不經過加上【\】的畜哩,直接取得使用者輸入的內容。
相關連結
  • TWAMP:免安裝的中英文伺服器執行環境。
  • PHP官方開發手冊:英文開發手冊。
  • W3School:簡體中文的網站開發程式語言文件,雖然有些不夠完整,不過很適合當工具書或初學參考(除了PHP,其他還有許多網頁語言)。

[程式]VB.Net2.0網頁原始碼取得函式

序言

我因為學術需求,要取得某些網址的網頁原始碼進行分析,因此寫了以下函式,貼出來與大家分享吧~

*2009/10/29 更新函式,加強切割網頁函式、抓網頁標頭。

開發環境

  • VB.Net 2.0 (VS2005)

專案設定

  • 建立專案後預設的專案屬性下,參考的元件如下圖:

  • 下面的程式中會用到【Web】這個物件,所以我們需要加入【System.Web】這個元件:

    點選加入,在.Net分頁下找到【System.Web】這個元件

網頁原始碼函式類別

Imports System.Net
Imports System.io
Imports System.Reflection

Public Class WebPageGenFunc
    Private Shared rd As New Random(Now.Second)
    Private Shared sec As Integer
    Private Shared lastsec As Integer = -1
    Public Shared Function sleepTime(Optional ByVal minimatime As Integer = 2600, Optional ByVal rangeSecond As Integer = 5) As Integer
        sec = (rd.Next Mod rangeSecond) * 1000 + minimatime
        While sec = lastsec
            sec = (rd.Next Mod rangeSecond) * 1000 + minimatime
        End While
        lastsec = sec
        System.Threading.Thread.Sleep(sec)
        Return sec
    End Function


    Private Shared Sub SetAllowUnsafeHeaderParsing20()
        Dim a As New System.Net.Configuration.SettingsSection
        Dim aNetAssembly As System.Reflection.Assembly = Assembly.GetAssembly(a.GetType)
        Dim aSettingsType As Type = aNetAssembly.GetType("System.Net.Configuration.SettingsSectionInternal")
        Dim args As Object() = Nothing
        Dim anInstance As Object = aSettingsType.InvokeMember("Section", BindingFlags.Static Or BindingFlags.GetProperty Or BindingFlags.NonPublic, Nothing, Nothing, args)
        Dim aUseUnsafeHeaderParsing As FieldInfo = aSettingsType.GetField("useUnsafeHeaderParsing", BindingFlags.NonPublic Or BindingFlags.Instance)
        aUseUnsafeHeaderParsing.SetValue(anInstance, True)
    End Sub


    ''' <summary>
    ''' 使用Get方法取得網頁內容
    ''' </summary>
    ''' <param name="url">網址</param>
    ''' <param name="noCashe">不使用快取</param>
    ''' <returns>網頁的HTML</returns>
    ''' <remarks>使用Get方法取得網頁內容</remarks>
    Public Shared Function getHTMLGet(ByVal url As String, Optional ByVal noCashe As Boolean = False) As String
        getHTMLGet = Nothing
        SetAllowUnsafeHeaderParsing20()
        Dim wRs As HttpWebResponse
        Dim wRq As HttpWebRequest
        ' Create the request using the WebRequestFactory.

        wRq = CType(WebRequest.Create(url), HttpWebRequest)
        With wRq
            .UserAgent = "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)"
            .ContentType = "application/x-www-form-urlencoded"
            .Headers.Add("Accept-Language", "zh-tw")
            .Method = "GET"
            .Timeout = 10000
            If noCashe Then
                Dim policy As New Cache.HttpRequestCachePolicy(Cache.HttpRequestCacheLevel.NoCacheNoStore)
                .CachePolicy = policy
                .Headers.Add("Cache-Control", "no-cache")
            End If
        End With

        Try
            ' Return the response stream.
            wRs = CType(wRq.GetResponse(), HttpWebResponse)
            Dim streamResponse As Stream = wRs.GetResponseStream()
            Dim streamRead As New StreamReader(streamResponse)
            Dim responseString As String = streamRead.ReadToEnd()
            getHTMLGet = responseString
            ' Close Stream object.
            streamResponse.Close()
            streamRead.Close()
            ' Release the HttpWebResponse.
            wRs.Close()
        Catch ex As Exception
            Console.WriteLine(ex.ToString)
        End Try
    End Function

    ''' <summary>
    ''' 使用Post方法取得網頁內容
    ''' </summary>
    ''' <param name="url">網址</param>
    ''' <param name="postdata">傳遞參數,如a=123&b=456</param>
    ''' <param name="noCashe">不使用快取</param>
    ''' <returns>網頁的HTML</returns>
    ''' <remarks>使用Post方法取得網頁內容</remarks>
    Public Shared Function getHTMLPost(ByVal url As String, Optional ByVal postdata As String = Nothing, Optional ByVal noCashe As Boolean = False) As String
        getHTMLPost = Nothing
        SetAllowUnsafeHeaderParsing20()
        Dim wRs As HttpWebResponse
        Dim wRq As HttpWebRequest
        ' Create the request using the WebRequestFactory.
        wRq = CType(WebRequest.Create(url), HttpWebRequest)
        With wRq
            .UserAgent = "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)"
            .Headers.Add("Accept-Language", "zh-tw")
            .Method = "POST"
            .Timeout = 10000
            .KeepAlive = False
            If noCashe Then
                Dim policy As New Cache.HttpRequestCachePolicy(Cache.HttpRequestCacheLevel.NoCacheNoStore)
                .CachePolicy = policy
                .Headers.Add("Cache-Control", "no-cache")
            End If
            If Not postdata Is Nothing Then
                .Timeout = 60000
                Dim encoding As New System.Text.ASCIIEncoding()
                Dim byte1 As Byte() = encoding.GetBytes(postdata)
                .ContentType = "application/x-www-form-urlencoded"
                .ContentLength = byte1.Length
                .GetRequestStream().Write(byte1, 0, byte1.Length)
            End If
        End With
        wRq.GetRequestStream().Close()
        Try
            ' Return the response stream.
            wRs = CType(wRq.GetResponse(), HttpWebResponse)
            Dim streamResponse As Stream = wRs.GetResponseStream()
            Dim streamRead As New StreamReader(streamResponse)
            Dim responseString As String = streamRead.ReadToEnd()
            getHTMLPost = responseString
            ' Close Stream object.
            streamResponse.Close()
            streamRead.Close()
            ' Release the HttpWebResponse.
            wRs.Close()

        Catch ex As Exception
            Console.WriteLine(ex.ToString)
        End Try
    End Function
    ''' <summary>
    ''' 使用WebClient取得網頁內容
    ''' </summary>
    ''' <param name="url">網址</param>
    ''' <param name="postdata">傳遞的參數</param>
    ''' <param name="method">使用的方法,預設為POST</param>
    ''' <param name="noCashe">不使用快取</param>
    ''' <returns>網頁的HTML</returns>
    ''' <remarks>使用WebClient取得網頁內容</remarks>
    Public Shared Function getHTMLWebClient(ByVal url As String, ByRef postdata As Specialized.NameValueCollection, Optional ByVal method As String = "POST", Optional ByVal noCashe As Boolean = False) As String
        getHTMLWebClient = Nothing
        Try
            SetAllowUnsafeHeaderParsing20()
            Dim myWebClient As New WebClient()
            If noCashe Then
                Dim policy As New Cache.HttpRequestCachePolicy(Cache.HttpRequestCacheLevel.NoCacheNoStore)
                myWebClient.CachePolicy = policy
                myWebClient.Headers.Add("Cache-Control", "no-cache")
            Else
                Dim rheaders As WebHeaderCollection = myWebClient.ResponseHeaders
                If Not rheaders Is Nothing Then
                    Dim header As String = rheaders("Set-Cookie")
                    If Not header Is Nothing Then
                        myWebClient.Headers.Add("Cookie", header)
                    End If
                End If
            End If
            myWebClient.Headers.Add("User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)")
            myWebClient.Headers.Add("Content-Type", "application/x-www-form-urlencoded")
            myWebClient.Headers.Add("Accept-Language", "zh-tw")
            If postdata Is Nothing Then postdata = New Specialized.NameValueCollection
            Dim responseArray As Byte() = myWebClient.UploadValues(url, method, postdata)
            Dim encoding As New System.Text.UTF8Encoding
            getHTMLWebClient = encoding.GetString(responseArray)
        Catch ex As Exception
            Console.WriteLine(ex.ToString)
        End Try
    End Function

    ''' <summary>
    ''' 取得網頁的Body區段
    ''' </summary>
    ''' <param name="url">網址</param>
    ''' <param name="postdata">傳遞的參數,若有值會使用getHTMLPost取得</param>
    ''' <param name="postValue">傳遞的參數,若有值會使用getHTMLWebClient取得</param>
    ''' <param name="noCashe">不使用快取</param>
    ''' <returns>網頁的HTML</returns>
    ''' <remarks>取得網頁的Body區段</remarks>
    Public Shared Function getHTMLBody(ByVal url As String, Optional ByVal postdata As String = Nothing, Optional ByVal postValue As Specialized.NameValueCollection = Nothing, Optional ByVal noCashe As Boolean = False, Optional ByVal useWebClient As Boolean = False) As String
        getHTMLBody = Nothing
        Dim html As String
        If Not postValue Is Nothing Then
            html = getHTMLWebClient(url, postValue, , noCashe)
        ElseIf Not postdata Is Nothing Then
            html = getHTMLPost(url, postdata, noCashe)
        Else
            If useWebClient Then
                html = getHTMLWebClient(url, Nothing, "GET", noCashe)
            Else
                html = getHTMLGet(url, noCashe)
            End If

        End If
        If Not html Is Nothing Then
            getHTMLTagContain(html, "body", getHTMLBody)
        End If
    End Function
    ''' <summary>
    ''' 取得HTML中第一個符合的標籤內容的指標,並將取得的標籤內容寫入參數中
    ''' </summary>
    ''' <param name="html">HTML</param>
    ''' <param name="tag">標籤</param>
    ''' <param name="contain">取得的標籤內容</param>
    ''' <param name="indexEnd">此標籤結束於HTML的指標</param>
    ''' <returns>標籤內容的指標</returns>
    ''' <remarks>取得HTML中第一個符合的標籤內容的指標,並將取得的標籤內容寫入參數中</remarks>
    Public Shared Function getHTMLTagContain(ByVal html As String, ByVal tag As String, Optional ByRef contain As String = Nothing, Optional ByRef indexEnd As Integer = -1) As Integer
        tag = tag.ToLower
        contain = Nothing
        indexEnd = -1
        Dim indexBegin As Integer = -1
        Dim indexbBegin As Integer = -1
        If Not html Is Nothing Then
            indexBegin = html.ToLower.IndexOf("<" & tag)
            If indexBegin > -1 Then
                indexbBegin = html.IndexOf(">", indexBegin)
                If indexbBegin > -1 Then
                    indexbBegin += 1
                End If
                Dim findTag As Boolean = False
                indexEnd = indexbBegin
                Dim lastStart As Integer = indexbBegin
                Dim stopLimit2 As Integer = 9999
                Do
                    indexEnd = html.ToLower.IndexOf("</" & tag, indexEnd)
                    If indexEnd > -1 Then
                        If html.Substring(lastStart, indexEnd - lastStart).IndexOf("<" & tag) > -1 Then
                            lastStart = indexEnd
                            indexEnd = indexEnd + ("</" & tag).Length
                            findTag = True
                        Else
                            findTag = False
                        End If
                    Else
                        findTag = False
                    End If
                    stopLimit2 -= 1
                Loop While findTag And stopLimit2 > 0

                If indexEnd > -1 Then
                    contain = html.Substring(indexbBegin, indexEnd - indexbBegin)

                    indexEnd = html.IndexOf(">", indexEnd)
                    If indexEnd > -1 Then
                        indexEnd += 1
                    End If
                End If

            End If
        End If
        Return indexbBegin
    End Function
    ''' <summary>
    ''' 取得HTML中第一個符合的標籤屬性的指標,並將取得的標籤內容與該屬性內容寫入參數中
    ''' </summary>
    ''' <param name="html">HTML</param>
    ''' <param name="tag">標籤</param>
    ''' <param name="attName">屬性名稱</param>
    ''' <param name="att">取得的屬性內容</param>
    ''' <param name="contain">取得的標籤內容</param>
    ''' <param name="indexEnd">此標籤結束於HTML的指標</param>
    ''' <returns></returns>
    ''' <remarks>取得HTML中第一個符合的標籤屬性的指標,並將取得的標籤內容與該屬性內容寫入參數中</remarks>
    Public Shared Function getHTMLTagAtt(ByVal html As String, ByVal tag As String, ByVal attName As String, Optional ByRef att As String = Nothing, Optional ByRef contain As String = Nothing, Optional ByRef indexEnd As Integer = -1) As Integer
        att = Nothing
        contain = Nothing
        tag = tag.ToLower
        attName = attName.ToLower
        indexEnd = -1
        Dim indexBegin As Integer = -1
        Dim indexaBegin As Integer = -1
        Dim indexbEnd As Integer = -1
        Dim findTag As Boolean = False
        Dim sign As String

        If Not html Is Nothing Then
            indexBegin = 0
            Dim stopLimit1 As Integer = 9999
            Do
                indexbEnd = -1
                indexBegin = html.ToLower.IndexOf("<" & tag, indexBegin)
                If indexBegin > -1 Then
                    indexbEnd = html.IndexOf(">", indexBegin)
                    If indexbEnd > -1 Then
                        indexaBegin = html.Substring(0, indexbEnd).Replace("""", "'").ToLower.IndexOf(attName & "='", indexBegin)
                        If indexaBegin > -1 Then
                            sign = html.Substring(indexaBegin + (attName & "=").Length, 1)
                            indexaBegin += (attName & "='").Length
                            Dim indexaEnd As Integer = html.Substring(0, indexbEnd).IndexOf(sign, indexaBegin)
                            If indexaEnd > -1 Then
                                att = html.Substring(indexaBegin, indexaEnd - indexaBegin)
                            End If
                        Else
                            indexBegin = indexbEnd + 1
                            Continue Do
                        End If
                        indexbEnd += 1
                    End If

                    findTag = False
                    indexEnd = indexbEnd
                    Dim lastStart As Integer = indexbEnd

                    Dim stopLimit2 As Integer = 9999
                    Do
                        indexEnd = html.ToLower.IndexOf("</" & tag, indexEnd)
                        If indexEnd > -1 Then
                            If html.Substring(lastStart, indexEnd - lastStart).IndexOf("<" & tag) > -1 Then
                                lastStart = indexEnd
                                indexEnd = indexEnd + ("</" & tag).Length
                                findTag = True
                            Else
                                findTag = False
                            End If
                        Else
                            findTag = False
                        End If
                        stopLimit2 -= 1
                    Loop While findTag And stopLimit2 > 0

                    If indexEnd > -1 Then
                        contain = html.Substring(indexbEnd, indexEnd - indexbEnd)
                        indexEnd = html.IndexOf(">", indexEnd)
                        If indexEnd > -1 Then
                            indexEnd += 1
                        End If
                    End If
                Else
                    Exit Do
                End If
                stopLimit1 -= 1
            Loop While att Is Nothing And stopLimit1 > 0
        End If
        Return indexbEnd
    End Function
    ''' <summary>
    ''' Url參數值編碼
    ''' </summary>
    ''' <param name="value">參數值</param>
    ''' <returns>編碼結果</returns>
    ''' <remarks>Url參數值編碼</remarks>
    Public Shared Function getEncodeStr(ByVal value As String)
        Return Web.HttpUtility.UrlEncode(value)
    End Function
    ''' <summary>
    ''' Url參數值解碼
    ''' </summary>
    ''' <param name="value">參數值</param>
    ''' <returns>解碼結果</returns>
    ''' <remarks>Url參數值解碼</remarks>
    Public Shared Function getDecodeStr(ByVal value As String)
        Return Web.HttpUtility.UrlDecode(value)
    End Function
End Class

使用範例程式

        Dim indexEnd As Integer
        Dim valueAttribute As String
        Dim valueContain As String
        Dim url As String = "http://allen080.blogspot.com/2009/05/vbnet20.html"
        '使用WebRequest的Get方法取得整個網頁的HTML
        'Dim htmlAll As String = WebPageGenFunc.getHTMLGet(url)
        '取得網頁Body的部份
        Dim htmlBody As String = WebPageGenFunc.getHTMLBody(url)
        If Not htmlBody Is Nothing Then
            indexEnd = htmlBody.IndexOf("<span class='item-control blog-admin'>")
            valueAttribute = Nothing
            valueContain = Nothing
            If indexEnd > 0 Then
                htmlBody = htmlBody.Substring(indexEnd)
                '取得a標籤1的內容
                WebPageGenFunc.getHTMLTagContain(htmlBody, "span", valueContain, indexEnd)
                Console.WriteLine("span標籤的內容: " & valueContain)
                'htmlBody = htmlBody.Substring(indexEnd)
                '取得a標籤2的內容與href的屬性
                WebPageGenFunc.getHTMLTagAtt(htmlBody, "a", "onclick", valueAttribute, valueContain, indexEnd)
                Console.WriteLine("a標籤的內容與onclick的屬性: " & valueAttribute & vbTab & ",內容:" & valueContain)
            End If
        End If
        

        '使用WebClient的Post方法取得資料
        url = "http://al080.summerhost.info/invoiceMe/index.php"
        Dim searchStr As String = "123"
        Dim postdata As New Specialized.NameValueCollection
        postdata.Add("ddlYear", "2009")
        postdata.Add("ddlMonth", "7-8")
        postdata.Add("txtInvNO", searchStr)
        postdata.Add("btnMatchInv", "對獎")
        htmlBody = WebPageGenFunc.getHTMLBody(url, , postdata)
        If Not htmlBody Is Nothing Then
            indexEnd = htmlBody.IndexOf("<div class=""DivRight"">")
            If indexEnd > 0 Then
                htmlBody = htmlBody.Substring(indexEnd)
                '取得div標籤的內容
                WebPageGenFunc.getHTMLTagContain(htmlBody, "div", valueContain, indexEnd)
                Console.WriteLine("使用WebClient的Post方法取得資料: " & valueContain)
            End If
        End If
        
        '使用WebRequest的Post方法取得資料
        searchStr = String.Empty
        '在組合參數時要同時編碼
        For i As Integer = 0 To postdata.Count - 1
            searchStr &= "&" & postdata.GetKey(i) & "=" & WebPageGenFunc.getEncodeStr(postdata(i))
        Next
        searchStr = searchStr.Substring(1)
        htmlBody = WebPageGenFunc.getHTMLBody(url, searchStr)
        If Not htmlBody Is Nothing Then
            indexEnd = htmlBody.IndexOf("<div class=""DivRight"">")
            If indexEnd > 0 Then
                htmlBody = htmlBody.Substring(indexEnd)
                '取得div標籤的內容
                WebPageGenFunc.getHTMLTagContain(htmlBody, "div", valueContain, indexEnd)
                Console.WriteLine("使用WebRequest的Post方法取得資料: " & valueContain)
            End If
        End If

執行結果

span標籤的內容:
<a class='quickedit' href='http://www.blogger.com/rearrange?blogID=2698062899592178296&widgetType=HTML&widgetId=HTML1&action=editWidget' onclick='return _WidgetManager._PopupConfig(document.getElementById("HTML1"));' target='configHTML1' title='編輯'>
<img alt='' height='18' src='http://img1.blogblog.com/img/icon18_wrench_allbkg.png' width='18'/>
</a>

a標籤的內容與onclick的屬性: return _WidgetManager._PopupConfig(document.getElementById("HTML1"));    ,內容:
<img alt='' height='18' src='http://img1.blogblog.com/img/icon18_wrench_allbkg.png' width='18'/>

使用WebClient的Post方法取得資料: <span style="font-weight:bold;">對獎結果:</span><br>
                <div class="DivResult">
            無中獎發票...<br>
                </div>
           
使用WebRequest的Post方法取得資料: <span style="font-weight:bold;">對獎結果:</span><br>
                <div class="DivResult">
            無中獎發票...<br>
                </div>

相關連結

[程式]VB.Net2.0資料庫通用存取函式

序言

在VS2005建立VB專案(或Web網站)後,若要進行資料庫存取,我個人比較習慣將連線字串寫在設定檔app.config(或web.config)下。

不過VB專案預設是不會產生這個檔,而且在程式中若要取得連線字串的設定也要對專案的參考進行設定。

以下記錄下我做的設定,並把資料庫存取程式簡化為函式。

開發環境

  • VB.Net 2.0 (VS2005)
  • MSSQL2005 Express

專案設定

  • 建立專案後預設的專案屬性下,參考的元件如下圖:

  • 下面的程式中會用到【ConfigurationManager】這個物件來取得設定檔的連線字串,所以我們需要加入【System.Configuration】這個元件:

    點選加入,在.Net分頁下找到【System.Configuration】這個元件
  • 再來要在專案加入新項目【應用程式組態】,名稱用預設的【app.config】就可以了:

  • 編輯app.config,在【configuration】標籤內加入連線字串的設定如:

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
    ...
    <connectionStrings>
    <add name="MSSQLDB1"
    connectionString="Data Source=localhost;Initial Catalog=MyTestDB;Integrated Security=True"
    providerName="System.Data.SqlClient" />
    <add name="MSSQLDB2"
    connectionString="Data Source=127.0.0.1;Initial Catalog=MyTestDB;User ID=sa;Password=1234"
    providerName="System.Data.SqlClient" />
    </connectionStrings>
    ...

通用存取函式類別


Imports System.Data.SqlClient
Imports System.Configuration
Public Class DBAccessFunc
    ''' <summary>
    ''' 從ConfigurationManager中的ConnectionStrings找出Initial Catalog
    ''' </summary>
    ''' <param name="ConnName">ConnectionStrings名稱(String)</param>
    ''' <returns>回傳資料庫名稱</returns>
    ''' <remarks>從ConfigurationManager中的ConnectionStrings找出Initial Catalog</remarks>
    Public Shared Function getDBName(ByVal ConnName As String) As String
        Dim val As String = ""
        Dim ConnString As String = ConfigurationManager.ConnectionStrings(ConnName).ConnectionString
        Dim i As Integer = ConnString.IndexOf("Initial Catalog=")
        If i > -1 Then
            val = ConnString.Substring(i + "Initial Catalog=".Length)
            i = val.IndexOf(";")
            If i > -1 Then
                val = val.Substring(0, i)
            End If
        End If
        Return val
    End Function

    Public Shared Function getConnString(ByVal ConnName As String) As String
        Return ConfigurationManager.ConnectionStrings(ConnName).ConnectionString
    End Function


    Public Shared Function getConn(ByVal ConnName As String) As SqlConnection
        Return New SqlConnection(getConnString(ConnName))
    End Function
    ''' <summary>
    ''' 查詢資料庫
    ''' </summary>
    ''' <param name="cn">連線</param>
    ''' <param name="sql">查詢內容</param>
    ''' <returns>回傳查詢結果的DataTable</returns>
    ''' <remarks>查詢資料庫並回傳查詢結果的DataTable</remarks>
    Public Shared Function getTable(ByRef cn As SqlConnection, ByRef sql As String, Optional ByVal isColseConn As Boolean = True) As Data.DataTable
        Dim da As New SqlDataAdapter(sql, cn)
        Dim dt As New Data.DataTable("dt_xml")
        Try
            If cn.State = ConnectionState.Closed Then cn.Open()
            da.SelectCommand.CommandTimeout = 36000000
            da.Fill(dt)
        Catch ex As Exception
            If isColseConn And cn.State = ConnectionState.Open Then cn.Close()
            Throw ex
        End Try
        If isColseConn And cn.State = ConnectionState.Open Then cn.Close()
        Return dt
    End Function

    ''' <summary>
    ''' 執行命令
    ''' </summary>
    ''' <param name="cn">連線</param>
    ''' <param name="sql">查詢內容</param>
    ''' <returns>回傳受影響的資料筆數</returns>
    ''' <remarks>執行命令並回傳受影響的資料筆數</remarks>
    Public Shared Function doCmd(ByRef cn As SqlConnection, ByRef sql As String, Optional ByRef param() As SqlParameter = Nothing, Optional ByVal isColseConn As Boolean = True) As Integer
        Dim result As Integer = 0
        Dim cmd As New SqlCommand
        Try
            cmd.Connection = cn
            cmd.CommandText = sql
            If Not param Is Nothing Then
                cmd.Parameters.AddRange(param)
            End If
            cmd.CommandTimeout = 36000000
            If cn.State = ConnectionState.Closed Then cn.Open()
            result = cmd.ExecuteNonQuery()
        Catch ex As Exception
            cmd.Parameters.Clear()
            If isColseConn And cn.State = ConnectionState.Open Then cn.Close()
            Throw ex
        Finally
            cmd.Parameters.Clear()
            If isColseConn And cn.State = ConnectionState.Open Then cn.Close()
        End Try
        Return result
    End Function


    ''' <summary>
    ''' 取得單一資料
    ''' </summary>
    ''' <param name="cn">連線</param>
    ''' <param name="sql">查詢內容</param>
    ''' <returns>回傳受影響的資料筆數</returns>
    ''' <remarks>執行命令並回傳受影響的資料筆數</remarks>
    Public Shared Function doScalar(ByRef cn As SqlConnection, ByRef sql As String, Optional ByRef param() As SqlParameter = Nothing, Optional ByVal isColseConn As Boolean = True) As Object
        Dim result As Object = Nothing
        Dim cmd As New SqlCommand
        Try
            cmd.Connection = cn
            cmd.CommandText = sql
            If Not param Is Nothing Then
                cmd.Parameters.AddRange(param)
            End If
            cmd.CommandTimeout = 36000000
            If cn.State = ConnectionState.Closed Then cn.Open()
            result = cmd.ExecuteScalar()
        Catch ex As Exception
            cmd.Parameters.Clear()
            If isColseConn And cn.State = ConnectionState.Open Then cn.Close()
            Throw ex
        Finally
            cmd.Parameters.Clear()
            If isColseConn And cn.State = ConnectionState.Open Then cn.Close()
        End Try
        Return result
    End Function

    Public Function executeStoredProcedure(ByRef cn As SqlConnection, ByVal procedure As String, Optional ByRef param As SqlParameter() = Nothing, Optional ByRef output As SqlParameter() = Nothing, Optional ByVal isColseConn As Boolean = True) As Integer
        Dim result As Integer = 0
        Dim cmd As New SqlCommand(procedure, cn)
        Try

            cmd.CommandText = procedure
            cmd.CommandTimeout = 36000000
            cmd.CommandType = CommandType.StoredProcedure
            If Not param Is Nothing Then cmd.Parameters.AddRange(param)
            If Not output Is Nothing Then
                For Each o As SqlParameter In output
                    o.Direction = ParameterDirection.Output
                Next
                cmd.Parameters.AddRange(output)
            End If

            '開啟資料庫連線
            If cn.State = ConnectionState.Closed Then cn.Open()
            '設定變數儲存受影響資料列
            result = cmd.ExecuteNonQuery()
        Catch ex As Exception
            cmd.Parameters.Clear()
            If isColseConn And cn.State = ConnectionState.Open Then cn.Close()
            Throw ex
        Finally
            cmd.Parameters.Clear()
            If isColseConn And cn.State = ConnectionState.Open Then cn.Close()
        End Try
        Return result
    End Function
End Class


使用範例程式

Dim connStr1 As String = DBGenFunc.getConnStr("MSSQLDB1")
Dim sourceTable As String() = {"[" & DBGenFunc.getDBName("MSSQLDB1") & "].[dbo].[" & "MyTestTab" & "]", _
"[" & DBGenFunc.getDBName("MSSQLDB1") & "].[dbo].[" & "MyContentTab" & "]"}
Dim param() As SqlClient.SqlParameter = {New SqlClient.SqlParameter("P0", SqlDbType.VarChar)}
Dim sql As String

Dim title As String

Console.WriteLine("doCmdScalar Start:")
sql = "SELECT Title" & vbCrLf & _
" FROM " & sourceTable(0) & "" & vbCrLf & _
" WHERE SN=@P0"
param(0).Value = 1
title = DBGenFunc.doCmdScalar(connStr1, sql, param)
Console.WriteLine("doCmdScalar" & vbTab & title)

Console.WriteLine("getTable Start:")
sql = "SELECT Title" & vbCrLf & _
" FROM " & sourceTable(0) & "" & vbCrLf
Dim dt As DataTable = DBGenFunc.getTable(connStr1, sql)
For Each r As DataRow In dt.Rows
title = r.Item(0)
Console.WriteLine("getTable" & vbTab & title)
Next

Console.WriteLine("getTableToArray Start:")
Dim data As String() = DBGenFunc.dataTableToArray(Of String)(dt, 0)
For Each r As String In data
title = r
Console.WriteLine("getTable" & vbTab & title)
Next

Console.WriteLine("doCmd Start:")
sql = "INSERT INTO " & sourceTable(0) & "" & vbCrLf & _
"VALUES(@P0,@P1)"
param = New SqlClient.SqlParameter() {New SqlClient.SqlParameter("P0", SqlDbType.VarChar), _
New SqlClient.SqlParameter("P1", SqlDbType.VarChar)}
param(0).Value = "TNew"
param(1).Value = "SNew"
title = DBGenFunc.doCmd(connStr1, sql, param)
Console.WriteLine("doCmd" & vbTab & title)

Console.WriteLine("getReader Start:")
sql = "SELECT Title, Subject" & vbCrLf & _
" FROM " & sourceTable(0) & "" & vbCrLf
Dim dr As SqlClient.SqlDataReader = DBGenFunc.getReader(connStr1, sql)
While dr.Read()
title = dr.Item(0)
Console.WriteLine("getReader" & vbTab & title & vbTab & dr.Item(1))
End While
DBGenFunc.closeReader()

執行結果

doCmdScalar Start:
doCmdScalar T1
getTable Start:
getTable T1
getTable T2
getTable T3
getTable TNew
getTableToArray Start:
getTable T1
getTable T2
getTable T3
getTable TNew
doCmd Start:
doCmd 1
getReader Start:
getReader T1 S1
getReader T2 S2
getReader T3 S3
getReader TNew SNew
getReader TNew SNew

[筆記]我的FireFox愛用套件

序言

此篇記下我常用的套件相關連結,方便安裝使用。

清單

  1. FireFox主程式(我都用Portable版不用安裝):
    FireFox Portable by portableapps.com
  2. Foxmarks官網。書籤同步,可至官網直接登入瀏覽你的書籤。
  3. FireGestures:滑鼠手式(控制上一頁下一頁等)。
  4. QuickDrag:用拖曳手勢更容易地在新分頁裡打開連結、搜尋文字和儲存圖片。
  5. 新同文堂官網。中文繁簡體互轉工具。
  6. Tab Mix Plus官網。分頁加強版。
  7. Mozilla Archive Format:MHT (MHTML) 檔案開啟外掛。
  8. Copy Links:方便複製連結。
  9. FacePad:Facebook相簿下載(安裝後於相簿連結點右鍵可以看到功能)。
  10. Greasemonkey官網。客製化網頁Script功能。搭配以下Script。
  11. FlashGot官網。搭配其他下載工具用。
  12. PCmanfx官網。上BBS。
  13. Profile Manager and Synchronizer :多帳號登入。
這裡是關於技術的手札~

也歡迎大家到

倫與貓的足跡



到噗浪來

關心一下我唷!
by 倫
 
Copyright 2009 倫倫3號Beta-Log All rights reserved.
Blogger Templates created by Deluxe Templates
Wordpress Theme by EZwpthemes