ok here are the details.
Calling Method:
public DataTable showApplications(GridView gv, string orderBy, string direction, string userEmail)
{
DataTable dt = new DataTable();
string userId = cbl.getLoginnedUserId();
IDataReader idr = db.GetAllLoanApplications(orderBy, direction, userEmail,"","","", Convert.ToString(userId)).ExecuteReader();
da.FillFromReader(dt, idr);
gv.DataSource = dt;
gv.DataBind();
return dt;
}
Calling Store Procedure:
GetAllLoanApplications
Passing Parameters:
@OrderBy varchar(30),
@Direction varchar(10),
@UserEmail varchar(50),
@UserId Varchar(150)
Sample Parameter Values:
@OrderBy = ""
@Direction = ""
@UserEmail = ""
@UserId = 37438d06-bcb8-47ab-9390-11416e36c704
Calling Store Procedure Definition:
Create Proc GetAllLoanApplications
@OrderBy varchar(30),
@Direction varchar(10),
@UserEmail varchar(50),
@UserId Varchar(150)
As
Declare @query varchar(max)
Set @query = 'Select rt.*, lkUpD.Col1Value As "CustomerType" from (Select la.LoanAppId , la.UserId, ci.FirstName , ci.LastName, la.ApplicationDateSubmitted, la.LoanAmount, la.DueDate, lkUpD.Col1Value As "Status" from LoanApplication la, ContactInfo ci, LookUpDetails lkUpD where la.UserId = ci.UserId and la.StatusId = lkUpD.LookUpDetailId '
If @UserEmail != ''
Begin
Set @query = 'Select rt.*, lkUpD.Col1Value As "CustomerType" from (Select la.LoanAppId , la.UserId, ci.FirstName , ci.LastName, la.ApplicationDateSubmitted, la.LoanAmount, la.DueDate, lkUpD.Col1Value As "Status" , aspNetMembership.Email from LoanApplication la, ContactInfo ci, LookUpDetails lkUpD , aspnet_Membership aspNetMembership where la.UserId = ci.UserId and la.StatusId = lkUpD.LookUpDetailId and la.UserId = aspNetMembership.UserId And aspNetMembership.Email = '
set @query = @query + '''' + @UserEmail + ''''
End
If @UserId !=''
Begin
set @query = @query + ' and la.UserId = ' + '''' + @UserId + ''''
End
set @query = @query + ' ) rt, ContactInfo ci, LookUpDetails lkUpD where rt.UserId = ci.UserId And ci.CustomerTypeId = lkUpD.LookUpDetailId '
If @OrderBy = 'Application Id'
Begin
Set @query = @query + ' order by la.LoanAppId ' + @Direction
end
If @OrderBy = 'Name'
Begin
Set @query = @query + ' order by ci.FirstName ' + @Direction
end
If @OrderBy = 'Application Submitted Date'
Begin
Set @query = @query + ' order by la.ApplicationDateSubmitted ' + @Direction
end
If @OrderBy = 'Requested Amount'
Begin
Set @query = @query + ' order by la.LoanAmount ' + @Direction
end
If @OrderBy = 'Due Date'
Begin
Set @query = @query + ' order by la.DueDate ' + @Direction
end
If @OrderBy = 'Status'
Begin
Set @query = @query + 'order by la.StatusId ' + @Direction
end
exec (@query)
If you require any further details then please let me know.
Regards,
Muhammad Naveed Khan
Email: [email protected]
Contact: +923014242889