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