Thursday, November 13, 2014

Get Yahoo Contact for C#






Get Yahoo Contact for C#

Create new Website

Create AppCode Folder

Create OAuthBase.cs in AppCode Folder like
using System;
using System.Security.Cryptography;
using System.Collections.Generic;
using System.Text;
using System.Web;

namespace OAuth
{
    public class OAuthBase
    {

        /// <summary>
        /// Provides a predefined set of algorithms that are supported officially by the protocol
        /// </summary>
        public enum SignatureTypes
        {
            HMACSHA1,
            PLAINTEXT,
            RSASHA1
        }

        /// <summary>
        /// Provides an internal structure to sort the query parameter
        /// </summary>
        protected class QueryParameter
        {
            private string name = null;
            private string value = null;

            public QueryParameter(string name, string value)
            {
                this.name = name;
                this.value = value;
            }

            public string Name
            {
                get { return name; }
            }

            public string Value
            {
                get { return value; }
            }
        }

        /// <summary>
        /// Comparer class used to perform the sorting of the query parameters
        /// </summary>
        protected class QueryParameterComparer : IComparer<QueryParameter>
        {

            #region IComparer<QueryParameter> Members

            public int Compare(QueryParameter x, QueryParameter y)
            {
                if (x.Name == y.Name)
                {
                    return string.Compare(x.Value, y.Value);
                }
                else
                {
                    return string.Compare(x.Name, y.Name);
                }
            }

            #endregion
        }

        protected const string OAuthVersion = "1.0";
        protected const string OAuthParameterPrefix = "oauth_";

        //
        // List of know and used oauth parameters' names
        //       
        protected const string OAuthConsumerKeyKey = "oauth_consumer_key";
        protected const string OAuthCallbackKey = "oauth_callback";
        protected const string OAuthVersionKey = "oauth_version";
        protected const string OAuthSignatureMethodKey = "oauth_signature_method";
        protected const string OAuthSignatureKey = "oauth_signature";
        protected const string OAuthTimestampKey = "oauth_timestamp";
        protected const string OAuthNonceKey = "oauth_nonce";
        protected const string OAuthTokenKey = "oauth_token";
        protected const string OAuthTokenSecretKey = "oauth_token_secret";

        protected const string HMACSHA1SignatureType = "HMAC-SHA1";
        protected const string PlainTextSignatureType = "PLAINTEXT";
        protected const string RSASHA1SignatureType = "RSA-SHA1";

        protected Random random = new Random();

        protected string unreservedChars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-_.~";

        /// <summary>
        /// Helper function to compute a hash value
        /// </summary>
        /// <param name="hashAlgorithm">The hashing algoirhtm used. If that algorithm needs some initialization, like HMAC and its derivatives, they should be initialized prior to passing it to this function</param>
        /// <param name="data">The data to hash</param>
        /// <returns>a Base64 string of the hash value</returns>
        private string ComputeHash(HashAlgorithm hashAlgorithm, string data)
        {
            if (hashAlgorithm == null)
            {
                throw new ArgumentNullException("hashAlgorithm");
            }

            if (string.IsNullOrEmpty(data))
            {
                throw new ArgumentNullException("data");
            }

            byte[] dataBuffer = System.Text.Encoding.ASCII.GetBytes(data);
            byte[] hashBytes = hashAlgorithm.ComputeHash(dataBuffer);

            return Convert.ToBase64String(hashBytes);
        }

        /// <summary>
        /// Internal function to cut out all non oauth query string parameters (all parameters not begining with "oauth_")
        /// </summary>
        /// <param name="parameters">The query string part of the Url</param>
        /// <returns>A list of QueryParameter each containing the parameter name and value</returns>
        private List<QueryParameter> GetQueryParameters(string parameters)
        {
            if (parameters.StartsWith("?"))
            {
                parameters = parameters.Remove(0, 1);
            }

            List<QueryParameter> result = new List<QueryParameter>();

            if (!string.IsNullOrEmpty(parameters))
            {
                string[] p = parameters.Split('&');
                foreach (string s in p)
                {
                    if (!string.IsNullOrEmpty(s) && !s.StartsWith(OAuthParameterPrefix))
                    {
                        if (s.IndexOf('=') > -1)
                        {
                            string[] temp = s.Split('=');
                            result.Add(new QueryParameter(temp[0], temp[1]));
                        }
                        else
                        {
                            result.Add(new QueryParameter(s, string.Empty));
                        }
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// This is a different Url Encode implementation since the default .NET one outputs the percent encoding in lower case.
        /// While this is not a problem with the percent encoding spec, it is used in upper case throughout OAuth
        /// </summary>
        /// <param name="value">The value to Url encode</param>
        /// <returns>Returns a Url encoded string</returns>
        protected string UrlEncode(string value)
        {
            StringBuilder result = new StringBuilder();

            foreach (char symbol in value)
            {
                if (unreservedChars.IndexOf(symbol) != -1)
                {
                    result.Append(symbol);
                }
                else
                {
                    result.Append('%' + String.Format("{0:X2}", (int)symbol));
                }
            }

            return result.ToString();
        }

        /// <summary>
        /// Normalizes the request parameters according to the spec
        /// </summary>
        /// <param name="parameters">The list of parameters already sorted</param>
        /// <returns>a string representing the normalized parameters</returns>
        protected string NormalizeRequestParameters(IList<QueryParameter> parameters)
        {
            StringBuilder sb = new StringBuilder();
            QueryParameter p = null;
            for (int i = 0; i < parameters.Count; i++)
            {
                p = parameters[i];
                sb.AppendFormat("{0}={1}", p.Name, p.Value);

                if (i < parameters.Count - 1)
                {
                    sb.Append("&");
                }
            }

            return sb.ToString();
        }

        /// <summary>
        /// Generate the signature base that is used to produce the signature
        /// </summary>
        /// <param name="url">The full url that needs to be signed including its non OAuth url parameters</param>
        /// <param name="consumerKey">The consumer key</param>       
        /// <param name="token">The token, if available. If not available pass null or an empty string</param>
        /// <param name="tokenSecret">The token secret, if available. If not available pass null or an empty string</param>
        /// <param name="httpMethod">The http method used. Must be a valid HTTP method verb (POST,GET,PUT, etc)</param>
        /// <param name="signatureType">The signature type. To use the default values use <see cref="OAuthBase.SignatureTypes">OAuthBase.SignatureTypes</see>.</param>
        /// <returns>The signature base</returns>
        public string GenerateSignatureBase(Uri url, string consumerKey, string token, string tokenSecret, string httpMethod, string timeStamp, string nonce, string signatureType, out string normalizedUrl, out string normalizedRequestParameters)
        {
            if (token == null)
            {
                token = string.Empty;
            }

            if (tokenSecret == null)
            {
                tokenSecret = string.Empty;
            }

            if (string.IsNullOrEmpty(consumerKey))
            {
                throw new ArgumentNullException("consumerKey");
            }

            if (string.IsNullOrEmpty(httpMethod))
            {
                throw new ArgumentNullException("httpMethod");
            }

            if (string.IsNullOrEmpty(signatureType))
            {
                throw new ArgumentNullException("signatureType");
            }

            normalizedUrl = null;
            normalizedRequestParameters = null;

            List<QueryParameter> parameters = GetQueryParameters(url.Query);
            parameters.Add(new QueryParameter(OAuthVersionKey, OAuthVersion));
            parameters.Add(new QueryParameter(OAuthNonceKey, nonce));
            parameters.Add(new QueryParameter(OAuthTimestampKey, timeStamp));
            parameters.Add(new QueryParameter(OAuthSignatureMethodKey, signatureType));
            parameters.Add(new QueryParameter(OAuthConsumerKeyKey, consumerKey));

            if (!string.IsNullOrEmpty(token))
            {
                parameters.Add(new QueryParameter(OAuthTokenKey, token));
            }

            parameters.Sort(new QueryParameterComparer());

            normalizedUrl = string.Format("{0}://{1}", url.Scheme, url.Host);
            if (!((url.Scheme == "http" && url.Port == 80) || (url.Scheme == "https" && url.Port == 443)))
            {
                normalizedUrl += ":" + url.Port;
            }
            normalizedUrl += url.AbsolutePath;
            normalizedRequestParameters = NormalizeRequestParameters(parameters);

            StringBuilder signatureBase = new StringBuilder();
            signatureBase.AppendFormat("{0}&", httpMethod.ToUpper());
            signatureBase.AppendFormat("{0}&", UrlEncode(normalizedUrl));
            signatureBase.AppendFormat("{0}", UrlEncode(normalizedRequestParameters));

            return signatureBase.ToString();
        }

        /// <summary>
        /// Generate the signature value based on the given signature base and hash algorithm
        /// </summary>
        /// <param name="signatureBase">The signature based as produced by the GenerateSignatureBase method or by any other means</param>
        /// <param name="hash">The hash algorithm used to perform the hashing. If the hashing algorithm requires initialization or a key it should be set prior to calling this method</param>
        /// <returns>A base64 string of the hash value</returns>
        public string GenerateSignatureUsingHash(string signatureBase, HashAlgorithm hash)
        {
            return ComputeHash(hash, signatureBase);
        }

        /// <summary>
        /// Generates a signature using the HMAC-SHA1 algorithm
        /// </summary>       
        /// <param name="url">The full url that needs to be signed including its non OAuth url parameters</param>
        /// <param name="consumerKey">The consumer key</param>
        /// <param name="consumerSecret">The consumer seceret</param>
        /// <param name="token">The token, if available. If not available pass null or an empty string</param>
        /// <param name="tokenSecret">The token secret, if available. If not available pass null or an empty string</param>
        /// <param name="httpMethod">The http method used. Must be a valid HTTP method verb (POST,GET,PUT, etc)</param>
        /// <returns>A base64 string of the hash value</returns>
        public string GenerateSignature(Uri url, string consumerKey, string consumerSecret, string token, string tokenSecret, string httpMethod, string timeStamp, string nonce, out string normalizedUrl, out string normalizedRequestParameters)
        {
            return GenerateSignature(url, consumerKey, consumerSecret, token, tokenSecret, httpMethod, timeStamp, nonce, SignatureTypes.HMACSHA1, out normalizedUrl, out normalizedRequestParameters);
        }

        /// <summary>
        /// Generates a signature using the specified signatureType
        /// </summary>       
        /// <param name="url">The full url that needs to be signed including its non OAuth url parameters</param>
        /// <param name="consumerKey">The consumer key</param>
        /// <param name="consumerSecret">The consumer seceret</param>
        /// <param name="token">The token, if available. If not available pass null or an empty string</param>
        /// <param name="tokenSecret">The token secret, if available. If not available pass null or an empty string</param>
        /// <param name="httpMethod">The http method used. Must be a valid HTTP method verb (POST,GET,PUT, etc)</param>
        /// <param name="signatureType">The type of signature to use</param>
        /// <returns>A base64 string of the hash value</returns>
        public string GenerateSignature(Uri url, string consumerKey, string consumerSecret, string token, string tokenSecret, string httpMethod, string timeStamp, string nonce, SignatureTypes signatureType, out string normalizedUrl, out string normalizedRequestParameters)
        {
            normalizedUrl = null;
            normalizedRequestParameters = null;

            switch (signatureType)
            {
                case SignatureTypes.PLAINTEXT:
                    return HttpUtility.UrlEncode(string.Format("{0}&{1}", consumerSecret, tokenSecret));
                case SignatureTypes.HMACSHA1:
                    string signatureBase = GenerateSignatureBase(url, consumerKey, token, tokenSecret, httpMethod, timeStamp, nonce, HMACSHA1SignatureType, out normalizedUrl, out normalizedRequestParameters);

                    HMACSHA1 hmacsha1 = new HMACSHA1();
                    hmacsha1.Key = Encoding.ASCII.GetBytes(string.Format("{0}&{1}", UrlEncode(consumerSecret), string.IsNullOrEmpty(tokenSecret) ? "" : UrlEncode(tokenSecret)));

                    return GenerateSignatureUsingHash(signatureBase, hmacsha1);
                case SignatureTypes.RSASHA1:
                    throw new NotImplementedException();
                default:
                    throw new ArgumentException("Unknown signature type", "signatureType");
            }
        }

        /// <summary>
        /// Generate the timestamp for the signature       
        /// </summary>
        /// <returns></returns>
      
        public virtual string GenerateTimeStamp()
        {
            // Default implementation of UNIX time of the current UTC time
            TimeSpan ts = DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0, 0);
            string timeStamp = ts.TotalSeconds.ToString();
            timeStamp = timeStamp.Substring(0, timeStamp.IndexOf("."));
            return timeStamp;
        }


        /// <summary>
        /// Generate a nonce
        /// </summary>
        /// <returns></returns>
        public virtual string GenerateNonce()
        {
            // Just a simple implementation of a random number between 123400 and 9999999
            return random.Next(123400, 9999999).ToString();
        }

    }
}



Download OAuth.Net.Common.dll and reference to bin folder

Create oauth-test.aspx page like
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="oauth-test.aspx.cs" Inherits="test_oauth_test" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="Button1" runat="server" Text="Connect to Yahoo!" OnClick="Button1_Click" />
        <asp:Label runat="server" ID="ResponseMessage1"></asp:Label>
        <asp:GridView ID="grvMyFriends" runat="server" AutoGenerateColumns="false" CellPadding="5"
            GridLines="None">
            <HeaderStyle BackColor="#025A8E" Font-Bold="true" ForeColor="White" HorizontalAlign="Left" />
            <RowStyle BackColor="#DFDFDF" HorizontalAlign="Left" />
            <AlternatingRowStyle BackColor="#F0F0F0" HorizontalAlign="Left" />
            <Columns>
                <asp:TemplateField HeaderText="Email">
                    <ItemTemplate>
                        <%# Container.DataItem %>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
            <EmptyDataTemplate>
                No Friend List Found
            </EmptyDataTemplate>
        </asp:GridView>
    </div>
    </form>
</body>
</html>
Create oauth-test.aspx.cs file like
using System;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Net;
using System.IO;
using OAuth.Net.Common;
using OAuth.Net.Components;
using OAuth.Net.Consumer;
using OAuth;
using System.Text;
using System.Xml;
using System.Collections;
using System.Collections.Generic;
using System.Net.Mail;
using System.Text.RegularExpressions;

public partial class test_oauth_test : System.Web.UI.Page
{

    string ConsumerKey = ConfigurationManager.AppSettings["ConsumerKey"].ToString();
    string ConsumerSecret = ConfigurationManager.AppSettings["ConsumerSecret"].ToString();
   
   

    public string OauthVerifier
    {
        get
        {
            try
            {
                if (!string.IsNullOrEmpty(Session["Oauth_Verifier"].ToString()))
                    return Session["Oauth_Verifier"].ToString();
                else
                    return string.Empty;
            }
            catch
            {
                return string.Empty;
            }
        }
        set
        {
            Session["Oauth_Verifier"] = value;
        }
    }
    public string OauthToken
    {
        get
        {
            if (!string.IsNullOrEmpty(Session["Oauth_Token"].ToString()))
                return Session["Oauth_Token"].ToString();
            else
                return string.Empty;
        }
        set
        {
            Session["Oauth_Token"] = value;
        }
    }
    public string OauthTokenSecret
    {
        get
        {
            if (!string.IsNullOrEmpty(Session["Oauth_Token_Secret"].ToString()))
                return Session["Oauth_Token_Secret"].ToString();
            else
                return string.Empty;
        }
        set
        {
            Session["Oauth_Token_Secret"] = value;
        }
    }
    public string OauthSessionHandle
    {
        get
        {
            if (!string.IsNullOrEmpty(Session["Oauth_Session_Handle"].ToString()))
                return Session["Oauth_Session_Handle"].ToString();
            else
                return string.Empty;
        }
        set
        {
            Session["Oauth_Session_Handle"] = value;
        }
    }
    public string OauthYahooGuid
    {
        get
        {
            try
            {
                if (!string.IsNullOrEmpty(Session["Oauth_Yahoo_Guid"].ToString()))
                    return Session["Oauth_Yahoo_Guid"].ToString();
                else
                    return string.Empty;
            }
            catch
            {
                return string.Empty;
            }
        }
        set
        {
            Session["Oauth_Yahoo_Guid"] = value;
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            string oauth_token = Request["oauth_token"];
            string oauth_verifier = Request["oauth_verifier"];

            if (!string.IsNullOrEmpty(oauth_verifier) && oauth_verifier != "")
            {
                Button1.Visible = false;
                OauthToken = oauth_token;
                OauthVerifier = oauth_verifier;
                RegisterStartupScript("refresh", "<script type='text/javascript'>window.opener.location = 'oauth-test.aspx'; self.close();</script>");
            }
            else if (!string.IsNullOrEmpty(OauthVerifier))
            {
                Button1.Visible = false;
                if (string.IsNullOrEmpty(OauthYahooGuid))
                    GetAccessToken(OauthToken, OauthVerifier);
                //RefreshToken();
                RetriveContacts();
            }
        }

    }
    private string GetRequestToken()
    {
        string authorizationUrl = string.Empty;
        OAuthBase oauth = new OAuthBase();

        Uri uri = new Uri("https://api.login.yahoo.com/oauth/v2/get_request_token");
        string nonce = oauth.GenerateNonce();
        string timeStamp = oauth.GenerateTimeStamp();
        string normalizedUrl;
        string normalizedRequestParameters;
        string sig = oauth.GenerateSignature(uri, ConsumerKey, ConsumerSecret, string.Empty, string.Empty, "GET", timeStamp, nonce, OAuthBase.SignatureTypes.PLAINTEXT, out normalizedUrl, out normalizedRequestParameters); //OAuthBase.SignatureTypes.HMACSHA1
        StringBuilder sbRequestToken = new StringBuilder(uri.ToString());
        sbRequestToken.AppendFormat("?oauth_nonce={0}&", nonce);
        sbRequestToken.AppendFormat("oauth_timestamp={0}&", timeStamp);
        sbRequestToken.AppendFormat("oauth_consumer_key={0}&", ConsumerKey);
        sbRequestToken.AppendFormat("oauth_signature_method={0}&", "PLAINTEXT"); //HMAC-SHA1
        sbRequestToken.AppendFormat("oauth_signature={0}&", sig);
        sbRequestToken.AppendFormat("oauth_version={0}&", "1.0");
        sbRequestToken.AppendFormat("oauth_callback={0}", HttpUtility.UrlEncode("http://localhost:7450/GetYahooContact/oauth-test.aspx"));
        //Response.Write(sbRequestToken.ToString());
        //Response.End();

        try
        {
            string returnStr = string.Empty;
            string[] returnData;

            HttpWebRequest req = (HttpWebRequest)WebRequest.Create(sbRequestToken.ToString());
            HttpWebResponse res = (HttpWebResponse)req.GetResponse();
            StreamReader streamReader = new StreamReader(res.GetResponseStream());
            returnStr = streamReader.ReadToEnd();
            returnData = returnStr.Split(new Char[] { '&' });
            //Response.Write(returnStr);

            int index;
            if (returnData.Length > 0)
            {
                //index = returnData[0].IndexOf("=");
                //string oauth_token = returnData[0].Substring(index + 1);
                //Session["Oauth_Token"] = oauth_token;

                index = returnData[1].IndexOf("=");
                string oauth_token_secret = returnData[1].Substring(index + 1);
                OauthTokenSecret = oauth_token_secret;

                //index = returnData[2].IndexOf("=");
                //int oauth_expires_in;
                //Int32.TryParse(returnData[2].Substring(index + 1), out oauth_expires_in);
                //Session["Oauth_Expires_In"] = oauth_expires_in;

                index = returnData[3].IndexOf("=");
                string oauth_request_auth_url = returnData[3].Substring(index + 1);
                authorizationUrl = HttpUtility.UrlDecode(oauth_request_auth_url);
            }
        }
        catch (WebException ex)
        {
            Response.Write(ex.Message);
        }
        return authorizationUrl;
    }
    private void RedirectUserForAuthorization(string authorizationUrl)
    {
        //Response.Redirect(authorizationUrl);
        RegisterStartupScript("openwin", "<script type='text/javascript'>window.open('" + authorizationUrl + "','mywindow', 'left=250,top=50,menubar=0,resizable=0,location=1,toolbar=0,status=1,scrollbars=0,width=500,height=455');</script>");
    }
    private void GetAccessToken(string oauth_token, string oauth_verifier)
    {
        OAuthBase oauth = new OAuthBase();

        Uri uri = new Uri("https://api.login.yahoo.com/oauth/v2/get_token");
        string nonce = oauth.GenerateNonce();
        string timeStamp = oauth.GenerateTimeStamp();
        string sig = ConsumerSecret + "%26" + OauthTokenSecret;

        StringBuilder sbAccessToken = new StringBuilder(uri.ToString());
        sbAccessToken.AppendFormat("?oauth_consumer_key={0}&", ConsumerKey);
        sbAccessToken.AppendFormat("oauth_signature_method={0}&", "PLAINTEXT"); //HMAC-SHA1
        sbAccessToken.AppendFormat("oauth_signature={0}&", sig);
        sbAccessToken.AppendFormat("oauth_timestamp={0}&", timeStamp);
        sbAccessToken.AppendFormat("oauth_version={0}&", "1.0");
        sbAccessToken.AppendFormat("oauth_token={0}&", oauth_token);
        sbAccessToken.AppendFormat("oauth_nonce={0}&", nonce);
        sbAccessToken.AppendFormat("oauth_verifier={0}", oauth_verifier);
        //Response.Write(sbAccessToken.ToString());
        //Response.End();

        try
        {
            string returnStr = string.Empty;
            string[] returnData;

            HttpWebRequest req = (HttpWebRequest)WebRequest.Create(sbAccessToken.ToString());
            HttpWebResponse res = (HttpWebResponse)req.GetResponse();
            StreamReader streamReader = new StreamReader(res.GetResponseStream());
            returnStr = streamReader.ReadToEnd();
            returnData = returnStr.Split(new Char[] { '&' });
            //Response.Write(returnStr);
            //Response.End();

            int index;
            if (returnData.Length > 0)
            {
                index = returnData[0].IndexOf("=");
                OauthToken = returnData[0].Substring(index + 1);

                index = returnData[1].IndexOf("=");
                string oauth_token_secret = returnData[1].Substring(index + 1);
                OauthTokenSecret = oauth_token_secret;

                //index = returnData[2].IndexOf("=");
                //int oauth_expires_in;
                //Int32.TryParse(returnData[2].Substring(index + 1), out oauth_expires_in);

                index = returnData[3].IndexOf("=");
                string oauth_session_handle = returnData[3].Substring(index + 1);
                OauthSessionHandle = oauth_session_handle;

                //index = returnData[4].IndexOf("=");
                //int oauth_authorization_expires_in;
                //Int32.TryParse(returnData[4].Substring(index + 1), out oauth_authorization_expires_in);

                index = returnData[5].IndexOf("=");
                string xoauth_yahoo_guid = returnData[5].Substring(index + 1);
                OauthYahooGuid = xoauth_yahoo_guid;
            }
        }
        catch (WebException ex)
        {
            Response.Write(ex.Message);
        }
    }
    private void RefreshToken()
    {
        OAuthBase oauth = new OAuthBase();

        Uri uri = new Uri("https://api.login.yahoo.com/oauth/v2/get_token");
        string nonce = oauth.GenerateNonce();
        string timeStamp = oauth.GenerateTimeStamp();
        string sig = ConsumerSecret + "%26" + OauthTokenSecret;

        StringBuilder sbrefreshToken = new StringBuilder(uri.ToString());
        sbrefreshToken.AppendFormat("?oauth_consumer_key={0}&", ConsumerKey);
        sbrefreshToken.AppendFormat("oauth_signature_method={0}&", "PLAINTEXT"); //HMAC-SHA1
        sbrefreshToken.AppendFormat("oauth_signature={0}&", sig);
        sbrefreshToken.AppendFormat("oauth_timestamp={0}&", timeStamp);
        sbrefreshToken.AppendFormat("oauth_version={0}&", "1.0");
        sbrefreshToken.AppendFormat("oauth_token={0}&", OauthToken);
        sbrefreshToken.AppendFormat("oauth_session_handle={0}&", OauthSessionHandle);
        sbrefreshToken.AppendFormat("oauth_nonce={0}", nonce);

        try
        {
            string returnStr = string.Empty;
            string[] returnData;

            HttpWebRequest req = (HttpWebRequest)WebRequest.Create(sbrefreshToken.ToString());
            HttpWebResponse res = (HttpWebResponse)req.GetResponse();
            StreamReader streamReader = new StreamReader(res.GetResponseStream());
            returnStr = streamReader.ReadToEnd();
            returnData = returnStr.Split(new Char[] { '&' });
            //Response.Write(returnStr);

            int index;
            if (returnData.Length > 0)
            {
                index = returnData[0].IndexOf("=");
                string oauth_token = returnData[0].Substring(index + 1);
                OauthToken = oauth_token;

                index = returnData[1].IndexOf("=");
                string oauth_token_secret = returnData[1].Substring(index + 1);
                OauthTokenSecret = oauth_token_secret;

                //index = returnData[2].IndexOf("=");
                //int oauth_expires_in;
                //Int32.TryParse(returnData[2].Substring(index + 1), out oauth_expires_in);

                index = returnData[3].IndexOf("=");
                string oauth_session_handle = returnData[3].Substring(index + 1);
                OauthSessionHandle = oauth_session_handle;

                //index = returnData[4].IndexOf("=");
                //int oauth_authorization_expires_in;
                //Int32.TryParse(returnData[4].Substring(index + 1), out oauth_authorization_expires_in);

                index = returnData[5].IndexOf("=");
                string xoauth_yahoo_guid = returnData[5].Substring(index + 1);
                OauthYahooGuid = xoauth_yahoo_guid;
            }
        }
        catch (WebException ex)
        {
            Response.Write(ex.Message);
        }
    }
    private void RetriveContacts()
    {
        OAuthBase oauth = new OAuthBase();

        Uri uri = new Uri("http://social.yahooapis.com/v1/user/" + OauthYahooGuid + "/contacts?format=XML");
        string nonce = oauth.GenerateNonce();
        string timeStamp = oauth.GenerateTimeStamp();
        string normalizedUrl;
        string normalizedRequestParameters;
        string sig = oauth.GenerateSignature(uri, ConsumerKey, ConsumerSecret, OauthToken, OauthTokenSecret, "GET", timeStamp, nonce, OAuthBase.SignatureTypes.HMACSHA1, out normalizedUrl, out normalizedRequestParameters);

        StringBuilder sbGetContacts = new StringBuilder(uri.ToString());

        //Response.Write("URL: " + sbGetContacts.ToString());
        //Response.End();

        try
        {
            string returnStr = string.Empty;
            HttpWebRequest req = (HttpWebRequest)WebRequest.Create(sbGetContacts.ToString());
            req.Method = "GET";

            string authHeader = "Authorization: OAuth " +
            "realm=\"yahooapis.com\"" +
            ",oauth_consumer_key=\"" + ConsumerKey + "\"" +
            ",oauth_nonce=\"" + nonce + "\"" +
            ",oauth_signature_method=\"HMAC-SHA1\"" +
            ",oauth_timestamp=\"" + timeStamp + "\"" +
            ",oauth_token=\"" + OauthToken + "\"" +
            ",oauth_version=\"1.0\"" +
            ",oauth_signature=\"" + HttpUtility.UrlEncode(sig) + "\"";

            //Response.Write("</br>Headers: " + authHeader);

            req.Headers.Add(authHeader);

            HttpWebResponse res = (HttpWebResponse)req.GetResponse();
            StreamReader streamReader = new StreamReader(res.GetResponseStream());
            returnStr = streamReader.ReadToEnd();
            XmlDocument xmldoc = new XmlDocument();
            xmldoc.LoadXml(returnStr);
            XmlNodeList elemList = xmldoc.DocumentElement.GetElementsByTagName("fields");

            ArrayList emails = new ArrayList();
            for (int i = 0; i < elemList.Count; i++)
            {
                if (elemList[i].ChildNodes[1].InnerText == "email")
                    emails.Add(elemList[i].ChildNodes[2].InnerText);
                //Response.Write(elemList[i].ChildNodes[2].InnerText + "<br/>");
            }
            grvMyFriends.DataSource = emails;
            grvMyFriends.DataBind();
        }
        #region error
        catch (WebException ex)
        {
            //Response.Write(ex.Message);
            Response.Write("<br/>" + ex.Message + "</br>xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");
            Response.Write("<br/>length: " + ex.Source.Length.ToString());
            Response.Write("<br/>stack trace: " + ex.StackTrace);
            Response.Write("<br/>status: " + ex.Status.ToString());
            HttpWebResponse res = (HttpWebResponse)ex.Response;
            int code = Convert.ToInt32(res.StatusCode);

            Response.Write("<br/>Status Code: (" + code.ToString() + ") " + res.StatusCode.ToString());
            Response.Write("<br/>Status Description: " + res.StatusDescription);

            if (ex.InnerException != null)
            {
                Response.Write("<br/>innerexception: " + ex.InnerException.Message);
            }

            if (ex.Source.Length > 0)
                Response.Write("<br/>source: " + ex.Source.ToString());

            if (res != null)
            {
                for (int i = 0; i < res.Headers.Count; i++)
                {
                    Response.Write("<br/>headers: " + i.ToString() + ": " + res.Headers[i]);
                }
            }
        }
        #endregion error
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string authorizationUrl = string.Empty;
        authorizationUrl = GetRequestToken();
        RedirectUserForAuthorization(authorizationUrl);
    }
  
}




Regards,                           
Manish Siddhapara || +91-97258 78998


Restore sql database by query

RESTORE DATABASE [Database name] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.OMEXDEV\MSSQL\Backup\ump.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

Friday, September 19, 2014

Install MVC website on IIS7

1st install ASP MVC, then run one of the following depending on your architecture:
 32bit (x86) Windows
%windir%\Microsoft.NET\Framework\v4.0.30319\aspnet_regiis.exe -ir
 64bit (x64) Windows
%windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -ir
 also check this thread for more info: ASP.NET MVC on IIS 7.5
 You could also need to recreate your application/site after these steps.

Query execution important for SQL Server performance SQL server


SQL Server performance monitoring depends on the performance goals. The first step in monitoring is to determine optimal performance for your server and create performance trends based on the captured metric information. If performance is not satisfactory, it’s necessary to diagnose performance problems and find their origin. This involves testing how different queries and applications affect performance


Whatever the performance goals are, they all have one thing in common – to ensure optimal performance. It doesn’t have to be the best performance possible, sometimes you have to tradeoff between needs and cost. To achieve this, analyze available hardware resources and their performance first. Then, analyze how the resources are used. Poor designed databases, inefficient queries, bad indexing, etc. can significantly slow down the whole system
One of the first issues you notice when working with a SQL Server database is the response time. If you execute a stored procedure, query, or search for specific data, you’d like the results to appear quickly. If not, is there anything that can be done to make the query run faster?
The SQL Server query execution plan can answer this question and help you diagnose the problem, if it exists. The execution plan can also help you write efficient queries, create the right indexes that quickly provide only the rows you need, instead of searching through millions of records, etc.
What is a SQL Server query execution plan?
A query plan, execution plan, or query execution plan is an algorithm showing a set of steps in a specific order that is executed to access data in a database

SQL Server query execution plan algorithm
A query plan shows how a query was executed, or how it will be executed which is significant for troubleshooting query performance issues. Executing a SELECT statement to find out its query plan and effect on SQL Server performance can be acceptable, but executing UPDATEs to find that out is not an option. The plan is calculated by a SQL Server component Query Optimizer using minimum of server resources. When creating the SQL Server query execution plan, the number of database objects involved, joins, indexes and their availability, number of output columns, and more is considered
When a new query is executed, Query Optimizer evaluates the query plan, optimizes and compiles it, and stores it in the plan cache. The plan cache is a part of SQL Server buffer where data and query plans are stored (buffered), so they can be reused later
When a query is executed, Query Optimizer first searches the plan cache looking for a query plan that can be reused, thus making the execution faster. If there’s no query plan that can be reused, a new one has to be created, which takes time and therefore makes query execution last longer
A very useful characteristic of query plans is that when a stored procedure is executed, the query plan is created for the stored procedure name and the same query plan will be reused whenever the stored procedure is executed, despite the values specified for procedure parameters. When it comes to executing ad hoc queries, query plans are created based on complete code, so different parameters or any change in code will prevent reuse of the existing plan. This clearly indicates what should be done to make your code run faster – wrap it up as stored procedures or functions, and the existing query plans will be reused and therefore code will be executed much faster
The slow execution of ad hoc queries can be mitigated by using the Optimize for ad hoc workloads option, introduced in SQL Server 2008. The option optimizes the plan cache use, as it solves the situation when query plans executed only once fill up the plan cache. As the buffer cache is used for both data and plan buffering, and the percentage of cache used for each changes in time depending on the current situation, it’s necessary to use the cache wisely. Instead of buffering the whole plan, when the option is set to “True”, only a fragment of the plan is buffered when the query is executed for the first time. When an ad hoc query is executed for the second time, its complete plan is buffered in the cache
The default value is False. The option in available in the SQL Server instance properties
Right-click the SQL Server instance in the SQL Server Management Studio Object Explorer
Select the Advanced tab
In the Miscellaneous options list, set the Optimize for ad hoc workloads option to True
Server properties dialog - setting the Optimize for ad hoc workloads option to True
Another way to change this option is to use T-SQL

sp_CONFIGURE 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'optimize for ad hoc workloads', 1
GO
RECONFIGURE
GO


However, keep in mind that this is an advanced option, therefore recommended to be modified only by advanced users
How to remove query plans in SQL Server?
Query plans are automatically removed from the plan cache when the SQL Server instance is restarted or memory pressure appears. Not reused plans and the ones that can be easily recompiled are first removed to resolve the memory pressure situation
To remove all query plans from cache manually, use the following statement

1

DBCC FREEPROCCACHE WITH NO_INFOMSGS

Using this statement is recommended for advanced users only and it should be never be used on a production server before previous analysis of the instance state, as it will cause recompilation of stored procedures and thus slowdown the system performance
How to recompile query plans in SQL Server?
Query plans are automatically recompiled whenever a cached plan becomes invalid. A plan can become invalid (or obsolete) if there is a database change. The most common causes are changes of the objects used by the stored procedure: the stored procedure itself, the referenced table or view, indexes used by the plan, statistics, significant key data changes, etc. Next time the same stored procedure is executed, Query Optimizer will find an invalid plan in the cache, and will recompile a new one. The good news is that starting with SQL Server 2005, only the statements that need recompilation are recompiled, not the whole batch
A SQL Server query plan can also be recompiled explicitly when the stored procedure is executed using the WITH RECOMPILE clause, or the sp_recompile stored procedure is executed
In this article we introduced SQL Server query execution plans – explained what they are, how they are created, and how they affect SQL Server performance. As shown, a query plan reuse makes the time needed for execution shorter, therefore it’s recommended to save the frequently used queries as stored procedures. In next part of this series, we’ll present various methods for viewing the query execution plans

- See more at: http://www.sqlshack.com/sql-server-query-execution-plans-basics

Saturday, August 30, 2014

Find Trigger with table name in sql server

SELECT
     sysobjects.name AS trigger_name
    ,USER_NAME(sysobjects.uid) AS trigger_owner
    ,s.name AS table_schema
    ,OBJECT_NAME(parent_obj) AS table_name
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects INNER JOIN sys.tables t
    ON sysobjects.parent_obj = t.object_id
INNER JOIN sys.schemas
    ON t.schema_id = s.schema_id
WHERE sysobjects.type = 'TR'

Wednesday, August 27, 2014

Link sql server to another sql server

USE [master]
GO

/****** Object:  LinkedServer [Server name or IP]    Script Date: 8/27/2014 4:50:48 AM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'Server name or IP', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Server name or IP',@useself=N'False',@locallogin=NULL,@rmtuser=N'DB user name',@rmtpassword='paessword'

GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO


Wednesday, August 13, 2014

Adding DB_executor Role sql server

SQL Server has several fixed database roles such as db_datareader and db_datawriter, which grants the user read and write access respectively to all the tables in the database. Curiously there is no role to grant a user permission to execute stored procedures, but fortunately this is easily resolved by creating a new role.

The following SQL creates the new role in a database, and then grants it execute rights :
-- Create a db_executor role
CREATE ROLE db_executor

-- Grant execute rights to the new role
GRANT EXECUTE TO db_executor
A user can then be added to the new role, much like the db_datareader and db_datawriter roles. 

If you want to check that the role has been created and then add a user to the role, right click on a user in the database in SQL Server Management Studio and select ‘Properties’. In the ‘Database role membership’ control notice that the new db_executor role now appears, click the checkbox to add the user to the role, as below : 

Adding a db_executor role

Alternatively the user can be added to the role in code using the following SQL :
-- to allocate a user to the new role :
EXEC sp_addrolemember 'db_executor','SQLMatters'
A user added to this role will be able to execute all stored procedures in the database, including ones created in the future.

Opps Part 1 : Abstraction

  Abstraction in C# is a fundamental concept of object-oriented programming (OOP) that allows developers t...