SharePoint 2016 and 2019 on premises
As a SharePoint developer, dealing with external databases and you need to perform the crude operation through SharePoint visual web-parts not through the business connectivity service (BCS).
There are two ways to perform such action. First is to create a folder under SharePoint solution to hold the entity framework and SharePoint visual web-part will consume it. Second, creating a class library and add a reference to it and start consuming the DLL.
Nowadays, after the revolution of the web API. Web API will be created and hold the entity framework and SharePoint will consume it via HTTP get and post methods.
We have database with name Human which has a table named child.
| ChildID | int |
| ChildName | string |
| DateOfBirth | DateTime |
| Gender | string |
| CreatedBy | string |
| Created | DateTime |
| ModifiedBy | string |
| Modified | DateTime |
So, first step is building the web API in visual studio add new project to the SharePoint solution and add to it the edmx file. In the modules folder, the class to map the child table will be autogenerated and will look like:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace Human.SP.WebAPI.Models
{
[Serializable]
public class ChildModel
{
public int ChildID { get; set; }
public string ChildName { get; set; }
public Nullable<System.DateTime> DateOfBirth { get; set; }
public string Gender { get; set; }
public string CreatedBy { get; set; }
public Nullable<System.DateTime> Created { get; set; }
public string ModifiedBy { get; set; }
public Nullable<System.DateTime> Modified { get; set; }
}
}Add new folder to the project and name it Repository and add a class inside it and name it ChildRepository to encapsulate the logic for adding and selecting and the code should look like the following:
using Human.SP.WebAPI.Models;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web;
namespace Human.SP.WebAPI.Repository
{
public class ChildRepository
{
public static ChildModel AddChild(ChildModel childModel)
{
try
{
if (childModel == null)
return null;
Child child = null;
using (DBEntities DB = new DBEntities())
{
if (childModel.ChildID == 0)
{
child = new Child()
{
//set the data
};
DB.Child.Add(child);
}
else
{
child = DB.Child.Where(c => c.ChildID == childModel.ChildID).FirstOrDefault();
if (child != null)
{
//set the updated data
}
}
DB.SaveChanges();
}
childModel.ChildID = child.ChildID;
}
catch (Exception ex)
{
//log exception
}
return childModel;
}
/// <summary>
/// get from database by childID
/// </summary>
/// <param name="childID"></param>
/// <returns></returns>
public static ChildModel GetChildByID(int childID)
{
ChildModel childModel = null;
try
{
using (DBEntities DB = new DBEntities())
{
var query = from c in DB.Child
where (c.ChildID == childID)
select new ChildModel()
{
ChildName = c.ChildName,
DateOfBirth = c.DateOfBirth,
Gender = c.Gender,
CreatedBy = c.CreatedBy,
Created = c.Created,
ModifiedBy = c.ModifiedBy,
Modified = c.Modified
};
childModel = query.FirstOrDefault();
}
}
catch (Exception ex)
{
//log exception
}
return childModel;
}
}The last step in the web API layer is to add the child controller to expose the methods outside the API layer and make it available for client application. So, r-click on the controller folder and add controller and name it childcontroller.
Notice that, there is add method and select method. the add method will be configured as post method and the input parameter will be sent through the message body . The select method will be configured as get method and the input parameter will be sent through the URL.
The controller class should look like
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
namespace Human.SP.WebAPI.Controllers
{
public class ChildController : ApiController
{
[System.Web.Http.HttpPost]
[System.Web.Http.Route("api/AddChild/")]
public HttpResponseMessage AddChild(Models.ChildModel childModel)
{
childModel = Repository.ChildRepository.AddChild(childModel);
HttpRequestMessage msg = new HttpRequestMessage();
return Request.CreateResponse(HttpStatusCode.OK, childModel);
}
[System.Web.Http.HttpGet]
[System.Web.Http.Route("api/GetChildByID/{childID}")]
public HttpResponseMessage GetChildByID(int childID)
{
List<Models.Child> _children = Repository.ChildRepository.GetChildByID(childID);
HttpRequestMessage msg = new HttpRequestMessage();
return Request.CreateResponse(HttpStatusCode.OK, _children);
}
}
}Let’s consume this API in the SharePoint project and prepare the visual web parts to add and display the data. To consume that in SharePoint, we need to create Model and repository with the same names as the API. In the SharePoint project add folder and name it SPModels. Inside that folder, add new class and name it ChildModel and add exactly the same properties.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace SP.SPModels
{
[Serializable]
public class ChildModel
{
public int ChildID { get; set; }
public string ChildName { get; set; }
public Nullable<System.DateTime> DateOfBirth { get; set; }
public string Gender { get; set; }
public string CreatedBy { get; set; }
public Nullable<System.DateTime> Created { get; set; }
public string ModifiedBy { get; set; }
public Nullable<System.DateTime> Modified { get; set; }
}
}In the SharePoint project add new folder and name it SPRepository. Inside that repository, we will call the methods from the API layer. To make it easier, create a class to retrieve the connection information from web.config file and the code will be
public class HttpUtility
{
public static string ConnectionUrl
{
get { return ConfigurationManager.AppSettings["ConnectionUrl"].ToString();}
}
public static HttpClient GetHttpClientConnection()
{
//Get it from web.config
HttpClient cons = new HttpClient() { BaseAddress = new Uri(ConnectionUrl) };
cons.DefaultRequestHeaders.Accept.Clear();
cons.DefaultRequestHeaders.Accept.Add(new System.Net.Http.Headers.MediaTypeWithQualityHeaderValue("application/json"));
return cons;
}
}
Add class and name it ChildRepository and by using Async calls, we will call the methods from API layer.
using System.Linq;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;
namespace Human.SP.SPRepository
{
public class ChildRepository
{
public static async Task<ChildModel> SaveChild(ChildModel childModel)
{
using (HttpClient cons = HttpUtility.GetHttpClientConnection())
{
HttpResponseMessage res = await cons.PostAsJsonAsync("api/AddChild/", childModel);
res.EnsureSuccessStatusCode();
if (res.IsSuccessStatusCode)
{
childModel = await res.Content.ReadAsAsync<ChildModel>();
}
return childModel;
}
}
public static async Task<ChildModel> GetChildByID(int childID)
{
using (HttpClient cons = HttpUtility.GetHttpClientConnection())
{
ChildModel childModel = new ChildModel();
HttpResponseMessage res = await cons.GetAsync("api/GetChildByID/"+ childID);
res.EnsureSuccessStatusCode();
if (res.IsSuccessStatusCode)
{
childModel = await res.Content.ReadAsAsync<ChildModel>();
}
return childModel;
}
}
}
}
So, now SharePoint is able to connect and retrieve the data through the REST API connection. In the SharePoint web part, consuming the methods is the same like consuming any other method.