Reference no: EM13909609

Database table: MosaicVisits

I have a database table named "MosaicVisits" attached. I would like to create search page to retrieve and display the data from the database table either search by the LastName or between a date range. For example, I can select the option search by LastName and enter last name into the textbox and click on Submit button, records associated to that LastName will display.

Or you can select to search by a date range. For example, the start date is 1/14/2013 and end date is 2/11/2013. Once you click on the Submit button, the following records will display.

VisitID	Tech_ID	LastName	PurposeStudy	PurposeSocial	PurposeGroupProjects	PurposeOther	Ethnicity	VisitDate
19	11053394	Ford	0	1	0		White	1/14/2013 9:44
17	12339966	Anderson	0	1	0		AfricanAmerican	1/14/2013 9:39
58	12339966	Anderson	1	1	0			1/16/2013 9:00
89	12339966	Anderson	1	1	0			1/18/2013 9:03
67	12339966	Anderson	1	0	0			1/17/2013 8:45
78	288982	Johnson	1	0	0		Asian	1/17/2013 11:50
28	713754	Vang	0	1	0		Asian	1/14/2013 12:25
25	234567	Vang	1	1	0		Asian	1/14/2013 10:56
118	234567	Vang	1	1	0		Asian	1/23/2013 10:53
190	10996760	Vang	1	0	0		Asian	2/11/2013 11:18

You can only perform one search option at a time. All leading zero should also display.

Below are what I need:

1. Create the script to create the MS SQL table

2. Create the script to create a store procedure

3. Create c# script that connect to the store procedure named MosaicVisitsDB.CS.

Below is a sample of the c# format to connect to the store procedure.

using System;
usingSystem.Collections.Generic;
usingSystem.Configuration;
usingSystem.Data;
usingSystem.Data.SqlClient;
usingSystem.Linq;
usingSystem.Web;

///<summary>
/// Summary description for StdntLookupDB
///</summary>
publicenumQueryType
{
ByLastName,
ByTechID
}

publicclassStudentDB
{
publicSqlDataReaderGetStudents(QueryTypequeryType, stringqueryText)
{
// Create Instance of Connection (establishing the SQL Server connection)
varmyConnection = newSqlConnection(ConfigurationManager.ConnectionStrings["ARDevConn"].ConnectionString);

//Create SqlCommandObj (usp_GetStudentInfoByLastName will be the name of the store procedure)
varmyCommand = newSqlCommand("usp_getstudentdata", myConnection);

// Mark the Command as a Store Procedure
myCommand.CommandType = CommandType.StoredProcedure;

//Set up params
varparamFilterBy = newSqlParameter("@FilterBy", SqlDbType.Int, 8);
paramFilterBy.Value = (int)queryType;
myCommand.Parameters.Add(paramFilterBy);

varparamFilterText = newSqlParameter("@FilterText", SqlDbType.NVarChar, 50);
paramFilterText.Value = queryText;
myCommand.Parameters.Add(paramFilterText);

// Open the connection
myConnection.Open();
//Execute the Command
SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

//Return the datareader result
return result;
}
}

4. Create the search web page by either the LastName or between a date range in asp.net 2013.

5. Create c# handler or code behind to perform the function, which will connect the store procedure to the search web page form.

Attachment:- Data.rar