Get DB Connection that store in Security store

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using Microsoft.SharePoint;
using Microsoft.BusinessData.Infrastructure.SecureStore;
using Microsoft.Office.SecureStoreService.Server;
using Microsoft.SharePoint.Administration;
using System.Security;
using Microsoft.SharePoint.Administration.Claims;
using System.Globalization;
using Microsoft.SharePoint.Utilities;
using System.Security.Principal;
using Microsoft.Office.Server.Diagnostics;
using System.Web;

namespace sharepoint.util
{
    public class SecureStoreCredentialLib
    {
        private const string SYSTEMACCOUNT = @"domain\\ishareadmin";

        /// <summary>
        /// Get DB Connection that store in Security store. You should add a Generic field to save your DB Instance.
        /// </summary>
        /// <param name="CredentialName"></param>
        /// <returns></returns>
        public string GetConnectionString(string CredentialName)
        {
            var connection = string.Empty;
            var username = string.Empty;
            var password = string.Empty;
            var dbinstance = string.Empty;

            SecureStoreCredentialCollection credentials = null;
            SPSecurity.RunWithElevatedPrivileges(() =>
            {
                SPContext.Current.Web.AllowUnsafeUpdates = true;
                var adminUser = SPContext.Current.Web.EnsureUser(SYSTEMACCOUNT);
                SPContext.Current.Web.AllowUnsafeUpdates = false;
                using (var site = new SPSite(SPContext.Current.Site.ID, adminUser.UserToken))
                {
                    var provider = new SecureStoreProvider();
                    var context = SPServiceContext.GetContext(site);
                    provider.Context = context;
                    credentials = provider.GetCredentials(CredentialName);
                }
            });

            if (credentials != null)
            {
                foreach (SecureStoreCredential sc in credentials)
                {
                    switch (sc.CredentialType)
                    {
                        case SecureStoreCredentialType.Generic:
                            dbinstance = SecureStoreCredentialLib.ToClrString(sc.Credential);
                            break;
                        case SecureStoreCredentialType.Key:
                            break;
                        case SecureStoreCredentialType.Password:
                            password = SecureStoreCredentialLib.ToClrString(sc.Credential);
                            break;
                        case SecureStoreCredentialType.Pin:
                            break;
                        case SecureStoreCredentialType.UserName:
                            username = SecureStoreCredentialLib.ToClrString(sc.Credential);
                            break;
                        case SecureStoreCredentialType.WindowsPassword:
                            break;
                        case SecureStoreCredentialType.WindowsUserName:
                            break;
                        default:
                            break;
                    }
                }

                connection = string.Format(
                    "Data Source={0};Initial Catalog=iShare2_SiteInfo;User ID={1};Password={2};Persist Security Info=True;",
                    dbinstance,
                    username,
                    password
                );
            }
            else
            {
                throw new Exception("Credentials is null. Cannot get credentials.");
            }

            return connection;
        }

        public SecureStoreCredentialCollection GetCredentials(string targetApplicationID)
        {
            SecureStoreCredentialCollection credentials = null;

            var iss = GetISecureStore();
            var app = iss.GetApplication(targetApplicationID);

            switch (app.Type)
            {
                case TargetApplicationType.Group:
                case TargetApplicationType.Individual:
                    credentials = iss.GetCredentials(targetApplicationID);
                    break;
                case TargetApplicationType.GroupWithTicketing:
                case TargetApplicationType.IndividualWithTicketing:
                    //Didn't test...
                    var ticket = iss.IssueTicket();
                    credentials = iss.RedeemTicket(targetApplicationID, ticket);
                    break;
                case TargetApplicationType.RestrictedGroup:
                case TargetApplicationType.RestrictedIndividual:
                    break;
                default:
                    break;
            }

            return credentials;
        }


        public void AddCredentials(string userName, string userPassword, string DBInstance, string targetApplicationID, string targetApplicationContactEmail)
        {
            CreateTargetApplication(targetApplicationID, targetApplicationContactEmail);

            var iss = GetISecureStore();
            var applicationFields = iss.GetApplicationFields(targetApplicationID);
            var creds = new List<ISecureStoreCredential>(applicationFields.Count);
            var ssClaims = iss.GetApplicationAdministratorClaims(targetApplicationID);

            using (var credentials = new SecureStoreCredentialCollection(creds))
            {
                foreach (var ssClaim in ssClaims)
                {
                    foreach (var taf in applicationFields)
                    {
                        switch (taf.CredentialType)
                        {
                            case SecureStoreCredentialType.Generic:
                                creds.Add(new SecureStoreCredential(MakeSecureString(DBInstance), SecureStoreCredentialType.Generic));
                                break;
                            case SecureStoreCredentialType.Key:
                                break;
                            case SecureStoreCredentialType.Password:
                                creds.Add(new SecureStoreCredential(MakeSecureString(userPassword), SecureStoreCredentialType.Password));
                                break;
                            case SecureStoreCredentialType.Pin:
                                break;
                            case SecureStoreCredentialType.UserName:
                                creds.Add(new SecureStoreCredential(MakeSecureString(userName), SecureStoreCredentialType.UserName));
                                break;
                            case SecureStoreCredentialType.WindowsPassword:
                                break;
                            case SecureStoreCredentialType.WindowsUserName:
                                break;
                            default:
                                break;
                        }
                    }

                    iss.SetCredentials(targetApplicationID, credentials);
                    iss.SetUserCredentials(targetApplicationID, ssClaim, credentials);
                }
            }
        }


        public void CreateTargetApplication(string targetApplicationID, string targetApplicationContactEmail)
        {
            var iss = GetISecureStore();
            var apps = iss.GetApplications();
            var result = apps.Where(a => a.ApplicationId == targetApplicationID);

            if (result.Count() == 0)
            {
                var ta = new TargetApplication(
                    targetApplicationID,
                    targetApplicationID,
                    targetApplicationContactEmail,
                    20,
                    TargetApplicationType.Individual,
                    null
                );
                var taf1 = new TargetApplicationField("UserName", false, SecureStoreCredentialType.UserName);
                var taf2 = new TargetApplicationField("Password", true, SecureStoreCredentialType.Password);
                var taf3 = new TargetApplicationField("DBInstance", false, SecureStoreCredentialType.Generic);
                var oSecureStoreServiceClaimList = new List<SecureStoreServiceClaim>();
                var claim = SPClaimProviderManager.CreateUserClaim(SYSTEMACCOUNT, SPOriginalIssuerType.Windows);
                var adminClaim = new SecureStoreServiceClaim(claim);
                oSecureStoreServiceClaimList.Add(adminClaim);
                var claimcurrent = SPClaimProviderManager.CreateUserClaim(WindowsIdentity.GetCurrent().Name, SPOriginalIssuerType.Windows);
                var ssClaimCurrent = new SecureStoreServiceClaim(claimcurrent);
                oSecureStoreServiceClaimList.Add(ssClaimCurrent);
                var targetClaims = new TargetApplicationClaims(oSecureStoreServiceClaimList, null, null);
                iss.CreateApplication(ta, new List<TargetApplicationField>() { taf1, taf2, taf3 }, targetClaims);
            }
        }

        public void DeleteTargetApplication(string targetApplicationID)
        {
            var iss = GetISecureStore();
            var apps = iss.GetApplications();
            var result = apps.Where(a => a.ApplicationId == targetApplicationID);

            if (result.Count() > 0)
            {
                iss.DeleteApplication(targetApplicationID);
            }
        }

        #region private method
        private static string ToClrString(System.Security.SecureString secureString)
        {
            var ptr = Marshal.SecureStringToBSTR(secureString);
            try
            {
                return Marshal.PtrToStringBSTR(ptr);
            }
            finally
            {
                Marshal.FreeBSTR(ptr);
            }
        }

        private static SecureString MakeSecureString(string value)
        {
            if (value == null)
            {
                return null;
            }

            var secureContent = new SecureString();
            var chArray = value.ToCharArray();

            for (int i = 0; i < chArray.Length; i++)
            {
                secureContent.AppendChar(chArray[i]);
                chArray[i] = '0';
            }
            return secureContent;
        }

        private ISecureStore GetISecureStore()
        {
            var context = SPServiceContext.GetContext(SPServiceApplicationProxyGroup.Default, SPSiteSubscriptionIdentifier.Default);
            var ssp = new SecureStoreServiceProxy();
            var iss = ssp.GetSecureStore(context);
            return iss;
        }

        private static SPSite GetCentralAdminSite()
        {
            var adminWebApp = SPAdministrationWebApplication.Local;
            SPSite adminSite = null;
            if (adminWebApp != null)
            {
                var adminSiteUri = adminWebApp.GetResponseUri(SPUrlZone.Default);
                if (adminSiteUri != null)
                {
                    adminSite = adminWebApp.Sites[adminSiteUri.AbsoluteUri];
                }
            }
            return adminSite;
        }
        #endregion
    }
}

Reference : Code Snippet: Get User Credentials Using the Default Secure Store Provider

Temporary Tables

Temporary Table

create table #temptable {
 id int,
 name nvarchar(50)
};

select * into #temptable from UserTable;

Features

• Table name begin with '#'
• Table will be automatically dropped when session closed
• Manually drop table will be suggested
• Temporary table will be stored at database 'tempdb'
• Different session/user can create temporary table with same table name
• Index supported

Table Variables

declare @temptable table{
 id int,
 name nvarchar(50)
};

Features

• Table name begin with '@'
• Table do not need drop
• Table data only exist in memory
• Can not create from select statement

jQuery.validationEngine

The following attribute's value will be loaded for the relative validation rule:

data-errormessage-value-missing

• required
• groupRequired
• condRequired

data-errormessage-type-mismatch

• past
• future
• dateRange
• dateTimeRange

data-errormessage-pattern-mismatch

• creditCard
• equals

data-errormessage-range-underflow

• minSize
• min
• minCheckbox

data-errormessage-range-overflow

• maxSize
• max
• maxCheckbox

data-errormessage-custom-error

• custom
• ajax
• funcCall

data-errormessage

• a generic fall-back error message

---

Validators

• required : Speaks for itself, fails if the element has no value. This validator can apply to pretty much any kind of input field.
• groupRequired : At least one of the field of the group must be filled. It needs to be given a group name that is unique across the form.
• condRequired : This makes the field required, but only if any of the referred fields has a value.
• custom[regex_name] : Validates the element's value to a predefined list of regular expressions.
• custom[function_name] : Validates the element's value to a predefined function included in the language file (compared to funcCall that can be anywhere in your application),
• funcCall[methodName] : Validates a field using a third party function call. If a validation error occurs, the function must return an error message that will automatically show in the error prompt.
• ajax[selector] : Delegates the validation to a server URL using an asynchronous Ajax request. The selector is used to identify a block of properties in the translation file, take the following for example.
• equals[field.id] : Checks if the current field's value equals the value of the specified field.
• min[float] : Validates when the field's value is less than, or equal to, the given parameter.
• max[float] : Validates when the field's value is more than, or equal to, the given parameter.
• minSize[integer] : Validates if the element content size (in characters) is more than, or equal to, the given integer. integer <= input.value.length
• maxSize[integer] : Validates if the element content size (in characters) is less than, or equal to, the given integer. input.value.length <= integer
• past[NOW, a date or another element's name] : Checks if the element's value (which is implicitly a date) is earlier than the given date. When "NOW" is used as a parameter, the date will be calculate in the browser. When a "#field name" is used ( The '#' is optional ), it will compare the element's value with another element's value within the same form. Note that this may be different from the server date. Dates use the ISO format YYYY-MM-DD
• future[NOW, a date or another element's name] : Checks if the element's value (which is implicitly a date) is greater than the given date. When "NOW" is used as a parameter, the date will be calculate in the browser. When a "#field name" is used ( The '#' is optional ), it will compare the element's value with another element's value within the same form. Note that this may be different from the server date. Dates use the ISO format YYYY-MM-DD
• minCheckbox[integer] : Validates when a minimum of integer checkboxes are selected. The validator uses a special naming convention to identify the checkboxes as part of a group.
• maxCheckbox[integer] : Same as above but limits the maximum number of selected check boxes.
• creditCard : Validates that a credit card number is at least theoretically valid, according the to the Luhn checksum algorithm, but not whether the specific card number is active with a bank, etc.

---

Custom Regex

• phone
• url
• email
• date
• number
• integer
• ipv4
• onlyNumberSp
• onlyLetterSp
• onlyLetterNumber

Custom prompt position

• data-prompt-position : topLeft, topRight, centerRight, bottomLeft, bottomRight, inline

Ignore validate

• data-validation-engine-skip: true
reference : jQuery.validationEngine v2.6.1

Catpture webpage snapshot by form control

[STAThread]
static Image Capture(string url, int width, int height)
{
    WebBrowser wb = new WebBrowser();

    wb.Navigate(url);
    wb.ScrollBarsEnabled = false;
    wb.ScriptErrorsSuppressed = false;

    while (wb.ReadyState != WebBrowserReadyState.Complete)
    {
        Application.DoEvents();
    }

    // Set the size of the WebBrowser control
    wb.Width = width;
    wb.Height = height;

    if (width == -1)
    {
        // Take Screenshot of the web pages full width
        wb.Width = wb.Document.Body.ScrollRectangle.Width;
    }

    if (height == -1)
    {
        // Take Screenshot of the web pages full height
        wb.Height = wb.Document.Body.ScrollRectangle.Height;
    }

    // Get a Bitmap representation of the webpage as it's rendered in the WebBrowser control
    Bitmap bitmap = new Bitmap(wb.Width, wb.Height);
    wb.DrawToBitmap(bitmap, new Rectangle(0, 0, wb.Width, wb.Height));
    wb.Dispose();

    return bitmap;
}