private void DoSQLQuery()
{
try
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "SELECT * FROM database.tbl_user WHERE tbl_user.email='" + emailTextBox.Text + "' AND tbl_user.password='******' AND tbl_user.estActif='true'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
name = "";
userRole = "";
while (reader.HasRows && reader.Read())
{
name = reader.GetString(reader.GetOrdinal("username")) + " " +
reader.GetString(reader.GetOrdinal("password"));
userRole = reader.GetString(reader.GetOrdinal("codeuser"));
userName = reader.GetString(reader.GetOrdinal("lastname")) + " " +
reader.GetString(reader.GetOrdinal("firstname"));
userNom = reader.GetString(reader.GetOrdinal("username"));
firstName = reader.GetString(reader.GetOrdinal("firstname"));
lastName = reader.GetString(reader.GetOrdinal("lastname"));
email = reader.GetString(reader.GetOrdinal("email"));
age = reader.GetString(reader.GetOrdinal("age"));
userId = reader.GetString(reader.GetOrdinal("id"));
pass = reader.GetString(reader.GetOrdinal("password"));
role = reader.GetString(reader.GetOrdinal("role"));
detail = reader.GetString(reader.GetOrdinal("detail"));
}
if (reader.HasRows)
{
Session["uname"] = name;
Session["urole"] = userRole;
Session["utname"] = userName;
Session["nom"] = userNom;
Session["role"] = role;
Session["detail"] = detail;
Session["ufirst"] = firstName;
Session["ulast"] = lastName;
Session["uemail"] = email;
Session["uage"] = age;
Session["Id"] = userId;
Session["upass"] = pass;
Response.BufferOutput = true;
Response.Redirect("~/Home/HomePage.aspx", false);
}
else
{
errorLabel.Text = "Invalid user";
}
reader.Close();
conn.Close();
}
catch (Exception e)
{
errorLabel.Text = e.ToString();
}
}
protected void submitEventMethod(object sender, EventArgs e)
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["webAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
// CHANGE TO UPDATE STATEMENT
queryStr = "SELECT * FROM app2000.user WHERE email='" + emailTextBox.Text /*Funker ikke med live share*/
+ "' AND password ='******'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
name = "";
while (reader.HasRows && reader.Read())
{
name = reader.GetString(reader.GetOrdinal("email"));
}
if (reader.HasRows)
{
Session["uname"] = name;
Response.BufferOutput = true;
// Response.Redirect("LoggedIn.aspx", false);
}
else
{
passwordTextBox.Text = "invalid user";
}
reader.Close();
conn.Close();
}
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;
}
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 ArrayList getFriends(long ID)
* {
* MySql.Data.MySqlClient.MySqlConnection conn;
* string myConnectionString = ConfigurationManager.ConnectionStrings["localDB"].ConnectionString;
* conn = new MySql.Data.MySqlClient.MySqlConnection();
* try
* {
* conn.ConnectionString = myConnectionString;
* conn.Open();
* ArrayList personArrayL = new ArrayList();
* MySql.Data.MySqlClient.MySqlDataReader mySQLReader = null;
*
* String sqlString = "SELECT FriendFBID FROM friends WHERE UserFBID=" + ID;
*
* MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
*
*
* mySQLReader = cmd.ExecuteReader();
* while (mySQLReader.Read())
* {
* Friends f = new Friends();
* f.FriendFBID = mySQLReader.GetInt64(0);
* personArrayL.Add(f);
* }
*
*
* return personArrayL;
* }
* catch (MySql.Data.MySqlClient.MySqlException ex)
* {
* throw ex;
* }
* finally
* {
* conn.Close();
* }
*
*
* }*/
public String saveFriend(Friends personToSave)
{
MySql.Data.MySqlClient.MySqlConnection conn;
//string myConnectionString = ConfigurationManager.ConnectionStrings["localDB"].ConnectionString;
string myConnectionString = connectionString;
conn = new MySql.Data.MySqlClient.MySqlConnection();
try
{
conn.ConnectionString = myConnectionString;
conn.Open();
String sqlString = "INSERT INTO Friends (UserFBID, FriendFBID, Status)"
+ "VALUES('" + personToSave.UserFBID + "','" + personToSave.FriendFBID + "'," + 0 + ");";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
cmd.ExecuteNonQuery();
return(personToSave.UserFBID);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
private void button15_Click(object sender, EventArgs e)
{
ArrayList alreadyBanned = new ArrayList();
alreadyBanned.AddRange(ArrayList.Adapter(ConfigLoadFunc.TimeBanLoad("banned.xml")));
Int64 unix_timebanned = (Int64)(DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0)).TotalSeconds;
string connectionString;
connectionString = "Data Source=" + databaseHost + ";User Id=" + databaseUser + ";Password="******";Database=" + realmDatabase;
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn.Open();
string query = "UPDATE account SET banned = \"1\" WHERE username = \"" + Convert.ToString(listBox1.SelectedItem) + "\"";
MySql.Data.MySqlClient.MySqlCommand resQuery = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
resQuery.ExecuteNonQuery();
conn.Close();
string tobeBanned = Convert.ToString(listBox1.SelectedItem);
Int64 timeBan = unix_timebanned + (Convert.ToInt64(timebanBox.Text) * 60);
alreadyBanned.Add(tobeBanned + @"%" + timeBan + @";");
if ((string)alreadyBanned[0] == "none")
{
alreadyBanned.RemoveAt(0);
}
MainFunctions.WriteBans((string[])alreadyBanned.ToArray(typeof(string)));
WriteLog("Account: " + listBox1.SelectedItem + " BANNNED FOR " + timebanBox.Text + " minutes!", Color.Red);
button12_Click(null, null);
}
public ActionResult Contact()
{
var users = new List <User>();
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString.Get("EscademyDB")))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT FirstName, LastName, ProfilePicture, Id FROM esc_accounts WHERE Id = 1 OR Id = 2";
var reader = cmd.ExecuteReader();
while (reader.Read())
{
users.Add(new User()
{
Id = reader.GetInt32("Id"),
FirstName = reader.GetString("FirstName"),
LastName = reader.GetString("LastName"),
Picture = reader.GetString("ProfilePicture")
});
}
}
conn.Close();
}
ViewBag.users = users;
return(View());
}
private void button11_Click(object sender, EventArgs e)
{
label2.Text = "";
string connectionString;
connectionString = "Data Source=" + databaseHost + ";User Id=" + databaseUser + ";Password="******";Database=" + realmDatabase;
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn.Open();
string query = "SELECT username FROM account WHERE username=\"" + userBox.Text + "\"";
MySql.Data.MySqlClient.MySqlCommand resQuery = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
MySql.Data.MySqlClient.MySqlDataReader myReader = resQuery.ExecuteReader();
while (myReader.Read())
{
label2.Text = myReader.GetString(0);
}
myReader.Close();
if (label2.Text != "")
{
MessageBox.Show("Account already exists!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
query = "INSERT INTO account(username,password,gmlevel,sessionkey,email,banned,last_ip,failed_logins,locked) VALUES (\"" + userBox.Text + "\",\"" + passBox.Text + "\",\"0\",\"\",\"\",\"0\",\"0\",\"0\",\"0\")";
MySql.Data.MySqlClient.MySqlCommand bQuery = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
bQuery.ExecuteNonQuery();
MessageBox.Show("Account: " + userBox.Text + " created!", "Complete", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
conn.Close();
WriteLog("Account: " + userBox.Text + " Created!", Color.Blue);
}
private void button10_Click(object sender, EventArgs e)
{
string connectionString;
connectionString = "Data Source=" + databaseHost + ";User Id=" + databaseUser + ";Password="******";Database=" + realmDatabase;
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn.Open();
string query = "SELECT banned FROM account WHERE username=\"" + Convert.ToString(listBox1.SelectedItem) + "\"";
MySql.Data.MySqlClient.MySqlCommand resQuery = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
MySql.Data.MySqlClient.MySqlDataReader myReader = resQuery.ExecuteReader();
while (myReader.Read())
{
label2.Text = myReader.GetString(0);
}
myReader.Close();
if (label2.Text == "0")
{
MessageBox.Show("Selected account is not banned!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
query = "UPDATE account SET banned = \"0\" WHERE username = \"" + Convert.ToString(listBox1.SelectedItem) + "\"";
MySql.Data.MySqlClient.MySqlCommand bQuery = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
bQuery.ExecuteNonQuery();
}
conn.Close();
WriteLog("Ban on account: " + listBox1.SelectedItem + " REMOVED!", Color.Red);
button4_Click(null, null);
}
private void button9_Click(object sender, EventArgs e)
{
listBox1.Items.Clear();
string connectionString;
connectionString = "Data Source=" + databaseHost + ";User Id=" + databaseUser + ";Password="******";Database=" + realmDatabase;
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn.Open();
string query = "SELECT username FROM account WHERE banned=\"1\"";
MySql.Data.MySqlClient.MySqlCommand resQuery = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
MySql.Data.MySqlClient.MySqlDataReader myReader = resQuery.ExecuteReader();
while (myReader.Read())
{
listBox1.Items.Add(myReader.GetString(0));
}
myReader.Close();
conn.Close();
pOnline = new string[listBox1.Items.Count];
listBox1.Items.CopyTo(pOnline, 0);
if (listBox1.Items.Count == 0)
{
listBox1.Items.Add("No banned accounts");
}
WriteLog("Get banned list", Color.Gray);
}
public PartialViewResult EditOrderId(EditOrderId model)
{
ViewBag.Title = "Modifica Codice Commessa";
if (!ModelState.IsValid)
{
return(PartialView("_EditOrderId", model));
}
try
{
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["GestionaleDB"].ToString()))
{
conn.Open();
using (var comm = new MySql.Data.MySqlClient.MySqlCommand("UPDATE orders SET OrderId = @NewOrderId WHERE OrderId = @OldOrderId", conn))
{
comm.CommandType = System.Data.CommandType.Text;
comm.CommandTimeout = 3000;
comm.Parameters.AddWithValue("@NewOrderId", model.NewOrderId);
comm.Parameters.AddWithValue("@OldOrderId", model.OldOrderId);
comm.ExecuteNonQuery();
}
conn.Close();
}
ViewBag.Message = "Modificato con Successo!";
ViewBag.ReturnUrl = "/Orders/Index";
return(PartialView("../Shared/_Success"));
}
catch (Exception ex)
{
ModelState.AddModelError("", ex.MySqlExMessage());
return(PartialView("_EditOrderId", model));
}
}
// 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);
}
}
//Verileri SQL komutuyla çekmek için kullanılan fonksiyon
static public DataSet getDataSet(string query, string tableName)
{
try
{
//DataSet sınıfından ds isminde nesne oluşturdu
//Veritabanı bağlantısı oluşturdu
//DataAdapter oluşturdu
DataSet ds = new DataSet();
Mycon mycnct = DatabaseInfo.getConnection();
Myad myadapter = new Myad();
//
//Gönderilen SQL komutunu DataAdapter yardımıyla çekti
//Veritabanı bağlantısını açtı
//Çekilen verileri DataSet'in içerisine doldurdu
//Veritabanı bağlantısını kapattı
//DataSet'i geri döndürdü
myadapter.SelectCommand = new Mycom(query, mycnct);
mycnct.Open();
myadapter.Fill(ds, tableName);
mycnct.Close();
return(ds);
//
}
catch (Exception ex)
{
//Eğer try kısmında hata alırsak çalışması gereken kod
MetroMessageBox.Show(data_entry.ActiveForm, ex.Message, "Database Error!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(null);
//
}
}
private static void MySQL_OpenConnection()
{
string info = "server=localhost;user=root;database=tun;port=3306;password=Camara20";
MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(info);
MySql.Data.MySqlClient.MySqlCommand command;
try
{
Console.WriteLine("MySQL: Connecting to 'tun' database...\n");
connection.Open();
// TODO: executing queries here
command = connection.CreateCommand();
command.CommandText = "DELETE FROM account";
command.ExecuteNonQuery();
Console.WriteLine("MySQL: all 'account' instances were successfully deleted!\n");
command.CommandText = "INSERT INTO account (username, password) VALUES ('ShaBer', 'qwerty');";
command.ExecuteNonQuery();
Console.WriteLine("MySQL: 'account' inserted successfully!\n");
}
catch (Exception ex)
{
Console.WriteLine("MySQL: An error occured while atempting to connect to 'tun'");
Console.WriteLine($"MySQL: - {ex.ToString()}\n");
}
connection.Close();
Console.WriteLine("MySQL: Closing connection");
}
public static MyResult SqlNoneQuery(string sql, string connection)
{
MyResult result = new MyResult();
try
{
MySql.Data.MySqlClient.MySqlConnection connRC = new MySql.Data.MySqlClient.MySqlConnection(connection);
MySql.Data.MySqlClient.MySqlCommand commRC = new MySql.Data.MySqlClient.MySqlCommand(sql, connRC);
connRC.Open();
try
{
commRC.ExecuteNonQuery();
result.HasError = false;
}
catch (Exception ex)
{
result.ErrorText = ex.Message;
result.HasError = true;
}
connRC.Close();
}
catch (Exception ex)
{
result.ErrorText = ex.Message;
result.HasError = true;
}
return(result);
}
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();
}
}
private void buttonSendTest_Click(object sender, EventArgs e)
{
string Connect = "Database=u0354899_diplom;Data Source=31.31.196.162;User Id=u0354899_vlad;Password=vlad19957;charset=cp1251";
MySql.Data.MySqlClient.MySqlConnection myConnection = new MySql.Data.MySqlClient.MySqlConnection(Connect);
MySql.Data.MySqlClient.MySqlCommand myCommand = new MySql.Data.MySqlClient.MySqlCommand();
myConnection.Open();
myCommand.Connection = myConnection;
if (CheckJPEG == true)
{
checkFolderFtp(url);
// читаем файл в строку
//string fileText = System.IO.File.ReadAllText(filename);
WebClient myWebClient = new WebClient();
Uri ftp_path = new Uri("ftp://*****:*****@31.31.196.162" + url + fileName); // file.txt - файл, который будет в конечном итоге залит; FTPLOGIN - логин к FTP; PASSWORD - пароль к FTP; LOGIN и PASSWORD разделяются двоеточием.
myWebClient.UploadFile(ftp_path, urlName); // anyfile.txt - загружаемый файл на FTP; C:/Files... - путь к загружаемому файлу; ftp_path - конечный путь и имя файла, которое будет на FTP сервере.
string urlFileJPEG = "http://www.imtis.ru/task/jpeg/" + Main.LoginGlobal + "/" + fileName;
myCommand.CommandText = string.Format("INSERT INTO task (TextTask,PicTask,idtest) VALUES('{0}','{1}','{2}')", textBoxVopros.Text, urlFileJPEG, Teacher.idtests);
myCommand.Prepare(); //подготавливает строку
myCommand.ExecuteNonQuery(); //выполняет запрос
CheckJPEG = false;
}
else
{
myCommand.CommandText = string.Format("INSERT INTO task (TextTask,idtest) VALUES('{0}','{1}')", textBoxVopros.Text, Teacher.idtests);
myCommand.Prepare(); //подготавливает строку
myCommand.ExecuteNonQuery(); //выполняет запрос
}
textBoxVopros.Text = "";
myConnection.Close();
}
/// <summary>
/// Для выполнения запросов к MySQL с возвращением 1 параметра.
/// </summary>
/// <param name="sql">Текст запроса к базе данных</param>
/// <param name="connection">Строка подключения к базе данных</param>
/// <returns>Возвращает значение при успешном выполнении запроса, текст ошибки - при ошибке.</returns>
public static MyResult SqlScalar(string sql, string connection)
{
MyResult result = new MyResult();
try
{
MySql.Data.MySqlClient.MySqlConnection connRC = new MySql.Data.MySqlClient.MySqlConnection(connection);
MySql.Data.MySqlClient.MySqlCommand commRC = new MySql.Data.MySqlClient.MySqlCommand(sql, connRC);
connRC.Open();
try
{
result.ResultText = commRC.ExecuteScalar().ToString();
result.HasError = false;
}
catch (Exception ex)
{
result.ErrorText = ex.Message;
result.HasError = true;
}
connRC.Close();
}
catch (Exception ex)//Этот эксепшн на случай отсутствия соединения с сервером.
{
result.ErrorText = ex.Message;
result.HasError = true;
}
return result;
}
public static void EditSptPassword(string passwordStr)
{
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();
int idSptinfo = 1;
msqlCommand.CommandText = "UPDATE sptinfo SET password='******' WHERE id_sptinfo='" + idSptinfo + "'; ";
msqlCommand.ExecuteNonQuery();
}
catch (Exception er)
{
//MessageBox.Show(er.Message);
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
static void Main(string[] args)
{
dbconnect();
Console.WriteLine("Email Server Connected");
listen = new TcpListener(IPAddress.Parse("127.0.0.1"), 1010);
listen.Start();
Byte[] bytes = new Byte[256];
string data = null;
while (true)
{
Console.WriteLine("Waiting for a connection ...");
TcpClient client = listen.AcceptTcpClient();
Console.WriteLine("Client connected");
data = null;
NetworkStream stream = client.GetStream();
int i;
while ((i = stream.Read(bytes, 0, bytes.Length)) != 0)
{
data = Encoding.ASCII.GetString(bytes, 0, i);
Console.WriteLine(data);
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.CommandText = string.Format("UPDATE `users` SET `ev` = '1' WHERE `users`.`email` =\"{0}\";", data);
cmd.Connection = connect;
cmd.ExecuteNonQuery();
connect.Close();
}
}
}
private void EditSptInfo(SettingsData returnEditedsettingsData)
{
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();
int idSptinfo = 1;
msqlCommand.CommandText = "UPDATE sptinfo SET name='" + returnEditedsettingsData.Name + "', address='" + returnEditedsettingsData.Address + "', phone='" +
returnEditedsettingsData.Phone + "', bill_disclaimer='" + returnEditedsettingsData.BillDisclaimer + "', invoice_prefix='" + returnEditedsettingsData.InvoicePrefix +
"' WHERE id_sptinfo='" + idSptinfo + "'; ";
msqlCommand.ExecuteNonQuery();
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
private void add_read_send(object sender, EventArgs e)
{
try
{
//Connect to Database
my.MySqlClient.MySqlConnection conn = connectToDatabase();
//Add Data to table using LocalSetupInsert - needs altering
LocalSetupInsert(conn);
//Read Data from table using LocalSetupGet
LocalSetupGet(conn);
//Save data into csv file
//save_into_csv(results);
//close connection to table
if (conn != null)
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
//call send_email function
send_email("*****@*****.**");
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
protected void saveToDB(String message)
{
MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
String queryStr;
ID = x.getUserIDcaseID();
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["projectConnectionString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
queryStr = "";
//Inset the message along wiht user_id and case_id into the databases.
queryStr = "INSERT INTO `project`.`messages` (`alphauser_id`, `case_id`, `messages_text`) VALUES (?x0, ?x1 , ?text)";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.Parameters.AddWithValue("?x0", ID[0]);
cmd.Parameters.AddWithValue("?x1", ID[1]);
cmd.Parameters.AddWithValue("?text", message);
try
{
conn.Open();
cmd.ExecuteReader();
conn.Close();
}
catch (Exception e)
{
}
}
public List <PlayerDal> GetPlayers()
{
List <PlayerDal> spelers = new List <PlayerDal>();
string serverIp = "Localhost";
string username = "******";
string password = "******";
string databaseName = "basketbal";
string dbConnectionString = string.Format("server={0};uid={1};pwd={2};database={3};", serverIp, username, password, databaseName);
string query = "SELECT * FROM speler";
var conn = new MySql.Data.MySqlClient.MySqlConnection(dbConnectionString);
conn.Open();
var cmd = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
spelers.Add(new PlayerDal(
Convert.ToString(reader["LastName"].ToString()),
Convert.ToString(reader["FirstName"].ToString()),
Convert.ToString(reader["Active Team"].ToString()),
Convert.ToInt32(reader["Games"].ToString()),
Convert.ToDouble(reader["Points"].ToString()),
Convert.ToDouble(reader["Rebounds"].ToString()),
Convert.ToDouble(reader["Assists"].ToString()),
Convert.ToDouble(reader["Blocks"].ToString())));
}
conn.Close();
return(spelers);
}
public static MyResultData SqlReturnDataset(string sql, string connection)
{
MyResultData result = new MyResultData();
try
{
MySql.Data.MySqlClient.MySqlConnection connRC = new MySql.Data.MySqlClient.MySqlConnection(connection);
MySql.Data.MySqlClient.MySqlCommand commRC = new MySql.Data.MySqlClient.MySqlCommand(sql, connRC);
connRC.Open();
try
{
MySql.Data.MySqlClient.MySqlDataAdapter AdapterP = new MySql.Data.MySqlClient.MySqlDataAdapter();
AdapterP.SelectCommand = commRC;
DataSet ds1 = new DataSet();
AdapterP.Fill(ds1);
result.ResultData = ds1.Tables[0];
}
catch (Exception ex)
{
result.HasError = true;
result.ErrorText = ex.Message;
}
connRC.Close();
}
catch (Exception ex)
{
result.ErrorText = ex.Message;
result.HasError = true;
}
return(result);
}
public List <UserDal> GetUsers()
{
List <UserDal> users = new List <UserDal>();
string serverIp = "Localhost";
string username = "******";
string password = "******";
string databaseName = "basketbal";
string dbConnectionString = string.Format("server={0};uid={1};pwd={2};database={3};", serverIp, username, password, databaseName);
string query = "SELECT * FROM user";
var conn = new MySql.Data.MySqlClient.MySqlConnection(dbConnectionString);
conn.Open();
var cmd = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
users.Add(new UserDal(
Convert.ToString(reader["Username"].ToString()),
Convert.ToString(reader["Password"].ToString())));
}
conn.Close();
return(users);
}
// 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 bool TestDBConnection()
{
try
{
using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("", con))
{
con.Open();
}
}
catch (Exception ex)
{
con.Close();
throw new Exception("Fout bij de verbinding met de database!");
}
con.Close();
return(true);
}
public void Ejecutar(string sql)
{
MySql.Data.MySqlClient.MySqlConnection Cnn = new MySql.Data.MySqlClient.MySqlConnection(ConfigurationManager.ConnectionStrings["CNN"].ToString());
try
{
MySql.Data.MySqlClient.MySqlCommand Cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, Cnn);
Cnn.Open();
Cmd.ExecuteNonQuery();
Cnn.Close();
}
catch (Exception ed)
{
Helper.RegistrarEvento("En funcion Regresunregistro : " + ed.Message);
Cnn.Close();
}
}
private void Form1_Load(object sender, EventArgs e)
{
var cn = new MySql.Data.MySqlClient.MySqlConnection();
cn.ConnectionString = "Server=mysql2.altaortopedia.com.br;Database=altaortopedia;Uid=altaortopedia;Pwd=alta@38072264";
cn.Open();
cn.Close();
}
private void button2_Click(object sender, EventArgs e)
{
if (connection == null)
{
connection = new MySql.Data.MySqlClient.MySqlConnection();
connection.ConnectionString = Settings.Default.wowahtPublicConnectionString;
}
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand("GetStatisticsForItemFromAuction", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("GetItemID", ItemStatisticsID.Text));
command.Connection.Open();
MySql.Data.MySqlClient.MySqlDataReader reader;
reader = command.ExecuteReader();
if (reader.Read())
{
GlobalMedianDisplay.Text = reader["Median"].ToString();
GlobalMeanDisplay.Text = reader["Mean"].ToString();
GlobalModeDisplay.Text = reader["Mode"].ToString();
GlobalStDevDisplay.Text = reader["Std. Deviation"].ToString();
MerchantBuyLabel.Text = reader["Buy_Price"].ToString();
if (MerchantBuyLabel.Text == "0")
{
MerchantBuyLabel.Text = "Unavailable";
}
MerchantSellLabel.Text = reader["Sell_Price"].ToString();
}
else
{
GlobalMedianDisplay.Text = "Invalid ID";
GlobalMeanDisplay.Text = "Invalid ID";
GlobalModeDisplay.Text = "Invalid ID";
GlobalStDevDisplay.Text = "Invalid ID";
MerchantBuyLabel.Text = "Invalid ID";
MerchantSellLabel.Text = "Invalid ID";
}
reader.Close();
MySql.Data.MySqlClient.MySqlCommand command2 = new MySql.Data.MySqlClient.MySqlCommand("GetRecipesForItem", connection);
command2.CommandType = CommandType.StoredProcedure;
command2.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("GetItemID", ItemStatisticsID.Text));
DataTable dt = new DataTable();
dt.Load(command2.ExecuteReader(), LoadOption.OverwriteChanges);
ItemRecipesGrid.DataSource = dt;
connection.Close();
}
protected void RegisterUser()
{
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
query = "INSERT INTO collectionapp.users (`FirstName`, `LastName`, `Email`, `Username`, `PWord`) VALUES ('" + TextBox3.Text + "', '" + TextBox4.Text + "', '" + TextBox5.Text + "', '" + TextBox6.Text + "', '" + TextBox7.Text + "')";
cmd = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
cmd.ExecuteReader();
conn.Close();
}
public void CreateMySqlCommand(string myExecuteQuery, MySql.Data.MySqlClient.MySqlConnection myConnection)
{
try
{
MySql.Data.MySqlClient.MySqlCommand myCommand = new MySql.Data.MySqlClient.MySqlCommand(myExecuteQuery, myConnection);
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "ERRO", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
connection.Close();
connection.Dispose();
}
}
public void TestKTUConnection()
{
//var conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=joe;Password=secret;Database=joedata;");
// Server = localhost; Port = 5432; User Id = postgres; Password = admin; Database = ktu_bakalauras;
var conn = new MySql.Data.MySqlClient.MySqlConnection(@"Server = db.if.ktu.lt; User Id = tauvai; Password = eivuShooYaeZagh1; Database = tauvai");
conn.Open();
conn.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
conn = new MySql.Data.MySqlClient.MySqlConnection(constr);
conn.Open();
data = "insert into emp_training values ('" + DropDownList1.SelectedValue + "','" + DropDownList2.SelectedValue + "')";
cmd = new MySql.Data.MySqlClient.MySqlCommand(data, conn);
cmd.ExecuteNonQuery();
conn.Close();
}
private void updatehireEmployee(double newsal)
{
conn = new MySql.Data.MySqlClient.MySqlConnection(constr);
conn.Open();
data = "update hr.hire_emp set Salary = '" + newsal + "' where EmployeeID = '" + Convert.ToInt32(DropDownList1.SelectedValue) + "'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(data, conn);
cmd.ExecuteNonQuery();
conn.Close();
}
private void button1_Click_1(object sender, EventArgs e)
{
MySql.Data.MySqlClient.MySqlConnection myconn = null;
myconn = new MySql.Data.MySqlClient.MySqlConnection("Database=qsgj;Data Source=127.0.0.1;User Id=root;Password=;pooling=false;CharSet=utf8;port=3306");
myconn.Open();
myconn.Close();
}
public static int Execute(string sql)
{
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectString()))
{
conn.Open();
var cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn);
int i = cmd.ExecuteNonQuery();
conn.Close();
return i;
}
}
internal void TruncateMinuteWiseMeasures()
{
using (var mySqlConn = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
mySqlConn.Open();
var sqlCom = mySqlConn.CreateCommand();
sqlCom.CommandText = @"TRUNCATE TABLE minute_wise;";
sqlCom.ExecuteNonQuery();
mySqlConn.Close();
}
}
public static DataTable Query(string sql)
{
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectString()))
{
conn.Open();
var adapter = new MySql.Data.MySqlClient.MySqlDataAdapter(sql, conn);
var ds = new DataSet();
var reader = adapter.Fill(ds);
conn.Close();
return ds.Tables[0];
}
}
/*Devolvemos un datatable con el resultado de la consulta que esta como paramatro en la bd cuyo string de conexion esta el parametro conexion del fichero de confi
guracon*/
public System.Data.DataTable pasar_consulta_datatable(string consulta)
{
MySql.Data.MySqlClient.MySqlConnection mscon = new MySql.Data.MySqlClient.MySqlConnection(Properties.Settings.Default.conexion);
MySql.Data.MySqlClient.MySqlDataAdapter mda = new MySql.Data.MySqlClient.MySqlDataAdapter(consulta, mscon);
mda.SelectCommand.CommandTimeout = 0;
System.Data.DataTable dt = new System.Data.DataTable();
mda.Fill(dt);
mscon.Close();
return dt;
}
public int Mysql_File_Save(int PropertyObject, int FileSize, string FileName, string Content_Type,
int Height, int Width, byte[] Image, byte[] ImagePreview, bool IsDeleted)
{
int result = -1;
using (MySql.Data.MySqlClient.MySqlConnection oConn =
new MySql.Data.MySqlClient.MySqlConnection(this.connStr))
{
oConn.Open();
MySql.Data.MySqlClient.MySqlCommand cmd = oConn.CreateCommand();
cmd.Connection = oConn;
//Add new
//oCommand.CommandText = "insert into cust_file(customer_id, filename, filedata, contenttype, length) " +
// "values( ?in_customer_id, ?in_filename, ?in_filedata, ?in_contenttype, ?in_length)";
//INSERT INTO myrealty.images (id, img) VALUES (<INT(11)>, <LONGBLOB>);
cmd.CommandText = @"SET GLOBAL max_allowed_packet=16*1024*1024;
INSERT INTO ObjectImages (PropertyObject_Id, FileSize, FileName, Content_Type, Height, Width,
Image, ImagePreview, IsDeleted) VALUES (?PropertyObject, ?FileSize, ?FileName, ?Content_Type, ?Height, ?Width,
?Image, ?ImagePreview, ?IsDeleted); select last_insert_id();";
//oCommand.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("?PropertyObject", PropertyObject);
cmd.Parameters.Add("?FileSize", FileSize);
cmd.Parameters.Add("?FileName", FileName);
cmd.Parameters.Add("?Content_Type", Content_Type);
cmd.Parameters.Add("?Height", Height);
cmd.Parameters.Add("?Width", Width);
cmd.Parameters.Add("?Image", MySql.Data.MySqlClient.MySqlDbType.LongBlob);
cmd.Parameters["?Image"].Value = Image;
cmd.Parameters.Add("?ImagePreview", MySql.Data.MySqlClient.MySqlDbType.LongBlob);
cmd.Parameters["?ImagePreview"].Value = ImagePreview;
cmd.Parameters.Add("?IsDeleted", IsDeleted);
result = Convert.ToInt32(cmd.ExecuteScalar());
oConn.Close();
}
return result;
}
/*ejecuta la consulta que se pasa como parametro cogiendo la base de datos cuyo string de conexion esta en el parametro
conexion del fichero de configuracion*/
public void ejecutar_sql(string sql)
{
try
{
MySql.Data.MySqlClient.MySqlConnection mscon = new MySql.Data.MySqlClient.MySqlConnection(Properties.Settings.Default.conexion);
mscon.Open();
MySql.Data.MySqlClient.MySqlCommand mscom = new MySql.Data.MySqlClient.MySqlCommand(sql, mscon);
mscom.ExecuteNonQuery();
mscon.Close();
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
LinkException le = new LinkException(e.Message);
throw le;
}
}
internal void TruncateAllTables()
{
using (var mySqlConn = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
mySqlConn.Open();
var sqlCom = mySqlConn.CreateCommand();
sqlCom.CommandText = @"
TRUNCATE TABLE plants;
TRUNCATE TABLE user_has_plant;
TRUNCATE TABLE kwh_by_day;
TRUNCATE TABLE user_has_plant;
TRUNCATE TABLE measure;
TRUNCATE TABLE temporary_measure;";
sqlCom.ExecuteNonQuery();
mySqlConn.Close();
}
}
private void Button_Click(object sender, RoutedEventArgs e)
{
MySql.Data.MySqlClient.MySqlConnection msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost; user id=root;password=technicise;database=my_schema;persist security info=false");
//open the connection
if (msqlConnection.State != System.Data.ConnectionState.Open)
msqlConnection.Open();
//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;
//define the command text
msqlcommand.CommandText = "insert into phonebook(id,name,mobileno)" + "values(@id,@name,@mobileno)";
//add values provided by user
msqlcommand.Parameters.AddWithValue("@id",id.Text);
msqlcommand.Parameters.AddWithValue("@name", name.Text);
msqlcommand.Parameters.AddWithValue("@mobileno", mobileno.Text);
msqlcommand.ExecuteNonQuery();
//close the connection
msqlConnection.Close();
//empty the text boxes
id.Text = null;
name.Text = null;
mobileno.Text = null;
MessageBox.Show("Info Added");
}
protected override System.Data.IDbCommand getCommand(string storedProcedure)
{
MySql.Data.MySqlClient.MySqlCommand mCommand;
if(CommandsCollection.Contains(storedProcedure))
{
mCommand = (MySql.Data.MySqlClient.MySqlCommand) CommandsCollection[storedProcedure];
}
else
{
MySql.Data.MySqlClient.MySqlConnection Conn = new MySql.Data.MySqlClient.MySqlConnection(this.ConnectionString);
Conn.Open();
mCommand = new MySql.Data.MySqlClient.MySqlCommand(storedProcedure,Conn);
mCommand.CommandType = System.Data.CommandType.StoredProcedure;
MySql.Data.MySqlClient.MySqlCommandBuilder.DeriveParameters(mCommand);
Conn.Close();
Conn.Dispose();
CommandsCollection.Add(storedProcedure, mCommand);
}
mCommand.Connection = (MySql.Data.MySqlClient.MySqlConnection) this.Connection;
return (System.Data.IDbCommand) mCommand;
}
public static System.Net.IPEndPoint GetGPSGatewayRouter(string gpsCode)
{
MySql.Data.MySqlClient.MySqlConnection gpsDBCon = null;
try
{
gpsDBCon = new MySql.Data.MySqlClient.MySqlConnection();
gpsDBCon.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SelpConnectionStr"].ToString();
gpsDBCon.Open();
System.Net.IPEndPoint g = null;
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.CommandText = "select gps_cachetable.value from gps_cachetable where gps_cachetable.key='gw" + gpsCode + "'";
cmd.CommandType = CommandType.Text;
cmd.Connection = gpsDBCon;
System.Data.DataSet ds = new DataSet();
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
gpsDBCon.Close();
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
string config = dr[0].ToString().Trim();
string[] ss = config.Split(":".ToCharArray());
g = new System.Net.IPEndPoint(System.Net.IPAddress.Parse(ss[0].Trim()), int.Parse(ss[1].Trim()));
}
return g;
}
catch (System.Exception ex)
{
PES.Beehive.Logging.Logger.Error("获取" + gpsCode + "网关配置信息失败", null);
throw ex;
}
finally
{
if (gpsDBCon != null)
gpsDBCon.Close();
}
}
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();
}
}
private void ExecuteSqlFromFile(string path)
{
try
{
if (File.Exists(path))
{
string sqlText = File.ReadAllText(path);
using (var mySqlConn = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
mySqlConn.Open();
var sqlCom = mySqlConn.CreateCommand();
sqlCom.CommandText = sqlText;
sqlCom.ExecuteNonQuery();
mySqlConn.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
private void EditVendorPayments(VendorPaymentData _vendorPaymentToEdit)
{
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 = "UPDATE vendor_payment SET payment_amount='" + _vendorPaymentToEdit.paymentAmount + "' WHERE payment_id='" + _vendorPaymentToEdit.paymentId + "'; ";
msqlCommand.ExecuteNonQuery();
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
private static void updateVersionInSQL(long svnVersion)
{
ConsoleLogStartAction(Resources.UILogging.UpdateSQL);
var mysqlHost = ConfigurationManager.AppSettings[AppSettingKeys.MySqlHost];
var mysqlUser = ConfigurationManager.AppSettings[AppSettingKeys.MySqlUser];
var mysqlDatabase = ConfigurationManager.AppSettings[AppSettingKeys.MySqlDatabase];
if (mysqlHost.isNullOrEmpty())
{
Console.WriteLine(Resources.Questions.MySQLHost);
mysqlHost = Console.ReadLine();
}
if (mysqlUser.isNullOrEmpty())
{
Console.WriteLine(Resources.Questions.MySQLUser);
mysqlUser = Console.ReadLine();
}
if (mysqlDatabase.isNullOrEmpty())
{
Console.WriteLine(Resources.Questions.MySQLDatabase);
mysqlDatabase = Console.ReadLine();
}
Console.WriteLine(Resources.Questions.MySQLPassword);
var connexionString = string.Empty;
using (SecureString mysqlPassword = GetPassword())
{
connexionString = string.Format(Program.MySqlConnectionString, mysqlHost, mysqlDatabase, mysqlUser, mysqlPassword.ConvertToUnsecureString());
using (var connexion = new MySql.Data.MySqlClient.MySqlConnection(connexionString))
{
try
{
connexion.Open();
var command = connexion.CreateCommand();
command.CommandText = Program.MySqlUpdate;
command.Parameters.AddWithValue(Program.MySqlVersion, svnVersion);
command.ExecuteNonQuery();
connexion.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(string.Format(Resources.Errors.MySqlUpdate, ex.Message));
}
}
}
ConsoleEndAction();
}
private void btnStartUpload_Click(object sender, RoutedEventArgs e)
{
//define the connection reference
MySql.Data.MySqlClient.MySqlConnection msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost; user id=root;password=technicise;database=photodb;persist security info=false");
//open the connection
if (msqlConnection.State != System.Data.ConnectionState.Open)
msqlConnection.Open();
//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;
//define the command text
msqlcommand.CommandText = "insert into pics(Name, Description, ImgFile,UploadedOn)" + "values(@Name,@Description,@ImgFile,@UploadedOn)";
//add values provided by user
string filepath = txtFileName.Text;
msqlcommand.Parameters.AddWithValue("@Name", filepath);
msqlcommand.Parameters.AddWithValue("@Description", txtDescription.Text);
byte[] imgbytes = File.ReadAllBytes("filepath");
// File.WriteAllBytes("c:\\aaa" + DateTime.Now.ToOADate().ToString() + ".jpg", imgbytes);
msqlcommand.Parameters.AddWithValue("@ImgFile", imgbytes);
msqlcommand.Parameters.AddWithValue("@UploadedOn", DateTime.Now);
msqlcommand.ExecuteNonQuery();
//close the connection
msqlConnection.Close();
//empty the text boxes
//rollTextBox.Text = null;
//nameTextBox.Text = null;
MessageBox.Show("Info Added");
// if (string.IsNullOrWhiteSpace(txtFileName.Text))
// {
// MessageBox.Show("Specifiy a file to upload.", "Upload", MessageBoxButton.OK, MessageBoxImage.Exclamation);
// txtFileName.Focus();
// }
// else if (!File.Exists(txtFileName.Text))
// {
// string message = string.Format("Unable to find '{0}'. Please check the file name and try again.", txtFileName.Text);
// MessageBox.Show(message, "Upload", MessageBoxButton.OK, MessageBoxImage.Exclamation);
// txtFileName.Focus();
// }
// else if (string.IsNullOrWhiteSpace(txtDescription.Text))
// {
// MessageBox.Show("Specify the description of the file to upload.", "Upload", MessageBoxButton.OK, MessageBoxImage.Exclamation);
// txtDescription.Focus();
// }
// else
// {
// try
// {
// // Create the REST request.
// string url = ConfigurationManager.AppSettings["serviceUrl"];
// string requestUrl = string.Format("{0}/UploadPhoto/{1}/{2}", url, System.IO.Path.GetFileName(txtFileName.Text), txtDescription.Text);
// HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(requestUrl);
// request.Method = "POST";
// request.ContentType = "text/plain";
// byte[] fileToSend = File.ReadAllBytes(txtFileName.Text);
// request.ContentLength = fileToSend.Length;
// using (Stream requestStream = request.GetRequestStream())
// {
// // Send the file as body request.
// requestStream.Write(fileToSend, 0, fileToSend.Length);
// requestStream.Close();
// }
// using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
// Console.WriteLine("HTTP/{0} {1} {2}", response.ProtocolVersion, (int)response.StatusCode, response.StatusDescription);
// MessageBox.Show("File sucessfully uploaded.", "Upload", MessageBoxButton.OK, MessageBoxImage.Information);
// this.DialogResult = true;
// }
// catch (Exception ex)
// {
// MessageBox.Show("Error during file upload: " + ex.Message, "Upload", MessageBoxButton.OK, MessageBoxImage.Error);
// }
// }
}
private void Form1_Load(object sender, EventArgs e)
{
MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection("Server=localhost;Port=3306;Database=destination;Uid=root;Pwd=12345;");
MySql.Data.MySqlClient.MySqlConnection sourceConnection = new MySql.Data.MySqlClient.MySqlConnection("Server=localhost;Port=3306;Database=users;Uid=root;Pwd=12345;");
try
{
connection.Open();
sourceConnection.Open();
DataTable table = new DataTable();
MySqlBulkCopy upload = new MySqlBulkCopy();
upload.DestinationTableName = "session";
ColumnMapItemCollection collection = new ColumnMapItemCollection();
ColumnMapItem sessionId = new ColumnMapItem();
ColumnMapItem userId = new ColumnMapItem();
ColumnMapItem dateLogged = new ColumnMapItem();
ColumnMapItem loggedFrom = new ColumnMapItem();
ColumnMapItem active = new ColumnMapItem();
sessionId.DataType = "text";
sessionId.DestinationColumn = "IdSession";
sessionId.SourceColumn = "IdSession";
userId.DataType = "int";
userId.DestinationColumn = "userid";
userId.SourceColumn = "userid";
dateLogged.DataType = "datetime";
dateLogged.DestinationColumn = "dateLogged";
dateLogged.SourceColumn = "dateLogged";
loggedFrom.DataType = "text";
loggedFrom.DestinationColumn = "loggedFrom";
loggedFrom.SourceColumn = "loggedFrom";
active.DataType = "int";
active.DestinationColumn = "active";
active.SourceColumn = "active";
collection.Add(sessionId);
collection.Add(userId);
collection.Add(dateLogged);
collection.Add(loggedFrom);
collection.Add(active);
upload.ColumnMapItems = collection;
upload.DestinationDbConnection = connection;
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand("select idsession,userid,datelogged,loggedfrom,active from session", sourceConnection);
MySql.Data.MySqlClient.MySqlDataReader reader = command.ExecuteReader();
upload.Upload(reader);
reader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
connection.Close();
connection.Dispose();
sourceConnection.Close();
sourceConnection.Dispose();
}
private void ConnectInsertTovendorPaymentTable(VendorPaymentData vendorDataObject)
{
//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();
FeedvendorData(msqlCommand, vendorDataObject);
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
finally
{
msqlConnection.Close();
}
}
private void ShowVendorsList()
{
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False");
//define the connection used by the command object
msqlCommand.Connection = msqlConnection;
if (msqlConnection.State != System.Data.ConnectionState.Open)
msqlConnection.Open();
msqlCommand.CommandText = "SELECT * FROM vendors";
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
_vendorCollection.Clear();
while (msqlReader.Read())
{
VendorsData cusData = new VendorsData();
cusData.vendorName = msqlReader.GetString("vendor_name");
cusData.vendorId = msqlReader.GetString("vendor_id");
cusData.vendorAdress = msqlReader.GetString("vendor_address");
cusData.phoneNumber = msqlReader.GetString("ph_no");
_vendorCollection.Add(cusData);
}
msqlConnection.Close();
}
public static SettingsData FetcheSettingsData()
{
SettingsData settingsDataObject = null;
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 * from sptinfo;";
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
msqlReader.Read();
settingsDataObject = new SettingsData();
settingsDataObject.Address = msqlReader.GetString("address");
settingsDataObject.Name = msqlReader.GetString("name");
settingsDataObject.Phone = msqlReader.GetString("phone");
settingsDataObject.Password = msqlReader.GetString("password");
settingsDataObject.BillDisclaimer = msqlReader.GetString("bill_disclaimer");
settingsDataObject.InvoicePrefix = msqlReader.GetString("invoice_prefix");
//settingsDataObject.sptinfo = msqlReader.GetString("id_sptinfo");
}
catch (Exception er)
{
//Assert//.Show(er.Message);
}
finally
{
//always close the connection
msqlConnection.Close();
}
return settingsDataObject;
}
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
if (msqlConnection.State != System.Data.ConnectionState.Open)
msqlConnection.Open();
TimeSpan diff = (TimeSpan)(endDatePicker.SelectedDate - startDatePicker.SelectedDate);
msqlCommand.CommandText = "SELECT * FROM saleslist where date(saleslist.dateSales) >= 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();
_salesDataCollection.Clear();
while (msqlReader.Read())
{
SalesData salesData = new SalesData();
salesData.customerId = msqlReader.GetString("customerId");
salesData.customerName = msqlReader.GetString("customerName"); //add
salesData.dateSales = msqlReader.GetDateTime("dateSales");
salesData.invoiceNo = msqlReader.GetString("invoiceNo");
salesData.payment = msqlReader.GetDouble("payment");
salesData.totalAmount = msqlReader.GetDouble("totalAmount");
//salesData.serialNo = (_salesDataCollection.Count + 1).ToString();
_salesDataCollection.Add(salesData);
}
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
private void DeleteStock(string stockToDelete)
{
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 = "DELETE FROM stock WHERE id= @vendorIdToDelete";
msqlCommand.Parameters.AddWithValue("@vendorIdToDelete", stockToDelete);
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
private void fetchevendorData()
{
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;
if (msqlConnection.State != System.Data.ConnectionState.Open)
msqlConnection.Open();
msqlCommand.CommandText = "Select * from stock;";
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
_stockCollection.Clear();
while (msqlReader.Read())
{
StockData stockDataObject = new StockData();
//stockDataObject.serialNo = GetCurrentSerialNumber();
stockDataObject.productId = msqlReader.GetString("id");
stockDataObject.purchaseDate = msqlReader.GetDateTime("date_purchased");
stockDataObject.productName = msqlReader.GetString("product_name");
stockDataObject.quantityPurchased = msqlReader.GetDouble("quantity_purchased");
stockDataObject.vendorId = msqlReader.GetString("vendor_id");
stockDataObject.rate = msqlReader.GetDouble("rate");
stockDataObject.vatRate = msqlReader.GetDouble("vat_rate");
stockDataObject.quantityAvailable = msqlReader.GetDouble("quantity_available");
_stockCollection.Add(stockDataObject);
}
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
private void ShowProductNames()
{
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False");
//define the connection used by the command object
msqlCommand.Connection = msqlConnection;
if (msqlConnection.State != System.Data.ConnectionState.Open)
msqlConnection.Open();
msqlCommand.CommandText = "SELECT product_name,id,rate,quantity_available,vat_rate FROM stock group by product_name";
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
_purchaseProductCollection.Clear();
while (msqlReader.Read())
{
StockData currentStock = new StockData();
currentStock.productName = msqlReader.GetString("product_name");
currentStock.rate = msqlReader.GetDouble("rate");
currentStock.productId = msqlReader.GetString("id");
currentStock.quantityAvailable = msqlReader.GetDouble("quantity_available");
currentStock.vatRate = msqlReader.GetDouble("vat_rate");
_purchaseProductCollection.Add(currentStock);
}
msqlConnection.Close();
}