public DataTable GetTableDataList(string dataBaseLinkId, string tableName)
{
DataBaseLinkService link = new DataBaseLinkService();
DataBaseLinkEntity dataBaseLinkEntity = link.GetEntity(dataBaseLinkId);
if (dataBaseLinkEntity != null)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM " + tableName + "");
return(this.BaseRepository(dataBaseLinkEntity.DbConnection).FindTable(strSql.ToString()));
}
return(null);
}
/// <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;
}
}