- using System;
- using System.Collections.Generic;
- using System.Data;
- using System.Text.RegularExpressions;
- public class ConvertJsonStringToDataTable
- {
- public DataTable JsonStringToDataTable(string jsonString)
- {
- DataTable dt = new DataTable();
- string[] jsonStringArray = Regex.Split(jsonString.Replace("[", "").Replace("]",""), "},{");
- List<string> ColumnsName = new List<string>();
- foreach (string jSA in jsonStringArray)
- {
- string[] jsonStringData = Regex.Split(jSA.Replace("{", "").Replace("}", ""),",");
- foreach (string ColumnsNameData in jsonStringData)
- {
- try
- {
- int idx = ColumnsNameData.IndexOf(":");
- string ColumnsNameString = ColumnsNameData.Substring(0, idx - 1).Replace("\"", "");
- if (!ColumnsName.Contains(ColumnsNameString))
- {
- ColumnsName.Add(ColumnsNameString);
- }
- }
- catch (Exception ex)
- {
- throw new Exception(string.Format("Error Parsing Column Name : {0}", ColumnsNameData));
- }
- }
- break;
- }
- foreach (string AddColumnName in ColumnsName)
- {
- dt.Columns.Add(AddColumnName);
- }
- foreach (string jSA in jsonStringArray)
- {
- string[] RowData = Regex.Split(jSA.Replace("{", "").Replace("}", ""), ",");
- DataRow nr = dt.NewRow();
- foreach (string rowData in RowData)
- {
- try
- {
- int idx = rowData.IndexOf(":");
- string RowColumns = rowData.Substring(0, idx - 1).Replace("\"", "");
- string RowDataString = rowData.Substring(idx + 1).Replace("\"", "");
- nr[RowColumns] = RowDataString;
- }
- catch (Exception ex)
- {
- continue;
- }
- }
- dt.Rows.Add(nr);
- }
- return dt;
- }
- }
Wednesday 20 September 2017
Convert JSON String to DataTable in ASP.Net
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
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)
A 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
Subscribe to:
Posts (Atom)
Upload valid file in C#
protected bool CheckFileExtandLength(HttpPostedFile HtmlDocFile) { try { Dictionary<string, byte[]>...
-
CREATE TAblE #temp ( T_Name VARCHAR(50), T_Times BIGINT ) INSERT INTO #temp(T_Name,T_Times) VALUES ('ASHISH',4) IN...
-
Step 1: Open URL: https://portal.azure.com/#home Step2 : Click Manage Azure Active Director Step3: Click Enterprise Application Step 4: Ne...
-
Injection Injection flaws, such as SQL injection, LDAP injection, and CRLF injection, occur when an attacker sends untrusted data to an ...