/// <summary> /// 判断是否数据有重复 /// </summary> /// <param name="dbService"></param> /// <param name="col"></param> /// <param name="sqlonly"></param> /// <param name="value"></param> /// <param name="dbId"></param> private void IsOnlyOne(DataBaseLinkService dbService, System_SetExcelImportFiledEntity col, string sqlonly, string value, string dbId) { if (col.F_OnlyOne == 1) { var parameter = new List <DbParameter>(); sqlonly += col.F_FliedName + " = @" + col.F_FliedName; parameter.Add(DbParameters.CreateDbParameter("@" + col.F_FliedName, value)); var d = dbService.FindTable(dbId, sqlonly, parameter.ToArray()); if (d.Rows.Count > 0) { throw new Exception("【" + col.F_ColName + "】此项数据不能重复"); } } }
/// <summary> /// 执行excel模板数据导入 /// </summary> /// <param name="keyValue"></param> /// <param name="dt"></param> /// <returns></returns> public DataTable ExcelImport(string keyValue, DataTable dt) { System_SetExcelImportFiledService filedService = new System_SetExcelImportFiledService(); DataItemDetailService dataItemService = new DataItemDetailService(); DataBaseLinkService dbService = new DataBaseLinkService(); try { System_SetExcelImprotEntity templateInfo = this.BaseRepository().FindEntity <System_SetExcelImprotEntity>(keyValue); IEnumerable <System_SetExcelImportFiledEntity> filedsInfo = filedService.GetList("{\"F_ImportTemplateId\":\"" + templateInfo.F_Id + "\"}"); Dictionary <string, IEnumerable <DataItemDetailEntity> > dicDataItem = new Dictionary <string, IEnumerable <DataItemDetailEntity> >(); string sql = " INSERT INTO " + templateInfo.F_DbTable + " ("; string sqlValue = "("; bool isfirt = true; //组织sql语句 //INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....) foreach (var col in filedsInfo) { if (!isfirt) { sql += ","; sqlValue += ","; } sql += col.F_FliedName; sqlValue += "@" + col.F_FliedName; isfirt = false; } sql += " ) VALUES " + sqlValue + ")"; string sqlonly = " select * from " + templateInfo.F_DbTable + " where "; dt.Columns.Add("learunColOk", typeof(string)); dt.Columns.Add("learunColError", typeof(string)); foreach (DataRow dr in dt.Rows) { try { var parameter = new List <DbParameter>(); foreach (var col in filedsInfo) { string paramName = "@" + col.F_FliedName; switch (col.F_RelationType) { case 0: //无关联 if (col.F_FiledType == "日期") { parameter.Add(DbParameters.CreateDbParameter(paramName, DateTime.ParseExact(dr[col.F_ColName].ToString(), "m/d/yy", System.Globalization.CultureInfo.CurrentCulture))); } else { parameter.Add(DbParameters.CreateDbParameter(paramName, dr[col.F_ColName].ToString())); } IsOnlyOne(dbService, col, sqlonly, dr[col.F_ColName].ToString(), templateInfo.F_DbId); break; case 1: //GUID parameter.Add(DbParameters.CreateDbParameter(paramName, Guid.NewGuid().ToString())); break; case 2: //数据字典 string dataItemName = ""; if (!dicDataItem.ContainsKey(col.F_FliedName)) { IEnumerable <DataItemDetailEntity> dataItemList = dataItemService.GetList(col.F_DataItemEncode); dicDataItem.Add(col.F_FliedName, dataItemList); } dataItemName = FindDataItemName(dicDataItem[col.F_FliedName], dr[col.F_ColName].ToString(), col.F_ColName); parameter.Add(DbParameters.CreateDbParameter(paramName, dataItemName)); IsOnlyOne(dbService, col, sqlonly, dataItemName, templateInfo.F_DbId); break; case 3: //数据表 string v = ""; try { string strSql = "select " + col.F_DbSaveFlied + " from " + col.F_DbTable + " where " + col.F_DbRelationFlied + " = @" + col.F_DbRelationFlied; var parameter2 = new List <DbParameter>(); parameter2.Add(DbParameters.CreateDbParameter("@" + col.F_DbRelationFlied, dr[col.F_ColName].ToString())); DataTable dt2 = dbService.FindTable(col.F_DbId, strSql, parameter2.ToArray()); v = dt2.Rows[0][0].ToString(); parameter.Add(DbParameters.CreateDbParameter(paramName, dt2.Rows[0][0].ToString())); } catch (Exception) { throw (new Exception("【" + col.F_ColName + "】 找不到对应的数据")); } IsOnlyOne(dbService, col, sqlonly, v, templateInfo.F_DbId); break; case 4: //固定值 parameter.Add(DbParameters.CreateDbParameter(paramName, col.F_Value)); break; case 5: //操作人ID parameter.Add(DbParameters.CreateDbParameter(paramName, OperatorProvider.Provider.Current().UserId)); break; case 6: //操作人名字 parameter.Add(DbParameters.CreateDbParameter(paramName, OperatorProvider.Provider.Current().UserName)); break; case 7: //操作时间 parameter.Add(DbParameters.CreateDbParameter(paramName, DateTime.Now)); break; } } dbService.ExecuteBySql(templateInfo.F_DbId, sql, parameter.ToArray()); dr["learunColOk"] = "1"; } catch (Exception ex) { if (templateInfo.F_ErrorType == 0) { break; } dr["learunColOk"] = "0"; dr["learunColError"] = ex.Message; } } return(dt); } catch (System.Exception) { throw; } }