Dynamic Search using Stored procedure

Hi

It is best practise to separate Data access layer from Business logic layer. It allows   easy source code maintainability, reusability and improves application performance. There are lot more advantages in doing so.

In order to implement a search engine where a ASP.NET form consists of various control to take user input, when you want to process your data access in code behind or using a separate class as data access layer it is mandatory to pass number of parameters and more number of lines of source code. Note that it is also same when you want to read user input,  process data access in code behind and bind retrieved data to data controls such as GridView or DetailsView etc.

Stored Procedure allows you to maintain your data access code in database side, providing security and improving performance with very few lines. It reduces number of lines of source code required to achieve the same in traditional approach.

This article explains how to write a stored procedure to process user input,return a Data Table and bind that Data Table to GridView control.

Presentation Layer (aspx)

<table cellpadding="0" cellspacing="0" width="70%">
<colgroup>
    <col width="15%" class="TableCol" />
    <col width="85%" />
</colgroup>
<tr>
    <td>
        <asp:Label ID="lblFirstName" runat="server" 
Text="First name" />
    </td>
    <td>
        <asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
    </td>
</tr>
<tr>
    <td>
<asp:Label ID="lblLastName" runat="server" Text="Last name" />
    </td>
    <td>
<asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
    </td>
</tr>
<tr>
    <td>
<asp:Label ID="lblEmail" runat="server" Text="Email" />
 </td>
 <td>
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<asp:Button ID="btnSearch" runat="server" Text="Search" 
OnClick="btnSearch_Click" ValidationGroup="btnSearch" />
</tr>
</table>

Business logic layer (code behind page)

/// <summary>
/// Execute the SP by passing search criteria
///using parameters
/// </summary>
/// <returns>DataTable with search results</returns>
private DataTable Search()
{

//DataTable to hold search resutls
DataTable SearchResultsTable = new DataTable();
//Read connectionString from web.config
SqlConnection conn = new SqlConnection(ConnectionString);

try
   {
//Create SQL command object by passing 
//connection and stored procedure name
SqlCommand cmd = new SqlCommand("spSearch", conn);
//command type to Stored procedure
cmd.CommandType = CommandType.StoredProcedure;

//pass search criteria using parameters
cmd.Parameters.AddWithValue("@firstName",txtFirstName.Text);
cmd.Parameters.AddWithValue("@lastName" , txtLastName.Text);
cmd.Parameters.AddWithValue("@email " , txtEmail.Text);

//create sql adapter by passing command object
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//fill the search results table
adapter.Fill(SearchResultsTable);
}

catch (Exception ex)
        {
Response.Write(ex.ToString();
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
        
        //Return search results DataTable
        return SearchResultsTable ;
    }

}

Search button click event

protected void btnSearch_Click(object sender, EventArgs e)
{
//Bind search results to GridView control
GridView1.DataSource = Search();
GridView1.DataBind();
}

Stored Procedure

Stored procedure that reads parameterised input from code behind, executes the SQL and returns search results.

Create Procedure spSearch
( 
@firstName nvarchar(50) = null,
@lastName nvarchar(50) = null,
@email nvarchar(50) = null
) 
AS 
BEGIN
//Set NULL when variable is NULL or lenght is zero
If @firstName is not null and Len(@firstName )=0 Set @firstName = null
If @lastName is not null and Len(@lastName )=0 Set @lastName = null
If @email is not null and Len(@email )=0 Set @email = null

Select 

*
From Students
Where 
( @firstName is null or firstName Like @firstName )
and ( @lastName is null or lastName Like @lastName )
and ( @email is null or email Like @email )
END 

--Note that you can replace 'Like' keyword above to
-- IN to implement other than exact match as you wish

Thats it you are done!!

As the above demo uses few variables to prove the concept, in real time stored procedure can handle more parameters and makes it quite easier to implement search functionality.

References:

http://msdn.microsoft.com/en-us/library/k10148y1.aspx

http://msdn.microsoft.com/en-us/library/k10148y1.aspx