Contact Me

Nirav Prabtani

Mobile : +91 738 308 2188

Email : niravjprabtani@gmail.com

Nirav Prabtani

Showing posts with label MyTricks. Show all posts
Showing posts with label MyTricks. Show all posts

Monday 21 April 2014

Bind Data to Gridview using Stored procedure


How to Convert JSON String into Dataset using c#



We can easily convert json string into Dataset using c#
To use that ,you must have to doenload  Json.NET
Json.NET is a popular high-performance JSON framework for .NET
To install Json.NET, run the following command in the Package Manager Console


 DataSet dsRecognize = new DataSet();
                    string jsonStringRecognize = "Json String";
                    XmlDocument xdRecognize = new XmlDocument();
                    jsonStringRecognize = "{ \"rootNode\": {" + jsonStringRecognize.Trim().TrimStart('{').TrimEnd('}') + "} }";
                    xdRecognize = (XmlDocument)JsonConvert.DeserializeXmlNode(jsonStringRecognize);

                    dsRecognize.ReadXml(new XmlNodeReader(xdRecognize));


Convert DataTable to XML using c#


We can convert DataTable to XML using c# in this way... :)


public string ConvertDatatableToXML(DataTable dt)
{
MemoryStream str = new MemoryStream();
dt.WriteXml(str, true);
str.Seek(0, SeekOrigin.Begin);
StreamReader sr = new StreamReader(str);
string xmlstr;
xmlstr = sr.ReadToEnd();
return (xmlstr);
}

Types of join in sql server

Introduction

In this tip, I am going to explain about types of join.

What is join??

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.
There are many types of join.
  • Inner Join
    1. Equi-join
    2. Natural Join
  • Outer Join
    1. Left outer Join
    2. Right outer join
    3. Full outer join
  • Cross Join
  • Self Join

Using the Code

Join is very useful to fetching records from multiple tables with reference to common column between them.
To understand join with example, we have to create two tables in SQL Server database.
  1. Employee
    create table Employee(
     
    id int identity(1,1) primary key,
    Username varchar(50),
    FirstName varchar(50),
    LastName varchar(50),
    DepartID int
     
    ) 
  2. Departments
  3. create table Departments(
     
    id int identity(1,1) primary key,
    DepartmentName varchar(50)
     
    ) 
Now fill Employee table with demo records like that.
Nirav Prabtani
Fill Department table also like this....
Nirav Prabtani

1) Inner Join

The join that displays only the rows that have a match in both the joined tables is known as inner join.
 select e1.Username,e1.FirstName,e1.LastName,e2.DepartmentName _
from Employee e1 inner join Departments e2 on e1.DepartID=e2.id 
It gives matched rows from both tables with reference to DepartID of first table and id of second table like this.
Nirav Prabtani
Equi-Join
Equi join is a special type of join in which we use only equality operator. Hence, when you make a query for join using equality operator, then that join query comes under Equi join. 
Equi join has only (=) operator in join condition.
Equi join can be inner join, left outer join, right outer join.
Check the query for equi-join:
 SELECT * FROM Employee e1 JOIN Departments e2 ON e1.DepartID = e2.id 
Nirav Prabtani

2) Outer Join

Outer join returns all the rows of both tables whether it has matched or not.
We have three types of outer join:
  1. Left outer join
  2. Right outer join
  3. Full outer join
a) Left Outer join
Left join displays all the rows from first table and matched rows from second table like that..
 SELECT * FROM Employee e1 LEFT OUTER JOIN Departments e2
ON e1.DepartID = e2.id 
Result:
Nirav Prabtani
b) Right outer join
Right outer join displays all the rows of second table and matched rows from first table like that.
 SELECT * FROM Employee e1 RIGHT OUTER JOIN Departments e2
ON e1.DepartID = e2.id
Result:
Nirav Prabtani
3) Full outer join
Full outer join returns all the rows from both tables whether it has been matched or not.
 SELECT * FROM Employee e1 FULL OUTER JOIN Departments e2
ON e1.DepartID = e2.id 
Result:
Nirav Prabtani

3) Cross Join

A cross join that produces Cartesian product of the tables that are involved in the join. The size of a Cartesian product is the number of the rows in the first table multiplied by the number of rows in the second table like this.
 SELECT * FROM Employee cross join Departments e2 
You can write a query like this also:
 SELECT * FROM Employee , Departments e2

 4) Self Join

Joining the table itself called self join. Self join is used to retrieve the records having some relation or similarity with other records in the same table. Here, we need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause.
SELECT e1.Username,e1.FirstName,e1.LastName from Employee e1 _
inner join Employee e2 on e1.id=e2.DepartID
Here, I have retrieved data in which id and DepartID of employee table has been matched:
Nirav Prabtani

Points of Interest

Here, I have taken one example of self join in this scenario where manager name can be retrieved by managerid with reference of employee id from one table.
Here, I have created one table employees like that:
Nirav Prabtani
If I have to retrieve manager name from manager id, then it can be possible by Self join:
 select e1.empName as ManagerName,e2.empName as EmpName _
from employees e1 inner join employees e2 on e1.id=e2.managerid 
Result:
Nirav Prabtani

History

  • 20 Jan 2014: Initial post

CTE Query in sql server.

Introduction

Common Table Expressions (CTE) query is very useful to reduce query length as well complexity.
Generally we are using Joins for retrieving records from multiple tables, It is difficult and complex little bit than CTE queries.
A common table expression (CTE) is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. CTE query returns us a single query from multiple joins query so we can easily deal with it and get more expected result in less effort.

Using the code

It is very easy to write CTE query.
A Common Table Expression defines with three components.
  1. CTE name after WITH phrase
  2. Column list (It is not mandatory)
  3. Final query (appears within parentheses after the AS keyword)
Let see Format of CTE query
WITH CTEname(Columnlist optional) AS(..sql query..)
SELECT * FROM CTE name
it returns all columns of sql query which has been written in parenthesis..
e.g.
WITH SampleData AS(SELECT e1.column2 AS EmpName,e2.column2 AS Department FROM table1 e1 INNER JOIN table2 e2 ON e1.Column3=e2.column1) 
select * from SampleData  
it returns two columns having name EmpName and Department and it act like single and simple query.
We can use multiple CTE query as well
with samp as
(select 'Nirav' as Text1 ,'Prabtani' as Text2)
    ,samp1 as(
            select * from samp
            union
            select 'Code' as Text1 ,'project' as Text2
         )
select * from samp1  
we can define column name as well like this.
WITH CTEname (columnlist) as (....) 
 with samp as
(select 'Nirav' as Text1 ,'Prabtani' as Text2)

    ,samp1(MyColumn1,MyColumn2) as(
            select * from samp
            union
            select 'Code' as Text1 ,'project' as Text2
         )
select * from samp1  
This query returns output of both queries.. 

Points of Interest

I have retrieved dates between date interval from first date to second date like this..
 WITH CTEQuery AS (
     SELECT CAST('23 Mar 2014' AS DATETIME) AS dt
     UNION ALL
     SELECT DATEADD(dd, 1, dt)
      FROM CTEQuery s
      WHERE DATEADD(dd, 1, dt) <= CAST('26 Jun 2014' AS DATETIME)
      )
select * from CTEQuery 
It returns all the date row wise between 23 Mar 2014 to 26 Jun 2014.... Smile | :)

History
  • 20 Jan 2014, initial level..