扩展字段主要是针对那些有不固定列的表，而且这些列不是系统运行所必须的。当一个系统或产品已经上线后，不需要修改原来的代码就可以满足客户增加字段的需求。
例如产品表在用户A里需要用到产地这个字段，在用户B里需要条形码这个字段，而这两个或更多的字段在原来的系统设计时并没考虑进去，这时就需要扩展字段。
首先需要建一张扩展字段映射表
View Code
CREATE TABLE [dbo].[ExColumnMapping](
[RowID] [int] IDENTITY(1,1) NOT NULL,
[TableName] [nvarchar](50) NULL,
[ColumnName] [nvarchar](50) NULL,
[MappingName] [nvarchar](50) NULL,
[InputType] [nvarchar](50) NULL,
[Remark] [nvarchar](200) NULL,
[EnableStatus] [int] NULL
CONSTRAINT [PK_ExColumnMapping] PRIMARY KEY CLUSTERED
(
[RowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
2条存储过程
View Code
create PROCEDURE [dbo].[GetNewMappingName]Create PROCEDURE [dbo].[IsExColExist]
@TableName nvarchar(50)
AS
BEGIN
SELECT top 1 name
FROM syscolumns
WHERE substring(name,1,3)='Col' and
id = (SELECT top 1 id
FROM sysobjects
WHERE name = @TableName order by id)
and name not in
(select MappingName from ExColumnMapping where TableName = @TableName)
END
@TableName nvarchar(50),
@ColumnName nvarchar(50)
AS
BEGIN
select * from ExColumnMapping
where TableName = @TableName
and ColumnName = @ColumnName
END
同时在产品表增加一些备用列 Col1，Col2，Col3.。。。。。。。。
建一个管理扩展字段表的页面：
View Code
<table class="innerform">
<tr>
<th width="20%">
数据表：
</th>
<td>
<select id="sltTableName" runat="server" >
<option value="Customer">客户表</option>
</select>
<asp:Label ID="txtTableName" runat="server" ReadOnly="true" MaxLength="20"></asp:Label>
<asp:Label ID="Label1" runat="server" Text="*" ForeColor="Red"></asp:Label>
</td>
</tr>
<tr>
<th>
字段名：
</th>
<td>
<input id="txtColName" type="text" runat="Server" maxlength="20" />
<asp:Label ID="lbCustName" runat="server" Text="*" ForeColor="Red"></asp:Label>
</td>
</tr>
<tr>
<th>
输入类型：
</th>
<td>
<select id="sltInputType" name="D3" runat="Server">
<option value="文本框">文本框</option>
<option value="下拉框">下拉框</option>
<option value="日历">日历</option>
</select>
</td>
<td>
<input type="button" id="btnAddSourceData" onclick="SetSourceData()" value="源数据" />
</td>
<td>
<input type="hidden" runat="server" id="hdSourceData" value="" />
</td>
</tr>
<tr>
<th>
可见状态：
</th>
<td>
<select id="sltEnable" name="D3" runat="Server">
<option value="1">可见</option>
<option value="2">不可见</option>
</select>
</td>
</tr>
<tr>
<th>
备注：
</th>
<td colspan="3">
<textarea id="taRemark" name="S1" rows="5" runat="Server" maxlength="200" ></textarea>
</td>
</tr>
</table>
在产品管理页面加上<div id="divExCol" style="100%" runat = "server"></div>
View Code
/// <summary>
/// 动态生成扩展字段相关控件
/// </summary>
public static void AddExControl(HtmlGenericControl divExCol,List<ExColumnMappingItem> exCols)
{
foreach (ExColumnMappingItem exColItem in exCols)
{
Label lblExCol = new Label();
lblExCol.Width = 130;
lblExCol.Style.Add(HtmlTextWriterStyle.TextAlign, "right");
lblExCol.Text = exColItem.ColumnName + "：";
divExCol.Controls.Add(lblExCol);
switch (exColItem.InputType)
{
case "下拉框":
DropDownList ddlExCol = new DropDownList();
ddlExCol.ID = exColItem.MappingName;
ddlExCol.Width = 300;
ddlExCol.Items.Add("");
foreach (string data in exColItem.SourceData.Split(','))
{
ddlExCol.Items.Add(data);
}
divExCol.Controls.Add(ddlExCol);
break;
case "日历":
TextBox txtDateExCol = new TextBox();
txtDateExCol.ID = exColItem.MappingName;
txtDateExCol.Width = 260;
divExCol.Controls.Add(txtDateExCol);
Button btnExCol = new Button();
btnExCol.Text ="...";
btnExCol.Height = 21;
btnExCol.Width = 40;
btnExCol.OnClientClick = "popUpCalendar(this, " + txtDateExCol.ClientID + ", 'mm/dd/yyyy',-1,-1,true);return false;";
divExCol.Controls.Add(btnExCol);
break;
default:
TextBox txtExCol = new TextBox();
txtExCol.ID = exColItem.MappingName;
txtExCol.Width = 300;
divExCol.Controls.Add(txtExCol);
break;
}
//换行
divExCol.Controls.Add(new LiteralControl("<br>"));
}
divExCol.DataBind();
}
View Code
/// <summary>
/// 将原有数据绑定到扩展字段控件
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <param name="exCols"></param>
/// <param name="divExCol"></param>
public static void BindExColData<T>(T t, List<ExColumnMappingItem> exCols, HtmlGenericControl divExCol)
{
foreach (ExColumnMappingItem exColItem in exCols)
{
object exColValue = t.GetType().GetProperty(exColItem.MappingName).GetValue(t, null);
string oldValue = exColValue == null ? "" : exColValue.ToString();
switch (exColItem.InputType)
{
case "下拉框":
DropDownList ddlExCol = (DropDownList)divExCol.FindControl(exColItem.MappingName);
ddlExCol.Text = oldValue;
break;
default:
TextBox txtExCol = (TextBox)divExCol.FindControl(exColItem.MappingName);
txtExCol.Text = oldValue;
break;
}
}
}
View Code
/// <summary>
/// 从文本框获取扩展字段值
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <param name="exCols"></param>
/// <param name="divExCol"></param>
public static void GetExColData<T>(T t, List<ExColumnMappingItem> exCols, HtmlGenericControl divExCol)
{
foreach (ExColumnMappingItem exColItem in exCols)
{
switch (exColItem.InputType)
{
case "下拉框":
DropDownList ddlExCol = (DropDownList)divExCol.FindControl(exColItem.MappingName);
t.GetType().GetProperty(exColItem.MappingName).SetValue(t, ddlExCol.Text, null);
break;
default:
TextBox txtExCol = (TextBox)divExCol.FindControl(exColItem.MappingName);
t.GetType().GetProperty(exColItem.MappingName).SetValue(t, txtExCol.Text, null);
break;
}
}
}