Wednesday, 20 September 2017

Convert JSON String to DataTable in ASP.Net

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data;
  4. using System.Text.RegularExpressions;

  5. public class ConvertJsonStringToDataTable
  6. {
  7.    public DataTable JsonStringToDataTable(string jsonString)
  8.    {
  9.       DataTable dt = new DataTable();
  10.       string[] jsonStringArray = Regex.Split(jsonString.Replace("[""").Replace("]",""), "},{");
  11.       List<string> ColumnsName = new List<string>();
  12.       foreach (string jSA in jsonStringArray)
  13.       {
  14.          string[] jsonStringData = Regex.Split(jSA.Replace("{""").Replace("}"""),",");
  15.          foreach (string ColumnsNameData in jsonStringData)
  16.          {
  17.             try
  18.             {
  19.                int idx = ColumnsNameData.IndexOf(":");
  20.                string ColumnsNameString = ColumnsNameData.Substring(0, idx - 1).Replace("\"""");
  21.                if (!ColumnsName.Contains(ColumnsNameString))
  22.                {
  23.                   ColumnsName.Add(ColumnsNameString);
  24.                }
  25.             }
  26.             catch (Exception ex)
  27.             {
  28.                throw new Exception(string.Format("Error Parsing Column Name : {0}", ColumnsNameData));
  29.             }
  30.          }
  31.          break;
  32.       }
  33.       foreach (string AddColumnName in ColumnsName)
  34.       {
  35.          dt.Columns.Add(AddColumnName);
  36.       }
  37.       foreach (string jSA in jsonStringArray)
  38.       {
  39.          string[] RowData = Regex.Split(jSA.Replace("{""").Replace("}"""), ",");
  40.          DataRow nr = dt.NewRow();
  41.          foreach (string rowData in RowData)
  42.          {
  43.             try
  44.             {
  45.                int idx = rowData.IndexOf(":");
  46.                string RowColumns = rowData.Substring(0, idx - 1).Replace("\"""");
  47.                string RowDataString = rowData.Substring(idx + 1).Replace("\"""");
  48.                nr[RowColumns] = RowDataString;
  49.             }
  50.             catch (Exception ex)
  51.             {
  52.                continue;
  53.             }
  54.          }
  55.          dt.Rows.Add(nr);
  56.       }
  57.       return dt;
  58.    }
  59. }


Wednesday, 13 September 2017

Resetting IDENTITY Seed in Table Variable

For a "normal" table, you would use: DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value]).
you cannot reseed a table variable

A workaround might be to use ROW_NUMBER() instead.

declare @a table (sno int identity,aa nvarchar(100))
insert into @a(aa) values ('testw'),('testwsfsf')

select row_number() OVER (ORDER BY sno) as sno,aa from @a

delete from @a
  
insert into @a(aa) values ('testw'),('testwsfsf')
select row_number() OVER (ORDER BY sno) as sno,aa from @a


Saturday, 19 August 2017

How to select alternate rows from a table in SQL Server

Alternate rows from a table:
CREATE TABLE STUDENTS(
      STUDENT_ID int NULL,
      [STUDENT_NAME] [varchar](50) NULL,
      DOB date NULL,
      DEPARTMENT_ID int NULL,
      DOJ date NULL
      )
GO   
INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID,DOJ) VALUES (1, N'ASHISH', '1990-10-28', 2, GETDATE())
INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID,DOJ) VALUES (2, N'MANISH', '1991-06-17', 1, GETDATE())
INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID,DOJ) VALUES (3, N'AMIT', '1992-06-30', 3, GETDATE())
INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID,DOJ) VALUES (4, N'PANKAJ', '1995-10-05', 4, GETDATE())
INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID,DOJ) VALUES (5, N'SUMIT', '1998-04-06', 5, GETDATE())





Method 1:
SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM(
SELECT ROW_NUMBER()OVER (ORDER BY STUDENT_ID)AS ROW,* FROM STUDENTS)
WHERE ROW%2=0



Method 2:
WITH CTE AS
(
SELECT ROW_NUMBER()OVER (ORDER BY STUDENT_ID)AS ROW,* FROM STUDENTS
)
SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM CTE WHERE ROW%2=0




Method 3:
SELECT  ROW_NUMBER()OVER (ORDER BY STUDENT_ID)AS ROW,* INTO_TEMP_STUDENTS FROM  STUDENTS
SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM_TEMP_STUDENTS WHERE  ROW%2=0



Method 4:
 SELECT IDENT=IDENTITY(int, 1,1),* into TEMP_STUDENTS  FROM STUDENTS
 SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM TEMP_STUDENTS         WHERE IDENT%2=0


Convert JSON String to DataTable in ASP.Net

using  System; using  System.Collections.Generic; using  System.Data; using  System.Text.RegularExpressions; public   class   Convert...