// Selects the place of the universities and inserts the active public void Activo_Data(MySql.Data.MySqlClient.MySqlConnection conn, String ID, String Name, String Place, String Quantity) { String Sede_ID; //variables for queries and insert string Query = ""; String DB_Insert; // Variable that take a query string Query = "SELECT `Sede`.`Id_Sede` FROM `mydb`.`Sede` WHERE Ciudad = '" + Place + "'; ";//get id by place //ID by place var ID_CMD = new MySql.Data.MySqlClient.MySqlCommand(Query, conn);//ejecuta query var Read = ID_CMD.ExecuteReader(); Read.Read(); Sede_ID = Read["Id_Sede"].ToString();//ID SEDE Read.Close(); //Insert in db DB_Insert = "INSERT INTO `mydb`.`Activo` (`Id_Activo`, `Id_Sede_Activo`, `Nombre`, `Cantidad`) VALUES ( " + Convert.ToInt32(ID) + "," + Convert.ToInt32(Sede_ID) + ",'" + Name + "'," + Convert.ToInt32(Quantity) + ");"; var Insert = new MySql.Data.MySqlClient.MySqlCommand(DB_Insert, conn); //Insert comm var executer = Insert.ExecuteNonQuery(); //execute non query }
public bool updateInputData(DateTime Id, InputData inputDataToSave, String DeviceId) { InputData myInputData = new InputData(); MySql.Data.MySqlClient.MySqlDataReader mySqlReader = null; String sqlString = "SELECT * FROM InputData WHERE Date = '" + Id.ToString("yyyy-MM-dd") + "' AND DeviceId='" + inputDataToSave.DeviceId + "'"; MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn); mySqlReader = cmd.ExecuteReader(); if (mySqlReader.Read()) { mySqlReader.Close(); sqlString = "UPDATE InputData Set Date='" + inputDataToSave.Date.ToString("yyyy-MM-dd") + "', ActiveMinutesReached= " + inputDataToSave.ActiveMinutesReached + ", StepsReached=" + inputDataToSave.StepsReached + ", DeviceId='" + inputDataToSave.DeviceId + "' WHERE Date = '" + Id.ToString("yyyy-MM-dd") + "' AND DeviceId='" + inputDataToSave.DeviceId + "'"; cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn); cmd.ExecuteNonQuery(); return(true); } else { mySqlReader.Close(); sqlString = "INSERT INTO InputData (Date, ActiveMinutesReached, StepsReached, DeviceId) VALUES ('" + inputDataToSave.Date.ToString("yyyy-MM-dd") + "'," + inputDataToSave.ActiveMinutesReached + "," + inputDataToSave.StepsReached + ",'" + inputDataToSave.DeviceId + "')"; cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn); cmd.ExecuteNonQuery(); return(true); } }
public static void StvoriBridoveIzBaze() { try { DatabaseConnection.CheckSqlConnection(); MySqlCommand sqlCmd = DatabaseConnection.sqlCmd; sqlCmd.Connection = DatabaseConnection.sqlConn; sqlCmd.CommandText = "SELECT Pocetni.Naziv AS PocetniCvor, " + "Zavrsni.Naziv AS ZavrsniCvor, " + "Bridovi.Naziv AS NazivBrida, " + "Bridovi.Vrijeme " + "FROM Bridovi " + "LEFT OUTER JOIN Cvorovi AS Pocetni ON Bridovi.Cvor1 = Pocetni.ID " + "LEFT OUTER JOIN Cvorovi AS Zavrsni ON Bridovi.Cvor2 = Zavrsni.ID"; MySqlDataReader sqlReader = sqlCmd.ExecuteReader(); while (sqlReader.Read()) { string početni = sqlReader["PocetniCvor"].ToString(), završni = sqlReader["ZavrsniCvor"].ToString(), naziv = sqlReader["NazivBrida"].ToString(); double vrijeme = (double)sqlReader["Vrijeme"]; Plan.DodajBrid(naziv, početni, završni, vrijeme); // možda provjeriti rezultat izvršavanja? } sqlReader.Close(); } catch (Exception ex) { MessageBox.Show("Greška pri čitanju iz baze! " + ex.Message, "Greška", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public IEnumerable <Specialty> GetAllSpecialties() { var specialties = new List <Specialty>(); using (var con = new MySql.Data.MySqlClient.MySqlConnection(connectionString)) { var cmd = new MySql.Data.MySqlClient.MySqlCommand ("SELECT SpecialtyID, SkillID, Name, Description FROM Specialties", con); cmd.CommandType = CommandType.Text; con.Open(); var rdrSkills = cmd.ExecuteReader(); while (rdrSkills.Read()) { var specialty = new Specialty(); specialty.SpecialtyID = Convert.ToInt32(rdrSkills["SpecialtyID"]); specialty.SkillID = rdrSkills.GetInt32("SkillID"); specialty.Name = rdrSkills["Name"].ToString(); specialty.Description = rdrSkills["Description"].ToString(); specialty.Bonuses = GetBonuses(specialty.SpecialtyID).ToList(); specialties.Add(specialty); } con.Close(); } return(specialties); }
public IEnumerable <Skill> GetAllSkills() { var skills = new List <Skill>(); using (var con = new MySql.Data.MySqlClient.MySqlConnection(connectionString)) { var cmd = new MySql.Data.MySqlClient.MySqlCommand ("SELECT SkillID, Name, Description FROM Skills", con); cmd.CommandType = CommandType.Text; con.Open(); var rdrSkills = cmd.ExecuteReader(); while (rdrSkills.Read()) { var skill = new Skill(); skill.ID = Convert.ToInt32(rdrSkills["SkillID"]); skill.Name = rdrSkills["Name"].ToString(); skill.Description = rdrSkills["Description"].ToString(); skill.Specialties = GetSpecialties(skill.ID); skills.Add(skill); } con.Close(); } return(skills); }
public static string FetchePassword() { string passwordStr = string.Empty; MySql.Data.MySqlClient.MySqlConnection msqlConnection = null; msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False"); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlConnection.Open(); msqlCommand.CommandText = "Select password from sptinfo;"; MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); msqlReader.Read(); passwordStr = msqlReader.GetString("password"); } catch (Exception er) { //Assert//.Show(er.Message); } finally { //always close the connection msqlConnection.Close(); } return passwordStr; }
public Specialty GetSpecialty(int specialtyID) { Specialty specialty = null; using (var con = new MySql.Data.MySqlClient.MySqlConnection(connectionString)) { var cmd = new MySql.Data.MySqlClient.MySqlCommand ("SELECT SkillID, Name, Description FROM Specialties WHERE SpecialtyID = @SpecialtyID", con); cmd.Parameters.AddWithValue("@SpecialtyID", specialtyID); cmd.CommandType = CommandType.Text; con.Open(); var rdrSkills = cmd.ExecuteReader(); while (rdrSkills.Read()) { specialty = new Specialty(); specialty.ID = specialtyID; specialty.SkillID = rdrSkills.GetInt32("SkillID"); specialty.Name = rdrSkills["Name"].ToString(); specialty.Description = rdrSkills["Description"].ToString(); specialty.Bonuses = GetBonuses(specialty.ID).ToList(); } con.Close(); } return(specialty); }
public Route ReadRoute(long RouteToReadID) { string readRouteSqlQuery = "SELECT * FROM Routes WHERE Route_ID = " + RouteToReadID.ToString(); var sqlReadRouteCommand = new MySql.Data.MySqlClient.MySqlCommand(readRouteSqlQuery, dbConnection); using (var mySQLReader = sqlReadRouteCommand.ExecuteReader()) { if (mySQLReader.Read()) { var RouteToGet = new Route { ID = mySQLReader.GetInt32(0), Cost = mySQLReader.GetFloat(1), Time = mySQLReader.GetFloat(2), StartPointID = mySQLReader.GetInt32(3), EndPointID = mySQLReader.GetInt32(4), }; return(RouteToGet); } else { return(null); } } }
public List <Route> ReadRoutes() { string readRoutesSqlQuery = "SELECT * FROM Routes"; var sqlReadRoutesCommand = new MySql.Data.MySqlClient.MySqlCommand(readRoutesSqlQuery, dbConnection); var RouteList = new List <Route>(); using (var mySQLReader = sqlReadRoutesCommand.ExecuteReader()) { while (mySQLReader.Read()) { Route RouteToGet = new Route { ID = mySQLReader.GetInt32(0), Cost = mySQLReader.GetFloat(1), Time = mySQLReader.GetFloat(2), StartPointID = mySQLReader.GetInt32(3), EndPointID = mySQLReader.GetInt32(4), }; RouteList.Add(RouteToGet); } return(RouteList); } }
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { String connString = System.Configuration.ConfigurationManager.ConnectionStrings["webAppConnString"].ToString(); conn = new MySql.Data.MySqlClient.MySqlConnection(connString); conn.Open(); queryStr = ""; queryStr = "SELECT name FROM genre"; cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn) { Connection = conn }; reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) // using read() method to read all rows one-by-one { output.Append("<div>"); output.Append(reader.GetString(0)); output.Append("</div>"); } } PlaceHolder1.Controls.Add(new Literal { Text = output.ToString() }); reader.Close(); conn.Close(); } }
public bool findUser(String username, String password) { bool flag = false; msqlConnection = new MySql.Data.MySqlClient.MySqlConnection(constr); //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); //define the connection used by the command object msqlCommand.Connection = this.msqlConnection; //define the command text msqlCommand.CommandText = "SELECT username FROM user WHERE username='******' AND password='******';"; Console.WriteLine(msqlCommand.CommandText); try { //open the connection this.msqlConnection.Open(); //use a DataReader to process each record msqlCommand.ExecuteNonQuery(); MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); if (msqlReader.HasRows) { flag = true; } } catch (Exception er) { //do something with the exception } finally { //always close the connection this.msqlConnection.Close(); } return(flag); }
private void btnEjecutarSentencia_Click(object sender, EventArgs e) { MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(txtSentence.Text, connection); try { MySql.Data.MySqlClient.MySqlDataReader rdr = cmd.ExecuteReader(); /*while (rdr.Read()) * { * lstResultado.Items.Add(rdr["NoCuenta"].ToString()); * } * rdr.Close();*/ while (rdr.Read()) { for (int i = 0; i < rdr.FieldCount; i++) { string c = rdr.GetName(i); lstResultado.Items.Add(c + ": " + rdr[c] + Environment.NewLine); } lstResultado.Items.Add(Environment.NewLine); } rdr.Close(); } catch (MySql.Data.MySqlClient.MySqlException ex) { MessageBox.Show("no ha seleccionado ninguna BD"); } }
// Devuelve la lista entera: internal List <Receta> Retrieve() { // Conexión a sql y consulta: MySql.Data.MySqlClient.MySqlConnection conexion = Connect(); MySql.Data.MySqlClient.MySqlCommand cmd = conexion.CreateCommand(); cmd.CommandText = " select * from recetas"; try { conexion.Open(); MySql.Data.MySqlClient.MySqlDataReader res = cmd.ExecuteReader(); // Creamos objeto mercado y lista: Receta receta = null; List <Receta> recetas = new List <Receta>(); while (res.Read()) { // Introducimos los datos y añadimos a la lista todo: receta = new Receta(res.GetInt32(0), res.GetString(1), res.GetString(2), res.GetInt32(3)); recetas.Add(receta); } // Cerramos la conexión a la bd: conexion.Close(); return(recetas); } catch (MySql.Data.MySqlClient.MySqlException e) { Debug.WriteLine("Se ha producido un error de conexión."); return(null); } }
public CityForeCast[] GetForecastFromDatabase() { //SqlConnection connection = new SqlConnection(@"Data Source=(LocalDb)\MSSQLLocalDb;Initial Catalog=weatherForecast;Integrated Security=True;Pooling=False"); MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(@"Server=eu-cdbr-azure-west-c.cloudapp.net;Database=BDMeteo;Uid=b95badd8e1dbad;Pwd=bde4c7b6;Pooling=True"); connection.Open(); MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM tablemeteo", connection); MySql.Data.MySqlClient.MySqlDataReader reader = cmd.ExecuteReader(); List<CityForeCast> forecasts = new List<CityForeCast>(); while (reader.Read()) { CityForeCast forecast = new CityForeCast(); forecast.City = (string)reader["City"]; forecast.description = (string)reader["Description"]; forecast.MaxTemp = (decimal)reader["Temperature"]; forecasts.Add(forecast); } reader.Close(); // Fermer le reader avant de fermer la connection connection.Close(); return forecasts.ToArray(); //var ctx = new weatherForecastEntities(); //var forecast = ctx.Tables.Select(f => new CityForeCast() //{ // City = f.City, // description = f.Description, // MaxTemp = (decimal)f.Temperature //}); //return forecast; }
protected void Page_Load(object sender, EventArgs e) { //UnobtrusiveValidationMode = UnobtrusiveValidationMode.None; //opens connection, queries all games Globals.conn.Open(); gameList = new List <Game>(); string query = "SELECT * FROM games;"; var cmd = new MySql.Data.MySqlClient.MySqlCommand(query, Globals.conn); var reader = cmd.ExecuteReader(); //reads every row, makes new object, adds to list while (reader.Read()) { Game tmpGame = new Game(); tmpGame.Id = reader["ID"].ToString(); tmpGame.Name = reader["Title"].ToString(); tmpGame.Price = Convert.ToDouble(reader["Price"]); tmpGame.ImgPath = reader["ImagePath"].ToString(); tmpGame.Description = reader["Description"].ToString(); tmpGame.Platform = reader["Platform"].ToString(); tmpGame.Genre = reader["Genre"].ToString(); gameList.Add(tmpGame); } Globals.conn.Close(); //END OF SEARCH BUTTON FUNCTIONALITY }
private List <Route> readRoutesConnectedToPoint(long PointID) { string readRoutesSqlQuery = "SELECT * FROM Routes WHERE START_POINT_ID='" + PointID + "' OR END_POINT_ID='" + PointID + "'"; var sqlReadRoutesCommand = new MySql.Data.MySqlClient.MySqlCommand(readRoutesSqlQuery, dbConnection); var RouteList = new List <Route>(); using (var mySQLReader = sqlReadRoutesCommand.ExecuteReader()) { while (mySQLReader.Read()) { Route RouteToGet = new Route { ID = mySQLReader.GetInt32(0), Cost = mySQLReader.GetFloat(1), Time = mySQLReader.GetFloat(2), StartPointID = mySQLReader.GetInt32(3), EndPointID = mySQLReader.GetInt32(4), }; RouteList.Add(RouteToGet); } return(RouteList); } }
public bool deleteMarca(int ID) { Marca m = new Marca(); MySql.Data.MySqlClient.MySqlDataReader mySqlDataReader = null; String sqlString = sqlString = "SELECT * FROM marca WHERE idMarca = " + ID.ToString(); MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn); mySqlDataReader = cmd.ExecuteReader(); if (mySqlDataReader.Read()) { mySqlDataReader.Close(); sqlString = "DELETE FROM marca WHERE idMarca = " + ID.ToString(); cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn); cmd.ExecuteNonQuery(); return(true); } else { return(false); } }
// TEST. // App_Start/WebApiConfig.cs // GET api/db public string Get() { string server = "127.0.0.1"; string db = "test"; string uId = "user"; string pwd = "user"; string connStr = string.Format("Server={0};Database={1};Uid={2};Pwd={3};Charset=utf8", server, db, uId, pwd); MySql.Data.MySqlClient.MySqlConnection conn; conn = new MySql.Data.MySqlClient.MySqlConnection(connStr); string result = ""; try { conn.Open(); string sql = "SELECT * FROM testT"; MySql.Data.MySqlClient.MySqlCommand mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(sql, conn); MySql.Data.MySqlClient.MySqlCommand cmd = mySqlCommand; MySql.Data.MySqlClient.MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { result += reader[0] + " " + reader[1]; } } catch (Exception e) { throw e; } finally { conn.Close(); } return("result : " + result); }
public Skill GetSkill(string name) { var skills = new List <Skill>(); using (var con = new MySql.Data.MySqlClient.MySqlConnection(connectionString)) { var cmd = new MySql.Data.MySqlClient.MySqlCommand ("SELECT SkillID, Name, Description FROM Skills WHERE Name = @Name", con); cmd.Parameters.AddWithValue("@Name", name); cmd.CommandType = CommandType.Text; con.Open(); var rdrSkills = cmd.ExecuteReader(); var skill = new Skill(); skill.ID = Convert.ToInt32(rdrSkills["SkillID"]); skill.Name = rdrSkills["Name"].ToString(); skill.Description = rdrSkills["Description"].ToString(); skill.Specialties = GetSpecialties(skill.ID); con.Close(); return(skill); } }
public static List <Rondas> GetRondas() { List <Rondas> _rondas = new List <Rondas>(); using (MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection(GetStringDb())) { con.Open(); using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM Rondas", con)) { using (MySql.Data.MySqlClient.MySqlDataReader read = cmd.ExecuteReader()) { while (read.Read()) { var Rondas = new Rondas(); Rondas.cpf_ronda = Convert.ToInt64(read["cpf_ronda"]); Rondas.datahora = Convert.ToDateTime(read["datahora"]); Rondas.id = Convert.ToInt32(read["id"]); Rondas.latitude = Convert.ToSingle(read["latitude"]); Rondas.longitude = Convert.ToSingle(read["longitude"]); _rondas.Add(Rondas); } } return(_rondas); } } }
public IEnumerable <Attr> GetAllAttributes(bool fill = true) { var attributes = new List <Attr>(); using (var con = new MySql.Data.MySqlClient.MySqlConnection(connectionString)) { var cmd = new MySql.Data.MySqlClient.MySqlCommand ("SELECT AttributeID, Name, Description FROM Attributes Order By AttributeID", con); cmd.CommandType = CommandType.Text; con.Open(); var rdrAttributes = cmd.ExecuteReader(); while (rdrAttributes.Read()) { var attribute = new Attr(); attribute.ID = Convert.ToInt32(rdrAttributes["AttributeID"]); attribute.Name = rdrAttributes["Name"].ToString(); attribute.Description = rdrAttributes["Description"].ToString(); attribute.Skills = GetSkills(attribute.ID).ToList(); attributes.Add(attribute); } con.Close(); } return(attributes); }
public Plant getplant(long ID) { Plant p = new Plant(); MySql.Data.MySqlClient.MySqlDataReader mySQLReader = null; string sqlString = "SELECT * From tblplants WHERE ID = " + ID.ToString(); MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn); mySQLReader = cmd.ExecuteReader(); if (mySQLReader.Read()) { p.ID = mySQLReader.GetInt32(0); p.Name = mySQLReader.GetString(1); p.SoilMoistureMin = mySQLReader.GetInt32(2); p.SoilMoistureMax = mySQLReader.GetInt32(3); p.SoilMoistureNow = mySQLReader.GetInt32(4); p.Category = mySQLReader.GetString(5); p.SunlightMin = mySQLReader.GetInt32(6); p.SunlightMax = mySQLReader.GetInt32(7); p.SunlightNow = mySQLReader.GetInt32(8); p.ID_Type = mySQLReader.GetInt32(9); p.StartDate = mySQLReader.GetDateTime(10); p.Updated = mySQLReader.GetDateTime(11); p.url = mySQLReader.GetString(12); return(p); } else { return(null); } }
public IEnumerable <Bonus> GetBonuses(int specialtyID) { var bonuses = new List <Bonus>(); using (var con = new MySql.Data.MySqlClient.MySqlConnection(connectionString)) { //need to grab bonuses somehow; var cmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT BonusID, Type, Value FROM SpecialtyValues WHERE SpecialtyID = @spID" , con ); cmd.Parameters.AddWithValue("@spID", specialtyID); cmd.CommandType = CommandType.Text; con.Open(); var rdrBonus = cmd.ExecuteReader(); while (rdrBonus.Read()) { var bonus = new Bonus(); bonus.ID = Convert.ToInt32(rdrBonus["BonusID"]); bonus.Value = Convert.ToInt32(rdrBonus["Value"]); if (Enum.TryParse(typeof(Mod), rdrBonus["Type"].ToString(), true, out var t)) { bonus.mod = (Mod)t; } bonuses.Add(bonus); } con.Close(); } return(bonuses); }
public bool deletePlant(long ID) { Plant p = new Plant(); MySql.Data.MySqlClient.MySqlDataReader mySQLReader = null; string sqlString = "SELECT * From tblplants WHERE ID = " + ID.ToString(); MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn); mySQLReader = cmd.ExecuteReader(); if (mySQLReader.Read()) { mySQLReader.Close(); sqlString = "DELETE From tblplants WHERE ID = " + ID.ToString(); cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn); cmd.ExecuteNonQuery(); return(true); } else { return(false); } }
public IEnumerable <Skill> GetSkills(int attributeID, bool fill = true) { var skills = new List <Skill>(); using (var con = new MySql.Data.MySqlClient.MySqlConnection(connectionString)) { var cmd = new MySql.Data.MySqlClient.MySqlCommand ("SELECT SkillID, Name, Description FROM Skills WHERE AttributeID = @attributeID Order By AttributeID", con); cmd.Parameters.AddWithValue("@attributeID", attributeID); cmd.CommandType = CommandType.Text; con.Open(); var rdrSkills = cmd.ExecuteReader(); while (rdrSkills.Read()) { var skill = new Skill(); skill.ID = Convert.ToInt32(rdrSkills["SkillID"]); skill.Name = rdrSkills["Name"].ToString(); skill.Description = rdrSkills["Description"].ToString(); if (fill) { skill.Specialties = GetSpecialties(skill.ID); } skills.Add(skill); } con.Close(); } return(skills); }
public string[] GetTeamNames() { List <string> teamNames = new List <string>(); string query = "SELECT `Name` FROM `Teams` ORDER BY `Name`"; IDbCommand command = new MySql.Data.MySqlClient.MySqlCommand(query); command.Connection = connection; IDataReader reader = command.ExecuteReader(); while (reader.Read()) { try { teamNames.Add(reader.GetString(0)); } catch { Console.WriteLine("Failed to read data"); } } reader.Close(); return(teamNames.ToArray()); }
public static void StvoriČvoroveIzBaze() { try { DatabaseConnection.CheckSqlConnection(); MySqlCommand sqlCmd = DatabaseConnection.sqlCmd; sqlCmd.Connection = DatabaseConnection.sqlConn; sqlCmd.CommandText = "SELECT Naziv, X, Y FROM Cvorovi"; MySqlDataReader sqlReader = sqlCmd.ExecuteReader(); while (sqlReader.Read()) { string naziv = sqlReader["Naziv"].ToString(); double x = (double)sqlReader["X"], y = (double)sqlReader["Y"]; Plan.DodajČvor(x, y, naziv); } sqlReader.Close(); } catch { MessageBox.Show("Greška pri čitanju iz baze!", "Greška", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
// Si se actuliza con exito retornara un TRUE y si no un FALSE public bool updateMarca(int ID, Marca marcaToSave) { MySql.Data.MySqlClient.MySqlDataReader mySqlDataReader = null; String sqlString = sqlString = "SELECT * FROM marca WHERE idMarca = " + ID.ToString(); MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn); mySqlDataReader = cmd.ExecuteReader(); if (mySqlDataReader.Read()) { mySqlDataReader.Close(); //UPDATE mantenimiento.marca SET nombreMarca = 'asd', // descripcion = 'asdas' , email = 'asd', telefono = 'asda' , website = 'adsas' , direccion = 'adsasd' WHERE idMarca = 8 sqlString = "UPDATE mantenimiento.marca SET nombreMarca = '" + marcaToSave.nombreMarca + "'," + " descripcion = '" + marcaToSave.descripcion + "', email = '" + marcaToSave.email + "', telefono = '" + marcaToSave.telefono + "', website = '" + marcaToSave.website + "', direccion = '" + marcaToSave.direccion + "' WHERE idMarca = " + ID.ToString(); cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn); cmd.ExecuteNonQuery(); return(true); } else { return(false); } }
public Plato ObtenerPlato(int idioma, int id) { MySql.Data.MySqlClient.MySqlDataReader mySQLReader; String sqlString = "CALL Select_plato(" + idioma.ToString() + ", " + id.ToString() + ");"; MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conexion); //Si hay un error al hacer la consulta que devuelva null try { mySQLReader = cmd.ExecuteReader(); if (mySQLReader.Read()) { return(LeerPlato(mySQLReader)); } else { conexion.Close(); return(null); } } catch (Exception e) { conexion.Close(); return(null); } finally { conexion.Close(); } }
public bool DeletePerson(long ID) { MySql.Data.MySqlClient.MySqlConnection conn; conn = new MySql.Data.MySqlClient.MySqlConnection(); try { conn.ConnectionString = connection.myConnectionString; conn.Open(); Person p = new Person(); MySql.Data.MySqlClient.MySqlDataReader mySqlDataReader = null; String sqlString = "DELETE FROM tblpersonnel WHERE ID = " + ID.ToString(); MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn); mySqlDataReader = cmd.ExecuteReader(); if (mySqlDataReader.Read()) { mySqlDataReader.Close(); sqlString = "DELETE FROM tblpersonnel WHERE ID = " + ID.ToString(); cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn); cmd.ExecuteNonQuery(); return(true); } else { return(false); } } catch (MySql.Data.MySqlClient.MySqlException ex) { throw ex; } finally { conn.Close(); } }
public IList SearchActivePartByBarcode(string barcode, bool active) { try { OpenConnection(); MySql.Data.MySqlClient.MySqlCommand aCommand = new MySql.Data.MySqlClient.MySqlCommand(); aCommand.Connection = m_connection; MySql.Data.MySqlClient.MySqlDataReader aReader; aCommand.CommandText = UnitConversion.GetAllByBarcodeSQL(barcode); aReader = aCommand.ExecuteReader(); IList unv = UnitConversion.GetAllStatic(aReader); aReader.Close(); IList result = new ArrayList(); foreach (UnitConversion c in unv) { c.PART = PartRepository.GetByID(aCommand, c.PART.ID); c.CONVERSION_UNIT = PartRepository.GetUnitByID(aCommand, c.CONVERSION_UNIT.ID); c.PART.UNIT_BY_SEARCH = c.CONVERSION_UNIT; c.PART.SELL_PRICE_BY_SEARCH = c.SELL_PRICE; c.PART.COST_PRICE_BY_SEARCH = c.COST_PRICE; result.Add(c.PART); } return(result); } catch (Exception x) { throw new Exception(getErrorMessage(x)); } finally { m_connection.Close(); } }
internal static IList FindPaymentUsingAPDN(MySql.Data.MySqlClient.MySqlCommand cmd, int apDNID) { cmd.CommandText = PaymentItem.GetPaymentItemByAPDN(apDNID); MySql.Data.MySqlClient.MySqlDataReader r = cmd.ExecuteReader(); IList result = PaymentItem.TransformReaderList(r); r.Close(); foreach (PaymentItem i in result) { cmd.CommandText = Payment.GetByIDSQL(i.EVENT_JOURNAL.ID); r = cmd.ExecuteReader(); i.EVENT_JOURNAL = Payment.TransformReader(r); r.Close(); } return(result); }
public static DataTable GetRemoteDataTable(string command, MySql.Data.MySqlClient.MySqlConnection msqConn) { DataTable rdt = new DataTable(); try { msqConn.Open(); MySql.Data.MySqlClient.MySqlCommand uploaderCmd = new MySql.Data.MySqlClient.MySqlCommand(command, msqConn); MySql.Data.MySqlClient.MySqlDataReader onj = uploaderCmd.ExecuteReader(); bool colWasPerf = false; int colIdx = 0; List<object> rowValues = new List<object>(); foreach (System.Data.Common.DbDataRecord ro in onj) { try { // get fields if (rdt.Columns.Count == 0 && !colWasPerf) { for (colIdx = 0; colIdx < ro.FieldCount; colIdx++) rdt.Columns.Add(ro.GetName(colIdx)); colWasPerf = true; } } catch { } try { for (colIdx = 0; colIdx < ro.FieldCount; colIdx++) rowValues.Add(ro[colIdx]); } catch { } try { rdt.Rows.Add(rowValues.ToArray()); rowValues.Clear(); } catch { } } onj.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } if (msqConn.State == System.Data.ConnectionState.Open) msqConn.Close(); return rdt; }
public override Bitmap LoadImage(int image_id) { MySql.Data.MySqlClient.MySqlDataReader myData; MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(); string SQL; byte[] rawData; MemoryStream ms; UInt32 FileSize; Bitmap outImage; SQL = "SELECT image_name, image_size, image_data FROM images WHERE id ="; SQL += image_id.ToString(); try { cmd.Connection = Connection; cmd.CommandText = SQL; myData = cmd.ExecuteReader(); if (!myData.HasRows) throw new Exception("There are no blobs to save"); myData.Read(); FileSize = myData.GetUInt32(myData.GetOrdinal("image_size")); rawData = new byte[FileSize]; myData.GetBytes(myData.GetOrdinal("image_data"), 0, rawData, 0, (Int32)FileSize); ms = new MemoryStream(rawData); outImage = new Bitmap(ms); ms.Close(); ms.Dispose(); myData.Close(); myData.Dispose(); cmd.Dispose(); return outImage; } catch (MySql.Data.MySqlClient.MySqlException ex) { return null; } }
private void ConnectFetchFromSaleslistTable() { //define the connection reference and initialize it msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False"); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); //define the connection used by the command object msqlCommand.Connection = msqlConnection; //open the connection msqlConnection.Open(); TimeSpan diff = (TimeSpan)(endDatePicker.SelectedDate - startDatePicker.SelectedDate); msqlCommand.CommandText = "SELECT * FROM purchaselist where date(purchaselist.datePurchase) >= DATE_SUB( @enddate, INTERVAL @diff DAY);"; msqlCommand.Parameters.AddWithValue("@enddate", endDatePicker.SelectedDate); msqlCommand.Parameters.AddWithValue("@diff", diff.Days); MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); _purchaseDataCollection.Clear(); while (msqlReader.Read()) { PurchaseData purchaseData = new PurchaseData(); purchaseData.vendorId = msqlReader.GetString("vendorId"); purchaseData.vendorName = msqlReader.GetString("vendorName"); purchaseData.datePurchase = msqlReader.GetDateTime("datePurchase"); purchaseData.invoiceNo = msqlReader.GetString("invoiceNo"); purchaseData.payment = msqlReader.GetDouble("payment"); purchaseData.totalAmount = msqlReader.GetDouble("totalAmount"); //purchaseData.serialNo = (_purchaseDataCollection.Count + 1).ToString(); _purchaseDataCollection.Add(purchaseData); } } catch (Exception er) { MessageBox.Show(er.Message); } finally { //always close the connection msqlConnection.Close(); } }
/// <summary> /// Loads XML from local database /// </summary> public void ImportFromMySQL(Database.DatabaseConfig xmlSource, Database.DatabaseConfig destination) { _xmlSource = xmlSource; _destination = destination; XMLImport importer = new XMLImport(); Database.MySQL.Database myDB = new Database.MySQL.Database(_destination); string connString = string.Format(DBConnectString, _xmlSource.ServerIp, _xmlSource.Schema, _xmlSource.Username, _xmlSource.Password); using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connString)) { conn.Open(); using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand()) { cmd.Connection = conn; cmd.CommandText = "SELECT `fishbaseId`, `genus`, `species`, `xmlSummary`, `xmlPointData`, `xmlCommonNames`, `xmlPhotos` FROM `fishbaseraw`;"; using (MySql.Data.MySqlClient.MySqlDataReader reader = cmd.ExecuteReader()) { while(reader.Read()) { Model.FishClass fc = importer.Import( reader.GetInt32("fishbaseId"), reader.GetString("genus"), reader.GetString("species"), reader.GetString("xmlSummary"), reader.GetString("xmlPointData"), reader.GetString("xmlCommonNames"), reader.GetString("xmlPhotos") ); if (fc != null) { try { myDB.CreateFish(fc); } catch (Database.Exceptions.CreatureAlreadyExists ex) { myDB.UpdateFish(fc); } } } } } } }
public static string FetcheId() { string idStr = string.Empty; int returnVal = 0; MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); //define the connection used by the command object msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "Select userid from user;"; MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); msqlReader.Read(); idStr = msqlReader.GetString("userid"); } catch (Exception er) { //Assert//.Show(er.Message); } finally { //always close the connection msqlConnection.Close(); } return idStr; }
protected void LoginButton_Click(object sender, EventArgs e) { String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString(); using (conn = new MySql.Data.MySqlClient.MySqlConnection(connString)) { conn.Open(); queryStr = ""; queryStr = "SELECT * FROM db_9f3dca_webapp.user_registration WHERE username='******' AND password='******' "; cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn); reader = cmd.ExecuteReader(); name = ""; while (reader.HasRows && reader.Read()) { name = reader.GetString(reader.GetOrdinal("username")); } if (reader.HasRows) { Session["uname"] = name; Response.BufferOutput = true; Response.Redirect("loggedIn.aspx", false); } else { LoginError.Text = "Invalid username password combination!"; } reader.Close(); conn.Close(); } }
public static void DeletePhoto(string PhotoToDelete) { MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "DELETE FROM pics WHERE PhotoID=@PhotoIdToDelete"; msqlCommand.Parameters.AddWithValue("@PhotoIdToDelete", PhotoToDelete); MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } }
public static PhotoItem GetPhoto(string PhotoToGet) { MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); PhotoItem thePhoto = null; try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "SELECT FROM pics WHERE PhotoID=@PhotoToGet"; MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { thePhoto = new PhotoItem(); thePhoto.PhotoID = msqlReader.GetInt32("PhotoID"); thePhoto.Description = msqlReader.GetString("Description"); thePhoto.Name = msqlReader.GetString("Name"); thePhoto.UploadedOn = msqlReader.GetDateTime("UploadedOn"); thePhoto.FileSize = msqlReader.GetInt32("FileSize"); thePhoto.ImgFile = new byte[thePhoto.FileSize]; msqlReader.GetBytes(msqlReader.GetOrdinal("ImgFile"), 0, thePhoto.ImgFile, 0, thePhoto.FileSize); } } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return thePhoto; }
public static List<PhotoItem> QueryAllPhotoList() { List<PhotoItem> photoList = new List<PhotoItem>(); MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "Select * From pics;"; MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { PhotoItem thePhoto = new PhotoItem(); thePhoto.PhotoID = msqlReader.GetInt32("PhotoID"); thePhoto.Description = msqlReader.GetString("Description"); thePhoto.Name = msqlReader.GetString("Name"); thePhoto.UploadedOn = msqlReader.GetDateTime("UploadedOn"); thePhoto.FileSize = msqlReader.GetInt32("FileSize"); thePhoto.ImgFile = new byte[thePhoto.FileSize]; msqlReader.GetBytes(msqlReader.GetOrdinal("ImgFile"), 0, thePhoto.ImgFile, 0, thePhoto.FileSize); photoList.Add(thePhoto); } } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return photoList; }
private static List<ShopInfo> QueryAllShopList() { List<ShopInfo> ShopList = new List<ShopInfo>(); MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "Select * From shop;"; MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { ShopInfo Shop = new ShopInfo(); Shop.id = msqlReader.GetString("id"); Shop.name = msqlReader.GetString("name"); Shop.tag = msqlReader.GetString("tag"); Shop.type = msqlReader.GetString("type"); Shop.availableinfloor = msqlReader.GetString("availableinfloor"); Shop.rating = msqlReader.GetString("rating"); Shop.description = msqlReader.GetString("description"); Shop.availableProduct = msqlReader.GetString("availableProduct"); ShopList.Add(Shop); } } catch (Exception er) { } finally { msqlConnection.Close(); } return ShopList; }
private DateTime GetServerDateTime() { DateTime curDate = DateTime.UtcNow; //should get it from server using (var conn = new MySql.Data.MySqlClient.MySqlConnection(exelconverterEntities2.ProviderConnectionString)) using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("SELECT now();", conn)) { conn.Open(); var rd = cmd.ExecuteReader(); rd.Read(); curDate = rd.GetDateTime(0); } return curDate; }
public static void UpdateVendorTableWithPaymentOnPurchase(double totalBilledAmount, double purchasePaymentAmount, string venIdKey) { MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); //define the connection used by the command object msqlCommand.Connection = msqlConnection; double dbDue = 0.0; double dbTurnOver = 0.0; string cmdStr = "SELECT turn_over,due FROM vendors WHERE vendor_id='" + venIdKey + "';"; msqlCommand.CommandText = cmdStr; MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { dbDue = double.Parse(msqlReader.GetString("due")); dbTurnOver = double.Parse(msqlReader.GetString("turn_over")); } if (msqlConnection.State == System.Data.ConnectionState.Open) msqlConnection.Close(); //updating the value if (msqlConnection.State != System.Data.ConnectionState.Open) msqlConnection.Open(); double newDueDouble = dbDue + totalBilledAmount - purchasePaymentAmount; String newDue = newDueDouble.ToString(); String newTurnOver = (dbTurnOver + totalBilledAmount).ToString(); msqlCommand.CommandText = "UPDATE vendors SET due='" + newDue + "', turn_over='" + newTurnOver + "' WHERE vendor_id='" + venIdKey + "'; "; msqlCommand.ExecuteNonQuery(); } catch (Exception er) { //MessageBox.Show("Error: " + MethodBase.GetCurrentMethod().Name + ":" + er.Message); } finally { if (msqlConnection.State == System.Data.ConnectionState.Open) msqlConnection.Close(); } }
public static List<CustomersData> FetchCustomersList() { List<CustomersData> customerCollection = new List<CustomersData>(); MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; if (msqlConnection.State != System.Data.ConnectionState.Open) msqlConnection.Open(); msqlCommand.CommandText = "SELECT * FROM customers"; MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); //_customerCollection.Clear(); while (msqlReader.Read()) { CustomersData cusData = new CustomersData(); cusData.customerName = msqlReader.GetString("customer_name"); cusData.customerId = msqlReader.GetString("id"); cusData.customerAdress = msqlReader.GetString("address"); cusData.phoneNumber = msqlReader.GetString("ph_no"); customerCollection.Add(cusData); } msqlConnection.Close(); return customerCollection; }
private static List<PasswordInfo> QueryAllPasswordList() { List<PasswordInfo> PasswordList = new List<PasswordInfo>(); MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "Select * From password ;"; MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { PasswordInfo Password = new PasswordInfo(); Password.id = msqlReader.GetString("id"); Password.name = msqlReader.GetString("name"); Password.email = msqlReader.GetString("email"); Password.userId = msqlReader.GetString("userId"); Password.password = msqlReader.GetString("password"); Password.scrtqstn = msqlReader.GetString("secretQuestion"); Password.scrtans = msqlReader.GetString("secretAnswer"); Password.otherInfo = msqlReader.GetString("otherInfo"); PasswordList.Add(Password); } } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return PasswordList; }
private static List<NoteInfo> QueryAllNoteList() { List<NoteInfo> NoteList = new List<NoteInfo>(); MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "Select * From note ;"; MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { NoteInfo Note = new NoteInfo(); //Note.id = msqlReader.GetString("id"); Note.noteDate = msqlReader.GetDateTime("date"); Note.note = msqlReader.GetString("note"); NoteList.Add(Note); } } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return NoteList; }
private static List<ContactInfo> QueryAllContactList() { List<ContactInfo> ContactList = new List<ContactInfo>(); MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "Select * From contact ;"; MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { ContactInfo Contact = new ContactInfo(); Contact.id = msqlReader.GetString("id"); Contact.name = msqlReader.GetString("name"); Contact.mobileno = msqlReader.GetString("mobile"); Contact.homeno = msqlReader.GetString("homePhone"); Contact.oficeno = msqlReader.GetString("officePhone"); Contact.email = msqlReader.GetString("email"); Contact.address = msqlReader.GetString("address"); Contact.faxno = msqlReader.GetString("faxNumber"); Contact.remark = msqlReader.GetString("remark"); ContactList.Add(Contact); } } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return ContactList; }
public static List<NoteInfo> searchMnthlyNoteList(DateTime date) { DateTime day1st = new DateTime(date.Year, date.Month, 1); DateTime dayLast = new DateTime(date.Year, date.Month + 1, 1); dayLast = dayLast.Subtract(new TimeSpan(1, 0, 0, 0)); List<NoteInfo> NoteList = new List<NoteInfo>(); MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "SELECT * FROM note where date(note.date) >= DATE_SUB( @dayLast, INTERVAL @diff DAY) group by date;"; msqlCommand.Parameters.AddWithValue("@dayLast", dayLast); msqlCommand.Parameters.AddWithValue("@diff", dayLast.Subtract(day1st)); MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { NoteInfo Note = new NoteInfo(); Note.noteDate = msqlReader.GetDateTime("date"); Note.note = msqlReader.GetString("note"); NoteList.Add(Note); } } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return NoteList; }
private static List<ShopInfo> searchAllShopList(ShopInfo shopinfo) { List<ShopInfo> ShopList = new List<ShopInfo>(); MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "Select * From shop where id = @input or name = @input or tag = @input or type = @input or rating = @input or description = @input ; "; msqlCommand.Parameters.AddWithValue("@input", shopinfo.name); MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { ShopInfo Shop = new ShopInfo(); Shop.id = msqlReader.GetString("id"); Shop.name = msqlReader.GetString("name"); Shop.tag = msqlReader.GetString("tag"); Shop.type = msqlReader.GetString("type"); Shop.availableinfloor = msqlReader.GetString("availableinfloor"); Shop.rating = msqlReader.GetString("rating"); Shop.description = msqlReader.GetString("description"); ShopList.Add(Shop); } } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return ShopList; }
private static List<ProductInfo> searchAllProductList(ProductInfo productinfo) { List<ProductInfo> ProductList = new List<ProductInfo>(); MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "Select * From product where id = @input or name = @input or brand = @input or type = @input or description = @input ; "; msqlCommand.Parameters.AddWithValue("@input", productinfo.name); MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { ProductInfo Product = new ProductInfo(); Product.id = msqlReader.GetString("id"); Product.name = msqlReader.GetString("name"); Product.brand = msqlReader.GetString("brand"); Product.type = msqlReader.GetString("type"); Product.description = msqlReader.GetString("description"); ProductList.Add(Product); } } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return ProductList; }
private static List<TaskInfo> QueryAllTaskList() { List<TaskInfo> TaskList = new List<TaskInfo>(); MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "Select * From task;"; MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { TaskInfo Task = new TaskInfo(); Task.id = msqlReader.GetString("taskId"); Task.value = msqlReader.GetString("priority"); Task.taskDetails = msqlReader.GetString("details"); TaskList.Add(Task); } } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return TaskList; }
public static List<CustomerPaymentData> FetchCustomerPaymentData() { List<CustomerPaymentData> customerPaymentCollection = new List<CustomerPaymentData>(); MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "Select customers.id, customers.address, customers.ph_no,customers.customer_name, customer_payment.payment_id, customer_payment.payment_amount, customer_payment.payment_date FROM customers,customer_payment WHERE customer_payment.customer_id = customers.id;"; MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { CustomerPaymentData addCustomerPaymentWindowObject = new CustomerPaymentData(); //addCustomerPaymentWindowObject.serialNo = (_customerPaymentCollection.Count + 1).ToString(); addCustomerPaymentWindowObject.customerId = msqlReader.GetString("id"); addCustomerPaymentWindowObject.paymentId = msqlReader.GetString("payment_id"); addCustomerPaymentWindowObject.customerName = msqlReader.GetString("customer_name"); addCustomerPaymentWindowObject.customerAddress = msqlReader.GetString("address"); addCustomerPaymentWindowObject.customerPhone = msqlReader.GetString("ph_no"); addCustomerPaymentWindowObject.paymentAmount = msqlReader.GetDouble("payment_amount"); addCustomerPaymentWindowObject.paymentDate = msqlReader.GetDateTime("payment_date"); customerPaymentCollection.Add(addCustomerPaymentWindowObject); } } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return customerPaymentCollection; }
private static List<ContactInfo> searchAllContactList(ContactInfo contactinfo) { List<ContactInfo> ContactList = new List<ContactInfo>(); MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; // msqlCommand.CommandText = "Select * From contact where id = @input name = @input or mobile = @input or homePhone = @input or officePhone = @input or email = @input address = @input or faxNumber = @input or officePhone = @input or remark = @input ; "; msqlCommand.CommandText = "Select * From contact where name = @input; "; msqlCommand.Parameters.AddWithValue("@input", contactinfo.name); MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { ContactInfo Contact = new ContactInfo(); //Contact.id = msqlReader.GetString("id"); Contact.name = msqlReader.GetString("name"); //Contact.mobileno = msqlReader.GetString("mobile"); //Contact.homeno = msqlReader.GetString("homePhone"); //Contact.oficeno = msqlReader.GetString("officePhone"); //Contact.email = msqlReader.GetString("email"); //Contact.address = msqlReader.GetString("address"); //Contact.faxno = msqlReader.GetString("faxNumber"); //Contact.remark = msqlReader.GetString("remark"); ContactList.Add(Contact); } } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return ContactList; }
public static List<ToDoData> fetcheToDoData() { List<ToDoData> _toDoCollection = new List<ToDoData>(); MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "Select * from to_do;"; MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { ToDoData ToDoData = new ToDoData(); ToDoData.to_do = msqlReader.GetString("to_do"); ToDoData.date_time = msqlReader.GetDateTime("date_time"); ToDoData.id = msqlReader.GetString("id"); _toDoCollection.Add(ToDoData); } } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return _toDoCollection; }
private static List<NoteInfo> searchAllNoteList(NoteInfo noteinfo) { List<NoteInfo> NoteList = new List<NoteInfo>(); MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "Select * From note where date = @date ; "; msqlCommand.Parameters.AddWithValue("@date", noteinfo.gotoDate); MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { NoteInfo Note = new NoteInfo(); Note.noteDate = msqlReader.GetDateTime("date"); NoteList.Add(Note); } } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return NoteList; }
public static void DeleteCustomerPayment(string customerPaymentToDelete) { MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "DELETE FROM customer_payment WHERE payment_id= @vendorIdToDelete"; msqlCommand.Parameters.AddWithValue("@vendorIdToDelete", customerPaymentToDelete); MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } }
private PhotoItem fetchPhoto() { MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); PhotoItem pHolder = new PhotoItem(); /* try { string sqlQuery = "SELECT * FROM tab_photo where PhotoId='" + photoId + "'"; MySqlDataReader rdr = MySqlHelper.ExecuteReader(connectionString, sqlQuery); while (rdr.Read()) { photo.PhotoId = rdr.GetString("PhotoId"); photo.ProjectID = rdr.GetString("ProjectID"); photo.Day = rdr.GetInt32("Day"); photo.Barcode = rdr.GetString("Barcode"); photo.Photoname = rdr.GetString("Photoname"); photo.PhotoXml = rdr.GetString("PhotoXml"); MemoryStream ms; UInt32 FileSize; Bitmap outImage; int fileSize = rdr.GetInt32(rdr.GetOrdinal("PhotoSize")); byte[] rawData = new byte[fileSize]; rdr.GetBytes(rdr.GetOrdinal("Photo"), 0, rawData, 0, (Int32)fileSize); photo.imageByte = rawData; } } catch (Exception e) { }*/ try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "Select ImgFile From pics where PhotoID = 12459 ;"; MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { MemoryStream ms; int fileSize = 7084;//msqlReader.GetInt32(msqlReader.GetOrdinal("PhotoSize")); byte[] rawData = new byte[fileSize]; msqlReader.GetBytes(msqlReader.GetOrdinal("ImgFile"), 0, rawData, 0, (Int32)fileSize); File.WriteAllBytes("d:\\test"+DateTime.Now.ToOADate().ToString()+".jpg", rawData); ////photo.imageByte = rawData; //ms = new MemoryStream(rawData); //System.Drawing.Image img = System.Drawing.Image.FromStream(ms); //img.Save("d:\test.jpg", System.Drawing.Imaging.ImageFormat.Jpeg); } } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } return pHolder; }
private static Dictionary<string, string> getConfigData() { // Query database for settings MySql.Data.MySqlClient.MySqlConnection con = null; Database.Configuration.open(ref con); var cmd = new MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM aspdashboard_settings", con); var rdr = cmd.ExecuteReader(); var tmp = new Dictionary<string, string>(); while (rdr.Read()) tmp.Add(rdr.GetString(1), rdr.GetString(2)); rdr.Close(); con.Close(); return tmp; }
private static List<ProductInfo> QueryAllProductList() { List<ProductInfo> ProductList = new List<ProductInfo>(); MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "Select * From product;"; MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); while (msqlReader.Read()) { ProductInfo Product = new ProductInfo(); Product.id = msqlReader.GetString("id"); Product.name = msqlReader.GetString("name"); Product.brand = msqlReader.GetString("brand"); Product.type = msqlReader.GetString("type"); Product.description = msqlReader.GetString("description"); Product.availableinshop = msqlReader.GetString("availableinshop"); ProductList.Add(Product); } } catch (Exception er) { } finally { msqlConnection.Close(); } return ProductList; }