public void RecordsAreNotSavedwhenLoadDataSetStoredProcCommandAndTransactionRollback()
{
OracleDatabase db = (OracleDatabase)DatabaseFactory.CreateDatabase("OracleTest");
DataSet dsCountry = new DataSet();
DbCommand dbAddCountry = db.GetStoredProcCommand("AddCountryListAll");
db.AddInParameter(dbAddCountry, "vCountryCode", DbType.String, "SCO");
db.AddInParameter(dbAddCountry, "vCountryName", DbType.String, "Scotland");
DbCommand dbAddCountry1 = db.GetStoredProcCommand("AddCountryListAll");
db.AddInParameter(dbAddCountry1, "vCountryCode", DbType.String, "SCO");
db.AddInParameter(dbAddCountry1, "vCountryName", DbType.String, "Scotland");
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
db.LoadDataSet(dbAddCountry, dsCountry, "Country", transaction);
db.LoadDataSet(dbAddCountry1, dsCountry, "Country", transaction);
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
}
}
string countryName = (string)db.ExecuteScalar(CommandType.Text, "select countryName from Country where CountryCode='SCO'");
Assert.IsNull(countryName);
}
/// <summary>
/// 功能：查询分页数据
/// 日期：2013-05-15
/// </summary>
/// <param name="sqlStr">查询SQL</param>
/// <param name="pi">页码</param>
/// <param name="pageSize">每页显示条数</param>
/// <param name="parList">参数类型</param>
/// <param name="dbT">执行库</param>
/// <returns></returns>
public static PagedTable GetDataByPage(string sqlStr, int pi, int pageSize, List <ParameterEntity> parList, DataBaseType dbT)
{
string falg = DataPageFalg;
#region 查询数据语句
//String sqlData = @"SELECT * FROM (SELECT A.*, ROWNUM ROW_NUM FROM (" + sqlStr + ") A) WHERE ROW_NUM > :ROW_NUM_OLD AND ROW_NUM <= :ROW_NUM_NEW";
String sqlData = @"SELECT * FROM (SELECT A.*, ROWNUM ROW_NUM FROM (" + sqlStr + ") A WHERE ROWNUM<= :ROW_NUM_NEW) WHERE ROW_NUM > :ROW_NUM_OLD";
#endregion
#region 查询数据总条数语句
String sqlCount = "(SELECT COUNT(1) ROW_TOTAL from (" + sqlStr + ")) ROW_TOTAL ";
#endregion
String SQL = "SELECT * FROM " + sqlCount + " ,(" + sqlData + ")";
OracleDatabase oraDb = DBManager.CreateDataBase(dbT);
DbCommand cmd = oraDb.GetSqlStringCommand(SQL);
if (parList != null && parList.Count > 0)
{
foreach (ParameterEntity par in parList)
{
oraDb.AddInParameter(cmd, par.Name, par.DbType, par.Value);
}
}
oraDb.AddInParameter(cmd, ":ROW_NUM_OLD", DbType.Int32, (pi - 1) * pageSize);
oraDb.AddInParameter(cmd, ":ROW_NUM_NEW", DbType.Int32, pageSize * pi);
DataSet ds = oraDb.ExecuteDataSet(cmd);
PagedTable pt = new PagedTable(ds.Tables[0], pi, pageSize, BusinessHelper.GetRowTotal(ds.Tables[0]));
return(pt);
}
/// <summary>
/// 根据角色名称及权限标识ID判断是否具有该权限
/// </summary>
/// <param name="privlegeId"></param>
/// <param name="roleName"></param>
/// <returns></returns>
public static bool HavingPrivilege(string roleName, int privlegeId)
{
if (!Available(roleName))
{
return(false);
}
//Self Privlege Check
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT PRIVILEGE_ID FROM PLM.ROLEINPRIVILEGE_TAB WHERE ROLENAME=:rolename AND PRIVILEGE_ID=:privilegeid";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "rolename", DbType.String, roleName);
db.AddInParameter(cmd, "privilegeid", DbType.Int32, privlegeId);
object ret = db.ExecuteScalar(cmd);
if (ret != null && ret != DBNull.Value)
{
return(true);
}
//Inherit Role Privilege Check
//bool hasPrivilege = false;
foreach (string inheritRole in FindInheritRole(roleName))
{
if (HavingPrivilege(inheritRole, privlegeId))
{
return(true);
}
}
return(false);
}
/// <summary>
/// 根据项目和专业更新MEOMSS的当前流水号
/// </summary>
/// <param name="dpid"></param>
/// <returns></returns>
public static int UPManualNo(int dpid, int pid)
{
string sql = "update project_discipline_oid t set t.o_id=t.o_id+1 where t.P_ID=:pid and t.D_ID=:dpid";
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "dpid", DbType.Int32, dpid);
db.AddInParameter(cmd, "pid", DbType.Int32, pid);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// 更新MEOMSS的关联单据ERP ID
/// </summary>
/// <param name="MEOMSSid"></param><param name="ERPid"></param>
/// <returns></returns>
public static int UPERPID(int MEOMSSid, int ERPid)
{
string sql = "update project_drawing_tab t set t.related_drawing=:ERPid where t.drawing_id=:MEOMSSid";
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "MEOMSSid", DbType.Int32, MEOMSSid);
db.AddInParameter(cmd, "ERPid", DbType.Int32, ERPid);
return(db.ExecuteNonQuery(cmd));
}
public int Update()
{
//Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand("update plm.MM_PART_REQUIRE_TAB set PART_NO=:partno,CONTRACT=:contract,REQUIRE_QTY=:requireqty,REQUIRE_DATE=:requirDate,REASON_CODE=:reasoncode,DESIGN_CODE=:designcode,INFORMATION=:information,IS_INVENTORY=:isinventory,UPDATE_DATE=sysdate,PROJECT_ID=:projectId,PARENT_SUB_PROJECT_ID=:parentProId,SUB_PROJECT_ID=:subProId,ACTIVITY_SEQ= :actId,REMARK=:remark,PART_NAME=:partname where REQUIRE_ID=:requireId");
db.AddInParameter(cmd, "partno", DbType.String, PART_NO);
db.AddInParameter(cmd, "requireId", DbType.Int32, REQUIRE_ID);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// 取得材料的库存信息
/// </summary>
/// <param name="site"></param>
/// <param name="PartNo"></param>
/// <param name="projectid"></param>
/// <returns></returns>
public static InventoryPart GetOnhandqty(string site, string PartNo, string projectid)
{
//Database db = DatabaseFactory.CreateDatabase();
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
DbCommand cmd = db.GetSqlStringCommand("select sum(qty_onhand) qty_onhand,sum(qty_reserved) qty_reserved,part_no,part_desc,unit from IFSAPP.yr_inv_on_hand_vw where CONTRACT=:site and PART_NO=:partno and req_dept like '%" + projectid + "%' group by part_no,part_desc,unit");
db.AddInParameter(cmd, "site", DbType.String, site);
db.AddInParameter(cmd, "partno", DbType.String, PartNo);
return(Populate(db.ExecuteReader(cmd)));
}
/// <summary>
/// 取得ERP中的积压库存
/// </summary>
/// <param name="site"></param>
/// <param name="PartNo"></param>
/// <returns></returns>
public static string GetInventoryJYqty(string site, string PartNo)
{
//Database db = DatabaseFactory.CreateDatabase();
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
DbCommand cmd = db.GetSqlStringCommand("select IFSAPP.Inventory_Part_In_Stock_API.Get_Inventory_Qty_Reserved(CONTRACT,PART_NO,NULL) invQty from IFSAPP.inventory_part where CONTRACT=:site and PART_NO=:partno");
db.AddInParameter(cmd, "site", DbType.String, site);
db.AddInParameter(cmd, "partno", DbType.String, PartNo);
return(string.IsNullOrEmpty(Convert.ToString(db.ExecuteScalar(cmd))) == false?Convert.ToString(db.ExecuteScalar(cmd)) : "0");
}
public static string GetIsInventory(string site, string PartNo)
{
//Database db = DatabaseFactory.CreateDatabase();
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
DbCommand cmd = db.GetSqlStringCommand("select IFSAPP.Inventory_Part_API.Part_Exist(CONTRACT, PART_NO) isExist from IFSAPP.inventory_part where CONTRACT=:site and PART_NO=:partno");
db.AddInParameter(cmd, "site", DbType.String, site);
db.AddInParameter(cmd, "partno", DbType.String, PartNo);
return(Convert.ToString(db.ExecuteScalar(cmd)));
}
/// <summary>
/// 根据项目返回其系统
/// </summary>
/// <returns></returns>
public static List <ProjectSystem> FindAll(int projectid, string newFlag)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT * FROM PLM.PROJECT_SYSTEM_TAB WHERE PROJECT_ID=:projectid AND NEWFLAG=:newflag";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "projectid", DbType.Int32, projectid);
db.AddInParameter(cmd, "newflag", DbType.String, newFlag);
return(EntityBase <ProjectSystem> .DReaderToEntityList(db.ExecuteReader(cmd)));
}
public int Add()
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
//OracleDatabase db = new OracleDatabase(UserSecurity.ConnectionString);
string sql = "INSERT INTO PLM.PRIVILEGE_NODE_TAB (PRIVILEGE_ID,NODE_ID) VALUES (:privilegeid,:nodeid)";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "privilegeid", DbType.Int32, PrivilegeId);
db.AddInParameter(cmd, "nodeid", DbType.Int32, NodeId);
return(db.ExecuteNonQuery(cmd));
}
public static List <PartRelative> FindRelativeERPPartList(string stnPartno, string projectId, string Site)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT * FROM plm.MM_IFS_STA_PART_TAB where STA_PART_NO=:stnPartno and PROJECTID=:proId and SITE=:site";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "stnPartno", DbType.String, stnPartno);
db.AddInParameter(cmd, "proId", DbType.String, projectId);
db.AddInParameter(cmd, "site", DbType.String, Site);
return(EntityBase <PartRelative> .DReaderToEntityList(db.ExecuteReader(cmd)));
}
/// <summary>
/// 删除预估
/// </summary>
/// <param name="contractno">域</param>
/// <param name="pid">项目ID</param>
/// <param name="dpline">项目专业</param>
/// <returns></returns>
public static void DeleteEstimate(string contractno, string pid, string dpline)
{
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand("update PLM.MM_PART_PARAMETER_TAB t set t.last_flag=0 where CONTRACT =:mSite and projectid=:pid and DISCIPLINE =:dpid");
db.AddInParameter(cmd, "mSite", DbType.String, contractno);
db.AddInParameter(cmd, "pid", DbType.String, pid);
db.AddInParameter(cmd, "dpid", DbType.String, dpline);
db.ExecuteNonQuery(cmd);
}
/// <summary>
/// 返回所有子项目下的子项目列表
/// </summary>
/// <returns></returns>
public static DataSet FindSub2ProjectsDs(string id, string subId)
{
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
// Database db = DatabaseFactory.CreateDatabase("ifsConnection");
string sql = " SELECT * FROM IFSAPP.SUB_PROJECT T WHERE T.PROJECT_ID = :id AND T.PARENT_SUB_PROJECT_ID =:subId ORDER BY t.sub_project_id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.String, id);
db.AddInParameter(cmd, "subId", DbType.String, subId);
return(db.ExecuteDataSet(cmd));
}
/// <summary>
/// 验证用户
/// </summary>
/// <param name="userName"></param>
/// <param name="password"></param>
/// <returns></returns>
public static bool Verify(string userName, string password)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
//Database db = DatabaseFactory.CreateDatabase("oidsConnection");
string sql = "SELECT COUNT(*) FROM PLM.USER_TAB WHERE TRIM(LOWER(NAME))=:username AND PASS=:userpass";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "username", DbType.String, userName.ToLower());
db.AddInParameter(cmd, "userpass", DbType.String, Security.HashCryptString(password));
return(Convert.ToInt32(db.ExecuteScalar(cmd)) >= 1);
}
/// <summary>
/// 返回所有子项目列表
/// </summary>
/// <returns></returns>
public static List <Activity> FindAll(string id, string SubPro)
{
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
string sql = "SELECT * FROM IFSAPP.ACTIVITY T WHERE T.PROJECT_ID =:id AND T.sub_project_id=:subPro ORDER BY t.activity_no";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.String, id);
db.AddInParameter(cmd, "subPro", DbType.String, SubPro);
return(EntityBase <Activity> .DReaderToEntityList(db.ExecuteReader(cmd)));
}
public static string GetAreaByBlock(string blockname, string projectid, string siteno)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT area_cn FROM PLM.MM_RELATION_TAB WHERE BLOCK_NO=:blockname and project_id =:pid and site =:csite";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "blockname", DbType.String, blockname);
db.AddInParameter(cmd, "pid", DbType.String, projectid);
db.AddInParameter(cmd, "csite", DbType.String, siteno);
return(Convert.ToString(db.ExecuteScalar(cmd)));
}
/// <summary>
/// 获取ERP中与零件相关的MEO号的集合
/// </summary>
/// <param name="Pid"></param>
/// <param name="PartNo"></param>
/// <returns></returns>
public static DataSet RequisionNoDs(string Pid, string PartNo)
{
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
// Database db = DatabaseFactory.CreateDatabase("ifsConnection");
string sql = "SELECT distinct P_REQUISITION_NO FROM IFSAPP.PROJECT_MISC_PROCUREMENT WHERE PROJECT_ID =:PROID AND (PART_NO =:partno)";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "PROID", DbType.String, Pid);
db.AddInParameter(cmd, "partno", DbType.String, PartNo);
return(db.ExecuteDataSet(cmd));
}
public static DataSet UnitDs(string Pid, string ACT_SEQ)
{
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
// Database db = DatabaseFactory.CreateDatabase("ifsConnection");
string sql = "SELECT distinct IFSAPP.INVENTORY_PART_API.GET_UNIT_MEAS(SITE, PART_NO) UNIT FROM IFSAPP.PROJECT_MISC_PROCUREMENT WHERE PROJECT_ID =:PROID AND (ACTIVITY_SEQ =:ACT_SEQ)";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "PROID", DbType.String, Pid);
db.AddInParameter(cmd, "ACT_SEQ", DbType.String, ACT_SEQ);
return(db.ExecuteDataSet(cmd));
}
public static DataSet ReasonCodeDs(string Pid, string ACT_SEQ)
{
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
string sql = "SELECT distinct REASON_CODE, IFSAPP.YRS_REQUISITION_REASON_API.GET_DESCRIPTION(REASON_CODE) REASON_DESC FROM IFSAPP.PROJECT_MISC_PROCUREMENT WHERE PROJECT_ID =:PROID AND (ACTIVITY_SEQ =:ACT_SEQ)";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "PROID", DbType.String, Pid);
db.AddInParameter(cmd, "ACT_SEQ", DbType.String, ACT_SEQ);
return(db.ExecuteDataSet(cmd));
}
/// <summary>
/// 更改角色的状态
/// </summary>
/// <returns></returns>
public static int UpdateState(string roleName, RoleState us)
{
//OracleDatabase db = new OracleDatabase(UserSecurity.ConnectionString);
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "UPDATE PLM.ROLE_TAB SET STATE=:state WHERE LOWER(ROLENAME)=:rolename";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "state", DbType.String, us.ToString());
db.AddInParameter(cmd, "rolename", DbType.String, roleName.ToLower());
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// 返回所有子项目列表
/// </summary>
/// <returns></returns>
public static List <PartBasicInfo> FindAll(string Pid, string ACT_SEQ)
{
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
string sql = "SELECT PROJECT_ID, ACTIVITY_SEQ, SITE, PART_NO, IFSAPP.PURCHASE_PART_API.GET_DESCRIPTION(SITE, PART_NO) PART_NAME,IFSAPP.INVENTORY_PART_API.GET_UNIT_MEAS(SITE, PART_NO) UNIT, IFSAPP.INVENTORY_PART_API.PART_EXIST(SITE, PART_NO) IS_EXIST, ISSUE_FROM_INV, REQUEST_QTY, IFSAPP.PROJ_PROCU_RATION_API.GET_ACCU_RATION_QTY(MATR_SEQ_NO) SUM, REQUIRE_QTY -IFSAPP.PROJ_PROCU_RATION_API.GET_ACCU_RATION_QTY(MATR_SEQ_NO) REST, REQUEST_DATE, P_REQUISITION_NO, P_REQ_LINE_NO, P_REQ_RELEASE_NO, INFORMATION, REASON_CODE, IFSAPP.YRS_REQUISITION_REASON_API.GET_DESCRIPTION(REASON_CODE) REASON_DESC, DESIGN_CODE FROM IFSAPP.PROJECT_MISC_PROCUREMENT WHERE PROJECT_ID =:PROID AND (ACTIVITY_SEQ =:ACT_SEQ)";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "PROID", DbType.String, Pid);
db.AddInParameter(cmd, "ACT_SEQ", DbType.String, ACT_SEQ);
return(EntityBase <PartBasicInfo> .DReaderToEntityList(db.ExecuteReader(cmd)));
}
/// <summary>
/// 添加角色的权限
/// </summary>
/// <param name="roleName">角色名称</param>
/// <param name="privilegeId">权限ID</param>
/// <param name="projectIDs">项目ID集合</param>
/// <returns></returns>
public static int AddPrivilege(string roleName, int privilegeId, string projectIDs)
{
string sql = "INSERT INTO PLM.ROLEINPRIVILEGE_TAB (ROLENAME, PRIVILEGE_ID, PROJECT_ID) VALUES (:rolename, :privilegeid, :projectids)";
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "rolename", DbType.String, roleName);
db.AddInParameter(cmd, "privilegeid", DbType.Int32, privilegeId);
db.AddInParameter(cmd, "projectids", DbType.String, projectIDs);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// 根据 参数system_id,项目根节点fid 找到路径
/// </summary>
/// <param name="id"></param>
/// <param name="fid"></param>
/// <returns></returns>
public static string FindPath(int id, int fid)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
//string sql = "select sys_connect_by_path(description,'/') from plm.project_system_tab where parent_id<>0 and system_id=:id start with system_id =:fid connect by parent_id = prior system_id";
string sql = "select sys_connect_by_path(description,'/') from plm.project_system_tab where system_id=:id start with system_id =:fid connect by parent_id = prior system_id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.Int32, id);
db.AddInParameter(cmd, "fid", DbType.Int32, fid);
return(db.ExecuteScalar(cmd).ToString());
}
public int Delete()
{
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand("delete plm.MM_IFS_STA_PART_TAB where ERP_PART_NO=:erpPartno and PROJECTID=:proId and ACTIVITYSEQ=:actSeq and SITE=:site");
db.AddInParameter(cmd, "erpPartno", DbType.String, ERP_PART_NO);
db.AddInParameter(cmd, "proId", DbType.String, PROJECTID);
db.AddInParameter(cmd, "actSeq", DbType.Int32, ACTIVITYSEQ);
db.AddInParameter(cmd, "site", DbType.String, SITE);
return(db.ExecuteNonQuery(cmd));
}
public static string FindRelativeStnPartno(string ErpPartno, string proId, int ActSeq, string site)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = " select STA_PART_NO from plm.MM_IFS_STA_PART_TAB where ERP_PART_NO=:erpPartno and PROJECTID=:proId and ACTIVITYSEQ=:actSeq and SITE=:site";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "erpPartno", DbType.String, ErpPartno);
db.AddInParameter(cmd, "proId", DbType.String, proId);
db.AddInParameter(cmd, "actSeq", DbType.Int32, ActSeq);
db.AddInParameter(cmd, "site", DbType.String, site);
return(Convert.ToString(db.ExecuteScalar(cmd)));
}
/// <summary>
/// 返回所有Project列表
/// </summary>
/// <returns></returns>
public static DataSet FindStnPartDataset(string ProjectId, string Site)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
string sql = "SELECT typeid, STA_PART_NO, PART_NAME FROM plm.MM_STA_PART_TAB a where PROJECTID=:proId and SITE=:site ";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "proId", DbType.String, ProjectId);
db.AddInParameter(cmd, "site", DbType.String, Site);
return(db.ExecuteDataSet(cmd));
}
/// <summary>
/// 根据PartNo判断零件是否存在
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public static bool Exist(string partno, int partid)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
//Database db = DatabaseFactory.CreateDatabase("oidsConnection");
string sql = "SELECT part_no FROM PLM.MM_PART_TAB WHERE LOWER(part_no)=:partno and ID<>:partid";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "partno", DbType.String, partno.ToLower());
db.AddInParameter(cmd, "partid", DbType.Int32, partid);
object rname = db.ExecuteScalar(cmd);
return((rname == null || rname == DBNull.Value) ? false : true);
}
public static string FindPreAlert(string project, string partno, string site)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
//OracleDatabase db = new OracleDatabase(UserSecurity.ConnectionString);
string sql = "SELECT PREDICTION_ALERT FROM plm.MM_PART_PARAMETER_TAB WHERE PROJECTID=:projectid and PART_NO=:partno and CONTRACT=:site";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "projectid", DbType.String, project);
db.AddInParameter(cmd, "partno", DbType.String, partno);
db.AddInParameter(cmd, "site", DbType.String, site);
return(Convert.ToString(db.ExecuteScalar(cmd)));
}
public static DataSet FindERPPartDataset(string partno, string site, string ProId, string ActSeq)
{
// Database db = DatabaseFactory.CreateDatabase("ifsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT ERP_PART_NO,PART_NAME,site FROM plm.MM_IFS_STA_PART_TAB where STA_PART_NO=:staPartno and PROJECTID=:proId and ACTIVITYSEQ=:actSeq and SITE=:site";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "staPartno", DbType.String, partno);
db.AddInParameter(cmd, "proId", DbType.String, ProId);
db.AddInParameter(cmd, "actSeq", DbType.Int32, ActSeq);
db.AddInParameter(cmd, "site", DbType.String, site);
return(db.ExecuteDataSet(cmd));
}