/// <summary> /// 验证表中字段是否全部有效 /// </summary> /// <returns>空表示执行正确,非空为出错信息</returns> public List<ColumnError> validateTableColums(Boolean isPmTable = true) { List<ColumnError> result = new List<ColumnError>(); //打开Access数据库连接 this.openOleDbConnection(); //初始化表结构信息 this.setColumnList(); String tableName; List<Column> columnList; if (isPmTable) { tableName = (hasFormatTable ? formatPmTableName : pmTableName); columnList = this.ColumnList; } else { tableName = symlTableName; columnList = this.symlColumnList; } //获取数据库中的字段名 DataTable columnsTable = this.ObjConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] { null, null, tableName, null }); int count = columnsTable.Rows.Count; int nameColumn = columnsTable.Columns.IndexOf("COLUMN_NAME"); int typeColumn = columnsTable.Columns.IndexOf("DATA_TYPE"); int lengthColumn = columnsTable.Columns.IndexOf("CHARACTER_MAXIMUM_LENGTH"); ColumnError error = null; foreach (Column tempColumn in columnList) { Boolean flag = true; for (int i = 0; i < count; i++) { DataRow dataRow = columnsTable.Rows[i]; //获取字段类型 OleDbType type = (OleDbType)dataRow.ItemArray.GetValue(typeColumn); //获取字段名称 String name = dataRow.ItemArray.GetValue(nameColumn).ToString(); //如果类型为Date或字段长度为0,则找到匹配;否则,继续判断字段长度 if (tempColumn.ColumnName.Equals(name)) { flag = false; if (tempColumn.ColumnType == type) { Int32 length; Int32.TryParse(dataRow.ItemArray.GetValue(lengthColumn).ToString(), out length); if (tempColumn.ColumnType == OleDbType.Date || tempColumn.ColumnLength == 0 || tempColumn.ColumnLength == length) { break; } else { error = new ColumnError(); error.Column = tempColumn.ColumnName; error.Error = "主库字段长度:" + tempColumn.ColumnLength + ";本地字段长度:" + length; } } else { error = new ColumnError(); error.Column = tempColumn.ColumnName; error.Error = "主库字段类型为:" + tempColumn.ColumnType + ";本地库字段类型:" + type; } result.Add(error); break; } } if (flag) { error = new ColumnError(); error.Column = tempColumn.ColumnName; error.Error = "没有找到该字段, 长度应为:" + tempColumn.ColumnLength; result.Add(error); } } this.closeOleDbConnection(); return result; }
/// <summary> /// 验证XW表中字段是否全部有效 /// </summary> /// <returns></returns> public List<ColumnError> validateTableColums() { List<ColumnError> result = new List<ColumnError>(); //打开Access数据库连接 this.openOleDbConnection(); //获取数据库中的表名 DataTable columnsTable = this.ObjConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] { null, null, "XW",null }); int count = columnsTable.Rows.Count; int nameColumn = columnsTable.Columns.IndexOf("COLUMN_NAME"); int typeColumn = columnsTable.Columns.IndexOf("DATA_TYPE"); int lengthColumn = columnsTable.Columns.IndexOf("CHARACTER_MAXIMUM_LENGTH"); ColumnError error = null; foreach (XWColumn temp in this.ColumnList) { Boolean flag = true; for (int i = 0; i < count; i++) { DataRow dataRow = columnsTable.Rows[i]; //获取字段类型 OleDbType type = (OleDbType)dataRow.ItemArray.GetValue(typeColumn); //获取字段名称 String name = dataRow.ItemArray.GetValue(nameColumn).ToString(); //如果类型为Date或列名是“中文摘要”或者是“英文摘要”,则找到了;否则,继续判断字段长度 if (temp.ColumnName.Equals(name)) { flag = false; if (temp.ColumnType == type) { Int32 length; Int32.TryParse(dataRow.ItemArray.GetValue(lengthColumn).ToString(), out length); if (temp.ColumnType == OleDbType.Date || temp.ColumnName.Equals("中文摘要") || temp.ColumnName.Equals("英文摘要") || temp.ColumnLength == length) { break; } else { error = new ColumnError(); error.Column = temp.ColumnName; error.Error="实际字段长度为"+temp.ColumnLength+";而给出的字段长度为"+length; } } else { error = new ColumnError(); error.Column = temp.ColumnName; error.Error = "实际类型为"+temp.ColumnType+";而给出的字段长度为"+type; } result.Add(error); break; } } if (flag) { error = new ColumnError(); error.Column = temp.ColumnName; error.Error = "没有找到该字段"; result.Add(error); } } return result; }