SQL(Insert with XML into SQL through C#)

//C#

/* Note: This is the simple example to insert data into database with xml. This will help the database server to handle multiple request at a time.*/

using System.Xml;

DALayer layer = new DALayer();

SqlConnection cn = layer.ReturnConnection();

SqlTransaction trans = null;

SqlDataReader reader = null;

try

{

    XmlDocument xmldoc = new XmlDocument();

    XmlElement doc = xmldoc.CreateElement("doc");

    xmldoc.AppendChild(doc);

    string[] arrValues = new string[] { "one", "two", "three", "four", "five" };

    for (int i = 0; i < arrValues.Length; i++)

    {

       XmlElement item = xmldoc.CreateElement("item");

       doc.AppendChild(item);

                    item.AppendChild(xmldoc.CreateElement("ColumnNameasPerDatabase")).InnerText = arrValues[i].ToString();

     }

     trans = cn.BeginTransaction();

     SqlCommand command = new SqlCommand("sp_ProcedureName", cn);

     command.CommandType = CommandType.StoredProcedure;

command.Parameters.AddWithValue("@LoginId", long.Parse(“1”));

     command.Parameters.AddWithValue("@doc", xmldoc.OuterXml);

     command.Transaction = trans;

     reader = command.ExecuteReader();

      

    int OrderId = 0;

    while (reader.Read())

    {

         OrderId = long.Parse(reader["OrderId"].ToString());

    }

      

    reader.Close();

    trans.Commit();

    if (OrderId > 0)

    {

//Query Performed Sucessfully
return true;

    }

    else

    {

        throw new Exception("Query Failed ");

    }

}
catch (Exception ex)
{
     if (trans != null)
     {
         trans.Rollback();
     }
     if (reader != null)
     {
          if (!reader.IsClosed) reader.Close();
     }
     return false;
 }

 Finally
 {
       cn.Close();
       cn.Dispose();
 }

-- SQL

-- The StoreProcedure with XML regarding to the example
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
Go

ALTER PROCEDURE [dbo].[sp_ProcedureName]
(@LoginId   bigint,
@doc varchar(max))

AS

DECLARE
@id bigint, @hdoc int,
@StatusN varchar(1), @StatusC varchar(1), @Amount decimal

SELECT @Amount=0, @StatusN='1', @StatusC='A'

INSERT INTO OrderMaster(LoginID) VALUES(@LoginId)

-- Get The Auto IncrementId into @id
set @id = scope_identity()

EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

-- Column Name as per Table to insert into database from XML
SELECT @id id, x.ColumnNameasPerDatabase

into #tmpTable
    FROM OPENXML ( @hdoc, '/doc/item', 2 )
 WITH (
          -- Comma seprated multiple colums
          COLUMNNAMEASPERDATABASE DataType 'ColumnNameasPerDatabase(s))'
      ) AS x

-- Release XML handle
EXEC sp_xml_removedocument @hdoc

--Detail Insert Query
INSERT INTO DetailTable(ColumnNameasPerDatabase(s))
SELECT ColumnNameasPerDatabase(s) FROM #tmpTable

--Return The OrderId If The Query Inserted Sucessfully!
SELECT @id OrderId