/// <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; } }
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 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); }
public void DataIsLoadedWhenLoadDataSetUsingStoredProcCommandAndMultipleTables() { OracleDatabase db = (OracleDatabase)DatabaseFactory.CreateDatabase("OracleTest"); DataSet ds = new DataSet(); OracleCommand dbCommand = (OracleCommand)db.GetStoredProcCommand("GetCustomersAndProductsView"); db.AddParameter(dbCommand, "cur_OUT", OracleType.Cursor, 100, ParameterDirection.Output, true, 1, 1, null, DataRowVersion.Default, null); db.AddParameter(dbCommand, "cur_Products", OracleType.Cursor, 100, ParameterDirection.Output, true, 1, 1, null, DataRowVersion.Default, null); db.LoadDataSet(dbCommand, ds, new string[] { "Customers", "Products" }); int rows = ds.Tables["Customers"].Rows.Count; int columns = ds.Tables["Customers"].Columns.Count; Assert.IsTrue(rows == dsCustomers.Tables[0].Rows.Count); Assert.IsTrue(columns == dsCustomers.Tables[0].Columns.Count); for (int i = 0; i < rows; i++) { for (int j = 0; j < columns; j++) { Assert.AreEqual(dsCustomers.Tables[0].Rows[i][j].ToString().Trim(), ds.Tables["Customers"].Rows[i][j].ToString().Trim()); } } rows = ds.Tables["Products"].Rows.Count; columns = ds.Tables["Products"].Columns.Count; Assert.IsTrue(rows == dsProducts.Tables[0].Rows.Count); Assert.IsTrue(columns == dsProducts.Tables[0].Columns.Count); for (int i = 0; i < rows; i++) { for (int j = 0; j < columns; j++) { Assert.AreEqual(dsProducts.Tables[0].Rows[i][j].ToString().Trim(), ds.Tables["Products"].Rows[i][j].ToString().Trim()); } } }
/// <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; } }
/// <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)); }
private void HSBA_update(object sender, RoutedEventArgs e) { try { string sql = "UPDATE QT.HSBA SET NGAYKB=TO_DATE('" + HSBA_NGAYKB.Text + "', 'dd/mm/yyyy'), MABS='" + HSBA_MABS.Text + "', MABN='" + HSBA_MABN.Text + "', MATT=SYS_CONTEXT('userenv', 'SESSION_USER'), TINHTRANGBANDAU='" + HSBA_TTBD.Text + "' WHERE MAKB='" + HSBA_MAKB.Text + "'"; if (HSBA_KLBS.Text.Trim().Length != 0) { sql = "UPDATE QT.HSBA SET NGAYKB=TO_DATE('" + HSBA_NGAYKB.Text + "', 'dd/mm/yyyy'), MABS='" + HSBA_MABS.Text + "', MABN='" + HSBA_MABN.Text + "', MATT=SYS_CONTEXT('userenv', 'SESSION_USER'), TINHTRANGBANDAU='" + HSBA_TTBD.Text + "', KETLUANBS='" + HSBA_KLBS.Text + "' WHERE MAKB='" + HSBA_MAKB.Text + "'"; } //string sql = $"EXEC UPDATE_HSBA('{HSBA_MAKB.Text}', '{HSBA_MABS.Text}', '{HSBA_MABN.Text}', '{HSBA_NGAYKB.Text}', '{HSBA_TTBD.Text}'"; //if (HSBA_KLBS.Text.Trim().Length != 0) // sql += $", '{HSBA_KLBS.Text}')"; //else // sql += ", NULL)"; db = OracleDatabase.Instance; db.Query(sql); db.Query("COMMIT"); MessageBox.Show("Update thành công"); this.UpdateDataGrid(); } catch { MessageBox.Show("Không thể chỉnh sửa bệnh án"); } }
public static void Clean(string path, OracleDatabase target) { ModelParser parser = new ModelParser(); CleanModelHelper c = new CleanModelHelper(path, target, parser); c.Visit(); }
private void HSBA_insert(object sender, RoutedEventArgs e) { try { string sql = ""; if (HSBA_KLBS.Text.Trim() == "") { sql = $"INSERT INTO QT.HSBA(MAKB, MABN, NGAYKB, MATT, MABS, TINHTRANGBANDAU) VALUES " + $"('{HSBA_MAKB.Text}', '{HSBA_MABN.Text}', TO_DATE('{HSBA_NGAYKB.Text}', 'dd/mm/yyyy'), SYS_CONTEXT('userenv', 'SESSION_USER'), '{HSBA_MABS.Text}', '{HSBA_TTBD.Text}')"; } else { sql = $"INSERT INTO QT.HSBA(MAKB, MABN, NGAYKB, MATT, MABS, TINHTRANGBANDAU, KETLUANBS) VALUES " + $"('{HSBA_MAKB.Text}', '{HSBA_MABN.Text}', TO_DATE('{HSBA_NGAYKB.Text}', 'dd/mm/yyyy'), SYS_CONTEXT('userenv', 'SESSION_USER'), '{HSBA_MABS.Text}', '{HSBA_TTBD.Text}', '{HSBA_KLBS.Text}')"; } db = OracleDatabase.Instance; db.Query(sql); db.Query("COMMIT"); MessageBox.Show("Thêm thành công"); this.UpdateDataGrid(); } catch { MessageBox.Show("Thêm không thành công"); } }
public void TestMethod2() { var db = new OracleDatabase(); var g = new GrantModel() { Key = "toto", ColumnObjectName = "col", FullObjectName = "", Files = new FileCollection() { new FileElement() { Path = "toto.sql" } }, Grantable = true, Hierarchy = true, ObjectName = "obj", ObjectSchema = "schema", Role = "role", Valid = true, }; g.Privileges.Add(new PrivilegeModel() { PrivilegeName = "SELECT" }); g.Privileges.Add(new PrivilegeModel() { PrivilegeName = "DELETE" }); db.Grants.Add(g); }
private void HSDV_update(object sender, RoutedEventArgs e) { try { string sql = ""; if (dr != null && dr["NGUOITHUCHIEN"].ToString() == HSDV_MANV.Text) { sql = $"UPDATE QT.HOSO_DICHVU SET MADV='{HSDV_MADV.Text}', NGAY=TO_DATE('{HSDV_NGAY.Text}','dd/mm/yyyy')" + $", KETLUAN='{HSDV_KL.Text}' WHERE MAKB='{HSDV_MAKB.Text}'"; } else { sql = $"UPDATE QT.HOSO_DICHVU SET MADV='{HSDV_MADV.Text}', NGAY=TO_DATE('{HSDV_NGAY.Text}','dd/mm/yyyy')" + $", NGUOITHUCHIEN='{HSDV_MANV.Text}', KETLUAN='{HSDV_KL.Text}' WHERE MAKB='{HSDV_MAKB.Text}'"; } db = OracleDatabase.Instance; db.Query(sql); db.Query("COMMIT"); MessageBox.Show("Cập nhật thành công"); this.UpdateDataGrid(); } catch (Exception exc) { MessageBox.Show("Không thể chỉnh sửa hồ sơ dịch vụ"); } }
/// <summary> /// 根据blockId列表查找BlockName /// </summary> /// <param name="ids"></param> /// <returns></returns> public static string GetNames(string ids) { if (ids.Trim() == string.Empty) { return(string.Empty); } string blockNames = string.Empty; OracleDatabase db = new OracleDatabase(DataAccess.OIDSConnStr); string Sql = "SELECT DESCRIPTION FROM PLM.PROJECT_BLOCK_TAB WHERE BLOCK_ID IN (" + ids + ")"; DbCommand cmd = db.GetSqlStringCommand(Sql); using (IDataReader dr = db.ExecuteReader(cmd)) { while (dr.Read()) { blockNames += "," + dr[0].ToString(); } dr.Close(); } if (blockNames != string.Empty) { blockNames = blockNames.Substring(1); } return(blockNames); }
public void DataIsLoadedWhenLoadDataSetUsingStoredProcAndMultipleTableOverload() { OracleDatabase db = (OracleDatabase)DatabaseFactory.CreateDatabase("OracleTest"); DataSet ds = new DataSet(); db.LoadDataSet("GetCustomersAndProductsView", ds, new string[] { "Customers", "Products" }, new object[] { 1, 1 }); int rows = ds.Tables["Customers"].Rows.Count; int columns = ds.Tables["Customers"].Columns.Count; Assert.IsTrue(rows == dsCustomers.Tables[0].Rows.Count); Assert.IsTrue(columns == dsCustomers.Tables[0].Columns.Count); for (int i = 0; i < rows; i++) { for (int j = 0; j < columns; j++) { Assert.AreEqual(dsCustomers.Tables[0].Rows[i][j].ToString().Trim(), ds.Tables["Customers"].Rows[i][j].ToString().Trim()); } } rows = ds.Tables["Products"].Rows.Count; columns = ds.Tables["Products"].Columns.Count; Assert.IsTrue(rows == dsProducts.Tables[0].Rows.Count); Assert.IsTrue(columns == dsProducts.Tables[0].Columns.Count); for (int i = 0; i < rows; i++) { for (int j = 0; j < columns; j++) { Assert.AreEqual(dsProducts.Tables[0].Rows[i][j].ToString().Trim(), ds.Tables["Products"].Rows[i][j].ToString().Trim()); } } }
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 object GetRemoteEquipmentList(string parentId) { try { ExeConfigurationFileMap fileMap = new ExeConfigurationFileMap(); fileMap.ExeConfigFilename = System.Web.HttpContext.Current.Server.MapPath(@"~/XmlConfig/remote_database.config"); Configuration config = ConfigurationManager.OpenMappedExeConfiguration(fileMap, ConfigurationUserLevel.None); AppSettingsSection appsection = (AppSettingsSection)config.GetSection("appSettings"); string connectstring = appsection.Settings["RemoteBaseDb"].Value; IDatabase dbBase = new OracleDatabase(connectstring); string strWhere = " 1=1"; if (!string.IsNullOrEmpty(parentId)) { strWhere += string.Format(" and parentid ='{0}' "); } else { strWhere += string.Format(" and id ='-1' "); //根 } string sql = string.Format("select facname,faccode,parentid, id from FACTBFACILITY t where {0} order by faccode", strWhere); DataTable dt = dbBase.FindTable(sql); dbBase.Close(); return(new { code = 0, info = "获取数据成功", count = dt.Rows.Count, data = dt }); } catch (Exception ex) { return(new { code = -1, info = ex.Message, count = 0 }); } }
/// <summary> /// Executes a database reader for parameterized sql. /// </summary> /// <param name="commandSql">The command SQL.</param> /// <param name="dbInstance">The database instance.</param> /// <param name="parms">The parameters collection as a dictionary.</param> /// <returns></returns> static public IDataReader ExecuteReader(string commandSql, string dbInstance, IDictionary <string, object> parms) { try { var instance = dbInstance; if (string.IsNullOrEmpty(instance)) { instance = ApplicationConfiguration.Instance.DefaultInstance; } var db = new OracleDatabase(GetConnectStringFromInstance(instance)); var reader = new SqlParameterReader(); foreach (var key in parms.Keys) { reader.AddParameter(key, parms[key]); } return(reader.ExecuteReader(db, commandSql)); } catch (Exception ex) { LogError(ex.Message, "ExecuteReader"); throw; } }
private static void HandleConnectionError(OracleDatabase db, Exception ex, int?tryCount) { // NOTE: Oracle의 경우, Connection 에러가 나면, 대기 후에 접속하면 성공한다. 왜 그런지는 잘 모른다... // HINT: http://kyeomstar.tistory.com/160 // const int MaxTimeout = 500; // milliseconds if (ex.Message.Contains("ORA-12519") || ex.Message.Contains("Unknown connection string parameter")) { if (log.IsWarnEnabled) { log.Warn("Database Connection 생성 및 Open 수행 시에 예외가 발생했습니다. ConnectionString=[{0}]", db.ConnectionString); log.Warn("Oracle 서버에 ALTER SYSTEM SET PROCESSES=500 SCOPE=SPFILE; 를 수행해 보세요. 기본 PROCESS 수가 40입니다. 이 값을 늘려보세요."); log.Warn(ex); } } else { if (log.IsErrorEnabled) { log.Error("Database Connection 생성 및 Open 수행 시에 예외가 발생했습니다. ConnectionString=[{0}]", db.ConnectionString); log.Error(ex); } } var timeout = Math.Min(MaxTimeout, Math.Abs(tryCount.GetValueOrDefault(1)) * 50); Thread.Sleep(timeout); }
private void NhanVien_update(object sender, RoutedEventArgs e) { try { string sql = ""; if (dr != null && dr["LUONG"].ToString() == NV_LUONG.Text) { sql = "UPDATE QT.NHANVIEN SET HOTEN='" + NV_HOTEN.Text + "', NGAYSINH=TO_DATE('" + NV_NGAYSINH.Text + "', 'mm/dd/yyyy'), VAITRO ='" + NV_VT.Text + "', DONVI='" + NV_DV.Text + "' WHERE MANV='" + NV_MANV.Text + "'"; } else { sql = "UPDATE QT.NHANVIEN SET HOTEN='" + NV_HOTEN.Text + "', NGAYSINH=TO_DATE('" + NV_NGAYSINH.Text + "', 'mm/dd/yyyy'), VAITRO ='" + NV_VT.Text + "', DONVI='" + NV_DV.Text + "', LUONG=" + Int32.Parse(NV_LUONG.Text) + " WHERE MANV='" + NV_MANV.Text + "'"; } db = OracleDatabase.Instance; db.Query(sql); db.Query("COMMIT"); MessageBox.Show("Cập nhật thành công"); this.UpdateDataGrid(); } catch (Exception exc) { MessageBox.Show("Không thể chỉnh sửa thông tin nhân viên"); } }
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 MedicalServiceSituationService() { //参数为数据库的用户名 String connectionString = OracleDatabaseData.ConnectionString; db = new OracleDatabase(connectionString); }
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">执行的数据库</param> /// <param name="parList">参数</param> /// <param name="dbT">执行库</param> /// <returns></returns> public static object ExecuteScalar(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); } } object obj = oraDb.ExecuteScalar(cmd); if (Convert.IsDBNull(obj)) { return(null); } return(obj); } catch (Exception e) { //return null; throw e; } }
/// <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)); }
public RadDBImpl() { connectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;//ConfigurationSettings.AppSettings["constr"] string providerName = Convert.ToString(System.Configuration.ConfigurationSettings.AppSettings["ProviderName"]); InitLogger(); if (string.IsNullOrEmpty(connectionString) || string.IsNullOrEmpty(providerName)) { } else { if (providerName == "System.Data.OracleClient") { OracleDatabase oraDb = new OracleDatabase(connectionString); _db = oraDb; } else if (providerName == "System.Data.SqlClient") { SqlDatabase sqlDb = new SqlDatabase(connectionString); _db = sqlDb; } else { } } }
protected override void Arrange() { EnvironmentHelper.AssertOracleClientIsInstalled(); String connectionString = ConfigurationManager.ConnectionStrings["OracleTest"].ConnectionString; database = new OracleDatabase(connectionString); }
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> /// 根据角色名称及权限标识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> /// 功能:查询分页数据 /// 日期: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); }
protected override void ProcessRecord() { LogInitializer.Initialize(); var ctx = new Bb.Oracle.Reader.ArgumentContext() { Login = Username, Pwd = Password, Source = Source, Filename = this.OutputFilename, ExcludeCode = ExcludeCode, Name = string.IsNullOrEmpty(Name) ? Source : Name, ExcludedSchemas = ExcludedSchemas, OwnerFilter = this.OwnerFilter }; FileInfo f = new FileInfo(this.OutputFilename); if (!f.Directory.Exists) { f.Directory.Create(); } OracleDatabase db = Database.GenerateFile(ctx); base.WriteObject(db); base.ProcessRecord(); }
public void CanGetExtraInformation() { ConnectionStringSettings data = new ConnectionStringSettings(name, "connection string;"); OracleConnectionData oracleConnectionData = new OracleConnectionData(); oracleConnectionData.Name = name; oracleConnectionData.Packages.Add(new OraclePackageData("package1", "pref1")); oracleConnectionData.Packages.Add(new OraclePackageData("package2", "pref2")); OracleConnectionSettings oracleConnectionSettings = new OracleConnectionSettings(); oracleConnectionSettings.OracleConnectionsData.Add(oracleConnectionData); configurationSource.Add(OracleConnectionSettings.SectionName, oracleConnectionSettings); OracleDatabase database = (OracleDatabase)assembler.Assemble(data.Name, data, configurationSource); Assert.IsNotNull(database); Assert.AreSame(typeof(OracleDatabase), database.GetType()); // can't access the packages - must resort to side effect DbCommand dBCommand = database.GetStoredProcCommand(OracleTestStoredProcedureInPackageWithTranslation); Assert.AreEqual((object)OracleTestTranslatedStoredProcedureInPackageWithTranslation, dBCommand.CommandText); }
public void CanExecuteCommandWithEmptyPackages() { ConnectionStringSettings data = ConfigurationManager.ConnectionStrings["OracleTest"]; OracleDatabase oracleDatabase = new OracleDatabase(data.ConnectionString); DbConnection connection = oracleDatabase.CreateConnection(); Assert.IsNotNull(connection); Assert.IsTrue(connection is OracleConnection); connection.Open(); DbCommand cmd = oracleDatabase.GetSqlStringCommand("Select * from Region"); cmd.CommandTimeout = 0; }
public void ConnectionTest() { OracleDatabase oracleDatabase = new OracleDatabase(); oracleDatabase.ConfigurationName = "OracleTest"; oracleDatabase.Initialize(new DatabaseConfigurationView(context)); IDbConnection connection = oracleDatabase.GetConnection(); Assert.IsNotNull(connection); Assert.IsTrue(connection is OracleConnection); connection.Open(); DBCommandWrapper cmd = oracleDatabase.GetSqlStringCommandWrapper("Select * from Region"); cmd.CommandTimeout = 0; Assert.AreEqual(cmd.CommandTimeout, -1); }
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(); } }
protected override void Arrange() { EnvironmentHelper.AssertOracleClientIsInstalled(); database = new OracleDatabase("server=entlib;user id=testuser;password=testuser"); }
protected override void Arrange() { EnvironmentHelper.AssertOracleClientIsInstalled(); database = new OracleDatabase("Data Source=XE;User id=system;Password=admin"); }
protected override void Arrange() { database = new OracleDatabase("server=entlib;user id=testuser;password=testuser"); }