Tuesday, 8 September 2015

Add dynamic DataTable Columns , Rows data

============c sharp code====================
  DataTable _dt1 = new DataTable();
        DataColumn col11 = new DataColumn("InvestmentObject");
        col11.DataType = System.Type.GetType("System.Int32");
//System.Type.GetType("System.String");
        _dt.Columns.Add(col11);

        int I;
        for (I = 0; I <= ddlInvestmentObject.Items.Count - 1; I++)
        {
            if (ddlInvestmentObject.Items[I].Selected)
            {
                DataRow researchRow1 = _dt1.NewRow();
                researchRow[col11] = Convert.ToInt32(ddlInvestmentObject.Items[I].Value);
                _dt1.Rows.Add(researchRow1);
            }
        }
        _admin.strInvestmentObject = GetXMLOfDataTable(_dt1);
================Function create here===================
 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;
        }

===============DATA Query=======================
Decalre @xmlLanguage xml
DELETE FROM tb_InvestmentObject_SchemeMaster_Mapping WHERE Scheme_ID = @Scheme_ID
SELECT
ISNULL(cast(Colx.query('data(InvestmentObject)') as varchar(max)),'0') as [InvestmentObject]
INTO #TMPLanguage2 FROM @InvestmentObject.nodes('DocumentElement/Table') AS Tabx(Colx)
insert into tb_InvestmentObject_SchemeMaster_Mapping([Scheme_Id],[InvestmentObject_Id])
select @Scheme_ID,[InvestmentObject] from #TMPLanguage2