private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
while (!backgroundWorker1.CancellationPending)
{
try
{
using (var conn = new OracleConnection(textBox1.Text))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT 'OK' FROM DUAL";
var result = (string) cmd.ExecuteScalar();
AppendText(string.Format("{0}: Sql executed OK", DateTime.Now));
}
}
}
catch (Exception ex)
{
//MessageBox.Show(ex.ToString());
AppendText(string.Format("{0}: Error:{1}", DateTime.Now, ex.Message));
}
for (var i = 0; i < 100; i++)
{
Thread.Sleep(30);
if (backgroundWorker1.CancellationPending) return;
}
}
}
public List<PaisDTO> getPais()
{
List<PaisDTO> lstPais = new List<PaisDTO>();
Parametros p = new Parametros();
PaisDTO itemPais;
using (OracleConnection con = new OracleConnection(p.oracleConnString().ToString()))
{
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "select IDPAIS, NOMBREPAIS from pais";
using (OracleDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
itemPais = new PaisDTO();
itemPais.idPais = reader.GetInt32(0);
itemPais.nombrePais = reader.GetString(1);
lstPais.Add(itemPais);
}
}
con.Close();
con.Dispose();
}
return lstPais;
}
public static int? SaveBinaryContent(int? id, string processName, string fieldName, byte[] content, OracleConnection connection)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
using (var command = connection.CreateCommand())
{
command.Parameters.Add("p_Content", content == null ? DBNull.Value : (object)content);
if (id == null || id.Value == 0)
{
command.CommandText = string.Format(CultureInfo.InvariantCulture, @"INSERT INTO """"{0}"""" (""""ProcessName"""", """"FieldName"""", """"Content"""", """"IsRemoved"""") VALUES (:p_ProcessName, :p_FieldName, :p_Content, '0'); RETURNING """"Id"""" INTO :p_Id", OracleNamesTranslator.Translate(Constants.BinaryContentTableName));
var idParam = command.Parameters.Add("p_Id", OracleDbType.Int32, ParameterDirection.Output);
command.Parameters.Add("p_ProcessName", processName == null ? DBNull.Value : (object)processName);
command.Parameters.Add("p_FieldName", fieldName == null ? DBNull.Value : (object)fieldName);
command.ExecuteNonQuery();
id = Convert.ToInt32(idParam.Value, CultureInfo.InvariantCulture);
}
else
{
command.Parameters.Add("p_Id", id.Value);
command.CommandText = string.Format(CultureInfo.InvariantCulture, @"UPDATE """"{0}"""" SET """"Content"""" = :p_Content WHERE """"Id"""" = :p_Id", OracleNamesTranslator.Translate(Constants.BinaryContentTableName));
command.ExecuteNonQuery();
}
}
return id;
}
public static bool Save(List<RoleFunctions> rolefunctions, OracleConnection conn)
{
try
{
bool saved = false;
string query = "INSERT INTO SYSTEMROLEFUNCTIONS (ROLEID, FUNCTIONID) values(:roleid, :functionid)";
using (var command = conn.CreateCommand())
{
command.CommandText = query;
command.CommandType = CommandType.Text;
command.BindByName = true;
// In order to use ArrayBinding, the ArrayBindCount property
// of OracleCommand object must be set to the number of records to be inserted
command.ArrayBindCount = rolefunctions.Count;
command.Parameters.Add(":roleid", OracleDbType.Int32, rolefunctions.Select(rf => rf.RoleId).ToArray(), ParameterDirection.Input);
command.Parameters.Add(":functionid", OracleDbType.Int32, rolefunctions.Select(rf => rf.FunctionId).ToArray(), ParameterDirection.Input);
int result = command.ExecuteNonQuery();
if (result == rolefunctions.Count)
saved = true;
return saved;
}
}
catch(Exception ex)
{
throw ex;
}
}
// read the BLOB into file "cs-parser2.cs"
public static void ReadBlob (OracleConnection connection)
{
if (File.Exists(outfilename) == true) {
Console.WriteLine("Filename already exists: " + outfilename);
return;
}
OracleCommand rcmd = connection.CreateCommand ();
rcmd.CommandText = "SELECT BLOB_COLUMN FROM BLOBTEST";
OracleDataReader reader2 = rcmd.ExecuteReader ();
if (!reader2.Read ())
Console.WriteLine ("ERROR: RECORD NOT FOUND");
Console.WriteLine (" TESTING OracleLob OBJECT 2...");
OracleLob lob2 = reader2.GetOracleLob (0);
Console.WriteLine (" LENGTH: {0}", lob2.Length);
Console.WriteLine (" CHUNK SIZE: {0}", lob2.ChunkSize);
byte[] lobvalue = (byte[]) lob2.Value;
if (ByteArrayCompare(bytes1, lobvalue) == true)
Console.WriteLine("bytes1 and bytes2 are equal: good");
else
Console.WriteLine("bytes1 and bytes2 are not equal: bad");
FileStream fs = new FileStream(outfilename, FileMode.CreateNew);
BinaryWriter w = new BinaryWriter(fs);
w.Write(lobvalue);
w.Close();
fs.Close();
lob2.Close ();
reader2.Close ();
}
// creates and prepares a new DbCommand object on a new connection
public static OracleCommand CreateCommand()
{
// Obtain the database provider name
string dataProviderName = BalloonShopConfiguration.DbProviderName;
// Obtain the database connection string
string connectionString = BalloonShopConfiguration.DbConnectionString;
// Create a new data provider factory
DbProviderFactory factory = DbProviderFactories.
GetFactory(dataProviderName);
// Obtain a database specific connection object
OracleConnection conn = new OracleConnection();
// Set the connection string
conn.ConnectionString = connectionString;
// Create a database specific command object
OracleCommand comm = conn.CreateCommand();
// DbCommand comm = conn.CreateCommand();
// Set the command type to stored procedure
comm.CommandType = CommandType.StoredProcedure;
//OracleParameter p2 =
//comm.Parameters.Add("refcur_out", OracleDbType.RefCursor);
//p2.Direction = ParameterDirection.Output;
// Return the initialized command object
return comm;
}
private void button2_Click(object sender, EventArgs e)
{
// LotNo.Clear();
// Quantity.Clear();
// Price.Clear();
string _connstring = "Data Source=ORCL;User Id = scott;Password=tiger;";
// decimal _totalRecords;
String name;
try
{
OracleConnection _connObj = new OracleConnection(_connstring);
_connObj.Open();
OracleCommand _cmdObj = _connObj.CreateCommand();
// _cmdObj.CommandText = "SELECT ename from employees where empid = 101";
_cmdObj.CommandText = "SELECT ename from emp";
// _totalRecords = (decimal)_cmdObj.ExecuteScalar();
name = (String)_cmdObj.ExecuteScalar();
MessageBox.Show("Total records:" + name);
textBox1.AppendText("Name: " + name);
_connObj.Close();
_connObj.Dispose();
_connObj = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public List<DepartamentoDTO> getDepartamento(int idPais = -1)
{
List<DepartamentoDTO> lstDepar = new List<DepartamentoDTO>();
Parametros p = new Parametros();
DepartamentoDTO itemDep;
using (OracleConnection con = new OracleConnection(p.oracleConnString().ToString()))
{
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = (idPais != -1) ? "select IDDEPARTAMENTO, IDPAIS, NOMBREDEPARTAMENTO from departamento where IDPAIS = " + idPais.ToString() : "select IDDEPARTAMENTO, IDPAIS, NOMBREDEPARTAMENTO from departamento";
using (OracleDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
itemDep = new DepartamentoDTO();
itemDep.idDepartamento = reader.GetInt32(0);
itemDep.idPais = reader.GetInt32(1);
itemDep.nombreDepartamento = reader.GetValue(2).ToString();
lstDepar.Add(itemDep);
}
}
con.Close();
con.Dispose();
}
return lstDepar;
}
private static void GrantConnect(string username, OracleConnection con)
{
string sql = "GRANT CONNECT TO " + username;
using (var cmd = con.CreateCommand())
{
cmd.CommandText = sql;
cmd.BindByName = true;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
private static void CreateUser(string username, string pass, OracleConnection con)
{
string sql = "create user " + username + " identified by " + pass;
using (var cmd = con.CreateCommand())
{
cmd.CommandText = sql;
cmd.BindByName = true;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
public void insertUserOracle(string user, string password)
{
Parametros p = new Parametros();
using (OracleConnection con = new OracleConnection(p.oracleConnString().ToString()))
{
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "INSERT INTO USUARIO(IDUSUARIO, EMAIL, PASSWORD, USERNAME) VALUES(SEQ_CLIENTE.nextval,'" + user + "','" + password + "','" + user + "')";
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
}
private static void ExecuteSql(string connectionString, string sql, string schemaName = null)
{
using (var conn = new OracleConnection(connectionString))
using (var command = conn.CreateCommand())
{
conn.Open();
if (!string.IsNullOrWhiteSpace(schemaName))
{
command.CommandText = "ALTER SESSION SET CURRENT_SCHEMA=" + schemaName;
command.ExecuteNonQuery();
}
command.CommandText = sql;
command.ExecuteNonQuery();
}
}
private static bool UserExists(string username, OracleConnection con)
{
string sql = "SELECT count(1) from all_users where username=upper(:username)";
using (var cmd = con.CreateCommand())
{
cmd.CommandText = sql;
cmd.BindByName = true;
cmd.CommandType = CommandType.Text;
using (var param = new OracleParameter("username", OracleDbType.Varchar2, ParameterDirection.Input))
{
param.Value = username;
cmd.Parameters.Add(param);
object o = cmd.ExecuteScalar();
return ((decimal)o)!= 0;
}
}
}
protected void ShowVolunteers()
{
string connectionString = GetConnectionString();
using (OracleConnection connection = new OracleConnection())
{
connection.ConnectionString = connectionString;
connection.Open();
OracleCommand command = connection.CreateCommand();
string sql = @"SELECT FNAME AS ""FIRST NAME"",LNAME AS ""LAST NAME"",DEGIGNATION AS ""DESIGNATION"",ADDR AS ""ADDRESS"", TEL1, TEL2,EMAIL,CASE SEX WHEN 'M' THEN 'MALE' ELSE 'FEMALE' END AS ""GENDER"",SERVED AS ""COURSES SERVED"" FROM T_VOLUNTEER ORDER BY FNAME";
command.CommandText = sql;
OracleDataReader reader = command.ExecuteReader();
gvVolunteers.DataSource = reader;
gvVolunteers.DataBind();
}
}
private static void DropUser(string username, OracleConnection con)
{
try
{
string sql = "DROP USER " + username;
using (var cmd = con.CreateCommand())
{
cmd.CommandText = sql;
cmd.BindByName = true;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
Console.WriteLine("DROP USER failed: {0}", e.Message);
}
}
public static void Update(SystemIdManager systemIdManager, OracleConnection conn)
{
try
{
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "update SYSTEMIDMANAGER set USERID = :1, FUNCTIONID = :2, ROLEID = :3, ROLEFUNCTIONID = :4, APPROVALID = :5 Where ID = :6";
cmd.Parameters.Add(":1", OracleDbType.Int32, systemIdManager.UserId, ParameterDirection.Input);
cmd.Parameters.Add(":2", OracleDbType.Int32, systemIdManager.FunctionId, ParameterDirection.Input);
cmd.Parameters.Add(":3", OracleDbType.Int32, systemIdManager.RoleId, ParameterDirection.Input);
cmd.Parameters.Add(":4", OracleDbType.Int32, systemIdManager.RoleFunctionId, ParameterDirection.Input);
cmd.Parameters.Add(":5", OracleDbType.Int32, systemIdManager.ApprovalId, ParameterDirection.Input);
cmd.Parameters.Add(":6", OracleDbType.Int32, systemIdManager.Id, ParameterDirection.Input);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
}
// creates and prepares a new DbCommand object on a new connection
public static OracleCommand CreateCommand()
{
// Obtain the database provider name
string dataProviderName = BalloonShopConfiguration.DbProviderName;
// Obtain the database connection string
string connectionString = BalloonShopConfiguration.DbConnectionString;
// Create a new data provider factory
DbProviderFactory factory = DbProviderFactories.
GetFactory(dataProviderName);
// Obtain a database specific connection object
OracleConnection conn = new OracleConnection();
// Set the connection string
conn.ConnectionString = connectionString;
// Create a database specific command object
OracleCommand comm = conn.CreateCommand();
// DbCommand comm = conn.CreateCommand();
// Set the command type to stored procedure
// Return the initialized command object
return comm;
}
public bool CreateDocument(string name, string type, decimal? parent_document_id, int? sequenceNo, string username, string filename, Stream input)
{
if (String.IsNullOrEmpty(name))
{
name = filename;
}
using (OracleConnection myConnection = new OracleConnection(ConnectionString))
{
using (BinaryReader r = new BinaryReader(input))
{
myConnection.Open();
OracleLob myLob = new OracleLob(myConnection, OracleDbType.Blob);
int streamLength = (int)input.Length;
myLob.Write(r.ReadBytes(streamLength), 0, streamLength);
using (OracleCommand dbcmd = myConnection.CreateCommand())
{
OracleParameter myParam1 = dbcmd.Parameters.Add("DOCUMENT", OracleDbType.Blob);
myParam1.OracleValue = myLob;
OracleParameter myParam2 = dbcmd.Parameters.Add("PARENT_DOCUMENT_ID", OracleDbType.Number);
myParam2.OracleValue = parent_document_id;
OracleParameter myParam3 = dbcmd.Parameters.Add("DOCUMENT_NAME", OracleDbType.VarChar);
myParam3.OracleValue = name;
OracleParameter myParam4 = dbcmd.Parameters.Add("SEQUENCE_NO", OracleDbType.Integer);
myParam4.OracleValue = sequenceNo;
OracleParameter myParamType = dbcmd.Parameters.Add("TYPE", OracleDbType.VarChar);
myParamType.OracleValue = type;
OracleParameter myParamUser = dbcmd.Parameters.Add("UPDATE_USER", OracleDbType.VarChar, 50);
myParamUser.OracleValue = username;
OracleParameter myParamDate = dbcmd.Parameters.Add("UPDATE_DATE", OracleDbType.TimeStampLTZ);
myParamDate.OracleValue = DateTime.UtcNow;
OracleParameter myParamFilename = dbcmd.Parameters.Add("FILENAME", OracleDbType.VarChar);
myParamFilename.OracleValue = filename;
dbcmd.CommandText = @"INSERT INTO TDOCUMENT (NAME, PARENT_DOCUMENT_ID,SEQUENCE_NO, DOCUMENT, TYPE, UPDATE_USER, UPDATE_DATE, FILENAME )
VALUES(:DOCUMENT_NAME, :PARENT_DOCUMENT_ID,:SEQUENCE_NO, :DOCUMENT, :TYPE, :UPDATE_USER, :UPDATE_DATE, :FILENAME)";
var result = dbcmd.ExecuteNonQuery();
}
}
}
return true;
}
protected void ShowTeachers(string strDesignation)
{
string connectionString = GetConnectionString();
switch(strDesignation)
{
case "T":
lblGridTitle.InnerText = "Teachers List";
break;
case "SAT":
lblGridTitle.InnerText = "Sr. AT List";
break;
case "AT":
lblGridTitle.InnerText = "AT List";
break;
default:
lblGridTitle.InnerText = "Teachers List";
break;
}
using (OracleConnection connection = new OracleConnection())
{
connection.ConnectionString = connectionString;
connection.Open();
OracleCommand command = connection.CreateCommand();
string sql = @"SELECT FNAME AS ""FIRST NAME"",MNAME AS ""MIDDLE NAME"",LNAME AS ""LAST NAME"",DEGIGNATION AS ""DESIGNATION"",ADDR AS ""ADDRESS"", TEL1, TEL2,EMAIL,CASE SEX WHEN 'M' THEN 'MALE' ELSE 'FEMALE' END AS ""GENDER"" FROM T_TEACHER WHERE (:pDesignation IS NULL OR DEGIGNATION LIKE :pDesignation) ORDER BY FNAME,LNAME,DEGIGNATION,SEX";
OracleParameter pDesignation = new OracleParameter();
pDesignation.OracleDbType = OracleDbType.Varchar2;
pDesignation.Value = strDesignation;
command.CommandText = sql;
command.Parameters.Add(pDesignation);
OracleDataReader reader = command.ExecuteReader();
gvTeachers.DataSource = reader;
gvTeachers.DataBind();
}
}
protected void btnSearch_Click(object sender, EventArgs e)
{
OracleDataReader rdr;
string connectionString = GetConnectionString();
using (OracleConnection connection = new OracleConnection())
{
string strQuery = @"SELECT * FROM T_APPLICATION WHERE APP_FNAME LIKE :srchCriteria OR APP_LNAME LIKE :srchCriteria";
connection.ConnectionString = connectionString;
connection.Open();
OracleCommand command = connection.CreateCommand();
// OR (NLS_LOWER(APP_ADDR) LIKE '%ason%' OR NLS_LOWER(REG_NO) LIKE '%an%' OR NLS_LOWER(APP_FNAME) LIKE '%an%' OR NLS_LOWER(APP_LNAME) LIKE NLS_LOWER('%an%') OR NLS_LOWER(APP_BUSINESS) LIKE '%an%') string sql = @"SELECT TRAIN_S_N AS ""Course No"",TO_CHAR(START_DATE,'MON-DD-YYYY') AS ""Start Date"",TO_CHAR(TILL_DATE,'MON-DD-YYYY') AS ""End Date"",APP_FNAME AS ""First Name"",APP_LNAME AS ""Last Name"", APP_AGE AS ""Age"",APP_HPH_NO AS ""Home Phone"",APP_OPH_NO AS ""Other Phone"",APP_EMAIL AS ""Email"",APP_ADDR AS ""Address"",APP_BUSINESS AS ""Occupation"", OLD_Y_N AS ""Old"",COURSE_TYPE AS ""Course Type"" FROM T_Application WHERE START_DATE> CURRENT_DATE ORDER BY START_DATE,APP_FNAME";
command.CommandText = strQuery;
OracleParameter parameter = new OracleParameter();
parameter.ParameterName = "srchCriteria";
parameter.OracleDbType = OracleDbType.Varchar2;
parameter.Value = txtSearch.Value; //machine is a variable of type string
command.Parameters.Add(parameter);
rdr = command.ExecuteReader();
gvApplication.DataSource = rdr;
gvApplication.DataBind();
}
}
public static Dictionary<string, string> GetStats(string strQuery)
{
Dictionary<string, string> dtStats = new Dictionary<string, string>(); ;
string connectionString = GetConnectionString();
using (OracleConnection connection = new OracleConnection())
{
connection.ConnectionString = connectionString;
connection.Open();
OracleCommand command = connection.CreateCommand();
// string sql = @"SELECT TRAIN_S_N AS ""Course No"",TO_CHAR(START_DATE,'MON-DD-YYYY') AS ""Start Date"",TO_CHAR(TILL_DATE,'MON-DD-YYYY') AS ""End Date"",APP_FNAME AS ""First Name"",APP_LNAME AS ""Last Name"", APP_AGE AS ""Age"",APP_HPH_NO AS ""Home Phone"",APP_OPH_NO AS ""Other Phone"",APP_EMAIL AS ""Email"",APP_ADDR AS ""Address"",APP_BUSINESS AS ""Occupation"", OLD_Y_N AS ""Old"",COURSE_TYPE AS ""Course Type"" FROM T_Application WHERE START_DATE> CURRENT_DATE ORDER BY START_DATE,APP_FNAME";
command.CommandText = strQuery;
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
dtStats.Add(reader[0].ToString(), reader[1].ToString());
}
}
return dtStats;
}
private void button1_Click(object sender, EventArgs e)
{
try
{
int _recordsAffected;
OracleConnection _connObj = new OracleConnection(_connstring);
_connObj.Open();
//Insert a new record
OracleCommand _cmdObj = _connObj.CreateCommand();
_cmdObj.CommandText = "INSERT INTO medicine(m_id,commname,sciname)VALUES(:MID, :COM, :SCI)";
_cmdObj.Parameters.Add(new OracleParameter("MID", midValue));
_cmdObj.Parameters.Add(new OracleParameter("COM", ComValue));
_cmdObj.Parameters.Add(new OracleParameter("SCI", SciValue));
_recordsAffected = _cmdObj.ExecuteNonQuery();
MessageBox.Show("Total records affected after insert:" + _recordsAffected);
}
catch
{
MessageBox.Show("Please retry. Either the value is incorrect or is being duplicated!");
}
}
// read the CLOB into file "cs-parser2.cs"
public static void ReadClob (OracleConnection connection)
{
OracleCommand rcmd = connection.CreateCommand ();
rcmd.CommandText = "SELECT CLOB_COLUMN FROM CLOBTEST";
OracleDataReader reader2 = rcmd.ExecuteReader ();
if (!reader2.Read ())
Console.WriteLine ("ERROR: RECORD NOT FOUND");
Console.WriteLine (" TESTING OracleLob OBJECT 2...");
OracleLob lob2 = reader2.GetOracleLob (0);
Console.WriteLine (" LENGTH: {0}", lob2.Length);
Console.WriteLine (" CHUNK SIZE: {0}", lob2.ChunkSize);
string lobvalue = (string) lob2.Value;
using (StreamWriter sw = new StreamWriter(outfilename)) {
sw.Write(lobvalue);
}
lob2.Close ();
reader2.Close ();
}
private void button1_Click(object sender, EventArgs e)
{
try
{
int _recordsAffected;
OracleConnection _connObj = new OracleConnection(_connstring);
_connObj.Open();
//Insert a new record
OracleCommand _cmdObj = _connObj.CreateCommand();
_cmdObj.CommandText = "INSERT INTO branch(bno, mgrid,location,phonenumber)VALUES(:BNO, :MGRID, :LOC, :PNO)";
_cmdObj.Parameters.Add(new OracleParameter("BNO", bnoValue));
_cmdObj.Parameters.Add(new OracleParameter("MGRID", mgrValue));
_cmdObj.Parameters.Add(new OracleParameter("LOC", locValue));
_cmdObj.Parameters.Add(new OracleParameter("PNO", pnoValue));
_recordsAffected = _cmdObj.ExecuteNonQuery();
MessageBox.Show("Total records affected after insert:" + _recordsAffected);
}
catch
{
MessageBox.Show("Please retry. Either the value is incorrect or is being duplicated!");
}
}
async Task Execute(string[] commands)
{
try
{
using (OracleConnection connection = new OracleConnection(_dbConnectionString))
{
connection.Open();
foreach(var command in commands.Where(c=> !string.IsNullOrWhiteSpace(c)))
{
using (OracleCommand cmd = connection.CreateCommand())
{
cmd.CommandText = command;
await cmd.ExecuteNonQueryAsync();
}
}
connection.Close();
}
}
catch(Exception ex)
{
Debug.WriteLine(ex.Message);
throw ex;
}
}
private void btnOK_Click(object sender, EventArgs e)
{
SysCommon.CProgress vProgress = new SysCommon.CProgress();
vProgress.EnableCancel = false;//设置进度条
vProgress.ShowDescription = true;
vProgress.FakeProgress = true;
vProgress.TopMost = true;
try
{
if (m_OracleConnection == "")
{
MessageBox.Show("连接信缺失！", "提示！");
return;
}
for (int i = 0; i < this.checkedMDData.Items.Count; i++) //遍历用户添加的源数据库
{
if (this.checkedMDData.GetItemChecked(i) == true) //用户勾选的默认为要入库的数据
{
string strPath = checkedMDData.Items[i].ToString();
OleDbConnection m_vConnSor = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + strPath + "'");
OleDbCommand vCommand = new OleDbCommand();
m_vConnSor.Open();
vProgress.ShowProgress();
vProgress.SetProgress("正在读取" + strPath + "数据库信息");
//获取源数据库中所有表的信息
DataTable schemaTable = m_vConnSor.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
for (int k = 0; k < schemaTable.Rows.Count; k++)
{
//获取表名
flog:
string strTableName = schemaTable.Rows[k][2].ToString();
OracleConnection pOracleConnection = new OracleConnection(m_OracleConnection);
if (pOracleConnection.State == ConnectionState.Closed)
{
pOracleConnection.Open();
}
OracleCommand pOracleCommand = pOracleConnection.CreateCommand();
//判断oracle数据库中是否存在该表
if (GetBySameName(strTableName, pOracleConnection))
{
if (m_vConnSor.State == ConnectionState.Closed)
{
m_vConnSor.Open();
}
pOracleCommand.CommandText = "select * from " + strTableName;
OracleDataAdapter pOracleDataAdapter = new OracleDataAdapter(pOracleCommand.CommandText, pOracleConnection);
OracleCommandBuilder pOracleCommandBuilder = new OracleCommandBuilder(pOracleDataAdapter);
DataSet pDataSet = new DataSet();
pDataSet.Tables.Add(strTableName);
pOracleDataAdapter.Fill(pDataSet, strTableName);
DataTable pOrDataTable = pDataSet.Tables[strTableName];
//获取当前获得的源数据库表的信息
vCommand = new OleDbCommand("select * from " + strTableName, m_vConnSor);
OleDbDataReader vDataReaderY = vCommand.ExecuteReader();
vProgress.SetProgress(strTableName + "表正在入库");
while (vDataReaderY.Read())
{
//获取源数据库表的架构信息
DataTable pDataTableY = vDataReaderY.GetSchemaTable();
//在oracle数据库新建的表中新建一行来存储读到源数据库表的一条记录
DataRow pDataRow = pOrDataTable.NewRow();
for (int m = 0; m < pDataTableY.Rows.Count; m++)
{
DataRow drowY = pDataTableY.Rows[m];
switch (drowY["DataType"].ToString())
{
case "System.DateTime":
if (vDataReaderY[drowY["ColumnName"].ToString()].ToString() != "")
{
pDataRow[drowY["ColumnName"].ToString()] = Convert.ToDateTime(vDataReaderY[drowY["ColumnName"].ToString()].ToString());
}
break;
case "System.Double":
case "System.Single":
if (vDataReaderY[drowY["ColumnName"].ToString()].ToString() != "")
{
pDataRow[drowY["ColumnName"].ToString()] = (Convert.ToDouble(vDataReaderY[drowY["ColumnName"].ToString()]).ToString());
}
break;
default:
pDataRow[drowY["ColumnName"].ToString()] = vDataReaderY[drowY["ColumnName"].ToString()].ToString();
break;
}
}
//提交新建行存储的信息
pOrDataTable.Rows.Add(pDataRow);
pOracleDataAdapter.Update(pDataSet, strTableName);
pDataSet.AcceptChanges();
}
vDataReaderY.Close();
pOracleDataAdapter = null;
pOracleCommandBuilder = null;
pDataSet = null;
pOrDataTable = null;
}
else//当oracle中不存在表时，新建表及其结构
{
vProgress.SetProgress("正在数据库中创建" + strTableName + "表结构");
pOracleCommand.CommandText = "create table " + strTableName + "(id number)";
if (pOracleConnection.State == ConnectionState.Closed)
{
pOracleConnection.Open();
}
pOracleCommand.ExecuteNonQuery();
if (m_vConnSor.State == ConnectionState.Closed)
{
m_vConnSor.Open();
}
vCommand = new OleDbCommand("select * from " + strTableName, m_vConnSor);
OleDbDataReader vDataReader = vCommand.ExecuteReader();
DataTable pDataTable = null;
while (vDataReader.Read())
{
pDataTable = vDataReader.GetSchemaTable();
for (int m = 0; m < pDataTable.Rows.Count; m++)
{
DataRow drow = pDataTable.Rows[m];
if (m == 0)
{
pOracleCommand.CommandText = "create table " + strTableName + "(" + drow["ColumnName"].ToString() + " " + NetChangOracleType(drow).ToString() + ")";
if (pOracleConnection.State == ConnectionState.Closed)
{
pOracleConnection.Open();
}
try
{
pOracleCommand.ExecuteNonQuery();
}
catch { MessageBox.Show(strTableName + "表新建失败！", "提示！"); }
}
else
{
pOracleCommand.CommandText = "alter table " + strTableName + " add " + drow["ColumnName"].ToString() + NetChangOracleType(drow).ToString();
try
{
pOracleCommand.ExecuteNonQuery();
}
catch { MessageBox.Show(drow["ColumnName"].ToString() + "列新建失败！", "提示！"); }
}
}
vDataReader.Close();
break;
}
//当新建表完成时返回到开始进入的状态进行执行
goto flog;
}
if (m_vConnSor.State == ConnectionState.Open)
{
m_vConnSor.Close();
}
if (pOracleConnection.State == ConnectionState.Open)
{
pOracleConnection.Close();
}
}
}
}
vProgress.SetProgress("完成入库操作！");
vProgress.Close();
}
catch (Exception ex)
{
vProgress.Close();
MessageBox.Show(ex.ToString() + "导致入库操作失败！", "提示！");
}
}
public static void Main(string[] args)
{
string tainted_2 = null;
string tainted_3 = null;
Process process = new Process();
process.StartInfo.FileName = "/bin/bash";
process.StartInfo.Arguments = "-c 'cat /tmp/tainted.txt'";
process.StartInfo.UseShellExecute = false;
process.StartInfo.RedirectStandardOutput = true;
process.Start();
using (StreamReader reader = process.StandardOutput) {
tainted_2 = reader.ReadToEnd();
process.WaitForExit();
process.Close();
}
tainted_3 = tainted_2;
if ((Math.Pow(4, 2) <= 42))
{
{}
}
else if (!(Math.Pow(4, 2) <= 42))
{
{}
}
else
{
StringBuilder text = new StringBuilder(tainted_2);
text.Replace("&", "&");
text.Replace("'", "'");
text.Replace(@"""", """);
text.Replace("<", "<");
text.Replace(">", ">");
tainted_3 = text.ToString();
}
//flaw
string query = "SELECT * FROM '" + tainted_3 + "'";
string connectionString = "Data Source=localhost;User ID=oracle_user;Password=oracle_password";
OracleConnection dbConnection = null;
try{
dbConnection = new OracleConnection(connectionString);
dbConnection.Open();
OracleCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = query;
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.ToString());
}
dbConnection.Close();
}catch (Exception e) {
Console.WriteLine(e.ToString());
}
}
public int GetOrdinal(BusinessModel input)
{
var category = input.businessCategory;
var countycode = input.countyCode;
var currentdate = DateTime.Now.Date;
var scurrentdate = string.Format("{0}/{1}/{2}", currentdate.Year, currentdate.Month, currentdate.Day);
Log.Info("000");
var OracleConnectionconn = new OracleConnection(DataSource);//进行连接
try
{
Log.Info("111");
OracleConnectionconn.Open(); //打开指定的连接
OracleCommand com = OracleConnectionconn.CreateCommand();
com.CommandText = string.Format("Select businessdate ,ordinal From businessordinal where category = '{0}' and countycode='{1}'", category, countycode); //写好想执行的Sql语句
Log.Info("222" + com.CommandText);
OracleDataReader odr = com.ExecuteReader();
var recordDate = string.Empty;
var ordinal = 0;
Log.Info("222");
while (odr.Read())//读取数据，如果返回为false的话，就说明到记录集的尾部了
{
for (int i = 0; i < odr.FieldCount; i++)
{
switch (i)
{
case 0:
recordDate = odr.GetString(i);
break;
case 1:
ordinal = odr.GetInt32(i);
break;
}
}
}
odr.Close();//关闭reader.这是一定要写的
Log.Info("222" + recordDate + "-" + ordinal + currentdate);
if (scurrentdate == recordDate)
{
com.CommandText =
string.Format("update businessordinal set ordinal = {1} where category = '{0}'and countycode='{2}' ", category, ++ordinal, countycode);
Log.Info("333" + com.CommandText);
com.ExecuteNonQuery();
return(ordinal);
}
else
{
com.CommandText =
string.Format("update businessordinal set ordinal = {1} , businessdate = '{2}' where category = '{0}'and countycode='{3}' ", category, 1, scurrentdate, countycode);
Log.Info("444" + com.CommandText);
com.ExecuteNonQuery();
return(1);
}
}
catch (Exception eex)
{
Log.Error("GetOrdinal operation:" + eex.Message);
}
finally
{
OracleConnectionconn.Close();//关闭打开的连接
}
return(0);
}
/// <summary>
/// Exec a storeprocedure with returl db table map value
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="StoreName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static IEnumerable <TEntity> ExecuteProcedure <TEntity>(string StoreName, params OracleParameter[] parameters) //where TEntity : class
{
Type myType = typeof(TEntity);
var prop = myType.GetProperties();
IEnumerable <TEntity> result = null;
using (var conn = new OracleConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
using (var command = conn.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = StoreName;
if (parameters != null)
{
foreach (OracleParameter param in parameters)
{
if (param.Value == null)
{
param.Value = System.DBNull.Value;
}
command.Parameters.Add(param);
}
}
var reader = command.ExecuteReader();
if (typeof(TEntity).IsClass)
//result = AutoMapper.Mapper.DynamicMap<IDataReader, IEnumerable<TEntity>>(reader);
{
List <TEntity> lst = new List <TEntity>();
while (reader.Read())
{
var entity = (TEntity)Activator.CreateInstance(myType);
foreach (var inf in prop)
{
object value = reader[inf.Name];
if (value == System.DBNull.Value)
{
continue;
}
inf.SetValue(entity, value, null);
}
lst.Add(entity);
}
if (lst.Count != 0)
{
result = lst;
}
}
else
{
List <TEntity> lst = new List <TEntity>();
while (reader.Read())
{
// get the results of each column
TEntity item = (TEntity)reader[0];
lst.Add(item);
}
if (lst.Count != 0)
{
result = lst;
}
}
}
conn.Close();
}
return(result);
}
private OracleCommand SetCommand()
{
setConnection();
return(conn.CreateCommand());
}
void ExecuteScripts(string scriptDirectory, string tablePrefix)
{
#region ExecuteScriptsSqlServer
using (var connection = new SqlConnection("ConnectionString"))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
foreach (var createScript in Directory.EnumerateFiles(
path: scriptDirectory,
searchPattern: "*_Create.sql",
searchOption: SearchOption.AllDirectories))
{
using (var command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = File.ReadAllText(createScript);
var tablePrefixParameter = command.CreateParameter();
tablePrefixParameter.ParameterName = "tablePrefix";
tablePrefixParameter.Value = tablePrefix;
command.Parameters.Add(tablePrefixParameter);
var schemaParameter = command.CreateParameter();
schemaParameter.ParameterName = "schema";
schemaParameter.Value = "dbo";
command.Parameters.Add(schemaParameter);
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
}
#endregion
#region ExecuteScriptsMySql
using (var connection = new MySqlConnection("ConnectionString"))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
foreach (var createScript in Directory.EnumerateFiles(
path: scriptDirectory,
searchPattern: "*_Create.sql",
searchOption: SearchOption.AllDirectories))
{
using (var command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = File.ReadAllText(createScript);
var parameter = command.CreateParameter();
parameter.ParameterName = "tablePrefix";
parameter.Value = tablePrefix;
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
}
#endregion
#region ExecuteScriptsPostgreSql
using (var connection = new NpgsqlConnection("ConnectionString"))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
foreach (var createScript in Directory.EnumerateFiles(
path: scriptDirectory,
searchPattern: "*_Create.sql",
searchOption: SearchOption.AllDirectories))
{
using (var command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = File.ReadAllText(createScript);
var parameter = command.CreateParameter();
parameter.ParameterName = "tablePrefix";
parameter.Value = tablePrefix;
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
}
#endregion
#region ExecuteScriptsOracle
using (var connection = new OracleConnection("ConnectionString"))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
foreach (var createScript in Directory.EnumerateFiles(
path: scriptDirectory,
searchPattern: "*_Create.sql",
searchOption: SearchOption.AllDirectories))
{
using (var command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = File.ReadAllText(createScript);
var parameter = command.CreateParameter();
parameter.ParameterName = "tablePrefix";
parameter.Value = tablePrefix;
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
}
#endregion
}
private void confirm_Click(object sender, RoutedEventArgs e)
{
if (fname.Text == "" || lname.Text == "" || cell.Text == "" || pno.Text == "" || address.Text == "" || start.Text == "" || end.Text == "" || rid.Text == "")
{
MessageBox.Show("Please fill all the details", "ERROR");
}
else
{
//check for gender
string gender;
if (gen_male.IsChecked == true)
{
gender = "m";
}
else
{
gender = "f";
}
string comd = "select * from reservation where room_id=" + rid.Text + "";
OracleDataAdapter da1 = new OracleDataAdapter(comd, con);
DataSet ds1 = new DataSet();
da1.Fill(ds1, "set");
DataTable dt1 = new DataTable();
dt1 = ds1.Tables[0];
if (dt1.Rows.Count != 0)
{
MessageBox.Show("Room already occupied", "ERROR");
fname.Text = "";
lname.Text = "";
cell.Text = "";
gen_female.IsChecked = false;
gen_male.IsChecked = false;
pno.Text = "";
address.Text = "";
start.Text = "";
end.Text = "";
rid.Text = "";
}
else
{
//update customer table
connection();
comd = "insert into customer values (" + cid.Text + ",'" + fname.Text + "','" + lname.Text + "','" + gender + "','" + address.Text + "'," + pno.Text + ",'" + mail.Text + "'," + cell.Text + ",'" + start.Text + "','" + end.Text + "','n' ," + rid.Text + ")";
OracleCommand cmd1 = con.CreateCommand();
cmd1.CommandText = comd;
cmd1.ExecuteNonQuery();
//find out no. of days
DateTime checkin = Convert.ToDateTime(start.Text);
DateTime checkout = Convert.ToDateTime(end.Text);
TimeSpan difference = checkout - checkin;
var days = difference.TotalDays;
int da = Convert.ToInt32(days);
//find price of allotted room
comd = "select price from room where room_id=" + rid.Text + "";
da1 = new OracleDataAdapter(comd, con);
ds1 = new DataSet();
da1.Fill(ds1, "set");
dt1 = new DataTable();
dt1 = ds1.Tables[0];
int price = Convert.ToInt32(dt1.Rows[0]["price"]);
price = price * da;
//update reservation table
comd = "insert into reservation values (" + cid.Text + "," + rid.Text + "," + price + ")";
cmd1 = con.CreateCommand();
cmd1.CommandText = comd;
cmd1.ExecuteNonQuery();
MessageBox.Show("Your room has Been Successfully Reserved \n room no :" + rid.Text, "Room Reserved");
//set each box to default null value
cid.Text = "";
fname.Text = "";
lname.Text = "";
cell.Text = "";
gen_female.IsChecked = false;
gen_male.IsChecked = false;
pno.Text = "";
address.Text = "";
start.Text = "";
end.Text = "";
rid.Text = "";
cancel_Click(sender, e);
}
}
}
/// <summary>
/// 执行查询操作
/// </summary>
/// <param name="p_strSql">查询sql语句</param>
/// <param name="p_dicDictionary">字典参数</param>
/// <param name="p_strTablename">产生的datatable名称</param>
/// <param name="cmd">cmd</param>
/// <returns>返回表</returns>
static public DataTable OraExecuteBySQL(string p_strSql, Dictionary<string, string> p_dicDictionary, string p_strTablename)
{
if ("" == m_strConnectionString)
{
MessageBox.Show("未设置数据库连接字符串！");
}
DataTable _dtTable = new DataTable(p_strTablename);
m_oraConn = new OracleConnection(m_strConnectionString);
m_oraCmd = m_oraConn.CreateCommand();
m_oraConn.Open();
ChangeSelectCommand(p_strSql, p_dicDictionary, ref m_oraCmd);
try
{
using (OracleDataAdapter adapter = new OracleDataAdapter(m_oraCmd))
{
adapter.Fill(_dtTable);
}
}
catch (Exception exp)
{
WriteLog(exp, p_strSql);
}
finally
{
m_oraConn.Dispose();
m_oraCmd.Dispose();
}
return _dtTable;
}
/// <summary>
/// Performs a patch check for the database.
/// </summary>
/// <param name="con">The oracle connection to use.</param>
/// <param name="codeSourceDirectory">The Code Source directory to start from.</param>
private static void PatchCheck(OracleConnection con, DirectoryInfo codeSourceDirectory)
{
Console.WriteLine(@"
#################################################
# Patches
# ");
OracleCommand patchCommand = con.CreateCommand();
patchCommand.CommandText = @"
SELECT pr.id FROM promotemgr.patch_runs pr
WHERE pr.patch_label = :patch_label
AND pr.patch_number = :patch_number
AND pr.ignore_flag IS NULL";
DirectoryInfo patchDirectory = new DirectoryInfo(Path.Combine(codeSourceDirectory.FullName, "DatabasePatches"));
foreach (DirectoryInfo subDir in patchDirectory.GetDirectories())
{
if (subDir.Name.Contains("NoDeploy"))
{
continue;
}
foreach (FileInfo patchFile in subDir.GetFiles("*.sql"))
{
Regex regex = new Regex(@"(\D+?)(\d+?) \(.+?\).sql");
Match match = regex.Match(patchFile.Name);
Debug.Assert(match.Groups.Count > 0, "The given file does not meet the patch naming guidelines");
string patchType = match.Groups[1].Value;
string patchNum = match.Groups[2].Value;
patchCommand.Parameters.Clear();
patchCommand.Parameters.Add("patch_label", patchType);
patchCommand.Parameters.Add("patch_number", patchNum);
OracleDataReader reader = patchCommand.ExecuteReader();
if (!reader.Read())
{
Console.WriteLine($"Patch {patchFile} will be run.");
}
reader.Close();
}
}
patchCommand.Dispose();
}
public Hashtable Attitude(string post_id, string actor_id, int attitude_type)
{
Hashtable ht = new Hashtable();
Random ran = new Random();
attitude_id = DateTime.Now.ToString("yyMMddHHmmss") + ran.Next(0, 999).ToString();
using (OracleConnection con = new OracleConnection(common.conString))
{
using (OracleCommand cmd = con.CreateCommand())
{
try
{
con.Open();
cmd.CommandText = " SELECT COUNT(*) from post_attitude where post_id = :post_id and actor_id = :actor_id and attitude_type = :attitude_type";
cmd.Parameters.Add(new OracleParameter("post_id", post_id));
cmd.Parameters.Add(new OracleParameter("actor_id", actor_id));
cmd.Parameters.Add(new OracleParameter("attitude_type", attitude_type));
OracleDataReader reader7 = cmd.ExecuteReader();
while (reader7.Read())
{
count = reader7.GetInt32(0);
}
ht.Add("count", count);
if (count == 0)
{
cmd.CommandText = "INSERT INTO POST_ATTITUDE(attitude_id, post_id, actor_id, attitude_type,time_1) " +
"values(:a1, :a2, :a3, :a4, " +
"to_date(to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'),'yyyy-mm-dd hh24:mi:ss'))";
cmd.Parameters.Clear();
cmd.Parameters.Add(new OracleParameter("a1", attitude_id));
cmd.Parameters.Add(new OracleParameter("a2", post_id));
cmd.Parameters.Add(new OracleParameter("a3", actor_id));
cmd.Parameters.Add(new OracleParameter("a4", attitude_type));
cmd.ExecuteNonQuery();
ht.Add("result", "success");
}
if (count != 0)
{
cmd.CommandText = "DELETE from post_attitude where post_id = :post_id AND actor_id = :actor_id AND attitude_type = :attitude_type";
cmd.Parameters.Clear();
cmd.Parameters.Add(new OracleParameter("post_id", post_id));
cmd.Parameters.Add(new OracleParameter("actor_id", actor_id));
cmd.Parameters.Add(new OracleParameter("attitude_type", attitude_type));
cmd.ExecuteNonQuery();
ht.Add("delete", "success");
}
}
catch (Exception ex)
{
ht.Add("result", "fail");
ht.Add("error2", ex.Message);
}
}
}
return(ht);
}
public void refresh_kuhni()
{
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
this.gridKuhni.Rows.Clear();
gridKuhni.RowsDefaultCellStyle.WrapMode = DataGridViewTriState.True;
DataGridViewTextBoxColumn col0 = new DataGridViewTextBoxColumn();
col0.HeaderText = "Дата отгрузки";
col0.Name = "prod_date_need";
col0.ReadOnly = true;
DataGridViewTextBoxColumn col1 = new DataGridViewTextBoxColumn();
col1.HeaderText = "№№";
col1.Name = "prod_id";
col1.ReadOnly = true;
DataGridViewTextBoxColumn col2 = new DataGridViewTextBoxColumn();
col2.HeaderText = "№ клиента";
col2.Name = "prod_name";
col2.ReadOnly = true;
DataGridViewTextBoxColumn col3 = new DataGridViewTextBoxColumn();
col3.HeaderText = "Клиент";
col3.Name = "client_name";
col3.ReadOnly = true;
DataGridViewTextBoxColumn col4 = new DataGridViewTextBoxColumn();
col4.HeaderText = "Описание";
col4.Name = "prod_descr";
col4.ReadOnly = true;
DataGridViewTextBoxColumn col5 = new DataGridViewTextBoxColumn();
col5.HeaderText = "Цена";
col5.Name = "prod_cena";
col5.ReadOnly = true;
col5.Visible = false;
if (vivaprocess.Global.prava == "руководство" || vivaprocess.Global.prava == "adminko")
{
col5.Visible = true;
}
DataGridViewTextBoxColumn col6 = new DataGridViewTextBoxColumn();
col6.HeaderText = "Дата поступления";
col6.Name = "prod_date_in";
col6.ReadOnly = true;
DataGridViewTextBoxColumn col7 = new DataGridViewTextBoxColumn();
col7.HeaderText = "В техотдел";
col7.Name = "date_tehnol";
col7.ReadOnly = true;
DataGridViewTextBoxColumn col8 = new DataGridViewTextBoxColumn();
col8.HeaderText = "В произв-во";
col8.Name = "proizvod_fakt";
col8.ReadOnly = true;
DataGridViewTextBoxColumn col9 = new DataGridViewTextBoxColumn();
col9.HeaderText = "Готов";
col9.Name = "sklad_gotov_fakt";
col9.ReadOnly = true;
//DataGridViewTextBoxColumn col10 = new DataGridViewTextBoxColumn();
//col10.HeaderText = "Примечание";
//col10.Name = "prod_descr";
//col10.ReadOnly = true;
if (fl_greed2 == 0)
{
this.gridKuhni.Columns.Add(col0);
this.gridKuhni.Columns.Add(col1);
this.gridKuhni.Columns.Add(col2);
this.gridKuhni.Columns.Add(col3);
this.gridKuhni.Columns.Add(col4);
this.gridKuhni.Columns.Add(col6);
this.gridKuhni.Columns.Add(col5);
this.gridKuhni.Columns.Add(col7);
this.gridKuhni.Columns.Add(col8);
this.gridKuhni.Columns.Add(col9);
//this.gridKuhni.Columns.Add(col10);
}
fl_greed2 = 1;
string constr = "User Id=kuhni_modul; Password=kuhni; Data Source=" + vivaprocess.Global.ora_serv;
OracleConnection con1 = new OracleConnection(constr);
try
{
con1.Open();
OracleCommand cmd = con1.CreateCommand();
cmd.CommandType = CommandType.Text;
if (chkInWork.Checked == true & chkGotov.Checked == false & chkOtgrugen.Checked == false)
{
cmd.CommandText = "select distinct nvl(prod_id,0) prod_id,nvl(prod_name,' ') prod_name,nvl(prod_descr,' ') prod_descr,nvl(client_name,' ') client_name,nvl(to_char(prod_date_in,'dd.mm.yyyy'),' ') prod_date_in,"
+ " nvl(to_char(prod_date_need,'dd.mm.yyyy'),' ') prod_date_need,nvl(prod_cena,0) prod_cena"
+ ",nvl(to_char(date_tehnol,'dd.mm.yyyy'),' ') date_tehnol,nvl(to_char(proizvod_fakt,'dd.mm.yyyy'),' ') proizvod_fakt,nvl(to_char(sklad_gotov_fakt,'dd.mm.yyyy'),' ') sklad_gotov_fakt"
+ " FROM disp_kuhni where mat_name='фасады' and prod_date_otgruzka is null and prod_id in (select t.prod_id from disp_kuhni t where (prod_date_in between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy')) and t.mat_descr<>'нет' and t.sklad_gotov_fakt is null)"
+ " and (prod_date_need between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy')) order by prod_date_need,prod_id,client_name,prod_name";
}
else if (chkInWork.Checked == true & chkGotov.Checked == true & chkOtgrugen.Checked == false)
{
cmd.CommandText = "select distinct nvl(prod_id,0) prod_id,nvl(prod_name,' ') prod_name,nvl(prod_descr,' ') prod_descr,nvl(client_name,' ') client_name,nvl(to_char(prod_date_in,'dd.mm.yyyy'),' ') prod_date_in,"
+ " nvl(to_char(prod_date_need,'dd.mm.yyyy'),' ') prod_date_need,nvl(prod_cena,0) prod_cena"
+ ",nvl(to_char(date_tehnol,'dd.mm.yyyy'),' ') date_tehnol,nvl(to_char(proizvod_fakt,'dd.mm.yyyy'),' ') proizvod_fakt,nvl(to_char(sklad_gotov_fakt,'dd.mm.yyyy'),' ') sklad_gotov_fakt"
+ " FROM disp_kuhni where mat_name='фасады' and prod_date_otgruzka is null"
+ " and (prod_date_need between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy')) order by prod_date_need,prod_id,client_name,prod_name";
}
else if (chkInWork.Checked == true & chkGotov.Checked == true & chkOtgrugen.Checked == true)
{
cmd.CommandText = "select distinct nvl(prod_id,0) prod_id,nvl(prod_name,' ') prod_name,nvl(prod_descr,' ') prod_descr,nvl(client_name,' ') client_name,nvl(to_char(prod_date_in,'dd.mm.yyyy'),' ') prod_date_in,"
+ " nvl(to_char(prod_date_need,'dd.mm.yyyy'),' ') prod_date_need,nvl(prod_cena,0) prod_cena"
+ ",nvl(to_char(date_tehnol,'dd.mm.yyyy'),' ') date_tehnol,nvl(to_char(proizvod_fakt,'dd.mm.yyyy'),' ') proizvod_fakt,nvl(to_char(sklad_gotov_fakt,'dd.mm.yyyy'),' ') sklad_gotov_fakt"
+ " FROM disp_kuhni where mat_name='фасады' and (prod_date_need between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy')) order by prod_date_need,prod_id,client_name,prod_name";
}
else if (chkInWork.Checked == true & chkGotov.Checked == false & chkOtgrugen.Checked == true)
{
cmd.CommandText = "select distinct nvl(prod_id,0) prod_id,nvl(prod_name,' ') prod_name,nvl(prod_descr,' ') prod_descr,nvl(client_name,' ') client_name,nvl(to_char(prod_date_in,'dd.mm.yyyy'),' ') prod_date_in,"
+ " nvl(to_char(prod_date_need,'dd.mm.yyyy'),' ') prod_date_need,nvl(prod_cena,0) prod_cena"
+ ",nvl(to_char(date_tehnol,'dd.mm.yyyy'),' ') date_tehnol,nvl(to_char(proizvod_fakt,'dd.mm.yyyy'),' ') proizvod_fakt,nvl(to_char(sklad_gotov_fakt,'dd.mm.yyyy'),' ') sklad_gotov_fakt"
+ " FROM disp_kuhni where mat_name='фасады' and prod_date_otgruzka is not null or prod_id in (select t.prod_id from disp_kuhni t where (prod_date_in between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy')) and t.mat_descr<>'нет' and t.sklad_gotov_fakt is null)"
+ " and (prod_date_need between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy')) order by prod_date_need,prod_id,client_name,prod_name";
}
else if (chkInWork.Checked == false & chkGotov.Checked == false & chkOtgrugen.Checked == true)
{
cmd.CommandText = "select distinct nvl(prod_id,0) prod_id,nvl(prod_name,' ') prod_name,nvl(prod_descr,' ') prod_descr,nvl(client_name,' ') client_name,nvl(to_char(prod_date_in,'dd.mm.yyyy'),' ') prod_date_in,"
+ " nvl(to_char(prod_date_need,'dd.mm.yyyy'),' ') prod_date_need,nvl(prod_cena,0) prod_cena"
+ ",nvl(to_char(date_tehnol,'dd.mm.yyyy'),' ') date_tehnol,nvl(to_char(proizvod_fakt,'dd.mm.yyyy'),' ') proizvod_fakt,nvl(to_char(sklad_gotov_fakt,'dd.mm.yyyy'),' ') sklad_gotov_fakt"
+ " FROM disp_kuhni where mat_name='фасады' and prod_date_otgruzka is not null"
+ " and (prod_date_need between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy')) order by prod_date_need,prod_id,client_name,prod_name";
}
else if (chkInWork.Checked == false & chkGotov.Checked == true & chkOtgrugen.Checked == false)
{
cmd.CommandText = "select distinct nvl(prod_id,0) prod_id,nvl(prod_name,' ') prod_name,nvl(prod_descr,' ') prod_descr,nvl(client_name,' ') client_name,nvl(to_char(prod_date_in,'dd.mm.yyyy'),' ') prod_date_in,"
+ " nvl(to_char(prod_date_need,'dd.mm.yyyy'),' ') prod_date_need,nvl(prod_cena,0) prod_cena"
+ ",nvl(to_char(date_tehnol,'dd.mm.yyyy'),' ') date_tehnol,nvl(to_char(proizvod_fakt,'dd.mm.yyyy'),' ') proizvod_fakt,nvl(to_char(sklad_gotov_fakt,'dd.mm.yyyy'),' ') sklad_gotov_fakt"
+ " FROM disp_kuhni where mat_name='фасады' and prod_date_otgruzka is null and prod_id not in (select t.prod_id from disp_kuhni t where (prod_date_in between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy')) and t.mat_descr<>'нет' and t.sklad_gotov_fakt is null)"
+ " and (prod_date_need between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy')) order by prod_date_need,prod_id,client_name,prod_name";
}
else if (chkInWork.Checked == false & chkGotov.Checked == true & chkOtgrugen.Checked == true)
{
cmd.CommandText = "select distinct nvl(prod_id,0) prod_id,nvl(prod_name,' ') prod_name,nvl(prod_descr,' ') prod_descr,nvl(client_name,' ') client_name,nvl(to_char(prod_date_in,'dd.mm.yyyy'),' ') prod_date_in,"
+ " nvl(to_char(prod_date_need,'dd.mm.yyyy'),' ') prod_date_need,nvl(prod_cena,0) prod_cena"
+ "nvl(to_char(date_tehnol,'dd.mm.yyyy'),' ') date_tehnol,nvl(to_char(proizvod_fakt,'dd.mm.yyyy'),' ') proizvod_fakt,nvl(to_char(sklad_gotov_fakt,'dd.mm.yyyy'),' ') sklad_gotov_fakt"
+ " FROM disp_kuhni where mat_name='фасады' and prod_date_otgruzka is not null or prod_id not in (select t.prod_id from disp_kuhni t where (prod_date_in between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy')) and t.mat_descr<>'нет' and t.sklad_gotov_fakt is null)"
+ " and (prod_date_need between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy')) order by prod_date_need,prod_id,client_name,prod_name";
}
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
DataGridViewCell cel0 = new DataGridViewTextBoxCell();
DataGridViewCell cel1 = new DataGridViewTextBoxCell();
DataGridViewCell cel2 = new DataGridViewTextBoxCell();
DataGridViewCell cel3 = new DataGridViewTextBoxCell();
DataGridViewCell cel4 = new DataGridViewTextBoxCell();
DataGridViewCell cel5 = new DataGridViewTextBoxCell();
DataGridViewCell cel6 = new DataGridViewTextBoxCell();
DataGridViewCell cel7 = new DataGridViewTextBoxCell();
DataGridViewCell cel8 = new DataGridViewTextBoxCell();
DataGridViewCell cel9 = new DataGridViewTextBoxCell();
//DataGridViewCell cel10 = new DataGridViewTextBoxCell();
DataGridViewRow row = new DataGridViewRow();
//cel0.Style.BackColor = Color.LightGray;
//string ssdata = (string)reader.GetString(5);
cel0.Value = DateTime.ParseExact((string)reader.GetString(5), "dd.M.yyyy", null); //(string)reader.GetString(5);
cel1.Value = (int)reader.GetDecimal(0);
cel2.Value = (string)reader.GetString(1);
cel3.Value = (string)reader.GetString(3);
cel4.Value = (string)reader.GetString(2);
if (reader.GetString(4).Length > 2)
{
cel6.Value = DateTime.ParseExact((string)reader.GetString(4), "dd.M.yyyy", null);
}
cel5.Value = (string)reader.GetDecimal(6).ToString();
if (reader.GetString(7).Length > 2)
{
cel7.Value = DateTime.ParseExact((string)reader.GetString(7), "dd.M.yyyy", null);
}
if (reader.GetString(8).Length > 2)
{
cel8.Value = DateTime.ParseExact((string)reader.GetString(8), "dd.M.yyyy", null);
}
if (reader.GetString(9).Length > 2)
{
cel9.Value = DateTime.ParseExact((string)reader.GetString(9), "dd.M.yyyy", null);
}
//cel10.Value = (string)reader.GetString(10);
row.Cells.AddRange(cel0, cel1, cel2, cel3, cel4, cel5, cel6, cel7, cel8, cel9);
this.gridKuhni.Rows.Add(row);
}
//gridKuhni.Columns[0].Frozen = true;
gridKuhni.Columns[0].DefaultCellStyle.Format = "d";
gridKuhni.Columns[6].DefaultCellStyle.Format = "d";
gridKuhni.Columns[7].DefaultCellStyle.Format = "d";
gridKuhni.Columns[8].DefaultCellStyle.Format = "d";
gridKuhni.Columns[9].DefaultCellStyle.Format = "d";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con1.Dispose();
}
this.Cursor = System.Windows.Forms.Cursors.Default;
}
public bool AddData(string query)
{
try
{
OracleCommand cmd = conn.CreateCommand();
OracleTransaction otn = conn.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
cmd.ExecuteNonQuery();
otn.Commit();
return(true);
}
catch { return(false); }
}
/// <summary>
/// 插入文书模板(t_patients_doc-文书;t_model_lable-标签模块;t_struct-结构化)
/// </summary>
/// <param name="PID">病人主ID(HIS)</param>
/// <param name="textKind">文书类型</param>
/// <param name="xmlDoc">文书模板</param>
/// <returns></returns>
public string InsertModel(string PID, int textKind_ID, string xmlDoc, int belongToSys_ID, int sickKind_ID, string textName)
{
//XmlElement xmlElement = xmlDoc.DocumentElement;
string strinsert = "";
OracleConnection cnn = new OracleConnection(ConnectionString);
cnn.Open();
OracleCommand command = cnn.CreateCommand();
OracleTransaction transaction = null;
try
{
transaction = cnn.BeginTransaction(IsolationLevel.ReadCommitted);
command.Transaction = transaction;
int tid = GenId("T_Patients_Doc", "TID");
strinsert = "insert into T_Patients_Doc values(" + tid.ToString() + ",'" + PID + "'," + textKind_ID + ",:Patients_Doc," + belongToSys_ID + "," + sickKind_ID + ",'" + textName + "')";
//OracleParameter[] xmlPars = new OracleParameter[1];
//xmlPars[0] = new OracleParameter();
//xmlPars[0].ParameterName = "Patients_Doc";
//xmlPars[0].Value = xmlDoc.OuterXml;
//xmlPars[0].OracleType = OracleType.Clob;
//xmlPars[0].Direction = ParameterDirection.Input;
OracleParameter xmlParDoc = new OracleParameter();
xmlParDoc.ParameterName = "Patients_Doc";
xmlParDoc.Value = xmlDoc;
xmlParDoc.OracleType = OracleType.Clob;
command.Parameters.Add(xmlParDoc);
command.CommandText = strinsert;
command.ExecuteNonQuery();
string msg = InsertLableModel(tid, xmlDoc); //===========插入标签模板与结构化
//message = ExecuteSQLWithParams(strinsert, xmlPars);//------------插入文书模板
if (msg == null)
{
transaction.Rollback();
return(null);
}
transaction.Commit();
//NClose();
return("成功");
}
catch (Exception ex)
{
transaction.Rollback();
return("数据库异常！----------------" + ex.ToString());
}
finally
{
cnn.Close();
}
}
public static void CLOBTest(OracleConnection connection)
{
Console.WriteLine(" BEGIN TRANSACTION ...");
OracleTransaction transaction = connection.BeginTransaction();
Console.WriteLine(" Drop table CLOBTEST ...");
try {
OracleCommand cmd2 = connection.CreateCommand();
cmd2.Transaction = transaction;
cmd2.CommandText = "DROP TABLE CLOBTEST";
cmd2.ExecuteNonQuery();
}
catch (OracleException oe1) {
// ignore if table already exists
}
Console.WriteLine(" CREATE TABLE ...");
OracleCommand create = connection.CreateCommand();
create.Transaction = transaction;
create.CommandText = "CREATE TABLE CLOBTEST (CLOB_COLUMN CLOB)";
create.ExecuteNonQuery();
Console.WriteLine(" INSERT RECORD ...");
OracleCommand insert = connection.CreateCommand();
insert.Transaction = transaction;
insert.CommandText = "INSERT INTO CLOBTEST VALUES (EMPTY_CLOB())";
insert.ExecuteNonQuery();
OracleCommand select = connection.CreateCommand();
select.Transaction = transaction;
select.CommandText = "SELECT CLOB_COLUMN FROM CLOBTEST FOR UPDATE";
Console.WriteLine(" SELECTING A CLOB (CHARACTER) VALUE FROM CLOBTEST");
OracleDataReader reader = select.ExecuteReader();
if (!reader.Read())
{
Console.WriteLine("ERROR: RECORD NOT FOUND");
}
Console.WriteLine(" TESTING OracleLob OBJECT ...");
OracleLob lob = reader.GetOracleLob(0);
Console.WriteLine(" LENGTH: {0}", lob.Length);
Console.WriteLine(" CHUNK SIZE: {0}", lob.ChunkSize);
UnicodeEncoding encoding = new UnicodeEncoding();
try {
// read file "cs-parser.cs" into the oracle clob
using (StreamReader sr = new StreamReader(infilename)) {
string sbuff = sr.ReadToEnd();
byte[] evalue = encoding.GetBytes(sbuff);
lob.Write(evalue, 0, evalue.Length);
}
}
catch (Exception e) {
Console.WriteLine("The file could not be read:");
Console.WriteLine(e.Message);
}
lob.Close();
Console.WriteLine(" CLOSING READER...");
reader.Close();
transaction.Commit();
}
public Menu BuscarRotinas(int matricula)
{
OracleConnection connection = DataBase.NovaConexao();
OracleCommand exec = connection.CreateCommand();
Menu listaRotinas = new Menu();
List <Modulo> ListaModulo = new List <Modulo>();
List <SubModulo> ListaSubModulo;
List <Rotina> ListaRotina;
StringBuilder queryModulo = new StringBuilder();;
StringBuilder querySubModulo;
StringBuilder queryRotina;
Int32 codmodulo;
Int32 codsubmodulo;
try
{
queryModulo.Append("select distinct rot.codmodulo, mod.modulo as descmodulo ");
queryModulo.Append(" from pcrotina rot inner join pcmodulo mod on (rot.codmodulo = mod.codmodulo)");
queryModulo.Append(" left outer join pccontro co on (rot.codigo = co.codrotina)");
queryModulo.Append(" where rot.rotinaweb = 'S'");
queryModulo.Append(" and rot.rotina = 'WEB'");
queryModulo.Append(" and co.acesso = 'S'");
queryModulo.Append($" and co.codusuario = {matricula}");
queryModulo.Append(" order by rot.codmodulo");
exec.CommandText = queryModulo.ToString();
OracleDataReader buscaModulos = exec.ExecuteReader();
while (buscaModulos.Read())
{
Modulo modulo = new Modulo
{
Codmodulo = buscaModulos.GetInt32(0),
Descmodulo = buscaModulos.GetString(1)
};
codmodulo = buscaModulos.GetInt32(0);
querySubModulo = new StringBuilder();
ListaSubModulo = new List <SubModulo>();
querySubModulo.Append("select distinct rot.codsubmodulo, sub.submodulo as descsubmodulo ");
querySubModulo.Append(" from pcrotina rot inner join pcmodulo mod on (rot.codmodulo = mod.codmodulo)");
querySubModulo.Append(" inner join pcsubmodulo sub on(mod.codmodulo = sub.codmodulo and rot.codsubmodulo = sub.codsubmodulo)");
querySubModulo.Append(" left outer join pccontro co on (rot.codigo = co.codrotina)");
querySubModulo.Append(" where rot.rotinaweb = 'S'");
querySubModulo.Append(" and co.acesso = 'S'");
querySubModulo.Append($" and rot.codmodulo = {codmodulo}");
querySubModulo.Append($" and co.codusuario = {matricula}");
querySubModulo.Append(" order by rot.codsubmodulo");
exec.CommandText = querySubModulo.ToString();
OracleDataReader buscaSubModulos = exec.ExecuteReader();
while (buscaSubModulos.Read())
{
SubModulo subModulo = new SubModulo
{
Codmodulo = buscaModulos.GetInt32(0),
Codsubmodulo = buscaSubModulos.GetInt32(0),
Descsubmodulo = buscaSubModulos.GetString(1)
};
codsubmodulo = buscaSubModulos.GetInt32(0);
queryRotina = new StringBuilder();
ListaRotina = new List <Rotina>();
queryRotina.Append("select rot.codigo as codrotina, rot.nomerotina as descrotina, nvl(rot.acao, '/') as rota, rot.codigo || ' - ' || rot.nomerotina as nomeFiltro");
queryRotina.Append(" from pcrotina rot inner join pcmodulo mod on (rot.codmodulo = mod.codmodulo)");
queryRotina.Append(" inner join pcsubmodulo sub on(mod.codmodulo = sub.codmodulo and rot.codsubmodulo = sub.codsubmodulo)");
queryRotina.Append(" left outer join pccontro co on (rot.codigo = co.codrotina)");
queryRotina.Append(" where rot.rotinaweb = 'S'");
queryRotina.Append(" and co.acesso = 'S'");
queryRotina.Append($" and rot.codmodulo = {codmodulo}");
queryRotina.Append($" and rot.codsubmodulo = {codsubmodulo}");
queryRotina.Append($" and co.codusuario = {matricula}");
queryRotina.Append(" order by rot.codigo");
exec.CommandText = queryRotina.ToString();
OracleDataReader buscaRotinas = exec.ExecuteReader();
while (buscaRotinas.Read())
{
Rotina rotina = new Rotina
{
Codsubmodulo = buscaSubModulos.GetInt32(0),
Codrotina = buscaRotinas.GetInt32(0),
Descrotina = buscaRotinas.GetString(1),
Acaorotina = buscaRotinas.GetString(2),
Nomerotina = buscaRotinas.GetString(3)
};
ListaRotina.Add(rotina);
}
subModulo.Rotinas = ListaRotina;
ListaSubModulo.Add(subModulo);
}
modulo.Submodulo = ListaSubModulo;
ListaModulo.Add(modulo);
}
listaRotinas.ListaMenu = ListaModulo;
return(listaRotinas);
}
catch (Exception ex)
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
throw new Exception(ex.ToString());
}
exec.Dispose();
connection.Dispose();
throw new Exception(ex.ToString());
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
exec.Dispose();
connection.Dispose();
}
}
//************************************************************
//** 조회 버튼 Click
//************************************************************
public void BtnSearch_Click()
{
if (authority.Read.Equals("0"))
{
Utility.MsgAuthorityViolation("조회");
return;
}
myViewModel?.Clear();
//--DB Handling(Start)-------------------------------------
try
{
con = Utility.SetOracleConnection();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = SQLStatement.SelectSQL;
cmd.Parameters.Add("papr_appno", OracleDbType.Varchar2).Value = searchText.Text + "%";
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
var data = new UcSubC19ViewModel
{
Papr_appno = dr.GetString(0),
Papr_content = dr.GetString(1),
Papr_num = Convert.ToInt32(dr.GetString(2)),
Papr_date = dr.IsDBNull(3) ? "" : Utility.FormatDate(dr.GetString(3)),
Key1 = dr.GetString(0),
DataStatus = ""
};
myViewModel.Add(data);
}
dr.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
finally
{
if (con != null)
{
con.Close();
}
}
//--DB Handling(End)-------------------------------------
SearchCount.Text = myViewModel.Count.ToString();
if (myViewModel.Count == 0)
{
UserMessage.Text = "조건을 만족하는 자료가 없습니다.";
Utility.SetFuncBtn(MainBtn, "1");
return;
}
{
UserMessage.Text = "자료가 정상적으로 조회 되었습니다.";
//**-개인정보 조회 Loging----------------------------
if (PersonalInfo.Equals("1"))
{
Utility.PersonalInfo_Logging(UserId, UserNm, MyIpAddress, ProgramName, "조회", myViewModel.Count);
}
Utility.SelectingFocusingGridControl(dataGrid, tableView, 0);
Utility.SetFuncBtn(MainBtn, "2");
}
}
private void SafeExecuteCommand(Action <OracleCommand> action)
{
using (var connection = new OracleConnection(_connectionString))
{
OracleTransaction transaction = null;
OracleCommand com;
try
{
connection.Open();
OracleSqlUtility.SetSqlUserInfo(connection, _userInfo);
transaction = connection.BeginTransaction();
com = connection.CreateCommand();
com.CommandTimeout = SqlUtility.SqlCommandTimeout;
com.Transaction = transaction;
}
catch (OracleException ex)
{
if (transaction != null)
{
transaction.Rollback();
}
var csb = new OracleConnectionStringBuilder(_connectionString);
string msg = string.Format(CultureInfo.InvariantCulture, "Could not connect to data source '{0}', userID '{1}'.", csb.DataSource, csb.UserID);
_logger.Error(msg);
_logger.Error(ex.ToString());
throw new FrameworkException(msg, ex);
}
try
{
var setNationalLanguage = OracleSqlUtility.SetNationalLanguageQuery();
if (!string.IsNullOrEmpty(setNationalLanguage))
{
_logger.Trace("Setting national language: {0}", SqlUtility.NationalLanguage);
com.CommandText = setNationalLanguage;
com.ExecuteNonQuery();
}
action(com);
transaction.Commit();
}
catch (OracleException ex)
{
if (com != null && !string.IsNullOrWhiteSpace(com.CommandText))
{
_logger.Error("Unable to execute SQL query:\r\n" + com.CommandText);
}
string msg = "OracleException has occurred:\r\n" + ReportSqlErrors(ex);
if (ex.Number == 911)
{
msg += "\r\nCheck that you are not using ';' at the end of the command's SQL query.";
}
_logger.Error(msg);
_logger.Error(ex.ToString());
throw new FrameworkException(msg, ex);
}
finally
{
TryRollback(transaction);
}
}
}
protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
{
if (e.CommandName == "updated")
{
DataSet ds1 = (DataSet)DataList1.DataSource;
String isbn = ds1.Tables[0].Rows[e.Item.ItemIndex]["isbn"].ToString();
String qty = ds1.Tables[0].Rows[e.Item.ItemIndex]["quantity"].ToString();
int tq = int.Parse(qty);
int i = 0;
try
{
com = oc.CreateCommand();
com.CommandText = "update_saling_date";
com.CommandType = CommandType.StoredProcedure;
OracleParameter p1 = new OracleParameter("sdate", OracleType.VarChar, 30);
com.Parameters.Add(p1);
p1.Direction = ParameterDirection.Input;
p1.Value = DateTime.Now.ToShortDateString();
OracleParameter p2 = new OracleParameter("bisbn", OracleType.VarChar, 30);
com.Parameters.Add(p2);
p2.Direction = ParameterDirection.Input;
p2.Value = isbn;
OracleParameter p3 = new OracleParameter("shid", OracleType.VarChar, 20);
com.Parameters.Add(p3);
p3.Direction = ParameterDirection.Input;
p3.Value = shopid;
OracleParameter p4 = new OracleParameter("mailadd", OracleType.VarChar, 30);
com.Parameters.Add(p4);
p4.Direction = ParameterDirection.Input;
p4.Value = mailadd;
OracleParameter p5 = new OracleParameter("cartiid", OracleType.VarChar, 30);
com.Parameters.Add(p5);
p5.Direction = ParameterDirection.Input;
p5.Value = cartiid;
OracleParameter p6 = new OracleParameter("qty", OracleType.Number);
com.Parameters.Add(p6);
p6.Direction = ParameterDirection.Input;
p6.Value = tq;
i = com.ExecuteNonQuery();
}
catch (Exception p) { }
if (i != 0)
{
Label20.Text = "Successfully delivered";
Label20.Visible = true;
}
/* else
* {
* Label20.Text = "Error";
* Label20.Visible = true;
* }*/
String qs = "select title,front_cover_image,books_from.isbn,edition,quantity,total from book,books_from,cart where books_from.email_add='" + mailadd + "' and shop_id='" + shopid + "' and books_from.cart_id='" + cartiid + "' and books_from.status='Not Delivered' and book.isbn=books_from.isbn and books_from.cart_id=cart.cart_id and books_from.email_add=cart.email_add";
try
{
com = oc.CreateCommand();
com.CommandText = qs;
com.CommandType = CommandType.Text;
adap = new OracleDataAdapter(com);
ds = new DataSet();
adap.Fill(ds);
DataList1.DataSource = ds;
DataList1.DataBind();
}
catch (Exception ppp) { }
}
}
public static void Main(string[] args)
{
string tainted_2 = null;
string tainted_3 = null;
Process process = new Process();
process.StartInfo.FileName = "/bin/bash";
process.StartInfo.Arguments = "-c 'cat /tmp/tainted.txt'";
process.StartInfo.UseShellExecute = false;
process.StartInfo.RedirectStandardOutput = true;
process.Start();
using (StreamReader reader = process.StandardOutput) {
tainted_2 = reader.ReadToEnd();
process.WaitForExit();
process.Close();
}
tainted_3 = tainted_2;
if ((Math.Sqrt(42) <= 42))
{
{}
}
else if (!(Math.Sqrt(42) <= 42))
{
StringBuilder escape = new StringBuilder();
for (int i = 0; i < tainted_2.Length; ++i)
{
char current = tainted_2[i];
switch (current)
{
case '\\':
escape.Append(@"\5c");
break;
case '*':
escape.Append(@"\2a");
break;
case '(':
escape.Append(@"\28");
break;
case ')':
escape.Append(@"\29");
break;
case '\u0000':
escape.Append(@"\00");
break;
case '/':
escape.Append(@"\2f");
break;
default:
escape.Append(current);
break;
}
}
tainted_3 = escape.ToString();
}
//flaw
string query = "SELECT * FROM Articles WHERE id=" + tainted_3;
string connectionString = "Data Source=localhost;User ID=oracle_user;Password=oracle_password";
OracleConnection dbConnection = null;
try{
dbConnection = new OracleConnection(connectionString);
dbConnection.Open();
OracleCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = query;
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.ToString());
}
dbConnection.Close();
}catch (Exception e) {
Console.WriteLine(e.ToString());
}
}
protected void Page_Load(object sender, EventArgs e)
{
Session["backto"] = "flasebackto";
if (!Convert.ToString(Session["ShopLoggedin"]).Equals("TrueShop"))
{
flag = 0;
Session["backto"] = "truebackto";
Session["takemeto"] = "BookshopsHome.aspx";
Response.Redirect("Signin.aspx");
}
String fname = Session["firstname"].ToString();
String lname = Session["lastname"].ToString();
cartiid = Session["carttid"].ToString();
String addrs = Session["addrs"].ToString();
mailadd = Session["mailaddress"].ToString();
order = Session["orderdate"].ToString();
delivery = Session["deliverydate"].ToString();
shopid = Session["shopuser"].ToString();
fname_label.Text = fname;
lname_label.Text = lname;
mail_label.Text = mailadd;
addrs_label.Text = addrs;
order_label.Text = order;
del_label.Text = delivery;
try
{
oc = new OracleConnection("Data Source=MYDB; User ID=scott; Password=tiger");
oc.Open();
com = oc.CreateCommand();
}
catch (Exception ee) { }
String q = "select crdt_co,credit_card_no from customer where email_add='" + mailadd + "'";
try
{
com.CommandText = q;
com.CommandType = CommandType.Text;
adap = new OracleDataAdapter(com);
db = new DataTable();
adap.Fill(db);
}
catch (Exception k) { }
foreach (DataRow row in this.db.Rows)
{
com_label.Text = row[0].ToString();
num_label.Text = row[1].ToString();
}
q = "select title,front_cover_image,books_from.isbn,edition,quantity,total from book,books_from,cart where books_from.email_add='" + mailadd + "' and shop_id='" + shopid + "' and books_from.cart_id='" + cartiid + "' and books_from.status='Not Delivered' and book.isbn=books_from.isbn and books_from.cart_id=cart.cart_id and books_from.email_add=cart.email_add";
try
{
com.CommandText = q;
com.CommandType = CommandType.Text;
adap = new OracleDataAdapter(com);
ds = new DataSet();
adap.Fill(ds);
DataList1.DataSource = ds;
DataList1.DataBind();
}
catch (Exception p) { Response.Write(p.StackTrace); }
flag = 1;
}
public void PopulateCotistaInfoSinacor(ref Dictionary <int, CotistaInfo> dicCotistas)
{
OracleConnection objORAConnection = new OracleConnection();
objORAConnection.ConnectionString = ConfigurationManager.ConnectionStrings["TRADE"].ConnectionString;
objORAConnection.Open();
string sqlQuery = "";
sqlQuery += " SELECT DISTINCT(TSCCLIGER.CD_CPFCGC) AS CD_CPFCGC, ";
sqlQuery += " TSCCLIGER.IN_SITUAC AS IN_SITUAC, ";
sqlQuery += " TSCCLIGER.NM_CLIENTE AS NM_CLIENTE, ";
sqlQuery += " TSCCLIGER.TP_PESSOA AS TP_PESSOA, ";
sqlQuery += " TSCCLIGER.TP_CLIENTE AS TP_CLIENTE, ";
sqlQuery += " TSCTIPCLI.DS_TIPO_CLIENTE AS DS_TIPO_CLIENTE ";
sqlQuery += " FROM TSCCLIGER, TSCTIPCLI ";
sqlQuery += " WHERE TSCCLIGER.TP_CLIENTE = TSCTIPCLI.TP_CLIENTE ";
Dictionary <string, CotistaInfo> dctPessoasSinacor = new Dictionary <string, CotistaInfo>();
using (OracleCommand objORACommand = objORAConnection.CreateCommand())
{
objORACommand.CommandText = sqlQuery;
OracleDataReader odr = objORACommand.ExecuteReader(CommandBehavior.CloseConnection);
if (odr.HasRows)
{
while (odr.Read())
{
try
{
CotistaInfo info = new CotistaInfo();
info.CpfCnpj = OracleConvert.GetNumber("CD_CPFCGC", odr).ToString();
info.NomeCotista = OracleConvert.GetString("NM_CLIENTE", odr);
info.TipoPessoaCotista = OracleConvert.GetString("TP_PESSOA", odr);
info.TipoClienteSinacor = OracleConvert.GetInt("TP_CLIENTE", odr);
if (!dctPessoasSinacor.ContainsKey(info.CpfCnpj))
{
dctPessoasSinacor.Add(info.CpfCnpj, info);
}
}
catch (Exception ex)
{
}
}
}
foreach (CotistaInfo cotista in dicCotistas.Values)
{
if (dctPessoasSinacor.ContainsKey(cotista.CpfCnpj))
{
cotista.TipoClienteSinacor = dctPessoasSinacor[cotista.CpfCnpj].TipoClienteSinacor;
cotista.TipoPessoaCotista = dctPessoasSinacor[cotista.CpfCnpj].TipoPessoaCotista;
}
else
{
cotista.TipoClienteSinacor = 99;
cotista.TipoPessoaCotista = "INCERTO";
}
}
}
}
protected void Submit_Click(object sender, EventArgs e)
{
if (Session["userName"] != null)
{
var countCon = new OracleConnection(connectionstring);
countCon.Open();
int recipe;
var cmd =
new OracleCommand("SELECT COUNT(*) FROM RECIPES WHERE RecipeName =: RecipeName", countCon);
cmd.Parameters.Add("RecipeName", Recipebox.Text);
recipe = Convert.ToInt32(cmd.ExecuteScalar());
countCon.Close();
//string user = Session["userName"].ToString();
Random priceRandom = new Random();
if (recipe == 0)
{
using (var conn = new OracleConnection(connectionstring))
{
conn.Open();
var command = conn.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "RECIPE_INSERT";
var RCategory = new OracleParameter()
{
ParameterName = "RCategory",
Direction = ParameterDirection.Input,
Value = CategoryList.Text,
OracleDbType = OracleDbType.Varchar2,
Size = 90
};
var cuisine = new OracleParameter()
{
ParameterName = "CUISINE",
Direction = ParameterDirection.Input,
Value = CuisineList.Text,
OracleDbType = OracleDbType.Varchar2,
Size = 90
};
var Description = new OracleParameter()
{
ParameterName = "DESCRIPTION",
Direction = ParameterDirection.Input,
Value = Steps.Text,
OracleDbType = OracleDbType.Varchar2,
Size = 90
};
var UserName = new OracleParameter()
{
ParameterName = "USERNAME",
Direction = ParameterDirection.Input,
Value = Session["userName"].ToString(),
OracleDbType = OracleDbType.Varchar2,
Size = 90
};
var EnrtyDAte = new OracleParameter()
{
ParameterName = "ENTRYDATE",
Direction = ParameterDirection.Input,
Value = DateTime.Today,
OracleDbType = OracleDbType.Date,
Size = 90
};
var RNAME = new OracleParameter()
{
ParameterName = "RNAME",
Direction = ParameterDirection.Input,
Value = Recipebox.Text,
OracleDbType = OracleDbType.Varchar2,
Size = 90
};
var Price = new OracleParameter()
{
ParameterName = "PRICE",
Direction = ParameterDirection.Input,
Value = priceRandom.Next(5, 25),
OracleDbType = OracleDbType.Varchar2,
Size = 90
};
command.Parameters.Add(RCategory);
command.Parameters.Add(cuisine);
command.Parameters.Add(Description);
command.Parameters.Add(UserName);
command.Parameters.Add(EnrtyDAte);
command.Parameters.Add(RNAME);
command.Parameters.Add(Price);
command.BindByName = true;
command.ExecuteNonQuery();
}
}
else
{
dynamic.InnerHtml = "Recipe already exits.";
}
}
else
{
Response.Redirect("Signin.aspx");
}
}
//************************************************************
//** 저장 버튼 Click (여러 건의 DATA 추가입력/수정 후 저장)
//************************************************************
public void BtnSave_Click()
{
if (MessageBox.Show("입력 및 수정중인 자료를 저장합니다.", "저장확인", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.No)
{
return;
}
if (!InputCheck())
{
return;
}
OracleTransaction tran = null;
try
{
con = Utility.SetOracleConnection();
tran = con.BeginTransaction(IsolationLevel.ReadCommitted);
OracleCommand cmd = con.CreateCommand();
cmd.Transaction = tran;
for (int i = 0; i <= myViewModel.Count - 1; i++)
{
UcSubC19ViewModel vm = (UcSubC19ViewModel)myViewModel.ElementAt(i);
if (vm.DataStatus.Equals(""))
{
continue;
}
if (vm.DataStatus.Equals("A"))
{
cmd.CommandText = SQLStatement.InsertSQL;
}
if (vm.DataStatus.Equals("U"))
{
cmd.CommandText = SQLStatement.UpdateSQL;
}
cmd.Parameters.Add("papr_appno", OracleDbType.Varchar2).Value = vm.Papr_appno;
cmd.Parameters.Add("papr_content", OracleDbType.Varchar2).Value = vm.Papr_content;
cmd.Parameters.Add("papr_num", OracleDbType.Int32).Value = vm.Papr_num;
cmd.Parameters.Add("papr_date", OracleDbType.Varchar2).Value = Utility.FormatDateR(vm.Papr_date);
if (vm.DataStatus.Equals("U"))
{
cmd.Parameters.Add("key1", OracleDbType.Varchar2).Value = vm.Key1;
}
cmd.ExecuteNonQuery();
cmd.Parameters.Clear(); //*----반드시 포함
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
MessageBox.Show(ex.Message);
return;
}
finally
{
if (con != null)
{
con.Close();
}
}
//**정상 저장 후 초기화*******************************************************
for (int i = 0; i <= myViewModel.Count - 1; i++)
{
UcSubC19ViewModel vm = (UcSubC19ViewModel)myViewModel.ElementAt(i);
if (vm.DataStatus.Equals(""))
{
continue;
}
vm.Key1 = vm.Papr_appno;
vm.DataStatus = "";
}
dataGrid.RefreshData();
UserMessage.Text = "자료가 정상적으로 저장 되었습니다.";
Utility.SetFuncBtn(MainBtn, "2");
}
/// <summary>
/// This method is responsible for launching threads of the same process
/// Business Logic to launch a reporting application that servers reports for the users that demands reports from the web interface
/// Oracle DB and a view have been used to fetch the data from Oracle if there are some report requests
/// </summary>
public void LaunchThreads()
{
try
{
OracleConnection Conn = new OracleConnection(ConnString);
OracleDataReader odr;
int ProcessCounter;
OracleCommand cmd = Conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT COUNT(*) AS CPT FROM VUserReportRequests";
int Counter = 0;
//Kind of infinitve loop until service is running
while (Thread.CurrentThread.IsAlive)
{
if (Counter == 0)
{
KillIdleProcesses();
}
try
{
if (!(Conn.State == ConnectionState.Open))
{
Conn.Open();
}
ProcessCounter = 0;
odr = cmd.ExecuteReader();
if (odr.Read())
{
ProcessCounter = Convert.ToInt32(odr.GetDecimal(0));
}
odr.Close();
if (ProcessCounter > 0)
{
Process[] processes = Process.GetProcessesByName("FILLREPORT");
// To launch maximum 4 process at a time
if (processes.Count() < MaxProcess)
{
Process p = Process.Start(@"D:\\BusinessReportsApp\\CreateReport.exe");
Conn.Close();
}
}
}
catch (Exception ex)
{
Trace.WriteLog("Exception Details : " + DateTime.Now.ToString() + ex.ToString());
}
if (Counter == 180)
{
Counter = 0;
}
else
{
Counter++;
}
}
Conn.Close();
}
catch (Exception ex)
{
Trace.WriteLog("Exception Details : " + DateTime.Now.ToString() + ex.ToString());
}
}
protected override bool ExportData()
{
bool success = false;
string year = IndihiangHelper.GetYearDataIndihiangFile(_databaseFile);
string conString = string.Format("server={0};database={1};uid={2};pwd={3}", _svr, _db, _uid, _pwd);
if (!string.IsNullOrEmpty(year))
{
SQLiteConnection con = null;
SQLiteDataReader rd = null;
OracleConnection con2 = null;
OracleTransaction trans = null;
con2 = new OracleConnection(conString);
con2.Open();
trans = con2.BeginTransaction();
using (OracleCommand cmd2 = con2.CreateCommand())
{
StringBuilder builder = new StringBuilder();
builder.Append("insert into [indihiang_data](fullfilename,a_day,a_month,a_year,server_ip,");
builder.Append("server_port,client_ip,page_access,query_page_access,access_username,");
builder.Append("user_agent,protocol_status,bytes_sent,bytes_received,referer,ip_country,time_taken,referer_class)");
builder.Append(" values(@par1,@par2,@par3,@par4,@par5,@par6,@par7,@par8,@par9,@par10,@par11,@par12,@par13,@par14,@par15,@par16,@par17,@par18)");
cmd2.CommandText = builder.ToString();
cmd2.Transaction = trans;
OracleParameter par1 = new OracleParameter("@par1", DbType.String);
cmd2.Parameters.Add(par1);
OracleParameter par2 = new OracleParameter("@par2", DbType.Int32);
cmd2.Parameters.Add(par2);
OracleParameter par3 = new OracleParameter("@par3", DbType.Int32);
cmd2.Parameters.Add(par3);
OracleParameter par4 = new OracleParameter("@par4", DbType.Int32);
cmd2.Parameters.Add(par4);
OracleParameter par5 = new OracleParameter("@par5", DbType.String);
cmd2.Parameters.Add(par5);
OracleParameter par6 = new OracleParameter("@par6", DbType.String);
cmd2.Parameters.Add(par6);
OracleParameter par7 = new OracleParameter("@par7", DbType.String);
cmd2.Parameters.Add(par7);
OracleParameter par8 = new OracleParameter("@par8", DbType.String);
cmd2.Parameters.Add(par8);
OracleParameter par9 = new OracleParameter("@par9", DbType.String);
cmd2.Parameters.Add(par9);
OracleParameter par10 = new OracleParameter("@par10", DbType.String);
cmd2.Parameters.Add(par10);
OracleParameter par11 = new OracleParameter("@par11", DbType.String);
cmd2.Parameters.Add(par11);
OracleParameter par12 = new OracleParameter("@par12", DbType.String);
cmd2.Parameters.Add(par12);
OracleParameter par13 = new OracleParameter("@par13", DbType.String);
cmd2.Parameters.Add(par13);
OracleParameter par14 = new OracleParameter("@par14", DbType.String);
cmd2.Parameters.Add(par14);
OracleParameter par15 = new OracleParameter("@par15", DbType.String);
cmd2.Parameters.Add(par15);
OracleParameter par16 = new OracleParameter("@par16", DbType.String);
cmd2.Parameters.Add(par16);
OracleParameter par17 = new OracleParameter("@par17", DbType.String);
cmd2.Parameters.Add(par17);
OracleParameter par18 = new OracleParameter("@par18", DbType.String);
cmd2.Parameters.Add(par18);
try
{
con = new SQLiteConnection(string.Format("Data Source={0}", _databaseFile));
con.Open();
string query = "select * from log_data";
SQLiteCommand cmd = new SQLiteCommand(query, con);
rd = cmd.ExecuteReader();
while (rd.Read())
{
par1.Value = rd["fullfilename"];
par2.Value = rd["a_day"];
par3.Value = rd["a_month"];
par4.Value = Convert.ToInt32(year);
par5.Value = rd["server_ip"];
par6.Value = rd["server_port"];
par7.Value = rd["client_ip"];
par8.Value = rd["page_access"];
par9.Value = rd["query_page_access"];
par10.Value = rd["access_username"];
par11.Value = rd["user_agent"];
par12.Value = rd["protocol_status"];
par13.Value = rd["bytes_sent"];
par14.Value = rd["bytes_received"];
par15.Value = rd["referer"];
par16.Value = rd["ip_country"];
par17.Value = rd["time_taken"];
par18.Value = rd["referer_class"];
cmd2.ExecuteNonQuery();
}
trans.Commit();
con2.Close();
success = true;
}
catch (Exception err)
{
System.Diagnostics.Debug.WriteLine(err.StackTrace);
}
finally
{
if (rd != null)
{
rd.Close();
}
if (con != null)
{
con.Close();
}
}
}
}
return(success);
}
/// <summary>
/// 批量导入到数据库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void toolStripButton2_Click(object sender, EventArgs e)
{
//bool ret = false;
DataSet ds;
DataSet sheetds = new DataSet();
int dsLength;
string formtext = this.Text;
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "Excel文件";
ofd.FileName = "";
ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments); //为了获取特定的系统文件夹，可以使用System.Environment类的静态方法GetFolderPath()。该方法接受一个Environment.SpecialFolder枚举，其中可以定义要返回路径的哪个系统目录
ofd.Filter = "Excel文件(*.xls)|*.xls";
ofd.ValidateNames = true; //文件有效性验证ValidateNames，验证用户输入是否是一个有效的Windows文件名
ofd.CheckFileExists = true; //验证路径有效性
ofd.CheckPathExists = true; //验证文件有效性
if (ofd.ShowDialog() == DialogResult.OK)
{
string sql = "SELECT EXCELTABLE FROM DATATABLE_TAB WHERE DESCRIPTION = '" + formtext + "'";
User.DataBaseConnect(sql, sheetds);
string excelsheet = sheetds.Tables[0].Rows[0][0].ToString();
ds = ImportExcel(ofd.FileName, excelsheet);//获得Excel
if (ds == null)
{
return;
}
}
else
{
return;
}
int odr = 0;
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//获得conn连接
conn.Open();
OracleTransaction trans = conn.BeginTransaction();
OracleCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
try
{
switch (formtext)
{
case "弯头列表":
cmd.CommandText = "INSERT INTO SP_BEND (M_NO, DN, ONE_DXW, ONEHALF_DXW, ONE_JCDXW, ONEHALF_JCDXW, PROJECT_ID) VALUES (:xh,:hpzl,:hphm,:bz,:larq,:fdjh,:clpp) ";
cmd.Parameters.Add("xh", OracleType.Number);
cmd.Parameters.Add("hpzl", OracleType.VarChar);
cmd.Parameters.Add("hphm", OracleType.Number);
cmd.Parameters.Add("bz", OracleType.Number);
cmd.Parameters.Add("larq", OracleType.Number);
cmd.Parameters.Add("fdjh", OracleType.Number);
cmd.Parameters.Add("clpp", OracleType.VarChar);
dsLength = ds.Tables[0].Rows.Count;
for (int i = 1; i < dsLength; i++)
{
cmd.Parameters["xh"].Value = ds.Tables[0].Rows[i][0];
cmd.Parameters["hpzl"].Value = ds.Tables[0].Rows[i][1];
cmd.Parameters["hphm"].Value = ds.Tables[0].Rows[i][2];
cmd.Parameters["bz"].Value = ds.Tables[0].Rows[i][3];
cmd.Parameters["larq"].Value = ds.Tables[0].Rows[i][4];
cmd.Parameters["fdjh"].Value = ds.Tables[0].Rows[i][5];
cmd.Parameters["clpp"].Value = ds.Tables[0].Rows[i][6];
odr = cmd.ExecuteNonQuery(); //提交
}
break;
case "舱室列表":
cmd.CommandText = "INSERT INTO SP_CABIN (PROJECT_ID, EN_CABIN, CH_CABIN) VALUES(:xh,:hpzl,:hphm) ";
cmd.Parameters.Add("xh", OracleType.VarChar);
cmd.Parameters.Add("hpzl", OracleType.VarChar);
cmd.Parameters.Add("hphm", OracleType.VarChar);
dsLength = ds.Tables[0].Rows.Count;
for (int i = 1; i < dsLength; i++)
{
cmd.Parameters["xh"].Value = ds.Tables[0].Rows[i][0];
cmd.Parameters["hpzl"].Value = ds.Tables[0].Rows[i][1];
cmd.Parameters["hphm"].Value = ds.Tables[0].Rows[i][2];
odr = cmd.ExecuteNonQuery(); //提交
}
break;
case "连接件列表":
cmd.CommandText = "INSERT INTO SP_CONNECTOR (NAME, PARTCODE, OUTDIAMETER, NUTWEIGHT, BOLTWEIGHT,PROJECT_ID) VALUES(:xh,:hpzl,:cjh,:jdcsyr,:cllx,:csys) ";
cmd.Parameters.Add("xh", OracleType.VarChar);
cmd.Parameters.Add("hpzl", OracleType.VarChar);
cmd.Parameters.Add("cjh", OracleType.Number);
cmd.Parameters.Add("jdcsyr", OracleType.Number);
cmd.Parameters.Add("cllx", OracleType.Number);
cmd.Parameters.Add("csys", OracleType.VarChar);
dsLength = ds.Tables[0].Rows.Count; //获得Excel中数据长度
for (int i = 1; i < dsLength; i++)
{
cmd.Parameters["xh"].Value = ds.Tables[0].Rows[i][0];
cmd.Parameters["hpzl"].Value = ds.Tables[0].Rows[i][1];
cmd.Parameters["cjh"].Value = ds.Tables[0].Rows[i][2];
cmd.Parameters["jdcsyr"].Value = ds.Tables[0].Rows[i][3];
cmd.Parameters["cllx"].Value = ds.Tables[0].Rows[i][4];
cmd.Parameters["csys"].Value = ds.Tables[0].Rows[i][5];
odr = cmd.ExecuteNonQuery();
}
break;
case "弯头材料对照列表":
cmd.CommandText = "INSERT INTO SP_ELBOWMATERIAL (PROJECT_ID, PIPEMATERIAL, EMATERIAL, FLAGE) VALUES(:xh,:hpzl,:hphm,:bz) ";
cmd.Parameters.Add("xh", OracleType.VarChar);
cmd.Parameters.Add("hpzl", OracleType.VarChar);
cmd.Parameters.Add("hphm", OracleType.VarChar);
cmd.Parameters.Add("bz", OracleType.VarChar);
dsLength = ds.Tables[0].Rows.Count;
for (int i = 1; i < dsLength; i++)
{
cmd.Parameters["xh"].Value = ds.Tables[0].Rows[i][0];
cmd.Parameters["hpzl"].Value = ds.Tables[0].Rows[i][1];
cmd.Parameters["hphm"].Value = ds.Tables[0].Rows[i][2];
cmd.Parameters["bz"].Value = ds.Tables[0].Rows[i][3];
odr = cmd.ExecuteNonQuery(); //提交
}
break;
case "弯模列表":
cmd.CommandText = "INSERT INTO SP_PSTAD (OUTSIDEDIAMETER, WAMO, QIANJA, HOUJA, PROJECT_ID, SECMACHINE) VALUES(:xh,:hpzl,:hphm,:bz,:larq,:fdjh) ";
cmd.Parameters.Add("xh", OracleType.Number);
cmd.Parameters.Add("hpzl", OracleType.VarChar);
cmd.Parameters.Add("hphm", OracleType.Number);
cmd.Parameters.Add("bz", OracleType.Number);
cmd.Parameters.Add("larq", OracleType.VarChar);
cmd.Parameters.Add("fdjh", OracleType.VarChar);
dsLength = ds.Tables[0].Rows.Count; //获得Excel中数据长度
for (int i = 1; i < dsLength; i++)
{
cmd.Parameters["xh"].Value = ds.Tables[0].Rows[i][0];
cmd.Parameters["hpzl"].Value = ds.Tables[0].Rows[i][1];
cmd.Parameters["hphm"].Value = ds.Tables[0].Rows[i][2];
cmd.Parameters["bz"].Value = ds.Tables[0].Rows[i][3];
cmd.Parameters["larq"].Value = ds.Tables[0].Rows[i][4];
cmd.Parameters["fdjh"].Value = ds.Tables[0].Rows[i][5];
odr = cmd.ExecuteNonQuery(); //提交
}
break;
case "承接弯头列表":
cmd.CommandText = "INSERT INTO SP_SOCKETELBOW (PROJECT_ID, DN, ELBOWONE, ELBOWTWO) VALUES(:xh,:hpzl,:hphm,:bz) ";
cmd.Parameters.Add("xh", OracleType.VarChar);
cmd.Parameters.Add("hpzl", OracleType.VarChar);
cmd.Parameters.Add("hphm", OracleType.Number);
cmd.Parameters.Add("bz", OracleType.Number);
dsLength = ds.Tables[0].Rows.Count;
for (int i = 1; i < dsLength; i++)
{
cmd.Parameters["xh"].Value = ds.Tables[0].Rows[i][0];
cmd.Parameters["hpzl"].Value = ds.Tables[0].Rows[i][1];
cmd.Parameters["hphm"].Value = ds.Tables[0].Rows[i][2];
cmd.Parameters["bz"].Value = ds.Tables[0].Rows[i][3];
odr = cmd.ExecuteNonQuery(); //提交
}
break;
case "表面处理列表":
cmd.CommandText = "INSERT INTO SP_SURFACE (CODE, DESCRIPTION, PROJECT_ID) VALUES(:xh,:hpzl,:hphm) ";
cmd.Parameters.Add("xh", OracleType.VarChar);
cmd.Parameters.Add("hpzl", OracleType.VarChar);
cmd.Parameters.Add("hphm", OracleType.VarChar);
dsLength = ds.Tables[0].Rows.Count;
for (int i = 1; i < dsLength; i++)
{
cmd.Parameters["xh"].Value = ds.Tables[0].Rows[i][0];
cmd.Parameters["hpzl"].Value = ds.Tables[0].Rows[i][1];
cmd.Parameters["hphm"].Value = ds.Tables[0].Rows[i][2];
odr = cmd.ExecuteNonQuery(); //提交
}
break;
case "系统列表":
cmd.CommandText = "INSERT INTO SP_SYSTEM (PROJECT_ID, SYSID, SYSCODE, SYSNAME, GASKET, BENDMACHINE) VALUES(:xh,:hpzl,:hphm,:bz,:larq,:fdjh) ";
cmd.Parameters.Add("xh", OracleType.VarChar);
cmd.Parameters.Add("hpzl", OracleType.VarChar);
cmd.Parameters.Add("hphm", OracleType.VarChar);
cmd.Parameters.Add("bz", OracleType.VarChar);
cmd.Parameters.Add("larq", OracleType.VarChar);
cmd.Parameters.Add("fdjh", OracleType.VarChar);
dsLength = ds.Tables[0].Rows.Count;
for (int i = 1; i < dsLength; i++)
{
cmd.Parameters["xh"].Value = ds.Tables[0].Rows[i][0];
cmd.Parameters["hpzl"].Value = ds.Tables[0].Rows[i][1];
cmd.Parameters["hphm"].Value = ds.Tables[0].Rows[i][2];
cmd.Parameters["bz"].Value = ds.Tables[0].Rows[i][3];
cmd.Parameters["larq"].Value = ds.Tables[0].Rows[i][4];
cmd.Parameters["fdjh"].Value = ds.Tables[0].Rows[i][5];
odr = cmd.ExecuteNonQuery(); //提交
}
break;
case "审核人列表":
cmd.CommandText = "INSERT INTO PROJECTAPPROVE (PROJECTID, ASSESOR, INDEX_ID) VALUES(:xh,:hpzl,:hphm) ";
cmd.Parameters.Add("xh", OracleType.VarChar);
cmd.Parameters.Add("hpzl", OracleType.VarChar);
cmd.Parameters.Add("hphm", OracleType.Number);
dsLength = ds.Tables[0].Rows.Count;
for (int i = 1; i < dsLength; i++)
{
cmd.Parameters["xh"].Value = ds.Tables[0].Rows[i][0];
cmd.Parameters["hpzl"].Value = ds.Tables[0].Rows[i][1];
cmd.Parameters["hphm"].Value = ds.Tables[0].Rows[i][2];
odr = cmd.ExecuteNonQuery(); //提交
}
break;
case "金属管线下料时间定额列表":
cmd.CommandText = "INSERT INTO BAITINGNORM_METALPIPE_TAB (CODE, NORM, PIPEMACHINING,EQUIPMENTOPERATION,UNPRODUCTIVETIME) VALUES(:xh,:hpzl,:hphm:bz,:larq) ";
cmd.Parameters.Add("xh", OracleType.VarChar);
cmd.Parameters.Add("hpzl", OracleType.Number);
cmd.Parameters.Add("hphm", OracleType.Number);
cmd.Parameters.Add("bz", OracleType.Number);
cmd.Parameters.Add("larq", OracleType.Number);
dsLength = ds.Tables[0].Rows.Count;
for (int i = 1; i < dsLength; i++)
{
cmd.Parameters["xh"].Value = ds.Tables[0].Rows[i][0];
cmd.Parameters["hpzl"].Value = ds.Tables[0].Rows[i][1];
cmd.Parameters["hphm"].Value = ds.Tables[0].Rows[i][2];
cmd.Parameters["bz"].Value = ds.Tables[0].Rows[i][3];
cmd.Parameters["larq"].Value = ds.Tables[0].Rows[i][4];
odr = cmd.ExecuteNonQuery(); //提交
}
break;
case "坡口加工时间定额列表":
cmd.CommandText = "INSERT INTO BEVEL_HOUR_NORM_TAB (CODE, NORM, EQUIPMENTOPERATION,UNPRODUCTIVETIME) VALUES(:xh,:hpzl,:hphm:bz) ";
cmd.Parameters.Add("xh", OracleType.VarChar);
cmd.Parameters.Add("hpzl", OracleType.Number);
cmd.Parameters.Add("hphm", OracleType.Number);
cmd.Parameters.Add("bz", OracleType.Number);
dsLength = ds.Tables[0].Rows.Count;
for (int i = 1; i < dsLength; i++)
{
cmd.Parameters["xh"].Value = ds.Tables[0].Rows[i][0];
cmd.Parameters["hpzl"].Value = ds.Tables[0].Rows[i][1];
cmd.Parameters["hphm"].Value = ds.Tables[0].Rows[i][2];
cmd.Parameters["bz"].Value = ds.Tables[0].Rows[i][3];
odr = cmd.ExecuteNonQuery(); //提交
}
break;
case "弯管时间定额列表":
cmd.CommandText = "INSERT INTO ELBOW_HOUR_NORM_TAB (CODE, NORM, PIPEMACHINING,EQUIPMENTOPERATION,UNPRODUCTIVETIME) VALUES(:xh,:hpzl,:hphm:bz,:larq) ";
cmd.Parameters.Add("xh", OracleType.VarChar);
cmd.Parameters.Add("hpzl", OracleType.Number);
cmd.Parameters.Add("hphm", OracleType.Number);
cmd.Parameters.Add("bz", OracleType.Number);
cmd.Parameters.Add("larq", OracleType.Number);
dsLength = ds.Tables[0].Rows.Count;
for (int i = 1; i < dsLength; i++)
{
cmd.Parameters["xh"].Value = ds.Tables[0].Rows[i][0];
cmd.Parameters["hpzl"].Value = ds.Tables[0].Rows[i][1];
cmd.Parameters["hphm"].Value = ds.Tables[0].Rows[i][2];
cmd.Parameters["bz"].Value = ds.Tables[0].Rows[i][3];
cmd.Parameters["larq"].Value = ds.Tables[0].Rows[i][4];
odr = cmd.ExecuteNonQuery(); //提交
}
break;
case "校管时间定额列表":
cmd.CommandText = "INSERT INTO PIPECHECKING_HOUR_NORM_TAB (CODE, NORM, PIPEMACHINING,EQUIPMENTOPERATION,WELD_WORKING,UNPRODUCTIVETIME) VALUES(:xh,:hpzl,:hphm:bz,:larq,:fdjh) ";
cmd.Parameters.Add("xh", OracleType.VarChar);
cmd.Parameters.Add("hpzl", OracleType.Number);
cmd.Parameters.Add("hphm", OracleType.Number);
cmd.Parameters.Add("bz", OracleType.Number);
cmd.Parameters.Add("larq", OracleType.Number);
cmd.Parameters.Add("fdjh", OracleType.Number);
dsLength = ds.Tables[0].Rows.Count;
for (int i = 1; i < dsLength; i++)
{
cmd.Parameters["xh"].Value = ds.Tables[0].Rows[i][0];
cmd.Parameters["hpzl"].Value = ds.Tables[0].Rows[i][1];
cmd.Parameters["hphm"].Value = ds.Tables[0].Rows[i][2];
cmd.Parameters["bz"].Value = ds.Tables[0].Rows[i][3];
cmd.Parameters["larq"].Value = ds.Tables[0].Rows[i][4];
cmd.Parameters["fdjh"].Value = ds.Tables[0].Rows[i][5];
odr = cmd.ExecuteNonQuery(); //提交
}
break;
default:
break;
}
trans.Commit();
MessageBox.Show("导入成功");
}
catch (OracleException ee)
{
trans.Rollback();
MessageBox.Show(ee.Message.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
conn.Close();
}
}
public IDbCommand CreateCommand()
{
return(sql?.CreateCommand());
}
public static void Main(string[] args)
{
string tainted_2 = null;
string tainted_3 = null;
Process process = new Process();
process.StartInfo.FileName = "/bin/bash";
process.StartInfo.Arguments = "-c 'cat /tmp/tainted.txt'";
process.StartInfo.UseShellExecute = false;
process.StartInfo.RedirectStandardOutput = true;
process.Start();
using (StreamReader reader = process.StandardOutput) {
tainted_2 = reader.ReadToEnd();
process.WaitForExit();
process.Close();
}
tainted_3 = tainted_2;
if ((Math.Sqrt(42) <= 42))
{
string pattern = @"/^[0-9]*$/";
Regex r = new Regex(pattern);
Match m = r.Match(tainted_2);
if (!m.Success)
{
tainted_3 = "";
}
else
{
tainted_3 = tainted_2;
}
}
else if (!(Math.Sqrt(42) <= 42))
{
{}
}
string query = "SELECT * FROM '" + tainted_3 + "'";
string connectionString = "Data Source=localhost;User ID=oracle_user;Password=oracle_password";
OracleConnection dbConnection = null;
try{
dbConnection = new OracleConnection(connectionString);
dbConnection.Open();
OracleCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = query;
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.ToString());
}
dbConnection.Close();
}catch (Exception e) {
Console.WriteLine(e.ToString());
}
}
private void btnAddQueue_Click(object sender, EventArgs e)
{
counteradd++;
OracleConnection con = new OracleConnection(constr);
con.Open();
string id = this.txtboxItemId.Text.ToString();
txtBoxAddTOueueQuantity.Text = txtboxQuantity.Text;
// string i = txtboxQuantity.Text.ToString();
// int numVal = Int32.Parse(i);
// if (numVal > 0)
// {
int stock = 0;
OracleCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from item where PRODUCT_NAME ='" + txtProName.Text + "'";
cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
OracleDataAdapter oda = new OracleDataAdapter(cmd);
oda.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
stock = Convert.ToInt32(dr["quantity"].ToString());
}
if (Convert.ToInt32(txtboxQuantity.Text) > stock)
{
MessageBox.Show("out of stock");
}
else
{
string qry = "update item set quantity = (quantity - '" + Int32.Parse(txtboxQuantity.Text.ToString()) + "') where item_id='" + id + "'";
OracleDataAdapter oda1 = new OracleDataAdapter(qry, con);
OracleCommand cmd1 = new OracleCommand(qry, con);
cmd1.ExecuteNonQuery();
total_price = total_price + (Convert.ToInt32(txtPrice.Text) * Convert.ToInt32(txtboxQuantity.Text));
lblFinalTotal.Text = total_price.ToString();
dgvTemp.Rows.Add(txtProName.Text, txtboxQuantity.Text, txtTotal.Text, txtPrice.Text, cmbLocation.Text, cmbPhone.Text, lblFinalTotal.Text, dtpHide.Text, txtitemIdTemp.Text);
loadGridview();
refresh();
}
// }
// else
// {
// MessageBox.Show("Out of stock", "Try later");
//}
}
/// <summary>
/// Compares the packages on the database with those in the CodeSource folder.
/// </summary>
/// <param name="con">The Oracle connection to check with.</param>
/// <param name="codeSourceDirectory">The Code Source directory to start from.</param>
private static void PackageCheck(OracleConnection con, DirectoryInfo codeSourceDirectory)
{
Console.WriteLine(@"
#################################################
# Database Source
# ");
DirectoryInfo datasouceDirectory = new DirectoryInfo(Path.Combine(codeSourceDirectory.FullName, "DatabaseSource", "CoreSource"));
foreach (DirectoryInfo dirInfo in datasouceDirectory.GetDirectories())
{
string packageOwner = dirInfo.Name;
foreach (FileInfo fileInfo in dirInfo.GetFiles())
{
string packageName = Path.GetFileNameWithoutExtension(fileInfo.Name);
string packageType = null;
switch (fileInfo.Extension)
{
case ".pks":
packageType = "PACKAGE";
break;
case ".pkb":
packageType = "PACKAGE_BODY";
break;
case ".vw":
packageType = "VIEW";
break;
case ".tps":
packageType = "TYPE";
break;
case ".trg":
packageType = "TRIGGER";
break;
case ".fnc":
packageType = "FUNCTION";
break;
case ".tpb":
packageType = "TYPE_BODY";
break;
case ".prc":
packageType = "PROCEDURE";
break;
default:
Debug.Assert(false, $"Unknown extension {fileInfo.Extension}");
break;
}
string result;
OracleCommand cmd = con.CreateCommand();
if (packageType == "PACKAGE" || packageType == "PACKAGE_BODY")
{
cmd.CommandText = $@"SELECT text
FROM dba_source
WHERE type = '{(packageType == "PACKAGE" ? "PACKAGE" : "PACKAGE BODY")}'
AND owner = '{packageOwner}'
AND name = '{packageName}'
ORDER BY line ASC";
OracleDataReader reader = cmd.ExecuteReader();
result = string.Empty;
while (reader.Read())
{
result += reader.GetString(0);
}
reader.Close();
}
else
{
cmd.CommandText = $"SELECT DBMS_METADATA.GET_DDL( object_type => '{packageType}', name => '{packageName.ToUpper()}', schema => '{packageOwner.ToUpper()}' ) FROM DUAL";
try
{
OracleDataReader reader = cmd.ExecuteReader();
reader.Read();
OracleClob clob = reader.GetOracleClob(0);
result = clob.Value;
reader.Close();
}
catch (OracleException ex)
{
if (ex.Number == 31603)
{
Console.WriteLine($"Adding new package {packageName}");
continue;
}
throw;
}
}
string databaseContents = CleanPackageSource(result, packageName);
string fileContents = File.ReadAllText(fileInfo.FullName);
fileContents = CleanPackageSource(fileContents, packageName);
if (databaseContents != fileContents)
{
File.WriteAllText("database.sql", databaseContents);
File.WriteAllText("file.sql", fileContents);
Console.WriteLine($"{packageType} object {fileInfo.Name} is different.");
}
cmd.Dispose();
}
}
}
public static void Main(string[] args)
{
string tainted_2 = null;
string tainted_3 = null;
tainted_2 = Console.ReadLine();
tainted_3 = tainted_2;
do
{
StringBuilder escape = new StringBuilder();
for (int i = 0; i < tainted_2.Length; ++i)
{
char current = tainted_2[i];
switch (current)
{
case '\\':
escape.Append(@"\5c");
break;
case '*':
escape.Append(@"\2a");
break;
case '(':
escape.Append(@"\28");
break;
case ')':
escape.Append(@"\29");
break;
case '\u0000':
escape.Append(@"\00");
break;
case '/':
escape.Append(@"\2f");
break;
default:
escape.Append(current);
break;
}
}
tainted_3 = escape.ToString();
break;
}while((Math.Pow(4, 2) <= 42));
//flaw
string query = "SELECT * FROM Articles WHERE id=" + tainted_3;
string connectionString = "Data Source=localhost;User ID=oracle_user;Password=oracle_password";
OracleConnection dbConnection = null;
try{
dbConnection = new OracleConnection(connectionString);
dbConnection.Open();
OracleCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = query;
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.ToString());
}
dbConnection.Close();
}catch (Exception e) {
Console.WriteLine(e.ToString());
}
}
/// <summary>
/// Processes the given folder definition object, printing any warnings.
/// </summary>
/// <param name="fd">The folder definition to use.</param>
/// <param name="con">The Oracle connection to use.</param>
/// <param name="codeSourceDirectory">The CodeSource directory to compare with.</param>
private static void ProcessFolderDefinition(FolderDefinition fd, OracleConnection con, DirectoryInfo codeSourceDirectory)
{
Console.WriteLine(@"
#################################################
# " + fd.Name + @"
# ");
DirectoryInfo dirInfo = new DirectoryInfo(Path.Combine(codeSourceDirectory.FullName, fd.Directory));
OracleCommand command = con.CreateCommand();
command.CommandText = fd.LoadStatement;
foreach (FileInfo fileInfo in dirInfo.GetFiles(fd.Extension, SearchOption.AllDirectories))
{
command.Parameters.Clear();
command.Parameters.Add("filename", fileInfo.Name);
OracleDataReader reader = command.ExecuteReader();
if (!reader.Read())
{
Console.WriteLine($"{fd.Name} {fileInfo.Name} is new.");
reader.Close();
continue;
}
string databaseContents = CleanXmlSource(reader.GetOracleClob(0).Value);
string fileContents = CleanXmlSource(File.ReadAllText(fileInfo.FullName));
if (databaseContents != fileContents)
{
Console.WriteLine($"{fd.Name} {fileInfo.Name} will be updated.");
}
reader.Close();
}
}
private void btnCancel_Click(object sender, EventArgs e)
{
Boolean delete = true;
int id = Convert.ToInt16(grdDataOrders.Rows[grdDataOrders.CurrentCell.RowIndex].Cells[3].Value.ToString());
float refund = float.Parse(grdDataOrders.Rows[grdDataOrders.CurrentCell.RowIndex].Cells[2].Value.ToString());
int suppid = Convert.ToInt16(grdData.Rows[grdData.CurrentCell.RowIndex].Cells[0].Value.ToString());
float balance = float.Parse(grdData.Rows[grdData.CurrentCell.RowIndex].Cells[2].Value.ToString());
float newBalance = balance - refund;
for (int i = 0; i < grdDataItems.RowCount; i++)
{
if (!grdDataItems.Rows[i].Cells[3].Value.Equals("O"))
{
delete = false;
break;
}
}
if (delete)
{
using (OracleConnection connection = new OracleConnection(DBConnect.oradb))
{
connection.Open();
OracleCommand command = connection.CreateCommand();
OracleTransaction transaction;
// Start a local transaction.
transaction = connection.BeginTransaction();
// Must assign both transaction object and connection
// to Command object for a pending local transaction
command.Connection = connection;
command.Transaction = transaction;
try
{
//microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.begintransaction?view=netframework-4.7.2
command.CommandText =
"UPDATE SUPPLIER SET BALANCE =" + newBalance + " where SUPPLIERID = " + suppid;
command.ExecuteNonQuery();
command.CommandText =
"DELETE FROM ORDERITEMS WHERE ORDERID = " + id;
command.ExecuteNonQuery();
command.CommandText =
"DELETE FROM ORDERS WHERE ORDERID = " + id;
command.ExecuteNonQuery();
MessageBox.Show("Commit next");
// Attempt to commit the transaction.
transaction.Commit();
DataSet ds = new DataSet();
grdData.DataSource = Supplier.getSupplierSummary(ds).Tables["stk"];
}
catch (Exception ex)
{
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
}
}
}
else
{
MessageBox.Show("This cannot be deleted as order has been partially received");
}
txtSupplierName.Clear();
grdData.DataSource = null;
grdDataOrders.DataSource = null;
grdDataItems.DataSource = null;
}
/// <summary>
/// 采用.net1.1 里的oracleclient驱动 执行增，删，改操作
/// </summary>
/// <param name="p_strSql">操作的sql</param>
/// <param name="p_dictParam">字典参数</param>
/// <param name="cmd">cmd</param>
/// <returns>返回结果</returns>
static public int OraExecuteNonQuery(string p_strSql, Dictionary<string, string> p_dictParam)
{
int _iExeCount = 0;
m_oraConn = new OracleConnection(m_strConnectionString);
m_oraCmd = m_oraConn.CreateCommand();
m_oraConn.Open();
OraChangeSelectCommand(p_strSql, p_dictParam, ref m_oraCmd);
try
{
_iExeCount = m_oraCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
WriteLog(exp, p_strSql);
_iExeCount = -1;
}
finally
{
m_oraConn.Dispose();
m_oraCmd.Dispose();
}
return _iExeCount;
}
private void button4_Click(object sender, EventArgs e)
{
this.SetConnection();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT CRP_CD FROM TBCB_CRP_DOCU_INFO where rownum <=1000";
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
// List<string[]> data = new List<string[]>();
while (dr.Read())
{
comboBox4.Items.Add(dr[0].ToString());
}
}