Friday, January 23, 2015

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

No comments:

Post a Comment