public static List<Auspiciante> getAllAuspiciantes()
{
OdbcConnection con = ConexionBD.ObtenerConexion();
DataSet ds = new DataSet();
List<Auspiciante> listaAuspiciantes = new List<Auspiciante>();
try
{
OdbcCommand cmd = new OdbcCommand("SELECT a.id, a.imagen FROM auspiciante a", con);
cmd.CommandType = CommandType.Text;
OdbcDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Auspiciante a = new Auspiciante();
a.IdAuspiciante = dr.GetInt32(dr.GetOrdinal("id"));
a.ImagenAuspiciante = ImagenDAL.getImagen(con,dr.GetInt32(dr.GetOrdinal("imagen")));
listaAuspiciantes.Add(a);
}
}
catch (Exception e)
{
throw new SportingException("Ocurrio un problema al intentar obtener los auspiciantes. " + e.Message);
}
return listaAuspiciantes;
}
/// <summary>
/// Setea la lista de imagenes de una noticia
/// </summary>
/// <returns></returns>
public static List<Imagen> getImagenes(Noticia noticia, OdbcConnection con)
{
List<Imagen> listaImagenes = new List<Imagen>();
OdbcDataReader dr = null;
String query = "SELECT i.id, i.pathBig, i.pathSmall, i.portada, i.pathMedium FROM imagen i, imagen_x_noticia n WHERE i.id=n.idImagen AND n.idNoticia=" + noticia.IdNoticia;
try
{
OdbcCommand cmd = new OdbcCommand(query, con);
cmd.CommandType = CommandType.Text;
dr = cmd.ExecuteReader();
while (dr.Read())
{
Imagen imagen = new Imagen();
imagen.IdImagen = dr.GetInt32(0);
imagen.PathBig = dr.GetString(1);
imagen.PathSmall = dr.GetString(2);
imagen.Portada = dr.GetBoolean(3);
imagen.PathMedium = dr.GetString(4);
listaImagenes.Add(imagen);
}
}
catch (Exception e)
{
throw new SportingException("Ocurrio un problema al intentar obtener las imagenes de las noticias. " + e.Message);
}
return listaImagenes;
}
/// <summary>
/// Obtiene la lista de empleados de la nómina
/// </summary>
/// <returns></returns>
public List <Catalogo> CatalogoEmpleados()
{
List <Catalogo> Empleados = new List <Catalogo>();
try
{
string QueryString = @"SELECT idEmpleado, NombreEmpleado FROM vwEmpleados";
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
Catalogo Empleado;
while (reader.Read())
{
Empleado = new Catalogo();
Empleado.id = reader.GetInt32(0);
Empleado.Descripcion = reader.GetString(1);
Empleados.Add(Empleado);
}
}
}
}
catch (Exception ex)
{
Log.EscribeLog("Error: ElsabonDA.CatalogoEmpleados - " + ex.Message);
}
return(Empleados);
}
public void selectemail()
{
indiaemail();
string[] strArray = new string[] { "Deli", "CHENNAI", "DELHI", "GZB.", "GRG.", "INDORE", "KANPUR", "MUMBAI -1", "MUMBAI-2", "NAVI MUMBAI", "Noida", "Pune" };
OdbcDataReader reader;
OdbcCommand cmd = new OdbcCommand();
Conn.Open();
cmd.Connection = Conn;
foreach (string city in strArray)
{
cmd.CommandText = "select * from cities where emailsent <> " + indiaemailid + " and currentlocation='" + city + "'";
reader = cmd.ExecuteReader();
if (reader.HasRows && (mailcount <= 10))
{
while (reader.Read())
{
string emailaddress = (string)reader["Email"];
if (emailaddress != null)
{
SendEmail(emailaddress, indiaemailid, city);
mailcount++;
}
}
break;
}
reader.Close();
}
Conn.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
string htmlstr = "<h2> </h2> <table style='width:100%;'>";
string cust_id = DropDownList1.SelectedItem.ToString();
string name;
string sex;
string dob;
string relationship;
string constr = Session["connection"].ToString();
OdbcConnection cn = new OdbcConnection(constr);
cn.Open();
string sql = "select * from NOMINEE where cust_id = '" + cust_id + "';";
OdbcCommand cmd = new OdbcCommand(sql, cn);
OdbcDataReader reader;
reader = cmd.ExecuteReader();
while (reader.Read())
{
name = reader["name"].ToString();
sex = reader["sex"].ToString();
dob = reader["dob"].ToString();
relationship = reader["relationship"].ToString();
htmlstr += "<tr><td class='style2'>Name:</td><td class='style1'>" + name + "</td><tr><td class = 'style2'>Sex:</td><td class = 'style1'>" +sex + "</td></tr><tr><td class='style2'> Relationship:</td><td class = 'style1'>" + relationship + "</td></tr><tr><td class='style2'>DOB:</td><td class = 'style1'>" + dob + "</td></tr><tr><td class='style2'>Customer ID :</td><td class = 'style1'>" + cust_id + "</td></tr>";
}
table_data.InnerHtml = htmlstr;
}
/// <summary>
/// Permite valorar si el empleado tiene justificado el día ingresado
/// </summary>
/// <param name="EmpleadoId"></param>
/// <param name="Fecha"></param>
/// <returns></returns>
public bool GetJustificacion(int EmpleadoId, DateTime Fecha)
{
bool DiaJustificado = false;
try
{
string QueryString = @"EXEC stp_GetJustificacionFalta " + EmpleadoId + ", '" + Fecha.ToString("yyyyMMdd") + "'";
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
DiaJustificado = reader.GetBoolean(0);
}
reader.Close();
reader.Dispose();
}
connection.Close();
connection.Dispose();
}
}
catch (Exception ex)
{
Log.EscribeLog("Error: ElsabonDA.GetJustificacion - " + ex.Message);
}
return(DiaJustificado);
}
protected void Page_Load(object sender, EventArgs e)
{
Button2.Enabled = false;
if (Session["auth"] == null)
{
Response.Redirect("Homepage.aspx");
}
ListItem newItem = new ListItem();
string constr = Session["connection"].ToString();
OdbcConnection cn = new OdbcConnection(constr);
cn.Open();
string sql = "select agent_id from AGENT";
OdbcCommand cmd = new OdbcCommand(sql, cn);
OdbcDataReader reader;
reader = cmd.ExecuteReader();
while (reader.Read())
{
newItem = new ListItem();
newItem.Text = reader["agent_id"].ToString();
DropDownList1.Items.Add(newItem);
}
reader.Close();
cn.Close();
}
// SQLGetReader
public Odbc.OdbcDataReader SQLGetReader(string command)
{
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = command;
sqlDataReader = sqlCommand.ExecuteReader();
return(sqlDataReader);
}
public override Stock GetStock(int stockId)
{
Stock stock = null;
using (OdbcCommand command = new OdbcCommand())
{
command.Connection = connection;
command.CommandText = string.Format("SELECT ticker, price FROM get_stock({0});", stockId);
using (OdbcDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult))
{
if (reader.HasRows)
{
stock = new Stock();
stock.Id = stockId;
stock.Ticker = reader.GetString(0);
stock.LastPrice = reader.GetDecimal(1);
}
reader.Close();
}
}
return stock;
}
/// <summary>
/// Permite obtener la fecha de antigüedad del empleado ingresado como parámetro
/// </summary>
/// <param name="EmpladoId"></param>
/// <returns>DateTime</returns>
public static DateTime GetAntiquity(int EmpleadoId)
{
DateTime AntiquityDate = new DateTime();
try
{
string QueryString = "SELECT fecha_antiguedad " +
" FROM Empleados EMP "+
"INNER JOIN vwEmpleados VISTA " +
" ON VISTA.idEmpleado = EMP.id "+
" AND VISTA.idCompania = EMP.compania "+
" WHERE VISTA.idEmpleado = "+ EmpleadoId;
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(CadenaConexion))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
AntiquityDate = reader.GetDateTime(0);
}
}
}
}
catch (Exception exc)
{
//Agregar bitácora para marcar las excepciones surgidas en la clase de acceso a datos.
}
return(AntiquityDate);
}
protected void Button1_Click(object sender, EventArgs e)
{
Button2.Enabled = true;
string constr = Session["connection"].ToString();
string agent_id = DropDownList1.SelectedItem.ToString();
OdbcConnection cn = new OdbcConnection(constr);
cn.Open();
string sql = "select * from AGENT where agent_id = '" + agent_id + "';";
OdbcCommand cmd = new OdbcCommand(sql, cn);
OdbcDataReader reader;
reader = cmd.ExecuteReader();
while (reader.Read())
{
TextBox1.Text = reader["f_name"].ToString();
TextBox2.Text = reader["l_name"].ToString();
TextBox3.Text = reader["phone"].ToString();
if (reader["sex"].ToString().Equals("M"))
{
slist.SelectedIndex = 1;
}
else
slist.SelectedIndex = 2;
TextBox5.Text = reader["address"].ToString();
}
}
//initializing the leader handler
public void BeginHandling()
{
try
{
email = DB_Handler.GetEmail(userName, dbConn);
// command = " SELECT T.Number, T.Issue_Date, I.Assigned, I.Attachment, I.JobDone, I.Status, I.Sequence, I.Due_Date, I.Requirements" +
// " FROM Ticket AS T, Ticket_Information AS I WHERE T.Number=I.Number";
command = " SELECT T.Number, T.Issue_Date, I.Assigned,I.Status, I.Due_Date" +
" FROM Ticket AS T, Ticket_Information AS I WHERE T.Number=I.Number AND T.Assigner ='" + userName + "'";
sqlCommand = new OdbcCommand(command, dbConn);
dbReader = sqlCommand.ExecuteReader();
while (dbReader.Read())
{
line = "";//empty the line so you can read the next row
for (int i = 0; i < dbReader.FieldCount-1; i++)
{
line += dbReader.GetString(i) + "##";// but the whole row in one column and send it, seperated by commas
}
writer.WriteLine(line);
writer.Flush();
}
writer.WriteLine(".");// end of reading from the database
writer.Flush();
DB_Handler.DisposeAll(sqlCommand, dbReader);
WaitForQueries();
}
catch
{
Console.WriteLine(" The Database is down please try again later");//for debugging server
DB_Handler.DisposeAll(sqlCommand, dbReader);
}
}
/// <summary>
/// Permite obtener la cantidad de días que el empleado ha gozado de vacaciones en el periodo definido entre FechaInicio y FechaFin
/// </summary>
/// <param name="empleado"></param>
/// <param name="FechaInicio"></param>
/// <param name="FechaFin"></param>
/// <returns></returns>
public static int GetTakenVacations(int EmpleadoId, DateTime FechaInicio, DateTime FechaFin)
{
int TakenVacations = 0;
try
{
string QueryString = "SELECT SUM(VACACIONES.dias_descanso) AS VacacionesTomadas " +
" FROM vacaciones_empleado VACACIONES "+
"INNER JOIN vwEmpleados EMPLEADOS " +
" ON VACACIONES.id = EMPLEADOS.idEmpleado " +
" AND VACACIONES.compania = EMPLEADOS.idCompania "+
" WHERE EMPLEADOS.idEmpleado = " + EmpleadoId +
" AND VACACIONES.fecha_inicio BETWEEN '"+ FechaInicio.ToString("yyyy-MM-dd HH:mm:ss") + "' " +
" AND '"+ FechaFin.ToString("yyyy-MM-dd HH:mm:ss") + "'";
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(CadenaConexion))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
TakenVacations = reader.GetInt32(0);
}
}
}
}
catch (Exception exc)
{
//Agregar bitácora para marcar las excepciones surgidas en la clase de acceso a datos.
}
return(TakenVacations);
}
static void Main(string[] args)
{
//"DRIVER={MySQL ODBC 5.2w Driver};SERVER=localhost;DATABASE=config;UID=root;PASSWORD=liu355dq;"
string MyConString = "DRIVER={MySQL ODBC 5.2w Driver};" +
"SERVER=localhost;" +
"DATABASE=g_db;" +
"UID=root;" +
"PASSWORD=liu355dq;";
OdbcConnection myconnection = new OdbcConnection(MyConString);
OdbcCommand cmd = new OdbcCommand("select * from g_operator");
cmd.Connection = myconnection;
myconnection.Open();
OdbcDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader[1]);
}
myconnection.Close();
Console.ReadKey();
}
public static bool credentials_correct(string driver, string user, string password)
{
crypt crypt = new crypt();
string encPass = crypt.HashString(password);
try
{
OdbcConnection con = new OdbcConnection("Driver=" + driver + ";Server=" + server + ";Database=" + dbase + ";UID=" + uid + ";PWD=" + crypt.DecryptString(pwd) + ";OPTION=3");
OdbcCommand com = new OdbcCommand("SELECT user FROM users where user = '******' and password = '******'", con);
con.Open();
OdbcDataReader dr = com.ExecuteReader();
int tmpInt = 0;
while (dr.Read())
{
tmpInt++;
}
dr.Close();
con.Close();
return tmpInt > 0;
}
catch
{
return false;
}
}
public static List<GisMovil> getAllGisMovil(OdbcConnection conn)
{
List<GisMovil> list = new List<GisMovil>();
string sqlquery = "select * from UNIGIS.GIS_MOVILES";
OdbcCommand comm;
OdbcDataReader reader;
DataTable dt;
object[] values;
int fields;
comm = new OdbcCommand(sqlquery, conn);
reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
if (reader.HasRows)
{
dt = reader.GetSchemaTable();
values = new Object[dt.Columns.Count];
//let's process all
GisMovil eq;
while (reader.Read())
{
fields = reader.GetValues(values);
eq = GisMovil.Parse(values);
list.Add(eq);
}
}
return list;
}
public static OdbcDataReader getODBC()
{
string strConn = @"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};ReadOnly=False;Dbq=";//Driver驱动程序,ReadOnly=False必须有，否则插入数据会报错,Dbq
OpenFileDialog file = new OpenFileDialog();
file.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
file.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
file.Multiselect = false;
if (file.ShowDialog() == DialogResult.Cancel)
{
return(null);
}
var path = file.FileName;
strConn += path;
System.Data.Odbc.OdbcConnection odbcConn = new System.Data.Odbc.OdbcConnection(strConn);//ODBC连接
try
{
odbcConn.Open(); //打开连接
string sSQL = "SELECT * FROM [appstore_games$]"; //注意格式，SQL语句查询表格所有的数据
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(sSQL, odbcConn); //初始化ODBC命令
mConn = odbcConn;
// comm.ExecuteNonQuery();//执行SQL语句
return(command.ExecuteReader());
}
catch (Exception excp)
{
throw excp;
}
}
/// <summary>
/// SELECT compania AS id, razon_social AS compania FROM companias
/// </summary>
/// <returns></returns>
public List <Catalogo> CatalogoCompanias()
{
List <Catalogo> Companias = new List <Catalogo>();
try
{
string QueryString = @"SELECT compania AS id, razon_social AS compania FROM companias";
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
Catalogo compania;
while (reader.Read())
{
compania = new Catalogo();
compania.id = reader.GetInt32(0);
compania.Descripcion = reader.GetString(1);
Companias.Add(compania);
}
compania = new Catalogo();
compania.id = 1000;
compania.Descripcion = "TODAS";
Companias.Add(compania);
}
}
}
catch (Exception ex)
{
Log.EscribeLog("Error: ElsabonDA.CatalogoCompanias - " + ex.Message);
}
return(Companias);
}
public void setClientDBData(string clientId)
{
try
{
#region get values from DB and set the clients prop
string cmd = "SELECT smoker_code, smoker_value, smoking_stat_assess_date, ss_demographics_dict_8_code,ss_demographics_dict_8_value " +
"FROM patient_current_demographics " +
"WHERE PATID = ?";
using (OdbcConnection con = new OdbcConnection(ConnectionStringPM))
{
con.Open();
using (var command = new OdbcCommand(cmd, con))
{
command.Parameters.Add(new OdbcParameter("PATID", clientId));
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
this.client.Id = clientId;
this.client.LGBT = reader["ss_demographics_dict_8_code"].ToString();
this.client.SmokerStatus = reader["smoker_code"].ToString();
}
}
}
}
status = "ClientId = " + clientId + "<br>" +
"LGBT = " + this.client.LGBT + "<br>" +
"Smoker = " + this.client.SmokerStatus + "<br>";
#endregion
}
catch (Exception exc) { status = exc.ToString(); }
}
/// <summary>
/// Permite obtener el puesto de un empleado
/// </summary>
/// <param name="EmpleadoId"></param>
/// <returns></returns>
public string ObtenerPuestoEmpleado(int EmpleadoId)
{
string Puesto = string.Empty;
try
{
string QueryString = @"SELECT Puesto
FROM vwEmpleados
WHERE idEmpleado = " + EmpleadoId.ToString();
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(CadenaConexion))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Puesto = reader.GetString(0);
}
}
}
}
catch (Exception ex)
{
//
}
return(Puesto);
}
public bool Select(string tableName , ref DataTable dataTable)
{
if ( connection == null )
return false ;
try
{
dataTable = new DataTable("ODBCDataTable");
string query = "SELECT * FROM " + OnGetTableName(tableName) ;
OdbcCommand cmd = new OdbcCommand(query, connection);
OdbcDataReader dataReader = cmd.ExecuteReader();
dataTable.Load(dataReader) ;
dataReader.Close();
}
catch( Exception ex )
{
if(!tableName.Equals("checkDataTable"))
{
Debug.Log( ex.ToString() ) ;
}
Close() ;
return false ;
}
return true ;
}
public Dev_Form()
{
InitializeComponent();
btnDel.Visible = false;
foreach (DataGridViewRow item in this.dataGrid_Dev.SelectedRows)
btnDel.Visible = true;
cn.Open();
using (cmd = new OdbcCommand("select strid,Structure from structure", cn))
{
using (OdbcDataReader reader = cmd.ExecuteReader())
{
strid.Add("");
Structure.Add("");
while (reader.Read())
{
strid.Add(reader.GetString(0));
Structure.Add(reader.GetString(1));
}
}
}
//structures.DataSource = items;
this.structures.Items.AddRange(Structure.ToArray());
// structures.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
structures.AutoCompleteSource = AutoCompleteSource.ListItems;
cn.Close();
}
/// <summary>
/// Permite obtener las vacaciones tomadas según el tiempo de antiguedad
/// </summary>
/// <param name="EmpleadoId"></param>
/// <param name="FechaInicio"></param>
/// <param name="FechaFin"></param>
/// <returns></returns>
public static int GetTakenVacations(int EmpleadoId, int Antiguedad)
{
int TakenVacations = 0;
try
{
string QueryString = "SELECT SUM(dias_descanso) FROM vacaciones_empleado WHERE id = " + EmpleadoId + " AND antiguedad = " + Antiguedad;
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(CadenaConexion))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
TakenVacations = reader.GetInt32(0);
}
}
}
}
catch (Exception exc)
{
//Agregar bitácora para marcar las excepciones surgidas en la clase de acceso a datos.
}
return(TakenVacations);
}
static void Main(string[] args)
{
string dbname = Keyval().GetKey(0);//use GetKey for getting the Key in position specified
string tblnm = Keyval().Get(dbname);// Get(string) where string is the key and thus it get s the value where key is string
string dir = Keyval()["dir"];
string UID = Keyval()["UID"];
string PWD = Keyval()["PWD"];
var conn = new OdbcConnection("DRIVER=Firebird/InterBase(r) driver; UID="+UID+"; PWD="+PWD+";DBNAME="+dir+";");
conn.Open();
//string tblnm = ReadDbname();
try{
string sql = "SELECT * FROM "+tblnm;
OdbcCommand cmd = new OdbcCommand(sql, conn);
OdbcDataReader dr = cmd.ExecuteReader();
Console.WriteLine("FROM DATABASE: "+dbname+" TABLE ==> "+tblnm);
while(dr.Read())
{
Console.WriteLine(dr[0]+"___"+dr[1]+"___"+dr[2]);
}
dr.Close();
}
catch(Exception ex){
Console.WriteLine(ex.ToString());
}
}
public void getCCTVDataByDeviceName(string devName, ref int cctvid, ref int preset)
{
OdbcConnection cn = new OdbcConnection(Global.Db2ConnectionString);
OdbcCommand cmd=new OdbcCommand("select CAMERA_ID,PRESET_ID from tblCCTVPreset where devicename='"+devName+"'");
cmd.Connection = cn;
OdbcDataReader rd;
try
{
cn.Open();
rd = cmd.ExecuteReader();
if (rd.Read())
{
cctvid = System.Convert.ToInt32(rd[0]);
preset = System.Convert.ToInt32(rd[1]);
}
else
{
cctvid = -1;
preset = -1;
}
}
catch (Exception ex)
{
RemoteInterface.ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace);
}
finally
{
cn.Close();
}
}
protected ArrayList alBuscarUsuarios()
{
ipHostName = Dns.GetHostEntry(Dns.GetHostName());
foreach (IPAddress ip in ipHostName.AddressList)
{
sQuery = "SELECT cuenta_usr, foto_usr FROM tabm_SGusuario T1 INNER JOIN tabt_SGsesion T2 ON T2.cod_usr = T1.cod_usr WHERE ipdir_usr= \"" + ip.ToString() + "\" LIMIT 6";
if (bAbrirConexion())
{
try
{
OdbcCommand ocComando = new OdbcCommand(sQuery, Conexion);
OdbcDataReader odcReader = ocComando.ExecuteReader();
while (odcReader.Read())
{
E_Usuario eUsuario = new E_Usuario();
eUsuario.Cuenta = odcReader[0].ToString();
eUsuario.ImagenBytes = (byte[])odcReader[1];
alResultados.Add(eUsuario);
}
vCerrarConexion();
}
catch (OdbcException ex)
{
return null;
}
}
}
return alResultados;
}
public static Hashtable getMapTypeList()
{
Hashtable result = new Hashtable();
OdbcConnection con = getCon();
string sql = "select * from MAPLINK ";
OdbcCommand mycm = new OdbcCommand(sql, con);
OdbcDataReader msdr = (OdbcDataReader)mycm.ExecuteReader();
while (msdr.Read())
{
if (msdr.HasRows)
{
ArrayList propList = new ArrayList();
string mapType = msdr.GetString(0);
string picSrc = msdr.GetString(1);
string mapTypeHtml = msdr.GetString(2);
propList.Add(picSrc);
propList.Add(mapTypeHtml);
result.Add(mapType, propList);
}
}
foreach (DictionaryEntry de in result)
{
Console.WriteLine(de.Key);// 取得键
ArrayList propList = (ArrayList)de.Value;
Console.WriteLine(propList[0] + "-------" + propList[1]);// 取得值
}
msdr.Close();
con.Close();
return result;
}
protected void Page_Load(object sender, EventArgs e)
{
// Cria lista para o objeto
List<agenda> msg = new List<agenda>();
// Adiciona dados ao objeto
try
{
using (OdbcConnection connection = new OdbcConnection(ConfigurationManager.ConnectionStrings["MySQLServerConnStr"].ConnectionString))
{
connection.Open();
using (OdbcCommand command = new OdbcCommand("SELECT * FROM agenda", connection))
using (OdbcDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
msg.Add(new agenda(Int32.Parse(dr["id"].ToString()), dr["nome"].ToString(), dr["telefone"].ToString()));
}
dr.Close();
}
connection.Close();
}
}
catch (Exception ex)
{
Response.Write("An error occured: " + ex.Message);
}
// Adiciona objeto a sessão
Session["registros"] = msg;
// Cria pagina no método HTTP GET
if (Request.HttpMethod.Equals("GET"))
doGet();
}
public List<string> GetTables()
{
var tableList = new List<string>();
using (_connection)
{
var cmd = new OdbcCommand("SELECT * FROM SYS.SYSTAB where creator = USER_ID();", _connection);
using (var reader = cmd.ExecuteReader())
{
for (int ordinal = 0; ordinal < reader.FieldCount; ordinal++)
{
var x = reader.GetName(ordinal);
}
while (reader.Read())
{
for (var i = 0; i < reader.FieldCount; i++)
{
// option here is to read a name of the column
Console.WriteLine(reader[i]);
tableList.Add(reader[i].ToString());
}
}
}
}
return tableList;
}
public ReviewDto Get(Guid id)
{
ReviewDto dto = new ReviewDto();
var connStr = Properties.Resources.ConnectionString;
using (OdbcConnection connection = new OdbcConnection(connStr))
{
connection.Open();
string queryStr = string.Format(@"SELECT * " +
@"FROM {0} " +
@"WHERE {1} = '{2}'",
Properties.Resources.ReviewTable,
Properties.Resources.ReviewIdColumn, id.ToString());
OdbcCommand cmd = new OdbcCommand(queryStr, connection);
var reader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleResult);
if (reader.Read())
{
dto.Id = reader.GetGuid(reader.GetOrdinal(Properties.Resources.ReviewIdColumn));
dto.Rating = reader.GetInt32(reader.GetOrdinal(Properties.Resources.ReviewRatingColumn));
dto.Comments = reader.GetString(reader.GetOrdinal(Properties.Resources.ReviewCommentsColumn));
dto.CustomerId = reader.GetGuid(reader.GetOrdinal(Properties.Resources.ReviewCustomerIdColumn));
}
else
throw new ReviewDataException();
}
return dto;
}
static int Main ()
{
if (Environment.GetEnvironmentVariable ("MONO_TESTS_ODBC") == null)
return 0;
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
Thread.CurrentThread.CurrentUICulture = CultureInfo.InvariantCulture;
OdbcConnection conn = new OdbcConnection (CreateOdbcConnectionString ());
conn.Open ();
OdbcCommand cmd;
OdbcDataReader dr;
cmd = new OdbcCommand ("SELECT @@version", conn);
dr = cmd.ExecuteReader ();
if (!dr.Read ())
return 1;
if (dr.GetString (0).IndexOf ("Microsoft SQL Server") == -1)
return 2;
dr.Close ();
conn.Dispose ();
return 0;
}
public static System.Data.Odbc.OdbcDataReader ExecuteReaderQueryOnDB(string sSQL, string s_ConnectionString)
{
System.Data.Odbc.OdbcConnection QConnection = null;
System.Data.Odbc.OdbcCommand QCommand = null;
try
{
QConnection = new System.Data.Odbc.OdbcConnection(s_ConnectionString);
QCommand = new System.Data.Odbc.OdbcCommand(sSQL, QConnection);
QConnection.Open();
return(QCommand.ExecuteReader());
}
finally
{
if (QCommand != null)
{
QCommand.Dispose();
}
QCommand = null;
if (QConnection != null && QConnection.State != System.Data.ConnectionState.Closed)
{
QConnection.Close();
}
if (QConnection != null)
{
QConnection.Dispose();
}
QConnection = null;
}
}
//initializing the leader handler
public void BeginHandling()
{
try
{
//writer.WriteLine("I am not done");
command = " SELECT T.Number, T.Assigner, T.Issue_Date, I.Assigned, I.Attachment, I.JobDone, I.Status, I.Sequence, I.Due_Date, I.Requirements" +
" FROM Ticket AS T, Ticket_Information AS I" +
" WHERE T.Number=I.Number AND I.Assigned='" + userName +
"' AND (I.Status = 'Assigned' OR I.Status='Waiting' OR I.Status='Work_In_Progress') AND I.Due_Date > #" + System.DateTime.Now + "#";
sqlCommand = new OdbcCommand(command, dbConn);
dbReader = sqlCommand.ExecuteReader();
//Console.WriteLine("I am done");
while (dbReader.Read())
{
line = "";//empty the line so you can read the next row
for (int i = 0; i < dbReader.FieldCount - 1; i++)
{
line += dbReader.GetString(i) + "##";// but the whole row in one column and send it, seperated by commas
}
writer.WriteLine(line);
writer.Flush();
}
writer.WriteLine(".");// end of reading from the database
writer.Flush();
DB_Handler.DisposeAll(sqlCommand, dbReader);
WaitForQueries();
//do not forget to close the reader
}
catch
{
Console.WriteLine(" The Database is down please try again later");//for debugging server
}
}
/// <summary>
/// Método que permite obtener el nivel de estructura de un empleado
/// </summary>
/// <param name="EmpleadoId"></param>
/// <returns></returns>
public string ObtenerNivelEstructura(int EmpleadoId)
{
string Response = string.Empty;
try
{
string QueryString = @"SELECT NivelDepartamento
FROM vwEmpleados
WHERE idEmpleado = '" + EmpleadoId.ToString() + "'";
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Response = reader.GetString(0);
}
}
}
}
catch (Exception ex)
{
Log.EscribeLog("Error: ElsabonDA.ObtenerNivelEstructura - " + ex.Message);
}
return(Response);
}
protected void Button1_Click1(object sender, EventArgs e)
{
int i;
string htmlstr = "<table style='width:100%;'>";
string data;
string sql = TextBox1.Text;
string constr = Session["connection"].ToString();
OdbcConnection cn = new OdbcConnection(constr);
cn.Open();
OdbcCommand cmd = new OdbcCommand(sql, cn);
OdbcDataReader reader;
reader = cmd.ExecuteReader();
int col_count = reader.FieldCount;
int count1;
while (reader.Read())
{
htmlstr += "<tr>";
count1 = col_count;
i = 0;
while (count1 != 0)
{
data = reader[i].ToString();
htmlstr += "<td class='style2'>" + data + "</td>";
count1--;
i++;
}
htmlstr += "</tr>";
}
htmlstr += "</table>";
table_data.InnerHtml = htmlstr;
}
protected void Page_Load(object sender, EventArgs e)
{
// string ImageId = System.Convert.ToString(Request.QueryString["name"]);
int articleID = System.Convert.ToInt32(Request.QueryString["textid"]);
string MyConString = ConfigurationSettings.AppSettings["connectionString"];
OdbcConnection connection = new OdbcConnection(MyConString);
string sqlNews1 = "select * FROM latestnews";
OdbcCommand commandNews1 = new OdbcCommand(sqlNews1, connection);
connection.Open();
OdbcDataReader dr = commandNews1.ExecuteReader(CommandBehavior.CloseConnection);
if (dr.Read())
{
Response.ContentType = dr["date"].ToString();
Response.ContentType = dr["title"].ToString();
Response.ContentType = dr["content"].ToString();
}
connection.Close();
string selectNews = "SELECT date, title, content FROM latestnews ORDER BY date DESC";
OdbcCommand command = new OdbcCommand(selectNews, connection);
OdbcDataAdapter adapter = new OdbcDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataList1.DataSource = ds;
DataList1.DataBind();
}
public static List<Jugador> getJugadores(Plantel plantel)
{
OdbcConnection con = ConexionBD.ObtenerConexion();
OdbcCommand cmd = null;
List<Jugador> jugadores = new List<Jugador>();
try
{
cmd = new OdbcCommand("SELECT j.id, j.nombreApellido, j.posicion, j.idPlantel " +
"FROM jugador j WHERE j.idPlantel = "+plantel.IdPlantel, con);
cmd.CommandType = CommandType.Text;
OdbcDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Jugador jugador = new Jugador();
jugador.IdJugador = dr.GetInt32(0);
jugador.NombreApellido = dr.GetString(1);
jugador.Posicion = dr.GetString(2);
jugador.Foto = ImagenDAL.getImagenJugador(con, dr.GetInt32(0));
jugadores.Add(jugador);
}
}
catch (Exception e)
{
throw new SportingException("Ocurrio un error al intentar obtener los jugadores del plantel. " + e.Message);
}
finally
{
cmd.Connection.Close();
}
return jugadores ;
}
/// <summary>
/// Use the Username property to fill in the remainder of the fields if the User exists in the database.
/// </summary>
/// <returns>True, if the record was found. Else the return value will be false.</returns>
public bool Find()
{
string mySelectQuery = "select AES_DECRYPT(Password, 'key'), idNumber, UserClass from User where Username = '******'";
OdbcConnection myConnection = GetConnection();
OdbcCommand myCommand = new OdbcCommand(mySelectQuery, myConnection);
myConnection.Open();
bool recordFound = false;
OdbcDataReader myReader;
myReader = myCommand.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (myReader.Read())
{
m_Password = myReader.GetString(0);
m_idNumber = myReader.GetInt32(1);
m_UserClass = myReader.GetString(2);
recordFound = true;
}
}
finally
{
myReader.Close();
myConnection.Close();
}
return recordFound;
}
public UserInfo GetUser(string userId)
{
string queryString = "SELECT * FROM UserDetail WHERE UserId Like '" + userId + "'";
using (OdbcConnection con = new OdbcConnection(ConnectionString))
{
con.Open();
using (OdbcCommand cmd = new OdbcCommand(queryString, con))
{
OdbcDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
UserInfo user = new UserInfo()
{
UserId = reader.GetString(reader.GetOrdinal("UserId")),
EmailId = reader.GetString(reader.GetOrdinal("EmailId")),
LastLogIn = reader.GetDateTime(reader.GetOrdinal("LastLogIn")),
RegisteredTimestamp = reader.GetDateTime(reader.GetOrdinal("RegisteredTimestamp"))
};
return user;
}
}
}
return null;
}
/// <summary>
/// Obtiene el parámetro máximo de antiguedad de la tabla de vacaciones
/// </summary>
/// <returns></returns>
public static int MaximaAntiguedadVacaciones(int EmpleadoId)
{
int AntVacaciones = 0;
try
{
string QueryString = @"SELECT MAX (antiguedad) FROM vacaciones_empleado WHERE id =" + EmpleadoId.ToString();
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(CadenaConexion))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
AntVacaciones = Convert.ToInt32(reader.GetString(0));
}
}
}
}
catch (Exception ex)
{
//
}
return(AntVacaciones);
}
protected void Button1_Click(object sender, EventArgs e)
{
string htmlstr = "<h2> </h2> <table style='width:100%;'>";
string report_no = DropDownList1.SelectedItem.ToString();
string claim_date;
string accident_date;
string claim_amount;
string pol_no;
string constr = Session["connection"].ToString();
OdbcConnection cn = new OdbcConnection(constr);
cn.Open();
string sql = "select * from CLAIM where report_no = '" + report_no+ "';";
OdbcCommand cmd = new OdbcCommand(sql, cn);
OdbcDataReader reader;
reader = cmd.ExecuteReader();
while (reader.Read())
{
claim_date = reader["claim_date"].ToString();
claim_amount = reader["claim_amount"].ToString();
accident_date = reader["accident_date"].ToString();
pol_no = reader["pol_no"].ToString();
htmlstr += "<tr><td class='style2'>Report No:</td><td class='style1'>" + report_no + "</td><tr><td class = 'style2'>Claim Date:</td><td class = 'style1'>" + claim_date + "</td></tr><tr><td class='style2'> Claim Amount:</td><td class = 'style1'>" + claim_amount +"</td></tr><tr><td class='style2'>Policy No:</td><td class = 'style1'>" + pol_no + "</td></tr>";
}
table_data.InnerHtml = htmlstr;
}
/// <summary>
/// retorna una imagen
/// </summary>
/// <returns></returns>
public static Imagen getImagen(OdbcConnection con, int idImagen)
{
DataSet ds = new DataSet();
Imagen imagen = new Imagen();
try
{
OdbcCommand cmd = new OdbcCommand("SELECT i.id, i.pathBig, i.pathSmall, i.portada, i.pathMedium FROM imagen i WHERE i.id=" + idImagen, con);
cmd.CommandType = CommandType.Text;
OdbcDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
imagen.IdImagen = dr.GetInt32(0);
imagen.PathBig = dr.GetString(1);
imagen.PathSmall = dr.GetString(2);
imagen.Portada = dr.GetBoolean(3);
imagen.PathMedium = dr.GetString(4);
}
}
catch (Exception e)
{
throw new SportingException("Ocurrio un problema al intentar obtener la imagen. " + e.Message);
}
return imagen;
}
private void InsertNotes()
{
Notes = Interaction.InputBox("Enter Note:", "TeleRad - Client", " ");
string strSQL = "";
string centre = " ";
Myconnectionclass CN2 = new Myconnectionclass();
OdbcCommand Cmd2;
OdbcDataReader Rs2;
CN2.OpenConnection();
Cmd2 = new System.Data.Odbc.OdbcCommand("SELECT * from UserMaster WHERE UserName='******'", CN2.DBConnection);
Rs2 = Cmd2.ExecuteReader();
if (Rs2.HasRows)
{
centre = Rs2["Centre"].ToString();
}
Rs2.Close();
Cmd2.Dispose();
CN2.closeconnection();
CN.OpenConnection();
Cmd = new System.Data.Odbc.OdbcCommand("SELECT * from Reports WHERE StudyUID='" + StID + "'", CN.DBConnection);
Rs1 = Cmd.ExecuteReader();
if (Rs1.HasRows)
{
if (Notes.Length <= 0)
{
strSQL = "UPDATE Reports SET IsNotes=0, Centre='" + centre + "' WHERE StudyUID='" + StID + "'";
}
else
{
strSQL = "UPDATE Reports SET Notes='" + Notes + "', IsNotes=1, Centre='" + centre + "' WHERE StudyUID='" + StID + "'";
}
}
else
{
if (Notes.Length <= 0)
{
strSQL = "INSERT INTO Reports (StudyUID, IsNotes,Centre) VALUES ('" + StID + "',1,'" + centre + "')";
}
else
{
strSQL = "INSERT INTO Reports (StudyUID, Notes, IsNotes,Centre) VALUES ('" + StID + "', '" + Notes + "', 1,'" + centre + "')";
}
}
Rs1.Close();
Cmd.Dispose();
CN.closeconnection();
CN.OpenConnection();
Cmd = new System.Data.Odbc.OdbcCommand(strSQL, CN.DBConnection);
Rs1 = Cmd.ExecuteReader();
Rs1.Close();
Cmd.Dispose();
CN.closeconnection();
MessageBox.Show("Successfully Sent Study", "TeleRad - Client", MessageBoxButtons.OK);
}
static List <etf_epf_record> GetListETF(string filter = "")
{
System.Data.Odbc.OdbcConnection l_objConnection = new System.Data.Odbc.OdbcConnection();
System.Data.Odbc.OdbcCommand l_objCmd = new System.Data.Odbc.OdbcCommand();
System.Data.Odbc.OdbcDataReader l_objRdr = null;
l_objConnection.ConnectionString = "DSN=PRODDSN_LIVE;PWD=INTERNET;UID=INTERNET";
l_objConnection.Open();
l_objCmd.Connection = l_objConnection;
l_objCmd.CommandType = CommandType.Text;
string sql = string.Empty;
if (string.IsNullOrEmpty(filter))
{
sql = "select etref,etreg,etamt,etpflg,etdate,ettime,etptyp,etfrpd,ettopd,etnomb,etbkcd,etbrcd,etacct,etdcd,etadat,etcopr,ettran from inetprddt1.etp1000 where etdate>='20170801' and etdate<='20170831' and etref like 'CBCETF%' order by etdate";
}
else
{
sql = "select etref,etreg,etamt,etpflg,etdate,ettime,etptyp,etfrpd,ettopd,etnomb,etbkcd,etbrcd,etacct,etdcd,etadat,etcopr,ettran from inetprddt1.etp1000 where etdate>='20170801' and etdate<='20170831' and etref like 'CBCETF%' and etref in " + filter + " order by etdate";
}
l_objCmd.CommandText = sql;
if (l_objRdr != null)
{
l_objRdr.Close();
}
l_objRdr = l_objCmd.ExecuteReader();
List <etf_epf_record> list = new List <etf_epf_record>();
if (l_objRdr.HasRows)
{
while (l_objRdr.Read())
{
etf_epf_record file = new etf_epf_record();
file.etref = l_objRdr["etref"].ToString().Trim();
file.etreg = l_objRdr["etreg"].ToString().Trim();
file.etamt = l_objRdr["etamt"].ToString().Trim();
file.etpflg = l_objRdr["etpflg"].ToString().Trim();
file.etdate = l_objRdr["etdate"].ToString().Trim();
file.ettime = l_objRdr["ettime"].ToString().Trim();
file.etptyp = l_objRdr["etptyp"].ToString().Trim();
file.etfrpd = l_objRdr["etfrpd"].ToString().Trim(); //From Period
file.ettopd = l_objRdr["ettopd"].ToString().Trim(); //To Period
file.etnomb = l_objRdr["etnomb"].ToString().Trim(); //EMployeed
file.etadat = l_objRdr["etadat"].ToString().Trim();
file.etcopr = l_objRdr["etcopr"].ToString().Trim();
file.ettran = l_objRdr["ettran"].ToString().Trim();
list.Add(file);
}
}
return(list);
}
protected void btnOK_Click(object sender, EventArgs e)
{
//System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection ();
System.Data.Odbc.OdbcConnection conn;
System.Data.Odbc.OdbcDataAdapter ap;
System.Data.Odbc.OdbcDataReader apcheck;
System.Data.Odbc.OdbcCommand objcommand;
objcommand = new System.Data.Odbc.OdbcCommand();
DataSet ds = new DataSet();
//string connectionString = "Data Source=(local);Initial Catalog=Northwind; User ID=Admin; Password=misdyu";
string constr = "FIL=MS Access;SERVER=localhost;DSN=Pform;UID=root;PWD=;OPTION=3";
conn = new System.Data.Odbc.OdbcConnection(constr);
conn.Open();
//採用ODBC，則禁止使用sqlcommand相關指令，會出現語法錯誤，一直抓不到bug
objcommand.Connection = conn;
objcommand.CommandText = "Select * From accountID Where Name = '" + TextBox1.Text + "'";
try
{
// Process data here.
apcheck = objcommand.ExecuteReader();
if (apcheck.HasRows)
{
Label1.Text = "帳號：該帳戶己註冊過";
apcheck.Close();
conn.Close();
}
else
{
conn.Close();
conn = new System.Data.Odbc.OdbcConnection(constr);
conn.Open();
string sql1 = "Insert into accountID (Name, Pass, Email, Address, Gender, Idt) values ('" + TextBox1.Text + "', '" + TextBox2.Text + "', '" + TextBox4.Text + "' , '" + TextBox3.Text + "' , '" + RadioButtonList1.Text + "', '" + DropDownList1.Text + "')";
ap = new System.Data.Odbc.OdbcDataAdapter(sql1, conn);
ap.Fill(ds);
//傳統寫法
Response.Redirect("ShowData.aspx?ID=" + TextBox1.Text + "&Password="******"&Msg=" + DropDownList1.Text);
//使用字串參數的型式,ERROR會出現兩次註冊
//Response.Redirect(string.Format("ShowData.aspx?ID={0}&Password={1}&Msg={2}", TextBox1.Text, TextBox2.Text, DropDownList1.Text));
}
}
catch (Exception ex)
{
//新版不支援下行
// MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
public Hashtable Read_Values(string[] Tag_Name, DateTime Time_Stamp)
{
Hashtable Result = new Hashtable();
string Tag_cond = "(FALSE"; // Makes it easier to add OR conditions.
foreach (string Tag in Tag_Name)
{
if (Sanitize(Tag))
{
Tag_cond += "\n OR NAME = '" + Tag + "'";
}
}
Tag_cond += ")";
Cmdr.CommandText =
@"SELECT
NAME, VALUE, STATUS
FROM
History
WHERE
" + Tag_cond + @"
AND TS > CAST('" + Time_Stamp.AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + @"' AS TIMESTAMP FORMAT 'YYYY-MM-DD HH:MI:SS')
AND TS < CAST('" + Time_Stamp.AddSeconds(1).ToString("yyyy-MM-dd HH:mm:ss") + @"' AS TIMESTAMP FORMAT 'YYYY-MM-DD HH:MI:SS')
AND PERIOD = 000:0:01.0;
";
#if DEBUG
DateTime Start_Time = DateTime.Now;
#endif
System.Data.Odbc.OdbcDataReader DR = Cmdr.ExecuteReader(System.Data.CommandBehavior.SingleResult);
while (DR.Read())
{
if (DR.GetValue(2).ToString() == "0") // if status is good
{
Result.Add(DR.GetValue(0).ToString(), DR.GetValue(1));
}
}
DR.Close();
#if DEBUG
double Run_Time = (DateTime.Now - Start_Time).TotalMilliseconds;
Console.WriteLine("Read {0} tags from IP21 in {1} ms", Tag_Name.Length, Run_Time);
foreach (DictionaryEntry p in Result)
{
Console.WriteLine("{0}: {1}", p.Key, p.Value);
}
#endif
return(Result);
}
private void ListTables(string Database)
{
if (OdbcCon.State == ConnectionState.Open)
{
OdbcCom = new System.Data.Odbc.OdbcCommand("SELECT * FROM [mydb].[dbo].[user]", OdbcCon);
OdbcDR = OdbcCom.ExecuteReader();
Console.WriteLine("Content in Table [" + Database + "].[dbo].[user] :\r\n");
while (OdbcDR.Read())
{
Console.WriteLine("Values >> " + OdbcDR[0] + " , " + OdbcDR[1] + " , " + OdbcDR[2] + " , " + OdbcDR[3] + " , " + OdbcDR[4] + "\r\n");
}
}
}
/// <summary>
/// Obtiene la información complementaria de un empleado desde la lectura del head count
/// </summary>
/// <returns></returns>
public List <ComplementoEmpleado> ObtenerInformacionComplementaria()
{
List <ComplementoEmpleado> InformacionComplementaria = new List <ComplementoEmpleado>();
try
{
string QueryString = @"SELECT id, RazonSocial, Nomina, CentroCostos FROM headcount";
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
ComplementoEmpleado Employer;
string [] CentroCostos;
while (reader.Read())
{
Employer = new ComplementoEmpleado();
Employer.EmpleadoId = reader.GetInt32(0);
Employer.RazonSocial = reader.GetString(1);
Employer.Nomina = reader.GetString(2);
CentroCostos = reader.GetString(3).Split(' ');
Employer.ClaveCentroCostos = CentroCostos.Length > 0 ? CentroCostos[0] : "";
if (Employer.ClaveCentroCostos != "")
{
for (int i = 1; i < CentroCostos.Length; i++)
{
Employer.DescripcionCentroCostos += CentroCostos[i] + " ";
}
}
InformacionComplementaria.Add(Employer);
}
reader.Close();
}
connection.Close();
}
}
catch (Exception exc)
{
Log.EscribeLog("Error: EslabonDA.ObtenerInformacionComplementaria - " + exc.Message);
}
return(InformacionComplementaria);
}
internal DataTable TraerDataTable(string odbc, IDbConnection con)
{
string TipoBase = archivos.nombremotorbase();
if (TipoBase == "Mysql")
{
MySqlCommand comando = new MySqlCommand();
comando.CommandType = CommandType.Text;
comando.CommandText = odbc;
comando.Connection = con as MySqlConnection;
MySqlDataReader reader = comando.ExecuteReader();
DataTable Tabla = new DataTable();
Tabla.Load(reader);
return(Tabla);
}
else
{
if (TipoBase == "sql")
{
SqlCommand comando = new SqlCommand();
comando.CommandType = CommandType.Text;
comando.CommandText = odbc;
comando.Connection = con as SqlConnection;
SqlDataReader reader = comando.ExecuteReader();
DataTable Tabla = new DataTable();
Tabla.Load(reader);
return(Tabla);
}
else
{
System.Data.Odbc.OdbcCommand comando = new System.Data.Odbc.OdbcCommand();
comando.CommandType = CommandType.Text;
comando.CommandText = odbc;
comando.Connection = con as System.Data.Odbc.OdbcConnection;
System.Data.Odbc.OdbcDataReader reader = comando.ExecuteReader();
DataTable Tabla = new DataTable();
Tabla.Load(reader);
return(Tabla);
}
}
}
// SQLGetRows
public List <string[]> SQLGetRows(string command)
{
List <string[]> rows = new List <string[]>();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = command;
sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
string[] row = new string[sqlDataReader.FieldCount];
for (int i = 0; i < row.Length; i++)
{
row[i] = sqlDataReader[i].ToString();
}
rows.Add(row);
}
sqlDataReader.Close();
sqlDataReader = null;
return(rows);
}
public DataTable Execute(string Command)
{
DataTable dt = null;
if (Conn != null)
{
try
{
Conn.Open();
dt = new DataTable();
System.Data.Odbc.OdbcCommand oCmd = Conn.CreateCommand();
oCmd.CommandText = Command;
dt.Load(oCmd.ExecuteReader());
Conn.Close();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
return(dt);
}
/// <summary>
/// SELECT DISTINCT descripcion AS Nomina FROM nominas
/// </summary>
/// <returns></returns>
public List <Catalogo> CatalogoNomina()
{
List <Catalogo> Nomina = new List <Catalogo>();
try
{
string QueryString = @"SELECT DISTINCT descripcion AS Nomina FROM nominas";
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
{
command.Connection = connection;
connection.Open();
int i = 1;
using (OdbcDataReader reader = command.ExecuteReader())
{
Catalogo modelo;
while (reader.Read())
{
modelo = new Catalogo();
modelo.id = i;
modelo.Descripcion = reader.GetString(0);
Nomina.Add(modelo);
i++;
}
modelo = new Catalogo();
modelo.id = 1000;
modelo.Descripcion = "TODOS";
Nomina.Add(modelo);
}
}
}
catch (Exception ex)
{
Log.EscribeLog("Error: ElsabonDA.CatalogoNomina - " + ex.Message);
}
return(Nomina);
}
private void btnViewNotes_Click(object sender, EventArgs e)
{
if (DataGridView1.SelectedCells[13].Value.ToString() == false.ToString())
{
MessageBox.Show("No Notes to view", "TeleRad - Client", MessageBoxButtons.OK);
return;
}
Myconnectionclass CN = new Myconnectionclass();
OdbcCommand Cmd;
OdbcDataReader Rs1;
CN.OpenConnection();
Cmd = new System.Data.Odbc.OdbcCommand("SELECT * FROM Reports WHERE StudyUID='" + DataGridView1.SelectedCells[5].Value.ToString() + "'", CN.DBConnection);
Rs1 = Cmd.ExecuteReader();
Rs1.Read();
if (Convert.ToBoolean(Rs1["IsNotes"]) == true)
{
MessageBox.Show("Notes sent: " + Rs1["Notes"].ToString(), "TeleRad - Client", MessageBoxButtons.OK);
}
Rs1.Close();
Cmd.Dispose();
CN.closeconnection();
}
/// <summary>
/// Permite obtener la lista de permisos, incapacidades y vacaciones solicitadas durante el periodo que se ingresa como parámetro
/// </summary>
/// <param name="FechaInicio"></param>
/// <param name="FechaFin"></param>
/// <returns></returns>
public List <DiasJustificados> ObtenerDiasJustificados(DateTime FechaInicio, DateTime FechaFin)
{
List <DiasJustificados> Lista = new List <DiasJustificados>();
FechaInicio = FechaInicio.AddMonths(-2); //Va y verifica si existen días de meses pasados
FechaFin = FechaFin.AddDays(1);
try
{
string QueryString = @" DECLARE @FECHA_INICIO DATETIME
DECLARE @FECHA_FIN DATETIME
SET @FECHA_INICIO = '" + FechaInicio.ToString("yyyyMMdd") + @"'
SET @FECHA_FIN = '" + FechaFin.ToString("yyyyMMdd") + @"'
SELECT A.ID, A.FECHA_DESDE, DATEDIFF(day, A.FECHA_DESDE, A.FECHA_HASTA) + 1 AS C_DIAS, A.CONCEPTO FROM (
SELECT r.id AS ID,
CONVERT(DATETIME, dbo.ScalarSplit(r.referencia, ' ', 1)) AS FECHA_DESDE,
CONVERT(DATETIME, dbo.ScalarSplit(r.referencia, ' ', 2)) AS FECHA_HASTA,
d.descripcion AS CONCEPTO
FROM recibos r INNER JOIN dips d on d.dip = r.dip
WHERE r.dip IN ('4090', '7470', '4070')
AND r.fecha_registro >= @FECHA_INICIO
UNION
SELECT c.id AS ID,
CONVERT(DATETIME, dbo.ScalarSplit(c.referencia, ' ', 1)) AS FECHA_DESDE,
CONVERT(DATETIME, dbo.ScalarSplit(c.referencia, ' ', 2)) AS FECHA_HASTA,
d.descripcion AS CONCEPTO
FROM captura c INNER JOIN dips d on d.dip = c.dip
WHERE c.dip IN ('4090', '7470', '4070')
AND c.fecha_registro >= @FECHA_INICIO ) AS A
WHERE (A.FECHA_DESDE BETWEEN @FECHA_INICIO AND @FECHA_FIN)
UNION
SELECT id AS ID,
fecha_incapacidad_desde AS FECHA_DESDE,
dias_incapacidad AS C_DIAS,
'INCAPACIDAD' AS CONCEPTO
FROM incapacidades
WHERE (fecha_incapacidad_desde BETWEEN @FECHA_INICIO AND @FECHA_FIN)
UNION
SELECT id AS ID,
fecha_inicio AS FECHA_DESDE,
dias_descanso AS C_DIAS,
'VACACIONES' AS CONCEPTO
FROM vacaciones_empleado
WHERE (fecha_inicio BETWEEN @FECHA_INICIO AND @FECHA_FIN)";
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
DiasJustificados Modelo;
while (reader.Read())
{
Modelo = new DiasJustificados();
Modelo.EmpleadoId = reader.GetInt32(0);
Modelo.FechaDesde = reader.GetDateTime(1);
Modelo.NumeroDias = reader.GetInt32(2);
Modelo.Concepto = reader.GetString(3);
Lista.Add(Modelo);
}
reader.Close();
reader.Dispose();
}
connection.Close();
connection.Dispose();
}
}
catch (Exception ex)
{
Log.EscribeLog("Error: ElsabonDA.ObtenerDiasJustificados - " + ex.Message);
}
return(Lista);
}
public Int16 ExecuteNonQuery_DR(string sQuery, OdbcParameter[] param)
{
string sFuncName = "ExecuteNonQuery_DR";
Int16 iResult = 0;
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Starting Function", sFuncName);
}
string sConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ToString();
System.Data.Odbc.OdbcConnection oCon = new System.Data.Odbc.OdbcConnection(sConstr);
System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand();
DbDataReader oDr;
try
{
oCon.Open();
oCmd.CommandType = CommandType.Text;
oCmd.CommandText = sQuery;
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("SQL Query : " + sQuery, sFuncName);
}
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Before adding Parameters", sFuncName);
}
foreach (var item in param)
{
oCmd.Parameters.Add(item);
}
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("After adding parameters", sFuncName);
}
oCmd.Connection = oCon;
oCmd.CommandTimeout = 120;
oDr = oCmd.ExecuteReader();
if (oDr.Read() && oDr.GetValue(0) != DBNull.Value)
{
iResult = Convert.ToInt16(oDr[0]);
}
else
{
iResult = 0;
}
oCon.Close();
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Completed with SUCCESS", sFuncName);
}
}
catch (Exception ex)
{
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Completed with ERROR", sFuncName);
}
oCon.Dispose();
throw new Exception(ex.Message);
}
return(iResult);
}
public bool ExportToTxt()
{
bool flag;
try
{
string str = string.Concat("SELECT COUNT(*) from ", this.TableName);
System.Data.Odbc.OdbcCommand sqlCommand = new System.Data.Odbc.OdbcCommand(str, this.conn);
OdbcDataReader sqlDataReader = sqlCommand.ExecuteReader();
sqlDataReader.Read();
int num = Convert.ToInt32(sqlDataReader[0].ToString());
string str1 = sqlDataReader[0].ToString();
sqlDataReader.Close();
if (!(this.SqlCommand != ""))
{
sqlCommand.CommandText = string.Concat("SELECT * from ", this.TableName);
}
else
{
sqlCommand.CommandText = this.SqlCommand;
}
sqlDataReader = sqlCommand.ExecuteReader();
int fieldCount = sqlDataReader.FieldCount;
decimal num1 = new decimal(0);
StreamWriter streamWriter = new StreamWriter(string.Concat(this.filename, ".Txt"));
StringBuilder stringBuilder = new StringBuilder();
while (sqlDataReader.Read())
{
decimal num2 = num1;
num1 = num2++;
this.updateDisplay(num2, num, str1);
for (int i = 0; i < fieldCount; i++)
{
if (!(sqlDataReader[i].GetType().ToString() == "System.Byte[]"))
{
stringBuilder.Append(sqlDataReader[i].ToString());
}
else
{
byte[] item = (byte[])sqlDataReader[i];
stringBuilder.Append(DataWriter2.BytesToHex(item));
}
if ((i >= fieldCount - 1 ? false : i >= 0))
{
stringBuilder.Append("|");
}
}
stringBuilder.Replace(Environment.NewLine, string.Empty);
streamWriter.WriteLine(stringBuilder.ToString());
stringBuilder.Length = 0;
}
streamWriter.Close();
streamWriter.Dispose();
stringBuilder = null;
sqlCommand.Dispose();
sqlDataReader.Dispose();
flag = true;
}
catch (OdbcException odbcException1)
{
OdbcException odbcException = odbcException1;
this.SimpleError = "OdbcError";
this.Error = odbcException.Message;
flag = false;
}
catch (IOException oException1)
{
IOException oException = oException1;
this.SimpleError = "FileError";
this.Error = oException.Message;
flag = false;
}
catch (UnauthorizedAccessException unauthorizedAccessException1)
{
UnauthorizedAccessException unauthorizedAccessException = unauthorizedAccessException1;
this.SimpleError = "PermissionsError";
this.Error = unauthorizedAccessException.Message;
flag = false;
}
return(flag);
}
internal DataTable TraerDataTableSp(string nombre, IDbConnection con, params System.Object[] parametros)
{
string TipoBase = archivos.nombremotorbase();
if (TipoBase == "Mysql")
{
MySqlCommand comando = new MySqlCommand();
comando.CommandType = CommandType.StoredProcedure;
comando.CommandText = nombre;
comando.Connection = con as MySqlConnection;
MySqlCommandBuilder.DeriveParameters(comando);
int limite = comando.Parameters.Count;
try
{
for (int i = 0; i < limite; i++)
{
comando.Parameters[i].Value = parametros[i];
}
}
catch (Exception ex)
{
throw ex;
}
DataTable Tabla = new DataTable();
Tabla.Load(comando.ExecuteReader());
return(Tabla);
}
else
{
if (TipoBase == "sql")
{
SqlCommand comando = new SqlCommand();
comando.CommandType = CommandType.StoredProcedure;
comando.CommandText = nombre;
comando.Connection = con as SqlConnection;
SqlCommandBuilder.DeriveParameters(comando);
int limite = comando.Parameters.Count;
try
{
for (int i = 1; i < limite; i++)
{
comando.Parameters[i].Value = parametros[i - 1];
}
}
catch (Exception ex)
{
throw ex;
}
DataTable Tabla = new DataTable();
Tabla.Load(comando.ExecuteReader());
return(Tabla);
}
else
{
System.Data.Odbc.OdbcCommand comando = new System.Data.Odbc.OdbcCommand();
comando.CommandType = CommandType.StoredProcedure;
comando.CommandText = nombre;
comando.Connection = con as System.Data.Odbc.OdbcConnection;
System.Data.Odbc.OdbcCommandBuilder.DeriveParameters(comando);
int limite = comando.Parameters.Count;
try
{
for (int i = 1; i < limite; i++)
{
comando.Parameters[i].Value = parametros[i - 1];
}
}
catch (Exception ex)
{
throw ex;
}
DataTable Tabla = new DataTable();
Tabla.Load(comando.ExecuteReader());
return(Tabla);
}
}
}
