Question

I have stored procedure which takes xml as an input .

执行特殊产品的C#代码是

using (SqlConnection conn = new SqlConnection(connString))
{
    try
    {
        conn.Open();
        SqlCommand command = new SqlCommand("sp_Configure_Users", conn);
        command.CommandType = CommandType.StoredProcedure;
        var xmlData = new SqlParameter(parameterName: "@XMLDATA", value: GenerateXML(_userDetails));
        command.Parameters.Add(xmlData);

        count = command.ExecuteNonQuery();
        if (count > 0)
        {
            return count;
        }
        else
        {
            return 0;
        }
    }
    catch (SqlException  )
    {

    }
}

基因组法以xml数据的形式回收。在我所储存的表格中,我删除了SET NOCOUNT ON,在插入、删除或更新时,将实际无行。如果我对样本Xml数据提出疑问:

<UserCollection>
    <InsertList><Users User_Id="438"  First_Name="Praveen"  Middle_Name=""  Last_Name="Kumar"  Designation_Id="1"  Email="" Contact_Number="96533"  Updated_By="pkumar"  />
    </InsertList>
    <UpdateList></UpdateList>
    <DeleteList></DeleteList>
</UserCollection>

退还的价值是3,而不是1,因为我的收集只是一个需要加插的单行。由于没有更新或删除的行文。三、何时必须返回?

储存的Proc

  set ANSI_NULLS ON
  set QUOTED_IDENTIFIER ON
   go



  ALTER PROCEDURE [dbo].[sp_Configure_Users]
@XMLDATA xml
  AS
 DECLARE @Users_Staging Table(
[User_Id] [int] NOT NULL,
[First_Name] [varchar](200) NOT NULL,
[Middle_Name] [varchar](200) NULL,
[Last_Name] [varchar](200) NOT NULL,
[Designation_Id] [int] NOT NULL,
[Email] [varchar](250) NOT NULL,
[Contact_Number] [varchar](50) NOT NULL,
[Updated_By] [varchar](255) NOT NULL
  )

  BEGIN

  Set nocount off
  Go    

  BEGIN TRY
  BEGIN TRANSACTION


  DECLARE @CurrDate datetime;
  SELECT @CurrDate = GETDATE();
   -- Insert statements for procedure 
  INSERT INTO Users
  ([User_Id]
  ,[First_Name]
  ,[Middle_Name]
  ,[Last_Name]
  ,[Designation_Id]
  ,[Email]
  ,[Contact_Number]
  ,[Updated_By]
  ,[Updated_Date]
  )
  Select    
XMLDATA.item.value( @User_Id[1] ,  int ) AS User_Id,    
XMLDATA.item.value( @First_Name[1] ,  varchar(200) ) AS First_Name,
XMLDATA.item.value( @Middle_Name[1] ,  varchar(200) ) AS Middle_Name,
XMLDATA.item.value( @Last_Name[1] ,  varchar(200) ) AS Last_Name,
XMLDATA.item.value( @Designation_Id[1] ,  int ) AS Designation_Id,
XMLDATA.item.value( @Email[1] ,  varchar(250) ) AS Email,
XMLDATA.item.value( @Contact_Number[1] ,  varchar(50) ) AS Contact_Number,
XMLDATA.item.value( @Updated_By[1] ,  varchar(255) ) AS Updated_By,
@CurrDate
  FROM @XMLDATA.nodes( //UserCollection/InsertList/Users ) AS XMLDATA(item)

  -- Update statements for procedure 

  INSERT INTO @Users_Staging
       ([User_Id]
  ,[First_Name]
  ,[Middle_Name]
  ,[Last_Name]
  ,[Designation_Id]
  ,[Email]
  ,[Contact_Number]
  ,[Updated_By])

Select  
XMLDATA.item.value( @User_Id[1] ,  int ) AS User_Id,    
XMLDATA.item.value( @First_Name[1] ,  varchar(200) ) AS First_Name,
XMLDATA.item.value( @Middle_Name[1] ,  varchar(200) ) AS Middle_Name,
XMLDATA.item.value( @Last_Name[1] ,  varchar(200) ) AS Last_Name,
XMLDATA.item.value( @Designation_Id[1] ,  int ) AS Designation_Id,
XMLDATA.item.value( @Email[1] ,  varchar(250) ) AS Email,
   XMLDATA.item.value( @Contact_Number[1] ,  varchar(50) ) AS Contact_Number,
XMLDATA.item.value( @Updated_By[1] ,  varchar(255) ) AS Updated_By
  FROM @XMLDATA.nodes( //UserCollection/InsertList/Users ) AS XMLDATA(item)


  UPDATE Users 
  SET First_Name=stgusr.First_Name,
  Middle_Name=stgusr.Middle_Name,
  Last_Name=stgusr.Last_Name,
  Email=stgusr.Email,
  Contact_Number=stgusr.Contact_Number,
  Updated_By=stgusr.Updated_By,
  Updated_Date=@CurrDate
  FROM Users usr INNER JOIN
  @Users_Staging stgusr
  ON usr.User_Id=stgusr.User_Id

-- Delete statements for procedure 

   DELETE Users
   WHERE User_Id
     IN (Select         
    XMLDATA.item.value( @User_Id[1] ,  int ) AS User_Id
FROM @XMLDATA.nodes( //UserCollection/DeleteList/Users ) AS XMLDATA(item))

   COMMIT TRANSACTION;

   END TRY

   BEGIN CATCH
--EXEC sp_rethrow_error;
ROLLBACK TRANSACTION;
    END CATCH; 
   END

Answer 1

如果您根据您的储存程序更新/插入的表格,就会影响到受影响的浏览量:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.
Ref.

建议你将所储存的程序张贴在有关表格中的图象/触发器。

友情链接