/BlogEngine/DotNetSlave.BusinessLogic/Providers/DbRoleProvider.cs
C# | 584 lines | 359 code | 92 blank | 133 comment | 34 complexity | f19448a353f975ae39a018a1044a6e01 MD5 | raw file
1namespace BlogEngine.Core.Providers 2{ 3 using System; 4 using System.Collections.Generic; 5 using System.Collections.Specialized; 6 using System.Configuration; 7 using System.Configuration.Provider; 8 using System.Data; 9 using System.Data.Common; 10 using System.Web.Security; 11 12 /// <summary> 13 /// Generic Db Role Provider 14 /// </summary> 15 public class DbRoleProvider : RoleProvider 16 { 17 #region Constants and Fields 18 19 /// <summary> 20 /// The application name. 21 /// </summary> 22 private string applicationName; 23 24 /// <summary> 25 /// The conn string name. 26 /// </summary> 27 private string connStringName; 28 29 /// <summary> 30 /// The parm prefix. 31 /// </summary> 32 private string parmPrefix; 33 34 /// <summary> 35 /// The table prefix. 36 /// </summary> 37 private string tablePrefix; 38 39 #endregion 40 41 #region Properties 42 43 /// <summary> 44 /// Gets or sets the application name as set in the web.config 45 /// otherwise returns BlogEngine. Set will throw an error. 46 /// </summary> 47 public override string ApplicationName 48 { 49 get 50 { 51 return this.applicationName; 52 } 53 54 set 55 { 56 throw new NotImplementedException(); 57 } 58 } 59 60 #endregion 61 62 #region Public Methods 63 64 /// <summary> 65 /// Adds all users in user array to all roles in role array 66 /// </summary> 67 /// <param name="usernames">A string array of user names to be added to the specified roles.</param> 68 /// <param name="roleNames">A string array of the role names to add the specified user names to.</param> 69 public override void AddUsersToRoles(string[] usernames, string[] roleNames) 70 { 71 using (var conn = this.CreateConnection()) 72 { 73 if (conn.HasConnection) 74 { 75 using (var cmd = conn.CreateCommand()) 76 { 77 cmd.CommandType = CommandType.Text; 78 79 var parms = cmd.Parameters; 80 81 foreach (var user in usernames) 82 { 83 //parms.Clear(); 84 //cmd.CommandText = string.Format("SELECT UserID FROM {0}Users WHERE BlogID = {1}blogid AND UserName = {1}user", this.tablePrefix, this.parmPrefix); 85 86 //parms.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString())); 87 //parms.Add(conn.CreateParameter(FormatParamName("user"), user)); 88 89 //var userId = Int32.Parse(cmd.ExecuteScalar().ToString()); 90 91 foreach (var role in roleNames) 92 { 93 if (!role.Equals(BlogConfig.AnonymousRole)) 94 { 95 //parms.Clear(); 96 //cmd.CommandText = string.Format("SELECT RoleID FROM {0}Roles WHERE BlogID = {1}blogid AND Role = {1}role", this.tablePrefix, this.parmPrefix); 97 98 //parms.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString())); 99 //parms.Add(conn.CreateParameter(FormatParamName("role"), role)); 100 101 //var roleId = Int32.Parse(cmd.ExecuteScalar().ToString()); 102 103 parms.Clear(); 104 cmd.CommandText = string.Format("INSERT INTO {0}UserRoles (BlogID, UserName, Role) VALUES ({1}blogID, {1}username, {1}role)", this.tablePrefix, this.parmPrefix); 105 106 parms.Add(conn.CreateParameter(FormatParamName("blogID"), Blog.CurrentInstance.Id.ToString())); 107 parms.Add(conn.CreateParameter(FormatParamName("username"), user.Trim())); 108 parms.Add(conn.CreateParameter(FormatParamName("role"), role.Trim())); 109 110 cmd.ExecuteNonQuery(); 111 } 112 } 113 } 114 } 115 } 116 } 117 118 // This needs to be called in order to keep the Right class in sync. 119 Right.RefreshAllRights(); 120 } 121 122 /// <summary> 123 /// Adds a new role to the database 124 /// </summary> 125 /// <param name="roleName">The name of the role to create.</param> 126 public override void CreateRole(string roleName) 127 { 128 using (var conn = this.CreateConnection()) 129 { 130 if (conn.HasConnection) 131 { 132 using (var cmd = conn.CreateTextCommand(string.Format("INSERT INTO {0}Roles (BlogID, role) VALUES ({1}blogid, {1}role)", this.tablePrefix, this.parmPrefix))) 133 { 134 cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString())); 135 cmd.Parameters.Add(conn.CreateParameter(FormatParamName("role"), roleName)); 136 cmd.ExecuteNonQuery(); 137 } 138 } 139 } 140 141 // This needs to be called in order to keep the Right class in sync. 142 Right.RefreshAllRights(); 143 } 144 145 /// <summary> 146 /// Removes a role from database 147 /// </summary> 148 /// <param name="roleName">The name of the role to delete.</param> 149 /// <param name="throwOnPopulatedRole">If true, throw an exception if <paramref name="roleName"/> has one or more members and do not delete <paramref name="roleName"/>.</param> 150 /// <returns>The delete role.</returns> 151 public override bool DeleteRole(string roleName, bool throwOnPopulatedRole) 152 { 153 var success = false; 154 155 if (!Security.IsSystemRole(roleName)) 156 { 157 using (var conn = this.CreateConnection()) 158 { 159 if (conn.HasConnection) 160 { 161 using (var cmd = conn.CreateTextCommand(string.Format("DELETE FROM {0}Roles WHERE BlogID = {1}blogid AND Role = {1}role", this.tablePrefix, this.parmPrefix))) 162 { 163 cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString())); 164 cmd.Parameters.Add(conn.CreateParameter(FormatParamName("role"), roleName)); 165 cmd.ExecuteNonQuery(); 166 success = true; 167 } 168 } 169 } 170 } 171 172 // This needs to be called in order to keep the Right class in sync. 173 Right.RefreshAllRights(); 174 175 return success; 176 } 177 178 /// <summary> 179 /// Returns all users in selected role with names that match usernameToMatch 180 /// </summary> 181 /// <param name="roleName">The role to search in.</param> 182 /// <param name="usernameToMatch">The user name to search for.</param> 183 /// <returns> 184 /// A string array containing the names of all the users where the user name matches <paramref name="usernameToMatch"/> and the user is a member of the specified role. 185 /// </returns> 186 public override string[] FindUsersInRole(string roleName, string usernameToMatch) 187 { 188 var users = new List<string>(); 189 190 using (var conn = this.CreateConnection()) 191 { 192 if (conn.HasConnection) 193 { 194 var sqlQuery = string.Format( 195 " SELECT ur.UserName " + 196 " FROM {0}UsersRoles ur " + 197 " WHERE ur.BlogID = {1}blogid " + 198 " AND ur.Role = {1}role " + 199 " AND ur.UserName LIKE {1}name", this.tablePrefix, this.parmPrefix); 200 201 using (var cmd = conn.CreateTextCommand(sqlQuery)) 202 { 203 var parms = cmd.Parameters; 204 parms.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString())); 205 parms.Add(conn.CreateParameter(FormatParamName("role"), roleName)); 206 parms.Add(conn.CreateParameter(FormatParamName("name"), string.Format("{0}%", usernameToMatch))); 207 208 using (var rdr = cmd.ExecuteReader()) 209 { 210 while (rdr.Read()) 211 { 212 if (!rdr.IsDBNull(0)) 213 { 214 users.Add(rdr.GetString(0)); 215 } 216 } 217 } 218 } 219 } 220 } 221 222 return users.ToArray(); 223 } 224 225 /// <summary> 226 /// Returns array of all roles in database 227 /// </summary> 228 /// <returns> 229 /// A string array containing the names of all the roles stored in the data source for the configured applicationName. 230 /// </returns> 231 public override string[] GetAllRoles() 232 { 233 var roles = new List<string>(); 234 235 using (var conn = this.CreateConnection()) 236 { 237 if (conn.HasConnection) 238 { 239 using (var cmd = conn.CreateTextCommand(string.Format("SELECT role FROM {0}Roles WHERE BlogID = {1}blogid ", this.tablePrefix, this.parmPrefix))) 240 { 241 cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString())); 242 243 using (var rdr = cmd.ExecuteReader()) 244 { 245 while (rdr.Read()) 246 { 247 if (!rdr.IsDBNull(0)) 248 { 249 roles.Add(rdr.GetString(0)); 250 } 251 } 252 } 253 } 254 } 255 } 256 257 return roles.ToArray(); 258 } 259 260 /// <summary> 261 /// Return an array of roles that user is in 262 /// </summary> 263 /// <param name="username">The user to return a list of roles for.</param> 264 /// <returns> 265 /// A string array containing the names of all the roles that the specified user is in for the configured applicationName. 266 /// </returns> 267 public override string[] GetRolesForUser(string username) 268 { 269 var roles = new List<string>(); 270 271 using (var conn = this.CreateConnection()) 272 { 273 if (conn.HasConnection) 274 { 275 var sqlQuery = string.Format( 276 " SELECT ur.role " + 277 " FROM {0}UserRoles ur " + 278 " WHERE ur.BlogID = {1}blogid " + 279 " AND ur.UserName = {1}name", this.tablePrefix, this.parmPrefix); 280 281 using (var cmd = conn.CreateTextCommand(sqlQuery)) 282 { 283 cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString())); 284 cmd.Parameters.Add(conn.CreateParameter(FormatParamName("name"), username)); 285 286 using (var rdr = cmd.ExecuteReader()) 287 { 288 while (rdr.Read()) 289 { 290 if (!rdr.IsDBNull(0)) 291 { 292 roles.Add(rdr.GetString(0)); 293 } 294 } 295 } 296 } 297 } 298 } 299 300 return roles.ToArray(); 301 } 302 303 /// <summary> 304 /// Returns array of users in selected role 305 /// </summary> 306 /// <param name="roleName">The name of the role to get the list of users for.</param> 307 /// <returns> 308 /// A string array containing the names of all the users who are members of the specified role for the configured applicationName. 309 /// </returns> 310 public override string[] GetUsersInRole(string roleName) 311 { 312 var users = new List<string>(); 313 314 using (var conn = this.CreateConnection()) 315 { 316 if (conn.HasConnection) 317 { 318 var sqlQuery = string.Format( 319 " SELECT ur.UserName " + 320 " FROM {0}UserRoles ur " + 321 " WHERE ur.BlogID = {1}blogid " + 322 " AND ur.Role = {1}role", this.tablePrefix, this.parmPrefix); 323 324 using (var cmd = conn.CreateTextCommand(sqlQuery)) 325 { 326 cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString())); 327 cmd.Parameters.Add(conn.CreateParameter(FormatParamName("role"), roleName)); 328 329 using (var rdr = cmd.ExecuteReader()) 330 { 331 while (rdr.Read()) 332 { 333 if (!rdr.IsDBNull(0)) 334 { 335 users.Add(rdr.GetString(0)); 336 } 337 } 338 } 339 } 340 } 341 } 342 343 return users.ToArray(); 344 } 345 346 /// <summary> 347 /// Initializes the provider 348 /// </summary> 349 /// <param name="name"> 350 /// Configuration name 351 /// </param> 352 /// <param name="config"> 353 /// Configuration settings 354 /// </param> 355 public override void Initialize(string name, NameValueCollection config) 356 { 357 if (config == null) 358 { 359 throw new ArgumentNullException("config"); 360 } 361 362 if (String.IsNullOrEmpty(name)) 363 { 364 name = "DbMembershipProvider"; 365 } 366 367 if (String.IsNullOrEmpty(config["description"])) 368 { 369 config.Remove("description"); 370 config.Add("description", "Generic Database Membership Provider"); 371 } 372 373 base.Initialize(name, config); 374 375 if (config["connectionStringName"] == null) 376 { 377 // default to BlogEngine 378 config["connectionStringName"] = "BlogEngine"; 379 } 380 381 this.connStringName = config["connectionStringName"]; 382 config.Remove("connectionStringName"); 383 384 if (config["tablePrefix"] == null) 385 { 386 // default 387 config["tablePrefix"] = "be_"; 388 } 389 390 this.tablePrefix = config["tablePrefix"]; 391 config.Remove("tablePrefix"); 392 393 if (config["parmPrefix"] == null) 394 { 395 // default 396 config["parmPrefix"] = "@"; 397 } 398 399 this.parmPrefix = config["parmPrefix"]; 400 config.Remove("parmPrefix"); 401 402 if (config["applicationName"] == null) 403 { 404 // default to BlogEngine 405 config["applicationName"] = "BlogEngine"; 406 } 407 408 this.applicationName = config["applicationName"]; 409 config.Remove("applicationName"); 410 411 // Throw an exception if unrecognized attributes remain 412 if (config.Count > 0) 413 { 414 var attr = config.GetKey(0); 415 if (!String.IsNullOrEmpty(attr)) 416 { 417 throw new ProviderException(string.Format("Unrecognized attribute: {0}", attr)); 418 } 419 } 420 } 421 422 /// <summary> 423 /// Check to see if user is in a role 424 /// </summary> 425 /// <param name="username">The user name to search for.</param> 426 /// <param name="roleName">The role to search in.</param> 427 /// <returns>The is user in role.</returns> 428 public override bool IsUserInRole(string username, string roleName) 429 { 430 var roleFound = false; 431 432 using (var conn = this.CreateConnection()) 433 { 434 if (conn.HasConnection) 435 { 436 var sqlQuery = string.Format( 437 " SELECT ur.UserRoleID " + 438 " FROM {0}UserRoles ur " + 439 " WHERE ur.BlogID = {1}blogid " + 440 " AND ur.UserName = {1}name " + 441 " AND ur.role = {1}role", this.tablePrefix, this.parmPrefix); 442 443 using (var cmd = conn.CreateTextCommand(sqlQuery)) 444 { 445 var parms = cmd.Parameters; 446 cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString())); 447 parms.Add(conn.CreateParameter(FormatParamName("name"), username)); 448 parms.Add(conn.CreateParameter(FormatParamName("role"), roleName)); 449 450 using (var rdr = cmd.ExecuteReader()) 451 { 452 roleFound = rdr.Read(); 453 } 454 } 455 } 456 } 457 458 return roleFound; 459 } 460 461 /// <summary> 462 /// Removes all users in user array from all roles in role array 463 /// </summary> 464 /// <param name="usernames">A string array of user names to be removed from the specified roles.</param> 465 /// <param name="roleNames">A string array of role names to remove the specified user names from.</param> 466 public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames) 467 { 468 469 using (var conn = this.CreateConnection()) 470 { 471 if (conn.HasConnection) 472 { 473 using (var cmd = conn.CreateCommand()) 474 { 475 cmd.CommandType = CommandType.Text; 476 477 var parms = cmd.Parameters; 478 479 foreach (var user in usernames) 480 { 481 //parms.Clear(); 482 //cmd.CommandText = string.Format("SELECT UserID FROM {0}Users WHERE BlogID = {1}blogid AND UserName = {1}user", this.tablePrefix, this.parmPrefix); 483 484 //parms.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString())); 485 //parms.Add(conn.CreateParameter(FormatParamName("user"), user)); 486 487 //int userId; 488 //try 489 //{ 490 // userId = Int32.Parse(cmd.ExecuteScalar().ToString()); 491 //} 492 //catch 493 //{ 494 // userId = 0; 495 //} 496 497 //if (userId <= 0) 498 //{ 499 // continue; 500 //} 501 502 foreach (var role in roleNames) 503 { 504 //parms.Clear(); 505 //cmd.CommandText = string.Format("SELECT RoleID FROM {0}Roles WHERE BlogID = {1}blogid AND Role = {1}role", this.tablePrefix, this.parmPrefix); 506 507 //parms.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString())); 508 //parms.Add(conn.CreateParameter(FormatParamName("role"), role)); 509 510 //var roleId = Int32.Parse(cmd.ExecuteScalar().ToString()); 511 512 parms.Clear(); 513 cmd.CommandText = string.Format("DELETE FROM {0}UserRoles WHERE BlogID = {1}blogid AND UserName = {1}username AND Role = {1}role", this.tablePrefix, this.parmPrefix); 514 515 parms.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString())); 516 parms.Add(conn.CreateParameter(FormatParamName("username"), user.Trim())); 517 parms.Add(conn.CreateParameter(FormatParamName("role"), role.Trim())); 518 519 cmd.ExecuteNonQuery(); 520 } 521 } 522 } 523 } 524 } 525 526 // This needs to be called in order to keep the Right class in sync. 527 Right.RefreshAllRights(); 528 } 529 530 /// <summary> 531 /// Checks to see if role exists 532 /// </summary> 533 /// <param name="roleName">The name of the role to search for in the data source.</param> 534 /// <returns>The role exists.</returns> 535 public override bool RoleExists(string roleName) 536 { 537 var roleFound = false; 538 539 using (var conn = this.CreateConnection()) 540 { 541 if (conn.HasConnection) 542 { 543 544 using (var cmd = conn.CreateTextCommand(string.Format("SELECT roleID FROM {0}Roles WHERE BlogID = {1}blogid AND role = {1}role", this.tablePrefix, this.parmPrefix))) 545 { 546 cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString())); 547 cmd.Parameters.Add(conn.CreateParameter(FormatParamName("role"), roleName)); 548 549 using (var rdr = cmd.ExecuteReader()) 550 { 551 roleFound = rdr.Read(); 552 } 553 } 554 } 555 } 556 557 return roleFound; 558 } 559 560 #endregion 561 562 #region "Methods" 563 564 private DbConnectionHelper CreateConnection() 565 { 566 var settings = ConfigurationManager.ConnectionStrings[this.connStringName]; 567 return new DbConnectionHelper(settings); 568 } 569 570 571 /// <summary> 572 /// Returns a formatted parameter name to include this DbRoleProvider instance's paramPrefix. 573 /// </summary> 574 /// <param name="parameterName"></param> 575 /// <returns></returns> 576 private string FormatParamName(string parameterName) 577 { 578 return String.Format("{0}{1}", this.parmPrefix, parameterName); 579 } 580 581 #endregion 582 583 } 584}