在项目中,需要将一个DataGrid中的信息传入数据库进行更新,前提是无法使用DataAdapter,请教了师傅,才知道了还有xml参数这个东东可以完成这个任务。
存储过程如下:
CREATE
PROCEDURE
dbo.UP_TestProc
(
@XML TEXT
, @DataSetName VARCHAR ( 100 )
, @DataTableName VARCHAR ( 100 )
)
AS
SET NOCOUNT ON
DECLARE @RowPattern VARCHAR ( 300 )
SET @RowPattern = ' / ' + @DataSetName + ' / ' + @DataTableName
DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @XML
SELECT Column1, Column2
INTO #tmpTable
FROM OPENXML ( @idoc , @RowPattern , 2 )
WITH
(
Column1 INT
Column1 CHAR (4)
)
EXEC sp_xml_removedocument @idoc
INSERT INTO Table1( [ Column1 ] , [ Column2 ] )
SELECT Column1, Column2 FROM #tmpTable (NOLOCK)
(
@XML TEXT
, @DataSetName VARCHAR ( 100 )
, @DataTableName VARCHAR ( 100 )
)
AS
SET NOCOUNT ON
DECLARE @RowPattern VARCHAR ( 300 )
SET @RowPattern = ' / ' + @DataSetName + ' / ' + @DataTableName
DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @XML
SELECT Column1, Column2
INTO #tmpTable
FROM OPENXML ( @idoc , @RowPattern , 2 )
WITH
(
Column1 INT
Column1 CHAR (4)
)
EXEC sp_xml_removedocument @idoc
INSERT INTO Table1( [ Column1 ] , [ Column2 ] )
SELECT Column1, Column2 FROM #tmpTable (NOLOCK)
传入三个参数,第一个是包含数据的XML,通常用DataSet.GetXml()方法就可以获得;第二个是DataSet名字;第三个是DataTable的名字。
具体使用方法,可以查看联机帮助。最近我越来越懒了,呵呵。