Geeks With Blogs
Łukasz Kuryło's blog
This text is based on the Chris Pels video and code located here.

ASP.NET Membership mechanism allows to use the build-in server tables to store and retrieve user data. By default, this mechanism can only be used with SQL Server database to which Microsoft provides suitable MembershipProvider class.
Inheriting from this abstract class, developer can use his own table(-es) from any database and use any database engine other than SQL Server, like Oracle, DB2 etc.

In this article I would like to show, how to implement this class for my own User table, using Linq to SQL.

This table looks like below. As you can see, there is less fields than in the default User table from the Membership mechanism, because now I have only these fields which I need in my project:














To select, delete and update data in this table, I created a several stored procedures:


















The T-SQL code for this table and procedures can be found at the end of this article.

Implementing the class derived from the MembershipProvider it isn't all what have to be done. Beyond this, the provider must also be configured in the Web.config file:

  1:  		<authentication mode="Forms">
2: <forms loginUrl="Default.aspx" name=".ASPXFORMSAUTH"/>
3: </authentication>
4: <authorization>
5: <deny users="?"/>
6: </authorization>
7: <machineKey validationKey="C50B3C89CB21F4F1422FF158A5B42D0E8DB8CB5CDA1742572A487D9401E3400267682B202B746511891C1BAF47F8D25C07F6C39A104696DB51F17C529AD3CABE" decryptionKey="8A9BE8FD67AF6979E7D20198CFEA50DD3D3799C77AF2B72F" validation="SHA1"/>
8: <!--Custom Membership Provider Configuration-->
9: <membership defaultProvider="ShopMembershipProvider" userIsOnlineTimeWindow="15">
10: <providers>
11: <clear/>
12: <add name="ShopMembershipProvider" type="Shop.Data.ShopMembershipProvider" connectionStringName="ShopConnectionString" enablePasswordRetrieval="true" enablePasswordReset="true" requiresQuestionAndAnswer="true" writeExceptionsToEventLog="false"/>
13: </providers>
14: </membership>


The abstract class have many methods, so the below source code is huge, despite the fact that, some of these methods aren't implemented, because I do not have corresponding fields in the data table.

  1:  using System;
2: using System.Collections.Generic;
3: using System.Linq;
4: using System.Web;
5: using System.Web.Security;
6: using System.Configuration;
7: using System.Configuration.Provider;
8: using System.Web.Configuration;
9: using System.Security.Cryptography;
10: using System.Text;
11:
12: namespace Shop.Data
13: {
14: public class ShopMembershipProvider : MembershipProvider
15: {
16: private MachineKeySection machineKey;
17: private ShopMembershipDataContext db;
18: private int newPasswordLength = 8;
19:
20: public override string ApplicationName
21: {
22: get;
23: set;
24: }
25:
26: private bool enablePasswordReset;
27: public override bool EnablePasswordReset
28: {
29: get { return enablePasswordReset; }
30: }
31:
32: private bool enablePasswordRetrieval;
33: public override bool EnablePasswordRetrieval
34: {
35: get { return enablePasswordRetrieval; }
36: }
37:
38: private bool requiresQuestionAndAnswer;
39: public override bool RequiresQuestionAndAnswer
40: {
41: get { return requiresQuestionAndAnswer; }
42: }
43:
44: private bool requiresUniqueEmail;
45: public override bool RequiresUniqueEmail
46: {
47: get { return requiresUniqueEmail; }
48: }
49:
50: private int maxInvalidPasswordAttempts;
51: public override int MaxInvalidPasswordAttempts
52: {
53: get { return maxInvalidPasswordAttempts; }
54: }
55:
56: private int passwordAttemptWindow;
57: public override int PasswordAttemptWindow
58: {
59: get { return passwordAttemptWindow; }
60: }
61:
62: private MembershipPasswordFormat passwordFormat;
63: public override MembershipPasswordFormat PasswordFormat
64: {
65: get { return passwordFormat; }
66: }
67:
68: private int minRequiredNonAlphanumericCharacters;
69: public override int MinRequiredNonAlphanumericCharacters
70: {
71: get { return minRequiredNonAlphanumericCharacters; }
72: }
73:
74: private int minRequiredPasswordLength;
75: public override int MinRequiredPasswordLength
76: {
77: get { return minRequiredPasswordLength; }
78: }
79:
80: private string passwordStrengthRegularExpression;
81: public override string PasswordStrengthRegularExpression
82: {
83: get { return passwordStrengthRegularExpression; }
84: }
85:
86: public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
87: {
88: if (config == null)
89: {
90: throw new ArgumentNullException("config");
91: }
92:
93: if (name == null || name.Length == 0)
94: {
95: name = "ShopMembershipProvider";
96: }
97:
98: base.Initialize(name, config);
99:
100: ApplicationName = GetConfigValue(config["applicationName"], System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
101: maxInvalidPasswordAttempts = Convert.ToInt32(GetConfigValue(config["maxInvalidPasswordAttempts"], "5"));
102: passwordAttemptWindow = Convert.ToInt32(GetConfigValue(config["passwordAttemptWindow"], "10"));
103: minRequiredNonAlphanumericCharacters = Convert.ToInt32(GetConfigValue(config["minRequiredAlphaNumericCharacters"], "1"));
104: minRequiredPasswordLength = Convert.ToInt32(GetConfigValue(config["minRequiredPasswordLength"], "7"));
105: passwordStrengthRegularExpression = Convert.ToString(GetConfigValue(config["passwordStrengthRegularExpression"], String.Empty));
106: enablePasswordReset = Convert.ToBoolean(GetConfigValue(config["enablePasswordReset"], "true"));
107: enablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(config["enablePasswordRetrieval"], "true"));
108: requiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(config["requiresQuestionAndAnswer"], "false"));
109: requiresUniqueEmail = Convert.ToBoolean(GetConfigValue(config["requiresUniqueEmail"], "true"));
110:
111: string temp_format = config["passwordFormat"];
112: if (temp_format == null)
113: {
114: temp_format = "Hashed";
115: }
116:
117: switch (temp_format)
118: {
119: case "Hashed":
120: passwordFormat = MembershipPasswordFormat.Hashed;
121: break;
122: case "Encrypted":
123: passwordFormat = MembershipPasswordFormat.Encrypted;
124: break;
125: case "Clear":
126: passwordFormat = MembershipPasswordFormat.Clear;
127: break;
128: default:
129: throw new ProviderException("Password format not supported.");
130: }
131:
132: System.Configuration.Configuration cfg = WebConfigurationManager.OpenWebConfiguration(System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
133: machineKey = cfg.GetSection("system.web/machineKey") as MachineKeySection;
134:
135: if (machineKey.ValidationKey.Contains("AutoGenerate"))
136: {
137: if (PasswordFormat != MembershipPasswordFormat.Clear)
138: {
139: throw new ProviderException("Hashed or Encrypted passwords are not supported with auto-generated keys.");
140: }
141: }
142: }
143:
144: private string GetConfigValue(string configValue, string defaultValue)
145: {
146: if (String.IsNullOrEmpty(configValue))
147: {
148: return defaultValue;
149: }
150:
151: return configValue;
152: }
153:
154: public override bool ChangePassword(string username, string oldPassword, string newPassword)
155: {
156: if (!ValidateUser(username, oldPassword))
157: {
158: return false;
159: }
160:
161: ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);
162:
163: OnValidatingPassword(args);
164:
165: if (args.Cancel)
166: {
167: if (args.FailureInformation != null)
168: {
169: throw args.FailureInformation;
170: }
171: else
172: {
173: throw new Exception("Change password canceled due to new password validation failure.");
174: }
175: }
176:
177: InitDB();
178:
179: db.ChangePassword(username, EncodePassword( newPassword));
180:
181: try
182: {
183: db.SubmitChanges();
184: }
185: catch (Exception e)
186: {
187: HttpContext.Current.Response.Write(e.Message);
188: return false;
189: }
190:
191: return true;
192: }
193:
194: public override bool ChangePasswordQuestionAndAnswer(string username, string password, string newPasswordQuestion, string newPasswordAnswer)
195: {
196: if (!ValidateUser(username, password))
197: {
198: return false;
199: }
200:
201: InitDB();
202:
203: int result = db.ChangePasswordQuestionAnswer(EncodePassword( newPasswordAnswer), newPasswordAnswer, username);
204:
205: try
206:
{
207: db.SubmitChanges();
208:
209: if (result != 0)
210: {
211: return false;
212: }
213: }
214: catch (Exception e)
215: {
216: HttpContext.Current.Response.Write(e.Message);
217: return false;
218: }
219:
220: return true;
221: }
222:
223: public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey, out MembershipCreateStatus status)
224: {
225: ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, password, true);
226:
227: OnValidatingPassword(args);
228:
229: if (args.Cancel)
230: {
231: status = MembershipCreateStatus.InvalidPassword;
232: return null;
233: }
234:
235: if ((RequiresUniqueEmail && (GetUserNameByEmail(email) != String.Empty)))
236: {
237: status = MembershipCreateStatus.DuplicateEmail;
238: return null;
239: }
240:
241: MembershipUser membershipUser = GetUser(username, false);
242:
243: if (membershipUser == null)
244: {
245: System.DateTime createDate = DateTime.Now;
246:
247: InitDB();
248:
249: int result = db.CreateUser(Guid.NewGuid(), username, EncodePassword(password), passwordQuestion, EncodePassword(passwordAnswer), email);
250:
251: try
252:
{
253: db.SubmitChanges();
254:
255: if (result == 0)
256: {
257: status = MembershipCreateStatus.Success;
258: }
259: else
260:
{
261: status = MembershipCreateStatus.UserRejected;
262: }
263: }
264: catch (Exception e)
265: {
266: HttpContext.Current.Response.Write(e.Message);
267: status = MembershipCreateStatus.ProviderError;
268: }
269:
270: return GetUser(username, false);
271: }
272: else
273:
{
274: status = MembershipCreateStatus.DuplicateUserName;
275: }
276:
277: return null;
278: }
279:
280: public override bool DeleteUser(string username, bool deleteAllRelatedData)
281: {
282: InitDB();
283:
284: int result = db.DeleteUser(username);
285:
286: try
287:
{
288: db.SubmitChanges();
289:
290: if (result == 0)
291: {
292: if (deleteAllRelatedData)
293: {
294: //Process commands to delete all data for the user in the database. 295: }
296: }
297: else
298:
{
299: return false;
300: }
301: }
302: catch (Exception e)
303: {
304: HttpContext.Current.Response.Write(e.Message);
305: }
306:
307: return true;
308: }
309:
310: public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
311: {
312: InitDB();
313:
314: FindUsersByEmailResult result = db.FindUsersByEmail(emailToMatch).FirstOrDefault();
315:
316: MembershipUserCollection membershipUsers = new MembershipUserCollection();
317:
318: int counter = 0;
319:
320: try
321:
{
322: db.SubmitChanges();
323:
324: int startIndex = pageSize * pageIndex;
325: int endIndex = startIndex + pageSize - 1;
326:
327: while (result!=null)
328: {
329: if (counter >= startIndex)
330: {
331: MembershipUser membershipUser = new MembershipUser(this.Name,
332: result.Login,
333: result.UserId,
334: result.Email,
335: result.Question,
336: null,
337: true,
338: false,
339: result.RegisterDate,
340: DateTime.Now, DateTime.Now, DateTime.Now, DateTime.Now);
341: membershipUsers.Add(membershipUser);
342: }
343:
344: counter += 1;
345: }
346: }
347: catch (Exception e)
348: {
349: HttpContext.Current.Response.Write(e.Message);
350: }
351:
352: totalRecords = counter;
353:
354: return membershipUsers;
355: }
356:
357: private void InitDB()
358: {
359: if (db == null)
360: {
361: db = new ShopMembershipDataContext();
362: }
363: }
364:
365: public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
366: {
367: InitDB();
368:
369: FindUsersByNameResult result = db.FindUsersByName(usernameToMatch).FirstOrDefault();
370:
371: MembershipUserCollection membershipUsers = new MembershipUserCollection();
372:
373: int counter = 0;
374:
375: try
376:
{
377: db.SubmitChanges();
378:
379: int startIndex = pageSize * pageIndex;
380: int endIndex = startIndex + pageSize - 1;
381:
382: while (result != null)
383: {
384: if (counter >= startIndex)
385: {
386: MembershipUser membershipUser = new MembershipUser(this.Name,
387: result.Login,
388: result.UserId,
389: result.Email,
390: result.Question,
391: null,
392: true,
393: false,
394: result.RegisterDate,
395: DateTime.Now, DateTime.Now, DateTime.Now, DateTime.Now);
396: membershipUsers.Add(membershipUser);
397: }
398:
399: counter += 1;
400: }
401: }
402: catch (Exception e)
403: {
404: HttpContext.Current.Response.Write(e.Message);
405: }
406:
407:
408: totalRecords = counter;
409:
410: return membershipUsers;
411: }
412:
413: public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
414: {
415: InitDB();
416:
417: GetAllUsersResult result = db.GetAllUsers().FirstOrDefault();
418:
419: MembershipUserCollection users = new MembershipUserCollection();
420:
421: totalRecords = 0;
422:
423: try
424:
{
425: db.SubmitChanges();
426:
427: int counter = 0;
428: int startIndex = pageSize * pageIndex;
429: int endIndex = startIndex + pageSize - 1;
430:
431: while (result!=null)
432: {
433: if (counter >= startIndex)
434: {
435: users.Add(new MembershipUser(this.Name,
436: result.Login,
437: result.UserId,
438: result.Email,
439: result.Question,
440: null,
441: true,
442: false,
443: result.RegisterDate,
444: DateTime.Now, DateTime.Now, DateTime.Now, DateTime.Now));
445: }
446:
447: counter += 1;
448: }
449: }
450: catch (Exception e)
451: {
452: HttpContext.Current.Response.Write(e.Message);
453: }
454:
455: return users;
456: }
457:
458: public override int GetNumberOfUsersOnline()
459: {
460: throw new NotImplementedException();
461: }
462:
463: public override string GetPassword(string username, string answer)
464: {
465: if (!EnablePasswordRetrieval)
466: {
467: throw new ProviderException("Password Retrieval Not Enabled.");
468: }
469:
470: if (PasswordFormat == MembershipPasswordFormat.Hashed)
471: {
472: throw new ProviderException("Cannot retrieve Hashed passwords.");
473: }
474:
475: InitDB();
476:
477: GetPasswordResult result = db.GetPassword(username).FirstOrDefault();
478:
479: string password = String.Empty;
480: string passwordAnswer = String.Empty;
481:
482: try
483:
{
484: db.SubmitChanges();
485:
486: if (result!=null)
487: {
488:
489: password = result.Password;
490: passwordAnswer = result.Answer;
491: }
492: else
493:
{
494: throw new MembershipPasswordException("The supplied user name is not found.");
495: }
496: }
497: catch (Exception e)
498: {
499: HttpContext.Current.Response.Write(e.Message);
500: }
501:
502: if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
503: {
504: throw new MembershipPasswordException("Incorrect password answer.");
505: }
506:
507: if (PasswordFormat == MembershipPasswordFormat.Encrypted)
508: {
509: password = UnEncodePassword(password);
510: }
511:
512: return password;
513: }
514:
515: public override MembershipUser GetUser(string username, bool userIsOnline)
516: {
517: InitDB();
518:
519: GetUserResult result = db.GetUser(username).FirstOrDefault();
520:
521: MembershipUser membershipUser = null;
522:
523: try
524: {
525: db.SubmitChanges();
526:
527: membershipUser = new MembershipUser(this.Name,
528: result.Login,
529: result.UserId,
530: result.Email,
531: result.Question,
532: null,
533: true,
534: false,
535: result.RegisterDate,
536: DateTime.Now, DateTime.Now, DateTime.Now, DateTime.Now);
537:
538: }
539: catch (Exception e)
540: {
541: HttpContext.Current.Response.Write(e.Message);
542: }
543:
544: return membershipUser;
545: }
546:
547: public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
548: {
549: InitDB();
550:
551: GetUserByUserIdResult result = db.GetUserByUserId((Guid)providerUserKey).FirstOrDefault();
552:
553: MembershipUser membershipUser = null;
554:
555: try
556: {
557: db.SubmitChanges();
558:
559: if (result!=null)
560: {
561:
562: membershipUser = new MembershipUser(this.Name,
563: result.Login,
564: result.UserId,
565: result.Email,
566: result.Question,
567: null,
568: true,
569: false,
570: result.RegisterDate,
571: DateTime.Now, DateTime.Now, DateTime.Now, DateTime.Now);
572: }
573: }
574: catch (Exception e)
575: {
576: HttpContext.Current.Response.Write(e.Message);
577: }
578:
579: return membershipUser;
580: }
581:
582: public override string GetUserNameByEmail(string email)
583: {
584: InitDB();
585:
586: GetUserNameByEmailResult result = db.GetUserNameByEmail(email).FirstOrDefault();
587:
588: string username = String.Empty;
589:
590: try
591: {
592: db.SubmitChanges();
593: username = result.Email;
594: }
595: catch (Exception e)
596: {
597: HttpContext.Current.Response.Write(e.Message);
598: }
599:
600: if (username == null)
601: {
602: return String.Empty;
603: }
604: else
605:
{
606: username.Trim();
607: }
608:
609: return username;
610: }
611:
612: public override string ResetPassword(string username, string answer)
613: {
614: if (!EnablePasswordReset)
615: {
616: throw new NotSupportedException("Password Reset is not enabled.");
617: }
618:
619: if ((answer == null) && (RequiresQuestionAndAnswer))
620: {
621:
622: throw new ProviderException("Password answer required for password Reset.");
623: }
624:
625: string newPassword =
626: System.Web.Security.Membership.GeneratePassword(
627: newPasswordLength,
628: MinRequiredNonAlphanumericCharacters
629: );
630:
631: ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);
632:
633: OnValidatingPassword(args);
634:
635: if (args.Cancel)
636: {
637: if (args.FailureInformation != null)
638: {
639: throw args.FailureInformation;
640: }
641: else
642: {
643: throw new MembershipPasswordException("Reset password canceled due to password validation failure.");
644: }
645: }
646:
647: InitDB();
648:
649: GetPasswordAnswerResult result = db.GetPasswordAnswer(username).FirstOrDefault();
650:
651: int rowsAffected = 0;
652: string passwordAnswer = String.Empty;
653:
654: try
655:
{
656: db.SubmitChanges();
657:
658: if (result != null)
659: {
660:
661: passwordAnswer = result.Answer;
662: }
663: else
664: {
665: throw new MembershipPasswordException("The supplied user name is not found.");
666: }
667:
668: if (RequiresQuestionAndAnswer && (!CheckPassword(answer, passwordAnswer)))
669: {
670: throw new MembershipPasswordException("Incorrect password answer.");
671: }
672:
673: int updateResult = db.UpdatePassword(EncodePassword(newPassword), username);
674: db.SubmitChanges();
675:
676: rowsAffected = updateResult;
677: }
678: catch (Exception e)
679: {
680: HttpContext.Current.Response.Write(e.Message);
681: }
682:
683: if (rowsAffected > 0)
684: {
685: return newPassword;
686: }
687: else
688:
{
689: throw new MembershipPasswordException("User not found, or user is locked out. Password not Reset.");
690: }
691: }
692:
693: public override bool UnlockUser(string userName)
694: {
695: throw new NotImplementedException();
696: }
697:
698: public override void UpdateUser(MembershipUser user)
699: {
700: InitDB();
701:
702: db.UpdateUser(user.Email, user.UserName);
703:
704: try
705:
{
706: db.SubmitChanges();
707: }
708: catch (Exception e)
709: {
710: HttpContext.Current.Response.Write(e.Message);
711: }
712: }
713:
714: public override bool ValidateUser(string username, string password)
715: {
716: bool isValid = false;
717:
718: InitDB();
719:
720: ValidateUserResult result = db.ValidateUser(username).FirstOrDefault();
721:
722: string storedPassword = String.Empty;
723:
724: try
725:
{
726: db.SubmitChanges();
727:
728: if (result != null)
729: {
730: storedPassword = result.Password;
731: }
732: else
733:
{
734: return false;
735: }
736:
737: if (CheckPassword(password, storedPassword))
738: {
739: isValid = true;
740: }
741: }
742: catch (Exception e)
743: {
744: HttpContext.Current.Response.Write(e.Message);
745: }
746:
747: return isValid;
748: }
749:
750: private bool CheckPassword(string password, string dbpassword)
751: {
752: string pass1 = password;
753: string pass2 = dbpassword;
754:
755: switch (PasswordFormat)
756: {
757: case MembershipPasswordFormat.Encrypted:
758: pass2 = UnEncodePassword(dbpassword);
759: break;
760: case MembershipPasswordFormat.Hashed:
761: pass1 = EncodePassword(password);
762: break;
763: default:
764: break;
765: }
766:
767: if (pass1 == pass2)
768: {
769: return true;
770: }
771:
772: return false;
773: }
774:
775: private string EncodePassword(string password)
776: {
777: string encodedPassword = password;
778:
779: switch (PasswordFormat)
780: {
781: case MembershipPasswordFormat.Clear:
782: break;
783: case MembershipPasswordFormat.Encrypted:
784: encodedPassword =
785: Convert.ToBase64String(EncryptPassword(Encoding.Unicode.GetBytes(password)));
786: break;
787: case MembershipPasswordFormat.Hashed:
788: HMACSHA1 hash = new HMACSHA1();
789: hash.Key = HexToByte(machineKey.ValidationKey);
790: encodedPassword =
791: Convert.ToBase64String(hash.ComputeHash(Encoding.Unicode.GetBytes(password)));
792: break;
793: default:
794: throw new ProviderException("Unsupported password format.");
795: }
796:
797: return encodedPassword;
798: }
799:
800: private string UnEncodePassword(string encodedPassword)
801: {
802: string password = encodedPassword;
803:
804: switch (PasswordFormat)
805: {
806: case MembershipPasswordFormat.Clear:
807: break;
808: case MembershipPasswordFormat.Encrypted:
809: password =
810: Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(password)));
811: break;
812: case MembershipPasswordFormat.Hashed:
813: throw new ProviderException("Cannot unencode a hashed password.");
814: default:
815: throw new ProviderException("Unsupported password format.");
816: }
817:
818: return password;
819: }
820:
821: private byte[] HexToByte(string hexString)
822: {
823: byte[] returnBytes = new byte[hexString.Length / 2];
824: for (int i = 0; i < returnBytes.Length; i++)
825: returnBytes[i] = Convert.ToByte(hexString.Substring(i * 2, 2), 16);
826: return returnBytes;
827: }
828: }
829: }


The T-SQL code:

  1:  USE DB
2: GO
3: SET ANSI_NULLS ON
4: GO
5: SET QUOTED_IDENTIFIER ON
6: GO
7: CREATE TABLE [dbo].[User](
8: [UserId] [uniqueidentifier] NOT NULL,
9: [Login] [nvarchar](150) COLLATE Polish_CI_AS NOT NULL,
10: [Password] [nvarchar](max) COLLATE Polish_CI_AS NOT NULL,
11: [Question] [nvarchar](255) COLLATE Polish_CI_AS NOT NULL,
12: [Answer] [nvarchar](255) COLLATE Polish_CI_AS NOT NULL,
13: [Email] [nvarchar](200) COLLATE Polish_CI_AS NOT NULL,
14: [RegisterDate] [datetime] NOT NULL,
15: CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
16: (
17: [UserId] ASC
18: )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
19: ) ON [PRIMARY]
20: go
21: create procedure ChangePassword(
22: @login nvarchar(150),
23: @password nvarchar(max)
24: )
25: as
26:
begin
27: SET NOCOUNT ON;
28:
29: Update [User] Set
30: Password=@password
31: Where
32: [Login]=@login
33:
34: end
35: go
36: create procedure GetUser(
37: @login nvarchar(150)
38: )
39: as
40:
begin
41: select UserId,
42: [Login],
43: Password,
44: Question,
45: Answer,
46: Email,
47: RegisterDate
48: from [User]
49: where
50:
[Login]=@login
51: end
52: go
53: create procedure CreateUser(
54: @UserId uniqueidentifier,
55: @Login nvarchar(150),
56: @Password nvarchar(MAX),
57: @Question nvarchar(255),
58: @Answer nvarchar(255),
59: @Email nvarchar(200)
60: )
61: as
62:
begin
63: SET NOCOUNT ON;
64:
65: insert into [User](
66: UserId,
67: [Login],
68: Password,
69: Question,
70: Answer,
71: Email,
72: RegisterDate
73: ) values(
74: @UserId,
75: @Login,
76: @Password,
77: @Question,
78: @Answer,
79: @Email,
80: getdate()
81: )
82: end
83: go
84: create procedure GetUserByUserId(
85: @UserId uniqueidentifier
86: )
87: as
88:
begin
89: select UserId,
90: [Login],
91: Password,
92: Question,
93: Answer,
94: Email,
95: RegisterDate
96: from [User]
97: where
98:
UserId=@UserId
99: end
100: go
101: create procedure GetUserNameByEmail(
102: @email nvarchar(200)
103: )
104: as
105:
begin
106: set @email=@email+'%'
107:
108: select UserId,
109: [Login],
110: Password,
111: Question,
112: Answer,
113: Email,
114: RegisterDate
115: from [User]
116: where
117:
Email like @email
118: order By [Login] Asc
119: end
120: go
121: create procedure ValidateUser(
122: @login nvarchar(150)
123: )
124: as
125:
begin
126: select Password
127: from [User]
128: where
129:
[Login]=@login
130: end
131: go
132: create procedure ChangePasswordQuestionAnswer(
133: @question nvarchar(255),
134: @answer nvarchar(255),
135: @userName nvarchar(150)
136: )
137: as
138:
begin
139:
140: SET NOCOUNT ON;
141:
142: Update [User] Set
143: Question=@question,
144: Answer=@answer
145: Where
146: [Login]=@userName
147:
148: end
149: go
150: create procedure DeleteUser(
151: @login nvarchar(150)
152: )
153: as
154: begin
155: Delete [User]
156: Where
157: [Login]=@login
158:
159: end
160: go
161: create procedure GetAllUsers
162: as
163:
begin
164: select UserId,
165: [Login],
166: Password,
167: Question,
168: Answer,
169: Email,
170: RegisterDate
171: from [User]
172: order by [Login] asc
173: end
174: go
175: create procedure GetPassword(
176: @login nvarchar(150)
177: )
178: as
179: begin
180: select Password,
181: Answer
182: from [User]
183: where [Login]=@login
184: end
185: go
186: create procedure GetPasswordAnswer(
187: @login nvarchar(150)
188: )
189: as
190: begin
191: Select Answer
192: From [User]
193: Where [Login]=@login
194: end
195: go
196: create procedure UpdateUser(
197: @email nvarchar(200),
198: @login nvarchar(150)
199: )
200: as
201: begin
202: Update [User]
203: Set Email=@email
204: Where [Login]=@login
205: end
206: go
207: create procedure FindUsersByName(
208: @login nvarchar(150)
209: )
210: as
211: begin
212: Set @login=@login+'%'
213:
214: select UserId,
215: [Login],
216: Password,
217: Question,
218: Answer,
219: Email,
220: RegisterDate
221: from [User]
222: where
223:
[Login] like @login
224: order by [Login] asc
225: end
226: go
227: create procedure FindUsersByEmail(
228: @email nvarchar(200)
229: )
230: as
231:
begin
232: Set @email=@email+'%'
233:
234: select UserId,
235: [Login],
236: Password,
237: Question,
238: Answer,
239: Email,
240: RegisterDate
241: from [User]
242: where
243: Email like @email
244: order by [Login] asc
245: end
246: go
247: create procedure UpdatePassword(
248: @password nvarchar(MAX),
249: @login nvarchar(150)
250: )
251: as
252: begin
253: Update [User] Set
254: [Password]=@password
255: Where [Login]=@login
256: end

Posted on Tuesday, November 10, 2009 2:42 PM ASP.NET , Linq | Back to top


Comments on this post: Writing custom MembershipProvider

Comments are closed.
Comments have been closed on this topic.
Copyright © Łukasz Kuryło | Powered by: GeeksWithBlogs.net