1.传XML节点到SQL SERVER 中处理
例子：
js 文件
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文件
public void UpdateUserTaskPerStation()
{
string json = _request["newUserTaskPerStation"];
XmlDocument doc = Newtonsoft.Json.JsonConvert.DeserializeXmlNode(json, "UserTaskPerStation");这里用到JQUERY的一个插件转化成XML节点
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);
}
业务层：
public string UpdateUserTaskPerStation(string userTaskPerStationXml, string username)
{
return userTasksessiondata.UpdateUserTaskPerStation(userTaskPerStationXml, username);
}
数据层：
public string UpdateUserTaskPerStation(string userTaskPerStationXml, string username)
{
ReturnValue returnValue = new ReturnValue();
string sqlCommand = "WhUserTaskPerStationSave";
DbCommand 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 XML ,
@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.碰到一些数据按每个字段分组，然后根据分组批量处理把分组了的一些值拼凑起来
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 A
GROUP BY UserId
) B