SqlProviderServices.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 / DataEntity / System / Data / SqlClient / SqlProviderServices.cs / 1305376 / SqlProviderServices.cs

                            //------------------------------------------------------------------------------ 
// 
//      Copyright (c) Microsoft Corporation.  All rights reserved.
// 
// 
// @owner  [....]
// @backupOwner [....] 
//----------------------------------------------------------------------------- 

namespace System.Data.SqlClient { 

    using System.Collections.Generic;
    using System.Data.Common;
    using System.Data.Common.CommandTrees; 
    using System.Data.Common.Utils;
    using System.Data.Entity; 
    using System.Data.Metadata.Edm; 
    using System.Diagnostics;
    using System.IO; 

    /// 
    /// The ProviderServices object for the SqlClient provider; we'll probably
    /// move this to the System.Data assembly once it can depend upon the extensions 
    /// assembly, and we'll make it public at that point.
    ///  
    internal sealed class SqlProviderServices : DbProviderServices { 

        ///  
        /// Singleton object;
        /// 
        internal static readonly SqlProviderServices Instance = new SqlProviderServices();
 
        /// 
        /// Create a Command Definition object, given the connection and command tree 
        ///  
        /// provider manifest that was determined from metadata
        /// command tree for the statement 
        /// an exectable command definition object
        protected override DbCommandDefinition CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree) {
            Debug.Assert(providerManifest != null, "CreateCommandDefinition passed null provider manifest to CreateDbCommandDefinition?");
            Debug.Assert(commandTree != null, "CreateCommandDefinition did not validate commandTree argument?"); 

            DbCommand prototype = CreateCommand(providerManifest, commandTree); 
            DbCommandDefinition result = this.CreateCommandDefinition(prototype); 
            return result;
        } 

        /// 
        /// Create a SqlCommand object given a command tree
        ///  
        /// command tree for the statement
        /// a command object 
        internal override DbCommand CreateCommand(DbCommandTree commandTree) { 
            EntityUtil.CheckArgumentNull(commandTree, "commandTree");
            StoreItemCollection storeMetadata = (StoreItemCollection)commandTree.MetadataWorkspace.GetItemCollection(DataSpace.SSpace); 
            Debug.Assert(storeMetadata.StoreProviderManifest != null, "StoreItemCollection has null StoreProviderManifest?");

            return this.CreateCommand(storeMetadata.StoreProviderManifest, commandTree);
        } 

        ///  
        /// Create a SqlCommand object, given the provider manifest and command tree 
        /// 
        /// provider manifest 
        /// command tree for the statement
        /// a command object
        private DbCommand CreateCommand(DbProviderManifest providerManifest, DbCommandTree commandTree) {
            EntityUtil.CheckArgumentNull(providerManifest, "providerManifest"); 
            EntityUtil.CheckArgumentNull(commandTree, "commandTree");
 
            SqlProviderManifest sqlManifest = (providerManifest as SqlProviderManifest); 
            if (sqlManifest == null)
            { 
                throw EntityUtil.Argument(System.Data.Entity.Strings.Mapping_Provider_WrongManifestType(typeof(SqlProviderManifest)));
            }

            SqlVersion sqlVersion = sqlManifest.SqlVersion; 
            SqlCommand command = new SqlCommand();
 
            EntityBid.Trace(" sqlVersion=%d commandTree=%d#\n", (int)sqlVersion, commandTree.ObjectId); 

            List parameters; 
            CommandType commandType;
            HashSet paramsToForceNonUnicode;
            command.CommandText = System.Data.SqlClient.SqlGen.SqlGenerator.GenerateSql(commandTree, sqlVersion, out parameters, out commandType, out paramsToForceNonUnicode);
            command.CommandType = commandType; 
            EntityBid.Trace(" Generated SQL=%ls\n", command.CommandText);
 
            // Get the function (if any) implemented by the command tree since this influences our interpretation of parameters 
            EdmFunction function = null;
            if (commandTree.CommandTreeKind == DbCommandTreeKind.Function) { 
                function = ((DbFunctionCommandTree)commandTree).EdmFunction;
            }
            // Now make sure we populate the command's parameters from the CQT's parameters:
            foreach (KeyValuePair queryParameter in commandTree.Parameters) { 
                SqlParameter parameter;
 
                // Use the corresponding function parameter TypeUsage where available (currently, the SSDL facets and 
                // type trump user-defined facets and type in the EntityCommand).
                FunctionParameter functionParameter; 
                if (null != function && function.Parameters.TryGetValue(queryParameter.Key, false, out functionParameter)) {
                    const bool preventTruncation = false;
                    parameter = CreateSqlParameter(functionParameter.Name, functionParameter.TypeUsage, functionParameter.Mode, DBNull.Value, preventTruncation, sqlVersion);
                } 
                else {
                    TypeUsage parameterType; 
                    if ( (paramsToForceNonUnicode != null) &&              //Reached when a Function Command Tree is passed an incorrect parameter name by the user. 
                         (paramsToForceNonUnicode.Contains(queryParameter.Key)) )
                    { 
                        parameterType = queryParameter.Value.ShallowCopy(new FacetValues { Unicode = false });
                    }
                    else
                    { 
                        parameterType = queryParameter.Value;
                    } 
                    const bool preventTruncation = false; 
                    parameter = CreateSqlParameter(queryParameter.Key, parameterType, ParameterMode.In, DBNull.Value, preventTruncation, sqlVersion);
                } 
                command.Parameters.Add(parameter);
            }

            // Now add parameters added as part of SQL gen (note: this feature is only safe for DML SQL gen which 
            // does not support user parameters, where there is no risk of name collision)
            if (null != parameters && 0 < parameters.Count) { 
                if (commandTree.CommandTreeKind != DbCommandTreeKind.Delete && 
                    commandTree.CommandTreeKind != DbCommandTreeKind.Insert &&
                    commandTree.CommandTreeKind != DbCommandTreeKind.Update) { 
                    throw EntityUtil.InternalError(EntityUtil.InternalErrorCode.SqlGenParametersNotPermitted);
                }
                foreach (SqlParameter parameter in parameters) {
                    command.Parameters.Add(parameter); 
                }
            } 
 
            return command;
        } 

        internal override void SetDbParameterValue(DbParameter parameter, TypeUsage parameterType, object value)
        {
            if (TypeSemantics.IsPrimitiveType(parameterType, PrimitiveTypeKind.String) || 
                TypeSemantics.IsPrimitiveType(parameterType, PrimitiveTypeKind.Binary))
            { 
                int? size = GetParameterSize(parameterType, ((parameter.Direction & ParameterDirection.Output) == ParameterDirection.Output)); 
                if(!size.HasValue)
                { 
                    // Remember the current Size
                    int previousSize = parameter.Size;

                    // Infer the Size from the value 
                    parameter.Size = 0;
                    parameter.Value = value; 
 
                    if (previousSize > -1)
                    { 
                        // The 'max' length was chosen as a specific value for the parameter's Size property on Sql8 (4000 or 8000)
                        // because no MaxLength was specified in the TypeUsage and the provider is Sql8.
                        // If the value's length is less than or equal to this preset size, then the Size value can be retained,
                        // otherwise this preset size must be removed in favor of the Size inferred from the value itself. 

                        // If the inferred Size is less than the preset 'max' size, restore that preset size 
                        if (parameter.Size < previousSize) 
                        {
                            parameter.Size = previousSize; 
                        }
                    }
                    else
                    { 
                        // -1 was chosen as the parameter's size because no MaxLength was specified in the TypeUsage and the
                        // provider is more recent than Sql8. However, it is more optimal to specify a non-max (-1) value for 
                        // the size where possible, since 'max' parameters may prevent, for example, filter pushdown. 
                        // (see Dev10#617447 for more details)
                        int suggestedLength = GetNonMaxLength(((SqlParameter)parameter).SqlDbType); 
                        if (parameter.Size < suggestedLength)
                        {
                            parameter.Size = suggestedLength;
                        } 
                        else if (parameter.Size > suggestedLength)
                        { 
                            // The parameter size is greater than the suggested length, so the suggested length cannot be used. 
                            // Since the provider is Sql9 or newer, set the size to max (-1) instead of the inferred size for better plan reuse.
                            parameter.Size = -1; 
                        }
                    }
                }
                else 
                {
                    // Just set the value 
                    parameter.Value = value; 
                }
            } 
            else
            {
                // Not a string or binary parameter - just set the value
                parameter.Value = value; 
            }
        } 
 
        protected override string GetDbProviderManifestToken(DbConnection connection) {
            EntityUtil.CheckArgumentNull(connection, "connection"); 

            SqlConnection sqlConnection = SqlProviderUtilities.GetRequiredSqlConnection(connection);

            if (string.IsNullOrEmpty(sqlConnection.ConnectionString)) { 
                throw EntityUtil.Argument(Strings.UnableToDetermineStoreVersion);
            } 
 
            string providerManifestToken = null;
            // Try to get the provider manifest token from the database connection 
            // That failing, try using connection to master database (in case the database doesn't exist yet)
            try
            {
                UsingConnection(sqlConnection, conn => 
                {
                    providerManifestToken = SqlVersionUtils.GetVersionHint(SqlVersionUtils.GetSqlVersion(conn)); 
                }); 
            }
            catch 
            {
                UsingMasterConnection(sqlConnection, conn =>
                {
                    providerManifestToken = SqlVersionUtils.GetVersionHint(SqlVersionUtils.GetSqlVersion(conn)); 
                });
            } 
            return providerManifestToken; 
        }
 
        protected override DbProviderManifest GetDbProviderManifest(string versionHint) {
            if (string.IsNullOrEmpty(versionHint)) {
                throw EntityUtil.Argument(Strings.UnableToDetermineStoreVersion);
            } 

            return new SqlProviderManifest(versionHint); 
        } 

        ///  
        /// Creates a SqlParameter given a name, type, and direction
        /// 
        internal static SqlParameter CreateSqlParameter(string name, TypeUsage type, ParameterMode mode, object value, bool preventTruncation, SqlVersion version) {
            int? size; 
            byte? precision;
            byte? scale; 
 
            SqlParameter result = new SqlParameter(name, value);
 
            // .Direction
            ParameterDirection direction = MetadataHelper.ParameterModeToParameterDirection(mode);
            if (result.Direction != direction) {
                result.Direction = direction; 
            }
 
            // .Size, .Precision, .Scale and .SqlDbType 
            // output parameters are handled differently (we need to ensure there is space for return
            // values where the user has not given a specific Size/MaxLength) 
            bool isOutParam = mode != ParameterMode.In;
            SqlDbType sqlDbType = GetSqlDbType(type, isOutParam, version, out size, out precision, out scale);

            if (result.SqlDbType != sqlDbType) { 
                result.SqlDbType = sqlDbType;
            } 
 
            // Note that we overwrite 'facet' parameters where either the value is different or
            // there is an output parameter. This is because output parameters in SqlClient have their 
            // facets clobbered if they are implicitly set (e.g. if the Size was implicitly set
            // by setting the value)
            if (size.HasValue)
            { 
                // size.HasValue is always true for Output parameters
                if ((isOutParam || result.Size != size.Value)) 
                { 
                    if (preventTruncation && size.Value != -1)
                    { 
                        // To prevent truncation, set the Size of the parameter to the larger of either
                        // the declared length or the actual length for the parameter. This allows SQL
                        // Server to complain if a value is too long while preventing cache misses for
                        // values within the range. 
                        result.Size = Math.Max(result.Size, size.Value);
                    } 
                    else 
                    {
                        result.Size = size.Value; 
                    }
                }
            }
            else 
            {
                PrimitiveTypeKind typeKind = MetadataHelper.GetPrimitiveTypeKind(type); 
                if (typeKind == PrimitiveTypeKind.String) 
                {
                    result.Size = GetDefaultStringMaxLength(version, sqlDbType); 
                }
                else if(typeKind == PrimitiveTypeKind.Binary)
                {
                    result.Size = GetDefaultBinaryMaxLength(version); 
                }
            } 
            if (precision.HasValue && (isOutParam || result.Precision != precision.Value)) { 
                result.Precision = precision.Value;
            } 
            if (scale.HasValue && (isOutParam || result.Scale != scale.Value)) {
                result.Scale = scale.Value;
            }
 
            // .IsNullable
            bool isNullable = TypeSemantics.IsNullable(type); 
            if (isOutParam || isNullable != result.IsNullable) { 
                result.IsNullable = isNullable;
            } 

            return result;
        }
 

        ///  
        /// Determines SqlDbType for the given primitive type. Extracts facet 
        /// information as well.
        ///  
        private static SqlDbType GetSqlDbType(TypeUsage type, bool isOutParam, SqlVersion version, out int? size, out byte? precision, out byte? scale) {
            // only supported for primitive type
            PrimitiveTypeKind primitiveTypeKind = MetadataHelper.GetPrimitiveTypeKind(type);
 
            size = default(int?);
            precision = default(byte?); 
            scale = default(byte?); 

            // 
            switch (primitiveTypeKind) {
                case PrimitiveTypeKind.Binary:
                    // for output parameters, ensure there is space...
                    size = GetParameterSize(type, isOutParam); 
                    return GetBinaryDbType(type);
 
                case PrimitiveTypeKind.Boolean: 
                    return SqlDbType.Bit;
 
                case PrimitiveTypeKind.Byte:
                    return SqlDbType.TinyInt;

                case PrimitiveTypeKind.Time: 
                    if (!SqlVersionUtils.IsPreKatmai(version)) {
                        precision = GetKatmaiDateTimePrecision(type, isOutParam); 
                    } 
                    return SqlDbType.Time;
 
                case PrimitiveTypeKind.DateTimeOffset:
                    if (!SqlVersionUtils.IsPreKatmai(version)) {
                        precision = GetKatmaiDateTimePrecision(type, isOutParam);
                    } 
                    return SqlDbType.DateTimeOffset;
 
                case PrimitiveTypeKind.DateTime: 
                    //For katmai pick the type with max precision which is datetime2
                    if (!SqlVersionUtils.IsPreKatmai(version)) { 
                        precision = GetKatmaiDateTimePrecision(type, isOutParam);
                        return SqlDbType.DateTime2;
                    }
                    else { 
                        return SqlDbType.DateTime;
                    } 
 
                case PrimitiveTypeKind.Decimal:
                    precision = GetParameterPrecision(type, null); 
                    scale = GetScale(type);
                    return SqlDbType.Decimal;

                case PrimitiveTypeKind.Double: 
                    return SqlDbType.Float;
 
                case PrimitiveTypeKind.Guid: 
                    return SqlDbType.UniqueIdentifier;
 
                case PrimitiveTypeKind.Int16:
                    return SqlDbType.SmallInt;

                case PrimitiveTypeKind.Int32: 
                    return SqlDbType.Int;
 
                case PrimitiveTypeKind.Int64: 
                    return SqlDbType.BigInt;
 
                case PrimitiveTypeKind.SByte:
                    return SqlDbType.SmallInt;

                case PrimitiveTypeKind.Single: 
                    return SqlDbType.Real;
 
                case PrimitiveTypeKind.String: 
                    size = GetParameterSize(type, isOutParam);
                    return GetStringDbType(type); 

                default:
                    Debug.Fail("unknown PrimitiveTypeKind " + primitiveTypeKind);
                    return SqlDbType.Variant; 
            }
        } 
 
        /// 
        /// Determines preferred value for SqlParameter.Size. Returns null 
        /// where there is no preference.
        /// 
        private static int? GetParameterSize(TypeUsage type, bool isOutParam) {
            Facet maxLengthFacet; 
            if (type.Facets.TryGetValue(DbProviderManifest.MaxLengthFacetName, false, out maxLengthFacet) &&
                null != maxLengthFacet.Value) { 
                if (maxLengthFacet.IsUnbounded) { 
                    return -1;
                } 
                else {
                    return (int?)maxLengthFacet.Value;
                }
            } 
            else if (isOutParam) {
                // if the parameter is a return/out/inout parameter, ensure there 
                // is space for any value 
                return -1;
            } 
            else {
                // no value
                return default(int?);
            } 
        }
 
        private static int GetNonMaxLength(SqlDbType type) 
        {
            int result = -1; 
            if (type == SqlDbType.NChar || type == SqlDbType.NVarChar)
            {
                result = 4000;
            } 
            else if(type == SqlDbType.Char || type == SqlDbType.VarChar ||
                    type == SqlDbType.Binary || type == SqlDbType.VarBinary) 
            { 
                result = 8000;
            } 
            return result;
        }

        private static int GetDefaultStringMaxLength(SqlVersion version, SqlDbType type) 
        {
            int result; 
            if (version < SqlVersion.Sql9) 
            {
                if (type == SqlDbType.NChar || type == SqlDbType.NVarChar) 
                {
                    result = 4000;
                }
                else 
                {
                    result = 8000; 
                } 
            }
            else 
            {
                result = -1;
            }
            return result; 
        }
 
        private static int GetDefaultBinaryMaxLength(SqlVersion version) 
        {
            int result; 
            if (version < SqlVersion.Sql9)
            {
                result = 8000;
            } 
            else
            { 
                result = -1; 
            }
            return result; 
        }

        /// 
        /// Returns SqlParameter.Precision where the type facet exists. Otherwise, 
        /// returns null or the maximum available precision to avoid truncation (which can occur
        /// for output parameters). 
        ///  
        private static byte? GetKatmaiDateTimePrecision(TypeUsage type, bool isOutParam) {
            byte? defaultIfUndefined = isOutParam ? (byte?)7 : (byte?)null; 
            return GetParameterPrecision(type, defaultIfUndefined);
        }

        ///  
        /// Returns SqlParameter.Precision where the type facet exists. Otherwise,
        /// returns null. 
        ///  
        private static byte? GetParameterPrecision(TypeUsage type, byte? defaultIfUndefined) {
            byte precision; 
            if (TypeHelpers.TryGetPrecision(type, out precision)) {
                return precision;
            }
            else { 
                return defaultIfUndefined;
            } 
        } 

        ///  
        /// Returns SqlParameter.Scale where the type facet exists. Otherwise,
        /// returns null.
        /// 
        private static byte? GetScale(TypeUsage type) { 
            byte scale;
            if (TypeHelpers.TryGetScale(type, out scale)) { 
                return scale; 
            }
            else { 
                return default(byte?);
            }
        }
 
        /// 
        /// Chooses the appropriate SqlDbType for the given string type. 
        ///  
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1308:NormalizeStringsToUppercase")]
        private static SqlDbType GetStringDbType(TypeUsage type) { 
            Debug.Assert(type.EdmType.BuiltInTypeKind == BuiltInTypeKind.PrimitiveType &&
                PrimitiveTypeKind.String == ((PrimitiveType)type.EdmType).PrimitiveTypeKind, "only valid for string type");

            SqlDbType dbType; 
            if (type.EdmType.Name.ToLowerInvariant() == "xml") {
                dbType = SqlDbType.Xml; 
            } 
            else {
                // Specific type depends on whether the string is a unicode string and whether it is a fixed length string. 
                // By default, assume widest type (unicode) and most common type (variable length)
                bool unicode;
                bool fixedLength;
                if (!TypeHelpers.TryGetIsFixedLength(type, out fixedLength)) { 
                    fixedLength = false;
                } 
 
                if (!TypeHelpers.TryGetIsUnicode(type, out unicode)) {
                    unicode = true; 
                }

                if (fixedLength) {
                    dbType = (unicode ? SqlDbType.NChar : SqlDbType.Char); 
                }
                else { 
                    dbType = (unicode ? SqlDbType.NVarChar : SqlDbType.VarChar); 
                }
            } 
            return dbType;
        }

        ///  
        /// Chooses the appropriate SqlDbType for the given binary type.
        ///  
        private static SqlDbType GetBinaryDbType(TypeUsage type) { 
            Debug.Assert(type.EdmType.BuiltInTypeKind == BuiltInTypeKind.PrimitiveType &&
                PrimitiveTypeKind.Binary == ((PrimitiveType)type.EdmType).PrimitiveTypeKind, "only valid for binary type"); 

            // Specific type depends on whether the binary value is fixed length. By default, assume variable length.
            bool fixedLength;
            if (!TypeHelpers.TryGetIsFixedLength(type, out fixedLength)) { 
                fixedLength = false;
            } 
 
            return fixedLength ? SqlDbType.Binary : SqlDbType.VarBinary;
        } 

        protected override string DbCreateDatabaseScript(string providerManifestToken, StoreItemCollection storeItemCollection)
        {
            EntityUtil.CheckArgumentNull(providerManifestToken, "providerManifestToken"); 
            EntityUtil.CheckArgumentNull(storeItemCollection, "storeItemCollection");
            SqlVersion version = SqlVersionUtils.GetSqlVersion(providerManifestToken); 
            return CreateObjectsScript(version, storeItemCollection); 
        }
 
        protected override void DbCreateDatabase(DbConnection connection, int? commandTimeout, StoreItemCollection storeItemCollection)
        {
            EntityUtil.CheckArgumentNull(connection, "connection");
            EntityUtil.CheckArgumentNull(storeItemCollection, "storeItemCollection"); 

            SqlConnection sqlConnection = SqlProviderUtilities.GetRequiredSqlConnection(connection); 
            string databaseName = GetDatabaseName(sqlConnection); 
            string dataFileName, logFileName;
            GetDatabaseFileNames(sqlConnection, out dataFileName, out logFileName); 
            string createDatabaseScript = SqlDdlBuilder.CreateDatabaseScript(databaseName, dataFileName, logFileName);
            SqlVersion sqlVersion = GetSqlVersion(storeItemCollection);

            string createObjectsScript = CreateObjectsScript(sqlVersion, storeItemCollection); 

            UsingMasterConnection(sqlConnection, conn => 
            { 
                // create database
                CreateCommand(conn, createDatabaseScript, commandTimeout).ExecuteNonQuery(); 
            });

            // Create database already succeeded. If there is a failure from this point on, the user should be informed.
            try 
            {
                // Clear connection pool for the database connection since after the 'create database' call, a previously 
                // invalid connection may now be valid. 
                SqlConnection.ClearPool(sqlConnection);
 
                UsingConnection(sqlConnection, conn =>
                {
                    // create database objects
                    CreateCommand(conn, createObjectsScript, commandTimeout).ExecuteNonQuery(); 
                });
            } 
            catch (Exception e) 
            {
                if (EntityUtil.IsCatchableExceptionType(e)) 
                {
                    throw new InvalidOperationException(Strings.SqlProvider_IncompleteCreateDatabase, e);
                }
                throw; 
            }
        } 
 
        private static SqlVersion GetSqlVersion(StoreItemCollection storeItemCollection)
        { 
            SqlProviderManifest sqlManifest = (storeItemCollection.StoreProviderManifest as SqlProviderManifest);
            if (sqlManifest == null)
            {
                throw EntityUtil.Argument(System.Data.Entity.Strings.Mapping_Provider_WrongManifestType(typeof(SqlProviderManifest))); 
            }
            SqlVersion sqlVersion = sqlManifest.SqlVersion; 
            return sqlVersion; 
        }
 
        private static void GetDatabaseFileNames(SqlConnection sqlConnection, out string dataFileName, out string logFileName)
        {
            Debug.Assert(sqlConnection != null);
 
            var connectionStringBuilder = new SqlConnectionStringBuilder(sqlConnection.ConnectionString);
            string attachDBFile = connectionStringBuilder.AttachDBFilename; 
            if (string.IsNullOrEmpty(attachDBFile)) 
            {
                dataFileName = null; 
                logFileName = null;
            }
            else
            { 
                string datadir = null;
 
                //Handle the case when attachDBFilename starts with |DataDirectory| 
                dataFileName = DbConnectionOptions.ExpandDataDirectory("AttachDBFilename", attachDBFile, ref datadir);
 
                //Handle the other cases
                dataFileName = dataFileName ?? attachDBFile;

                logFileName = Path.ChangeExtension(dataFileName, "ldf"); 
            }
        } 
 
        protected override bool DbDatabaseExists(DbConnection connection, int? commandTimeout, StoreItemCollection storeItemCollection)
        { 
            EntityUtil.CheckArgumentNull(connection, "connection");
            EntityUtil.CheckArgumentNull(storeItemCollection, "storeItemCollection");

            SqlConnection sqlConnection = SqlProviderUtilities.GetRequiredSqlConnection(connection); 
            string databaseName = GetDatabaseName(sqlConnection);
 
            bool exists = false; 
            UsingMasterConnection(sqlConnection, conn =>
            { 
                SqlVersion sqlVersion = SqlVersionUtils.GetSqlVersion(conn);
                string databaseExistsScript = SqlDdlBuilder.CreateDatabaseExistsScript(databaseName, useDeprecatedSystemTable: sqlVersion == SqlVersion.Sql8);

                int result = (int) CreateCommand(conn, databaseExistsScript, commandTimeout).ExecuteScalar(); 
                exists = (result == 1);
            }); 
 
            return exists;
        } 

        protected override void DbDeleteDatabase(DbConnection connection, int? commandTimeout, StoreItemCollection storeItemCollection)
        {
            EntityUtil.CheckArgumentNull(connection, "connection"); 
            EntityUtil.CheckArgumentNull(storeItemCollection, "storeItemCollection");
 
            SqlConnection sqlConnection = SqlProviderUtilities.GetRequiredSqlConnection(connection); 
            string dropDatabaseScript = SqlDdlBuilder.DropDatabaseScript(GetDatabaseName(sqlConnection));
 
            // clear the connection pool in case someone's holding on to the database still
            SqlConnection.ClearPool(sqlConnection);
            UsingMasterConnection(sqlConnection, (conn) =>
            { 
                CreateCommand(conn, dropDatabaseScript, commandTimeout).ExecuteNonQuery();
            }); 
        } 

 
        private static string CreateObjectsScript(SqlVersion version, StoreItemCollection storeItemCollection)
        {
            return SqlDdlBuilder.CreateObjectsScript(storeItemCollection, createSchemas: version != SqlVersion.Sql8);
        } 

        private static SqlCommand CreateCommand(SqlConnection sqlConnection, string commandText, int? commandTimeout) 
        { 
            Debug.Assert(sqlConnection != null);
            if (string.IsNullOrEmpty(commandText)) 
            {
                // SqlCommand will complain if the command text is empty
                commandText = Environment.NewLine;
            } 
            var command = new SqlCommand(commandText, sqlConnection);
            if (commandTimeout.HasValue) 
            { 
                command.CommandTimeout = commandTimeout.Value;
            } 
            return command;
        }

        private static string GetDatabaseName(SqlConnection sqlConnection) 
        {
            var connectionBuilder = new SqlConnectionStringBuilder(sqlConnection.ConnectionString); 
            string initialCatalog = connectionBuilder.InitialCatalog; 
            if (string.IsNullOrEmpty(initialCatalog))
            { 
                throw EntityUtil.InvalidOperation(Strings.SqlProvider_DdlGeneration_MissingInitialCatalog);
            }
            return initialCatalog;
        } 

        private static void UsingConnection(SqlConnection sqlConnection, Action act) 
        { 
            // remember the connection string so that we can reset it credentials are wiped
            string holdConnectionString = sqlConnection.ConnectionString; 
            bool openingConnection = sqlConnection.State == ConnectionState.Closed;
            if (openingConnection)
            {
                sqlConnection.Open(); 
            }
            try 
            { 
                act(sqlConnection);
            } 
            finally
            {
                if (openingConnection && sqlConnection.State == ConnectionState.Open)
                { 
                    // if we opened the connection, we should close it
                    sqlConnection.Close(); 
                } 
                if (sqlConnection.ConnectionString != holdConnectionString)
                { 
                    sqlConnection.ConnectionString = holdConnectionString;
                }
            }
        } 

        private static void UsingMasterConnection(SqlConnection sqlConnection, Action act) 
        { 
            var connectionBuilder = new SqlConnectionStringBuilder(sqlConnection.ConnectionString)
            { 
                InitialCatalog = "master",
                AttachDBFilename = string.Empty, // any AttachDB path specified is not relevant to master
            };
 
            try
            { 
                using (var masterConnection = new SqlConnection(connectionBuilder.ConnectionString)) 
                {
                    UsingConnection(masterConnection, act); 
                }
            }
            catch (SqlException e)
            { 
                // if it appears that the credentials have been removed from the connection string, use an alternate explanation
                if (!connectionBuilder.IntegratedSecurity && 
                    (string.IsNullOrEmpty(connectionBuilder.UserID) || string.IsNullOrEmpty(connectionBuilder.Password))) 
                {
                    throw new InvalidOperationException(Strings.SqlProvider_CredentialsMissingForMasterConnection, e); 
                }
                throw;
            }
        } 

    } 
} 

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

namespace System.Data.SqlClient { 

    using System.Collections.Generic;
    using System.Data.Common;
    using System.Data.Common.CommandTrees; 
    using System.Data.Common.Utils;
    using System.Data.Entity; 
    using System.Data.Metadata.Edm; 
    using System.Diagnostics;
    using System.IO; 

    /// 
    /// The ProviderServices object for the SqlClient provider; we'll probably
    /// move this to the System.Data assembly once it can depend upon the extensions 
    /// assembly, and we'll make it public at that point.
    ///  
    internal sealed class SqlProviderServices : DbProviderServices { 

        ///  
        /// Singleton object;
        /// 
        internal static readonly SqlProviderServices Instance = new SqlProviderServices();
 
        /// 
        /// Create a Command Definition object, given the connection and command tree 
        ///  
        /// provider manifest that was determined from metadata
        /// command tree for the statement 
        /// an exectable command definition object
        protected override DbCommandDefinition CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree) {
            Debug.Assert(providerManifest != null, "CreateCommandDefinition passed null provider manifest to CreateDbCommandDefinition?");
            Debug.Assert(commandTree != null, "CreateCommandDefinition did not validate commandTree argument?"); 

            DbCommand prototype = CreateCommand(providerManifest, commandTree); 
            DbCommandDefinition result = this.CreateCommandDefinition(prototype); 
            return result;
        } 

        /// 
        /// Create a SqlCommand object given a command tree
        ///  
        /// command tree for the statement
        /// a command object 
        internal override DbCommand CreateCommand(DbCommandTree commandTree) { 
            EntityUtil.CheckArgumentNull(commandTree, "commandTree");
            StoreItemCollection storeMetadata = (StoreItemCollection)commandTree.MetadataWorkspace.GetItemCollection(DataSpace.SSpace); 
            Debug.Assert(storeMetadata.StoreProviderManifest != null, "StoreItemCollection has null StoreProviderManifest?");

            return this.CreateCommand(storeMetadata.StoreProviderManifest, commandTree);
        } 

        ///  
        /// Create a SqlCommand object, given the provider manifest and command tree 
        /// 
        /// provider manifest 
        /// command tree for the statement
        /// a command object
        private DbCommand CreateCommand(DbProviderManifest providerManifest, DbCommandTree commandTree) {
            EntityUtil.CheckArgumentNull(providerManifest, "providerManifest"); 
            EntityUtil.CheckArgumentNull(commandTree, "commandTree");
 
            SqlProviderManifest sqlManifest = (providerManifest as SqlProviderManifest); 
            if (sqlManifest == null)
            { 
                throw EntityUtil.Argument(System.Data.Entity.Strings.Mapping_Provider_WrongManifestType(typeof(SqlProviderManifest)));
            }

            SqlVersion sqlVersion = sqlManifest.SqlVersion; 
            SqlCommand command = new SqlCommand();
 
            EntityBid.Trace(" sqlVersion=%d commandTree=%d#\n", (int)sqlVersion, commandTree.ObjectId); 

            List parameters; 
            CommandType commandType;
            HashSet paramsToForceNonUnicode;
            command.CommandText = System.Data.SqlClient.SqlGen.SqlGenerator.GenerateSql(commandTree, sqlVersion, out parameters, out commandType, out paramsToForceNonUnicode);
            command.CommandType = commandType; 
            EntityBid.Trace(" Generated SQL=%ls\n", command.CommandText);
 
            // Get the function (if any) implemented by the command tree since this influences our interpretation of parameters 
            EdmFunction function = null;
            if (commandTree.CommandTreeKind == DbCommandTreeKind.Function) { 
                function = ((DbFunctionCommandTree)commandTree).EdmFunction;
            }
            // Now make sure we populate the command's parameters from the CQT's parameters:
            foreach (KeyValuePair queryParameter in commandTree.Parameters) { 
                SqlParameter parameter;
 
                // Use the corresponding function parameter TypeUsage where available (currently, the SSDL facets and 
                // type trump user-defined facets and type in the EntityCommand).
                FunctionParameter functionParameter; 
                if (null != function && function.Parameters.TryGetValue(queryParameter.Key, false, out functionParameter)) {
                    const bool preventTruncation = false;
                    parameter = CreateSqlParameter(functionParameter.Name, functionParameter.TypeUsage, functionParameter.Mode, DBNull.Value, preventTruncation, sqlVersion);
                } 
                else {
                    TypeUsage parameterType; 
                    if ( (paramsToForceNonUnicode != null) &&              //Reached when a Function Command Tree is passed an incorrect parameter name by the user. 
                         (paramsToForceNonUnicode.Contains(queryParameter.Key)) )
                    { 
                        parameterType = queryParameter.Value.ShallowCopy(new FacetValues { Unicode = false });
                    }
                    else
                    { 
                        parameterType = queryParameter.Value;
                    } 
                    const bool preventTruncation = false; 
                    parameter = CreateSqlParameter(queryParameter.Key, parameterType, ParameterMode.In, DBNull.Value, preventTruncation, sqlVersion);
                } 
                command.Parameters.Add(parameter);
            }

            // Now add parameters added as part of SQL gen (note: this feature is only safe for DML SQL gen which 
            // does not support user parameters, where there is no risk of name collision)
            if (null != parameters && 0 < parameters.Count) { 
                if (commandTree.CommandTreeKind != DbCommandTreeKind.Delete && 
                    commandTree.CommandTreeKind != DbCommandTreeKind.Insert &&
                    commandTree.CommandTreeKind != DbCommandTreeKind.Update) { 
                    throw EntityUtil.InternalError(EntityUtil.InternalErrorCode.SqlGenParametersNotPermitted);
                }
                foreach (SqlParameter parameter in parameters) {
                    command.Parameters.Add(parameter); 
                }
            } 
 
            return command;
        } 

        internal override void SetDbParameterValue(DbParameter parameter, TypeUsage parameterType, object value)
        {
            if (TypeSemantics.IsPrimitiveType(parameterType, PrimitiveTypeKind.String) || 
                TypeSemantics.IsPrimitiveType(parameterType, PrimitiveTypeKind.Binary))
            { 
                int? size = GetParameterSize(parameterType, ((parameter.Direction & ParameterDirection.Output) == ParameterDirection.Output)); 
                if(!size.HasValue)
                { 
                    // Remember the current Size
                    int previousSize = parameter.Size;

                    // Infer the Size from the value 
                    parameter.Size = 0;
                    parameter.Value = value; 
 
                    if (previousSize > -1)
                    { 
                        // The 'max' length was chosen as a specific value for the parameter's Size property on Sql8 (4000 or 8000)
                        // because no MaxLength was specified in the TypeUsage and the provider is Sql8.
                        // If the value's length is less than or equal to this preset size, then the Size value can be retained,
                        // otherwise this preset size must be removed in favor of the Size inferred from the value itself. 

                        // If the inferred Size is less than the preset 'max' size, restore that preset size 
                        if (parameter.Size < previousSize) 
                        {
                            parameter.Size = previousSize; 
                        }
                    }
                    else
                    { 
                        // -1 was chosen as the parameter's size because no MaxLength was specified in the TypeUsage and the
                        // provider is more recent than Sql8. However, it is more optimal to specify a non-max (-1) value for 
                        // the size where possible, since 'max' parameters may prevent, for example, filter pushdown. 
                        // (see Dev10#617447 for more details)
                        int suggestedLength = GetNonMaxLength(((SqlParameter)parameter).SqlDbType); 
                        if (parameter.Size < suggestedLength)
                        {
                            parameter.Size = suggestedLength;
                        } 
                        else if (parameter.Size > suggestedLength)
                        { 
                            // The parameter size is greater than the suggested length, so the suggested length cannot be used. 
                            // Since the provider is Sql9 or newer, set the size to max (-1) instead of the inferred size for better plan reuse.
                            parameter.Size = -1; 
                        }
                    }
                }
                else 
                {
                    // Just set the value 
                    parameter.Value = value; 
                }
            } 
            else
            {
                // Not a string or binary parameter - just set the value
                parameter.Value = value; 
            }
        } 
 
        protected override string GetDbProviderManifestToken(DbConnection connection) {
            EntityUtil.CheckArgumentNull(connection, "connection"); 

            SqlConnection sqlConnection = SqlProviderUtilities.GetRequiredSqlConnection(connection);

            if (string.IsNullOrEmpty(sqlConnection.ConnectionString)) { 
                throw EntityUtil.Argument(Strings.UnableToDetermineStoreVersion);
            } 
 
            string providerManifestToken = null;
            // Try to get the provider manifest token from the database connection 
            // That failing, try using connection to master database (in case the database doesn't exist yet)
            try
            {
                UsingConnection(sqlConnection, conn => 
                {
                    providerManifestToken = SqlVersionUtils.GetVersionHint(SqlVersionUtils.GetSqlVersion(conn)); 
                }); 
            }
            catch 
            {
                UsingMasterConnection(sqlConnection, conn =>
                {
                    providerManifestToken = SqlVersionUtils.GetVersionHint(SqlVersionUtils.GetSqlVersion(conn)); 
                });
            } 
            return providerManifestToken; 
        }
 
        protected override DbProviderManifest GetDbProviderManifest(string versionHint) {
            if (string.IsNullOrEmpty(versionHint)) {
                throw EntityUtil.Argument(Strings.UnableToDetermineStoreVersion);
            } 

            return new SqlProviderManifest(versionHint); 
        } 

        ///  
        /// Creates a SqlParameter given a name, type, and direction
        /// 
        internal static SqlParameter CreateSqlParameter(string name, TypeUsage type, ParameterMode mode, object value, bool preventTruncation, SqlVersion version) {
            int? size; 
            byte? precision;
            byte? scale; 
 
            SqlParameter result = new SqlParameter(name, value);
 
            // .Direction
            ParameterDirection direction = MetadataHelper.ParameterModeToParameterDirection(mode);
            if (result.Direction != direction) {
                result.Direction = direction; 
            }
 
            // .Size, .Precision, .Scale and .SqlDbType 
            // output parameters are handled differently (we need to ensure there is space for return
            // values where the user has not given a specific Size/MaxLength) 
            bool isOutParam = mode != ParameterMode.In;
            SqlDbType sqlDbType = GetSqlDbType(type, isOutParam, version, out size, out precision, out scale);

            if (result.SqlDbType != sqlDbType) { 
                result.SqlDbType = sqlDbType;
            } 
 
            // Note that we overwrite 'facet' parameters where either the value is different or
            // there is an output parameter. This is because output parameters in SqlClient have their 
            // facets clobbered if they are implicitly set (e.g. if the Size was implicitly set
            // by setting the value)
            if (size.HasValue)
            { 
                // size.HasValue is always true for Output parameters
                if ((isOutParam || result.Size != size.Value)) 
                { 
                    if (preventTruncation && size.Value != -1)
                    { 
                        // To prevent truncation, set the Size of the parameter to the larger of either
                        // the declared length or the actual length for the parameter. This allows SQL
                        // Server to complain if a value is too long while preventing cache misses for
                        // values within the range. 
                        result.Size = Math.Max(result.Size, size.Value);
                    } 
                    else 
                    {
                        result.Size = size.Value; 
                    }
                }
            }
            else 
            {
                PrimitiveTypeKind typeKind = MetadataHelper.GetPrimitiveTypeKind(type); 
                if (typeKind == PrimitiveTypeKind.String) 
                {
                    result.Size = GetDefaultStringMaxLength(version, sqlDbType); 
                }
                else if(typeKind == PrimitiveTypeKind.Binary)
                {
                    result.Size = GetDefaultBinaryMaxLength(version); 
                }
            } 
            if (precision.HasValue && (isOutParam || result.Precision != precision.Value)) { 
                result.Precision = precision.Value;
            } 
            if (scale.HasValue && (isOutParam || result.Scale != scale.Value)) {
                result.Scale = scale.Value;
            }
 
            // .IsNullable
            bool isNullable = TypeSemantics.IsNullable(type); 
            if (isOutParam || isNullable != result.IsNullable) { 
                result.IsNullable = isNullable;
            } 

            return result;
        }
 

        ///  
        /// Determines SqlDbType for the given primitive type. Extracts facet 
        /// information as well.
        ///  
        private static SqlDbType GetSqlDbType(TypeUsage type, bool isOutParam, SqlVersion version, out int? size, out byte? precision, out byte? scale) {
            // only supported for primitive type
            PrimitiveTypeKind primitiveTypeKind = MetadataHelper.GetPrimitiveTypeKind(type);
 
            size = default(int?);
            precision = default(byte?); 
            scale = default(byte?); 

            // 
            switch (primitiveTypeKind) {
                case PrimitiveTypeKind.Binary:
                    // for output parameters, ensure there is space...
                    size = GetParameterSize(type, isOutParam); 
                    return GetBinaryDbType(type);
 
                case PrimitiveTypeKind.Boolean: 
                    return SqlDbType.Bit;
 
                case PrimitiveTypeKind.Byte:
                    return SqlDbType.TinyInt;

                case PrimitiveTypeKind.Time: 
                    if (!SqlVersionUtils.IsPreKatmai(version)) {
                        precision = GetKatmaiDateTimePrecision(type, isOutParam); 
                    } 
                    return SqlDbType.Time;
 
                case PrimitiveTypeKind.DateTimeOffset:
                    if (!SqlVersionUtils.IsPreKatmai(version)) {
                        precision = GetKatmaiDateTimePrecision(type, isOutParam);
                    } 
                    return SqlDbType.DateTimeOffset;
 
                case PrimitiveTypeKind.DateTime: 
                    //For katmai pick the type with max precision which is datetime2
                    if (!SqlVersionUtils.IsPreKatmai(version)) { 
                        precision = GetKatmaiDateTimePrecision(type, isOutParam);
                        return SqlDbType.DateTime2;
                    }
                    else { 
                        return SqlDbType.DateTime;
                    } 
 
                case PrimitiveTypeKind.Decimal:
                    precision = GetParameterPrecision(type, null); 
                    scale = GetScale(type);
                    return SqlDbType.Decimal;

                case PrimitiveTypeKind.Double: 
                    return SqlDbType.Float;
 
                case PrimitiveTypeKind.Guid: 
                    return SqlDbType.UniqueIdentifier;
 
                case PrimitiveTypeKind.Int16:
                    return SqlDbType.SmallInt;

                case PrimitiveTypeKind.Int32: 
                    return SqlDbType.Int;
 
                case PrimitiveTypeKind.Int64: 
                    return SqlDbType.BigInt;
 
                case PrimitiveTypeKind.SByte:
                    return SqlDbType.SmallInt;

                case PrimitiveTypeKind.Single: 
                    return SqlDbType.Real;
 
                case PrimitiveTypeKind.String: 
                    size = GetParameterSize(type, isOutParam);
                    return GetStringDbType(type); 

                default:
                    Debug.Fail("unknown PrimitiveTypeKind " + primitiveTypeKind);
                    return SqlDbType.Variant; 
            }
        } 
 
        /// 
        /// Determines preferred value for SqlParameter.Size. Returns null 
        /// where there is no preference.
        /// 
        private static int? GetParameterSize(TypeUsage type, bool isOutParam) {
            Facet maxLengthFacet; 
            if (type.Facets.TryGetValue(DbProviderManifest.MaxLengthFacetName, false, out maxLengthFacet) &&
                null != maxLengthFacet.Value) { 
                if (maxLengthFacet.IsUnbounded) { 
                    return -1;
                } 
                else {
                    return (int?)maxLengthFacet.Value;
                }
            } 
            else if (isOutParam) {
                // if the parameter is a return/out/inout parameter, ensure there 
                // is space for any value 
                return -1;
            } 
            else {
                // no value
                return default(int?);
            } 
        }
 
        private static int GetNonMaxLength(SqlDbType type) 
        {
            int result = -1; 
            if (type == SqlDbType.NChar || type == SqlDbType.NVarChar)
            {
                result = 4000;
            } 
            else if(type == SqlDbType.Char || type == SqlDbType.VarChar ||
                    type == SqlDbType.Binary || type == SqlDbType.VarBinary) 
            { 
                result = 8000;
            } 
            return result;
        }

        private static int GetDefaultStringMaxLength(SqlVersion version, SqlDbType type) 
        {
            int result; 
            if (version < SqlVersion.Sql9) 
            {
                if (type == SqlDbType.NChar || type == SqlDbType.NVarChar) 
                {
                    result = 4000;
                }
                else 
                {
                    result = 8000; 
                } 
            }
            else 
            {
                result = -1;
            }
            return result; 
        }
 
        private static int GetDefaultBinaryMaxLength(SqlVersion version) 
        {
            int result; 
            if (version < SqlVersion.Sql9)
            {
                result = 8000;
            } 
            else
            { 
                result = -1; 
            }
            return result; 
        }

        /// 
        /// Returns SqlParameter.Precision where the type facet exists. Otherwise, 
        /// returns null or the maximum available precision to avoid truncation (which can occur
        /// for output parameters). 
        ///  
        private static byte? GetKatmaiDateTimePrecision(TypeUsage type, bool isOutParam) {
            byte? defaultIfUndefined = isOutParam ? (byte?)7 : (byte?)null; 
            return GetParameterPrecision(type, defaultIfUndefined);
        }

        ///  
        /// Returns SqlParameter.Precision where the type facet exists. Otherwise,
        /// returns null. 
        ///  
        private static byte? GetParameterPrecision(TypeUsage type, byte? defaultIfUndefined) {
            byte precision; 
            if (TypeHelpers.TryGetPrecision(type, out precision)) {
                return precision;
            }
            else { 
                return defaultIfUndefined;
            } 
        } 

        ///  
        /// Returns SqlParameter.Scale where the type facet exists. Otherwise,
        /// returns null.
        /// 
        private static byte? GetScale(TypeUsage type) { 
            byte scale;
            if (TypeHelpers.TryGetScale(type, out scale)) { 
                return scale; 
            }
            else { 
                return default(byte?);
            }
        }
 
        /// 
        /// Chooses the appropriate SqlDbType for the given string type. 
        ///  
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1308:NormalizeStringsToUppercase")]
        private static SqlDbType GetStringDbType(TypeUsage type) { 
            Debug.Assert(type.EdmType.BuiltInTypeKind == BuiltInTypeKind.PrimitiveType &&
                PrimitiveTypeKind.String == ((PrimitiveType)type.EdmType).PrimitiveTypeKind, "only valid for string type");

            SqlDbType dbType; 
            if (type.EdmType.Name.ToLowerInvariant() == "xml") {
                dbType = SqlDbType.Xml; 
            } 
            else {
                // Specific type depends on whether the string is a unicode string and whether it is a fixed length string. 
                // By default, assume widest type (unicode) and most common type (variable length)
                bool unicode;
                bool fixedLength;
                if (!TypeHelpers.TryGetIsFixedLength(type, out fixedLength)) { 
                    fixedLength = false;
                } 
 
                if (!TypeHelpers.TryGetIsUnicode(type, out unicode)) {
                    unicode = true; 
                }

                if (fixedLength) {
                    dbType = (unicode ? SqlDbType.NChar : SqlDbType.Char); 
                }
                else { 
                    dbType = (unicode ? SqlDbType.NVarChar : SqlDbType.VarChar); 
                }
            } 
            return dbType;
        }

        ///  
        /// Chooses the appropriate SqlDbType for the given binary type.
        ///  
        private static SqlDbType GetBinaryDbType(TypeUsage type) { 
            Debug.Assert(type.EdmType.BuiltInTypeKind == BuiltInTypeKind.PrimitiveType &&
                PrimitiveTypeKind.Binary == ((PrimitiveType)type.EdmType).PrimitiveTypeKind, "only valid for binary type"); 

            // Specific type depends on whether the binary value is fixed length. By default, assume variable length.
            bool fixedLength;
            if (!TypeHelpers.TryGetIsFixedLength(type, out fixedLength)) { 
                fixedLength = false;
            } 
 
            return fixedLength ? SqlDbType.Binary : SqlDbType.VarBinary;
        } 

        protected override string DbCreateDatabaseScript(string providerManifestToken, StoreItemCollection storeItemCollection)
        {
            EntityUtil.CheckArgumentNull(providerManifestToken, "providerManifestToken"); 
            EntityUtil.CheckArgumentNull(storeItemCollection, "storeItemCollection");
            SqlVersion version = SqlVersionUtils.GetSqlVersion(providerManifestToken); 
            return CreateObjectsScript(version, storeItemCollection); 
        }
 
        protected override void DbCreateDatabase(DbConnection connection, int? commandTimeout, StoreItemCollection storeItemCollection)
        {
            EntityUtil.CheckArgumentNull(connection, "connection");
            EntityUtil.CheckArgumentNull(storeItemCollection, "storeItemCollection"); 

            SqlConnection sqlConnection = SqlProviderUtilities.GetRequiredSqlConnection(connection); 
            string databaseName = GetDatabaseName(sqlConnection); 
            string dataFileName, logFileName;
            GetDatabaseFileNames(sqlConnection, out dataFileName, out logFileName); 
            string createDatabaseScript = SqlDdlBuilder.CreateDatabaseScript(databaseName, dataFileName, logFileName);
            SqlVersion sqlVersion = GetSqlVersion(storeItemCollection);

            string createObjectsScript = CreateObjectsScript(sqlVersion, storeItemCollection); 

            UsingMasterConnection(sqlConnection, conn => 
            { 
                // create database
                CreateCommand(conn, createDatabaseScript, commandTimeout).ExecuteNonQuery(); 
            });

            // Create database already succeeded. If there is a failure from this point on, the user should be informed.
            try 
            {
                // Clear connection pool for the database connection since after the 'create database' call, a previously 
                // invalid connection may now be valid. 
                SqlConnection.ClearPool(sqlConnection);
 
                UsingConnection(sqlConnection, conn =>
                {
                    // create database objects
                    CreateCommand(conn, createObjectsScript, commandTimeout).ExecuteNonQuery(); 
                });
            } 
            catch (Exception e) 
            {
                if (EntityUtil.IsCatchableExceptionType(e)) 
                {
                    throw new InvalidOperationException(Strings.SqlProvider_IncompleteCreateDatabase, e);
                }
                throw; 
            }
        } 
 
        private static SqlVersion GetSqlVersion(StoreItemCollection storeItemCollection)
        { 
            SqlProviderManifest sqlManifest = (storeItemCollection.StoreProviderManifest as SqlProviderManifest);
            if (sqlManifest == null)
            {
                throw EntityUtil.Argument(System.Data.Entity.Strings.Mapping_Provider_WrongManifestType(typeof(SqlProviderManifest))); 
            }
            SqlVersion sqlVersion = sqlManifest.SqlVersion; 
            return sqlVersion; 
        }
 
        private static void GetDatabaseFileNames(SqlConnection sqlConnection, out string dataFileName, out string logFileName)
        {
            Debug.Assert(sqlConnection != null);
 
            var connectionStringBuilder = new SqlConnectionStringBuilder(sqlConnection.ConnectionString);
            string attachDBFile = connectionStringBuilder.AttachDBFilename; 
            if (string.IsNullOrEmpty(attachDBFile)) 
            {
                dataFileName = null; 
                logFileName = null;
            }
            else
            { 
                string datadir = null;
 
                //Handle the case when attachDBFilename starts with |DataDirectory| 
                dataFileName = DbConnectionOptions.ExpandDataDirectory("AttachDBFilename", attachDBFile, ref datadir);
 
                //Handle the other cases
                dataFileName = dataFileName ?? attachDBFile;

                logFileName = Path.ChangeExtension(dataFileName, "ldf"); 
            }
        } 
 
        protected override bool DbDatabaseExists(DbConnection connection, int? commandTimeout, StoreItemCollection storeItemCollection)
        { 
            EntityUtil.CheckArgumentNull(connection, "connection");
            EntityUtil.CheckArgumentNull(storeItemCollection, "storeItemCollection");

            SqlConnection sqlConnection = SqlProviderUtilities.GetRequiredSqlConnection(connection); 
            string databaseName = GetDatabaseName(sqlConnection);
 
            bool exists = false; 
            UsingMasterConnection(sqlConnection, conn =>
            { 
                SqlVersion sqlVersion = SqlVersionUtils.GetSqlVersion(conn);
                string databaseExistsScript = SqlDdlBuilder.CreateDatabaseExistsScript(databaseName, useDeprecatedSystemTable: sqlVersion == SqlVersion.Sql8);

                int result = (int) CreateCommand(conn, databaseExistsScript, commandTimeout).ExecuteScalar(); 
                exists = (result == 1);
            }); 
 
            return exists;
        } 

        protected override void DbDeleteDatabase(DbConnection connection, int? commandTimeout, StoreItemCollection storeItemCollection)
        {
            EntityUtil.CheckArgumentNull(connection, "connection"); 
            EntityUtil.CheckArgumentNull(storeItemCollection, "storeItemCollection");
 
            SqlConnection sqlConnection = SqlProviderUtilities.GetRequiredSqlConnection(connection); 
            string dropDatabaseScript = SqlDdlBuilder.DropDatabaseScript(GetDatabaseName(sqlConnection));
 
            // clear the connection pool in case someone's holding on to the database still
            SqlConnection.ClearPool(sqlConnection);
            UsingMasterConnection(sqlConnection, (conn) =>
            { 
                CreateCommand(conn, dropDatabaseScript, commandTimeout).ExecuteNonQuery();
            }); 
        } 

 
        private static string CreateObjectsScript(SqlVersion version, StoreItemCollection storeItemCollection)
        {
            return SqlDdlBuilder.CreateObjectsScript(storeItemCollection, createSchemas: version != SqlVersion.Sql8);
        } 

        private static SqlCommand CreateCommand(SqlConnection sqlConnection, string commandText, int? commandTimeout) 
        { 
            Debug.Assert(sqlConnection != null);
            if (string.IsNullOrEmpty(commandText)) 
            {
                // SqlCommand will complain if the command text is empty
                commandText = Environment.NewLine;
            } 
            var command = new SqlCommand(commandText, sqlConnection);
            if (commandTimeout.HasValue) 
            { 
                command.CommandTimeout = commandTimeout.Value;
            } 
            return command;
        }

        private static string GetDatabaseName(SqlConnection sqlConnection) 
        {
            var connectionBuilder = new SqlConnectionStringBuilder(sqlConnection.ConnectionString); 
            string initialCatalog = connectionBuilder.InitialCatalog; 
            if (string.IsNullOrEmpty(initialCatalog))
            { 
                throw EntityUtil.InvalidOperation(Strings.SqlProvider_DdlGeneration_MissingInitialCatalog);
            }
            return initialCatalog;
        } 

        private static void UsingConnection(SqlConnection sqlConnection, Action act) 
        { 
            // remember the connection string so that we can reset it credentials are wiped
            string holdConnectionString = sqlConnection.ConnectionString; 
            bool openingConnection = sqlConnection.State == ConnectionState.Closed;
            if (openingConnection)
            {
                sqlConnection.Open(); 
            }
            try 
            { 
                act(sqlConnection);
            } 
            finally
            {
                if (openingConnection && sqlConnection.State == ConnectionState.Open)
                { 
                    // if we opened the connection, we should close it
                    sqlConnection.Close(); 
                } 
                if (sqlConnection.ConnectionString != holdConnectionString)
                { 
                    sqlConnection.ConnectionString = holdConnectionString;
                }
            }
        } 

        private static void UsingMasterConnection(SqlConnection sqlConnection, Action act) 
        { 
            var connectionBuilder = new SqlConnectionStringBuilder(sqlConnection.ConnectionString)
            { 
                InitialCatalog = "master",
                AttachDBFilename = string.Empty, // any AttachDB path specified is not relevant to master
            };
 
            try
            { 
                using (var masterConnection = new SqlConnection(connectionBuilder.ConnectionString)) 
                {
                    UsingConnection(masterConnection, act); 
                }
            }
            catch (SqlException e)
            { 
                // if it appears that the credentials have been removed from the connection string, use an alternate explanation
                if (!connectionBuilder.IntegratedSecurity && 
                    (string.IsNullOrEmpty(connectionBuilder.UserID) || string.IsNullOrEmpty(connectionBuilder.Password))) 
                {
                    throw new InvalidOperationException(Strings.SqlProvider_CredentialsMissingForMasterConnection, e); 
                }
                throw;
            }
        } 

    } 
} 

// 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