Convert html table to dataset

XmlDocument doc = new XmlDocument();

doc.LoadXml(@"<table border='1' cellpadding='0' cellspacing='0'>

<tr>

<td width='50%'>cell 1a</td>

<td width='50%'>cell 1b</td>

</tr>

<tr>

<td width='50%'>cell 2a</td>

<td width='50%'>cell 2b</td>

</tr>

</table>");

DataTable dt = new DataTable();

dt.Columns.Add("Col1");

dt.Columns.Add("Col2");

foreach (XmlNode ndRow in doc.DocumentElement.ChildNodes)

{

DataRow dr = dt.NewRow();

for (int colIndex = 0; colIndex < ndRow.ChildNodes.Count; colIndex++)

dr[colIndex] = ndRow.ChildNodes[colIndex].InnerText;

dt.Rows.Add(dr);

}

}

Sorting and limiting datetime rows in datatable using linq

  private DataTable SortAndLimitRowsDateWise(DataTable dtTable, int datepositionintable, int returnrowscount)

    {

        DataTable dtReturnTable = new DataTable();

        DataTable dtMergeTable = new DataTable();

        if (returnrowscount <= dtTable.Rows.Count)

        {

            //ordering datewise the fields, it will convert automatically the datefields

            EnumerableRowCollection<DataRow> query = from order in dtTable.AsEnumerable()

                                                     orderby Convert.ToDateTime(order.Field<String>(datepositionintable))

                                                     select order;

            DataView view = query.AsDataView();

            dtMergeTable = view.ToTable();

            //selecting return rows count

            var dtTrec = from item in dtMergeTable.AsEnumerable()

                         select item;

            var topN = dtTrec.Take(returnrowscount);

            dtReturnTable = dtMergeTable.Clone();

            foreach (DataRow drrow in topN.ToArray())

            {

                dtReturnTable.ImportRow(drrow);

            }

        }

        return dtReturnTable;

    }

Update stored procedure for multi value parameter

Create procedure SP_UpdateMultiValueParameter

(

@condition_field_commasplitted nvarchar(max)

)

as

begin

update table_name set [update-field]='value' where condition_field in (select * from dbo.fnSplit(@condition_field_commasplitted,',')) 

--Use custom split function available in this same blog

end

Split function for SQL stored procedure

Create FUNCTION [dbo].[fnSplit](

    @sInputList VARCHAR(8000) -- List of delimited items

  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items

) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN

DECLARE @sItem VARCHAR(8000)

WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0

 BEGIN

 SELECT

  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),

  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0

  INSERT INTO @List SELECT @sItem

 END

IF LEN(@sInputList) > 0

 INSERT INTO @List SELECT @sInputList -- Put the last item in

RETURN

END