Using Windows Desktop Search in ASP.NET VB Application

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.

Working With a Database in C#

I do not use C# much. Here is some code I found while browsing the Internet that helps to connect to a database:

using System.Data;
using System.Data.SqlClient;
string cs = @"Data Source=.\SQLEXPRESS;" +
string cs = @"Initial Catalog=NamesDB;" +
string cs = @"Integrated Security=True;";
using (SqlConnection con = new SqlConnection(cs)) {
con.Open();
string sql = "INSERT INTO Names(Name) VALUES(@Name)";
// insert a record
SqlCommand cmd1 = new SqlCommand(sql, con);
cmd1.Parameters.Add("@Name", SqlDbType.NVarChar, 100);
cmd1.Parameters["@Name"].Value = "Bob";
cmd1.ExecuteNonQuery();
// insert a second record
cmd1.Parameters["@Name"].Value = "David";
cmd1.ExecuteNonQuery();
// read records
sql = "SELECT * FROM Names";
SqlCommand cmd2 = new SqlCommand(sql, con);
using (SqlDataReader r = cmd2.ExecuteReader()) {
int iName = r.GetOrdinal("Name");
while (r.Read()) {
Console.WriteLine(
r.IsDBNull(iName)?"Null":r.GetString(iName)
);
}
}
// read a single value
sql = "SELECT TOP 1 Name FROM Names";
SqlCommand cmd3 = new SqlCommand(sql, con);
Console.WriteLine(cmd3.ExecuteScalar());
}

Good luck and Happy Coding!