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