1. query the qualified records from the database, store them in a DataTable, traverse the table using c #, and use the primary key in DataRow, then read multiple matching records, merge the Record Content read for the second time, and return it to the preceding DataRow data row. There is no error in doing so, but if the data volume is large, we may face countless attempts to open and disconnect database connections, resulting in low speed and efficiency.
2. read data from the database once to a table and return it to the UI Layer. Everyone wants to do this, but it was too stupid to study it. Today, due to the large amount of data, I have to think of other ways to improve efficiency.
Google ~ Hk, there is an answer, and then I wrote
The purpose is to obtain non-quantitative qualified part-time records, and record the participation of each part-time project to several fields, and then return the Table
Copy codeThe Code is as follows:
Create function Fn_GetJobListByPID -- Create a user-defined function to obtain the number and number of all projects involved in a specified part-time job
(
@ ParttimerID int
)
Returns @ t table (Jobs varchar (5000), ParttimerID int, TotalCount int)
As
Begin
Declare @ SQL varchar (5000), @ TotalCount int
Set @ SQL =''
Set @ TotalCount = 0
Select @ SQL = @ SQL + j. JobNo + '-' + j. JobWave + '', @ TotalCount = @ TotalCount + 1
From ONJB_JobApplication a, ONJB_Jobs j
Where a. ParttimerID = @ ParttimerID
And a. Result = 'V'
And a. JobID = j. JobID
Insert @ t values (@ SQL, @ ParttimerID, @ TotalCount)
Return
End
Reference
Copy codeThe Code is as follows:
--...........................
-- Project created
Left join (select Jobs, ParttimerID, TotalCount From Fn_GetJobListByPID (@ ParttimerID) as j1
On p. ParttimerID = j1.ParttimerID
-- Project Creation
Left join (select CurJobs, ParttimerID, CurCount From Fn_GetCurJobsByPID (@ ParttimerID) as j2
On p. ParttimerID = j2.ParttimerID
Where p. ParttimerID = @ ParttimerID