public NoSqlCommand(string sqlText, DbBase dbBase)
{
try
{
sourceSql = sqlText;
FormatSqlText(sqlText);
}
catch (Exception err)
{
Log.WriteLogToTxt(err);
}
if (IsSelect || IsUpdate || IsDelete || IsInsert)
{
MDataRow row = new MDataRow();
if (TableSchema.FillTableSchema(ref row, ref dbBase, tableName, tableName))
{
row.Conn = dbBase.conn;
action = new NoSqlAction(ref row, tableName, dbBase.Con.DataSource, dbBase.dalType);
}
}
else
{
Log.WriteLogToTxt("NoSql Grammar Error Or No Support : " + sqlText);
}
}
private static CacheManage _MemCache = CacheManage.Instance; //有可能使用MemCache操作
internal static bool GetCache(AopEnum action, AopInfo aopInfo) //Begin
{
switch (action)
{
case AopEnum.ExeNonQuery:
case AopEnum.Insert:
case AopEnum.Update:
case AopEnum.Delete:
return(false);
}
if (!IsCanOperateCache(action, aopInfo))
{
return(false);
}
string baseKey = GetBaseKey(aopInfo);
//查看是否通知我移除
string key = GetKey(action, aopInfo, baseKey);
object obj = _MemCache.Get(key);
switch (action)
{
case AopEnum.ExeMDataTableList:
if (obj != null)
{
List <MDataTable> list = new List <MDataTable>();
Dictionary <string, string> jd = JsonHelper.Split(obj.ToString());
if (jd != null && jd.Count > 0)
{
foreach (KeyValuePair <string, string> item in jd)
{
list.Add(MDataTable.CreateFrom(item.Value, null, EscapeOp.Encode));
}
}
aopInfo.TableList = list;
}
break;
case AopEnum.Select:
case AopEnum.ExeMDataTable:
if (obj != null)
{
aopInfo.Table = MDataTable.CreateFrom(obj.ToString(), null, EscapeOp.Encode);
}
break;
case AopEnum.ExeScalar:
if (obj != null)
{
aopInfo.ExeResult = obj;
}
break;
case AopEnum.Fill:
if (obj != null)
{
MDataRow row = obj as MDataRow;
if (_MemCache.CacheType == CacheType.LocalCache)
{
row = row.Clone();
}
aopInfo.Row = row;
aopInfo.IsSuccess = true;
}
break;
case AopEnum.GetCount:
if (obj != null)
{
aopInfo.RowCount = int.Parse(obj.ToString());
}
break;
case AopEnum.Exists:
if (obj != null)
{
aopInfo.ExeResult = obj;
}
break;
}
baseKey = key = null;
return(obj != null);
}
public virtual void Import()
{
//根据视图名读取ExcelConfig信息。
MDataRow excelInfo = null;
int index = 0, headCrossRowNum = 0;
string sheetName = null;
try
{
excelInfo = ExcelConfig.GetExcelRow(ObjName);
if (excelInfo != null)
{
index = excelInfo.Get <int>(Config_Excel.StartIndex, 0);
headCrossRowNum = excelInfo.Get <int>(Config_Excel.HeadCrossRowNum, 0);
sheetName = excelInfo.Get <string>(Config_Excel.Description);
}
}
catch (Exception err)
{
Log.WriteLogToTxt(err);//避免其它地方没有升级数据库表脚本。
}
MDataTable dt = ExcelHelper.ReadExcel(null, sheetName, index, headCrossRowNum, excelInfo != null);
if (!dt.Columns.Contains(LangConst.ErrorInfo))
{
dt.Columns.Add(LangConst.ErrorInfo, System.Data.SqlDbType.NVarChar);
}
dt.TableName = excelInfo != null ? ObjName : TableName;
bool result = false;
string msg = string.Empty;
ImportResult iResult = BeforeImport(dt, excelInfo, out msg);
if (iResult == ImportResult.Continue)
{
result = FormatExcel(dt, excelInfo);
if (result)
{
dt.Columns.Remove(LangConst.ErrorInfo);
result = ExcelConfig.AcceptChanges(dt, excelInfo, ObjName);// dt.AcceptChanges(AcceptOp.Auto);
}
}
else
{
result = iResult == ImportResult.True;
}
if (!result)
{
if (dt.DynamicData != null && dt.DynamicData is Exception)
{
msg = ((Exception)dt.DynamicData).Message;
msg += LangConst.ImportTemplateNotMatch;
}
if (excelStream == null)
{
excelStream = ExcelHelper.SetError(dt);
}
if (string.IsNullOrEmpty(msg))
{
msg = LangConst.ImportError + (excelStream != null ? LangConst.ImportCheckErrorInfo : LangConst.ImportCheckTemplateIsRight);
}
}
else if (string.IsNullOrEmpty(msg))
{
msg = LangConst.ImportSuccess;
}
dt.DynamicData = null;
dt = null;
GC.Collect();
jsonResult = JsonHelper.OutResult(result, msg);
}
/// <summary>
/// （新增加数据前事件）根据业务处理值
/// </summary>
/// <param name="actionRow">需要被设置值的行</param>
/// <param name="sourceRow">原始Excel被格式化后数据行</param>
/// <param name="isEnd">是否结束（即完成）</param>
public void AfterInsert(MDataRow actionRow, MDataRow sourceRow, bool isEnd)
{
}
/// <summary>
/// 获取SQl脚本
/// </summary>
/// <param name="row"></param>
/// <param name="keys"></param>
/// <returns></returns>
public static string GetSQLScript(MDataRow row, params string[] keys)
{
DalType dalType = CrossDb.GetDalType(row.TableName);
string tableName = DBTool.Keyword(row.TableName, dalType);
string where = string.Empty;
StringBuilder sb = new StringBuilder();
//sb.AppendFormat("if not EXISTS (select 1 from {0} where ", tableName);
//foreach (string key in keys)
//{
// if (where != string.Empty)
// {
// where += " and ";
// }
// where +=DBTool.Keyword(key,dalType)+"='" + row[key].ToString() + "'";
//}
//sb.Append(where);
//sb.AppendLine(")\r\nbegin");//Insert
sb.AppendFormat("insert into {0}(", tableName);//Insert
string columns = string.Empty;
foreach (var ct in row.Columns)
{
columns += DBTool.Keyword(ct.ColumnName, dalType) + ",";
}
sb.Append(columns.TrimEnd(',') + ") \r\n values(");
columns = string.Empty;
foreach (var ct in row.Columns)
{
if (row[ct.ColumnName].IsNull)
{
columns += "null,";
}
else
{
columns += "'" + row[ct.ColumnName].ToString() + "',";
}
}
sb.Append(columns.TrimEnd(',') + ")\r\n");
//sb.AppendLine("end");
//sb.AppendLine("else");
//sb.AppendLine("begin");//Update
//sb.AppendFormat("update {0} set ", tableName);//Insert
//columns = string.Empty;
//List<string> items = new List<string>();
//items.AddRange(keys);
//foreach (var ct in row.Columns)
//{
// if (items.Contains(ct.ColumnName))
// {
// continue;
// }
// if (row[ct.ColumnName].IsNull)
// {
// columns += DBTool.Keyword(ct.ColumnName,dalType)+ "=null,";
// }
// else
// {
// columns += DBTool.Keyword(ct.ColumnName,dalType) + "='" + row[ct.ColumnName].ToString() + "',";
// }
//}
//sb.AppendFormat(columns.TrimEnd(',') + " where {0}\r\n", where);
//sb.AppendLine("end \r\n");
return(sb.ToString());
}
/// <summary>
/// 批量更新或插入。
/// </summary>
/// <param name="dt"></param>
/// <param name="excelRow"></param>
/// <returns></returns>
public static bool AcceptChanges(MDataTable dt, MDataRow excelRow, string objName = null)
{
if (excelRow == null)
{
MDataTable dtImportUnique = GridConfig.GetList(objName, GridConfig.SelectType.ImportUnique);
string[] names = null;
if (dtImportUnique != null && dtImportUnique.Rows.Count > 0)
{
names = new String[dtImportUnique.Rows.Count];
for (int i = 0; i < dtImportUnique.Rows.Count; i++)
{
names[i] = dtImportUnique.Rows[i].Get <string>(Config_Grid.Field);
}
}
return(dt.AcceptChanges(AcceptOp.Auto, null, names));
}
bool result = true;
//获取相关配置
string[] tables = excelRow.Get <string>(Config_Excel.TableNames).Split(',');
MDataTable configTable = GetExcelInfo(excelRow.Get <string>(Config_Excel.ExcelID));
Dictionary <string, string> rowPrimaryValue = new Dictionary <string, string>(); //存档每个表每行的主键值。
Dictionary <string, string> wherePrimaryValue = new Dictionary <string, string>(); //存档where条件对应的主键值。
int acceptType = excelRow.Get <int>(Config_Excel.AcceptType);
using (MAction action = new MAction(tables[0]))
{
action.SetAopState(AopOp.CloseAll);
action.BeginTransation();
AppConfig.Debug.OpenDebugInfo = false;
IExcelConfig excelConfigExtend = ExcelConfigFactory.GetExcelConfigExtend();
foreach (var table in tables)
{
GC.Collect();//后面的Fill查询代码循环上万次会增长太多内存，提前调用，能降低内存。
action.ResetTable(table);
for (int i = 0; i < dt.Rows.Count; i++)
{
action.Data.Clear();
var row = dt.Rows[i];
foreach (var cell in row) //遍历所有数据行
{
if (cell.Struct.TableName != null && cell.Struct.TableName.ToLower() == table.ToLower()) //过滤出属于本表的字段。
{
string[] items = cell.ColumnName.Split('.');
string columnName = items[items.Length - 1];
action.Set(columnName, cell.Value);
}
}
#region 检测是否需要插入外键。
MDataTable foreignTable = configTable.FindAll("TableName='" + table + "' and IsForeignkey=1");
if (foreignTable != null)
{
foreach (var foreignRow in foreignTable.Rows)
{
string formatter = foreignRow.Get <string>("Formatter");
string fTableName = foreignRow.Get <string>("ForeignTable");
if (string.IsNullOrEmpty(formatter))
{
//获取主键外值键
string key = fTableName + i;
if (rowPrimaryValue.ContainsKey(key))
{
string value = rowPrimaryValue[key];
action.Set(foreignRow.Get <string>("Field"), value);
}
}
else // 从其它自定义列取值。
{
MDataCell cell = row[formatter];
cell = cell ?? row[fTableName + "." + formatter];
if (cell != null)
{
action.Set(foreignRow.Get <string>("Field"), cell.Value);
}
}
}
foreignTable = null;
}
#endregion
#region //获取唯一联合主键,检测是否重复
string where = string.Empty;
List <MDataRow> rowList = configTable.FindAll("TableName='" + table + "' and IsUnique=1");
if (rowList != null && rowList.Count > 0)
{
bool isUniqueOr = excelRow.Get <bool>(Config_Excel.WhereType);
List <MDataCell> cells = new List <MDataCell>();
string errText = string.Empty;
int errorCount = 0;
foreach (var item in rowList)
{
var cell = action.Data[item.Get <string>(Config_ExcelInfo.Field)];
if (cell != null)
{
if (cell.IsNullOrEmpty) // 唯一主键是必填写字段
{
errorCount++;
errText += "[第" + (i + 1) + "行数据]：" + cell.Struct.ColumnName + "[" + cell.Struct.Description + "]不允许为空！\r\n";
}
else
{
cells.Add(cell);
}
}
}
if (errorCount > 0)
{
if (!isUniqueOr || errorCount == rowList.Count)
{
result = false;
dt.DynamicData = new Exception(errText);
goto err;
}
}
MDataCell[] item2s = cells.ToArray();
where = action.GetWhere(!isUniqueOr, item2s);
item2s = null;
rowList = null;
}
if (!string.IsNullOrEmpty(where))
{
MDataRow data = action.Data.Clone();
action.SetSelectColumns(action.Data.PrimaryCell.ColumnName);
if (action.Fill(where))//根据条件查出主键ID (数据被清空)
{
string key = table + where;
if (wherePrimaryValue.ContainsKey(key))
{
rowPrimaryValue.Add(table + i, wherePrimaryValue[key]);//记录上一个主键值。
}
else
{
rowPrimaryValue.Add(table + i, action.Get <string>(action.Data.PrimaryCell.ColumnName)); //记录上一个主键值。
}
action.Data.LoadFrom(data, RowOp.IgnoreNull, false); //还原数据。
if (action.Data.GetState() == 2 && acceptType != 1) //排除掉仅插入选项
{
ExcelResult eResult = excelConfigExtend.BeforeUpdate(action.Data, row);
if (eResult == ExcelResult.Ignore || (eResult == ExcelResult.Default && action.Update(where)))
{
continue;//已经存在了，更新，准备下一条。
}
else
{
result = false;
dt.DynamicData = new Exception("[第" + (i + 1) + "行数据]：" + action.DebugInfo);
goto err;
}
}
else
{
continue;//已经存在了，同时没有可更新字段
}
}
else if (action.RecordsAffected == -2)//产生错误信息，发生异常
{
result = false;
dt.DynamicData = new Exception("[第" + (i + 1) + "行数据]：" + action.DebugInfo);
goto err;
}
}
#endregion
if (action.Data.GetState() == 0 || acceptType == 2) //仅更新则跳过插入
{
continue; //没有可映射插入的列。
}
//插入前，调用函数（插入特殊主键等值）
string errMsg;
ExcelResult excelResult = excelConfigExtend.BeforeInsert(action.Data, row, out errMsg);
if (excelResult == ExcelResult.Ignore)
{
continue;
}
if (excelResult == ExcelResult.Error || !action.Insert(InsertOp.ID))
{
result = false;
action.RollBack();
if (string.IsNullOrEmpty(errMsg))
{
errMsg = "[第" + (i + 1) + "行数据]：" + action.DebugInfo;
}
dt.DynamicData = new Exception(errMsg);
excelConfigExtend.OnInsertError(errMsg, dt);
goto err;
}
//插入后事件（可以触发其它事件）
excelConfigExtend.AfterInsert(action.Data, row, i == dt.Rows.Count - 1);
string primaryKey = action.Get <string>(action.Data.PrimaryCell.ColumnName);
rowPrimaryValue.Add(table + i, primaryKey);//记录上一个主键值。
if (!wherePrimaryValue.ContainsKey(table + where))
{
wherePrimaryValue.Add(table + where, primaryKey);
}
}
}
err:
action.EndTransation();
excelConfigExtend.Dispose();
}
return(result);
}
public NoSqlAction(ref MDataRow row, string fileName, string filePath, DalType dalType)
{
Reset(ref row, fileName, filePath, dalType);
}
// private static CacheManage _SchemaCache = CacheManage.Instance;//Cache����
internal static bool FillTableSchema(ref MDataRow row, ref DbBase dbBase, string tableName, string sourceTableName)
{
if (FillSchemaFromCache(ref row, ref dbBase, tableName, sourceTableName))
{
return true;
}
else//��Cache����ʧ��
{
return FillSchemaFromDb(ref row, ref dbBase, tableName, sourceTableName);
}
}
public override void GetData(object target, System.IO.Stream outgoingData)
{
MDataRow row = target as DataRow;
base.GetData(row, outgoingData);
}
private void Form1_Load(object sender, EventArgs e)
{
MDataRow row = new MDataRow();
row.LoadFrom("{name:'妹子你在哪？'}");
row.SetToAll(this);//继承自IUIValue的接口的控件，都可以用自动取值或赋值功能。
}
public ExcelResult BeforeUpdate(MDataRow actionRow, MDataRow sourceRow)
{
return ExcelResult.Default;
}
public ExcelResult BeforeInsert(MDataRow actionRow, MDataRow sourceRow, out string errMsg)
{
errMsg = string.Empty;
return ExcelResult.Default;
}
public void AfterInsert(MDataRow actionRow, MDataRow sourceRow, bool isEnd)
{
}
private List<string> autoPrefixList; //调用插入和更新,自动获取控件名的前缀
#endregion Fields
#region Constructors
public MActionUI(ref MDataRow row)
{
_Row = row;
}
public void LoadData(MDataRow row)
{
_Row = row;
}
/// <summary>
/// 可以对导入的数据进行验证
/// </summary>
public virtual ImportResult BeforeImport(MDataTable dt, MDataRow excelInfo, out string msg)
{
msg = "";
return(ImportResult.Continue);
}
public static MemoryStream CreateExcelHeader(MDataTable header, Dictionary <string, string[]> validateData)
{
MemoryStream ms = new MemoryStream();
if (header != null && header.Rows.Count > 0)
{
MDataTable importHeader = header.FindAll("Import=1");
try
{
XSSFWorkbook export = new XSSFWorkbook();
ICellStyle style = GetStyle(export, HSSFColor.LightOrange.Index);
ISheet sheet = export.CreateSheet("Sheet1");//创建内存Excel
#region 创建引用
int rowStartIndex = 1;
CreateValidationSheet(export, validateData, rowStartIndex);
#endregion
importHeader.Rows.Sort("ORDER BY MergeIndexed DESC");//Hidden=0 AND (Export=1 OR Field LIKE 'mg_%')
MDataTable headTable = importHeader.Clone();
int ColTitleRowCount = 0;
Dictionary <string, int> formatdic = new Dictionary <string, int>();
for (int i = importHeader.Rows.Count - 1; i >= 0; i--)//MDataTable 不支持 NOT LIKE
{
if (importHeader.Rows[i]["Field"].Value.ToString().IndexOf("mg") > -1)
{
importHeader.Rows.RemoveAt(i);//非字段列移除
}
}
int colSum = importHeader.Rows.Count;//实际列数
importHeader.Rows.Sort("ORDER BY OrderNum ASC");
if (!ExportMulHeader(header, true))
{
IRow row = sheet.CreateRow(0);
ICell cell;
for (int i = 0; i < colSum; i++)
{
string title = importHeader.Rows[i]["Title"].Value.ToString();
cell = row.CreateCell(i);
cell.SetCellValue(title);//设置列头
sheet.SetColumnWidth(i, 3000);
cell.CellStyle = style;
}
}
else
{
CreateMulHeadExcel(export, headTable, out ColTitleRowCount, colSum);
ColTitleRowCount -= 1;
}
for (int i = 0; i < importHeader.Rows.Count; i++)
{
string formater = importHeader.Rows[i].Get <string>("Formatter");
if (!string.IsNullOrEmpty(formater) && formater.Length > 1 && !formatdic.ContainsKey(formater))
{
formatdic.Add(formater, i);//存储列索引
}
}
int maxRow = 50000;//限制最大行数(07之前版本的excel最大行数为65536，但NOPI似乎没有支持到最大行数,这里设置为50000行,到60000行数据有效性失效)
XSSFSheet xssfSheet = (XSSFSheet)sheet;
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(xssfSheet);
for (int i = 0; i < importHeader.Rows.Count; i++)
{
MDataRow dtRow = importHeader.Rows[i];
string formatter = dtRow.Get <string>("Formatter");
if (formatter == "boolFormatter")
{
formatter = "#是否"; //对bool型特殊处理。
}
if (!string.IsNullOrEmpty(formatter) && formatter.StartsWith("#") && validateData != null && formatter.Length > 1) //&& validateData.ContainsKey(formatter)
{
//处理数据的有效性
CellRangeAddressList regions = null;
IDataValidationConstraint constraint = null;
IDataValidation dataValidate = null;
//int maxRow = 65535;
if (validateData.ContainsKey(formatter))
{
regions = new CellRangeAddressList(ColTitleRowCount + 1, maxRow, i, i);
string key = formatter.Split('=')[0].Replace("#", "");// "V" + (char)formatter.Length;// formatter.Replace("#", "V");
/*03版本api
* constraint = DVConstraint.CreateFormulaListConstraint(key);//);//validateData[formatter]
* dataValidate = new HSSFDataValidation(regions, constraint);
*/
constraint = dvHelper.CreateFormulaListConstraint(key);
dataValidate = dvHelper.CreateValidation(constraint, regions);
sheet.AddValidationData(dataValidate);
//regions = new CellRangeAddressList(ColTitleRowCount, maxRow, i, i);
//string key = formatter.Split('=')[0].Replace("#", "");// "V" + (char)formatter.Length;// formatter.Replace("#", "V");
//constraint = DVConstraint.CreateFormulaListConstraint(key);//);//validateData[formatter]
//dataValidate = new HSSFDataValidation(regions, constraint);
//sheet.AddValidationData(dataValidate);
}
//
if (formatter.StartsWith("#C"))//级联要接着父级后加数据有效性才行
{
string Parentformatter = formatter;
while (formatdic.ContainsKey(Parentformatter))
{
int point = 0;
int parentindex = formatdic[Parentformatter];
formatdic.Remove(Parentformatter);
foreach (var item in formatdic)
{
if (item.Key.IndexOf('=') > -1)
{
string parent = item.Key.Split('=')[1];
parent = parent.Replace(">", "#");
if (parent.Equals(Parentformatter.Split('=')[0]))
{
int selfindex = item.Value;
//int parentindex = formatdic[hereformatter];
string t = IntToMoreChar(parentindex);
for (int im = ColTitleRowCount; im < maxRow; im++)
{
string func = string.Format("@INDIRECT({0}{1})", t, im + 1);
regions = new CellRangeAddressList(im, im, selfindex, selfindex);
constraint = dvHelper.CreateFormulaListConstraint(func);
dataValidate = dvHelper.CreateValidation(constraint, regions);
sheet.AddValidationData(dataValidate);
}
//for (int im = ColTitleRowCount; im < maxRow; im++)//1000应为maxRow
//{
// string func = "INDIRECT(" + t + (im + 1) + ")";//excel2013不能级联，03可以，其他没测过
// regions = new CellRangeAddressList(ColTitleRowCount, im, selfindex, selfindex);
// constraint = DVConstraint.CreateFormulaListConstraint(func);
// dataValidate = new HSSFDataValidation(regions, constraint);
// sheet.AddValidationData(dataValidate);
//}
Parentformatter = item.Key;
break;
}
}
point += 1;
}
if (point.Equals(formatdic.Count))
{
Parentformatter = string.Empty;
}
}
}
}
}
export.Write(ms);
ms.Flush();
ms.Close();
}
catch (Exception err)
{
Log.WriteLogToTxt(err);
}
}
return(ms);
}
/// <summary>
/// 类型转换(精准强大)
/// </summary>
/// <param name="value">值处理</param>
/// <param name="t">类型</param>
/// <returns></returns>
public static object ChangeType(object value, Type t)
{
if (t == null)
{
return(null);
}
string strValue = Convert.ToString(value);
if (t.IsEnum)
{
if (strValue != "")
{
if (Enum.IsDefined(t, strValue))
{
return(Enum.Parse(t, strValue));
}
int v = 0;
if (int.TryParse(strValue, out v))
{
object v1 = Enum.Parse(t, strValue);
if (v1.ToString() != strValue)
{
return(v1);
}
}
string[] names = Enum.GetNames(t);
string lower = strValue.ToLower();
foreach (string name in names)
{
if (name.ToLower() == lower)
{
return(Enum.Parse(t, name));
}
}
}
//取第一个值。
string firstKey = Enum.GetName(t, -1);
if (!string.IsNullOrEmpty(firstKey))
{
return(Enum.Parse(t, firstKey));
}
return(Enum.Parse(t, Enum.GetNames(t)[0]));
}
if (value == null)
{
return(t.IsValueType ? Activator.CreateInstance(t) : null);
}
if (t.FullName == "System.Object")
{
return(value);
}
if (t.FullName == "System.Type")
{
return((Type)value);
}
if (t.FullName == "System.IO.Stream" && value is HttpPostedFile)
{
return(((HttpPostedFile)value).InputStream);
}
if (t.IsGenericType && t.Name.StartsWith("Nullable"))
{
t = Nullable.GetUnderlyingType(t);
if (strValue == "")
{
return(null);
}
}
if (t.Name == "String")
{
if (value is byte[])
{
return(Convert.ToBase64String((byte[])value));
}
return(strValue);
}
if (t.FullName == "System.Text.StringBuilder")
{
return(value as StringBuilder);
}
if (t.FullName == "System.Text.Encoding")
{
return(value as Encoding);
}
if (strValue.Trim() == "")
{
if (t.Name.EndsWith("[]"))
{
return(null);
}
return(Activator.CreateInstance(t));
}
else if (t.IsValueType)
{
strValue = strValue.Trim('\r', '\n', '\t', ' ');
if (t.Name == "DateTime")
{
switch (strValue.ToLower().TrimEnd(')', '('))
{
case "now":
case "getdate":
case "current_timestamp":
return(DateTime.Now);
}
if (DateTime.Parse(strValue) == DateTime.MinValue)
{
return((DateTime)SqlDateTime.MinValue);
}
return(Convert.ChangeType(value, t));//这里用value，避免丢失毫秒
}
else if (t.Name == "Guid")
{
if (strValue == SqlValue.Guid || strValue.StartsWith("newid"))
{
return(Guid.NewGuid());
}
else if (strValue.ToLower() == "null")
{
return(Guid.Empty);
}
return(new Guid(strValue));
}
else
{
switch (strValue.ToLower())
{
case "yes":
case "true":
case "success":
case "1":
case "on":
case "是":
if (t.Name == "Boolean")
{
return(true);
}
else
{
strValue = "1";
}
break;
case "no":
case "false":
case "fail":
case "0":
case "":
case "否":
case "null":
if (t.Name == "Boolean")
{
return(false);
}
else
{
strValue = "0";
}
break;
case "infinity":
case "正无穷大":
if (t.Name == "Double" || t.Name == "Single")
{
return(double.PositiveInfinity);
}
break;
case "-infinity":
case "负无穷大":
if (t.Name == "Double" || t.Name == "Single")
{
return(double.NegativeInfinity);
}
break;
default:
if (t.Name == "Boolean")
{
return(false);
}
break;
}
if (t.Name.StartsWith("Int") || t.Name == "Byte")
{
if (strValue.IndexOf('.') > -1)//11.22
{
strValue = strValue.Split('.')[0];
}
else if (value.GetType().IsEnum)
{
return((int)value);
}
}
}
return(Convert.ChangeType(strValue, t));
}
else
{
Type valueType = value.GetType();
//if(valueType.IsEnum && t.is)
if (valueType.FullName != t.FullName)
{
switch (ReflectTool.GetSystemType(ref t))
{
case SysType.Custom:
return(MDataRow.CreateFrom(strValue).ToEntity(t));
case SysType.Generic:
if (t.Name.StartsWith("List") || t.Name.StartsWith("IList"))
{
return(MDataTable.CreateFrom(strValue).ToList(t));
}
break;
case SysType.Array:
if (t.Name == "Byte[]")
{
if (valueType.Name == "String")
{
return(Convert.FromBase64String(strValue));
}
using (MemoryStream ms = new MemoryStream())
{
new BinaryFormatter().Serialize(ms, value);
return(ms.ToArray());
}
}
break;
}
}
return(Convert.ChangeType(value, t));
}
}
private static bool FillSchemaFromDb(ref MDataRow row, ref DbBase dbBase, string tableName, string sourceTableName)
{
try
{
MDataColumn mdcs = null;
//if (tableName.IndexOf('(') > -1 && tableName.IndexOf(')') > -1)//�Զ�����ͼtable
//{
// dbBase.tempSql = "view";//ʹ��access��ʽ������
//}
mdcs = GetColumns(tableName, ref dbBase);
if (mdcs.Count == 0)
{
return false;
}
row = mdcs.ToRow(sourceTableName);
row.TableName = sourceTableName;
string key = GetSchemaKey(tableName, dbBase.DataBase, dbBase.dalType);
CacheManage.LocalInstance.Add(key, mdcs.Clone(), null, 1440);
switch (dbBase.dalType)//�ı����ݿⲻ���档
{
case DalType.Access:
case DalType.SQLite:
case DalType.MsSql:
case DalType.MySql:
case DalType.Oracle:
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
string folderPath = AppDomain.CurrentDomain.BaseDirectory + AppConfig.DB.SchemaMapPath;
if (System.IO.Directory.Exists(folderPath))
{
mdcs.WriteSchema(folderPath + key + ".ts");
}
}
break;
}
return true;
}
catch (Exception err)
{
Log.WriteLogToTxt(err);
return false;
}
}
public ExcelResult BeforeInsert(MDataRow actionRow, MDataRow sourceRow, out string errMsg)
{
errMsg = string.Empty;
return(ExcelResult.Default);
}
public void LoadData(MDataTable table)
{
_Table = table;
if (_Table.Rows.Count > 0)
{
_Row = _Table.Rows[0];
}
}
/// <summary>
/// 读取上传文件中的Excel（返回表格，如果需要导入，还需要格式化表头）
/// </summary>
/// <param name="workbook">Excel工作本</param>
/// <param name="sheetIndex">第N个Sheet表格</param>
/// <param name="isMergedCellName">是否把合并列头的名称连接起来</param>
/// <param name="startIndex">开始索引</param>
/// <param name="headCrossRowNum">头部跨行数（为0时自动识别）</param>
/// <returns></returns>
private static MDataTable ReadExcel(IWorkbook workbook, Stream stream = null, int sheetIndex = 0, int startIndex = 0, int headCrossRowNum = 0, bool isMergedCellName = false)
{
MDataTable dt = new MDataTable();
try
{
if (workbook != null)
{
ISheet sheet = workbook.GetSheetAt(sheetIndex);
dt.TableName = sheet.SheetName;
dt.DynamicData = sheet;
IRow excelRow = sheet.GetRow(startIndex);
int dataRowStart = startIndex;
if (headCrossRowNum <= 0)
{
dataRowStart += 1;
#region 遍历、找出(头部跨行数)最大行。用最大行进行遍历列（如果为空，往上一级找）
int mIndex = 0;
for (int i = 0; i < excelRow.Cells.Count; i++)
{
ICell cell = excelRow.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);// .Cells[i];
if (cell.IsMergedCell)
{
NPOI.SS.Util.CellRangeAddress range = sheet.GetCellRange(cell);//获取范围块。
if (range != null)
{
dataRowStart = Math.Max(dataRowStart, range.LastRow + 1);//设置数据的读取行数。
mIndex++;
i += range.LastColumn - range.FirstColumn;
}
}
}
#endregion
}
else
{
dataRowStart += headCrossRowNum;
}
//读取列头。
if (dataRowStart > 1)
{
excelRow = sheet.GetRow(dataRowStart - 1);
}
dt.RecordsAffected = dataRowStart;
dt.Columns.CheckDuplicate = false;
#region 读取列头
//if (excelRow.FirstCellNum > 0)
//{
// for (int i = 0; i < excelRow.FirstCellNum; i++)
// {
// string columnName = "该列头为空_" + i;
// dt.Columns.Add(columnName);
// }
//}
int emptyCellCount = 0;//兼容处理错误的Excel格式（读了256个空格列）
for (int i = 0; i < excelRow.Cells.Count; i++)
{
string columnName = string.Empty;
for (int j = dataRowStart; j > startIndex; j--)
{
#region MyRegion
IRow row = sheet.GetRow(j - 1);
ICell cell;
try
{
cell = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);//不能用GetCell(i)，会多出一行导致下面错误位。
}
catch (Exception)
{
continue;
}
string name = cell.ToString().Trim(); // .StringCellValue.Trim();
if (!string.IsNullOrEmpty(name) && !columnName.Contains(name)) //
{
columnName += name + "_";
}
else if (j != dataRowStart && cell.IsMergedCell)
{
cell = sheet.GetMergedRegion(cell);//获取范围块。
if (cell != null)
{
name = cell.ToString().Trim();
if (!string.IsNullOrEmpty(name) && !columnName.Contains(name))
{
columnName += name + "_";
}
}
}
if (!isMergedCellName && !string.IsNullOrEmpty(columnName))
{
break;
}
#endregion
}
columnName = columnName.TrimEnd('_').Trim();
if (string.IsNullOrEmpty(columnName))
{
if (emptyCellCount > 30)//连续30次空格列
{
break;
}
emptyCellCount++;
columnName = "该列头为空_" + i;
}
else
{
emptyCellCount = 0;//只要一个正常，即回归索引
}
if (dt.Columns.Contains(columnName))
{
columnName += "_" + i;
}
dt.Columns.Add(columnName);
}
//移除空格列
if (emptyCellCount > 0)
{
dt.Columns.RemoveRange(dt.Columns.Count - emptyCellCount, emptyCellCount);
}
if (dt.Columns.Count > 0)
{
dt.Conn = dt.Columns.Count.ToString();//找个变量存储实际的列的长度，在SetError中使用。（dt可能在SetError前列被变更)
}
#endregion
ICell sheetCell;
int emptyCount = 0;
for (int i = dataRowStart; i <= sheet.LastRowNum; i++)
{
excelRow = sheet.GetRow(i);
if (excelRow == null)
{
break;
}
MDataRow tbRow = dt.NewRow();
bool isOk = false;
for (int j = 0; j < dt.Columns.Count; j++)
{
#region 读一行
sheetCell = excelRow.GetCell(j, MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (sheetCell != null)
{
string value = string.Empty;
if (sheetCell.CellType == CellType.Numeric)
{
try
{
if (sheetCell.ToString().Split('/', '-').Length > 1)
{
value = sheetCell.DateCellValue.ToString();
}
else
{
value = sheetCell.NumericCellValue.ToString();
}
}
catch
{
value = sheetCell.ToString();
}
}
else if (sheetCell.CellType == CellType.Formula)
{
try
{
/*公式不一定是Numeric的取值，也有可能是=G4 这种，然而单元格G4不是数字;
* 公式单元格也有可能读取错误 #VALUE! #REF! 等*/
CellType resultType = sheetCell.CachedFormulaResultType;
switch (resultType)
{
case CellType.Boolean:
value = sheetCell.BooleanCellValue.ToString();
break;
case CellType.Numeric:
value = sheetCell.NumericCellValue.ToString();
break;
case CellType.Blank:
case CellType.Error:
case CellType.Unknown:
value = string.Empty;
break;
default:
value = sheetCell.StringCellValue;
break;
}
//value = sheetCell.NumericCellValue.ToString(); //由公式取值
}
catch
{
}
}
else
{
value = sheetCell.ToString();
}
value = value.Trim();
if (!isOk && !string.IsNullOrEmpty(value))
{
isOk = true;
}
if (!string.IsNullOrEmpty(value))//空值当Null值处理，避免字段有Check对空值的约束
{
tbRow.Set(sheetCell.ColumnIndex, value);
}
}
#endregion
}
if (isOk)//忽略空行数据。
{
dt.Rows.Add(tbRow);
}
else
{
if (dt.Rows.Count == 0)
{
dt.RecordsAffected++;
}
emptyCount++;
if (emptyCount > 1)//超过2次空格行，跳出。
{
break;
}
}
}
workbook.Close();//关闭了，dt.DynamicData带出了Sheet,还是可以后续使用（估计NPOI的没处理）
}
}
catch (Exception err)
{
Log.WriteLogToTxt(err);
}
return(dt);
}
/// <summary>
/// 导入时：把中文列头翻译成英文列头（同时处理列头结构）。
/// 并返回字典：key:列头,value:格式化名
/// </summary>
public static Dictionary <string, string> FormatterTitle(MDataTable dt, MDataRow info, string objName)
{
if (info == null)
{
return(GridConfig.SetHeaderField(dt, objName));
}
else
{
Dictionary <string, string> formatDic = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);
MDataTable infoConfig = GetExcelInfo(info.Get <string>(0));
if (infoConfig != null)
{
//附加自定义列。
foreach (var configRow in infoConfig.Rows)
{
string formatter = configRow.Get <string>(Config_ExcelInfo.Formatter);
if (!string.IsNullOrEmpty(formatter) && formatter[0] != '#')//增加默认值的列。
{
string excelName = configRow.Get <string>(Config_ExcelInfo.ExcelName);
if (!dt.Columns.Contains(excelName))
{
MCellStruct ms = new MCellStruct(excelName, System.Data.SqlDbType.NVarChar);
ms.TableName = configRow.Get <string>(Config_ExcelInfo.TableName);
dt.Columns.Insert(dt.Columns.Count - 1, ms);
}
}
}
MDataRow infoRow;
foreach (MCellStruct item in dt.Columns)
{
infoRow = infoConfig.FindRow(Config_ExcelInfo.ExcelName + "='" + item.ColumnName + "'");
if (infoRow == null && item.ColumnName.IndexOf('_') > 0) // 兼容只找一级的映射列。
{
string columnName = item.ColumnName.Split('_')[0];
infoRow = infoConfig.FindRow(Config_ExcelInfo.ExcelName + "='" + columnName + "'");
}
if (infoRow != null)
{
string field = infoRow.Get <string>(Config_ExcelInfo.Field);
if (string.IsNullOrEmpty(field))
{
continue;
}
item.Description = item.ColumnName;//把中文列名放到描述里。
item.TableName = infoRow.Get <string>(Config_ExcelInfo.TableName);
if (string.Compare(item.ColumnName, field, StringComparison.OrdinalIgnoreCase) != 0)
{
int index = dt.Columns.GetIndex(field);
if (index < 0)
{
item.ColumnName = field;//
}
else // 字段同名
{
item.ColumnName = item.TableName + "." + field;
//修改上一个，也增加表名。
dt.Columns[index].ColumnName = dt.Columns[index].TableName + "." + dt.Columns[index].ColumnName;
}
}
string formatter = infoRow.Get <string>(Config_ExcelInfo.Formatter);
if (!string.IsNullOrEmpty(formatter)) // 需要格式化的项
{
if (formatter.Length > 2 && formatter[0] == '#')
{
//item.SqlType = System.Data.SqlDbType.NVarChar;//重置数据类型(int数据将格式成文本）
formatDic.Add(item.ColumnName, formatter.Substring(1).Split(new string[] { "=>" }, StringSplitOptions.None)[0]);
}
else
{
item.DefaultValue = SqlCode.FormatPara(formatter);//如果不是#开头的，设置为默认值（同时处理@参数）。
}
}
}
}
}
return(formatDic);
}
}
/// <summary>
/// 将错误信息写回原始存档中，并返回文件流。
/// </summary>
public static MemoryStream SetError(MDataTable dt, bool isReturnStream = true)
{
if (dt != null && dt.Columns.Contains("错误信息") && dt.DynamicData != null && dt.DynamicData is ISheet)
{
try
{
ISheet sheet = dt.DynamicData as ISheet;
IWorkbook workBook = sheet.Workbook;
ICellStyle style = GetStyle(workBook, HSSFColor.Red.Index);
int dataRowStart = dt.RecordsAffected;
IRow excelRow = null;
excelRow = sheet.GetRow(0);//处理列头的前面有垃圾数据导致列头比下面的列头少的情况
IRow dataRow = sheet.GetRow(dataRowStart - 1);
int firstRow = 0;
if (excelRow.LastCellNum < dataRow.LastCellNum)
{
excelRow = dataRow;
firstRow = dataRowStart - 1;
}
//int cellCount = excelRow.Cells.Count;
int cellCount;
if (!int.TryParse(dt.Conn, out cellCount))
{
cellCount = excelRow.LastCellNum; //修复左上角第一个单元格为空时，错误信息错位的问题
}
if (cellCount > 255) //如果出现256列（后面全是空格列）
{
cellCount = dt.Columns.Count;
}
ICell errorCell = excelRow.CreateCell(cellCount, CellType.String);
sheet.SetColumnWidth(cellCount, 100 * 256);
if (dataRowStart > 1)
{
//添加错误信息列
CellRangeAddress cellAddress = new CellRangeAddress(firstRow, dataRowStart - 1, cellCount, cellCount);
sheet.AddMergedRegion(cellAddress);
}
errorCell.SetCellValue("错误信息");
bool hasError = false;
int errColumnIndex = dt.Columns.GetIndex("错误信息");
for (int i = 0; i < dt.Rows.Count; i++)
{
MDataRow dtRow = dt.Rows[i];
string value = dtRow[errColumnIndex].ToString();
if (!string.IsNullOrEmpty(value))
{
IRow row = sheet.GetRow(i + dataRowStart);
if (row == null)
{
break;
}
hasError = true;
ICell cell = row.CreateCell(cellCount, CellType.String);
cell.SetCellValue(value);
cell.CellStyle = style;
for (int j = 0; j < dt.Columns.Count; j++) //标识错误的格的背景色为红色。
{
if (dtRow[j].State == -1) // && j < row.Cells.Count
{
cell = row.GetCell(j, MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (cell != null)
{
cell.CellStyle = style;
}
}
}
}
}
if (hasError && isReturnStream)
{
MemoryStream ms = new MemoryStream();
workBook.Write(ms);
return(ms);
}
return(null);
}
catch (Exception err)
{
Log.WriteLogToTxt(err);
}
}
return(null);
}
/// <summary>
/// 类型转换(精准强大)
/// </summary>
/// <param name="value">值处理</param>
/// <param name="t">类型</param>
/// <returns></returns>
public static object ChangeType(object value, Type t)
{
if (t == null)
{
return(null);
}
if (t.FullName == "System.Type")
{
return((Type)value);
}
string strValue = Convert.ToString(value);
if (t.IsGenericType && t.Name.StartsWith("Nullable"))
{
t = Nullable.GetUnderlyingType(t);
if (strValue == "")
{
return(null);
}
}
if (t.Name == "String")
{
if (value is byte[])
{
return(Convert.ToBase64String((byte[])value));
}
return(strValue);
}
if (t.FullName == "System.Text.StringBuilder")
{
return(value as StringBuilder);
}
if (t.FullName == "System.Text.Encoding")
{
return(value as Encoding);
}
if (strValue == "")
{
return(Activator.CreateInstance(t));
}
else if (t.IsValueType)
{
if (t.Name == "DateTime")
{
switch (strValue.ToLower().TrimEnd(')', '('))
{
case "now":
case "getdate":
case "current_timestamp":
return(DateTime.Now);
}
if (DateTime.Parse(strValue) == DateTime.MinValue)
{
return((DateTime)SqlDateTime.MinValue);
}
return(Convert.ChangeType(value, t));//这里用value，避免丢失毫秒
}
else if (t.Name == "Guid")
{
if (strValue == SqlValue.Guid || strValue.StartsWith("newid"))
{
return(Guid.NewGuid());
}
return(new Guid(strValue));
}
else
{
switch (strValue.ToLower())
{
case "yes":
case "true":
case "1":
case "on":
case "是":
if (t.Name == "Boolean")
{
return(true);
}
else
{
strValue = "1";
}
break;
case "no":
case "false":
case "0":
case "":
case "否":
if (t.Name == "Boolean")
{
return(false);
}
else
{
strValue = "0";
}
break;
case "infinity":
case "正无穷大":
if (t.Name == "Double" || t.Name == "Single")
{
return(double.PositiveInfinity);
}
break;
case "-infinity":
case "负无穷大":
if (t.Name == "Double" || t.Name == "Single")
{
return(double.NegativeInfinity);
}
break;
default:
if (t.Name == "Boolean")
{
return(false);
}
break;
}
if (t.Name.StartsWith("Int"))
{
if (strValue.IndexOf('.') > -1)//11.22
{
strValue = strValue.Split('.')[0];
}
else if (value.GetType().IsEnum)
{
return((int)value);
}
}
else if (t.IsEnum)
{
return(Enum.Parse(t, strValue, true));
}
}
return(Convert.ChangeType(strValue, t));
}
else
{
Type valueType = value.GetType();
//if(valueType.IsEnum && t.is)
if (valueType.FullName != t.FullName)
{
switch (ReflectTool.GetSystemType(ref t))
{
case SysType.Custom:
return(MDataRow.CreateFrom(strValue).ToEntity(t));
case SysType.Generic:
if (t.Name.StartsWith("List"))
{
return(MDataTable.CreateFrom(strValue).ToList(t));
}
break;
case SysType.Array:
if (t.Name == "Byte[]")
{
if (valueType.Name == "String")
{
return(Convert.FromBase64String(strValue));
}
using (MemoryStream ms = new MemoryStream())
{
new BinaryFormatter().Serialize(ms, value);
return(ms.ToArray());
}
}
break;
}
}
return(Convert.ChangeType(value, t));
}
}
/// <summary>
/// 生成Sheet的级联指定
/// </summary>
private static void CreateCascadeSheet(ISheet sheet, MDataTable header, Dictionary <string, string[]> validateData, int maxLevel)
{
Dictionary <string, int> formatdic = new Dictionary <string, int>();
MDataTable[] dt2 = header.Split(Config_Grid.Field + " like 'mg_%'");
header = dt2[1];//去掉多级表头的数据。
for (int i = 0; i < header.Rows.Count; i++)
{
string formater = header.Rows[i].Get <string>(Config_Grid.Formatter);
if (!string.IsNullOrEmpty(formater) && formater.Length > 1 && !formatdic.ContainsKey(formater))
{
formatdic.Add(formater, i);//存储列索引
}
}
int maxRow = 1000;//限制最大行数(07之前版本的excel最大行数为65536，但NOPI似乎没有支持到最大行数,这里设置为50000行,到60000行数据有效性失效)
IDataValidationHelper dvHelper = sheet.GetDataValidationHelper();
for (int i = 0; i < header.Rows.Count; i++)
{
MDataRow dtRow = header.Rows[i];
string formatter = dtRow.Get <string>(Config_Grid.Formatter);
if (formatter == "boolFormatter")
{
formatter = "#是否"; //对bool型特殊处理。
}
if (!string.IsNullOrEmpty(formatter) && formatter.StartsWith("#") && validateData != null && formatter.Length > 1) //&& validateData.ContainsKey(formatter)
{
//处理数据的有效性
CellRangeAddressList regions = null;
IDataValidationConstraint constraint = null;
IDataValidation dataValidate = null;
if (validateData.ContainsKey(formatter))
{
regions = new CellRangeAddressList(maxLevel, maxRow, i, i);
string key = formatter.Split('=')[0].Replace("#", "").Replace(" ", "");// "V" + (char)formatter.Length;// formatter.Replace("#", "V");
constraint = dvHelper.CreateFormulaListConstraint(key);
dataValidate = dvHelper.CreateValidation(constraint, regions);
sheet.AddValidationData(dataValidate);
}
if (formatter.StartsWith("#C"))//级联要接着父级后加数据有效性才行
{
string parentFormatter = formatter;
while (formatdic.ContainsKey(parentFormatter))
{
int point = 0;
int parentindex = formatdic[parentFormatter];
formatdic.Remove(parentFormatter);
foreach (var item in formatdic)
{
if (item.Key.IndexOf('=') > -1)
{
string parent = item.Key.Split('=')[1];
parent = parent.Replace(">", "#");
if (parent.Equals(parentFormatter.Split('=')[0]))
{
int selfindex = item.Value;
string t = ConvertIndexToChar(parentindex);
for (int im = maxLevel; im < maxRow; im++)
{
string func = string.Format("INDIRECT(${0}${1})", t, im + 1);
regions = new CellRangeAddressList(im, im, selfindex, selfindex);
constraint = dvHelper.CreateFormulaListConstraint(func);
dataValidate = dvHelper.CreateValidation(constraint, regions);
sheet.AddValidationData(dataValidate);
}
parentFormatter = item.Key;
break;
}
}
point += 1;
}
if (point.Equals(formatdic.Count))
{
parentFormatter = string.Empty;
}
}
}
}
}
}
public ExcelResult BeforeUpdate(MDataRow actionRow, MDataRow sourceRow)
{
return(ExcelResult.Default);
}
/// <summary>
/// 获得多表头层级，并分组(算法和前端Aries.Common.Js里的getColumnGroup一致)
/// </summary>
private static Dictionary <int, List <MDataRow> > GetColumnGroup(MDataTable header)
{
Dictionary <int, List <MDataRow> > result = new Dictionary <int, List <MDataRow> >();
int index = 0;
int[] num = new int[6];
for (int i = 0; i < header.Rows.Count; i++)
{
MDataRow row = header.Rows[i];
if (row.Get <string>(Config_Grid.Field).IndexOf("mg_") != -1)
{
int colspan = row.Get <int>(Config_Grid.Colspan, 1);
if (num[index] > 0)//内部嵌套
{
index++;
num[index] = colspan;
num[index - 1] = num[index - 1] - num[index];//父级数字要减掉子级的数量
}
else
{
num[index] = colspan;
}
if (!result.ContainsKey(index))
{
result.Add(index, new List <MDataRow>());
}
result[index].Add(row);
}
else
{
var level = (num[index] > 0) ? index + 1 : index;
if (num[index] > 0)
{
num[index]--;
if (num[index] == 0) //列已经够了
{
if (index > 0) //如果是子级
{
index--;
}
}
}
if (!result.ContainsKey(level))
{
result.Add(level, new List <MDataRow>());
}
result[level].Add(row);
}
}
//设置RowSpan属性
int maxLen = result.Count;
for (int i = 0; i < result.Count; i++)
{
for (int k = 0; k < result[i].Count; k++)
{
MDataRow row = result[i][k];
if (row.Get <int>(Config_Grid.Rowspan, 1) == 1 && !row.Get <string>(Config_Grid.Field).StartsWith("mg_"))
{
row.Set(Config_Grid.Rowspan, maxLen - i);
}
}
}
return(result);
}
internal MDataCell[] GetJoinPrimaryCell(MDataRow row)
{
MDataCell[] cells = null;
if (jointPrimaryIndex != null && jointPrimaryIndex.Count > 0)
{
cells = new MDataCell[jointPrimaryIndex.Count];
for (int i = 0; i < jointPrimaryIndex.Count; i++)
{
cells[i] = row[jointPrimaryIndex[i]];
}
}
else
{
cells = row.JointPrimaryCell.ToArray();
}
return cells;
}
private static void FillTable(string objName, string objCode, MDataTable dt)
{
//Dictionary<string, string> fieldTitleDic = GridConfig.FieldTitle;
string errInfo;
string tableName = objCode;
MDataColumn mdc = DBTool.GetColumns(tableName, null, out errInfo);
if (mdc == null || mdc.Count == 0)
{
if (!string.IsNullOrEmpty(errInfo))
{
dt.DynamicData = errInfo;
}
return;
}
MCellStruct cell = null;
int jointPrimaryCount = mdc.JointPrimary.Count;
for (int i = 0; i < mdc.Count; i++)
{
cell = mdc[i];
MDataRow row = dt.NewRow();
row.Set(Config_Grid.ObjName, objName);
row.Set(Config_Grid.Field, cell.ColumnName);
row.Set(Config_Grid.Title, string.IsNullOrEmpty(cell.Description) ? cell.ColumnName : cell.Description);
row.Set(Config_Grid.Hidden, (i == 0 && jointPrimaryCount < 2) || i > 25);//超过25个字段，后面的都先隐藏。
row.Set(Config_Grid.OrderNum, (i + 1) * 10);
row.Set(Config_Grid.Width, 100);
row.Set(Config_Grid.Sortable, i > 0);
row.Set(Config_Grid.Import, i > 0);
row.Set(Config_Grid.Export, i > 0);
row.Set(Config_Grid.Colspan, 1);
row.Set(Config_Grid.Rowspan, 1);
row.Set(Config_Grid.Edit, i > 0 || jointPrimaryCount > 1);
row.Set(Config_Grid.Frozen, i < 4);
row.Set(Config_Grid.Align, "center");
string value = DataType.GetType(cell.SqlType).Name.ToLower() + "," + cell.MaxSize + "," + cell.Scale + (cell.IsCanNull ? ",0" : ",1") + (cell.IsPrimaryKey ? ",1" : ",0");
row.Set(Config_Grid.DataType, value);
if (i == 0)
{
if (jointPrimaryCount < 2)
{
row.Set(Config_Grid.Formatter, "#");
}
}
else
{
switch (DataType.GetGroup(cell.SqlType))
{
case 2:
row.Set(Config_Grid.Formatter, "dateFormatter");
break;
case 3:
row.Set(Config_Grid.Formatter, "boolFormatter");
break;
default:
if (cell.MaxSize > 50)
{
row.Set(Config_Grid.Formatter, "stringFormatter");
}
break;
}
}
dt.Rows.Add(row);
}
}
public void Import()
{
//根据视图名读取ExcelConfig信息。
MDataRow excelInfo = null;
int index = 0, headCrossRowNum = 0;
string sheetName = null;
try
{
excelInfo = ExcelConfig.GetExcelRow(ObjName);
if (excelInfo != null)
{
index = excelInfo.Get <int>(Config_Excel.StartIndex, 0);
headCrossRowNum = excelInfo.Get <int>(Config_Excel.HeadCrossRowNum, 0);
sheetName = excelInfo.Get <string>(Config_Excel.Description);
}
}
catch (Exception err)
{
Log.WriteLogToTxt(err);//避免其它地方没有升级数据库表脚本。
}
MDataTable dt = ExcelHelper.ReadExcel(null, sheetName, index, headCrossRowNum, excelInfo != null);
if (!dt.Columns.Contains("错误信息"))
{
dt.Columns.Add("错误信息", System.Data.SqlDbType.NVarChar);
}
dt.TableName = excelInfo != null ? ObjName : TableName;
bool result = false;
string msg = string.Empty;
ImportResult iResult = BeforeImport(dt, excelInfo, out msg);
if (iResult == ImportResult.Continue)
{
result = FormatExcel(dt, excelInfo);
if (result)
{
result = ExcelConfig.AcceptChanges(dt, excelInfo, ObjName);// dt.AcceptChanges(AcceptOp.Auto);
}
}
else
{
result = iResult == ImportResult.True;
}
if (!result)
{
if (dt.DynamicData != null && dt.DynamicData is Exception)
{
msg = ((Exception)dt.DynamicData).Message;
msg += "（PS：可能模板不匹配）";
}
if (excelStream == null)
{
excelStream = ExcelHelper.SetError(dt);
}
if (string.IsNullOrEmpty(msg))
{
msg = "导入失败" + (excelStream != null ? "(请查看输出的Excel错误信息)" : "（请检测是否模板错误）");
}
}
else if (string.IsNullOrEmpty(msg))
{
msg = "导入成功";
}
dt.DynamicData = null;
dt = null;
GC.Collect();
jsonResult = JsonHelper.OutResult(result, msg);
}
public override void GetData(object target, System.IO.Stream outgoingData)
{
MDataTable dt = null;
#region 类型判断
if (target is MDataTable)
{
dt = target as MDataTable;
}
else if (target is MDataRow)
{
dt = ((MDataRow)target).ToTable();
}
else if (target is MDataColumn)
{
dt = ((MDataColumn)target).ToTable();
}
else if (target is MDataRowCollection)
{
dt = target as MDataRowCollection;
}
else if (target is DataRow)
{
MDataRow row = target as DataRow;
dt = row.ToTable();
}
else if (target is DataColumnCollection)
{
MDataColumn mdc = target as DataColumnCollection;
dt = mdc.ToTable();
}
else if (target is DataRowCollection)
{
MDataRowCollection rows = target as DataRowCollection;
dt = rows;
}
else if (target is NameObjectCollectionBase)
{
dt = MDataTable.CreateFrom(target as NameObjectCollectionBase);
}
else if (target is IEnumerable)
{
dt = MDataTable.CreateFrom(target as IEnumerable);
}
else
{
dt = MDataTable.CreateFrom(target);
if (dt == null)
{
MDataRow row = MDataRow.CreateFrom(target);
if (row != null)
{
dt = row.ToTable();
}
}
}
#endregion
dt = Format(dt);
if (dt != null)
{
base.GetData(dt.ToDataTable(), outgoingData);
}
else
{
base.GetData(new DataTable("Empty Table"), outgoingData);
}
}
public NoSqlAction(ref MDataRow row, string fileName, string filePath, DalType dalType)
{
Reset(ref row, fileName, filePath, dalType);
}
/// <summary>
/// 类型转换(精准强大)
/// </summary>
/// <param name="value">值处理</param>
/// <param name="t">类型</param>
/// <returns></returns>
public static object ChangeType(object value, Type t)
{
if (t == null)
{
return(null);
}
string strValue = Convert.ToString(value);
if (t.IsGenericType && t.Name.StartsWith("Nullable"))
{
t = Nullable.GetUnderlyingType(t);
if (strValue == "")
{
return(null);
}
}
if (t.Name == "String")
{
return(strValue);
}
if (t.FullName == "System.Text.Encoding")
{
return(value as Encoding);
}
if (strValue == "")
{
return(Activator.CreateInstance(t));
}
else if (t.IsValueType)
{
if (t.Name == "DateTime")
{
return(Convert.ChangeType(value, t));//这里用value，避免丢失毫秒
}
if (t.Name == "Guid")
{
return(new Guid(strValue));
}
else if (t.Name.StartsWith("Int") && strValue.IndexOf('.') > -1)
{
strValue = strValue.Split('.')[0];
}
else if (t.Name == "Boolean")
{
switch (strValue.ToLower())
{
case "yes":
case "true":
case "1":
case "on":
case "是":
return(true);
case "no":
case "false":
case "0":
case "":
case "否":
default:
return(false);
}
}
return(Convert.ChangeType(strValue, t));
}
else
{
if (strValue != t.FullName)
{
switch (GetSystemType(ref t))
{
case SysType.Custom:
return(MDataRow.CreateFrom(strValue).ToEntity(t));
case SysType.Generic:
if (t.Name.StartsWith("List"))
{
return(MDataTable.CreateFrom(strValue).ToList(t));
}
break;
case SysType.Array:
if (t.Name == "Byte[]" && value.GetType().Name != t.Name)
{
using (MemoryStream ms = new MemoryStream())
{
new BinaryFormatter().Serialize(ms, value);
return(ms.ToArray());
}
}
break;
}
}
return(Convert.ChangeType(value, t));
}
}
/// <summary>
/// 验证基础数据(数据类型、长度、是否为Null）
/// </summary>
/// <returns></returns>
public static bool ValidateData(MDataTable dt, MDataRow info)
{
bool result = true;
string[] tables = null;
List <string> requiredList = new List <string>();//必填项表。
if (info != null)
{
tables = info.Get <string>(Config_Excel.TableNames, string.Empty).Split(',');
MDataTable dtRequired = GetExcelInfo(info.Get <string>(0));//必填项表。
if (dtRequired != null && dtRequired.Rows.Count > 0)
{
dtRequired = dtRequired.Select("IsRequired=1");
if (dtRequired != null && dtRequired.Rows.Count > 0)
{
foreach (var row in dtRequired.Rows)
{
requiredList.Add(row.Get <string>("TableName") + row.Get <string>("Field"));
}
}
}
}
else
{
tables = dt.TableName.Split(',');
}
bool isOK = false;
foreach (var table in tables)//重置列头。
{
MDataColumn mdc = CYQ.Data.Tool.DBTool.GetColumns(table);
foreach (var cs in dt.Columns)
{
string[] items = cs.ColumnName.Split('.');
if (cs.TableName == table)
{
int index = mdc.GetIndex(items[items.Length - 1]);
if (index > -1)
{
isOK = true;//至少需要一个列对应上，若没有，则模板错误
cs.SqlType = mdc[index].SqlType;
cs.IsCanNull = mdc[index].IsCanNull;
if (requiredList.Contains(table + mdc[index].ColumnName))//要求必填
{
cs.IsCanNull = false;
}
cs.MaxSize = mdc[index].MaxSize;
}
}
}
}
if (!isOK)
{
return(false);
}
foreach (var row in dt.Rows)
{
StringBuilder sb = new StringBuilder();
foreach (var cell in row)
{
if (!string.IsNullOrEmpty(cell.Struct.TableName))
{
string columnName = string.IsNullOrEmpty(cell.Struct.Description) ? cell.Struct.ColumnName : cell.Struct.Description;
if (!cell.Struct.IsCanNull && cell.IsNullOrEmpty)
{
sb.AppendFormat("[{0}]不允许为空。", columnName);
cell.State = -1;
}
else if (cell.Struct.MaxSize != -1 && cell.ToString().Length > cell.Struct.MaxSize && cell.Struct.SqlType != System.Data.SqlDbType.Bit)
{
sb.AppendFormat("[{0}]长度超过{1}。", columnName, cell.Struct.MaxSize);
cell.State = -1;
}
else if (!cell.FixValue())
{
sb.AppendFormat("[{0}]数据类型错误。", columnName);
cell.State = -1;
}
}
}
if (sb.Length > 0)
{
result = false;
row.Set("错误信息", row.Get <string>("错误信息") + sb.ToString());
}
}
return(result);
}
/// <summary>
/// 导入时：把中文列头翻译成英文列头（同时处理列头结构）。
/// 并返回字典：key:列头,value:格式化名
/// </summary>
public static Dictionary<string, string> FormatterTitle(MDataTable dt, MDataRow info, string objName)
{
if (info == null)
{
return GridConfig.SetHeaderField(dt, objName);
}
else
{
Dictionary<string, string> formatDic = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
MDataTable infoConfig = GetExcelInfo(info.Get<string>(0));
if (infoConfig != null)
{
//附加自定义列。
foreach (var configRow in infoConfig.Rows)
{
string formatter = configRow.Get<string>(Config_ExcelInfo.Formatter);
if (!string.IsNullOrEmpty(formatter) && formatter[0] != '#')//增加默认值的列。
{
string excelName = configRow.Get<string>(Config_ExcelInfo.ExcelName);
if (!dt.Columns.Contains(excelName))
{
MCellStruct ms = new MCellStruct(excelName, System.Data.SqlDbType.NVarChar);
ms.TableName = configRow.Get<string>(Config_ExcelInfo.TableName);
dt.Columns.Insert(dt.Columns.Count - 1, ms);
}
}
}
MDataRow infoRow;
foreach (MCellStruct item in dt.Columns)
{
infoRow = infoConfig.FindRow(Config_ExcelInfo.ExcelName + "='" + item.ColumnName + "'");
if (infoRow == null && item.ColumnName.IndexOf('_') > 0) // 兼容只找一级的映射列。
{
string columnName = item.ColumnName.Split('_')[0];
infoRow = infoConfig.FindRow(Config_ExcelInfo.ExcelName + "='" + columnName + "'");
}
if (infoRow != null)
{
string field = infoRow.Get<string>(Config_ExcelInfo.Field);
if (string.IsNullOrEmpty(field))
{
continue;
}
item.Description = item.ColumnName;//把中文列名放到描述里。
item.TableName = infoRow.Get<string>(Config_ExcelInfo.TableName);
if (string.Compare(item.ColumnName, field, StringComparison.OrdinalIgnoreCase) != 0)
{
int index = dt.Columns.GetIndex(field);
if (index < 0)
{
item.ColumnName = field;//
}
else // 字段同名
{
item.ColumnName = item.TableName + "." + field;
//修改上一个，也增加表名。
dt.Columns[index].ColumnName = dt.Columns[index].TableName + "." + dt.Columns[index].ColumnName;
}
}
string formatter = infoRow.Get<string>(Config_ExcelInfo.Formatter);
if (!string.IsNullOrEmpty(formatter)) // 需要格式化的项
{
if (formatter.Length > 2 && formatter[0] == '#')
{
//item.SqlType = System.Data.SqlDbType.NVarChar;//重置数据类型(int数据将格式成文本）
formatDic.Add(item.ColumnName, formatter.Substring(1).Split(new string[] { "=>" }, StringSplitOptions.None)[0]);
}
else
{
item.DefaultValue = SqlCode.FormatPara(formatter);//如果不是#开头的，设置为默认值（同时处理@参数）。
}
}
}
}
}
return formatDic;
}
}
private static bool FillSchemaFromCache(ref MDataRow row, ref DbBase dbBase, string tableName, string sourceTableName)
{
bool returnResult = false;
string key = GetSchemaKey(tableName, dbBase.DataBase, dbBase.dalType);
if (CacheManage.LocalInstance.Contains(key))//�������ȡ
{
try
{
row = ((MDataColumn)CacheManage.LocalInstance.Get(key)).ToRow(sourceTableName);
returnResult = row.Count > 0;
}
catch (Exception err)
{
Log.WriteLogToTxt(err);
}
}
else if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
string fullPath = AppDomain.CurrentDomain.BaseDirectory + AppConfig.DB.SchemaMapPath + key + ".ts";
if (System.IO.File.Exists(fullPath))
{
MDataColumn mdcs = MDataColumn.CreateFrom(fullPath);
if (mdcs.Count > 0)
{
row = mdcs.ToRow(sourceTableName);
returnResult = row.Count > 0;
CacheManage.LocalInstance.Add(key, mdcs.Clone(), null, 1440);
}
}
}
return returnResult;
}
/// <summary>
/// 批量更新或插入。
/// </summary>
/// <param name="dt"></param>
/// <param name="excelRow"></param>
/// <returns></returns>
public static bool AcceptChanges(MDataTable dt, MDataRow excelRow, string objName = null)
{
if (excelRow == null)
{
MDataTable dtImportUnique = GridConfig.GetList(objName, GridConfig.SelectType.ImportUnique);
string[] names = null;
if (dtImportUnique != null && dtImportUnique.Rows.Count > 0)
{
names = new String[dtImportUnique.Rows.Count];
for (int i = 0; i < dtImportUnique.Rows.Count; i++)
{
names[i] = dtImportUnique.Rows[i].Get<string>(Config_Grid.Field);
}
}
return dt.AcceptChanges(AcceptOp.Auto, null, names);
}
bool result = true;
//获取相关配置
string[] tables = excelRow.Get<string>(Config_Excel.TableNames).Split(',');
MDataTable configTable = GetExcelInfo(excelRow.Get<string>(Config_Excel.ExcelID));
Dictionary<string, string> rowPrimaryValue = new Dictionary<string, string>();//存档每个表每行的主键值。
Dictionary<string, string> wherePrimaryValue = new Dictionary<string, string>();//存档where条件对应的主键值。
int acceptType = excelRow.Get<int>(Config_Excel.AcceptType);
using (MAction action = new MAction(tables[0]))
{
action.SetAopState(AopOp.CloseAll);
action.BeginTransation();
AppConfig.Debug.OpenDebugInfo = false;
IExcelConfig excelConfigExtend = ExcelConfigFactory.GetExcelConfigExtend();
foreach (var table in tables)
{
GC.Collect();//后面的Fill查询代码循环上万次会增长太多内存，提前调用，能降低内存。
action.ResetTable(table);
for (int i = 0; i < dt.Rows.Count; i++)
{
action.Data.Clear();
var row = dt.Rows[i];
foreach (var cell in row)//遍历所有数据行
{
if (cell.Struct.TableName != null && cell.Struct.TableName.ToLower() == table.ToLower())//过滤出属于本表的字段。
{
string[] items = cell.ColumnName.Split('.');
string columnName = items[items.Length - 1];
action.Set(columnName, cell.Value);
}
}
#region 检测是否需要插入外键。
MDataTable foreignTable = configTable.FindAll("TableName='" + table + "' and IsForeignkey=1");
if (foreignTable != null)
{
foreach (var foreignRow in foreignTable.Rows)
{
string formatter = foreignRow.Get<string>("Formatter");
string fTableName = foreignRow.Get<string>("ForeignTable");
if (string.IsNullOrEmpty(formatter))
{
//获取主键外值键
string key = fTableName + i;
if (rowPrimaryValue.ContainsKey(key))
{
string value = rowPrimaryValue[key];
action.Set(foreignRow.Get<string>("Field"), value);
}
}
else // 从其它自定义列取值。
{
MDataCell cell = row[formatter];
cell = cell ?? row[fTableName + "." + formatter];
if (cell != null)
{
action.Set(foreignRow.Get<string>("Field"), cell.Value);
}
}
}
foreignTable = null;
}
#endregion
#region //获取唯一联合主键,检测是否重复
string where = string.Empty;
List<MDataRow> rowList = configTable.FindAll("TableName='" + table + "' and IsUnique=1");
if (rowList != null && rowList.Count > 0)
{
bool isUniqueOr = excelRow.Get<bool>(Config_Excel.WhereType);
List<MDataCell> cells = new List<MDataCell>();
string errText = string.Empty;
int errorCount = 0;
foreach (var item in rowList)
{
var cell = action.Data[item.Get<string>(Config_ExcelInfo.Field)];
if (cell != null)
{
if (cell.IsNullOrEmpty) // 唯一主键是必填写字段
{
errorCount++;
errText += "[第" + (i + 1) + "行数据]：" + cell.Struct.ColumnName + "[" + cell.Struct.Description + "]不允许为空！\r\n";
}
else
{
cells.Add(cell);
}
}
}
if (errorCount > 0)
{
if (!isUniqueOr || errorCount == rowList.Count)
{
result = false;
dt.DynamicData = new Exception(errText);
goto err;
}
}
MDataCell[] item2s = cells.ToArray();
where = action.GetWhere(!isUniqueOr, item2s);
item2s = null;
rowList = null;
}
if (!string.IsNullOrEmpty(where))
{
MDataRow data = action.Data.Clone();
action.SetSelectColumns(action.Data.PrimaryCell.ColumnName);
if (action.Fill(where))//根据条件查出主键ID (数据被清空)
{
string key = table + where;
if (wherePrimaryValue.ContainsKey(key))
{
rowPrimaryValue.Add(table + i, wherePrimaryValue[key]);//记录上一个主键值。
}
else
{
rowPrimaryValue.Add(table + i, action.Get<string>(action.Data.PrimaryCell.ColumnName));//记录上一个主键值。
}
action.Data.LoadFrom(data, RowOp.IgnoreNull, false);//还原数据。
if (action.Data.GetState() == 2 && acceptType != 1)//排除掉仅插入选项
{
ExcelResult eResult = excelConfigExtend.BeforeUpdate(action.Data, row);
if (eResult == ExcelResult.Ignore || (eResult == ExcelResult.Default && action.Update(where)))
{
continue;//已经存在了，更新，准备下一条。
}
else
{
result = false;
dt.DynamicData = new Exception("[第" + (i + 1) + "行数据]：" + action.DebugInfo);
goto err;
}
}
else
{
continue;//已经存在了，同时没有可更新字段
}
}
else if (action.RecordsAffected == -2)//产生错误信息，发生异常
{
result = false;
dt.DynamicData = new Exception("[第" + (i + 1) + "行数据]：" + action.DebugInfo);
goto err;
}
}
#endregion
if (action.Data.GetState() == 0 || acceptType == 2)//仅更新则跳过插入
{
continue;//没有可映射插入的列。
}
//插入前，调用函数（插入特殊主键等值）
string errMsg;
ExcelResult excelResult = excelConfigExtend.BeforeInsert(action.Data, row, out errMsg);
if (excelResult == ExcelResult.Ignore)
{
continue;
}
if (excelResult == ExcelResult.Error || !action.Insert(InsertOp.ID))
{
result = false;
action.RollBack();
if (string.IsNullOrEmpty(errMsg))
{
errMsg = "[第" + (i + 1) + "行数据]：" + action.DebugInfo;
}
dt.DynamicData = new Exception(errMsg);
excelConfigExtend.OnInsertError(errMsg, dt);
goto err;
}
//插入后事件（可以触发其它事件）
excelConfigExtend.AfterInsert(action.Data, row, i == dt.Rows.Count - 1);
string primaryKey = action.Get<string>(action.Data.PrimaryCell.ColumnName);
rowPrimaryValue.Add(table + i, primaryKey);//记录上一个主键值。
if (!wherePrimaryValue.ContainsKey(table + where))
{
wherePrimaryValue.Add(table + where, primaryKey);
}
}
}
err:
action.EndTransation();
excelConfigExtend.Dispose();
}
return result;
}
/// <summary>
/// ���
/// </summary>
/// <param name="row">�����нṹ</param>
/// <param name="fileName">�ļ�����</param>
/// <param name="filePath">�ļ�·��</param>
/// <param name="dalType">��������</param>
public void Reset(ref MDataRow row, string fileName, string filePath, DalType dalType)
{
_insertRows.Clear();//�л����ʱ�����á�
_Row = row;
string exName = Path.GetExtension(fileName);
if (string.IsNullOrEmpty(exName))
{
switch (dalType)
{
case DalType.Txt:
_FileName = fileName + ".txt";
break;
case DalType.Xml:
_FileName = fileName + ".xml";
break;
}
}
else
{
_FileName = fileName;
}
_FileFullName = filePath + _FileName;
_DalType = dalType;
if (!_needToSaveState.ContainsKey(_FileFullName))
{
_needToSaveState.Add(_FileFullName, 0);
}
if (!_lockNextIDObj.ContainsKey(_FileFullName))
{
_lockNextIDObj.Add(_FileFullName, new object());
}
if (!_lockWriteTxtObj.ContainsKey(_FileFullName))
{
_lockWriteTxtObj.Add(_FileFullName, new object());
}
if (!_maxID.ContainsKey(_FileFullName))
{
_maxID.Add(_FileFullName, 0);
}
}
/// <summary>
/// 验证基础数据(数据类型、长度、是否为Null）
/// </summary>
/// <returns></returns>
public static bool ValidateData(MDataTable dt, MDataRow info)
{
bool result = true;
string[] tables = null;
List<string> requiredList = new List<string>();//必填项表。
if (info != null)
{
tables = info.Get<string>(Config_Excel.TableNames, string.Empty).Split(',');
MDataTable dtRequired = GetExcelInfo(info.Get<string>(0));//必填项表。
if (dtRequired != null && dtRequired.Rows.Count > 0)
{
dtRequired = dtRequired.Select(Config_ExcelInfo.IsRequired + "=1");
if (dtRequired != null && dtRequired.Rows.Count > 0)
{
foreach (var row in dtRequired.Rows)
{
requiredList.Add(row.Get<string>(Config_ExcelInfo.TableName) + row.Get<string>(Config_ExcelInfo.Field));
}
}
}
}
else
{
tables = dt.TableName.Split(',');
}
bool isOK = false;
foreach (var table in tables)//重置列头。
{
MDataColumn mdc = DBTool.GetColumns(table);
foreach (var cs in dt.Columns)
{
string[] items = cs.ColumnName.Split('.');
if (cs.TableName == table)
{
int index = mdc.GetIndex(items[items.Length - 1]);
if (index > -1)
{
isOK = true;//至少需要一个列对应上，若没有，则模板错误
cs.SqlType = mdc[index].SqlType;
cs.IsCanNull = mdc[index].IsCanNull;
if (requiredList.Contains(table + mdc[index].ColumnName))//要求必填
{
cs.IsCanNull = false;
}
cs.MaxSize = mdc[index].MaxSize;
}
}
}
}
if (!isOK) { return false; }
foreach (var row in dt.Rows)
{
StringBuilder sb = new StringBuilder();
foreach (var cell in row)
{
if (!string.IsNullOrEmpty(cell.Struct.TableName))
{
string columnName = string.IsNullOrEmpty(cell.Struct.Description) ? cell.Struct.ColumnName : cell.Struct.Description;
if (!cell.Struct.IsCanNull && cell.IsNullOrEmpty)
{
sb.AppendFormat("[{0}]不允许为空。", columnName);
cell.State = -1;
}
else if (cell.Struct.MaxSize != -1 && cell.ToString().Length > cell.Struct.MaxSize && cell.Struct.SqlType != System.Data.SqlDbType.Bit)
{
sb.AppendFormat("[{0}]长度超过{1}。", columnName, cell.Struct.MaxSize);
cell.State = -1;
}
else if (!cell.FixValue())
{
sb.AppendFormat("[{0}]数据类型错误。", columnName);
cell.State = -1;
}
}
}
if (sb.Length > 0)
{
result = false;
row.Set("错误信息", row.Get<string>("错误信息") + sb.ToString());
}
}
return result;
}
/// <summary>
/// 值比较
/// </summary>
private static bool CompareMore(MDataRow row, params TFilter[] filters)
{
bool result = false;
MDataCell cell = null, otherCell = null;
SqlDbType sqlDbType = SqlDbType.Int;
bool moreResult = false;
object valueA = null, valueB = null;
foreach (TFilter item in filters)
{
if (item._Op == Op.None)
{
moreResult = true;
}
else
{
if (item._columnAIndex == -1)
{
valueA = item._valueA;
sqlDbType = SqlDbType.NVarChar;
}
else
{
cell = row[item._columnAIndex];
valueA = cell.Value;
sqlDbType = cell.Struct.SqlType;
}
if (item._columnBIndex > -1)
{
otherCell = row[item._columnBIndex];
valueB = otherCell.Value;
if (DataType.GetGroup(sqlDbType) != DataType.GetGroup(otherCell.Struct.SqlType))
{
sqlDbType = SqlDbType.NVarChar;//不同类型的比较，转成字符串比较。
}
}
else
{
valueB = item._valueB;
}
switch (item._Op)
{
case Op.IsNull:
moreResult = cell != null && cell.IsNull;
break;
case Op.IsNotNull:
moreResult = cell != null && !cell.IsNull;
break;
default:
if (Convert.ToString(valueA) == "" || Convert.ToString(valueB) == "")//空格的问题，稍后回来处理。
{
int a = valueA == null ? 1 : (Convert.ToString(valueA) == "" ? 2 : 3);
int b = valueB == null ? 1 : (Convert.ToString(valueB) == "" ? 2 : 3);
switch (item._Op)
{
case Op.Big:
moreResult = a > b;
break;
case Op.Like:
case Op.Equal:
moreResult = a == b;
break;
case Op.SmallEqual:
moreResult = a <= b;
break;
case Op.BigEqual:
moreResult = a >= b;
break;
case Op.Small:
moreResult = a < b;
break;
case Op.NotEqual:
moreResult = a != b;
break;
//case Op.In:
// break;
//case Op.NotIn:
// break;
}
}
else
{
moreResult = Compare(sqlDbType, valueA, item._Op, valueB);
}
break;
}
}
switch (item._Ao)
{
case Ao.And:
result = result && moreResult;
break;
case Ao.Or:
result = result || moreResult;
break;
default:
result = moreResult;
break;
}
}
return result;
}
/// <summary>
/// ���
/// </summary>
/// <param name="row">�����нṹ</param>
/// <param name="fileName">�ļ�����</param>
/// <param name="filePath">�ļ�·��</param>
/// <param name="dalType">��������</param>
public void Reset(ref MDataRow row, string fileName, string filePath, DalType dalType)
{
string exName = Path.GetExtension(fileName);
if (string.IsNullOrEmpty(exName))
{
switch (dalType)
{
case DalType.Txt:
fileName = fileName + ".txt";
break;
case DalType.Xml:
fileName = fileName + ".xml";
break;
}
}
if (fileName != _FileName)
{
_insertRows.Clear();//�л����ʱ�����á�
Dispose();//�ȱ���
}
_Row = row;
_FileName = fileName;
_FileFullName = filePath + _FileName;
_DalType = dalType;
}
