In this articel i will explain how we will save a record in the data base when the record
is sent in xml format from front end using stored procedure
First
take a new stored procedure. When we take a new stored procedure we will get
the following default document
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname,
ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1,sysname,@p1> <Datatype_For_Param1,int> = <Default_Value_For_Param1,
, 0>,
<@Param2,sysname,@p2><Datatype_For_Param2,
int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
Now
we will make changes to the given default document as per our requirements.
Let’s
name the stored procedure. We have to write the stored procedure name after the
command
CREATE PROCEDURE
We can give our own desired name. Then the stored procedure
would be created with that name.
CREATE PROCEDURE StoredProcedureName
As the data is received in the form of xml we need to declare
an xml variable and a flag to note its status.
@xmlParam xml,
@flag int
In order to use exceptional handling we will begin a try
block. The begin try block is written as
BEGIN TRY
Now we will declare another int variable.
DECLARE @hdoc AS INT
We will execute the preapred xml document in the following
way
EXEC sp_xml_preparedocument @hdoc
OUTPUT,@xmlparam
Now declare a temporary table with the required number of
columns and their data type.
DECLARE TempTable Table(ColumnName Datatype)
I have created a temporary table with column name as RoleName
and its datatype as nvarchar(150)
DECLARE @TemporaryTable TABLE(RoleName NVARCHAR(150))
Insert the xml column into the temporary table.
Here we need to give the dataset name that we have given in
the generated xml using C#.NET
Then the whole command is
INSERT INTO @TemporaryTable
SELECT * FROM OPENXML (@hdoc,'/DatasetName/Table1',2) WITH (RoleName NVARCHAR(150))
Once the data is stored in the temporary table we will remove
the xml document.
EXEC sp_xml_removedocument @hdoc
If the flag equals 1 i.e., the data is inserted into the
temporary table successfully, we will move to insert the data in the original
table.
IF @flag=1
Here we will begin the insertion of the record into the
original table.
BEGIN
Now we will insert the record into the original table, where
it is selected from the temporary table.
INSERT INTO tblRoleNames(EmployeeRoleName)
SELECT RoleName FROM @TemporaryTable
If the record is successfully inserted into the table we have
to send a success message to the front end which will be displayed in a dialog
box. For this we will select 1
SELECT 1
If there are any errors in the saving the record the catch
block will be called automatically and it returns a value -99 which shows that
the syatem has failed to store the given name.
BEGIN CATCH
SELECT -99
END CATCH
And finally we will close the begin that we have intiated at
the beginning of the stored procedure
END
Now will have a look at the entire stored procedure
USE
[DatabaseName]
GO
/****** Object:
StoredProcedure [dbo].[
StoredProcedureName] Script Date: 11/05/2013 12:11:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
--EXEC StoreProcdedureName
=============================================
CREATE PROCEDURE [dbo].[StoredProcedureName]
-- Add the parameters for the stored procedure here
@xmlParam xml,
@flag int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
DECLARE @hdoc AS INT
EXEC sp_xml_preparedocument @hdoc OUTPUT,@xmlparam
DECLARE @InsertRoleName TABLE(EmployeeRoleName NVARCHAR(150))
INSERT INTO
@InsertRoleName
SELECT * FROM OPENXML (@hdoc,'/DatasetName/Table1',2) WITH (EmployeeRoleName NVARCHAR(150))
EXEC sp_xml_removedocument @hdoc
IF @flag=1
BEGIN
INSERT INTO
tblRoleNames(EmployeeRoleName)
SELECT EmployeeRoleName FROM
@InsertRoleName
SELECT 1
END
END TRY
BEGIN CATCH
SELECT -99
END CATCH
END
After executing once the stored procedure with the given name
is saved in the database. Then change the command create to alter
ALTER PROCEDURE StoredProcedureName
No comments:
Post a Comment