Tech Programming: July 2013

Saturday, July 20, 2013

SQL Server Solve Error: Database diagram support objects cannot be installed because this database does not have a valid owner

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

You also like to know about:

Thursday, July 18, 2013

C Programming Questions and Answers, C Interview Questions, GATE previous year Questions in C Programming

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?

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

Wednesday, July 17, 2013

Programming and Data Structure

In this post, I am going to cover all the topics related to GATE and other technical interviews which comes under programming in C and Data Structure.
C is the most basic fundamental language that every CS/IT engineer should know. Also I would say everyone should know at least one computer programming language to understand this fastest growing digital world. Currently we are using many highly technical gadgets those are very user friendly, but one should know after how much effort that product is available to use by a totally non-technical person.
In this post I am going to cover most fundamental concepts of computer science those every Software Engineer should know.

Followings are the important topics related to Programming and Data Structure:

What is C Programming Language
Scope
Binding
Abstract Data Types
Array
Pointer
Stack
Queues
Functions
Recursion
Parameter Passing
Link List
Trees
Binary Search Tree
Binary Heap

What is C Programming Language

C is a programming language developed in 1972 by Dennis Ritchie in Bell Lab. C is reliable, simple, and easy to use. If you know C then you can quickly learn any programming language. No one can directly learn C++ or JAVA, so C is the first step for programming learner. Like a child can not start talking directly without learning words. So in simple language C is the letters and words and Grammar of programming languages.

We can compare C language with any spoken language for example:English

In English language we have:

Alphabets --> Words --> Sentences --> Paragraph

Similarly in C language we have

Alphabets, Digits, Special Symbol --> Constants, Variables, Keywords --> Instructions --> Programs

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:

Integer Constants
Real Constants (Fractional, exponential)
Characters Constants

Secondary constants are followings:

Array
Pointer
Structure
Union
Enum

Scope

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.

Inside a function or a block which is called local variables
Outside of all functions which is called global variables
In the definition of function parameters which is called parameters

Local Variables

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

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.

Function

Function ........

Study for GATE Exam and also Useful for Technical Interviews in IT Industry

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

Choose a topic
Study topic completely
Solve topic related questions from books
Solve topic related previous years GATE questions
Solve topic related questions from other material, if you have any

You can download GATE CS/IT syllabus from here

I am going to cover following topics here:

Programming and Data Structures

Algorithms: Analysis and Asymptotic notation

Worst and average case analysis

Design: Greedy approach, Dynamic programming

Divide-and-conquer

ER- model, Relational model, Database design

Query languages (SQL)

File structures (sequential files, indexing, B and B+ trees)

Transactions and concurrency control

Information Systems and Software Engineering

HTML

XML

Basic concepts of client-server computing

Digital Logic: Logic functions, Minimization

Design and synthesis of combinational and sequential circuits

Number representation and computer arithmetic (fixed and floating point)

Computer Networks: ISO/OSI stack

LAN technologies (Ethernet, Token ring)

Flow and error control techniques, Routing algorithms, Congestion control

TCP/UDP and sockets

IP(v4), Application layer protocols (icmp, dns, smtp, pop, ftp, http)

Basic concepts of hubs, switches, gateways, and routers

Network security basic concepts of public key and private key cryptography

Digital signature, firewalls

Operating System: Processes, Threads

Operating System:Inter-process communication

Operating System:Concurrency, Synchronization

Deadlock, CPU scheduling

Memory management and virtual memory

File systems

I/O systems

Protection and security

You can post a comment if you have any doubt or any question, I will try my best to response you back with solution

Monday, July 15, 2013

How to Create Condition and Policy in SQL Server 2012 / Create a Policy for Naming Convention of Stored Procedure

This article will cover most salient feature of SQL Server, Policy based administrative management of SQL Server to create a condition at server level.
In almost all of the projects we follow some standards for naming convention, so to avoid redundant naming of objects like stored procedure we can create a policy for naming convention.
I have been working with SQL Server from past one year and we always follow naming convention, so it will be better if admin create one policy for naming convention so that no developer can miss it.

Policies

Policy-Based Management is a new management feature introduced in SQL Server 2008. Policy-Based Management allows Database Administrators to define a set of policies that can control many different aspects of SQL Server. Policies can be applied to a single server or to groups of servers. For example, a Database Administrators could define a policy that specifies how a particular configuration option should be set on all the servers in the enterprise.
Before creating policy, first we will create one condition that will be used in policy

How to create Condition

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

Write any name in the Name field (for example: StoredProcNamingConvention)
Select ‘Stored Procedure’ in the Facet drop down list
Keep AndOr blank because this single condition
Select Operator LIKE in drop down list
Write rule in the value column enclosed with single quotes (for example: ‘usp%’)

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

How to create Policy

5. Now right click on Policy and click on New Policy

6. New Policy window will open, fill all the fields

Enter name of policy
Enable it by checking Enabled checkbox
Select condition created for naming convention of stored procedure
Select Evaluation Mode as On change: prevent
Server restriction as None

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

Monday, July 8, 2013

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

How to generate Insert Script of a table in SQL Server

Many times we need to generate SQL Script of table not only schema but with data also. SQL server provide this functionality inbuilt. TO generate SQL script of any object in a DB of SQL server follow below steps Generate Script for an object

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

Friday, July 5, 2013

How To Store Any File into SQL Database

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, 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

Thursday, July 4, 2013

How to fetch last row from a table Or How to fetch Second Last row from a SQL table

This is similar to my blog-post: How to fetch second row from a table in SQL Server
To fetch rows from last, first you have to query number of rows in your table then use this number in OFFSET FETCH clause
Consider I have a table with following Data

SELECT [ID]
      ,[Name]
      ,[Post]
  FROM [JPDB].[dbo].[Employee]

Result:

Below is the query to fetch Last row in a result set

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:

I am taking one more example, suppose I want last three rows
Below is the query

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:

One more trick to get last row from a table using OFFSET FETCH Clause
Use reverse order DESC in place of ASC
Below is the query

SELECT [ID]
      ,[Name]
      ,[Post]
  FROM [JPDB].[dbo].[Employee]
  ORDER BY [ID] DESC  OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY

Result

How to fetch second row from a table in SQL Server, Or How to retrieve specific row from a ordered result of SQL Query

SQL Server 2012 has many enhanced features, one is OFFSET FETCH clause. To fetch second row or any specific row from an ordered result of SQL query we can use this feature of SQL Server. We can fetch specific row from ordered result set using only single statement as I explained below.

OFFSET FETCH Clause

The OFFSET-FETCH clause provides us option to fetch only a window or page of results from the result set. The only limitation is, you can use this with ORDER BY clause only. That make sense actually, if we want second row of a result set it means some order should present from which we want second row
I have one table which has employee information as shown below

SELECT [ID]
      ,[Name]
      ,[Post]
  FROM [JPDB].[dbo].[Employee]

Result of above query is:

Below is the SQL script using OFFSET FETCH to get 2nd row of a result

 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
Result of above query:

Suppose I want 2nd and 3rd row then below is the query:

SELECT [ID]
      ,[Name]
      ,[Post]
  FROM [JPDB].[dbo].[Employee]
  ORDER BY [ID] ASC OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY

Result:

Wednesday, July 3, 2013

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:

<root>  
 <firstnode>  
   <insidefirstnode>Inside First Node</insidefirstnode>  
 </firstnode>  
 <secondnode>  
   <insidesecondnode1>Inside Second Node 1</insidesecondnode1>  
   <insidesecondnode2>inside Second Node 2</insidesecondnode2>  
 </secondnode>  
 <thirdnode>  
   <item id="0">Item 1</item>  
   <item id="1">Item 2</item>  
   <item id="2">Item 3</item>  
 </thirdnode>  
</root> 

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=
'<root>    
 <firstnode>    
   <insidefirstnode>Inside First Node</insidefirstnode>    
 </firstnode>    
 <secondnode>    
   <insidesecondnode1>Inside Second Node 1</insidesecondnode1>    
   <insidesecondnode2>inside Second Node 2</insidesecondnode2>    
 </secondnode>    
 <thirdnode>    
   <item id="0">Item 1</item>    
   <item id="1">Item 2</item>    
   <item id="2">Item 3</item>    
 </thirdnode>    
</root>'
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=
'<root>    
 <firstnode>    
   <insidefirstnode>Inside First Node</insidefirstnode>    
 </firstnode>    
 <secondnode>    
   <insidesecondnode1>Inside Second Node 1</insidesecondnode1>    
   <insidesecondnode2>inside Second Node 2</insidesecondnode2>    
 </secondnode>    
 <thirdnode>    
   <item id="0">Item 1</item>    
   <item id="1">Item 2</item>    
   <item id="2">Item 3</item>    
 </thirdnode>    
</root>'
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:

  <firstnode>    
   <insidefirstnode>Inside First Node</insidefirstnode>    
 </firstnode>    
 <secondnode>    
   <insidesecondnode1>Inside Second Node 1</insidesecondnode1>    
   <insidesecondnode2>inside Second Node 2</insidesecondnode2>    
 </secondnode>    
 <thirdnode>    
   <item id="0">Item 1</item>    
   <item id="1">Item 2</item>    
   <item id="2">Item 3</item>    
 </thirdnode>    
'
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=
'<root>    
 <firstnode>    
   <insidefirstnode>Inside First Node</insidefirstnode>    
 </firstnode>    
 <secondnode>    
   <insidesecondnode1>Inside Second Node 1</insidesecondnode1>    
   <insidesecondnode2>inside Second Node 2</insidesecondnode2>    
 </secondnode>    
 <thirdnode>    
   <item id="0">Item 1</item>    
   <item id="1">Item 2</item>    
   <item id="2">Item 3</item>    
 </thirdnode>    
</root>'
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:

<root>
 <firstnode>
   <insidefirstnode>Inside First Node</insidefirstnode>
 </firstnode>
 <secondnode>
   <insidesecondnode1>Inside Second Node 1</insidesecondnode1>
   <insidesecondnode2>inside Second Node 2</insidesecondnode2>
 </secondnode>
 <thirdnode>
   <item id="0">Item 1</item>
   <item id="1">Item 2</item>
   <item id="2">Item 3</item>
 </thirdnode>
</root>

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

1 2	`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

1	`@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’

Tuesday, July 2, 2013

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

Saturday, July 20, 2013

Thursday, July 18, 2013

Wednesday, July 17, 2013

Followings are the important topics related to Programming and Data Structure:

We can compare C language with any spoken language for example:English

Local Variables

Global Variables

I am going to cover following topics here:

Monday, July 15, 2013

Policies

How to create Condition

How to create Policy

Monday, July 8, 2013

Friday, July 5, 2013

Thursday, July 4, 2013

OFFSET FETCH Clause

Wednesday, July 3, 2013

nodes method in T-SQL

OPENXML in T-SQL

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

Tuesday, July 2, 2013

Translate