private void button1_Click(object sender, EventArgs e)
{
string connection = "Data Source=ORCL; User Id=pharmacy; Password=pharmacy";
OracleConnection InsertData = new OracleConnection(connection);
InsertData.Open();
OracleParameter empno = new OracleParameter();
empno.OracleDbType = OracleDbType.Int16;
empno.Value = Quantity.Text;
OracleParameter deptno = new OracleParameter();
deptno.OracleDbType = OracleDbType.Int16;
deptno.Value = Price.Text;
OracleCommand insertEmpno = new OracleCommand();
insertEmpno.Connection = InsertData;
insertEmpno.Parameters.Add(empno);
insertEmpno.CommandText = "select ename from employees where empid = 101";
insertEmpno.CommandType = CommandType.Text;
OracleDataReader DataRead = insertEmpno.ExecuteReader();
DataRead.Read();
while (DataRead.Read())
{
listBox1.Items.Add(DataRead.GetString(1));
}
InsertData.Dispose();
}
/// <summary>
/// 执行数据库查询操作,返回受影响的行数
/// </summary>
/// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param>
/// <param name="commandParameters">命令参数集合</param>
/// <returns>当前查询操作影响的数据行数</returns>
public static int ExecuteNonQuery(string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand command = new OracleCommand();
OracleConnection connection = new OracleConnection(connstr);
int result = 0;
try
{
PrepareCommand(command, connection, null, CommandType.Text, cmdText, commandParameters);
result = command.ExecuteNonQuery();
command.Parameters.Clear();
}
catch
{
throw;
}
finally
{
command.Dispose();
connection.Close();
connection.Dispose();
}
return result;
}
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 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;
}
// GET: Album
// [HttpPost]
public List<AlbumModels> Album()
{
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
string selectStr = /* "insert into album(albumID,albumname,username) values('111','testalbum','KING')";*/
"select * "
+ "from album ";
// " +(Session["username"] as string)+"
OracleCommand cmd = new OracleCommand(selectStr, conn);
OracleDataReader dr = cmd.ExecuteReader();
var Models = new List<AlbumModels>();
while (dr.Read())
{
AlbumModels model = new AlbumModels();
model.AlbumName = dr["albumname"].ToString();
model.AlbumId = dr["albumid"].ToString();
//model.Username = Session["username"] as string ;
string selectStr1 = "select * from photo where albumid = " + model.AlbumId;
OracleCommand cmd1 = new OracleCommand(selectStr1, conn);
OracleDataReader dr1 = cmd1.ExecuteReader();
dr1.Read();
model.Firstphoto = dr1["photoaddress"].ToString();
Models.Add(model);
//Session.Add("currentAlbumName", albumName);
//Session.Add("currentAlbumId", albumId); //点击时保存到session
}
dr.Close();
int insertedLines = cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
return Models;
}
public override async Task <DbConnection> NewConnection()
{
OracleConnection connection = null;
try
{
string connectionString;
if (UseConnectionString)
{
connectionString = ConnectionString;
}
else
{
var hostport = Server.Split(':');
string port;
if (hostport.Count() == 1)
{
port = "";
}
else
{
port = ":" + hostport[1];
}
connectionString = "Data Source=" + hostport[0] + port + "; User Id=" + Username + "; Password="******"SYS")
{
connectionString = connectionString + "; DBA Privilege=SYSDBA";
}
}
connection = new OracleConnection(connectionString);
await connection.OpenAsync();
State = (EConnectionState)connection.State;
if (connection.State != ConnectionState.Open)
{
connection.Dispose();
throw new ConnectionException($"The Oracle connection has a state of {connection.State}.");
}
// set the default schema
if (!string.IsNullOrEmpty(DefaultDatabase))
{
using (var cmd = CreateCommand(connection, $"ALTER SESSION SET CURRENT_SCHEMA={AddDelimiter(DefaultDatabase)}"))
{
await cmd.ExecuteNonQueryAsync();
}
}
return(connection);
}
catch (Exception ex)
{
connection?.Dispose();
throw new ConnectionException($"Oracle connection failed. {ex.Message}", ex);
}
}
public static void ConnectionPoolTest(int iterations)
{
// used to track execution duration
DateTime timeStart;
DateTime timeEnd;
double totalSeconds;
// the connection object to use for the test
OracleConnection con;
// display simple prompt text
Console.WriteLine("Beginning Connection Pool Test with {0} iterations...", iterations.ToString());
// capture test start time for no pooling test
timeStart = DateTime.Now;
// loop creating a connection with no connection pooling
// number of loops is determined by the iterations parameter
for (int i = 0; i < iterations; i++) {
con = new OracleConnection(no_pool);
con.Open();
con.Dispose();
}
// capture test end time for no pooling test
timeEnd = DateTime.Now;
// calculate total seconds for this test
totalSeconds = timeEnd.Subtract(timeStart).TotalSeconds;
// display time used for no pooling test
Console.WriteLine(" No Pooling: {0} total seconds.", totalSeconds.ToString());
// capture test start time for pooling test
timeStart = DateTime.Now;
// loop creating a connection with connection pooling
// number of loops is determined by the iterations parameter
for (int i = 0; i < iterations; i++) {
con = new OracleConnection(with_pool);
con.Open();
con.Dispose();
}
// capture test end time for pooling test
timeEnd = DateTime.Now;
// calculate total seconds for this test
totalSeconds = timeEnd.Subtract(timeStart).TotalSeconds;
// display time used for pooling test
Console.WriteLine(" With Pooling: {0} total seconds.", totalSeconds.ToString());
Console.WriteLine();
}
public AddAnEmployee()
{
InitializeComponent();
ex_empid.Hide();
ex_ename.Hide();
ex_inss.Hide();
ex_payroll.Hide();
ex_sal.Hide();
msg_bno.Show();
msg_bno.Text = "Please Select A Branch";
msg_mgr.Show();
msg_mgr.Text = "Please Select A Manager";
// string _sql;
try
{
DataSet _ds;
OracleConnection _connObj = new OracleConnection(_connstring);
_ds = new DataSet();
_connObj.Open();
OracleCommand ShowBNO = new OracleCommand();
ShowBNO.Connection = _connObj;
ShowBNO.CommandText = "select bno from branch";
ShowBNO.CommandType = CommandType.Text;
OracleDataReader DataRead = ShowBNO.ExecuteReader();
DataRead.Read();
while (DataRead.Read())
{
bno.Items.Add(DataRead.GetString(1));
}
ShowBNO.Dispose();
//_sql = "SELECT * FROM branch";
// OracleDataAdapter _adapterObj = new OracleDataAdapter(_sql, _connObj);
// _adapterObj.Fill(_ds);
_connObj.Close();
_connObj.Dispose();
_connObj = null;
bno.DataSource = _ds.Tables[0] ;
dataGridView1.DataSource = _ds.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
protected virtual void Dispose(bool disposing)
{
if (disposing && _OracleConnection != null)
{
_OracleConnection?.Dispose();
if (_OracleConnection.State == ConnectionState.Open)
{
_OracleConnection.Close();
}
}
}
protected void AgentBtnClick(object sender, EventArgs e)
{
long iAgentId;
long iOfficePhone;
long iPager;
long iCellPhone;
long iHomePhone;
DateTime dHireDate;
string sTitle;
string sLastName;
string sFirstName;
string sMiddleInital;
s = "Data Source=nova; User Id=#######; Password=#######";
con = new OracleConnection(s);
con.Open();
cmd = new OracleCommand();
cmd.Connection = con;
iAgentId = Convert.ToInt64(agentIdTxtBox.Text);
iAgentId = int.Parse(agentIdTxtBox.Text);
iOfficePhone = Convert.ToInt64(agentOfficePhTxtBox.Text);
iOfficePhone = int.Parse(agentOfficePhTxtBox.Text);
iPager = Convert.ToInt64(agentPagerTxtBox.Text);
iPager = int.Parse(agentPagerTxtBox.Text);
iCellPhone = Convert.ToInt64(agentCellPhTxtBox.Text);
iCellPhone = Int64.Parse(agentCellPhTxtBox.Text);
iHomePhone = Convert.ToInt64(agentHomePhTxtBox.Text);
iHomePhone = int.Parse(agentHomePhTxtBox.Text);
dHireDate = DateTime.Parse(agentHireDtTxtBox.Text);
sTitle = agentTitleTxtBox.Text;
sLastName = agentLastNameTxtBox.Text;
sFirstName = agentFirstNameTxtBox.Text;
sMiddleInital = agentMidInitTxtBox.Text;
cmd.CommandText = "INSERT INTO AGENT VALUES(" + iAgentId + "," + iOfficePhone +
"," + iPager + "," + iCellPhone + ", " + iHomePhone + ", Trunc(TO_DATE('" + dHireDate.ToString("dd-MMM-yyyy") + "','DD-MON-YYYY')), '" +
sTitle + "', '" + sLastName + "', '" + sFirstName + "', '" + sMiddleInital +"')";
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
Result.InnerText = "Record not inserted";
else
Result.InnerText = "Information Submitted Successfully";
con.Dispose();
System.Threading.Thread.Sleep(50);
Response.Redirect("Insert.aspx");
}
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 bool disposedValue = false; // To detect redundant calls
protected virtual void Dispose(bool disposing)
{
if (!disposedValue)
{
if (disposing)
{
// TODO: dispose managed state (managed objects).
_oracleConnection?.Dispose();
}
// TODO: free unmanaged resources (unmanaged objects) and override a finalizer below.
// TODO: set large fields to null.
disposedValue = true;
}
}
public static bool TableExists(string name, OracleConnection con)
{
string sql = "SELECT count(*) AS c FROM " + name;
string count = string.Empty;
long output;
using (OracleCommand command = new OracleCommand(sql, con))
{
try
{
using (OracleDataReader reader = command.ExecuteReader())
{
try
{
while (reader.Read())
{
count = reader["c"].ToString();
}
}
finally
{
reader.Close();
}
}
}
catch (Exception ex)
{
if (!ex.Message.Contains("ORA-00942"))
{
throw new Exception(string.Format(Resources.NoSniff, name), ex);
}
}
}
con.Close();
con.Dispose();
if (Int64.TryParse(count, out output))
{
return true;
}
return false;
}
public int checkExistance(int employeeid)
{
OracleConnection con = new OracleConnection(cnString);
con.Open();
OracleCommand cmd = new OracleCommand("Select * from employeess where employeeid=" + employeeid + "", con);
OracleDataReader dr;
try
{
dr = cmd.ExecuteReader();
if (dr.Read())
{
return 1;
}
else
return 0;
}
catch (Exception ex)
{
throw;
}
finally
{
con.Close();
con.Dispose();
}
}
// GET: WebView
public ActionResult Fakebook()
{
Console.WriteLine("fakebook");
string oradb = "Data Source=(DESCRIPTION="
+ "(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))"
+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));"
+ "User Id=monzy;Password=123456;";
OracleConnection conn = new OracleConnection(oradb);
try
{
conn.Open();
string selectStr = "select * from test1";
string insertStr = "insert into test(t) values(500)";
OracleCommand cmd = new OracleCommand(insertStr, conn);
//OracleDataReader dr = cmd.ExecuteReader();
//dr.Read();
int insertedLines = cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
//return View("insertedLines: " + insertedLines);
}
catch (OracleException ex) // catches only Oracle errors
{
switch (ex.Number)
{
case 1:
//return "Error attempting to insert duplicate data.";
case 12545:
//return "The database is unavailable.";
default:
return View();
//return "Database error: " + ex.Message.ToString();
}
}
catch (Exception ex) // catches any error not previously caught
{
Console.Write("Other exception");
}
Console.WriteLine("success");
return View();
}
/// <summary>
/// 关闭数据库并释放资源
/// </summary>
public void Close()
{
if (_connSql?.State != ConnectionState.Open)
{
return;
}
try
{
_connSql.Close();
_connSql.Dispose();
_connSql = null;
}
catch (Exception)
{
_connSql?.Dispose();
_connSql = null;
throw new Exception("关闭数据库失败！");
}
}
//新增照片
public string AddPhoto(string id,string address)
{
OracleConnection conn = new OracleConnection(oradb);
try
{
conn.Open();
string insertStr = "insert into photo(photoId,albumId,photoAddress) values('"+ id+"','"
+(Session["currentAlbumId"] as string) + "','"
+address +"')";
OracleCommand cmd = new OracleCommand(insertStr, conn);
int insertedLines = cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
return "new PHOTO:" + (Session["username"] as string);
}
catch (Exception ex)
{
return "exception";
}
}
private bool LoginUser(string dataSource, string username, string password)
{
IDbConnection con = null;
try
{
con = new OracleConnection();
con.ConnectionString = String.Format(config.GetConnectionString("UserConnection"), dataSource, username, password);
con.Open();
return(true);
}
catch (Exception e)
{
ModelState.AddModelError(string.Empty, "Invalid login attempt.");
return(false);
}
finally
{
con?.Close();
con?.Dispose();
}
}
/**Funcion de insercion a la DB*/
private int oracleInsert(ArrayList q)
{
/**Almacenamos data en BD*/
int count = 0;
OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString)/*"Data Source=localhost;Persist Security Info=True;User ID=webadmin;Password=webadmin;Unicode=True")*/;
conn.Open();
OracleTransaction t = conn.BeginTransaction();
try
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.Transaction = t;
cmd.CommandType = CommandType.Text;
//Ejecutamos cada insert en la lista
foreach (String s in q)
{
cmd.CommandText = s;
count += cmd.ExecuteNonQuery();
}
//Si fue exitoso, Commit
t.Commit();
}
catch (Exception ex)
{
//Sino rollback
Response.Write(ex);
t.Rollback();
}
finally
{
//Cerramos conexion
t.Dispose();
conn.Dispose();
conn.Close();
}
return count;
}
public void BookItem(string username, string password)
{
//Creating Connection with SQL Server Database
OracleConnection con = new OracleConnection(cnString);
con.Open();
//store procedure name
OracleCommand cmd = new OracleCommand("user_login", con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
//inserting with data procedure paramenters
cmd.Parameters.Add("firstname", OracleDbType.Varchar2, username, ParameterDirection.Input);
cmd.Parameters.Add("lastname", OracleDbType.Varchar2, password, ParameterDirection.Input);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
con.Dispose();
}
}
//签到加分
// [HttpPost]
public string AddPoints()
{
OracleConnection conn = new OracleConnection(oradb);
try
{
conn.Open();
string insertStr = "update fake_user "
+ "set points = points + 5 "
+ "where userName = '******'";
OracleCommand cmd = new OracleCommand(insertStr, conn);
int insertedLines = cmd.ExecuteNonQuery();
string selectStr = "select points "
+ "from fake_user "
+ "where userName = '******'";
cmd = new OracleCommand(selectStr, conn);
insertedLines = cmd.ExecuteNonQuery();
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
int currentPoints = dr.GetInt32(0);
conn.Close();
conn.Dispose();
return "AddPonints:" + (Session["username"] as string) + "+ 5" + " NOW:" + currentPoints;
}
catch (Exception ex)
{
return "exception";
}
}
//建立相册
public string CreateAlbum(string id,string name)
{
OracleConnection conn = new OracleConnection(oradb);
try
{
conn.Open();
string insertStr = "insert into album(albumId,albumName,username) values('"+id+"','"
+name+"','"
+(Session["username"] as string)+"')";
OracleCommand cmd = new OracleCommand(insertStr, conn);
int insertedLines = cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
return "new ALBUM:" + (Session["username"] as string);
}
catch (Exception ex)
{
return "exception";
}
}
public void SendToDb(List <InvalidPush> input)
{
#if DEBUG
Console.WriteLine("Вошли в метод SendToDb");
#endif
inAnswer = input.ToString();
OracleConnection conn = DBUtils.GetDBConnection();
#if DEBUG
Console.WriteLine("Отправляем ответ ***************в БД");
#endif
OracleCommand cmd = new OracleCommand(procedure, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(nameof(inAnswer), OracleDbType.Clob);
cmd.Parameters.Add(new OracleParameter(nameof(outErrCode), OracleDbType.Int32));
cmd.Parameters.Add(new OracleParameter(nameof(outErrText), OracleDbType.Varchar2));
cmd.Parameters[nameof(inAnswer)].Direction = System.Data.ParameterDirection.Input;
cmd.Parameters[nameof(outErrCode)].Direction = System.Data.ParameterDirection.Output;
cmd.Parameters[nameof(outErrText)].Direction = System.Data.ParameterDirection.Output;
cmd.Parameters[nameof(inAnswer)].Value = inAnswer;
try
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
cmd.ExecuteNonQuery();
#if DEBUG
Console.WriteLine($"Процедура {procedure} извлечена успешно");
#endif
inAnswer = cmd.Parameters[nameof(inAnswer)].Value.ToString();
outErrCode = Convert.ToInt32(cmd.Parameters[nameof(outErrCode)].Value?.ToString());
outErrText = cmd.Parameters[nameof(outErrText)].Value.ToString();
#if DEBUG
Console.WriteLine(cmd.Parameters[nameof(inAnswer)].ParameterName + "\t:\t" + inAnswer);
Console.WriteLine(cmd.Parameters[nameof(outErrCode)].ParameterName + "\t:\t" + outErrCode);
Console.WriteLine(cmd.Parameters[nameof(outErrText)].ParameterName + "\t:\t" + outErrText);
#endif
#if !DEBUG
if (outErrCode != 0)
{
Console.WriteLine(cmd.Parameters[nameof(outErrText)].ParameterName + "\t:\t" + outErrText);
}
#endif
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex);
//Console.WriteLine(ex.StackTrace);
}
finally
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
conn?.Dispose();
}
}
public List<CiudadDTO> getCiudad(int idDepartamento = -1)
{
List<CiudadDTO> lstCiudad = new List<CiudadDTO>();
Parametros p = new Parametros();
CiudadDTO itemCiudad;
using (OracleConnection con = new OracleConnection(p.oracleConnString().ToString()))
{
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = (idDepartamento != -1) ? "select IDCIUDAD, IDDEPARTAMENTO, NOMBRECIUDAD from ciudad where IDDEPARTAMENTO =" + idDepartamento.ToString() : "select IDCIUDAD, IDDEPARTAMENTO, NOMBRECIUDAD from ciudad";
using (OracleDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
itemCiudad = new CiudadDTO();
itemCiudad.idCiudad = reader.GetInt32(0);
itemCiudad.idDepartamento = reader.GetInt32(1);
itemCiudad.nombreCiudad = reader.GetValue(2).ToString();
lstCiudad.Add(itemCiudad);
}
}
con.Close();
con.Dispose();
}
return lstCiudad;
}
private void saveMainDetails()
{
if (txtName.Text.Trim() == "")
{
lblMsg.Text = "Please Enter Name";
return;
}
if (txtEPF.Text.Trim() == "")
{
lblMsg.Text = "Please Enter EPF";
return;
}
if (!rbtnMale.Checked && !rbtnFemale.Checked)
{
lblMsg.Text = "Sex must be selected";
return;
}
if (!rbtnMaritialStatusMarried.Checked && !rbtnMaritialStatusSingle.Checked)
{
lblMsg.Text = "Maritial Status must be selected";
return;
}
//if (rbtnMaritialStatusMarried.Checked)
//{
// if (txtNameOfSpouse.Text.Trim() == "")
// {
// lblMsg.Text = "Please Enter Name of spouse";
// return;
// }
//}
if (ddlBranch.SelectedValue == "0")
{
lblMsg.Text = "Branch must be selected";
return;
}
if (txtNoOfChildren.Text != "")
{
if (Convert.ToInt32(txtNoOfChildren.Text) > 0)
{
}
}
try
{
if (CheckUserIdAlreadySaved(ddlCompany.SelectedValue.ToString(), txtEPF.Text))
{
deletePreviousUserRecord(ddlCompany.SelectedValue.ToString(), txtEPF.Text);
}
OracleConnection conProcess = new OracleConnection(ConfigurationManager.ConnectionStrings["CGConnectionString"].ToString());
conProcess.Open();
OracleCommand spProcess = null;
spProcess = new OracleCommand("INSERT_WS_SURVEY_2016_MAIN");
spProcess.CommandType = CommandType.StoredProcedure;
spProcess.Connection = conProcess;
spProcess.Parameters.Add("V_COMPANY", OracleType.VarChar).Value = ddlCompany.SelectedValue.ToString();
spProcess.Parameters.Add("V_EPF_NO", OracleType.Number).Value = txtEPF.Text;
spProcess.Parameters.Add("V_NAME", OracleType.VarChar).Value = txtName.Text;
spProcess.Parameters.Add("V_BRANCH_CODE", OracleType.VarChar).Value = ddlBranch.SelectedValue.ToString();
spProcess.Parameters.Add("V_RES_ADDRESS_1", OracleType.VarChar).Value = txtAddressLine1.Text;
spProcess.Parameters.Add("V_RES_ADDRESS_2", OracleType.VarChar).Value = txtAddressLine2.Text;
spProcess.Parameters.Add("V_RES_ADDRESS_3", OracleType.VarChar).Value = txtAddressLine3.Text;
if (rbtnMale.Checked)
{
spProcess.Parameters.Add("V_SEX", OracleType.VarChar).Value = "M";
}
else if (rbtnFemale.Checked)
{
spProcess.Parameters.Add("V_SEX", OracleType.VarChar).Value = "F";
}
if (rbtnMaritialStatusSingle.Checked)
{
spProcess.Parameters.Add("V_MARITIAL_STATUS", OracleType.VarChar).Value = "S";
}
else if (rbtnMaritialStatusMarried.Checked)
{
spProcess.Parameters.Add("V_MARITIAL_STATUS", OracleType.VarChar).Value = "M";
}
spProcess.Parameters.Add("V_NAME_OF_SPOUSE", OracleType.VarChar).Value = txtNameOfSpouse.Text;
if (txtNoOfChildren.Text == "")
{
spProcess.Parameters.Add("V_NO_OF_CHILDREN", OracleType.Number).Value = DBNull.Value;
}
else
{
spProcess.Parameters.Add("V_NO_OF_CHILDREN", OracleType.Number).Value = txtNoOfChildren.Text;
}
////--------------------------------
spProcess.Parameters.Add("V_CH1_NAME", OracleType.VarChar).Value = txtCH1Name.Text;
if (txtCH1DOB.Text == "")
{
spProcess.Parameters.Add("V_CH1_DOB", OracleType.DateTime).Value = DBNull.Value;
}
else
{
spProcess.Parameters.Add("V_CH1_DOB", OracleType.DateTime).Value = txtCH1DOB.Text;
}
if (rbtnCH1Male.Checked)
{
spProcess.Parameters.Add("V_CH1_SEX", OracleType.VarChar).Value = "M";
}
else if (rbtnCH1Female.Checked)
{
spProcess.Parameters.Add("V_CH1_SEX", OracleType.VarChar).Value = "F";
}
else
{
spProcess.Parameters.Add("V_CH1_SEX", OracleType.VarChar).Value = DBNull.Value;
}
////---------------------------------
////--------------------------------
spProcess.Parameters.Add("V_CH2_NAME", OracleType.VarChar).Value = txtCH2Name.Text;
if (txtCH2DOB.Text == "")
{
spProcess.Parameters.Add("V_CH2_DOB", OracleType.DateTime).Value = DBNull.Value;
}
else
{
spProcess.Parameters.Add("V_CH2_DOB", OracleType.DateTime).Value = txtCH2DOB.Text;
}
if (rbtnCH2Male.Checked)
{
spProcess.Parameters.Add("V_CH2_SEX", OracleType.VarChar).Value = "M";
}
else if (rbtnCH2Female.Checked)
{
spProcess.Parameters.Add("V_CH2_SEX", OracleType.VarChar).Value = "F";
}
else
{
spProcess.Parameters.Add("V_CH2_SEX", OracleType.VarChar).Value = DBNull.Value;
}
////---------------------------------
//--------------------------------
spProcess.Parameters.Add("V_CH3_NAME", OracleType.VarChar).Value = txtCH3Name.Text;
if (txtCH3DOB.Text == "")
{
spProcess.Parameters.Add("V_CH3_DOB", OracleType.DateTime).Value = DBNull.Value;
}
else
{
spProcess.Parameters.Add("V_CH3_DOB", OracleType.DateTime).Value = txtCH3DOB.Text;
}
if (rbtnCH3Male.Checked)
{
spProcess.Parameters.Add("V_CH3_SEX", OracleType.VarChar).Value = "M";
}
else if (rbtnCH3Female.Checked)
{
spProcess.Parameters.Add("V_CH3_SEX", OracleType.VarChar).Value = "F";
}
else
{
spProcess.Parameters.Add("V_CH3_SEX", OracleType.VarChar).Value = DBNull.Value;
}
//---------------------------------
//--------------------------------
spProcess.Parameters.Add("V_CH4_NAME", OracleType.VarChar).Value = txtCH4Name.Text;
if (txtCH4DOB.Text == "")
{
spProcess.Parameters.Add("V_CH4_DOB", OracleType.DateTime).Value = DBNull.Value;
}
else
{
spProcess.Parameters.Add("V_CH4_DOB", OracleType.DateTime).Value = txtCH4DOB.Text;
}
if (rbtnCH4Male.Checked)
{
spProcess.Parameters.Add("V_CH4_SEX", OracleType.VarChar).Value = "M";
}
else if (rbtnCH4Female.Checked)
{
spProcess.Parameters.Add("V_CH4_SEX", OracleType.VarChar).Value = "F";
}
else
{
spProcess.Parameters.Add("V_CH4_SEX", OracleType.VarChar).Value = DBNull.Value;
}
//---------------------------------
//--------------------------------
spProcess.Parameters.Add("V_CH5_NAME", OracleType.VarChar).Value = txtCH5Name.Text;
if (txtCH5DOB.Text == "")
{
spProcess.Parameters.Add("V_CH5_DOB", OracleType.DateTime).Value = DBNull.Value;
}
else
{
spProcess.Parameters.Add("V_CH5_DOB", OracleType.DateTime).Value = txtCH5DOB.Text;
}
if (rbtnCH5Male.Checked)
{
spProcess.Parameters.Add("V_CH5_SEX", OracleType.VarChar).Value = "M";
}
else if (rbtnCH5Female.Checked)
{
spProcess.Parameters.Add("V_CH5_SEX", OracleType.VarChar).Value = "F";
}
else
{
spProcess.Parameters.Add("V_CH5_SEX", OracleType.VarChar).Value = DBNull.Value;
}
//---------------------------------
spProcess.ExecuteNonQuery();
conProcess.Close();
conProcess.Dispose();
ClearComponents();
/// lblMsg.Text = "Details Successfully Submitted";
// Page.ClientScript.RegisterStartupScript(GetType(), "Message", "alert('Details Successfully Submitted')", true);
// Response.Redirect("index.aspx");
HTMLHelper.jsAlertAndRedirect(this, "Details Successfully Submitted", ResolveUrl("~/SurveyForm.aspx"));
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, GetType(), "Message", "alert('Error while Saving');", true);
}
}
/// <summary>
/// 执行数据库查询操作,返回受影响的行数
/// </summary>
/// <param name="connection">Oracle数据库连接对象</param>
/// <param name="cmdType">Command类型</param>
/// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param>
/// <param name="commandParameters">命令参数集合</param>
/// <returns>当前查询操作影响的数据行数</returns>
public static int ExecuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("当前数据库连接不存在");
OracleCommand command = new OracleCommand();
int result = 0;
try
{
PrepareCommand(command, connection, null, cmdType, cmdText, commandParameters);
result = command.ExecuteNonQuery();
command.Parameters.Clear();
}
catch
{
throw;
}
finally
{
command.Dispose();
connection.Close();
connection.Dispose();
}
return result;
}
//static private ResponseEntity pushResult;
public static void SendAnswer(DbEntity record, ResponseEntity pushResult)
{
#if DEBUG
Console.WriteLine("Вошли в метод SendAnswer");
#endif
//pushResult = result;
inMsg_id = pushResult.MessageId;
inAnswer = GetResponseXml(pushResult).ToString();
inStatus = pushResult.Exception == null
? 1
: -1;
OracleConnection conn = DBUtils.GetDBConnection();
#if DEBUG
Console.WriteLine("Отправляем ответ от сервера PUSH в БД");
#endif
OracleCommand cmd = new OracleCommand(procedure, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(nameof(inMsg_id), OracleDbType.Varchar2);
cmd.Parameters.Add(nameof(inStatus), OracleDbType.Int32);
cmd.Parameters.Add(nameof(inAnswer), OracleDbType.Clob);
cmd.Parameters.Add(new OracleParameter(nameof(outErrCode), OracleDbType.Int32));
cmd.Parameters.Add(new OracleParameter(nameof(outErrText), OracleDbType.Varchar2));
cmd.Parameters[nameof(inMsg_id)].Direction = System.Data.ParameterDirection.Input;
cmd.Parameters[nameof(inStatus)].Direction = System.Data.ParameterDirection.Input;
cmd.Parameters[nameof(inAnswer)].Direction = System.Data.ParameterDirection.Input;
cmd.Parameters[nameof(outErrCode)].Direction = System.Data.ParameterDirection.Output;
cmd.Parameters[nameof(outErrText)].Direction = System.Data.ParameterDirection.Output;
cmd.Parameters[nameof(inMsg_id)].Value = inMsg_id;
cmd.Parameters[nameof(inAnswer)].Value = inAnswer;
cmd.Parameters[nameof(inStatus)].Value = inStatus;
try
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
cmd.ExecuteNonQuery();
#if DEBUG
Console.WriteLine($"Процедура {procedure} извлечена успешно");
#endif
inMsg_id = cmd.Parameters[nameof(inMsg_id)].Value.ToString();
inStatus = Convert.ToInt32(cmd.Parameters[nameof(inStatus)].Value?.ToString());
inAnswer = cmd.Parameters[nameof(inAnswer)].Value.ToString();
outErrCode = Convert.ToInt32(cmd.Parameters[nameof(outErrCode)].Value?.ToString());
outErrText = cmd.Parameters[nameof(outErrText)].Value.ToString();
#if DEBUG
Console.WriteLine(cmd.Parameters[nameof(inMsg_id)].ParameterName + "\t:\t" + inMsg_id);
Console.WriteLine(cmd.Parameters[nameof(inStatus)].ParameterName + "\t:\t" + inStatus);
Console.WriteLine(cmd.Parameters[nameof(inAnswer)].ParameterName + "\t:\t" + inAnswer);
Console.WriteLine(cmd.Parameters[nameof(outErrCode)].ParameterName + "\t:\t" + outErrCode);
Console.WriteLine(cmd.Parameters[nameof(outErrText)].ParameterName + "\t:\t" + outErrText);
#endif
record.outErrCode = Convert.ToInt32(cmd.Parameters[nameof(outErrCode)].Value.ToString());
record.outErrText = cmd.Parameters[nameof(outErrText)].Value?.ToString();
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex);
//Console.WriteLine(ex.StackTrace);
}
finally
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
conn?.Dispose();
//ErrorInfoClass.Dispose(responseEntity);
//if ( ErrorInfoClass.ResponseEntities.All(en => en.Equals(responseEntity)) )
//{
// ErrorInfoClass.ResponseEntities = new System.Collections.Generic.List<ResponseEntity>();
//}
}
}
public static ProcedureInfo ListProcedureParameters(string ownerName, string package, string procedureName)
{
OracleConnection connection = null;
var result = new ProcedureInfo(0, package, procedureName);
try
{
connection = new OracleConnection(_ConnectionString);
connection.Open();
using var command = new OracleCommand();
command.BindByName = true;
command.CommandType = CommandType.Text;
if (!string.IsNullOrEmpty(package))
{
const string sql =
"SELECT t.ARGUMENT_NAME, t.in_out, t.DATA_TYPE FROM SYS.ALL_ARGUMENTS t WHERE package_name = :packageName AND OBJECT_NAME = :procName AND owner = :owner AND ARGUMENT_NAME IS NOT NULL ORDER BY t.SEQUENCE";
command.CommandText = sql;
command.Parameters
.Add("packageName", OracleDbType.Varchar2, ParameterDirection.Input)
.Value = package;
}
else
{
const string sql =
"SELECT t.ARGUMENT_NAME, t.in_out, t.DATA_TYPE FROM SYS.ALL_ARGUMENTS t WHERE package_name IS NULL AND OBJECT_NAME = :procName AND owner = :owner AND ARGUMENT_NAME IS NOT NULL ORDER BY t.SEQUENCE";
command.CommandText = sql;
}
command.Connection = connection;
//command.Parameters.Add("packageName", OracleDbType.Varchar2, ParameterDirection.Input).Value = package;
command.Parameters
.Add("procName", OracleDbType.Varchar2, ParameterDirection.Input)
.Value = procedureName;
command.Parameters
.Add("owner", OracleDbType.Varchar2, ParameterDirection.Input)
.Value = ownerName;
using var reader = command.ExecuteReader();
if (reader.HasRows)
{
var i = 0;
while (reader.Read())
{
var name = GetString(reader["ARGUMENT_NAME"]);
var info = new ParameterInfo(
name,
reader["DATA_TYPE"].GetString(),
reader["in_out"].GetString().IsEqual("IN"),
i++,
reader["DATA_TYPE"].GetString().GetNetType(),
Utils.ToUpperCamelCase(name, false),
Utils.ToLowerCamelCase(name, false)
);
result.AddParam(info);
}
}
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
return(result);
}
public async Task <IHttpActionResult> GetAll_MerchandiseTypeRoot()
{
string rootUnitcode = ConfigurationManager.AppSettings["rootUnitCode"];
var result = new TransferObj <List <ChoiceObj> >();
List <ChoiceObj> lstResult = new List <ChoiceObj>();
using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["LTT.Connection"].ConnectionString))
{
try
{
connection.Open();
if (connection.State == ConnectionState.Open)
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;
cmd.InitialLONGFetchSize = 1000;
cmd.CommandText = "SELECT MALOAIVATTU,TENLOAIVT,UNITCODE FROM DM_LOAIVATTU WHERE TRANGTHAI = 10 AND UNITCODE = '" + rootUnitcode + "'";
cmd.CommandType = CommandType.Text;
OracleDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
ChoiceObj _DTO = new ChoiceObj();
string MALOAIVATTU = dataReader["MALOAIVATTU"] != null ? dataReader["MALOAIVATTU"].ToString() : "";
string TENLOAIVT = dataReader["TENLOAIVT"] != null ? dataReader["TENLOAIVT"].ToString() : "";
_DTO.Value = MALOAIVATTU;
_DTO.Text = MALOAIVATTU + "|" + TENLOAIVT;
_DTO.Description = TENLOAIVT;
_DTO.ExtendValue = dataReader["UNITCODE"] != null ? dataReader["UNITCODE"].ToString() : "";
lstResult.Add(_DTO);
}
}
if (lstResult.Count > 0)
{
result.Status = true;
result.Data = lstResult;
}
else
{
result.Status = false;
result.Message = "NotFound";
}
}
else
{
result.Status = false;
result.Message = "NotFound";
}
}
catch
{
result.Status = false;
result.Message = "NotFound";
}
finally
{
connection.Close();
connection.Dispose();
}
}
return(Ok(result));
}
public static HANGKHACHHANG_DTO LAY_QUYDOI_THEOHANGKH_FROM_ORACLE(string MAHANGKHACHHANG)
{
HANGKHACHHANG_DTO RESULT = new HANGKHACHHANG_DTO();
try
{
using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["ERBusConnection"].ConnectionString))
{
try
{
connection.Open();
if (connection.State == ConnectionState.Open)
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;
cmd.CommandText = string.Format(@"SELECT
MAHANG,
TENHANG,
SOTIEN_LENHANG,
TYLE_SINHNHAT,
TYLE_DACBIET,
QUYDOITIEN_THANH_DIEM,
QUYDOIDIEM_THANH_TIEN
FROM
HANGKHACHHANG WHERE TRANGTHAI = 10 AND MAHANG = '" + MAHANGKHACHHANG + "' AND UNITCODE = '" + Session.Session.CurrentUnitCode + "'");
cmd.CommandType = CommandType.Text;
OracleDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
decimal SOTIEN_LENHANG = 0;
decimal TYLE_SINHNHAT = 0;
decimal TYLE_DACBIET = 0;
decimal QUYDOITIEN_THANH_DIEM = 0;
decimal QUYDOIDIEM_THANH_TIEN = 0;
RESULT.MAHANG = dataReader["MAHANG"] != null ? dataReader["MAHANG"].ToString() : "";
RESULT.TENHANG = dataReader["TENHANG"] != null ? dataReader["TENHANG"].ToString() : "";
decimal.TryParse(dataReader["SOTIEN_LENHANG"] != null ? dataReader["SOTIEN_LENHANG"].ToString() : "", out SOTIEN_LENHANG);
decimal.TryParse(dataReader["TYLE_SINHNHAT"] != null ? dataReader["TYLE_SINHNHAT"].ToString() : "", out TYLE_SINHNHAT);
decimal.TryParse(dataReader["TYLE_DACBIET"] != null ? dataReader["TYLE_DACBIET"].ToString() : "", out TYLE_DACBIET);
decimal.TryParse(dataReader["QUYDOITIEN_THANH_DIEM"] != null ? dataReader["QUYDOITIEN_THANH_DIEM"].ToString() : "", out QUYDOITIEN_THANH_DIEM);
decimal.TryParse(dataReader["QUYDOIDIEM_THANH_TIEN"] != null ? dataReader["QUYDOIDIEM_THANH_TIEN"].ToString() : "", out QUYDOIDIEM_THANH_TIEN);
RESULT.SOTIEN_LENHANG = SOTIEN_LENHANG;
RESULT.TYLE_SINHNHAT = TYLE_SINHNHAT;
RESULT.TYLE_DACBIET = TYLE_DACBIET;
RESULT.QUYDOITIEN_THANH_DIEM = QUYDOITIEN_THANH_DIEM;
RESULT.QUYDOIDIEM_THANH_TIEN = QUYDOIDIEM_THANH_TIEN;
}
}
}
}
catch
{
NotificationLauncher.ShowNotificationWarning("THÔNG BÁO", "KHÔNG TÌM THẤY THÔNG TIN HẠNG KHÁCH HÀNG", 1, "0x1", "0x8", "normal");
}
finally
{
connection.Close();
connection.Dispose();
}
}
}
catch (Exception ex)
{
WriteLogs.LogError(ex);
NotificationLauncher.ShowNotificationError("Thông báo", "Xảy ra lỗi", 1, "0x1", "0x8", "normal");
}
return(RESULT);
}
public static List <KHACHHANG_DTO> TIMKIEM_KHACHHANG_FROM_ORACLE(string P_KEYSEARCH, int P_USE_TIMKIEM_ALL, int P_DIEUKIEN_TIMKIEM, string UNITCODE)
{
List <KHACHHANG_DTO> _LST_KHACHHANG_DTO = new List <KHACHHANG_DTO>();
try
{
using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["ERBusConnection"].ConnectionString))
{
try
{
connection.Open();
if (connection.State == ConnectionState.Open)
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;
cmd.InitialLONGFetchSize = 1000;
cmd.CommandText = "TIMKIEM_KHACHHANG";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("P_KEYSEARCH", OracleDbType.Varchar2).Value = P_KEYSEARCH;
cmd.Parameters.Add("P_UNITCODE", OracleDbType.Varchar2).Value = UNITCODE;
cmd.Parameters.Add("P_USE_TIMKIEM_ALL", OracleDbType.Int32).Value = P_USE_TIMKIEM_ALL;
cmd.Parameters.Add("P_DIEUKIEN_TIMKIEM", OracleDbType.Int32).Value = P_DIEUKIEN_TIMKIEM;
cmd.Parameters.Add("CURSOR_RESULT", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
OracleDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
KHACHHANG_DTO _KHACHHANG_DTO = new KHACHHANG_DTO();
_KHACHHANG_DTO.MAKHACHHANG = dataReader["MAKHACHHANG"] != null ? dataReader["MAKHACHHANG"].ToString().Trim() : "";
_KHACHHANG_DTO.TENKHACHHANG = dataReader["TENKHACHHANG"] != null ? dataReader["TENKHACHHANG"].ToString().Trim() : "";
_KHACHHANG_DTO.DIACHI = dataReader["DIACHI"] != null ? dataReader["DIACHI"].ToString().Trim() : "";
_KHACHHANG_DTO.DIENTHOAI = dataReader["DIENTHOAI"] != null ? dataReader["DIENTHOAI"].ToString().Trim() : "";
_KHACHHANG_DTO.CANCUOC_CONGDAN = dataReader["CANCUOC_CONGDAN"] != null ? dataReader["CANCUOC_CONGDAN"].ToString().Trim() : "";
_KHACHHANG_DTO.NGAYSINH = dataReader["NGAYSINH"] != DBNull.Value ? DateTime.Parse(dataReader["NGAYSINH"].ToString()) : (DateTime?)null;
_KHACHHANG_DTO.NGAYDACBIET = dataReader["NGAYDACBIET"] != DBNull.Value ? DateTime.Parse(dataReader["NGAYDACBIET"].ToString()) : (DateTime?)null;
_KHACHHANG_DTO.MATHE = dataReader["MATHE"] != null ? dataReader["MATHE"].ToString().Trim() : "";
_KHACHHANG_DTO.HANGKHACHHANG = dataReader["MAHANG"] != null ? dataReader["MAHANG"].ToString().Trim() : "";
decimal SODIEM = 0;
if (dataReader["SODIEM"] != null)
{
decimal.TryParse(dataReader["SODIEM"].ToString(), out SODIEM);
}
_KHACHHANG_DTO.SODIEM = SODIEM;
decimal TONGTIEN = 0;
if (dataReader["TONGTIEN"] != null)
{
decimal.TryParse(dataReader["TONGTIEN"].ToString(), out TONGTIEN);
}
_KHACHHANG_DTO.TONGTIEN = TONGTIEN;
_KHACHHANG_DTO.DIENGIAI = dataReader["DIENGIAI"] != null ? dataReader["DIENGIAI"].ToString().Trim() : "";
_LST_KHACHHANG_DTO.Add(_KHACHHANG_DTO);
}
}
}
}
catch
{
NotificationLauncher.ShowNotificationWarning("THÔNG BÁO", "KHÔNG TÌM THẤY THÔNG TIN KHÁCH HÀNG", 1, "0x1", "0x8", "normal");
}
finally
{
connection.Close();
connection.Dispose();
}
}
}
catch (Exception ex)
{
WriteLogs.LogError(ex);
NotificationLauncher.ShowNotificationError("Thông báo", "Xảy ra lỗi", 1, "0x1", "0x8", "normal");
}
return(_LST_KHACHHANG_DTO);
}
public override Task StopAsync(CancellationToken cancellationToken)
{
_dependecy.RemoveRegistration(_connection);
_connection?.Dispose();
return(base.StopAsync(cancellationToken));
}
public void CerrarConexion(OracleCommand cmd)
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
public static bool ListenPipeMethod()
{
#if DEBUG
Console.WriteLine("Вошли в метод ListenPipeMethod");
#endif
bool SuccessFlag = false;
OracleConnection conn = DBUtils.GetDBConnection();
#if DEBUG
Console.WriteLine("Инициализация именованного канала");
#endif
OracleCommand cmd = new OracleCommand(procedure, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("pipe_name", OracleDbType.Varchar2).Value = pipe_name;
cmd.Parameters.Add("TIMEOUT", OracleDbType.Int32).Value = pipe_timeout_delay; // длительность прослушки пайпа
cmd.Parameters.Add("msg", OracleDbType.Varchar2).Value = null;
cmd.Parameters.Add(new OracleParameter("rc", OracleDbType.Int32));
cmd.Parameters.Add(new OracleParameter("otext", OracleDbType.Varchar2, 4000));
cmd.Parameters["pipe_name"].Direction = System.Data.ParameterDirection.Input;
cmd.Parameters["TIMEOUT"].Direction = System.Data.ParameterDirection.Input;
cmd.Parameters["msg"].Direction = System.Data.ParameterDirection.Input;
cmd.Parameters["rc"].Direction = System.Data.ParameterDirection.Output;
cmd.Parameters["otext"].Direction = System.Data.ParameterDirection.Output;
try
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
#if DEBUG
Console.WriteLine($"\nСоединяю с пайпом \"{pipe_name}\". Ждём {pipe_timeout_delay}c до таймаута");
#endif
cmd.ExecuteNonQuery();
int rc = Convert.ToInt32(cmd.Parameters["rc"].Value.ToString());
string otext = cmd.Parameters["otext"].Value.ToString();
if (rc == 0)
{
Console.WriteLine($"Пайп принял сигнал. Переход к считыванию данных");
}
#if DEBUG
if (rc == 0)
{
System.Console.Beep();
}
Console.WriteLine(cmd.Parameters["rc"].ParameterName + " : " + rc.ToString());
Console.WriteLine(cmd.Parameters["otext"].ParameterName + " : " + otext);
#endif
SuccessFlag = (rc == 0)
? true
: false;
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex);
Console.WriteLine(ex.StackTrace);
}
finally
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
conn?.Dispose();
}
return(SuccessFlag);
}
protected void FillLoad()
{
DataTable dtLoanList = new DataTable();
DataTable dtListOD = new DataTable();
if (Session["constring"] != null)
{
oraConn = new OracleConnection(Session["constring"].ToString());
if (oraConn.State != ConnectionState.Open)
{
oraConn.Open();
}
}
try
{
Bal.Gen objFillFromDate = new Bal.Gen();
objFillFromDate.Call_Date(ddFromDay, ddFromMonth, ddFromYear);
Bal.Gen objFillDate = new Bal.Gen();
objFillDate.Call_Date(ddlPostingDay, ddlPostingMonth, ddlPostingYear);
strSql = objLoanInsurance.BranchFill();
objDataFetch.FillDdl(strSql, ddlBranch, "BRAN_SNO", "BRAN_NAME");
ddlBranch.SelectedValue = Session["BranchLogin"].ToString();
PanelOD.Visible = false;
strSql = objLoanInsurance.LoanFill(ddlBranch.SelectedValue);
OracleDataAdapter oDaLoan = new OracleDataAdapter(strSql, oraConn);
oDaLoan.Fill(dtLoanList);
if (dtLoanList.Rows.Count <= 0)
{
ClientScript.RegisterStartupScript(Page.GetType(), "VALIDATION", "<script language='javascript'>alert ('No Loan Scheme Found...') </script>");
return;
}
chkList.DataSource = dtLoanList;
chkList.DataValueField = "LSCH_ABBREV";
chkList.DataTextField = "LSCH_DES";
chkList.DataBind();
oraConn.Close();
strSql = objLoanInsurance.ODFill(ddlBranch.SelectedValue);
OracleDataAdapter oDaOD = new OracleDataAdapter(strSql, oraConn);
oDaOD.Fill(dtListOD);
if (dtListOD.Rows.Count <= 0)
{
ClientScript.RegisterStartupScript(Page.GetType(), "VALIDATION", "<script language='javascript'>alert ('No OD Scheme Found...') </script>");
return;
}
chkListOd.DataSource = dtListOD;
chkListOd.DataValueField = "ODST_ABBR";
chkListOd.DataTextField = "ODST_NAME";
chkListOd.DataBind();
}
catch (Exception ex)
{
objLog.WriteLog("LoanInsrnceAutoPosting Exception in FillLoad : " + ex.Message);
ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('" + ex.Message + "')</script>");
}
finally
{
if (oraConn.State == ConnectionState.Open)
{
oraConn.Close();
}
oraConn.Dispose();
dtLoanList.Dispose();
dtListOD.Dispose();
OracleConnection.ClearAllPools();
}
}
void Close()
{
con.Close();
con.Dispose();
}
public IHttpActionResult GetNhomQuyenChuaCauHinh(string userName)
{
var result = new TransferObj <List <NhomQuyenViewModel.Dto> >();
List <NhomQuyenViewModel.Dto> listNhomQuyen = new List <NhomQuyenViewModel.Dto>();
if (!string.IsNullOrEmpty(userName))
{
try
{
string stringConnect = ConfigurationManager.ConnectionStrings["ERBusConnection"].ConnectionString;
using (OracleConnection connection = new OracleConnection(stringConnect))
{
try
{
connection.Open();
if (connection.State == ConnectionState.Open)
{
OracleCommand command = new OracleCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = string.Format(@"SELECT
a.ID,
a.MANHOMQUYEN,
a.TENNHOMQUYEN
FROM
NHOMQUYEN a
WHERE a.MANHOMQUYEN NOT IN (
SELECT
b.MANHOMQUYEN
FROM
NGUOIDUNG_NHOMQUYEN b
WHERE
b.USERNAME = :USERNAME)
");
command.Parameters.Add(@"USERNAME", OracleDbType.NVarchar2, 50).Value = userName;
OracleDataReader dataReader = command.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
NhomQuyenViewModel.Dto row = new NhomQuyenViewModel.Dto();
if (dataReader["ID"] != null)
{
row.ID = dataReader["ID"].ToString();
}
if (dataReader["MANHOMQUYEN"] != null)
{
row.MANHOMQUYEN = dataReader["MANHOMQUYEN"].ToString();
}
if (dataReader["TENNHOMQUYEN"] != null)
{
row.TENNHOMQUYEN = dataReader["TENNHOMQUYEN"].ToString();
}
listNhomQuyen.Add(row);
}
}
dataReader.Close();
}
}
catch (Exception ex)
{
}
finally
{
connection.Close();
connection.Dispose();
}
}
if (listNhomQuyen.Count > 0)
{
result.Status = true;
result.Data = listNhomQuyen;
result.Message = "Thành công";
}
else
{
result.Status = false;
result.Data = null;
result.Message = "Không có dữ liệu";
}
}
catch (Exception ex)
{
result.Status = false;
result.Message = ex.Message;
}
}
return(Ok(result));
}
public Boolean cargar_operador(String id_operador)
{
OracleConnection CnOra = new OracleConnection(conexion_cliente_oracle);
OracleCommand cmdAcceso = new OracleCommand("PK_OPERADOR.CARGAR_DATOS_OPERADOR", CnOra);
OracleDataAdapter da = new OracleDataAdapter(cmdAcceso);
PRO.agrega_parametro_sp(da, "o_cursor", OracleType.Cursor, ParameterDirection.Output, "");
PRO.agrega_parametro_sp(da, "i_id_operador", OracleType.Number, ParameterDirection.Input, id_operador);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable dtOrdenes = new DataTable();
try
{
CnOra.Open();
da.Fill(dtOrdenes);
CnOra.Close();
CnOra.Dispose();
lb_mensaje.Text = "";
}
catch (Exception ex)
{
lb_mensaje.ForeColor = System.Drawing.Color.Red;
lb_mensaje.Text = ex.Message;
return(false);
}
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("NOMBRE", typeof(String)));
dt.Columns.Add(new DataColumn("APELLIDO", typeof(String)));
dt.Columns.Add(new DataColumn("COD_OPERADOR", typeof(String)));
dt.Columns.Add(new DataColumn("CLAVE_OPERADOR", typeof(String)));
dt.Columns.Add(new DataColumn("CLAVE_ADMIN", typeof(String)));
dt.Columns.Add(new DataColumn("ESTADO", typeof(String)));
dt.Columns.Add(new DataColumn("REGION", typeof(String)));
dt.Columns.Add(new DataColumn("CONTRATISTA", typeof(String)));
dt.Columns.Add(new DataColumn("BODEGA", typeof(String)));
dt.Columns.Add(new DataColumn("DEVOLUCION_ORDENES", typeof(String)));
DataRow dr;
int total = 0;
foreach (DataRow drOrdenItem in dtOrdenes.Rows)
{
dr = dt.NewRow();
tb_nombres.Text = drOrdenItem["NOMBRE"].ToString();
tb_apellidos.Text = drOrdenItem["APELLIDO"].ToString();
tb_codigo.Text = drOrdenItem["COD_OPERADOR"].ToString();
rescate_old_codigo.Text = tb_codigo.Text; //RESCATAMOS CODIGO OPERADOR.
tb_clave_operador.Text = drOrdenItem["CLAVE_OPERADOR"].ToString();
tb_clave_admin.Text = drOrdenItem["CLAVE_ADMIN"].ToString();
if (drOrdenItem["ESTADO"].ToString() == "")
{
drOrdenItem["ESTADO"] = "0";
}
cb_estados.SelectedValue = drOrdenItem["ESTADO"].ToString();
if (drOrdenItem["CONTRATISTA"].ToString() == "")
{
drOrdenItem["CONTRATISTA"] = "0";
}
cb_contratistas.SelectedValue = drOrdenItem["CONTRATISTA"].ToString();
if (drOrdenItem["REGION"].ToString() == "")
{
drOrdenItem["REGION"] = "0";
}
cb_regiones.SelectedValue = drOrdenItem["REGION"].ToString();
if (drOrdenItem["BODEGA"].ToString() == "")
{
drOrdenItem["BODEGA"] = "0";
}
cb_bodegas.SelectedValue = drOrdenItem["BODEGA"].ToString();
rescate_old_bodega.Text = cb_bodegas.SelectedValue;
try
{
if (drOrdenItem["DEVOLUCION_ORDENES"].ToString().Equals("0"))
{
rb_autoriza.Checked = false;
}
else
{
rb_autoriza.Checked = true;
}
}
catch (Exception ex)
{
rb_autoriza.Checked = false;
}
dt.Rows.Add(dr);
total = total + 1;
}
return(true);
}
/// <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;
}
public Dictionary <string, string> ExecutaProcedure(string nomeProc, List <ParamProcedure> parametros)
{
OracleConnection cn = new OracleConnection();
OracleCommand dbCommand = cn.CreateCommand();
cn = GetConnection();
dbCommand.CommandText = nomeProc;
dbCommand.CommandType = CommandType.StoredProcedure;
// Parâmetros
foreach (ParamProcedure item in parametros)
{
if (item.direcao == ParameterDirection.Output)
{
dbCommand.Parameters.Add(item.nome, item.tipo, 2000).Direction = ParameterDirection.Output;
}
else
{
if (item.tipo == OracleDbType.Double || item.tipo == OracleDbType.Decimal)
{
dbCommand.Parameters.Add(item.nome, item.tipo).Value = Convert.ToDecimal(item.valor);
}
else if (item.tipo == OracleDbType.Date)
{
DateTime dt = DateTime.ParseExact(item.valor, "dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture);
dbCommand.Parameters.Add(item.nome, item.tipo).Value = dt;
}
else if (item.tipo == OracleDbType.Int16 || item.tipo == OracleDbType.Int32)
{
dbCommand.Parameters.Add(item.nome, item.tipo).Value = Convert.ToInt32(item.valor);
}
else
{
dbCommand.Parameters.Add(item.nome, item.tipo).Value = item.valor;
}
}
//Uteis.Log("ExecutaProcedure", "Parametro: " + item.nome.ToString() + " valor: " + item.valor.ToString() + " direcao: " + item.direcao);
}
//Uteis.Log("ExecutaProcedure", "Parametros count: " + dbCommand.Parameters.Count.ToString());
Dictionary <string, string> ret = new Dictionary <string, string>();
//Criar o tratamento para
try
{
dbCommand.Connection = cn;
cn.Open();
dbCommand.ExecuteNonQuery();
// Parâmetros de retorno
foreach (ParamProcedure item in parametros)
{
if (item.direcao == ParameterDirection.Output)
{
ret.Add(item.nome, dbCommand.Parameters[item.nome].Value.ToString());
}
}
}
catch (Exception ex)
{
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
dbCommand.Dispose();
cn.Dispose();
throw ex;
}
finally
{
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
dbCommand.Dispose();
cn.Dispose();
}
return(ret);
}
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 void Dispose()
{
Close();
conn.Dispose();
}
//Cargamos los usuarios del sistema según su filtro de búsqueda, el campo para buscar un usuario en específico y de acuerdo al perfil del usuario logeado.
public void carga_grilla(String busqueda, String filtro, String usuario, String contratista)
{
busqueda = busqueda.Trim(); //quitamos los espacios laterales en blanco.
OracleConnection CnOra = new OracleConnection(conexion_cliente_oracle);
OracleCommand cmdAcceso = new OracleCommand("PK_OPERADOR.CARGAR_OPERADORES", CnOra);
OracleDataAdapter da = new OracleDataAdapter(cmdAcceso);
PRO.agrega_parametro_sp(da, "o_cursor", OracleType.Cursor, ParameterDirection.Output, "");
PRO.agrega_parametro_sp(da, "i_busqueda", OracleType.NVarChar, ParameterDirection.Input, busqueda);
PRO.agrega_parametro_sp(da, "i_filtro", OracleType.Number, ParameterDirection.Input, filtro);
PRO.agrega_parametro_sp(da, "i_id_usuario", OracleType.Number, ParameterDirection.Input, usuario);
PRO.agrega_parametro_sp(da, "i_id_contratista", OracleType.Number, ParameterDirection.Input, contratista);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable dtOrdenes = new DataTable();
try
{
CnOra.Open();
da.Fill(dtOrdenes);
CnOra.Close();
CnOra.Dispose();
lb_mensaje.Text = "";
}
catch (Exception ex)
{
lb_mensaje.ForeColor = System.Drawing.Color.Red;
lb_mensaje.Text = ex.Message;
}
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("ID_OPERADOR", typeof(String)));
dt.Columns.Add(new DataColumn("NOMBRE_COMPLETO", typeof(String)));
dt.Columns.Add(new DataColumn("COD_OPERADOR", typeof(String)));
dt.Columns.Add(new DataColumn("CLAVE_OPERADOR", typeof(String)));
dt.Columns.Add(new DataColumn("CLAVE_ADMIN", typeof(String)));
dt.Columns.Add(new DataColumn("ESTADO", typeof(String)));
dt.Columns.Add(new DataColumn("REGION", typeof(String)));
dt.Columns.Add(new DataColumn("CONTRATISTA", typeof(String)));
dt.Columns.Add(new DataColumn("BODEGA", typeof(String)));
DataRow dr;
int total = 0;
foreach (DataRow drOrdenItem in dtOrdenes.Rows)
{
dr = dt.NewRow();
dr["ID_OPERADOR"] = drOrdenItem["ID_OPERADOR"].ToString();
dr["NOMBRE_COMPLETO"] = drOrdenItem["NOMBRE_COMPLETO"].ToString();
dr["COD_OPERADOR"] = drOrdenItem["COD_OPERADOR"].ToString();
dr["CLAVE_OPERADOR"] = drOrdenItem["CLAVE_OPERADOR"].ToString();
dr["CLAVE_ADMIN"] = drOrdenItem["CLAVE_ADMIN"].ToString();
dr["ESTADO"] = drOrdenItem["ESTADO"].ToString();
dr["REGION"] = drOrdenItem["REGION"].ToString();
dr["CONTRATISTA"] = drOrdenItem["CONTRATISTA"].ToString();
dr["BODEGA"] = drOrdenItem["BODEGA"].ToString();
dt.Rows.Add(dr);
total = total + 1;
}
DataView MydataView;
MydataView = new DataView(dt);
gridOrden.DataSource = MydataView;
gridOrden.DataBind();
gridOrden.Columns[0].Visible = false;
lb_contador.Text = total + " operador(es) encontrado(s)";
GridViewRow gvRow;
for (int i = 0; i < gridOrden.Rows.Count; i++)
{
gvRow = gridOrden.Rows[i];
if (gvRow.Cells[5].Text.Equals("HABILITADO"))
{
gvRow.Cells[5].BackColor = System.Drawing.Color.LightGreen;
}
else
{
gvRow.Cells[5].BackColor = System.Drawing.Color.IndianRed;
}
}
}
public ActionResult Login(string username, string password)
{
OracleConnection conn = new OracleConnection(oradb);
try
{
conn.Open();
string selectStr = "select * from fake_user where username ='******' and password = '******'";
OracleCommand cmd = new OracleCommand(selectStr, conn);
OracleDataReader dr = cmd.ExecuteReader();
bool findUser = dr.Read();
if (findUser)
{
string dbusername = dr.GetOracleString(0).ToString();
string dbnickname = dr.GetOracleString(2).ToString();
string dbheadimg = dr.GetOracleString(10).ToString();
Session.Add("username", username);
Session.Add("nickname", dbnickname);
Session.Add("headimg", dbheadimg);
dbug("Login", "success");
dr.Close();
return RedirectToAction("UserPage", "Account");
}
else
{
dr.Close();
return View();
}
}
catch (OracleException ex) // catches only Oracle errors
{
switch (ex.Number)
{
case 1:
//return "Error attempting to insert duplicate data.";
case 12545:
//return "The database is unavailable.";
default:
return View();
//return "Database error: " + ex.Message.ToString();
}
}
catch (Exception ex) // catches any error not previously caught
{
return View();
//return "other exception: " + ex.Message.ToString();
}
finally {
conn.Close();
conn.Dispose();
}
}
//删除照片
public string DeletePhoto(string deleteid)
{
OracleConnection conn = new OracleConnection(oradb);
try
{
conn.Open();
string deleteStr = "delete from photo "
+"where photoid = '"
+deleteid + "'";
OracleCommand cmd = new OracleCommand(deleteStr, conn);
int insertedLines = cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
return "new PHOTO:" + (Session["username"] as string);
}
catch (Exception ex)
{
return "exception";
}
}
public void SendLocation(double longitude, double latitude)
{
OracleConnection conn = new OracleConnection(oradb);
try{
conn.Open();
string selectStr = "select * from location where username = '******'";
Debug.WriteLine(selectStr);
OracleCommand selectCMD = new OracleCommand(selectStr, conn);
OracleDataReader dr = selectCMD.ExecuteReader();
if (dr.Read())
{
string loginuser = Session["username"] as string;
//string delStr = "delete from location where username = '******'";
/*
string updateStr = "update location set longitude = " + longitude + ", latitude = " + latitude + " where username = "******"delete from location where username = '******'";
OracleCommand cmd = new OracleCommand(delStr, conn);
int deletedLines = cmd.ExecuteNonQuery();
Debug.WriteLine("[DELETE]");
string insertStr = "insert into location(username, longitude, latitude) values('"
+ (Session["username"] as string)
+ "', "
+ longitude
+ ", "
+ latitude
+ ")";
OracleCommand insertCMD = new OracleCommand(insertStr, conn);
int insertedLines = insertCMD.ExecuteNonQuery();
Debug.WriteLine("Insert: " + insertStr + " " + insertedLines);
conn.Close();
conn.Dispose();
//delete
//delete from location where username = '******';
//string deleteStr = "delete from location where username = '******'";
/*
string deleteStr = "delete from location where username = '******'";
Debug.WriteLine("1.Delete: " + deleteStr);
OracleCommand deleteCMD = new OracleCommand(deleteStr, conn);
deleteCMD.ExecuteNonQueryAsync();
deleteCMD.Dispose();
Debug.WriteLine("2.Delete: " + deleteStr);
*/
//update
//update location set longitude = 333, latitude = 222 where username = '******';
/*
OracleCommand updateCMD = conn.CreateCommand();
updateCMD.CommandType = System.Data.CommandType.Text;
updateCMD.CommandText = "update location set longitude=111111,latitude=111111 where username='******'";
updateCMD.ExecuteNonQuery();
Debug.WriteLine("update: " + updateCMD.CommandText);
*/
// Debug.WriteLine("3.Update: " + updateStr);
}
else
{
//insert new
//insert into location(username, longitude, latitude) values('123', 123, 234)
string insertStr = "insert into location(username, longitude, latitude) values('"
+ (Session["username"] as string)
+ "', "
+ longitude
+ ", "
+ latitude
+ ")";
OracleCommand insertCMD = new OracleCommand(insertStr, conn);
int insertedLines = insertCMD.ExecuteNonQuery();
Debug.WriteLine("Insert: " + insertStr + " " + insertedLines);
conn.Close();
conn.Dispose();
}
}
catch (Exception ex)
{
Debug.WriteLine("[SEND LOCATION Exception: " + ex);
}
finally {
}
Debug.WriteLine("Longitude: " + longitude + ", Latitude: " + latitude);
}
/// <summary>
/// Processes the m71 message.
/// </summary>
/// <returns>A string collection with the data to be returned</returns>
public System.Collections.Specialized.StringCollection Process()
{
StringCollection res = null;
OracleConnection oraDBConn = null;
OracleCommand oraCmd = null;
OracleDataReader dr = null;
ILogger logger = null;
bool bRes = false;
try
{
Database d = OPS.Components.Data.DatabaseFactory.GetDatabase();
logger = DatabaseFactory.Logger;
System.Data.IDbConnection DBCon = d.GetNewConnection();
oraDBConn = (OracleConnection)DBCon;
oraDBConn.Open();
if (oraDBConn.State == System.Data.ConnectionState.Open)
{
oraCmd = new OracleCommand();
oraCmd.Connection = (OracleConnection)oraDBConn;
oraCmd.CommandText = string.Format("insert into INCIDENTS (INCID_TYPE_ID," +
"INCID_UNI_ID," +
"INCID_PLAT_ID," +
"INCID_BYC_ID," +
"INCID_BUSER_ID," +
"INCID_DATE,INCID_EX_TRIGGER) " +
"values ({0}, {1}, {2}, null, {3}, to_date('{4}', 'HH24MISSDDMMYY'),1)",
_typeId, _unitId, _platformId, _userId, _date);
if (oraCmd.ExecuteNonQuery() == 1)
{
bRes = true;
}
}
if (bRes)
{
res = ReturnAck(AckMessage.AckTypes.ACK_PROCESSED);
}
else
{
if (logger != null)
{
logger.AddLog("[Msg71:Process]: Error.", LoggerSeverities.Error);
}
res = ReturnNack(NackMessage.NackTypes.NACK_ERROR_BECS);
}
}
catch (Exception e)
{
if (logger != null)
{
logger.AddLog("[Msg71:Process]: Error: " + e.Message, LoggerSeverities.Error);
}
res = ReturnNack(NackMessage.NackTypes.NACK_ERROR_BECS);
}
finally
{
if (dr != null)
{
dr.Close();
dr.Dispose();
dr = null;
}
if (oraCmd != null)
{
oraCmd.Dispose();
oraCmd = null;
}
if (oraDBConn != null)
{
oraDBConn.Close();
oraDBConn.Dispose();
oraDBConn = null;
}
}
return(res);
}
/// <summary>
/// 销毁数据库连接
/// </summary>
public static void DisConnect()
{
conn.Close();
conn.Dispose();
//XtraMessageBox.Show("我被销毁!");
}
private void loadEmployeeData(string epf)
{
OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["CGConnectionString"].ToString());
OracleDataReader dr;
con.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
String selectQuery = "";
selectQuery = " SELECT T.EPF_NO,T.NAME,T.SEX,T.MARITIAL_STATUS,T.BRANCH_CODE,T.RES_ADDRESS_1,T.RES_ADDRESS_2,T.RES_ADDRESS_3,T.NAME_OF_SPOUSE,T.NO_OF_CHILDREN " +
" FROM WS_SURVEY_MAIN T " +
" WHERE T.EPF_NO=" + epf + "";
cmd.CommandText = selectQuery;
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
txtEPF.Text = dr[0].ToString();
txtName.Text = dr[1].ToString();
if (dr[2].ToString() == "M")
{
rbtnMale.Checked = true;
}
else if (dr[2].ToString() == "F")
{
rbtnFemale.Checked = true;
}
if (dr[3].ToString() == "M")
{
rbtnMaritialStatusMarried.Checked = true;
pnlFamilyMemberDetails.Visible = true;
}
else if (dr[3].ToString() == "S")
{
rbtnMaritialStatusSingle.Checked = true;
}
string a = dr[4].ToString();
ddlBranch.SelectedValue = dr[4].ToString();
txtAddressLine1.Text = dr[5].ToString();
txtAddressLine2.Text = dr[6].ToString();
txtAddressLine3.Text = dr[7].ToString();
txtNameOfSpouse.Text = dr[8].ToString();
txtNoOfChildren.Text = dr[9].ToString();
if (dr[9].ToString() != "")
{
if (Convert.ToInt32(dr[9].ToString()) > 0)
{
pnlChildren.Visible = true;
}
}
}
dr.Close();
dr.Dispose();
cmd.Dispose();
con.Close();
con.Dispose();
}
public void killConnection()
{
con.Close();
con.Dispose();
}
} //ConsultarOrdenesUsuario
public List<EstadoOrdenDTO> getEstadoOrden()
{
List<EstadoOrdenDTO> lstEstados = new List<EstadoOrdenDTO>();
EstadoOrdenDTO estado;
Parametros p = new Parametros();
using (OracleConnection con = new OracleConnection(p.oracleConnString().ToString()))
{
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT IDESTADOORDEN, NOMBREESTADOORDEN FROM ESTADO_ORDEN ORDER BY IDESTADOORDEN ASC";
using (OracleDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
estado = new EstadoOrdenDTO();
estado.idEstadoOrden = reader.GetInt32(0);
estado.nombreEstadoOrden = reader.GetString(1);
lstEstados.Add(estado);
}
}
con.Close();
con.Dispose();
}
return lstEstados;
} // getEstadoOrden()
/// <summary>
/// 执行数据库查询操作,返回DataTable类型的结果集
/// </summary>
/// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param>
/// <param name="commandParameters">命令参数集合</param>
/// <returns>当前查询操作返回的DataTable类型的结果集</returns>
public static DataTable ExecuteDataTable(string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand command = new OracleCommand();
OracleConnection connection = new OracleConnection(connstr);
DataTable table = null;
try
{
PrepareCommand(command, connection, null, CommandType.Text, cmdText, commandParameters);
OracleDataAdapter adapter = new OracleDataAdapter();
adapter.SelectCommand = command;
table = new DataTable();
adapter.Fill(table);
command.Parameters.Clear();
}
catch
{
throw;
}
finally
{
command.Dispose();
connection.Close();
connection.Dispose();
}
return table;
}
public string Forget(string username)
{
OracleConnection conn = new OracleConnection(oradb);
try
{
conn.Open();
string selectStr = "select email,password "
+ "from fake_user "
+ "where userName = '******'"; //忘记密码时没有登录
OracleCommand cmd = new OracleCommand(selectStr, conn);
int insertedLines = cmd.ExecuteNonQuery();
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
string TO = dr.GetOracleString(0).ToString(); //目标地址
string PASSWORD = dr.GetOracleString(1).ToString();
conn.Close();
conn.Dispose();
string smtpServer = "smtp.qq.com"; //SMTP服务器
string mailFrom = "*****@*****.**"; //登陆用户名
string userPassword = "******";//登陆密码
// 邮件服务设置
SmtpClient smtpClient = new SmtpClient();
smtpClient.DeliveryMethod = SmtpDeliveryMethod.Network;//指定电子邮件发送方式
smtpClient.Host = smtpServer; //指定SMTP服务器
smtpClient.Port = 25;
smtpClient.Credentials = new System.Net.NetworkCredential(mailFrom, userPassword);//用户名和密码
// 发送邮件设置
MailMessage mailMessage = new MailMessage(mailFrom, TO); // 发送人和收件人
mailMessage.Subject = "Fakebook密码找回服务";//主题
mailMessage.Body = "密码找回成功！ " + "用户" + username + " 您的密码是：" + PASSWORD;//内容
mailMessage.BodyEncoding = Encoding.UTF8;//正文编码
mailMessage.IsBodyHtml = true;//设置为HTML格式
mailMessage.Priority = MailPriority.Low;//优先级
//System.Diagnostics.Debug.WriteLine("haiwefasong ,please waiting-----");
smtpClient.Send(mailMessage); // 发送邮件
// Console.WriteLine("success!..");
return "EMAIL SENT:";
}
catch (Exception ex)
{
return "exception";
}
}
protected void Page_Load(object sender, EventArgs e)
{
objLog.WriteLog("DBTL_LogDisp : display function : ");
if (Session.Contents.Count == 0)
{
Response.Redirect("ShowMessage.aspx?msg=" + GlobalConstants.msg);
}
else
{
try
{
if (!IsPostBack)
{
OracleConnection oraConn = new OracleConnection(Session["constring"].ToString());
if (oraConn.State != ConnectionState.Open)
{
oraConn.Open();
}
ViewState["Pagename"] = null;
if (Session["Display"] != null)
{
objLog.WriteLog("DBTL_LogDisp :-Session Display");
ViewState["Pagename"] = "DBTL_Log";
dtDisplay = (DataTable)Session["Display"];
}
sSql = "Select SITP_TEXTVALUE From Siteparameters Where SITP_NAME = 'COMPANY NAME'";
OracleDataAdapter odaC = new OracleDataAdapter(sSql, oraConn);
odaC.Fill(dtcom);
if (dtcom.Rows.Count != 0)
{
strBnkName = dtcom.Rows[0]["SITP_TEXTVALUE"].ToString();
}
lblBank.Text = strBnkName;
if (Session["LoginBranchName"] != null)
{
lblBranch.Text = Session["LoginBranchName"].ToString();
}
if (Session["Heading"] != null)
{
lblRptHead.Text = Session["Heading"].ToString();
}
if (Session["NonCredit"] != null)
{
lblNonCre.Text = Session["NonCredit"].ToString();
}
if (Session["Credit"] != null)
{
lblCredit.Text = Session["Credit"].ToString();
}
if (dtDisplay.Rows.Count > 0)
{
gvDBTL.DataSource = dtDisplay;
gvDBTL.DataBind();
Session["datatable"] = dtDisplay;
Session["Display"] = null;
objFrmPt.FormatGridView(gvDBTL);
GridPrint(gvDBTL);
}
}
}
catch (Exception ex)
{
objLog.WriteLog("DBTL_LogDisp Exception in PageLoad : " + ex.Message);
ClientScript.RegisterStartupScript(Page.GetType(), "VALIDATION", "<script language='javascript'>alert('" + ex.Message + "')</script>");
}
finally
{
if (oraConn.State == ConnectionState.Open)
{
oraConn.Close();
}
oraConn.Dispose();
OracleConnection.ClearAllPools();
}
}
}
public string Register(string username, string password, string nickname, string email)
{
OracleConnection conn = new OracleConnection(oradb);
try
{
conn.Open();
string insertStr = "insert into fake_user(username, password, nickname, email, state) values('"
+ username + "', '"
+ password + "', '"
+ nickname + "', '"
+ email + "', 2)";
OracleCommand cmd = new OracleCommand(insertStr, conn);
//OracleDataReader dr = cmd.ExecuteReader();
//dr.Read();
int insertedLines = cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
ViewBag.username = username;
ViewBag.password = password;
Response.Redirect("Login");
// return ("insertedLines: " + insertedLines);
}
catch (OracleException ex) // catches only Oracle errors
{
switch (ex.Number)
{
case 1:
return "Error attempting to insert duplicate data.";
case 12545:
return "The database is unavailable.";
default:
return "Database error: " + ex.Message.ToString();
}
}
catch (Exception ex) // catches any error not previously caught
{
return "other expectation";
}
Console.WriteLine("success");
return "nothing";
}
public void Dispose()
{
_dbConnection?.Dispose();
}
public ActionResult UserPage()
{
string currentUser = Session["username"] as string;
if (currentUser != null && !currentUser.Equals(""))
{
ViewBag.username = currentUser;
ViewBag.headimg = "~/Portraits/" + (Session["headimg"] as string);
OracleConnection conn = new OracleConnection(oradb);
string friendGetStr = "select u.username, u.nickname from fake_user u, friend f where f.username = '******' and f.friendname = u.username";
OracleCommand cmd = new OracleCommand(friendGetStr, conn);
List<FriendModel> friendList = new List<FriendModel>();
try
{
conn.Open();
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read()) {
string _id = dr.GetOracleString(0).ToString();
string nickname = dr.GetOracleString(1).ToString();
string json = "{\"_id\":\""
+ _id
+ "\",\"nickname\":\""
+ nickname
+ "\"}";
FriendModel model = new FriendModel();
model._id = _id;
model.nickname = nickname;
Debug.WriteLine("Friend: " + json);
friendList.Add(model);
}
ViewData["friendList"] = friendList;
}
catch (Exception ex)
{
Debug.WriteLine("Exception: " + ex);
}
finally {
conn.Close();
conn.Dispose();
}
return View();
}
else {
return RedirectToAction("Login", "Account");
}
}
public void CloseConnect()
{
_con.Close();
_con.Dispose();
}
/// <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;
}
public bool SelectFromDbMethod()
{
DbEntity result;
PushNotificationsLists.ListNotificationsForSend.Clear();
try
{
OracleConnection conn = DBUtils.GetDBConnection();
// Создать объект Command.
OracleCommand cmd = new OracleCommand();
// Сочетать Command с Connection.
cmd.Connection = conn;
cmd.CommandText = procedure1;
try
{
if (conn?.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
int numberOfColumns = reader.FieldCount;
while (reader.Read())
{
result = new DbEntity();
for (int i = 0; i < numberOfColumns; i++)
{
string nameOfColumn = reader.GetName(i);
int indexOfFiled = reader.GetOrdinal(nameOfColumn);
object fieldValue = reader.GetValue(indexOfFiled);
switch (nameOfColumn)
{
case "MSG_ID": result.Message_id = fieldValue.ToString(); break;
case "MSG_TEXT": result.Message_text = fieldValue.ToString(); break;
case "PUSH_ID": result.Push_id = fieldValue.ToString(); break;
case "PHONE": result.Phone = fieldValue.ToString(); break;
case "SERVICE_ID": result.Service_id = fieldValue.ToString(); break;
case "SERVICE_NAME": result.Service_name = fieldValue.ToString(); break;
case "MSG_TTL": result.Msg_Ttl = Convert.ToInt32(fieldValue); break;
default:
Console.WriteLine($"Неизвестная ошибка. Поле [{nameOfColumn}] не опознано");
return(false);
}
}
PushNotificationsLists.ListNotificationsForSend.Add(result);
}
}
else
{
#if DEBUG
Console.WriteLine("В БД нет новых данных для считывания");
#endif
return(false);
}
}
}
catch (Exception ex)
{
Console.WriteLine("## ERROR: " + ex.Message);
return(false);
}
finally
{
cmd.CommandText = "ROLLBACK";
if (conn?.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
cmd.ExecuteNonQuery();
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
conn?.Dispose();
}
#if DEBUG
Console.WriteLine("\n=====================\r\n============List of Android entities: =========");
foreach (var item in
PushNotificationsLists
.ListNotificationsForSend
.Where(el => el.Service_name == "FCM"))
{
Console.WriteLine("=================");
Console.WriteLine($"{nameof(item.Message_id)} :\t {item.Message_id}");
Console.WriteLine($"{nameof(item.Message_text)} :\t {item.Message_text}");
Console.WriteLine($"{nameof(item.Push_id)} :\t {item.Push_id}");
Console.WriteLine($"{nameof(item.Phone)} :\t {item.Phone}");
Console.WriteLine($"{nameof(item.Service_id)} :\t {item.Service_id}");
Console.WriteLine($"{nameof(item.Service_name)} :\t {item.Service_name}");
Console.WriteLine($"{nameof(item.Msg_Ttl)} :\t {item.Msg_Ttl}");
}
Console.WriteLine("\n=====================\r\n============End of the List of Android ========");
Console.WriteLine("\n=====================\r\n================List of IOs entities: =========");
foreach (var item in
PushNotificationsLists
.ListNotificationsForSend
.Where(el => el.Service_name == "APNS"))
{
Console.WriteLine("=================");
Console.WriteLine($"{nameof(item.Message_id)} :\t {item.Message_id}");
Console.WriteLine($"{nameof(item.Message_text)} :\t {item.Message_text}");
Console.WriteLine($"{nameof(item.Push_id)} :\t {item.Push_id}");
Console.WriteLine($"{nameof(item.Phone)} :\t {item.Phone}");
Console.WriteLine($"{nameof(item.Service_id)} :\t {item.Service_id}");
Console.WriteLine($"{nameof(item.Service_name)} :\t {item.Service_name}");
Console.WriteLine($"{nameof(item.Msg_Ttl)} :\t {item.Msg_Ttl}");
}
Console.WriteLine("\n=====================\r\n============End of the List of IOs ============");
Console.WriteLine($"Количество записей для отправки: {PushNotificationsLists.ListNotificationsForSend.Count}");
#endif
return(PushNotificationsLists.ListNotificationsForSend.Count != 0);
}
catch (Exception ex)
{
Console.WriteLine($"Error: Exception.Message = [{ex.Message}], Exception.StackTrace = [{ex.StackTrace}]");
return(false);
}
}
