Post
Ask Question

SharePoint Site Analytics Through Web API

Introduction

To design the web API to send data into the database, the analytics depend on the site page's open tracking and the report generation.

Using the code

Step 1 - Create New Project

Go to File > New > Project > Web > Asp.net MVC web project > Enter Application Name > Select your project location > click to add button > It will show new dialog window for select template > here we will select Web API project > then click ok

SharePoint
 
SharePoint

Step 2

Add Connection string in web.config file for database connection,
  1. <connectionStrings>  
  2.    <add name="con" connectionString="Data Source=.;Initial Catalog=test;Integrated Security=True" providerName="System.Data.SqlClient"/>  
  3. </connectionStrings>  

Step 3 - Add Model for Customer table.

In Models Folder create the class called Site Details.

 SharePoint
  1. public string SiteCollectionURL { get; set; }  
  2. public string SiteName { get; set; }  
  3. public string SiteURL { get; set; }  
  4. public string URL { get; set; }  
  5. public string UserID { get; set; }  
  6. public string UserName { get; set; }  
  7. public string UserEmail { get; set; }  
  8. public string ItemType { get; set; }  
  9. public bool iso365 { get; set; }  
  10. public string CreatedBy { get; set; }  
  11. public DateTime Created { get; set; }  
  12. public string ModifiedBy { get; set; }  
  13. public DateTime Modified { get; set; }  

Step 4 - Create Database Access layer

Go to Solutions Explorer > right click on project solution> Add New Folder > Enter Folder name (Here I rename it as ‘Data’) > right click on folder > Add new class call Data access

Now add the fallowing method into Database access class named Add_Customer that does all the database access related activities.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Data;  
  6. using System.Data.SqlClient;  
  7. using System.Configuration;  
  8. using InsertdataWebapi.Models;  
  9. namespace InsertdataWebapi.database_access_layer {  
  10.     public class db {  
  11.         SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);  
  12.         customer cs = new customer();  
  13.         public void Add_Customer(customer cs) {  
  14.             SqlCommand com = new SqlCommand("Analytics_Details_Add", con);  
  15.             com.CommandType = CommandType.StoredProcedure;  
  16.             com.Parameters.AddWithValue("@SiteCollectionURL", cs.SiteCollectionURL);  
  17.             com.Parameters.AddWithValue("@SiteName", cs.SiteName);  
  18.             com.Parameters.AddWithValue("@SiteURL", cs.SiteURL);  
  19.             com.Parameters.AddWithValue("@URL", cs.URL);  
  20.             com.Parameters.AddWithValue("@UserID", cs.UserID);  
  21.             com.Parameters.AddWithValue("@UserName", cs.UserName);  
  22.             com.Parameters.AddWithValue("@UserEmail", cs.UserEmail);  
  23.             com.Parameters.AddWithValue("@ItemType", cs.ItemType);  
  24.             com.Parameters.AddWithValue("@iso365", cs.iso365);  
  25.             com.Parameters.AddWithValue("@CreatedBy", cs.CreatedBy);  
  26.             com.Parameters.AddWithValue("@Created", cs.Created);  
  27.             com.Parameters.AddWithValue("@ModifiedBy", cs.ModifiedBy);  
  28.             com.Parameters.AddWithValue("@Modified", cs.Modified);  
  29.             con.Open();  
  30.             com.ExecuteNonQuery();  
  31.             con.Close();  
  32.         }  
  33.     }  
  34. }  

Step 5 - Create POST method

Create a Post method in the Asp.net Web API Controller Class.

Open the Values Controller, delete the existing method and create the AddCustomer method and call the database access class (db.cs) method as the following,

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Net;  
  5. using System.Net.Http;  
  6. using System.Web.Http;  
  7. using InsertdataWebapi.Models;  
  8. namespace InsertdataWebapi.Controllers {  
  9.     public class ValuesController: ApiController {  
  10.         database_access_layer.db dblayer = new database_access_layer.db();  
  11.         [HttpPost]  
  12.         public IHttpActionResult AddCustomer([FromBody] customer cs) {  
  13.             try {  
  14.                 if (!ModelState.IsValid) {  
  15.                     return BadRequest(ModelState);  
  16.                 }  
  17.                 dblayer.Add_Customer(cs);  
  18.                 return Ok("Success");  
  19.             } catch (Exception) {  
  20.                 return Ok("Something went wrong, try later");  
  21.             }  
  22.         }  
  23.     }  
  24. }  

Step 6 - Configure Asp.net Web API routing

We need to configure the routing of the incoming request. Let us create WebApiConfig.cs by right-clicking on the App_start folder and creating the following method:

  1. public static void Register(HttpConfiguration config) {  
  2.     config.MapHttpAttributeRoutes();  
  3.     config.Routes.MapHttpRoute(name: "DefaultApi", routeTemplate: "api/{controller}/{id}", defaults: new {  
  4.         id = RouteParameter.Optional  
  5.     });  
  6. }  

Step 7 - Call Asp.net Web API Controller method

Call Asp.net Web API Controller method from the HTMLpage using JSON.

Now we need to call to Web API Controller method from HTML page. To do this we need to create a JSON method using Jquery. The entire test.html page will be as follows,

  1. <script type="text/javascript">  
  2.     $(function() {  
  3.         var PersonalDetails = {  
  4.             "SiteCollectionURL": "",  
  5.             "SiteName": "",  
  6.             "SiteURL": "",  
  7.             "URL": "",  
  8.             "UserID": "7364",  
  9.             "UserName": "prasanan",  
  10.             "UserEmail": "",  
  11.             "ItemType": "Item",  
  12.             "iso365": "false",  
  13.             "CreatedBy": "harup",  
  14.             "Created": "08-01-2017",  
  15.             "ModifiedBy": "Venkata",  
  16.             "Modified": "08-01-2017"  
  17.         };  
  18.         $.ajax({  
  19.             type: "POST",  
  20.             url: '/api/Analytics/AddSiteDetails',  
  21.             data: JSON.stringify(PersonalDetails),  
  22.             contentType: "application/json;charset=utf-8",  
  23.             success: function(data, status, xhr) {  
  24.                 alert("The result is : " + data);  
  25.             },  
  26.             error: function(xhr) {  
  27.                 alert(xhr.responseText);  
  28.             }  
  29.         });  
  30.     });  
  31. </script>  

Step 8 - Run Application/ Host the Application into IIS

We have done all steps, now it’s time to run the application

Database Section

Step 1 - Create a table and Stored Procedure.

Table Creation

  1. CREATE TABLE [dbo].[webapitable]   
  2.   (   
  3.      [id]                [INT] IDENTITY(1, 1) NOT NULL,   
  4.      [sitecollectionurl] [NVARCHAR](max) NOT NULL,   
  5.      [sitename]          [NVARCHAR](100) NOT NULL,   
  6.      [siteurl]           [NVARCHAR](max) NOT NULL,   
  7.      [url]               [NVARCHAR](max) NOT NULL,   
  8.      [userid]            [NVARCHAR](100) NOT NULL,   
  9.      [username]          [NVARCHAR](100) NOT NULL,   
  10.      [useremail]         [NVARCHAR](max) NOT NULL,   
  11.      [itemtype]          [NVARCHAR](50) NOT NULL,   
  12.      [createdby]         [NVARCHAR](max) NULL,   
  13.      [created]           [DATETIME] NULL,   
  14.      [modifiedby]        [NVARCHAR](max) NULL,   
  15.      [modified]          [DATETIME] NULL,   
  16.      PRIMARY KEY CLUSTERED ( [id] ASC )WITH (pad_index = OFF,   
  17.      statistics_norecompute = OFF, ignore_dup_key = OFF, allow_row_locks = on,   
  18.      allow_page_locks = on) ON [PRIMARY]   
  19.   )   
  20. ON [PRIMARY]   
  21. textimage_on [PRIMARY]   
  22.   
  23. go   

Create a stored procedure to insert data into the table as,

  1. CREATE PROC [dbo].[Analytics_details_add] @SiteCollectionURL NVARCHAR (max),   
  2.                                           @SiteName          NVARCHAR (100),   
  3.                                           @SiteURL           NVARCHAR (max),   
  4.                                           @URL               NVARCHAR(max),   
  5.                                           @UserID            NVARCHAR(100),   
  6.                                           @UserName          NVARCHAR(100),   
  7.                                           @UserEmail         NVARCHAR(max),   
  8.                                           @ItemType          NVARCHAR(50),   
  9.                                           @iso365            BIT,   
  10.                                           @CreatedBy         NVARCHAR(max),   
  11.                                           @Created           DATETIME,   
  12.                                           @ModifiedBy        NVARCHAR(max),   
  13.                                           @Modified          DATETIME   
  14. AS   
  15.   BEGIN   
  16.       INSERT INTO [dbo].[siteanalytics]   
  17.                   (sitecollectionurl,   
  18.                    sitename,   
  19.                    siteurl,   
  20.                    url,   
  21.                    userid,   
  22.                    username,   
  23.                    useremail,   
  24.                    itemtype,   
  25.                    iso365,   
  26.                    createdby,   
  27.                    created,   
  28.                    modifiedby,   
  29.                    modified)   
  30.       VALUES      ( @SiteCollectionURL,   
  31.                     @SiteName,   
  32.                     @SiteURL,   
  33.                     @URL,   
  34.                     @UserID,   
  35.                     @UserName,   
  36.                     @UserEmail,   
  37.                     @ItemType,   
  38.                     @iso365,   
  39.                     @CreatedBy,   
  40.                     Getdate(),   
  41.                     @ModifiedBy,   
  42.                     Getdate() )   
  43.   END   
  44.   
  45. go   
Run the above script in the MS SQL Server and click the execute button.


Similar Articles