public static string GetSerializedString(string tableName)
{
var builder = new StringBuilder();
var list = GetTableMetadataInfoList(tableName);
var sortedlist = new SortedList();
foreach (var metadataInfo in list)
{
if (metadataInfo.IsSystem == false)
{
/*
* AttributeName,
* DataType,
* DataLength,
* CanBeNull,
* DBDefaultValue
* */
string serialize =
$"AttributeName:{metadataInfo.AttributeName};DataType:{EDataTypeUtils.GetValue(metadataInfo.DataType)};DataLength={metadataInfo.DataLength}";
sortedlist.Add(metadataInfo.AttributeName, serialize);
}
}
foreach (string attributeName in sortedlist.Keys)
{
builder.Append(sortedlist[attributeName]);
}
return(builder.ToString());
}
public void Page_Load(object sender, EventArgs e)
{
if (IsForbidden)
{
return;
}
PageUtils.CheckRequestParameter("TableName", "TableType");
_tableName = Body.GetQueryString("TableName");
_tableType = EAuxiliaryTableTypeUtils.GetEnumType(Body.GetQueryString("TableType"));
if (!IsPostBack)
{
EDataTypeUtils.AddListItemsToAuxiliaryTable(DataType);
if (Body.IsQueryExists("TableMetadataID"))
{
var tableMetadataId = Body.GetQueryInt("TableMetadataID");
var info = BaiRongDataProvider.TableMetadataDao.GetTableMetadataInfo(tableMetadataId);
if (info != null)
{
AttributeName.Text = info.AttributeName;
AttributeName.Enabled = false;
ControlUtils.SelectListItemsIgnoreCase(DataType, info.DataType.ToString());
DataLength.Text = info.DataLength.ToString();
}
}
}
}
public static string GetTableMetadataDataType(string tableName, string attributeName)
{
var metadataInfo = GetTableMetadataInfo(tableName, attributeName);
if (metadataInfo != null)
{
return(EDataTypeUtils.GetTextByAuxiliaryTable(metadataInfo.DataType, metadataInfo.DataLength));
}
return(string.Empty);
}
public static string GetMySqlColumnSqlString(EDataType dataType, string attributeName, int length)
{
string retval;
var sqlDbType = EDataTypeUtils.ToSqlDbType(dataType);
switch (sqlDbType)
{
case SqlDbType.Char:
retval = $"{attributeName} VARCHAR({length})";
break;
case SqlDbType.DateTime:
retval = $"{attributeName} DATETIME";
break;
case SqlDbType.Decimal:
retval = $"{attributeName} DECIMAL(18, 2)";
break;
case SqlDbType.Float:
retval = $"{attributeName} FLOAT(18, 2)";
break;
case SqlDbType.Int:
retval = $"{attributeName} INT";
break;
case SqlDbType.NChar:
retval = $"{attributeName} VARCHAR({length})";
break;
case SqlDbType.NText:
retval = $"{attributeName} LONGTEXT";
break;
case SqlDbType.NVarChar:
retval = $"{attributeName} VARCHAR({length})";
break;
case SqlDbType.Text:
retval = $"{attributeName} LONGTEXT";
break;
case SqlDbType.VarChar:
retval = $"{attributeName} VARCHAR({length})";
break;
default:
retval = $"{attributeName} VARCHAR({length})";
break;
}
return(retval);
}
private static AtomEntry GetAtomEntry(TableMetadataInfo metaInfo)
{
var entry = AtomUtility.GetEmptyEntry();
AtomUtility.AddDcElement(entry.AdditionalElements, "TableMetadataID", metaInfo.TableMetadataId.ToString());
AtomUtility.AddDcElement(entry.AdditionalElements, "AuxiliaryTableENName", metaInfo.AuxiliaryTableEnName);
AtomUtility.AddDcElement(entry.AdditionalElements, "AttributeName", metaInfo.AttributeName);
AtomUtility.AddDcElement(entry.AdditionalElements, "DataType", EDataTypeUtils.GetValue(metaInfo.DataType));
AtomUtility.AddDcElement(entry.AdditionalElements, "DataLength", metaInfo.DataLength.ToString());
AtomUtility.AddDcElement(entry.AdditionalElements, "Taxis", metaInfo.Taxis.ToString());
AtomUtility.AddDcElement(entry.AdditionalElements, "IsSystem", metaInfo.IsSystem.ToString());
return(entry);
}
public static IDbDataParameter GetIDbDataParameter(string parameterName, EDataType dataType, int size)
{
IDbDataParameter parameter;
if (WebConfigUtils.IsMySql)
{
parameter = new MySqlParameter(parameterName, EDataTypeUtils.ToMySqlDbType(dataType), size);
}
else
{
parameter = new SqlParameter(parameterName, EDataTypeUtils.ToSqlDbType(dataType), size);
}
return(parameter);
}
private void SetColumns(NameValueCollection columnsMap)
{
Columns.Items.Clear();
ColumnsToMatch.Items.Clear();
var tableColumnInfoList = BaiRongDataProvider.TableStructureDao.GetTableColumnInfoList(GetDatabaseConnectionString(), DatabaseName.SelectedValue, RelatedTableName.SelectedValue);
var columnToMatchArrayList = new ArrayList();
foreach (var tableColumnInfo in tableColumnInfoList)
{
string text =
$"{tableColumnInfo.ColumnName}({EDataTypeUtils.GetValue(tableColumnInfo.DataType)} {tableColumnInfo.Length})";
var value = tableColumnInfo.ColumnName.ToLower();
var columnToMatch = columnsMap[value];
if (!string.IsNullOrEmpty(columnToMatch))
{
var tableMetadataInfoToMatch = TableManager.GetTableMetadataInfo(TableNameToMatch.Text, columnToMatch);
if (tableMetadataInfoToMatch != null)
{
columnToMatchArrayList.Add(columnToMatch);
text += " -> " +
$"{tableMetadataInfoToMatch.AttributeName}({EDataTypeUtils.GetValue(tableMetadataInfoToMatch.DataType)} {tableMetadataInfoToMatch.DataLength})";
value += "&" + columnToMatch;
}
}
Columns.Items.Add(new ListItem(text, value));
}
var tableMetadataInfoList = TableManager.GetTableMetadataInfoList(TableNameToMatch.Text);
foreach (var tableMetadataInfo in tableMetadataInfoList)
{
var value = tableMetadataInfo.AttributeName.ToLower();
if (!columnToMatchArrayList.Contains(tableMetadataInfo.AttributeName))
{
string text =
$"{tableMetadataInfo.AttributeName}({EDataTypeUtils.GetValue(tableMetadataInfo.DataType)} {tableMetadataInfo.DataLength})";
ColumnsToMatch.Items.Add(new ListItem(text, value));
}
}
SaveColumnsMap(columnsMap);
}
public void RelatedTable_Changed(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(RelatedTableName.SelectedValue))
{
RelatedIdentity.Items.Clear();
RelatedOrderBy.Items.Clear();
}
else
{
var tableColumnInfoList = BaiRongDataProvider.TableStructureDao.GetTableColumnInfoList(GetDatabaseConnectionString(), DatabaseName.SelectedValue, RelatedTableName.SelectedValue);
RelatedIdentity.Items.Clear();
RelatedOrderBy.Items.Clear();
var item = new ListItem("请选择主键字段名称", string.Empty);
RelatedIdentity.Items.Add(item);
item = new ListItem("请选择排序字段名称", string.Empty);
RelatedOrderBy.Items.Add(item);
foreach (var tableColumnInfo in tableColumnInfoList)
{
item = new ListItem(
$"{tableColumnInfo.ColumnName}({EDataTypeUtils.GetValue(tableColumnInfo.DataType)} {tableColumnInfo.Length})", tableColumnInfo.ColumnName);
if (StringUtils.EqualsIgnoreCase(tableColumnInfo.ColumnName, RelatedIdentityHidden.Value))
{
item.Selected = true;
}
RelatedIdentity.Items.Add(item);
}
foreach (var tableColumnInfo in tableColumnInfoList)
{
item = new ListItem(
$"{tableColumnInfo.ColumnName}({EDataTypeUtils.GetValue(tableColumnInfo.DataType)} {tableColumnInfo.Length})", tableColumnInfo.ColumnName);
if (StringUtils.EqualsIgnoreCase(tableColumnInfo.ColumnName, RelatedOrderByHidden.Value))
{
item.Selected = true;
}
RelatedOrderBy.Items.Add(item);
}
}
}
public void Update(TableMetadataInfo info)
{
var isSqlChanged = true;
var originalInfo = GetTableMetadataInfo(info.TableMetadataId);
if (originalInfo != null)
{
if (info.AuxiliaryTableEnName == originalInfo.AuxiliaryTableEnName &&
info.AttributeName == originalInfo.AttributeName &&
info.DataType == originalInfo.DataType &&
info.DataLength == originalInfo.DataLength &&
info.Taxis == originalInfo.Taxis)
{
isSqlChanged = false;
}
}
var updateParms = new IDataParameter[]
{
GetParameter(ParmAuxiliaryTableEnname, EDataType.VarChar, 50, info.AuxiliaryTableEnName),
GetParameter(ParmAttributeName, EDataType.VarChar, 50, info.AttributeName),
GetParameter(ParmDataType, EDataType.VarChar, 50, EDataTypeUtils.GetValue(info.DataType)),
GetParameter(ParmDataLength, EDataType.Integer, info.DataLength),
GetParameter(ParmIsSystem, EDataType.VarChar, 18, info.IsSystem.ToString()),
GetParameter(ParmTableMetadataId, EDataType.Integer, info.TableMetadataId)
};
using (var conn = GetConnection())
{
conn.Open();
ExecuteNonQuery(conn, SqlUpdateTableMetadata, updateParms);
if (isSqlChanged)
{
BaiRongDataProvider.TableCollectionDao.UpdateIsChangedAfterCreatedInDb(true, info.AuxiliaryTableEnName);
}
TableManager.IsChanged = true;
}
}
internal void InsertWithTransaction(TableMetadataInfo info, EAuxiliaryTableType tableType, int taxis, IDbTransaction trans)
{
var sqlString = "INSERT INTO bairong_TableMetadata (AuxiliaryTableENName, AttributeName, DataType, DataLength, Taxis, IsSystem) VALUES (@AuxiliaryTableENName, @AttributeName, @DataType, @DataLength, @Taxis, @IsSystem)";
var insertParms = new IDataParameter[]
{
GetParameter(ParmAuxiliaryTableEnname, EDataType.VarChar, 50, info.AuxiliaryTableEnName),
GetParameter(ParmAttributeName, EDataType.VarChar, 50, info.AttributeName),
GetParameter(ParmDataType, EDataType.VarChar, 50, EDataTypeUtils.GetValue(info.DataType)),
GetParameter(ParmDataLength, EDataType.Integer, info.DataLength),
GetParameter(ParmTaxis, EDataType.Integer, taxis),
GetParameter(ParmIsSystem, EDataType.VarChar, 18, info.IsSystem.ToString())
};
ExecuteNonQuery(trans, sqlString, insertParms);
if (info.StyleInfo != null)
{
info.StyleInfo.TableName = info.AuxiliaryTableEnName;
info.StyleInfo.AttributeName = info.AttributeName;
BaiRongDataProvider.TableStyleDao.InsertWithTransaction(info.StyleInfo, EAuxiliaryTableTypeUtils.GetTableStyle(tableType), trans);
TableStyleManager.IsChanged = true;
}
TableManager.IsChanged = true;
}
public void Insert(TableMetadataInfo info)
{
var sqlString = "INSERT INTO bairong_TableMetadata (AuxiliaryTableENName, AttributeName, DataType, DataLength, Taxis, IsSystem) VALUES (@AuxiliaryTableENName, @AttributeName, @DataType, @DataLength, @Taxis, @IsSystem)";
var insertParms = new IDataParameter[]
{
GetParameter(ParmAuxiliaryTableEnname, EDataType.VarChar, 50, info.AuxiliaryTableEnName),
GetParameter(ParmAttributeName, EDataType.VarChar, 50, info.AttributeName),
GetParameter(ParmDataType, EDataType.VarChar, 50, EDataTypeUtils.GetValue(info.DataType)),
GetParameter(ParmDataLength, EDataType.Integer, info.DataLength),
GetParameter(ParmTaxis, EDataType.Integer, GetMaxTaxis(info.AuxiliaryTableEnName) + 1),
GetParameter(ParmIsSystem, EDataType.VarChar, 18, info.IsSystem.ToString())
};
using (var conn = GetConnection())
{
conn.Open();
ExecuteNonQuery(conn, sqlString, insertParms);
BaiRongDataProvider.TableCollectionDao.UpdateAttributeNum(info.AuxiliaryTableEnName);
BaiRongDataProvider.TableCollectionDao.UpdateIsChangedAfterCreatedInDb(true, info.AuxiliaryTableEnName);
TableManager.IsChanged = true;
}
}
public override void Submit_OnClick(object sender, EventArgs e)
{
var isChanged = false;
var attributeNameList = TranslateUtils.StringCollectionToStringList(Request.Form["attributeName"]);
var dataTypeList = TranslateUtils.StringCollectionToStringList(Request.Form["dataType"]);
var dataLengthList = TranslateUtils.StringCollectionToStringList(Request.Form["dataLength"]);
for (var i = 0; i < attributeNameList.Count; i++)
{
if (dataTypeList.Count < attributeNameList.Count)
{
dataTypeList.Add(string.Empty);
}
if (dataLengthList.Count < attributeNameList.Count)
{
dataLengthList.Add(string.Empty);
}
}
var tableStyle = EAuxiliaryTableTypeUtils.GetTableStyle(_tableType);
var attributeNameArrayList = TableManager.GetAttributeNameList(tableStyle, _tableName, true);
attributeNameArrayList.AddRange(TableManager.GetHiddenAttributeNameList(tableStyle));
for (var i = 0; i < attributeNameList.Count; i++)
{
var attributeName = attributeNameList[i];
var dataType = dataTypeList[i];
var dataLength = dataLengthList[i];
if (attributeNameArrayList.IndexOf(attributeName.Trim().ToLower()) != -1)
{
FailMessage("字段添加失败，字段名已存在！");
}
else if (!SqlUtils.IsAttributeNameCompliant(attributeName))
{
FailMessage("字段名不符合系统要求！");
}
else
{
var info = new TableMetadataInfo
{
AuxiliaryTableEnName = _tableName,
AttributeName = attributeName,
DataType = EDataTypeUtils.GetEnumType(dataType)
};
var hashtable = new Hashtable
{
[EDataType.DateTime] = new[] { "8", "false" },
[EDataType.Integer] = new[] { "4", "false" },
[EDataType.NChar] = new[] { "50", "true" },
[EDataType.NText] = new[] { "16", "false" },
[EDataType.NVarChar] = new[] { "255", "true" }
};
var strArr = (string[])hashtable[EDataTypeUtils.GetEnumType(dataType)];
if (strArr[1].Equals("false"))
{
dataLength = strArr[0];
}
if (string.IsNullOrEmpty(dataLength))
{
dataLength = strArr[0];
}
info.DataLength = int.Parse(dataLength);
if (info.DataType == EDataType.NVarChar || info.DataType == EDataType.NChar)
{
var maxLength = SqlUtils.GetMaxLengthForNVarChar();
if (info.DataLength <= 0 || info.DataLength > maxLength)
{
FailMessage($"字段修改失败，数据长度的值必须位于 1 和 {maxLength} 之间");
return;
}
}
info.IsSystem = false;
try
{
BaiRongDataProvider.TableMetadataDao.Insert(info);
Body.AddAdminLog("添加辅助表字段",
$"辅助表:{_tableName},字段名:{info.AttributeName}");
isChanged = true;
}
catch (Exception ex)
{
FailMessage(ex, ex.Message);
}
}
}
if (isChanged)
{
PageUtils.CloseModalPage(Page);
}
}
/// <summary>
/// 将频道模板中的辅助表导入发布系统中，返回修改了的表名对照
/// 在导入辅助表的同时检查发布系统辅助表并替换对应表
/// </summary>
public NameValueCollection ImportAuxiliaryTables(int publishmentSystemId, bool isUserTables)
{
if (!DirectoryUtils.IsDirectoryExists(_directoryPath))
{
return(null);
}
var publishmentSystemInfo = PublishmentSystemManager.GetPublishmentSystemInfo(publishmentSystemId);
var nameValueCollection = new NameValueCollection();
var tableNamePrefix = publishmentSystemInfo.PublishmentSystemDir + "_";
var filePaths = DirectoryUtils.GetFilePaths(_directoryPath);
foreach (var filePath in filePaths)
{
var feed = AtomFeed.Load(FileUtils.GetFileStreamReadOnly(filePath));
var tableName = AtomUtility.GetDcElementContent(feed.AdditionalElements, "TableENName");
var tableType = EAuxiliaryTableTypeUtils.GetEnumType(AtomUtility.GetDcElementContent(feed.AdditionalElements, "AuxiliaryTableType"));
if (!isUserTables)
{
nameValueCollection[tableName] = NodeManager.GetTableName(publishmentSystemInfo, tableType);
continue;
}
var tableCnName = AtomUtility.GetDcElementContent(feed.AdditionalElements, "TableCNName");
var serializedString = AtomUtility.GetDcElementContent(feed.AdditionalElements, "SerializedString");
var tableNameToInsert = string.Empty;//需要增加的表名，空代表不需要添加辅助表
var tableInfo = BaiRongDataProvider.TableCollectionDao.GetAuxiliaryTableInfo(tableName);
if (tableInfo == null)//如果当前系统无此表名
{
tableNameToInsert = tableName;
}
else
{
var serializedStringForExistTable = TableManager.GetSerializedString(tableName);
if (!string.IsNullOrEmpty(serializedString))
{
if (serializedString != serializedStringForExistTable)//仅有此时，导入表需要修改表名
{
tableNameToInsert = tableNamePrefix + tableName;
tableCnName = tableNamePrefix + tableCnName;
nameValueCollection[tableName] = tableNameToInsert;
}
}
}
if (!string.IsNullOrEmpty(tableNameToInsert))//需要添加
{
if (!BaiRongDataProvider.TableStructureDao.IsTableExists(tableNameToInsert))
{
tableInfo = new AuxiliaryTableInfo
{
TableEnName = tableNameToInsert,
TableCnName = tableCnName,
AttributeNum = 0,
AuxiliaryTableType = tableType,
IsCreatedInDb = false,
IsChangedAfterCreatedInDb = false,
Description = AtomUtility.GetDcElementContent(feed.AdditionalElements, "Description")
};
BaiRongDataProvider.TableCollectionDao.Insert(tableInfo);
var tableStyle = EAuxiliaryTableTypeUtils.GetTableStyle(tableInfo.AuxiliaryTableType);
var attributeNameList =
TableManager.GetAttributeNameList(tableStyle, tableInfo.TableEnName, true);
attributeNameList.AddRange(
TableManager.GetHiddenAttributeNameList(tableStyle));
foreach (AtomEntry entry in feed.Entries)
{
var metaInfo = new TableMetadataInfo
{
AuxiliaryTableEnName = tableNameToInsert,
AttributeName =
AtomUtility.GetDcElementContent(entry.AdditionalElements, "AttributeName"),
DataType = EDataTypeUtils.GetEnumType(
AtomUtility.GetDcElementContent(entry.AdditionalElements, "DataType")),
DataLength = TranslateUtils.ToInt(
AtomUtility.GetDcElementContent(entry.AdditionalElements, "DataLength")),
Taxis =
TranslateUtils.ToInt(AtomUtility.GetDcElementContent(entry.AdditionalElements,
"Taxis")),
IsSystem = TranslateUtils.ToBool(
AtomUtility.GetDcElementContent(entry.AdditionalElements, "IsSystem"))
};
if (attributeNameList.IndexOf(metaInfo.AttributeName.Trim().ToLower()) != -1)
{
continue;
}
if (metaInfo.IsSystem)
{
continue;
}
BaiRongDataProvider.TableMetadataDao.Insert(metaInfo);
}
BaiRongDataProvider.TableMetadataDao.CreateAuxiliaryTable(tableNameToInsert);
}
}
var tableNameToChange = (!string.IsNullOrEmpty(tableNameToInsert)) ? tableNameToInsert : tableName;
//更新发布系统后台内容表及栏目表
if (tableType == EAuxiliaryTableType.BackgroundContent)
{
publishmentSystemInfo.AuxiliaryTableForContent = tableNameToChange;
DataProvider.PublishmentSystemDao.Update(publishmentSystemInfo);
}
else if (tableType == EAuxiliaryTableType.GovPublicContent)
{
publishmentSystemInfo.AuxiliaryTableForGovPublic = tableNameToChange;
DataProvider.PublishmentSystemDao.Update(publishmentSystemInfo);
}
else if (tableType == EAuxiliaryTableType.GovInteractContent)
{
publishmentSystemInfo.AuxiliaryTableForGovInteract = tableNameToChange;
DataProvider.PublishmentSystemDao.Update(publishmentSystemInfo);
}
else if (tableType == EAuxiliaryTableType.JobContent)
{
publishmentSystemInfo.AuxiliaryTableForJob = tableNameToChange;
DataProvider.PublishmentSystemDao.Update(publishmentSystemInfo);
}
else if (tableType == EAuxiliaryTableType.VoteContent)
{
publishmentSystemInfo.AuxiliaryTableForVote = tableNameToChange;
DataProvider.PublishmentSystemDao.Update(publishmentSystemInfo);
}
}
return(nameValueCollection);
}
public static string GetSqlServerColumnSqlString(EDataType dataType, string attributeName, int length)
{
var retval = string.Empty;
var sqlDbType = EDataTypeUtils.ToSqlDbType(dataType);
switch (sqlDbType)
{
case SqlDbType.BigInt:
retval = $"[{attributeName}] [bigint]";
break;
case SqlDbType.Binary:
retval = $"[{attributeName}] [binary] ({length})";
break;
case SqlDbType.Bit:
retval = $"[{attributeName}] [bit]";
break;
case SqlDbType.Char:
retval = $"[{attributeName}] [char] ({length})";
break;
case SqlDbType.DateTime:
retval = $"[{attributeName}] [datetime]";
break;
case SqlDbType.Decimal:
retval = $"[{attributeName}] [decimal] (18, 2)";
break;
case SqlDbType.Float:
retval = $"[{attributeName}] [float]";
break;
case SqlDbType.Image:
retval = $"[{attributeName}] [image]";
break;
case SqlDbType.Int:
retval = $"[{attributeName}] [int]";
break;
case SqlDbType.Money:
retval = $"[{attributeName}] [money]";
break;
case SqlDbType.NChar:
retval = $"[{attributeName}] [nchar] ({length})";
break;
case SqlDbType.NText:
retval = $"[{attributeName}] [ntext]";
break;
case SqlDbType.NVarChar:
retval = $"[{attributeName}] [nvarchar] ({length})";
break;
case SqlDbType.Real:
retval = $"[{attributeName}] [real]";
break;
case SqlDbType.SmallDateTime:
retval = $"[{attributeName}] [smalldatetime]";
break;
case SqlDbType.SmallInt:
retval = $"[{attributeName}] [smallint]";
break;
case SqlDbType.SmallMoney:
retval = $"[{attributeName}] [smallmoney]";
break;
case SqlDbType.Text:
retval = $"[{attributeName}] [text]";
break;
case SqlDbType.Timestamp:
retval = $"[{attributeName}] [timestamp]";
break;
case SqlDbType.TinyInt:
retval = $"[{attributeName}] [tinyint]";
break;
case SqlDbType.VarBinary:
retval = $"[{attributeName}] [varbinary] ({length})";
break;
case SqlDbType.VarChar:
retval = $"[{attributeName}] [varchar] ({length})";
break;
}
retval += " NULL";
return(retval);
}
public List <TableMetadataInfo> GetTableMetadataInfoList(string tableEnName)
{
var list = new List <TableMetadataInfo>();
var parms = new IDataParameter[]
{
GetParameter(ParmAuxiliaryTableEnname, EDataType.VarChar, 50, tableEnName)
};
using (var rdr = ExecuteReader(SqlSelectAllTableMetadataByEnname, parms))
{
while (rdr.Read())
{
var i = 0;
var info = new TableMetadataInfo(GetInt(rdr, i++), GetString(rdr, i++), GetString(rdr, i++), EDataTypeUtils.GetEnumType(GetString(rdr, i++)), GetInt(rdr, i++), GetInt(rdr, i++), GetBool(rdr, i));
list.Add(info);
}
rdr.Close();
}
return(list);
}
public TableMetadataInfo GetTableMetadataInfo(string tableEnName, string attributeName)
{
TableMetadataInfo info = null;
var parms = new IDataParameter[]
{
GetParameter(ParmAuxiliaryTableEnname, EDataType.VarChar, 50, tableEnName),
GetParameter(ParmAttributeName, EDataType.VarChar, 50, attributeName)
};
using (var rdr = ExecuteReader(SqlSelectTableMetadataByTableEnnameAndAttributeName, parms))
{
if (rdr.Read())
{
var i = 0;
info = new TableMetadataInfo(GetInt(rdr, i++), GetString(rdr, i++), GetString(rdr, i++), EDataTypeUtils.GetEnumType(GetString(rdr, i++)), GetInt(rdr, i++), GetInt(rdr, i++), GetBool(rdr, i));
}
rdr.Close();
}
return(info);
}
public TableMetadataInfo GetTableMetadataInfo(int tableMetadataId)
{
TableMetadataInfo info = null;
var parms = new IDataParameter[]
{
GetParameter(ParmTableMetadataId, EDataType.Integer, tableMetadataId)
};
using (var rdr = ExecuteReader(SqlSelectTableMetadata, parms))
{
if (rdr.Read())
{
var i = 0;
info = new TableMetadataInfo(GetInt(rdr, i++), GetString(rdr, i++), GetString(rdr, i++), EDataTypeUtils.GetEnumType(GetString(rdr, i++)), GetInt(rdr, i++), GetInt(rdr, i++), GetBool(rdr, i));
}
rdr.Close();
}
return(info);
}
public static string GetDefaultDateString()
{
return(EDataTypeUtils.GetDefaultString(EDataType.DateTime));
}
public override void Submit_OnClick(object sender, EventArgs e)
{
var isChanged = false;
if (Body.IsQueryExists("TableMetadataID"))
{
var tableMetadataId = Body.GetQueryInt("TableMetadataID");
var info = BaiRongDataProvider.TableMetadataDao.GetTableMetadataInfo(tableMetadataId);
info.AuxiliaryTableEnName = _tableName;
info.AttributeName = AttributeName.Text;
info.DataType = EDataTypeUtils.GetEnumType(DataType.SelectedValue);
var hashtable = new Hashtable
{
[EDataType.DateTime] = new[] { "8", "false" },
[EDataType.Integer] = new[] { "4", "false" },
[EDataType.NChar] = new[] { "50", "true" },
[EDataType.NText] = new[] { "16", "false" },
[EDataType.NVarChar] = new[] { "255", "true" }
};
var strArr = (string[])hashtable[EDataTypeUtils.GetEnumType(DataType.SelectedValue)];
if (strArr[1].Equals("false"))
{
DataLength.Text = strArr[0];
}
info.DataLength = int.Parse(DataLength.Text);
if (info.DataType == EDataType.NVarChar || info.DataType == EDataType.NChar)
{
var maxLength = SqlUtils.GetMaxLengthForNVarChar();
if (info.DataLength <= 0 || info.DataLength > maxLength)
{
FailMessage($"字段修改失败，数据长度的值必须位于 1 和 {maxLength} 之间");
return;
}
}
try
{
BaiRongDataProvider.TableMetadataDao.Update(info);
Body.AddAdminLog("修改辅助表字段",
$"辅助表:{_tableName},字段名:{info.AttributeName}");
isChanged = true;
}
catch (Exception ex)
{
FailMessage(ex, ex.Message);
}
}
else
{
var tableStyle = EAuxiliaryTableTypeUtils.GetTableStyle(_tableType);
var attributeNameList = TableManager.GetAttributeNameList(tableStyle, _tableName, true);
attributeNameList.AddRange(TableManager.GetHiddenAttributeNameList(tableStyle));
if (attributeNameList.IndexOf(AttributeName.Text.Trim().ToLower()) != -1)
{
FailMessage("字段添加失败，字段名已存在！");
}
else if (!SqlUtils.IsAttributeNameCompliant(AttributeName.Text))
{
FailMessage("字段名不符合系统要求！");
}
else
{
var info = new TableMetadataInfo
{
AuxiliaryTableEnName = _tableName,
AttributeName = AttributeName.Text,
DataType = EDataTypeUtils.GetEnumType(DataType.SelectedValue)
};
var hashtable = new Hashtable
{
[EDataType.DateTime] = new[] { "8", "false" },
[EDataType.Integer] = new[] { "4", "false" },
[EDataType.NChar] = new[] { "50", "true" },
[EDataType.NText] = new[] { "16", "false" },
[EDataType.NVarChar] = new[] { "255", "true" }
};
var strArr = (string[])hashtable[EDataTypeUtils.GetEnumType(DataType.SelectedValue)];
if (strArr[1].Equals("false"))
{
DataLength.Text = strArr[0];
}
info.DataLength = int.Parse(DataLength.Text);
if (info.DataType == EDataType.NVarChar || info.DataType == EDataType.NChar)
{
var maxLength = SqlUtils.GetMaxLengthForNVarChar();
if (info.DataLength <= 0 || info.DataLength > maxLength)
{
FailMessage($"字段修改失败，数据长度的值必须位于 1 和 {maxLength} 之间");
return;
}
}
info.IsSystem = false;
try
{
BaiRongDataProvider.TableMetadataDao.Insert(info);
Body.AddAdminLog("添加辅助表字段",
$"辅助表:{_tableName},字段名:{info.AttributeName}");
isChanged = true;
}
catch (Exception ex)
{
FailMessage(ex, ex.Message);
}
}
}
if (isChanged)
{
PageUtils.CloseModalPage(Page);
}
}
public List <TableColumnInfo> GetTableColumnInfoList(string connectionString, string databaseName, string tableName, string tableId)
{
if (string.IsNullOrEmpty(connectionString))
{
connectionString = ConnectionString;
}
var cacheList = SqlUtils.Cache_GetTableColumnInfoListCache(connectionString, databaseName, tableId);
if (cacheList != null && cacheList.Count > 0)
{
return(cacheList);
}
var list = new List <TableColumnInfo>();
var isIdentityExist = false;
if (WebConfigUtils.IsMySql)
{
string sqlString = $"select COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, COLUMN_KEY from information_schema.columns where table_schema = '{databaseName}' and table_name = '{tableName}' order by table_name,ordinal_position; ";
using (var rdr = ExecuteReader(connectionString, sqlString))
{
while (rdr.Read())
{
var columnName = Convert.ToString(rdr.GetValue(0));
var isNullable = Convert.ToString(rdr.GetValue(1)) == "YES";
var dataType = EDataTypeUtils.FromMySql(Convert.ToString(rdr.GetValue(2)));
var length = rdr.IsDBNull(3) || dataType == EDataType.NText || dataType == EDataType.Text ? 0 : Convert.ToInt32(rdr.GetValue(3));
var precision = rdr.IsDBNull(4) ? 0 : Convert.ToInt32(rdr.GetValue(4));
var scale = rdr.IsDBNull(5) ? 0 : Convert.ToInt32(rdr.GetValue(5));
var isPrimaryKey = Convert.ToString(rdr.GetValue(6)) == "PRI";
var isIdentity = isPrimaryKey && StringUtils.EqualsIgnoreCase(columnName, "ID");
var info = new TableColumnInfo(databaseName, tableId, columnName, dataType, length, precision, scale, isPrimaryKey, isNullable, isIdentity);
list.Add(info);
}
rdr.Close();
}
}
else
{
string sqlString =
$"select C.name, T.name, C.length, C.xprec, C.xscale, C.colstat, C.isnullable, case when C.autoval is null then 0 else 1 end, SC.text, (select CForgin.name from [{databaseName}].dbo.sysreferences Sr,[{databaseName}].dbo.sysobjects O,[{databaseName}].dbo.syscolumns CForgin where Sr.fkeyid={tableId} and Sr.fkey1=C.colid and Sr.rkeyid=O.id and CForgin.id=O.id and CForgin.colid=Sr.rkey1), (select O.name from [{databaseName}].dbo.sysreferences Sr,[{databaseName}].dbo.sysobjects O,[{databaseName}].dbo.syscolumns CForgin where Sr.fkeyid={tableId} and Sr.fkey1=C.colid and Sr.rkeyid=O.id and CForgin.id=O.id and CForgin.colid=Sr.rkey1), (select Sr.rkeyid from [{databaseName}].dbo.sysreferences Sr,[{databaseName}].dbo.sysobjects O,[{databaseName}].dbo.syscolumns CForgin where Sr.fkeyid={tableId} and Sr.fkey1=C.colid and Sr.rkeyid=O.id and CForgin.id=O.id and CForgin.colid=Sr.rkey1) from [{databaseName}].dbo.systypes T, [{databaseName}].dbo.syscolumns C left join [{databaseName}].dbo.syscomments SC on C.cdefault=SC.id where C.id={tableId} and C.xtype=T.xusertype order by C.colid";
using (var rdr = ExecuteReader(connectionString, sqlString))
{
while (rdr.Read())
{
var columnName = Convert.ToString(rdr.GetValue(0));
if (columnName == "msrepl_tran_version")//sqlserver 发布订阅字段，忽略
{
continue;
}
var dataType = EDataTypeUtils.FromSqlServer(Convert.ToString(rdr.GetValue(1)));
var length = GetDataLength(dataType, Convert.ToInt32(rdr.GetValue(2)));
var precision = Convert.ToInt32(rdr.GetValue(3));
var scale = Convert.ToInt32(rdr.GetValue(4));
var isPrimaryKeyInt = Convert.ToInt32(rdr.GetValue(5));
var isNullableInt = Convert.ToInt32(rdr.GetValue(6));
var isIdentityInt = Convert.ToInt32(rdr.GetValue(7));
var isPrimaryKey = isPrimaryKeyInt == 1;
var isNullable = isNullableInt == 1;
var isIdentity = isIdentityInt == 1 || StringUtils.EqualsIgnoreCase(columnName, "ID");
//sqlserver 2005 返回isIdentity结果不正确,so 在此假设所有ID字段为Idenity字段
if (isIdentity)
{
isIdentityExist = true;
}
var info = new TableColumnInfo(databaseName, tableId, columnName, dataType, length, precision, scale, isPrimaryKey, isNullable, isIdentity);
list.Add(info);
}
rdr.Close();
}
if (!isIdentityExist)
{
var sqlIdentity = "select name from syscolumns where id = object_id(N'" + tableName + "') and COLUMNPROPERTY(id, name,'IsIdentity')= 1";
var clName = "";
using (var rdr = ExecuteReader(sqlIdentity))
{
if (rdr.Read())
{
clName = GetString(rdr, 0);
}
rdr.Close();
}
foreach (var info in list)
{
if (clName == info.ColumnName)
{
info.IsIdentity = true;
}
}
}
}
SqlUtils.Cache_CacheTableColumnInfoList(connectionString, databaseName, tableId, list);
return(list);
}
void dgContents_ItemDataBound(object sender, DataGridItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
var tableMetadataId = SqlUtils.EvalInt(e.Item.DataItem, "TableMetadataID");
var attributeName = SqlUtils.EvalString(e.Item.DataItem, "AttributeName");
var dataType = SqlUtils.EvalString(e.Item.DataItem, "DataType");
var dataLength = SqlUtils.EvalInt(e.Item.DataItem, "DataLength");
var isSystem = SqlUtils.EvalString(e.Item.DataItem, "IsSystem");
var ltlAttributeName = e.Item.FindControl("ltlAttributeName") as Literal;
var ltlDisplayName = e.Item.FindControl("ltlDisplayName") as Literal;
var ltlIsVisible = e.Item.FindControl("ltlIsVisible") as Literal;
var ltlValidate = e.Item.FindControl("ltlValidate") as Literal;
var ltlDataType = e.Item.FindControl("ltlDataType") as Literal;
var ltlInputType = e.Item.FindControl("ltlInputType") as Literal;
var upLinkButton = e.Item.FindControl("UpLinkButton") as HyperLink;
var downLinkButton = e.Item.FindControl("DownLinkButton") as HyperLink;
var ltlStyle = e.Item.FindControl("ltlStyle") as Literal;
var ltlEditValidate = e.Item.FindControl("ltlEditValidate") as Literal;
var ltlEditUrl = e.Item.FindControl("ltlEditUrl") as Literal;
var ltlDeleteUrl = e.Item.FindControl("ltlDeleteUrl") as Literal;
var showPopWinString = ModalTableMetadataView.GetOpenWindowString(_tableType, _tableName, attributeName);
ltlAttributeName.Text =
$"<a href=\"javascript:void 0;\" onClick=\"{showPopWinString}\">{attributeName}</a>";
var styleInfo = TableStyleManager.GetTableStyleInfo(EAuxiliaryTableTypeUtils.GetTableStyle(_tableType), _tableName, attributeName, null);
ltlDisplayName.Text = styleInfo.DisplayName;
ltlIsVisible.Text = StringUtils.GetTrueOrFalseImageHtml(styleInfo.IsVisible.ToString());
ltlValidate.Text = EInputValidateTypeUtils.GetValidateInfo(styleInfo);
ltlDataType.Text = EDataTypeUtils.GetTextByAuxiliaryTable(EDataTypeUtils.GetEnumType(dataType), dataLength);
ltlInputType.Text = EInputTypeUtils.GetText(EInputTypeUtils.GetEnumType(styleInfo.InputType));
if (IsSystem(isSystem))
{
if (upLinkButton != null)
{
upLinkButton.NavigateUrl = PageUtils.GetSysUrl(nameof(PageTableMetadata),
new NameValueCollection
{
{ "PublishmentSystemID", PublishmentSystemId.ToString() },
{ "SetTaxis", "True" },
{ "TableStyleID", styleInfo.TableStyleId.ToString() },
{ "Direction", "UP" },
{ "TableMetadataId", tableMetadataId.ToString() },
{ "ENName", _tableName },
{ "TableType", EAuxiliaryTableTypeUtils.GetValue(_tableType) }
});
}
if (downLinkButton != null)
{
downLinkButton.NavigateUrl = PageUtils.GetSysUrl(nameof(PageTableMetadata),
new NameValueCollection
{
{ "PublishmentSystemID", PublishmentSystemId.ToString() },
{ "SetTaxis", "True" },
{ "TableStyleID", styleInfo.TableStyleId.ToString() },
{ "Direction", "DOWN" },
{ "TableMetadataId", tableMetadataId.ToString() },
{ "ENName", _tableName },
{ "TableType", EAuxiliaryTableTypeUtils.GetValue(_tableType) }
});
}
}
ltlStyle.Text = GetEditStyleHtml(tableMetadataId, attributeName);
showPopWinString = ModalTableStyleValidateAdd.GetOpenWindowString(styleInfo.TableStyleId, _tableName, styleInfo.AttributeName, EAuxiliaryTableTypeUtils.GetTableStyle(_tableType), _redirectUrl);
ltlEditValidate.Text = $"<a href=\"javascript:void 0;\" onClick=\"{showPopWinString}\">设置</a>";
ltlEditUrl.Text = GetEditHtml(isSystem, tableMetadataId);
if (!IsSystem(isSystem))
{
var attributes = new NameValueCollection
{
{ "Delete", true.ToString() },
{ "TableMetadataID", tableMetadataId.ToString() }
};
var deleteUrl = PageUtils.AddQueryString(_redirectUrl, attributes);
ltlDeleteUrl.Text =
$@"<a href=""{deleteUrl}"" onClick=""javascript:return confirm('此操作将删除辅助字段“{attributeName}”，确认吗？');"">删除字段</a>";
}
}
}
