Implement a custom profile provider in ASP.NET MVC

Question

I knew a lot about implementing a custom profile provider in ASP.NET MVC. I have read many, many textbooks, but I cannot find where my problem is. This is pretty similar to Implementing a Profile Provider in ASP.NET MVC .

But I would like to create my own profile provider, so I wrote the following class that inherits from ProfileProvider :

 public class UserProfileProvider : ProfileProvider { #region Variables public override string ApplicationName { get; set; } public string ConnectionString { get; set; } public string UpdateProcedure { get; set; } public string GetProcedure { get; set; } #endregion #region Methods public UserProfileProvider() { } internal static string GetConnectionString(string specifiedConnectionString) { if (String.IsNullOrEmpty(specifiedConnectionString)) return null; // Check <connectionStrings> config section for this connection string ConnectionStringSettings connObj = ConfigurationManager.ConnectionStrings[specifiedConnectionString]; if (connObj != null) return connObj.ConnectionString; return null; } #endregion #region ProfileProvider Methods Implementation public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config) { if (config == null) throw new ArgumentNullException("config"); if (String.IsNullOrEmpty(name)) name = "UserProfileProvider"; if (String.IsNullOrEmpty(config["description"])) { config.Remove("description"); config.Add("description", "My user custom profile provider"); } base.Initialize(name, config); if (String.IsNullOrEmpty(config["connectionStringName"])) throw new ProviderException("connectionStringName not specified"); ConnectionString = GetConnectionString(config["connectionStringName"]); if (String.IsNullOrEmpty(ConnectionString)) throw new ProviderException("connectionStringName not specified"); if ((config["applicationName"] == null) || String.IsNullOrEmpty(config["applicationName"])) ApplicationName = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath; else ApplicationName = config["applicationName"]; if (ApplicationName.Length > 256) throw new ProviderException("Application name too long"); UpdateProcedure = config["updateUserProcedure"]; if (String.IsNullOrEmpty(UpdateProcedure)) throw new ProviderException("updateUserProcedure not specified"); GetProcedure = config["getUserProcedure"]; if (String.IsNullOrEmpty(GetProcedure)) throw new ProviderException("getUserProcedure not specified"); } public override System.Configuration.SettingsPropertyValueCollection GetPropertyValues(System.Configuration.SettingsContext context, System.Configuration.SettingsPropertyCollection collection) { SettingsPropertyValueCollection values = new SettingsPropertyValueCollection(); SqlConnection myConnection = new SqlConnection(ConnectionString); SqlCommand myCommand = new SqlCommand(GetProcedure, myConnection); myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.AddWithValue("@FirstName", (string)context["FirstName"]); try { myConnection.Open(); SqlDataReader reader = myCommand.ExecuteReader(CommandBehavior.SingleRow); reader.Read(); foreach (SettingsProperty property in collection) { SettingsPropertyValue value = new SettingsPropertyValue(property); if (reader.HasRows) { value.PropertyValue = reader[property.Name]; values.Add(value); } } } finally { myConnection.Close(); myCommand.Dispose(); } return values; } public override void SetPropertyValues(System.Configuration.SettingsContext context, System.Configuration.SettingsPropertyValueCollection collection) { SqlConnection myConnection = new SqlConnection(ConnectionString); SqlCommand myCommand = new SqlCommand(UpdateProcedure, myConnection); myCommand.CommandType = CommandType.StoredProcedure; foreach (SettingsPropertyValue value in collection) { myCommand.Parameters.AddWithValue(value.Name, value.PropertyValue); } myCommand.Parameters.AddWithValue("@FirstName", (string)context["FirstName"]); try { myConnection.Open(); myCommand.ExecuteNonQuery(); } finally { myConnection.Close(); myCommand.Dispose(); } } 

Here is my CreateProfile action in my controller:

 [AcceptVerbs(HttpVerbs.Post)] public ActionResult CreateProfile(string Username, string Password, string FirstName, string LastName) { MembershipCreateStatus IsCreated = MembershipCreateStatus.ProviderError; MembershipUser user = null; user = Membership.CreateUser(Username, Password, " [e-mail] ", "Q", "A", true, out IsCreated); if (IsCreated == MembershipCreateStatus.Success && user != null) { ProfileCommon profile = (ProfileCommon)ProfileBase.Create(user.UserName); profile.FirstName = FirstName; profile.LastName = LastName; profile.Save(); } return RedirectToAction("Index", "Home"); } 

My procedure usp_GetUserProcedure is nothing special:

 ALTER PROCEDURE [dbo].[usp_GetUserProcedure] -- Add the parameters for the stored procedure here @FirstName varchar(50) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT * FROM dbo.Users WHERE FirstName = @FirstName END 

And my Web.Config file:

 <profile enabled="true" automaticSaveEnabled="false" defaultProvider="UserProfileProvider" inherits="Test.Models.ProfileCommon"> <providers> <clear/> <add name="UserProfileProvider" type="Test.Controllers.UserProfileProvider" connectionStringName="ApplicationServices" applicationName="UserProfileProvider" getUserProcedure="usp_GetUserProcedure" updateUserProcedure="usp_UpdateUserProcedure"/> </providers> </profile> 

But I always get this exception:

The procedure or function 'usp_GetUserProcedure' expects the parameter '@FirstName', which was not provided.

Any thoughts on what I can do wrong?

Answer 1

The most likely reason is that

 myCommand.Parameters.AddWithValue("@FirstName", (string)context["FirstName"]); 

(string)context["FirstName"] is a null value. Even if you pass the sproc parameter, if the value is NULL on the required parameter, you will see this error. SQL Server (efficiently) does not distinguish between a parameter that is not passed and one passed with a null value.

You see an SQL error. This is not MVC related, and MVC is not a problem. Determine if null is a valid context["FirstName"] value, and if so, modify your function to accept null values. If not, find out why context["FirstName"] is null.

In addition, I do not think that this line will correctly add your parameter names (with the prefix "@").

myCommand.Parameters.AddWithValue (value .Name, value.PropertyValue);

Also, since this is MVC, make sure you have a control named FirstName in the form posting:

 public ActionResult CreateProfile(string Username, string Password, string FirstName, string LastName) 

It reads fields based on name, not identifier

Answer 2

Yes, this is because I use a class for my properties, ProfileCommon, which inherits from ProfileBase.

 public class ProfileCommon : ProfileBase { public virtual string Label { get { return ((string)(this.GetPropertyValue("Label"))); } set { this.SetPropertyValue("Label", value); } } public virtual string FirstName { get { return ((string)(this.GetPropertyValue("FirstName"))); } set { this.SetPropertyValue("FirstName", value); } } public virtual string LastName { get { return ((string)(this.GetPropertyValue("LastName"))); } set { this.SetPropertyValue("LastName", value); } } public virtual ProfileCommon GetProfile(string username) { return Create(username) as ProfileCommon; } } 

You can see that I am using this class in the Web.Config file:

 <profile enabled="true" automaticSaveEnabled="false" defaultProvider="UserProfileProvider" inherits="Test.Models.ProfileCommon"> [...] 

And with ASP.Net MVC, if I wrote my properties in Web.Config, I can no longer access them using Profile.PropertyName. Maybe there is a way, but I do not find examples.