public static int updateBusinessPK(string fid, string businessPk)
{
string sql = string.Format("UPDATE MDS_IMP_DATA_SCRIPT_RULE SET ");
sql += string.Format("DES_BUSINESS_PK='{0}'", businessPk);
sql += string.Format("WHERE FID='{0}' ", fid);
return(OracleHelper.Excuse(sql));
}
public static int Insert(DataLogItems item)
{
string sql = string.Format("INSERT INTO MDS_IMP_DATA_LOG_ITEMS(FID,LOGID,IMP_STATUS,ERROR_MSG,ROW_INDEX,CONTENT) ");
sql += string.Format("VALUES('{0}','{1}',{2},'{3}',{4},'{5}')",
item.FID, item.LogID, item.ImpStatus, item.ErrorMsg, item.RowIndex, item.Content);
return(OracleHelper.Excuse(sql));
}
public static int Insert(ColumInfo item)
{
string sql = string.Format("INSERT INTO MDS_IMP_COLUM_INFO(FID,PM_TASK_TIME_ID,TABLE_NAME,COL_NAME,REMARK,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_IP,VERSION) ");
sql += string.Format("VALUES('{0}','{1}','{2}','{3}','{4}','{5}',to_date('{6}','yyyy/mm/dd hh24:mi:ss'),'{7}',to_date('{8}','yyyy/mm/dd hh24:mi:ss'),'{9}',{10})",
item.FID, item.TaskTimeID, item.TableName, item.ColName, item.Remark, item.CreatedBy,
item.CreationDate, item.LastUpdatedBy, item.LastUpdateDate, item.LastUpdateIp, item.Version);
return(OracleHelper.Excuse(sql));
}
public static int Insert(ObjtableInfo item)
{
string sql = string.Format("INSERT INTO MDS_IMP_OBJTABLE_INFO(FID,OBJECT_TABLE_CODE,OBJECT_TABLE_NAME,STATUS,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_IP,VERSION) ");
sql += string.Format("VALUES('{0}','{1}','{2}','{3}','{4}',to_date('{5}','yyyy/mm/dd hh24:mi:ss'),'{6}',to_date('{7}','yyyy/mm/dd hh24:mi:ss'),'{8}',{9})",
item.FID, item.ObjectTableCode, item.ObjectTableName, item.Status, item.CreatedBy,
item.CreationDate, item.LastUpdatedBy, item.LastUpdateDate, item.LastUpdateIp, item.Version);
return(OracleHelper.Excuse(sql));
}
public static int Insert(DataScriptRule item)
{
string sql = string.Format("INSERT INTO MDS_IMP_DATA_SCRIPT_RULE(FID,MDS_IMP_DATA_SCRIPT_ID,LINE_SEPERATOR,COL_SEPERATOR,COLNAME_LINES,DATA_LINES,DES_TABLE,REMARK,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_IP,VERSION,DES_FILE) ");
sql += string.Format("VALUES('{0}','{1}','{2}','{3}',{4},{5},'{6}','{7}','{8}',to_date('{9}','yyyy/mm/dd hh24:mi:ss'),'{10}',to_date('{11}','yyyy/mm/dd hh24:mi:ss'),'{12}',{13},'{14}')",
item.FID, item.MdsImpDataScriptID, item.LineSeperator, item.ColSperator, item.ColnameLines, item.DataLines,
item.DesTable, item.Remark, item.CreatedBy,
item.CreationDate.ToString("yyyy/MM/dd HH:mm:ss"), item.LastUpdatedBy, item.LastUpdateDate.ToString("yyyy/MM/dd HH:mm:ss"), item.LastUpdateIp, item.Version, item.DesFile);
return(OracleHelper.Excuse(sql));
}
public static int Insert(DataScript item)
{
string sql = string.Format("INSERT INTO MDS_IMP_DATA_SCRIPT(FID,MIDS_SCRIPT_CODE,MIDS_SCRIPT_NAME,MIDS_SCRIPT_VERSION,FILE_TYPE,INDEX_KEY,VALID_FLAG,APPLY_TEST_PROJECT,REMARK,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_IP,VERSION,SCRIPT_TYPE,PROJECT_CODE,TASK_NAME,TABLE_NAME,RELEASE,TABLE_NAMEEXT) ");
sql += string.Format("VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',to_date('{10}','yyyy/mm/dd hh24:mi:ss'),'{11}',to_date('{12}','yyyy/mm/dd hh24:mi:ss'),'{13}',{14},{15},'{16}','{17}','{18}','01','{19}')",
item.FID, item.MidsScriptCode, item.MidsScriptName, item.MidsScriptVesion, item.FileType,
item.IndexKey, item.ValidFlag, item.ApplyTestProject, item.Remark, item.CreatedBy,
item.CreationDate.ToString("yyyy/MM/dd HH:mm:ss"), item.LastUpdatedBy,
item.LastUpdateDate.ToString("yyyy/MM/dd HH:mm:ss"), item.LastUpdateIp, item.Version, item.ScriptType, item.ProjectCode,
item.TaskName, item.TableName, item.TableNameExt);
return(OracleHelper.Excuse(sql));
}
public static int Insert(DataScriptMap item)
{
string sql = string.Format("INSERT INTO MDS_IMP_DATA_SCRIPT_MAP(FID,MDS_IMP_DATA_SCRIPT_RULE_ID,TABLE_COL_NAME,FILE_COL_NAME,TRANSFER_TYPE,INSERT_VALUE_METHOD,TRANSFER_SCRIPT,REMARK,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_IP,VERSION) ");
sql += string.Format("VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',to_date('{9}','yyyy/mm/dd hh24:mi:ss'),'{10}',to_date('{11}','yyyy/mm/dd hh24:mi:ss'),'{12}',{13})",
item.FID, item.MdsImpDataScriptRuleID, item.TableColName, item.FileColName, item.TransferType, item.InsertValueMethod,
item.TransferScript.Replace("'", "''").Replace("\r\n", "'||chr(13)||chr(10)||'"),
item.Remark, item.CreatedBy, item.CreationDate.ToString("yyyy/MM/dd HH:mm:ss"), item.LastUpdatedBy,
item.LastUpdateDate.ToString("yyyy/MM/dd HH:mm:ss"), item.LastUpdateIp, item.Version);
//SaveScriptFile(item);
return(OracleHelper.Excuse(sql));
}
public static int update(string tablename, Dictionary <Structure, string> valueMap, string keys)
{
string sql;
string set = "";
string where = "";
foreach (var key in valueMap.Keys)
{
string tempval = "";
switch (key.DataType.ToUpper())
{
case "DATE":
tempval = string.Format("to_date('{0}','yyyy/mm/dd hh24:mi:ss')", valueMap[key]);
break;
case "LONG":
case "NUMBER":
tempval = string.Format("{0}", valueMap[key]);
break;
case "CHAR":
case "VARCHAR2":
case " NVARCHAR2":
default:
tempval = string.Format("'{0}'", valueMap[key]);
break;
}
if (keys.ToUpper().Contains(key.ColumnName.ToUpper()))
{
where += string.Format(" {0}={1} and ", key.ColumnName, tempval);
}
else
{
set += string.Format(" {0}={1},", key.ColumnName, tempval);
}
}
set = set.Trim().TrimEnd(new char[] { ',' });
if (where.Length > 0)
{
where = where.Remove(where.Length - 5);
}
sql = string.Format("UPDATE {0} SET {1} WHERE {2}", tablename, set, where);
return(OracleHelper.Excuse(sql));
}
public static int Insert(DataLog item)
{
string sql = string.Format("INSERT INTO MDS_IMP_DATA_LOG(FID,TEST_PROJECT_ID,IMP_DATE_TIME,FULL_FLODERNAME,IMPROWSCOUNT,IMP_STATUS,ERROR_MSG,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_IP,VERSION,IMPFILENAME,OBJECT_TABLE,TYPENAME) ");
sql += string.Format("VALUES('{0}','{1}',to_date('{2}','yyyy/mm/dd hh24:mi:ss'),'{3}','{4}','{5}','{6}','{7}',to_date('{8}','yyyy/mm/dd hh24:mi:ss'),'{9}',to_date('{10}','yyyy/mm/dd hh24:mi:ss'),'{11}',{12},'{13}','{14}','{15}')",
item.FID, item.TestProjectID,
item.ImpDateTime.ToString("yyyy/MM/dd HH:mm:ss"),
item.FullFloderName,
item.ImpRowsCount, item.ImpStatus, item.ErrorMsg, item.CreatedBy,
item.CreationDate.ToString("yyyy/MM/dd HH:mm:ss"), item.LastUpdatedBy,
item.LastUpdateDate.ToString("yyyy/MM/dd HH:mm:ss"), item.LastUpdateIp,
item.Version, item.ImpFileName, item.ObjectTable, item.TypeName);
return(OracleHelper.Excuse(sql));
}
public static int update(DataScriptRule item)
{
string sql = string.Format("UPDATE MDS_IMP_DATA_SCRIPT_RULE SET ");
sql += string.Format("LINE_SEPERATOR='{0}',", item.LineSeperator);
sql += string.Format("COL_SEPERATOR='{0}',", item.ColSperator);
sql += string.Format("COLNAME_LINES={0},", item.ColnameLines);
sql += string.Format("DATA_LINES={0},", item.DataLines);
sql += string.Format("DES_TABLE='{0}',", item.DesTable);
sql += string.Format("DES_FILE='{0}',", item.DesFile);
sql += string.Format("REMARK='{0}',", item.Remark);
sql += string.Format("LAST_UPDATE_DATE=to_date('{0}','yyyy/mm/dd hh24:mi:ss') ", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
sql += string.Format("WHERE FID='{0}' ", item.FID);
return(OracleHelper.Excuse(sql));
}
public static int Insert(ImportLog item)
{
string sql = string.Format("INSERT INTO MDS_IMP_IMPORT_LOG(CONTENT,FILE_NAME,PROJECT_CODE,TIMES, CREATED_BY,CREATION_DATE,TASK_CODE,FILE_SIZE,SCRIPT_CODE ) ");
sql += string.Format("VALUES('{0}','{1}','{2}','{3}','{4}',to_date('{5}','yyyy/mm/dd hh24:mi:ss'),'{6}','{7}','{8}')",
item.Content,
item.FileName,
item.ProjectCode,
item.Times,
item.CreatedBy,
DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"),
item.TaskCode,
item.FileSize,
item.ScriptCode
);
return(OracleHelper.Excuse(sql));
}
public static int update(DataScript item)
{
string sql = string.Format("UPDATE MDS_IMP_DATA_SCRIPT SET ");
sql += string.Format("MIDS_SCRIPT_CODE='{0}',", item.MidsScriptCode);
sql += string.Format("MIDS_SCRIPT_NAME='{0}',", item.MidsScriptName);
sql += string.Format("MIDS_SCRIPT_VERSION='{0}',", item.MidsScriptVesion);
sql += string.Format("FILE_TYPE='{0}',", item.FileType);
sql += string.Format("INDEX_KEY='{0}',", item.IndexKey);
sql += string.Format("VALID_FLAG='{0}',", item.ValidFlag);
sql += string.Format("APPLY_TEST_PROJECT='{0}',", item.ApplyTestProject);
sql += string.Format("REMARK='{0}',", item.Remark);
sql += string.Format("SCRIPT_TYPE={0},", item.ScriptType);
sql += string.Format("PROJECT_CODE='{0}',", item.ProjectCode);
sql += string.Format("TASK_NAME='{0}',", item.TaskName);
sql += string.Format("TABLE_NAME='{0}',", item.TableName);
sql += string.Format("TABLE_NAMEEXT='{0}',", item.TableNameExt);
sql += string.Format("INVALID={0},", item.Invalid);
sql += string.Format("LAST_UPDATE_DATE=to_date('{0}','yyyy/mm/dd hh24:mi:ss') ", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
sql += string.Format("WHERE FID='{0}' ", item.FID);
return(OracleHelper.Excuse(sql));
}
public static int Insert(string tablename, Dictionary <Structure, string> valueMap, string userid)
{
string sql;
string col = "";
string val = "";
foreach (var key in valueMap.Keys)
{
col += key.ColumnName + ",";
switch (key.DataType.ToUpper())
{
case "DATE":
val += string.Format("to_date('{0}','yyyy/mm/dd hh24:mi:ss'),", valueMap[key]);
break;
case "LONG":
case "NUMBER":
val += string.Format("{0},", valueMap[key]);
break;
case "CHAR":
case "VARCHAR2":
case " NVARCHAR2":
default:
val += string.Format("'{0}',", valueMap[key]);
break;
}
}
col = col.TrimEnd(new char[] { ',' });
val = val.TrimEnd(new char[] { ',' });
sql = string.Format("INSERT INTO {0} ({1},created_by,last_updated_by,CREATION_DATE,LAST_UPDATE_DATE) VALUES({2},'{3}','{3}','{4}','{4}')", tablename, col, val, userid, DateTime.Now.ToString());
return(OracleHelper.Excuse(sql));
}
public static int Comment(string tableName, string columnName, string comments)
{
string sql = string.Format("comment on column {0}.{1} is '{2}'", tableName, columnName, comments);
return(OracleHelper.Excuse(sql));
}
public static int delAll(string mdsID)
{
string sql = string.Format("DELETE FROM MDS_IMP_DATA_SCRIPT_MAP WHERE MDS_IMP_DATA_SCRIPT_RULE_ID='{0}'", mdsID);
return(OracleHelper.Excuse(sql));
}
public static int updateLevel(string fid, string level)
{
string sql = string.Format("UPDATE MDS_IMP_DATA_SCRIPT SET MIDS_SCRIPT_VERSION='{1}' WHERE FID='{0}'", fid, level);
return(OracleHelper.Excuse(sql));
}
public static int updateInvalid(string fid, int Invalid)
{
string sql = string.Format("UPDATE MDS_IMP_DATA_SCRIPT SET INVALID={1} WHERE FID='{0}'", fid, Invalid);
return(OracleHelper.Excuse(sql));
}
public static int Release(string fid)
{
string sql = string.Format("UPDATE MDS_IMP_DATA_SCRIPT SET RELEASE='02' WHERE FID='{0}'", fid);
return(OracleHelper.Excuse(sql));
}
public static int dropColumn(string tableName, string columnName)
{
string sql = string.Format("ALTER TABLE {0} DROP COLUMN {1}", tableName, columnName);
return(OracleHelper.Excuse(sql));
}
/// <summary>
/// 用已有表结构，创建一个新表
/// </summary>
/// <param name="sourceTable"></param>
/// <param name="tempTable"></param>
/// <returns></returns>
public static int createtempTable(string sourceTable, string tempTable)
{
string sql = string.Format("create table {1} as select * from {0} where 1<>1", sourceTable, tempTable);
return(OracleHelper.Excuse(sql));
}
public static int removeDataRow(string tableName, string projectId, int times)
{
string sql = string.Format("delete from {0} where PROJECTID ='{1}' and TASKTIMES={2}", tableName, projectId, times);
return(OracleHelper.Excuse(sql));
}
public static void DropTable(string tableName)
{
string sql = string.Format("drop table {0}", tableName);
OracleHelper.Excuse(sql);
}
public static int Delete(string FID)
{
string sql = string.Format("DELETE FROM MDS_IMP_DATA_SCRIPT_RULE WHERE FID='{0}'", FID);
return(OracleHelper.Excuse(sql));
}