Some of my colleagues are often reluctant to use ASP.NET 2.0 profile provider to store profile data for their web applications. The main reason for this is the fact that the default SqlProfileProvider that ships with ASP.NET 2.0 “blobicizes” Profile data using string, XML or binary serialization prior to storing information in SQL Server. This obviously puts a rather large overhead when you need to query your profile data.
What most developers aren’t aware though is that you can build your own custom profile provider to store Profile data “in the clear” in the database so that the data is available for querying and use in stored procedures.
As a matter of fact Microsoft provides an SqlTableProfileProvider sample implementation which stores each Profile property in a separate database column without serializing it, which means that the Profile property can be easily queried (of course the profile property type needs to be compatible with the target database column).
There is also a second sample provider, SqlStoredProcedureProfileProvider, which maps each Profile property to a parameter on a custom stored procedure. Like the table based provider, this provider expects that each Profile property is of a type that is compatible with its corresponding stored procedure parameter. The powerful aspect of the stored procedure based provider is that other than the requirement to implement some stored procedures with a specific set of parameters, you can implement whatever business logic you need in the stored procedures to map the Profile data to your own database schema and database logic.
You can learn more about these profile providers here.
I was reluctant to using it also, for exactly this reason. Perhaps we should reconsider now.