#眉標= 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-----