Google-like site search in Visual Basic

For a long time, I wondered how to organise a search feature on my site. I could never figure it out… I knew that the search needed something to search (like a database, a web page, or something), but I had no idea about how to conduct the search.

Now I’ve started working again, I’ve learnt a lot of things in the last short month. One of these things that I’ve learnt is how to make an app (web/windows forms/wpf) search for records in a database.
This’ll work very well if your website uses a SQL database as a DataSource for content.

I’ll walk you through creating a Stored Procedure in your database, and having your vb code execute that sp and return the results ^_^

So, we need a Stored Procedure to run, lets write it real quick:
You’ll need either Visual Studio (express editions will do), or SQL Server Management Studio.
I’m using Management Studio here, but there should be little difference to VS’s interface.

Connect to your server, and expand your database from the list on the left. You should find something that says “Programmability” or “Stored Procedures” in the list under your database’s name. If it’s “Programmability”, expand it, and right-click on “Stored Procedures” and select “New Stored Procedure…”

This will create a new query. In the query window, add the following code:

1	SET ANSI_NULLS ON
2	SET QUOTED_IDENTIFIER ON
3	GO
4
5	USE MyBlog
6
7	CREATE PROCEDURE [dbo].[sp_Search]
8	(
9	@SearchText varchar(50)
10	)
11	AS
12	SELECT *
13	FROM Articles
14	WHERE ArticleTitle LIKE @SearchText

This selects all the rows in the table where the ArticleTitle column’s value is similar to the search text.
Now, we need to hook this into our site/app – this is the fun bit.

We need to write code that executes our Stored Procedure. It’s tidier in your code to place the Function that we’re writing below in a class file separate to your form.
I’m naming my class file Search.vb.

1	Imports System.Data
2	Imports System.Data.SqlClient
3
4	Public Class Search
5	‘ This Region houses all the variables we’re declaring globally.
6	#Region “Global Variables”
7	‘ Connection String to be used throughout the application.
8	Public ConnectionString As String = “Data Source=.\SQLEXPRESS; “ & _
9	“Initial Catalog=MyBlog; “ & _
10	“Integrated Security=True;”
11
12	‘ Connection object.
13	Public conn As New SqlConnection(ConnectionString)
14	#End Region
15
16	‘ Execute the Stored Procedure based on input from the user in
17	‘ the form of a Function Parameter.
18	Public Function SearchData(ByVal SearchText As String)
19
20	‘ Command and connection to use when processing the search.
21	Dim cmd As New SqlCommand(“[dbo].[sp_Search]”, conn)
22	cmd.CommandType = CommandType.StoredProcedure
23
24	‘ Parameters to use. This will be your search criteria.
25	cmd.Parameters.AddWithValue(“@SearchText”, SearchText)
26	Try
27	‘ Attempt to open the connection and execute the Stored Procedure.
28	conn.Open()
29	cmd.ExecuteNonQuery()
30	Catch ex As Exception
31	‘ If there’s an error with processing the above, alert the user.
32	‘ If your application of this is web-based, then replace the following line
33	‘ of code with this:
34	‘ <Label>.Text = “An error was encountered: ” & vbCrLf & ex.ToString()
35	MsgBox(“An error was encountered: ” & vbCrLf & ex.ToString)
36	Finally
37	‘ Close the connection and clear the memory blocks that the connection
38	‘ Close the connection and clear the memory blocks that the connection
39	‘ and command objects were using
40	conn.Close()
41	conn.Dispose()
42	cmd.Dispose()
43	End Try
44	End Function
45
46	End Class

We’re not done yet. Save your class file and then go back to your form/page’s code behind.
We need to make sure we have the controls on our form to use for the search. Typically, this is just a textbox and a button. I’ve named mine txtSearch and btnSearch respectively.
Add the following to your code behind (obviously replacing names with your own):

1	Imports System.Data
2	Imports System.Data.SqlClient
3
4	‘ Import the class file that has our stored procedure in it (Search.vb)
5	‘ Dim <variable name> As New <external class name>
6	Dim Search As New Search
7
8	Private Sub btnSearch_Click(ByVal Sender As Object, ByVal e As System.EventArgs) Handles Me.btnSearch.Click
9	‘ We want to force conversion on our textbox to make sure that the right data type is being passed to the Stored Procedure
10	Dim SearchText As String = txtSearch.Text.ToString
11
12	‘ Create the ADO Data Objects for the DataGrid to use.
13	Dim SearchDataSet As New DataSet(“ArticlesDataSet”)
14	Dim SearchDataAdapter As New SqlDataAdapter(“SELECT * FROM Articles”)
15
16	‘ Set the schema (structure definition) of the table and fill the DataAdapter
17	SearchDataAdapter.FillSchema(SearchDataSet, SchemaType.Source, “Articles”)
18	SearchDataAdapter.Fill(SearchDataSet, “Articles”)
19	‘ Set the DataSource for the grid to use our newly created dataset
20	SearchGrid.DataSource = SearchDataSet.Tables(“Articles”)
21
22	‘ Execute the function that runs our Stored Procedure.
23	Search.SearchData(SearchText)
24	End Sub

A good idea might be to put the code that creates and populates the DataSet in a separate procedure and just have your Event Handler execute that procedure. This will cause the DataGridView to update every time the button is clicked, meaning that the user can change search criteria and get new results all the time.
There’s nothing different that goes on with the operation of the app when doing this, it just makes the event handler a little neater:

1	Private Sub CreateADO()
2	‘ We want to force conversion on our textbox to make sure that the right data type is being passed to the Stored Procedure
3	Dim SearchText As String = txtSearch.Text.ToString
4	‘ Create the ADO Data Objects for the DataGrid to use.
5	Dim SearchDataSet As New DataSet(“ArticlesDataSet”)
6	Dim SearchDataAdapter As New SqlDataAdapter(“SELECT * FROM Articles”)
7
8	‘ Set the schema (structure definition) of the table and fill the DataAdapter
9	SearchDataAdapter.FillSchema(SearchDataSet, SchemaType.Source, “Articles”)
10	SearchDataAdapter.Fill(SearchDataSet, “Articles”)
11
12	‘ Set the DataSource for the grid to use our newly created dataset
13	SearchGrid.DataSource = SearchDataSet.Tables(“Articles”)
14
15	‘ Execute the function that runs our Stored Procedure.
16	Search.SearchData(SearchText)
17	End Sub
18
19	Private Sub btnSearch_Click(ByVal Sender As Object, ByVal e As System.EventArgs) Handles Me.btnSearch.Click
20	CreateADO()
21
22	‘ Do something else here (if needed)
23	End Sub

OK, so, there’s a database search. I did it simply for the purposes of this post (placing search results in a DataGridView, but you can customize the output of your search as you like.

An app like this will be easier to create using LINQ Queries, but that’s for another post. I strongly recommend getting your hands on (at least) the Express Edition of VB2008 and playing around with it. It simplifies so much from VS2005, and, based on the speed at which MS is releasing new stuff, it may be a better idea to forget VS2005 altogether in favor of VS2008.

That’s it for me. For now. Hope you enjoyed reading.

-L