Anup Das Gupta's blog: 2014

Wednesday, December 24, 2014

Consuming Microsoft web API from Angular JS application – Cross domain – Cross site: JSONP

In this post we are going to explore how to consume web API from angular JS application. This is a cross domain call where the web API will reside in a different application than the angular call. We will do this with JSONP.

In this example we will be using visual studio 2013, web API 2 and angular JS 1.3.8

Here I am using the Product web API sample from asp.net site. You can find the steps here. Follow the steps for creating the web API project.

Now if we check the URL(api/products), we will get listing of all products in the browser in xml format. In this example we will use JSON data as result, we can change this by changing formatter. We can add the following code to change the formatter in WebApiConfig at the end of Register(HttpConfiguration config) method.

config.Formatters.Clear();
config.Formatters.Add(new JsonMediaTypeFormatter());

Check the difference after changing the formatter.

Now we are ready for consuming it from angular JS.

We can add the angular JS reference using NuGet like below-

If you have issue with NuGet, you can download the latest version of angular JS from the angular site and refer it to the page.

Now let’s define a module(ProductModule) and a controller(ProductCtrl) for reading the products from the web API method.

var product = angular.module("ProductModule", []);
product.controller("ProductCtrl", ["$scope", "$http", function ($scope, $http) {
//to do    
}]);

Here we have added $http as we need to consume a service. We can do the call to API service with a callback and jsonp as calling method. We are using jsonp here are the angular site and the api are two different web site and hence cross domain.

var product = angular.module("ProductModule", []);

product.controller("ProductCtrl", ["$scope", "$http", function ($scope, $http) {
    $http({
        method: 'jsonp',
        url: 'http://localhost:51116/api/products?callback=JSON_CALLBACK'
    })
        .success(function (data, status, headers, config) {
            $scope.Products = data;
            
        })
        .error(function (data, status, headers, config) {
            
        });
}]);

As we are using jsonp, we need to append a callback to the calling URI, hence ?callback=JSON_CALLBACK'. Angular JS internally handles and returns the result with the data parameter in the success method.

Now let’s design a HTML page for consuming the result from the service. HTML goes like this-

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title></title>
</head>
<body ng-app="ProductModule">
    <table ng-controller="ProductCtrl">
        <thead>
            <tr>
                <th>Id</th>
                <th>Name</th>
                <th>Category</th>
                <th>Price</th>
            </tr>
        </thead>
        <tbody>
            <tr ng-repeat="product in Products">
                <td>{{product.Id}}</td>
                <td>{{product.Name}}</td>
                <td>{{product.Category}}</td>
                <td>{{product.Price}}</td>
            </tr>
        </tbody>
    </table>
    <script src="Scripts/angular.js"></script>
    <script src="Domain JS/ProductCtrl.js"></script>
</body>
</html>

Now before running the application please check the URL specially the port number. If this is correct, you should be getting some 404 error. Let’s check this in chrome developer tool. Screenshots are there in the image below. The Ajax request is 200 OK and there is proper data in the response tab-

Then what is the Issue. If you check the request URL, it is http://localhost:51116/api/products?callback=angular.callbacks._0 But we have provided the URL as http://localhost:51116/api/products?callback=JSON_CALLBACK. This is not an issue. Angular JS is internally changing JSON_CALLBACK to angular.callback._0. Now let’s check the response. It’s coming as-

[{"Id":1,"Name":"Tomato Soup","Category":"Groceries","Price":1.0},{"Id":2,"Name":"Yo-yo","Category":"Toys","Price":3.75},{"Id":3,"Name":"Hammer","Category":"Hardware","Price":16.99}]

Here is the problem. As it is a JSONP request the response should be like angular.callback._0(response) that is –

angular.callback._0( [{"Id":1,"Name":"Tomato Soup","Category":"Groceries","Price":1.0},{"Id":2,"Name":"Yo-yo","Category":"Toys","Price":3.75},{"Id":3,"Name":"Hammer","Category":"Hardware","Price":16.99}])

That is what Web API should be doing and the JSON formatter should support this. The formatter that we have used (JsonMediaTypeFormatter) is not having this feature. There is a nice work around for this. We can add JsonpFormatter for this. Please check the below link for detail-

https://github.com/WebApiContrib/WebApiContrib.Formatting.Jsonp

Let’s install JsonpFormatter formatter using NuGet. Search for WebApiContrib.Formatting.Jsonp in NuGet manager and install like below-

Next we need to add the callback capability and expose json data like below-

GlobalConfiguration.Configuration.AddJsonpFormatter();
GlobalConfiguration.Configuration.Formatters.XmlFormatter.SupportedMediaTypes.Clear();

Now the results is coming in correct format and successful. The complete source code is attached here.

Monday, August 4, 2014

Accessing derived class properties in the base class public method and creating dynamic queries

There are many ORM models that work on Object Relation Mapping. It generates queries on the fly based on the XML config file and reflection.

This post is intended to explain a simple scenario of how we can write a method like on a base class say ”save”, and any class that is derived from this base class should automatically call the save method, create a dynamic query and save the data on database.

Now let’s take a simple class named Person that has only two fields as below-

public class Person {
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

And on doing the following code it should take the value from the instance “p”, create a dynamic query and save the data to database-

Person p = new Person() { FirstName="John", LastName="Smit" };
        p.save();

We can follow few steps for doing that.

Step 1 : Let’s create an class label attribute that will carry the name of the table for saving data in the database. It goes like this-

[System.AttributeUsage(System.AttributeTargets.Class )]
public class TableName : System.Attribute
{
    public string name;
    public TableName() 
    {
    }
    public TableName(string name)
    {
        this.name = name;
    }
}

Step 2 : is to create a base class that will implement a public method “save”. Ideally we should make this class as generic class as we need to access the property and the value of the properties. So, we can have this class as –

public class DBObjectwhere T:class { 
    public void save(){

        //implementation goes here
            
    }
}

So the inheritance will be below. Here we have added TableName attribute and tblPerson is the database table name-

[TableName(name="tblPerson")]
public class Person : DBObject {
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Step 3 : What we need to do in this step is five things.

Get the name of the table
Create comma separated table field name
Create comma separated table field value to insert
Create the query
Do database call

Step 3-1: We are passing type information to DBObject so we can get custom attribute(TableName) value through T like below-

 (typeof(T).GetCustomAttributes(false).FirstOrDefault() as TableName).Name

Step 3-2: We can get comma separated field names from T using reflection like below-

string.Join(",", (from p in typeof(T).GetProperties().AsEnumerable()
                                   select p.Name).ToArray());

Step 3-3: We can get comma separated field values from T using reflection like below-

string.Join(",", (from p in typeof(T).GetProperties().AsEnumerable()
                                   select "'"+ p.GetValue(this,null)+"'").ToArray());

Step 3-4: We can create final query as following-

string.Format("insert into {0} ({1}) values ({2})  ", attr.name, fields, values);

So the complete save method will look like below-

    public void save(){
        var attr = typeof(T).GetCustomAttributes(false).FirstOrDefault() as TableName;
        string fields, values;
        fields = string.Join(",", (from p in typeof(T).GetProperties().AsEnumerable()
                                   select p.Name).ToArray());
        values = string.Join(",", (from p in typeof(T).GetProperties().AsEnumerable()
                                   select "'"+ p.GetValue(this,null)+"'").ToArray());
        string query = string.Format("insert into {0} ({1}) values ({2})  ", attr.name, fields, values);
    }

Step 3-5: Variable query will finally result the following string for this example-

insert into tblPerson (FirstName,LastName) values ('John','Smit')

This is the query we are looking for. I am not completing the data base call part.

How to upload multiple records in C# and stored procedure without looping

Recently I come across a question regarding how to save multiple records in sql server using C# and without using any loop.

Let’s take a simple case of saving list of persons. Let’s have the following class as person-

public class Person {
        public string PFirstName { get; set; }
        public string PLastName { get; set; }
        public int PAge { get; set; }
    }

Then let’s create a simple data base table called Person as below-

CREATE TABLE [dbo].[Person](
	[FirstName] [varchar](100) NULL,
	[LastName] [varchar](100) NULL,
	[Age] [int] NULL
) ON [PRIMARY]

Now we can solve this problem by passing XML data as input to a stored procedure and using SQL XML for parsing data and saving to database.

So, let’s first create a list of persons for this example like below-

 List personList =new List(){
                new Person(){ PFirstName="abc", PLastName= "smit", PAge=32},
                new Person(){ PFirstName="bcd", PLastName= "pal", PAge=32}
            };

Now let’s parse this list to XML for saving and put it into a string variable-

 string SProc = "dbo.save_person_bulk";
            string ConnectonString = @"Data Source=(local);Initial Catalog=sample;Integrated Security=True;";
            using (SqlConnection sqlConnection = new SqlConnection(ConnectonString))
            {
                sqlConnection.Open();

                using (SqlCommand sqlCommand = new SqlCommand(SProc, sqlConnection))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@person_data", SqlDbType.VarChar)
                          {
                              Value = xmlPersonData
                          });
                    using (DataTable dataTable = new DataTable())
                    {
                        using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter())
                        {
                            sqlDataAdapter.SelectCommand = sqlCommand;
                            sqlDataAdapter.Fill(dataTable);
                        }
                    }
                }
            }

On doing the list parsing the resulting XML will look like this-

 CREATE PROCEDURE [dbo].[save_person_bulk]   
 @person_data xml  
AS  
BEGIN  
 SET NOCOUNT ON;  
 
 INSERT INTO [dbo].[person]  (FirstName, LastName, Age)    
           select  
           e.value('(PFirstName/text())[1]', 'varchar(100)')   
           ,e.value('(PLastName/text())[1]', 'varchar(100)')   
           ,e.value('(PAge/text())[1]', 'int')   
      FROM  
            @person_data.nodes('//ArrayOfPerson/Person') pdata(e)  
END

Looking at the generated XML and the stored procedure the code is self explanatory.