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