C#和SQL操作Xml

 #region DataTableToXml
        public static string DataTableToXml(System.Data.DataTable Dt)
        {
            StringBuilder sbHtml = new StringBuilder();
            if (Dt.Rows.Count > 0)
            {
                sbHtml.Append("<Root>");
                for (int j = 0; j < Dt.Rows.Count; j++)
                {
                    sbHtml.Append("<Detail ");
                    for (int i = 0; i < Dt.Columns.Count; i++)
                    {
                        sbHtml.AppendFormat("{0}=\"{1}\"  ", Dt.Columns[i], Dt.Rows[j][Dt.Columns[i]]);
                    }
                    sbHtml.Append("></Detail>");
                }
                sbHtml.Append("</Root>");
            }
            return sbHtml.ToString();
        }
        #endregion

 

USE [DataBaseName]
GO
/****** Object:  StoredProcedure [dbo].[AdjustPriceDetail_Temp_Excel]    Script Date: 12/25/2014 11:11:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[AdjustPriceDetail_Temp_Excel]
@UserName nvarchar(50),
@AdjustNo nvarchar(50),
@Detail NText
As

declare @hDoc int
Declare @ProductNo Nvarchar(50)
Declare @ErrorSql Nvarchar(200)
Begin Tran
Begin
          set nocount on
         
          ------首先清除之前导入的数据
          delete from AdjustPriceDetail_Temp where UserName=@UserName
          
          -------导入表格
          
          Select Top 0 ProductNo,GoldPrice,acGold,MStoneCost,SStoneCost,acStone,acLabor,acOthers,acTotal,SalesFactor,SalesPrice
          Into #AdjustPrice From AdjustPriceDetail_Temp

          exec sp_xml_preparedocument @hDoc output,@Detail
          Insert Into #AdjustPrice(ProductNo,GoldPrice,acGold,MStoneCost,SStoneCost,acStone,acLabor,acOthers,acTotal,SalesFactor,SalesPrice)
          Select ProductNo,GoldPrice,acGold,MStoneCost,SStoneCost,acStone,acLabor,acOthers,acTotal,SalesFactor,SalesPrice
          from openxml(@hDoc,'/Root/Detail')
          with AdjustPriceDetail_Temp where ProductNo<>''
          
          if @@Error<>0 goto Err
          exec sp_xml_removedocument @hDoc
        
          --1.产品编号
          Set @ProductNo=(Select Top 1 ProductNo From #AdjustPrice Group By ProductNo having(COUNT(ProductNo)>1))
          IF @ProductNo Is Not Null
          Begin
            Set @ErrorSql= N'EXCEL表格中货品编号为：'+@ProductNo+'不能出现重复！';
            Goto Err
          End
          
         Set @ProductNo=(Select Top 1 ProductNo From AdjustPriceDetail_Temp Where ProductNo In(Select ProductNo From #AdjustPrice))
          IF @ProductNo Is Not Null
          Begin
            Set @ErrorSql= N'数据库中货品编号为：'+@ProductNo+'不能出现重复导入！';
            Goto Err
          End
          
          Set @ProductNo=(Select Top 1 ProductNo From #AdjustPrice Where ProductNo NOT In(Select ProductNo From Product))
          IF @ProductNo Is Not Null
          Begin
            Set @ErrorSql= N'库存表中不存在中货品编号为：'+@ProductNo+'的货品！';
            Goto Err
          End
          
       INSERT INTO AdjustPriceDetail_Temp
           ([UserName]
           ,[AdjustPriceNo]
           ,[ProductNo]
           ,[ProductName]
           ,[StyleNo]
           ,[SubTypeNo]
           ,[ItemTypeNo]
           ,[FinenessNo]
           ,[SalesTypeNo]
           ,[NetWtGM]
           ,[acGoldWtGM]
           ,[GoldPrice]
           ,[acGold]
           ,[MStoneQty]
           ,[MStoneWt]
           ,[SStoneQty]
           ,[SStoneWt]
           ,[MStoneCost]
           ,[SStoneCost]
           ,[acStone]
           ,[acLabor]
           ,[acOthers]
           ,[acTotal]
           ,[SalesFactor]
           ,[SalesPrice])
     select
           @UserName,
           @AdjustNo,
           A.ProductNo,
           B.ProductName,
           B.StyleNo,
           B.SubTypeNo,
           B.ItemTypeNo,
           B.FinenessNo,
           B.SalesTypeNo,
           B.NetWtGM,
           B.acGoldWtGM,
           A.GoldPrice,
           A.acGold,
           B.MStoneQty,
           B.MStoneWt,
           B.SStoneQty,
           B.SStoneWt,
           A.MStoneCost,
           A.SStoneCost,
           A.acStone,
           A.acLabor,
           A.acOthers,
           A.acTotal,
           A.SalesFactor,
           A.SalesPrice
           from #AdjustPrice AS A left join Product AS B On A.ProductNo = B.ProductNo
        
        if @@Error<>0 goto Err
        
        Drop Table #AdjustPrice
        
        if @@Error<>0 goto Err
       
       Select N'success' As ErrorSql

End
Commit Tran
Return
Err:
RollBack Tran
Select @ErrorSql As ErrorSql
Return

转载于:https://www.cnblogs.com/Gxiaopan/p/4184526.html