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

Featured Post

Protect an API by using OAuth 2.0 with Azure Active Directory and API Management

Protect an API by using OAuth 2.0 with Azure Active Directory and API Management https://docs.microsoft.com/en-us/azure/api-management/api-...

Popular posts