mcs/class/Mainsoft.Web/Mainsoft.Web.Profile/DerbyProfileHelper.cs

   1 //\r
   2 // Mainsoft.Web.Profile.DerbyProfileHelper\r
   3 //\r
   4 // Authors:\r
   5 //      Vladimir Krasnov (vladimirk@mainsoft.com)\r
   6 //\r
   7 // (C) 2006 Mainsoft\r
   8 //\r
   9 // Permission is hereby granted, free of charge, to any person obtaining\r
  10 // a copy of this software and associated documentation files (the\r
  11 // "Software"), to deal in the Software without restriction, including\r
  12 // without limitation the rights to use, copy, modify, merge, publish,\r
  13 // distribute, sublicense, and/or sell copies of the Software, and to\r
  14 // permit persons to whom the Software is furnished to do so, subject to\r
  15 // the following conditions:\r
  16 // \r
  17 // The above copyright notice and this permission notice shall be\r
  18 // included in all copies or substantial portions of the Software.\r
  19 // \r
  20 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,\r
  21 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF\r
  22 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND\r
  23 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE\r
  24 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION\r
  25 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION\r
  26 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.\r
  27 //\r
  28 \r
  29 \r
  30 using System;\r
  31 using System.Web.Security;\r
  32 using System.Web.Configuration;\r
  33 using System.Data;\r
  34 using System.Data.OleDb;\r
  35 using System.Data.Common;\r
  36 using System.Collections.Generic;\r
  37 using System.Text;\r
  38 \r
  39 using Mainsoft.Web.Security;\r
  40 \r
  41 namespace Mainsoft.Web.Profile\r
  42 {\r
  43         class DerbyProfileHelper\r
  44         {\r
  45                 private static OleDbParameter AddParameter (OleDbCommand command, string paramName, object paramValue)\r
  46                 {\r
  47                         OleDbParameter prm = new OleDbParameter (paramName, paramValue);\r
  48                         command.Parameters.Add (prm);\r
  49                         return prm;\r
  50                 }\r
  51 \r
  52                 public static int Profile_DeleteInactiveProfiles(DbConnection connection, string applicationName, int profileAuthOptions, DateTime inactiveSinceDate)\r
  53                 {\r
  54                         string appId = DerbyApplicationsHelper.GetApplicationId (connection, applicationName);\r
  55                         if (appId == null)\r
  56                                 return 0;\r
  57 \r
  58                         string queryDelete = @"DELETE FROM aspnet_Profile WHERE UserId IN ( " +\r
  59                                 "SELECT UserId FROM aspnet_Users WHERE ApplicationId = ? AND LastActivityDate <= ? " + \r
  60                                 GetProfileAuthOptions (profileAuthOptions) + ")";\r
  61                         OleDbCommand cmdDelete = new OleDbCommand (queryDelete, (OleDbConnection) connection);\r
  62                         AddParameter (cmdDelete, "ApplicationId", appId);\r
  63                         AddParameter (cmdDelete, "LastActivityDate", inactiveSinceDate);\r
  64 \r
  65                         return cmdDelete.ExecuteNonQuery ();\r
  66                 }\r
  67 \r
  68                 public static int Profile_DeleteProfiles(DbConnection connection, string applicationName, string [] userNames)\r
  69                 {\r
  70                         int deletedUsers = 0;\r
  71                         string appId = DerbyApplicationsHelper.GetApplicationId (connection, applicationName);\r
  72                         if (appId == null)\r
  73                                 return 0;\r
  74 \r
  75                         OleDbTransaction trans = (OleDbTransaction) connection.BeginTransaction ();\r
  76                         try {\r
  77                                 foreach (string username in userNames) {\r
  78                                         string userId = GetUserId (connection, trans, appId, username);\r
  79                                         if (userId == null)\r
  80                                                 continue;\r
  81 \r
  82                                         string queryDelete = "DELETE FROM aspnet_Profile WHERE UserId = ?";\r
  83                                         OleDbCommand cmdDelete = new OleDbCommand (queryDelete, (OleDbConnection) connection);\r
  84                                         cmdDelete.Transaction = trans;\r
  85                                         AddParameter (cmdDelete, "UserId", userId);\r
  86                                         cmdDelete.Transaction = trans;\r
  87                                         deletedUsers += cmdDelete.ExecuteNonQuery ();\r
  88                                 }\r
  89                                 trans.Commit ();\r
  90                                 return deletedUsers;\r
  91                         }\r
  92                         catch (Exception e) {\r
  93                                 trans.Rollback ();\r
  94                                 throw e;\r
  95                         }\r
  96                 }\r
  97 \r
  98                 public static int Profile_GetNumberOfInactiveProfiles (DbConnection connection, string applicationName, int profileAuthOptions, DateTime inactiveSinceDate)\r
  99                 {\r
 100                         string appId = DerbyApplicationsHelper.GetApplicationId (connection, applicationName);\r
 101                         if (appId == null)\r
 102                                 return 0;\r
 103 \r
 104                         string querySelect = @"SELECT COUNT(*) FROM aspnet_Users usr, aspnet_Profile prf WHERE ApplicationId = ? " +\r
 105                                 "AND usr.UserId = prf.UserId AND LastActivityDate <= ? " + GetProfileAuthOptions(profileAuthOptions);\r
 106                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 107                         AddParameter (cmdSelect, "ApplicationId", appId);\r
 108                         AddParameter (cmdSelect, "LastActivityDate", inactiveSinceDate);\r
 109 \r
 110                         using (OleDbDataReader reader = cmdSelect.ExecuteReader ()) {\r
 111                                 if (reader.Read ())\r
 112                                         return reader.GetInt32 (0);\r
 113                         }\r
 114                         return 0;\r
 115                 }\r
 116 \r
 117                 public static int Profile_GetInactiveProfiles (DbConnection connection, string applicationName, int profileAuthOptions, int pageIndex, int pageSize, string userNameToMatch, DateTime inactiveSinceDate, out DbDataReader reader)\r
 118                 {\r
 119                         reader = null;\r
 120                         string appId = DerbyApplicationsHelper.GetApplicationId (connection, applicationName);\r
 121                         if (appId == null)\r
 122                                 return -1;\r
 123 \r
 124                         string querySelect = @"SELECT usr.UserName, usr.IsAnonymous, usr.LastActivityDate, prf.LastUpdatedDate, " +\r
 125                                 "LENGTH(prf.PropertyNames) + LENGTH(prf.PropertyValuesString) + LENGTH(prf.PropertyValuesBinary)" +\r
 126                                 "FROM aspnet_Users usr, aspnet_Profile prf WHERE usr.ApplicationId = ? AND usr.UserId = prf.UserId " +\r
 127                                 "AND usr.LastActivityDate <= ? " + GetProfileAuthOptions (profileAuthOptions) +\r
 128                                 (string.IsNullOrEmpty(userNameToMatch) ? "" : " AND usr.LoweredUserName LIKE ?");\r
 129                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 130                         AddParameter (cmdSelect, "ApplicationId", appId);\r
 131                         AddParameter (cmdSelect, "LastActivityDate", inactiveSinceDate);\r
 132                         if (!string.IsNullOrEmpty (userNameToMatch))\r
 133                                 AddParameter (cmdSelect, "LoweredUserName", userNameToMatch.ToLowerInvariant());\r
 134                         reader = cmdSelect.ExecuteReader ();\r
 135                         return 0;\r
 136                 }\r
 137 \r
 138                 public static int Profile_GetProfiles (DbConnection connection, string applicationName, int profileAuthOptions, int pageIndex, int pageSize, string userNameToMatch, out DbDataReader reader)\r
 139                 {\r
 140                         reader = null;\r
 141                         string appId = DerbyApplicationsHelper.GetApplicationId (connection, applicationName);\r
 142                         if (appId == null)\r
 143                                 return -1;\r
 144 \r
 145                         string querySelect = @"SELECT usr.UserName, usr.IsAnonymous, usr.LastActivityDate, prf.LastUpdatedDate, " +\r
 146                                 "LENGTH(prf.PropertyNames) + LENGTH(prf.PropertyValuesString) + LENGTH(prf.PropertyValuesBinary)" +\r
 147                                 "FROM aspnet_Users usr, aspnet_Profile prf WHERE ApplicationId = ? AND usr.UserId = prf.UserId " +\r
 148                                 GetProfileAuthOptions (profileAuthOptions) +\r
 149                                 (string.IsNullOrEmpty (userNameToMatch) ? "" : " AND usr.LoweredUserName LIKE ?");\r
 150                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 151                         AddParameter (cmdSelect, "ApplicationId", appId);\r
 152                         if (!string.IsNullOrEmpty (userNameToMatch))\r
 153                                 AddParameter (cmdSelect, "LoweredUserName", userNameToMatch.ToLowerInvariant ());\r
 154                         reader = cmdSelect.ExecuteReader ();\r
 155                         return 0;\r
 156                 }\r
 157 \r
 158                 public static int Profile_GetProperties (DbConnection connection, string applicationName, string username, DateTime currentTimeUtc, out DbDataReader reader)\r
 159                 {\r
 160                         reader = null;\r
 161                         string appId = DerbyApplicationsHelper.GetApplicationId (connection, applicationName);\r
 162                         if (appId == null)\r
 163                                 return -1;\r
 164 \r
 165                         string userId = GetUserId (connection, null, appId, username);\r
 166                         if (userId == null)\r
 167                                 return -1;\r
 168 \r
 169                         string queryUpdUser = @"UPDATE aspnet_Users SET LastActivityDate = ? WHERE UserId = ?";\r
 170                         OleDbCommand cmdUpdUser = new OleDbCommand (queryUpdUser, (OleDbConnection) connection);\r
 171                         AddParameter (cmdUpdUser, "LastActivityDate", currentTimeUtc);\r
 172                         AddParameter (cmdUpdUser, "UserId", userId);\r
 173                         cmdUpdUser.ExecuteNonQuery ();\r
 174 \r
 175                         string querySelect = @"SELECT PropertyNames, PropertyValuesString, PropertyValuesBinary FROM aspnet_Profile WHERE UserId = ?";\r
 176                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 177                         AddParameter (cmdSelect, "UserId", userId);\r
 178                         reader = cmdSelect.ExecuteReader ();\r
 179                         return 0;\r
 180                 }\r
 181 \r
 182                 public static int Profile_SetProperties (DbConnection connection, string applicationName, string propertyNames, string propertyValuesString, byte [] propertyValuesBinary, string username, bool isUserAnonymous, DateTime currentTimeUtc)\r
 183                 {\r
 184                         string appId = DerbyApplicationsHelper.GetApplicationId (connection, applicationName);\r
 185                         if (appId == null) {\r
 186                                 object newAppId = DerbyApplicationsHelper.Applications_CreateApplication (connection, applicationName);\r
 187                                 appId = newAppId as string;\r
 188                                 if (appId == null)\r
 189                                         return -1;\r
 190                         }\r
 191 \r
 192                         OleDbTransaction trans = (OleDbTransaction) connection.BeginTransaction ();\r
 193                         try {\r
 194                                 string userId = GetUserId (connection, trans, appId, username);\r
 195                                 if (userId == null) {\r
 196                                         object newUserId = null;\r
 197                                         DerbyMembershipHelper.Users_CreateUser (connection, trans, appId, username, true, currentTimeUtc, ref newUserId);\r
 198                                         userId = newUserId as string;\r
 199                                         if (userId == null) {\r
 200                                                 trans.Rollback ();\r
 201                                                 return -1;\r
 202                                         }\r
 203                                 }\r
 204 \r
 205                                 string queryUpdUser = @"UPDATE aspnet_Users SET LastActivityDate=? WHERE UserId = ?";\r
 206                                 OleDbCommand cmdUpdUser = new OleDbCommand (queryUpdUser, (OleDbConnection) connection);\r
 207                                 cmdUpdUser.Transaction = trans;\r
 208                                 AddParameter (cmdUpdUser, "LastActivityDate", currentTimeUtc);\r
 209                                 AddParameter (cmdUpdUser, "UserId", userId);\r
 210                                 cmdUpdUser.ExecuteNonQuery ();\r
 211 \r
 212                                 string querySelect = @"SELECT * FROM aspnet_Profile WHERE UserId = ?";\r
 213                                 OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 214                                 cmdSelect.Transaction = trans;\r
 215                                 AddParameter (cmdSelect, "UserId", userId);\r
 216                                 bool userHasRecords = false;\r
 217                                 using (OleDbDataReader reader = cmdSelect.ExecuteReader ()) {\r
 218                                         userHasRecords = reader.HasRows;\r
 219                                 }\r
 220 \r
 221                                 if (userHasRecords) {\r
 222                                         string queryUpdate = @"UPDATE aspnet_Profile SET PropertyNames = ?, PropertyValuesString = ?, " +\r
 223                                                 "PropertyValuesBinary = ?, LastUpdatedDate = ? WHERE  UserId = ?";\r
 224                                         OleDbCommand cmdUpdate = new OleDbCommand (queryUpdate, (OleDbConnection) connection);\r
 225                                         cmdUpdate.Transaction = trans;\r
 226                                         AddParameter (cmdUpdate, "PropertyNames", propertyNames);\r
 227                                         AddParameter (cmdUpdate, "PropertyValuesString", propertyValuesString);\r
 228                                         AddParameter (cmdUpdate, "PropertyValuesBinary", propertyValuesBinary);\r
 229                                         AddParameter (cmdUpdate, "LastUpdatedDate", currentTimeUtc);\r
 230                                         AddParameter (cmdUpdate, "UserId", userId);\r
 231                                         cmdUpdate.ExecuteNonQuery ();\r
 232                                 }\r
 233                                 else {\r
 234                                         string queryInsert = @"INSERT INTO aspnet_Profile(UserId, PropertyNames, PropertyValuesString, " +\r
 235                                                 "PropertyValuesBinary, LastUpdatedDate) VALUES (?, ?, ?, ?, ?)";\r
 236                                         OleDbCommand cmdInsert = new OleDbCommand (queryInsert, (OleDbConnection) connection);\r
 237                                         cmdInsert.Transaction = trans;\r
 238                                         AddParameter (cmdInsert, "UserId", userId);\r
 239                                         AddParameter (cmdInsert, "PropertyNames", propertyNames);\r
 240                                         AddParameter (cmdInsert, "PropertyValuesString", propertyValuesString);\r
 241                                         AddParameter (cmdInsert, "PropertyValuesBinary", propertyValuesBinary);\r
 242                                         AddParameter (cmdInsert, "LastUpdatedDate", currentTimeUtc);\r
 243                                         cmdInsert.ExecuteNonQuery ();\r
 244                                 }\r
 245                                 trans.Commit ();\r
 246                         }\r
 247                         catch (Exception e) {\r
 248                                 trans.Rollback ();\r
 249                                 throw e;\r
 250                         }\r
 251                         return 0;\r
 252                 }\r
 253 \r
 254                 private static string GetUserId (DbConnection connection, DbTransaction trans, string applicationId, string username)\r
 255                 {\r
 256                         if (username == null)\r
 257                                 return null;\r
 258 \r
 259                         string selectQuery = "SELECT UserId FROM aspnet_Users WHERE LoweredUserName = ? AND ApplicationId = ?";\r
 260 \r
 261                         OleDbCommand selectCmd = new OleDbCommand (selectQuery, (OleDbConnection) connection);\r
 262                         if (trans != null)\r
 263                                 selectCmd.Transaction = (OleDbTransaction) trans;\r
 264 \r
 265                         AddParameter (selectCmd, "LoweredUserName", username.ToLowerInvariant ());\r
 266                         AddParameter (selectCmd, "ApplicationId", applicationId);\r
 267                         using (OleDbDataReader reader = selectCmd.ExecuteReader ()) {\r
 268                                 if (reader.Read ())\r
 269                                         return reader.GetString (0);\r
 270                         }\r
 271 \r
 272                         return null;\r
 273                 }\r
 274                 \r
 275                 private static string GetProfileAuthOptions (int profileAuthOptions)\r
 276                 {\r
 277                         switch (profileAuthOptions) {\r
 278                                 case 1:\r
 279                                         return "AND IsAnonymous = 0";\r
 280 \r
 281                                 case 2:\r
 282                                         return "AND IsAnonymous = 1";\r
 283                         }\r
 284                         return string.Empty;\r
 285                 }\r
 286         }\r
 287 }\r
 288 \r