mcs/class/referencesource/System.Data.Entity/System/Data/SqlClient/SqlProviderServices.cs

   1 //------------------------------------------------------------------------------
   2 // <copyright file="SqlProviderServices.cs" company="Microsoft">
   3 //      Copyright (c) Microsoft Corporation.  All rights reserved.
   4 // </copyright>
   5 //
   6 // @owner  Microsoft
   7 // @backupOwner Microsoft
   8 //------------------------------------------------------------------------------
   9
  10 namespace System.Data.SqlClient {
  11
  12     using System.Collections.Generic;
  13     using System.Data.Common;
  14     using System.Data.Common.CommandTrees;
  15     using System.Data.Common.Utils;
  16     using System.Data.Entity;
  17     using System.Data.Metadata.Edm;
  18     using System.Data.Spatial;
  19     using System.Diagnostics;
  20     using System.IO;
  21     using System.Globalization;
  22
  23     /// <summary>
  24     /// The DbProviderServices implementation for the SqlClient provider for SQL Server.
  25     /// </summary>
  26     [CLSCompliant(false)]
  27     public sealed class SqlProviderServices : DbProviderServices
  28     {
  29         /// <summary>
  30         /// Private constructor to ensure only Singleton instance is created.
  31         /// </summary>
  32         private SqlProviderServices()
  33         {
  34         }
  35
  36         /// <summary>
  37         /// Singleton object;
  38         /// </summary>
  39         internal static readonly SqlProviderServices Instance = new SqlProviderServices();
  40
  41         /// <summary>
  42         /// The Singleton instance of the SqlProviderServices type.
  43         /// </summary>
  44         public static SqlProviderServices SingletonInstance
  45         {
  46             get { return Instance; }
  47         }
  48
  49         /// <summary>
  50         /// Create a Command Definition object, given the connection and command tree
  51         /// </summary>
  52         /// <param name="providerManifest">provider manifest that was determined from metadata</param>
  53         /// <param name="commandTree">command tree for the statement</param>
  54         /// <returns>an exectable command definition object</returns>
  55         protected override DbCommandDefinition CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree) {
  56             Debug.Assert(providerManifest != null, "CreateCommandDefinition passed null provider manifest to CreateDbCommandDefinition?");
  57             Debug.Assert(commandTree != null, "CreateCommandDefinition did not validate commandTree argument?");
  58
  59             DbCommand prototype = CreateCommand(providerManifest, commandTree);
  60             DbCommandDefinition result = this.CreateCommandDefinition(prototype);
  61             return result;
  62         }
  63
  64         /// <summary>
  65         /// Create a SqlCommand object given a command tree
  66         /// </summary>
  67         /// <param name="commandTree">command tree for the statement</param>
  68         /// <returns>a command object</returns>
  69         internal override DbCommand CreateCommand(DbCommandTree commandTree) {
  70             EntityUtil.CheckArgumentNull(commandTree, "commandTree");
  71             StoreItemCollection storeMetadata = (StoreItemCollection)commandTree.MetadataWorkspace.GetItemCollection(DataSpace.SSpace);
  72             Debug.Assert(storeMetadata.StoreProviderManifest != null, "StoreItemCollection has null StoreProviderManifest?");
  73
  74             return this.CreateCommand(storeMetadata.StoreProviderManifest, commandTree);
  75         }
  76
  77         /// <summary>
  78         /// Create a SqlCommand object, given the provider manifest and command tree
  79         /// </summary>
  80         /// <param name="providerManifest">provider manifest</param>
  81         /// <param name="commandTree">command tree for the statement</param>
  82         /// <returns>a command object</returns>
  83         private DbCommand CreateCommand(DbProviderManifest providerManifest, DbCommandTree commandTree) {
  84             EntityUtil.CheckArgumentNull(providerManifest, "providerManifest");
  85             EntityUtil.CheckArgumentNull(commandTree, "commandTree");
  86
  87             SqlProviderManifest sqlManifest = (providerManifest as SqlProviderManifest);
  88             if (sqlManifest == null)
  89             {
  90                 throw EntityUtil.Argument(System.Data.Entity.Strings.Mapping_Provider_WrongManifestType(typeof(SqlProviderManifest)));
  91             }
  92
  93             SqlVersion sqlVersion = sqlManifest.SqlVersion;
  94             SqlCommand command = new SqlCommand();
  95
  96             List<SqlParameter> parameters;
  97             CommandType commandType;
  98             HashSet<string> paramsToForceNonUnicode;
  99             command.CommandText = System.Data.SqlClient.SqlGen.SqlGenerator.GenerateSql(commandTree, sqlVersion, out parameters, out commandType, out paramsToForceNonUnicode);
 100             command.CommandType = commandType;
 101
 102             // Get the function (if any) implemented by the command tree since this influences our interpretation of parameters
 103             EdmFunction function = null;
 104             if (commandTree.CommandTreeKind == DbCommandTreeKind.Function) {
 105                 function = ((DbFunctionCommandTree)commandTree).EdmFunction;
 106             }
 107             // Now make sure we populate the command's parameters from the CQT's parameters:
 108             foreach (KeyValuePair<string, TypeUsage> queryParameter in commandTree.Parameters) {
 109                 SqlParameter parameter;
 110
 111                 // Use the corresponding function parameter TypeUsage where available (currently, the SSDL facets and
 112                 // type trump user-defined facets and type in the EntityCommand).
 113                 FunctionParameter functionParameter;
 114                 if (null != function && function.Parameters.TryGetValue(queryParameter.Key, false, out functionParameter)) {
 115                     const bool preventTruncation = false;
 116                     parameter = CreateSqlParameter(functionParameter.Name, functionParameter.TypeUsage, functionParameter.Mode, DBNull.Value, preventTruncation, sqlVersion);
 117                 }
 118                 else {
 119                     TypeUsage parameterType;
 120                     if ( (paramsToForceNonUnicode != null) &&              //Reached when a Function Command Tree is passed an incorrect parameter name by the user.
 121                          (paramsToForceNonUnicode.Contains(queryParameter.Key)) )
 122                     {
 123                         parameterType = queryParameter.Value.ShallowCopy(new FacetValues { Unicode = false });
 124                     }
 125                     else
 126                     {
 127                         parameterType = queryParameter.Value;
 128                     }
 129                     const bool preventTruncation = false;
 130                     parameter = CreateSqlParameter(queryParameter.Key, parameterType, ParameterMode.In, DBNull.Value, preventTruncation, sqlVersion);
 131                 }
 132                 command.Parameters.Add(parameter);
 133             }
 134
 135             // Now add parameters added as part of SQL gen (note: this feature is only safe for DML SQL gen which
 136             // does not support user parameters, where there is no risk of name collision)
 137             if (null != parameters && 0 < parameters.Count) {
 138                 if (commandTree.CommandTreeKind != DbCommandTreeKind.Delete &&
 139                     commandTree.CommandTreeKind != DbCommandTreeKind.Insert &&
 140                     commandTree.CommandTreeKind != DbCommandTreeKind.Update) {
 141                     throw EntityUtil.InternalError(EntityUtil.InternalErrorCode.SqlGenParametersNotPermitted);
 142                 }
 143                 foreach (SqlParameter parameter in parameters) {
 144                     command.Parameters.Add(parameter);
 145                 }
 146             }
 147
 148             return command;
 149         }
 150
 151         protected override void SetDbParameterValue(DbParameter parameter, TypeUsage parameterType, object value)
 152         {
 153             // Ensure a value that can be used with SqlParameter
 154             value = EnsureSqlParameterValue(value);
 155
 156             if (TypeSemantics.IsPrimitiveType(parameterType, PrimitiveTypeKind.String) ||
 157                 TypeSemantics.IsPrimitiveType(parameterType, PrimitiveTypeKind.Binary))
 158             {
 159                 int? size = GetParameterSize(parameterType, ((parameter.Direction & ParameterDirection.Output) == ParameterDirection.Output));
 160                 if(!size.HasValue)
 161                 {
 162                     // Remember the current Size
 163                     int previousSize = parameter.Size;
 164
 165                     // Infer the Size from the value
 166                     parameter.Size = 0;
 167                     parameter.Value = value;
 168
 169                     if (previousSize > -1)
 170                     {
 171                         // The 'max' length was chosen as a specific value for the parameter's Size property on Sql8 (4000 or 8000)
 172                         // because no MaxLength was specified in the TypeUsage and the provider is Sql8.
 173                         // If the value's length is less than or equal to this preset size, then the Size value can be retained,
 174                         // otherwise this preset size must be removed in favor of the Size inferred from the value itself.
 175
 176                         // If the inferred Size is less than the preset 'max' size, restore that preset size
 177                         if (parameter.Size < previousSize)
 178                         {
 179                             parameter.Size = previousSize;
 180                         }
 181                     }
 182                     else
 183                     {
 184                         // -1 was chosen as the parameter's size because no MaxLength was specified in the TypeUsage and the
 185                         // provider is more recent than Sql8. However, it is more optimal to specify a non-max (-1) value for
 186                         // the size where possible, since 'max' parameters may prevent, for example, filter pushdown.
 187                         // (see Dev10#617447 for more details)
 188                         int suggestedLength = GetNonMaxLength(((SqlParameter)parameter).SqlDbType);
 189                         if (parameter.Size < suggestedLength)
 190                         {
 191                             parameter.Size = suggestedLength;
 192                         }
 193                         else if (parameter.Size > suggestedLength)
 194                         {
 195                             // The parameter size is greater than the suggested length, so the suggested length cannot be used.
 196                             // Since the provider is Sql9 or newer, set the size to max (-1) instead of the inferred size for better plan reuse.
 197                             parameter.Size = -1;
 198                         }
 199                     }
 200                 }
 201                 else
 202                 {
 203                     // Just set the value
 204                     parameter.Value = value;
 205                 }
 206             }
 207             else
 208             {
 209                 // Not a string or binary parameter - just set the value
 210                 parameter.Value = value;
 211             }
 212         }
 213
 214         protected override string GetDbProviderManifestToken(DbConnection connection) {
 215             EntityUtil.CheckArgumentNull(connection, "connection");
 216
 217             SqlConnection sqlConnection = SqlProviderUtilities.GetRequiredSqlConnection(connection);
 218
 219             if (string.IsNullOrEmpty(sqlConnection.ConnectionString)) {
 220                 throw EntityUtil.Argument(Strings.UnableToDetermineStoreVersion);
 221             }
 222
 223             string providerManifestToken = null;
 224             // Try to get the provider manifest token from the database connection
 225             // That failing, try using connection to master database (in case the database doesn't exist yet)
 226             try
 227             {
 228                 UsingConnection(sqlConnection, conn =>
 229                 {
 230                     providerManifestToken = SqlVersionUtils.GetVersionHint(SqlVersionUtils.GetSqlVersion(conn));
 231                 });
 232             }
 233             catch
 234             {
 235                 UsingMasterConnection(sqlConnection, conn =>
 236                 {
 237                     providerManifestToken = SqlVersionUtils.GetVersionHint(SqlVersionUtils.GetSqlVersion(conn));
 238                 });
 239             }
 240             return providerManifestToken;
 241         }
 242
 243         protected override DbProviderManifest GetDbProviderManifest(string versionHint) {
 244             if (string.IsNullOrEmpty(versionHint)) {
 245                 throw EntityUtil.Argument(Strings.UnableToDetermineStoreVersion);
 246             }
 247
 248             return new SqlProviderManifest(versionHint);
 249         }
 250
 251         protected override Spatial.DbSpatialDataReader GetDbSpatialDataReader(DbDataReader fromReader, string versionHint)
 252         {
 253             EntityUtil.CheckArgumentNull(fromReader, "fromReader");
 254
 255             ValidateVersionHint(versionHint);
 256
 257             SqlDataReader underlyingReader = fromReader as SqlDataReader;
 258             if (underlyingReader == null)
 259             {
 260                 throw EntityUtil.ProviderIncompatible(Strings.SqlProvider_NeedSqlDataReader(fromReader.GetType()));
 261             }
 262
 263
 264              return new SqlSpatialDataReader(underlyingReader);
 265         }
 266
 267         protected override DbSpatialServices DbGetSpatialServices(string versionHint)
 268         {
 269             ValidateVersionHint(versionHint);
 270             return SqlSpatialServices.Instance;
 271         }
 272
 273         void ValidateVersionHint(string versionHint)
 274         {
 275             if (string.IsNullOrEmpty(versionHint))
 276             {
 277                 throw EntityUtil.Argument(Strings.UnableToDetermineStoreVersion);
 278             }
 279
 280             // GetSqlVersion will throw ArgumentException if manifestToken is null, empty, or not recognized.
 281             SqlVersion tokenVersion = SqlVersionUtils.GetSqlVersion(versionHint);
 282
 283             // SQL spatial support is only available for SQL Server 2008 and later
 284             if (tokenVersion < SqlVersion.Sql10)
 285             {
 286                 throw EntityUtil.ProviderIncompatible(Strings.SqlProvider_Sql2008RequiredForSpatial);
 287             }
 288         }
 289
 290         internal static SqlTypesAssembly GetSqlTypesAssembly()
 291         {
 292             SqlTypesAssembly sqlTypes;
 293             if (!TryGetSqlTypesAssembly(out sqlTypes))
 294             {
 295                 throw EntityUtil.SqlTypesAssemblyNotFound();
 296             }
 297             Debug.Assert(sqlTypes != null);
 298             return sqlTypes;
 299         }
 300
 301         internal static bool SqlTypesAssemblyIsAvailable
 302         {
 303             get
 304             {
 305                 SqlTypesAssembly notUsed;
 306                 return TryGetSqlTypesAssembly(out notUsed);
 307             }
 308         }
 309
 310         private static bool TryGetSqlTypesAssembly(out SqlTypesAssembly sqlTypesAssembly)
 311         {
 312             sqlTypesAssembly = SqlTypesAssembly.Latest;
 313             return sqlTypesAssembly != null;
 314         }
 315
 316         /// <summary>
 317         /// Creates a SqlParameter given a name, type, and direction
 318         /// </summary>
 319         internal static SqlParameter CreateSqlParameter(string name, TypeUsage type, ParameterMode mode, object value, bool preventTruncation, SqlVersion version) {
 320             int? size;
 321             byte? precision;
 322             byte? scale;
 323             string udtTypeName;
 324
 325             value = EnsureSqlParameterValue(value);
 326
 327             SqlParameter result = new SqlParameter(name, value);
 328
 329             // .Direction
 330             ParameterDirection direction = MetadataHelper.ParameterModeToParameterDirection(mode);
 331             if (result.Direction != direction) {
 332                 result.Direction = direction;
 333             }
 334
 335             // .Size, .Precision, .Scale and .SqlDbType
 336             // output parameters are handled differently (we need to ensure there is space for return
 337             // values where the user has not given a specific Size/MaxLength)
 338             bool isOutParam = mode != ParameterMode.In;
 339             SqlDbType sqlDbType = GetSqlDbType(type, isOutParam, version, out size, out precision, out scale, out udtTypeName);
 340
 341             if (result.SqlDbType != sqlDbType) {
 342                 result.SqlDbType = sqlDbType;
 343             }
 344
 345             if (sqlDbType == SqlDbType.Udt)
 346             {
 347                 result.UdtTypeName = udtTypeName;
 348             }
 349
 350             // Note that we overwrite 'facet' parameters where either the value is different or
 351             // there is an output parameter. This is because output parameters in SqlClient have their
 352             // facets clobbered if they are implicitly set (e.g. if the Size was implicitly set
 353             // by setting the value)
 354             if (size.HasValue)
 355             {
 356                 // size.HasValue is always true for Output parameters
 357                 if ((isOutParam || result.Size != size.Value))
 358                 {
 359                     if (preventTruncation && size.Value != -1)
 360                     {
 361                         // To prevent truncation, set the Size of the parameter to the larger of either
 362                         // the declared length or the actual length for the parameter. This allows SQL
 363                         // Server to complain if a value is too long while preventing cache misses for
 364                         // values within the range.
 365                         result.Size = Math.Max(result.Size, size.Value);
 366                     }
 367                     else
 368                     {
 369                         result.Size = size.Value;
 370                     }
 371                 }
 372             }
 373             else
 374             {
 375                 PrimitiveTypeKind typeKind = MetadataHelper.GetPrimitiveTypeKind(type);
 376                 if (typeKind == PrimitiveTypeKind.String)
 377                 {
 378                     result.Size = GetDefaultStringMaxLength(version, sqlDbType);
 379                 }
 380                 else if(typeKind == PrimitiveTypeKind.Binary)
 381                 {
 382                     result.Size = GetDefaultBinaryMaxLength(version);
 383                 }
 384             }
 385             if (precision.HasValue && (isOutParam || result.Precision != precision.Value)) {
 386                 result.Precision = precision.Value;
 387             }
 388             if (scale.HasValue && (isOutParam || result.Scale != scale.Value)) {
 389                 result.Scale = scale.Value;
 390             }
 391
 392             // .IsNullable
 393             bool isNullable = TypeSemantics.IsNullable(type);
 394             if (isOutParam || isNullable != result.IsNullable) {
 395                 result.IsNullable = isNullable;
 396             }
 397
 398             return result;
 399         }
 400
 401         /// <summary>
 402         /// Validates that the specified value is compatible with SqlParameter and if not, attempts to return an appropriate value that is.
 403         /// Currently only spatial values (DbGeography/DbGeometry) may not be directly usable with SqlParameter. For these types, an instance
 404         /// of the corresponding SQL Server CLR spatial UDT will be manufactured based on the spatial data contained in <paramref name="value"/>.
 405         /// If <paramref name="value"/> is an instance of DbGeography/DbGeometry that was read from SQL Server by this provider, then the wrapped
 406         /// CLR UDT value is available via the ProviderValue property (see SqlSpatialServices for the full conversion process from instances of
 407         /// DbGeography/DbGeometry to instances of the CLR SqlGeography/SqlGeometry UDTs)
 408         /// </summary>
 409         internal static object EnsureSqlParameterValue(object value)
 410         {
 411             if (value != null &&
 412                 value != DBNull.Value &&
 413                 Type.GetTypeCode(value.GetType()) == TypeCode.Object)
 414             {
 415                 // If the parameter is being created based on an actual value (typically for constants found in DML expressions) then a DbGeography/DbGeometry
 416                 // value must be replaced by an an appropriate Microsoft.SqlServer.Types.SqlGeography/SqlGeometry instance. Since the DbGeography/DbGeometry
 417                 // value may not have been originally created by this SqlClient provider services implementation, just using the ProviderValue is not sufficient.
 418                 DbGeography geographyValue = value as DbGeography;
 419                 if (geographyValue != null)
 420                 {
 421                     value = GetSqlTypesAssembly().ConvertToSqlTypesGeography(geographyValue);
 422                 }
 423                 else
 424                 {
 425                     DbGeometry geometryValue = value as DbGeometry;
 426                     if (geometryValue != null)
 427                     {
 428                         value = GetSqlTypesAssembly().ConvertToSqlTypesGeometry(geometryValue);
 429                     }
 430                 }
 431             }
 432
 433             return value;
 434         }
 435
 436         /// <summary>
 437         /// Determines SqlDbType for the given primitive type. Extracts facet
 438         /// information as well.
 439         /// </summary>
 440         private static SqlDbType GetSqlDbType(TypeUsage type, bool isOutParam, SqlVersion version, out int? size, out byte? precision, out byte? scale, out string udtName) {
 441             // only supported for primitive type
 442             PrimitiveTypeKind primitiveTypeKind = MetadataHelper.GetPrimitiveTypeKind(type);
 443
 444             size = default(int?);
 445             precision = default(byte?);
 446             scale = default(byte?);
 447             udtName = default(string);
 448
 449             //
 450             switch (primitiveTypeKind) {
 451                 case PrimitiveTypeKind.Binary:
 452                     // for output parameters, ensure there is space...
 453                     size = GetParameterSize(type, isOutParam);
 454                     return GetBinaryDbType(type);
 455
 456                 case PrimitiveTypeKind.Boolean:
 457                     return SqlDbType.Bit;
 458
 459                 case PrimitiveTypeKind.Byte:
 460                     return SqlDbType.TinyInt;
 461
 462                 case PrimitiveTypeKind.Time:
 463                     if (!SqlVersionUtils.IsPreKatmai(version)) {
 464                         precision = GetKatmaiDateTimePrecision(type, isOutParam);
 465                     }
 466                     return SqlDbType.Time;
 467
 468                 case PrimitiveTypeKind.DateTimeOffset:
 469                     if (!SqlVersionUtils.IsPreKatmai(version)) {
 470                         precision = GetKatmaiDateTimePrecision(type, isOutParam);
 471                     }
 472                     return SqlDbType.DateTimeOffset;
 473
 474                 case PrimitiveTypeKind.DateTime:
 475                     //For katmai pick the type with max precision which is datetime2
 476                     if (!SqlVersionUtils.IsPreKatmai(version)) {
 477                         precision = GetKatmaiDateTimePrecision(type, isOutParam);
 478                         return SqlDbType.DateTime2;
 479                     }
 480                     else {
 481                         return SqlDbType.DateTime;
 482                     }
 483
 484                 case PrimitiveTypeKind.Decimal:
 485                     precision = GetParameterPrecision(type, null);
 486                     scale = GetScale(type);
 487                     return SqlDbType.Decimal;
 488
 489                 case PrimitiveTypeKind.Double:
 490                     return SqlDbType.Float;
 491
 492                 case PrimitiveTypeKind.Geography:
 493                     {
 494                         udtName = "geography";
 495                         return SqlDbType.Udt;
 496                     }
 497
 498                 case PrimitiveTypeKind.Geometry:
 499                     {
 500                         udtName = "geometry";
 501                         return SqlDbType.Udt;
 502                     }
 503
 504                 case PrimitiveTypeKind.Guid:
 505                     return SqlDbType.UniqueIdentifier;
 506
 507                 case PrimitiveTypeKind.Int16:
 508                     return SqlDbType.SmallInt;
 509
 510                 case PrimitiveTypeKind.Int32:
 511                     return SqlDbType.Int;
 512
 513                 case PrimitiveTypeKind.Int64:
 514                     return SqlDbType.BigInt;
 515
 516                 case PrimitiveTypeKind.SByte:
 517                     return SqlDbType.SmallInt;
 518
 519                 case PrimitiveTypeKind.Single:
 520                     return SqlDbType.Real;
 521
 522                 case PrimitiveTypeKind.String:
 523                     size = GetParameterSize(type, isOutParam);
 524                     return GetStringDbType(type);
 525
 526                 default:
 527                     Debug.Fail("unknown PrimitiveTypeKind " + primitiveTypeKind);
 528                     return SqlDbType.Variant;
 529             }
 530         }
 531
 532         /// <summary>
 533         /// Determines preferred value for SqlParameter.Size. Returns null
 534         /// where there is no preference.
 535         /// </summary>
 536         private static int? GetParameterSize(TypeUsage type, bool isOutParam) {
 537             Facet maxLengthFacet;
 538             if (type.Facets.TryGetValue(DbProviderManifest.MaxLengthFacetName, false, out maxLengthFacet) &&
 539                 null != maxLengthFacet.Value) {
 540                 if (maxLengthFacet.IsUnbounded) {
 541                     return -1;
 542                 }
 543                 else {
 544                     return (int?)maxLengthFacet.Value;
 545                 }
 546             }
 547             else if (isOutParam) {
 548                 // if the parameter is a return/out/inout parameter, ensure there
 549                 // is space for any value
 550                 return -1;
 551             }
 552             else {
 553                 // no value
 554                 return default(int?);
 555             }
 556         }
 557
 558         private static int GetNonMaxLength(SqlDbType type)
 559         {
 560             int result = -1;
 561             if (type == SqlDbType.NChar || type == SqlDbType.NVarChar)
 562             {
 563                 result = 4000;
 564             }
 565             else if(type == SqlDbType.Char || type == SqlDbType.VarChar ||
 566                     type == SqlDbType.Binary || type == SqlDbType.VarBinary)
 567             {
 568                 result = 8000;
 569             }
 570             return result;
 571         }
 572
 573         private static int GetDefaultStringMaxLength(SqlVersion version, SqlDbType type)
 574         {
 575             int result;
 576             if (version < SqlVersion.Sql9)
 577             {
 578                 if (type == SqlDbType.NChar || type == SqlDbType.NVarChar)
 579                 {
 580                     result = 4000;
 581                 }
 582                 else
 583                 {
 584                     result = 8000;
 585                 }
 586             }
 587             else
 588             {
 589                 result = -1;
 590             }
 591             return result;
 592         }
 593
 594         private static int GetDefaultBinaryMaxLength(SqlVersion version)
 595         {
 596             int result;
 597             if (version < SqlVersion.Sql9)
 598             {
 599                 result = 8000;
 600             }
 601             else
 602             {
 603                 result = -1;
 604             }
 605             return result;
 606         }
 607
 608         /// <summary>
 609         /// Returns SqlParameter.Precision where the type facet exists. Otherwise,
 610         /// returns null or the maximum available precision to avoid truncation (which can occur
 611         /// for output parameters).
 612         /// </summary>
 613         private static byte? GetKatmaiDateTimePrecision(TypeUsage type, bool isOutParam) {
 614             byte? defaultIfUndefined = isOutParam ? (byte?)7 : (byte?)null;
 615             return GetParameterPrecision(type, defaultIfUndefined);
 616         }
 617
 618         /// <summary>
 619         /// Returns SqlParameter.Precision where the type facet exists. Otherwise,
 620         /// returns null.
 621         /// </summary>
 622         private static byte? GetParameterPrecision(TypeUsage type, byte? defaultIfUndefined) {
 623             byte precision;
 624             if (TypeHelpers.TryGetPrecision(type, out precision)) {
 625                 return precision;
 626             }
 627             else {
 628                 return defaultIfUndefined;
 629             }
 630         }
 631
 632         /// <summary>
 633         /// Returns SqlParameter.Scale where the type facet exists. Otherwise,
 634         /// returns null.
 635         /// </summary>
 636         private static byte? GetScale(TypeUsage type) {
 637             byte scale;
 638             if (TypeHelpers.TryGetScale(type, out scale)) {
 639                 return scale;
 640             }
 641             else {
 642                 return default(byte?);
 643             }
 644         }
 645
 646         /// <summary>
 647         /// Chooses the appropriate SqlDbType for the given string type.
 648         /// </summary>
 649         [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1308:NormalizeStringsToUppercase")]
 650         private static SqlDbType GetStringDbType(TypeUsage type) {
 651             Debug.Assert(type.EdmType.BuiltInTypeKind == BuiltInTypeKind.PrimitiveType &&
 652                 PrimitiveTypeKind.String == ((PrimitiveType)type.EdmType).PrimitiveTypeKind, "only valid for string type");
 653
 654             SqlDbType dbType;
 655             if (type.EdmType.Name.ToLowerInvariant() == "xml") {
 656                 dbType = SqlDbType.Xml;
 657             }
 658             else {
 659                 // Specific type depends on whether the string is a unicode string and whether it is a fixed length string.
 660                 // By default, assume widest type (unicode) and most common type (variable length)
 661                 bool unicode;
 662                 bool fixedLength;
 663                 if (!TypeHelpers.TryGetIsFixedLength(type, out fixedLength)) {
 664                     fixedLength = false;
 665                 }
 666
 667                 if (!TypeHelpers.TryGetIsUnicode(type, out unicode)) {
 668                     unicode = true;
 669                 }
 670
 671                 if (fixedLength) {
 672                     dbType = (unicode ? SqlDbType.NChar : SqlDbType.Char);
 673                 }
 674                 else {
 675                     dbType = (unicode ? SqlDbType.NVarChar : SqlDbType.VarChar);
 676                 }
 677             }
 678             return dbType;
 679         }
 680
 681         /// <summary>
 682         /// Chooses the appropriate SqlDbType for the given binary type.
 683         /// </summary>
 684         private static SqlDbType GetBinaryDbType(TypeUsage type) {
 685             Debug.Assert(type.EdmType.BuiltInTypeKind == BuiltInTypeKind.PrimitiveType &&
 686                 PrimitiveTypeKind.Binary == ((PrimitiveType)type.EdmType).PrimitiveTypeKind, "only valid for binary type");
 687
 688             // Specific type depends on whether the binary value is fixed length. By default, assume variable length.
 689             bool fixedLength;
 690             if (!TypeHelpers.TryGetIsFixedLength(type, out fixedLength)) {
 691                 fixedLength = false;
 692             }
 693
 694             return fixedLength ? SqlDbType.Binary : SqlDbType.VarBinary;
 695         }
 696
 697         protected override string DbCreateDatabaseScript(string providerManifestToken, StoreItemCollection storeItemCollection)
 698         {
 699             EntityUtil.CheckArgumentNull(providerManifestToken, "providerManifestToken");
 700             EntityUtil.CheckArgumentNull(storeItemCollection, "storeItemCollection");
 701             SqlVersion version = SqlVersionUtils.GetSqlVersion(providerManifestToken);
 702             return CreateObjectsScript(version, storeItemCollection);
 703         }
 704
 705         /// <summary>
 706         /// Create the database and the database objects.
 707         /// If initial catalog is not specified, but AttachDBFilename is specified, we generate a random database name based on the AttachDBFilename.
 708         /// Note: this causes pollution of the db, as when the connection string is later used, the mdf will get attached under a different name.
 709         /// However if we try to replicate the name under which it would be attached, the following scenario would fail:
 710         ///    The file does not exist, but registered with database.
 711         ///    The user calls:  If (DatabaseExists) DeleteDatabase
 712         ///                     CreateDatabase
 713         /// For further details on the behavior when AttachDBFilename is specified see Dev10# 188936
 714         /// </summary>
 715         protected override void DbCreateDatabase(DbConnection connection, int? commandTimeout, StoreItemCollection storeItemCollection)
 716         {
 717             EntityUtil.CheckArgumentNull(connection, "connection");
 718             EntityUtil.CheckArgumentNull(storeItemCollection, "storeItemCollection");
 719
 720             SqlConnection sqlConnection = SqlProviderUtilities.GetRequiredSqlConnection(connection);
 721             string databaseName, dataFileName, logFileName;
 722             GetOrGenerateDatabaseNameAndGetFileNames(sqlConnection, out databaseName, out dataFileName, out logFileName);
 723             string createDatabaseScript = SqlDdlBuilder.CreateDatabaseScript(databaseName, dataFileName, logFileName);
 724             SqlVersion sqlVersion = GetSqlVersion(storeItemCollection);
 725
 726             string createObjectsScript = CreateObjectsScript(sqlVersion, storeItemCollection);
 727
 728             UsingMasterConnection(sqlConnection, conn =>
 729             {
 730                 // create database
 731                 CreateCommand(conn, createDatabaseScript, commandTimeout).ExecuteNonQuery();
 732             });
 733
 734             // Create database already succeeded. If there is a failure from this point on, the user should be informed.
 735             try
 736             {
 737                 // Clear connection pool for the database connection since after the 'create database' call, a previously
 738                 // invalid connection may now be valid.
 739                 SqlConnection.ClearPool(sqlConnection);
 740
 741                 UsingConnection(sqlConnection, conn =>
 742                 {
 743                     // create database objects
 744                     CreateCommand(conn, createObjectsScript, commandTimeout).ExecuteNonQuery();
 745                 });
 746             }
 747             catch (Exception e)
 748             {
 749                 if (EntityUtil.IsCatchableExceptionType(e))
 750                 {
 751                     // Try to drop the database
 752                     try
 753                     {
 754                         DropDatabase(sqlConnection, commandTimeout, databaseName);
 755                     }
 756                     catch (Exception ie)
 757                     {
 758                         // The creation of the database succeeded, the creation of the database objects failed, and the dropping of the database failed.
 759                         if (EntityUtil.IsCatchableExceptionType(ie))
 760                         {
 761                             throw new InvalidOperationException(Strings.SqlProvider_IncompleteCreateDatabase, new AggregateException(Strings.SqlProvider_IncompleteCreateDatabaseAggregate, e, ie));
 762                         }
 763                         throw;
 764                     }
 765                     // The creation of the database succeeded, the creation of the database objects failed, the database was dropped, no reason to wrap the exception
 766                     throw;
 767                 }
 768                 throw;
 769             }
 770         }
 771
 772         private static SqlVersion GetSqlVersion(StoreItemCollection storeItemCollection)
 773         {
 774             SqlProviderManifest sqlManifest = (storeItemCollection.StoreProviderManifest as SqlProviderManifest);
 775             if (sqlManifest == null)
 776             {
 777                 throw EntityUtil.Argument(System.Data.Entity.Strings.Mapping_Provider_WrongManifestType(typeof(SqlProviderManifest)));
 778             }
 779             SqlVersion sqlVersion = sqlManifest.SqlVersion;
 780             return sqlVersion;
 781         }
 782
 783         private static void GetOrGenerateDatabaseNameAndGetFileNames(SqlConnection sqlConnection, out string databaseName, out string dataFileName, out string logFileName)
 784         {
 785             Debug.Assert(sqlConnection != null);
 786
 787             var connectionStringBuilder = new SqlConnectionStringBuilder(sqlConnection.ConnectionString);
 788
 789             // Get the file names
 790             string attachDBFile = connectionStringBuilder.AttachDBFilename;
 791             if (string.IsNullOrEmpty(attachDBFile))
 792             {
 793                 dataFileName = null;
 794                 logFileName = null;
 795             }
 796             else
 797             {
 798                 //Handle the other cases
 799                 dataFileName = GetMdfFileName(attachDBFile);
 800                 logFileName = GetLdfFileName(dataFileName);
 801             }
 802
 803             // Get the database name
 804             if (!string.IsNullOrEmpty(connectionStringBuilder.InitialCatalog))
 805             {
 806                 databaseName = connectionStringBuilder.InitialCatalog;
 807             }
 808             else if (dataFileName != null)
 809             {
 810                 //generate the database name here
 811                 databaseName = GenerateDatabaseName(dataFileName);
 812             }
 813             else
 814             {
 815                 throw EntityUtil.InvalidOperation(Strings.SqlProvider_DdlGeneration_MissingInitialCatalog);
 816             }
 817         }
 818
 819         /// <summary>
 820         /// Get the Ldf name given the Mdf full name
 821         /// </summary>
 822         private static string GetLdfFileName(string dataFileName)
 823         {
 824             string logFileName;
 825             var directory = new FileInfo(dataFileName).Directory;
 826             logFileName = Path.Combine(directory.FullName, String.Concat(Path.GetFileNameWithoutExtension(dataFileName), "_log.ldf"));
 827             return logFileName;
 828         }
 829
 830         /// <summary>
 831         /// Generates database name based on the given mdfFileName.
 832         /// The logic is replicated from System.Web.DataAccess.SqlConnectionHelper
 833         /// </summary>
 834         private static string GenerateDatabaseName(string mdfFileName)
 835         {
 836             string toUpperFileName = mdfFileName.ToUpper(CultureInfo.InvariantCulture);
 837             char [] strippedFileNameChars = Path.GetFileNameWithoutExtension(toUpperFileName).ToCharArray();
 838
 839             for (int iter = 0; iter < strippedFileNameChars.Length; iter++)
 840             {
 841                 if (!char.IsLetterOrDigit(strippedFileNameChars[iter]))
 842                 {
 843                     strippedFileNameChars[iter] = '_';
 844                 }
 845             }
 846
 847             string strippedFileName = new string(strippedFileNameChars);
 848             strippedFileName = strippedFileName.Length > 30 ? strippedFileName.Substring(0, 30) : strippedFileName;
 849
 850             string databaseName =  databaseName = String.Format(CultureInfo.InvariantCulture, "{0}_{1}", strippedFileName, Guid.NewGuid().ToString("N", CultureInfo.InvariantCulture));
 851             return databaseName;
 852         }
 853
 854         /// <summary>
 855         /// Get the full mdf file name given the attachDBFile value from the connection string
 856         /// </summary>
 857         /// <param name="attachDBFile"></param>
 858         /// <returns></returns>
 859         private static string GetMdfFileName(string attachDBFile)
 860         {
 861             Debug.Assert(!string.IsNullOrEmpty(attachDBFile));
 862
 863             //Handle the case when attachDBFilename starts with |DataDirectory|
 864             string dataFileName = System.Data.EntityClient.DbConnectionOptions.ExpandDataDirectory("AttachDBFilename", attachDBFile);
 865
 866             //Handle the other cases
 867             dataFileName = dataFileName ?? attachDBFile;
 868             return dataFileName;
 869         }
 870
 871         /// <summary>
 872         /// Determines whether the database for the given connection exists.
 873         /// There are three cases:
 874         /// 1.  Initial Catalog = X, AttachDBFilename = null:   (SELECT Count(*) FROM sys.databases WHERE [name]= X) > 0
 875         /// 2.  Initial Catalog = X, AttachDBFilename = F:      if (SELECT Count(*) FROM sys.databases WHERE [name]= X) >  true,
 876         /// if not, try to open the connection and then return (SELECT Count(*) FROM sys.databases WHERE [name]= X) > 0
 877         /// 3.  Initial Catalog = null, AttachDBFilename = F:   Try to open the connection. If that succeeds the result is true, otherwise
 878         /// if the there are no databases corresponding to the given file return false, otherwise throw.
 879         ///
 880         /// Note: We open the connection to cover the scenario when the mdf exists, but is not attached.
 881         /// Given that opening the connection would auto-attach it, it would not be appropriate to return false in this case.
 882         /// Also note that checking for the existence of the file does not work for a remote server.  (Dev11 #290487)
 883         /// For further details on the behavior when AttachDBFilename is specified see Dev10# 188936
 884         /// </summary>
 885         protected override bool DbDatabaseExists(DbConnection connection, int? commandTimeout, StoreItemCollection storeItemCollection)
 886         {
 887             EntityUtil.CheckArgumentNull(connection, "connection");
 888             EntityUtil.CheckArgumentNull(storeItemCollection, "storeItemCollection");
 889
 890             SqlConnection sqlConnection = SqlProviderUtilities.GetRequiredSqlConnection(connection);
 891             var connectionBuilder = new SqlConnectionStringBuilder(sqlConnection.ConnectionString);
 892
 893             if (string.IsNullOrEmpty(connectionBuilder.InitialCatalog) && string.IsNullOrEmpty(connectionBuilder.AttachDBFilename))
 894             {
 895                 throw EntityUtil.InvalidOperation(Strings.SqlProvider_DdlGeneration_MissingInitialCatalog);
 896             }
 897
 898             if (!string.IsNullOrEmpty(connectionBuilder.InitialCatalog))
 899             {
 900                 if (CheckDatabaseExists(sqlConnection, commandTimeout, connectionBuilder.InitialCatalog))
 901                 {
 902                     //Avoid further processing
 903                     return true;
 904                 }
 905             }
 906
 907             if (!string.IsNullOrEmpty(connectionBuilder.AttachDBFilename))
 908             {
 909                 try
 910                 {
 911                     UsingConnection(sqlConnection, (SqlConnection con) => { });
 912                     return true;
 913                 }
 914                 catch (SqlException e)
 915                 {
 916                     if (!string.IsNullOrEmpty(connectionBuilder.InitialCatalog))
 917                     {
 918                         return CheckDatabaseExists(sqlConnection, commandTimeout, connectionBuilder.InitialCatalog);
 919                     }
 920                     // Initial catalog not specified
 921                     string fileName = GetMdfFileName(connectionBuilder.AttachDBFilename);
 922                     bool databaseDoesNotExistInSysTables = false;
 923                     UsingMasterConnection(sqlConnection, conn =>
 924                     {
 925                         SqlVersion sqlVersion = SqlVersionUtils.GetSqlVersion(conn);
 926                         string databaseExistsScript = SqlDdlBuilder.CreateCountDatabasesBasedOnFileNameScript(fileName, useDeprecatedSystemTable: sqlVersion == SqlVersion.Sql8);
 927                         int result = (int)CreateCommand(conn, databaseExistsScript, commandTimeout).ExecuteScalar();
 928                         databaseDoesNotExistInSysTables = (result == 0);
 929                     });
 930                     if (databaseDoesNotExistInSysTables)
 931                     {
 932                         return false;
 933                     }
 934                     throw EntityUtil.InvalidOperation(Strings.SqlProvider_DdlGeneration_CannotTellIfDatabaseExists, e);
 935                 }
 936             }
 937
 938             // CheckDatabaseExists returned false and no AttachDBFilename is specified
 939             return false;
 940         }
 941
 942         private static bool CheckDatabaseExists(SqlConnection sqlConnection, int? commandTimeout, string databaseName)
 943         {
 944             bool databaseExistsInSysTables = false;
 945             UsingMasterConnection(sqlConnection, conn =>
 946             {
 947                 SqlVersion sqlVersion = SqlVersionUtils.GetSqlVersion(conn);
 948                 string databaseExistsScript = SqlDdlBuilder.CreateDatabaseExistsScript(databaseName, useDeprecatedSystemTable: sqlVersion == SqlVersion.Sql8);
 949                 int result = (int)CreateCommand(conn, databaseExistsScript, commandTimeout).ExecuteScalar();
 950                 databaseExistsInSysTables = (result > 0);
 951             });
 952             return databaseExistsInSysTables;
 953         }
 954
 955         /// <summary>
 956         /// Delete the database for the given connection.
 957         /// There are three cases:
 958         /// 1.  If Initial Catalog is specified (X) drop database X
 959         /// 2.  Else if AttachDBFilename is specified (F) drop all the databases corresponding to F
 960         /// if none throw
 961         /// 3.  If niether the catalog not the file name is specified - throw
 962         ///
 963         /// Note that directly deleting the files does not work for a remote server.  However, even for not attached
 964         /// databases the current logic would work assuming the user does: if (DatabaseExists) DeleteDatabase
 965         /// </summary>
 966         /// <param name="connection"></param>
 967         /// <param name="commandTimeout"></param>
 968         /// <param name="storeItemCollection"></param>
 969         protected override void DbDeleteDatabase(DbConnection connection, int? commandTimeout, StoreItemCollection storeItemCollection)
 970         {
 971             EntityUtil.CheckArgumentNull(connection, "connection");
 972             EntityUtil.CheckArgumentNull(storeItemCollection, "storeItemCollection");
 973             SqlConnection sqlConnection = SqlProviderUtilities.GetRequiredSqlConnection(connection);
 974
 975             var connectionBuilder = new SqlConnectionStringBuilder(sqlConnection.ConnectionString);
 976             string initialCatalog = connectionBuilder.InitialCatalog;
 977             string attachDBFile = connectionBuilder.AttachDBFilename;
 978
 979             if (!string.IsNullOrEmpty(initialCatalog))
 980             {
 981                 DropDatabase(sqlConnection, commandTimeout, initialCatalog);
 982             }
 983
 984             // initial catalog not specified
 985             else if (!string.IsNullOrEmpty(attachDBFile))
 986             {
 987                 string fullFileName = GetMdfFileName(attachDBFile);
 988
 989                 List<string> databaseNames = new List<string>();
 990                 UsingMasterConnection(sqlConnection, conn =>
 991                 {
 992                     SqlVersion sqlVersion = SqlVersionUtils.GetSqlVersion(conn);
 993                     string getDatabaseNamesScript = SqlDdlBuilder.CreateGetDatabaseNamesBasedOnFileNameScript(fullFileName, sqlVersion == SqlVersion.Sql8);
 994                     var command = CreateCommand(conn, getDatabaseNamesScript, commandTimeout);
 995                     using (var reader = command.ExecuteReader())
 996                     {
 997                         while (reader.Read())
 998                         {
 999                             databaseNames.Add(reader.GetString(0));
1000                         }
1001                     }
1002                 });
1003                 if (databaseNames.Count > 0)
1004                 {
1005                     foreach (var databaseName in databaseNames)
1006                     {
1007                         DropDatabase(sqlConnection, commandTimeout, databaseName);
1008                     }
1009                 }
1010                 else
1011                 {
1012                   throw EntityUtil.InvalidOperation(Strings.SqlProvider_DdlGeneration_CannotDeleteDatabaseNoInitialCatalog);
1013                 }
1014             }
1015             // neither initial catalog nor attachDB file name are specified
1016             else
1017             {
1018                 throw EntityUtil.InvalidOperation(Strings.SqlProvider_DdlGeneration_MissingInitialCatalog);
1019             }
1020         }
1021
1022         private static void DropDatabase(SqlConnection sqlConnection, int? commandTimeout, string databaseName)
1023         {
1024             // clear the connection pool in case someone's holding on to the database still
1025             SqlConnection.ClearPool(sqlConnection);
1026
1027             string dropDatabaseScript = SqlDdlBuilder.DropDatabaseScript(databaseName);
1028             UsingMasterConnection(sqlConnection, (conn) =>
1029             {
1030                 CreateCommand(conn, dropDatabaseScript, commandTimeout).ExecuteNonQuery();
1031             });
1032         }
1033
1034         private static string CreateObjectsScript(SqlVersion version, StoreItemCollection storeItemCollection)
1035         {
1036             return SqlDdlBuilder.CreateObjectsScript(storeItemCollection, createSchemas: version != SqlVersion.Sql8);
1037         }
1038
1039         private static SqlCommand CreateCommand(SqlConnection sqlConnection, string commandText, int? commandTimeout)
1040         {
1041             Debug.Assert(sqlConnection != null);
1042             if (string.IsNullOrEmpty(commandText))
1043             {
1044                 // SqlCommand will complain if the command text is empty
1045                 commandText = Environment.NewLine;
1046             }
1047             var command = new SqlCommand(commandText, sqlConnection);
1048             if (commandTimeout.HasValue)
1049             {
1050                 command.CommandTimeout = commandTimeout.Value;
1051             }
1052             return command;
1053         }
1054
1055         private static void UsingConnection(SqlConnection sqlConnection, Action<SqlConnection> act)
1056         {
1057             // remember the connection string so that we can reset it credentials are wiped
1058             string holdConnectionString = sqlConnection.ConnectionString;
1059             bool openingConnection = sqlConnection.State == ConnectionState.Closed;
1060             if (openingConnection)
1061             {
1062                 sqlConnection.Open();
1063             }
1064             try
1065             {
1066                 act(sqlConnection);
1067             }
1068             finally
1069             {
1070                 if (openingConnection && sqlConnection.State == ConnectionState.Open)
1071                 {
1072                     // if we opened the connection, we should close it
1073                     sqlConnection.Close();
1074                 }
1075                 if (sqlConnection.ConnectionString != holdConnectionString)
1076                 {
1077                     sqlConnection.ConnectionString = holdConnectionString;
1078                 }
1079             }
1080         }
1081
1082         private static void UsingMasterConnection(SqlConnection sqlConnection, Action<SqlConnection> act)
1083         {
1084             var connectionBuilder = new SqlConnectionStringBuilder(sqlConnection.ConnectionString)
1085             {
1086                 InitialCatalog = "master",
1087                 AttachDBFilename = string.Empty, // any AttachDB path specified is not relevant to master
1088             };
1089
1090             try
1091             {
1092                 using (var masterConnection = new SqlConnection(connectionBuilder.ConnectionString))
1093                 {
1094                     UsingConnection(masterConnection, act);
1095                 }
1096             }
1097             catch (SqlException e)
1098             {
1099                 // if it appears that the credentials have been removed from the connection string, use an alternate explanation
1100                 if (!connectionBuilder.IntegratedSecurity &&
1101                     (string.IsNullOrEmpty(connectionBuilder.UserID) || string.IsNullOrEmpty(connectionBuilder.Password)))
1102                 {
1103                     throw new InvalidOperationException(Strings.SqlProvider_CredentialsMissingForMasterConnection, e);
1104                 }
1105                 throw;
1106             }
1107         }
1108
1109     }
1110 }