c# sqlserver向存储过程传入xml数据

c#代码：

点击查看代码

var xmlData = new XDocument(new XElement("Data", query.weldPlanList.Select(r => new XElement("Row",
                            new XAttribute("APSFactoryID", query.APSFactoryID),
                            new XAttribute("Class", query.Class),
                            new XAttribute("PlanSourceType", query.PlanSourceType),
                            new XAttribute("ProduceDate", query.ProduceDate),
                            new XAttribute("userNo", query.userNo)

                  ))));



            using (IDbConnection conn = new SqlConnection(ConnectionHelp.ConnectionString))
            {
                conn.Execute("Add", new { DATA = xmlData.ToString() }, null, null, CommandType.StoredProcedure);
            }
            return true;

SQL server

点击查看代码

CREATE PROCEDURE [dbo].[Add]
	 @DATA XML-- 批量数据
    
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON
	BEGIN TRANSACTION	 --事务开始


	DECLARE @CurrTime DATETIME
	SET @CurrTime = GETDATE();
	DECLARE @Voucher NVARCHAR(50)
	SET @Voucher=NEWID();
	IF OBJECT_ID(N'tempdb..#orginData', N'U') IS NOT NULL
	BEGIN
		--删除临时表
		DROP TABLE #orginData;
	END;
	CREATE TABLE #orginData(
	    ID   int IDENTITY (1,1) NOT	NULL, --创建列ID,并且每次新增一条记录就会加1
		APSFactoryID NVARCHAR(50) NOT NULL,
		Class INT NOT NULL,
		PlanSourceType INT NOT NULL,
		ProduceDate DATETIME NOT NULL,
		userNo NVARCHAR(10) NOT NULL
	)

    INSERT  INTO #orginData
                    SELECT  			
			    T.C.value('(@APSFactoryID)[1]','[NVARCHAR](50)')  AS APSFactoryID,
				T.C.value('(@Class)[1]','[FLOAT]')  AS Class,
			    T.C.value('(@PlanSourceType)[1]','[FLOAT]')  AS PlanSourceType,
				T.C.value('(@ProduceDate)[1]','[NVARCHAR](20)')  AS ProduceDate,
				T.C.value('(@userNo)[1]','[NVARCHAR](10)')  AS userNo
                    FROM  @DATA.nodes('/Data/Row') AS T ( C )
	COMMIT TRANSACTION  --提交事务
END



GO