特殊需求
一个非常特别需求,需要提取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李某 |
///
<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;
}
/// 获取 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 |