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