public static User GetUser(string username)
{
User user = null;
if (username != null)
{
try
{
using (OracleConnection connection = new OracleConnection(CONNECTION_STRING))
{
OracleCommand command = new OracleCommand();
command.CommandText = "SELECT password,customerId,securityQuestion,securityAnswer,email FROM Users WHERE username LIKE :username";
command.Parameters.Add(":username", OracleDbType.NVarchar2).Value = username;
command.Connection = connection;
connection.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string password = reader["password"].ToString();
string customerId = reader["customerId"].ToString();
string securityQuestion = reader["securityQuestion"].ToString();
string securityAnswer = reader["securityAnswer"].ToString();
string email = reader["email"].ToString();
user = new User(username, password, customerId, securityQuestion, securityAnswer, email);
}
}
}
catch (Exception e)
{
user = null;
Logger.LogException(e);
}
}
return user;
}
public DataTable CheckTableExistence()
{
DataTable dataTable = new DataTable(((Type)recordItem).UnderlyingSystemType.Name);
Type metainformation = Type.GetType("Object.Meta." + dataTable.TableName+ "Metainformation");
string SqlCommand = string.Empty;
SqlCommand = "SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME='" + dataTable.TableName.ToUpper() + "' ";
OracleCommand OracleTriggerCommand = new OracleCommand(SqlCommand, oracleConnection);
OracleDataReader dr = OracleTriggerCommand.ExecuteReader();
if (!dr.HasRows)
{
dataTable = CreateTable(((Type)recordItem).UnderlyingSystemType.Name);
dr = OracleTriggerCommand.ExecuteReader();
}
else
{
dataTable.Columns.Clear();
while (dr.Read())
{
if (!string.IsNullOrEmpty(dr[0].ToString()))
{
Type columnType = typeof(string);
string type = dr[2].ToString();
switch (type)
{
case "NUMBER": columnType = typeof(decimal); break;
}
DataColumn newColumn = new DataColumn(dr[1].ToString(), columnType);
dataTable.Columns.Add(newColumn);
newColumn.AllowDBNull = true;
}
}
}
System.Reflection.FieldInfo[] mi = metainformation.GetFields();
dataTable.Columns["NAME"].AllowDBNull = false;
foreach (System.Reflection.FieldInfo fi in mi)
{
if (fi.Name.StartsWith("isNullable"))
{
dataTable.Columns[fi.Name.Substring(10)].AllowDBNull = false;
}
}
// Assign MetaInformations
//foreach(MemberInfo in Cats)
return dataTable;
}
//krijg alle producten uit DB
public List<Product> GetAll()
{
List<Product> producten = new List<Product>();
using (OracleConnection conn = Database.Instance.GetConnection())
{
conn.Open();
string query = string.Format("Select * from product");
using (OracleCommand cmd = new OracleCommand(query, conn))
{
using (OracleDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Product product = new Product(Convert.ToInt32(reader["ID"]),
reader["productnaam"].ToString(), Convert.ToDecimal(reader["prijs"]),
reader["sale"].ToString()
, Convert.ToInt32(reader["categorie_Id"]), Convert.ToInt32(reader["voorraad"]),
Convert.ToInt32(reader["Specificaties_ID"]));
producten.Add(product);
}
}
}
}
return producten;
}
//发表评论
public ActionResult AddComment(string content)
{
//假设登录用户为123，需换为session
string loginuser = "******";
//string loginuser = Session["username"] as string
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
string selectStr = "select textcommentid from textcomment";
OracleCommand cmd = new OracleCommand(selectStr, conn);
OracleDataReader dr = cmd.ExecuteReader();
int temp = 0;
while (dr.Read())
{
if (Convert.ToInt32(dr[0]) > temp)
{
temp = Convert.ToInt32(dr[0]);
}
}
temp++;
string addStr = "insert into textcomment(textcommentid, textid, content, textcomment_date, username)values(" + temp + "," + textId + ",'" + content + "','" + DateTime.Now.ToString() + "','" + loginuser + "')";
OracleCommand cmd2 = new OracleCommand(addStr, conn);
int insertedLines = cmd2.ExecuteNonQuery();
conn.Close();
return RedirectToAction("Comment", "Account");
}
private static void OracleTest()
{
var connection = new OracleConnectionHolder("192.168.0.108", "customer", "customer");
connection.SetCallback(new UniversalConnectionHolderCallback());
connection.OpenConnection();
var commandCount = 0;
while (true) {
try {
if (connection.IsConnected()) {
using (var command = new OracleCommand("select * from DEMO_USERS", connection.GetOracleConnection()))
using (var reader = command.ExecuteReader()) {
var count = 0;
while (reader.Read()) {
count++;
}
Console.WriteLine(commandCount++ + ") Команда выполнена. Count = " + count);
}
}
}
catch (Exception ex) {
if (!connection.ProcessError(ex)) {
throw;
}
}
finally {
Thread.Sleep(1000);
}
}
}
public List<Assunto> Get()
{
using (OracleConnection conn = BaseDAO.GetConnection())
{
//Define o comando
using (OracleCommand cmd = new OracleCommand("SELECT ID_ASSUNTO, NOME FROM ASSUNTO", conn))
{
List<Assunto> lista = new List<Assunto>();
cmd.CommandType = CommandType.Text;
//Executando o select
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Assunto a = new Assunto();
a.Id_Assunto = Convert.ToInt32(reader["ID_ASSUNTO"]);
a.Nome = Convert.ToString(reader["NOME"]);
lista.Add(a);
}
return lista;
}
}
}
public List<Troca> Get()
{
using (OracleConnection conn = BaseDAO.GetConnection())
{
//Define o comando
using (OracleCommand cmd = new OracleCommand("SELECT ID_TROCA, ID_USUARIO_SOLICITANTE, ID_USUARIO_DETENTOR, DATA_INCLUSAO, DATA_SITUACAO,"
+"SITUACAO, POSICAO_SOLICITANTE, POSICAO_DETENTOR FROM TROCA", conn))
{
List<Troca> lista = new List<Troca>();
cmd.CommandType = CommandType.Text;
//Executando o select
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Troca t = new Troca();
t.Id_Troca = Convert.ToInt32(reader["ID_TROCA"]);
t.Id_Usuario_Solicitante = Convert.ToInt32(reader["ID_USUARIO_SOLICITANTE"]);
t.Id_Usuario_Detentor = Convert.ToInt32(reader["ID_USUARIO_DETENTOR"]);
t.Data_Inclusao = Convert.ToDateTime(reader["DATA_INCLUSAO"]);
t.Data_Situacao = Convert.ToDateTime(reader["DATA_SITUACAO"]);
t.Situacao = Convert.ToInt32(reader["SITUACAO"]);
t.Posicao_Solicitante = Convert.ToInt32(reader["POSICAO_SOLICITANTE"]);
t.Posicao_Detentor = Convert.ToInt32(reader["POSICAO_DETENTOR"]);
lista.Add(t);
}
return lista;
}
}
}
public SesionUsuario inicioSesion(string userCorreo, string userPassword, string tipoUsuario)
{
base.abrirConexion();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conexion;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "pk_usuarios.session_login";
OracleParameter userCorreoP = new OracleParameter("correoLogin", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
userCorreoP.Value = userCorreo;
OracleParameter userPasswordP = new OracleParameter("passwordLogin", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
userPasswordP.Value = userPassword;
//cmd.Parameters.Add("co", OracleDbType.RefCursor).Direction = System.Data.ParameterDirection.Output;
OracleParameter tipoUsuarioP = new OracleParameter("rol", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
tipoUsuarioP.Value = tipoUsuario;
OracleParameter resultado = new OracleParameter("resultado", OracleDbType.RefCursor, System.Data.ParameterDirection.Output);
cmd.Parameters.AddRange(new OracleParameter[] { userCorreoP, userPasswordP, tipoUsuarioP, resultado });
OracleDataReader lectorDatos = cmd.ExecuteReader();
if (lectorDatos.HasRows)
{
lectorDatos.Read();
return new SesionUsuario() { id = lectorDatos.GetString(0), operacionExitosa = true, rol = tipoUsuario };
}
else
return new SesionUsuario() { id = string.Empty, operacionExitosa = false, rol = tipoUsuario };
}
public static Category FindById(int id)
{
string query = "SELECT * FROM TBL_CATEGORY WHERE ID=:id";
using (OracleConnection connection = CreateConnection())
using (OracleCommand command = new OracleCommand(query, connection))
{
command.BindByName = true;
command.Parameters.Add(new OracleParameter("id", id));
using (OracleDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
var dicId = GetCategoryIdFromRecord(reader);
if (!Dictionaries.Categories.ContainsKey(dicId))
{
Dictionaries.Categories.Add(dicId, GetCategoryFromDataRecord(reader));
}
return Dictionaries.Categories[dicId];
}
}
}
return null;
}
public List<Fabricante> Get()
{
using (OracleConnection conn = BaseDAO.GetConnection())
{
//Define o comando
using (OracleCommand cmd = new OracleCommand("SELECT ID_FABRICANTE, NOME FROM FABRICANTE", conn))
{
List<Fabricante> lista = new List<Fabricante>();
cmd.CommandType = CommandType.Text;
//Executando o select
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Fabricante f = new Fabricante();
f.Id_Fabricante = Convert.ToInt32(reader["ID_FABRICANTE"]);
f.Nome = Convert.ToString(reader["NOME"]);
lista.Add(f);
}
return lista;
}
}
}
public static void GetEmployeeMatches(List<Employee> list, DPFP.Sample Sample,int FingerNo)
{
OracleConnection conn = new OracleConnection(ConStr);
Employee info = null;
string FingerDesc = Util.GetFingerDesc(FingerNo);
try
{
string _result = string.Empty;
string sql = "select Empl_Id_No,RTF,RIF,RMF,RRF,RSF,LTF,LIF,LMF,LRF,LSF from EMPTSI";
OracleCommand command = new OracleCommand(sql, conn);
command.CommandType = CommandType.Text;
conn.Open();
OracleDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
info = dcEmployee.GetInfo(reader, Sample, FingerDesc);
AddInfo(list, info);
}
}
if (conn.State == ConnectionState.Open) conn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Selects and retrieves values from the database
/// </summary>
/// <param name="query">The selection statement</param>
/// <returns>A DataTable with the retrieved values></returns>
public static DataTable RetrieveQuery(string query)
{
if (Regex.IsMatch(query, @"-{2,}"))
{
throw new SQLInjectionException();
}
using (OracleConnection c = new OracleConnection(@connectionstring))
{
try
{
c.Open();
OracleCommand cmd = new OracleCommand(@query);
cmd.Connection = c;
try
{
OracleDataReader r = cmd.ExecuteReader();
DataTable result = new DataTable();
result.Load(r);
c.Close();
return result;
}
catch (OracleException e)
{
Console.Write(e.Message);
throw;
}
}
catch (OracleException e)
{
Console.Write(e.Message);
return new DataTable();
}
}
}
void AutocompleteText()
{
textBox5.AutoCompleteMode = AutoCompleteMode.Suggest;
textBox5.AutoCompleteSource = AutoCompleteSource.CustomSource;
AutoCompleteStringCollection coll = new AutoCompleteStringCollection();
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
string sqluery = "SELECT * FROM MED_INFO WHERE MED_MGF='" + comboBox1.Text + "' ORDER BY MED_NAME";
OracleCommand cd = new OracleCommand(sqluery, conn);
OracleDataReader r;
r = cd.ExecuteReader();
while(r.Read())
{
string sn = r.GetString(1);
coll.Add(sn);
}
r.Dispose();
cd.Dispose();
conn.Close();
}
catch (Exception exe)
{
MessageBox.Show(exe.Message);
}
textBox5.AutoCompleteCustomSource = coll;
}
// 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 OracleSessionDetails(OracleDBConnection connection)
{
InitializeComponent();
this.dbConnection = connection;
OracleConnection databaseConnection = dbConnection.getDatabaseConnection();
String query;
OracleCommand command = new OracleCommand("DBMS_SESSION.set_identifier",databaseConnection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(dbConnection.getUsername(), OracleDbType.Char);
command.ExecuteNonQuery();
query = "SELECT SYS_CONTEXT('userenv', 'SID') AS SID, "
+ "SYS_CONTEXT('userenv', 'HOST') AS HOST, "
+ "USER AS username "
+ "FROM dual";
command = new OracleCommand(query, databaseConnection);
OracleDataReader reader;
command.CommandText = query;
command.CommandType = CommandType.Text;
reader = command.ExecuteReader();
while (reader.Read())
{
this.sessionListing.Rows.Add("SID", reader["SID"]);
this.sessionListing.Rows.Add("HOST", reader["HOST"]);
this.sessionListing.Rows.Add("DATABASE", dbConnection.getDatabaseConnection().DataSource);
this.sessionListing.Rows.Add("USER", reader["USERNAME"]);
}
reader.Close();
}
private void button1_Click(object sender, EventArgs e)
{
string connectionstring = "User Id=dbi324175;Password=wgSZJnGDZe;Data Source=fhictora01.fhict.local:1521/fhictora;";
string ConString1 = connectionstring;
using (OracleConnection con = new OracleConnection(ConString1))
{
con.Open();
string query = "select id from product where id = 1";
using (OracleCommand cmd = new OracleCommand(query, con))
{
using (OracleDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
button1.Text = reader["ID"].ToString();
}
}
}
}
}
public List<Account> HaalAccountsOp()
{
List<Account> tempAccounts = new List<Account>();
try
{
conn.Open();
string query = "SELECT * FROM ACCOUNT";
command = new OracleCommand(query, conn);
OracleDataReader dataReader = command.ExecuteReader();
while(dataReader.Read())
{
tempAccounts.Add(new Account(Convert.ToString(dataReader["INLOGNAAM"]), new Vereniging(Convert.ToString(dataReader["VERENIGING_NAAM"])),
Convert.ToString(dataReader["WACHTWOORD"]), Convert.ToString(dataReader["ACCOUNTTYPE"])));
}
return tempAccounts;
}
catch
{
return tempAccounts;
}
finally
{
conn.Close();
}
}
public UsAuth getAuthForIdUser(string email, string passwd)
{
OracleConnection connection = null;
OracleDataReader dr = null;
UsAuth auth = null;
try
{
using (connection = new OracleConnection(OracleHelper.connectionString()))
{
using (var command = new OracleCommand("US_AUTH_GETFOR_USER", connection))
{
connection.Open();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("P_CUR_RESULT", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
command.Parameters.Add("EMAIL", email);
command.Parameters.Add("PASWD", passwd);
using (dr = command.ExecuteReader())
{
if (dr.HasRows)
{
auth = new UsAuth()
{
IdUser = Convert.ToInt32(dr["IDUSER"].ToString()),
Email = dr["EMAIL"].ToString(),
FirstName = dr["FIRSTNAME"].ToString(),
LastName = dr["LASTNAME"].ToString(),
Role = new UsRole()
{
Id = Convert.ToInt32(dr["IDROLE"].ToString()),
Name = dr["NAMEROLE"].ToString()
},
Photo = dr["PHOTO"].ToString(),
ListRoleModule = new DARoleModule().getRoleModuleForRole(Convert.ToInt32(dr["IDROLE"].ToString())),
ListRoleModulePrivilege = new DARoleModulePrivilege().getRoleModulePrivilegeForRole(Convert.ToInt32(dr["IDROLE"].ToString()))
};
}
}
}
}
return auth;
}
catch (Exception e)
{
dr.Dispose();
if (connection.State == ConnectionState.Open)
connection.Dispose();
LogHelper.WriteLog(e);
throw e;
}
finally
{
auth = null;
}
}
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();
}
public List<Editora> Get()
{
using (OracleConnection conn = BaseDAO.GetConnection())
{
//Define o comando
using (OracleCommand cmd = new OracleCommand("SELECT ID_EDITORA, NOME FROM EDITORA", conn))
{
List<Editora> lista = new List<Editora>();
cmd.CommandType = CommandType.Text;
//Executando o select
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Editora e = new Editora();
e.Id_Editora = Convert.ToInt32(reader["ID_EDITORA"]);
e.Nome = Convert.ToString(reader["NOME"]);
lista.Add(e);
}
return lista;
}
}
}
/// <summary>
/// Use only to retrieve user on initialization
/// </summary>
/// <param name="query">The input query</param>
/// <returns>A DataTable containing the results of the query</returns>
public static DataTable HaalGebruikersOp(string query)
{
using (OracleConnection conn = new OracleConnection(connectionstring2))
{
try
{
conn.Open();
OracleCommand cmd = new OracleCommand(query);
cmd.Connection = conn;
try
{
OracleDataReader reader = cmd.ExecuteReader();
DataTable result = new DataTable();
result.Load(reader);
conn.Close();
return result;
}
catch (OracleException ex)
{
Console.WriteLine(ex.Message);
throw;
}
}
catch (OracleException ex)
{
Console.WriteLine(ex.Message);
return new DataTable();
}
}
}
public static bool AddIncident(Incident i, int missieUID)
{
using (OracleConnection o = new OracleConnection(connstring))
{
using (OracleCommand c = new OracleCommand())
{
try
{
c.Connection = o;
o.Open();
c.CommandText = "INSERT INTO TIncident (Beschrijving) VALUES ('" + i.Beschrijving + "')";
c.ExecuteNonQuery();
c.CommandText = "SELECT MAX(ID) FROM TIncident";
OracleDataReader dr = c.ExecuteReader();
if (dr.Read())
{
c.CommandText = "INSERT INTO TMissie_Incident (MissieID, IncidentID) VALUES ('" + missieUID + "', '" + GetInt(dr[0]) + "')";
c.ExecuteNonQuery();
}
return true;
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return false;
}
finally
{
o.Close();
}
}
}
}
//____________________________________________________________
//Lista "objeto" con Text
public static List<LocalidadDTO> ListarLocalidadText()
{
OracleCommand cmd = new OracleCommand();
List<LocalidadDTO> ListaLocalidades = new List<LocalidadDTO>();
cmd.Connection = new OracleConnection(ConfigurationManager.ConnectionStrings["CHAPA"].ConnectionString);
string strParcial = "Select * from localidad";
cmd.CommandType = CommandType.Text;
cmd.CommandText = strParcial;
cmd.Connection.Open();
OracleDataReader odr = cmd.ExecuteReader();
while (odr.Read())
{
LocalidadDTO objLoc = new LocalidadDTO();
objLoc.locId = Convert.ToInt32(odr[0]);
objLoc.locDescripcion = odr[1].ToString();
ListaLocalidades.Add(objLoc);
}
cmd.Connection.Close();
cmd.Dispose();
return ListaLocalidades;
}
public List<Employee> datos()
{
base.abrirConexion();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conexion;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "myProcedure";
cmd.Parameters.Add("co", OracleDbType.RefCursor).Direction = System.Data.ParameterDirection.Output;
OracleDataReader dr = cmd.ExecuteReader();
List<Employee> employees = new List<Employee>();
if (dr.HasRows)
{
while (dr.Read())
{
int id = dr.GetInt32(0);
string first_name = dr.GetString(1);
string last_name = dr.GetString(2);
employees.Add(new Employee(id, first_name, last_name));
Console.WriteLine(dr.GetString(1));
}
}
else
{
Console.WriteLine("No data found");
}
dr.Close();
cmd.Dispose();
base.cerrarConexion();
return employees;
}
public IBeheerContextEntity GetBusinessObject(string qry, BeheerContextEntity beheerObject)
{
using (var conn = new OracleConnection(_oradb))
{
conn.Open();
var cmd = new OracleCommand
{
Connection = conn,
CommandText = qry,
CommandType = CommandType.Text
};
cmd.Connection = conn;
var reader = cmd.ExecuteReader();
var smartReader = new SmartDataReader(reader);
BeheerContextEntity businessObject = null;
while (smartReader.Read())
{
businessObject = new BeheerContextEntity
{
Id = smartReader.GetInt32("id"),
DataKeyValue = smartReader.GetString(beheerObject.DataKeyName, ""),
Tablename = beheerObject.Tablename,
DataKeyName = beheerObject.DataKeyName
};
}
return businessObject;
}
}
//____________________________________________________________
//Lista "objeto" con StoredProcedure
public static List<LocalidadDTO> ListarLocalidadSP()
{
OracleCommand cmd = new OracleCommand();
List<LocalidadDTO> ListaLocalidades = new List<LocalidadDTO>();
cmd.Connection = new OracleConnection(ConfigurationManager.ConnectionStrings["CHAPA"].ConnectionString);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "LOCALIDAD_SELECT";
cmd.BindByName = true;
cmd.Parameters.Add("o_c_loc", OracleDbType.RefCursor, ParameterDirection.Output);
cmd.Connection.Open();
OracleDataReader odr = cmd.ExecuteReader();
while (odr.Read())
{
LocalidadDTO objLoc = new LocalidadDTO();
objLoc.locId = Convert.ToInt32(odr[0]);
objLoc.locDescripcion = odr[1].ToString();
ListaLocalidades.Add(objLoc);
}
cmd.Connection.Close();
cmd.Dispose();
return ListaLocalidades;
}
static void Main(string[] args)
{
try
{
string connstring =
"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora-002.cafe24.com)(PORT=1521))" +
"(CONNECT_DATA=(SID=orcl)));User Id=gds;Password=1234;";
using (OracleConnection conn = new OracleConnection(connstring))
{
conn.Open();
Console.WriteLine("접속 성공");
string sql = "select test from testdb";
using (OracleCommand comm = new OracleCommand(sql, conn))
{
using (OracleDataReader rdr = comm.ExecuteReader())
{
while (rdr.Read())
{
Console.WriteLine(rdr.GetString(0));
Console.ReadLine();
}
}
}
}
}
catch
{
Console.WriteLine("접속 실패");
}
}
public static ProductType FindById(int id)
{
string query = "SELECT * " +
"FROM TBL_PRODUCTTYPE " +
"WHERE ID=:id";
using (OracleConnection connection = CreateConnection())
using (OracleCommand command = new OracleCommand(query, connection))
{
command.BindByName = true;
command.Parameters.Add(new OracleParameter("id", id));
using (OracleDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
if (!reader.IsDBNull(0))
{
return GetProductTypeFromDataRecord(reader);
}
}
}
}
return null;
}
public List<Produto> Get()
{
using (OracleConnection conn = BaseDAO.GetConnection())
{
//Define o comando
using (OracleCommand cmd = new OracleCommand("SELECT ID_PRODUTO, ID_USUARIO, ID_ROTULO, DESCRICAO, DATA_INCLUSAO, SITUACAO, DATA_SITUACAO,"
+"FL_ROUPA, DEFAULT_IMAGEM FROM PRODUTO", conn))
{
List<Produto> lista = new List<Produto>();
cmd.CommandType = CommandType.Text;
//Executando o select
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Produto p = new Produto();
p.Id_Produto = Convert.ToInt32(reader["ID_PRODUTO"]);
p.Id_Usuario = Convert.ToInt32(reader["ID_USUARIO"]);
p.Id_Rotulo = Convert.ToInt32(reader["ID_ROTULO"]);
p.Descricao = Convert.ToString(reader["DESCRICAO"]);
p.Data_Inclusao = Convert.ToDateTime(reader["DATA_INCLUSAO"]);
p.Situacao = Convert.ToInt32(reader["SITUACAO"]);
p.Data_Situacao = Convert.ToDateTime(reader["DATA_SITUACAO"]);
p.Fl_Roupa = Convert.ToChar(reader["FL_ROUPA"]);
p.Imagem = Convert.ToString(reader["IMAGEM"]);
lista.Add(p);
}
return lista;
}
}
}
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>
/// Handles the Click event of the button2 control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
private void button2_Click(object sender, EventArgs e)
{
// Add a record.
try
{
// The fields must not be empty.
if (!string.IsNullOrEmpty(name) && !string.IsNullOrEmpty(mothername) && !string.IsNullOrEmpty(location) && !string.IsNullOrEmpty(birthdate.ToString()))
{
id = 0;
bool matchFound = false;
// Open a connection to the database.
OracleConnection oracleSqlConnection = new OracleConnection(url);
oracleSqlConnection.Open();
// Get the items from the People table.
OracleCommand oracleSqlCommand1 = new OracleCommand("SELECT * FROM root.People", oracleSqlConnection);
// Execute.
OracleDataReader oracleSqlDataReader1 = oracleSqlCommand1.ExecuteReader();
// Get the number of the existing items.
while (oracleSqlDataReader1.Read())
{
id++;
// Search for deleted matches.
if (!matchFound &&
oracleSqlDataReader1.GetString(1) == name &&
oracleSqlDataReader1.GetString(2) == mothername &&
oracleSqlDataReader1.GetString(3) == location &&
oracleSqlDataReader1.GetDateTime(4) == DateTime.Parse(birthdate) &&
oracleSqlDataReader1.GetInt32(5) == 1)
{
matchFound = true;
id = oracleSqlDataReader1.GetInt32(0);
break;
}
}
// Close the dataReader.
oracleSqlDataReader1.Close();
// Add a new item if no match exists or restore the deleted item.
if (!matchFound)
{
// Add new item to the People table.
OracleCommand oracleSqlCommand = new OracleCommand("INSERT INTO root.People (Id, Name, Mothername, Location, Birthdate, IsDeleted) VALUES (:param1, :param2, :param3, :param4, :param5, :param6)", oracleSqlConnection);
// Fill the values of the command.
oracleSqlCommand.Parameters.AddWithValue(":param1", id + 1);
oracleSqlCommand.Parameters.AddWithValue(":param2", name);
oracleSqlCommand.Parameters.AddWithValue(":param3", mothername);
oracleSqlCommand.Parameters.AddWithValue(":param4", location);
oracleSqlCommand.Parameters.AddWithValue(":param5", DateTime.Parse(birthdate));
oracleSqlCommand.Parameters.AddWithValue(":param6", 0);
// Execute.
oracleSqlCommand.ExecuteNonQuery();
}
else
{
// Edit the IsDeleted value to false.
OracleCommand oracleSqlCommand = new OracleCommand("UPDATE root.People SET IsDeleted = :param2 WHERE Id = :param1", oracleSqlConnection);
// Fill the values of the command.
oracleSqlCommand.Parameters.AddWithValue(":param1", id);
oracleSqlCommand.Parameters.AddWithValue(":param2", 0);
// Execute.
oracleSqlCommand.ExecuteNonQuery();
}
// Close the connection.
oracleSqlConnection.Close();
// Refresh.
GetData();
}
}
catch
{
}
}
public static void addDepartmentNumbers(List <img> icsList)
{
// hurr durr pointer
List <img> icsListBackup = new List <img>(icsList.ToArray());
int count = 0;
Dictionary <string, string> map = new Dictionary <string, string>();
// init database
con = new OracleConnection();
con.ConnectionString =
"Data Source=" +
"(DESCRIPTION = " +
"(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.4)(PORT = 9101))" +
"(CONNECT_DATA =" +
"(SID = KOHLDBPA1)" +
")" +
");Persist Security Info=True;User ID=pkmdes2;Password=pkd2iu4ro";
// build the query
string prodIds = "";
string collIds = "";
// chunk in 500s
while (icsList.Count > 0)
{
prodIds = "";
for (int i = 0; i <= 500; i++)
{
if (icsList.Count != 0)
{
img curr = icsList[0];
//foreach (img curr in icsList) {
// might as well count here
count++;
if (!curr.productId.Contains("c"))
{
prodIds += "'" + curr.productId + "'" + ", ";
}
else
{
collIds += "'" + curr.productId + "'" + ", ";
}
icsList.Remove(curr);
}
}
// fix leading comma
prodIds = prodIds.Substring(0, prodIds.Length - 2);
// Generate sql
string sql = "SELECT product_id, max(dept_no) from atgprdcata.kls_sku sku";
sql += " inner join atgprdcata.dcs_prd_chldsku child on child.sku_id = sku.sku_id";
sql += " where product_id in (" + prodIds + ") group by product_id";
// Run query
OracleCommand command = new OracleCommand(sql, con);
OracleDataReader reader;
try {
con.Open();
reader = command.ExecuteReader();
while (reader.Read())
{
try {
map.Add(reader.GetString(0), reader.GetString(1));
} catch (Exception e) {
// nothing to see here, duplicate map add
}
}
} catch (Exception ex) {
// Shits broke yo
Console.WriteLine("ERROR: " + ex.Message);
Console.ReadKey();
} finally {
con.Close();
}
}
// refer to backup... really should clean this up
icsList = icsListBackup;
// Now map them dept_id's
foreach (var s in map)
{
for (int i = 0; i < icsList.Count; i++)
{
if (s.Key == icsList[i].productId)
{
icsList[i].dept = s.Value;
}
}
}
// write csv
if (File.Exists(Environment.CurrentDirectory + @"\out\MissingImages.csv"))
{
File.Delete(Environment.CurrentDirectory + @"\out\MissingImages.csv");
}
File.Create(Environment.CurrentDirectory + @"\out\MissingImages.csv").Close();
using (StreamWriter sw = new StreamWriter(Environment.CurrentDirectory + @"\out\MissingImages.csv")) {
for (int i = 0; i < icsList.Count; i++)
{
sw.WriteLine(String.Format("{0},{1},{2},{3},{4},{5}", icsList[i].dept, ((img)icsList[i]).productId, ((img)icsList[i]).type, ((img)icsList[i]).color, ((img)icsList[i]).imageName, ((img)icsList[i]).nav_from));
}
}
// Output
logger.Info(String.Format("{0}/{1} images processed", count, icsList.Count));
}
// Ações do botão Entrar
private void btEntrar_Click(object sender, EventArgs e)
{
string SQL = "SELECT usu_login, usu_senha, usu_amb_identi FROM USUARIO WHERE USU_LOGIN=:nome AND USU_SENHA=MD5(:senha)";
OracleCommand comandoSQL = new OracleCommand(SQL);
comandoSQL.Connection = conexaoDB;
// Adiciona os parametros para select na base e validação de usuário.
comandoSQL.Parameters.AddWithValue(":nome", txtUsuario.Text);
comandoSQL.Parameters.AddWithValue(":senha", txtSenha.Text);
// Valida se os campos estão preenchidos e se correspondem com o select
if (txtUsuario.Text == "")
{
MessageBox.Show("Usuário não pode estar em branco.");
}
else if (txtSenha.Text == "")
{
MessageBox.Show("Senha não pode ser nula.");
}
else
{
try
{
OracleDataReader leitura = comandoSQL.ExecuteReader();
if (leitura.Read())
{
conectadoComo = txtUsuario.Text;
// Abre o console de liberação de solicitações
if (radioLiberacao.Checked)
{
conexaoDB.Close();
telaAcesso = "principal";
this.DialogResult = DialogResult.OK;
this.Close();
}
// Abre uma nova solicitação
else if (radioSolicitacao.Checked)
{
conexaoDB.Close();
telaAcesso = "solicitacao";
this.DialogResult = DialogResult.OK;
this.Close();
}
// Abre mudança de senha
else if (radioMudaSenha.Checked)
{
txtUsuario.Clear();
txtSenha.Clear();
frmMudaSenha ms = new frmMudaSenha();
//ms.Show();
ms.ShowDialog();
}
}
else
{
MessageBox.Show("Usuário e/ou senha não conferem. Tente novamente.");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
// Valida se o usuário pode liberar ou apenas solicitar
private void organizaUsuario()
{
string strUserSQL = "SELECT usu_per_identi FROM USUARIO WHERE usu_login = :usuLogin";
OracleCommand userSQL = new OracleCommand(strUserSQL, conexaoDB);
userSQL.Parameters.Add(":usuLogin", txtUsuario.Text);
OracleDataReader drUser = userSQL.ExecuteReader();
try
{
while (drUser.Read())
{
int permissao = int.Parse(drUser["usu_per_identi"].ToString());
// Usuário ROOT
if (permissao == 1)
{
radioLiberacao.Enabled = true;
radioSolicitacao.Enabled = true;
radioLiberacao.PerformClick();
}
// Usuário Admin
else if (permissao == 4)
{
radioLiberacao.Enabled = true;
radioSolicitacao.Enabled = true;
radioLiberacao.PerformClick();
}
// Usuário comum
else if (permissao == 2)
{
radioSolicitacao.Checked = true;
}
// Usuário Bloqueado
else if (permissao == 3)
{
var invalido = MessageBox.Show("Usuário bloqueado. Verique com seu lider ou gestor.", "Bloqueado", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
if (invalido == DialogResult.OK)
{
txtUsuario.Clear();
txtSenha.Clear();
txtUsuario.Focus();
}
else if (invalido == DialogResult.Cancel)
{
return;
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void Read(string sql, GetCpid getIpid, ReadCallback callback)
{
OracleCommand cmd = connection.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandTimeout = 0;
OracleDataReader reader = cmd.ExecuteReader();
string[] cpids = new string[reader.FieldCount - 1];
Int32[] ipids = new Int32[reader.FieldCount - 1];
decimal[] values = new decimal[reader.FieldCount - 1];
int[] ipididexs = new int[reader.FieldCount - 1];
int timeidx = -1;
int cpidcnt = 0;
for (int i = 0; i < reader.FieldCount; i++)
{
string columnName = reader.GetName(i);
if ("sjsj".Equals(columnName.ToLower()))
{
timeidx = i;
}
else
{
cpids[cpidcnt] = columnName;
ipididexs[cpidcnt++] = i;
}
}
if (timeidx == -1)
{
throw new Exception("sql '" + sql + "' timestamp or datetime(SJSJ) filed not found.");
}
ipids = getIpid(cpids, true);
while (reader.Read())
{
decimal tmp = 0;
for (int i = 0; i < ipids.Length; i++)
{
if (reader.IsDBNull(ipididexs[i]))
{
values[i] = 0;
}
else
{
values[i] = reader.GetDecimal(ipididexs[i]);
tmp = values[i];
}
}
DateTime dtm = DateTime.Now;
if ("sjsj".Equals(reader.GetName(timeidx).ToLower()))
{
dtm = reader.GetDateTime(timeidx);
}
else
{
throw new Exception("sql '" + sql + "' timestamp or datetime(SJSJ) filed not found.");
}
callback(dtm, ipids, values);
}
reader.Close();
}
public static ExcelWorksheet generateReport(ExcelWorksheet ws, string img, string rName, string ID, string cardScheme, string reportType, string DOMINT, string reportClass, string sett, int startRow)
{
ExcelRange cols = ws.Cells["A:XFD"];
cols.Style.Fill.PatternType = ExcelFillStyle.Solid;
cols.Style.Fill.BackgroundColor.SetColor(Color.White);;
System.Drawing.Image myImage = System.Drawing.Image.FromFile(img);
var pic = ws.Drawings.AddPicture("LOGO", myImage);
// Row, RowoffsetPixel, Column, ColumnOffSetPixel
pic.SetPosition(0, 0, 0, 0);
//Insert a row at the top. Note that the formula-addresses are shifted down
//ws.InsertRow(startRow-1, 1);
if (cardScheme == null)
{
cardScheme = String.Empty;
}
//Write the headers and style them
ws.Cells["A2"].Value = "Unified Payment Services Ltd";
ws.Cells["A2"].Style.Font.Size = 18;
ws.Cells["A2"].Style.Font.Bold = true;
ws.Cells["A2:M2"].Merge = true;
ws.Cells["A2:M2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
String CARDSCHEMDESC = String.Empty;
if (cardScheme.ToUpper() == "PAYA")
{
CARDSCHEMDESC = "PAY ATTITUDE";
}
else if (cardScheme.ToUpper() == "MAST")
{
CARDSCHEMDESC = "MASTER CARD";
}
else if (cardScheme.ToUpper() == "VISA")
{
CARDSCHEMDESC = "VISA CARD";
}
else if (cardScheme.ToUpper() == "CUPI")
{
CARDSCHEMDESC = "CHINA UNION PAY";
}
else
{
CARDSCHEMDESC = cardScheme.ToUpper();
}
ws.Cells["A4"].Value = "PTSP REPORT FOR " + CARDSCHEMDESC;
ws.Cells["A4"].Style.Font.Size = 14;
ws.Cells["A4"].Style.Font.Bold = true;
ws.Cells["A4:M4"].Merge = true;
ws.Cells["A4:M4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
ws.Cells["A5"].Value = "SETTLEMENT DATE: " + sett;
ws.Cells["A6"].Value = rName;
ws.Cells["A6"].Style.Font.Size = 14;
ws.Cells["A6"].Style.Font.Bold = true;
ws.Cells["A6:H6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
ws.View.FreezePanes(8, 1);
int row = startRow;
try
{
OracleConnection Standby_connection = new OracleConnection(oradb);
string qry = string.Empty;
if (DOMINT == "DOM")
{
qry = "RPT_SETTLEMETDETAIL";
}
else
{
qry = "RPT_SETTLEMETDETAIL_INT";
}
OracleCommand cmd = new OracleCommand();
cmd.Connection = Standby_connection;
// var dr = default(OracleDataReader);
if (Standby_connection == null)
{
Standby_connection = new OracleConnection(oradb);
}
if (Standby_connection.State != ConnectionState.Open)
{
Standby_connection.Open();
}
cmd.Connection = Standby_connection;
cmd.CommandText = qry;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter(":P_searchID", OracleDbType.Varchar2, ParameterDirection.Input)).Value = ID;
cmd.Parameters.Add(new OracleParameter(":P_CardScheme", OracleDbType.Varchar2, ParameterDirection.Input)).Value = cardScheme;
cmd.Parameters.Add(new OracleParameter(":P_reporttype", OracleDbType.Varchar2, ParameterDirection.Input)).Value = reportType;
cmd.Parameters.Add(new OracleParameter(":P_reportClass", OracleDbType.Varchar2, ParameterDirection.Input)).Value = reportClass;
cmd.Parameters.Add(new OracleParameter(":P_SETT", OracleDbType.Varchar2, ParameterDirection.Input)).Value = sett;
cmd.Parameters.Add(new OracleParameter(":CURSOR_ ", OracleDbType.RefCursor, ParameterDirection.Output));
using (var dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
int columnID = 1;
var fieldcount = dr.FieldCount;
DataTable dtSchema = dr.GetSchemaTable();
foreach (DataRow drow in dtSchema.Rows)
{
string columnName = Convert.ToString(drow["ColumnName"]);
ws.SetValue(startRow - 1, columnID, columnName);
columnID += 1;
}
while (dr.Read())
{
for (int i = 0; i < fieldcount; i++)
{
ws.SetValue(row, i + 1, dr[i]);
}
row++;
}
}
cmd.Dispose();
}
}
catch (Exception ex)
{
//Console.WriteLine(ex.Message );
}
ws.Cells[2, 3, row + 1, 3].Style.Numberformat.Format = "dd-mm-yyyy hh:mm:ss AM/PM";
ws.Cells[2, 5, row + 1, 6].Style.Numberformat.Format = "dd-mm-yyyy";
ws.Cells[2, 8, row + 1, 8].Style.Numberformat.Format = "dd-mm-yyyy";
ws.Cells[2, 44, row + 1, 54].Style.Numberformat.Format = "#,##0.00";
ws.Cells[2, 57, row + 1, 58].Style.Numberformat.Format = "#,##0.00";
ws.Cells[2, 60, row + 1, 77].Style.Numberformat.Format = "#,##0.00";
ws.Cells[row + 1, 1].Value = "TOTAL";
ws.Cells[row + 1, 11, row + 1, 11].Formula = string.Format("=SUM(K{0}:K{1})", startRow, row);
ws.Cells[row + 1, 11, row + 1, 11].Style.Numberformat.Format = "#,##0.00";
ws.Cells[row + 1, 12, row + 1, 12].Formula = string.Format("=SUM(L{0}:L{1})", startRow, row);
ws.Cells[row + 1, 12, row + 1, 12].Style.Numberformat.Format = "#,##0.00";
ws.Cells[row + 1, 13, row + 1, 13].Formula = string.Format("=SUM(M{0}:M{1})", startRow, row);
ws.Cells[row + 1, 13, row + 1, 13].Style.Numberformat.Format = "#,##0.00";
ws.Cells[row + 1, 14, row + 1, 14].Formula = string.Format("=SUM(N{0}:N{1})", startRow, row);
ws.Cells[row + 1, 14, row + 1, 14].Style.Numberformat.Format = "#,##0.00";
ws.Cells[row + 1, 15, row + 1, 15].Formula = string.Format("=SUM(O{0}:O{1})", startRow, row);
ws.Cells[row + 1, 15, row + 1, 15].Style.Numberformat.Format = "#,##0.00";
ws.Cells[startRow, 1, row, 80].AutoFitColumns(20);
ws.Select("C8");
return(ws);
}
public static void Main(string[] args)
{
string tainted_2 = null;
string tainted_3 = null;
Process process = new Process();
process.StartInfo.FileName = "/bin/bash";
process.StartInfo.Arguments = "-c 'cat /tmp/tainted.txt'";
process.StartInfo.UseShellExecute = false;
process.StartInfo.RedirectStandardOutput = true;
process.Start();
using (StreamReader reader = process.StandardOutput) {
tainted_2 = reader.ReadToEnd();
process.WaitForExit();
process.Close();
}
tainted_3 = tainted_2;
if ((4 + 2 <= 42))
{
{}
}
else if (!(4 + 2 <= 42))
{
{}
}
else
{
string pattern = @"/^[0-9]*$/";
Regex r = new Regex(pattern);
Match m = r.Match(tainted_2);
if (!m.Success)
{
tainted_3 = "";
}
else
{
tainted_3 = tainted_2;
}
}
//flaw
string query = "SELECT * FROM '" + tainted_3 + "'";
string connectionString = "Data Source=localhost;User ID=oracle_user;Password=oracle_password";
OracleConnection dbConnection = null;
try{
dbConnection = new OracleConnection(connectionString);
dbConnection.Open();
OracleCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = query;
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.ToString());
}
dbConnection.Close();
}catch (Exception e) {
Console.WriteLine(e.ToString());
}
}
public Pessoa CarregaEmpresa()
{
using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["OracleConnection"].ConnectionString))
{
string query = @"SELECT
PKNI002_IDEMPRESA,
ATSF002_NOMEFANTASIA,
ATSF002_RAZAOSOCIAL,
ATSF002_EMAIL,
ATNI002_CNPJ,
FKNI002_IDPESSOA,
TB2.ATSF008_LOGRADOURO,
TB2.ATNI008_NUMERO ENDERECO_NUMERO,
TB2.ATSF008_BAIRRO,
TB2.ATSF008_CEP,
TB3.ATNI009_DDD,
TB3.ATSF009_NUMERO TELEFONE,
TB3.FKNI009_IDTIPOTELEFONE,
TB4.PKNI012_IDMUNICIPIO,
TB4.ATSF012_NOME CIDADE,
TB5.PKNI013_IDESTADO,
TB5.ATSF013_SIGLA,
TB5.ATSF013_NOME ESTADO
FROM
ALC002T_EMPRESA TB1
INNER JOIN ALC008T_ENDERECO TB2
ON TB1.FKNI002_IDPESSOA = TB2.FKNI008_IDPESSOA
INNER JOIN ALC009T_TELEFONE TB3
ON TB1.FKNI002_IDPESSOA = TB3.FKNI009_IDPESSOA
INNER JOIN ALC012T_MUNICIPIO TB4
ON TB2.FKNI008_IDMUNICIPIO = TB4.PKNI012_IDMUNICIPIO
INNER JOIN ALC013T_ESTADO TB5
ON TB2.FKNI008_IDESTADO = TB5.PKNI013_IDESTADO
INNER JOIN ALC014T_PESSOA TB6
ON TB1.FKNI002_IDPESSOA = TB6.PKNI014_IDPESSOA
WHERE PKNI002_IDEMPRESA = 1 ";
conn.Open();
using (OracleCommand cmd = conn.CreateCommand())
{
cmd.CommandText = query;
using (OracleDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
Pessoa dbPessoa = new Pessoa();
dbPessoa.IdPessoa = Convert.ToInt32(reader["FKNI002_IDPESSOA"]);
dbPessoa.NomeFantasia = reader["ATSF002_NOMEFANTASIA"].ToString();
dbPessoa.RazaoSocial = reader["ATSF002_RAZAOSOCIAL"].ToString();
dbPessoa.Email = reader["ATSF002_EMAIL"].ToString();
dbPessoa.CNPJ = Convert.ToInt64(reader["ATNI002_CNPJ"]);
dbPessoa.endereco = new Endereco();
dbPessoa.endereco.Logradouro = reader["ATSF008_LOGRADOURO"].ToString();
dbPessoa.endereco.Numero = reader["ENDERECO_NUMERO"].ToString();
dbPessoa.endereco.Bairro = reader["ATSF008_BAIRRO"].ToString();
dbPessoa.endereco.CEP = reader["ATSF008_CEP"].ToString();
dbPessoa.endereco.IdMunicipio = Convert.ToInt32(reader["PKNI012_IDMUNICIPIO"]);
dbPessoa.endereco.IdEstado = Convert.ToInt32(reader["PKNI013_IDESTADO"]);
dbPessoa.endereco.NomeMunicipio = reader["CIDADE"].ToString();
dbPessoa.endereco.NomeEstado = reader["PKNI013_IDESTADO"].ToString();
dbPessoa.endereco.Sigla = reader["ATSF013_SIGLA"].ToString();
dbPessoa.telefone = new Telefone();
dbPessoa.telefone.DDD = Convert.ToInt32(reader["ATNI009_DDD"]);
dbPessoa.telefone.Numero = reader["TELEFONE"].ToString();
return(dbPessoa);
}
return(null);
}
}
}
}
public static Task <OracleDataReader> ExecuteReaderAsynchronous(this OracleCommand command, CommandBehavior behavior, CancellationToken cancellationToken)
{
return(App.ExecuteAsynchronous(command.CancelIgnoreFailure, () => command.ExecuteReader(behavior), cancellationToken));
}
private void searchTool_Click(object sender, EventArgs e)
{
if (searchType.selectedValue.ToString() == "Director")
{
try
{
con = new OracleConnection(ordb);
con.Open();
panel1.Controls.Clear();
con = new OracleConnection(ordb);
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = " select movie.movie_name,movie.date_of_puplish , moviecategory.categoryname,movie.avg_rate,director.dir_name from movie inner join director on movie.director_id = director.dir_id inner join moviecategory ON moviecategory.categoryid = movie.category_id where director.dir_name= :name";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("name", searchbox.Text.ToString());
con.Open();
OracleDataReader reader = cmd.ExecuteReader();
for (int i = 0; reader.Read(); i++)
{
string name = reader[0].ToString();
string year = reader[1].ToString();
string category = reader[2].ToString();
string rate = reader[3].ToString();
string director = reader[4].ToString();
rate = get_avg_Rate(name);
if (rate == "null")
{
rate = "0";
}
update_rate(name, rate);
search s = new search(name, category, director, rate, year);
s.Visible = true;
s.Location = new Point(70, 490 * i + 5);
panel1.Controls.Add(s);
s.BringToFront();
}
reader.Close();
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else if (searchType.selectedValue.ToString() == "Actor")
{
try
{
panel1.Controls.Clear();
string cmdStr = "select movie.movie_name,movie.date_of_puplish , moviecategory.categoryname,movie.avg_rate,director.dir_name from movie INNER JOIN movie_actor on movie_actor.movie_name=movie.movie_name INNER JOIN actor on movie_actor.actor_id=actor.actor_id inner join director on movie.director_id = director.dir_id inner join moviecategory ON moviecategory.categoryid = movie.category_id where Actor.Actor_name=:actor_name";
adapter = new OracleDataAdapter(cmdStr, ordb);
adapter.SelectCommand.Parameters.Add("actor_name", searchbox.Text.ToString());
DataSet ds = new DataSet();
adapter.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string name = ds.Tables[0].Rows[i]["MOVIE_NAME"].ToString();
string year = ds.Tables[0].Rows[i]["DATE_OF_PUPLISH"].ToString();
string category = ds.Tables[0].Rows[i]["categoryname"].ToString();
string rate = ds.Tables[0].Rows[i]["AVG_RATE"].ToString();
string director = ds.Tables[0].Rows[i]["dir_name"].ToString();
if (rate == "")
{
rate = "0";
}
search s = new search(name, category, director, rate, year);
s.Visible = true;
s.Location = new Point(70, 490 * i + 5);
panel1.Controls.Add(s);
s.BringToFront();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else if (searchType.selectedValue.ToString() == "Title")
{
try
{
OracleConnection conn;
string ordb = "data source = orcl; user id =scott; password=tiger;";
con = new OracleConnection(ordb);
con.Open();
panel1.Controls.Clear();
con = new OracleConnection(ordb);
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = " select movie.movie_name,movie.date_of_puplish , moviecategory.categoryname,movie.avg_rate,director.dir_name from movie inner join director on movie.director_id = director.dir_id inner join moviecategory ON moviecategory.categoryid = movie.category_id where movie.movie_name= :name";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("name", searchbox.Text.ToString());
con.Open();
OracleDataReader reader = cmd.ExecuteReader();
for (int i = 0; reader.Read(); i++)
{
string name = reader[0].ToString();
string year = reader[1].ToString();
string category = reader[2].ToString();
string rate = reader[3].ToString();
string director = reader[4].ToString();
rate = get_avg_Rate(name);
if (rate == "null")
{
rate = "0";
}
update_rate(name, rate);
search s = new search(name, category, director, rate, year);
s.Visible = true;
s.Location = new Point(70, 490 * i + 5);
panel1.Controls.Add(s);
s.BringToFront();
}
reader.Close();
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else if (searchType.selectedValue.ToString() == "Category")
{
try
{
con = new OracleConnection(ordb);
con.Open();
panel1.Controls.Clear();
con = new OracleConnection(ordb);
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "SEARCH_CATEGORY";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("cname", searchbox.Text.ToString());
cmd.Parameters.Add("c", OracleDbType.RefCursor, ParameterDirection.Output);
con.Open();
OracleDataReader reader = cmd.ExecuteReader();
for (int i = 0; reader.Read(); i++)
{
string name = reader[0].ToString();
string year = reader[1].ToString();
string category = reader[2].ToString();
string rate = reader[3].ToString();
string director = reader[4].ToString();
rate = get_avg_Rate(name);
if (rate == "null")
{
rate = "0";
}
update_rate(name, rate);
search s = new search(name, category, director, rate, year);
s.Visible = true;
s.Location = new Point(70, 490 * i + 5);
panel1.Controls.Add(s);
s.BringToFront();
}
reader.Close();
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void searchType_onItemSelected(object sender, EventArgs e)
{
//rate above
if (searchType.selectedValue.ToString() == "Rate above")
{
con = new OracleConnection(ordb);
con.Open();
panel1.Controls.Clear();
con = new OracleConnection(ordb);
con.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT * FROM MOVIE ORDER BY AVG_RATE DESC";
cmd.CommandType = CommandType.Text;
OracleDataReader reader = cmd.ExecuteReader();
for (int i = 0; reader.Read(); i++)
{
string name = reader[0].ToString();
string year = reader[1].ToString();
string rate = reader[5].ToString();
rate = get_avg_Rate(name);
if (rate == "null")
{
rate = "0";
}
update_rate(name, rate);
RATE s = new RATE(name, rate, year);
s.Visible = true;
s.Location = new Point(70, 490 * i + 5);
panel1.Controls.Add(s);
s.BringToFront();
}
reader.Close();
con.Close();
}
else if (searchType.selectedValue.ToString() == "Rate below")
{
con = new OracleConnection(ordb);
con.Open();
panel1.Controls.Clear();
con = new OracleConnection(ordb);
con.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT * FROM MOVIE ORDER BY AVG_RATE";
cmd.CommandType = CommandType.Text;
OracleDataReader reader = cmd.ExecuteReader();
for (int i = 0; reader.Read(); i++)
{
string name = reader[0].ToString();
string year = reader[1].ToString();
string rate = reader[5].ToString();
rate = get_avg_Rate(name);
if (rate == "null")
{
rate = "0";
}
update_rate(name, rate);
RATE s = new RATE(name, rate, year);
s.Visible = true;
s.Location = new Point(70, 490 * i + 5);
panel1.Controls.Add(s);
s.BringToFront();
}
reader.Close();
con.Close();
}
}
public static void Main(string[] args)
{
string tainted_2 = null;
string tainted_3 = null;
tainted_2 = args[1];
tainted_3 = tainted_2;
if ((4 + 2 >= 42))
{
StringBuilder escape = new StringBuilder();
for (int i = 0; i < tainted_2.Length; ++i)
{
char current = tainted_2[i];
switch (current)
{
case '\\':
escape.Append(@"\5c");
break;
case '*':
escape.Append(@"\2a");
break;
case '(':
escape.Append(@"\28");
break;
case ')':
escape.Append(@"\29");
break;
case '\u0000':
escape.Append(@"\00");
break;
case '/':
escape.Append(@"\2f");
break;
default:
escape.Append(current);
break;
}
}
tainted_3 = escape.ToString();
}
else if (!(4 + 2 >= 42))
{
{}
}
else
{
{}
}
//flaw
string query = "SELECT * FROM Articles WHERE id=" + tainted_3;
string connectionString = "Data Source=localhost;User ID=oracle_user;Password=oracle_password";
OracleConnection dbConnection = null;
try{
dbConnection = new OracleConnection(connectionString);
dbConnection.Open();
OracleCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = query;
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.ToString());
}
dbConnection.Close();
}catch (Exception e) {
Console.WriteLine(e.ToString());
}
}
private void CreateImage(string function)
{
string rtnValue = "";
try
{
string _dbuser = function.Substring(0, function.IndexOf(".", 0));
string conString = ConfigurationManager.AppSettings["strConn"];
conString += "User Id=" + _dbuser + ";Password="******"2";
OracleConnection con = new OracleConnection(conString);
con.Open();
string queryString = "select " + function + " xxx from dual";
OracleCommand command = new OracleCommand(queryString, con);
OracleDataReader reader;
reader = command.ExecuteReader();
while (reader.Read())
{
rtnValue = Convert.ToString(reader["xxx"]);
}
reader.Close();
con.Close();
}
catch (Exception ex)
{
ESysLib.WriteLogError(ex.Message + "\n" + ex.StackTrace);
return;
}
if (rtnValue == "")
{
return;
}
Bitmap objBitmap;
Graphics objGraphics = null;
int ActualRows = 0;
int ActualCols = 0;
int ActualWidth = 0;
int ActualHeight = 0;
int ExtraWidth, ExtraHeight;
Point p;
Response.ContentType = "image/gif";
PDF417Net MyPDF417 = new PDF417Net();
MyPDF417.BackColor = Color.FromName("White");
MyPDF417.BarColor = Color.FromName("Black");
MyPDF417.Data = rtnValue; // "12321984912\n3ry8sdjhfv\nsdj sdjsd \nds v sdhsdjvsjd\njdsjsdahjsd sdav csaj sjd jhv\nahsdv chjw";//Request.QueryString["Data"];
//MyPDF417.ErrorCorrectionLevel = 2; //0->8
MyPDF417.Mode = 0; //0:Binary, 1:Text, 2:Auto
//MyPDF417.NarrowBarWidth = 0.07;//(float)System.Convert.ToDouble(Request.QueryString["NarrowBarWidth"]);
MyPDF417.Orientation = 0; //0: 0 degree, 1: 90 degree, 2: 180 degree, 3: 270 degree
MyPDF417.Columns = 4; //1->30
MyPDF417.Rows = 3; //1->90
MyPDF417.TruncateSymbol = false; //(Request.QueryString["TruncateSymbol"] == "ON");
MyPDF417.HandleTilde = false; // (Request.QueryString["HandleTilde"] == "ON");
//MyPDF417.Y2XRatio = 3.0;//(float)System.Convert.ToDouble(Request.QueryString["Y2XRatio"]);
//// Get actual rows and columns of PDF417 barcode
MyPDF417.GetActualRC(ref ActualRows, ref ActualCols);
//// Get actual barcode width and height
MyPDF417.GetActualSize(ref ActualWidth, ref ActualHeight);
ExtraWidth = 30; //System.Convert.ToInt16(Request.QueryString["ExtraWidth"]);
ExtraHeight = 40; //System.Convert.ToInt16(Request.QueryString["ExtraHeight"]);
// Image size = barcode size + extra space
//if (MyPDF417.Orientation == 0 || MyPDF417.Orientation == 2)
//{
// MyPDF417.SetSize(ActualWidth + ExtraWidth, ActualHeight + ExtraHeight);
// objBitmap = new Bitmap(ActualWidth + ExtraWidth, ActualHeight + ExtraHeight);
//}
//else
//{
// MyPDF417.SetSize(ActualHeight + ExtraWidth, ActualWidth + ExtraHeight);
// objBitmap = new Bitmap(ActualHeight + ExtraWidth, ActualWidth + ExtraHeight);
//}
MyPDF417.SetSize(ActualWidth + ExtraWidth, ActualHeight + ExtraHeight);
objBitmap = new Bitmap(ActualWidth + ExtraWidth, ActualHeight + ExtraHeight);
objGraphics = Graphics.FromImage(objBitmap);
p = new Point(0, 0);
MyPDF417.Render(objGraphics, p);
objBitmap.Save(Response.OutputStream, System.Drawing.Imaging.ImageFormat.Gif);
objGraphics.Flush();
}
private void AUDOgretmen(String sql_stmt, int state)
{
String msg = "";
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = sql_stmt;
cmd.CommandType = CommandType.Text;
switch (state)
{
case 0:
string oradb = "DATA SOURCE=localhost:1521/orcl;USER ID=harun;PASSWORD=harun";
OracleConnection conn = new OracleConnection(oradb); // C#
conn.Close();
conn.Open();
string sql = "SELECT MAX(CONTACT_ID) as max from CONTACT ";
OracleCommand cmdMAX = new OracleCommand(sql, conn);
int BuyukId = 0;
OracleDataReader readercmdMAX = cmdMAX.ExecuteReader();
while (readercmdMAX.Read())
{
BuyukId = Int32.Parse(readercmdMAX["max"].ToString());
}
msg = "Kayıt işlemi başarılı bir şekilde gerçekleşti.";
//cmd.Parameters.Add("TEACHER_ID", OracleDbType.Int32, 6).Value = Int32.Parse(txtOgretmenId.Text);
cmd.Parameters.Add("NAME", OracleDbType.Varchar2, 35).Value = txtOgretmenAd.Text;
cmd.Parameters.Add("SURNAME", OracleDbType.Varchar2, 35).Value = txtOgretmenSoyad.Text;
cmd.Parameters.Add("AGE", OracleDbType.Varchar2, 20).Value = txtOgretmenCinsiyet.Text;
cmd.Parameters.Add("CONTACT_ID", OracleDbType.Int32).Value = BuyukId;
//cmd.Parameters.Add("CLASSID", OracleDbType.Int32).Value = cmbOgrenciSinif.SelectedIndex;
break;
case 1:
msg = "Güncelleme işlemi başarılı bir şekilde gerçekleşti.";
cmd.Parameters.Add("NAME", OracleDbType.Varchar2, 35).Value = txtOgretmenAd.Text;
cmd.Parameters.Add("SURNAME", OracleDbType.Varchar2, 35).Value = txtOgretmenSoyad.Text;
cmd.Parameters.Add("AGE", OracleDbType.Varchar2, 20).Value = txtOgretmenCinsiyet.Text;
cmd.Parameters.Add("CONTACT_ID", OracleDbType.Int32).Value = txtOgretmenContactId.Text;
cmd.Parameters.Add("TEACHER_ID", OracleDbType.Int32, 6).Value = txtOgretmenId.Text;
break;
case 2:
msg = "Silme işlemi başarılı bir şekilde gerçekleşti.";
cmd.Parameters.Add("TEACHER_ID", OracleDbType.Int32, 6).Value = Int32.Parse(txtOgretmenId.Text);
break;
}
try
{
int n = cmd.ExecuteNonQuery();
if (n > 0)
{
MessageBox.Show(msg);
this.TeacherDataGrid();
}
}
catch (Exception expe) { }
}
/// <summary>
/// To Get Route Coordinates
/// </summary>
/// <param name="RouteId"></param>
/// <returns></returns>
public RouteCoordinatesList GetRouteCoordinates(string RouteId)
{
OracleConnection cnn = new OracleConnection(AppConfiguration.ConnectionString);
OracleCommand cmd;
string proc = "USP_TRN_GETCOORDINATES";
cmd = new OracleCommand(proc, cnn);
cmd.CommandType = CommandType.StoredProcedure;
if (RouteId.ToString() == "")
{
cmd.Parameters.Add("@Route_IdIN", DBNull.Value);
}
else
{
cmd.Parameters.Add("@Route_IdIN", RouteId.ToString());
}
cmd.Parameters.Add("Sp_recordset", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
cmd.Connection.Open();
OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
RouteCoordinatesBO objRouteCoordinates = null;
RouteCoordinatesList RouteCoordinates = new RouteCoordinatesList();
while (dr.Read())
{
objRouteCoordinates = new RouteCoordinatesBO();
if (!dr.IsDBNull(dr.GetOrdinal("ROUTE_COORDINATEID")))
{
objRouteCoordinates.Route_CoordinateID = Convert.ToInt32(dr.GetValue(dr.GetOrdinal("ROUTE_COORDINATEID")));
}
if (!dr.IsDBNull(dr.GetOrdinal("ROUTEID")))
{
objRouteCoordinates.Route_ID = Convert.ToInt32(dr.GetValue(dr.GetOrdinal("ROUTEID")));
}
if (!dr.IsDBNull(dr.GetOrdinal("ROUTENAME")))
{
objRouteCoordinates.Routename = dr.GetString(dr.GetOrdinal("ROUTENAME"));
}
if (!dr.IsDBNull(dr.GetOrdinal("X_LONGITUDE")))
{
objRouteCoordinates.X_axis = dr.GetString(dr.GetOrdinal("X_LONGITUDE"));
}
if (!dr.IsDBNull(dr.GetOrdinal("Y_LATITUDE")))
{
objRouteCoordinates.Y_axis = dr.GetString(dr.GetOrdinal("Y_LATITUDE"));
}
if (!dr.IsDBNull(dr.GetOrdinal("Z_HEIGHT")))
{
objRouteCoordinates.Z_axis = dr.GetString(dr.GetOrdinal("Z_HEIGHT"));
}
if (!dr.IsDBNull(dr.GetOrdinal("latitude")))
{
objRouteCoordinates.Latitude = dr.GetString(dr.GetOrdinal("latitude"));
}
if (!dr.IsDBNull(dr.GetOrdinal("LONGITUDE")))
{
objRouteCoordinates.Longitude = dr.GetString(dr.GetOrdinal("LONGITUDE"));
}
RouteCoordinates.Add(objRouteCoordinates);
}
dr.Close();
return(RouteCoordinates);
}
private void Perfil_Load(object sender, EventArgs e)
{
try
{
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
OracleCommand oda = new OracleCommand("SELECT p.nome, p.sexo, p.cpf, p.datanascimento, p.numtelefone, p.email, e.cep, e.bairro, e.rua, e.uf FROM pessoa p JOIN endereco e ON e.cep = p.cep WHERE p.matricula =" + matricula_aluno, conn);
OracleDataReader reader = oda.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
nome = reader.GetString(0);
txb_nome.Text = nome;
string sexo = reader.GetString(1);
if (sexo == "M")
{
ckb_masculino.Checked = true;
}
else
{
ckb_feminino.Checked = true;
}
cpf = reader.GetDecimal(2);
txb_cpf.Text = Convert.ToString(cpf);
DateTime nascimento = reader.GetDateTime(3);
dataNascimento.Value = nascimento;
telefone = reader.GetDecimal(4);
txb_telefone.Text = Convert.ToString(telefone);
email = reader.GetString(5);
txb_email.Text = email;
cep = reader.GetInt32(6);
txb_cep.Text = Convert.ToString(cep);
bairro = reader.GetString(7);
txb_bairro.Text = bairro;
rua = reader.GetString(8);
txb_rua.Text = rua;
uf = reader.GetString(9);
txb_estado.Text = uf;
}
conn.Close();
}
catch (Exception ex) // detecta todos os erros
{
MessageBox.Show(ex.Message.ToString());
}
try
{
OracleConnection conn2 = new OracleConnection(oradb);
conn2.Open();
OracleCommand oda2 = new OracleCommand("SELECT valor, statusmensalidade, vencimento, mesreferencia FROM mensalidade WHERE matricula_aluno=" + matricula_aluno, conn2);
OracleDataReader reader2 = oda2.ExecuteReader();
if (reader2.HasRows)
{
reader2.Read();
string valor = reader2.GetString(0);
txb_valorMensalidade.Text = valor;
string status = reader2.GetString(1);
txb_statusMensalidade.Text = status;
string vencimento = reader2.GetString(2);
DateTime venc = Convert.ToDateTime(vencimento);
date_VencimentoMesalidade.Value = new DateTime(venc.Year, venc.Month, venc.Day);
string mesrefencia = reader2.GetString(3);
txb_mesReferencia.Text = mesrefencia;
}
conn2.Close();
}
catch (Exception ex) // detecta todos os erros
{
MessageBox.Show(ex.Message.ToString());
}
}
private void btnCreateSheets_Click(object sender, EventArgs e)
{
if (cboOwner.SelectedIndex == -1)
{
MessageBox.Show("Please, select a owner for continue.");
return;
}
string domainPathSource = "C:\\ExcelSheets\\TEMP\\" + cboOwner.SelectedValue.ToString();
string strSQL = "";
int count = 0;
strSQL = TablesForOwnerQuery(cboOwner.SelectedValue.ToString());
Oracle.DataAccess.Client.OracleConnection conn = DBConnectionDTOP();
conn.Open();
try
{
OracleCommand cmd = new OracleCommand(strSQL, conn);
OracleDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
int totalLines = dt.Rows.Count;
dt.Dispose();
progressBar2.Value = 0; // Esse é o valor da progress bar ela vai de 0 a Maximum (padrão 100)
progressBar2.Maximum = totalLines;
dr.Close();
dr = cmd.ExecuteReader();
while (dr.Read())
{
string domainPathTarget = "C:\\ExcelSheets\\TEMP\\" + cboOwner.SelectedValue.ToString() + "\\" + dr[2].ToString();
if (!System.IO.Directory.Exists(domainPathTarget))
{
System.IO.Directory.CreateDirectory(domainPathTarget);
}
if (System.IO.File.Exists(domainPathSource + "\\" + dr[1].ToString() + ".xls"))
{
System.IO.File.Move(domainPathSource + "\\" + dr[1].ToString() + ".xls", domainPathTarget + "\\" + dr[1].ToString() + ".xls");
}
else
{
label2.Text = "Exportando tabela: " + cboOwner.SelectedValue.ToString() + "." + dr[1].ToString() + " para planilha: " + domainPathTarget + "\\" + dr[1].ToString() + ".xls";
label2.Visible = true;
strSQL = MakeDomainsQuery(cboOwner.SelectedValue.ToString(), dr[1].ToString());
SQLToCSV(cboOwner.SelectedValue.ToString(), strSQL, dr[1].ToString(), domainPathTarget);
}
count++;
progressBar2.Value = count;
progressBar2.Visible = true;
label3.Text = "Exportada: " + count.ToString() + " de " + totalLines + " tabelas do Owner " + cboOwner.SelectedValue.ToString();
label3.Visible = true;
}
label3.Text = "Exportação realizada com sucesso para : " + count.ToString() + " tabelas do Owner " + cboOwner.SelectedValue.ToString();
}
finally { conn.Close(); conn.Dispose(); }
}
public static bool CalculateOperationsDuration()
{
bool bReturn = true;
OracleDataReader dr = null;
OracleCommand selOpsCmd = null;
int iRealTime = -1;
int iQuantity = -1;
int iOpeBaseId = -1;
try
{
if (OpenDBs())
{
selOpsCmd = new OracleCommand();
selOpsCmd.Connection = OPSBDCon;
selOpsCmd.CommandText = "select ope_id, ope_vehicleid, ope_dart_id, " +
"ope_grp_id, ope_uni_id, ope_dope_id, " +
"to_char(ope_inidate,'HH24MISSDDMMYY') ope_inidate , " +
"to_char(ope_enddate,'HH24MISSDDMMYY') ope_enddate, " +
"to_char(ope_movdate,'HH24MISSDDMMYY') movdate " +
"from operations " +
"where ope_dope_id in (1,2,3) " +
// "and (ope_realduration is null or ope_id=ope_base_ope_id) "+
"order by ope_movdate asc";
dr = selOpsCmd.ExecuteReader();
while (dr.Read())
{
try
{
_operationId = dr.GetInt32(dr.GetOrdinal("OPE_ID"));
_vehicleId = dr.GetString(dr.GetOrdinal("OPE_VEHICLEID"));
_articleDefId = dr.GetInt32(dr.GetOrdinal("OPE_DART_ID"));
_groupId = dr.GetInt32(dr.GetOrdinal("OPE_GRP_ID"));
_unitId = dr.GetInt32(dr.GetOrdinal("OPE_UNI_ID"));
_dopeid = dr.GetInt32(dr.GetOrdinal("OPE_DOPE_ID"));
_dateMov = OPS.Comm.Dtx.StringToDtx(dr.GetString(dr.GetOrdinal("MOVDATE")));
_dateIni = OPS.Comm.Dtx.StringToDtx(dr.GetString(dr.GetOrdinal("OPE_INIDATE")));
_dateEnd = OPS.Comm.Dtx.StringToDtx(dr.GetString(dr.GetOrdinal("OPE_ENDDATE")));
switch (_dopeid)
{
case 1:
UpdateBaseOpeId(_operationId);
Logger_AddLogMessage(string.Format("Estacionamiento {0} con ope_base={1}", _operationId, _operationId), LoggerSeverities.Info);
break;
case 2:
iOpeBaseId = GetOpeBaseIdAmp();
if (iOpeBaseId < 0)
{
iOpeBaseId = _operationId;
}
UpdateBaseOpeId(iOpeBaseId);
Logger_AddLogMessage(string.Format("Prolongacion {0} con ope_base={1}", _operationId, iOpeBaseId), LoggerSeverities.Info);
break;
case 3:
GetM2CompData(ref iRealTime, ref iQuantity);
iOpeBaseId = GetOpeBaseIdDev();
if (iOpeBaseId < 0)
{
iOpeBaseId = _operationId;
}
UpdateRealTime(_operationId, iRealTime, iQuantity, iOpeBaseId);
UpdateBaseOpeIdDev(iOpeBaseId);
Logger_AddLogMessage(string.Format("Devolucion {0} con tiempo real={1} minutos, cantidad={2} y ope_base={3}", _operationId, iRealTime, iQuantity, iOpeBaseId), LoggerSeverities.Info);
/*UpdateBaseOpeId(iOpeBaseId);
* UpdateBaseOpeIdDev(iOpeBaseId);
* Logger_AddLogMessage(string.Format("Devolucion{0} con ope_base={1}",_operationId,iOpeBaseId), LoggerSeverities.Info);*/
break;
default:
break;
}
}
catch (Exception e)
{
Logger_AddLogException(e);
}
}
}
else
{
bReturn = false;
}
}
catch (Exception e)
{
Logger_AddLogException(e);
bReturn = false;
}
finally
{
if (dr != null)
{
dr.Close();
dr = null;
}
if (selOpsCmd != null)
{
selOpsCmd.Dispose();
selOpsCmd = null;
}
CloseDBs();
}
return(bReturn);
}
/// <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);
}
public IEnumerable <object> GetTable(String owner, String entityName, String condition)
{
List <object> results = new List <object>();
String whereClause = " WHERE ";
if (String.IsNullOrEmpty(condition))
{
whereClause = "";
}
else
{
whereClause += condition;
}
Assembly datacontractAssembly = this.GetType().Assembly;
Type entityType = datacontractAssembly.GetType("OracleDbCrawler.ObjectEntity." + entityName);
if (entityType == null)
{
foreach (Type t in datacontractAssembly.GetTypes())
{
//Logging.WriteLog(t.Name);
}
}
object[] tableAttributes = entityType.GetCustomAttributes(typeof(TableNameAttribute), false);
String tableName = "";
if (tableAttributes == null || tableAttributes.Count() != 1)
{
return(null);
}
tableName = (tableAttributes.First() as TableNameAttribute).TableName;
OracleConnection conn = Conn;
OracleCommand cmd = new OracleCommand(String.Format("SELECT * FROM {0}.{1}{2}", owner, tableName, whereClause), conn);
cmd.InitialLONGFetchSize = 1;
try
{
conn.Open();
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var item = Activator.CreateInstance(entityType);
for (int colIndex = 0; colIndex < reader.FieldCount; colIndex++)
{
string columnname = reader.GetName(colIndex);
PropertyInfo pi = entityType.GetProperties().ToList().Find(p =>
{
ColumnAttribute[] columns = p.GetCustomAttributes(typeof(ColumnAttribute), false) as ColumnAttribute[];
if (columns == null || columns.Count() == 0)
{
return(false);
}
if (columns[0].ColumnName == columnname)
{
return(true);
}
return(false);
});
if (!reader.IsDBNull(colIndex))
{
switch (reader.GetDataTypeName(colIndex))
{
case "Decimal": pi.SetValue(item, reader.GetDecimal(colIndex), null); break;
case "Date": pi.SetValue(item, reader.GetDateTime(colIndex), null); break;
case "Varchar2": pi.SetValue(item, reader.GetString(colIndex), null); break;
case "Raw":
byte[] data = new byte[16];
reader.GetBytes(colIndex, 0, data, 0, 16);
pi.SetValue(item, data, null); break;
case "NVarchar2": pi.SetValue(item, reader.GetString(colIndex), null); break;
case "Clob": pi.SetValue(item, reader.GetOracleClob(colIndex).Value, null); break;
case "Char": pi.SetValue(item, reader.GetString(colIndex), null); break;
case "Int16": pi.SetValue(item, reader.GetDecimal(colIndex), null); break;
case "Int64": pi.SetValue(item, reader.GetInt64(colIndex), null); break;
default: break;
}
}
}
results.Add(item);
}
}
catch (Exception ex)
{
// ex.Log();
}
finally
{
conn.Close();
}
return(results);
}
public List <RequiredDocuments> GetAllRequiredDocumentsByTravelId(string travelRequestId, int badgeNumber)
{
List <RequiredDocuments> result = new List <RequiredDocuments>();
try
{
using (dbConn = ConnectionFactory.GetOpenDefaultConnection())
{
for (int i = 1; i < 6; i++)
{
result.Add(new RequiredDocuments()
{
DocumentNumber = i,
TravelRequestId = travelRequestId,
FileName = string.Empty,
DocumentName = string.Empty,
Visible = (i == 1 || i == 2) ? true : false
});
}
string query = string.Format("Select CASHADVANCE from TRAVELREQUEST_ESTIMATEDEXPENSE where TRAVELREQUESTID = {0} AND CASHADVANCE > 0 ", travelRequestId);
OracleCommand command = new OracleCommand(query, (OracleConnection)dbConn);
command.CommandText = query;
DbDataReader dataReader = command.ExecuteReader();
if (dataReader != null)
{
while (dataReader.Read())
{
result.FirstOrDefault(p => p.DocumentNumber == 3).Visible = true;
}
}
dataReader.Close();
command.Dispose();
string query2 = string.Format("Select TOTALESTIMATEDREGISTRATION from TRAVELREQUEST_ESTIMATEDEXPENSE where TRAVELREQUESTID = {0} AND TOTALESTIMATEDREGISTRATION > 0 ", travelRequestId);
OracleCommand command2 = new OracleCommand(query2, (OracleConnection)dbConn);
command2.CommandText = query2;
DbDataReader dataReader2 = command2.ExecuteReader();
if (dataReader2 != null)
{
while (dataReader2.Read())
{
result.FirstOrDefault(p => p.DocumentNumber == 4).Visible = true;
}
}
dataReader2.Close();
command2.Dispose();
string query1 = string.Format("Select TRAVELREQUESTID, FILENAME, REQUIREDORDER from Travel_Uploads where TRAVELREQUESTID = {0} and Requiredorder is not null ", travelRequestId);
OracleCommand command1 = new OracleCommand(query1, (OracleConnection)dbConn);
command1.CommandText = query1;
DbDataReader dataReader1 = command1.ExecuteReader();
if (dataReader1 != null)
{
while (dataReader1.Read())
{
result.FirstOrDefault(p1 => p1.DocumentNumber == Convert.ToInt32(dataReader1["REQUIREDORDER"])).FileName = dataReader1["FILENAME"].ToString();
}
}
dataReader1.Close();
command1.Dispose();
dbConn.Close();
dbConn.Dispose();
return(result);
}
}
catch (Exception ex)
{
LogMessage.Log("DocumentRepository : GetAllDocumentsByTravelId - returns required documents" + ex.Message);
throw new Exception("Couldn't retrieve all the documents by travel id");
}
}
private void BindList()
{
string connectionString =
ConfigurationManager.ConnectionStrings["ConnectionString2"].ConnectionString;
OracleConnection conn = new OracleConnection();
conn.ConnectionString = connectionString;
OracleCommand comm = conn.CreateCommand();
OracleDataReader reader;
DataTable recipeDetailTable = new DataTable();
DataTable categoryTable = new DataTable();
DataTable ingredientsTable = new DataTable();
DataTable listTable = new DataTable();
try
{
// load recipe data
comm.CommandText = "select u.name as Submity_by, c.type as category, r.recipeid, r.categoryid, r.recipename,r.userID, r.description, r.servingnum, r.cookingminutes from recipes r right outer join users u on r.userid = u.userid join categories c on c.categoryid = r.categoryid join recipesLinkIngredients on r.recipeid = recipesLinkIngredients.recipeid right outer join ingredients i on recipesLinkIngredients.ingredientID = i.ingredientID where r.recipeid=" + Request.QueryString["key"];
comm.CommandType = CommandType.Text;
comm.Connection.Open();
reader = comm.ExecuteReader();
recipeDetailTable.Load(reader);
// bind recipe detail view
DetailsViewDetail.DataSource = recipeDetailTable;
DetailsViewDetail.DataBind();
// load selected category data
comm.Parameters.Clear();
comm.CommandText = "select r.categoryid, c.type from recipes r left join categories c on c.categoryid = r.categoryid right outer join recipesLinkIngredients on r.recipeid = recipesLinkIngredients.recipeid right outer join ingredients i on recipesLinkIngredients.ingredientID = i.ingredientID where r.recipeid=" + Request.QueryString["key"];
reader = comm.ExecuteReader();
categoryTable.Load(reader);
// load category list data
comm.Parameters.Clear();
comm.CommandText = "select categoryid, type from categories";
reader = comm.ExecuteReader();
listTable.Load(reader);
// bind category label (normal mode) and category droplist (edit mode)
Label category = (Label)DetailsViewDetail.FindControl("catgoryLabel");
if (category != null)
{
category.Text = categoryTable.Rows[0]["type"].ToString();
}
DropDownList categoryDropList = (DropDownList)DetailsViewDetail.FindControl("categoryList");
if (categoryDropList != null)
{
categoryDropList.DataSource = listTable;
categoryDropList.DataTextField = "Type";
categoryDropList.DataValueField = "categoryid";
categoryDropList.DataBind();
ListItem selectedListItem = categoryDropList.Items.FindByValue(categoryTable.Rows[0]["categoryid"].ToString());
if (selectedListItem != null)
{
selectedListItem.Selected = true;
}
}
// load ingredients data
comm.Parameters.Clear();
comm.CommandText = "select ingredients.ingredientid,ingredients.name,ingredients.quantity,ingredients.unitofmeasure from ingredients join recipesLinkIngredients on ingredients.INGREDIENTID = recipesLinkIngredients.INGREDIENTID where recipesLinkIngredients .recipeid = " + Request.QueryString["key"];
reader = comm.ExecuteReader();
ingredientsTable.Load(reader);
// bind ingredientView
ingredientView.DataSource = ingredientsTable;
ingredientView.DataBind();
}
catch (SqlException ex)
{
exception.Text = ex.Message;
}
catch (Exception ex)
{
exception.Text = ex.Message;
}
finally
{
comm.Connection.Close();
}
}
/// <summary>
/// Retorna uma lista de itens de notas de saída
/// </summary>
/// <param name="filial">Código da filial</param>
/// <param name="numeroNota">Número da Nota</param>
/// <param name="serieNota">Série da Nota</param>
/// <returns>listaItens</returns>
public List <E140IPVModel> PesquisarItensNotasFiscaisSaida(int filial, long numeroNota, string serieNota)
{
try
{
string sql = "Select A.SeqIpv as Sequencia, " +
" A.TnsPro as Transacao, " +
" A.CodPro || ' ' || A.CODDER as CodigoProduto, " +
" B.DesPro || ' ' || E.DESDER as DescricaoProduto, " +
" A.QtdFat as QtdeFaturada, " +
" 0 as QtdeDevolucao, " +
" TO_CHAR(A.PreUni, '999999990D99999') as PrecoUnitario, " +
" 'Selecione...' As MotivoDevolucao, " +
" 'Selecione...' As OrigemOcorrencia, " +
" TO_CHAR(A.PerDsc, '999999990D99') as PercDescUnit, " +
" TO_CHAR(A.PerIpi, '999999990D99') as PercIpi, " +
" TO_CHAR(A.VlrIpi, '999999990D99') as ValorIpi, " +
" TO_CHAR(A.VlrLiq, '999999990D99') as ValorLiquido, " +
" TO_CHAR(A.vlrics, '999999990D99') as ValorSt, " +
" TO_CHAR(A.VLRDZF + A.VLRPIT + A.VLRCRT, '999999990D99') as DescontoSuframa, " +
" B.CodFam as CodigoFamilia, " +
" A.VLRFRE AS ValorFrete, " +
" COALESCE((SELECT SUM(QTDDEV) " +
" FROM NWMS_PRODUCAO.N0203IPV SUBIPV " +
" INNER JOIN NWMS_PRODUCAO.N0203REG SUBREG " +
" ON SUBIPV.NUMREG = SUBREG.NUMREG " +
" WHERE SUBIPV.NUMNFV = " + numeroNota + " AND SUBREG.SITREG NOT IN (5, 7)" +
" AND SUBIPV.CODPRO = A.CodPro " +
" AND SUBIPV.CODDER = A.CodDer),0) AS QTDDEVSALDO " +
" From SAPIENS.E140IPV A, SAPIENS.E075PRO B, SAPIENS.E075DER E " +
" Where A.CodEmp = B.CodEmp " +
" And A.CodPro = B.CodPro " +
" AND A.CodEmp = E.CodEmp " +
" And A.CodPro = E.CodPro " +
" AND A.CODDER = E.CODDER " +
" And A.CodEmp = 1 " +
" And A.CodFil = " + filial +
" And A.CodSnf = " + "'" + serieNota + "'" +
" And A.NumNfv = " + numeroNota;
OracleConnection conn = new OracleConnection(OracleStringConnection);
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.CommandType = CommandType.Text;
conn.Open();
OracleDataReader dr = cmd.ExecuteReader();
List <E140IPVModel> listaItens = new List <E140IPVModel>();
E140IPVModel itemNota = new E140IPVModel();
while (dr.Read())
{
itemNota = new E140IPVModel();
itemNota.Sequencia = dr.GetInt32(0);
itemNota.Transacao = dr.GetString(1);
itemNota.CodigoProduto = dr.GetString(2);
itemNota.DescricaoProduto = dr.GetString(3);
itemNota.QtdeFaturada = dr.GetInt32(4);
itemNota.QtdeDevolucao = dr.GetInt32(5);
itemNota.PrecoUnitario = dr.GetString(6);
itemNota.MotivoDevolucao = dr.GetString(7);
itemNota.OrigemOcorrencia = dr.GetString(8);
itemNota.PercDescUnit = dr.GetString(9);
itemNota.PercIpi = dr.GetString(10);
itemNota.ValorIpi = dr.GetString(11);
itemNota.ValorLiquido = dr.GetString(12);
itemNota.ValorSt = dr.GetString(13);
itemNota.DescontoSuframa = dr.GetString(14);
itemNota.CodigoFamilia = dr.GetString(15);
itemNota.ValorFrete = dr.GetDecimal(16);
itemNota.SaldoDevolucao = Convert.ToInt32(dr["QTDDEVSALDO"]);
string codigoDep = string.Empty;
string DescricaoDep = string.Empty;
PesquisarCentroCustoItemNoRateio(1, filial, numeroNota, serieNota, itemNota.Sequencia, out codigoDep, out DescricaoDep);
if (!string.IsNullOrEmpty(codigoDep))
{
itemNota.Departamento = codigoDep;
itemNota.DescricaoDepartamento = DescricaoDep;
}
else
{
PesquisarCentroCustoItemNoProduto(1, itemNota.CodigoProduto, out codigoDep, out DescricaoDep);
if (!string.IsNullOrEmpty(codigoDep))
{
itemNota.Departamento = codigoDep;
itemNota.DescricaoDepartamento = DescricaoDep;
}
else
{
PesquisarCentroCustoItemNaFamilia(1, itemNota.CodigoFamilia, out codigoDep, out DescricaoDep);
itemNota.Departamento = codigoDep;
itemNota.DescricaoDepartamento = DescricaoDep;
}
}
listaItens.Add(itemNota);
}
dr.Close();
conn.Close();
return(listaItens);
}
catch (Exception ex)
{
throw ex;
}
}
private void AUDVeli(String sql_stmt, int state)
{
String msg = "";
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = sql_stmt;
cmd.CommandType = CommandType.Text;
switch (state)
{
case 0:
string oradb = "DATA SOURCE=localhost:1521/orcl;USER ID=harun;PASSWORD=harun";
OracleConnection conn = new OracleConnection(oradb); // C#
conn.Close();
conn.Open();
string sql = "SELECT MAX(CONTACT_ID) as max from CONTACT ";
OracleCommand cmdMAX = new OracleCommand(sql, conn);
int BuyukId = 0;
OracleDataReader readercmdMAX = cmdMAX.ExecuteReader();
while (readercmdMAX.Read())
{
BuyukId = Int32.Parse(readercmdMAX["max"].ToString());
}
msg = "Ekleme Başarılı";
//cmd.Parameters.Add("PARENT_ID", OracleDbType.Int32, 38).Value = Int32.Parse(txtVeliId.Text);
cmd.Parameters.Add("NAME", OracleDbType.Varchar2, 20).Value = txtVeliAd.Text;
cmd.Parameters.Add("SURNAME", OracleDbType.Varchar2, 20).Value = txtVeliSoyad.Text;
cmd.Parameters.Add("CONTACT_ID", OracleDbType.Int32, 38).Value = BuyukId;
//cmd.Parameters.Add("AGE", OracleDbType.Varchar2, 20).Value = txtOgrenciYas.Text;
//cmd.Parameters.Add("CLASS_ID", OracleDbType.Int32).Value = Convert.ToInt32(txtOgrenciSinifId.Text);
//cmd.Parameters.Add("PARENT_ID", OracleDbType.Int32).Value = Convert.ToInt32(txtOgrenciVeliId.Text);
//cmd.Parameters.Add("CLASSID", OracleDbType.Int32).Value = cmbOgrenciSinif.SelectedIndex;
break;
case 1:
msg = "Güncelleme Başarılı";
cmd.Parameters.Add("NAME", OracleDbType.Varchar2, 20).Value = txtVeliAd.Text;
cmd.Parameters.Add("SURNAME", OracleDbType.Varchar2, 20).Value = txtVeliSoyad.Text;
//cmd.Parameters.Add("CONTACT_ID", OracleDbType.Int32, 38).Value = Convert.ToInt32(txtVeliContactId.Text);
//cmd.Parameters.Add("AGE", OracleDbType.Varchar2, 20).Value = txtOgrenciYas.Text;
//cmd.Parameters.Add("CLASS_ID", OracleDbType.Int32).Value = Convert.ToInt32(txtOgrenciSinifId.Text);
//cmd.Parameters.Add("PARENT_ID", OracleDbType.Int32).Value = Convert.ToInt32(txtOgrenciVeliId.Text);
cmd.Parameters.Add("PARENT_ID", OracleDbType.Int32, 38).Value = Int32.Parse(txtVeliId.Text);
break;
case 2:
msg = "Row Deleted Successfully!";
cmd.Parameters.Add("PARENT_ID", OracleDbType.Int32, 38).Value = Int32.Parse(txtVeliId.Text);
break;
}
try
{
int n = cmd.ExecuteNonQuery();
if (n > 0)
{
MessageBox.Show(msg);
this.VeliDataGrid();
}
}
catch (Exception expe) { }
}
public static void Main(string[] args)
{
string tainted_2 = null;
string tainted_3 = null;
Process process = new Process();
process.StartInfo.FileName = "/bin/bash";
process.StartInfo.Arguments = "-c 'cat /tmp/tainted.txt'";
process.StartInfo.UseShellExecute = false;
process.StartInfo.RedirectStandardOutput = true;
process.Start();
using (StreamReader reader = process.StandardOutput) {
tainted_2 = reader.ReadToEnd();
process.WaitForExit();
process.Close();
}
tainted_3 = tainted_2;
if ((Math.Sqrt(42) >= 42))
{
{}
}
else if (!(Math.Sqrt(42) >= 42))
{
StringBuilder text = new StringBuilder(tainted_2);
text.Replace("&", "&");
text.Replace("'", "'");
text.Replace(@"""", """);
text.Replace("<", "<");
text.Replace(">", ">");
tainted_3 = text.ToString();
}
else
{
{}
}
//flaw
string query = "SELECT * FROM Articles WHERE id=" + tainted_3;
string connectionString = "Data Source=localhost;User ID=oracle_user;Password=oracle_password";
OracleConnection dbConnection = null;
try{
dbConnection = new OracleConnection(connectionString);
dbConnection.Open();
OracleCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = query;
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.ToString());
}
dbConnection.Close();
}catch (Exception e) {
Console.WriteLine(e.ToString());
}
}
private void AUDIletisimOgretmen(String sql_stmt, int state)
{
String msg = "";
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = sql_stmt;
cmd.CommandType = CommandType.Text;
string oradb = "DATA SOURCE=localhost:1521/orcl;USER ID=harun;PASSWORD=harun";
OracleConnection conn = new OracleConnection(oradb); // C#
conn.Open();
string sql_iletisim = "SELECT CONTACT_ID from CONTACT WHERE TEL_NUMBER='" + txtVeliTel.Text.ToString() + "'";
OracleCommand cmd_iletisim = new OracleCommand(sql_iletisim, conn);
int iletisim_id = 0;
OracleDataReader readercmd_iletisim = cmd_iletisim.ExecuteReader();
while (readercmd_iletisim.Read())
{
iletisim_id = Int32.Parse(readercmd_iletisim["CONTACT_ID"].ToString());
}
switch (state)
{
case 0:
msg = "Ekleme Başarılı";
//d.Parameters.Add("CONTACT_ID", OracleDbType.Int32, 38).Value = Convert.ToInt32(txtIletisimId.Text);
cmd.Parameters.Add("STREET", OracleDbType.Varchar2, 20).Value = txtOgretmenCadde.Text;
cmd.Parameters.Add("VILLAGE", OracleDbType.Varchar2, 20).Value = txtOgretmenMahalle.Text;
cmd.Parameters.Add("CITY", OracleDbType.Varchar2, 20).Value = txtOgretmenSehir.Text;
cmd.Parameters.Add("TEL_NUMBER", OracleDbType.Varchar2, 20).Value = txtOgretmenTel.Text;
//cmd.Parameters.Add("CLASSID", OracleDbType.Int32).Value = cmbOgrenciSinif.SelectedIndex;
break;
case 1:
msg = "Güncelleme Başarılı";
cmd.Parameters.Add("STREET", OracleDbType.Varchar2, 20).Value = txtOgretmenCadde.Text;
cmd.Parameters.Add("VILLAGE", OracleDbType.Varchar2, 20).Value = txtOgretmenMahalle.Text;
cmd.Parameters.Add("CITY", OracleDbType.Varchar2, 20).Value = txtOgretmenSehir.Text;
cmd.Parameters.Add("TEL_NUMBER", OracleDbType.Varchar2, 20).Value = txtOgretmenTel.Text;
cmd.Parameters.Add("CONTACT_ID", OracleDbType.Int32, 38).Value = txtOgretmenContactId.Text;
break;
case 2:
msg = "Row Deleted Successfully!";
//cmd.Parameters.Add("CONTACT_ID", OracleDbType.Int32, 38).Value = Int32.Parse(txtIletisimId.Text);
break;
}
try
{
int n = cmd.ExecuteNonQuery();
if (n > 0)
{
MessageBox.Show(msg);
this.VeliDataGrid();
}
}
catch (Exception expe) { }
}
public void getData(DateTime rq1, DateTime rq2)
{
OracleConnection cn = new OracleConnection();
cn.ConnectionString = Properties.Settings.Default.ltznConnectionString;
cn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = cn;
cmd.Parameters.Add(":RQ1", OracleType.DateTime).Value = rq1;
cmd.Parameters.Add(":RQ2", OracleType.DateTime).Value = rq2;
cmd.CommandText = "select gaolu,GETBANCI1(ZDSJ),SUM(FELIANG),sum(wdsj) from ddluci where GETRQ1(zdsj)>=:RQ1 and GETRQ1(zdsj)<=:RQ2 and zdsj is not null group by gaolu,GETBANCI1(ZDSJ)";
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (!dr.IsDBNull(0))
{
int gaolu = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
string banbie = dr.IsDBNull(1) ? "" : dr.GetString(1);
if (gaolu > 0 && gaolu < 7)
{
switch (banbie)
{
case "甲班":
chanliang[gaolu - 1, 0] = dr.IsDBNull(2) ? 0 : dr.GetDouble(2);
wandian[gaolu - 1, 0] = dr.IsDBNull(3) ? 0 : dr.GetDouble(3);
break;
case "乙班":
chanliang[gaolu - 1, 1] = dr.IsDBNull(2) ? 0 : dr.GetDouble(2);
wandian[gaolu - 1, 1] = dr.IsDBNull(3) ? 0 : dr.GetDouble(3);
break;
case "丙班":
chanliang[gaolu - 1, 2] = dr.IsDBNull(2) ? 0 : dr.GetDouble(2);
wandian[gaolu - 1, 2] = dr.IsDBNull(3) ? 0 : dr.GetDouble(3);
break;
case "丁班":
chanliang[gaolu - 1, 3] = dr.IsDBNull(2) ? 0 : dr.GetDouble(2);
wandian[gaolu - 1, 3] = dr.IsDBNull(3) ? 0 : dr.GetDouble(3);
break;
}
}
}
}
dr.Close();
cmd.CommandText = "select gaolu,GETBANCI2(ZDSJ),round(avg(fesi),4) from ddluci where GETRQ2(zdsj)>=:RQ1 and GETRQ2(zdsj)<=:RQ2 and zdsj is not null group by gaolu,GETBANCI2(ZDSJ)";
dr = cmd.ExecuteReader();
while (dr.Read())
{
if (!dr.IsDBNull(0))
{
int gaolu = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
string banbie = dr.IsDBNull(1) ? "" : dr.GetString(1);
if (gaolu > 0 && gaolu < 7)
{
switch (banbie)
{
case "甲班":
si[gaolu - 1, 0] = dr.IsDBNull(2) ? 0 : dr.GetDouble(2);
break;
case "乙班":
si[gaolu - 1, 1] = dr.IsDBNull(2) ? 0 : dr.GetDouble(2);
break;
case "丙班":
si[gaolu - 1, 2] = dr.IsDBNull(2) ? 0 : dr.GetDouble(2);
break;
case "丁班":
si[gaolu - 1, 3] = dr.IsDBNull(2) ? 0 : dr.GetDouble(2);
break;
}
}
}
}
dr.Close();
cmd.CommandText = "select gaolu,GETBANCI3(sj,hour),round(sum(pml),2),round(avg(ldwd),2),round(avg(rfwd),2) from rbcaozuo where GETRQ3(sj,hour)>=:RQ1 and GETRQ3(sj,hour)<=:RQ2 and ldwd>0 and rfwd>0 group by gaolu,GETBANCI3(sj,hour)";
dr = cmd.ExecuteReader();
while (dr.Read())
{
if (!dr.IsDBNull(0))
{
int gaolu = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
string banbie = dr.IsDBNull(1) ? "" : dr.GetString(1);
if (gaolu > 0 && gaolu < 7)
{
switch (banbie)
{
case "甲班":
mei[gaolu - 1, 0] = dr.IsDBNull(2) ? 0 : dr.GetDouble(2) * 1000;
if (chanliang[gaolu - 1, 0] > 0)
{
meibi[gaolu - 1, 0] = mei[gaolu - 1, 0] / chanliang[gaolu - 1, 0];
}
else
{
meibi[gaolu - 1, 0] = 0;
}
dingwen[gaolu - 1, 0] = dr.IsDBNull(3) ? 0 : dr.GetDouble(3);
fengwen[gaolu - 1, 0] = dr.IsDBNull(4) ? 0 : dr.GetDouble(4);
break;
case "乙班":
mei[gaolu - 1, 1] = dr.IsDBNull(2) ? 0 : dr.GetDouble(2) * 1000;
if (chanliang[gaolu - 1, 1] > 0)
{
meibi[gaolu - 1, 1] = mei[gaolu - 1, 1] / chanliang[gaolu - 1, 1];
}
else
{
meibi[gaolu - 1, 1] = 0;
}
dingwen[gaolu - 1, 1] = dr.IsDBNull(3) ? 0 : dr.GetDouble(3);
fengwen[gaolu - 1, 1] = dr.IsDBNull(4) ? 0 : dr.GetDouble(4);
break;
case "丙班":
mei[gaolu - 1, 2] = dr.IsDBNull(2) ? 0 : dr.GetDouble(2) * 1000;
if (chanliang[gaolu - 1, 2] > 0)
{
meibi[gaolu - 1, 2] = mei[gaolu - 1, 2] / chanliang[gaolu - 1, 2];
}
else
{
meibi[gaolu - 1, 2] = 0;
}
dingwen[gaolu - 1, 2] = dr.IsDBNull(3) ? 0 : dr.GetDouble(3);
fengwen[gaolu - 1, 2] = dr.IsDBNull(4) ? 0 : dr.GetDouble(4);
break;
case "丁班":
mei[gaolu - 1, 3] = dr.IsDBNull(2) ? 0 : dr.GetDouble(2) * 1000;
if (chanliang[gaolu - 1, 3] > 0)
{
meibi[gaolu - 1, 3] = mei[gaolu - 1, 3] / chanliang[gaolu - 1, 3];
}
else
{
meibi[gaolu - 1, 3] = 0;
}
dingwen[gaolu - 1, 3] = dr.IsDBNull(3) ? 0 : dr.GetDouble(3);
fengwen[gaolu - 1, 3] = dr.IsDBNull(4) ? 0 : dr.GetDouble(4);
break;
}
}
}
}
dr.Close();
cmd.CommandText = "select gaolu,GETBANCI4(T),sum(jt),sum(jd) from lt_liao where GETRQ4(T)>=:RQ1 and GETRQ4(T)<=:RQ2 group by gaolu,GETBANCI4(T)";
dr = cmd.ExecuteReader();
while (dr.Read())
{
if (!dr.IsDBNull(0))
{
int gaolu = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
string banbie = dr.IsDBNull(1) ? "" : dr.GetString(1);
if (gaolu > 0 && gaolu < 7)
{
switch (banbie)
{
case "甲班":
jt[gaolu - 1, 0] = dr.IsDBNull(2) ? 0 : dr.GetDouble(2);
jd[gaolu - 1, 0] = dr.IsDBNull(3) ? 0 : dr.GetDouble(3);
if (chanliang[gaolu - 1, 0] > 0)
{
jiaobi[gaolu - 1, 0] = (jt[gaolu - 1, 0] + jd[gaolu - 1, 0] * 0.9) / chanliang[gaolu - 1, 0];
zonghejiaobi[gaolu - 1, 0] = (jt[gaolu - 1, 0] + jd[gaolu - 1, 0] * 0.9 + mei[gaolu - 1, 0] * 0.8) / chanliang[gaolu - 1, 0];
}
else
{
jiaobi[gaolu - 1, 0] = 0;
zonghejiaobi[gaolu - 1, 0] = 0;
}
break;
case "乙班":
jt[gaolu - 1, 1] = dr.IsDBNull(2) ? 0 : dr.GetDouble(2);
jd[gaolu - 1, 1] = dr.IsDBNull(3) ? 0 : dr.GetDouble(3);
if (chanliang[gaolu - 1, 1] > 0)
{
jiaobi[gaolu - 1, 1] = (jt[gaolu - 1, 1] + jd[gaolu - 1, 1] * 0.9) / chanliang[gaolu - 1, 0];
zonghejiaobi[gaolu - 1, 1] = (jt[gaolu - 1, 1] + jd[gaolu - 1, 1] * 0.9 + mei[gaolu - 1, 1] * 0.8) / chanliang[gaolu - 1, 1];
}
else
{
jiaobi[gaolu - 1, 1] = 0;
zonghejiaobi[gaolu - 1, 1] = 0;
}
break;
case "丙班":
jt[gaolu - 1, 2] = dr.IsDBNull(2) ? 0 : dr.GetDouble(2);
jd[gaolu - 1, 2] = dr.IsDBNull(3) ? 0 : dr.GetDouble(3);
if (chanliang[gaolu - 1, 2] > 0)
{
jiaobi[gaolu - 1, 2] = (jt[gaolu - 1, 2] + jd[gaolu - 1, 2] * 0.9) / chanliang[gaolu - 1, 2];
zonghejiaobi[gaolu - 1, 2] = (jt[gaolu - 1, 2] + jd[gaolu - 1, 2] * 0.9 + mei[gaolu - 1, 2] * 0.8) / chanliang[gaolu - 1, 2];
}
else
{
jiaobi[gaolu - 1, 2] = 0;
zonghejiaobi[gaolu - 1, 2] = 0;
}
break;
case "丁班":
jt[gaolu - 1, 3] = dr.IsDBNull(2) ? 0 : dr.GetDouble(2);
jd[gaolu - 1, 3] = dr.IsDBNull(3) ? 0 : dr.GetDouble(3);
if (chanliang[gaolu - 1, 3] > 0)
{
jiaobi[gaolu - 1, 3] = (jt[gaolu - 1, 3] + jd[gaolu - 1, 3] * 0.9) / chanliang[gaolu - 1, 3];
zonghejiaobi[gaolu - 1, 3] = (jt[gaolu - 1, 3] + jd[gaolu - 1, 3] * 0.9 + mei[gaolu - 1, 3] * 0.8) / chanliang[gaolu - 1, 3];
}
else
{
jiaobi[gaolu - 1, 3] = 0;
zonghejiaobi[gaolu - 1, 3] = 0;
}
break;
}
}
}
}
dr.Close();
cn.Close();
}
public List <ORAChiamate> GetListaChiamate(string CodSede)
{
List <ORAChiamate> ListaChiamate = new List <ORAChiamate>();
DBContext context = new DBContext();
Connessione InfoCon = context.GetConnectionFromCodiceSede(CodSede);
OracleConnection conn = new OracleConnection(InfoCon.ConnectionString);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "select "+
" NVL(CHIAMATA, 0) as CHIAMATA," +
" NVL(DATA_CHIAMATA,'') as DATA_CHIAMATA,"+
" NVL(ORA_CHIAMATA,'') as ORA_CHIAMATA," +
" NVL(TURNO_CHIAMATA,'') as TURNO_CHIAMATA,"+
" NVL(COD_TIPOLOGIA, 0) as COD_TIPOLOGIA,"+
" NVL(DETTAGLIO_TIPOLOGIA,'') as DETTAGLIO_TIPOLOGIA,"+
" NVL(NOTE_INTERVENTO,'') as NOTE_INTERVENTO,"+
" NVL(LOC_INDIRIZZO,'') as LOC_INDIRIZZO,"+
" NVL(LOCALITA,'') as LOCALITA," +
" NVL(COD_STRADA,'') as COD_STRADA,"+
" NVL(NUM_CIVICO,'') as NUM_CIVICO,"+
" NVL(COD_COMUNE, 0) as COD_COMUNE,"+
" NVL(SIGLA_PROVINCIA,'') as SIGLA_PROVINCIA,"+
" NVL(RICHIEDENTE,'') as RICHIEDENTE,"+
" NVL(TELE_NUMERO,'') as TELE_NUMERO,"+
" NVL(COMANDO,'') as COMANDO,"+
" NVL(SCHEDA_ALTRO_COMANDO,'') as SCHEDA_ALTRO_COMANDO," +
" NVL(MATRICOLA_OPERATORE_CHIAMATA,'') as MATRICOLA_OPERATORE_CHIAMATA," +
" NVL(RICEVUTA_TRASMESSA,'') as RICEVUTA_TRASMESSA,"+
" NVL(COD_OBIETTIVO, 0) as COD_OBIETTIVO,"+
" NVL(COD_STRADA_INCROCIO,'') as COD_STRADA_INCROCIO,"+
" NVL(FLAG_CIV_KM,'') as FLAG_CIV_KM,"+
" NVL(COD_PRIORITA, 0) as COD_PRIORITA," +
" NVL(EDGID_STRADA, 0) as EDGID_STRADA," +
" NVL(CODICE_PI, 0) as CODICE_PI,"+
" NVL(X, 0) as X,"+
" NVL(Y, 0) as Y,"+
" NVL(ID_INCROCIO, 0) as ID_INCROCIO,"+
" NVL(ID_ZONA, 0) as ID_ZONA,"+
" NVL(DESC_LUOGO,'') as DESC_LUOGO,"+
" NVL(FLAG_R,'') as FLAG_R,"+
" NVL(ID_112, 0) as ID_112,"+
" NVL(COD_DIST_PREALL,0) as COD_DIST_PREALL,"+
" NVL(ZONA_EMERGENZA,'') as ZONA_EMERGENZA,"+
" NVL(PALAZZO,'') as PALAZZO,"+
" NVL(SCALA,'') as SCALA,"+
" NVL(PIANO,'') as PIANO,"+
" NVL(INTERNO,'') as INTERNO,"+
" NVL(CIVICO_VICINO , '') as CIVICO_VICINO "+
"from SALAOPER.CHIAMATE ";
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ORAChiamate ORACh = new ORAChiamate();
ORACh.CHIAMATA = Utility.Utility.GetDBField(dr, "CHIAMATA");
ORACh.DATA_CHIAMATA = Utility.Utility.GetDBField(dr, "DATA_CHIAMATA");
ORACh.ORA_CHIAMATA = Utility.Utility.GetDBField(dr, "ORA_CHIAMATA");
ORACh.TURNO_CHIAMATA = Utility.Utility.GetDBField(dr, "TURNO_CHIAMATA");
ORACh.COD_TIPOLOGIA = Utility.Utility.GetDBField(dr, "COD_TIPOLOGIA");
ORACh.DETTAGLIO_TIPOLOGIA = Utility.Utility.GetDBField(dr, "DETTAGLIO_TIPOLOGIA");
ORACh.NOTE_INTERVENTO = Utility.Utility.GetDBField(dr, "NOTE_INTERVENTO");
ORACh.LOC_INDIRIZZO = Utility.Utility.GetDBField(dr, "LOC_INDIRIZZO");
ORACh.LOCALITA = Utility.Utility.GetDBField(dr, "LOCALITA");
ORACh.COD_STRADA = Utility.Utility.GetDBField(dr, "COD_STRADA");
ORACh.NUM_CIVICO = Utility.Utility.GetDBField(dr, "NUM_CIVICO");
ORACh.COD_COMUNE = Utility.Utility.GetDBField(dr, "COD_COMUNE");
ORACh.SIGLA_PROVINCIA = Utility.Utility.GetDBField(dr, "SIGLA_PROVINCIA");
ORACh.RICHIEDENTE = Utility.Utility.GetDBField(dr, "RICHIEDENTE");
ORACh.TELE_NUMERO = Utility.Utility.GetDBField(dr, "TELE_NUMERO");
ORACh.COMANDO = Utility.Utility.GetDBField(dr, "COMANDO");
ORACh.SCHEDA_ALTRO_COMANDO = Utility.Utility.GetDBField(dr, "SCHEDA_ALTRO_COMANDO");
ORACh.MATRICOLA_OPERATORE_CHIAMATA = Utility.Utility.GetDBField(dr, "MATRICOLA_OPERATORE_CHIAMATA");
ORACh.RICEVUTA_TRASMESSA = Utility.Utility.GetDBField(dr, "RICEVUTA_TRASMESSA");
ORACh.COD_OBIETTIVO = Utility.Utility.GetDBField(dr, "COD_OBIETTIVO");
ORACh.COD_STRADA_INCROCIO = Utility.Utility.GetDBField(dr, "COD_STRADA_INCROCIO");
ORACh.FLAG_CIV_KM = Utility.Utility.GetDBField(dr, "FLAG_CIV_KM");
ORACh.COD_PRIORITA = Utility.Utility.GetDBField(dr, "COD_PRIORITA");
ORACh.EDGID_STRADA = Utility.Utility.GetDBField(dr, "EDGID_STRADA");
ORACh.CODICE_PI = Utility.Utility.GetDBField(dr, "CODICE_PI");
ORACh.X = Utility.Utility.GetDBField(dr, "X");
ORACh.Y = Utility.Utility.GetDBField(dr, "Y");
ORACh.ID_INCROCIO = Utility.Utility.GetDBField(dr, "ID_INCROCIO");
ORACh.ID_ZONA = Utility.Utility.GetDBField(dr, "ID_ZONA");
ORACh.DESC_LUOGO = Utility.Utility.GetDBField(dr, "DESC_LUOGO");
ORACh.FLAG_R = Utility.Utility.GetDBField(dr, "FLAG_R");
ORACh.ID_112 = Utility.Utility.GetDBField(dr, "ID_112");
ORACh.COD_DIST_PREALL = Utility.Utility.GetDBField(dr, "COD_DIST_PREALL");
ORACh.ZONA_EMERGENZA = Utility.Utility.GetDBField(dr, "ZONA_EMERGENZA");
ORACh.PALAZZO = Utility.Utility.GetDBField(dr, "PALAZZO");
ORACh.SCALA = Utility.Utility.GetDBField(dr, "SCALA");
ORACh.PIANO = Utility.Utility.GetDBField(dr, "PIANO");
ORACh.INTERNO = Utility.Utility.GetDBField(dr, "INTERNO");
ORACh.CIVICO_VICINO = Utility.Utility.GetDBField(dr, "CIVICO_VICINO");
ListaChiamate.Add(ORACh);
}
conn.Dispose();
return(ListaChiamate);
}
private void button1_Click(object sender, EventArgs e)
{
using (OpenFileDialog ofd = new OpenFileDialog()
{
Filter = "Excel|*.xls", ValidateNames = true
})
{
if (ofd.ShowDialog() == DialogResult.OK)
{
FileStream fs = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read);
IExcelDataReader okuyucu = ExcelReaderFactory.CreateBinaryReader(fs);
okuyucu.IsFirstRowAsColumnNames = true;
ds = okuyucu.AsDataSet();
foreach (DataTable dt in ds.Tables)
{
dgvOgrenci.DataSource = ds.Tables[dt.TableName];
}
okuyucu.Close();
}
}
string oradb = "DATA SOURCE=localhost:1521/orcl;USER ID=harun;PASSWORD=harun";
OracleConnection conn = new OracleConnection(oradb); // C#
conn.Open();
string sql = "SELECT MAX(STD_ID) as max from STUDENT ";
OracleCommand cmdMAX = new OracleCommand(sql, conn);
int BuyukId = 0;
OracleDataReader readercmdMAX = cmdMAX.ExecuteReader();
while (readercmdMAX.Read())
{
BuyukId = Int32.Parse(readercmdMAX["max"].ToString());
}
for (int index = 0; index < dgvOgrenci.RowCount - 1; index++)
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO STUDENT(STD_ID, NAME, SURNAME, AGE, PARENT_ID, CLASS_ID) " + "VALUES(:STD_ID, :NAME, :SURNAME, :AGE, :PARENT_ID, :CLASS_ID)";
cmd.Parameters.Add("STD_ID", OracleDbType.Int32, 38).Value = BuyukId + 1 + index;
cmd.Parameters.Add("NAME", OracleDbType.Varchar2, 35).Value = Convert.ToString(dgvOgrenci.Rows[index].Cells[1].Value.ToString());
cmd.Parameters.Add("SURNAME", OracleDbType.Varchar2, 35).Value = dgvOgrenci.Rows[index].Cells[2].Value.ToString();
cmd.Parameters.Add("AGE", OracleDbType.Varchar2, 20).Value = dgvOgrenci.Rows[index].Cells[3].Value.ToString();
cmd.Parameters.Add("PARENT_ID", OracleDbType.Int32, 38).Value = Convert.ToInt32(dgvOgrenci.Rows[index].Cells[4].Value.ToString());
cmd.Parameters.Add("CLASS_ID", OracleDbType.Int32, 38).Value = Convert.ToInt32(dgvOgrenci.Rows[index].Cells[5].Value.ToString());
cmd.ExecuteNonQuery();
//con.Close();
// con.Open();
}
this.StudentDataGrid();
MessageBox.Show("İmport İşlemi Başarlı Bir Şekilde Gerçekleşti");
}
public string[] GetSearchItems(string prefix)
{
List<string> items = new List<string>();
using (OracleConnection conn = new OracleConnection())
{
conn.ConnectionString = ConfigurationManager
.ConnectionStrings["Bazi"].ConnectionString;
using (OracleCommand cmd = new OracleCommand())
{
cmd.CommandText = "select ime,igraid from Igra where upper(ime) like upper(:SearchText)||'%'";
cmd.Parameters.Add(new OracleParameter("SearchText", prefix));
cmd.Connection = conn;
conn.Open();
using (OracleDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
items.Add(string.Format("{0}`{1} Game", sdr.GetString(0), sdr.GetInt64(1)));
}
}
cmd.CommandText = "select IMEMODEL,HARDID from HARDVERPROIZVOD where upper(IMEMODEL) like upper(:SearchText)||'%'";
using (OracleDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
items.Add(string.Format("{0}`{1} Hardware", rdr.GetString(0), rdr.GetInt64(1)));
}
}
conn.Close();
}
return items.ToArray();
}
}