Insert multiple records to a Stored Procedure in SQL Server using XML in C#

Insert multiple records to a Stored Procedure in SQL Server using XML in C#

SharePointProject2.aspx:-

<%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>

<%@ Import Namespace="Microsoft.SharePoint.ApplicationPages" %>

<%@ Register TagPrefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls"

    Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register TagPrefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register TagPrefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>

<%@ Import Namespace="Microsoft.SharePoint" %>

<%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ApplicationPage1.aspx.cs"

    Inherits="SharePointProject2.Layouts.SharePointProject2.ApplicationPage1" DynamicMasterPageFile="~masterurl/default.master" %>

<asp:Content ID="PageHead" ContentPlaceHolderID="PlaceHolderAdditionalPageHead" runat="server">

</asp:Content>

<asp:Content ID="Main" ContentPlaceHolderID="PlaceHolderMain" runat="server">

    <table id="tbl" runat="server">

        <tr>

            <td>

                <asp:Button ID="btnInsert" runat="server" Text="Insert" OnClick="btnInsert_Click" />

            </td>

        </tr>

    </table>

</asp:Content>

<asp:Content ID="PageTitle" ContentPlaceHolderID="PlaceHolderPageTitle" runat="server">

    Application Page

</asp:Content>

<asp:Content ID="PageTitleInTitleArea" ContentPlaceHolderID="PlaceHolderPageTitleInTitleArea"

    runat="server">

    My Application Page

</asp:Content>

SharePointProject2.aspx.cs

using System;

using Microsoft.SharePoint;

using Microsoft.SharePoint.WebControls;

using System.Text;

using System.Data.SqlClient;

using System.Data;

namespace SharePointProject2.Layouts.SharePointProject2

{

    public partial class ApplicationPage1 : LayoutsPageBase

    {

        SqlConnection conn = null;

        SqlDataReader rdr = null;

        SqlCommand cmd = null;

        string ConnectionString = string.Empty;

        protected void Page_Load(object sender, EventArgs e)

        {

            ConnectionString = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DB_NAme;Data Source=Server_Name";

        }

        protected void btnInsert_Click(object sender, EventArgs e)

        {

            StringBuilder sbXML = new StringBuilder();

            sbXML.Append("<XML>");

            sbXML.Append("<Employee>");

            sbXML.Append("<Items>");

            sbXML.Append("<Item>");

            sbXML.Append("<ID>");

            sbXML.Append("1");

            sbXML.Append("</ID>");

            sbXML.Append("<Name>");

            sbXML.Append("emp name 1");

            sbXML.Append("</Name>");

            sbXML.Append("</Item>");

            sbXML.Append("<Item>");

            sbXML.Append("<ID>");

            sbXML.Append("2");

            sbXML.Append("</ID>");

            sbXML.Append("<Name>");

            sbXML.Append("emp name 2");

            sbXML.Append("</Name>");

            sbXML.Append("</Item>");

            sbXML.Append("</Items>");

            sbXML.Append("</Employee>");

            sbXML.Append("</XML>");

            using (conn = new SqlConnection(ConnectionString))

            {

                using (cmd = new SqlCommand("spEmployee_Insert", conn))

                {

                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@strRequestXML", sbXML.ToString()));

                    if (conn.State != ConnectionState.Open)

                    {

                        conn.Close();

                        conn.Open();

                    }

                    rdr = cmd.ExecuteReader();

                    if (conn != null && conn.State != ConnectionState.Closed)

                    {

                        conn.Close();

                    }

                }

            }

        }

    }

}

Create Table in SQL:-

CREATE TABLE [dbo].[Employee](

      [ID] [int] NOT NULL,

      [Name] [nvarchar](50) NULL,

 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED

(

      [ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Create a Stored Procedure “spEmployee_Insert”:-

ALTER PROCEDURE [dbo].[spEmployee_Insert]

      -- Add the parameters for the stored procedure here

      @strRequestXML    XML   =     NULL

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      DECLARE @spsDoc   INT

           

      EXEC sp_xml_preparedocument @spsDoc OUTPUT, @strRequestXML

    -- Insert statements for procedure here

    BEGIN TRY

           

            BEGIN TRAN

            IF OBJECT_ID('tempdb..#TempEmployee') IS NOT NULL 

            DROP TABLE #TempEmployee

                                         

            SELECT      P.ID,

                        P.Name

            INTO  #TempEmployee          

            FROM  OPENXML(@spsDoc, 'XML/Employee/Items/Item')

            WITH 

            (

                  ID int  'ID',

                  Name [nvarchar] (50) 'Name'

            )AS P

                                               

            SELECT * FROM [#TempEmployee]

           

            INSERT INTO [dbo].[Employee]

            (

                  [ID],

                  [Name]

            )

            SELECT * FROM [#TempEmployee]

              

            COMMIT TRAN

            END TRY

            BEGIN CATCH

            SELECT

                  ERROR_NUMBER() AS ErrorNumber,

                  ERROR_SEVERITY() AS ErrorSeverity,

                  ERROR_STATE() AS ErrorState,

                  ERROR_PROCEDURE() AS ErrorProcedure,

                  ERROR_LINE() AS ErrorLine,

                  ERROR_MESSAGE() AS ErrorMessage;

            IF @@TRANCOUNT > 0

            ROLLBACK TRAN

        

      END CATCH

END