mcs/class/System.Web/System.Web.Profile/SqlProfileProvider.cs

   1 //
   2 // System.Web.UI.WebControls.SqlProfileProvider.cs
   3 //
   4 // Authors:
   5 //      Chris Toshok (toshok@ximian.com)
   6 //  Vladimir Krasnov (vladimirk@mainsoft.com)
   7 //
   8 // (C) 2006 Novell, Inc (http://www.novell.com)
   9 //
  10 // Permission is hereby granted, free of charge, to any person obtaining
  11 // a copy of this software and associated documentation files (the
  12 // "Software"), to deal in the Software without restriction, including
  13 // without limitation the rights to use, copy, modify, merge, publish,
  14 // distribute, sublicense, and/or sell copies of the Software, and to
  15 // permit persons to whom the Software is furnished to do so, subject to
  16 // the following conditions:
  17 //
  18 // The above copyright notice and this permission notice shall be
  19 // included in all copies or substantial portions of the Software.
  20 //
  21 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  22 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  23 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  24 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  25 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  26 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  27 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  28 //
  29
  30 #if NET_2_0
  31 using System;
  32 using System.Data;
  33 using System.Data.Common;
  34 using System.Collections;
  35 using System.Globalization;
  36 using System.Configuration;
  37 using System.Configuration.Provider;
  38 using System.Web.Configuration;
  39 using System.Collections.Specialized;
  40 using System.IO;
  41 using System.Text;
  42
  43 namespace System.Web.Profile
  44 {
  45         public class SqlProfileProvider : ProfileProvider
  46         {
  47                 ConnectionStringSettings connectionString;
  48                 DbProviderFactory factory;
  49
  50                 string applicationName;
  51
  52                 public override int DeleteInactiveProfiles (ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate)
  53                 {
  54                         using (DbConnection connection = CreateConnection ()) {
  55                                 DbCommand command = factory.CreateCommand ();
  56                                 command.Connection = connection;
  57                                 command.CommandType = CommandType.StoredProcedure;
  58                                 command.CommandText = @"aspnet_Profile_DeleteInactiveProfiles";
  59
  60                                 AddParameter (command, "ApplicationName", ApplicationName);
  61                                 AddParameter (command, "ProfileAuthOptions", authenticationOption);
  62                                 AddParameter (command, "InactiveSinceDate", userInactiveSinceDate);
  63                                 DbParameter returnValue = AddParameter (command, null, ParameterDirection.ReturnValue, null);
  64
  65                                 command.ExecuteNonQuery ();
  66                                 int retVal = GetReturnValue (returnValue);
  67                                 return retVal;
  68                         }
  69                 }
  70
  71                 public override int DeleteProfiles (ProfileInfoCollection profiles)
  72                 {
  73                         if (profiles == null)
  74                                 throw new ArgumentNullException ("prfoles");
  75                         if (profiles.Count == 0)
  76                                 throw new ArgumentException ("prfoles");
  77
  78                         string [] usernames = new string [profiles.Count];
  79
  80                         int i = 0;
  81                         foreach (ProfileInfo pi in profiles) {
  82                                 if (pi.UserName == null)
  83                                         throw new ArgumentNullException ("element in profiles collection is null");
  84
  85                                 if (pi.UserName.Length == 0 || pi.UserName.Length > 256 || pi.UserName.IndexOf (",") != -1)
  86                                         throw new ArgumentException ("element in profiles collection in illegal format");
  87
  88                                 usernames [i++] = pi.UserName;
  89                         }
  90
  91                         return DeleteProfilesInternal (usernames);
  92                 }
  93
  94                 public override int DeleteProfiles (string [] usernames)
  95                 {
  96                         if (usernames == null)
  97                                 throw new ArgumentNullException ("usernames");
  98
  99                         Hashtable users = new Hashtable ();
 100                         foreach (string username in usernames) {
 101                                 if (username == null)
 102                                         throw new ArgumentNullException ("element in usernames array is null");
 103
 104                                 if (username.Length == 0 || username.Length > 256 || username.IndexOf (",") != -1)
 105                                         throw new ArgumentException ("element in usernames array in illegal format");
 106
 107                                 if (users.ContainsKey(username))
 108                                         throw new ArgumentException ("duplicate element in usernames array");
 109
 110                                 users.Add (username, username);
 111                         }
 112
 113                         return DeleteProfilesInternal (usernames);
 114                 }
 115
 116                 private int DeleteProfilesInternal (string [] usernames)
 117                 {
 118                         using (DbConnection connection = CreateConnection ()) {
 119                                 DbCommand command = factory.CreateCommand ();
 120                                 command.Connection = connection;
 121                                 command.CommandType = CommandType.StoredProcedure;
 122                                 command.CommandText = @"aspnet_Profile_DeleteProfiles";
 123
 124                                 AddParameter (command, "ApplicationName", ApplicationName);
 125                                 AddParameter (command, "UserNames", string.Join (",", usernames));
 126                                 DbParameter returnValue = AddParameter (command, null, ParameterDirection.ReturnValue, null);
 127
 128                                 command.ExecuteNonQuery ();
 129                                 int retVal = GetReturnValue (returnValue);
 130                                 return retVal;
 131                         }
 132                 }
 133
 134                 public override ProfileInfoCollection FindInactiveProfilesByUserName (ProfileAuthenticationOption authenticationOption,
 135                                                                                       string usernameToMatch,
 136                                                                                       DateTime userInactiveSinceDate,
 137                                                                                       int pageIndex,
 138                                                                                       int pageSize,
 139                                                                                       out int totalRecords)
 140                 {
 141                         CheckParam ("usernameToMatch", usernameToMatch, 256);
 142                         if (pageIndex < 0)
 143                                 throw new ArgumentException("pageIndex is less than zero");
 144                         if (pageSize < 1)
 145                                 throw new ArgumentException ("pageIndex is less than one");
 146                         if (pageIndex * pageSize + pageSize - 1 > Int32.MaxValue)
 147                                 throw new ArgumentException ("pageIndex and pageSize are too large");
 148
 149                         using (DbConnection connection = CreateConnection ()) {
 150                                 DbCommand command = factory.CreateCommand ();
 151                                 command.Connection = connection;
 152                                 command.CommandType = CommandType.StoredProcedure;
 153                                 command.CommandText = @"aspnet_Profile_GetProfiles";
 154
 155                                 AddParameter (command, "ApplicationName", ApplicationName);
 156                                 AddParameter (command, "ProfileAuthOptions", authenticationOption);
 157                                 AddParameter (command, "PageIndex", pageIndex);
 158                                 AddParameter (command, "PageSize", pageSize);
 159                                 AddParameter (command, "UserNameToMatch", usernameToMatch);
 160                                 AddParameter (command, "InactiveSinceDate", userInactiveSinceDate);
 161
 162                                 using (DbDataReader reader = command.ExecuteReader ()) {
 163                                         return BuildProfileInfoCollection (reader, out totalRecords);
 164                                 }
 165                         }
 166                 }
 167
 168                 public override ProfileInfoCollection FindProfilesByUserName (ProfileAuthenticationOption authenticationOption,
 169                                                                               string usernameToMatch,
 170                                                                               int pageIndex,
 171                                                                               int pageSize,
 172                                                                               out int totalRecords)
 173                 {
 174                         CheckParam ("usernameToMatch", usernameToMatch, 256);
 175                         if (pageIndex < 0)
 176                                 throw new ArgumentException ("pageIndex is less than zero");
 177                         if (pageSize < 1)
 178                                 throw new ArgumentException ("pageIndex is less than one");
 179                         if (pageIndex * pageSize + pageSize - 1 > Int32.MaxValue)
 180                                 throw new ArgumentException ("pageIndex and pageSize are too large");
 181
 182                         using (DbConnection connection = CreateConnection ()) {
 183                                 DbCommand command = factory.CreateCommand ();
 184                                 command.Connection = connection;
 185                                 command.CommandType = CommandType.StoredProcedure;
 186                                 command.CommandText = @"aspnet_Profile_GetProfiles";
 187
 188                                 AddParameter (command, "ApplicationName", ApplicationName);
 189                                 AddParameter (command, "ProfileAuthOptions", authenticationOption);
 190                                 AddParameter (command, "PageIndex", pageIndex);
 191                                 AddParameter (command, "PageSize", pageSize);
 192                                 AddParameter (command, "UserNameToMatch", usernameToMatch);
 193                                 AddParameter (command, "InactiveSinceDate", null);
 194
 195                                 using (DbDataReader reader = command.ExecuteReader ()) {
 196                                         return BuildProfileInfoCollection (reader, out totalRecords);
 197                                 }
 198                         }
 199                 }
 200
 201                 public override ProfileInfoCollection GetAllInactiveProfiles (ProfileAuthenticationOption authenticationOption,
 202                                                                               DateTime userInactiveSinceDate,
 203                                                                               int pageIndex,
 204                                                                               int pageSize,
 205                                                                               out int totalRecords)
 206                 {
 207                         if (pageIndex < 0)
 208                                 throw new ArgumentException ("pageIndex is less than zero");
 209                         if (pageSize < 1)
 210                                 throw new ArgumentException ("pageIndex is less than one");
 211                         if (pageIndex * pageSize + pageSize - 1 > Int32.MaxValue)
 212                                 throw new ArgumentException ("pageIndex and pageSize are too large");
 213
 214                         using (DbConnection connection = CreateConnection ()) {
 215                                 DbCommand command = factory.CreateCommand ();
 216                                 command.Connection = connection;
 217                                 command.CommandType = CommandType.StoredProcedure;
 218                                 command.CommandText = @"aspnet_Profile_GetProfiles";
 219
 220                                 AddParameter (command, "ApplicationName", ApplicationName);
 221                                 AddParameter (command, "ProfileAuthOptions", authenticationOption);
 222                                 AddParameter (command, "PageIndex", pageIndex);
 223                                 AddParameter (command, "PageSize", pageSize);
 224                                 AddParameter (command, "UserNameToMatch", null);
 225                                 AddParameter (command, "InactiveSinceDate", null);
 226
 227                                 using (DbDataReader reader = command.ExecuteReader ()) {
 228                                         return BuildProfileInfoCollection (reader, out totalRecords);
 229                                 }
 230                         }
 231                 }
 232
 233                 public override ProfileInfoCollection GetAllProfiles (ProfileAuthenticationOption authenticationOption,
 234                                                                       int pageIndex,
 235                                                                       int pageSize,
 236                                                                       out int totalRecords)
 237                 {
 238                         if (pageIndex < 0)
 239                                 throw new ArgumentException ("pageIndex is less than zero");
 240                         if (pageSize < 1)
 241                                 throw new ArgumentException ("pageIndex is less than one");
 242                         if (pageIndex * pageSize + pageSize - 1 > Int32.MaxValue)
 243                                 throw new ArgumentException ("pageIndex and pageSize are too large");
 244
 245                         using (DbConnection connection = CreateConnection ()) {
 246                                 DbCommand command = factory.CreateCommand ();
 247                                 command.Connection = connection;
 248                                 command.CommandType = CommandType.StoredProcedure;
 249                                 command.CommandText = @"aspnet_Profile_GetProfiles";
 250
 251                                 AddParameter (command, "ApplicationName", ApplicationName);
 252                                 AddParameter (command, "ProfileAuthOptions", authenticationOption);
 253                                 AddParameter (command, "PageIndex", pageIndex);
 254                                 AddParameter (command, "PageSize", pageSize);
 255                                 AddParameter (command, "UserNameToMatch", null);
 256                                 AddParameter (command, "InactiveSinceDate", null);
 257
 258                                 using (DbDataReader reader = command.ExecuteReader ()) {
 259                                         return BuildProfileInfoCollection (reader, out totalRecords);
 260                                 }
 261                         }
 262                 }
 263
 264                 public override int GetNumberOfInactiveProfiles (ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate)
 265                 {
 266                         using (DbConnection connection = CreateConnection ()) {
 267
 268                                 DbCommand command = factory.CreateCommand ();
 269                                 command.Connection = connection;
 270                                 command.CommandType = CommandType.StoredProcedure;
 271                                 command.CommandText = @"aspnet_Profile_GetNumberOfInactiveProfiles";
 272
 273                                 AddParameter (command, "ApplicationName", ApplicationName);
 274                                 AddParameter (command, "ProfileAuthOptions", authenticationOption);
 275                                 AddParameter (command, "InactiveSinceDate", userInactiveSinceDate);
 276
 277                                 int returnValue = 0;
 278                                 using (DbDataReader reader = command.ExecuteReader ()) {
 279                                         if (reader.Read ())
 280                                                 returnValue = reader.GetInt32 (0);
 281                                 }
 282                                 return returnValue;
 283                         }
 284                 }
 285
 286                 public override SettingsPropertyValueCollection GetPropertyValues (SettingsContext sc, SettingsPropertyCollection properties)
 287                 {
 288                         SettingsPropertyValueCollection settings = new SettingsPropertyValueCollection ();
 289
 290                         if (properties.Count == 0)
 291                                 return settings;
 292
 293                         foreach (SettingsProperty property in properties) {
 294                                 if (property.SerializeAs == SettingsSerializeAs.ProviderSpecific)
 295                                         if (property.PropertyType.IsPrimitive || property.PropertyType == typeof (String))
 296                                                 property.SerializeAs = SettingsSerializeAs.String;
 297                                         else
 298                                                 property.SerializeAs = SettingsSerializeAs.Xml;
 299
 300                                 settings.Add (new SettingsPropertyValue (property));
 301                         }
 302
 303                         string username = (string) sc ["UserName"];
 304                         using (DbConnection connection = CreateConnection ()) {
 305                                 DbCommand command = factory.CreateCommand ();
 306                                 command.Connection = connection;
 307                                 command.CommandType = CommandType.StoredProcedure;
 308                                 command.CommandText = @"aspnet_Profile_GetProperties";
 309
 310                                 AddParameter (command, "ApplicationName", ApplicationName);
 311                                 AddParameter (command, "UserName", username);
 312                                 AddParameter (command, "CurrentTimeUtc", DateTime.UtcNow);
 313
 314                                 using (DbDataReader reader = command.ExecuteReader ()) {
 315                                         if (reader.Read ()) {
 316                                                 string allnames = reader.GetString (0);
 317                                                 string allvalues = reader.GetString (1);
 318                                                 int binaryLen = (int) reader.GetBytes (2, 0, null, 0, 0);
 319                                                 byte [] binaryvalues = new byte [binaryLen];
 320                                                 reader.GetBytes (2, 0, binaryvalues, 0, binaryLen);
 321
 322                                                 DecodeProfileData (allnames, allvalues, binaryvalues, settings);
 323                                         }
 324                                 }
 325                         }
 326
 327                         return settings;
 328                 }
 329
 330                 public override void SetPropertyValues (SettingsContext sc, SettingsPropertyValueCollection properties)
 331                 {
 332                         string username = (string) sc ["UserName"];
 333                         bool isAnonymous = !(bool) sc ["IsAuthenticated"];
 334
 335                         string names = String.Empty;
 336                         string values = String.Empty;
 337                         byte [] buf = null;
 338
 339                         EncodeProfileData (ref names, ref values, ref buf, properties, !isAnonymous);
 340
 341                         using (DbConnection connection = CreateConnection ()) {
 342                                 DbCommand command = factory.CreateCommand ();
 343                                 command.Connection = connection;
 344                                 command.CommandType = CommandType.StoredProcedure;
 345                                 command.CommandText = @"aspnet_Profile_SetProperties";
 346
 347                                 AddParameter (command, "ApplicationName", ApplicationName);
 348                                 AddParameter (command, "PropertyNames", names);
 349                                 AddParameter (command, "PropertyValuesString", values);
 350                                 AddParameter (command, "PropertyValuesBinary", buf);
 351                                 AddParameter (command, "UserName", username);
 352                                 AddParameter (command, "IsUserAnonymous", isAnonymous);
 353                                 AddParameter (command, "CurrentTimeUtc", DateTime.UtcNow);
 354                                 DbParameter returnValue = AddParameter (command, null, ParameterDirection.ReturnValue, null);
 355
 356                                 command.ExecuteNonQuery ();
 357                                 return;
 358                         }
 359                 }
 360
 361                 public override void Initialize (string name, NameValueCollection config)
 362                 {
 363                         if (config == null)
 364                                 throw new ArgumentNullException ("config");
 365
 366                         base.Initialize (name, config);
 367
 368                         applicationName = GetStringConfigValue (config, "applicationName", "/");
 369
 370                         ProfileSection section = (ProfileSection) WebConfigurationManager.GetSection ("system.web/profile");
 371                         string connectionStringName = config ["connectionStringName"];
 372
 373                         if (applicationName.Length > 256)
 374                                 throw new ProviderException ("The ApplicationName attribute must be 256 characters long or less.");
 375                         if (connectionStringName == null || connectionStringName.Length == 0)
 376                                 throw new ProviderException ("The ConnectionStringName attribute must be present and non-zero length.");
 377
 378                         connectionString = WebConfigurationManager.ConnectionStrings [connectionStringName];
 379                         factory = connectionString == null || String.IsNullOrEmpty (connectionString.ProviderName) ?
 380                                 System.Data.SqlClient.SqlClientFactory.Instance :
 381                                 ProvidersHelper.GetDbProviderFactory (connectionString.ProviderName);
 382                 }
 383
 384                 public override string ApplicationName {
 385                         get { return applicationName; }
 386                         set { applicationName = value; }
 387                 }
 388
 389                 DbConnection CreateConnection ()
 390                 {
 391                         DbConnection connection = factory.CreateConnection ();
 392                         connection.ConnectionString = connectionString.ConnectionString;
 393
 394                         connection.Open ();
 395                         return connection;
 396                 }
 397
 398                 DbParameter AddParameter (DbCommand command, string parameterName, object parameterValue)
 399                 {
 400                         return AddParameter (command, parameterName, ParameterDirection.Input, parameterValue);
 401                 }
 402
 403                 DbParameter AddParameter (DbCommand command, string parameterName, ParameterDirection direction, object parameterValue)
 404                 {
 405                         DbParameter dbp = command.CreateParameter ();
 406                         dbp.ParameterName = parameterName;
 407                         dbp.Value = parameterValue;
 408                         dbp.Direction = direction;
 409                         command.Parameters.Add (dbp);
 410                         return dbp;
 411                 }
 412
 413                 void CheckParam (string pName, string p, int length)
 414                 {
 415                         if (p == null)
 416                                 throw new ArgumentNullException (pName);
 417                         if (p.Length == 0 || p.Length > length || p.IndexOf (",") != -1)
 418                                 throw new ArgumentException (String.Format ("invalid format for {0}", pName));
 419                 }
 420
 421                 static int GetReturnValue (DbParameter returnValue)
 422                 {
 423                         object value = returnValue.Value;
 424                         return value is int ? (int) value : -1;
 425                 }
 426
 427                 private ProfileInfo ReadProfileInfo (DbDataReader reader)
 428                 {
 429                         ProfileInfo pi = null;
 430                         try {
 431                                 string username = reader.GetString (0);
 432                                 bool anonymous = reader.GetBoolean (1);
 433                                 DateTime lastUpdate = reader.GetDateTime (2);
 434                                 DateTime lastActivity = reader.GetDateTime (3);
 435                                 int size = reader.GetInt32 (4);
 436
 437                                 pi = new ProfileInfo (username, anonymous, lastActivity, lastUpdate, size);
 438                         }
 439                         catch {
 440                         }
 441
 442                         return pi;
 443                 }
 444
 445                 private ProfileInfoCollection BuildProfileInfoCollection (DbDataReader reader, out int totalRecords)
 446                 {
 447                         ProfileInfoCollection pic = new ProfileInfoCollection ();
 448                         while (reader.Read ()) {
 449                                 ProfileInfo pi = ReadProfileInfo (reader);
 450                                 if (pi != null)
 451                                         pic.Add (pi);
 452                         }
 453                         totalRecords = 0;
 454                         if (reader.NextResult ()) {
 455                                 if (reader.Read ())
 456                                         totalRecords = reader.GetInt32 (0);
 457                         }
 458                         return pic;
 459                 }
 460
 461                 string GetStringConfigValue (NameValueCollection config, string name, string def)
 462                 {
 463                         string retVal = def;
 464                         string val = config [name];
 465                         if (val != null)
 466                                 retVal = val;
 467                         return retVal;
 468                 }
 469
 470                 // Helper methods
 471                 private void DecodeProfileData (string allnames, string values, byte [] buf, SettingsPropertyValueCollection properties)
 472                 {
 473                         if (allnames == null || values == null || buf == null || properties == null)
 474                                 return;
 475
 476                         string [] names = allnames.Split (':');
 477                         for (int i = 0; i < names.Length; i += 4) {
 478                                 string name = names [i];
 479                                 SettingsPropertyValue pp = properties [name];
 480
 481                                 if (pp == null)
 482                                         continue;
 483
 484                                 int pos = Int32.Parse (names [i + 2], CultureInfo.InvariantCulture);
 485                                 int len = Int32.Parse (names [i + 3], CultureInfo.InvariantCulture);
 486
 487                                 if (len == -1 && !pp.Property.PropertyType.IsValueType) {
 488                                         pp.PropertyValue = null;
 489                                         pp.IsDirty = false;
 490                                         pp.Deserialized = true;
 491                                 }
 492                                 else if (names [i + 1] == "S" && pos >= 0 && len > 0 && values.Length >= pos + len) {
 493                                         pp.SerializedValue = values.Substring (pos, len);
 494                                 }
 495                                 else if (names [i + 1] == "B" && pos >= 0 && len > 0 && buf.Length >= pos + len) {
 496                                         byte [] buf2 = new byte [len];
 497                                         Buffer.BlockCopy (buf, pos, buf2, 0, len);
 498                                         pp.SerializedValue = buf2;
 499                                 }
 500                         }
 501                 }
 502
 503                 private void EncodeProfileData (ref string allNames, ref string allValues, ref byte [] buf, SettingsPropertyValueCollection properties, bool userIsAuthenticated)
 504                 {
 505                         StringBuilder names = new StringBuilder ();
 506                         StringBuilder values = new StringBuilder ();
 507                         MemoryStream stream = new MemoryStream ();
 508
 509                         try {
 510                                 foreach (SettingsPropertyValue pp in properties) {
 511                                         if (!userIsAuthenticated && !(bool) pp.Property.Attributes ["AllowAnonymous"])
 512                                                 continue;
 513
 514                                         if (!pp.IsDirty && pp.UsingDefaultValue)
 515                                                 continue;
 516
 517                                         int len = 0, pos = 0;
 518                                         string propValue = null;
 519
 520                                         if (pp.Deserialized && pp.PropertyValue == null)
 521                                                 len = -1;
 522                                         else {
 523                                                 object sVal = pp.SerializedValue;
 524
 525                                                 if (sVal == null)
 526                                                         len = -1;
 527                                                 else if (sVal is string) {
 528                                                         propValue = (string) sVal;
 529                                                         len = propValue.Length;
 530                                                         pos = values.Length;
 531                                                 }
 532                                                 else {
 533                                                         byte [] b2 = (byte []) sVal;
 534                                                         pos = (int) stream.Position;
 535                                                         stream.Write (b2, 0, b2.Length);
 536                                                         stream.Position = pos + b2.Length;
 537                                                         len = b2.Length;
 538                                                 }
 539                                         }
 540
 541                                         names.Append (pp.Name + ":" + ((propValue != null) ? "S" : "B") + ":" + pos.ToString (CultureInfo.InvariantCulture) + ":" + len.ToString (CultureInfo.InvariantCulture) + ":");
 542
 543                                         if (propValue != null)
 544                                                 values.Append (propValue);
 545                                 }
 546                                 buf = stream.ToArray ();
 547                         }
 548                         finally {
 549                                 if (stream != null)
 550                                         stream.Close ();
 551                         }
 552
 553                         allNames = names.ToString ();
 554                         allValues = values.ToString ();
 555                 }
 556         }
 557 }
 558
 559 #endif