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();
}
}
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();
}
}
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;
}
/// <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;
}
private void button1_Click(object sender, EventArgs e)
{
MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection(string.Format("Server={1};Port={2};Uid={3};Pwd={4};Database={0};", "sagaeco", "127.0.0.001", "3306", "root", "lh630206"));
string sqlstr = string.Format("INSERT INTO `login`(`username`,`password`,`deletepass`) VALUES ('{0}','{1}','{2}')", this.textBox1.Text, GetMD5(this.textBox2.Text), GetMD5(this.textBox3.Text));
con.Open();
MySql.Data.MySqlClient.MySqlHelper.ExecuteNonQuery(con, sqlstr, null);
}
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();
}
}
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;
}
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 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;
}
}
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];
}
}
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();
}
}
private static MySql.Data.MySqlClient.MySqlConnection OpenDbConnection()
{
MySql.Data.MySqlClient.MySqlConnection msqlConnection = null;
msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password="******";database=" + dbmsCurrent + ";persist security info=False");
//open the connection
if (msqlConnection.State != System.Data.ConnectionState.Open)
msqlConnection.Open();
return msqlConnection;
}
/// <summary>
/// Add all associated models to the models data structure prior to calling process
/// </summary>
public Processor()
{
models = new List<Model>();
// Connect to our SQL Server Instance Running Locally
this.sqlserver_conn = new System.Data.SqlClient.SqlConnection();
this.sqlserver_conn.ConnectionString = sqlserver_conn_string;
// Connect to our MySQL Server Instance Running Locally
this.mysql_conn = new MySql.Data.MySqlClient.MySqlConnection();
this.mysql_conn.ConnectionString = mysql_conn_string;
}
public void RunTableList()
{
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(MySql))
{
var dr = new DatabaseSchemaReader.DatabaseReader(connection);
dr.Owner = "sakila";
var schema = dr.ReadAll();
var tableList = dr.TableList();
var tables = dr.AllTables();
var views = dr.AllViews();
Assert.NotEmpty(tableList);
}
}
/*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;
}
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();
}
}
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;
}
private void ConnectFetchFromSaleslistTable(string invoiceNumber)
{
//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();
msqlCommand.CommandText = "SELECT * FROM salesBilling WHERE invoiceNo = @invoiceNo;";
msqlCommand.Parameters.AddWithValue("@invoiceNo", invoiceNumber);
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
double totalVat = 0.0;
while (msqlReader.Read())
{
BillingData billedData = new BillingData();
billedData.amount = msqlReader.GetDouble("amount");
billedData.productName = msqlReader.GetString("description");
billedData.quantity = msqlReader.GetDouble("quantity");
billedData.vat = msqlReader.GetDouble("vat");
billedData.calVat = Convert.ToDouble(billedData.amount) * Convert.ToDouble(billedData.vat) * (.01); ;
totalVat += billedData.calVat;
billedData.rate = msqlReader.GetDouble("rate");
billedData.serialNo = billingItemListView.Items.Count + 1; //msqlReader.GetString("serialNo");
_billingCollection.Add(billedData);
vatAmount.Content = totalVat;
}
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
public void Disconnect()
{
try
{
if (m_connection != null)
{
m_connection.Close();
m_connection = null;
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
public void Connect()
{
m_connString = System.Configuration.ConfigurationManager.ConnectionStrings["db-utos"].ConnectionString;
Disconnect();
try
{
m_connection = new MySql.Data.MySqlClient.MySqlConnection();
m_connection.ConnectionString = m_connString;
m_connection.Open();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
public static DbConnection CreateConnection()
{
try
{
DbConnection dbconn = null;
string connectionString = ConfigurationManager.ConnectionStrings[@"SelpConnectionStr"].ConnectionString;
dbconn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
return dbconn;
}
catch (Exception ex)
{
Logger.Info("GPSDataHelper.CreateConnection:" + ex);
return null;
}
}
/// <summary>
/// Loads XML from local database
/// </summary>
public void ImportFromMySQL(Database.DatabaseConfig xmlSource, Database.DatabaseConfig destination)
{
_xmlSource = xmlSource;
_destination = destination;
XMLImport importer = new XMLImport();
Database.MySQL.Database myDB = new Database.MySQL.Database(_destination);
string connString = string.Format(DBConnectString, _xmlSource.ServerIp, _xmlSource.Schema, _xmlSource.Username, _xmlSource.Password);
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connString))
{
conn.Open();
using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT `fishbaseId`, `genus`, `species`, `xmlSummary`, `xmlPointData`, `xmlCommonNames`, `xmlPhotos` FROM `fishbaseraw`;";
using (MySql.Data.MySqlClient.MySqlDataReader reader = cmd.ExecuteReader())
{
while(reader.Read())
{
Model.FishClass fc = importer.Import(
reader.GetInt32("fishbaseId"),
reader.GetString("genus"),
reader.GetString("species"),
reader.GetString("xmlSummary"),
reader.GetString("xmlPointData"),
reader.GetString("xmlCommonNames"),
reader.GetString("xmlPhotos")
);
if (fc != null)
{
try
{
myDB.CreateFish(fc);
}
catch (Database.Exceptions.CreatureAlreadyExists ex)
{
myDB.UpdateFish(fc);
}
}
}
}
}
}
}
public static User Login(string userName, string password)
{
User u = new User();
using(var db = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString)){
var q = @"select UserId, DisplayName, UserName, EmailId, UserRole from Users where UserName=@username and Password=@password";
var user = db.Query<User>(q, new { username = userName, password = password });
foreach (var uu in user) {
u.UserId = Convert.ToInt32(uu.UserId);
u.UserName = uu.DisplayName;
u.EmailId = uu.EmailId;
u.UserName = uu.UserName;
u.UserRole = uu.UserRole;
}
}
return u;
}
public DbConnection BuildConnection()
{
DbConnection connection = null;
switch (serverType.Value)
{
case SqlServerType.SQLite:
//connection = new System.Data.SQLite.SQLiteConnection(string.Format("Data Source={0};", sqliteMode.Value == SQLiteMode.Memory ? ":memory:" : file.Value.FullName));
break;
case SqlServerType.MySQL:
connection = new MySql.Data.MySqlClient.MySqlConnection(string.Format("Server={0};Port={1};Database={4};Uid={2};Pwd={3};", host.Value, port.Value, user.Value, password.Value, database.Value));
break;
}
return connection;
}
/*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;
}
}
private static int DoRegisterNewEmployeeInDb(EmployeeDetails employeeDetails)
{
int returnVal = 0;
MySql.Data.MySqlClient.MySqlConnection msqlConnection = null;
msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=Mmm_mb;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();
msqlCommand.CommandText = "INSERT INTO employees(employee_id,employee_name,employee_dob,employee_address,employee_phone_no,employee_email,employee_join_date,department,salary,employee_manager_id) "
+ "VALUES(@employee_id,@employee_name,@employee_dob,@employee_address,@employee_phone_no,@employee_email,@employee_join_date,@department,@salary,@employee_manager_id)";
msqlCommand.Parameters.AddWithValue("@employee_id", employeeDetails.employeeId);
msqlCommand.Parameters.AddWithValue("@employee_name", employeeDetails.employeeName);
msqlCommand.Parameters.AddWithValue("@employee_dob", employeeDetails.employeeDob);
msqlCommand.Parameters.AddWithValue("@employee_address", employeeDetails.employeeAddress);
msqlCommand.Parameters.AddWithValue("@employee_phone_no", employeeDetails.employeePhoneNumber);
msqlCommand.Parameters.AddWithValue("@employee_email", employeeDetails.employeeEmail);
msqlCommand.Parameters.AddWithValue("@employee_join_date", employeeDetails.employeeJoinDate);
msqlCommand.Parameters.AddWithValue("@department", employeeDetails.employeeDepartment);
msqlCommand.Parameters.AddWithValue("@salary", employeeDetails.employeeSalary);
msqlCommand.Parameters.AddWithValue("@employee_manager_id", employeeDetails.employeeManagerId);
msqlCommand.ExecuteNonQuery();
returnVal = 1;
}
catch (Exception er)
{
returnVal = 0;
}
finally
{
//always close the connection
msqlConnection.Close();
}
return returnVal;
}
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 CadastroProgramacao_Load(object sender, EventArgs e)
{
this.programacao_ipTableAdapter.Fill(this.eletrocadDataSet.programacao_ip);
myconn = new MySql.Data.MySqlClient.MySqlConnection(strconn);
myconn.StateChange += new StateChangeEventHandler(myconn_StateChange);
Cursor.Current = Cursors.Default;
Application.DoEvents();
/* APENAS PARA TESTE, APAGAR QUANDO FOR COMPILADO
* FUNÇÃO PARA CLICAR NO BOTAO APÓS ALGUNS SEGUNDOS
* EVITANDO QUE O PROGRAMADOR TENHA QUE CLICAR SEMPRE
* NO MESMO BOTÃO PARA TESTAR O APLICATIVO
*/
//System.Threading.Thread.Sleep(1000);
//menuItem5_Click(sender, e);
}
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");
}
public void ViewInfo()
{
string conString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConString"].ToString();
con = new MySql.Data.MySqlClient.MySqlConnection(conString);
con.Open();
queryStr = "";
userID = (Int32)(Session["userID"]);
queryStr = "SELECT userName,spec,kurs FROM guner_db.registration WHERE userID='" + userID + "'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, con);
MySql.Data.MySqlClient.MySqlDataReader reader = null;
reader = cmd.ExecuteReader();
if (reader.Read())
{
UserName.Text = reader["userName"].ToString();
UserSpec.Text = reader["spec"].ToString();
UserKurs.Text = reader["kurs"].ToString();
}
con.Close();
}
public void MigrateDatabase(string connection)
{
try
{
var envolveConnection = new MySql.Data.MySqlClient.MySqlConnection(connection);
var envolve = new Evolve.Evolve(envolveConnection, msg => Log.Information(msg))
{
Locations = new List <string> {
"db/migrations", "db/dataset"
},
IsEraseDisabled = false,
};
envolve.Migrate();
}
catch (Exception ex)
{
Log.Error("Database migration failed", ex);
throw;
}
}
public async Task <List <T> > GetAsync <T>(string connection, string sql, object parameters)
{
var result = new List <T>();
//var test = new MySql.Data.MySqlClient()
using (var c = new MySqlData.MySqlClient.MySqlConnection(connection))
{
_log.LogTrace <MySqlQuery>($"SELECT: {sql}. Parameters: {JsonConvert.SerializeObject(parameters)}");
await c.OpenAsync();
var query = await c.QueryAsync <T>(sql, parameters);
result = query.ToList();
await c.CloseAsync();
}
return(result.ToList());
}
private void Btn_ProbarConexion_Click(object sender, EventArgs e)
{
MySql.Data.MySqlClient.MySqlConnectionStringBuilder MyCnnString = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
MyCnnString.Server = Txt_Servidor.Text;
MyCnnString.UserID = Txt_Usuario.Text;
MyCnnString.Database = Txt_DB.Text;
MyCnnString.Password = Txt_Contraseña.Text;
MySql.Data.MySqlClient.MySqlConnection MyCnn = new MySql.Data.MySqlClient.MySqlConnection(MyCnnString.ConnectionString);
try
{
MyCnn.Open();
MyCnn.Close();
MessageBox.Show("Conexión exitosa!");
}
catch (Exception Error)
{
MessageBox.Show("No se puede realizar la coneccion con estos datos.\r\nDetalles:\r\n" + Error.Message);
}
}
private static Dictionary <string, string> getConfigData()
{
// Query database for settings
MySql.Data.MySqlClient.MySqlConnection con = null;
Database.Configuration.open(ref con);
var cmd = new MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM aspdashboard_settings", con);
var rdr = cmd.ExecuteReader();
var tmp = new Dictionary <string, string>();
while (rdr.Read())
{
tmp.Add(rdr.GetString(1), rdr.GetString(2));
}
rdr.Close();
con.Close();
return(tmp);
}
private void MigrateDatabase(string connection)
{
try
{
var evolveConnection = new MySql.Data.MySqlClient.MySqlConnection(connection);
var evolve = new Evolve.Evolve(evolveConnection, msg => Log.Information(msg))
{
Locations = new List <string> {
"db/migrations", "db/datasets"
},
IsEraseDisabled = true
};
evolve.Migrate();
}
catch (Exception ex)
{
Log.Error("Erro ao executar a migration no database", ex);
throw;
}
}
/// <summary>
/// 创建数据库连接对象
/// </summary>
/// <param name="server">服务器地址</param>
/// <param name="database">数据库</param>
/// <param name="uid">用户名</param>
/// <param name="pwd">密码</param>
/// <param name="dbType">数据库类型,默认MYSQL</param>
/// <returns>数据库连接对象</returns>
public DbConnection CreateConection(string server, string database, string uid, string pwd, string dbType)
{
string connectionString = string.Empty;
DbConnection dbConection = null;
switch (dbType)
{
default:
case "MYSQL":
dbConection = new MySql.Data.MySqlClient.MySqlConnection(string.Format("server={0};user id={1};password={2};persistsecurityinfo=True;database={3};Character Set=utf8;Allow Zero Datetime=true;Convert Zero Datetime=true;pooling=true;MaximumPoolsize=100;", server, uid, pwd, database));
break;
case "SQLSERVER":
throw new NotImplementedException();
case "ORACLE":
throw new NotImplementedException();
}
return(dbConection);
}
public void send(string form_id)
{
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection();;
string serverName = "server=einstein.etsu.edu";
string databaseName = "database=schaum";
string username = "******";
string password = "******";
int userLevel = 0;
conn.ConnectionString = (string.Format("{0};{1};{2};{3}", serverName, databaseName, username, password));
MySql.Data.MySqlClient.MySqlCommand cmd1 = new MySql.Data.MySqlClient.MySqlCommand();
try
{
conn.Open();
cmd1.Connection = conn;
foreach (string userTitle in workFlow)
{
userLevel = isAdmin(userTitle);
cmd1.CommandText = "INSERT INTO workflow(form_id, user_title, user_level_required) VALUES(@form_id, @user_title, @user_level_required);";
cmd1.Parameters.AddWithValue("@user_title", userTitle);
cmd1.Parameters.AddWithValue("@user_level_required", userLevel);
cmd1.Parameters.AddWithValue("@form_id", form_id);
cmd1.ExecuteNonQuery();
cmd1.Parameters.Clear();
}
conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}//send()
public override bool bUserAssociate()
{
if (bUserAssociated())
{
return(true);
}
MySql.Data.MySqlClient.MySqlConnection scConnection = scAdminMaster();
MySql.Data.MySqlClient.MySqlCommand scomUser = new MySql.Data.MySqlClient.MySqlCommand("SELECT Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Alter_priv FROM db WHERE ((Host = '%') AND (Db = '" + m_strDBDataBaseName + "') AND (User = '******'))", scConnection);
try
{
scConnection.Open();
string strSQL = "";
MySql.Data.MySqlClient.MySqlDataReader sreUser = scomUser.ExecuteReader();
if (sreUser.HasRows)
{
strSQL = "UPDATE db SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y',Delete_priv = 'Y',Create_priv = 'Y',Drop_priv = 'Y',Alter_priv = 'Y' WHERE ((Host = '%') AND (Db = '" + m_strDBDataBaseName + "') AND (User = '******'))";
}
else
{
strSQL = "INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Alter_Priv) VALUES ('%','" + m_strDBDataBaseName + "','" + m_strDBUser + "', 'Y','Y','Y','Y','Y','Y','Y')";
}
sreUser.Close();
scomUser = new MySql.Data.MySqlClient.MySqlCommand(strSQL, scConnection);
sreUser = scomUser.ExecuteReader();
sreUser.Close();
strSQL = "FLUSH PRIVILEGES";
scomUser = new MySql.Data.MySqlClient.MySqlCommand(strSQL, scConnection);
sreUser = scomUser.ExecuteReader();
sreUser.Close();
scConnection.Close();
return(bUserAssociated());
}
catch
{
if (scConnection.State == System.Data.ConnectionState.Open)
{
scConnection.Close();
}
return(false);
}
}
public void metodo()
{
articoli.Clear();
try
{
conn = new MySql.Data.MySqlClient.MySqlConnection(connessione);
conn.Open();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.CommandText = "Select * from articolo";
cmd.Connection = conn;
adapter.SelectCommand = cmd;
adapter.Fill(articoli);
cmbl = new MySql.Data.MySqlClient.MySqlCommandBuilder(adapter);
dataGridView1.DataSource = articoli;
cmd = new MySql.Data.MySqlClient.MySqlCommand();
string nomecli = textBox3.Text;
string piva = getPartitaIvaDiCliente(nomecli);
cmd.CommandText = "Select * from prezzo_articolo_cliente where nomecliente = @nomecli";
cmd.Parameters.AddWithValue("@nomecli", piva);
cmd.Connection = conn;
adapter.SelectCommand = cmd;
adapter.Fill(prezzocliente);
conn.Close();
foreach (DataRow row in prezzocliente.Rows)
{
foreach (DataRow art in articoli.Rows)
{
if (art["codicearticolo"].Equals(row["codicearticolo"]))
{
art["prezzolistino"] = (double)row["prezzocliente"] + 999999;
}
}
}
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
conn.Close();
throw;
}
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
var connectionString = _configuration["MySqlConnection:MySqlConnectionString"];
services.AddDbContext <MySQLContext>(options => options.UseMySql(connectionString));
if (_environment.IsDevelopment())
{
try
{
var evolveConnection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
var evolve = new Evolve.Evolve("evolve.json", evolveConnection, msg => _logger.LogInformation(msg))
{
Locations = new List <string> {
"db/migrations"
},
IsEraseDisabled = true,
};
evolve.Migrate();
}
catch (Exception ex)
{
_logger.LogCritical("Database migration failed.", ex);
throw;
}
}
services.AddMvc();
services.AddApiVersioning(option => option.ReportApiVersions = true);
//Dependency Injection
services.AddScoped <IPersonBusiness, PersonBusinessImpl>();
services.AddScoped <IBookBusiness, BookBusinessImpl>();
services.AddScoped <IPersonRepository, PersonRepositoryImpl>();
services.AddScoped(typeof(IRepository <>), typeof(GenericRepository <>));
}
public bool deletePerson(long ID)
{
MySql.Data.MySqlClient.MySqlConnection conn;
string myConnectionString = ConfigurationManager.ConnectionStrings["EmployeeDBConnection"].ConnectionString;
conn = new MySql.Data.MySqlClient.MySqlConnection();
try
{
conn.ConnectionString = myConnectionString;
conn.Open();
Person p = new Person();
MySql.Data.MySqlClient.MySqlDataReader mySQLReader = null;
String sqlString = "SELECT * FROM tblpersonnel WHERE ID = " + ID.ToString();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySQLReader = cmd.ExecuteReader();
if (mySQLReader.Read())
{
mySQLReader.Close();
sqlString = "DELETE FROM tblpersonnel WHERE ID = " + ID.ToString();
cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
cmd.ExecuteNonQuery();
return(true);
}
else
{
return(false);
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public void LoadInventory()
{
try {
MySql.Data.MySqlClient.MySqlConnection mConnection = Globals.GetInstance().GameDatabase.CreateConnection();
MySql.Data.MySqlClient.MySqlCommand mCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT code, expiredate FROM inventory WHERE owner='" + this.Account.Idx + "'", mConnection);
MySql.Data.MySqlClient.MySqlDataReader mReader = mCommand.ExecuteReader();
if (mReader.HasRows)
{
while (mReader.Read())
{
GameFramework.Elements.EItem mItem = new GameFramework.Elements.EItem();
mItem.Code = mReader.GetString(0);
mItem.ExpireDate = mReader.GetString(1);
this.Inventory.itemTable.Add(mItem);
}
}
mReader.Close();
mConnection.Close();
mConnection = Globals.GetInstance().GameDatabase.CreateConnection();
mCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT class1, class2, class3, class4, class5 FROM equipment WHERE owner='" + this.Account.Idx + "'", mConnection);
mReader = mCommand.ExecuteReader();
if (mReader.HasRows)
{
while (mReader.Read())
{
this.Inventory.Engineer = mReader.GetString(0).Split(',');
this.Inventory.Medic = mReader.GetString(1).Split(',');
this.Inventory.Sniper = mReader.GetString(2).Split(',');
this.Inventory.Assault = mReader.GetString(3).Split(',');
this.Inventory.Heavy = mReader.GetString(4).Split(',');
}
}
else
{
this.Disconnect();
}
mReader.Close();
mConnection.Close();
} catch { }
}
public Person GetPerson(long ID)
{
MySql.Data.MySqlClient.MySqlConnection conn;
conn = new MySql.Data.MySqlClient.MySqlConnection();
try
{
conn.ConnectionString = connection.myConnectionString;
conn.Open();
Person p = new Person();
MySql.Data.MySqlClient.MySqlDataReader mySqlDataReader = null;
String sqlString = "SELECT * FROM tblpersonnel WHERE ID = " + ID.ToString();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySqlDataReader = cmd.ExecuteReader();
if (mySqlDataReader.Read())
{
p.ID = mySqlDataReader.GetInt32(0);
p.FirstName = mySqlDataReader.GetString(1);
p.LastName = mySqlDataReader.GetString(2);
p.PayRate = mySqlDataReader.GetDouble(3);
p.StartDate = mySqlDataReader.GetDateTime(4);
p.EndDate = mySqlDataReader.GetDateTime(5);
p.Address = mySqlDataReader.GetString(6);
p.State = mySqlDataReader.GetString(7);
p.ZipCode = mySqlDataReader.GetInt64(8);
p.PhoneNumber = mySqlDataReader.GetDouble(9);
return(p);
}
else
{
return(null);
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
// return the creation of an incidence and the data of it
public Incidencia_Data ListarIncidencias(MySql.Data.MySqlClient.MySqlConnection conn)
{
ArrayList Lista_Incidente = new ArrayList();
string Query = "SELECT * FROM Report;";
string SecondQuery = "Select COUNT(*) FROM mydb.Incidencia;";
var Cmd = new MySql.Data.MySqlClient.MySqlCommand(SecondQuery, conn);//command variable
var Reader = Cmd.ExecuteReader();
Reader.Read();
//get quantity of items
int Incidencia_Quantity = Convert.ToInt32(Reader["COUNT(*)"].ToString());
Reader.Close();
Cmd = new MySql.Data.MySqlClient.MySqlCommand(Query, conn);
Reader = Cmd.ExecuteReader();
for (int i = 0; i < Incidencia_Quantity; i++)
{
Reader.Read();
Lista_Incidente.Add(Reader["Id_Incidencia"].ToString());
Lista_Incidente.Add(Reader["Asunto"].ToString());
Lista_Incidente.Add(Reader["Fecha_Registro"].ToString());
Lista_Incidente.Add(GetUser_byID(Reader["Id_Usuario"].ToString()));
Lista_Incidente.Add(Reader["Tipo_Solicitud"].ToString());
Lista_Incidente.Add(Reader["Categoria_Incidente"].ToString());
Lista_Incidente.Add(Reader["Prioridad"].ToString());
Lista_Incidente.Add(Reader["Estado"].ToString());
Lista_Incidente.Add(Reader["Tecnico"].ToString());
}
Incidencia_Data Lista = new Incidencia_Data
{
Activos = Lista_Incidente
};
Reader.Close();
return(Lista);
}
/// <summary>
/// Obtain details of all Person resources
/// </summary>
/// <returns></returns>
public ArrayList GetPersons()
{
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection
{
ConnectionString = ConfigurationManager.ConnectionStrings["localDB"].ConnectionString
};
ArrayList personsArray = new ArrayList();
try
{
conn.Open();
MySql.Data.MySqlClient.MySqlDataReader mySQLReader = null;
string sqlString = "SELECT * FROM tblpersonnel";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySQLReader = cmd.ExecuteReader();
while (mySQLReader.Read())
{
Person p = new Person();
p.ID = mySQLReader.GetInt32(0);
p.FirstName = mySQLReader.GetString(1);
p.LastName = mySQLReader.GetString(2);
p.PayRate = mySQLReader.GetFloat(3);
p.StartDate = mySQLReader.GetDateTime(4);
p.EndDate = mySQLReader.GetDateTime(5);
personsArray.Add(p);
}
return(personsArray);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public override void LoadEntities()
{
MySql.Data.MySqlClient.MySqlConnection dbConn = this.ActiveConnection as MySql.Data.MySqlClient.MySqlConnection;
if (dbConn != null && dbConn.State == System.Data.ConnectionState.Open)
{
// get tables
String sql = "Show tables;";
var cmd = dbConn.CreateCommand();
cmd.CommandText = sql;
var reader = cmd.ExecuteReader();
Entities.Clear();
while (reader.HasRows && reader.Read())
{
Entity ent = new Entity();
System.Object obj = reader[0];
ent.Name = obj.ToString();
Entities.Add(ent);
}
reader.Close();
foreach (Entity ent in Entities)
{
ent.Load(dbConn);
sql = "show index from `" + ent.Name + "`;";
cmd.CommandText = sql;
reader = cmd.ExecuteReader();
while (reader.HasRows && reader.Read())
{
String isUnique = reader[1].ToString();
if (isUnique == "0")
{
String col = reader[4].ToString();
if (!ent.UniqueColumns.Contains(col))
{
ent.UniqueColumns.Add(col);
}
}
}
reader.Close();
}
}
}
//accesses database to sum up all the prices in the product cart table
public double CalculateTotal()
{
//records user input and declare variables
string query = "";
double totalAmount = 0;
//establishes connection with server
string dbServer = "cis425.wpcarey.asu.edu"; //"<Server IP or Hostname>"
string username = "******"; // "<DB username>"
string password = "******"; // "<DB password>"
string dbName = "groupb04"; // "<DB name>"
string dbConnectionString = string.Format("server={0};uid={1};pwd={2};database={3};",
dbServer, username, password, dbName);
var conn = new MySql.Data.MySqlClient.MySqlConnection(dbConnectionString);
conn.Open();
//populates query
query = $"select sum(productCart.productCartPrice) as 'totalprice'from productcart";
//runs Query
//creates command and reader object using query string and connection
var cmd = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
var reader = cmd.ExecuteReader();
//goes through database and store products in list_product
while (reader.Read())
{
//records information and converts some variables for constructor
var totalPrice = reader["totalPrice"];
if (totalPrice.ToString() != "")
{
totalAmount = Convert.ToDouble(totalPrice);
}
}
reader.Close();
return(totalAmount);
}
protected void Submit(object sender, EventArgs e)
{
String connStr = System.Configuration.ConfigurationManager.ConnectionStrings["WebConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connStr);
conn.Open();
queryStr = "SELECT * FROM tasarimdersi.hakemkullanici WHERE KullaniciAdi='" + GirisUsername.Text + "' AND Sifre='" + GirisPassword.Text + "'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
name = "";
while (reader.HasRows && reader.Read())
{
name = reader.GetString(reader.GetOrdinal("ID"));
kAdi = reader.GetString(reader.GetOrdinal("KullaniciAdi"));
isim = reader.GetString(reader.GetOrdinal("Isim"));
soyisim = reader.GetString(reader.GetOrdinal("Soyisim"));
email = reader.GetString(reader.GetOrdinal("Email"));
}
if (reader.HasRows)
{
Session["IDSes"] = name;
Session["kAdiSes"] = kAdi;
Session["IsimSes"] = isim;
Session["SoyisimSes"] = soyisim;
Session["EmailSes"] = email;
Response.BufferOutput = true;
Response.Redirect("MainHakem.aspx", false);
}
else
{
uyari.InnerText = "Giriş Başarısız";
}
reader.Close();
conn.Close();
}
public void GlobalSetup()
{
using (var connection = new MySqlConnector.MySqlConnection(s_connectionString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = @"
create schema if not exists benchmark;
drop table if exists benchmark.integers;
create table benchmark.integers (value int not null primary key);
insert into benchmark.integers(value) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
drop table if exists benchmark.blobs;
create table benchmark.blobs(
rowid integer not null primary key auto_increment,
`Blob` longblob null
);
insert into benchmark.blobs(`Blob`) values(null), (@Blob1), (@Blob2);";
// larger blobs make the tests run much slower
AddBlobParameter(cmd, "@Blob1", 75000);
AddBlobParameter(cmd, "@Blob2", 150000);
cmd.ExecuteNonQuery();
}
}
s_connectionString += ";database=benchmark";
var mySqlData = new MySql.Data.MySqlClient.MySqlConnection(s_connectionString);
mySqlData.Open();
m_connections.Add("MySql.Data", mySqlData);
var mySqlConnector = new MySqlConnector.MySqlConnection(s_connectionString);
mySqlConnector.Open();
m_connections.Add("MySqlConnector", mySqlConnector);
}
// This method is used to grab the user and case ID to map the cases. No longer needed because case number is no longer entered when registering a user However, will be used again when opening a NEW CHAT and mapping the case and then mapping the messages.
private void getUserID()
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "SELECT user_id FROM project.client WHERE username=?uname";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.Parameters.AddWithValue("?uname", generateUsername);
reader = cmd.ExecuteReader();
while (reader.HasRows && reader.Read())
{
userIDa = reader.GetString(reader.GetOrdinal("user_id"));
}
reader.Close();
queryStr = "SELECT case_id FROM project.allcases WHERE case_name=?cname";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.Parameters.AddWithValue("?cname", "INPUT CASE REF");
reader = cmd.ExecuteReader();
while (reader.HasRows && reader.Read())
{
caseIDa = reader.GetString(reader.GetOrdinal("case_id"));
}
reader.Close();
queryStr = "INSERT INTO project.mapcases (user_id, case_id) VALUES (?userIDa, ?caseIDa )";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.Parameters.AddWithValue("?userIDa", userIDa);
cmd.Parameters.AddWithValue("?caseIDa", caseIDa);
cmd.ExecuteReader();
conn.Close();
}
private bool TestMysqlConnection()
{
string connStr = "server=" + tbIP.Text + ";port=3306;user="******";password="******"; database=" + tbDBName.Text + ";";
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connStr);
try
{
conn.Open();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
conn.Close();
conn.Dispose();
}
}
private void ExecuteMigrations(string connectionString)
{
if (_environment.IsDevelopment())
{
try
{
var evolveConnection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
var evolve = new Evolve.Evolve(evolveConnection, msg => _logger.LogInformation(msg))
{
Locations = new[] { "db/migrations" },
IsEraseDisabled = true,
};
evolve.Migrate();
}
catch (Exception ex)
{
_logger.LogCritical("Database migration failed", ex);
throw;
}
}
}
public static bool VerifyConnection(string address, string database, string user, string password, string port)
{
MySql.Data.MySqlClient.MySqlConnection connection =
new MySql.Data.MySqlClient.MySqlConnection(
String.Format(
"Server={0};Port={4};Database={1};Allow Zero Datetime=true;Convert Zero Datetime=True;Allow User Variables=True;Uid={2};Pwd={3};Charset=utf8;",
address,
database,
user,
password,
port
)
);
using (connection)
{
connection.Open();
}
return(true);
}
private MySql.Data.MySqlClient.MySqlConnection conn; // one connection that whole class will use it (MySqlConnection)
//create a constructor that will create a connection to the database.
//Benefit of that would be that when ever we will create an instance of this class it connection to the database will be ready to use.
public PersonTable()
{
string myConnectionString;
myConnectionString = "server=127.0.0.1;uid=admin;pwd=password;database=employeedb";
try
{
//set our private data member to new connection
conn = new MySql.Data.MySqlClient.MySqlConnection();
//connection string is on that class is my string
conn.ConnectionString = myConnectionString;
conn.Open();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
}
}
private void employee()
{
conn = new MySql.Data.MySqlClient.MySqlConnection(constr);
conn.Open();
data = "select EmployeeID,Name from hr.hire_emp";
cmd = new MySql.Data.MySqlClient.MySqlCommand(data, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
ListItem item = new ListItem();
item.Text = reader["Name"].ToString();
item.Value = reader["EmployeeID"].ToString();
DropDownList1.Items.Add(item);
DropDownList2.Items.Add(item);
}
conn.Close();
}
public void prendiarticoli()
{
try
{
conn = new MySql.Data.MySqlClient.MySqlConnection(connessione);
conn.Open();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.CommandText = "Select * from articolo";
cmd.Connection = conn;
articoliadapter.SelectCommand = cmd;
articoliadapter.Fill(articoli);
articolibuilder = new MySql.Data.MySqlClient.MySqlCommandBuilder(articoliadapter);
datagridarticoli.DataSource = articoli;
conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
conn.Close();
throw;
}
}
public void Delete(Character ch)
{
using (var con = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
{
var cmd = new MySql.Data.MySqlClient.MySqlCommand(
"DELETE FROM CharactersSpecialty WHERE @CharacterID = CharacterID"
, con
);
cmd.Parameters.AddWithValue("@CharacterID", ch.ID);
con.Open();
cmd.ExecuteNonQuery();
cmd = new MySql.Data.MySqlClient.MySqlCommand(
"DELETE FROM Characters WHERE @CharacterID = CharacterID"
, con
);
cmd.Parameters.AddWithValue("@CharacterID", ch.ID);
cmd.ExecuteNonQuery();
con.Close();
}
}
public static void InserirPanico(long cpf, float lati, float longi, int senha)
{
using (MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection(GetStringDb()))
{
using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("INSERT INTO ocorrencias (cpf_cliente, datahora, latitude, longitude, senha_utilizada) VALUES (@cpf_cliente, @datahora, @latitude, @longitude, @senha)", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@cpf_cliente", cpf);
cmd.Parameters.AddWithValue("@datahora", DateTime.Now);
cmd.Parameters.AddWithValue("@latitude", lati);
cmd.Parameters.AddWithValue("@longitude", longi);
cmd.Parameters.AddWithValue("@senha", senha);
con.Open();
cmd.ExecuteNonQuery();
con.Clone();
}
}
}
private static void load_db()
{
Core.Log.WritePlain("MySQL", "Connecting to database... (" + Globals.GetInstance().Config.GetValue("SQL_HOSTNAME") + ":3306)");
Globals.GetInstance().GameDatabase = new Core.DB.Instance();
Globals.GetInstance().GameDatabase.SetCredentials(Globals.GetInstance().Config.GetValue("SQL_HOSTNAME"), Globals.GetInstance().Config.GetValue("SQL_USERNAME"), Globals.GetInstance().Config.GetValue("SQL_PASSWORD"), Globals.GetInstance().Config.GetValue("SQL_DATABASE"));
if (!Globals.GetInstance().GameDatabase.TestConnection())
{
Core.Log.WriteError("Couldn't connect to database!");
Console.ReadKey();
Environment.Exit(0);
}
Core.Log.WritePlain("MySQL", "Done!");
Console.WriteLine();
MySql.Data.MySqlClient.MySqlConnection mConnection = Globals.GetInstance().GameDatabase.CreateConnection();
MySql.Data.MySqlClient.MySqlCommand mCommand = new MySql.Data.MySqlClient.MySqlCommand("UPDATE accounts SET online=0 WHERE 1;", mConnection);
mCommand.ExecuteNonQuery();
mConnection.Close();
}
