/// <summary>
/// 获取列信息
/// </summary>
/// <param name="xnColumn">列节点</param>
/// <returns>列信息</returns>
private ColumnInfo GetColumn(XmlNode xnColumn)
{
ColumnInfo mColumn = new ColumnInfo();
XmlElement xe = (XmlElement)xnColumn;
mColumn.ColumnId = xe.GetAttribute("Id");
XmlNodeList xnCProperty = xe.ChildNodes;
foreach (XmlNode xnP in xnCProperty)
{
switch (xnP.Name)
{
//列ID
case "a:ObjectID":
mColumn.ColumnObjectId = xnP.InnerText;
break;
//列中文名称
case "a:Name":
mColumn.Name = xnP.InnerText;
break;
//列英文名称
case "a:Code":
mColumn.Code = xnP.InnerText;
break;
//列描述
case "a:Comment":
mColumn.Comment = xnP.InnerText;
break;
//列数据类型
case "a:DataType":
mColumn.DataTypeStr = xnP.InnerText.Replace("（", "(").Replace("）", ")");
mColumn.DataType = Common.GetColumnDataType(mColumn.DataTypeStr);
mColumn.Width = Common.GetColumnWidth(mColumn.DataTypeStr);
break;
//列宽度
case "a:Length":
mColumn.Length = xnP.InnerText;
break;
//列是否自增
case "a:Identity":
mColumn.Identity = Common.ConvertToBooleanPG(xnP.InnerText);
break;
//列默认值
case "a:DefaultValue":
mColumn.DefaultValue = xnP.InnerText;
break;
//列是否可为空
case "a:Mandatory":
mColumn.Nullable = Common.ConvertToBooleanPG(xnP.InnerText);
break;
default:
break;
}
}
if (string.IsNullOrEmpty(mColumn.Comment))
{
mColumn.Comment = mColumn.Name;
}
if (string.IsNullOrEmpty(mColumn.DefaultValue))
{
mColumn.DefaultValue = "";
}
return mColumn;
}
/// <summary>
/// 将详细表信息转换成List对象
/// </summary>
/// <param name="dt">dt</param>
/// <returns>List<TableInfo></returns>
public static List<TableInfo> DetailDT2List(DataTable dt, List<int> listTableId)
{
List<TableInfo> list = new List<TableInfo>();
TableInfo entity = null;
ColumnInfo colEntity = null;
List<ColumnInfo> listCol = null;
foreach (int tableid in listTableId)
{
DataRow[] drs = dt.Select("TableId=" + tableid);
entity = new TableInfo();
listCol = new List<ColumnInfo>();
foreach (DataRow dr in drs)
{
entity.Code = dr["table_name"].ToString();
entity.Name = dr["table_name_c"].ToString();
entity.IsUpdate = Convert.ToBoolean(dr["IsUpdate"]);
colEntity = new ColumnInfo();
colEntity.Code = dr["field_name"].ToString();
colEntity.Name = dr["field_name_c"].ToString();
colEntity.Sequence = Convert.ToInt32(dr["field_sequence"]);
colEntity.Nullable = Convert.ToBoolean(dr["isnullable"]);
colEntity.PK = Convert.ToBoolean(dr["pk"]);
colEntity.Identity = Convert.ToBoolean(dr["isidentity"]);
colEntity.DataType = dr["date_type"].ToString();
colEntity.DefaultValue = Common.GetDefaultValue(dr["defaultvalue"].ToString(), colEntity.DataType);
colEntity.Width = Common.GetColumnWidth(colEntity.DataType, dr["prec"].ToString(), dr["scale"].ToString());
listCol.Add(colEntity);
}
entity.ListColumnInfo = listCol;
list.Add(entity);
}
return list;
}
/// <summary>
/// 获取表中的列信息
/// </summary>
/// <param name="row">行节点</param>
/// <param name="dic">列名字典集合</param>
/// <param name="pTable">表信息</param>
private void InitColumns(Row row, Dictionary<int, string> dic, TableInfo pTable)
{
ColumnInfo mColumn = new ColumnInfo();
int iCell = 0;
//列ID
mColumn.ColumnObjectId = Guid.NewGuid().ToString();
string sTemp="";
foreach (var cell in row.Cells)
{
sTemp=cell.Paragraphs[0].Text.Trim();
Common.GetColumnInfo(dic, sTemp, mColumn, iCell, pTable);
iCell++;
}
//2014-07-28 添加错误详细信息提示,以便准确知道错误地方
Common.JudgeColumnInfo(mColumn, pTable.Code);
mColumn.DataTypeStr=Common.GetDataTypeStr(mColumn.DataTypeStr, mColumn.Width);
mColumn.Width = Common.GetColumnWidth(mColumn.DataTypeStr);
//杜冬军2014-07-23修改,添加主键列的判定方式 ,如果中文名称类似 A(主键) 则认为该列是主键列
Common.GetPrimaryKeyInfo(mColumn, pTable);
if (string.IsNullOrEmpty(mColumn.Comment))
{
mColumn.Comment = mColumn.Name;
}
if (string.IsNullOrEmpty(mColumn.DefaultValue))
{
mColumn.DefaultValue = "";
}
mColumn.Sequence = pTable.ListColumnInfo.Count + 1;
pTable.ListColumnInfo.Add(mColumn);
}
/// <summary>
/// 获取表中的列信息
/// </summary>
/// <param name="row">行节点</param>
/// <param name="dic">列名字典集合</param>
/// <param name="pTable">表信息</param>
private void InitColumns(Row row, Dictionary<int, string> dic, TableInfo pTable)
{
ColumnInfo mColumn = new ColumnInfo();
int iCell = 0;
//列ID
mColumn.ColumnObjectId = Guid.NewGuid().ToString();
string sTemp="";
foreach (var cell in row.Cells)
{
sTemp=cell.Paragraphs[0].Text.Trim();
Common.GetColumnInfo(dic, sTemp, mColumn, iCell, pTable);
iCell++;
}
mColumn.DataTypeStr=Common.GetDataTypeStr(mColumn.DataTypeStr, mColumn.Width);
mColumn.Width = Common.GetColumnWidth(mColumn.DataTypeStr);
if (string.IsNullOrEmpty(mColumn.Comment))
{
mColumn.Comment = mColumn.Name;
}
if (string.IsNullOrEmpty(mColumn.DefaultValue))
{
mColumn.DefaultValue = "";
}
mColumn.Sequence = pTable.ListColumnInfo.Count + 1;
pTable.ListColumnInfo.Add(mColumn);
}
/// <summary>
/// 获取表中的列信息
/// </summary>
/// <param name="row">列节点</param>
/// <param name="iCell">列起始索引</param>
/// <param name="pTable">表信息</param>
/// <param name="dic">列名字典集合</param>
private void InitColumns(IRow row, int iCell, TableInfo pTable, Dictionary<int, string> dic)
{
ColumnInfo mColumn = new ColumnInfo();
//列ID
mColumn.ColumnObjectId = Guid.NewGuid().ToString();
string sTemp = "";
int LastCellIndex = dic.Keys.Last<int>();
for (int i = dic.Keys.First<int>(); i <= LastCellIndex; i++)
{
//2014-07-01杜冬军修改，row.LastCellNum取出来有误，确保不出现空异常
sTemp = row.GetCell(i) == null ? "" : row.GetCell(i).ToString().Trim();
Common.GetColumnInfo(dic, sTemp, mColumn, i, pTable);
}
//2014-07-28 添加错误详细信息提示,以便准确知道错误地方
Common.JudgeColumnInfo(mColumn, pTable.Code);
mColumn.DataTypeStr = Common.GetDataTypeStr(mColumn.DataTypeStr, mColumn.Width);
mColumn.Width = Common.GetColumnWidth(mColumn.DataTypeStr);
//杜冬军2014-07-23修改,添加主键列的判定方式 ,如果中文名称类似 A(主键) 则认为该列是主键列
Common.GetPrimaryKeyInfo(mColumn, pTable);
if (string.IsNullOrEmpty(mColumn.Comment))
{
mColumn.Comment = mColumn.Name;
}
if (string.IsNullOrEmpty(mColumn.DefaultValue))
{
mColumn.DefaultValue = "";
}
mColumn.Sequence = pTable.ListColumnInfo.Count + 1;
pTable.ListColumnInfo.Add(mColumn);
}
/// <summary>
/// 判断列信息是否准确，如果有误直接抛出异常
/// </summary>
/// <param name="mColumn">列信息</param>
/// <param name="pTableCode">表英文名</param>
public static void JudgeColumnInfo(ColumnInfo mColumn, string pTableCode)
{
if (string.IsNullOrEmpty(mColumn.Code) || string.IsNullOrEmpty(mColumn.Name))
{
if (string.IsNullOrEmpty(mColumn.Code) && string.IsNullOrEmpty(mColumn.Name))
{
throw new Exception("表(" + pTableCode + ")的“字段中文名”和“字段英文名”不能为空！");
}
if (string.IsNullOrEmpty(mColumn.Code))
{
throw new Exception("表(" + pTableCode + ")的列(" + mColumn.Name + ")“字段英文名”不能为空！");
}
if (string.IsNullOrEmpty(mColumn.Name))
{
throw new Exception("表(" + pTableCode + ")的列(" + mColumn.Code + ")“字段中文名”不能为空！");
}
}
if (string.IsNullOrEmpty(mColumn.DataType))
{
throw new Exception("表(" + pTableCode + ")的列(" + mColumn.Code + ")“数据类型”不能为空！");
}
string DataType = mColumn.DataType.ToLower();
if (DataType.Contains("(") || DataType.Contains("（"))
{
if (!DataType.Contains(')') && !DataType.Contains('）'))
{
throw new Exception("表(" + pTableCode + ")的列(" + mColumn.Code + ")“数据类型”格式填写错误，应类似VARCHAR(MAX)！");
}
}
else
{
if (DataType.Contains("dec") || DataType.Contains("char") || DataType.Contains("binary") || DataType.Contains("numeric"))
{
if (string.IsNullOrEmpty(mColumn.Width))
{
if (String.IsNullOrEmpty(mColumn.DataTypeStr))
{
throw new Exception("表(" + pTableCode + ")的列(" + mColumn.Code + ")“宽度”不能为空！");
}
if (!mColumn.DataTypeStr.Contains('(') && !mColumn.DataTypeStr.Contains('（'))
{
throw new Exception("表(" + pTableCode + ")的列(" + mColumn.Code + ")“宽度”不能为空！");
}
}
}
}
}
/// <summary>
/// EXECL，WORD模版添加新的主键列判定方式，如列中文名称 A(主键) 带有(主键)则认为该列是主键列
/// 2014-07-23添加该方法以支持项目判断主键的方式
/// </summary>
/// <param name="mColumn">列信息</param>
public static void GetPrimaryKeyInfo(ColumnInfo mColumn, TableInfo pTable)
{
if (mColumn == null || string.IsNullOrEmpty(mColumn.Name) || mColumn.PK)
{
return;
}
string temp = ClearEmpty(mColumn.Name);
temp = temp.Replace("（", "(").Replace("）", ")").ToUpper();
if (temp.Contains("(主键)") || temp.Contains("(PK)"))
{
mColumn.Name = temp.Replace("(主键)", "").Replace("(PK)", "");
mColumn.Nullable = true;
PkKeyInfo pk = new PkKeyInfo();
pk.Name = mColumn.Code;
mColumn.PK = true;
pTable.ListPkKeyInfo.Add(pk);
}
}
/// <summary>
/// 获取EXECL或者Word的列信息
/// </summary>
/// <param name="dic">列信息字典信息</param>
/// <param name="sTemp">列的值</param>
/// <param name="mColumn">列</param>
/// <returns>列信息</returns>
public static void GetColumnInfo(Dictionary<int, string> dic, string sTemp, ColumnInfo mColumn, int iCell, TableInfo pTable)
{
switch (dic[iCell])
{
//列中文名称
case "字段中文名":
mColumn.Name = sTemp;
break;
//列英文名称
case "字段英文名":
mColumn.Code = sTemp;
break;
case "数据类型":
mColumn.DataTypeStr = sTemp.Replace("（", "(").Replace("）", ")");
mColumn.DataType = Common.GetColumnDataType(mColumn.DataTypeStr);
break;
case "宽度":
mColumn.Width = sTemp;
break;
//主键信息
case "主键":
case "约束":
if (sTemp.ToLower() == "pk" || Common.ConvertToBooleanPG(sTemp))
{
PkKeyInfo pk = new PkKeyInfo();
pk.Name = mColumn.Code;
mColumn.PK = true;
pTable.ListPkKeyInfo.Add(pk);
}
break;
//列默认值
case "默认值":
mColumn.DefaultValue = sTemp;
break;
//列描述
case "说明":
case "枚举&说明":
mColumn.Comment = sTemp;
break;
//列是否可为空
case "空值":
//是否为空值处理和其它的不相同,只会填N或0表示该列不允许为空
sTemp = sTemp.ToLower();
if ((sTemp.Equals("n") || sTemp.Equals("0")) || sTemp.Equals("false") || sTemp.Equals("否"))
{
mColumn.Nullable = true;
}
else
{
mColumn.Nullable = false;
}
break;
//列是否自增
case "自增":
mColumn.Identity = Common.ConvertToBooleanPG(sTemp);
break;
default:
break;
}
}
/// <summary>
/// 获取列信息
/// </summary>
/// <param name="xnColumn">列节点</param>
/// <returns>列信息</returns>
private ColumnInfo GetColumn(XmlNode xnColumn)
{
ColumnInfo mColumn = new ColumnInfo();
XmlElement xe = (XmlElement)xnColumn;
mColumn.ColumnId = xe.GetAttribute("Id");
XmlNodeList xnCProperty = xe.ChildNodes;
foreach (XmlNode xnP in xnCProperty)
{
switch (xnP.Name)
{
//列ID
case "a:ObjectID":
mColumn.ColumnObjectId = xnP.InnerText;
break;
//列中文名称
case "a:Name":
mColumn.Name = xnP.InnerText;
break;
//列英文名称
case "a:Code":
mColumn.Code = xnP.InnerText;
break;
//列描述
case "a:Comment":
mColumn.Comment = xnP.InnerText;
break;
//列数据类型
case "a:DataType":
mColumn.DataTypeStr = xnP.InnerText.Replace("（", "(").Replace("）", ")");
mColumn.DataType = Common.GetColumnDataType(mColumn.DataTypeStr);
mColumn.Width = Common.GetColumnWidth(mColumn.DataTypeStr);
break;
//列宽度
case "a:Length":
mColumn.Length = xnP.InnerText;
break;
//列是否自增
case "a:Identity":
mColumn.Identity = Common.ConvertToBooleanPG(xnP.InnerText);
break;
//列默认值
case "a:DefaultValue":
mColumn.DefaultValue = xnP.InnerText;
break;
//列是否可为空
case "a:Mandatory":
mColumn.Nullable = Common.ConvertToBooleanPG(xnP.InnerText);
break;
default:
break;
}
}
if (string.IsNullOrEmpty(mColumn.Comment))
{
mColumn.Comment = mColumn.Name;
}
if (string.IsNullOrEmpty(mColumn.DefaultValue))
{
mColumn.DefaultValue = "";
}
return(mColumn);
}
/// <summary>
/// 获取节点中表的信息
/// </summary>
/// <param name="xnTable">xmlNode</param>
/// <returns>表信息</returns>
private TableInfo GetTable(XmlNode xnTable)
{
try
{
TableInfo mTable = new TableInfo();
XmlElement xe = (XmlElement)xnTable;
mTable.TableID = xe.GetAttribute("Id");
XmlNodeList xnTProperty = xe.ChildNodes;
foreach (XmlNode xnP in xnTProperty)
{
switch (xnP.Name)
{
//表的ID
case "a:ObjectID":
mTable.TableObjectID = xnP.InnerText;
break;
//表的中文名称
case "a:Name":
mTable.Name = xnP.InnerText;
break;
//表的英文名称
case "a:Code":
mTable.Code = xnP.InnerText;
break;
//表的描述
case "a:Comment":
mTable.Comment = xnP.InnerText;
break;
//表的列信息
case "c:Columns":
InitColumns(xnP, mTable);
break;
//表的主键信息
case "c:Keys":
InitKeys(xnP, mTable);
break;
default:
break;
}
}
if (string.IsNullOrEmpty(mTable.Comment))
{
mTable.Comment = mTable.Name;
}
if (mTable.ListPkKeyInfo != null && mTable.ListPkKeyInfo.Count > 0)
{
foreach (PkKeyInfo pkInfo in mTable.ListPkKeyInfo)
{
ColumnInfo info = mTable.ListColumnInfo.Single(c => c.ColumnId == pkInfo.ColumnId);
pkInfo.Name = info.Code;
info.PK = true;
mTable.PkKeyNameList = mTable.PkKeyNameList + pkInfo.Name + ",";
}
}
//杜冬军2014-05-16 修改没有主键 生成SQL有问题的BUG V1.4
else
{
mTable.ListPkKeyInfo = new List <PkKeyInfo>();
}
if (!string.IsNullOrEmpty(mTable.PkKeyNameList))
{
mTable.PkKeyNameList = mTable.PkKeyNameList.Substring(0, mTable.PkKeyNameList.Length - 1);
}
return(mTable);
}
catch (Exception ex)
{
throw ex;
}
}