SQL Server 2005 Full-Text Search

SQL Server 2005 comes with its own full-text search capabilities, which enables developers to use full-text search functions and predicates, provided that the correct full-text search catalog and indexing exist.

Full-text functions and predicates include:

1. CONTAINS (predicate)
2. FREETEXT (predicate)
3. CONTAINSTABLE (function)
4. FREETEXTTABLE (function)

Here are some of their characteristics and differences:

CONTAINS and FREETEXT both return a TRUE or FALSE value and are specified in the WHERE or HAVING clauses of a SELECT statement.

CONTAINSTABLE and FREETEXTTABLE both return a table of zero, one, or more rows, so they must always be specified in the FROM clause.

CONTAINS and FREETEXT can only be used to specify selection criteria, which Microsoft SQL Server uses to determine the membership of the result set, ie, inside or outside the result set.

CONTAINSTABLE and FREETEXTTABLE are also used to specify selection criteria. The table returned has a column named KEY that contains full-text key values. Each full-text registered table has a column whose values are guaranteed to be unique. The values returned in the KEY column of CONTAINSTABLE or FREETEXTTABLE are the unique values, from the full-text registered table, of the rows that match the selection criteria specified in the full-text search condition.

Here are some example full-text queries(extracted from MSDN):

1. SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5 AND
CONTAINS(Description, ' Aluminum AND spindle');


2. DECLARE @SearchWord nvarchar(30)
SET @SearchWord = N'performance'
SELECT Description
FROM Production.ProductDescription
WHERE FREETEXT(Description, @SearchWord);

3. SELECT KEY_TBL.RANK, FT_TBL.Description
FROM Categories AS FT_TBL
INNER JOIN
FREETEXTTABLE(Categories, Description,
'How can I make my own beers and ales?') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;

4. SELECT K.RANK, CompanyName, ContactName, Address
FROM Customers AS C
INNER JOIN
CONTAINSTABLE(Customers,Address, 'ISABOUT ("des*",
Rue WEIGHT(0.5),
Bouchers WEIGHT(0.9))', 3) AS K
ON C.CustomerID = K.[KEY];


5. SELECT FT_TBL.Description,
FT_TBL.CategoryName,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)'
, 10
) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY];

Note that full-text search is different from LIKE predicate. With the LIKE predicate, you will get exact matches, whereas in full-text search, you can specify certain ambiguity.

Also, for some far eastern characters, such as Chinese, Japanese...etc., SQL Server has some language-specific features to generate more desirable results.

Reference: http://msdn2.microsoft.com/en-us/library/ms142519.aspx

.NET GridView EmptyDataTemplate

在.NET 2.0裡面,GridView的EmptyDataTemplate是用來指定GridView內容為0比資料時的顯示結果,當葉面執行以後,GridView會被render為以下形式:

<div>
<table cellspacing="2" border="0" id="ctl00_ContentPlaceHolder1_gv_Authpat">
......
</table>
</div>

而EmptyDataTemplate則會被render為以下形式:

<tr class="Theader" align="center">
<td colspan="8">

~ EmptyDataTemplate的內容 ~

</td>
</tr>

在EmptyDataTemplate裡面,我們除了可以使用一般的server control之外,也可以加入一般的html語法,因為在EmptyDataTemplate裡面的html語法並不需要通過editor的驗證,所以即使是不合乎定義的html語法,也可以被加進來,例如:

專利件編號</td>
<td style="width:8%">單位</td>
<td style="width:10%">申請國別</td>
<td style="width:25%">專利名稱</td>
<td style="width:10%">成果來源</td>
<td style="width:20%">獲證日及証號</td>
<td style="width:10%">申請狀態

綜合以上的例子,所render出來的html結果如下:

<div>
<table cellspacing="2" border="0" id="ctl00_ContentPlaceHolder1_gv_Authpat">
<tr class="Theader" align="center">
<td colspan="8">
專利件編號</td>
<td style="width:8%">單位</td>
<td style="width:10%">申請國別</td>
<td style="width:25%">專利名稱</td>
<td style="width:10%">成果來源</td>
<td style="width:20%">獲證日及証號</td>
<td style="width:10%">申請狀態
</td>
</tr>
</table>
</div>

.NET 識別

ASP.NET Web 應用程式可以使用以下的識別來存取資源：

1. ASP.NET 處理序識別 (如同 web.config 中的 項目上所設定)。預設值是以使用者名稱「machine」和密碼「AutoGenerate」指定的本機 ASPNET 帳戶。

<identity impersonate="false" />

2. 通過驗證的呼叫者的識別(如果已在 web.config 內啟用模擬)。這個設定會模擬原始呼叫者，由 IIS 進行驗證，如果您未停用 IIS 的匿名存取，這將會是 IUSR_MACHINE。設定方法是在web.config檔裡面指定：

(<authentication mode="Windows" />)
<identity impersonate="true" />

3. 指定的模擬識別 (雖然不建議這樣做) 如下所示。

<identity impersonate="true" username="Bob" password="password">

.NET 匯入Excel

1. 使用OleDbDataAdapter + GridView

string OleConStr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'", Server.MapPath(xlsFullName));

OleDbConnection Conn = new OleDbConnection(OleConStr);

Conn.Open();
DataSet Ds = new DataSet();
OleDbDataAdapter Da = new OleDbDataAdapter("Select * from [Sheet1$]", Conn);
Da.Fill(Ds, "excel_data");
Conn.Close();

gv_test.DataSource = Ds.Tables["excel_data"];
gv_test.DataBind();

OleCn.Close();

2. 使用OleDbDataReader (read row by row)

string OleConStr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'", Server.MapPath(xlsFullName));

OleDbConnection OleCn = new OleDbConnection(OleConStr);
OleDbCommand OleCmd = new OleDbCommand();
OleCmd.Connection = OleCn;
string strSQL = @"SELECT * FROM [Sheet1$]";
OleCmd.CommandText = strSQL;

try
{
OleCn.Open();
OleDbDataReader OleDr = OleCmd.ExecuteReader();
while (OleDr.Read())
{

~~~ CUSTOM CODE ~~~

{
catch (Exception ex)
{
String scriptString = string.Format(@"alert('Excel格式錯誤! FilePath:{0} ; Advance MSG:{1}');", xlsFullName, ex.Message);
ClientScript.RegisterStartupScript(this.GetType(), "s", scriptString, true);
return;
}

OleCn.Close();


註:read Excel檔案時要注意是否有權限,包括檔案的權限,資料夾的權限,以及web.config檔裡面的屬性設定成 false

<identity impersonate="false"/>