Tuesday, 4 June 2013

Xml DataTable dump SQL SP

 @xmlLanguage xml  

SET ARITHABORT ON

if (@Action='insertDownloadCenterDetails')
                Begin
                           insert into DownloadCenter(Title,UploadFile)values(@Title,@UploadFile)
                           set @DownloadCenterId=@@identity
                 end
                   Begin
                       DELETE FROM DownloadCenterDetails WHERE fkDownloadCenterId = @DownloadCenterId
                   end                 
      
            SELECT  
                ISNULL(cast(Colx.query('data(fkTabId)') as varchar(max)),'0') as fkTabId,
                ISNULL(cast(Colx.query('data(fkSubTabId)') as varchar(max)),'0') as fkSubTabId
                INTO #TMPLanguage1 FROM @xmlLanguage.nodes('DocumentElement/Table') AS Tabx(Colx)
                insert into DownloadCenterDetails(fkDownloadCenterId,fkTabId,fkSubTabId)
                select @DownloadCenterId,fkTabId, fkSubTabId from #TMPLanguage1
 =================================================================

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;

public partial class Xml_DataTable_dump_SQL_SP_code : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DataTable _dt = new DataTable();

        DataColumn col1 = new DataColumn("fkTabId");
        DataColumn col2 = new DataColumn("fkSubTabId");


        col1.DataType = System.Type.GetType("System.Int32");
        col2.DataType = System.Type.GetType("System.Int32");


        _dt.Columns.Add(col1);
        _dt.Columns.Add(col2);


        //fore each loop while for etc
        _dt.Rows.Add("", "");

        //_DownloadCenter.strxmlData = CommonFunctions.GetXMLOfDataTable(_dt);
        //_DownloadCenter.InsertDownloadCenterDetails();
        //_DownloadCenter = null;
    }
    public bool InsertDownloadCenterDetails()
    {
        bool flag = false;
        try
        {

            //obj = _accessData.GetObject();
            //obj[0] = "insertDownloadCenterDetails";
            //obj[1] = _DownloadCenterId;
            //obj[2] = _TabId;
            //obj[3] = _SubTabId;
            //obj[6] = _Title;
            //obj[7] = _UploadFile;
            //obj[12] = strxmlData;
            //flag = _accessData.executeStatement(obj);

            return flag;
        }
        catch (Exception ex)
        {
            throw (ex);
        }
    }
    public static string GetXMLOfDataTable(DataTable dtToProcess)
    {
        string strOutput = "";
        try
        {
            dtToProcess.TableName = "Table";

            StringWriter sw = new StringWriter();

            dtToProcess.WriteXml(sw);
            strOutput = sw.ToString();
        }
        catch (Exception ex)
        {
        }


        return strOutput;
    }
}

No comments:

Post a Comment