public IList<City> Load()
{
using (MySqlConnection conn = new MySqlConnection("server = localhost; user id = root; password = ; database = test"))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "set character set 'utf8'";
cmd.ExecuteNonQuery();
var cmdText = @"select * from dol_city";
cmd = conn.CreateCommand();
cmd.CommandText = cmdText;
var reader = cmd.ExecuteReader();
var cityList = new List<City>();
while (reader.Read())
{
var city = new City()
{
ID = reader.GetInt32("id"),
Name = reader.GetString("city_name"),
X = reader.GetFloat("x"),
Y = reader.GetFloat("y")
};
cityList.Add(city);
}
reader.Close();
return cityList;
}
}
public IList<Sim> Load()
{
using (MySqlConnection conn = new MySqlConnection("server = localhost; user id = root; password = ; database = test"))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "set character set 'utf8'";
cmd.ExecuteNonQuery();
var cmdText = @"select * from sim";
cmd = conn.CreateCommand();
cmd.CommandText = cmdText;
var reader = cmd.ExecuteReader();
var cityList = new List<Sim>();
while (reader.Read())
{
var city = new Sim()
{
ID = reader.GetInt32("id"),
QuestID = reader.GetInt32("quest_id"),
CompareID = reader.GetInt32("compare_id"),
Value = reader.GetInt32("value"),
};
cityList.Add(city);
}
reader.Close();
return cityList;
}
}
public ActionResult AddClientRequest(String resource, String project, DateTime projectDate)
{
MySqlCommand cmd = dbConn.CreateCommand();
cmd.CommandText =
"INSERT into request (context, deliveryDate, resourceType, status) VALUES (@project, @delivery, @resource, @status)";
cmd.Parameters.AddWithValue("@project", project);
cmd.Parameters.AddWithValue("@delivery", projectDate);
cmd.Parameters.AddWithValue("@resource", resource);
cmd.Parameters.AddWithValue("@status", 0);
try
{
dbConn.Open();
}
catch (Exception erro)
{
Console.WriteLine(erro);
}
cmd.ExecuteNonQuery();
cmd.CommandText =
"INSERT into client_request VALUES (2, (select id from request order by id desc limit 1))";
cmd.ExecuteNonQuery();
return(RedirectToAction("AddClientRequest"));
}
public MySQL(Config.Config config)
{
this.config = config;
this.Table = config.Table;
String connectionString = "server={0};port={1};uid={2};pwd={3};";
connectionString = String.Format(connectionString,
config.Host, config.Port, config.User, config.Pass);
conn = new MySqlConnection();
conn.ConnectionString = connectionString;
conn.Open();
// Create DB
var cmd = conn.CreateCommand();
cmd.CommandText = String.Format("CREATE DATABASE IF NOT EXISTS `{0}`;", config.Database);
cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = conn.CreateCommand();
cmd.CommandText = String.Format("USE `{0}`;", config.Database);
cmd.ExecuteNonQuery();
cmd.Dispose();
// Create Table
cmd = conn.CreateCommand();
cmd.CommandText = String.Format(getTableCreateString(), config.Table);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
public static void Main(string[] args)
{
string connectionString=
"Server= Localhost;"+
"Database = dbprueba;"+
"User Id=root;"+
"Password=sistemas";
MySqlConnection mySqlConnection = new MySqlConnection(connectionString);
mySqlConnection.Open ();
MySqlCommand updateMySqlCommand= mySqlConnection.CreateCommand();
updateMySqlCommand.CommandText="update articulo set nombre=:nombre where id=!";
updateMySqlCommand.ExecuteNonQuery();
MySqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText = "select * from articulo";
MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader ();
while (mySqlDataReader.Read ()){
Console.WriteLine("id={0} nombre={1}",mySqlDataReader["id"],mySqlDataReader["nombre"]);
}
mySqlDataReader.Close();
mySqlConnection.Close();
string hora=DateTime.Now.ToString();
}
public void Save(IEnumerable<Sim> simList)
{
if (simList.Count() == 0)
return;
using (MySqlConnection conn = new MySqlConnection("server = localhost; user id = root; password = ; database = test"))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "set character set 'utf8'";
cmd.ExecuteNonQuery();
var cmdText = @"select * from quest_sim";
cmd = conn.CreateCommand();
cmd.CommandText = cmdText;
var quest_sim = @"INSERT INTO quest_sim SET quest_id=?quest_id,compare_id=?compare_id,value=?value,start=?start";
//路径
simList.All(sim =>
{
cmd = conn.CreateCommand();
cmd.CommandText = quest_sim;
cmd.Parameters.AddWithValue("?quest_id", sim.QuestID);
cmd.Parameters.AddWithValue("?compare_id", sim.CompareID);
cmd.Parameters.AddWithValue("?value", sim.Value);
cmd.Parameters.AddWithValue("?start", sim.StartCity);
cmd.ExecuteNonQuery();
return true;
});
}
}
public override string Select(string table, string rows, string query, int columns, char separator = '|')
{
string sql = "";
string result = "";
lock (DatabaseLock)
{
if (!IsConnected)
{
ErrorBuffer = "Not connected";
return(null);
}
sql = "SELECT " + rows + " FROM " + table + " " + query;
MySqlCommand xx = Connection.CreateCommand();
xx.CommandText = sql;
MySqlDataReader r = xx.ExecuteReader();
while (r.Read())
{
int i = 0;
while (i < columns)
{
if (result == "")
{
result += r.GetString(i);
}
else
{
result += separator.ToString() + r.GetString(i);
}
i++;
}
}
return(result);
}
}
//Custom query x
public static void custom()
{
try
{
string MyConnectionString = "Server=localhost;Database=databier;Uid=root;Pwd=;";
MySqlConnection connection = new MySqlConnection(MyConnectionString);
connection.Open();
MySqlCommand cmd = connection.CreateCommand();
Console.Write("\n\nVoer een query in: ");
string queryinput = Console.ReadLine();
MySqlCommand querydata = connection.CreateCommand();
cmd.CommandText = queryinput;
Console.Clear();
MySqlDataReader query = cmd.ExecuteReader();
while (query.Read())
{
Console.WriteLine(query.GetString(0) + " | " + query.GetString(1) + " | " + query.GetString(2));
}
connection.Close();
}
catch (Exception optieexception)
{
Console.WriteLine("ERROR: \n{0}", optieexception);
Console.WriteLine("\n> Terug naar de database: Typ (DATABASE NAAM).");
Console.WriteLine("> Terug naar het menu: Typ MENU.");
Console.WriteLine("> Afsluiten: Typ EXIT.");
}
}
//Data uit tabel tonen x
public static void datashow()
{
string MyConnectionString = "Server=localhost;Database=databier;Uid=root;Pwd=;";
MySqlConnection connection = new MySqlConnection(MyConnectionString);
connection.Open();
MySqlCommand cmd = connection.CreateCommand();
try
{
//Data in tabel bekijken
Console.Write("\n\nKies een tabel die u wilt bekijken: ");
string input = Console.ReadLine();
MySqlCommand cmdbierdata = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM " + input;
Console.Clear();
MySqlDataReader readerbierdata = cmd.ExecuteReader();
while (readerbierdata.Read())
{
Console.WriteLine(readerbierdata.GetString(0) + " | " + readerbierdata.GetString(1) + " | " + readerbierdata.GetString(2));
}
}
catch (MySqlException exception)
{
Console.WriteLine("ERROR: \n{0}", exception);
Console.WriteLine("\n> Terug naar de database: Typ (DATABASE NAAM).");
Console.WriteLine("> Terug naar het menu: Typ MENU.");
Console.WriteLine("> Afsluiten: Typ EXIT.");
}
connection.Close();
}
/// <summary>
/// Ajout d'un membre
/// </summary>
/// <param name="nouveauMembre"></param>
/// <returns>Retourne l'id du membre inséré</returns>
public int InsertMember(Membre nouveauMembre)
{
FileStream fs;
BinaryReader br;
string passwordCrypte = CryptPassword(nouveauMembre.password);
command = connection.CreateCommand();
command.CommandText = "INSERT INTO utilisateur (username, password," +
"Nom, Prenom, email, sexe, image, imgFile_name, imgFile_size) " +
"VALUES (@nom_utilisateur, @mdp, @nom," +
"@prenom, @email, @sexe, @image, @imgFile_name, @imgFile_size)";
command.Parameters.AddWithValue("@nom_utilisateur", nouveauMembre.username);
command.Parameters.AddWithValue("@mdp", passwordCrypte);
command.Parameters.AddWithValue("@nom", nouveauMembre.lastName);
command.Parameters.AddWithValue("@prenom", nouveauMembre.firstName);
command.Parameters.AddWithValue("@email", nouveauMembre.email);
command.Parameters.AddWithValue("@sexe", Enum.GetName(typeof(Genre), nouveauMembre.gender));
command.Parameters.AddWithValue("@image", nouveauMembre.imageData);
command.Parameters.AddWithValue("@imgFile_name", nouveauMembre.imgFileName);
command.Parameters.AddWithValue("@imgFile_size", nouveauMembre.fileSize);
// connection.Open();
command.ExecuteNonQuery();
return(Convert.ToInt32(command.LastInsertedId));
}
public void alterUser(TextBox textBoxGammelPassord, TextBox textBoxEpost, TextBox textBoxNyPassord, TextBox textBoxAdresse, TextBox textBoxTelefon, TextBox textBoxID, String bondeID)
{
String dbconnect = myconnectionstring;
MySqlConnection dbconn = new MySqlConnection(dbconnect);
if (textBoxGammelPassord.Text == gammeltpassordLocal)
{
MySqlCommand cmd = dbconn.CreateCommand();
cmd.CommandText = "UPDATE login SET epost='" + textBoxEpost.Text + "', passord= '" + textBoxNyPassord.Text + "'WHERE bondeID= '" + bondeID + "'";
dbconn.Open();
cmd.ExecuteNonQuery();
dbconn.Close();
MySqlCommand cmd2 = dbconn.CreateCommand();
cmd2.CommandText = "UPDATE Kontakt SET adresse= '" + textBoxAdresse.Text + "', telefonnr= '" + textBoxTelefon.Text + "' WHERE bondeID= '" + bondeID + "'";
dbconn.Open();
cmd2.ExecuteNonQuery();
dbconn.Close();
MessageBox.Show(textBoxTelefon.Text);
getinfobruker(textBoxGammelPassord, textBoxEpost, textBoxNyPassord, textBoxAdresse, textBoxTelefon ,textBoxID, bondeID);
}
else
{
MessageBox.Show("Feil passord");
}
}
public bool writeDonneesXML(List<Station> listStation)
{
MySqlConnection connection = new MySqlConnection(myConnectionString);
MySqlCommand cmd;
try
{
connection.Open();
int id = 3;
foreach(Station uneStation in listStation)
{
// Renseignement Station
cmd = connection.CreateCommand();
cmd.CommandText = "INSERT INTO station(station_id,station_adresse,station_cp,station_ville,station_tel,station_lat, station_long, station_id_enseigne)VALUES(@station_id,@station_adresse,@station_cp,@station_ville,@station_tel,@station_lat,@station_long,@station_id_enseigne);commit;";
cmd.Parameters.AddWithValue("@station_id", id);
cmd.Parameters.AddWithValue("@station_adresse", uneStation.address);
cmd.Parameters.AddWithValue("@station_cp", uneStation.code_postal);
cmd.Parameters.AddWithValue("@station_ville", uneStation.city);
cmd.Parameters.AddWithValue("@station_tel", DBNull.Value);
cmd.Parameters.AddWithValue("@station_lat", uneStation.lattitude);
cmd.Parameters.AddWithValue("@station_long", uneStation.longitude);
cmd.Parameters.AddWithValue("@station_id_enseigne", getIdEnseigne());
cmd.ExecuteNonQuery();
// Renseignement Prix
foreach (Prix unPrix in uneStation.price_list)
{
string id_prix = getIdTypeEssence(unPrix);
if(Int32.Parse(id_prix) !=-1)
{
cmd = connection.CreateCommand();
cmd.CommandText = "INSERT INTO prix(prix_type_id, prix_station_id, prix_valeur, prix_date)VALUES(@prix_type_id,@prix_station_id,@prix_valeur,@prix_date);commit;";
cmd.Parameters.AddWithValue("@prix_type_id", id_prix);
cmd.Parameters.AddWithValue("@prix_station_id", id);
cmd.Parameters.AddWithValue("@prix_valeur", unPrix.price);
cmd.Parameters.AddWithValue("@prix_date", unPrix.dateMiseAjour);
cmd.ExecuteNonQuery();
}
}
id++;
}
}
catch (Exception e)
{
AffichagePrix.logger.ecrireInfoLogger("ERROR : " + e.StackTrace, true);
return false;
}
finally
{
if(connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
}
return true;
}
public Boolean ItemAdd(String productName, String productDescription, float price, int productId, String office, int officeId)
{
try
{
DbConnection dbConnection = new DbConnection();
connection = dbConnection.getConnection();
connection.Open();
List<int> storeList = new List<int>();
storeList = getChildStoreIds(officeId, office);
//store(storeList);
if (storeList.Count > 0)
{
if (productId == 0)
{
sqlQuery = "INSERT INTO product (product_id, product_name, product_description) VALUES (NULL, '" + productName + "', '" + productDescription + "')";
MySqlCommand newCommand = connection.CreateCommand();
newCommand.CommandText = sqlQuery;
newCommand.ExecuteNonQuery();
}
foreach (int eachStore in storeList)
{
sqlQuery = "SELECT * FROM store_product WHERE product_id=" + productId + " AND store_id=" + eachStore;
MySqlCommand command = new MySqlCommand(sqlQuery, connection);
MySqlDataReader sdr = command.ExecuteReader();
if (!sdr.HasRows)
{
sdr.Close();
sqlQuery = "INSERT INTO store_product (store_id, product_id, price) VALUES (" + eachStore + ", " + productId + ", " + price + ")";
MySqlCommand newCommand = connection.CreateCommand();
newCommand.CommandText = sqlQuery;
newCommand.ExecuteNonQuery();
}
sdr.Close();
}
return true;
}
return false;
}
catch (Exception ex)
{
return false;
//return new JavaScriptSerializer().Serialize(ex.Message); ;
}
finally
{
connection.Close();
}
}
public static void Main(string[] args)
{
string connectionString =
"Server=localhost;" +
"Database=dbrepaso;" +
"User Id=root;" +
"Password=sistemas";
string command = "SELECT * FROM articulo";
MySqlConnection mySqlConnection = new MySqlConnection(connectionString); //Crear conexión a bd
mySqlConnection.Open(); //Abrimos la conexión
MySqlCommand mySqlCommand = mySqlConnection.CreateCommand(); //Creamos comando SQL
mySqlCommand.CommandText = command; //Cambiamos el texto del comando SQL por command.
MySqlCommand mySqlCommandChange = mySqlConnection.CreateCommand(); //Creamos comando SQL
mySqlCommandChange.CommandText = "UPDATE articulo SET nombre=@nombre WHERE id=1"; //Cambiamos el texto del comando SQL por command.
MySqlParameter mySqlParameter = mySqlCommandChange.CreateParameter();
mySqlParameter.ParameterName = "nombre";
mySqlParameter.Value = DateTime.Now.ToString();
mySqlCommandChange.Parameters.Add(mySqlParameter);
//MySqlDataReader mySqlDataReader; //Creamos un DataReader
//mySqlDataReader = mySqlCommand.ExecuteReader(); // Nos devuelve un mySqlDataReader, lector de datos
MySqlDataReader mySqlDataReaderChange; //Creamos otro DataReader
//mySqlDataReaderChange = mySqlCommandChange.ExecuteNonQuery();
// ExecuteNonQuery ejecuta el Update
mySqlDataReaderChange.Close();
//while(mySqlDataReader.Read()){ //Leemos todas las filas
// Console.WriteLine (mySqlDataReader.GetString (0) + ", " + mySqlDataReader.GetString (1));
//}
while(mySqlDataReaderChange.Read()){ //Leemos todas las filas
Console.WriteLine (mySqlDataReaderChange.GetString (0) + ", " + mySqlDataReaderChange.GetString (1));
}
/* do {
if (mySqlConnection.State == ConnectionState.Open) {
Console.WriteLine ("Conexión establecida.");
} else {
Console.WriteLine ("...");
}
} while (mySqlConnection.State == ConnectionState.Closed);
*/
mySqlConnection.Close();
}
//nathan's point stuff
private void updatePointWell(String vote, String accountID)
{
string myaccountID = (String)Session["accountID"];
int votecount = 0;
conn.Open();
//CHECK THIS
queryStr = "SELECT votecount FROM account where accountId = '" + myaccountID + "'";
using (cmd = new MySqlCommand(queryStr, conn))
{
votecount = Convert.ToInt32(cmd.ExecuteScalar());
}
conn.Close();
//INCREASE THE VOTECOUNT
conn.Open();
queryStr = "";
queryStr = "UPDATE account SET votecount = votecount+1 WHERE accountId = " + myaccountID;
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.ExecuteReader();
conn.Close();
point_algo pa = new point_algo();
float pointworth = pa.point_worth(votecount);
/*fixed for SQL injection */
//ADD POINT WORTH INTO THE POINT WELL (well drys up if it isnt used, the decay... pretty good right? :D
if (vote == "+1")
{
//CHECK THIS (upvote)
conn.Open();
MySqlCommand cmddd = conn.CreateCommand();
queryStr = "";
queryStr = "UPDATE account SET pointwell = pointwell +'" + pointworth + "' WHERE accountId = " + accountID;
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.ExecuteReader();
conn.Close();
}
else
{
//DOWNVOTE
conn.Open();
MySqlCommand cmddd = conn.CreateCommand();
queryStr = "";
queryStr = "UPDATE account SET pointwell = pointwell -'" + pointworth + "' WHERE accountId = " + accountID;
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.ExecuteReader();
conn.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
string MyConnectionString = "Server=localhost;Database=EMS;Uid=root;Pwd='';";
MySqlConnection connection = new MySqlConnection(MyConnectionString);
connection.Open();
MySqlCommand cmd = connection.CreateCommand();
MySqlCommand cmd2 = connection.CreateCommand();
MySqlCommand cmd3 = connection.CreateCommand();
cmd.CommandText = "INSERT INTO sensor(sensor_id,sensor_type,contract_id,refresh_time,sensor_status) VALUES(@sensor,@type,@cont,@time,@status)";
cmd.Parameters.AddWithValue("@sensor", id.Text);
cmd.Parameters.AddWithValue("@type", type.SelectedItem.ToString());
cmd.Parameters.AddWithValue("@cont",contract.Text.ToString());
DateTime time = Convert.ToDateTime(refreshtime.Text);
cmd.Parameters.AddWithValue("@time", time);
if (contract.Text != null)
{
cmd.Parameters.AddWithValue("@status", true);
}else{
cmd.Parameters.AddWithValue("@status", true);
}
cmd.ExecuteNonQuery();
connection.Close();
if (contract.Text != null)
{
cmd2.CommandText = "INSERT INTO contract(contract_id,sensor_id,established_date,expire_date,agent_id,Service_provider) VALUES(@contract_id,@sensor_id,@established_date,@expire_date,@agent_id,@Service_provider)";
cmd2.Parameters.AddWithValue("@contract_id", contract.Text.ToString());
cmd2.Parameters.AddWithValue("@sensor_id", id.Text.ToString());
string date1 = establised.Text;
string date2 = establised.Text;
DateTime dt1 = Convert.ToDateTime(date1);
DateTime dt2 = Convert.ToDateTime(date2);
cmd2.Parameters.AddWithValue("@established_date", dt1.ToString());
cmd2.Parameters.AddWithValue("@expire_date", dt2.ToString());
cmd2.Parameters.AddWithValue("@agent_id", agent.SelectedItem.ToString());
cmd2.Parameters.AddWithValue("@Service_provider", service.SelectedItem.ToString());
connection.Open();
cmd2.ExecuteNonQuery();
connection.Close();
}
Login.warning ww = new Login.warning("Sensor Successfully Added!", this);
ww.Show();
}
public bool credentialCheck(string username, string password, out int userId)
{
userId = 0;
/*Byte[] hash; // we will use this in the query
* var salt = "LesKoding"; // Our app spesific key. To prevent our app from direct attacks
* var pass_data = Encoding.UTF8.GetBytes(salt + password); // Convert password to Byte[]
* using (SHA512 shaM = new SHA512Managed())
* {
* hash = shaM.ComputeHash(pass_data);
* }*/
MySqlCommand com = client.CreateCommand();
com.CommandText = "SELECT * FROM accountdata WHERE username='******' AND password ='******'";
com.Connection = client;
MySqlDataReader reader = null;
try
{
reader = com.ExecuteReader(new System.Data.CommandBehavior());
if (reader.Read()) // tek column okusak yeter, o yuzden while yapmadim.
{
userId = reader.GetInt32("id");
return(true);
}
else
{
return(false);
}
}
catch
{
return(false);
}
finally
{
try
{
reader.Close();
}
catch { }
}
}
static void Main(string[] args)
{
var conn = new MySql.Data.MySqlClient.MySqlConnection();
string myConnectionString = "server=**********;uid=*****;" + "pwd=***********;database=*********;";
try
{
conn.ConnectionString = myConnectionString;
conn.Open();
}
catch(Exception e) {
Console.WriteLine(e.ToString());
}
//Loading names
names = new Name();
int failed = 0;
int total = 0;
for (int i = 0; i < 1000; i++){
total++;
Person p = generateRandomPerson();
MySqlCommand commnd = conn.CreateCommand();
commnd.CommandText = "INSERT INTO person VALUES ('" + p.personnummer + "','" + p.Fornavn + "', '" + p.Etternavn + "')";
try {
commnd.ExecuteNonQuery();
//Console.WriteLine("+");
}
catch (Exception e) {
failed++;
Console.WriteLine(failed + "/" + total);
//Console.WriteLine("--" + p.personnummer);
}
}
conn.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
string thisProjectID = Request.QueryString["ProjectID"];
if (!string.IsNullOrEmpty(thisProjectID))
{
DBConnection = new MySqlConnection(objUKFilmLocation.DBConnect);
DBCommand = DBConnection.CreateCommand();
DBConnection.Open();
// Update Record
DBCommand.CommandText = "update ProjectDetails set ShelvedDate = '" + UK_Film_Location_Class.UKFilmLocation.makeSQLDate(DateTime.Now.ToString()) + "' where ProjectID = '" + thisProjectID + "';";
DBCommand.ExecuteNonQuery();
DBConnection.Close();
}
DBConnection.Dispose();
Response.Redirect("/Opportunities.aspx");
}
/// <summary>
/// Query an SQL database
/// </summary>
/// <param name="query">Select query that returns a data table</param>
/// <param name="Parameters">Query parameters with their values</param>
/// <returns>Query results as a DataTable</returns>
public DataTable Select(string query, Dictionary <string, object> Parameters = null)
{
DataTable dt = new DataTable();
//Create Query
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(connString))
{
conn.ConnectionString = connString;
using (DbCommand cmd = conn.CreateCommand())
using (DbDataAdapter da = new MySqlDataAdapter())
{
cmd.CommandText = query;
da.SelectCommand = cmd;
//Add Parameters
if (Parameters != null)
{
foreach (KeyValuePair <string, object> kvp in Parameters)
{
DbParameter parameter = cmd.CreateParameter();
parameter.ParameterName = kvp.Key;
parameter.Value = kvp.Value;
cmd.Parameters.Add(parameter);
}
}
//Execute Query
conn.Open();
da.Fill(dt);
return(dt);
}
}
}
public async Task <string> GetPasswordHash(string user, MySql.Data.MySqlClient.MySqlConnection conn)
{
try
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select password from users where user=@user";
cmd.Parameters.AddWithValue("@user", user);
cmd.Connection = conn;
await conn.OpenAsync();
MySqlDataReader uid = cmd.ExecuteReader();
if (await uid.ReadAsync())
{
return(uid["password"].ToString());
}
else
{
return(null);
}
}
catch (MySqlException ex)
{
MessageBox.Show("Error: " + ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
finally
{
conn.Close();
}
}
public static string FetchPublicContent(string password)
{
using (MySqlConnection con = new MySqlConnection(Database.ConnectionString))
{
con.Open();
using (MySqlCommand command = con.CreateCommand())
{
command.Parameters.AddWithValue("@docID", requestID);
command.Parameters.AddWithValue("@password", password);
command.CommandText =
"SELECT Revisions.Content from Revisions join Documents on Revisions.docID=Documents.docID where Revisions.docID=@docID and Revisions.revisionID=(Select Max(revisionID) from Revisions where docID=@docID) AND publicPassword = @password" ;
MySqlDataReader reader = command.ExecuteReader();
if(reader.HasRows)
{
reader.Read();
return LiveDocs.livedocs.Editor.ParseMarkup((String)reader[0]);
}
else
{
throw new Exception("Password not correct or document does not exist");
}
}
}
return null;
}
protected void Button1_Click(object sender, EventArgs e)
{
// MySql.Data.MySqlClient.MySqlConnection dbConn = new MySql.Data.MySqlClient.MySqlConnection("Persist Security Info=False;server=77.237.11.222;database=mati;uid=user1;password=user1");
string cnString = ConfigurationManager.ConnectionStrings["MatiDB"].ConnectionString;
MySql.Data.MySqlClient.MySqlConnection dbConn = new MySql.Data.MySqlClient.MySqlConnection(cnString);
MySqlCommand cmd = dbConn.CreateCommand();
cmd.CommandText = "SELECT mail from mati WHERE login ='******'";
string flag1 = "1";
try
{
dbConn.Open();
}
catch (Exception erro)
{
flag1 = erro.Message;
}
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
flag1 = reader["mail"].ToString() + "now";
}
LabelMail.Text = flag1.ToString();
}
public static string[] details(int[] psn)
{
//Function prints the SQL Details and returns an Array of the fetched Subject key Identifiers from the Database
string[] ski = new string[50];
for (int i = 0; psn[i] != 0; i++)
{
Console.WriteLine(psn[i]);
string dbConnectionString = string.Format("server={0};uid={1};pwd={2};database={3};", serverIp, username, password, databaseName);
var conn = new MySql.Data.MySqlClient.MySqlConnection(dbConnectionString);
MySqlCommand command = conn.CreateCommand();
command.CommandText = "SELECT * FROM signaturedb.signature WHERE psn_number=('" + psn[i] + "')";
try
{
conn.Open();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
ski[i] = reader["key"].ToString();
Console.WriteLine(reader["key"].ToString());
Console.WriteLine(reader["password"].ToString());
Console.WriteLine(reader["key_text"].ToString());
}
}
return(ski);
}
private void btnZarejestruj_Click(object sender, RoutedEventArgs e)
{
string MyConnectionString = "Server=localhost;Database=hosting;Uid=root;";
MySqlConnection con = new MySqlConnection(MyConnectionString);
MySqlCommand cmd;
con.Open();
try
{
cmd = con.CreateCommand();
cmd.CommandText = "insert into user (login, haslo, poziom_dostepu) values (@login, @haslo, @poziom);";
cmd.Parameters.AddWithValue("@login", txtZarejestrujNazwa.Text);
cmd.Parameters.AddWithValue("@haslo", txtZarejestrujHaslo.Password);
cmd.Parameters.AddWithValue("@poziom", txtZarejestrujEmail.Text);
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
con.Close();
txtZarejestrujNazwa.Text = "";
txtZarejestrujHaslo.Password = "";
txtZarejestrujEmail.Text = "";
}
public bool addLike(string username, string owner, string url)
{
MySql.Data.MySqlClient.MySqlConnection conn;
try
{
if (hasNotLiked(username, owner, url))
{
conn = new MySql.Data.MySqlClient.MySqlConnection();
conn.ConnectionString = myConnectionString;
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "update users set likes=concat(likes,'" + owner + " ',+'" + url + ",') WHERE username='******'";
cmd.ExecuteNonQuery();
conn.Close();
return(true);
}
else
{
return(false);
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
System.Diagnostics.Debug.Write(ex.Message);
System.Diagnostics.Debug.Write(ex.StackTrace);
}
return(false);
}
public async Task SET(Config.Noty noty, MySql.Data.MySqlClient.MySqlConnection conn)
{
try
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "insert into noty(title,content,autor,encr_alg) values('" + noty.Title +
"','" + noty.Content + "','" + noty.Autor + "','" + noty.EncrAlg + "');";
cmd.Connection = conn;
await conn.OpenAsync();
var result = Convert.ToString(await cmd.ExecuteNonQueryAsync());
if (Convert.ToInt16(result) == 1)
{
MessageBox.Show(@"Noty Created!", @"Saved", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
else
{
MessageBox.Show(
@"Error, we can not Create the Noty! " + Environment.NewLine + "For more informations please contact the developers.",
@"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (MySqlException ex)
{
MessageBox.Show("Error: " + ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
conn.Close();
}
}
protected void btn_show_Click(object sender, EventArgs e)
{
conn = new MySql.Data.MySqlClient.MySqlConnection(ConnString);
conn.Open();
try
{
cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM assigned_employee where reqid='" + tb_reqid.Text + "'";
reader = cmd.ExecuteReader();
//int x =reader.ro;
//Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('COUNT IS '+'"+x+"');</script>");
//("COUNT IS " + x);
//DataTable dt = new DataTable();
//dt.Load(reader);
//int numberOfResults = dt.Rows.Count;
//Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('COUNT IS '+'" + numberOfResults + "');</script>");
//for (int z=0; z< numberOfResults; z++)
//{
while (reader.Read())
{
tb_assignedto.Text = tb_assignedto.Text + reader.GetString("name") + "\n";
}
//}
}
catch { }
conn.Close();
}
private void InsertTraza(string mensaje, int acceso = -1, Nivel nivel = 0, SubNivel subNivel = 0, int elemento = 0, SubElemento subElemento = 0, Terciario terciario = 0, Accion accion = 0, SubAccion subAccion = 0)
{
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_config.GetConnectionString(_connectionString)))
using (var command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO log (IdNivel,IdSubNivel,IdentificadorAcceso,IdElemento,IdSubElemento,IdTerciario,IdAccion,IdSubAccion,Texto) " +
" VALUES ( @IdNivel,@IdSubNivel,@IdentificadorAcceso,@IdElemento,@IdSubElemento,@IdTerciario,@IdAccion,@IdSubAccion,@Texto);";
command.Parameters.Add("@IdNivel", MySqlDbType.Int16);
command.Parameters.Add("@IdSubNivel", MySqlDbType.Int16);
command.Parameters.Add("@IdentificadorAcceso", MySqlDbType.Int16);
command.Parameters.Add("@IdElemento", MySqlDbType.Int16);
command.Parameters.Add("@IdSubElemento", MySqlDbType.Int16);
command.Parameters.Add("@IdTerciario", MySqlDbType.Int16);
command.Parameters.Add("@IdAccion", MySqlDbType.Int16);
command.Parameters.Add("@IdSubAccion", MySqlDbType.Int16);
command.Parameters.Add("@Texto", MySqlDbType.String);
command.Parameters["@IdNivel"].Value = (int)nivel;
command.Parameters["@IdSubNivel"].Value = (int)subNivel;
command.Parameters["@IdentificadorAcceso"].Value = acceso;
command.Parameters["@IdElemento"].Value = elemento;
command.Parameters["@IdSubElemento"].Value = (int)subElemento;
command.Parameters["@IdTerciario"].Value = (int)terciario;
command.Parameters["@IdAccion"].Value = (int)accion;
command.Parameters["@IdSubAccion"].Value = (int)subAccion;
command.Parameters["@Texto"].Value = LimitSize(mensaje, 5000);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
}
public void InsertEntrada(int?acceso, string origen, string destino)
{
try
{
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_config.GetConnectionString(_connectionString)))
using (var command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO movimientos (idAcceso, procedencia, destino) " +
" VALUES ( @idAcceso,@procedencia,@destino);";
command.Parameters.Add("@idAcceso", MySqlDbType.Int16);
command.Parameters.Add("@procedencia", MySqlDbType.String);
command.Parameters.Add("@destino", MySqlDbType.String);
command.Parameters["@idAcceso"].Value = acceso ?? -1;
command.Parameters["@procedencia"].Value = origen;
command.Parameters["@destino"].Value = destino;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
}
catch (Exception ex)
{
InsertException(ex, -102);
}
}
public void ProcessRequest(HttpContext context)
{
if (PhpSessionId == null)
PhpSessionId = context.Request.QueryString["sid"];
if (Username == null)
Username = context.Request.QueryString["username"];
if (Username != null)
{
FormsAuthentication.RedirectFromLoginPage(Username, false);
}
else
{
try
{
if (PhpSessionId == null)
throw new Exception("Invalid Session Id");
var connectionString = ConfigurationManager.ConnectionStrings["FFVA"].ConnectionString;
using (var cn = new MySqlConnection(connectionString))
{
string joomlaSessionsTable = WebConfigurationManager.AppSettings["JoomlaSessionsTable"];
cn.Open();
using (var cmd = cn.CreateCommand())
{
cmd.CommandText = "select username from " + joomlaSessionsTable + " where session_id = ?sid";
cmd.Parameters.Add(new MySqlParameter("?sid", PhpSessionId));
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
object username = reader["username"];
if (username != null && username != DBNull.Value)
FormsAuthentication.RedirectFromLoginPage(username.ToString(), false);
else
throw new Exception("Username is null");
}
}
}
}
}
catch (Exception ex)
{
context.Response.ContentType = "text/html";
context.Response.Write("<html>");
context.Response.Write("<body>");
context.Response.Write("Por favor logueese al sitio y vuelva a acceder al torneo ");
context.Response.Write("<div style='display:none'>" + ex.Message + "</div>");
context.Response.Write("<div style='display:none'>" + ex.StackTrace + "</div>");
context.Response.Write("</body>");
context.Response.Write("</html>");
}
}
}
public bool InsertarUsuario(objNuevoUsuario Usuario)
{
MySql.Data.MySqlClient.MySqlConnection dbConn = new MySql.Data.MySqlClient.MySqlConnection("Persist Security Info=False;server=localhost;database=hotel_bd;uid=conexion;password=pruebas1.");
bool idnumber = false;
MySqlCommand cmd = dbConn.CreateCommand();
cmd.CommandText = @" INSERT INTO usuario(username, password, email, dni, nombre, apellidos, direccion, tlf, enabled)
VALUES('" + Usuario.strUser + "', '" + Usuario.strPass + "', '" + Usuario.strCorreo + "', " + Usuario.int64Dni + "," +
" '" + Usuario.strNombre + "', '" + Usuario.strApellidos + "', '" + Usuario.strDireccion + "', " + Usuario.strTelefono + ", 1)";
try
{
dbConn.Open();
}
catch (Exception erro)
{
strMensajeError = erro.Message;
dbConn.Close();
}
try
{
int reader = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
strMensajeError = e.Message;
return(false);
}
return(idnumber);
}
public static List<BusStops> GetAllBusStops()
{
string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["TrackABus"].ConnectionString;
using (var connection = new MySqlConnection(ConnectionString))
{
using (var cmd = connection.CreateCommand())
{
try
{
var stops = new List<BusStops>();
connection.Open();
cmd.CommandText = "SELECT StopName FROM BusStop;";
var read = cmd.ExecuteReader();
while (read.Read())
{
stops.Add(new BusStops(){busStopNames = read.GetString(0)});
}
read.Close();
connection.Close();
return stops;
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
connection.Close();
return null;
}
}
}
}
public bool ConsultarUsuario(objUsuario Usuario)
{
MySql.Data.MySqlClient.MySqlConnection dbConn = new MySql.Data.MySqlClient.MySqlConnection("Persist Security Info=False;server=localhost;database=hotel_bd;uid=conexion;password=pruebas1.");
bool idnumber = false;
MySqlCommand cmd = dbConn.CreateCommand();
cmd.CommandText = "SELECT COUNT(*) resultado from usuario WHERE username = '******'";
try
{
dbConn.Open();
}
catch (Exception erro)
{
strMensajeError = erro.Message;
dbConn.Close();
}
try
{
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
idnumber = reader.HasRows;
}
return(idnumber);
}
catch (Exception ex)
{
strMensajeError = ex.Message;
return(false);
}
}
public static void chatMsg(string connectionString, string message, string fromuser, string usercolor, string textcolor, string textsize, string addhours)
{
string step1 = message.Replace("'", "’");
string parsedmsg = step1.Replace(@"\", @"\\");
DateTime value = DateTime.Now.AddHours(Convert.ToDouble(addhours));
string time = value.ToString("yyyyMMddHHmmssffff");
// logs player IP to mysql table 'terraria_iplog'
IDbConnection dbcon2;
dbcon2 = new MySqlConnection(connectionString);
dbcon2.Open();
IDbCommand dbcmd2 = dbcon2.CreateCommand();
string sql2 = "INSERT INTO pcchat_message " +
"( date, type, author_id, target_room_id, body, offline, author_nickname, css_properties ) " +
"VALUES ( '" + time + "', '3001', '1', '1', '" + parsedmsg + "', 'n', '^" + usercolor + fromuser + "', 'color:#" + textcolor + ";font-family:Verdana;font-size:" + textsize + "px;' )";
dbcmd2.CommandText = sql2;
IDataReader reader2 = dbcmd2.ExecuteReader();
// clean up
reader2.Close();
reader2 = null;
dbcmd2.Dispose();
dbcmd2 = null;
dbcon2.Close();
dbcon2 = null;
//end mysql
}
public bool InsertarRol(objUsuarioRol Usuario)
{
MySql.Data.MySqlClient.MySqlConnection dbConn = new MySql.Data.MySqlClient.MySqlConnection("Persist Security Info=False;server=localhost;database=hotel_bd;uid=conexion;password=pruebas1.");
bool idnumber = false;
MySqlCommand cmd = dbConn.CreateCommand();
cmd.CommandText = @" INSERT INTO user_role(user_id, role_id)
VALUES(" + Usuario.strUser + ", " + Usuario.strRol + ") ";
try
{
dbConn.Open();
}
catch (Exception erro)
{
strMensajeError = erro.Message;
dbConn.Close();
}
try
{
int reader = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
strMensajeError = e.Message;
return(false);
}
return(idnumber);
}
//m mayucula en main obligatoriamente.
public static void Main(string[] args)
{
MySqlConnection mysqlconection = new MySqlConnection (
"Database=dbprueba;Data Source=localhost;User id=root; Password=sistemas");
mysqlconection.Open ();
MySqlCommand mysqlcommand = mysqlconection.CreateCommand ();
mysqlcommand.CommandText = "select * from articulo";
// "select a.categoria as articulocategoria, c.nombre as categorianombre, count(*)" +
// "from articulo a " +
// "left join categoria c on a.categoria= c.id " +
// "group by articulocategoria, categorianombre";
MySqlDataReader mysqldatareader = mysqlcommand.ExecuteReader ();
//---------------------------------------------------------------
updateDatabase (mysqlconection);
showColumnNames (mysqldatareader);
show (mysqldatareader);
//---------------------------------------------------------------
mysqldatareader.Close ();
mysqlconection.Close ();
}
public bool InsertarHotel(objHotel Hotel)
{
MySql.Data.MySqlClient.MySqlConnection dbConn = new MySql.Data.MySqlClient.MySqlConnection("Persist Security Info=False;server=localhost;database=hotel_bd;uid=conexion;password=pruebas1.");
bool idnumber = false;
MySqlCommand cmd = dbConn.CreateCommand();
cmd.CommandText = @" INSERT INTO hotel(nombre, estrellas, direccion, ciudad, telefono, precio_supletoria, tiempo_reserva_id)
VALUES('" + Hotel.strNombreHotel + "', '" + Hotel.strEstrellas + "', '" + Hotel.strDireccion + "', '" + Hotel.strCiudad + @"',
'" + Hotel.strTelefono + "', '" + Hotel.strPrecio + "', " + Hotel.strTiempoReserva + ") ";
try
{
dbConn.Open();
}
catch (Exception erro)
{
strMensajeError = erro.Message;
dbConn.Close();
}
try
{
int reader = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
strMensajeError = e.Message;
return(false);
}
return(idnumber);
}
/// <summary>
/// Executes the gives SQL query
/// </summary>
/// <param name="query">The SQL query to be executed</param>
/// <param name="Parameters">Query parameters and their values</param>
/// <returns>Number of rows affected</returns>
public int ExecuteCommand(string query, Dictionary <string, object> Parameters = null)
{
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(connString))
{
conn.ConnectionString = connString;
using (MySqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = query;
//Add Parameters
if (Parameters != null)
{
foreach (KeyValuePair <string, object> kvp in Parameters)
{
DbParameter parameter = cmd.CreateParameter();
parameter.ParameterName = kvp.Key;
parameter.Value = kvp.Value;
cmd.Parameters.Add(parameter);
}
}
//Execute Query
conn.Open();
return(cmd.ExecuteNonQuery());
}
}
}
public async Task <string> Notys(int IDNOTY, MySql.Data.MySqlClient.MySqlConnection conn)
{
try
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select content from noty where id=@user";
cmd.Parameters.AddWithValue("@user", IDNOTY);
cmd.Connection = conn;
await conn.OpenAsync();
MySqlDataReader uid = cmd.ExecuteReader();
if (await uid.ReadAsync())
{
return(uid["content"].ToString());
}
return(null);
}
catch (MySqlException ex)
{
MessageBox.Show("Error: " + ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
finally
{
conn.Close();
}
}
public static IDataReader ExecuteReader(MySqlConnection conn, CommandType cmdType, string cmdText, Dictionary<string, object> cmdParms)
{
MySqlCommand cmd = conn.CreateCommand();
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return rdr;
}
/*
* This function to insert into
* new and old BUIDs table
* */
public void insert_into_database(String old_buid, String new_buid)
{
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "INSERT INTO old_new_buid(OLD_BUID,NEW_BUID) VALUES(" + old_buid + "," + new_buid + ")";
comm.ExecuteNonQuery();
}
public static Boolean Delete(ClienteTO clsCliente)
{
String myConnection = "Server=localhost;Database=gerenciadornf;Uid=root;Pwd=;";
MySqlConnection connection = new MySqlConnection(myConnection);
MySqlCommand cmd;
connection.Open();
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("DELETE FROM cliente WHERE IDCliente=@IDCliente");
cmd = connection.CreateCommand();
cmd.CommandText = strSql.ToString();
cmd.Parameters.AddWithValue("@IDCliente", clsCliente.IDCliente);
cmd.ExecuteNonQuery();
return true;
}
catch (Exception)
{
throw;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
public async Task <bool> Delete(string id, MySql.Data.MySqlClient.MySqlConnection conn)
{
try
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "delete from noty where id=@id";
cmd.Parameters.AddWithValue("@id", id);
cmd.Connection = conn;
await conn.OpenAsync();
MySqlDataReader uid = cmd.ExecuteReader();
if (await uid.ReadAsync())
{
return(true);
}
else
{
return(false);
}
}
catch (MySqlException ex)
{
MessageBox.Show("Error: " + ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
finally
{
conn.Close();
}
}
public override DataSet ExecuteSQL(string SQL, out int count,
CommandType type = (CommandType)1,
params DbParameter[] args)
{
count = 0;
using (MySqlConnection conn = new MySqlConnection(this.ConnectionString)) {
try {
conn.Open ();
using (MySqlCommand cmd = conn.CreateCommand()) {
cmd.CommandText = SQL;
cmd.CommandType = type;
if (args.Length != 0)
cmd.Parameters.AddRange (args);
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)) {
DataSet ds = new DataSet ();
count = da.Fill (ds);
return ds;
}
}
} catch (MySqlException) {
return null;
} finally {
if (conn.State == ConnectionState.Open) {
conn.Close ();
}
}
}
}
public void cancelAnything(string username, string hotel, string flight)
{
MySql.Data.MySqlClient.MySqlConnection conn;
try
{
conn = new MySql.Data.MySqlClient.MySqlConnection();
conn.ConnectionString = myConnectionString;
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "delete from hotelrez WHERE client='" + username.ToString() + "';";
cmd.ExecuteNonQuery();
cmd.CommandText = "delete from flightrez WHERE client='" + username.ToString() + "';";
cmd.ExecuteNonQuery();
cmd.CommandText = "update hotels set rooms=rooms+1 WHERE name='" + hotel.ToString() + "';";
cmd.ExecuteNonQuery();
cmd.CommandText = "update flights set seats=seats+1 WHERE name='" + flight.ToString() + "';";
cmd.ExecuteNonQuery();
conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
System.Diagnostics.Debug.Write(ex.Message);
System.Diagnostics.Debug.Write(ex.StackTrace);
}
}
// uses it's own connection
public void checkTable(string tablename)
{
bool isTable = false;
//mysql- check to see if tables already exist
IDbConnection dbcon = new MySqlConnection (connectionString);
dbcon.Open ();
IDbCommand dbcmd = dbcon.CreateCommand ();
string sql =
" SHOW tables LIKE '" + tablename + "'";
dbcmd.CommandText = sql;
IDataReader reader = dbcmd.ExecuteReader ();
while (reader.Read()) {
string checkresult = (string)reader ["Tables_in_" + mysqldatabase + " (" + tablename + ")"];
isTable = true;
}
ProgramLog.Plugin.Log ("[Mysql] checkTable " + tablename + ": Found:" + isTable);
// clean up
reader.Close ();
reader = null;
dbcmd.Dispose ();
dbcmd = null;
dbcon.Close ();
dbcon = null;
//end check
if (!isTable) {
//mysql- create table if not found
string connectionString2 =
"Server=" + mysqlserver + ";" +
"Database=" + mysqldatabase + ";" +
"User ID=" + mysqluser + ";" +
"Password="******";" +
"Pooling=false";
dbcon = new MySqlConnection (connectionString);
dbcon.Open ();
IDbCommand dbcmd2 = dbcon.CreateCommand ();
string sql2 = "";
if (tablename == "terraria") {
sql2 = " CREATE TABLE " + tablename + " ( total INT NOT NULL , timestamp DATETIME NOT NULL , players INT NOT NULL , playernames TEXT NOT NULL , ops INT NOT NULL , opnames TEXT NOT NULL , allnames TEXT NOT NULL ) ";
}
if (tablename == "terraria_tiles") {
sql2 = " CREATE TABLE " + tablename + " ( timestamp DATETIME NOT NULL , player TEXT NOT NULL , x INT NOT NULL , y INT NOT NULL , action TEXT NOT NULL , tile INT NOT NULL, tiletype TEXT NOT NULL, wall INT NOT NULL, undone BOOLEAN NOT NULL ) ENGINE = MYISAM ;";
}
if (tablename == "terraria_iplog") {
sql2 = " CREATE TABLE " + tablename + " ( timestamp DATETIME NOT NULL , player TEXT NOT NULL , ip TEXT NOT NULL);";
}
dbcmd2.CommandText = sql2;
IDataReader reader2 = dbcmd2.ExecuteReader ();
// clean up
reader2.Close ();
reader2 = null;
dbcmd2.Dispose ();
dbcmd2 = null;
dbcon.Close ();
dbcon = null;
ProgramLog.Plugin.Log ("[Mysql] Table '" + tablename + "' created.");
//end create
}
}
private void aggiornaPunti()
{
Form2 f2 = (Form2)Application.OpenForms["Form2"];
string dbpwd = "lightwave";
MySql.Data.MySqlClient.MySqlConnection dbConn = new MySql.Data.MySqlClient.MySqlConnection("Persist Security Info=False;server=localhost;database=phidelity;uid=root;password="******"UPDATE phidelity.cards SET punti = punti +" + label1.Text + " where barcode='" + f2.TextBoxBarcode + "'";
Console.WriteLine("MySQL command= {0}", cmd.CommandText);
try
{
dbConn.Open();
int rc_updated = cmd.ExecuteNonQuery();
using (var wb = new WebClient())
{
String url = "http://www.phi-lab.com/insertRecord.php?nome=" + f2.TextBoxNome + "&cognome=" + f2.TextBoxCognome + "&punti=" + newPunti + "&barcode=" + f2.TextBoxBarcode + "&email=" + f2.TextBoxEmail + "&telefono=" + f2.TextBoxTelefono;
var response = wb.DownloadString(url);
}
}
catch (Exception erro)
{
MessageBox.Show("Erro" + erro);
this.Close();
}
dbConn.Close();
}
private void ProximoCodigo()
{
try
{
// Step 1 - Connection stablished
MySqlConnection MyConexion = new MySqlConnection(Conexion.ConectionString);
// Step 2 - Create command
MySqlCommand MyCommand = MyConexion.CreateCommand();
// Step 3 - Set the commanndtext property
MyCommand.CommandText = "SELECT count(*) FROM calendarioencintado";
// Step 4 - Open connection
MyConexion.Open();
// Step 5 - Execute the SQL Statement y Asigno el valor resultante a la variable "codigo"
int codigo;
codigo = Convert.ToInt32(MyCommand.ExecuteScalar());
codigo = codigo + 1;
txtRegistro.Text = Convert.ToString(codigo);
txtYear.Focus();
// Step 5 - Close the connection
MyConexion.Close();
}
catch (MySqlException MyEx)
{
MessageBox.Show(MyEx.Message);
}
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
string dbpwd = "lightwave";
MySql.Data.MySqlClient.MySqlConnection dbConn = new MySql.Data.MySqlClient.MySqlConnection("Persist Security Info=False;server=localhost;database=phidelity;uid=root;password="******"SELECT * FROM phidelity.cards where barcode='" + textBox1.Text + "' ;";
try
{
dbConn.Open();
}
catch (Exception erro)
{
MessageBox.Show("Erro" + erro);
this.Close();
}
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("MySQL version : {0}", reader.ToString());
}
}
public GeneralService()
{
oData = new CallBackData();
oData.bIsOK = true;
#region open mysql connection
try
{
oDbCon = new MySqlConnection(sConnectionString);
oDbCon.Open();
oDbCmd = oDbCon.CreateCommand();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
switch (ex.Number)
{
case 0:
oData.bIsOK = false;
oData.Result = "Cannot connect to server. Contact administrator";
//return oData;
break;
case 1045:
oData.bIsOK = false;
oData.Result = "Invalid username/password, please try again";
//return oData;
break;
}
}
#endregion
}
public bool InsertarBebida(ObjNuevaBebida Usuario)
{
MySql.Data.MySqlClient.MySqlConnection dbConn = new MySql.Data.MySqlClient.MySqlConnection("Persist Security Info=False;server=localhost;database=hotel_bd;uid=conexion;password=pruebas1.");
bool idnumber = false;
MySqlCommand cmd = dbConn.CreateCommand();
cmd.CommandText = @" INSERT INTO bebida(precio, nombre, FECHA_VENCIMIENTO, MARCA)
VALUES(" + Usuario.strPrecio + ", '" + Usuario.strBebida + "', '" + Usuario.strFecha + "', '" + Usuario.strMarca + "')";
try
{
dbConn.Open();
}
catch (Exception erro)
{
strMensajeError = erro.Message;
dbConn.Close();
}
try
{
int reader = cmd.ExecuteNonQuery();
idnumber = true;
}
catch (Exception e)
{
strMensajeError = e.Message;
return(false);
}
return(idnumber);
}
/// <summary>
/// Get Player Abilities list
/// </summary>
/// <param name="playerId"></param>
/// <returns></returns>
public static Abilities GetPlayerAbility(int playerId, SkillType skillType)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
Abilities abilities = new Abilities();
using (MySqlCommand command = connection.CreateCommand())
{
command.CommandText = "GAME_PLAYER_ABILITY_GET";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@in_PlayerId", playerId);
command.Parameters.AddWithValue("@in_AbilityType", skillType);
var reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
int abilityId = reader.GetInt32(1);
int abilityLevel = reader.GetInt32(2);
abilities.AddAbility(abilityId, abilityLevel);
}
}
}
connection.Close();
return abilities;
}
}
public void ShouldStoreEndpoint()
{
ClearEndpointsTable();
ICollectorDAL dal = new DAL(testDbConnectionString);
var expectedTimestamp = DateTime.Now;
dal.StoreDhcpEndpoint("0123456", "192.168.1.1", "mike", expectedTimestamp, "sensor1");
expectedTimestamp = expectedTimestamp.ToUniversalTime();
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(testDbConnectionString))
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM endpoint_latest";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var record = (IDataRecord)reader;
var mac = record[0].ToString();
var ip = record[1].ToString();
var hostname = record[2].ToString();
var timestamp = (DateTime)record[3];
var detectedBy = record[4].ToString();
Assert.Equal("0123456", mac);
Assert.Equal("192.168.1.1", ip);
Assert.Equal("mike", hostname);
Assert.True((expectedTimestamp - timestamp) < TimeSpan.FromSeconds(1));
Assert.Equal("sensor1", detectedBy);
}
}
}
}
public static Task<bool> LoadNewExamination(string opinion)
{
MySqlConnection сonnection = new MySqlConnection(Worker.ConnectionString);
MySqlCommand command = сonnection.CreateCommand();
Task<bool> task = new Task<bool>(() =>
{
command.CommandText =
("INSERT INTO examination (DateOnset, Opinion, Specialist, patient_idPatient, worker_idWorker) " +
"VALUES('" + DateTime.Now.ToString(CultureInfo.InvariantCulture) + "', '" + opinion + "', '" +
Worker.Specialty + "', '" + Patient.IdPatient + "', '" + Worker.IdWorker + "')");
try
{
сonnection.Open();
command.ExecuteNonQuery();
}
catch (Exception e)
{
WpfMessageBox.Show("Помилка підключення. \n Звернітся до адміністратора!");
Exceptions.AddException(e);
return false;
}
return true;
});
task.Start();
return task;
}
private string getPatternFromDB(string variable)
{
String varRegex = "";
try
{
checkAndOpenConn();
MySqlCommand cmd = _conn.CreateCommand();
cmd.CommandText = "select regex_pattern from variable_regex_map where variable_name='" + variable + "'";
MySqlDataAdapter dap = new MySqlDataAdapter(cmd);
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
varRegex = dataReader.GetString("regex_pattern");
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(varRegex);
}
public static bool IsFullPublic()
{
bool isPublic = false;
bool isFullyPublic = false;
using (MySqlConnection con = new MySqlConnection(Database.ConnectionString))
{
con.Open();
using (MySqlCommand command = con.CreateCommand())
{
command.Parameters.AddWithValue("@docID", requestID);
command.CommandText =
"SELECT public,publicPassword FROM documents WHERE docID = @docID";
MySqlDataReader r = command.ExecuteReader();
while(r.Read())
{
isPublic = Convert.ToBoolean(r["public"]);
if(isPublic && (r["publicPassword"].ToString() == "" || r["publicPassword"].ToString() == "public"))
{
isFullyPublic = true;
}
}
}
}
return isFullyPublic;
}
private void aggiornaDatiAnagrafici( )
{
string dbpwd = "lightwave";
MySql.Data.MySqlClient.MySqlConnection dbConn = new MySql.Data.MySqlClient.MySqlConnection("Persist Security Info=False;server=localhost;database=phidelity;uid=root;password="******"UPDATE phidelity.cards SET nome ='" + tbNome.Text.Replace("'", "''") + "', cognome=" + "'" + tbCognome.Text.Replace("'", "''") + "'" +
" ,telefono= '" + tbTelefono.Text + "'" +
" ,email= '" + tbEmail.Text + "' WHERE barcode = '" + barcode + "';";
Console.WriteLine("MySQL command= {0}", cmd.CommandText);
try
{
dbConn.Open();
}
catch (Exception erro)
{
MessageBox.Show("Erro" + erro);
this.Close();
}
int rc_updated = cmd.ExecuteNonQuery();
dbConn.Close();
}
public static void mysqlVer(MySqlConnection mySqlConnection)
{
MySqlCommand mySqlCommand = mySqlConnection.CreateCommand ();
mySqlCommand.CommandText = "SELECT * FROM categoria";
MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader ();
Console.WriteLine ("FieldCount = {0}", mySqlDataReader.FieldCount);
for (int index = 0; index < mySqlDataReader.FieldCount; index++)
{
Console.WriteLine ("Column {0} = {1}", index, mySqlDataReader.GetName (index));
}
while (mySqlDataReader.Read())
{
object id = mySqlDataReader ["id"];
object nombre = mySqlDataReader ["nombre"];
Console.Write ("\n{0}, {1}", id, nombre);
}
Console.WriteLine ("\n\nPress any key to continue...");
Console.Read ();
mySqlDataReader.Close ();
}
/// <summary>
/// Voegt data toe aan een nieuwe regel in de database en commit deze transactie.
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlParameters"></param>
/// <returns></returns>
//insert
public int Insert(string sql, List <MySqlParameter> sqlParameters)
{
int affected;
this.Open();
try
{
MySqlTransaction transaction = conn.BeginTransaction();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
foreach (MySqlParameter param in sqlParameters)
{
cmd.Parameters.Add(param);
}
affected = cmd.ExecuteNonQuery();
transaction.Commit();
return(affected);
}
catch (Exception)
{
return(0);
}
finally
{
this.Close();
}
}
