/// <summary>
/// 根据 SQL 返回 DataSet 数据集，带参数
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">参数化</param>
/// <returns>DataSet</returns>
public DataSet GetDataSetBySQL(StringBuilder sql, SqlParam[] param)
{
try
{
dbCommand = this.GetInstance().GetSqlStringCommand(Replace(sql.ToString()));
DbCommon.AddInParameter(db, dbCommand, param);
return(db.ExecuteDataSet(dbCommand));
}
catch (Exception e)
{
DbLog.WriteException(e);
return(null);
}
}
/// <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);
}
public DataTable select_tmp_poslog_ora()
{
DataTable dtdoc = null;
//string sqlquery = "select RTL_LOC_ID,WKSTN_ID,TRANS_SEQ,BUSINESS_DATE,NUMDOC,TOTAL,DOCUMENT_TYPCODE," +
// "(SELECT POSLOG_DATA FROM TRN_POSLOG_DATA B WHERE B.ORGANIZATION_ID = 2000 " +
// "AND B.RTL_LOC_ID = A.RTL_LOC_ID AND B.WKSTN_ID = A.WKSTN_ID AND B.TRANS_SEQ = A.TRANS_SEQ " +
// "AND ROWNUM = 1) AS POSLOG_DATA " +
// "from " + Ent_Acceso_BD.nom_tabla_poslog + " A WHERE (SEND_SERVER IS NULL OR SEND_SERVER='X') AND ROWNUM<=50";
string sqlquery = "select A.RTL_LOC_ID,A.WKSTN_ID,A.TRANS_SEQ,A.BUSINESS_DATE,A.NUMDOC,A.TOTAL,A.DOCUMENT_TYPCODE,B.POSLOG_DATA " +
"from " + Ent_Acceso_BD.nom_tabla_poslog + " A " +
"INNER JOIN TRN_POSLOG_DATA B ON B.ORGANIZATION_ID = 2000 AND B.RTL_LOC_ID = A.RTL_LOC_ID " +
"AND B.WKSTN_ID = A.WKSTN_ID AND B.TRANS_SEQ = A.TRANS_SEQ " +
"WHERE(SEND_SERVER IS NULL OR SEND_SERVER = 'X') AND ROWNUM<= 50";
try
{
object results = new object[1];
Database db = new OracleDatabase(Ent_Acceso_BD.conn());
DbCommand dbCommandWrapper = db.GetSqlStringCommand(sqlquery);
dtdoc = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
}
catch
{
dtdoc = null;
}
return(dtdoc);
}
public static DataSet FindSelectResultDS(string sql)
{
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
DbCommand cmd = db.GetSqlStringCommand(sql);
return(db.ExecuteDataSet(cmd));
}
/// <summary>
/// 返回数据集
/// </summary>
/// <param name="sr"></param>
/// <param name="dat"></param>
public static DataSet getds(string sr)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = sr;
DbCommand cmd = db.GetSqlStringCommand(sql);
return(db.ExecuteDataSet(cmd));
}
public static DataSet QueryPartRationListERP(string sql)
{
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
DbCommand cmd = db.GetSqlStringCommand(sql);
return(db.ExecuteDataSet(cmd));
}
public static DataSet FindInvPartDimDataset()
{
// Database db = DatabaseFactory.CreateDatabase("ifsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
string sql = "SELECT distinct dim_quality FROM IFSAPP.inventory_part";
DbCommand cmd = db.GetSqlStringCommand(sql);
return(db.ExecuteDataSet(cmd));
}
public static DataSet FindInvPartFamilyDataset()
{
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
string sql = " select PART_PRODUCT_FAMILY, IFSAPP.INVENTORY_PRODUCT_FAMILY_API.Get_Description(PART_PRODUCT_FAMILY) familyname from (SELECT distinct PART_PRODUCT_FAMILY FROM IFSAPP.inventory_part)";
DbCommand cmd = db.GetSqlStringCommand(sql);
return(db.ExecuteDataSet(cmd));
}
public static DataSet FindInvPartInfByPartNameDataset(string partname, string site)
{
// Database db = DatabaseFactory.CreateDatabase("ifsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
string sql = "SELECT PART_NO,DESCRIPTION FROM IFSAPP.inventory_part where DESCRIPTION like '%" + partname + "%' and CONTRACT='" + site + "'";
DbCommand cmd = db.GetSqlStringCommand(sql);
return(db.ExecuteDataSet(cmd));
}
/// <summary>
/// 返回所有Project列表
/// </summary>
/// <returns></returns>
public static DataSet FindUnitDataset()
{
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
// Database db = DatabaseFactory.CreateDatabase("ifsConnection");
string sql = "SELECT UNIT_CODE from IFSAPP.ISO_UNIT";
DbCommand cmd = db.GetSqlStringCommand(sql);
return(db.ExecuteDataSet(cmd));
}
/// <summary>
/// 返回所有Project列表
/// </summary>
/// <returns></returns>
public static DataSet FindProDataset()
{
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
string sql = "SELECT * FROM IFSAPP.PROJECT where project_id in ('YRO-06-194','YRO-06-209','YRO-197-C', 'YRO-06-201','YRO-07-218','YRO-07-233','YRO-07-211', 'YRO-06-206','YRO-11-266','YCRO11-256','YRO-11MA20','YRO-06-195','YRO-07-212','YRO-11-267', 'YRO-06-196','YRO-11-264','YRO-11-265') order by project_id";
DbCommand cmd = db.GetSqlStringCommand(sql);
return(db.ExecuteDataSet(cmd));
}
/// <summary>
/// 取得零件Spec列表
/// </summary>
/// <param name="pid"></param>
/// <param name="contract"></param>
/// <returns></returns>
public static DataSet PartSpecData(string pid, string contract)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
string sql = "SELECT part_no,part_spec FROM plm.MM_PART_TAB where parentid=" + pid + " and contract='" + contract + "'";
DbCommand cmd = db.GetSqlStringCommand(sql);
return(db.ExecuteDataSet(cmd));
}
/// <summary>
/// 返回所有Project列表
/// </summary>
/// <returns></returns>
public static DataSet FindReasonDataset()
{
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
string sql = "SELECT * FROM IFSAPP.YRS_REQUISITION_REASON_TAB";
DbCommand cmd = db.GetSqlStringCommand(sql);
return(db.ExecuteDataSet(cmd));
}
/// <summary>
/// 返回所有Project列表
/// </summary>
/// <returns></returns>
public static DataSet FindProDataset()
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
string sql = "SELECT * FROM IFSAPP.PROJECT";
DbCommand cmd = db.GetSqlStringCommand(sql);
return(db.ExecuteDataSet(cmd));
}
public static DataSet PartTypeDs()
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
string sql = "SELECT TYPEID, TYPE_DESC FROM plm.MM_PART_TYPE_TAB";
DbCommand cmd = db.GetSqlStringCommand(sql);
return(db.ExecuteDataSet(cmd));
}
/// <summary>
/// 获得该节点所有的子节点
/// </summary>
/// <param name="nodeid"></param>
/// <returns></returns>
public static DataSet GetSubNodes(int nodeid)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
//OracleDatabase db = new OracleDatabase(UserSecurity.ConnectionString);
string sql = "SELECT PARENT_INDEX,TEXT FROM PLM.TREENODES_TAB WHERE PARENT_ID=" + nodeid + " ORDER BY PARENT_INDEX";
DbCommand cmd = db.GetSqlStringCommand(sql);
return(db.ExecuteDataSet(cmd));
}
/// <summary>
/// 返回所有子项目列表
/// </summary>
/// <returns></returns>
public static DataSet FindAllSubPro(string id)
{
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
//Database db = DatabaseFactory.CreateDatabase("ifsConnection");
string sql = "SELECT t.project_id, t.sub_project_id,nvl(parent_sub_project_id,'0') parent_sub_project_id,t.description FROM IFSAPP.SUB_PROJECT T WHERE T.PROJECT_ID =:id ORDER BY t.sub_project_id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.String, id);
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));
}
/// <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>
/// 获取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 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 DataSet FindActivityDs(string id)
{
// Database db = DatabaseFactory.CreateDatabase("ifsConnection");
OracleDatabase db = new OracleDatabase(DataAccess.IFSConnStr);
string sql = "SELECT activity_seq,description,sub_project_id,activity_no FROM IFSAPP.ACTIVITY T WHERE T.PROJECT_ID =:id ORDER BY t.activity_seq";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.String, id);
return(db.ExecuteDataSet(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));
}
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));
}
/// <summary>
/// 查看有PROJECT_System_TAB表里有没有 选择的项目
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
public static DataSet TreeByPid(string id, string keyword, string newFlag)
{
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
string sql = "SELECT * FROM PLM.PROJECT_System_TAB WHERE project_id=:id and NEWFLAG ='" + newFlag + "'";
if (keyword != string.Empty)
{
sql += " AND DESCRIPTION LIKE '%" + keyword + "%'";
}
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "id", DbType.String, id);
return(db.ExecuteDataSet((cmd)));
}
public void RecordIsUpdatedWhenUsingStoredProcCommandWithParameterAndCommit()
{
OracleDatabase db = (OracleDatabase)DatabaseFactory.CreateDatabase("OracleTest");
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
int initialCount = Convert.ToInt32(db.ExecuteScalar(CommandType.Text, "select count(countrycode) from Country"));
db.ExecuteDataSet(transaction, "AddCountryListAll", new object[] { "TEMP", "Temporary", "" });
db.ExecuteDataSet(transaction, "DeleteCountryListAll", new object[] { "TEMP", "" });
transaction.Commit();
int finalCount = Convert.ToInt32(db.ExecuteScalar(CommandType.Text, "select count(countrycode) from Country"));
Assert.IsTrue(finalCount == initialCount);
}
catch (Exception e)
{
transaction.Rollback();
Assert.Fail("Transaction Rolled Back : " + e.Message);
}
}
}
private void ModifyNode_Load(object sender, EventArgs e)
{
#region 初始化扩展的下拉框控件
//checkedListBox1.SelectionMode= SelectionMode.MultiSimple;
comboBox1.ImageList = imageList1;
comboBox2.ImageList = imageList1;
for (int i = 0; i < imageList1.Images.Count; i++)
{
comboBox1.Items.Add(new ComboBoxExItem(i.ToString(), i));
comboBox2.Items.Add(new ComboBoxExItem(i.ToString(), i));
}
#endregion
textBox2.Text = currentnode.Text;
textBox3.Text = currentnode.Name;
comboBox1.Text = currentnode.ImageIndex.ToString();
comboBox2.Text = currentnode.SelectedImageIndex.ToString();
flag = DetailInfo.Categery.TreeNodes.GetNodeFlag(Convert.ToInt32(currentnode.Tag));
if (flag == "N")
{
radioBtn.Checked = true;
radioButton1.Checked = false;
checkedListBox1.Visible = false;
}
else
{
radioButton1.Checked = true;
radioBtn.Checked = false;
checkedListBox1.Visible = true;
}
string sql = "select t.privilege_id,t.privilege_flag from privilege_tab t where t.privilege_cata='Pipe' order by privilege_id";
DataBind(sql);
List <int> privilegeids = PrivilegeNode.GetPrivilegeIds(Convert.ToInt32(currentnode.Tag));
OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr);
DataSet ds = db.ExecuteDataSet(CommandType.Text, sql);
//string aa = checkedListBox1.GetItemText(checkedListBox1.Items[1]);
for (int j = 0; j < checkedListBox1.Items.Count; j++)
{
for (int i = 0; i < privilegeids.Count; i++)
{
if (ds.Tables[0].Rows[j]["privilege_id"].ToString() == privilegeids[i].ToString())
{
checkedListBox1.SetItemChecked(j, true);
}
}
}
}
/// <summary>
/// 功能：对传入的SQL执行查询操作，返回DataTable
/// 日期：2013-05-15
/// </summary>
/// <param name="sql">执行的SQL</param>
/// <param name="parList">参数列表</param>
/// <param name="dbT">执行库</param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string sql, List <ParameterEntity> parList, DataBaseType dbT)
{
//try
{
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.ExecuteDataSet(cmd).Tables[0]);
}
//catch
//{
// return null;
//}
}
/// <summary>
/// SELECCCIONAR EL TEMPORAL ORACLE
/// </summary>
/// <returns></returns>
public DataTable select_tmp_ora()
{
DataTable dtdoc = null;
string sqlquery = "SELECT RTL_LOC_ID,BUSINESS_DATE,WKSTN_ID,TRANS_SEQ,TOTAL,FISCAL_NUMBER FROM " + Ent_Acceso_BD.nom_tabla + " WHERE SEND_SERVER IS NULL AND SUBSTR(FISCAL_NUMBER,0,1) IN ('B','F')";
try
{
object results = new object[1];
Database db = new OracleDatabase(Ent_Acceso_BD.conn());
DbCommand dbCommandWrapper = db.GetSqlStringCommand(sqlquery);
dtdoc = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
}
catch
{
dtdoc = null;
}
return(dtdoc);
}
public DataTable get_poslog(string caja, string nro_ticket)
{
DataTable dtdoc = null;
string sqlquery = "SELECT * FROM TRN_POSLOG_DATA where trans_seq=" + nro_ticket.ToString() + " and WKSTN_ID=" + caja + "";
try
{
object results = new object[1];
Database db = new OracleDatabase(Ent_Acceso_BD.conn());
//string bus = "0103001000036-1";
DbCommand dbCommandWrapper = db.GetSqlStringCommand(sqlquery);
dtdoc = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
}
catch (Exception exc)
{
dtdoc = null;
}
return(dtdoc);
}