I was searching around the Internet for something or someone to help me with seaching files in a given directory. No, not that easy, it had to be recursive. That is where I ran into problems. As I was looking, I came across something that I wouldn’t even have thought of, why not use windows desktop search?! I was like what? There is no way. Then I got to thinking, it’s Microsoft. Why not. So I dove right in. I am all up for learning something new. Even if it is something I was not even looking for to begin with, sort of. Well, I got it working and I thought I would share with you what I did and post some code for you to look at. I can even use multiple keywords to search on. I do use drop downs vice free for text field though. I already know what the files are named if you have been following the blog, you know what I am refering to. With that said, lets get into some code.
First here is the code for the datagrid. I chose to use a datagrid because of the ease of creating the columns. It was simple to create a hyperlink field. See below.
<asp:DataGrid runat=”server” ID=”datagrid1″ Font-Name=”Verdana”
AutoGenerateColumns=”False”
AlternatingItemStyle-BackColor=”#eeeeee”
HeaderStyle BackColor=”Navy”
HeaderStyle-ForeColor=”White”
HeaderStyle-Font-Size=”15pt”
HeaderStyle-Font-Bold=”True”>
<AlternatingItemStyle BackColor=”#EEEEEE”>
</AlternatingItemStyle>
<Columns>
<asp:HyperLinkColumn
DataNavigateUrlField=”Path”
DataTextField=”Filename”
HeaderText=”File Name” />
</Columns>
<HeaderStyle BackColor=”Navy”
Font-Bold=”True”
Font-Size=”15pt”
ForeColor=”White”>
</HeaderStyle>
</asp:DataGrid>
On this page I also have drop downs and a search button. That is what is used to perform the search.
In the code behind in the Page_Load function(I really wish MVC would be finished so I could start using that, but that’s another story) I added the following:
If Page.IsPostBack Then
Dim keyword1 As String = dropdown1.Text
Dim keyword2 As String = dropdown2.Text
Dim keyword3 As String = dropdown3.Text
Dim keyword4 As String = dropdown4.Text
Dim strCatalog As String
‘ Catalog Name
strCatalog = “catalogNameHere”
Dim strQuery As String
strQuery = “select create, filename, path from scope() “
strQuery = strQuery & ” WHERE FILENAME LIKE ‘%” & keyword1 & “%’”
strQuery = strQuery & “AND FILENAME LIKE ‘%” & keyword2 & “%’”
strQuery = strQuery & “AND FILENAME LIKE ‘%” & keyword3 & “%’”
strQuery = strQuery & “AND FILENAME LIKE ‘%” & keyword4 & “%’”
strQuery = strQuery & ” AND Path NOT LIKE ‘%vti%’ ORDER BY create DESC”
Dim connString As String = “Provider=MSIDXS.1;Integrated Security .=”;Data Source=’”
& strCatalog & “‘”
Dim cn As New
System.Data.OleDb.OleDbConnection(connString)
Dim cmd As New
System.Data.OleDb.OleDbDataAdapter(strQuery, cn)
Dim testDataSet As New Data.DataSet()
cmd.Fill(testDataSet)
Dim srvrnm As String = Server.MachineName
Dim dt As DataTable = testDataSet.Tables(0)
Dim rownum As Integer = 0
For Each row As DataRow In dt.Rows
dt.Rows(rownum)(“path”) = dt.Rows(rownum)(“path”). _
ToString.Replace(“c:\inetpub\dirname\”, “\\” & srvrnm & “\cgweb\”)
rownum = rownum + 1
Next
Dim source As Data.DataView = testDataSet.Tables(0).DefaultView
datagrid1.DataSource = dt
datagrid1.DataBind()
End If
Now step by step through the code.
First we create variables to hold the values of the dropdowns to use later in the query.
Dim keyword1 As String = dropdown1.Text
Dim keyword2 As String = dropdown2.Text
Dim keyword3 As String = dropdown3.Text
Dim keyword4 As String = dropdown4.Text
We then create a string variable to hold the catalog name (which will be covered later).
‘ Catalog Name
strCatalog = “catalogNameHere”
Now we build our query using dynamic data. Like I said previosly, I used drop down because the file names are known. Here is the query string being built:
Dim strQuery As String
strQuery = “select create, filename, path from scope() “
strQuery = strQuery & ” WHERE FILENAME LIKE ‘%” & keyword1 & “%’”
strQuery = strQuery & “AND FILENAME LIKE ‘%” & keyword2 & “%’”
strQuery = strQuery & “AND FILENAME LIKE ‘%” & keyword3 & “%’”
strQuery = strQuery & “AND FILENAME LIKE ‘%” & keyword4 & “%’”
strQuery = strQuery & ” AND Path NOT LIKE ‘%vti%’ ORDER BY create DESC”
We then have to create our connection string to use the catalog and then fill a dataset with the data returned:
Dim connString As String = “Provider=MSIDXS.1;Integrated Security .=”;Data Source=’” & strCatalog & “‘”\
Dim cn As New
System.Data.OleDb.OleDbConnection(connString)
Dim cmd As New
System.Data.OleDb.OleDbDataAdapter(strQuery, cn)
Dim testDataSet As New Data.DataSet()
cmd.Fill(testDataSet)
I needed to replace some of the text before it was displayed so I set the dataset to a datatable and looped through the rows and changed what I needed. I use the varibale rownum so I can look at each row in the loop. See below:
Dim srvrnm As String = Server.MachineName
Dim dt As DataTable = testDataSet.Tables(0)
Dim rownum As Integer = 0
For Each row As DataRow In dt.Rows
dt.Rows(rownum)(“path”) = dt.Rows(rownum)(“path”).ToString.Replace _
(“c:\inetpub\dirname\”, “\\” & srvrnm & “\newdirname\”)
rownum = rownum + 1
Next
Last but not least I databind the datatable to the datagrid on the display page.
datagrid1.DataSource = dt
datagrid1.DataBind()
That’s it. Not too bad. I was happy with myself. I do have to add in the handling of the ‘no records found’ still.
As always, if there are any questions or suggestions, they are welcome. Thank you.
P.S. I will work on my coloring scheme. Having issues at the moment.
