Topic: Retrive data from DB using SQL and Display into grid

Question

Hi

I'm working in .net C# 3.5,

I want to search Employee details from DB (ms SQL)  and display to gridview

Its kind of search button .

I want to do it using SQL helper,Store procedure and jquery.

It will be very helpull if You help me on this.

 

I have done this

 Search.cs page

 

protected void btnSrchBox_Click(object sender, EventArgs e)

{

string srchEmpID = txtSrchEmpID.Text;

string srchEmpName = txtSrchEmpName.Text;

string dbMessage = string.Empty;

// loadUsers();

gridView1.DataBind();

TCSRR.DMTController.Users C_srchUser = new TCSRR.DMTController.Users();

TCSRR.DMTBusinessEntity.User user = new TCSRR.DMTBusinessEntity.User();

user.EmployeeNo = txtSrchEmpID.Text;

user.FirstName = txtSrchEmpName.Text;

int flag = C_srchUser.SearchEmpDetails(srchEmpID,srchEmpName);

if (flag != -1)

{

txtSrchEmpID.Text="";

txtSrchEmpName.Text="";

dbMessage = ((flag==0)?"User Updated Sucessfully":"User Added Sucessfully");

}

else

dbMessage = "Duplicate entry : Unable to Add User details!";

//loadUsers();

gridView1.DataBind();

}

}

 

search.Controller

 

public int SearchEmpDetails(string srchEmpID,string srchEmpName)

{

int flag = 0;

try

{

UserData SrchUserData = new UserData();

flag = SrchUserData.SearchEmpDetails(srchEmpID, srchEmpName);

}

catch (Exception ex)

{

flag = -1;

}

return flag;

}

 

search.model

 

public int SearchEmpDetails(string srchEmpID,string srchEmpName)

{

//DataTable = dt;

int flag = 0;

try

{

SqlParameter[] arParam = new SqlParameter[3];

arParam[0] = new SqlParameter("@UM_Employee_Number", srchEmpID);

arParam[1] = new SqlParameter("@UM_Name", srchEmpName);

// arParam[2] = new SqlParameter("@UM_Status",'A');

// arParam[2] = new SqlParameter("@Flag", SqlDbType.Int);

arParam[1].Direction = ParameterDirection.Output;

SqlHelper.ExecuteNonQuery(FileManager.GetSettingsValue("DBConnection"), CommandType.StoredProcedure, "spmt_SearchBoxEmpDetails", arParam);

//flag = Convert.ToInt32(arParam[2].Value);

}

catch (Exception ex)

{

flag = -1;

}

return ;

}

 

DB Query

 

ALTER PROCEDURE [dbo].[spmt_SearchBoxEmpDetails]

@UM_Employee_Number nvarchar(15),

@UM_FirstName nvarchar(30),

@FLAG INT OUT

--@UM_Status nchar(1)

AS

BEGIN

SET NOCOUNT ON;

BEGIN TRY

SET @Flag = 0

BEGIN TRANSACTION

SET @Flag = 0

SELECT UM_FirstName, UM_Employee_Number

FROM User_Master

WHERE UM_Employee_Number='%"+txtSrchEmpID.Text+"%' OR UM_FirstName= '%"+txtSrchEmpName.Text+"%';

--' AND UM_Status='A

SET @Flag = 1

COMMIT TRANSACTION

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

SET @Flag = -1

END CATCH

END