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)); }