Search Members

In the sample, you will play with searching members by certain criteria. You will learn to interact with the backend database through stored procedures with parameters. Do as follows:

1) Run “SQL Query Analyzer” and connect to www.development.ccs.neu.edu via SQL Server authentication. Please type in your user name and MS SQL password. Copy the script in stored proc and paste it into the query window you just opened. Run the script to create the required stored procedure.

1) Copy the script in aspx to a file named SampleSearchMembers.aspx, and copy the script in code to a file named SampleSearchMembers.aspx.cs.

2) Modify the connection string in the file SampleSearchMembers.aspx.cs, so that initial catalog=your-database-name, user id=your-username and password=your-mssql-password.

3) Create a subdirectory 3rdApp under \\Development\net\home\your-username\www, and upload the two files SampleSearchMembers.aspx and SampleSearchMembers.aspx.cs to \\Development\net\home\your-username\www\3rdApp.

4) Open IE browser, try http://www.development.ccs.neu.edu/home/your-username/3rdApp/SampleSearchMembers.aspx, and you will see all members are listed. You can select a specific state and search again. The textbox for zip is disabled right now.

Script Explanation

SearchMembers.sql

1) The stored procedure searchMembers accepts one parameter, which specifies the match pattern of the state name. The users can either specify “%” which means all states or a specific state. The symbol “%” is used following the syntax of Microsoft T-SQL Like component.

SampleSearchMembers.aspx

1) The ListBox is used to provide all state names of U.S.A. Different from the ListBox in the first sample, the list items are specified statically here. While in first sample, the list items are from the backend database.

2) The text box is not used in the sample right now.

3) Labels “state like” and “and zip like” are simply HTML elements (<DIV …>). They are not processed/compiled by the web server.

SamplesSearchMembers.aspx.cs

1) Page_Init() is called when the controls in the page are created. It is called before Page_Load() is called. The event handler for button click is specified within Page_Init().

2) The select command associated with sqlDataAdapter1 has a type of System.Data.CommandType.StoredProcedure. sqlSelectCommand1.Parameters.Add(…) is used to specify the parameter name and type required by the stored procedure.

3) Every time when the “Search” button is clicked, Button1_Click() is called. The value of the parameter required by the stored procedure is specified. sqlDataAdapter1 then populate the data set via sqlDataAdapter1.Fill(), which implicitly connects to the backend database and calls the stored procedure to obtain the search result. Since the underlying data set may have changed, Repeater1 has to rebind to the data set again. Button1_Click() is called after Page_Load().

4) When Page_Load() is called, if this page is not posted back, the repeater is populated with all members. TextBox1 is disabled by TextBox1.Enabled = false for now. When it is a postback, it must correspond to a ‘Click’. Then it is unnecessary to do rebind, because this rebinding will be done in Button1_Click().

5) Execution order: Page_Init() à Page_Load() à Button1_Clicked().

Exercise

Please extend the search criteria by allowing specifying the match pattern for zip, in addition to for state. The two conditions are “AND” together. You are required to use stored procedure with parameters to access the backend database. The visual text box for inputting the match pattern is already there and you need to enable it. The pattern string follows Microsoft T-SQL Like syntax.

%	Any string of zero or more characters.
_ (underscore)	Any single character.
[ ]	Any single character within the specified range ([a-f]) or set ([abcdef]).
[^]	Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

This exercise is optional. If you have any question, please contact me.