Web API using Stored Procedures and passed optional parameters :
Web API using Stored Procedures :
using System;
using System.Collections.Generic;
using System.Linq;
using System.ComponentModel.DataAnnotations.Schema;
using Sample.API.Database;
using Sample.API.Models;
using Sample.API;
using Sample.API.Controllers;
using Sample.Common;
using System.Web.Http;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.Entity.Core.EntityClient;
using System.Collections;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Core.Objects;
using System.Reflection;
using System.Net;
namespace Sample.Controllers
{
//[RoutePrefix("users/profileSearch/{keyword}")]
public class profileSearchController : TGApiController
{
#region DTO & Validation
public class profileSearchRequest
{
public string keyword { get; set; }
public string firstName { get; set; }
public string lastName { get; set; }
public string summary { get; set; }
public int userId { get; set; }
public int rowfrom { get; set; }
public int rowto { get; set; }
public int count { get; set; }
}
public class profileSearchResponseJSON
{
public int rowfrom { get; set; }
public int rowto { get; set; }
public int count { get; set; }
public int pageSize { get; set; }
public int pageing { get; set; }
public List<profileSearchResponse> profilesearch { get; set; }
public List<profileSearchlistsParameter> searchParameter { get; set; }
}
public class profileSearchResponse
{
public long id { get; set; }
public long userId { get; set; }
public string currentTitle { get; set; }
public string firstName { get; set; }
public string lastName { get; set; }
public string summary { get; set; }
public string emailAddress { get; set; }
public string pictureURL { get; set; }
public DateTime? created { get; set; }
public string profileName { get; set; }
public string city { get; set; }
public string firstNames { get; set; }
}
#endregion
//Route
[Route("search/profileSearch")]
public dynamic Post(profileSearchRequest request)
{
var lstRecSearch = new List<profileSearchResponse>();
string Cou = WebConfigurationManager.AppSettings["RecSearchpagesize"];
string pictureHostUrl = WebConfigurationManager.AppSettings["ProfilePictureURL"];
int PageSize = Int32.Parse(Cou.ToString());
List<SqlParameter> objSqlParams = new List<SqlParameter>();
string strFiled = string.Empty;
var searchlistParameter = new profileSearchlistsParameter();
List<profileSearchlistsParameter> lstsearchParameter = new List<profileSearchlistsParameter>();
if (request.keyword != null && request.keyword != "")
{
strFiled = " @keyword,";
var keywordParm = new SqlParameter { ParameterName = "keyword", Value = request.keyword };
objSqlParams.Add(keywordParm);
searchlistParameter.keyword = request.keyword;
}
if (request.firstName != null && request.firstName != "")
{
strFiled += "@firstName,";
var firstNameParm = new SqlParameter { ParameterName = "firstName", Value = request.firstName };
objSqlParams.Add(firstNameParm);
searchlistParameter.firstNames = request.firstName;
}
if (request.lastName != null && request.lastName != "")
{
strFiled += "@lastName,";
var firstNameParm = new SqlParameter { ParameterName = "lastName", Value = request.lastName };
objSqlParams.Add(firstNameParm);
searchlistParameter.mostplacelastName = request.lastName;
}
if (request.summary != null && request.summary != "")
{
strFiled += "@summary,";
var firstNameParm = new SqlParameter { ParameterName = "summary", Value = request.summary };
objSqlParams.Add(firstNameParm);
searchlistParameter.summary = request.summary;
}
if (request.userId != 0 && request.userId != null)
{
strFiled += "@userId,";
var firstNameParm = new SqlParameter { ParameterName = "userId", Value = request.userId };
objSqlParams.Add(firstNameParm);
searchlistParameter.userId = request.userId;
}
List<profileSearchResponse> getlist = null;
var _list = new profileSearchResponseJSON();
//Stored Procedures Call
SampleDb.Database.Connection.Open();
DbCommand cmd = SampleDb.Database.Connection.CreateCommand();
if (objSqlParams.Count != 0)
{
//Stored Procedures Name
cmd.CommandText = "searchprofiles";
cmd.Parameters.AddRange(objSqlParams.ToArray<SqlParameter>());
cmd.CommandType = CommandType.StoredProcedure;
using (var reader = cmd.ExecuteReader())
{
//MapToList
getlist = reader.MapToList<profileSearchResponse>();
}
}
var listprofile = new List<profileSearchResponse>();
if (getlist != null) {
foreach (var lstSearch in getlist.ToList())
{
string pictureUrl = string.Empty;
if (lstSearch.pictureURL != null)
{
pictureUrl = pictureHostUrl + lstSearch.pictureURL;
}
var response = new profileSearchResponse()
{
id = lstSearch.id,
userId = lstSearch.userId,
firstName = lstSearch.firstName,
lastName = lstSearch.lastName,
currentTitle = lstSearch.currentTitle,
summary = lstSearch.summary,
pictureURL = pictureUrl,
created = lstSearch.created,
profileName = lstSearch.profileName,
city = lstSearch.city,
firstNames = lstSearch.firstNames
};
listprofile.Add(response);
}
_list.pageSize = PageSize;
_list.pageing = _list.count / PageSize;
_list.profilesearch = listprofile;
_list.count = getlist.Count();
List<profileSearchResponseJSON> lstprofileSearch = new List<profileSearchResponseJSON>();
lstprofileSearch.Add(_list);
return lstprofileSearch;
}
else
{
_list.pageSize = PageSize;
_list.pageing = _list.count / PageSize;
_list.profilesearch = listprofile;
_list.count = _list.profilesearch.Count();
List<profileSearchResponseJSON> lstprofileSearch = new List<profileSearchResponseJSON>();
lstprofileSearch.Add(_list);
return lstprofileSearch;
}
}
}
}
//MapToList
public static List<T> MapToList<T>(this DbDataReader dr) where T : new()
{
if (dr != null && dr.HasRows)
{
var entity = typeof(T);
var entities = new List<T>();
var propDict = new Dictionary<string, PropertyInfo>();
var props = entity.GetProperties(BindingFlags.Instance | BindingFlags.Public);
propDict = props.ToDictionary(p => p.Name.ToUpper(), p => p);
while (dr.Read())
{
T newObject = new T();
for (int index = 0; index < dr.FieldCount; index++)
{
if (propDict.ContainsKey(dr.GetName(index).ToUpper()))
{
var info = propDict[dr.GetName(index).ToUpper()];
if ((info != null) && info.CanWrite)
{
var val = dr.GetValue(index);
info.SetValue(newObject, (val == DBNull.Value) ? null : val, null);
}
}
}
entities.Add(newObject);
}
return entities;
}
return null;
}
Run Program :
using Fiddler
http://localhost:1234/search/Search
Type:Post
User-Agent: Fiddler
Content-Type: application/json;charset=UTF-8
Host: localhost:5321
Content-Length: 18
//Passed Optional parameter
{"keyword":"test","firstname":"aaa"}
(or)
{"keyword":"test"}
No comments:
Post a Comment