asp.net 2.0 自定义 基于 Table 的 Profile Provide

最近公司里要求统计用户注册的省市什么的乱七八糟的信息,asp.net 2.0自带的那个  ProfileProvider满足不了。从http://www.asp.net/downloads/sandbox/table-profile-provider-samples/下载了他的源码，觉的那个东西太啰嗦了，不过收益非浅。自己在此基础上又做了一些改进。源码如下：


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Profile;
using System.Configuration;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
namespace yxyProviders
{
    public class yxyProfileProvider : ProfileProvider
    {
        string _appName;
        string _cnnName;
        public override void Initialize(string name, NameValueCollection config)
        {
            if (config == null)
                throw new ArgumentNullException("config");
            if (String.IsNullOrEmpty(name))
                name = "yxyProfileProvider";
            _cnnName = config["connectionStringName"];
            if (String.IsNullOrEmpty(_cnnName))
                throw new ConfigurationErrorsException("connectionStringName not specified");
            string _appName = config["applicationName"];
            if (string.IsNullOrEmpty(_appName))
                throw new ConfigurationErrorsException("applicationName not specified");
            base.Initialize(name, config);
        }

        public override int DeleteInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate)
        {
            throw new NotImplementedException();
        }

        public override int DeleteProfiles(string[] usernames)
        {
             using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings[_cnnName].ConnectionString))
             using (SqlCommand cmd = cnn.CreateCommand())
             {
                 cnn.Open();
                 cmd.CommandText = "app_DeleteUserProfile";
                 cmd.CommandType = CommandType.StoredProcedure;
                 cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 255);
                 foreach (string s in usernames)
                 {
                     cmd.Parameters[0].Value = s;
                     cmd.ExecuteNonQuery();
                 }
                 cnn.Close();
             }
             return 0;
        }

        public override int DeleteProfiles(ProfileInfoCollection profiles)
        {
           return  0；
        }

        public override ProfileInfoCollection FindInactiveProfilesByUserName(ProfileAuthenticationOption authenticationOption, string usernameToMatch, DateTime userInactiveSinceDate, int pageIndex, int pageSize, out int totalRecords)
        {
            throw new NotImplementedException();
        }

        public override ProfileInfoCollection FindProfilesByUserName(ProfileAuthenticationOption authenticationOption, string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            throw new NotImplementedException();
        }

        public override ProfileInfoCollection GetAllInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate, int pageIndex, int pageSize, out int totalRecords)
        {
            throw new NotImplementedException();
        }

        public override ProfileInfoCollection GetAllProfiles(ProfileAuthenticationOption authenticationOption, int pageIndex, int pageSize, out int totalRecords)
        {
            throw new NotSupportedException();
        }

        public override int GetNumberOfInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate)
        {
            throw new NotImplementedException();
        }

        public override string ApplicationName
        {
            get
            {
                return _appName;
            }
            set
            {
                _appName = value;
            }
        }

        public override SettingsPropertyValueCollection GetPropertyValues(SettingsContext context, SettingsPropertyCollection collection)
        {
            SettingsPropertyValueCollection svc = new SettingsPropertyValueCollection();
            foreach (SettingsProperty prop in collection)
            {
                SettingsPropertyValue value = new SettingsPropertyValue(prop);
                value.PropertyValue = prop.DefaultValue;
                svc.Add(value);
            }
            if (collection == null || collection.Count < 1 || context == null)
                return svc;
            string name = (string)context["UserName"];
            bool userIsAuthenticated = (bool)context["IsAuthenticated"];

            using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings[_cnnName].ConnectionString))
            using (SqlCommand cmd = cnn.CreateCommand())
            {
                cnn.Open();
                cmd.CommandText = "app_GetUserProfile";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 255);
                cmd.Parameters[0].Value = name;
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    if (dr.Read())
                    {
                        foreach (SettingsProperty prop in collection)
                        {
                            string persistenceData = prop.Attributes["CustomProviderData"] as string;
                            string[] chunk = persistenceData.Split(new char[] { ';' });
                            svc[prop.Name].PropertyValue = dr[chunk[3]];
                        }
                    }
                }
                cnn.Close();
            }
            return svc;
        }

        public override void SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection collection)
        {
            string objValue = (string)context["UserName"];
            bool userIsAuthenticated = (bool)context["IsAuthenticated"];

            using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings[_cnnName].ConnectionString))
            using (SqlCommand cmd = cnn.CreateCommand())
            {
                cnn.Open();
                cmd.CommandText = "app_SaveUserProfile";
                cmd.CommandType = CommandType.StoredProcedure;
                 foreach (SettingsPropertyValue pi in collection)
                {
                    string cpdata = pi.Property.Attributes["CustomProviderData"] as string;
                    string[] args = cpdata.Split(new char[] { ';' });
                    SqlDbType dbtype = (SqlDbType)Enum.Parse(typeof(SqlDbType), args[1], true);
                    int size = int.Parse(args[2]);
                    SqlParameter pa = new SqlParameter(args[0], dbtype, size);
                    pa.Value = pi.PropertyValue;
                    cmd.Parameters.Add(pa);
                }
                cmd.ExecuteNonQuery();
                cnn.Close();
            }


        }
    }
}

-------------------------web.config人配置如下：
<connectionStrings>
        <add name="wwwUserConn" connectionString="Data Source=dhz-pc/db2005;Initial Catalog=wwwUser;Integrated Security=True" providerName="System.Data.SqlClient"/>
    </connectionStrings>
     
        <profile enabled="true" defaultProvider="yxyTableProfile">
            <providers>
                <clear/>
                <add name="yxyTableProfile"  type="yxyProviders.yxyProfileProvider,yxyProviders" connectionStringName="wwwUserConn"     applicationName="wwwUserConn"
                />
            </providers>
            <properties>
                <add name="UserName" customProviderData="@UserName;nvarchar;255;UserName"  type="string" defaultValue=""/>
                <add name="Age" customProviderData="@Age;nvarchar;255;Age" type="int"   defaultValue="0"/>
                <add name="Province" customProviderData="@Province;nvarchar;255;Province"   type="string" defaultValue=""/>
            </properties>
        </profile>
         ----------------------------------------用法----------------
  MembershipUser mu = Membership.GetUser();
        if (mu != null)
        {
            ProfileCommon pfc = Profile.GetProfile(mu.UserName);
            pfc.Age =30;
            pfc.UserName = mu.UserName;
            pfc.Province = "HuBei";
            pfc.Save();
        }
----------------------------------补充；
这个实现其实是不完整的。


像 匿名用户是就有问题。不过，在解决的时候，处理匿名时我将他的用户名设为 一个临时的GUID并存到 客户机的COOKIE中。DB那里有个JOB调度，每天晚上自动删除那些超过一定时间的用户PROFILE数据。

--------------------SqlScript

create table app_UserProfile(
  UserName  nvarchar(255),
  Age      int ,
  Province  nvarchar(255)
)
go
create proc app_GetUserProfile
 @UserName nvarchar(255)
as
begin
   Select UserName,Age,Province from app_UserProfile  where UserName=@UserName
end
go
create proc app_SaveUserProfile
  @UserName nvarchar(255),
  @Age int ,
  @Province nvarchar(255)
as
begin
  if exists(select * from app_UserProfile  where UserName=@UserName)
  begin
      update app_UserProfile
        set  Age=@Age,Province=@Province
      where UserName=@UserName
  end
  else
  begin
      insert into app_UserProfile(UserName,Age,Province)
      values(@UserName,@Age,@Province)
  end
end
go
create proc app_DeleteUserProfile
  @UserName nvarchar(255)
as
begin
      delete app_UserProfile where UserName=@UserName
end