Web Api -Get and Post Data From Client(Html) And Server(ASPX) Side

- First create new project as web api project
- First Edit WebConfig File To  Add AppSettings

WebConfig:

<configuration> 
  <appSettings>
    <add key="DataSource" value="TEST" />
    <add key="InitialCatalog" value="TestProject" />
    <add key="UserID" value="sa" />
    <add key="Password" value="sql2008" />
    <add key="ServerName" value="localhost" />
  </appSettings>
</configuration>

 - Create New Folder "MyHelperLib" and Add New Class File "APICommonUtil.cs"

APICommonUtil.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

namespace D2D.WebAPI.MyHelperLib
{
    public class APICommonUtil
    {
        internal static string GetDBConnString()
        {
            SqlConnectionStringBuilder oSQLConnStr = new SqlConnectionStringBuilder();
            oSQLConnStr.DataSource = WebConfigurationManager.AppSettings["DataSource"].ToString();
            oSQLConnStr.InitialCatalog = WebConfigurationManager.AppSettings["InitialCatalog"].ToString();
            oSQLConnStr.UserID = WebConfigurationManager.AppSettings["UserID"].ToString();
            oSQLConnStr.Password = WebConfigurationManager.AppSettings["Password"].ToString();
            return oSQLConnStr.ToString();
        }
    }
}

- Add new Controller as TestController

TestController.cs :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Web.Http;
using System.Web.Routing;
using System.Web.Script.Serialization;

[RoutePrefix("api/test")]
public class UserController : ApiController
{
    #region "Connection String"
    string MyConnectionString = D2D.WebAPI.MyHelperLib.APICommonUtil.GetDBConnString();
    #endregion

    #region ''

    // GET api/<controller>
    public IEnumerable<string> GetValues()
    {
        return new string[] { "value1", "value2" };
    }

    // GET api/<controller>/5

    public string GetValue(int id)
    {
        return "value";
    }
    // POST api/<controller>
    public void PostValue([FromBody]string data)
    {

    }
    // PUT api/<controller>/5

    public void PutValue(int id, [FromBody]string data)
    {

    }
    // DELETE api/<controller>/5
    public void DeleteValue(int id)
    { }

    #endregion

    #region "Get"

 
    /**Get Method To Get Data**/

    [Route("UserInfo//{UserId}")]
    public DataSet GetRoleMenu(string UserId)
    {
        using (SqlConnection connection = new SqlConnection(MyConnectionString))
        {
            connection.Open();
            SqlCommand cmd = new SqlCommand("select * from UserInfo Where        UserId='"+UserId+"'", connection);       
            SqlDataAdapter adpt = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adpt.Fill(ds);
            ds.Tables[0].TableName = "Users";

            if (ds.Tables[0].Rows.Count == 0)
            {
                throw new Exception("Unable to find any User.");
            }
            else
            {
                return ds;
            }
        }
    }

    #endregion

    #region "POST api/<controller>"

    /*Post Method To Insert-Update User*/
  //With QueryString
    [HttpPost]
    [Route("UserInfo}")]
    public int SetUserInfo()
    {      
        NameValueCollection User= HttpUtility.ParseQueryString(Request.RequestUri.Query);
        SqlConnection connection = new SqlConnection(MyConnectionString);
        SqlCommand cmd = new SqlCommand("spAddUpdateUser", connection);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter adpt = new SqlDataAdapter(cmd);
        //string server = WebConfigurationManager.AppSettings["ServerName"].ToString();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@UserId", Item[0] == "" ? DBNull.Value.ToString() : User[0]);
        cmd.Parameters.AddWithValue("@UserName", Item[1] == "" ? DBNull.Value.ToString() : User[1]);   
        cmd.Connection = connection;
        int UserId= 0;
        try
        {
            connection.Open();
            UserId= (int)cmd.ExecuteScalar();
        }
        catch (Exception)
        { }
        finally
        {
            connection.Close();
        }
        return itemId;
    }
    #endregion
}

-  Create Wrapper.js File To Post And Get Data From Client Side i.e From HTML Page

Wrapper.jsvar APIWrapper = function (url) {

    var Priviliged = {
        myWebReqUrl: null,

        WebApiUriConstant: function () {
            try {
                var apiUri = "http://localhost/WebAPI/api";           
            } catch (e) {
                //alert(e.stack);
            }
            return apiUri;
        },

        CallWebAPI: function (webApiUrl, isAsync, myCallBackOnSuccess, myCallBackOnFailure) {

            var sData = "";
            try {
                $.ajax({
                    type: "GET",
                    url: webApiUrl,
                    data: sData,
                    async: isAsync,
                    contentType: "application/json;",
                    dataType: "json",
                    success: function (data) {
                        if (myCallBackOnSuccess != null); myCallBackOnSuccess(data);

                    },
                    error: function (xhr, status, error) {

                        var errorMessage = '';
                        try {
                            var xhr = JSON.parse(xhr.responseText);
                            errorMessage = xhr.ExceptionMessage;
                        } catch (e) {
                            errorMessage = "";
                        }

                        if (myCallBackOnFailure != null); myCallBackOnFailure(xhr.responseText);
                    }
                });
            }
            catch (e) {
                //alert(e.stack);
                //TODO - Display error message in popup screen.
            }
        },

        PostWebAPI: function (webApiUrl, isAsync, myCallBackOnSuccess, myCallBackOnFailure) {

            var sData = "";
            try {
                $.ajax({
                    type: "POST",
                    url: webApiUrl,
                    data: sData,
                    async: isAsync,
                    contentType: "application/json;",
                    dataType: "json",
                    sucess: function (data) {
                        if (myCallBackOnSuccess != null);
                        myCallBackOnSuccess(data);
                    },
                    error: function (xhr, satus, error) {
                        var errorMessage = "";
                        try {
                            var xhr = JSON.parse(xhr.responseText);
                            errorMessage = xhr.ExceptionMessage;
                        }
                        catch (e) {
                            errorMessage = "";
                        }
                        if (myCallBackOnFailure != null);
                        myCallBackOnFailure(xhr.responseText);
                    }
                });
            }
            catch (e) {
                //alert(e.stack);
                //TODO - Display error message in popup screen.
            }
        }
    };//End Priviliged


    var External = {

        /***** Start User Public function for the js  *****/

        InsertUser: function (UserDetail, myCallBackOnSuccess, myCallBackOnError) {
          Priviliged.myWebReqUrl = Priviliged.WebApiUriConstant() + "/UserInfo/?UserId=" + UserDetail.UserId+
                                                                                    "&UserName=" + UserDetail.UserName;
            Priviliged.PostD2DWebAPI(Priviliged.myWebReqUrl, false, callBackOnSuccess, callBackOnError)
        },      

        GetUser: function (UserId, callBackOnSuccess, callBackOnError) {
            Priviliged.myWebReqUrl = Priviliged.WebApiUriConstant() + "/UserInfo/" + UserId;
            Priviliged.CallD2DWebAPI(Priviliged.myWebReqUrl, true, callBackOnSuccess, callBackOnError)
        },
   }
}


-  Now Call This Wrapper.Js Function To HTML Page Where Data Is Sent

Test.html:


//Insert User
 var BindUser= ({ UserId: $('#txtUser').val(), UserName: $('#txtUserName').val()});
APIWrapper.InsertUser(BindUser, function (objUser) {                 }, function () {
                    });

//GET User
APIWrapper.GetUser(hdnUserId,function (objUser) {
            try {
                if (objUser!= null && objUser!= undefined && objUser.Users.length > 0) {
//Bind DropDownList With Get Data
                    $.each(objUser.Users, function (index, item) {
                        $("#cmbUser").get(0).options[$("#cmbUser").get(0).options.length] = new Option(item.UserName, item.UserId);                  
                    });
                }
            }
            catch (e) {
            }
        }, function () {
        });
/********************** TO Get And Post Data From Server Side ********************/
- First create ApiCall.cs

ApiCall.cs


using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Web;

namespace Helper
{
    public class ApiCall
    {
        private string JSON = "";

        //Get
        public string GetFromWebService(string url)
        {
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
            JSON = "";
            try
            {
                request.Timeout = 60000;
                WebResponse response = request.GetResponse();
                using (Stream responseStream = response.GetResponseStream())
                {
                    //System.Threading.Thread.Sleep(500);
                    StreamReader reader = new StreamReader(responseStream, Encoding.UTF8);
                    string value = reader.ReadToEnd();
                    JSON = value;
                    return value;
                }
            }
            catch (WebException ex)
            {
                WebResponse errorResponse = ex.Response;
                using (Stream responseStream = errorResponse.GetResponseStream())
                {
                    StreamReader reader = new StreamReader(responseStream, Encoding.GetEncoding("utf-8"));
                    String errorText = reader.ReadToEnd();
                    // log errorText
                }
                throw;
            }
        }

        // POST a JSON string
        public string PostToWebService(string url, string jsonContent)
        {
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
            request.Method = "POST";
            request.Timeout = 6000;

            System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();
            Byte[] byteArray = encoding.GetBytes(jsonContent);

            request.ContentLength = byteArray.Length;
            request.ContentType = @"application/json";

            using (Stream dataStream = request.GetRequestStream())
            {
                dataStream.Write(byteArray, 0, byteArray.Length);
            }

            long length = 0;
            try
            {
                using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
                {
                    length = response.ContentLength;
                    var value = new StreamReader(response.GetResponseStream()).ReadToEnd();
                    JSON = value;
                    return JSON;
                    //JSONObject = response;
                }
            }
            catch (WebException ex)
            {
                // Log exception and throw as for GET example above
                WebResponse errorResponse = ex.Response;
                using (Stream responseStream = errorResponse.GetResponseStream())
                {
                    StreamReader reader = new StreamReader(responseStream, Encoding.GetEncoding("utf-8"));
                    String errorText = reader.ReadToEnd();
                    // log errorText
                }
                throw;
            }
        }
    }
}
- Now Call ApiCall.cs Method From ASPX Code Behinde

Test.aspx //To Post Data
protected void btnSave_Click(object sender, EventArgs e)
        {
            string URL_ADDRESS = String.Format("http://localhost/WebAPI/api/UserInfo/{0}/{1}", txtUserId.Text, txtUserName.Text);
            ApiCall objAPICall = new ApiCall();
            objAPICall.PostToWebService(URL_ADDRESS, "");
            Page.ClientScript.RegisterStartupScript(this.GetType(), "Clear", "clear()", true);  
        }

read more

To Calculate Time Difference As HH:MM:SS With Today Date And Column Date

SELECT
 CAST(DATEDIFF(second, Dttm, getdate()) / 60 / 60 / 24 AS NVARCHAR(50)) + ':'+
     CAST(DATEDIFF(second, Dttm, getdate()) / 60 / 60 % 24  AS NVARCHAR(50)) + ':' +
     CAST(DATEDIFF(second, Dttm, getdate()) / 60 % 60 AS NVARCHAR(50)) as Hour
FROM YourTable

read more

Cursor To insert comma seprated value to table

SP :

CREATE PROCEDURE spCursor  
(
    @Name VARCHAR(250),   
)
AS
BEGIN
    DECLARE @lReturnCode INT
    DECLARE @lErrorCode INT
 
//declare temporary table
DECLARE @tempCursor Table (
        Name  Varchar(250)
        )

    SET NOCOUNT ON
    SELECT @lErrorCode = 0

//fnSplitIntoTable is function used to convert string to rows by seperating with commas 

BEGIN


    INSERT INTO @tempCursor
        SELECT  vcValuesList as 'CursorName' FROM fnSplitIntoTable(@Name ,',') 
   
        DECLARE @NameToPass VARCHAR(MAX)
        DECLARE cur4Name CURSOR FOR
        SELECT Name from @tempCursor

        OPEN   cur4Name

        FETCH NEXT FROM cur4Name INTO @NameToPass
         WHILE @@FETCH_STATUS = 0
            BEGIN
                SELECT @lReturnCode = 1                
                        BEGIN       
                            INSERT INTO YourTable(Name) VALUES (@NameToPass)
                        END
   
                    SELECT @lErrorCode = @@ERROR
                    IF @lErrorCode != 0 GOTO ErrorHandler
           
                     -- If we get here, no Error occurred
                    SELECT @lReturnCode = 0                 

                  --print @NameToPass
                 FETCH NEXT FROM cur4Name INTO @NameToPass
             END
             CLOSE cur4Name
             DEALLOCATE cur4Name
                        
    END
    -- On success, returns 0 as lReturnCode and inserted data
   
   
    -- Error Handler returns the return code
    ErrorHandler:

    SET NOCOUNT OFF

    SELECT 'lReturnCode'=@lReturnCode, 'lErrorCode'=@lErrorCode

    RETURN
END







Function To Split Comma To Rows :


CREATE FUNCTION [dbo].[fnSplitIntoTable] (@vcInString VARCHAR(MAX), @cDELIMITER CHAR(1))
RETURNS @OutTable TABLE (vcValuesList VARCHAR(255))
AS
BEGIN
DECLARE @pos INT
SET @vcInString = RTRIM(LTRIM(@vcInString))
SET @pos = CHARINDEX(@cDELIMITER,@vcInString,1)
WHILE @pos <> 0
BEGIN
INSERT INTO @OutTable
SELECT LTRIM(RTRIM(LEFT(@vcInString,@pos-1)))
SET @vcInString = SUBSTRING(@vcInString,@pos+1,LEN(@vcInString))
SET @pos = CHARINDEX(@cDELIMITER,@vcInString,1)
END
INSERT INTO @OutTable
SELECT RTRIM(LTRIM(@vcInString))
RETURN
END
 

read more