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