private List<ILogItem> BuildObjects(OracleDataReader rdr) { var output = new List<ILogItem>(); while(rdr.Read()) { CSELogItem temp = new CSELogItem(); temp.ID = rdr[0].ToString(); temp.UserID = rdr[1].ToString(); temp.Date = rdr[2].ToString(); temp.ProcedureName = rdr[3].ToString(); temp.Message = rdr[4].ToString(); output.Add(temp); } rdr.Dispose(); return output; }
/// <summary> /// Opens the data stored under the given ID for reading /// </summary> public Task <Stream> Read(string id) { try { // update last read time quickly UpdateLastReadTime(id); OracleDbConnection connection = null; OracleCommand command = null; OracleDataReader reader = null; try { connection = _connectionHelper.GetConnection(); command = connection.CreateCommand(); command.CommandText = $"SELECT data FROM {_table} WHERE id = :id"; command.Parameters.Add("id", id); command.InitialLOBFetchSize = 4000; reader = command.ExecuteReader(CommandBehavior.SingleRow); if (!reader.Read()) { throw new ArgumentException($"DataBus row with ID {id} not found"); } var blob = reader.GetOracleBlob(0); return(Task.FromResult <Stream>(new StreamWrapper(blob, /* dispose with stream: */ reader, command, connection))); } catch { // if something of the above fails, we did not pass ownership to someone who can dispose it... therefore: reader?.Dispose(); command?.Dispose(); connection?.Dispose(); throw; } } catch (Exception exception) { // Wrap in AggregateException to comply with Rebus contract. Tests do look for this specific exception type. throw new AggregateException(exception); } }
public bool Fill() { try { conn = new OracleConnection(GetConnectionString()); conn.Open(); transaction = conn.BeginTransaction(); } catch (Exception e) { MessageBox.Show("При подключении к базе данных произошла ошибка:\n\n" + e.Message); conn.Close(); conn.Dispose(); return false; } cmd = conn.CreateCommand(); cmd.CommandText = "SELECT TO_CHAR(id), l_name from akriko.cat_executors WHERE id in (SELECT distinct(ispolnitel_cik_id) FROM akriko.appeal) ORDER BY UPPER(l_name)"; try { dr = cmd.ExecuteReader(); while (dr.Read()) { if (dr.IsDBNull(0) || dr.IsDBNull(1)) { MessageBox.Show("При чтении данных из базы данных произошла ошибка:\n\n"); break; } else { UsersGrid.Rows.Add(); UsersGrid.Rows[UsersGrid.Rows.Count - 1].Cells["executor_id"].Value = dr.GetString(0); UsersGrid.Rows[UsersGrid.Rows.Count - 1].Cells["akriko"].Value = dr.GetString(1); } } dr.Close(); dr.Dispose(); } catch (Exception e) { MessageBox.Show("При чтении данных из базы данных произошла ошибка:\n\n" + e.Message); } if (UsersGrid.Rows.Count == 0) { MessageBox.Show("Отсутствуют неотконвертированные исполнители", "Конвертация исполнителей", MessageBoxButtons.OK, MessageBoxIcon.Information); ConvertButton.Enabled = false; } FillDeloDepartmentsColumn(); return true; }
void Backup(string executor_id) { cmd = conn.CreateCommand(); cmd.CommandText = "select TO_CHAR(id) from akriko.appeal WHERE ISPOLNITEL_CIK_ID=" + executor_id; try { dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { this.Log(dr.GetString(0), executor_id); } } } catch (Exception e) { MessageBox.Show("При чтении данных о подразделениях из базы данных произошла ошибка:\n\n" + e.Message); } if (dr != null) { dr.Close(); dr.Dispose(); } if (cmd != null) { cmd.Dispose(); } }
private void FillDeloDepartmentsColumn() { ((DataGridViewComboBoxColumn)UsersGrid.Columns["delo"]).Items.Add(""); cmd = conn.CreateCommand(); cmd.CommandText = "select SUBSTR(p.due, 0, INSTR(p.due, '.', 3 )) departmentCode, p.due personCode, (SELECT d.classif_name FROM department d WHERE d.due = SUBSTR(p.due, 0, INSTR(p.due, '.', 3 ))) departmentName, classif_name from department p where DELETED=0 and due != '0.' order by due"; try { dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { ((DataGridViewComboBoxColumn)UsersGrid.Columns["delo"]).Items.Add(dr.GetString(3)); DeloPersons[dr.GetString(3)] = new DeloPerson(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3)); } } } catch (Exception e) { MessageBox.Show("При чтении данных о подразделениях из базы данных произошла ошибка:\n\n" + e.Message); } if (dr != null) { dr.Close(); dr.Dispose(); } if (cmd != null) { cmd.Dispose(); } }
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline. public void Configure(IApplicationBuilder app, IHostingEnvironment env) { if (env.IsDevelopment()) { app.UseDeveloperExceptionPage(); } else { // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts. app.UseHsts(); } app.Run(async(context) => { //Demo: Basic ODP.NET Core application for ASP.NET Core // to connect, query, and return results to a web page //Create a connection to Oracle string conString = "User Id=hr;Password=<password>;" + //How to connect to an Oracle DB without SQL*Net configuration file // also known as tnsnames.ora. "Data Source=<ip or hostname>:1521/<service name>;"; //How to connect to an Oracle DB with a DB alias. //Uncomment below and comment above. //"Data Source=<service name alias>;"; using (OracleConnection con = new OracleConnection(conString)) { using (OracleCommand cmd = con.CreateCommand()) { try { con.Open(); cmd.BindByName = true; //Use the command to display employee names from // the EMPLOYEES table cmd.CommandText = "select first_name from employees where department_id = :id"; // Assign id to the department number 50 OracleParameter id = new OracleParameter("id", 50); cmd.Parameters.Add(id); //Execute the command and use DataReader to display the data OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { await context.Response.WriteAsync("Employee First Name: " + reader.GetString(0) + "\n"); } reader.Dispose(); } catch (Exception ex) { await context.Response.WriteAsync(ex.Message); } } } }); app.UseHttpsRedirection(); app.UseMvc(); }
/// <summary> /// 打印骨灰安放卡 /// </summary> /// <param name="rc001"></param> public static void Print_RegSettle(string rc001) { StringBuilder sb_1 = new StringBuilder(200); OracleCommand oc_command = new OracleCommand("select * from rc01 where rc001 = :rc001", SqlHelper.conn); OracleParameter op_rc001 = new OracleParameter("rc001", OracleDbType.Varchar2, 10); op_rc001.Direction = ParameterDirection.Input; op_rc001.Value = rc001; oc_command.Parameters.Add(op_rc001); OracleDataReader reader = oc_command.ExecuteReader(); if (reader.HasRows && reader.Read()) { sb_1.Append(reader["RC003"].ToString() + PADSTR); //逝者姓名 sb_1.Append(reader["RC109"].ToString() + PADSTR); //寄存证号 if (reader["RC002"] == null || reader["RC002"] is DBNull) { sb_1.Append("" + PADSTR); //性别 } else { sb_1.Append(reader["RC002"].ToString() + PADSTR); } if (reader["RC004"] == null || reader["RC004"] is DBNull) { sb_1.Append("" + PADSTR); //年龄 } else { sb_1.Append(reader["RC004"].ToString() + PADSTR); } //寄存位置 string s_position = RegAction.GetRegPosition(reader["RC001"].ToString()); if (string.IsNullOrEmpty(s_position)) { sb_1.Append("" + PADSTR); } else { sb_1.Append(s_position + PADSTR); } if (reader["RC050"] == null || reader["RC050"] is DBNull) { sb_1.Append("" + PADSTR); //联系人 } else { sb_1.Append(reader["RC050"].ToString() + PADSTR); } if (reader["RC051"] == null || reader["RC051"] is DBNull) { sb_1.Append("" + PADSTR); //联系电话 } else { sb_1.Append(reader["RC051"].ToString() + PADSTR); } if (reader["RC052"] == null || reader["RC052"] is DBNull) { sb_1.Append("" + PADSTR); //与逝者关系 } else { sb_1.Append(reader["RC052"].ToString() + PADSTR); } if (reader["RC200"] == null || reader["RC200"] is DBNull) { sb_1.Append("" + PADSTR); //经办日期 } else { sb_1.Append(string.Format("{0:yyyy年MM月dd日}", reader["RC200"]) + PADSTR); } if (reader["RC055"] == null || reader["RC055"] is DBNull) { sb_1.Append("" + PADSTR); //联系地址 } else { sb_1.Append(reader["RC055"].ToString() + PADSTR); } Send_PrintData printData = new Send_PrintData(); printData.command = "RegSettleCard"; printData.data = sb_1.ToString(); Frm_main.socket.sendMsg(Tool.ConvertObjectToJson(printData)); } reader.Dispose(); oc_command.Dispose(); }
/// <summary> /// 打印寄存证(基础) /// </summary> /// <param name="rc001"></param> public static void Print_RegCardBase(string rc001) { StringBuilder sb_1 = new StringBuilder(200); OracleCommand oc_command = new OracleCommand("select * from rc01 where rc001 = :rc001", SqlHelper.conn); OracleParameter op_rc001 = new OracleParameter("rc001", OracleDbType.Varchar2, 10); op_rc001.Direction = ParameterDirection.Input; op_rc001.Value = rc001; oc_command.Parameters.Add(op_rc001); OracleDataReader reader = oc_command.ExecuteReader(); if (reader.HasRows && reader.Read()) { sb_1.Append(reader["RC003"].ToString() + PADSTR); //逝者姓名 sb_1.Append(reader["RC109"].ToString() + PADSTR); //寄存证号 if (reader["RC002"] == null || reader["RC002"] is DBNull) { sb_1.Append("" + PADSTR); //性别 } else { sb_1.Append(reader["RC002"].ToString() + PADSTR); } if (reader["RC004"] == null || reader["RC004"] is DBNull) { sb_1.Append("" + PADSTR); //年龄 } else { sb_1.Append(reader["RC004"].ToString() + PADSTR); } //寄存位置 string s_position = RegAction.GetRegPosition(reader["RC001"].ToString()); if (string.IsNullOrEmpty(s_position)) { sb_1.Append("" + PADSTR); } else { sb_1.Append(s_position + PADSTR); } if (reader["RC050"] == null || reader["RC050"] is DBNull) { sb_1.Append("" + PADSTR); //联系人 } else { sb_1.Append(reader["RC050"].ToString() + PADSTR); } if (reader["RC051"] == null || reader["RC051"] is DBNull) { sb_1.Append("" + PADSTR); //联系电话 } else { sb_1.Append(reader["RC051"].ToString() + PADSTR); } if (reader["RC200"] == null || reader["RC200"] is DBNull) { sb_1.Append("" + PADSTR); //经办日期 } else { sb_1.Append(string.Format("{0:yyyy年MM月dd日}", reader["RC200"]) + PADSTR); } if (reader["RC055"] == null || reader["RC055"] is DBNull) { sb_1.Append("" + PADSTR); //联系地址 } else { sb_1.Append(reader["RC055"].ToString() + PADSTR); } if (reader["RC140"] == null || reader["RC140"] is DBNull) { sb_1.Append("" + PADSTR); //寄存日期 } else { sb_1.Append(string.Format("{0:yyyy-MM-dd}", reader["RC140"]) + PADSTR); } DateTime d_end = Convert.ToDateTime(SqlHelper.ExecuteScalar("select min(rc022) from rc04 where status = '1' and rc001 = '" + rc001 + "'")); if (d_end == null) { sb_1.Append("" + PADSTR); //截至日期 } else { sb_1.Append(string.Format("{0:yyyy-MM-dd}", d_end) + PADSTR); } decimal price = RegAction.GetRegPrice(rc001); sb_1.Append(price.ToString() + PADSTR); Send_PrintData printData = new Send_PrintData(); printData.command = "RegCardBase"; printData.data = sb_1.ToString(); Frm_main.socket.sendMsg(Tool.ConvertObjectToJson(printData)); } reader.Dispose(); oc_command.Dispose(); }
protected void Page_Load(object sender, EventArgs e) { try { string v_barcode1 = ""; if ((Request.Params["id"] != null) & (Request.Params["pw"] != null)) { v_id = Request.Params["id"].ToString(); v_pw = Request.Params["pw"].ToString(); Sql_str = " SELECT S.USER_ID, S.USERNAME, S.PASSWORD, "; Sql_str += " S.LOGIN_NAME, S.EMP_NO, S.EMP_NAME, "; Sql_str += " S.DEPT_NO, S.DEPT_NAME "; Sql_str += " FROM EAGLE.SYS_USERS S "; Sql_str += " WHERE USERNAME = :v_ID "; Sql_str += " AND PASSWORD = :v_PW "; //建立連線 //使用web.config conn string OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["jh815"].ConnectionString); conn.Open(); OracleCommand cmd = new OracleCommand(Sql_str, conn); cmd.Parameters.Add(new OracleParameter("v_ID", OracleType.VarChar, 100)); cmd.Parameters["v_ID"].Value = v_id; cmd.Parameters.Add(new OracleParameter("v_PW", OracleType.VarChar, 100)); cmd.Parameters["v_PW"].Value = v_pw; OracleDataReader dr = cmd.ExecuteReader(); try { //while (dr.Read()) //{ //組成JSON字串 if (dr.HasRows) { var dataQuery = from d in dr.Cast <DbDataRecord>() select new { STATUS = "PASS", USERNAME = (String)dr["USERNAME"], EMP_NAME = (String)dr["EMP_NAME"] }; string v_json = (JsonConvert.SerializeObject(dataQuery)); tsconn.save_log("guest", "APP Login", "192.168.0.19", v_json); Response.Write(v_json); //} } else { var dataQuery = new { STATUS = "REJECT", USERNAME = v_id }; string v_json = (JsonConvert.SerializeObject(dataQuery)); tsconn.save_log("guest", "APP Login", "192.168.0.19", v_json); Response.Write(v_json); } } catch (Exception ex) { var dataQuery = new { STATUS = "REJECT", USERNAME = v_id }; string v_json = (JsonConvert.SerializeObject(dataQuery)); tsconn.save_log("guest", "APP Login", "192.168.0.19", v_json); Response.Write(v_json); } finally { cmd.Dispose(); dr.Close(); dr.Dispose(); conn.Close(); conn.Dispose(); } //tsconn.trans_oracle815(Sql_str, "insert"); //Response.Write("Insert success!"); } //else //{ // Response.Write("No data insert!"); //} } catch (Exception ex) { var dataQuery = new { STATUS = "REJECT", USERNAME = v_id }; string v_json = (JsonConvert.SerializeObject(dataQuery)); tsconn.save_log("guest", "APP Login", "192.168.0.19", v_json); Response.Write(v_json); } }
public void ExportToCSV(SqlDataSource dataSrc, string fileName) { //Add Response header Response.Clear(); Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.csv", fileName)); Response.Charset = ""; Response.ContentType = "application/vnd.xls"; //GET Data From Database OracleConnection cn = new OracleConnection(dataSrc.ConnectionString); string query = dataSrc.SelectCommand.Replace("\r\n", " ").Replace("\t", " "); OracleCommand cmd = new OracleCommand(query, cn); cmd.CommandTimeout = 999999; cmd.CommandType = CommandType.Text; try { cn.Open(); OracleDataReader dr = cmd.ExecuteReader(); StringBuilder sb = new StringBuilder(); //CSV Header for (int count = 0; count < dr.FieldCount; count++) { if (dr.GetName(count) != null) { sb.Append(dr.GetName(count)); } if (count < dr.FieldCount - 1) { sb.Append(","); } } Response.Write(sb.ToString() + "\n"); Response.Flush(); //CSV Body while (dr.Read()) { sb = new StringBuilder(); for (int col = 0; col < dr.FieldCount - 1; col++) { if (!dr.IsDBNull(col)) { if (dr.GetDataTypeName(col).ToUpper() == "DATE") { sb.Append(DateTime.Parse(dr.GetValue(col).ToString()).ToString("dd-MMM-yyyy")); } else { sb.Append(dr.GetValue(col).ToString().Replace(",", " ")); } } sb.Append(","); } if (!dr.IsDBNull(dr.FieldCount - 1)) { if (!dr.IsDBNull(dr.FieldCount - 1)) { if (dr.GetDataTypeName(dr.FieldCount - 1).ToUpper() == "DATE") { sb.Append(DateTime.Parse(dr.GetValue(dr.FieldCount - 1).ToString()).ToString("dd-MMM-yyyy")); } else { sb.Append(dr.GetValue(dr.FieldCount - 1).ToString().Replace(",", " ")); } } } Response.Write(sb.ToString() + "\n"); Response.Flush(); } dr.Dispose(); } catch (Exception ex) { Response.Write(ex.Message); } finally { cmd.Connection.Close(); cn.Close(); } Response.End(); }
public List <STEmitent> GetData(int trcode) { List <STEmitent> list_data = new List <STEmitent>(); STEmitent item; OracleConnection connet = new OracleConnection(); try { connet = new OracleConnection(config.connectionstring); connet.Open(); if (connet.State != ConnectionState.Open) { log.LogLine(string.Format("No connection to DB Oracle. CS: {0}", config.connectionstring)); return(list_data); } string query = string.Format("SELECT EmtCode,EmtName,Address,Phone,PostIndex,E_mail,BankCode,Account, " + "DeptCode,Shifr_OKPO,Shifr_UNN,Shifr_OKED,TransCode,PeriodBeg,PeriodEnd,DirectName," + "BuchName,CBSCode,ConnectionString,EmtShortName FROM {0}.Emitent WHERE TransCode={1} ORDER BY EmtCode", "RCD", trcode); OracleCommand cmd = new OracleCommand(query, connet); OracleDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { item = new STEmitent(); item.code = reader.GetInt32(0); item.name = reader["EmtName"].ToString().Trim(); item.address = reader["Address"].ToString().Trim(); item.phone = reader["Phone"].ToString().Trim(); item.postindex = reader["PostIndex"].ToString().Trim(); item.email = reader["E_mail"].ToString().Trim(); if (!reader.IsDBNull(6)) { item.bankcode = reader.GetInt32(6); } item.account = reader["Account"].ToString().Trim(); if (!reader.IsDBNull(8)) { item.deptcode = reader.GetInt32(8); } item.OKPO = reader["Shifr_OKPO"].ToString().Trim(); item.UNN = reader["Shifr_UNN"].ToString().Trim(); item.OKED = reader["Shifr_OKED"].ToString().Trim(); if (!reader.IsDBNull(12)) { item.transcode = reader.GetInt32(12); } if (!reader.IsDBNull(13)) { item.periodbegin = reader.GetDateTime(13); } if (!reader.IsDBNull(14)) { item.periodend = reader.GetDateTime(14); } item.directname = reader["DirectName"].ToString().Trim(); item.buchname = reader["BuchName"].ToString().Trim(); if (!reader.IsDBNull(17)) { item.CBScode = reader.GetInt32(17); } if (!reader.IsDBNull(18)) { item.c_string = reader.GetString(18); } else { item.c_string = null; } if (!reader.IsDBNull(19)) { item.shortname = reader.GetString(19); } else { item.shortname = null; } list_data.Add(item); } } reader.Dispose(); } catch (Exception ex) { log.LogLine(ex.Message); if (connet.State == ConnectionState.Open) { connet.Close(); } } return(list_data); }
//======================================================================================== // Edit() //======================================================================================== internal override void Edit(River.Orqa.Query.QueryWindow window) { Statusbar.Message = "Reading text..."; OracleCommand cmd = new OracleCommand( "SELECT text" + " FROM dba_source" + " WHERE owner='" + schemaName + "' AND name='" + Text + "' AND type='FUNCTION'" + " ORDER BY line", dbase.OraConnection ); try { OracleDataReader reader = cmd.ExecuteReader(); if (reader.FieldCount > 0) { StringBuilder text = new StringBuilder(); if (reader.Read()) { // Modify first line to insert cmd and qualify name // We purposefully do not trim the end to preserve the space string preamble = reader.GetString(0).Substring("FUNCTION".Length).TrimStart(); if (preamble.IndexOf("wrapped") > 0) { MessageBox.Show( "Unable to display function; contents are wrapped.", "Wrapped Content", MessageBoxButtons.OK, MessageBoxIcon.Information ); } else { text.Append("CREATE OR REPLACE FUNCTION " + schemaName + "." + preamble ); // append rest of content while (reader.Read()) { text.Append(reader.GetString(0)); } window.InsertText(text.ToString()); window.IsSaved = true; window.SetTitle(schemaName + "." + this.Text); } } } reader.Close(); reader.Dispose(); reader = null; Statusbar.Message = String.Empty; } catch (Exception exc) { River.Orqa.Dialogs.ExceptionDialog.ShowException(exc); } cmd.Dispose(); cmd = null; }
/// <summary> /// Recherche les informations sur les comptes en mode droits restreints /// </summary> /// <param name="userConnected"></param> /// <returns></returns> private bool SelectArchiConnectedUser(AccountMgmt.DataAccess.ConnectedUser userConnected) { OracleCommand sqlCommand = null; OracleDataReader sqlReader = null; string sql = "SELECT proj.id_project,proj.project, proj.date_creation, proj.date_modification, proj.commentary, proj.activation, " + " app.id_application, app.application, app.id_project, app.default_tablespace, app.date_creation, app.date_modification, app.commentary, app.activation, " + " mod.id_module, mod.module, mod.id_application, mod.date_creation, mod.date_modification, mod.commentary, mod.activation, " + //" prof.id_profile, prof.profile, prof.id_module, prof.date_creation, prof.date_modification, prof.commentary, prof.activation, prof.Profile_oracle, Rsrc_conso_group " + " prof.id_profile, prof.profile, prof.id_module, prof.date_creation, prof.date_modification, prof.commentary, prof.activation, prof.Profile_oracle " + "FROM MOU01.module mod, MOU01.profile prof, MOU01.affectation aff, MOU01.application app, MOU01.project proj " + "WHERE aff.id_user_ = " + userConnected.UserConnected.Id.ToString("0") + " AND aff.activation < " + AccountMgmt.Common.Constants.DesactivationLevel.ToString("0") + " AND aff.admin>=" + AccountMgmt.Common.Constants.AdminLevel.ToString("0") + " AND aff.id_profile = prof.id_profile " + " AND prof.activation < " + AccountMgmt.Common.Constants.DesactivationLevel.ToString("0") + " AND prof.id_module = mod.id_module " + " AND mod.activation < " + AccountMgmt.Common.Constants.DesactivationLevel.ToString("0") + " AND app.id_application = mod.id_application " + " AND app.activation < " + AccountMgmt.Common.Constants.DesactivationLevel.ToString("0") + " AND app.id_project = proj.id_project " + " AND proj.activation < " + AccountMgmt.Common.Constants.DesactivationLevel.ToString("0") + " ORDER BY proj.project, app.application, mod.module, prof.profile"; bool bResult = false; try { sqlCommand = new OracleCommand(sql, userConnected.Connection); sqlReader = sqlCommand.ExecuteReader(); long oldProject = -1, oldApplication = -1, oldModule = -1, oldProfile = -1; //lecture des données while (sqlReader.Read()) { //recupère le projet if (oldProject != sqlReader.GetInt64(0)) { Project newProject = new Project(); newProject.Id = sqlReader.GetInt64(0); newProject.Name = sqlReader.GetString(1); newProject.DateCreation = sqlReader.GetDateTime(2); if (!sqlReader.IsDBNull(3)) { newProject.DateModification = sqlReader.GetDateTime(3); } if (!sqlReader.IsDBNull(4)) { newProject.Commentary = sqlReader.GetString(4); } if (sqlReader.GetInt16(5) == AccountMgmt.Common.Constants.ActivationLevel) { newProject.Activation = true; } else { newProject.Activation = false; } m_listProject.Add(newProject); oldProject = sqlReader.GetInt64(0); } //recupère l'application if (oldApplication != sqlReader.GetInt64(6)) { Application newApplication = new Application(); newApplication.Id = sqlReader.GetInt64(6); newApplication.Name = sqlReader.GetString(7); newApplication.IdProject = sqlReader.GetInt64(8); newApplication.DateCreation = sqlReader.GetDateTime(10); if (!sqlReader.IsDBNull(11)) { newApplication.DateModification = sqlReader.GetDateTime(11); } if (!sqlReader.IsDBNull(12)) { newApplication.Commentary = sqlReader.GetString(12); } if (sqlReader.GetInt16(13) == AccountMgmt.Common.Constants.ActivationLevel) { newApplication.Activation = true; } else { newApplication.Activation = false; } m_listApplication.Add(newApplication); oldApplication = sqlReader.GetInt64(6); } //recupère le module if (oldModule != sqlReader.GetInt64(14)) { Module newModule = new Module(); newModule.Id = sqlReader.GetInt64(14); newModule.Name = sqlReader.GetString(15); newModule.IdApplication = sqlReader.GetInt64(16); newModule.DateCreation = sqlReader.GetDateTime(17); if (!sqlReader.IsDBNull(18)) { newModule.DateModification = sqlReader.GetDateTime(18); } if (!sqlReader.IsDBNull(19)) { newModule.Commentary = sqlReader.GetString(19); } if (sqlReader.GetInt16(20) == AccountMgmt.Common.Constants.ActivationLevel) { newModule.Activation = true; } else { newModule.Activation = false; } m_listModule.Add(newModule); oldModule = sqlReader.GetInt64(14); } //recupère le profile if (oldProfile != sqlReader.GetInt64(21)) { Profile newProfile = new Profile(); newProfile.Id = sqlReader.GetInt64(21); newProfile.Name = sqlReader.GetString(22); newProfile.IdModule = sqlReader.GetInt64(23); newProfile.DateCreation = sqlReader.GetDateTime(24); if (!sqlReader.IsDBNull(25)) { newProfile.DateModification = sqlReader.GetDateTime(25); } if (!sqlReader.IsDBNull(26)) { newProfile.Commentary = sqlReader.GetString(26); } if (sqlReader.GetInt16(27) == AccountMgmt.Common.Constants.ActivationLevel) { newProfile.Activation = true; } else { newProfile.Activation = false; } if (!sqlReader.IsDBNull(28)) { newProfile.OracleProfile = sqlReader.GetString(28); } /*if(!sqlReader.IsDBNull(29)) * newProfile.RsrcConsoGroup = sqlReader.GetString(29);*/ m_listProfile.Add(newProfile); oldProfile = sqlReader.GetInt64(21); //récupère les roles associés au profile AccessRole listRole = new AccessRole(); if (!listRole.SelectRight(userConnected.Connection, "ro.id_role = ri.id_role AND ri.id_profile=" + newProfile.Id.ToString(), "role")) { return(false); } m_listRole.AddRange((ArrayList)listRole.ListRole.Clone()); listRole.Dispose(); } } bResult = true; } catch (Exception error) { MessageBox.Show("Message d’erreur : " + error.Message); } finally { try { // Fermeture de la base de données if (sqlReader != null) { sqlReader.Close(); sqlReader.Dispose(); } if (sqlCommand != null) { sqlCommand.Dispose(); } } catch (Exception error) { MessageBox.Show("Message d’erreur : " + error.Message); } } return(bResult); }
/// <summary> /// Supprime l'affectation entre les rôles du profile et l'utilisateur, revoke aussi les rôles à l'utilisateur /// </summary> /// <param name="connection"></param> /// <param name="strUserOracle">UserOrcale</param> /// <returns></returns> public bool Delete(OracleConnection connection, string strUserOracle) { bool bResult = false; // Start a local transaction OracleCommand command = new OracleCommand(); command.Connection = connection; OracleDataReader sqlReader = null; OracleTransaction transaction = null; string sql = "", strRoleList = ""; try { //lock de la table affectation sql = "LOCK TABLE MOU01.affectation IN EXCLUSIVE MODE"; command.CommandText = sql; command.ExecuteNonQuery(); try { transaction = connection.BeginTransaction(); //Recherche des rôles à REVOKER sql = "SELECT nom, SUM(nb) FROM(" + "(SELECT role.role nom, '+'||COUNT(role.role) nb FROM MOU01.role, MOU01.right, MOU01.profile, MOU01.affectation " + "WHERE Role.id_role = Right.id_role " + " AND right.id_profile = profile.id_profile " + " AND affectation.id_profile = profile.id_profile " + " AND affectation.id_user_=" + this.m_dIdUser.ToString("0") + " " + "GROUP BY role.role) " + "UNION ALL " + "(SELECT role.role nom, '-'||COUNT(role.role) nb FROM MOU01.role, MOU01.right " + "WHERE Role.id_role = Right.id_role" + " AND right.id_profile=" + this.m_dIdProfile.ToString("0") + " " + "GROUP BY role.role)) GROUP BY nom HAVING SUM(nb)=0"; command.CommandText = sql; sqlReader = command.ExecuteReader(); //lecture des données while (sqlReader.Read()) { if (strRoleList != "") { strRoleList += ", "; } strRoleList = strRoleList + sqlReader.GetString(0); } //revoke les roles du profile à l'utilisateur if (strRoleList != "") { sql = "REVOKE " + strRoleList + " FROM " + strUserOracle; command.CommandText = sql; command.ExecuteNonQuery(); } //mise à jour de la table affectation sql = "DELETE FROM MOU01.affectation WHERE id_user_=" + this.m_dIdUser.ToString("0") + " AND id_profile=" + this.m_dIdProfile.ToString("0"); command.CommandText = sql; command.ExecuteNonQuery(); transaction.Commit(); sql = "COMMIT"; command.CommandText = sql; command.ExecuteNonQuery(); bResult = true; } catch (Exception error) { transaction.Rollback(); sql = "ROLLBACK"; command.CommandText = sql; command.ExecuteNonQuery(); MessageBox.Show("Problème lors de la suppression de l'affectation de l'utilisateur au profile avec l'erreur : " + error.Message); } } catch (Exception error) { sql = "ROLLBACK"; command.CommandText = sql; command.ExecuteNonQuery(); MessageBox.Show("Problème lors du lock de la table Affectation avec l'erreur : " + error.Message); } finally { //deconnexion try { // Fermeture de la base de données if (sqlReader != null) { sqlReader.Close(); sqlReader.Dispose(); } if (command != null) { command.Dispose(); } } catch (Exception error) { MessageBox.Show("Message d’erreur : " + error.Message); } } return(bResult); }
/// <summary> /// Permet de sélectionner des enregistrements suivant un critère de recherche et de trier ces résultats /// </summary> /// <param name="connection">Objet de connexion à la base de données</param> /// <param name="idProfile">Identificateur du profile affecté</param> /// <param name="strSort">Critère de trie</param> /// <returns></returns> public bool Select(OracleConnection connection, long idProfile, string strSort) { OracleCommand sqlCommand = null; OracleDataReader sqlReader = null; string sql = "SELECT u.id_user_, u.id_language_data_i, u.id_region_data, u.user_, u.user_oracle, u.service, u.date_creation, u.date_modification, u.commentary, u.activation, u.id_language_user_, u.pwd, u.date_beginning, u.date_end, u.id_user_modification, u.id_temporary_tablespace, u.dba_status, id_source," + " a.admin, a.date_creation, a.date_modification, a.commentary, a.activation, a.date_beginning, a.date_end FROM MOU01.list_user u, MOU01.AFFECTATION a " + " WHERE u.id_user_ = a.id_user_ AND a.id_profile = " + idProfile.ToString("0"); if (strSort != "") { sql = sql + " ORDER BY " + strSort; } bool bResult = false; try { sqlCommand = new OracleCommand(sql, connection); sqlReader = sqlCommand.ExecuteReader(); //lecture des données while (sqlReader.Read()) { User newUser = new User(); newUser.Id = long.Parse(sqlReader.GetValue(0).ToString()); //sqlReader.GetInt64(0); newUser.IdLanguageData = long.Parse(sqlReader.GetValue(1).ToString()); //sqlReader.GetInt64(1); newUser.IdRegionData = long.Parse(sqlReader.GetValue(2).ToString()); //sqlReader.GetInt64(2); newUser.Name = sqlReader.GetString(3); newUser.UserOracle = sqlReader.GetString(4); newUser.Service = sqlReader.GetString(5); newUser.DateCreation = sqlReader.GetDateTime(6); if (!sqlReader.IsDBNull(7)) { newUser.DateModification = sqlReader.GetDateTime(7); } if (!sqlReader.IsDBNull(8)) { newUser.Commentary = sqlReader.GetString(8); } if (/*sqlReader.GetInt16(9)*/ short.Parse(sqlReader.GetValue(9).ToString()) == AccountMgmt.Common.Constants.ActivationLevel) { newUser.Activation = true; } else { newUser.Activation = false; } newUser.IdLanguageUser = long.Parse(sqlReader.GetValue(10).ToString()); //sqlReader.GetInt64(10); if (!sqlReader.IsDBNull(11)) { newUser.Password = sqlReader.GetString(11); } if (!sqlReader.IsDBNull(12)) { newUser.DateBeginning = sqlReader.GetDateTime(12); } if (!sqlReader.IsDBNull(13)) { newUser.DateEnd = sqlReader.GetDateTime(13); } if (!sqlReader.IsDBNull(14)) { newUser.IdUserModification = long.Parse(sqlReader.GetValue(14).ToString()); //sqlReader.GetInt64(14); } newUser.IdTemporaryTableSpace = long.Parse(sqlReader.GetValue(15).ToString()); //sqlReader.GetInt64(15); if (!sqlReader.IsDBNull(16)) { newUser.Admin = short.Parse(sqlReader.GetValue(16).ToString()); } else { newUser.Admin = AccountMgmt.Common.Constants.NoAdminLevel; } if (!sqlReader.IsDBNull(17)) { newUser.IdSource = long.Parse(sqlReader.GetValue(17).ToString()); } Affectation newAffectation = new Affectation(); newAffectation.IdUser = long.Parse(sqlReader.GetValue(0).ToString()); //sqlReader.GetInt64(0); newAffectation.IdProfile = idProfile; if (sqlReader.GetInt16(18) == AccountMgmt.Common.Constants.AdminLevel) { newAffectation.Admin = true; } else { newAffectation.Admin = false; } newAffectation.DateCreation = sqlReader.GetDateTime(19); if (!sqlReader.IsDBNull(20)) { newAffectation.DateModification = sqlReader.GetDateTime(20); } if (!sqlReader.IsDBNull(21)) { newAffectation.Commentary = sqlReader.GetString(21); } if (sqlReader.GetInt16(22) == AccountMgmt.Common.Constants.ActivationLevel) { newAffectation.Activation = true; } else { newAffectation.Activation = false; } if (!sqlReader.IsDBNull(23)) { newAffectation.DateBeginning = sqlReader.GetDateTime(23); } if (!sqlReader.IsDBNull(24)) { newAffectation.DateEnd = sqlReader.GetDateTime(24); } newAffectation.AffectedUser = newUser; m_listAffectedUser.Add(newAffectation); } bResult = true; } catch (Exception error) { MessageBox.Show("Message d’erreur : " + error.Message); } finally { try { // Fermeture de la base de données if (sqlReader != null) { sqlReader.Close(); sqlReader.Dispose(); } if (sqlCommand != null) { sqlCommand.Dispose(); } } catch (Exception error) { MessageBox.Show("Message d’erreur : " + error.Message); } } return(bResult); }
protected void categoryddn_SelectedIndexChanged(object sender, EventArgs e) { string category = categoryddn.SelectedItem.Text; OracleConnection con = new OracleConnection(); con.ConnectionString = "Data Source=orcl;User Id=aadbase;Password=GvI.@HYnP;Integrated Security = no;"; con.Open(); OracleCommand cmd = new OracleCommand(@"SELECT Discount FROM Category where CategoryNAME='" + category + "'", con); OracleCommand catidcmd = new OracleCommand(@"SELECT CategoryID FROM Category where CategoryNAME='" + category + "'", con); //OracleDataAdapter da = new OracleDataAdapter(cmd); //OracleDataAdapter catidda = new OracleDataAdapter(catidcmd); // SqlDataAdapter da = new SqlDataAdapter(cmd); double discount; string categoryid; OracleDataReader cmdreader = cmd.ExecuteReader(); while (cmdreader.Read()) { discount = cmdreader.GetDouble(0); Session["catdiscount"] = discount; } OracleDataReader catidreader = catidcmd.ExecuteReader(); while (catidreader.Read()) { categoryid = catidreader.GetString(0); Session["CategoryID"] = categoryid; } //DataTable dt = new DataTable(); //da.Fill(dt); //int discount = Convert.ToInt32(da); //Session["Discount"] = discount; //DataTable catiddt = new DataTable(); //catidda.Fill(catiddt); //string categoryid = Convert.ToString(catidda); //Session["CategoryID"] = categoryid; cmdreader.Dispose(); catidreader.Dispose(); con.Close(); int finalcardfare = Convert.ToInt32(Session["cardfareamount"]); double finaldiscount = (Convert.ToDouble(Session["catdiscount"])); double cardfinalamount = finalcardfare - (finaldiscount * finalcardfare); //int cardfinalamount = Convert.ToInt32(Session["cardfareamount"]) - (Convert.ToInt32(Session["Discount"]) * Convert.ToInt32(Session["cardfareamount"])); finalamountid.Visible = true; finalamountid.Text = "YOUR FINAL AMOUNT W/ DISCOUNT: $" + cardfinalamount.ToString(); buynow.Visible = true; Session["CardFinalAmount"] = cardfinalamount; //if (category == "Student") //{ //discountlbl.Text = "Congratulations! Your Discount of " + (discount * 100) + "% will be applied"; //} }
/// <summary> /// Processes the m52 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; bool bExist = 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; if (!ExistWork(ref bExist, ref oraDBConn, ref oraCmd)) { bRes = false; } else { bRes = true; } string strEndDate; if (_EndDate == "") { strEndDate = "NULL"; } else { strEndDate = string.Format("to_date('{0}', 'HH24MISSDDMMYY')", _EndDate); } if ((bRes) && (!bExist)) { bRes = false; oraCmd.CommandText = string.Format("insert into WORKS (WORK_SS_ID, WORK_PDA_ID, WORK_USR_ID, WORK_UNI_ID, WORK_NUM_PARK_SPACES, WORK_REMARKS, WORK_INI_DATE, WORK_END_DATE, WORK_LICEN_NUMBER, WORK_LICEN_CORP) " + "values ({0}, '{1}', {2} , {3}, {4}, '{5}', to_date('{6}', 'HH24MISSDDMMYY'), {7}, '{8}', '{9}')", _streetStretch, _PDAID, _userId, _unitId, _NumPlaces, _Remarks, _StartDate, strEndDate, _LicenseNumber, _LicenseCompany); oraCmd.ExecuteNonQuery(); bRes = true; } else if ((bRes) && (bExist)) { bRes = false; oraCmd.CommandText = string.Format("update WORKS set WORK_SS_ID={0}, WORK_USR_ID={2}, WORK_UNI_ID={3}, WORK_NUM_PARK_SPACES={4}, WORK_REMARKS='{5}', WORK_INI_DATE= to_date('{6}', 'HH24MISSDDMMYY'), WORK_END_DATE={7}, WORK_LICEN_NUMBER='{8}', WORK_LICEN_CORP='{9}' " + "where WORK_PDA_ID='{1}'", _streetStretch, _PDAID, _userId, _unitId, _NumPlaces, _Remarks, _StartDate, strEndDate, _LicenseNumber, _LicenseCompany); oraCmd.ExecuteNonQuery(); bRes = true; } } if (bRes) { res = ReturnAck(AckMessage.AckTypes.ACK_PROCESSED); } else { if (logger != null) { logger.AddLog("[Msg56:Process]: Error.", LoggerSeverities.Error); } res = ReturnNack(NackMessage.NackTypes.NACK_ERROR_BECS); } } catch (Exception e) { if (logger != null) { logger.AddLog("[Msg56: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); }
static void Main(string[] args) { // This sample demonstrates how to use ODP.NET Core Configuration API // Add connect descriptors and net service names entries. OracleConfiguration.OracleDataSources.Add("orclpdb", "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname or IP>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<service name>)(SERVER=dedicated)))"); OracleConfiguration.OracleDataSources.Add("orcl", "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname or IP>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<service name>)(SERVER=dedicated)))"); // Set default statement cache size to be used by all connections. OracleConfiguration.StatementCacheSize = 25; // Disable self tuning by default. OracleConfiguration.SelfTuning = false; // Bind all parameters by name. OracleConfiguration.BindByName = true; // Set default timeout to 60 seconds. OracleConfiguration.CommandTimeout = 60; // Set default fetch size as 1 MB. OracleConfiguration.FetchSize = 1024 * 1024; // Set tracing options OracleConfiguration.TraceOption = 1; OracleConfiguration.TraceFileLocation = @"D:\traces"; // Uncomment below to generate trace files //OracleConfiguration.TraceLevel = 7; // Set network properties OracleConfiguration.SendBufferSize = 8192; OracleConfiguration.ReceiveBufferSize = 8192; OracleConfiguration.DisableOOB = true; OracleConnection orclCon = null; try { // Open a connection orclCon = new OracleConnection("user id=hr; password=<password>; data source=orclpdb"); orclCon.Open(); // Execute simple select statement that returns first 10 names from EMPLOYEES table OracleCommand orclCmd = orclCon.CreateCommand(); orclCmd.CommandText = "select first_name from employees where rownum <= 10 "; OracleDataReader rdr = orclCmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine("Employee Name: " + rdr.GetString(0)); } Console.ReadLine(); rdr.Dispose(); orclCmd.Dispose(); } finally { // Close the connection if (null != orclCon) { orclCon.Close(); } } }
/// <summary> /// Recherche des utilisateurs affectés à un profile /// </summary> /// <param name="connection"></param> /// <param name="OracleProfile"></param> /// <param name="IdProfile"></param> /// <param name="bAllUsers"></param> /// <returns></returns> public bool Select(OracleConnection connection, string OracleProfile, long IdProfile, bool bAllUsers) { OracleCommand sqlCommand = null; OracleDataReader sqlReader = null; string sql = ""; string strOracleProfileNull = ""; if (OracleProfile == null) { strOracleProfileNull = " is null "; } else { strOracleProfileNull = " = '" + OracleProfile + "'"; } if (!bAllUsers) //Etude de profil sur l'ensemble des utilisateurs affectés au profil MOU { sql = "select distinct f.username,f.user_,f.profile,f.profile_oracle,f.color from " + "( " + //-- Vert ( Affecté - Pas autre profile possible ) "select du.username,u.user_, du.profile,p.profile_oracle,'vert' as color from dba_users du,mou01.affectation a, mou01.profile p, mou01.user_ u " + "where p.id_profile= " + IdProfile + " and p.id_profile=a.id_profile " + "and u.id_user_=a.id_user_ " + "and u.user_oracle=du.username " + "and du.profile=nvl(p.profile_oracle,'DEFAULT') " + "and du.username not in " + "(select u1.user_oracle from mou01.user_ u1,mou01.affectation a1, mou01.profile p1 " + "where p1.profile_oracle <> 'DEFAULT' " + "and p1.profile_oracle is not null " + "and p1.profile_oracle<> '" + OracleProfile + "' " + "and a1.id_profile=p1.id_profile " + "and u1.id_user_=a1.id_user_ " + ") " + "union " + //-- Jaune ( Affecté - Autre profile possible ) "select du.username,u.user_, du.profile,p.profile_oracle,'jaune' as color " + "from dba_users du,mou01.affectation a, mou01.profile p, mou01.user_ u " + "where p.id_profile = " + IdProfile + " and p.id_profile=a.id_profile " + "and u.id_user_=a.id_user_ " + "and u.user_oracle=du.username " + "and du.profile=nvl(p.profile_oracle,'DEFAULT') " + "and du.username in " + "(select u1.user_oracle from mou01.user_ u1,mou01.affectation a1, mou01.profile p1 " + "where p1.profile_oracle <> 'DEFAULT' " + "and p1.profile_oracle is not null " + "and p1.profile_oracle <> '" + OracleProfile + "' " + "and a1.id_profile=p1.id_profile " + "and u1.id_user_=a1.id_user_ " + ") " + "union " + //-- Orange ( Pas affecté - Pas autre profile possible ) "select du.username,u.user_, du.profile,p.profile_oracle,'orange' as color from dba_users du,mou01.affectation a, mou01.profile p, mou01.user_ u " + "where p.id_profile= " + IdProfile + " and p.id_profile=a.id_profile " + "and u.id_user_=a.id_user_ " + "and u.user_oracle=du.username " + "and du.profile<>nvl(p.profile_oracle,'DEFAULT') " + "and du.username not in " + "(select u1.user_oracle from mou01.user_ u1,mou01.affectation a1, mou01.profile p1 " + "where p1.profile_oracle <> 'DEFAULT' " + "and p1.profile_oracle is not null " + "and p1.profile_oracle<> '" + OracleProfile + "' " + "and a1.id_profile=p1.id_profile " + "and u1.id_user_=a1.id_user_ " + ") " + "union " + //-- Rouge ( Pas affecté - Autre profile possible ) "select du.username,u.user_, du.profile,p.profile_oracle,'rouge' as color from dba_users du,mou01.affectation a, mou01.profile p, mou01.user_ u " + "where p.id_profile= " + IdProfile + " and p.id_profile=a.id_profile " + "and u.id_user_=a.id_user_ " + "and u.user_oracle=du.username " + "and du.profile<>nvl(p.profile_oracle,'DEFAULT') " + "and du.username in " + "(select u1.user_oracle from mou01.user_ u1,mou01.affectation a1, mou01.profile p1 " + "where p1.profile_oracle <> 'DEFAULT' " + "and p1.profile_oracle is not null " + "and p1.profile_oracle <> '" + OracleProfile + "' " + "and a1.id_profile=p1.id_profile " + "and u1.id_user_=a1.id_user_ " + ") " + ") f " + "order by f.username"; } else //Etude de profil sur l'ensemble des utilisateurs affectés au profil ORACLE { sql = "select distinct f.username,f.user_,f.profile,f.profile_oracle,f.color from " + "( " + //-- Vert ( Affecté - Pas autre profile possible ) "select du.username,u.user_, du.profile,p.profile_oracle,'vert' as color from dba_users du,mou01.affectation a, mou01.profile p, mou01.user_ u " + "where p.id_profile=a.id_profile " + "and u.id_user_=a.id_user_ " + "and u.user_oracle=du.username " + "and du.profile=nvl(p.profile_oracle,'DEFAULT') " + "and du.username not in " + "(select u1.user_oracle from mou01.user_ u1,mou01.affectation a1, mou01.profile p1 " + "where p1.profile_oracle <> 'DEFAULT' " + "and p1.profile_oracle is not null " + "and p1.profile_oracle<> '" + OracleProfile + "' " + "and a1.id_profile=p1.id_profile " + "and u1.id_user_=a1.id_user_ " + ") " + "and p.profile_oracle " + strOracleProfileNull + "union " + //-- Jaune ( Affecté - Autre profile possible ) "select du.username,u.user_, du.profile,p.profile_oracle,'jaune' as color from dba_users du,mou01.affectation a, mou01.profile p, mou01.user_ u " + "where p.id_profile=a.id_profile " + "and u.id_user_=a.id_user_ " + "and u.user_oracle=du.username " + "and du.profile=nvl(p.profile_oracle,'DEFAULT') " + "and du.username in " + "(select u1.user_oracle from mou01.user_ u1,mou01.affectation a1, mou01.profile p1 " + "where p1.profile_oracle <> 'DEFAULT' " + "and p1.profile_oracle is not null " + "and p1.profile_oracle <> '" + OracleProfile + "' " + "and a1.id_profile=p1.id_profile " + "and u1.id_user_=a1.id_user_ " + ") " + "and p.profile_oracle " + strOracleProfileNull + "union " + //-- Orange ( Pas affecté - Pas autre profile possible ) "select du.username,u.user_, du.profile,p.profile_oracle,'orange' as color from dba_users du,mou01.affectation a, mou01.profile p, mou01.user_ u " + "where p.id_profile=a.id_profile " + "and u.id_user_=a.id_user_ " + "and u.user_oracle=du.username " + "and du.profile<>nvl(p.profile_oracle,'DEFAULT') " + "and du.username not in " + "(select u1.user_oracle from mou01.user_ u1,mou01.affectation a1, mou01.profile p1 " + "where p1.profile_oracle <> 'DEFAULT' " + "and p1.profile_oracle is not null " + "and p1.profile_oracle<> '" + OracleProfile + "' " + "and a1.id_profile=p1.id_profile " + "and u1.id_user_=a1.id_user_ " + ") " + "and p.profile_oracle " + strOracleProfileNull + "union " + //-- Rouge ( Pas affecté - Autre profile possible ) "select du.username,u.user_, du.profile,p.profile_oracle,'rouge' as color from dba_users du,mou01.affectation a, mou01.profile p, mou01.user_ u " + "where p.id_profile=a.id_profile " + "and u.id_user_=a.id_user_ " + "and u.user_oracle=du.username " + "and du.profile<>nvl(p.profile_oracle,'DEFAULT') " + "and du.username in " + "(select u1.user_oracle from mou01.user_ u1,mou01.affectation a1, mou01.profile p1 " + "where p1.profile_oracle <> 'DEFAULT' " + "and p1.profile_oracle is not null " + "and p1.profile_oracle <> '" + OracleProfile + "' " + "and a1.id_profile=p1.id_profile " + "and u1.id_user_=a1.id_user_ " + ") " + "and p.profile_oracle " + strOracleProfileNull + ") f " + "order by f.username"; } bool bResult = false; try { sqlCommand = new OracleCommand(sql, connection); sqlReader = sqlCommand.ExecuteReader(); //lecture des données while (sqlReader.Read()) { OracleProfile newOracleProfile = new OracleProfile(); newOracleProfile.UserOracle = sqlReader.GetValue(0).ToString(); newOracleProfile.UserName = sqlReader.GetValue(1).ToString(); newOracleProfile.OracleProfileAffected = sqlReader.GetValue(2).ToString(); newOracleProfile.OracleProfileSel = sqlReader.GetValue(3).ToString(); newOracleProfile.Color = sqlReader.GetValue(4).ToString(); m_listOracleProfile.Add(newOracleProfile); } bResult = true; } catch (Exception error) { MessageBox.Show("Message d’erreur : " + error.Message); } finally { try { // Fermeture de la base de données if (sqlReader != null) { sqlReader.Close(); sqlReader.Dispose(); } if (sqlCommand != null) { sqlCommand.Dispose(); } } catch (Exception error) { MessageBox.Show("Message d’erreur : " + error.Message); } } return(bResult); }
public object GetObject(string sql, string _ObjeclClassName, string _NameSpaceName) { if (sql == null || sql.Trim().Equals("")) { return(null); } OracleDataReader reader = GetTableData(sql); try { if (reader.Read()) { Assembly assembly = Assembly.Load(_NameSpaceName); object obj = assembly.CreateInstance(_ObjeclClassName); if (null == obj) { return(null); } Type type = obj.GetType(); PropertyInfo[] ps = type.GetProperties(); foreach (PropertyInfo pi in ps) { string _fieldName = pi.Name; string _typeName = pi.PropertyType.Name; if (IsContainColumn(reader, _fieldName)) //查询的结果包含列 { if (_typeName.ToLower() == "string") //赋字符串 { pi.SetValue(obj, reader[_fieldName].ToString().Trim(), null); } else if (_typeName.ToLower().Contains("int")) //赋整数 { pi.SetValue(obj, ConvertToInt32(reader[_fieldName].ToString().Trim()), null); } else if (_typeName.ToLower().Contains("datetime")) //赋整数 { pi.SetValue(obj, ConvertToDatetime(reader[_fieldName]), null); } else if (_typeName.ToLower().Contains("date")) //赋整数 { pi.SetValue(obj, ConvertToDatetime(reader[_fieldName]), null); } else if (_typeName.ToLower().Contains("byte")) //赋byte { pi.SetValue(obj, reader[_fieldName], null); } else //赋double { pi.SetValue(obj, ConvertToDouble(reader[_fieldName].ToString().Trim()), null); } } } return(obj); } } catch (Exception e) { throw e; } finally { reader.Close(); reader.Dispose(); CloseConnection(); } return(null); }
protected void Page_Load(object sender, EventArgs e) { try { string v_ver = ""; if ((Request.Params["version"] != null)) { v_ver = Request.Params["version"].ToString(); //Y = 啟動 , N = 停用 Sql_str = " SELECT TITTLE, CONTENT,VERSION FROM PDA_NOTIFICATION WHERE STATUS = 'Y' AND VERSION > '" + v_ver + "' "; //建立連線 //使用web.config conn string OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["jheip"].ConnectionString); conn.Open(); OracleCommand cmd = new OracleCommand(Sql_str, conn); OracleDataReader dr = cmd.ExecuteReader(); try { //while (dr.Read()) //{ //組成JSON字串 if (dr.HasRows) { var dataQuery = from d in dr.Cast <DbDataRecord>() select new { STATUS = "PASS", TITTLE = (String)dr["TITTLE"], CONTENT = (String)dr["CONTENT"], VERSION = (String)dr["VERSION"] }; string v_json = (JsonConvert.SerializeObject(dataQuery)); //tsconn.save_log("guest", "NOTFICATION PASS", "192.168.0.19", v_json); Response.Write(v_json); //} } } catch (Exception ex) { Response.Write(ex.ToString()); } finally { cmd.Dispose(); dr.Close(); dr.Dispose(); conn.Close(); conn.Dispose(); } } } catch (Exception ex) { var dataQuery = new { STATUS = "REJECT" }; string v_json = (JsonConvert.SerializeObject(dataQuery)); //tsconn.save_log("guest", "NOTFICATION REJECT", "192.168.0.19", v_json); Response.Write(v_json); } }
/// <summary> /// Executes the query using the information in the passed <paramref name="binder"/>. When no rows are found, an empty list is returned. /// </summary> /// <typeparam name="T">The type of each row in the list of results</typeparam> /// <param name="xmlQuery"></param> /// <param name="binder">Information which describes the query to execute</param> /// <param name="maxRows">Maximum number of rows to retrieve. 0 means all.</param> /// <returns>A list of rows returned by the query</returns> /// <exception cref="ArgumentNullException"><paramref name="binder"/> is null</exception> /// <exception cref="OperationCanceledException">Number of rows retturned by query exceeded <see cref="DefaultMaxRows"/></exception> /// <exception cref="OracleException">Query execution error</exception> /// <remarks> /// <para> /// The connection is opened if it is not already open. /// </para> /// <para> /// Sharad 17 Nov 2011: Added new parameter <paramref name="maxRows"/> /// </para> /// </remarks> public IList <T> ExecuteReader <T>(string xmlQuery, SqlBinder <T> binder, int maxRows) { if (binder == null) { throw new ArgumentNullException("binder"); } OracleCommand cmd = null; OracleDataReader reader = null; try { cmd = CreateCommand(xmlQuery, binder.GetParameter, binder.ActionName); //PrepareConnection(binder.ActionName); reader = cmd.ExecuteReader(maxRows == 1 ? CommandBehavior.SingleRow : CommandBehavior.Default); if (reader.RowSize > 0 && maxRows > 0) { // Safety check. FetchSize optimization is applied only if RowSize is known and maxRows is specified // Treat default Fetch Size specified in configuration as the maximum allowed. // See whether we can reduce the fetch size to save memory reader.FetchSize = Math.Min(reader.RowSize * maxRows, reader.FetchSize); } // Lazy loading will not work because the map context will not be available for mapping //var rowcount = 0; //foreach (var result in from object row in reader select binder.Mapper.Engine.Map<IOracleDataRow, T>(dict)) var results = binder.MapRows(reader).Take(maxRows > 0 ? maxRows : this.DefaultMaxRows).ToList(); if (maxRows == 0 && reader.Read()) { // User did not specify maxRows and we did not read all of them. Generate error. var msg = string.Format("Query aborted because more than {0} rows were retrieved", this.DefaultMaxRows); if (_traceContext != null) { _traceContext.Warn(msg); } throw new OperationCanceledException(msg); //} } if (_traceContext != null) { _traceContext.Write(string.Format("ExecuteReader returned {0} rows", results.Count)); } var b = reader.NextResult(); return(results); } catch (OracleException ex) { if (_traceContext != null) { this._traceContext.Warn("Exception", "", ex); } if (ex.Number == 28150) { var userId = _builder.UserID; var proxyUser = _builder.ProxyUserId; var msg = string.Format("User '{0}' was not authenticated to connect as proxy user", userId) + "\n\n Excute following script to get rid of this error: \n\n" + string.Format("ALTER USER {0} GRANT CONNECT THROUGH {1};", userId, proxyUser); throw new AuthenticationException(msg); } throw new OracleDataStoreException(ex, cmd); } catch (Exception ex) { if (cmd == null) { throw; } // Include the query as part of the exception throw new Exception(cmd.CommandText, ex); } finally { if (reader != null) { reader.Dispose(); } if (cmd != null) { foreach (OracleParameter parameter in cmd.Parameters) { parameter.Dispose(); } cmd.Dispose(); } QueryLogging.TraceQueryEnd(_traceContext); } }
/// <summary> /// 打印收款凭证(寄存收费) /// </summary> /// <param name="fa001"></param> public static void Print_Skpz1(string fa001) { //结算数据 OracleCommand oc_jsd = new OracleCommand("select * from v_fa01 where fa001= :fa001", SqlHelper.conn); OracleParameter op_fa001 = new OracleParameter("fa001", OracleDbType.Varchar2, 10); op_fa001.Direction = ParameterDirection.Input; op_fa001.Value = fa001; OracleParameter op_sa010 = new OracleParameter("sa010", OracleDbType.Varchar2, 10); op_sa010.Direction = ParameterDirection.Input; op_sa010.Value = fa001; oc_jsd.Parameters.Add(op_fa001); //结算明细数据 OracleCommand oc_detail = new OracleCommand("select * from v_sa01 where sa010= :sa010", SqlHelper.conn); oc_detail.Parameters.Add(op_sa010); OracleDataReader reader = oc_jsd.ExecuteReader(); OracleDataReader reader2 = oc_detail.ExecuteReader(); string s_skr = string.Empty; string s_skrq = string.Empty; string s_cuname = string.Empty; string s_memo = string.Empty; int i_count = 0; if (reader.HasRows && reader.Read()) { StringBuilder sb_1 = new StringBuilder(100); while (reader2.Read()) { sb_1.Append(reader2["SA002"].ToString() + PADSTR); // 服务商品类别 sb_1.Append(reader2["SA003"].ToString() + PADSTR); // 服务或商品名 sb_1.Append(reader2["PRICE"].ToString() + PADSTR); // 单价 sb_1.Append(reader2["NUMS"].ToString() + PADSTR); // 数量 sb_1.Append(reader2["SA007"].ToString() + PADSTR); // 销售金额 sb_1.Append(PADSTR2); i_count++; } s_skr = MiscAction.Mapper_Operator(reader["FA100"].ToString()); s_skrq = string.Format("{0:yyyyMMdd}", reader["FA200"]); s_cuname = reader["FA003"].ToString(); Send_PrintData printData = new Send_PrintData(); printData.command = "print_skpz1"; printData.data = sb_1.ToString(); printData.extra1 = s_cuname; printData.extra2 = s_skr; printData.extra3 = s_skrq; printData.extra4 = RegAction.GetRegFinMemo(fa001); //i_pages = (int)Math.Ceiling(i_count / (App_Const.BILL_COUNT * 1.0)); //XtraMessageBox.Show("现在开始打印【结算单】,共需要" + i_pages.ToString() + "张!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); Frm_main.socket.sendMsg(Tool.ConvertObjectToJson(printData)); } else { MessageBox.Show("未找到结算数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error); } reader.Dispose(); reader2.Dispose(); oc_jsd.Dispose(); }
protected override void DoDiscoverWork(object sender, DoWorkEventArgs e) { string sql = "SELECT O.object_name, O.created, O.last_ddl_time, O.status, S.text," + " COALESCE(E.IsError, 0) AS IsError" + " FROM dba_objects O" + " JOIN dba_source S" + " ON S.owner = O.owner" + " AND S.name = O.object_name" + " AND S.type = O.object_type" + " AND S.line = 1" + " LEFT OUTER JOIN" + " (SELECT DISTINCT E.name, 1 AS IsError" + " FROM User_Errors E" + " WHERE E.type = 'PACKAGE') E" + " ON E.name = O.object_name" + " WHERE O.owner = '" + schemaName + "'" + " AND O.object_type = 'PACKAGE'" + " ORDER BY O.object_name"; OracleCommand cmd = new OracleCommand(sql, dbase.OraConnection); try { SchemataPackage package; OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { if (Logger.IsEnabled) { Logger.WriteLine(schemaName + "." + reader.GetString(0)); } package = new SchemataPackage(dbase, srvnode, reader.GetString(0)); package.AddProperty("Name", reader.GetString(0)); package.AddProperty("Created", reader.GetDateTime(1).ToString()); package.AddProperty("Last DDL Time", reader.GetDateTime(2).ToString()); package.AddProperty("Status", reader.GetString(3)); string preamble = reader.GetString(4).ToString().Trim().ToLower(); package.IsWrapped = preamble.Contains("wrapped"); package.AddProperty("Wrapped", package.IsWrapped ? "Yes" : "No"); if ((int)reader.GetDecimal(5) == 1) { package.ForeColor = System.Drawing.Color.Red; } else if (reader.GetString(3) == "INVALID") { package.ForeColor = System.Drawing.Color.Green; } discoveries.Add(package); } reader.Close(); reader.Dispose(); reader = null; } catch (Exception exc) { River.Orqa.Dialogs.ExceptionDialog.ShowException(exc); } cmd.Dispose(); cmd = null; }
/// <summary> /// 打印火化证明 /// </summary> /// <param name="ac001"></param> public static void Print_HHZM(string ac001) { StringBuilder sb_1 = new StringBuilder(200); OracleCommand oc_command = new OracleCommand("select * from V_PRINT_HHZM where ac001 = :ac001", SqlHelper.conn); OracleParameter op_ac001 = new OracleParameter("ac001", OracleDbType.Varchar2, 10); op_ac001.Direction = ParameterDirection.Input; op_ac001.Value = ac001; oc_command.Parameters.Add(op_ac001); OracleDataReader reader = oc_command.ExecuteReader(); if (reader.HasRows && reader.Read()) { if (reader["MM"] == null || reader["MM"] is DBNull) { sb_1.Append("" + PADSTR); //密码 } else { sb_1.Append(reader["MM"].ToString() + PADSTR); } if (reader["AC003"] == null || reader["AC003"] is DBNull) { sb_1.Append("" + PADSTR); //逝者姓名 } else { sb_1.Append(reader["AC003"].ToString() + PADSTR); } if (reader["AC002"] == null || reader["AC002"] is DBNull) { sb_1.Append("" + PADSTR); //逝者性别 } else { sb_1.Append(reader["AC002"].ToString() + PADSTR); } if (reader["AC004"] == null || reader["AC004"] is DBNull) { sb_1.Append("" + PADSTR); //逝者年龄 } else { sb_1.Append(reader["AC004"].ToString() + PADSTR); } if (reader["AC008"] == null || reader["AC008"] is DBNull) { sb_1.Append("" + PADSTR); //住址 } else { sb_1.Append(reader["AC008"].ToString() + PADSTR); } if (reader["AC005"] == null || reader["AC005"] is DBNull) { sb_1.Append("" + PADSTR); //死因 } else { sb_1.Append(reader["AC005"].ToString() + PADSTR); } if (reader["FIRETIME"] == null || reader["FIRETIME"] is DBNull) { sb_1.Append("" + PADSTR); //火化时间 } else { sb_1.Append(reader["FIRETIME"].ToString() + PADSTR); } sb_1.Append(Envior.cur_user.UC003 + PADSTR); //经办人 if (reader["FIRETIME"] == null || reader["FIRETIME"] is DBNull) { sb_1.Append("" + PADSTR); //经办时间 } else { sb_1.Append(reader["FIRETIME"].ToString() + PADSTR); } sb_1.Append(reader["UNITNAME"].ToString() + PADSTR); //单位 Send_PrintData printData = new Send_PrintData(); printData.command = "hhzm"; printData.data = sb_1.ToString(); Frm_main.socket.sendMsg(Tool.ConvertObjectToJson(printData)); } else { XtraMessageBox.Show("未找到数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error); } reader.Dispose(); oc_command.Dispose(); }
public CargaDados Consultar(string carga) { CargaDados objCarga = new CargaDados(); ConexaoOracle objConexao = new ConexaoOracle(); try { objConexao.AdicionarParametro("P_CARGA", carga); objConexao.AdicionarParametroCursor("DADOS"); OracleDataReader leitor = objConexao.ObterLeitor("FUN_PKG_CARGA_DADOS.CONSULTAR"); objCarga.de_para = new List <CargaDadosDePara>(); while (leitor.Read()) { CargaDadosDePara objDePara = new CargaDadosDePara(); objCarga.id_carga = int.Parse(leitor["ID_CARGA"].ToString()); objCarga.carga = leitor["NM_CARGA"].ToString(); objCarga.titulo = leitor["TITULO"].ToString(); objCarga.carga_extensao = leitor["EXTENSAO"].ToString(); objCarga.pkg_listar = leitor["NM_PKG_LISTAR"].ToString(); objCarga.pkg_deletar = leitor["NM_PKG_DELETAR"].ToString(); if (leitor["ID_CARGA_TIPO"] != null) { objCarga.tipo = Convert.ToInt32(leitor["ID_CARGA_TIPO"]); } if (!leitor["TABELA_DESTINO"].ToString().Equals("")) { objDePara.tabela_destino = leitor["TABELA_DESTINO"].ToString(); objDePara.origem_campo = leitor["ORIGEM_CAMPO"].ToString(); objDePara.destino_campo = leitor["DESTINO_CAMPO"].ToString(); if (leitor["ORIGEM_TIPO"] != null) { objDePara.origem_tipo = int.Parse(leitor["ORIGEM_TIPO"].ToString()); } if (leitor["DESTINO_TIPO"] != null) { objDePara.destino_tipo = int.Parse(leitor["DESTINO_TIPO"].ToString()); } if (leitor["VALOR_PADRAO"] != null) { objDePara.destino_valor_padrao = leitor["VALOR_PADRAO"].ToString(); } if (leitor["NUM_ORDEM"] != null) { objDePara.ordem = int.Parse(leitor["NUM_ORDEM"].ToString()); } objCarga.de_para.Add(objDePara); } } leitor.NextResult(); leitor.Dispose(); } catch (Exception ex) { throw new Exception("Problemas contate o administrador do sistema [CargaDadosDAL.Consultar]: //n" + ex.Message); } finally { objConexao.Dispose(); } return(objCarga); }
/// <summary> /// 打印 迁出通知单 /// </summary> /// <param name="rc001"></param> public static void Print_OutCard(string rc001) { StringBuilder sb_1 = new StringBuilder(200); OracleCommand oc_command = new OracleCommand("select * from v_outcard where rc001 = :rc001", SqlHelper.conn); OracleParameter op_rc001 = new OracleParameter("rc001", OracleDbType.Varchar2, 10); op_rc001.Direction = ParameterDirection.Input; op_rc001.Value = rc001; oc_command.Parameters.Add(op_rc001); OracleDataReader reader = oc_command.ExecuteReader(); if (reader.HasRows && reader.Read()) { sb_1.Append(reader["RC003"].ToString().Trim() + PADSTR); //逝者姓名 sb_1.Append(reader["RC109"].ToString().Trim() + PADSTR); //寄存证号 sb_1.Append(reader["POSITION"].ToString() + PADSTR); //寄存位置 if (reader["RC050"] == null || reader["RC050"] is DBNull) { sb_1.Append("" + PADSTR); //家属姓名 } else { sb_1.Append(reader["RC050"].ToString().Trim() + PADSTR); } if (reader["RC051"] == null || reader["RC051"] is DBNull) { sb_1.Append("" + PADSTR); //联系电话 } else { sb_1.Append(reader["RC051"].ToString().Trim() + PADSTR); } if (reader["OC002"] == null || reader["OC002"] is DBNull) { sb_1.Append("" + PADSTR); //迁出日期 } else { sb_1.Append(string.Format("{0:yyyy-MM-dd}", reader["OC002"]) + PADSTR); } if (reader["RC055"] == null || reader["RC055"] is DBNull) { sb_1.Append("" + PADSTR); //联系地址 } else { sb_1.Append(reader["RC055"].ToString().Trim() + PADSTR); } if (reader["OC005"] == null || reader["OC005"] is DBNull) { sb_1.Append("" + PADSTR); //迁出原因 } else { sb_1.Append(reader["OC005"].ToString().Trim() + PADSTR); } Send_PrintData printData = new Send_PrintData(); printData.command = "OutCard"; printData.data = sb_1.ToString(); Frm_main.socket.sendMsg(Tool.ConvertObjectToJson(printData)); } reader.Dispose(); oc_command.Dispose(); }
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { Session["pozaAngajat"] = null; Session["postedFile"] = null; salarBazaTB.Text = 0.ToString(); sporProcentTB.Text = 0.ToString(); premiiBruteTB.Text = 0.ToString(); totalBrutTB.Text = 0.ToString(); casTB.Text = 0.ToString(); cassTB.Text = 0.ToString(); brutImpozabilTB.Text = 0.ToString(); impozitTB.Text = 0.ToString(); retineriTB.Text = 0.ToString(); viratCardTB.Text = 0.ToString(); try { con.Open(); string cmd = "select casprocent, cassprocent, impozitprocent from procente where id=1"; oCmd = new OracleCommand(cmd, con); oDr = oCmd.ExecuteReader(); if (oDr.HasRows) { oDr.Read(); cas = Int32.Parse(oDr["casprocent"].ToString().Trim()); cass = Int32.Parse(oDr["cassprocent"].ToString().Trim()); impozit = Int32.Parse(oDr["impozitprocent"].ToString().Trim()); } oDr.Dispose(); oCmd.Dispose(); LabelPrincipalErr.Text = ""; LabelCAS.Text = "CAS (" + cas + "%)"; LabelCASS.Text = "CASS (" + cass + "%)"; LabelImpozit.Text = "Impozit (" + impozit + "%)"; } catch (Exception ex) { LabelPrincipalErr.ForeColor = Color.Red; LabelPrincipalErr.Text = "Nu se poate realiza conexiunea la baza de date: " + ex.Message; } finally { con.Close(); } } //If first time page is submitted and we have file in FileUpload control but not in session // Store the values to SEssion Object if (Session["pozaAngajat"] == null && pozaAngajat.HasFile != false && Session["postedFile"] == null) { var img = Server.MapPath(ClientID + ".jpg"); pozaAngajat.PostedFile.SaveAs(img); Session["pozaAngajat"] = ClientID + ".jpg"; Session["postedFile"] = pozaAngajat.PostedFile; pozaAngajatImg.ImageUrl = ClientID + ".jpg"; pozaAngajatImg.Attributes.Add("style", "visibility:visible;"); //pfile = (HttpPostedFile)Session["postedFile"]; //pfile.SaveAs(Server.MapPath("test.jpg")); //pozaAngajatImg.ImageUrl = "test.jpg"; } // Next time submit and Session has values but FileUpload is Blank // Return the values from session to FileUpload else if (Session["pozaAngajat"] != null && pozaAngajat.HasFile == false && Session["postedFile"] != null) { pozaAngajatImg.ImageUrl = Session["pozaAngajat"].ToString(); pozaAngajatImg.Attributes.Add("style", "visibility:visible;"); //pfile = (HttpPostedFile)Session["postedFile"]; //pfile.SaveAs(Server.MapPath("test.jpg")); //pozaAngajatImg.ImageUrl = "test.jpg"; } // Now there could be another sictution when Session has File but user want to change the file // In this case we have to change the file in session object else if (pozaAngajat.HasFile != false) { var img = Server.MapPath(ClientID + ".jpg"); pozaAngajat.PostedFile.SaveAs(img); Session["pozaAngajat"] = ClientID + ".jpg"; Session["postedFile"] = pozaAngajat.PostedFile; pozaAngajatImg.ImageUrl = ClientID + ".jpg"; pozaAngajatImg.Attributes.Add("style", "visibility:visible;"); //pfile = (HttpPostedFile)Session["postedFile"]; //pfile.SaveAs(Server.MapPath("test.jpg")); //pozaAngajatImg.ImageUrl = "test.jpg"; } }
public static List <List <string> > ToTableList(this OracleDataReader reader) { //所有行 var rows = new List <List <string> >(); if (reader.HasRows) { while (reader.Read()) { //逐行添加内容 var cols = new List <string>(); for (var i = 0; i < reader.FieldCount; i++) { var value = ""; #region 根据格式取值 var type = reader.GetDataTypeName(i).ToLower(); if (reader.IsDBNull(i)) { value = ""; } else if (type == ("smallint")) { value = reader.GetInt16(i) + ""; } else if (type == ("int64")) { value = reader.GetInt64(i) + ""; } else if (type.Contains("int")) { value = reader.GetInt32(i) + ""; // //var v = reader.GetInt32(i); //if (v > 1000000000) //{ // value = reader.GetInt32(i).ToDateTime().ToStr(); //} //else //{ // value = reader.GetInt32(i) + ""; //} } else if (type.Contains("clob")) { value = reader.GetOracleClob(i).Value + ""; } else if (type.Contains("float")) { value = reader.GetDouble(i) + ""; } else if (type.Contains("datetime") || type.Contains("date")) { value = reader.GetDateTime(i).ToString("yyyy-MM-dd HH:mm") + ""; } else if (type.Contains("char") || type.Contains("text") || type.Contains("varchar")) { value = reader.GetString(i) + ""; } else if (type.Contains("decimal")) { value = reader.GetOracleDecimal(i) + ""; } else if (type.Contains("date")) { var temp = reader.GetOracleDate(i); if (!temp.IsNull) { value = temp.Value.FormatTime(); } } else { value = reader.GetString(i) + ""; // Replace("%", "");//2016-09-05 配合报表转义规则 2016-09-10 更新规则 //if (value.Contains("%")) // throw new Exception("替换不完全!"); } #endregion cols.Add(value); } rows.Add(cols); } } reader.Close(); reader.Dispose(); return(rows); }
private void button2_Click(object sender, EventArgs e) { string inv = this.textBox1.Text; string cust = this.textBox3.Text; string mob = this.textBox14.Text; string add = this.richTextBox1.Text; string amount = this.textBox12.Text; string tax = this.textBox11.Text; string dis = this.textBox10.Text; if (string.IsNullOrWhiteSpace(textBox10.Text)) { dis = "0"; } string gt = this.textBox13.Text; /// pinvoice database insert try { conn.Open(); string sqlquery = "INSERT INTO SALESINVOICE(SALE_ID,SDATE,CUST_NAME,CUST_MOBILE,CUST_ADD,AMOUNT,TAX,DISCOUNT,GTOTAL) VALUES('" + inv + "',TO_DATE('" + tdate + "', 'yyyymmdd'),'" + cust + "','" + mob + "','" + add + "','" + amount + "','" + tax + "','" + dis + "','" + gt + "')"; OracleCommand cmd = new OracleCommand(sqlquery, conn); int i = cmd.ExecuteNonQuery(); if (i > 0) { cmd.Dispose(); MessageBox.Show("Done"); } conn.Close(); } catch (Exception exe) { MessageBox.Show(exe.Message); } ///////////////////////////////////////// string StrQuery; // int j=0; try { if (conn.State != ConnectionState.Open) { conn.Open(); } OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { StrQuery = "INSERT INTO SALES_DETAILS(SALE_ID,MED_NAME,UPRICE,QTY,TOTAL) VALUES ('" + this.textBox1.Text + "','" + dataGridView1.Rows[i].Cells[1].Value + "','" + dataGridView1.Rows[i].Cells[2].Value + "','" + dataGridView1.Rows[i].Cells[3].Value + "','" + dataGridView1.Rows[i].Cells[4].Value + "')"; cmd.CommandText = StrQuery; cmd.ExecuteNonQuery(); } //int i = cmd.ExecuteNonQuery(); cmd.Dispose(); MessageBox.Show("Done"); // this.Hide(); conn.Close(); } catch (Exception exe) { MessageBox.Show(exe.Message); } /////////////////////////////////////////////// try { if (conn.State != ConnectionState.Open) { conn.Open(); } for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { string sid = dataGridView1.Rows[i].Cells[1].Value.ToString(); string sqlquery = "SELECT * FROM MED_STORE WHERE MED_NAME='" + sid + "'"; OracleCommand cmd = new OracleCommand(sqlquery, conn); OracleDataReader r = cmd.ExecuteReader(); r.Read(); // textBox5.Text = r.GetValue(3).ToString(); // MessageBox.Show(r.GetValue(2).ToString()); int qty = int.Parse(r.GetValue(2).ToString()) - int.Parse(dataGridView1.Rows[i].Cells[3].Value.ToString()); r.Dispose(); cmd = new OracleCommand("UPDATE MED_STORE SET MED_QNTY='" + qty + "' WHERE MED_NAME='" + sid + "'", conn); cmd.ExecuteNonQuery(); } conn.Close(); } catch (Exception exe) { MessageBox.Show(exe.Message); } }
/// <summary> /// Executes a search for Providers matching the search keyword. /// </summary> /// <param name="providerSearchKeyword">Keyword for Provider search.</param> /// <returns>A collection of matching Providers.</returns> public List <Provider> GetProviders(string providerSearchKeyword) { OracleConnection connection = null; OracleCommand command = null; OracleParameter providerKeywordIn = null; OracleParameter usernameIn = null; OracleParameter errorMessageOut = null; OracleParameter resultsOut = null; OracleDataReader dr = null; List <Provider> providers = new List <Provider>(); try { // Create Oracle connection connection = new OracleConnection(Utilities.GetDatabaseConnection()); connection.Open(); // Create Oracle command command = new OracleCommand(Constants.PROVIDER_SEARCH_SP, connection); command.CommandType = CommandType.StoredProcedure; // Input parameters providerKeywordIn = new OracleParameter( Constants.ProviderSearchParameters.PROVIDER_KEYWORD, OracleDbType.Varchar2, providerSearchKeyword, ParameterDirection.Input); command.Parameters.Add(providerKeywordIn); usernameIn = new OracleParameter( Constants.ProviderSearchParameters.EMAIL, OracleDbType.Varchar2, "CourseSearchSvc", ParameterDirection.Input); command.Parameters.Add(usernameIn); // Output parameters errorMessageOut = new OracleParameter( Constants.ProviderSearchParameters.ERROR_MSG, OracleDbType.Varchar2, ParameterDirection.Output); command.Parameters.Add(errorMessageOut); resultsOut = new OracleParameter( Constants.ProviderSearchParameters.RESULTS_CURSOR, OracleDbType.RefCursor, ParameterDirection.Output); command.Parameters.Add(resultsOut); dr = command.ExecuteReader(); // get error message string errorMessage = null; if (!((OracleString)(command.Parameters[Constants.ProviderSearchParameters.ERROR_MSG].Value)).IsNull) { errorMessage = command.Parameters[Constants.ProviderSearchParameters.ERROR_MSG].Value.ToString(); } if ((errorMessage == null || errorMessage.Length == 0) && dr.HasRows) { while (dr.Read()) { Provider provider = new Provider(); provider.ProviderId = dr[Constants.ProviderSearchColumns.PROVIDER_ID].ToString(); provider.ProviderName = dr[Constants.ProviderSearchColumns.PROVIDER_NAME].ToString(); provider.AddressLine1 = dr[Constants.ProviderSearchColumns.ADDRESS_LINE1].ToString(); provider.AddressLine2 = dr[Constants.ProviderSearchColumns.ADDRESS_LINE2].ToString(); provider.Town = dr[Constants.ProviderSearchColumns.TOWN].ToString(); provider.County = dr[Constants.ProviderSearchColumns.COUNTY].ToString(); provider.Postcode = dr[Constants.ProviderSearchColumns.POSTCODE].ToString(); provider.Phone = dr[Constants.ProviderSearchColumns.PHONE].ToString(); provider.Email = dr[Constants.ProviderSearchColumns.EMAIL].ToString(); provider.Fax = dr[Constants.ProviderSearchColumns.FAX].ToString(); provider.Website = dr[Constants.ProviderSearchColumns.WEBSITE].ToString(); provider.Ukprn = dr[Constants.ProviderSearchColumns.UKPRN].ToString(); provider.Upin = dr[Constants.ProviderSearchColumns.UPIN].ToString(); provider.TFPlusLoans = Convert.ToBoolean(dr[Constants.ProviderSearchColumns.TFPLUSLOANS].ToString()); providers.Add(provider); } } else { // throw error? } } catch (Exception e) { } finally { // clean up after call ... dr.Dispose(); resultsOut.Dispose(); errorMessageOut.Dispose(); usernameIn.Dispose(); providerKeywordIn.Dispose(); command.Dispose(); connection.Dispose(); } return(providers); }
/// <summary> /// Get Store Name, Indicator /// </summary> /// <param name="model"></param> /// <returns></returns> public List <Dictionary <string, object> > GetExcelConfigData(ExcelXmlModel model) { //Dictionary<string, object> dicParas = new Dictionary<string, object>(); //Dictionary<string, object> dicParaOuts = new Dictionary<string, object>(); List <Dictionary <string, object> > lst_store = new List <Dictionary <string, object> >(); List <Dictionary <string, object> > lst_kpi = new List <Dictionary <string, object> >(); List <Dictionary <string, object> > ouput = new List <Dictionary <string, object> >(); try { string procedureName = null; if (model.ExcelXmlCommon.exportDirectory.Equals("PL_CongTy")) { procedureName = "pws_getreporsetupcompany_excel"; } else { procedureName = "pws_getreporsetupstore_excel"; } this.ConnectOracleDB.command.Parameters.Clear(); var op = new OracleParameter("PK_SRPTNBR_para", OracleDbType.Varchar2); op.Direction = ParameterDirection.Input; op.Value = model.ExcelXmlCommon.ReportName; this.ConnectOracleDB.command.Parameters.Add(op); this.ConnectOracleDB.command.Parameters.Add("p_recordset", OracleDbType.RefCursor).Direction = ParameterDirection.Output; this.ConnectOracleDB.command.CommandText = procedureName; this.ConnectOracleDB.command.CommandType = CommandType.StoredProcedure; OracleDataReader objReader = this.ConnectOracleDB.command.ExecuteReader(); while (objReader.Read()) { var columns = Enumerable.Range(0, objReader.FieldCount).Select(objReader.GetName).ToList(); Dictionary <string, object> dic = new Dictionary <string, object>(); for (int i = 0; i < columns.Count; i++) { foreach (ExcelXMLColumn xmlColumn in model.LstColumn) { if (!xmlColumn.IsGetData) { continue; } if (xmlColumn.Name != columns[i].ToLower()) { continue; } if ((objReader[xmlColumn.Name] != null) && (xmlColumn.IsGetData)) { dic.Add(xmlColumn.Name, objReader[xmlColumn.Name]); } } } ouput.Add(dic); //objReader.NextResult(); } objReader.Close(); objReader.Dispose(); } catch (Exception ex) { } finally { this.ConnectOracleDB.CloseDBConnect(); } return(ouput); }
public List <Dictionary <string, object> > GetStoreDataInventory(ExcelXmlModel model, int month, int year) { //Dictionary<string, object> dicParas = new Dictionary<string, object>(); //Dictionary<string, object> dicParaOuts = new Dictionary<string, object>(); List <Dictionary <string, object> > lst_store = new List <Dictionary <string, object> >(); List <Dictionary <string, object> > lst_kpi = new List <Dictionary <string, object> >(); List <Dictionary <string, object> > ouput = new List <Dictionary <string, object> >(); try { this.ConnectOracleDB.command.Parameters.Clear(); var time = month.ToString() + "-" + year.ToString().Substring(2, 2); if (month < 10) { time = '0' + time; } var dateTime = new OracleParameter("dateTime", OracleDbType.Varchar2); dateTime.Direction = ParameterDirection.Input; dateTime.Value = time; this.ConnectOracleDB.command.Parameters.Add(dateTime); this.ConnectOracleDB.command.Parameters.Add("p_recordset", OracleDbType.RefCursor).Direction = ParameterDirection.Output; this.ConnectOracleDB.command.CommandText = "PWS_GETINVENTORYDATA_EXCEL"; this.ConnectOracleDB.command.CommandType = CommandType.StoredProcedure; OracleDataReader objReader = this.ConnectOracleDB.command.ExecuteReader(); while (objReader.Read()) { var columns = Enumerable.Range(0, objReader.FieldCount).Select(objReader.GetName).ToList(); Dictionary <string, object> dic = new Dictionary <string, object>(); for (int i = 0; i < columns.Count; i++) { foreach (ExcelXMLColumn xmlColumn in model.LstColumn) { if (!xmlColumn.IsGetData) { continue; } if (xmlColumn.Name.ToLower() != columns[i].ToLower()) { continue; } if ((objReader[xmlColumn.Name] != null) && (xmlColumn.IsGetData)) { dic.Add(xmlColumn.Name, objReader[xmlColumn.Name]); } } } ouput.Add(dic); //objReader.NextResult(); } objReader.Close(); objReader.Dispose(); } catch (Exception ex) { } finally { this.ConnectOracleDB.CloseDBConnect(); } return(ouput); }
/// <summary> /// 读取发票信息 /// </summary> private void ReadInvoiceBaseInfo() { OracleDataReader reader = SqlAssist.ExecuteReader("select * from sp01 where sp001 < '0000000100' "); if (reader.HasRows) { while (reader.Read()) { if (reader["SP002"].ToString() == "tax_no") //纳税人识别号 { Envior.TAX_ID = reader["SP005"].ToString(); } else if (reader["SP002"].ToString() == "tax_addrtele") //税票-销方地址电话 { Envior.TAX_ADDR_TELE = reader["SP005"].ToString(); } else if (reader["SP002"].ToString() == "tax_bankaccount") //税票-销方银行账号 { Envior.TAX_BANK_ACCOUNT = reader["SP005"].ToString(); } else if (reader["SP002"].ToString() == "tax_appid") //税票-应用账号 { Envior.TAX_APPID = reader["SP005"].ToString(); } else if (reader["SP002"].ToString() == "tax_invoicetype") //税票-发票类型 { Envior.TAX_INVOICE_TYPE = reader["SP005"].ToString(); } else if (reader["SP002"].ToString() == "tax_server_url") //税票-服务请求URL { Envior.TAX_SERVER_URL = reader["SP005"].ToString(); } else if (reader["SP002"].ToString() == "tax_publickey") //税票-公钥 { Envior.TAX_PUBLIC_KEY = reader["SP005"].ToString(); } else if (reader["SP002"].ToString() == "tax_privatekey") //税票-私钥 { Envior.TAX_PRIVATE_KEY = reader["SP005"].ToString(); } else if (reader["SP002"].ToString() == "tax_cashier") //税票-收款人 { Envior.TAX_CASHIER = reader["SP005"].ToString(); } else if (reader["SP002"].ToString() == "tax_checker") //税票-复核人 { Envior.TAX_CHECKER = reader["SP005"].ToString(); } else if (reader["SP002"].ToString() == "tax_max_fee") { decimal dec_max; if (decimal.TryParse(reader["SP006"].ToString(), out dec_max)) { Envior.TAX_MAX_FEE = dec_max; } else { Envior.TAX_MAX_FEE = 0; } } } } reader.Dispose(); }