SQL中的数据的批量操作

在某些情况下，当我们要批量的向数据库读写数据，通常有以下几种方式可供选择：

1. 循环调用Store Procedure，一条条的更新，这种做法在数据量较小，服务器压力不大时可以考虑，否则会严重加重服务器的负担。

2. 拼接参数列表，一次性传入参数，然后再在SP里解析。比如我要开发一个GetUserByUserIdList(string userIdList)，那么我可以把要传入的userId拼接成一个字符串传入："101,102,103"，然后在SP里将它解析即可。 这种做法对于简单的参数没什么问题，可一旦参数相对复杂就很难办到了。比如我要向数据库批量插入数据，并且每条数据都包括很多信息，这样拼字符串的话将会非常麻烦。

3. 使用C#提供的 SqlBulkCopy (位于System.Data.SqlClient下)， 它提供了从集合到数据库表的直接映射，先指定数据库连接字符串，再指定列的映射和数据源，然后一次性拷贝， 见示例：

private static bool BulkCopyItems(DataTable batchItems)
        {
            bool result = false;
            try
            {
                using (SqlBulkCopy bcp = new SqlBulkCopy(ConnectionStringManager.GetConnectionString(context)))
                {
                    bcp.ColumnMappings.Add(BatchTemplateFieldNames.Email, BatchItemStorageFieldNames.Email);
                    bcp.ColumnMappings.Add(BatchItemStorageFieldNames.Member_id, BatchItemStorageFieldNames.Member_id);
                    bcp.ColumnMappings.Add(BatchTemplateFieldNames.FirstName, BatchItemStorageFieldNames.FirstName);
                    bcp.ColumnMappings.Add(BatchTemplateFieldNames.LastName, BatchItemStorageFieldNames.LastName);
                    bcp.ColumnMappings.Add(BatchTemplateFieldNames.Password, BatchItemStorageFieldNames.Password);
                    bcp.ColumnMappings.Add(BatchTemplateFieldNames.Telephone, BatchItemStorageFieldNames.Telephone);
                    
                    bcp.BatchSize = 100;
                    bcp.NotifyAfter = 100;
                    bcp.DestinationTableName = BatchManagementConstants.BatchItemTableName;
                    bcp.WriteToServer(batchItems);
                    result = true;
                }
            }
            catch (Exception ex)
            {
                ExceptionManager.Publish(ex);
                result = false;
            }
            return result;
        }

这种方式比较适合一次性大量写入数据，缺点是对行为的控制力太弱，比如我想对不存在的User进行Create，对已存在的进行Update就很难满足要求。

4. 使用XML传参数，然后再SQL Server短解析XML获取数据，SQL对XML的解析语法如下：

declare @xml as xml
set @xml = N'
<ExtraInfoList>
    <ExtraInfo Id = "1002">
        <Key>32</Key>
        <Value>bbb</Value>
        <BirthDate>1982/12/09</BirthDate>
    </ExtraInfo>
    <ExtraInfo Id = "1006">
        <Key>24</Key>
        <Value>yyy</Value>
        <BirthDate>1990/5/16</BirthDate>
    </ExtraInfo>
    <ExtraInfo Id = "1006">
        <Key>24</Key>
        <Value>ggg</Value>
        <BirthDate>1990/5/16</BirthDate>
    </ExtraInfo>
</ExtraInfoList>'
--declare @f bit
--set @f = @xml.exist('/ExtraInfoList/ExtraInfo/Key')
--IF @f != 1
--PRINT 'NOT EXIST'
--ELSE
--PRINT 'EXIST'

select 
    265 as GroupId,
    T.col.value('@Id', 'int') as Id,
    T.col.value('Key[1]', 'int') as [Key],
    T.col.value('Value[1]', 'nvarchar(50)') as Value,
    T.col.value('BirthDate[1]', 'datetime') as BirthDate
    into #temp2
    from @xml.nodes('/ExtraInfoList/ExtraInfo') as T(col)

 

以下是我写的一个完整 Store Procedure：

USE ET_Partner
GO

/*
IF OBJECT_ID(N'dbo.MemberExtraInfo_BatchSave_p', N'P') IS NOT NULL
     DROP PROCEDURE dbo.MemberExtraInfo_BatchSave_p
*/
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.MemberExtraInfo_BatchSave_p
     @Member_id AS INT
   , @ExtraInfoList AS XML
   , @OverwriteExistingItem AS BIT
   , @Succeed AS BIT OUTPUT
AS
    DECLARE @exist AS BIT
    
-- The format of the xml should be:
--<ExtraInfoList>
--    <ExtraInfo>
--        <Key>32</Key>
--        <Value>bbb</Value>
--    </ExtraInfo>
--</ExtraInfoList>
    
    -- memberid not exists
    IF NOT EXISTS (SELECT 1 FROM ET_Main.dbo.Members WITH (NOLOCK) WHERE MemberId = @Member_id)
    BEGIN
        SET @Succeed = 0
        RETURN
    END
    
    -- no extra info provided
    SET @exist = @ExtraInfoList.exist('/ExtraInfoList/ExtraInfo/Key')
    IF @exist != 1
    BEGIN
        SET @Succeed = 0
        RETURN
    END
    
    SELECT 
        @Member_id AS [MemberId],
        T.col.value('Key[1]', 'int') AS [ExtraInfoId],
        T.col.value('Value[1]', 'nvarchar(100)') AS [ExtraInfoValue]
        INTO #temp
        FROM @ExtraInfoList.nodes('/ExtraInfoList/ExtraInfo') AS T(col)
    
    -- contains invalid extra id
    IF EXISTS (
                    SELECT 1 
                    FROM #temp AS t 
                    LEFT JOIN dbo.ExtraInfoKey AS e WITH (NOLOCK) 
                        ON t.ExtraInfoId = e.ExtraInfoId 
                    WHERE e.ExtraInfoId IS NULL
              )
    BEGIN
        SET @Succeed = 0
        RETURN
    END
    
    -- update existing extra info
    IF @OverwriteExistingItem = 1
    BEGIN
        UPDATE dbo.MembersExtraInfo
            SET ExtraInfoValue = t.ExtraInfoValue, ModifiedDate = GETDATE()
        FROM dbo.MembersExtraInfo AS m
        INNER JOIN #temp AS t ON m.MemberId = t.MemberId AND m.ExtraInfoId = t.ExtraInfoId
    END
    
    -- insert new extra info
    INSERT INTO dbo.MembersExtraInfo (MemberId, ExtraInfoId, ExtraInfoValue, ModifiedDate)
    SELECT t.MemberId, t.ExtraInfoId, t.ExtraInfoValue, GETDATE()
    FROM #temp AS t
    LEFT JOIN dbo.MembersExtraInfo AS e WITH (NOLOCK) 
        ON t.MemberId = e.MemberId AND t.ExtraInfoId = e.ExtraInfoId
    WHERE e.ExtraInfoId IS NULL AND e.MemberId IS NULL
    
    SET @Succeed = 1
    
    DROP TABLE #temp

    RETURN
GO

GRANT EXECUTE ON dbo.MemberExtraInfo_BatchSave_p TO [SomeRole]
GO

　　在这里，先将传入的XML解析成一张临时表，然后就好做了，需要注意的是select xml的语法问题，跟一般对表的select有差别，很容易出错。

　　另外还有一种解析XML的方式是SQL内置的 OPENXML， 具体怎么做可以参考：http://msdn.microsoft.com/en-us/library/ms186918.aspx