sqlstateclientmanager.cs source code in C# .NET

Source code for the .NET framework in C#

                        

Code:

/ 4.0 / 4.0 / DEVDIV_TFS / Dev10 / Releases / RTMRel / ndp / fx / src / xsp / System / Web / State / sqlstateclientmanager.cs / 1305376 / sqlstateclientmanager.cs

                            //------------------------------------------------------------------------------ 
// 
//     Copyright (c) Microsoft Corporation.  All rights reserved.
// 
//----------------------------------------------------------------------------- 

/* 
 * SqlSessionStateStore.cs 
 *
 * Copyright (c) 1998-2000, Microsoft Corporation 
 *
 */

namespace System.Web.SessionState { 

    using System; 
    using System.Configuration; 
    using System.Collections;
    using System.Threading; 
    using System.IO;
    using System.Web;
    using System.Web.Caching;
    using System.Web.Util; 
    using System.Data;
    using System.Data.SqlClient; 
    using System.Data.Common; 
    using System.Text;
    using System.Security.Principal; 
    using System.Xml;
    using System.Collections.Specialized;
    using System.Configuration.Provider;
    using System.Globalization; 
    using System.Web.Management;
    using System.Web.Hosting; 
    using System.Web.Configuration; 

    /* 
     * Provides session state via SQL Server
     */
    internal class SqlSessionStateStore : SessionStateStoreProviderBase {
 
        internal enum SupportFlags : uint {
            None =              0x00000000, 
            GetLockAge =        0x00000001, 
            Uninitialized =     0xFFFFFFFF
        } 

        #pragma warning disable 0649
        static ReadWriteSpinLock    s_lock;
        #pragma warning restore 0649 
        static int          s_isClearPoolInProgress;
        static int          s_commandTimeout; 
        static TimeSpan     s_retryInterval; 
        static SqlPartitionInfo s_singlePartitionInfo;
        static PartitionManager s_partitionManager; 
        static bool         s_oneTimeInited;
        static bool         s_usePartition;
        static EventHandler s_onAppDomainUnload;
 

        // We keep these info because we don't want to hold on to the config object. 
        static string       s_configPartitionResolverType; 
        static string       s_configSqlConnectionFileName;
        static int          s_configSqlConnectionLineNumber; 
        static bool         s_configAllowCustomSqlDatabase;
        static bool         s_configCompressionEnabled;

        // Per request info 
        HttpContext         _rqContext;
        int                 _rqOrigStreamLen; 
        IPartitionResolver  _partitionResolver; 
        SqlPartitionInfo    _partitionInfo;
 
        const int ITEM_SHORT_LENGTH =   7000;
        const int SQL_ERROR_PRIMARY_KEY_VIOLATION = 2627;
        const int SQL_LOGIN_FAILED = 18456;
        const int SQL_LOGIN_FAILED_2 = 18452; 
        const int SQL_LOGIN_FAILED_3 = 18450;
        const int SQL_CANNOT_OPEN_DATABASE_FOR_LOGIN = 4060; 
        const int SQL_TIMEOUT_EXPIRED = -2; 
        const int APP_SUFFIX_LENGTH = 8;
        const int FIRST_RETRY_SLEEP_TIME = 5000; 
        const int RETRY_SLEEP_TIME = 1000;

        static int ID_LENGTH = SessionIDManager.SessionIDMaxLength + APP_SUFFIX_LENGTH;
        internal const int SQL_COMMAND_TIMEOUT_DEFAULT = 30;        // in sec 

        internal SqlSessionStateStore() { 
        } 

        internal override void Initialize(string name, NameValueCollection config, IPartitionResolver partitionResolver) { 
            _partitionResolver = partitionResolver;
            Initialize(name, config);
        }
 
#if DBG
        SessionStateModule  _module; 
 
        internal void SetModule(SessionStateModule module) {
            _module = module; 
        }
#endif

        public override void Initialize(string name, NameValueCollection config) 
        {
            if (String.IsNullOrEmpty(name)) 
                name = "SQL Server Session State Provider"; 

            base.Initialize(name, config); 

            if (!s_oneTimeInited) {
                s_lock.AcquireWriterLock();
                try { 
                    if (!s_oneTimeInited) {
                        OneTimeInit(); 
                    } 
                }
                finally { 
                    s_lock.ReleaseWriterLock();
                }
            }
 
            if (!s_usePartition) {
                // For single partition, the connection info won't change from request to request 
                Debug.Assert(_partitionResolver == null); 
                _partitionInfo = s_singlePartitionInfo;
            } 
        }

        void OneTimeInit() {
            SessionStateSection config = RuntimeConfig.GetAppConfig().SessionState; 

            s_configPartitionResolverType = config.PartitionResolverType; 
            s_configSqlConnectionFileName = config.ElementInformation.Properties["sqlConnectionString"].Source; 
            s_configSqlConnectionLineNumber = config.ElementInformation.Properties["sqlConnectionString"].LineNumber;
            s_configAllowCustomSqlDatabase = config.AllowCustomSqlDatabase; 
            s_configCompressionEnabled = config.CompressionEnabled;

            if (_partitionResolver == null) {
                String sqlConnectionString = config.SqlConnectionString; 

                SessionStateModule.ReadConnectionString(config, ref sqlConnectionString, "sqlConnectionString"); 
                s_singlePartitionInfo = (SqlPartitionInfo)CreatePartitionInfo(sqlConnectionString); 
            }
            else { 
                s_usePartition = true;
                s_partitionManager = new PartitionManager(new CreatePartitionInfo(CreatePartitionInfo));
            }
 
            s_commandTimeout = (int)config.SqlCommandTimeout.TotalSeconds;
            s_retryInterval = config.SqlConnectionRetryInterval; 
 
            s_isClearPoolInProgress = 0;
 
            // We only need to do this in one instance
            s_onAppDomainUnload = new EventHandler(OnAppDomainUnload);
            Thread.GetDomain().DomainUnload += s_onAppDomainUnload;
 
            // Last thing to set.
            s_oneTimeInited = true; 
        } 

        void OnAppDomainUnload(Object unusedObject, EventArgs unusedEventArgs) { 
            Debug.Trace("SqlSessionStateStore", "OnAppDomainUnload called");

            Thread.GetDomain().DomainUnload -= s_onAppDomainUnload;
 
            if (_partitionResolver == null) {
                if (s_singlePartitionInfo != null) { 
                    s_singlePartitionInfo.Dispose(); 
                }
            } 
            else {
                if (s_partitionManager != null) {
                    s_partitionManager.Dispose();
                } 
            }
        } 
 
        internal IPartitionInfo CreatePartitionInfo(string sqlConnectionString) {
            /* 
             * Parse the connection string for errors. We want to ensure
             * that the user's connection string doesn't contain an
             * Initial Catalog entry, so we must first create a dummy connection.
             */ 
            SqlConnection   dummyConnection;
            string          attachDBFilename = null; 
 
            try {
                dummyConnection = new SqlConnection(sqlConnectionString); 
            }
            catch (Exception e) {
                if (s_usePartition) {
                    HttpException outerException = new HttpException( 
                           SR.GetString(SR.Error_parsing_sql_partition_resolver_string, s_configPartitionResolverType, e.Message), e);
 
                    outerException.SetFormatter(new UseLastUnhandledErrorFormatter(outerException)); 

                    throw outerException; 
                }
                else {
                    throw new ConfigurationErrorsException(
                        SR.GetString(SR.Error_parsing_session_sqlConnectionString, e.Message), e, 
                        s_configSqlConnectionFileName, s_configSqlConnectionLineNumber);
                } 
            } 

            // Search for both Database and AttachDbFileName.  Don't append our 
            // database name if either of them exists.
            string database = dummyConnection.Database;
            SqlConnectionStringBuilder  scsb = new SqlConnectionStringBuilder(sqlConnectionString);
 
            if (String.IsNullOrEmpty(database)) {
                database = scsb.AttachDBFilename; 
                attachDBFilename = database; 
            }
 
            if (!String.IsNullOrEmpty(database)) {
                if (!s_configAllowCustomSqlDatabase) {
                    if (s_usePartition) {
                        throw new HttpException( 
                                SR.GetString(SR.No_database_allowed_in_sql_partition_resolver_string,
                                            s_configPartitionResolverType, dummyConnection.DataSource, database)); 
                    } 
                    else {
                        throw new ConfigurationErrorsException( 
                                SR.GetString(SR.No_database_allowed_in_sqlConnectionString),
                                s_configSqlConnectionFileName, s_configSqlConnectionLineNumber);
                    }
                } 

                if (attachDBFilename != null) { 
                    HttpRuntime.CheckFilePermission(attachDBFilename, true); 
                }
            } 
            else {
                sqlConnectionString += ";Initial Catalog=ASPState";
            }
 
            return new SqlPartitionInfo(new ResourcePool(new TimeSpan(0, 0, 5), int.MaxValue),
                                            scsb.IntegratedSecurity, 
                                            sqlConnectionString); 

        } 

        public override bool SetItemExpireCallback(SessionStateItemExpireCallback expireCallback) {
            return false;
        } 

        public override void Dispose() { 
        } 

        public override void InitializeRequest(HttpContext context) { 
            Debug.Assert(context != null, "context != null");

            _rqContext = context;
            _rqOrigStreamLen = 0; 

            if (s_usePartition) { 
                // For multiple partition case, the connection info can change from request to request 
                _partitionInfo = null;
            } 
        }

        public override void EndRequest(HttpContext context) {
            Debug.Assert(context != null, "context != null"); 
            _rqContext = null;
        } 
 
        public bool KnowForSureNotUsingIntegratedSecurity {
            get { 
                if (_partitionInfo == null) {
                    Debug.Assert(s_usePartition, "_partitionInfo can be null only if we're using paritioning and we haven't called GetConnection yet.");
                    // If we're using partitioning, we need the session id to figure out the connection
                    // string.  Without it, we can't know for sure. 
                    return false;
                } 
                else { 
                    Debug.Assert(_partitionInfo != null);
                    return !_partitionInfo.UseIntegratedSecurity; 
                }
            }
        }
 
        //
        // Regarding resource pool, we will turn it on if in : 
        //  - User is not using integrated security 
        //  - impersonation = "false"
        //  - impersonation = "true" and userName/password is NON-null 
        //  - impersonation = "true" and IIS is using Anonymous
        //
        // Otherwise, the impersonated account will be dynamic and we have to turn
        // resource pooling off. 
        //
        // Note: 
        // In case 2. above, the user can specify different usernames in different 
        // web.config in different subdirs in the app.  In this case, we will just
        // cache the connections in the resource pool based on the identity of the 
        // connection.  So in this specific scenario it is possible to have the
        // resource pool filled with mixed identities.
        //
        bool CanUsePooling() { 
            bool    ret;
 
            if (KnowForSureNotUsingIntegratedSecurity) { 
                Debug.Trace("SessionStatePooling", "CanUsePooling: not using integrated security");
                ret = true; 
            }
            else if (_rqContext == null) {
                // One way this can happen is we hit an error on page compilation,
                // and SessionStateModule.OnEndRequest is called 
                Debug.Trace("SessionStatePooling", "CanUsePooling: no context");
                ret = false; 
            } 
            else if (!_rqContext.IsClientImpersonationConfigured) {
                Debug.Trace("SessionStatePooling", "CanUsePooling: mode is None or Application"); 
                ret = true;
            }
            else if (HttpRuntime.IsOnUNCShareInternal) {
                Debug.Trace("SessionStatePooling", "CanUsePooling: mode is UNC"); 
                ret = false;
            } 
            else { 
                string logon = _rqContext.WorkerRequest.GetServerVariable("LOGON_USER");
 
                Debug.Trace("SessionStatePooling", "LOGON_USER = '" + logon + "'; identity = '" + _rqContext.User.Identity.Name + "'; IsUNC = " + HttpRuntime.IsOnUNCShareInternal);

                if (String.IsNullOrEmpty(logon)) {
                    ret = true; 
                }
                else { 
                    ret = false; 
                }
            } 

            Debug.Trace("SessionStatePooling", "CanUsePooling returns " + ret);
            return ret;
        } 

        SqlStateConnection GetConnection(string id, ref bool usePooling) { 
            SqlStateConnection conn = null; 

            if (_partitionInfo == null) { 
                Debug.Assert(s_partitionManager != null);
                Debug.Assert(_partitionResolver != null);

                _partitionInfo = (SqlPartitionInfo)s_partitionManager.GetPartition(_partitionResolver, id); 
            }
 
            Debug.Trace("SessionStatePooling", "Calling GetConnection under " + WindowsIdentity.GetCurrent().Name); 
#if DBG
            Debug.Assert(_module._rqChangeImpersonationRefCount != 0, 
                "SessionStateModule.ChangeImpersonation should have been called before making any call to SQL");
#endif

            usePooling = CanUsePooling(); 
            if (usePooling) {
                conn = (SqlStateConnection) _partitionInfo.RetrieveResource(); 
                if (conn != null && (conn.Connection.State & ConnectionState.Open) == 0) { 
                    conn.Dispose();
                    conn = null; 
                }
            }

            if (conn == null) { 
                conn = new SqlStateConnection(_partitionInfo, s_retryInterval);
            } 
 
            return conn;
        } 

        void DisposeOrReuseConnection(ref SqlStateConnection conn, bool usePooling) {
            try {
                if (conn == null) { 
                    return;
                } 
 
                if (usePooling) {
                    conn.ClearAllParameters(); 
                    _partitionInfo.StoreResource(conn);
                    conn = null;
                }
            } 
            finally {
                if (conn != null) { 
                    conn.Dispose(); 
                }
            } 
        }

        internal static void ThrowSqlConnectionException(SqlConnection conn, Exception e) {
            if (s_usePartition) { 
                throw new HttpException(
                    SR.GetString(SR.Cant_connect_sql_session_database_partition_resolver, 
                                s_configPartitionResolverType, conn.DataSource, conn.Database)); 
            }
            else { 
                throw new HttpException(
                    SR.GetString(SR.Cant_connect_sql_session_database),
                    e);
            } 
        }
 
        SessionStateStoreData DoGet(HttpContext context, String id, bool getExclusive, 
                                        out bool locked,
                                        out TimeSpan lockAge, 
                                        out object lockId,
                                        out SessionStateActions actionFlags) {
            SqlDataReader       reader;
            byte []             buf; 
            MemoryStream        stream = null;
            SessionStateStoreData    item; 
            bool                useGetLockAge = false; 
            SqlStateConnection  conn = null;
            SqlCommand          cmd = null; 
            bool                usePooling = true;

            Debug.Assert(id.Length <= SessionIDManager.SESSION_ID_LENGTH_LIMIT, "id.Length <= SessionIDManager.SESSION_ID_LENGTH_LIMIT");
            Debug.Assert(context != null, "context != null"); 

            // Set default return values 
            locked = false; 
            lockId = null;
            lockAge = TimeSpan.Zero; 
            actionFlags = 0;

            buf = null;
            reader = null; 

            conn = GetConnection(id, ref usePooling); 
 
            Debug.Assert(_partitionInfo != null, "_partitionInfo != null");
            Debug.Assert(_partitionInfo.SupportFlags != SupportFlags.Uninitialized, "_partitionInfo.SupportFlags != SupportFlags.Uninitialized"); 

            //
            // In general, if we're talking to a SQL 2000 or above, we use LockAge; otherwise we use LockDate.
            // Below are the details: 
            //
            // Version 1 
            // --------- 
            // In v1, the lockDate is generated and stored in SQL using local time, and we calculate the "lockage"
            // (i.e. how long the item is locked) by having the web server read lockDate from SQL and substract it 
            // from DateTime.Now.  But this approach introduced two problems:
            //  1. SQL server and web servers need to be in the same time zone.
            //  2. Daylight savings problem.
            // 
            // Version 1.1
            // ----------- 
            // In v1.1, if using SQL 2000 we fixed the problem by calculating the "lockage" directly in SQL 
            // so that the SQL server and the web server don't have to be in the same time zone.  We also
            // use UTC date to store time in SQL so that the Daylight savings problem is solved. 
            //
            // In summary, if using SQL 2000 we made the following changes to the SQL tables:
            //      i. The column Expires is using now UTC time
            //     ii. Add new SP TempGetStateItem2 and TempGetStateItemExclusive2 to return a lockage 
            //         instead of a lockDate.
            //    iii. To support v1 web server, we still need to have TempGetStateItem and 
            //         TempGetStateItemExclusive.  However, we modify it a bit so that they use 
            //         UTC time to update Expires column.
            // 
            // If using SQL 7, we decided not to fix the problem, and the SQL scripts for SQL 7 remain pretty much
            // the same. That means v1.1 web server will continue to call TempGetStateItem and
            // TempGetStateItemExclusive and use v1 way to calculate the "lockage".
            // 
            // Version 2.0
            // ----------- 
            // In v2.0 we added some new SP TempGetStateItem3 and TempGetStateItemExclusive3 
            // because we added a new return value 'actionFlags'.  However, the principle remains the same
            // that we support lockAge only if talking to SQL 2000. 
            //
            // (When one day MS stops supporting SQL 7 we can remove all the SQL7-specific scripts and
            //  stop all these craziness.)
            // 
            if ((_partitionInfo.SupportFlags & SupportFlags.GetLockAge) != 0) {
                useGetLockAge = true; 
            } 

            try { 
                if (getExclusive) {
                    cmd = conn.TempGetExclusive;
                }
                else { 
                    cmd = conn.TempGet;
                } 
 
                cmd.Parameters[0].Value = id + _partitionInfo.AppSuffix; // @id
                cmd.Parameters[1].Value = Convert.DBNull;   // @itemShort 
                cmd.Parameters[2].Value = Convert.DBNull;   // @locked
                cmd.Parameters[3].Value = Convert.DBNull;   // @lockDate or @lockAge
                cmd.Parameters[4].Value = Convert.DBNull;   // @lockCookie
                cmd.Parameters[5].Value = Convert.DBNull;   // @actionFlags 

                using(reader = SqlExecuteReaderWithRetry(cmd, CommandBehavior.Default)) { 
 
                    /* If the cmd returned data, we must read it all before getting out params */
                    if (reader != null) { 
                        try {
                            if (reader.Read()) {
                                Debug.Trace("SqlSessionStateStore", "Sql Get returned long item");
                                buf = (byte[]) reader[0]; 
                            }
                        } catch(Exception e) { 
                            ThrowSqlConnectionException(cmd.Connection, e); 
                        }
                    } 
                }

                /* Check if value was returned */
                if (Convert.IsDBNull(cmd.Parameters[2].Value)) { 
                    Debug.Trace("SqlSessionStateStore", "Sql Get returned null");
                    return null; 
                } 

                /* Check if item is locked */ 
                Debug.Assert(!Convert.IsDBNull(cmd.Parameters[3].Value), "!Convert.IsDBNull(cmd.Parameters[3].Value)");
                Debug.Assert(!Convert.IsDBNull(cmd.Parameters[4].Value), "!Convert.IsDBNull(cmd.Parameters[4].Value)");

                locked = (bool) cmd.Parameters[2].Value; 
                lockId = (int) cmd.Parameters[4].Value;
 
                if (locked) { 
                    Debug.Trace("SqlSessionStateStore", "Sql Get returned item that was locked");
                    Debug.Assert(((int)cmd.Parameters[5].Value & (int)SessionStateActions.InitializeItem) == 0, 
                        "(cmd.Parameters[5].Value & SessionStateActions.InitializeItem) == 0; uninit item shouldn't be locked");

                    if (useGetLockAge) {
                        lockAge = new TimeSpan(0, 0, (int) cmd.Parameters[3].Value); 
                    }
                    else { 
                        DateTime            lockDate; 
                        lockDate = (DateTime) cmd.Parameters[3].Value;
                        lockAge = DateTime.Now - lockDate; 
                    }

                    Debug.Trace("SqlSessionStateStore", "LockAge = " + lockAge);
 
                    if (lockAge > new TimeSpan(0, 0, Sec.ONE_YEAR)) {
                        Debug.Trace("SqlSessionStateStore", "Lock age is more than 1 year!!!"); 
                        lockAge = TimeSpan.Zero; 
                    }
                    return null; 
                }

                actionFlags = (SessionStateActions) cmd.Parameters[5].Value;
 
                if (buf == null) {
                    /* Get short item */ 
                    Debug.Assert(!Convert.IsDBNull(cmd.Parameters[1].Value), "!Convert.IsDBNull(cmd.Parameters[1].Value)"); 
                    Debug.Trace("SqlSessionStateStore", "Sql Get returned short item");
                    buf = (byte[]) cmd.Parameters[1].Value; 
                    Debug.Assert(buf != null, "buf != null");
                }

                // Done with the connection. 
                DisposeOrReuseConnection(ref conn, usePooling);
 
                using(stream = new MemoryStream(buf)) { 
                    item = SessionStateUtility.DeserializeStoreData(context, stream, s_configCompressionEnabled);
                    _rqOrigStreamLen = (int) stream.Position; 
                }
                return item;
            }
            finally { 
                DisposeOrReuseConnection(ref conn, usePooling);
            } 
        } 

        public override SessionStateStoreData  GetItem(HttpContext context, 
                                                        String id,
                                                        out bool locked,
                                                        out TimeSpan lockAge,
                                                        out object lockId, 
                                                        out SessionStateActions actionFlags) {
            Debug.Trace("SqlSessionStateStore", "Calling Sql Get, id=" + id); 
 
            SessionIDManager.CheckIdLength(id, true /* throwOnFail */);
            return DoGet(context, id, false, out locked, out lockAge, out lockId, out actionFlags); 
        }

        public override SessionStateStoreData  GetItemExclusive(HttpContext context,
                                                String id, 
                                                out bool locked,
                                                out TimeSpan lockAge, 
                                                out object lockId, 
                                                out SessionStateActions actionFlags) {
            Debug.Trace("SqlSessionStateStore", "Calling Sql GetExclusive, id=" + id); 

            SessionIDManager.CheckIdLength(id, true /* throwOnFail */);
            return DoGet(context, id, true, out locked, out lockAge, out lockId, out actionFlags);
        } 

 
        public override void ReleaseItemExclusive(HttpContext context, 
                                String id,
                                object lockId) { 
            Debug.Trace("SqlSessionStateStore", "Calling Sql ReleaseExclusive, id=" + id);
            Debug.Assert(lockId != null, "lockId != null");
            Debug.Assert(context != null, "context != null");
 
            bool                usePooling = true;
            SqlStateConnection  conn = null; 
            int                 lockCookie = (int)lockId; 

            try { 
                SessionIDManager.CheckIdLength(id, true /* throwOnFail */);

                conn = GetConnection(id, ref usePooling);
                SqlCommand cmd = conn.TempReleaseExclusive; 
                cmd.Parameters[0].Value = id + _partitionInfo.AppSuffix;
                cmd.Parameters[1].Value = lockCookie; 
                SqlExecuteNonQueryWithRetry(cmd, false, null); 

            } 
            finally {
                DisposeOrReuseConnection(ref conn, usePooling);
            }
        } 

        public override void SetAndReleaseItemExclusive(HttpContext context, 
                                    String id, 
                                    SessionStateStoreData item,
                                    object lockId, 
                                    bool newItem) {
            byte []             buf;
            int                 length;
            SqlCommand          cmd; 
            bool                usePooling = true;
            SqlStateConnection  conn = null; 
            int                 lockCookie; 

            Debug.Assert(context != null, "context != null"); 

            try {
                Debug.Trace("SqlSessionStateStore", "Calling Sql Set, id=" + id);
 
                Debug.Assert(item.Items != null, "item.Items != null");
                Debug.Assert(item.StaticObjects != null, "item.StaticObjects != null"); 
 
                SessionIDManager.CheckIdLength(id, true /* throwOnFail */);
 
                try {
                    SessionStateUtility.SerializeStoreData(item, ITEM_SHORT_LENGTH, out buf, out length, s_configCompressionEnabled);
                }
                catch { 
                    if (!newItem) {
                        ((SessionStateStoreProviderBase)this).ReleaseItemExclusive(context, id, lockId); 
                    } 
                    throw;
                } 

                // Save it to the store

                if (lockId == null) { 
                    lockCookie = 0;
                } 
                else { 
                    lockCookie = (int)lockId;
                } 

                conn = GetConnection(id, ref usePooling);

                if (!newItem) { 
                    Debug.Assert(_rqOrigStreamLen > 0, "_rqOrigStreamLen > 0");
                    if (length <= ITEM_SHORT_LENGTH) { 
                        if (_rqOrigStreamLen <= ITEM_SHORT_LENGTH) { 
                            cmd = conn.TempUpdateShort;
                        } 
                        else {
                            cmd = conn.TempUpdateShortNullLong;
                        }
                    } 
                    else {
                        if (_rqOrigStreamLen <= ITEM_SHORT_LENGTH) { 
                            cmd = conn.TempUpdateLongNullShort; 
                        }
                        else { 
                            cmd = conn.TempUpdateLong;
                        }
                    }
 
                }
                else { 
                    if (length <= ITEM_SHORT_LENGTH) { 
                        cmd = conn.TempInsertShort;
                    } 
                    else {
                        cmd = conn.TempInsertLong;
                    }
                } 

                cmd.Parameters[0].Value = id + _partitionInfo.AppSuffix; 
                cmd.Parameters[1].Size = length; 
                cmd.Parameters[1].Value = buf;
                cmd.Parameters[2].Value = item.Timeout; 
                if (!newItem) {
                    cmd.Parameters[3].Value = lockCookie;
                }
                SqlExecuteNonQueryWithRetry(cmd, newItem, id); 
            }
            finally { 
                DisposeOrReuseConnection(ref conn, usePooling); 
            }
        } 

        public override void RemoveItem(HttpContext context,
                                        String id,
                                        object lockId, 
                                        SessionStateStoreData item) {
            Debug.Trace("SqlSessionStateStore", "Calling Sql Remove, id=" + id); 
            Debug.Assert(lockId != null, "lockId != null"); 
            Debug.Assert(context != null, "context != null");
 
            bool                usePooling = true;
            SqlStateConnection  conn = null;
            int                 lockCookie = (int)lockId;
 
            try {
                SessionIDManager.CheckIdLength(id, true /* throwOnFail */); 
 
                conn = GetConnection(id, ref usePooling);
                SqlCommand cmd = conn.TempRemove; 
                cmd.Parameters[0].Value = id + _partitionInfo.AppSuffix;
                cmd.Parameters[1].Value = lockCookie;
                SqlExecuteNonQueryWithRetry(cmd, false, null);
            } 
            finally {
                DisposeOrReuseConnection(ref conn, usePooling); 
            } 
        }
 
        public override void ResetItemTimeout(HttpContext context, String id) {
            Debug.Trace("SqlSessionStateStore", "Calling Sql ResetTimeout, id=" + id);
            Debug.Assert(context != null, "context != null");
 
            bool                usePooling = true;
            SqlStateConnection  conn = null; 
 
            try {
                SessionIDManager.CheckIdLength(id, true /* throwOnFail */); 

                conn = GetConnection(id, ref usePooling);
                SqlCommand cmd = conn.TempResetTimeout;
                cmd.Parameters[0].Value = id + _partitionInfo.AppSuffix; 
                SqlExecuteNonQueryWithRetry(cmd, false, null);
            } 
            finally { 
                DisposeOrReuseConnection(ref conn, usePooling);
            } 
        }

        public override SessionStateStoreData CreateNewStoreData(HttpContext context, int timeout)
        { 
            Debug.Assert(context != null, "context != null");
            return SessionStateUtility.CreateLegitStoreData(context, null, null, timeout); 
        } 

        public override void CreateUninitializedItem(HttpContext context, String id, int timeout) { 
            Debug.Trace("SqlSessionStateStore", "Calling Sql InsertUninitializedItem, id=" + id);
            Debug.Assert(context != null, "context != null");

            bool                    usePooling = true; 
            SqlStateConnection      conn = null;
            byte []                 buf; 
            int                     length; 

            try { 
                SessionIDManager.CheckIdLength(id, true /* throwOnFail */);

                // Store an empty data
                SessionStateUtility.SerializeStoreData(CreateNewStoreData(context, timeout), 
                                ITEM_SHORT_LENGTH, out buf, out length, s_configCompressionEnabled);
 
                conn = GetConnection(id, ref usePooling); 

                SqlCommand cmd = conn.TempInsertUninitializedItem; 
                cmd.Parameters[0].Value = id + _partitionInfo.AppSuffix;
                cmd.Parameters[1].Size = length;
                cmd.Parameters[1].Value = buf;
                cmd.Parameters[2].Value = timeout; 
                SqlExecuteNonQueryWithRetry(cmd, true, id);
            } 
            finally { 
                DisposeOrReuseConnection(ref conn, usePooling);
            } 
        }

        static bool IsInsertPKException(SqlException ex, bool ignoreInsertPKException, string id) {
            // If the severity is greater than 20, we have a serious error. 
            // The server usually closes the connection in these cases.
           if (ex != null && 
                ex.Number == SQL_ERROR_PRIMARY_KEY_VIOLATION && 
                ignoreInsertPKException) {
 
                Debug.Trace("SessionStateClientSet",
                    "Insert failed because of primary key violation; just leave gracefully; id=" + id);

                // It's possible that two threads (from the same session) are creating the session 
                // state, both failed to get it first, and now both tried to insert it.
                // One thread may lose with a Primary Key Violation error. If so, that thread will 
                // just lose and exit gracefully. 
                return true;
           } 
           return false;
        }

        static bool IsFatalSqlException(SqlException ex) { 
            // We will retry sql operations for serious errors.
            // We consider fatal exceptions any error with severity >= 20. 
            // In this case, the SQL server closes the connection. 
            //
 


            if(ex != null &&
                (ex.Class >= 20 || 
                 ex.Number == SQL_CANNOT_OPEN_DATABASE_FOR_LOGIN ||
                 ex.Number == SQL_TIMEOUT_EXPIRED)) { 
                return true; 
            }
            return false; 
        }

        static void ClearFlagForClearPoolInProgress() {
           // clear s_isClearPoolInProgress if it was set 
           Interlocked.CompareExchange(ref s_isClearPoolInProgress, 0, 1);
        } 
 
        static bool CanRetry(SqlException ex, SqlConnection conn,
                                            ref bool isFirstAttempt, ref DateTime endRetryTime) { 
            if (s_retryInterval.Seconds <= 0) {
                // no retry policy set
                return false;
            } 
            if (!IsFatalSqlException(ex)) {
                if (!isFirstAttempt) { 
                    ClearFlagForClearPoolInProgress(); 
                }
                return false; 
            }
            if (isFirstAttempt) {
                // check if someone has called ClearPool for this connection string
                // s_isClearPoolInProgress can be: 
                // 0 - no one called ClearPool;
                // 1 - ClearPool is in progress or has already been called 
 
                // If no one called ClearPool (s_isClearPoolInProgress = 0), then
                // make s_isClearPoolInProgress 1 and call clear pool 
                if (0 == Interlocked.CompareExchange(ref s_isClearPoolInProgress, 1, 0)) {
                    Debug.Trace("SqlSessionStateStore", "CanRetry: Call ClearPool to destroy the corrupted connections in the pool");
                    SqlConnection.ClearPool(conn);
                } 

                // First time we sleep longer than for subsequent retries. 
                Thread.Sleep(FIRST_RETRY_SLEEP_TIME); 
                endRetryTime = DateTime.UtcNow.Add(s_retryInterval);
 
                isFirstAttempt = false;
                return true;
            }
            if (DateTime.UtcNow > endRetryTime) { 
                // the specified retry interval is up, we can't retry anymore
                if (!isFirstAttempt) { 
                    ClearFlagForClearPoolInProgress(); 
                }
                return false; 
            }
            // sleep the specified time and allow retry
            Thread.Sleep(RETRY_SLEEP_TIME);
            return true; 
        }
 
        static int SqlExecuteNonQueryWithRetry(SqlCommand cmd, bool ignoreInsertPKException, string id) { 
            bool isFirstAttempt = true;
            DateTime endRetryTime = DateTime.UtcNow; 

            while(true) {
                try {
                   if (cmd.Connection.State != ConnectionState.Open) { 
                       // reopen the connection
                       // (gets closed if a previous operation throwed a SQL exception with severity >= 20) 
                       cmd.Connection.Open(); 
                   }
                   int result = cmd.ExecuteNonQuery(); 
                   // the operation succeeded
                   // If we retried, it's possible ClearPool has been called.
                   // In this case, we clear the flag that shows ClearPool is in progress.
                   if (!isFirstAttempt) { 
                       ClearFlagForClearPoolInProgress();
                   } 
                   return result; 
                }
                catch (SqlException e) { 
                   // if specified, ignore primary key violations
                   if (IsInsertPKException(e, ignoreInsertPKException, id)) {
                       // ignoreInsertPKException = insert && newItem
                       return -1; 
                   }
 
                   if (!CanRetry(e, cmd.Connection, ref isFirstAttempt, ref endRetryTime)) { 
                       // just throw, because not all conditions to retry are satisfied
                       ThrowSqlConnectionException(cmd.Connection, e); 
                   }
                }
                catch (Exception e) {
                   // just throw, we have a different Exception 
                   ThrowSqlConnectionException(cmd.Connection, e);
                } 
            } 
        }
 
        static SqlDataReader SqlExecuteReaderWithRetry(SqlCommand cmd, CommandBehavior cmdBehavior) {
            bool isFirstAttempt = true;
            DateTime endRetryTime = DateTime.UtcNow;
 
            while(true) {
                try { 
                    if (cmd.Connection.State != ConnectionState.Open) { 
                       // reopen the connection
                       // (gets closed if a previous operation throwed a SQL exception with severity >= 20) 
                        cmd.Connection.Open();
                    }
                    SqlDataReader reader = cmd.ExecuteReader(cmdBehavior);
                    // the operation succeeded 
                    if (!isFirstAttempt) {
                        ClearFlagForClearPoolInProgress(); 
                    } 
                    return reader;
                } 
                catch (SqlException e) {
                    if (!CanRetry(e, cmd.Connection, ref isFirstAttempt, ref endRetryTime)) {
                        // just throw, default to previous behavior
                        ThrowSqlConnectionException(cmd.Connection, e); 
                    }
                } 
                catch (Exception e) { 
                   // just throw, we have a different Exception
                   ThrowSqlConnectionException(cmd.Connection, e); 
                }
            }
        }
 

        internal class SqlPartitionInfo : PartitionInfo { 
            bool            _useIntegratedSecurity; 
            string          _sqlConnectionString;
            string          _tracingPartitionString; 
            SupportFlags    _support = SupportFlags.Uninitialized;
            string          _appSuffix;
            object          _lock = new object();
            bool            _sqlInfoInited; 

            const string APP_SUFFIX_FORMAT = "x8"; 
            const int   APPID_MAX = 280; 
            const int   SQL_2000_MAJ_VER = 8;
 
            internal SqlPartitionInfo(ResourcePool rpool, bool useIntegratedSecurity, string sqlConnectionString)
                    : base(rpool) {
                _useIntegratedSecurity = useIntegratedSecurity;
                _sqlConnectionString = sqlConnectionString; 
                Debug.Trace("PartitionInfo", "Created a new info, sqlConnectionString=" + sqlConnectionString);
            } 
 
            internal bool UseIntegratedSecurity {
                get { return _useIntegratedSecurity; } 
            }

            internal string SqlConnectionString {
                get { return _sqlConnectionString; } 
            }
 
            internal SupportFlags SupportFlags { 
                get { return _support; }
                set { _support = value; } 
            }

            protected override string TracingPartitionString {
                get { 
                    if (_tracingPartitionString == null) {
                        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(_sqlConnectionString); 
                        builder.Password = String.Empty; 
                        builder.UserID = String.Empty;
                        _tracingPartitionString = builder.ConnectionString; 
                    }
                    return _tracingPartitionString;
                }
            } 

            internal string AppSuffix { 
                get { return _appSuffix; } 
            }
 
            void GetServerSupportOptions(SqlConnection sqlConnection) {
                Debug.Assert(SupportFlags == SupportFlags.Uninitialized);

                SqlCommand      cmd; 
                SqlDataReader   reader = null;
                SupportFlags    flags = SupportFlags.None; 
                bool            v2 = false; 
                SqlParameter    p;
 
                // First, check if the SQL server is running Whidbey scripts
                cmd = new SqlCommand("Select name from sysobjects where type = 'P' and name = 'TempGetVersion'", sqlConnection);
                cmd.CommandType = CommandType.Text;
 
                using(reader = SqlExecuteReaderWithRetry(cmd, CommandBehavior.SingleRow)) {
                    if (reader.Read()) { 
                        // This function first appears in Whidbey (v2).  So we know it's 
                        // at least 2.0 even without reading its content.
                        v2 = true; 
                    }
                }

                if (!v2) { 

                    if (s_usePartition) { 
                        throw new HttpException( 
                                SR.GetString(SR.Need_v2_SQL_Server_partition_resolver,
                                            s_configPartitionResolverType, sqlConnection.DataSource, sqlConnection.Database)); 
                    }
                    else {
                        throw new HttpException(
                            SR.GetString(SR.Need_v2_SQL_Server)); 
                    }
                } 
 
                // Then, see if it's SQL 2000 or above
 
                cmd = new SqlCommand("dbo.GetMajorVersion", sqlConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                p = cmd.Parameters.Add(new SqlParameter("@@ver", SqlDbType.Int));
                p.Direction = ParameterDirection.Output; 

                SqlExecuteNonQueryWithRetry(cmd, false, null); 
                try { 
                    if ((int)p.Value >= SQL_2000_MAJ_VER) {
                        // For details, see the extensive doc in DoGet method. 
                        flags |= SupportFlags.GetLockAge;
                    }

                    Debug.Trace("PartitionInfo", "SupportFlags initialized to " + flags); 

                    SupportFlags = flags; 
                } 
                catch (Exception e) {
                    SqlSessionStateStore.ThrowSqlConnectionException(sqlConnection, e); 
                }

            }
 

            internal void InitSqlInfo(SqlConnection sqlConnection) { 
                if (_sqlInfoInited) { 
                    return;
                } 

                lock (_lock) {
                    if (_sqlInfoInited) {
                        return; 
                    }
 
                    GetServerSupportOptions(sqlConnection); 

                    // Get AppSuffix info 

                    SqlParameter p;

                    SqlCommand  cmdTempGetAppId = new SqlCommand("dbo.TempGetAppID", sqlConnection); 
                    cmdTempGetAppId.CommandType = CommandType.StoredProcedure;
                    cmdTempGetAppId.CommandTimeout = s_commandTimeout; 
 
                    // AppDomainAppIdInternal will contain the whole metabase path of the request's app
                    // e.g. /lm/w3svc/1/root/fxtest 
                    p = cmdTempGetAppId.Parameters.Add(new SqlParameter("@appName", SqlDbType.VarChar, APPID_MAX));
                    p.Value = HttpRuntime.AppDomainAppIdInternal;

                    p = cmdTempGetAppId.Parameters.Add(new SqlParameter("@appId", SqlDbType.Int)); 
                    p.Direction = ParameterDirection.Output;
                    p.Value = Convert.DBNull; 
 
                    cmdTempGetAppId.ExecuteNonQuery();
                    Debug.Assert(!Convert.IsDBNull(p), "!Convert.IsDBNull(p)"); 
                    int appId = (int) p.Value;
                    _appSuffix = (appId).ToString(APP_SUFFIX_FORMAT, CultureInfo.InvariantCulture);

                    _sqlInfoInited = true; 
                }
            } 
        }; 

        /* 
            Here are all the sprocs created for session state and how they're used:

            CreateTempTables
            - Called during setup 

            DeleteExpiredSessions 
            - Called by SQL agent to remove expired sessions 

            GetHashCode 
            - Called by sproc TempGetAppID

            GetMajorVersion
            - Called during setup 

            TempGetAppID 
            - Called when an asp.net application starts up 

            TempGetStateItem 
            - Used for ReadOnly session state
            - Called by v1 asp.net
            - Called by v1.1 asp.net against SQL 7
 
            TempGetStateItem2
            - Used for ReadOnly session state 
            - Called by v1.1 asp.net against SQL 2000 

            TempGetStateItem3 
            - Used for ReadOnly session state
            - Called by v2 asp.net

            TempGetStateItemExclusive 
            - Called by v1 asp.net
            - Called by v1.1 asp.net against SQL 7 
 
            TempGetStateItemExclusive2
            - Called by v1.1 asp.net against SQL 2000 

            TempGetStateItemExclusive3
            - Called by v2 asp.net
 
            TempGetVersion
            - Called by v2 asp.net when an application starts up 
 
            TempInsertStateItemLong
            - Used when creating a new session state with size > 7000 bytes 

            TempInsertStateItemShort
            - Used when creating a new session state with size <= 7000 bytes
 
            TempInsertUninitializedItem
            - Used when creating a new uninitilized session state (cookieless="true" and regenerateExpiredSessionId="true" in config) 
 
            TempReleaseStateItemExclusive
            - Used when a request that has acquired the session state (exclusively) hit an error during the page execution 

            TempRemoveStateItem
            - Used when a session is abandoned
 
            TempResetTimeout
            - Used when a request (with an active session state) is handled by an HttpHandler which doesn't support IRequiresSessionState interface. 
 
            TempUpdateStateItemLong
            - Used when updating a session state with size > 7000 bytes 

            TempUpdateStateItemLongNullShort
            - Used when updating a session state where original size <= 7000 bytes but new size > 7000 bytes
 
            TempUpdateStateItemShort
            - Used when updating a session state with size <= 7000 bytes 
 
            TempUpdateStateItemShortNullLong
            - Used when updating a session state where original size > 7000 bytes but new size <= 7000 bytes 

        */
        class SqlStateConnection : IDisposable {
            SqlConnection   _sqlConnection; 
            SqlCommand      _cmdTempGet;
            SqlCommand      _cmdTempGetExclusive; 
            SqlCommand      _cmdTempReleaseExclusive; 
            SqlCommand      _cmdTempInsertShort;
            SqlCommand      _cmdTempInsertLong; 
            SqlCommand      _cmdTempUpdateShort;
            SqlCommand      _cmdTempUpdateShortNullLong;
            SqlCommand      _cmdTempUpdateLong;
            SqlCommand      _cmdTempUpdateLongNullShort; 
            SqlCommand      _cmdTempRemove;
            SqlCommand      _cmdTempResetTimeout; 
            SqlCommand      _cmdTempInsertUninitializedItem; 

            SqlPartitionInfo    _partitionInfo; 

            internal SqlStateConnection(SqlPartitionInfo sqlPartitionInfo, TimeSpan retryInterval) {
                Debug.Trace("SessionStateConnectionIdentity", "Connecting under " + WindowsIdentity.GetCurrent().Name);
 
                _partitionInfo = sqlPartitionInfo;
                _sqlConnection = new SqlConnection(sqlPartitionInfo.SqlConnectionString); 
 
                bool isFirstAttempt = true;
                DateTime endRetryTime = DateTime.UtcNow; 

                while(true) {
                    try {
                        _sqlConnection.Open(); 
                        // the operation succeeded, exit the loop
                        if(!isFirstAttempt) { 
                            ClearFlagForClearPoolInProgress(); 
                        }
                        break; 
                    }
                    catch (SqlException e) {
                        if (e != null &&
                            (e.Number == SQL_LOGIN_FAILED || 
                             e.Number == SQL_LOGIN_FAILED_2 ||
                             e.Number == SQL_LOGIN_FAILED_3)) 
                        { 
                            string  user;
 
                            SqlConnectionStringBuilder  scsb = new SqlConnectionStringBuilder(sqlPartitionInfo.SqlConnectionString);
                            if (scsb.IntegratedSecurity) {
                                user = WindowsIdentity.GetCurrent().Name;
                            } 
                            else {
                                user = scsb.UserID; 
                            } 

                            HttpException outerException = new HttpException( 
                                    SR.GetString(SR.Login_failed_sql_session_database, user ), e);

                            outerException.SetFormatter(new UseLastUnhandledErrorFormatter(outerException));
                            ClearConnectionAndThrow(outerException); 
                        }
                        if (!CanRetry(e, _sqlConnection, ref isFirstAttempt, ref endRetryTime)) 
                        { 
                            // just throw, the retry conditions are not satisfied
                            ClearConnectionAndThrow(e); 
                        }
                    }
                    catch (Exception e) {
                        // just throw, we have a different Exception 
                        ClearConnectionAndThrow(e);
                    } 
                } 

                try { 
                    _partitionInfo.InitSqlInfo(_sqlConnection);
                    Debug.Assert(sqlPartitionInfo.SupportFlags != SupportFlags.Uninitialized);

                    PerfCounters.IncrementCounter(AppPerfCounter.SESSION_SQL_SERVER_CONNECTIONS); 
                }
                catch { 
                    Dispose(); 
                    throw;
                } 
            }

            void ClearConnectionAndThrow(Exception e) {
                SqlConnection connection = _sqlConnection; 
                _sqlConnection = null;
                ThrowSqlConnectionException(connection, e); 
            } 

             internal void ClearAllParameters() { 
                 ClearAllParameters(_cmdTempGet);
                 ClearAllParameters(_cmdTempGetExclusive);
                 ClearAllParameters(_cmdTempReleaseExclusive);
                 ClearAllParameters(_cmdTempInsertShort); 
                 ClearAllParameters(_cmdTempInsertLong);
                 ClearAllParameters(_cmdTempUpdateShort); 
                 ClearAllParameters(_cmdTempUpdateShortNullLong); 
                 ClearAllParameters(_cmdTempUpdateLong);
                 ClearAllParameters(_cmdTempUpdateLongNullShort); 
                 ClearAllParameters(_cmdTempRemove);
                 ClearAllParameters(_cmdTempResetTimeout);
                 ClearAllParameters(_cmdTempInsertUninitializedItem);
             } 

             internal void ClearAllParameters(SqlCommand cmd) { 
                 if (cmd == null) { 
                     return;
                 } 

                 foreach (SqlParameter param in cmd.Parameters) {
                     param.Value = Convert.DBNull;
                 } 
             }
 
            internal SqlCommand TempGet { 
                get {
                    if (_cmdTempGet == null) { 
                        SqlParameter p;

                        _cmdTempGet = new SqlCommand("dbo.TempGetStateItem3", _sqlConnection);
                        _cmdTempGet.CommandType = CommandType.StoredProcedure; 
                        _cmdTempGet.CommandTimeout = s_commandTimeout;
 
                        // Use a different set of parameters for the sprocs that support GetLockAge 
                        if ((_partitionInfo.SupportFlags &  SupportFlags.GetLockAge) != 0) {
                            _cmdTempGet.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH)); 
                            p = _cmdTempGet.Parameters.Add(new SqlParameter("@itemShort", SqlDbType.VarBinary, ITEM_SHORT_LENGTH));
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGet.Parameters.Add(new SqlParameter("@locked", SqlDbType.Bit));
                            p.Direction = ParameterDirection.Output; 
                            p = _cmdTempGet.Parameters.Add(new SqlParameter("@lockAge", SqlDbType.Int));
                            p.Direction = ParameterDirection.Output; 
                            p = _cmdTempGet.Parameters.Add(new SqlParameter("@lockCookie", SqlDbType.Int)); 
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGet.Parameters.Add(new SqlParameter("@actionFlags", SqlDbType.Int)); 
                            p.Direction = ParameterDirection.Output;
                        }
                        else {
                            _cmdTempGet.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH)); 
                            p = _cmdTempGet.Parameters.Add(new SqlParameter("@itemShort", SqlDbType.VarBinary, ITEM_SHORT_LENGTH));
                            p.Direction = ParameterDirection.Output; 
                            p = _cmdTempGet.Parameters.Add(new SqlParameter("@locked", SqlDbType.Bit)); 
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGet.Parameters.Add(new SqlParameter("@lockDate", SqlDbType.DateTime)); 
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGet.Parameters.Add(new SqlParameter("@lockCookie", SqlDbType.Int));
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGet.Parameters.Add(new SqlParameter("@actionFlags", SqlDbType.Int)); 
                            p.Direction = ParameterDirection.Output;
                        } 
                    } 

                    return _cmdTempGet; 
                }
            }

            internal SqlCommand TempGetExclusive { 
                get {
                    if (_cmdTempGetExclusive == null) { 
                        SqlParameter p; 

                        _cmdTempGetExclusive = new SqlCommand("dbo.TempGetStateItemExclusive3", _sqlConnection); 
                        _cmdTempGetExclusive.CommandType = CommandType.StoredProcedure;
                        _cmdTempGetExclusive.CommandTimeout = s_commandTimeout;

                        // Use a different set of parameters for the sprocs that support GetLockAge 
                        if ((_partitionInfo.SupportFlags &  SupportFlags.GetLockAge) != 0) {
                            _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH)); 
                            p = _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@itemShort", SqlDbType.VarBinary, ITEM_SHORT_LENGTH)); 
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@locked", SqlDbType.Bit)); 
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@lockAge", SqlDbType.Int));
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@lockCookie", SqlDbType.Int)); 
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@actionFlags", SqlDbType.Int)); 
                            p.Direction = ParameterDirection.Output; 
                        }
                        else { 
                            _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH));
                            p = _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@itemShort", SqlDbType.VarBinary, ITEM_SHORT_LENGTH));
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@locked", SqlDbType.Bit)); 
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@lockDate", SqlDbType.DateTime)); 
                            p.Direction = ParameterDirection.Output; 
                            p = _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@lockCookie", SqlDbType.Int));
                            p.Direction = ParameterDirection.Output; 
                            p = _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@actionFlags", SqlDbType.Int));
                            p.Direction = ParameterDirection.Output;
                        }
                    } 

                    return _cmdTempGetExclusive; 
                } 
            }
 
            internal SqlCommand TempReleaseExclusive {
                get {
                    if (_cmdTempReleaseExclusive == null) {
                        /* ReleaseExlusive */ 
                        _cmdTempReleaseExclusive = new SqlCommand("dbo.TempReleaseStateItemExclusive", _sqlConnection);
                        _cmdTempReleaseExclusive.CommandType = CommandType.StoredProcedure; 
                        _cmdTempReleaseExclusive.CommandTimeout = s_commandTimeout; 
                        _cmdTempReleaseExclusive.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH));
                        _cmdTempReleaseExclusive.Parameters.Add(new SqlParameter("@lockCookie", SqlDbType.Int)); 
                    }

                    return _cmdTempReleaseExclusive;
                } 
            }
 
            internal SqlCommand TempInsertLong { 
                get {
                    if (_cmdTempInsertLong == null) { 
                        _cmdTempInsertLong = new SqlCommand("dbo.TempInsertStateItemLong", _sqlConnection);
                        _cmdTempInsertLong.CommandType = CommandType.StoredProcedure;
                        _cmdTempInsertLong.CommandTimeout = s_commandTimeout;
                        _cmdTempInsertLong.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH)); 
                        _cmdTempInsertLong.Parameters.Add(new SqlParameter("@itemLong", SqlDbType.Image, 8000));
                        _cmdTempInsertLong.Parameters.Add(new SqlParameter("@timeout", SqlDbType.Int)); 
                    } 

                    return _cmdTempInsertLong; 
                }
            }

            internal SqlCommand TempInsertShort { 
                get {
                    /* Insert */ 
                    if (_cmdTempInsertShort == null) { 
                        _cmdTempInsertShort = new SqlCommand("dbo.TempInsertStateItemShort", _sqlConnection);
                        _cmdTempInsertShort.CommandType = CommandType.StoredProcedure; 
                        _cmdTempInsertShort.CommandTimeout = s_commandTimeout;
                        _cmdTempInsertShort.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH));
                        _cmdTempInsertShort.Parameters.Add(new SqlParameter("@itemShort", SqlDbType.VarBinary, ITEM_SHORT_LENGTH));
                        _cmdTempInsertShort.Parameters.Add(new SqlParameter("@timeout", SqlDbType.Int)); 
                    }
 
                    return _cmdTempInsertShort; 
                }
            } 

            internal SqlCommand TempUpdateLong {
                get {
                    if (_cmdTempUpdateLong == null) { 
                        _cmdTempUpdateLong = new SqlCommand("dbo.TempUpdateStateItemLong", _sqlConnection);
                        _cmdTempUpdateLong.CommandType = CommandType.StoredProcedure; 
                        _cmdTempUpdateLong.CommandTimeout = s_commandTimeout; 
                        _cmdTempUpdateLong.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH));
                        _cmdTempUpdateLong.Parameters.Add(new SqlParameter("@itemLong", SqlDbType.Image, 8000)); 
                        _cmdTempUpdateLong.Parameters.Add(new SqlParameter("@timeout", SqlDbType.Int));
                        _cmdTempUpdateLong.Parameters.Add(new SqlParameter("@lockCookie", SqlDbType.Int));
                    }
 
                    return _cmdTempUpdateLong;
                } 
            } 

            internal SqlCommand TempUpdateShort { 
                get {
                    /* Update */
                    if (_cmdTempUpdateShort == null) {
                        _cmdTempUpdateShort = new SqlCommand("dbo.TempUpdateStateItemShort", _sqlConnection); 
                        _cmdTempUpdateShort.CommandType = CommandType.StoredProcedure;
                        _cmdTempUpdateShort.CommandTimeout = s_commandTimeout; 
                        _cmdTempUpdateShort.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH)); 
                        _cmdTempUpdateShort.Parameters.Add(new SqlParameter("@itemShort", SqlDbType.VarBinary, ITEM_SHORT_LENGTH));
                        _cmdTempUpdateShort.Parameters.Add(new SqlParameter("@timeout", SqlDbType.Int)); 
                        _cmdTempUpdateShort.Parameters.Add(new SqlParameter("@lockCookie", SqlDbType.Int));
                    }

                    return _cmdTempUpdateShort; 

                } 
            } 

            internal SqlCommand TempUpdateShortNullLong { 
                get {
                    if (_cmdTempUpdateShortNullLong == null) {
                        _cmdTempUpdateShortNullLong = new SqlCommand("dbo.TempUpdateStateItemShortNullLong", _sqlConnection);
                        _cmdTempUpdateShortNullLong.CommandType = CommandType.StoredProcedure; 
                        _cmdTempUpdateShortNullLong.CommandTimeout = s_commandTimeout;
                        _cmdTempUpdateShortNullLong.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH)); 
                        _cmdTempUpdateShortNullLong.Parameters.Add(new SqlParameter("@itemShort", SqlDbType.VarBinary, ITEM_SHORT_LENGTH)); 
                        _cmdTempUpdateShortNullLong.Parameters.Add(new SqlParameter("@timeout", SqlDbType.Int));
                        _cmdTempUpdateShortNullLong.Parameters.Add(new SqlParameter("@lockCookie", SqlDbType.Int)); 
                    }

                    return _cmdTempUpdateShortNullLong;
                } 
            }
 
            internal SqlCommand TempUpdateLongNullShort { 
                get {
                    if (_cmdTempUpdateLongNullShort == null) { 
                        _cmdTempUpdateLongNullShort = new SqlCommand("dbo.TempUpdateStateItemLongNullShort", _sqlConnection);
                        _cmdTempUpdateLongNullShort.CommandType = CommandType.StoredProcedure;
                        _cmdTempUpdateLongNullShort.CommandTimeout = s_commandTimeout;
                        _cmdTempUpdateLongNullShort.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH)); 
                        _cmdTempUpdateLongNullShort.Parameters.Add(new SqlParameter("@itemLong", SqlDbType.Image, 8000));
                        _cmdTempUpdateLongNullShort.Parameters.Add(new SqlParameter("@timeout", SqlDbType.Int)); 
                        _cmdTempUpdateLongNullShort.Parameters.Add(new SqlParameter("@lockCookie", SqlDbType.Int)); 
                    }
 
                    return _cmdTempUpdateLongNullShort;
                }
            }
 
            internal SqlCommand TempRemove {
                get { 
                    if (_cmdTempRemove == null) { 
                        /* Remove */
                        _cmdTempRemove = new SqlCommand("dbo.TempRemoveStateItem", _sqlConnection); 
                        _cmdTempRemove.CommandType = CommandType.StoredProcedure;
                        _cmdTempRemove.CommandTimeout = s_commandTimeout;
                        _cmdTempRemove.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH));
                        _cmdTempRemove.Parameters.Add(new SqlParameter("@lockCookie", SqlDbType.Int)); 

                    } 
 
                    return _cmdTempRemove;
                } 
            }

            internal SqlCommand TempInsertUninitializedItem {
                get { 
                    if (_cmdTempInsertUninitializedItem == null) {
                        _cmdTempInsertUninitializedItem = new SqlCommand("dbo.TempInsertUninitializedItem", _sqlConnection); 
                        _cmdTempInsertUninitializedItem.CommandType = CommandType.StoredProcedure; 
                        _cmdTempInsertUninitializedItem.CommandTimeout = s_commandTimeout;
                        _cmdTempInsertUninitializedItem.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH)); 
                        _cmdTempInsertUninitializedItem.Parameters.Add(new SqlParameter("@itemShort", SqlDbType.VarBinary, ITEM_SHORT_LENGTH));
                        _cmdTempInsertUninitializedItem.Parameters.Add(new SqlParameter("@timeout", SqlDbType.Int));
                    }
 
                    return _cmdTempInsertUninitializedItem;
                } 
            } 

            internal SqlCommand TempResetTimeout { 
                get {
                    if (_cmdTempResetTimeout == null) {
                        /* ResetTimeout */
                        _cmdTempResetTimeout = new SqlCommand("dbo.TempResetTimeout", _sqlConnection); 
                        _cmdTempResetTimeout.CommandType = CommandType.StoredProcedure;
                        _cmdTempResetTimeout.CommandTimeout = s_commandTimeout; 
                        _cmdTempResetTimeout.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH)); 
                    }
 
                    return _cmdTempResetTimeout;
                }
            }
 
            public void Dispose() {
                Debug.Trace("ResourcePool", "Disposing SqlStateConnection"); 
                if (_sqlConnection != null) { 
                    _sqlConnection.Close();
                    _sqlConnection = null; 
                    PerfCounters.DecrementCounter(AppPerfCounter.SESSION_SQL_SERVER_CONNECTIONS);
                }
            }
 
            internal SqlConnection Connection {
                get { return _sqlConnection; } 
            } 
        }
    } 
}

// File provided for Reference Use Only by Microsoft Corporation (c) 2007.
//------------------------------------------------------------------------------ 
// 
//     Copyright (c) Microsoft Corporation.  All rights reserved.
// 
//----------------------------------------------------------------------------- 

/* 
 * SqlSessionStateStore.cs 
 *
 * Copyright (c) 1998-2000, Microsoft Corporation 
 *
 */

namespace System.Web.SessionState { 

    using System; 
    using System.Configuration; 
    using System.Collections;
    using System.Threading; 
    using System.IO;
    using System.Web;
    using System.Web.Caching;
    using System.Web.Util; 
    using System.Data;
    using System.Data.SqlClient; 
    using System.Data.Common; 
    using System.Text;
    using System.Security.Principal; 
    using System.Xml;
    using System.Collections.Specialized;
    using System.Configuration.Provider;
    using System.Globalization; 
    using System.Web.Management;
    using System.Web.Hosting; 
    using System.Web.Configuration; 

    /* 
     * Provides session state via SQL Server
     */
    internal class SqlSessionStateStore : SessionStateStoreProviderBase {
 
        internal enum SupportFlags : uint {
            None =              0x00000000, 
            GetLockAge =        0x00000001, 
            Uninitialized =     0xFFFFFFFF
        } 

        #pragma warning disable 0649
        static ReadWriteSpinLock    s_lock;
        #pragma warning restore 0649 
        static int          s_isClearPoolInProgress;
        static int          s_commandTimeout; 
        static TimeSpan     s_retryInterval; 
        static SqlPartitionInfo s_singlePartitionInfo;
        static PartitionManager s_partitionManager; 
        static bool         s_oneTimeInited;
        static bool         s_usePartition;
        static EventHandler s_onAppDomainUnload;
 

        // We keep these info because we don't want to hold on to the config object. 
        static string       s_configPartitionResolverType; 
        static string       s_configSqlConnectionFileName;
        static int          s_configSqlConnectionLineNumber; 
        static bool         s_configAllowCustomSqlDatabase;
        static bool         s_configCompressionEnabled;

        // Per request info 
        HttpContext         _rqContext;
        int                 _rqOrigStreamLen; 
        IPartitionResolver  _partitionResolver; 
        SqlPartitionInfo    _partitionInfo;
 
        const int ITEM_SHORT_LENGTH =   7000;
        const int SQL_ERROR_PRIMARY_KEY_VIOLATION = 2627;
        const int SQL_LOGIN_FAILED = 18456;
        const int SQL_LOGIN_FAILED_2 = 18452; 
        const int SQL_LOGIN_FAILED_3 = 18450;
        const int SQL_CANNOT_OPEN_DATABASE_FOR_LOGIN = 4060; 
        const int SQL_TIMEOUT_EXPIRED = -2; 
        const int APP_SUFFIX_LENGTH = 8;
        const int FIRST_RETRY_SLEEP_TIME = 5000; 
        const int RETRY_SLEEP_TIME = 1000;

        static int ID_LENGTH = SessionIDManager.SessionIDMaxLength + APP_SUFFIX_LENGTH;
        internal const int SQL_COMMAND_TIMEOUT_DEFAULT = 30;        // in sec 

        internal SqlSessionStateStore() { 
        } 

        internal override void Initialize(string name, NameValueCollection config, IPartitionResolver partitionResolver) { 
            _partitionResolver = partitionResolver;
            Initialize(name, config);
        }
 
#if DBG
        SessionStateModule  _module; 
 
        internal void SetModule(SessionStateModule module) {
            _module = module; 
        }
#endif

        public override void Initialize(string name, NameValueCollection config) 
        {
            if (String.IsNullOrEmpty(name)) 
                name = "SQL Server Session State Provider"; 

            base.Initialize(name, config); 

            if (!s_oneTimeInited) {
                s_lock.AcquireWriterLock();
                try { 
                    if (!s_oneTimeInited) {
                        OneTimeInit(); 
                    } 
                }
                finally { 
                    s_lock.ReleaseWriterLock();
                }
            }
 
            if (!s_usePartition) {
                // For single partition, the connection info won't change from request to request 
                Debug.Assert(_partitionResolver == null); 
                _partitionInfo = s_singlePartitionInfo;
            } 
        }

        void OneTimeInit() {
            SessionStateSection config = RuntimeConfig.GetAppConfig().SessionState; 

            s_configPartitionResolverType = config.PartitionResolverType; 
            s_configSqlConnectionFileName = config.ElementInformation.Properties["sqlConnectionString"].Source; 
            s_configSqlConnectionLineNumber = config.ElementInformation.Properties["sqlConnectionString"].LineNumber;
            s_configAllowCustomSqlDatabase = config.AllowCustomSqlDatabase; 
            s_configCompressionEnabled = config.CompressionEnabled;

            if (_partitionResolver == null) {
                String sqlConnectionString = config.SqlConnectionString; 

                SessionStateModule.ReadConnectionString(config, ref sqlConnectionString, "sqlConnectionString"); 
                s_singlePartitionInfo = (SqlPartitionInfo)CreatePartitionInfo(sqlConnectionString); 
            }
            else { 
                s_usePartition = true;
                s_partitionManager = new PartitionManager(new CreatePartitionInfo(CreatePartitionInfo));
            }
 
            s_commandTimeout = (int)config.SqlCommandTimeout.TotalSeconds;
            s_retryInterval = config.SqlConnectionRetryInterval; 
 
            s_isClearPoolInProgress = 0;
 
            // We only need to do this in one instance
            s_onAppDomainUnload = new EventHandler(OnAppDomainUnload);
            Thread.GetDomain().DomainUnload += s_onAppDomainUnload;
 
            // Last thing to set.
            s_oneTimeInited = true; 
        } 

        void OnAppDomainUnload(Object unusedObject, EventArgs unusedEventArgs) { 
            Debug.Trace("SqlSessionStateStore", "OnAppDomainUnload called");

            Thread.GetDomain().DomainUnload -= s_onAppDomainUnload;
 
            if (_partitionResolver == null) {
                if (s_singlePartitionInfo != null) { 
                    s_singlePartitionInfo.Dispose(); 
                }
            } 
            else {
                if (s_partitionManager != null) {
                    s_partitionManager.Dispose();
                } 
            }
        } 
 
        internal IPartitionInfo CreatePartitionInfo(string sqlConnectionString) {
            /* 
             * Parse the connection string for errors. We want to ensure
             * that the user's connection string doesn't contain an
             * Initial Catalog entry, so we must first create a dummy connection.
             */ 
            SqlConnection   dummyConnection;
            string          attachDBFilename = null; 
 
            try {
                dummyConnection = new SqlConnection(sqlConnectionString); 
            }
            catch (Exception e) {
                if (s_usePartition) {
                    HttpException outerException = new HttpException( 
                           SR.GetString(SR.Error_parsing_sql_partition_resolver_string, s_configPartitionResolverType, e.Message), e);
 
                    outerException.SetFormatter(new UseLastUnhandledErrorFormatter(outerException)); 

                    throw outerException; 
                }
                else {
                    throw new ConfigurationErrorsException(
                        SR.GetString(SR.Error_parsing_session_sqlConnectionString, e.Message), e, 
                        s_configSqlConnectionFileName, s_configSqlConnectionLineNumber);
                } 
            } 

            // Search for both Database and AttachDbFileName.  Don't append our 
            // database name if either of them exists.
            string database = dummyConnection.Database;
            SqlConnectionStringBuilder  scsb = new SqlConnectionStringBuilder(sqlConnectionString);
 
            if (String.IsNullOrEmpty(database)) {
                database = scsb.AttachDBFilename; 
                attachDBFilename = database; 
            }
 
            if (!String.IsNullOrEmpty(database)) {
                if (!s_configAllowCustomSqlDatabase) {
                    if (s_usePartition) {
                        throw new HttpException( 
                                SR.GetString(SR.No_database_allowed_in_sql_partition_resolver_string,
                                            s_configPartitionResolverType, dummyConnection.DataSource, database)); 
                    } 
                    else {
                        throw new ConfigurationErrorsException( 
                                SR.GetString(SR.No_database_allowed_in_sqlConnectionString),
                                s_configSqlConnectionFileName, s_configSqlConnectionLineNumber);
                    }
                } 

                if (attachDBFilename != null) { 
                    HttpRuntime.CheckFilePermission(attachDBFilename, true); 
                }
            } 
            else {
                sqlConnectionString += ";Initial Catalog=ASPState";
            }
 
            return new SqlPartitionInfo(new ResourcePool(new TimeSpan(0, 0, 5), int.MaxValue),
                                            scsb.IntegratedSecurity, 
                                            sqlConnectionString); 

        } 

        public override bool SetItemExpireCallback(SessionStateItemExpireCallback expireCallback) {
            return false;
        } 

        public override void Dispose() { 
        } 

        public override void InitializeRequest(HttpContext context) { 
            Debug.Assert(context != null, "context != null");

            _rqContext = context;
            _rqOrigStreamLen = 0; 

            if (s_usePartition) { 
                // For multiple partition case, the connection info can change from request to request 
                _partitionInfo = null;
            } 
        }

        public override void EndRequest(HttpContext context) {
            Debug.Assert(context != null, "context != null"); 
            _rqContext = null;
        } 
 
        public bool KnowForSureNotUsingIntegratedSecurity {
            get { 
                if (_partitionInfo == null) {
                    Debug.Assert(s_usePartition, "_partitionInfo can be null only if we're using paritioning and we haven't called GetConnection yet.");
                    // If we're using partitioning, we need the session id to figure out the connection
                    // string.  Without it, we can't know for sure. 
                    return false;
                } 
                else { 
                    Debug.Assert(_partitionInfo != null);
                    return !_partitionInfo.UseIntegratedSecurity; 
                }
            }
        }
 
        //
        // Regarding resource pool, we will turn it on if in : 
        //  - User is not using integrated security 
        //  - impersonation = "false"
        //  - impersonation = "true" and userName/password is NON-null 
        //  - impersonation = "true" and IIS is using Anonymous
        //
        // Otherwise, the impersonated account will be dynamic and we have to turn
        // resource pooling off. 
        //
        // Note: 
        // In case 2. above, the user can specify different usernames in different 
        // web.config in different subdirs in the app.  In this case, we will just
        // cache the connections in the resource pool based on the identity of the 
        // connection.  So in this specific scenario it is possible to have the
        // resource pool filled with mixed identities.
        //
        bool CanUsePooling() { 
            bool    ret;
 
            if (KnowForSureNotUsingIntegratedSecurity) { 
                Debug.Trace("SessionStatePooling", "CanUsePooling: not using integrated security");
                ret = true; 
            }
            else if (_rqContext == null) {
                // One way this can happen is we hit an error on page compilation,
                // and SessionStateModule.OnEndRequest is called 
                Debug.Trace("SessionStatePooling", "CanUsePooling: no context");
                ret = false; 
            } 
            else if (!_rqContext.IsClientImpersonationConfigured) {
                Debug.Trace("SessionStatePooling", "CanUsePooling: mode is None or Application"); 
                ret = true;
            }
            else if (HttpRuntime.IsOnUNCShareInternal) {
                Debug.Trace("SessionStatePooling", "CanUsePooling: mode is UNC"); 
                ret = false;
            } 
            else { 
                string logon = _rqContext.WorkerRequest.GetServerVariable("LOGON_USER");
 
                Debug.Trace("SessionStatePooling", "LOGON_USER = '" + logon + "'; identity = '" + _rqContext.User.Identity.Name + "'; IsUNC = " + HttpRuntime.IsOnUNCShareInternal);

                if (String.IsNullOrEmpty(logon)) {
                    ret = true; 
                }
                else { 
                    ret = false; 
                }
            } 

            Debug.Trace("SessionStatePooling", "CanUsePooling returns " + ret);
            return ret;
        } 

        SqlStateConnection GetConnection(string id, ref bool usePooling) { 
            SqlStateConnection conn = null; 

            if (_partitionInfo == null) { 
                Debug.Assert(s_partitionManager != null);
                Debug.Assert(_partitionResolver != null);

                _partitionInfo = (SqlPartitionInfo)s_partitionManager.GetPartition(_partitionResolver, id); 
            }
 
            Debug.Trace("SessionStatePooling", "Calling GetConnection under " + WindowsIdentity.GetCurrent().Name); 
#if DBG
            Debug.Assert(_module._rqChangeImpersonationRefCount != 0, 
                "SessionStateModule.ChangeImpersonation should have been called before making any call to SQL");
#endif

            usePooling = CanUsePooling(); 
            if (usePooling) {
                conn = (SqlStateConnection) _partitionInfo.RetrieveResource(); 
                if (conn != null && (conn.Connection.State & ConnectionState.Open) == 0) { 
                    conn.Dispose();
                    conn = null; 
                }
            }

            if (conn == null) { 
                conn = new SqlStateConnection(_partitionInfo, s_retryInterval);
            } 
 
            return conn;
        } 

        void DisposeOrReuseConnection(ref SqlStateConnection conn, bool usePooling) {
            try {
                if (conn == null) { 
                    return;
                } 
 
                if (usePooling) {
                    conn.ClearAllParameters(); 
                    _partitionInfo.StoreResource(conn);
                    conn = null;
                }
            } 
            finally {
                if (conn != null) { 
                    conn.Dispose(); 
                }
            } 
        }

        internal static void ThrowSqlConnectionException(SqlConnection conn, Exception e) {
            if (s_usePartition) { 
                throw new HttpException(
                    SR.GetString(SR.Cant_connect_sql_session_database_partition_resolver, 
                                s_configPartitionResolverType, conn.DataSource, conn.Database)); 
            }
            else { 
                throw new HttpException(
                    SR.GetString(SR.Cant_connect_sql_session_database),
                    e);
            } 
        }
 
        SessionStateStoreData DoGet(HttpContext context, String id, bool getExclusive, 
                                        out bool locked,
                                        out TimeSpan lockAge, 
                                        out object lockId,
                                        out SessionStateActions actionFlags) {
            SqlDataReader       reader;
            byte []             buf; 
            MemoryStream        stream = null;
            SessionStateStoreData    item; 
            bool                useGetLockAge = false; 
            SqlStateConnection  conn = null;
            SqlCommand          cmd = null; 
            bool                usePooling = true;

            Debug.Assert(id.Length <= SessionIDManager.SESSION_ID_LENGTH_LIMIT, "id.Length <= SessionIDManager.SESSION_ID_LENGTH_LIMIT");
            Debug.Assert(context != null, "context != null"); 

            // Set default return values 
            locked = false; 
            lockId = null;
            lockAge = TimeSpan.Zero; 
            actionFlags = 0;

            buf = null;
            reader = null; 

            conn = GetConnection(id, ref usePooling); 
 
            Debug.Assert(_partitionInfo != null, "_partitionInfo != null");
            Debug.Assert(_partitionInfo.SupportFlags != SupportFlags.Uninitialized, "_partitionInfo.SupportFlags != SupportFlags.Uninitialized"); 

            //
            // In general, if we're talking to a SQL 2000 or above, we use LockAge; otherwise we use LockDate.
            // Below are the details: 
            //
            // Version 1 
            // --------- 
            // In v1, the lockDate is generated and stored in SQL using local time, and we calculate the "lockage"
            // (i.e. how long the item is locked) by having the web server read lockDate from SQL and substract it 
            // from DateTime.Now.  But this approach introduced two problems:
            //  1. SQL server and web servers need to be in the same time zone.
            //  2. Daylight savings problem.
            // 
            // Version 1.1
            // ----------- 
            // In v1.1, if using SQL 2000 we fixed the problem by calculating the "lockage" directly in SQL 
            // so that the SQL server and the web server don't have to be in the same time zone.  We also
            // use UTC date to store time in SQL so that the Daylight savings problem is solved. 
            //
            // In summary, if using SQL 2000 we made the following changes to the SQL tables:
            //      i. The column Expires is using now UTC time
            //     ii. Add new SP TempGetStateItem2 and TempGetStateItemExclusive2 to return a lockage 
            //         instead of a lockDate.
            //    iii. To support v1 web server, we still need to have TempGetStateItem and 
            //         TempGetStateItemExclusive.  However, we modify it a bit so that they use 
            //         UTC time to update Expires column.
            // 
            // If using SQL 7, we decided not to fix the problem, and the SQL scripts for SQL 7 remain pretty much
            // the same. That means v1.1 web server will continue to call TempGetStateItem and
            // TempGetStateItemExclusive and use v1 way to calculate the "lockage".
            // 
            // Version 2.0
            // ----------- 
            // In v2.0 we added some new SP TempGetStateItem3 and TempGetStateItemExclusive3 
            // because we added a new return value 'actionFlags'.  However, the principle remains the same
            // that we support lockAge only if talking to SQL 2000. 
            //
            // (When one day MS stops supporting SQL 7 we can remove all the SQL7-specific scripts and
            //  stop all these craziness.)
            // 
            if ((_partitionInfo.SupportFlags & SupportFlags.GetLockAge) != 0) {
                useGetLockAge = true; 
            } 

            try { 
                if (getExclusive) {
                    cmd = conn.TempGetExclusive;
                }
                else { 
                    cmd = conn.TempGet;
                } 
 
                cmd.Parameters[0].Value = id + _partitionInfo.AppSuffix; // @id
                cmd.Parameters[1].Value = Convert.DBNull;   // @itemShort 
                cmd.Parameters[2].Value = Convert.DBNull;   // @locked
                cmd.Parameters[3].Value = Convert.DBNull;   // @lockDate or @lockAge
                cmd.Parameters[4].Value = Convert.DBNull;   // @lockCookie
                cmd.Parameters[5].Value = Convert.DBNull;   // @actionFlags 

                using(reader = SqlExecuteReaderWithRetry(cmd, CommandBehavior.Default)) { 
 
                    /* If the cmd returned data, we must read it all before getting out params */
                    if (reader != null) { 
                        try {
                            if (reader.Read()) {
                                Debug.Trace("SqlSessionStateStore", "Sql Get returned long item");
                                buf = (byte[]) reader[0]; 
                            }
                        } catch(Exception e) { 
                            ThrowSqlConnectionException(cmd.Connection, e); 
                        }
                    } 
                }

                /* Check if value was returned */
                if (Convert.IsDBNull(cmd.Parameters[2].Value)) { 
                    Debug.Trace("SqlSessionStateStore", "Sql Get returned null");
                    return null; 
                } 

                /* Check if item is locked */ 
                Debug.Assert(!Convert.IsDBNull(cmd.Parameters[3].Value), "!Convert.IsDBNull(cmd.Parameters[3].Value)");
                Debug.Assert(!Convert.IsDBNull(cmd.Parameters[4].Value), "!Convert.IsDBNull(cmd.Parameters[4].Value)");

                locked = (bool) cmd.Parameters[2].Value; 
                lockId = (int) cmd.Parameters[4].Value;
 
                if (locked) { 
                    Debug.Trace("SqlSessionStateStore", "Sql Get returned item that was locked");
                    Debug.Assert(((int)cmd.Parameters[5].Value & (int)SessionStateActions.InitializeItem) == 0, 
                        "(cmd.Parameters[5].Value & SessionStateActions.InitializeItem) == 0; uninit item shouldn't be locked");

                    if (useGetLockAge) {
                        lockAge = new TimeSpan(0, 0, (int) cmd.Parameters[3].Value); 
                    }
                    else { 
                        DateTime            lockDate; 
                        lockDate = (DateTime) cmd.Parameters[3].Value;
                        lockAge = DateTime.Now - lockDate; 
                    }

                    Debug.Trace("SqlSessionStateStore", "LockAge = " + lockAge);
 
                    if (lockAge > new TimeSpan(0, 0, Sec.ONE_YEAR)) {
                        Debug.Trace("SqlSessionStateStore", "Lock age is more than 1 year!!!"); 
                        lockAge = TimeSpan.Zero; 
                    }
                    return null; 
                }

                actionFlags = (SessionStateActions) cmd.Parameters[5].Value;
 
                if (buf == null) {
                    /* Get short item */ 
                    Debug.Assert(!Convert.IsDBNull(cmd.Parameters[1].Value), "!Convert.IsDBNull(cmd.Parameters[1].Value)"); 
                    Debug.Trace("SqlSessionStateStore", "Sql Get returned short item");
                    buf = (byte[]) cmd.Parameters[1].Value; 
                    Debug.Assert(buf != null, "buf != null");
                }

                // Done with the connection. 
                DisposeOrReuseConnection(ref conn, usePooling);
 
                using(stream = new MemoryStream(buf)) { 
                    item = SessionStateUtility.DeserializeStoreData(context, stream, s_configCompressionEnabled);
                    _rqOrigStreamLen = (int) stream.Position; 
                }
                return item;
            }
            finally { 
                DisposeOrReuseConnection(ref conn, usePooling);
            } 
        } 

        public override SessionStateStoreData  GetItem(HttpContext context, 
                                                        String id,
                                                        out bool locked,
                                                        out TimeSpan lockAge,
                                                        out object lockId, 
                                                        out SessionStateActions actionFlags) {
            Debug.Trace("SqlSessionStateStore", "Calling Sql Get, id=" + id); 
 
            SessionIDManager.CheckIdLength(id, true /* throwOnFail */);
            return DoGet(context, id, false, out locked, out lockAge, out lockId, out actionFlags); 
        }

        public override SessionStateStoreData  GetItemExclusive(HttpContext context,
                                                String id, 
                                                out bool locked,
                                                out TimeSpan lockAge, 
                                                out object lockId, 
                                                out SessionStateActions actionFlags) {
            Debug.Trace("SqlSessionStateStore", "Calling Sql GetExclusive, id=" + id); 

            SessionIDManager.CheckIdLength(id, true /* throwOnFail */);
            return DoGet(context, id, true, out locked, out lockAge, out lockId, out actionFlags);
        } 

 
        public override void ReleaseItemExclusive(HttpContext context, 
                                String id,
                                object lockId) { 
            Debug.Trace("SqlSessionStateStore", "Calling Sql ReleaseExclusive, id=" + id);
            Debug.Assert(lockId != null, "lockId != null");
            Debug.Assert(context != null, "context != null");
 
            bool                usePooling = true;
            SqlStateConnection  conn = null; 
            int                 lockCookie = (int)lockId; 

            try { 
                SessionIDManager.CheckIdLength(id, true /* throwOnFail */);

                conn = GetConnection(id, ref usePooling);
                SqlCommand cmd = conn.TempReleaseExclusive; 
                cmd.Parameters[0].Value = id + _partitionInfo.AppSuffix;
                cmd.Parameters[1].Value = lockCookie; 
                SqlExecuteNonQueryWithRetry(cmd, false, null); 

            } 
            finally {
                DisposeOrReuseConnection(ref conn, usePooling);
            }
        } 

        public override void SetAndReleaseItemExclusive(HttpContext context, 
                                    String id, 
                                    SessionStateStoreData item,
                                    object lockId, 
                                    bool newItem) {
            byte []             buf;
            int                 length;
            SqlCommand          cmd; 
            bool                usePooling = true;
            SqlStateConnection  conn = null; 
            int                 lockCookie; 

            Debug.Assert(context != null, "context != null"); 

            try {
                Debug.Trace("SqlSessionStateStore", "Calling Sql Set, id=" + id);
 
                Debug.Assert(item.Items != null, "item.Items != null");
                Debug.Assert(item.StaticObjects != null, "item.StaticObjects != null"); 
 
                SessionIDManager.CheckIdLength(id, true /* throwOnFail */);
 
                try {
                    SessionStateUtility.SerializeStoreData(item, ITEM_SHORT_LENGTH, out buf, out length, s_configCompressionEnabled);
                }
                catch { 
                    if (!newItem) {
                        ((SessionStateStoreProviderBase)this).ReleaseItemExclusive(context, id, lockId); 
                    } 
                    throw;
                } 

                // Save it to the store

                if (lockId == null) { 
                    lockCookie = 0;
                } 
                else { 
                    lockCookie = (int)lockId;
                } 

                conn = GetConnection(id, ref usePooling);

                if (!newItem) { 
                    Debug.Assert(_rqOrigStreamLen > 0, "_rqOrigStreamLen > 0");
                    if (length <= ITEM_SHORT_LENGTH) { 
                        if (_rqOrigStreamLen <= ITEM_SHORT_LENGTH) { 
                            cmd = conn.TempUpdateShort;
                        } 
                        else {
                            cmd = conn.TempUpdateShortNullLong;
                        }
                    } 
                    else {
                        if (_rqOrigStreamLen <= ITEM_SHORT_LENGTH) { 
                            cmd = conn.TempUpdateLongNullShort; 
                        }
                        else { 
                            cmd = conn.TempUpdateLong;
                        }
                    }
 
                }
                else { 
                    if (length <= ITEM_SHORT_LENGTH) { 
                        cmd = conn.TempInsertShort;
                    } 
                    else {
                        cmd = conn.TempInsertLong;
                    }
                } 

                cmd.Parameters[0].Value = id + _partitionInfo.AppSuffix; 
                cmd.Parameters[1].Size = length; 
                cmd.Parameters[1].Value = buf;
                cmd.Parameters[2].Value = item.Timeout; 
                if (!newItem) {
                    cmd.Parameters[3].Value = lockCookie;
                }
                SqlExecuteNonQueryWithRetry(cmd, newItem, id); 
            }
            finally { 
                DisposeOrReuseConnection(ref conn, usePooling); 
            }
        } 

        public override void RemoveItem(HttpContext context,
                                        String id,
                                        object lockId, 
                                        SessionStateStoreData item) {
            Debug.Trace("SqlSessionStateStore", "Calling Sql Remove, id=" + id); 
            Debug.Assert(lockId != null, "lockId != null"); 
            Debug.Assert(context != null, "context != null");
 
            bool                usePooling = true;
            SqlStateConnection  conn = null;
            int                 lockCookie = (int)lockId;
 
            try {
                SessionIDManager.CheckIdLength(id, true /* throwOnFail */); 
 
                conn = GetConnection(id, ref usePooling);
                SqlCommand cmd = conn.TempRemove; 
                cmd.Parameters[0].Value = id + _partitionInfo.AppSuffix;
                cmd.Parameters[1].Value = lockCookie;
                SqlExecuteNonQueryWithRetry(cmd, false, null);
            } 
            finally {
                DisposeOrReuseConnection(ref conn, usePooling); 
            } 
        }
 
        public override void ResetItemTimeout(HttpContext context, String id) {
            Debug.Trace("SqlSessionStateStore", "Calling Sql ResetTimeout, id=" + id);
            Debug.Assert(context != null, "context != null");
 
            bool                usePooling = true;
            SqlStateConnection  conn = null; 
 
            try {
                SessionIDManager.CheckIdLength(id, true /* throwOnFail */); 

                conn = GetConnection(id, ref usePooling);
                SqlCommand cmd = conn.TempResetTimeout;
                cmd.Parameters[0].Value = id + _partitionInfo.AppSuffix; 
                SqlExecuteNonQueryWithRetry(cmd, false, null);
            } 
            finally { 
                DisposeOrReuseConnection(ref conn, usePooling);
            } 
        }

        public override SessionStateStoreData CreateNewStoreData(HttpContext context, int timeout)
        { 
            Debug.Assert(context != null, "context != null");
            return SessionStateUtility.CreateLegitStoreData(context, null, null, timeout); 
        } 

        public override void CreateUninitializedItem(HttpContext context, String id, int timeout) { 
            Debug.Trace("SqlSessionStateStore", "Calling Sql InsertUninitializedItem, id=" + id);
            Debug.Assert(context != null, "context != null");

            bool                    usePooling = true; 
            SqlStateConnection      conn = null;
            byte []                 buf; 
            int                     length; 

            try { 
                SessionIDManager.CheckIdLength(id, true /* throwOnFail */);

                // Store an empty data
                SessionStateUtility.SerializeStoreData(CreateNewStoreData(context, timeout), 
                                ITEM_SHORT_LENGTH, out buf, out length, s_configCompressionEnabled);
 
                conn = GetConnection(id, ref usePooling); 

                SqlCommand cmd = conn.TempInsertUninitializedItem; 
                cmd.Parameters[0].Value = id + _partitionInfo.AppSuffix;
                cmd.Parameters[1].Size = length;
                cmd.Parameters[1].Value = buf;
                cmd.Parameters[2].Value = timeout; 
                SqlExecuteNonQueryWithRetry(cmd, true, id);
            } 
            finally { 
                DisposeOrReuseConnection(ref conn, usePooling);
            } 
        }

        static bool IsInsertPKException(SqlException ex, bool ignoreInsertPKException, string id) {
            // If the severity is greater than 20, we have a serious error. 
            // The server usually closes the connection in these cases.
           if (ex != null && 
                ex.Number == SQL_ERROR_PRIMARY_KEY_VIOLATION && 
                ignoreInsertPKException) {
 
                Debug.Trace("SessionStateClientSet",
                    "Insert failed because of primary key violation; just leave gracefully; id=" + id);

                // It's possible that two threads (from the same session) are creating the session 
                // state, both failed to get it first, and now both tried to insert it.
                // One thread may lose with a Primary Key Violation error. If so, that thread will 
                // just lose and exit gracefully. 
                return true;
           } 
           return false;
        }

        static bool IsFatalSqlException(SqlException ex) { 
            // We will retry sql operations for serious errors.
            // We consider fatal exceptions any error with severity >= 20. 
            // In this case, the SQL server closes the connection. 
            //
 


            if(ex != null &&
                (ex.Class >= 20 || 
                 ex.Number == SQL_CANNOT_OPEN_DATABASE_FOR_LOGIN ||
                 ex.Number == SQL_TIMEOUT_EXPIRED)) { 
                return true; 
            }
            return false; 
        }

        static void ClearFlagForClearPoolInProgress() {
           // clear s_isClearPoolInProgress if it was set 
           Interlocked.CompareExchange(ref s_isClearPoolInProgress, 0, 1);
        } 
 
        static bool CanRetry(SqlException ex, SqlConnection conn,
                                            ref bool isFirstAttempt, ref DateTime endRetryTime) { 
            if (s_retryInterval.Seconds <= 0) {
                // no retry policy set
                return false;
            } 
            if (!IsFatalSqlException(ex)) {
                if (!isFirstAttempt) { 
                    ClearFlagForClearPoolInProgress(); 
                }
                return false; 
            }
            if (isFirstAttempt) {
                // check if someone has called ClearPool for this connection string
                // s_isClearPoolInProgress can be: 
                // 0 - no one called ClearPool;
                // 1 - ClearPool is in progress or has already been called 
 
                // If no one called ClearPool (s_isClearPoolInProgress = 0), then
                // make s_isClearPoolInProgress 1 and call clear pool 
                if (0 == Interlocked.CompareExchange(ref s_isClearPoolInProgress, 1, 0)) {
                    Debug.Trace("SqlSessionStateStore", "CanRetry: Call ClearPool to destroy the corrupted connections in the pool");
                    SqlConnection.ClearPool(conn);
                } 

                // First time we sleep longer than for subsequent retries. 
                Thread.Sleep(FIRST_RETRY_SLEEP_TIME); 
                endRetryTime = DateTime.UtcNow.Add(s_retryInterval);
 
                isFirstAttempt = false;
                return true;
            }
            if (DateTime.UtcNow > endRetryTime) { 
                // the specified retry interval is up, we can't retry anymore
                if (!isFirstAttempt) { 
                    ClearFlagForClearPoolInProgress(); 
                }
                return false; 
            }
            // sleep the specified time and allow retry
            Thread.Sleep(RETRY_SLEEP_TIME);
            return true; 
        }
 
        static int SqlExecuteNonQueryWithRetry(SqlCommand cmd, bool ignoreInsertPKException, string id) { 
            bool isFirstAttempt = true;
            DateTime endRetryTime = DateTime.UtcNow; 

            while(true) {
                try {
                   if (cmd.Connection.State != ConnectionState.Open) { 
                       // reopen the connection
                       // (gets closed if a previous operation throwed a SQL exception with severity >= 20) 
                       cmd.Connection.Open(); 
                   }
                   int result = cmd.ExecuteNonQuery(); 
                   // the operation succeeded
                   // If we retried, it's possible ClearPool has been called.
                   // In this case, we clear the flag that shows ClearPool is in progress.
                   if (!isFirstAttempt) { 
                       ClearFlagForClearPoolInProgress();
                   } 
                   return result; 
                }
                catch (SqlException e) { 
                   // if specified, ignore primary key violations
                   if (IsInsertPKException(e, ignoreInsertPKException, id)) {
                       // ignoreInsertPKException = insert && newItem
                       return -1; 
                   }
 
                   if (!CanRetry(e, cmd.Connection, ref isFirstAttempt, ref endRetryTime)) { 
                       // just throw, because not all conditions to retry are satisfied
                       ThrowSqlConnectionException(cmd.Connection, e); 
                   }
                }
                catch (Exception e) {
                   // just throw, we have a different Exception 
                   ThrowSqlConnectionException(cmd.Connection, e);
                } 
            } 
        }
 
        static SqlDataReader SqlExecuteReaderWithRetry(SqlCommand cmd, CommandBehavior cmdBehavior) {
            bool isFirstAttempt = true;
            DateTime endRetryTime = DateTime.UtcNow;
 
            while(true) {
                try { 
                    if (cmd.Connection.State != ConnectionState.Open) { 
                       // reopen the connection
                       // (gets closed if a previous operation throwed a SQL exception with severity >= 20) 
                        cmd.Connection.Open();
                    }
                    SqlDataReader reader = cmd.ExecuteReader(cmdBehavior);
                    // the operation succeeded 
                    if (!isFirstAttempt) {
                        ClearFlagForClearPoolInProgress(); 
                    } 
                    return reader;
                } 
                catch (SqlException e) {
                    if (!CanRetry(e, cmd.Connection, ref isFirstAttempt, ref endRetryTime)) {
                        // just throw, default to previous behavior
                        ThrowSqlConnectionException(cmd.Connection, e); 
                    }
                } 
                catch (Exception e) { 
                   // just throw, we have a different Exception
                   ThrowSqlConnectionException(cmd.Connection, e); 
                }
            }
        }
 

        internal class SqlPartitionInfo : PartitionInfo { 
            bool            _useIntegratedSecurity; 
            string          _sqlConnectionString;
            string          _tracingPartitionString; 
            SupportFlags    _support = SupportFlags.Uninitialized;
            string          _appSuffix;
            object          _lock = new object();
            bool            _sqlInfoInited; 

            const string APP_SUFFIX_FORMAT = "x8"; 
            const int   APPID_MAX = 280; 
            const int   SQL_2000_MAJ_VER = 8;
 
            internal SqlPartitionInfo(ResourcePool rpool, bool useIntegratedSecurity, string sqlConnectionString)
                    : base(rpool) {
                _useIntegratedSecurity = useIntegratedSecurity;
                _sqlConnectionString = sqlConnectionString; 
                Debug.Trace("PartitionInfo", "Created a new info, sqlConnectionString=" + sqlConnectionString);
            } 
 
            internal bool UseIntegratedSecurity {
                get { return _useIntegratedSecurity; } 
            }

            internal string SqlConnectionString {
                get { return _sqlConnectionString; } 
            }
 
            internal SupportFlags SupportFlags { 
                get { return _support; }
                set { _support = value; } 
            }

            protected override string TracingPartitionString {
                get { 
                    if (_tracingPartitionString == null) {
                        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(_sqlConnectionString); 
                        builder.Password = String.Empty; 
                        builder.UserID = String.Empty;
                        _tracingPartitionString = builder.ConnectionString; 
                    }
                    return _tracingPartitionString;
                }
            } 

            internal string AppSuffix { 
                get { return _appSuffix; } 
            }
 
            void GetServerSupportOptions(SqlConnection sqlConnection) {
                Debug.Assert(SupportFlags == SupportFlags.Uninitialized);

                SqlCommand      cmd; 
                SqlDataReader   reader = null;
                SupportFlags    flags = SupportFlags.None; 
                bool            v2 = false; 
                SqlParameter    p;
 
                // First, check if the SQL server is running Whidbey scripts
                cmd = new SqlCommand("Select name from sysobjects where type = 'P' and name = 'TempGetVersion'", sqlConnection);
                cmd.CommandType = CommandType.Text;
 
                using(reader = SqlExecuteReaderWithRetry(cmd, CommandBehavior.SingleRow)) {
                    if (reader.Read()) { 
                        // This function first appears in Whidbey (v2).  So we know it's 
                        // at least 2.0 even without reading its content.
                        v2 = true; 
                    }
                }

                if (!v2) { 

                    if (s_usePartition) { 
                        throw new HttpException( 
                                SR.GetString(SR.Need_v2_SQL_Server_partition_resolver,
                                            s_configPartitionResolverType, sqlConnection.DataSource, sqlConnection.Database)); 
                    }
                    else {
                        throw new HttpException(
                            SR.GetString(SR.Need_v2_SQL_Server)); 
                    }
                } 
 
                // Then, see if it's SQL 2000 or above
 
                cmd = new SqlCommand("dbo.GetMajorVersion", sqlConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                p = cmd.Parameters.Add(new SqlParameter("@@ver", SqlDbType.Int));
                p.Direction = ParameterDirection.Output; 

                SqlExecuteNonQueryWithRetry(cmd, false, null); 
                try { 
                    if ((int)p.Value >= SQL_2000_MAJ_VER) {
                        // For details, see the extensive doc in DoGet method. 
                        flags |= SupportFlags.GetLockAge;
                    }

                    Debug.Trace("PartitionInfo", "SupportFlags initialized to " + flags); 

                    SupportFlags = flags; 
                } 
                catch (Exception e) {
                    SqlSessionStateStore.ThrowSqlConnectionException(sqlConnection, e); 
                }

            }
 

            internal void InitSqlInfo(SqlConnection sqlConnection) { 
                if (_sqlInfoInited) { 
                    return;
                } 

                lock (_lock) {
                    if (_sqlInfoInited) {
                        return; 
                    }
 
                    GetServerSupportOptions(sqlConnection); 

                    // Get AppSuffix info 

                    SqlParameter p;

                    SqlCommand  cmdTempGetAppId = new SqlCommand("dbo.TempGetAppID", sqlConnection); 
                    cmdTempGetAppId.CommandType = CommandType.StoredProcedure;
                    cmdTempGetAppId.CommandTimeout = s_commandTimeout; 
 
                    // AppDomainAppIdInternal will contain the whole metabase path of the request's app
                    // e.g. /lm/w3svc/1/root/fxtest 
                    p = cmdTempGetAppId.Parameters.Add(new SqlParameter("@appName", SqlDbType.VarChar, APPID_MAX));
                    p.Value = HttpRuntime.AppDomainAppIdInternal;

                    p = cmdTempGetAppId.Parameters.Add(new SqlParameter("@appId", SqlDbType.Int)); 
                    p.Direction = ParameterDirection.Output;
                    p.Value = Convert.DBNull; 
 
                    cmdTempGetAppId.ExecuteNonQuery();
                    Debug.Assert(!Convert.IsDBNull(p), "!Convert.IsDBNull(p)"); 
                    int appId = (int) p.Value;
                    _appSuffix = (appId).ToString(APP_SUFFIX_FORMAT, CultureInfo.InvariantCulture);

                    _sqlInfoInited = true; 
                }
            } 
        }; 

        /* 
            Here are all the sprocs created for session state and how they're used:

            CreateTempTables
            - Called during setup 

            DeleteExpiredSessions 
            - Called by SQL agent to remove expired sessions 

            GetHashCode 
            - Called by sproc TempGetAppID

            GetMajorVersion
            - Called during setup 

            TempGetAppID 
            - Called when an asp.net application starts up 

            TempGetStateItem 
            - Used for ReadOnly session state
            - Called by v1 asp.net
            - Called by v1.1 asp.net against SQL 7
 
            TempGetStateItem2
            - Used for ReadOnly session state 
            - Called by v1.1 asp.net against SQL 2000 

            TempGetStateItem3 
            - Used for ReadOnly session state
            - Called by v2 asp.net

            TempGetStateItemExclusive 
            - Called by v1 asp.net
            - Called by v1.1 asp.net against SQL 7 
 
            TempGetStateItemExclusive2
            - Called by v1.1 asp.net against SQL 2000 

            TempGetStateItemExclusive3
            - Called by v2 asp.net
 
            TempGetVersion
            - Called by v2 asp.net when an application starts up 
 
            TempInsertStateItemLong
            - Used when creating a new session state with size > 7000 bytes 

            TempInsertStateItemShort
            - Used when creating a new session state with size <= 7000 bytes
 
            TempInsertUninitializedItem
            - Used when creating a new uninitilized session state (cookieless="true" and regenerateExpiredSessionId="true" in config) 
 
            TempReleaseStateItemExclusive
            - Used when a request that has acquired the session state (exclusively) hit an error during the page execution 

            TempRemoveStateItem
            - Used when a session is abandoned
 
            TempResetTimeout
            - Used when a request (with an active session state) is handled by an HttpHandler which doesn't support IRequiresSessionState interface. 
 
            TempUpdateStateItemLong
            - Used when updating a session state with size > 7000 bytes 

            TempUpdateStateItemLongNullShort
            - Used when updating a session state where original size <= 7000 bytes but new size > 7000 bytes
 
            TempUpdateStateItemShort
            - Used when updating a session state with size <= 7000 bytes 
 
            TempUpdateStateItemShortNullLong
            - Used when updating a session state where original size > 7000 bytes but new size <= 7000 bytes 

        */
        class SqlStateConnection : IDisposable {
            SqlConnection   _sqlConnection; 
            SqlCommand      _cmdTempGet;
            SqlCommand      _cmdTempGetExclusive; 
            SqlCommand      _cmdTempReleaseExclusive; 
            SqlCommand      _cmdTempInsertShort;
            SqlCommand      _cmdTempInsertLong; 
            SqlCommand      _cmdTempUpdateShort;
            SqlCommand      _cmdTempUpdateShortNullLong;
            SqlCommand      _cmdTempUpdateLong;
            SqlCommand      _cmdTempUpdateLongNullShort; 
            SqlCommand      _cmdTempRemove;
            SqlCommand      _cmdTempResetTimeout; 
            SqlCommand      _cmdTempInsertUninitializedItem; 

            SqlPartitionInfo    _partitionInfo; 

            internal SqlStateConnection(SqlPartitionInfo sqlPartitionInfo, TimeSpan retryInterval) {
                Debug.Trace("SessionStateConnectionIdentity", "Connecting under " + WindowsIdentity.GetCurrent().Name);
 
                _partitionInfo = sqlPartitionInfo;
                _sqlConnection = new SqlConnection(sqlPartitionInfo.SqlConnectionString); 
 
                bool isFirstAttempt = true;
                DateTime endRetryTime = DateTime.UtcNow; 

                while(true) {
                    try {
                        _sqlConnection.Open(); 
                        // the operation succeeded, exit the loop
                        if(!isFirstAttempt) { 
                            ClearFlagForClearPoolInProgress(); 
                        }
                        break; 
                    }
                    catch (SqlException e) {
                        if (e != null &&
                            (e.Number == SQL_LOGIN_FAILED || 
                             e.Number == SQL_LOGIN_FAILED_2 ||
                             e.Number == SQL_LOGIN_FAILED_3)) 
                        { 
                            string  user;
 
                            SqlConnectionStringBuilder  scsb = new SqlConnectionStringBuilder(sqlPartitionInfo.SqlConnectionString);
                            if (scsb.IntegratedSecurity) {
                                user = WindowsIdentity.GetCurrent().Name;
                            } 
                            else {
                                user = scsb.UserID; 
                            } 

                            HttpException outerException = new HttpException( 
                                    SR.GetString(SR.Login_failed_sql_session_database, user ), e);

                            outerException.SetFormatter(new UseLastUnhandledErrorFormatter(outerException));
                            ClearConnectionAndThrow(outerException); 
                        }
                        if (!CanRetry(e, _sqlConnection, ref isFirstAttempt, ref endRetryTime)) 
                        { 
                            // just throw, the retry conditions are not satisfied
                            ClearConnectionAndThrow(e); 
                        }
                    }
                    catch (Exception e) {
                        // just throw, we have a different Exception 
                        ClearConnectionAndThrow(e);
                    } 
                } 

                try { 
                    _partitionInfo.InitSqlInfo(_sqlConnection);
                    Debug.Assert(sqlPartitionInfo.SupportFlags != SupportFlags.Uninitialized);

                    PerfCounters.IncrementCounter(AppPerfCounter.SESSION_SQL_SERVER_CONNECTIONS); 
                }
                catch { 
                    Dispose(); 
                    throw;
                } 
            }

            void ClearConnectionAndThrow(Exception e) {
                SqlConnection connection = _sqlConnection; 
                _sqlConnection = null;
                ThrowSqlConnectionException(connection, e); 
            } 

             internal void ClearAllParameters() { 
                 ClearAllParameters(_cmdTempGet);
                 ClearAllParameters(_cmdTempGetExclusive);
                 ClearAllParameters(_cmdTempReleaseExclusive);
                 ClearAllParameters(_cmdTempInsertShort); 
                 ClearAllParameters(_cmdTempInsertLong);
                 ClearAllParameters(_cmdTempUpdateShort); 
                 ClearAllParameters(_cmdTempUpdateShortNullLong); 
                 ClearAllParameters(_cmdTempUpdateLong);
                 ClearAllParameters(_cmdTempUpdateLongNullShort); 
                 ClearAllParameters(_cmdTempRemove);
                 ClearAllParameters(_cmdTempResetTimeout);
                 ClearAllParameters(_cmdTempInsertUninitializedItem);
             } 

             internal void ClearAllParameters(SqlCommand cmd) { 
                 if (cmd == null) { 
                     return;
                 } 

                 foreach (SqlParameter param in cmd.Parameters) {
                     param.Value = Convert.DBNull;
                 } 
             }
 
            internal SqlCommand TempGet { 
                get {
                    if (_cmdTempGet == null) { 
                        SqlParameter p;

                        _cmdTempGet = new SqlCommand("dbo.TempGetStateItem3", _sqlConnection);
                        _cmdTempGet.CommandType = CommandType.StoredProcedure; 
                        _cmdTempGet.CommandTimeout = s_commandTimeout;
 
                        // Use a different set of parameters for the sprocs that support GetLockAge 
                        if ((_partitionInfo.SupportFlags &  SupportFlags.GetLockAge) != 0) {
                            _cmdTempGet.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH)); 
                            p = _cmdTempGet.Parameters.Add(new SqlParameter("@itemShort", SqlDbType.VarBinary, ITEM_SHORT_LENGTH));
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGet.Parameters.Add(new SqlParameter("@locked", SqlDbType.Bit));
                            p.Direction = ParameterDirection.Output; 
                            p = _cmdTempGet.Parameters.Add(new SqlParameter("@lockAge", SqlDbType.Int));
                            p.Direction = ParameterDirection.Output; 
                            p = _cmdTempGet.Parameters.Add(new SqlParameter("@lockCookie", SqlDbType.Int)); 
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGet.Parameters.Add(new SqlParameter("@actionFlags", SqlDbType.Int)); 
                            p.Direction = ParameterDirection.Output;
                        }
                        else {
                            _cmdTempGet.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH)); 
                            p = _cmdTempGet.Parameters.Add(new SqlParameter("@itemShort", SqlDbType.VarBinary, ITEM_SHORT_LENGTH));
                            p.Direction = ParameterDirection.Output; 
                            p = _cmdTempGet.Parameters.Add(new SqlParameter("@locked", SqlDbType.Bit)); 
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGet.Parameters.Add(new SqlParameter("@lockDate", SqlDbType.DateTime)); 
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGet.Parameters.Add(new SqlParameter("@lockCookie", SqlDbType.Int));
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGet.Parameters.Add(new SqlParameter("@actionFlags", SqlDbType.Int)); 
                            p.Direction = ParameterDirection.Output;
                        } 
                    } 

                    return _cmdTempGet; 
                }
            }

            internal SqlCommand TempGetExclusive { 
                get {
                    if (_cmdTempGetExclusive == null) { 
                        SqlParameter p; 

                        _cmdTempGetExclusive = new SqlCommand("dbo.TempGetStateItemExclusive3", _sqlConnection); 
                        _cmdTempGetExclusive.CommandType = CommandType.StoredProcedure;
                        _cmdTempGetExclusive.CommandTimeout = s_commandTimeout;

                        // Use a different set of parameters for the sprocs that support GetLockAge 
                        if ((_partitionInfo.SupportFlags &  SupportFlags.GetLockAge) != 0) {
                            _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH)); 
                            p = _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@itemShort", SqlDbType.VarBinary, ITEM_SHORT_LENGTH)); 
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@locked", SqlDbType.Bit)); 
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@lockAge", SqlDbType.Int));
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@lockCookie", SqlDbType.Int)); 
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@actionFlags", SqlDbType.Int)); 
                            p.Direction = ParameterDirection.Output; 
                        }
                        else { 
                            _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH));
                            p = _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@itemShort", SqlDbType.VarBinary, ITEM_SHORT_LENGTH));
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@locked", SqlDbType.Bit)); 
                            p.Direction = ParameterDirection.Output;
                            p = _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@lockDate", SqlDbType.DateTime)); 
                            p.Direction = ParameterDirection.Output; 
                            p = _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@lockCookie", SqlDbType.Int));
                            p.Direction = ParameterDirection.Output; 
                            p = _cmdTempGetExclusive.Parameters.Add(new SqlParameter("@actionFlags", SqlDbType.Int));
                            p.Direction = ParameterDirection.Output;
                        }
                    } 

                    return _cmdTempGetExclusive; 
                } 
            }
 
            internal SqlCommand TempReleaseExclusive {
                get {
                    if (_cmdTempReleaseExclusive == null) {
                        /* ReleaseExlusive */ 
                        _cmdTempReleaseExclusive = new SqlCommand("dbo.TempReleaseStateItemExclusive", _sqlConnection);
                        _cmdTempReleaseExclusive.CommandType = CommandType.StoredProcedure; 
                        _cmdTempReleaseExclusive.CommandTimeout = s_commandTimeout; 
                        _cmdTempReleaseExclusive.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH));
                        _cmdTempReleaseExclusive.Parameters.Add(new SqlParameter("@lockCookie", SqlDbType.Int)); 
                    }

                    return _cmdTempReleaseExclusive;
                } 
            }
 
            internal SqlCommand TempInsertLong { 
                get {
                    if (_cmdTempInsertLong == null) { 
                        _cmdTempInsertLong = new SqlCommand("dbo.TempInsertStateItemLong", _sqlConnection);
                        _cmdTempInsertLong.CommandType = CommandType.StoredProcedure;
                        _cmdTempInsertLong.CommandTimeout = s_commandTimeout;
                        _cmdTempInsertLong.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH)); 
                        _cmdTempInsertLong.Parameters.Add(new SqlParameter("@itemLong", SqlDbType.Image, 8000));
                        _cmdTempInsertLong.Parameters.Add(new SqlParameter("@timeout", SqlDbType.Int)); 
                    } 

                    return _cmdTempInsertLong; 
                }
            }

            internal SqlCommand TempInsertShort { 
                get {
                    /* Insert */ 
                    if (_cmdTempInsertShort == null) { 
                        _cmdTempInsertShort = new SqlCommand("dbo.TempInsertStateItemShort", _sqlConnection);
                        _cmdTempInsertShort.CommandType = CommandType.StoredProcedure; 
                        _cmdTempInsertShort.CommandTimeout = s_commandTimeout;
                        _cmdTempInsertShort.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH));
                        _cmdTempInsertShort.Parameters.Add(new SqlParameter("@itemShort", SqlDbType.VarBinary, ITEM_SHORT_LENGTH));
                        _cmdTempInsertShort.Parameters.Add(new SqlParameter("@timeout", SqlDbType.Int)); 
                    }
 
                    return _cmdTempInsertShort; 
                }
            } 

            internal SqlCommand TempUpdateLong {
                get {
                    if (_cmdTempUpdateLong == null) { 
                        _cmdTempUpdateLong = new SqlCommand("dbo.TempUpdateStateItemLong", _sqlConnection);
                        _cmdTempUpdateLong.CommandType = CommandType.StoredProcedure; 
                        _cmdTempUpdateLong.CommandTimeout = s_commandTimeout; 
                        _cmdTempUpdateLong.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH));
                        _cmdTempUpdateLong.Parameters.Add(new SqlParameter("@itemLong", SqlDbType.Image, 8000)); 
                        _cmdTempUpdateLong.Parameters.Add(new SqlParameter("@timeout", SqlDbType.Int));
                        _cmdTempUpdateLong.Parameters.Add(new SqlParameter("@lockCookie", SqlDbType.Int));
                    }
 
                    return _cmdTempUpdateLong;
                } 
            } 

            internal SqlCommand TempUpdateShort { 
                get {
                    /* Update */
                    if (_cmdTempUpdateShort == null) {
                        _cmdTempUpdateShort = new SqlCommand("dbo.TempUpdateStateItemShort", _sqlConnection); 
                        _cmdTempUpdateShort.CommandType = CommandType.StoredProcedure;
                        _cmdTempUpdateShort.CommandTimeout = s_commandTimeout; 
                        _cmdTempUpdateShort.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH)); 
                        _cmdTempUpdateShort.Parameters.Add(new SqlParameter("@itemShort", SqlDbType.VarBinary, ITEM_SHORT_LENGTH));
                        _cmdTempUpdateShort.Parameters.Add(new SqlParameter("@timeout", SqlDbType.Int)); 
                        _cmdTempUpdateShort.Parameters.Add(new SqlParameter("@lockCookie", SqlDbType.Int));
                    }

                    return _cmdTempUpdateShort; 

                } 
            } 

            internal SqlCommand TempUpdateShortNullLong { 
                get {
                    if (_cmdTempUpdateShortNullLong == null) {
                        _cmdTempUpdateShortNullLong = new SqlCommand("dbo.TempUpdateStateItemShortNullLong", _sqlConnection);
                        _cmdTempUpdateShortNullLong.CommandType = CommandType.StoredProcedure; 
                        _cmdTempUpdateShortNullLong.CommandTimeout = s_commandTimeout;
                        _cmdTempUpdateShortNullLong.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH)); 
                        _cmdTempUpdateShortNullLong.Parameters.Add(new SqlParameter("@itemShort", SqlDbType.VarBinary, ITEM_SHORT_LENGTH)); 
                        _cmdTempUpdateShortNullLong.Parameters.Add(new SqlParameter("@timeout", SqlDbType.Int));
                        _cmdTempUpdateShortNullLong.Parameters.Add(new SqlParameter("@lockCookie", SqlDbType.Int)); 
                    }

                    return _cmdTempUpdateShortNullLong;
                } 
            }
 
            internal SqlCommand TempUpdateLongNullShort { 
                get {
                    if (_cmdTempUpdateLongNullShort == null) { 
                        _cmdTempUpdateLongNullShort = new SqlCommand("dbo.TempUpdateStateItemLongNullShort", _sqlConnection);
                        _cmdTempUpdateLongNullShort.CommandType = CommandType.StoredProcedure;
                        _cmdTempUpdateLongNullShort.CommandTimeout = s_commandTimeout;
                        _cmdTempUpdateLongNullShort.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH)); 
                        _cmdTempUpdateLongNullShort.Parameters.Add(new SqlParameter("@itemLong", SqlDbType.Image, 8000));
                        _cmdTempUpdateLongNullShort.Parameters.Add(new SqlParameter("@timeout", SqlDbType.Int)); 
                        _cmdTempUpdateLongNullShort.Parameters.Add(new SqlParameter("@lockCookie", SqlDbType.Int)); 
                    }
 
                    return _cmdTempUpdateLongNullShort;
                }
            }
 
            internal SqlCommand TempRemove {
                get { 
                    if (_cmdTempRemove == null) { 
                        /* Remove */
                        _cmdTempRemove = new SqlCommand("dbo.TempRemoveStateItem", _sqlConnection); 
                        _cmdTempRemove.CommandType = CommandType.StoredProcedure;
                        _cmdTempRemove.CommandTimeout = s_commandTimeout;
                        _cmdTempRemove.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH));
                        _cmdTempRemove.Parameters.Add(new SqlParameter("@lockCookie", SqlDbType.Int)); 

                    } 
 
                    return _cmdTempRemove;
                } 
            }

            internal SqlCommand TempInsertUninitializedItem {
                get { 
                    if (_cmdTempInsertUninitializedItem == null) {
                        _cmdTempInsertUninitializedItem = new SqlCommand("dbo.TempInsertUninitializedItem", _sqlConnection); 
                        _cmdTempInsertUninitializedItem.CommandType = CommandType.StoredProcedure; 
                        _cmdTempInsertUninitializedItem.CommandTimeout = s_commandTimeout;
                        _cmdTempInsertUninitializedItem.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH)); 
                        _cmdTempInsertUninitializedItem.Parameters.Add(new SqlParameter("@itemShort", SqlDbType.VarBinary, ITEM_SHORT_LENGTH));
                        _cmdTempInsertUninitializedItem.Parameters.Add(new SqlParameter("@timeout", SqlDbType.Int));
                    }
 
                    return _cmdTempInsertUninitializedItem;
                } 
            } 

            internal SqlCommand TempResetTimeout { 
                get {
                    if (_cmdTempResetTimeout == null) {
                        /* ResetTimeout */
                        _cmdTempResetTimeout = new SqlCommand("dbo.TempResetTimeout", _sqlConnection); 
                        _cmdTempResetTimeout.CommandType = CommandType.StoredProcedure;
                        _cmdTempResetTimeout.CommandTimeout = s_commandTimeout; 
                        _cmdTempResetTimeout.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar, ID_LENGTH)); 
                    }
 
                    return _cmdTempResetTimeout;
                }
            }
 
            public void Dispose() {
                Debug.Trace("ResourcePool", "Disposing SqlStateConnection"); 
                if (_sqlConnection != null) { 
                    _sqlConnection.Close();
                    _sqlConnection = null; 
                    PerfCounters.DecrementCounter(AppPerfCounter.SESSION_SQL_SERVER_CONNECTIONS);
                }
            }
 
            internal SqlConnection Connection {
                get { return _sqlConnection; } 
            } 
        }
    } 
}

// File provided for Reference Use Only by Microsoft Corporation (c) 2007.
                        

Link Menu

Network programming in C#, Network Programming in VB.NET, Network Programming in .NET
This book is available now!
Buy at Amazon US or
Buy at Amazon UK