How to reconfigure JSON length or size in C# / ASP.Net

It is very easy to reconfigure the max JSON size, just we need to include one line code as shown below

JavaScriptSerializer js = new JavaScriptSerializer();
js.MaxJsonLength = int.MaxValue;

Int.MaxValue is 2147483647, which is equivalent to 4 GB, We can simply assign one integer value to JavaScriptSerializer.MaxJsonLength, in above case max value of integer is assigned

There is one more alternative to do this, we can configure the max length in the system.web.extensions configuration section in web.config. The example below sets the maxJsonLength value to 500000 characters

Note: Also keep in mind that the maxJsonLength in the web.config file automatically overrides the JavaScriptSerializer.MaxJsonLength property

Also see post: How to Convert Hexadecimal String to Byte Array in C#

How to Convert Hexadecimal String to Byte Array in C#

We can convert a character to byte in C#, we can also convert a string to byte array using Encoding.ASCII.GetBytes

But if we want to convert a hexadecimal string to byte array then there is no inbuilt method in C#.

We can achieve this in C# by reading characters in the hexadecimal string

Below is a method "HexToByte" which take string as input and return byte array

private static byte[] HexToByte(string hexString)
        {
            int lengthCount = 0;

            // offset value is 2 for removing first two characters '0x' from hexadecimal string
            int offset = 2;
            int byteLength = 0;

            // byte array length will be half of hexadecimal string length
            byte[] bytes = new byte[(hexString.Length - offset) / 2];
            byteLength = bytes.Length;
            for (lengthCount = 0; lengthCount < byteLength; lengthCount++)
            {
                // Adding two nybble from hexadecimal string to create one byte
                bytes[lengthCount] = (byte)((int.Parse(hexString[offset].ToString(), System.Globalization.NumberStyles.HexNumber, CultureInfo.InvariantCulture) << 4) | int.Parse(hexString[offset + 1].ToString(), System.Globalization.NumberStyles.HexNumber, CultureInfo.InvariantCulture));
                offset += 2;
            }
            
            return bytes;
        }

Basic concept of above code is read two characters from hex string and create one byte, because one hexadecimal character is nibble, so to create a byte we can add two nibble

It is useful when you store a file in SQL table and column type is varchar as we have seen in post: How To Store Any File into SQL Database

Using above method "HexToByte" we can convert file stored in sql table of varchar type column in byte array, which can be used to read stored file

XML Parser in SQL Server (T-SQL), How to parse XML in SQL

There are many cases when we want to read XML file and store data in SQL Server table

Suppose we have XML file as show below:

  
   
   Inside First Node  
   
   
   Inside Second Node 1  
   inside Second Node 2  
   
   
   Item 1  
   Item 2  
   Item 3  
   
 

There are two options to parse XML in T-SQL

1- nodes method msdn link

2- OPENXML msdn link

nodes method in T-SQL

nodes method is useful when you want to read a node data from a XML, Lets take an example for above xml

If I want to read data from a specific node 'insidesecondnode1' from above xml file

DECLARE @x xml 
SET @x=
'    
     
   Inside First Node    
     
     
   Inside Second Node 1    
   inside Second Node 2    
     
     
   Item 1    
   Item 2    
   Item 3    
     
'
SELECT T.c.query('text()') AS result
FROM   @x.nodes('/root/secondnode/insidesecondnode1') T(c)
GO

Result for above query is as shown below

In the line 18, what ever path you will give this query will result the data inside the node

If a node is present multiple time in xml for example 'item' node inside 'thirdnode' in above xml, then this query will fetch data from all the nodes

DECLARE @x xml 
SET @x=
'    
     
   Inside First Node    
     
     
   Inside Second Node 1    
   inside Second Node 2    
     
     
   Item 1    
   Item 2    
   Item 3    
     
'
SELECT T.c.query('text()') AS result
FROM   @x.nodes('/root/thirdnode/item') T(c)
GO

Result of above query:

If you want to fetch particular node in above case and id value also, then use below query:

      
   Inside First Node    
     
     
   Inside Second Node 1    
   inside Second Node 2    
     
     
   Item 1    
   Item 2    
   Item 3    
     
'
SELECT T.c.value('@id','int') ID
       ,T.c.query('text()') AS result
FROM   @x.nodes('/root/thirdnode/item[2]') T(c)
GO

In above query line 18, by appending with [number] will read only that specific node, in this case second node

Result of above query:

OPENXML in T-SQL

In nodes method there is one restriction, you can not pass SQL parameter in nodes method

Suppose you have many keys stored in one table as shown below

We can iterate over all the keys using this table to fetch corresponding data from xml file and store to another table as simple text

We can do this by applying a loop and passing all the keys stores in table

SQL Query to implement this approach is as shown below:

DECLARE @hdoc [int]
DECLARE @Key [varchar](1000)
DECLARE @XMLDoc [XML]
DECLARE @NumberOfKeys [int]
DECLARE @Counter [int] = 0;
DECLARE @Value [varchar](2000);

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempTable]') AND TYPE IN (N'U'))
  BEGIN
   DROP TABLE [dbo].[TempTable]
  END
  -- Creating temp table to store values parsed from XML
  CREATE TABLE [dbo].[TempTable](
      [ID] [int],
   [Key] [varchar](2000),
   [Value] [varchar](2000)
  ) 
SET @XMLDoc=
'    
     
   Inside First Node    
     
     
   Inside Second Node 1    
   inside Second Node 2    
     
     
   Item 1    
   Item 2    
   Item 3    
     
'
SET @NumberOfKeys= (SELECT COUNT([Key]) FROM [JPDB].[dbo].[XMLKeysTable]);

EXEC sp_xml_preparedocument @hdoc OUTPUT, @XMLDoc

 WHILE(@Counter < @NumberOfKeys)
 BEGIN
   SELECT @Key = [Key] FROM [JPDB].[dbo].[XMLKeysTable] ORDER BY [Key] OFFSET @Counter ROWS FETCH NEXT 1 ROWS ONLY
   SELECT @Value = Value FROM OPENXML (@hdoc, @Key,1) WITH (Value [varchar](1000) 'text()[1]')
   INSERT INTO [dbo].[TempTable] 
   VALUES(@Counter, @Key, @Value)
   SET @Counter += 1;
  END

--Removes the internal representation of the XML document specified by the document handle and invalidates the document handle
EXEC sp_xml_removedocument @hdoc

SELECT * FROM [dbo].[TempTable]

Result of above query:

You can also refer my blog: Pass XML file in stored procedure as a input parameter from C#

Pass XML file in stored procedure as a input parameter from C#

Using xml data type as input parameter of stored procedure, you can store XML documents and fragments in a SQL Server database

Suppose we have XML file as show below:

 
   Inside First Node
 
 
   Inside Second Node 1
   inside Second Node 2
 
 
   Item 1
   Item 2
   Item 3
 

      We can store above XML file using C# code snippet by passing XML file in a stored procedure as shown below

 static void Main(string[] args)
        {
            string SProc = "dbo.usp_XMLParser";
            string ConnectonString = @"Data Source=JITENDRAPAL\SQLEXPRESS;Initial Catalog=JPDB;Integrated Security=True;";
            XmlDocument xmldoc = new XmlDocument();
            xmldoc.Load(@"my.xml");
            using (SqlConnection sqlConnection = new SqlConnection(ConnectonString))
            {
                sqlConnection.Open();

                using (SqlCommand sqlCommand = new SqlCommand(SProc, sqlConnection))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(
                          new SqlParameter("@XMLFile", SqlDbType.Xml)
                          {
                              Value = new SqlXml(new XmlTextReader(xmldoc.InnerXml, XmlNodeType.Document, null))
                          });
                    using (DataTable dataTable = new DataTable())
                    {
                        dataTable.Locale = CultureInfo.InvariantCulture;
                        using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter())
                        {
                            sqlDataAdapter.SelectCommand = sqlCommand;
                            sqlDataAdapter.Fill(dataTable);
                        }
                        Console.WriteLine(dataTable.Rows[0]["Col1"].ToString());
                        Console.Read();
                    }
                }
            }

            Console.Read();
        }

In above code:

a.     My.xml is xml file as shown in point 1

b.     We can load xml file using XMLDocument Load method as shown below

 XmlDocument xmldoc = new XmlDocument();
 xmldoc.Load(@"my.xml");

c.     Pass xml file using SQLParameter to the stored procedure as shown below

 sqlCommand.Parameters.Add(
                          new SqlParameter("@XMLFile", SqlDbType.Xml)
                          {
                              Value = new SqlXml(new XmlTextReader(xmldoc.InnerXml, XmlNodeType.Document, null))
                          });

Stored procedure ‘usp_XMLParser’ takes XML file as input and store XML file in SQL table

SQL Script of stored procedure is as shown below:

 USE [JPDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_XMLParser] @XMLFile XML
AS
BEGIN
DECLARE @hdoc [int]
DECLARE @ID [int]
DECLARE @Key [varchar](1000)
DECLARE @NumberOfKeys [int]
DECLARE @Counter [int] = 0;
DECLARE @Value [varchar](2000);

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[XMLDataTable]') AND TYPE IN (N'U'))
                   BEGIN
                             DROP TABLE [dbo].[XMLDataTable]
                   END
                   -- Creating table
                   CREATE TABLE [dbo].[XMLDataTable](
            [ID] [int]
                       ,[Key] [varchar](1000)
                             ,[Value] [varchar](2000)
                   )

SET @NumberOfKeys= (SELECT COUNT([Key]) FROM [JPDB].[dbo].[XMLKeysTable]);

EXEC sp_xml_preparedocument @hdoc OUTPUT, @XMLFile

 WHILE(@Counter < @NumberOfKeys)
 BEGIN
   SELECT @ID= [ID], @Key = [Key] FROM [JPDB].[dbo].[XMLKeysTable] ORDER BY [Key] OFFSET @Counter ROWS FETCH NEXT 1 ROWS ONLY
   SELECT @Value = Value FROM OPENXML (@hdoc, @Key,1) WITH (Value [varchar](1000) 'text()[1]')
   INSERT INTO [dbo].[XMLDataTable]
   VALUES(@ID, @Key, @Value)
   SET @Counter += 1;
  END

--Removes the internal representation of the XML document specified by the document handle and invalidates the document handle
EXEC sp_xml_removedocument @hdoc

SELECT 'true' AS [Col1] 
END

·         Above stored procedure takes XML file as input using XML type input parameter

@XMLFile XML

I am storing XML file in table ‘dbo.XMLDataTable’

To know more about XML parsing in T-SQL and explanation of above stored procedure refer my next post ‘XML Parser in SQL Server’

Add/ Install JSCop in Visual Studio and How to Run JSCop in Visual Studio for JS files

JSCop: JSCop is a code analysis tool for java script. We can integrate JSCop in Visual Studio to improve code quality of java script when writing the code in Visual Studio.

Steps to add JSCop in Visual Studio Professional 2012

• Open Visual Studio
• Go to Tools -> External Tools (When you click on External Tools a new window will open as shown below)

• Click on 'Add' button

• Below screen will appear

• Fill values as shown in below image and click 'OK'
• You can enter any name in Title which you want for JSCop, I am writing just JSCop
• In Command text box write complete path of js50.exe 
• Arguments:  /file:$(ItemPath)
• Initial directory: Complete path till bin folder

• JSCop option will now appear in 'Tools' menu

How to run JSCop

• Open JS file (JSCop will run only on JS file i.e. file with extension JS. It will not run on HTML, ASPX, etc.)
• Select Tools -> JSCop
• See output window for warnings
• JSCop option should now be available in ‘Tools’ menu