/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(SOSOshop.Model.Service.Orders.yw_xsdjhzb model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update yw_xsdjhzb set ");
strSql.Append("djid=@djid,");
strSql.Append("jgid=@jgid,");
strSql.Append("jjid=@jjid,");
strSql.Append("rq=@rq,");
strSql.Append("djbh=@djbh,");
strSql.Append("djbs=@djbs,");
strSql.Append("dwid=@dwid,");
strSql.Append("ywyid=@ywyid,");
strSql.Append("lxrid=@lxrid,");
strSql.Append("jsfsid=@jsfsid,");
strSql.Append("hsje=@hsje,");
strSql.Append("name=@name,");
strSql.Append("url=@url,");
strSql.Append("is_kp=@is_kp");
strSql.Append(" where djid=@djid ");
DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
db.AddInParameter(dbCommand, "djid", DbType.String, model.djid);
db.AddInParameter(dbCommand, "jgid", DbType.String, model.jgid);
db.AddInParameter(dbCommand, "jjid", DbType.String, model.jjid);
db.AddInParameter(dbCommand, "rq", DbType.String, model.rq);
db.AddInParameter(dbCommand, "djbh", DbType.String, model.djbh);
db.AddInParameter(dbCommand, "djbs", DbType.String, model.djbs);
db.AddInParameter(dbCommand, "dwid", DbType.String, model.dwid);
db.AddInParameter(dbCommand, "ywyid", DbType.String, model.ywyid);
db.AddInParameter(dbCommand, "lxrid", DbType.String, model.lxrid);
db.AddInParameter(dbCommand, "jsfsid", DbType.String, model.jsfsid);
db.AddInParameter(dbCommand, "hsje", DbType.String, model.hsje);
db.AddInParameter(dbCommand, "name", DbType.String, model.name);
db.AddInParameter(dbCommand, "url", DbType.String, model.url);
db.AddInParameter(dbCommand, "is_kp", DbType.String, model.is_kp);
int rows = db.ExecuteNonQuery(dbCommand);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 执行数据库语句支持存储过程 2013-07-01
/// </summary>
/// <param name="sql"></param>
/// <param name="parList"></param>
/// <param name="dbT"></param>
/// <param name="cmdType"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, List <ParameterEntity> parList, DataBaseType dbT, CommandType cmdType)
{
try
{
DbCommand cmd;
OracleDatabase oraDb = DBManager.CreateDataBase(dbT);
if (cmdType == CommandType.StoredProcedure)
{
cmd = oraDb.GetStoredProcCommand(sql);
}
else
{
cmd = oraDb.GetSqlStringCommand(sql);
}
cmd.CommandType = cmdType;
if (parList != null && parList.Count > 0)
{
foreach (ParameterEntity par in parList)
{
oraDb.AddInParameter(cmd, par.Name, par.DbType, par.Value);
}
}
return(oraDb.ExecuteNonQuery(cmd));
}
catch (Exception e)
{
//return 0;
throw e;
}
}
public int Add()
{
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand("INSERT INTO PLM.MM_PART_PARAMETER_TAB(description,blockid,systemid,ecprojectid,last_flag,PART_NO,CONTRACT,WEIGHT_SINGLE,PREDICTION_QTY,PREDICTION_ALERT,COEFFICIENT_ERP,PROJECTID,PREDICT_CREATOR,PROJECT_ZONE,DISCIPLINE,FIRSTBATCH_QTY,FIRSTBATCH_DATE,SECONDBATCH_QTY,SECONDBATCH_DATE,PART_TYPE,FINAL_PREDICTION_QTY,OPERATOR,MODIFY_REASON,PREDICT_DATE,UNIT) VALUES (:description,:blockid,:systemid,:ecprojectid,:lastflag,:partno,:site,:weightSingle,:preQty,:pre_alert,:coeficient,:PROJECTID,:creator,:predictzone,:discipline,:firstpreQty,:firstpreDate,:secondpreQty,:secondpreDate,:part_type,:finalpreQty,:operator,:modreason,:predictdate,:unitmeas)");
db.AddInParameter(cmd, "description", DbType.String, DESCRIPTION);
db.AddInParameter(cmd, "blockid", DbType.String, BLOCKID);
db.AddInParameter(cmd, "systemid", DbType.Int32, SYSTEMID);
db.AddInParameter(cmd, "ecprojectid", DbType.Int32, ECPROJECTID);
db.AddInParameter(cmd, "lastflag", DbType.Int32, LAST_FLAG);
db.AddInParameter(cmd, "partno", DbType.String, PART_NO);
db.AddInParameter(cmd, "site", DbType.String, CONTRACT);
db.AddInParameter(cmd, "weightSingle", DbType.Decimal, WEIGHT_SINGLE);
db.AddInParameter(cmd, "preQty", DbType.Decimal, PREDICTION_QTY);
db.AddInParameter(cmd, "pre_alert", DbType.Decimal, PREDICTION_ALERT);
db.AddInParameter(cmd, "coeficient", DbType.Decimal, COEFFICIENT_ERP);
db.AddInParameter(cmd, "PROJECTID", DbType.String, PROJECTID);
db.AddInParameter(cmd, "creator", DbType.String, PREDICT_CREATOR);
db.AddInParameter(cmd, "predictzone", DbType.String, PROJECT_ZONE);
db.AddInParameter(cmd, "discipline", DbType.String, DISCIPLINE);
db.AddInParameter(cmd, "firstpreQty", DbType.Decimal, FIRSTBATCH_QTY);
db.AddInParameter(cmd, "firstpreDate", DbType.DateTime, FIRSTBATCH_DATE);
db.AddInParameter(cmd, "secondpreQty", DbType.Decimal, SECONDBATCH_QTY);
db.AddInParameter(cmd, "secondpreDate", DbType.DateTime, SECONDBATCH_DATE);
db.AddInParameter(cmd, "part_type", DbType.String, PART_TYPE);
db.AddInParameter(cmd, "finalpreQty", DbType.Decimal, FINAL_PREDICTION_QTY);
db.AddInParameter(cmd, "predictdate", DbType.DateTime, PREDICT_DATE);
db.AddInParameter(cmd, "operator", DbType.String, OPERATOR);
db.AddInParameter(cmd, "modreason", DbType.String, MODIFY_REASON);
db.AddInParameter(cmd, "unitmeas", DbType.String, UNIT);
return(db.ExecuteNonQuery(cmd));
}
public static Boolean ExecuteStoredProc(string storedProcedureName)
{
bool results;
try
{
string instance = ApplicationConfiguration.Instance.DefaultInstance;
var db = new OracleDatabase(GetConnectStringFromInstance(instance));
System.Data.Common.DbCommand cmd = db.GetStoredProcCommand(storedProcedureName);
if (cmd != null)
{
var records = db.ExecuteNonQuery(cmd);
results = records == 1;
}
else
{
results = false;
}
}
catch (Exception ex)
{
results = false;
LogError(ex.Message, "ExecuteStoredProc");
}
return(results);
}
/// <summary>
/// 更新材料
/// </summary>
/// <returns></returns>
public int Update()
{
//Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand("Update PLM.MM_PART_TAB set" +
" part_densityunit=:partdensityunit, supplycircle=:spcircle,PART_NO=:partno,CONTRACT=:site,PART_SPEC=:partspec,Part_TYpe=:parttype,parentid=:parentid,part_mat=:partmat,part_cert=:partcert,PART_level=:partlevel,part_unit=:partunit,part_unitdensity=:partunitdensity,part_spec1=:partspec1,part_spec2=:partspec2,part_spec3=:partspec3,part_spec4=:partspec4,UPDATER=:creator,UPDATEDATE=:updatetime,Replace_code=:replace_code" +
" where id=:partid");
db.AddInParameter(cmd, "spcircle", DbType.Int32, SUPPLYCIRCLE);
db.AddInParameter(cmd, "partno", DbType.String, PART_NO);
db.AddInParameter(cmd, "site", DbType.String, CONTRACT);
db.AddInParameter(cmd, "partspec", DbType.String, PART_SPEC);
db.AddInParameter(cmd, "parttype", DbType.String, PART_TYPE);
db.AddInParameter(cmd, "parentid", DbType.Int32, PARENTID);
db.AddInParameter(cmd, "partmat", DbType.String, PART_MAT);
db.AddInParameter(cmd, "partcert", DbType.String, PART_CERT);
db.AddInParameter(cmd, "partlevel", DbType.String, PART_LEVEL);
db.AddInParameter(cmd, "creator", DbType.String, UPDATER);
db.AddInParameter(cmd, "partunit", DbType.String, PART_UNIT);
db.AddInParameter(cmd, "partunitdensity", DbType.Decimal, PART_UNITDENSITY);
db.AddInParameter(cmd, "partspec1", DbType.String, PART_SPEC1);
db.AddInParameter(cmd, "partspec2", DbType.String, PART_SPEC2);
db.AddInParameter(cmd, "partspec3", DbType.String, PART_SPEC3);
db.AddInParameter(cmd, "partspec4", DbType.String, PART_SPEC4);
db.AddInParameter(cmd, "partdensityunit", DbType.String, PART_DENSITYUNIT);
db.AddInParameter(cmd, "partid", DbType.Int32, ID);
db.AddInParameter(cmd, "updatetime", DbType.DateTime, UPDATEDATE);
db.AddInParameter(cmd, "replace_code", DbType.String, REPLACE_CODE);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// 新增材料
/// </summary>
/// <returns></returns>
public int Add()
{
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand("INSERT INTO PLM.MM_PART_TAB" +
"(part_densityunit,SUPPLYCIRCLE,PART_NO,CONTRACT,PART_SPEC,Part_TYpe,parentid,part_mat,part_cert,PART_level,part_unit,part_unitdensity,part_spec1,part_spec2,part_spec3,part_spec4,CREATOR)" +
" VALUES (:partdensityunit,:spcircle,:partno,:site,:partspec,:parttype,:parentid,:partmat,:partcert,:partlevel,:partunit,:partunitdensity,:partspec1,:partspec2,:partspec3,:partspec4,:creator)");
db.AddInParameter(cmd, "partno", DbType.String, PART_NO);
db.AddInParameter(cmd, "site", DbType.String, CONTRACT);
db.AddInParameter(cmd, "partspec", DbType.String, PART_SPEC);
db.AddInParameter(cmd, "parttype", DbType.String, PART_TYPE);
db.AddInParameter(cmd, "parentid", DbType.Int32, PARENTID);
db.AddInParameter(cmd, "spcircle", DbType.Int32, SUPPLYCIRCLE);
db.AddInParameter(cmd, "partmat", DbType.String, PART_MAT);
db.AddInParameter(cmd, "partcert", DbType.String, PART_CERT);
db.AddInParameter(cmd, "partlevel", DbType.String, PART_LEVEL);
db.AddInParameter(cmd, "creator", DbType.String, CREATOR);
db.AddInParameter(cmd, "partunit", DbType.String, PART_UNIT);
db.AddInParameter(cmd, "partunitdensity", DbType.Decimal, PART_UNITDENSITY);
db.AddInParameter(cmd, "partspec1", DbType.String, PART_SPEC1);
db.AddInParameter(cmd, "partspec2", DbType.String, PART_SPEC2);
db.AddInParameter(cmd, "partspec3", DbType.String, PART_SPEC3);
db.AddInParameter(cmd, "partspec4", DbType.String, PART_SPEC4);
db.AddInParameter(cmd, "partdensityunit", DbType.String, PART_DENSITYUNIT);
return(db.ExecuteNonQuery(cmd));
}
public int Add()
{
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand("INSERT INTO plm.MM_PART_RATION_TAB(PART_NO,CONTRACT,P_REQUISITION_NO,ISSUED_QTY,ISSUED_DATE,IF_INVENTORY,PURPOSE,BLOCK_ID,REASON_CODE,DESIGN_CODE,REMARK,CREATE_DATE,CREATER,PROJECT_ID,part_zone,part_discipline,part_fx,part_type,unit,PART_NAME) VALUES (:partno,:contract,:requisionNo,:requireqty,:requirDate,:isInventory,:purpose,:blockID,:reasoncode,:designcode,:information,sysdate,:creater,:projectId,:parentProId,:subProId,:actId,:parttype,:unit,:partname)");
db.AddInParameter(cmd, "partno", DbType.String, PART_NO);
db.AddInParameter(cmd, "contract", DbType.String, CONTRACT);
db.AddInParameter(cmd, "requisionNo", DbType.String, P_REQUISITION_NO);
db.AddInParameter(cmd, "requireqty", DbType.Single, ISSUED_QTY);
db.AddInParameter(cmd, "requirDate", DbType.Date, ISSUED_DATE);
db.AddInParameter(cmd, "isInventory", DbType.String, IF_INVENTORY);
db.AddInParameter(cmd, "purpose", DbType.String, PURPOSE);
db.AddInParameter(cmd, "blockID", DbType.String, BLOCK_ID);
db.AddInParameter(cmd, "reasoncode", DbType.String, REASON_CODE);
db.AddInParameter(cmd, "designcode", DbType.String, DESIGN_CODE);
db.AddInParameter(cmd, "information", DbType.String, INFORMATION);
db.AddInParameter(cmd, "creater", DbType.String, CREATER);
db.AddInParameter(cmd, "projectId", DbType.String, PROJECT_ID);
db.AddInParameter(cmd, "parentProId", DbType.String, PART_ZONE);
db.AddInParameter(cmd, "subProId", DbType.String, PART_DISCIPLINE);
db.AddInParameter(cmd, "actId", DbType.String, PART_FX);
db.AddInParameter(cmd, "parttype", DbType.String, PART_TYPE);
db.AddInParameter(cmd, "unit", DbType.String, UNIT);
db.AddInParameter(cmd, "partname", DbType.String, PART_NAME);
;
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_RATION_TAB set DISCIPLINE=:dpname,PART_NO=:partno,CONTRACT=:contract,P_REQUISITION_NO=:reqNo,ISSUED_QTY=:issqty,ISSUED_DATE=:issDate,IF_INVENTORY=:isInv,PURPOSE=:purpose,BLOCK_ID=:BLOCK_ID,REASON_CODE=:reasoncode,DESIGN_CODE=:designcode,REMARK=:information,UPDATE_DATE=sysdate,PROJECT_ID=:projectId,PART_ZONE=:partzone,part_discipline=:partdp,PART_FX= :actId,PART_TYPE=:parttype,unit=:unit,PART_NAME=:partname where RATION_ID=:rationId");
db.AddInParameter(cmd, "partno", DbType.String, PART_NO);
db.AddInParameter(cmd, "partname", DbType.String, PART_NAME);
db.AddInParameter(cmd, "contract", DbType.String, CONTRACT);
db.AddInParameter(cmd, "reqNo", DbType.String, P_REQUISITION_NO);
db.AddInParameter(cmd, "issqty", DbType.Single, ISSUED_QTY);
db.AddInParameter(cmd, "issDate", DbType.Date, ISSUED_DATE);
db.AddInParameter(cmd, "isInv", DbType.String, IF_INVENTORY);
db.AddInParameter(cmd, "purpose", DbType.String, PURPOSE);
db.AddInParameter(cmd, "BLOCK_ID", DbType.String, BLOCK_ID);
db.AddInParameter(cmd, "reasoncode", DbType.String, REASON_CODE);
db.AddInParameter(cmd, "designcode", DbType.String, DESIGN_CODE);
db.AddInParameter(cmd, "information", DbType.String, REMARK);
db.AddInParameter(cmd, "projectId", DbType.String, PROJECT_ID);
db.AddInParameter(cmd, "partzone", DbType.String, PART_ZONE);
db.AddInParameter(cmd, "partdp", DbType.String, PART_DISCIPLINE);
db.AddInParameter(cmd, "unit", DbType.String, UNIT);
db.AddInParameter(cmd, "actId", DbType.String, PART_FX);
db.AddInParameter(cmd, "parttype", DbType.String, PART_TYPE);
db.AddInParameter(cmd, "rationId", DbType.Int32, RATION_ID);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// 功能:对传入的SQL执行增、改、删等操作【附加事物】
/// 日期:2013-05-15
/// </summary>
/// <param name="sql">执行的SQL</param>
/// <param name="parList">参数列表</param>
/// <param name="tran">事物</param>
/// <param name="dbT">执行的库</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, List <ParameterEntity> parList, DbTransaction tran, DataBaseType dbT)
{
try
{
if (string.IsNullOrEmpty(sql))
{
return(1);
}
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);
}
}
return(oraDb.ExecuteNonQuery(cmd, tran));
}
catch (Exception e)
{
//return 0;
throw e;
}
}
/// <summary>
/// 新增MEO从表
/// </summary>
/// <returns></returns>
public int REQUIRE_Add()
{
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand("INSERT INTO plm.MM_PART_REQUIRE_TAB(DISCIPLINEID,ECPROJECTID,REQUIRE_NO,CONTRACT,INFORMATION,IS_INVENTORY,CREATE_DATE,CREATER,PROJECT_ID,SYSTEM_ID,PARTTYPE_ID,REMARK,ERP_PARTTYPE,ERP_DISCIPLINE,state) VALUES (:discpid,:ECprojectid,:requireno,:contract,:information,:isinventory,sysdate,:creater,:projectId,:sysId,:parttypeid,:remark,:ERPtypeid,:ERPDiscip,1)");
db.AddInParameter(cmd, "requireno", DbType.String, REQUIRE_NO);
db.AddInParameter(cmd, "contract", DbType.String, CONTRACT);
db.AddInParameter(cmd, "information", DbType.String, INFORMATION);
db.AddInParameter(cmd, "isinventory", DbType.String, IS_INVENTORY);
db.AddInParameter(cmd, "creater", DbType.String, CREATER);
//db.AddInParameter(cmd, "createrole", DbType.String, "MaterialApp");
db.AddInParameter(cmd, "projectId", DbType.String, PROJECT_ID);
db.AddInParameter(cmd, "sysId", DbType.String, SYSTEM_ID);
db.AddInParameter(cmd, "remark", DbType.String, REMARK);
db.AddInParameter(cmd, "parttypeid", DbType.String, PARTTYPE_ID);
db.AddInParameter(cmd, "ECprojectid", DbType.String, ECPROJECTID);
db.AddInParameter(cmd, "discpid", DbType.String, DISCIPLINEID);
db.AddInParameter(cmd, "ERPtypeid", DbType.String, ERP_PARTTYPE);
db.AddInParameter(cmd, "ERPDiscip", DbType.String, ERP_DISCIPLINE);
int requireId = 0;
int rowsAffected = db.ExecuteNonQuery(cmd);
if (rowsAffected > 0)
{
DbCommand cmdSeq = db.GetSqlStringCommand("SELECT PLM.MM_REQUIRE_SEQ.CURRVAL FROM DUAL");
requireId = Convert.ToInt32(db.ExecuteScalar(cmdSeq));
}
return(requireId);
}
/// <summary>
/// 删除选中零件
/// </summary>
/// <param name="partnolist"></param>
/// <returns></returns>
public static int Delete(string partnolist)
{
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand("delete PLM.MM_PART_TAB t where ID in " + partnolist);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// 更新父节点及parentIndex值
/// </summary>
/// <param name="nodeid"></param>
/// <param name="num"></param>
/// <returns></returns>
public static int UpdateParentAndParentIndex(int nodeid, int parentid, int num)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "UPDATE PLM.TREENODES_TAB SET PARENT_ID=" + parentid + ",PARENT_INDEX=" + num + " WHERE ID=" + nodeid;
DbCommand cmd = db.GetSqlStringCommand(sql);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// parentIndex依次减1
/// </summary>
/// <param name="nodeid"></param>
/// <param name="beforenode"></param>
/// <returns></returns>
public static int UpdateParentIndexDel(int nodeid, int beforenode, int dropindex)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "UPDATE (SELECT PARENT_INDEX FROM PLM.TREENODES_TAB S WHERE S.PARENT_ID=" + nodeid + ")T SET T.PARENT_INDEX=T.PARENT_INDEX-1 WHERE T.PARENT_INDEX<" + dropindex + " AND T.PARENT_INDEX>=" + beforenode;
DbCommand cmd = db.GetSqlStringCommand(sql);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// 功能：执行事物 (返回结果List 0 影响行数 1 执行出现错误的SQL )
/// 日期：2013-05-15
/// </summary>
/// <param name="dic">SQL列表</param>
/// <param name="dbT">库类型</param>
/// <returns></returns>
public static List <object> ExecuteDBTransaction(Dictionary <string, List <ParameterEntity> > dic, DataBaseType dbT)
{
List <object> obj = new List <object>();
OracleDatabase oraDb = DBManager.CreateDataBase(dbT);
int result = 0;
string errorSQL = "";
using (DbConnection conn = oraDb.CreateConnection())
{
DbTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
foreach (string item in dic.Keys)
{
if (!string.IsNullOrEmpty(item))
{
errorSQL = item;
DbCommand cmd = oraDb.GetSqlStringCommand(item);
if (dic[item] != null && dic[item].Count > 0)
{
foreach (ParameterEntity par in dic[item])
{
oraDb.AddInParameter(cmd, par.Name, par.DbType, par.Value);
}
}
if (oraDb.ExecuteNonQuery(cmd, tran) >= 0)
{
result++;
}
else
{
tran.Rollback();
result = 0;
obj.Add(result);
obj.Add(errorSQL);
return(obj);
}
}
}
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
throw (e);
result = 0;
obj.Add(result);
obj.Add(errorSQL);
return(obj);
}
obj.Add(result);
obj.Add(errorSQL);
return(obj);
}
}
/// <summary>
/// 删除MEO从表中的记录
/// </summary>
/// <param name="seqNo"></param>
/// <returns></returns>
public int MEODelete(int seqNo)
{
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand("update PLM.MM_PART_REQUIRELINE_TAB t set t.deleteflag='Y' where REQUIRE_ID=:seqNo");
db.AddInParameter(cmd, "seqNo", DbType.Int32, seqNo);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// 对角色移除权限
/// </summary>
/// <param name="roleName"></param>
/// <returns></returns>
public static int RemovePrivilege(string roleName)
{
string sql = "DELETE FROM PLM.ROLEINPRIVILEGE_TAB WHERE ROLENAME=:rolename";
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "rolename", DbType.String, roleName);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// 删除预估
/// </summary>
/// <param name="seqNo"></param>
/// <returns></returns>
public static int Delete(int seqNo)
{
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand("delete PLM.MM_PART_PARAMETER_TAB t where SEQUENCE_ID=:seqNo");
db.AddInParameter(cmd, "seqNo", DbType.Int32, seqNo);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// 更新ERP的定额流水号
/// </summary>
/// <param name="id"></param>
public static int UpdateERPMSSNO(int id, string mssno)
{
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "UPDATE plm.MM_PART_RATION_TAB set ERP_QUEUENO='" + mssno + "'WHERE RATION_ID=:id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.Int32, id);
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="id"></param>
public static int UpdateMSSState(string id, int strstate)
{
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "UPDATE plm.MM_PART_RATION_TAB set state='" + strstate + "' WHERE RATION_ID=:id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.String, id);
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));
}
/// <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>
/// 审核反审核MEO单据
/// </summary>
/// <param name="id"></param>
/// <param name="flag"></param>
/// <returns></returns>
public static int ApproveMEO(int id, string flag)
{
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "UPDATE plm.MM_PART_REQUIRE_TAB SET STATE='" + flag + "' WHERE require_ID=:id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.Int32, id);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// 删除申请
/// </summary>
/// <param name="id"></param>
public static int Del(int id)
{
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "DELETE FROM plm.MM_PART_RATION_TAB WHERE RATION_ID=:id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.Int32, id);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// 确认预估
/// </summary>
/// <param name="mainid"></param>
public static void DeleteEstimate(string seqidList)
{
// Database db = DatabaseFactory.CreateDatabase("oidsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DbCommand cmd = db.GetSqlStringCommand("update PLM.MM_PART_PARAMETER_TAB t set t.confirmed='Y' where sequence_id in (" + seqidList + ")");
string stringlist = "update PLM.MM_PART_PARAMETER_TAB t set t.confirmed='Y' where sequence_id in (" + seqidList + ")";
//db.AddInParameter(cmd, "dpid", DbType.String, seqidList);
db.ExecuteNonQuery(cmd);
}
/// <summary>
/// 执行数据库语句支持存储过程,含输出参数 2013-07-01
/// </summary>
/// <param name="sql">存储过程名称</param>
/// <param name="parList">参数</param>
/// <param name="outParIdName">out 参数1名称</param>
/// <param name="outParIdValue">out 参数1值（必须是int/long）</param>
/// <param name="outParName">out 参数2名称</param>
/// <param name="outParValue">out 参数2值 </param>
/// <param name="dbT">数据库</param>
/// <param name="cmdType">sql类型</param>
/// <returns></returns>
public static bool ExecuteNonQueryWithOutResult(string sql, List <ParameterEntity> parList, string outParIdName, out string outParIdValue, string outParName, out string outParValue, DataBaseType dbT, CommandType cmdType)
{
try
{
DbCommand cmd;
OracleDatabase oraDb = DBManager.CreateDataBase(dbT);
if (cmdType == CommandType.StoredProcedure)
{
cmd = oraDb.GetStoredProcCommand(sql);
}
else
{
cmd = oraDb.GetSqlStringCommand(sql);
}
cmd.CommandType = cmdType;
if (parList != null && parList.Count > 0)
{
foreach (ParameterEntity par in parList)
{
oraDb.AddInParameter(cmd, par.Name, par.DbType, par.Value);
}
}
if (!string.IsNullOrEmpty(outParIdName))
{
oraDb.AddOutParameter(cmd, outParIdName, DbType.Int64, 1024);
}
if (!string.IsNullOrEmpty(outParName))
{
oraDb.AddOutParameter(cmd, outParName, DbType.String, 1024);
}
oraDb.ExecuteNonQuery(cmd);
if (!string.IsNullOrEmpty(outParIdName))
{
outParIdValue = cmd.Parameters[outParIdName].Value.ToString();
}
else
{
outParIdValue = "";
}
if (!string.IsNullOrEmpty(outParName))
{
outParValue = cmd.Parameters[outParName].Value.ToString();
}
else
{
outParValue = "";
}
return(true);
}
catch (Exception e)
{
outParValue = e.Message;
throw e;
}
}
/// <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>
/// 删除预估
/// </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 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));
}
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));
}
private void button1_Click(object sender, EventArgs e)
{
OracleDatabase db = null;
string constring = "user id=hs_fund;password=handsome;data source=devgh";
OperLog oplog = OperLog.instance;
try
{
// Create an instance of an OracleDatbase object
db = new OracleDatabase(constring);
// Start up the database
//db.Startup();
Console.WriteLine("The Oracle database is now up.");
// Executing Startup() is the same as the following:
// db.Startup(OracleDBStartupMode.NoRestriction, null, true);
// which is also the same as:
// db.Startup(OracleDBStartupMode.NoRestriction, null, false);
db.ExecuteNonQuery("");
// db.ExecuteNonQuery("ALTER DATABASE OPEN");
}
catch (OracleException ex)
{
string err = "连接Oracle数据库失败，TNSNAME:racle异常信息：" + ex.Message;
oplog.WriteLog(err, LogLevel.Error);
System.Windows.Forms.MessageBox.Show(err);
}
catch (Exception ex)
{
string err = "连接Oracle数据库失败，TNSNAME:异常信息：" + ex.Message;
oplog.WriteLog(err, LogLevel.Error);
System.Windows.Forms.MessageBox.Show(err);
}
finally
{
// Dispose the OracleDatabase object
db.Dispose();
}
}