#眉標= SQL 2000
#副標= SQL 2000進階應用
#大標=文件檔案全文檢索功能的實現
#作者=文／李明儒
-----box-----
程式1 上傳檔案之後端接應
private void Submit1_ServerClick(object sender,
System.EventArgs e)
{
HttpPostedFile file = uploadFile.PostedFile;
int fileLen = file.ContentLength;
byte[] data = new byte[fileLen];
file.InputStream.Read(data, 0, fileLen);
string fileExt=
System.IO.Path.GetExtension(file.FileName);
SqlConnection dbConn =
new SqlConnection(
Application["SqlCnnString"].ToString());
dbConn.Open();
SqlCommand cmdUploadDoc =
new SqlCommand("proc_UploadFile",dbConn);
cmdUploadDoc.CommandType =
CommandType.StoredProcedure;
cmdUploadDoc.Parameters.Add
("@Title",SqlDbType.NVarChar,64);
cmdUploadDoc.Parameters.Add
("@Description",SqlDbType.NText);
cmdUploadDoc.Parameters.Add
("@Doc",SqlDbType.Image);
cmdUploadDoc.Parameters.Add
("@DocType",SqlDbType.Char,3);
cmdUploadDoc.Parameters[0].Value =
txtTitle.Text;
cmdUploadDoc.Parameters[1].Value =
txtDescription.Text;
cmdUploadDoc.Parameters[2].Value =
data;
cmdUploadDoc.Parameters[3].Value =
fileExt.Substring(1).ToLower();
int r=cmdUploadDoc.ExecuteNonQuery();
dbConn.Close();
Response.Write("上傳完成!");
Response.End();
}
-----end-----
-----box-----
程式2 檔案查詢介面
//使用Like查詢條件，針對Title與Description
//兩個欄位進行查詢
private void Button1_Click(object sender,
System.EventArgs e)
{
SqlConnection cn=
new SqlConnection(
Application["SqlCnnString"].ToString());
cn.Open();
string likeCriteria=
"'%"+txtKeyword.Text.Replace("'","''")
+"%'";
string sql=
"select docId,title,description "+
"from doclibrary where title like "+
likeCriteria+" or description like "+
likeCriteria;
SqlCommand cmd=
new SqlCommand(sql,cn);
SqlDataReader dr=
cmd.ExecuteReader(
CommandBehavior.CloseConnection);
DataGrid1.DataSource=dr;
DataGrid1.DataBind();
}
//使用Contains查詢條件，對所有欄位做全文檢索
private void Button2_Click(object sender,
System.EventArgs e)
{
SqlConnection cn=
new SqlConnection(
Application["SqlCnnString"].ToString());
cn.Open();
string sql=
"select docId,title,description "+
"from doclibrary where contains(*,"+
"'"+txtKeyword.Text.Replace("'","''")
+"')";
SqlCommand cmd=
new SqlCommand(sql,cn);
SqlDataReader dr=
cmd.ExecuteReader(
CommandBehavior.CloseConnection);
DataGrid1.DataSource=dr;
DataGrid1.DataBind();
}
-----box-----
程式3 下載檔案的程式範例
private void Page_Load(object sender, System.EventArgs e)
{
SqlConnection cn=
new SqlConnection( Application["SqlCnnString"].ToString());
cn.Open();
int docId=int.Parse(Request["id"]);
string sql="select content,fileType "+
"from doclibrary where docId="+
docId.ToString();
SqlCommand cmd=
new SqlCommand(sql,cn);
SqlDataReader dr=
cmd.ExecuteReader(
CommandBehavior.CloseConnection);
if (dr.Read())
{
changeContentType(
dr["fileType"].ToString());
byte[] data=(byte[]) dr["content"];
Response.OutputStream.Write(data,0,
data.Length);
}
else
{
Response.Write("找不到文件!");
}
dr.Close();
cn.Close();
Response.End();
}
private void changeContentType(string fileExt)
{
switch (fileExt)
{
case "doc":
Response.ContentType=
"application/msword";
break;
case "xls":
Response.ContentType=
"application/vnd.ms-excel";
break;
case "ppt":
Response.ContentType=
"application/vnd.ms-powerpoint";
break;
case "pdf":
Response.ContentType=
"application/pdf";
break;
default:
Response.ContentType=
"application/octet-stream";
break;
}
}
-----end-----