sql server 一些處理技巧

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