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|
|7||CREATE PROCEDURE [dbo].[sp_Search]|
|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.
|4||Public Class Search|
|5||‘ This Region houses all the variables we’re declaring globally.|
|7||‘ Connection String to be used throughout the application.|
|8||Public ConnectionString As String = “Data Source=.\SQLEXPRESS; “ & _|
|9||“Initial Catalog=MyBlog; “ & _|
|12||‘ Connection object.|
|13||Public conn As New SqlConnection(ConnectionString)|
|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)|
‘ Command and connection to use when processing the search.
|21||Dim cmd As New SqlCommand(“[dbo].[sp_Search]”, conn)|
|22||cmd.CommandType = CommandType.StoredProcedure|
|24||‘ Parameters to use. This will be your search criteria.|
|27||‘ Attempt to open the connection and execute the Stored Procedure.|
|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)|
|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|
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):
|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|
|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|
|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”)|
|16||‘ Set the schema (structure definition) of the table and fill the DataAdapter|
|17||SearchDataAdapter.FillSchema(SearchDataSet, SchemaType.Source, “Articles”)|
|19||‘ Set the DataSource for the grid to use our newly created dataset|
|20||SearchGrid.DataSource = SearchDataSet.Tables(“Articles”)|
|22||‘ Execute the function that runs our Stored Procedure.|
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”)|
|8||‘ Set the schema (structure definition) of the table and fill the DataAdapter|
|9||SearchDataAdapter.FillSchema(SearchDataSet, SchemaType.Source, “Articles”)|
|12||‘ Set the DataSource for the grid to use our newly created dataset|
|13||SearchGrid.DataSource = SearchDataSet.Tables(“Articles”)|
|15||‘ Execute the function that runs our Stored Procedure.|
|19||Private Sub btnSearch_Click(ByVal Sender As Object, ByVal e As System.EventArgs) Handles Me.btnSearch.Click|
|22||‘ Do something else here (if needed)|
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.