Contact Me

Nirav Prabtani

Mobile : +91 738 308 2188

Email : niravjprabtani@gmail.com

Nirav Prabtani

Friday 25 April 2014

Google news API using C#


Introduction

In this tip, I am going to discuss about Google news API.

Background

I have used HttpWebRequest and HttpWebResponse for retrieving news from Google API URL. I have written all code for that....
I have one textbox for insert search parameters and one button for search data using Google API.
When we click on Get News button, it calls JavaScript GetNews() function.
From JavaScript function, Ajax post method calls web method from code behind.
Google News content has been retrieved using HttpWebRequest and HttpWebRequest, this WebMethod returns data into array format from code behind.
Now Div having name "DivNews" has been appended for showing news content using jQuery.
For further details, please take a look at the code.

Using the Code

1) News.aspx

  • JavaScript Code




   //For Enter Key Press Event
        function runScript(e) {
            if (e.keyCode == 13) {

                //Call GetNews Function
                GetNews();

                return false;
            }
        }

        //Function for GetNews Using Ajax Post Method
        function GetNews() {

            //Set FadeIn for Progressive Div
            $("#ProgressiveDiv").fadeIn();

            //Create Ajax Post Method
            $.ajax({
                type: "POST", // Ajax Method
                url: "News.aspx/GetNewsContent",  //Page URL / Method name
                data: "{'NewsParameters':'"
                document.getElementById("txtSubject").value + "'}", // Pass Parameters
                contentType: "application/json;charset=utf-8",
                dataType: "json",
                success: function (data) { // Function call on success

                    $("#DivNews").empty(); // Set Div Empty

                    //Set For loop for binding Div Row wise
                    for (var i = 0; i < data.d.length; i++) {

                        //Design Div Dynamically using append
                        $("#DivNews").append("<tr><td>
                       <B style='color:Red'>" + data.d[i].title + 
                        "- By: Nirav Prabtani</B> </td></tr><tr><td>"
                        data.d[i].Description + "</td></tr>");
                    }

                    //set fadeOut for ProgressiveDiv
                    $("#ProgressiveDiv").fadeOut(500);
                },

                error: function (result) { // Function call on failure or error
                    alert(result.d);
                }
            });
        }

  • CSS Code



     .classname
        {
            -moz-box-shadow: inset 0px 1px 0px 0px #ffffff;
            -webkit-box-shadow: inset 0px 1px 0px 0px #ffffff;
            box-shadow: inset 0px 1px 0px 0px #ffffff;
            background: -webkit-gradient( linear, left top
            left bottom, color-stop(0.05, #ededed), color-stop(1, #dfdfdf) );
            background: -moz-linear-gradient( center top, #ededed 5%, #dfdfdf 100% );
            filter: progid:DXImageTransform.Microsoft.gradient(startColorstr='#ededed', endColorstr='#dfdfdf');
            background-color: #ededed;
            -webkit-border-top-left-radius: 6px;
            -moz-border-radius-topleft: 6px;
            border-top-left-radius: 6px;
            -webkit-border-top-right-radius: 6px;
            -moz-border-radius-topright: 6px;
            border-top-right-radius: 6px;
            -webkit-border-bottom-right-radius: 6px;
            -moz-border-radius-bottomright: 6px;
            border-bottom-right-radius: 6px;
            -webkit-border-bottom-left-radius: 6px;
            -moz-border-radius-bottomleft: 6px;
            border-bottom-left-radius: 6px;
            text-indent: 0;
            border: 1px solid #dcdcdc;
            display: inline-block;
            color: #777777;
            font-family: arial;
            font-size: 15px;
            font-weight: bold;
            font-style: normal;
            height: 25px;
            line-height: 50px;
            width: 100px;
            text-decoration: none;
            text-align: center;
            text-shadow: 1px 1px 0px #ffffff;
        }
        .classname:hover
        {
            background: -webkit-gradient( linear, left top
            left bottom, color-stop(0.05, #dfdfdf), color-stop(1, #ededed) );
            background: -moz-linear-gradient( center top, #dfdfdf 5%, #ededed 100% );
            filter: progid:DXImageTransform.Microsoft.gradient(startColorstr='#dfdfdf', endColorstr='#ededed');
            background-color: #dfdfdf;
        }
        .classname:active
        {
            position: relative;
            top: 1px;
        }      
        
        .textbox
        {
            background: #FFF url(http://html-generator.weebly.com/files/theme/input-text-9.png) no-repeat 4px 4px;
            border: 1px solid #999;
            outline: 0;
            padding-left: 25px;
            height: 25px;
            width: 275px;
        }
        .style1
        {
            height: 61px;
        }
        #ProgressiveDiv
        {
            width: 100%;
            height: 100%;
            display: none;
            opacity: 0.4;
            position: fixed;
            top: 0px;
            left: 0px;
            vertical-align: middle;
        }

  • HTML Code




  <body>
    <form id="form1" runat="server">
    <div>
        <table>
            <tr>
                <td align="center" class="style1">
                    <h3>
                        Welcome to My news Portal</h3>
                </td>
            </tr>
            <tr>
                <td>
                    <asp:TextBox runat="server" ID="txtSubject" 
                    CssClass="textbox" onkeypress="return runScript(event)" />
                </td>
            </tr>
            <tr>
                <td align="right">
                    <h6 style="height: 35px">
                        By: Nirav Prabtani</h6>
                </td>
            </tr>
        </table>
        <div id="DivNews">
        </div>

    </div>
    <%--This Div is For Binding News--%>
    <div id="ProgressiveDiv" style="padding-left: 500px">
        <img src="Image/loading.gif" />
    </div>
    </form>
</body> 

2) News.aspx.cs

All news content from Google API has been retrieved using webMethod from code behind.

  • C# Code




using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Services;
using System.Net;
using System.IO;
using System.Text;
using System.Data;

namespace GoogleNews_API
{
    public partial class News : System.Web.UI.Page
    {
        //Page Load Method
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        //WenMethod GetNewsContent for Retrieving News from Google API
        [WebMethod]
        public static ItemNews[] GetNewsContent(string NewsParameters)
        {

            List<ItemNews> Details = new List<ItemNews>();

            // httpWebRequest with API URL
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create
            ("http://news.google.com/news?q=" + NewsParameters + "&output=rss");

            //Method GET
            request.Method = "GET";

            //HttpWebResponse for result
            HttpWebResponse response = (HttpWebResponse)request.GetResponse();


            //Mapping of status code
            if (response.StatusCode == HttpStatusCode.OK)
            {
                Stream receiveStream = response.GetResponseStream();
                StreamReader readStream = null;

                if (response.CharacterSet == "")
                    readStream = new StreamReader(receiveStream);
                else
                    readStream = new StreamReader(receiveStream, Encoding.GetEncoding(response.CharacterSet));

                //Get news data in json string

                string data = readStream.ReadToEnd();

                //Declare DataSet for putting data in it.
                DataSet ds = new DataSet();
                StringReader reader = new StringReader(data);
                ds.ReadXml(reader);
                DataTable dtGetNews = new DataTable();

                if (ds.Tables.Count > 3)
                {
                    dtGetNews = ds.Tables["item"];

                    foreach (DataRow dtRow in dtGetNews.Rows)
                    {
                        ItemNews DataObj = new ItemNews();
                        DataObj.title = dtRow["title"].ToString();
                        DataObj.link = dtRow["link"].ToString();
                        DataObj.item_id = dtRow["item_id"].ToString();
                        DataObj.PubDate = dtRow["pubDate"].ToString();
                        DataObj.Description = dtRow["description"].ToString();
                        Details.Add(DataObj);
                    }
                }
            }

            //Return News array
            return Details.ToArray();
        }

        //Define Class to return news data
        public class ItemNews
        {
            public string title { get; set; }
            public string link { get; set; }
            public string item_id { get; set; }
            public string PubDate { get; set; }
            public string Description { get; set; }
        }
    }

Final Output

Nirav Prabtani

History

  • 26th Mar 2014: Initial post

Expandable Table like Gridview using c#


Nirav Prabtani

Introduction

In this tip, I am going to write about Expandable table using Ajax post method. I have used Div having name "tblUsers" to append using jQuery Ajax post method.
Let's see further in detail about that.

Background

In this tip, all the users data has been structured in gridview form.
Using Ajax post method, all user's data is retrieved and set to Div using jQuery.
It is very fast and smooth during expansion and there is no need for any other third party DLLs or setups.

Using the Code


I have implemented code here. There is only one page for making an expandable table. It is very easy and fast.
First, create a database named DemoDatabase in your SQL Server and that runs that script in your SQL server (only for this demo purpose, you can integrate your own database in this demo)


 CREATE TABLE [dbo].[Users](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [UserName] [varchar](50) NULL,
    [Email] [nvarchar](150) NULL,
    [imageURL] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Users] ON 

GO
INSERT [dbo].[Users] ([id], [FirstName], [LastName], [UserName], [Email], _
[imageURL]) VALUES (1, N'Nirav', N'Prabtani', N'Nills', _
N'niravjprabtani@gmail.com', N'nirav.jpg')
GO
INSERT [dbo].[Users] ([id], [FirstName], [LastName], [UserName], [Email], _
[imageURL]) VALUES (2, N'Rajan', N'Mrug', N'Raj', _
N'Raj@gmail.com', N'rajan.jpg')
GO
INSERT [dbo].[Users] ([id], [FirstName], [LastName], [UserName], [Email], _
[imageURL]) VALUES (3, N'Rajesh', N'Saradhara', N'Rajesh', _
N'rajesh@yahoo.in', N'rajesh.jpg')
GO
INSERT [dbo].[Users] ([id], [FirstName], [LastName], [UserName], [Email], _
[imageURL]) VALUES (4, N'Dharmendra', N'Pansara', _
N'Dhamo', N'Dhamo@hotmail.com', N'demo.jpg')
GO
SET IDENTITY_INSERT [dbo].[Users] OFF
GO 



Now create WebForm IndexTable in your project.

1) IndexTable.aspx

  • Javascript Code

Put Javascript js link like jQuery.1.4.1.min.js or other..... :)

 $(document).ready(function () {
        //Call BindUsers function during Page load
            BindUsers();

        });

        //Bind all data to div using ajax Post method
        function BindUsers() {

            $.ajax({
                type: 'POST', // Method type
                url: 'IndexTable.aspx/GetUsers', //Page URL/Function Name
                data: {}, //Parameters
                contentType: 'application/json',
                dataType: 'json',
                success: function (data) { //Returned data

                    if (data.d.length > 0) { //Check Count of data

                        // Set Div to empty.
                        $("#tblUsers").empty(); 

                      //Create Header of a Tbale
                        $("#tblUsers").append("<tr class='trHead'  
                       ><td class='tdHead'>Firstname</td>
                       <td class='tdHead'>Lastname</td>
                       <td class='tdHead'>Username</td></tr>");

                        //Bind Div in tabular form using For loop

                        for (var i = 0; i < data.d.length; i++) {
                            $("#tblUsers").append("<tr  
                           onclick='ExpandGrid(" + data.d[i].id + ")' >
                           <td class='tdHead'>" + data.d[i].FirstName + "
                           </td><td class='tdHead'>" + data.d[i].LastName + "
                           </td><td class='tdHead'>" + data.d[i].UserName + "
                           </td></tr><tr style='display:none;background-color:#F2F2F2'
                           id='trExpand" + data.d[i].id + "'><td valign='middle'
                           align='center' ><img src='Images/" + data.d[i].ImageURL + 
                            "' height='80px' width='50px'></td>
                           <td colspan='2' align='center' valign='middle'>
                           <table><tr><td><input id='txtFirstName"
                            data.d[i].id + "' value=" + data.d[i].FirstName + " >
                           </td></tr><td><input id='txtLastName"
                            data.d[i].id + "' value=" + data.d[i].LastName + " >
                           </td><tr><td><input id='txtUserName" + data.d[i].id + 
                            "' value=" + data.d[i].UserName + " ></td></tr>
                           <tr><td><input id='txtEmail" + data.d[i].id + "'
                           value=" + data.d[i].Email + " ></td></tr></table>  
                           </td> </tr>");
                        }
                    }
                },
                error: function (result) { //Call on failure or error

                    alert(result.d);
                }
            });
        }

        //Function for expansion of a table
        function ExpandGrid(id) {

            $("#trExpand" + id).toggle(300);
        }

   

  • CSS Styles

        .trHead
        {
            height: 30px;
            background-color: #c8c8c8;
            color: White;
            font-weight: bold;
        }
        .tdHead
        {
            width: 150px;
            border: 1px solid #c8c8c8;
            height: 30px;
            vertical-align: middle;
            padding-left: 5px;
            cursor: pointer;
        }
        .trExpand
        {
            border: 1px solid #c8c8c8;
        }

  • HTML Code


<div id="tblUsers"></div>


2) IndexTable.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Services;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace ExpandableTable
{
    public partial class IndexTable : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        // Get All users data

        [WebMethod]
        public static Users[] GetUsers()
        {
            // Create Object of a list
            List<Users> Details = new List<Users>();


            //Select All users data from SQL database
            string SelectUsers = "select * from Users";
            SqlConnection cn = new SqlConnection
            (ConfigurationManager.ConnectionStrings["cs_Users"].ConnectionString);
            SqlCommand cmd = new SqlCommand(SelectUsers, cn);

            cn.Open();
            cmd.ExecuteNonQuery();

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtUsers = new DataTable();

            da.Fill(dtUsers);

            //Insert all users data into LIST
            foreach (DataRow dtrow in dtUsers.Rows)
            {
                Users Dataobj = new Users();
                Dataobj.id = Convert.ToInt32(dtrow["id"]);
                Dataobj.FirstName = dtrow["FirstName"].ToString();
                Dataobj.LastName = dtrow["LastName"].ToString();
                Dataobj.UserName = dtrow["UserName"].ToString();
                Dataobj.Email = dtrow["Email"].ToString();
                Dataobj.ImageURL = dtrow["imageURL"].ToString();
                Details.Add(Dataobj);
            }

            //Return array value to bind data
            return Details.ToArray();
        }

        //Class of users
        public class Users
        {
            public int id { get; set; }
            public string FirstName { get; set; }
            public string LastName { get; set; }
            public string UserName { get; set; }
            public string Email { get; set; }
            public string ImageURL { get; set; }
        }
    }

In this manner Div "tblUsers" has been bound using Ajax Post method like Expandable table.

How Does It Work??

When application is started, first of all, jQuery page load method occurs and using Ajax post method WebMethod GetUsers() is called and all users data from database returned in array format using Users class.
We can struct Div using append jQuery syntax.
In this example, there are two   tags that have been initialized in Div, one is visible and the second is invisible using CSS property "display:block".
When Div will bind, it will look like that:


Nirav Prabtani


There are four Users, therefore there are four columns in a table.
Now if you click on any column of a table, then the second row which is already invisible is set to visible by calling JavaScript function ExpandGrid():


function ExpandGrid(id) {
            $("#trExpand" + id).toggle(300);
        } 


and it will look like...
Nirav Prabtani

Points of Interest

I have designed it in a very simple form. You can modify by simply assigning styles or classes.
We can also put up and down arrows for expanding functionality.
We can apply transition effects using jQuery during expansion of table.

History

  • 27th March 2014: Initial post
  • See original Article at 

Google News API using c#



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