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#
Hello friend, In this post i would like share solution of one small problem, which occurs many times in SQL Server.
When we restore Database to any other server and try to create database diagram then it does not allow to create database diagram and its give error
Error:
Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects
Solution is very small. just execute below query:
Replace MYDB with your DB name then execute the query
ALTER AUTHORIZATION ON DATABASE::MYDB TO sa
After executing above query we can create DB diagram
Hello friend, In this post I am going to create a huge collection of all types of Questions with Answers. Wherever I will find any question that is important for GATE or any interview, I will post it here
For programming in C/ C++, you can use Dev C++ IDE, it is free IDE for C and C++ developer, Link to download
Q. What is the difference between the declaration and definition of a variable?
Ans: The definition is the one that actually allocate space, and provides an initialization value, if any. There can be many declaration, but there must be exactly one definition
Q. What is the difference between a statement and a block?
Ans: A statement is single C expression terminated with a semicolon. A block is a series of statements, the group of which is inclosed in curly braces.
Q. How to add two numbers without using arithmetic operators?
OR
Q. Write a function that returns sum of two integers. The function should not use any of the arithmetic operators (+, ++, -, --, .. etc).
Ans: We can not use arithmetic operator but we can use C Bitwise Operators, below is the function that will add two number without using arithmetic operators
int AddNumbers(int x, int y)
{
if (y == 0)
return x;
else
return AddNumbers( x ^ y, (x & y) << 1);
}
Q. How many squares does a chess-board contain?
Ans: Total number of squares = 8²+7²+...+1² = 204
This concept can be generalized to calculate number of squares in any N*N matrix
1² + 2² + 3² + ....n² = n(n+1)(2n+1)/6
Q. How many rectangles does a chess-board contain?
Ans: Total = [n(n+1)/2]^2
Here n is 8
Alphabets similar to English language, digits (0-9), special symbols like (~,!,@,#,$,%,^,&, etc.)
Constants are of two types: Primary constants and Secondary constants
Primary constants are followings:
Secondary constants are followings:
The scope is the context within the program in which an identifier/ variable is valid and can be resolved to find the entity associated to the identifier. A scope in any programming is a region of the program where a defined variable can have its existence and beyond that variable can not be accessed. There are three places where variables can be declared in C programming language.
Local variables are the variables declared inside a function or any block. They can be used only by statements that are inside that function or block of code. They can not be used outside that function or block in which they have been declared. For example in below code var1, var2, var3 are local variables
#include <stdio.h>
#include <conio.h>
/* declaration of global variable*/
int global_var;
int main(){
/* declaration of local variables */
int var1;
int var2;
int var3;
/* initialization of local variables */
var1 = 10;
var2 = 20;
var3 = var1 + var2;
global_var = var3 + 1;
printf ("Value of var1 = %d, var2 = %d and var3 = %d\n", var1, var2, var3);
printf ("Global variable global_var = %d\n", global_var);
system("pause");
return 0;
}
Global variables are defined outside of a function, usually on top of the program. The global variables will hold their value throughout the lifetime of your program and they can be accessed inside any of the functions defined for the program.
A global variable can be accessed by any function. That is, a global variable is available for use throughout your entire program after its declaration. In above example global_var is a global variable declared on top that can be used in any function.
Hello friends, I welcome you and appreciate you to prepare for GATE Exam. You have taken right decision of preparing for GATE exam, It will help you in two way, first you are preparing for GATE so you will get good score to take admission in better college like IITs, IIITs and NITs for better study, second if any you does not get good score in GATE, then you will surely get a good job if you were preparing seriously, so don't loose hope keep learning daily.
Just think about, How many engineering graduates does India produce each year? The answer is around 200,000. and now think how many jobs India produce per year in IT industry, the answer is maximum 15,000. So this analysis shows that if you want job you have to make yourself seen. You have to stand apart using your ability and knowledge.
I will try my best to help you scoring best in GATE exam or compete any Interview for a job, I am going to cover most of the CS/IT syllabus of GATE which is asked in most of technical interviews also. As per my experience best strategy to prepare for GATE is go topic wise
You can post a comment if you have any doubt or any question, I will try my best to response you back with solution
Following are the steps to create condition in SQL Server
1. Go to Management -> Conditions as show below
2. Right click on Condition and click on New Condition, a new window will appear as show below
3. Fill all the fields
Note: This rule is to start stored procedures with ‘usp’, if we want stored procedure to start with ‘usp_’ the value should be ‘usp[_]%’
4. After clicking on OK button condition will be created
5. Now right click on Policy and click on New Policy
6. New Policy window will open, fill all the fields
7. After Clicking on OK button policy will be created
8. Now if we execute following script to create stored procedure ‘sp_MyStoredProc’
CREATE PROCEDURE [dbo].[spMyStoredproc] AS BEGIN SELECT * FROM [dbo].[APIConfiguration] END
Message from SQL Server for violating policy:
Policy 'StoredProcNamingConventionPolicy' has been violated by 'SQLSERVER:\SQL\ABCServer\DEFAULT\Databases\JPDB\StoredProcedures\dbo.spMyStoredproc'. This transaction will be rolled back. Policy condition: '@Name LIKE 'usp%'' Policy description: '' Additional help: '' : '' Statement: 'CREATE PROCEDURE [dbo].[spMyStoredproc] AS BEGIN SELECT * FROM [dbo].[APIConfiguration] END'.Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 65 The transaction ended in the trigger. The batch has been aborted.
9. But if we write stored procedure name which start with ‘usp’, then script will be executed successfully
CREATE PROCEDURE [dbo].[uspMyStoredproc] AS BEGIN SELECT * FROM [dbo].[APIConfiguration] END
Message
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
Follow below steps to generate script of table with data
Right click on DB -> Task -> GenerateScripts
A new Window will open as shown below, then click on 'Next' button
After clicking on Next button, a new wizard will open for selecting object for which you want to create script, select second radio button for selecting specific database object and then select the check-box of your table as shown below
After clicking on 'Next' button, Click on 'Advance' button
In the advance option change general settings of 'Types of data to script' -> Schema and data and then click on Ok button
After clicking on Ok button, you can change directory path where you want to store your script and then click on 'Next' button
Click on 'Next' button
Click finish button, your script will be stored in the given location
Open the script in SQL Server
Hello friends, I am posting a solution to store any file in SQL server table and use it any where in an application as downloadable file
using OPENROWSET you can insert a file data in binary format to SQL table
Lets take one example to understand completely
One simple scenario is to just store the file in table and read from it from an application/ website in WCF or .NET
Create one table with a column as varbinay type
CREATE TABLE [dbo].[FileTable]( [FileName] [nvarchar](50) NOT NULL, [FileData] [varbinary](max) NOT NULL ) GO
Using above query table will be created
Now to store file in this table use below SQL query
DECLARE @Data varbinary(MAX) = (SELECT * FROM OPENROWSET(BULK N'D:\JP\Bloger\PDFFile.pdf',SINGLE_BLOB) AS AnyFile);
INSERT INTO [JPDB].[dbo].[FileTable]
VALUES('PDFFile.pdf',@Data)
Above SQL Query will store binary data of file: 'D:\JP\Bloger\PDFFile.pdf' in the column 'FileData' of table 'FileTable'
Now if we execute below query
SELECT [FileName]
,[FileData]
FROM [JPDB].[dbo].[FileTable]
We will get the result
This table can be used in any application to download this stored file easily
For example in C# you can use Response.BinaryWrite to download the file from SQL DB
I would like to explain one more thing here as per my experience in IT industry, many projects does not allow varbinary cloumn data type in a table
In these type of cases, we can insert data existing schema of database in varchar type column
To insert file data in a varchar type column we can use same approach as explained above
Suppose we have one table with following schema
To insert file data in this table having column varchar type, we need two steps:
First insert binary data in a temporary Table in varbinary column using below query
DECLARE @Data varbinary(MAX) = (SELECT * FROM OPENROWSET(BULK N'D:\JP\Bloger\PDFFile.pdf',SINGLE_BLOB) AS AnyFile);
SELECT 'PDFFile.pdf' AS [FileName],@Data AS [FileData]
INTO [JPDB].[dbo].[temporaryTable]
SELECT [FileName]
,[FileData]
FROM [JPDB].[dbo].[temporaryTable]
Above query will insert file data in table 'temporaryTable'
Now in second step copy this binary data of file in the table which has varchar column data type
Best way to insert binary data (actually this is hexadecimal string) in varchar type column is, generate sql script from temporaryTable
Follow below steps to generate script of table with data. Also you can refer my blog "How to generate Insert Script of a table in SQL Server"
Right click on DB -> Task -> GenerateScripts
A new Window will open as shown below, then click on 'Next' button
After clicking on Next button, a new wizard will open for selecting object for which you want to create script, select second radio button for selecting specific database object and then select the check-box of your table as shown below
After clicking on 'Next' button, Click on 'Advance' button
In the advance option change general settings of 'Types of data to script' -> Schema and data and then click on Ok button
After clicking on Ok button, you can change directory path where you want to store your script and then click on 'Next' button
Click on 'Next' button
Click finish button, your script will be stored in the given location
Open the script in SQL Server
Copy the hex string as highlighted in the above image to insert in the table which has column of varchar type, as shown in below query
SELECT TOP 1000 [FileName]
,[FileData]
FROM [JPDB].[dbo].[FileTable2]
Now table 'FileTable2' has PDF file data in hexadecimal string, we can convert hexadecimal string into Byte to read this file in C# or any other language
SQL Server 2012 provide better functionality to store files in DB with File Table, to see more detail refer msdn
SELECT [ID]
,[Name]
,[Post]
FROM [JPDB].[dbo].[Employee]
Result:
DECLARE @Total [int]
SET @Total= (SELECT COUNT(*) FROM [JPDB].[dbo].[Employee])
SELECT [ID]
,[Name]
,[Post]
FROM [JPDB].[dbo].[Employee]
ORDER BY [ID] ASC OFFSET (@Total-1) ROWS FETCH NEXT 1 ROWS ONLY
Result:
DECLARE @Total [int]
SET @Total= (SELECT COUNT(*) FROM [JPDB].[dbo].[Employee])
SELECT [ID]
,[Name]
,[Post]
FROM [JPDB].[dbo].[Employee]
ORDER BY [ID] ASC OFFSET (@Total-3) ROWS FETCH NEXT 3 ROWS ONLY
Result:
SELECT [ID]
,[Name]
,[Post]
FROM [JPDB].[dbo].[Employee]
ORDER BY [ID] DESC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
Result
SELECT [ID]
,[Name]
,[Post]
FROM [JPDB].[dbo].[Employee]
Result of above query is:
SELECT [ID]
,[Name]
,[Post]
FROM [JPDB].[dbo].[Employee]
ORDER BY [ID] ASC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY
The number I have written after OFFSET skips rows, and the number written after NEXT get total number of rows
SELECT [ID]
,[Name]
,[Post]
FROM [JPDB].[dbo].[Employee]
ORDER BY [ID] ASC OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY
Result:
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 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= '' SELECT T.c.query('text()') AS result FROM @x.nodes('/root/secondnode/insidesecondnode1') T(c) GO Inside First Node Inside Second Node 1 inside Second Node 2 - Item 1
- Item 2
- Item 3
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= '' SELECT T.c.query('text()') AS result FROM @x.nodes('/root/thirdnode/item') T(c) GO Inside First Node Inside Second Node 1 inside Second Node 2 - Item 1
- Item 2
- Item 3
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 ' SELECT T.c.value('@id','int') ID ,T.c.query('text()') AS result FROM @x.nodes('/root/thirdnode/item[2]') T(c) GO - Item 1
- Item 2
- Item 3
In above query line 18, by appending with [number] will read only that specific node, in this case second node
Result of above query:
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#
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
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:XmlDocument xmldoc = new XmlDocument(); xmldoc.Load(@"my.xml");
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
@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’
