SQL Server processing skills

1. upload an XML node to SQL Server for processing

Example:

JS files

VaR wmsusertask = [];

Editpopupbody. Find ('tr '). Each (function (){
VaR DATA = jquery (this). Find (': Nth-child (2 )');
VaR newvalue = data. Text ();
VaR oldvalue = data. ATTR ('oldvalue ');
VaR usertaskid = data. ATTR ('id'). Split ('_') [1];
// Alert ('Id: '+ usertaskid + 'newvalue:' + newvalue );
If (newvalue! = Oldvalue ){
VaR usertask = "";
Usertask + = '"usertask ":{'
Usertask + = '\ "usertaskid \":' + '\ "' + usertaskid + '\",';
Usertask + = '\ "usertaskperstation \":' + '\ "' + newvalue + '\"}';
Wmsusertask. Push (usertask );
}
});
If (wmsusertask. length> 0 ){
Jquery ("# editstation"). Unbind ('click ');
VaR sjson = "{" + wmsusertask. Join (",") + "}";
Jquery. Post (page. Handler + "updateusertaskperstation ",{ Newusertaskperstation: sjson, RD: parseint (10000 * Math. Random (1)}, function (data ){
If (Data! = NULL & Data! = ""){
If (! Data. Error ){
Page. popu. Close ();
Editpopupbody.html ('');
Jquery ("# searchbutton"). Click ();
} Else {
Jquery ("# msglable" ).html ("*" + data. Error );
}
Jquery ("# editstation"). Click (page. editusertaskperstationpopup );
}
}, "JSON ");
} Else {
Jquery ("# msglable" ).html ('* nothing to update .');
}
},

Handler File

Public void updateusertaskperstation ()
{
String JSON = _ request ["newusertaskperstation"];
Xmldocument Doc = newtonsoft. JSON. jsonconvert. deserializexmlnode (JSON, "usertaskperstation"); here a jquery plug-in is used to convert it into an XML node.
Jsontransfer JS = new jsontransfer ();
If (Doc! = NULL)
{
String rs = funmngr. updateusertaskperstation (Doc. innerxml, contextaccessor. Current. username );
If (string. isnullorempty (RS ))
JS. Add ("success", "OK ");
Else
JS. seterror ("Update fail .");
}
Else
{
JS. seterror ("nothing to update .");
}
_ Response. Write (JS );
}

Business Layer:

Public String updateusertaskperstation (string usertaskperstationxml, string username)
{
Return usertasksessiondata. updateusertaskperstation (usertaskperstationxml, username );
}

Data Layer:

Public String updateusertaskperstation (string usertaskperstationxml, string username)
{
Returnvalue = new returnvalue ();
String sqlcommand = "whusertaskperstationsave ";
Dbcommand = database. getstoredproccommand (sqlcommand );
Database. addinparameter (dbcommand, "@ usertaskperstationxml", dbtype. XML, dataaccessutility. getdbparametervalue (usertaskperstationxml ));
Database. addinparameter (dbcommand, "@ username", dbtype. String, dataaccessutility. getdbparametervalue (username ));
Database. addoutparameter (dbcommand, "@ returnmessage", dbtype. ansistring, 300 );
If (transaction! = NULL)
{
Executenonquery (dbcommand, transaction );
}
Else
{
Executenonquery (dbcommand );
}
Returnvalue. Message = database. getparametervalue (dbcommand, "@ returnmessage"). tostring ();
Return returnvalue. message;
}

SP:

Create proc whusertaskperstationsave
(
@ Usertaskperstationxml,
@ Username varchar (50 ),
@ Returnmessage varchar (100) Output
)
As
Begin
Declare @ IDOC int

If object_id ('tempdb .. # usertaskperstation') is not null
Drop table # usertaskperstation

Exec sp_xml_preparedocument @ IDOC output, @ usertaskperstationxml

Select usertaskid as usertaskid,
Usertaskperstation as usertaskperstation,
@ Username as updateby,
Getdate () as updatedate
Into # usertaskperstation
From openxml (@ IDOC, '/usertaskperstation/usertask', 2)
With (
Usertaskid int,
Usertaskperstation int
)

Exec sp_xml_removedocument @ IDOC

-- Select * from # usertaskperstation

Update DBO. whusertask
Set usercountperstation = UTP. usertaskperstation,
Updateby = UTP. updateby,
Updatedate = UTP. updatedate
From DBO. whusertask ut with (nolock)
Inner join # usertaskperstation UTP on UT. usertaskid = UTP. usertaskid

End
Go

2. Some data is grouped by each field, and some values of the group are pieced together by Batch Processing Based on the grouping.

Insert into # tempresult
(Userid,
Username,
Usertaskid,
Usertaskname

)
Select Wu. userid,
Max (Wu. username) as username,
WUT. usertaskid as usertaskid,
Max (WUT. usertaskname) as usertaskname
From DBO. whuser Wu with (nolock)
Inner join DBO. whusergroup wug with (nolock) on Wu. userid = wug. userid
Inner join DBO. whgroupusertaskmapping wutm with (nolock) on wug. groupid = wutm. groupid
Inner join DBO. whusertask WUT with (nolock) on WUT. usertaskid = wutm. usertaskid
Left join # usertable T1 on Wu. userid = t1.userid
And t1.systemtype = 'wms'
Where WUT. logintype = 'user task'
And t1.userid is null
And (@ usertaskid = 0
Or WUT. usertaskid = @ usertaskid
)
And (@ username =''
Or @ username is null
Or Wu. Username like '%' + @ username + '%'
)
Group by Wu. userid,
WUT. usertaskid

Insert into # usertable
(Userid,
Username,
Systemtype,
Isactive,
Usertaskid,
Usertaskname,
Locationid,
Locationname

)
Select B. userid,
B. username,
'Wms ',
0,
Left (resultlist, Len (resultlist)-1) as usertaskid,
'',
'',
''
From (select userid,
Max (username) as username,
(Select convert (varchar, usertaskid)
+ ',' + Usertaskname + ':'
From # tempresult
Where userid = A. userid
For
XML Path ('')
) As resultlist
From # tempresult
Group by userid
) B