提取aspnet_Profile中自定义的用户属性（C#实现）

特殊需求

一个非常特别需求，需要提取aspnet_Profile中自定义的用户属性。这个特殊的需求是这样的:

提取用户的属性列表，我的机器只能访问开发库，没法访问生产库。DBA也比较忙，于是乎就把原始数据拿了过来，用程序进行分解。

Profile对象存储表（aspnet_Profile）

 

字段名	类型	属性	说明
UserId	uniqueidentifier	FK: aspnet_Users.UserId	用户ID
PropertyNames	ntext		属性名称
PropertyValuesString	ntext		字符串值
PropertyValuesBinary	image		二进制值

数据是这个样子的：

propertynames

NID:S:0:3:Company:S:3:3:PID:S:6:3:TrueName:S:9:2:

propertyvaluesstring

338com549李某

C#实现

  ///   <summary>
         ///  获取 aspnet_Profile中的属性值
         ///   </summary>
         ///   <returns></returns>
         private  DataTable GetProfileProperty()
        {
             string  sql  =   @" SELECT 
                            [username]
                          , [rolename]
                          , [propertynames]
                          , [propertyvaluesstring]
                      FROM [VUserProfile] " ;
            DataSet ds  =  DBUtility.getDataSet(sql);
 
             // NID，Company，PID，TrueName是自定义的用户属性
            DataTable dt  =   new  DataTable();
            DataColumn UserName  =   new  DataColumn( " UserName " ,  typeof ( string ));
            DataColumn RoleName  =   new  DataColumn( " RoleName " ,  typeof ( string ));
            DataColumn NID  =   new  DataColumn( " NID " ,  typeof ( string ));
            DataColumn Company  =   new  DataColumn( " Company " ,  typeof ( string ));
            DataColumn PID  =   new  DataColumn( " PID " ,  typeof ( string ));
            DataColumn TrueName  =   new  DataColumn( " TrueName " ,  typeof ( string ));
            dt.Columns.Add(UserName);
            dt.Columns.Add(RoleName);
            dt.Columns.Add(NID);
            dt.Columns.Add(Company);
            dt.Columns.Add(PID);
            dt.Columns.Add(TrueName);
             for  ( int  i  =   0 ; i  <  ds.Tables[ 0 ].Rows.Count; i ++ )
            {
                DataRow row  =  ds.Tables[ 0 ].Rows[i];
                 string  username  =  row[ " username " ].ToString();
                 string  rolename  =  row[ " rolename " ].ToString();
                 string  propertynames  =  row[ " propertynames " ].ToString().Replace( " :S: " ,  " * " );
                 string  propertyvaluesstring  =  row[ " propertyvaluesstring " ].ToString();

                DataRow dtrow  =  dt.NewRow();
                dtrow[ " UserName " ]  =  username;
                dtrow[ " RoleName " ]  =  rolename;
                 string [] p1  =  propertynames.Split( ' * ' );
                 for  ( int  j  =   0 ; j  <  p1.Length  -   1 ; j ++ )
                {
                     string [] tmp  =  p1[j].Split( ' : ' );
                     string [] tmp2  =  p1[j  +   1 ].Split( ' : ' );
                    string propertyname  =  tmp[tmp.Length  -   1 ];
                    string propertyvalue  =  propertyvaluesstring.Substring( int .Parse(tmp2[ 0 ]),  int .Parse(tmp2[ 1 ]));
                    dtrow[propertyname]  =  propertyvalue;
                }
                dt.Rows.Add(dtrow);
            }
             return  dt;
        }

效果

UserName	RoleName	NID	CompanyID	PID	TrueName
leex	客服	800	c1	201	李 X
leey	财务	801	c2	202	李 Y
leez	代理商	801	c3	203	李 Z