MS SQLSERVER使用TVP进行批量更新

1.  创建用户自定义的表类型(Table Type)

CREATE TYPE [dbo].[TVPGameDataItem] AS TABLE (

[UserID] [BIGINT] NULL,

[DBID] [INT] NULL,

[Wins] [INT] NULL,

[Balances] [INT] NULL,

[Loses] [INT] NULL,

[Escapes] [INT] NULL,

[Score] [INT] NULL,

[Money] [BIGINT] NULL,

[Experience] [INT] NULL

)

 

2.  存储过程中使用TVP (Table Valued Parameter)

CREATE  PROCEDURE [dbo].[xp_User_ChangeGameResources]  

     @GameID         int,

@Items  dbo.TVPGameDataItem READONLY

AS 

SET NOCOUNT ON

BEGIN TRAN 

更新game_data记录

MERGE INTO tb_game_data t1 USING @Items t2 ON t1.GameID = @GameID AND t1.UserID = t2.UserID

WHEN MATCHED THEN 

UPDATE SET

t1.Wins  =  t1.Wins  + t2.Wins,     

t1.Balances    =  t1.Balances + t2.Balances,

t1.Loses       =  t1.Loses  + t2.Loses, 

t1.Escapes     =  t1.Escapes  + t2.Escapes,

t1.Score       =  t1.Score  + t2.Score,  

t1.Money       =  t1.Money  + t2.Money,  

t1.Experience =  t1.Experience + t2.Experience

WHEN NOT MATCHED THEN  

INSERT (UserID, GameID, Wins, Balances, Loses, Escapes, Score, Money, Experience)

VALUES (t2.UserID, @GameID, t2.Wins, t2.Balances, t2.Loses, t2.Escapes, t2.Score, t2.Money, t2.Experience);

 

更新user信息

UPDATE tb_user_BaseInfo_0

SET tb_user_BaseInfo_0.Money = t1.Money + t2.Money

FROM tb_user_BaseInfo_0 t1

INNER JOIN @Items t2 ON t1.UserID = t2.UserID

COMMIT TRAN

RETURN @@error

 

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

CREATE  PROC [dbo].[xp_UserLog_GameResChange]

     @DataType            int,

@GameResChangeType   int,

     @GameID              int,

 @IsPlaying           int,

@Items  dbo.TVPGameLogItem READONLY

AS 

 

SET NOCOUNT ON

 

DECLARE @BattleID BIGINT

 

SET @BattleID = 0

 

BEGIN TRAN 

IF (@IsPlaying = 1)

        BEGIN

            SELECT @BattleID = Intvalue FROM tb_LGSystermParam WHERE SysID = 1

            if @BattleID = 0

               BEGIN

                  SET @BattleID = 1

               END

            

             UPDATE tb_LGSystermParam SET Intvalue = (@BattleID + 1) WHERE SysID = 1

        END

 

INSERT INTO tb_game_log  

(

UserID             ,

GameID            ,

GameResult         ,

cashChanged        ,

scoreChanged       ,

ExperienceChanged  ,

TableFee           ,

ChangingType       ,

BattleID           ,

ChangingTime

)

SELECT 

UserID, 

@GameID, 

Result, 

Money, 

Score, 

Experience, 

TableFee, 

@GameResChangeType, 

@BattleID,

GETDATE() 

FROM @Items

 

COMMIT TRAN

RETURN @@error


 3.  程序中使用ODBC向存储过程的TVP参数绑定多行数据

1) 绑定TVP参数时,参数类型(第5个参数为SQL_SS_TABLE

// Bind TVP parameter

SQLLEN cbTVP;

 m_cbValue[++m_nBindParam] = 0;

 SQLBindParameter(m_hstmt,

   m_nBindParam,  // ParameterNumber

  SQL_PARAM_INPUT, // InputOutputType

 SQL_C_DEFAULT,   // ValueType

 SQL_SS_TABLE,   // Parametertype

  MAX_CHAIRS,          // ColumnSize – for a TVP this the row array size

  0,               // DecimalDigits – for a TVP this is the number of columns in the TVP

  NULL,               // ParameterValuePtr – for a TVP this is the type name of the TVP

                               // (not needed with stored proc)

NULL,             // BufferLength – for a TVP this is the length of the type name or SQL_NTS

                                // (not needed with stored proc)

 &cbTVP);    // StrLen_or_IndPtr – for a TVP this is the number of rows available

 

2) TVP参数内的每个Column绑定数据(实际就是一个包含该Column多行数据的数组)分2步:

– 先使用SQLSetStmtAttr(m_hstmt, SQL_SOPT_SS_PARAM_FOCUS …)  聚焦到TVP参数,

– 然后调用SQLBindParameter依次绑定TVP内部的各个Column

SQLSetStmtAttr(m_hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER)m_nBindParam, SQL_IS_INTEGER);

 

SQLBindParameter(m_hstmt, 1, SQL_PARAM_INPUT, SQL_C_SBIGINT, SQL_BIGINT, 0, 0, items.userid, sizeof(SQLBIGINT), NULL);

SQLBindParameter(m_hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, items.dbid, sizeof(SQLINTEGER), NULL);

SQLBindParameter(m_hstmt, 3, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, items.result, sizeof(SQLINTEGER), NULL);

SQLBindParameter(m_hstmt, 4, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, items.score, sizeof(SQLINTEGER), NULL);

SQLBindParameter(m_hstmt, 5, SQL_PARAM_INPUT, SQL_C_SBIGINT, SQL_BIGINT, 0, 0, items.money, sizeof(SQLBIGINT), NULL);

SQLBindParameter(m_hstmt, 6, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, items.experience, sizeof(SQLINTEGER), NULL);

SQLBindParameter(m_hstmt, 7, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, items.tablefee, sizeof(SQLINTEGER), NULL);

 

3) 绑定完TVP内部字段后,再次调用SQLSetStmtAttr(m_hstmt, SQL_SOPT_SS_PARAM_FOCUS …)  重置Focus.

// Reset param focus

 SQLSetStmtAttr(m_hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER)0, SQL_IS_INTEGER);

 

// Initialize TVP row count

 cbTVP = (SQLUINTEGER)uicts;

 

4.  注意事项

1) TVP (Table Valued Parameter)SQL Server 2008 R2引入的新特性,之前的版本不支持。

2) ODBC驱动的版本是从10.0开始支持TVP参数,所以使用TVP操作SQL Server的程序运行环境上需要安装10.0或以上版本的数据库连接驱动。

注:可以通过运行odbc数据源管理程序查看本机安装的odbc驱动版本。

图片1.png

3) 关于odbc错误“[Microsoft][ODBC SQL Server Driver]无效的参数类型”

出现该错误,说明我们的程序使用的是10.0以前的odbc驱动访问的SQL Server,需要在创建DB连接的地方,将DB连接串中DRIVER指定10.0或以上版本,如下所示:

图片2.png

"DRIVER={SQL Server Native Client 10.0};SERVER=x.x.x.x;DATABASE=MiniGameLogNewDB;UID=sa;PWD=123456;"

4)     关于” [Microsoft][SQL Server Native Client 10.0]Dialog failed”错误

当出现该错误时,请将SQLDriverConnect()函数的最后一个参数由SQL_DRIVER_COMPLETE改为SQL_DRIVER_NOPROMPT

 

发表评论

邮箱地址不会被公开。 必填项已用*标注