///////////////////////////////////////////////////////////////////////////////////////////////////
//Menu Control, User Information & Status Display Components
private void logOutToolStripMenuItem_Click(object sender, EventArgs e)
{
_connection?.Close();
Hide();
var login = new FormMain();
login.Closed += (s, args) => Close();
login.Show();
}
public void StateChange()
{
MySqlConnection c = new MySqlConnection(GetConnectionString(true));
c.StateChange += new StateChangeEventHandler(StateChangeHandler);
c.Open();
c.Close();
}
public async Task <DataTable> GetDataTableAsync(MySqlConnection conn, string sql, MySqlParameter[] parameter = null, int timeout = 180)
{
var ds = new DataTable();
try
{
var cmd = new MySqlCommand(sql, conn)
{
CommandTimeout = timeout
};
if (parameter != null)
{
foreach (var tmp in parameter)
{
cmd.Parameters.Add(tmp);
}
}
conn.Open();
var adapter = new MySqlDataAdapter(cmd);
await adapter.FillAsync(ds);
}
catch (System.Exception ex)
{
throw new RepositoryException(ex.Message, ex);
}
finally
{
conn?.Close();
}
return(ds);
}
public void CommitAfterConnectionDead()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test(id INT, name VARCHAR(20))");
string connStr = GetConnectionString(true) + ";pooling=false";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlTransaction trans = c.BeginTransaction();
using (MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (1, 'boo')", c))
{
cmd.ExecuteNonQuery();
}
KillConnection(c);
try
{
trans.Commit();
Assert.Fail("Should have thrown an exception");
}
catch (Exception)
{
}
Assert.AreEqual(ConnectionState.Closed, c.State);
c.Close(); // this should work even though we are closed
}
}
public async Task <bool> ExecuteNonQueryAsync(MySqlConnection conn, string sql, MySqlParameter[] parameter = null, int timeout = 180)
{
var iResults = 0;
try
{
var cmd = new MySqlCommand(sql, conn)
{
CommandTimeout = timeout
};
if (parameter != null)
{
foreach (var tmp in parameter)
{
cmd.Parameters.Add(tmp);
}
}
cmd.Connection.Open();
iResults = await cmd.ExecuteNonQueryAsync();
}
catch (System.Exception ex)
{
throw new RepositoryException(ex.Message, ex);
}
finally
{
conn?.Close();
}
return(iResults >= 0);
}
private static DataSet Query(string SQLString, string dbName = "zhgd_lw")
{
DataSet ds = new DataSet();
if (client.IsConnected)
{
using (MySqlConnection connection = new MySqlConnection($"SERVER={ portForwarded.BoundHost };PORT={ portForwarded.BoundPort };DATABASE={ dbName };UID=zzsa;PASSWORD=NL88tNkfHnE3kFgT"))
{
try
{
LogUtils4Debug.Logger.Debug($"向品茗数据库查询：{ SQLString }");
MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (MySqlException e)
{
throw e;
}
catch (SocketException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
finally
{
connection?.Close();
}
}
}
return(ds);
}
public static DataSet Query(string SQLString, string dbName = "zhgd_lw")
{
DataSet ds = new DataSet();
if (client.IsConnected)
{
using (MySqlConnection connection = new MySqlConnection($"SERVER={ portForwarded.BoundHost };PORT={ portForwarded.BoundPort };DATABASE={ dbName };UID=zzsa;PASSWORD=NL88tNkfHnE3kFgT"))
{
try
{
MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
connection?.Close();
}
}
}
DataSet newSet = new DataSet();
foreach (DataTable source in ds.Tables)
{
newSet.Tables.Add(ChangeDataType(source));
}
return(newSet);
}
public void Button2_Click(object sender, EventArgs e)
{
try
{
MySqlConnection mySqlConnection = new MySqlConnection(Globals.connStr);
if (mySqlConnection.State == ConnectionState.Closed)
{
mySqlConnection.Open();
}
string selectCommandText = "SELECT CODE_SORTIE,REFART,LIBART,quantite,client,date_sortie,STOCK_AVANT,STOCK_APRES,TYPE_SORTIE from sortie_stock order by CODE_SORTIE desc limit 100";
MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(selectCommandText, mySqlConnection);
DataTable dataTable = new DataTable();
mySqlDataAdapter.Fill(dataTable);
dgv1.DataSource = dataTable;
FormatDGV(dgv1);
mySqlConnection?.Close();
REFART.Text = "";
LIBART.Text = "";
}
catch (Exception ex)
{
ProjectData.SetProjectError(ex);
Exception ex2 = ex;
MessageBox.Show(ex2.Message.ToString());
ProjectData.ClearProjectError();
}
finally
{
if (Globals.conn != null)
{
Globals.conn.Close();
}
}
}
public void btnInit_Click(object sender, EventArgs e)
{
try
{
MySqlConnection mySqlConnection = new MySqlConnection(Globals.connStr);
if (mySqlConnection.State == ConnectionState.Closed)
{
mySqlConnection.Open();
}
string selectCommandText = "select CODE_ENTREE,REFART,LIBART,quantite,PRIX_UE,fournisseur,date_entree,STOCK_AVANT,STOCK_APRES,TYPE_ENTREE from entree_stock order by CODE_ENTREE desc limit 100";
MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(selectCommandText, mySqlConnection);
DataTable dataTable = new DataTable();
mySqlDataAdapter.Fill(dataTable);
dgv1.DataSource = dataTable;
mySqlConnection?.Close();
FormatDGV(dgv1);
REFART.Text = "";
LIBART.Text = "";
}
catch (Exception ex)
{
ProjectData.SetProjectError(ex);
Exception ex2 = ex;
MessageBox.Show(ex2.Message.ToString());
ProjectData.ClearProjectError();
}
}
public void Unicode()
{
if (version < new Version(4, 1)) return;
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (u2 varchar(255) CHARACTER SET ucs2)");
MySqlConnection c = new MySqlConnection(conn.ConnectionString + ";charset=utf8");
c.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES ( CONVERT('困巫忘否役' using ucs2))", c);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM Test";
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
reader.Read();
string s1 = reader.GetString(0);
Assert.AreEqual("困巫忘否役", s1);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null) reader.Close();
c.Close();
}
}
public void CloseConnection()
{
if (_connection?.State != ConnectionState.Closed)
{
_connection?.Close();
}
}
/// <summary>
/// ExecuteNonQuery
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public int ExecuteNonQuery(string query)
{
MySqlConnection conn = null;
MySqlCommand com = null;
int result = 0;
try
{
conn = new MySqlConnection
{
ConnectionString = myConnectionString
};
com = conn.CreateCommand();
conn.Open();
com.CommandText = query;
com.CommandTimeout = 3600;
result = com.ExecuteNonQuery();
return(result);
}
catch (MySqlException ex)
{
string log = ex.Message;
return(-1);
}
finally
{
conn?.Close();
}
}
public static void insertPagamenti_gruppo(Pagamenti_gruppo item)
{
using (MySqlConnection cnMySql = new MySqlConnection(connectionString))
{
cnMySql.Open();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("INSERT INTO antique.pagamenti_gruppo(`id_pagamenti`, `transaction_id`, `tipo_pagamento`, `nome_esecutore`, `email_esecutore`, `data_pagamento`, `importo_pagamento`)");
sb.Append("VALUES ");
sb.Append("(?id_pagamenti, ?transaction_id, ?tipo_pagamento, ?nome_esecutore, ?email_esecutore, ?data_pagamento, ?importo_pagamento)");
using (MySqlCommand cmd = new MySqlCommand(sb.ToString(), cnMySql))
{
try
{
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
EventLogger.LogError("Errore durante l'inserimento dell'oggetto Pagamenti_gruppo nel database.", ex);
}
}
cnMySql.Close();
}
}
public void TestConnectionStrings()
{
MySqlConnection c = new MySqlConnection();
c.ConnectionString = "server=localhost;userid=root;database=mysql;port=3305;includesecurityasserts=true;";
c.Open();
c.Close();
}
public string AddUser(string ime, string ura, string minuta)
{
try
{
string myConnection = "SERVER=studsrv.uni-mb.si;" + "DATABASE=varnepoti;" + "UID=ronzyfonzy;" + "PASSWORD=snopy02;";
connect = new MySqlConnection(myConnection);
connect.Open();
/*maxInserts = connect.CreateCommand();
maxInserts.CommandText = "SELECT MAX(id) AS max FROM EXT_REMINDER;";
dataReader = maxInserts.ExecuteReader();
dataReader.Read();
int max = Convert.ToInt32(dataReader["max"].ToString());
dataReader.Close();*/
insertAlarm = connect.CreateCommand();
//INSERT INTO `EXT_REMINDER` (`ime`, `ura`, `minuta`) VALUES ('test1', '23', '12')
//insertAlarm.CommandText = "INSERT INTO EXT_REMINDER VALUES(" + 1 + ", '" + ime + "', '" + ura + "', '" + minuta + "');";
insertAlarm.CommandText = "INSERT INTO `EXT_REMINDER` (`ime`, `ura`, `minuta`) VALUES ('" + ime + "', '" + ura + "', '" + minuta + "');";
insertAlarm.ExecuteNonQuery();
connect.Close();
return "narejeno";
}
catch (Exception e)
{
return "ni_narejeno";
}
}
public static void updatePagamenti_gruppo(Pagamenti_gruppo item)
{
using (MySqlConnection cnMySql = new MySqlConnection(connectionString))
{
cnMySql.Open();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("UPDATE antique.pagamenti_gruppo ");
sb.Append("SET ");
sb.Append("`id_pagamenti` = ?id_pagamenti, ");
sb.Append("`transaction_id` = ?transaction_id, ");
sb.Append("`tipo_pagamento` = ?tipo_pagamento, ");
sb.Append("`nome_esecutore` = ?nome_esecutore, ");
sb.Append("`email_esecutore` = ?email_esecutore, ");
sb.Append("`data_pagamento` = ?data_pagamento, ");
sb.Append("`importo_pagamento` = ?importo_pagamento, ");
sb.Append("WHERE id=?id;");
using (MySqlCommand cmd = new MySqlCommand(sb.ToString(), cnMySql))
{
//lista parametri comando
try
{
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
EventLogger.LogError("Errore durante l'aggiornamento dell'oggetto Pagamenti_gruppo nel database.", ex);
}
}
cnMySql.Close();
}
}
void TransactionScopeInternal(bool commit)
{
MySqlConnection c = new MySqlConnection(GetConnectionString(true));
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES ('a', 'name', 'name2')", c);
try
{
using (TransactionScope ts = new TransactionScope())
{
c.Open();
cmd.ExecuteNonQuery();
if (commit)
ts.Complete();
}
cmd.CommandText = "SELECT COUNT(*) FROM Test";
object count = cmd.ExecuteScalar();
Assert.AreEqual(commit ? 1 : 0, count);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (c != null)
{
c.Close();
}
}
}
public void Close()
{
_Reader?.Close();
_Reader = null;
_SqlConnection?.Close();
_SqlConnection = null;
}
public static void insertPagamenti(Pagamenti item)
{
using (MySqlConnection cnMySql = new MySqlConnection(connectionString))
{
cnMySql.Open();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("INSERT INTO antique.pagamenti(`id_pagamenti`, `data_ultimo_pagamento`, `totale_pagamento`, `numero_fattura`, `anno_fattura`, `tipo_fattura`, `numregpn`, `annocom`)");
sb.Append("VALUES ");
sb.Append("(?id_pagamenti, ?data_ultimo_pagamento, ?totale_pagamento, ?numero_fattura, ?anno_fattura, ?tipo_fattura, ?numregpn, ?annocom)");
using (MySqlCommand cmd = new MySqlCommand(sb.ToString(), cnMySql))
{
try
{
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
EventLogger.LogError("Errore durante l'inserimento dell'oggetto Pagamenti nel database.", ex);
}
}
cnMySql.Close();
}
}
public static ProjectInfo GetProjectInfo(string projectName, string connectionString)
{
var connection = new MySqlConnection(connectionString);
var command = new MySqlCommand("", connection);
try
{
connection.Open();
command.CommandText = $"SELECT `id` FROM `projects` WHERE `name` = '{projectName}';";
var projectId = command.ExecuteScalar();
command.CommandText = $"SELECT * FROM `projects` WHERE `id` = {projectId} ";
var reader = command.ExecuteReader();
var projectsInfoDataTable = new DataTable();
projectsInfoDataTable.Load(reader);
var row = projectsInfoDataTable.Rows[0];
var projectInfo = new ProjectInfo
{
Id = (int)row[0],
Name = (string)row[1],
Created = DateTime.Parse(row[2].ToString()),
Published = DateTime.Parse(row[3].ToString()),
Description = (string)row[4]
};
return(projectInfo);
}
finally
{
connection?.Close();
command?.Dispose();
}
}
public static IEnumerable <ImageInfo> GetImagesInfo(int projectId, string connectionString)
{
var connection = new MySqlConnection(connectionString);
var command = new MySqlCommand("", connection);
try
{
connection.Open();
command.CommandText = $"SELECT * FROM `projects_images` WHERE `projectid` = {projectId};";
var reader = command.ExecuteReader();
var imageInfoDataTable = new DataTable();
imageInfoDataTable.Load(reader);
foreach (DataRow row in imageInfoDataTable.Rows)
{
var imageInfo = new ImageInfo
{
Id = (int)row[1],
Extension = (string)row[3],
TimeStamp = DateTime.Parse(row[4].ToString())
};
yield return(imageInfo);
}
}
finally
{
connection?.Close();
command?.Dispose();
}
}
public static IEnumerable <ProjectInfo> GetProjectsInfo(string connectionString)
{
var connection = new MySqlConnection(connectionString);
var command = new MySqlCommand("", connection);
try
{
command.CommandText = "SELECT * FROM `projects`";
connection.Open();
var reader = command.ExecuteReader();
var projectsInfoDataTable = new DataTable();
projectsInfoDataTable.Load(reader);
foreach (DataRow row in projectsInfoDataTable.Rows)
{
var projectInfo = new ProjectInfo
{
Id = (int)row[0],
Name = (string)row[1],
Created = DateTime.Parse(row[2].ToString()),
Published = DateTime.Parse(row[3].ToString()),
Description = (string)row[4],
ImagesChanged = DateTime.Parse(row[5].ToString())
};
yield return(projectInfo);
}
}
finally
{
connection?.Close();
command?.Dispose();
}
}
public void Timeout()
{
for (int i=1; i < 10; i++)
execSQL("INSERT INTO Test VALUES (" + i + ", 'This is a long text string that I am inserting')");
// we create a new connection so our base one is not closed
MySqlConnection c2 = new MySqlConnection(conn.ConnectionString);
c2.Open();
KillConnection(c2);
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", c2);
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
reader.Read();
reader.Read();
reader.Close();
Assert.Fail("We should not reach this code");
}
catch (Exception)
{
Assert.AreEqual(ConnectionState.Closed, c2.State);
}
finally
{
if (reader != null) reader.Close();
c2.Close();
}
}
private async Task <IndexModel> LoadModel(IndexModel prms)
{
if (prms == null || string.IsNullOrEmpty(prms.Host))
{
return(new IndexModel());
}
MySqlConnection conn = null;
try
{
var connStrBuilder = new MySqlConnectionStringBuilder();
connStrBuilder.Server = prms.Host;
connStrBuilder.Port = (uint)prms.Port;
connStrBuilder.CharacterSet = "utf8";
connStrBuilder.Pooling = false;
connStrBuilder.SslMode = MySqlSslMode.Disabled;
conn = new MySqlConnection(connStrBuilder.ToString());
try
{
await conn.OpenAsync();
}
catch
{
// workaround http://sphinxsearch.com/bugs/view.php?id=2196
// for version of Sphinx earlier than 2.2.9
if (conn.State != System.Data.ConnectionState.Open)
{
throw;
}
}
var m = new IndexModel
{
Indexes = await MakeQueryAsync("show tables", conn),
};
if (!string.IsNullOrEmpty(prms.Statement))
{
m.Rows = MakeQueryAsync(prms.Statement, conn).Result;
m.Meta = MakeQueryAsync("show meta", conn).Result;
m.Status = MakeQueryAsync("show status", conn).Result;
}
return(m);
}
catch (Exception x)
{
return(new IndexModel {
Exception = x
});
}
finally
{
conn?.Close();
}
}
public void Dispose()
{
if (_transaction != null)
{
_transaction.Rollback();
}
_connection?.Close();
}
public void Dispose()
{
if (connection != null && connection.State == ConnectionState.Open)
{
connection?.Close();
}
connection?.Dispose();
}
public void Sorite_Stock_Load(object sender, EventArgs e)
{
checked
{
try
{
MySqlConnection mySqlConnection = new MySqlConnection(Globals.connStr);
if (mySqlConnection.State == ConnectionState.Closed)
{
mySqlConnection.Open();
}
string selectCommandText = "SELECT CODE_SORTIE,REFART,LIBART,quantite,client,date_sortie,STOCK_AVANT,STOCK_APRES,TYPE_SORTIE from sortie_stock order by CODE_SORTIE desc limit 100";
MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(selectCommandText, mySqlConnection);
DataTable dataTable = new DataTable();
mySqlDataAdapter.Fill(dataTable);
dgv1.DataSource = dataTable;
MySqlCommand mySqlCommand = new MySqlCommand();
MySqlDataAdapter mySqlDataAdapter2 = new MySqlDataAdapter(mySqlCommand);
DataSet dataSet = new DataSet();
if (mySqlConnection.State == ConnectionState.Closed)
{
mySqlConnection.Open();
}
mySqlCommand.Connection = mySqlConnection;
mySqlCommand.CommandText = "select distinct REFART from ARTICLE_D";
mySqlDataAdapter2.Fill(dataSet, "list");
AutoCompleteStringCollection autoCompleteStringCollection = new AutoCompleteStringCollection();
int num = dataSet.Tables[0].Rows.Count - 1;
for (int i = 0; i <= num; i++)
{
autoCompleteStringCollection.Add(dataSet.Tables[0].Rows[i]["REFART"].ToString());
}
REFART.AutoCompleteSource = AutoCompleteSource.CustomSource;
REFART.AutoCompleteCustomSource = autoCompleteStringCollection;
REFART.AutoCompleteMode = AutoCompleteMode.Suggest;
mySqlConnection?.Close();
FormatDGV(dgv1);
}
catch (Exception ex)
{
ProjectData.SetProjectError(ex);
Exception ex2 = ex;
MessageBox.Show(ex2.Message.ToString());
ProjectData.ClearProjectError();
}
finally
{
if (Globals.conn != null)
{
Globals.conn.Close();
}
}
if (Globals.conn != null)
{
Globals.conn.Close();
}
}
}
private void Close()
{
if (Status == DatabaseTransactionStatus.Open)
{
dbTransaction?.Rollback();
Status = DatabaseTransactionStatus.RolledBack;
}
dbConnection?.Close();
}
/// <summary>
/// 建立数据库连接.
/// </summary>
/// <returns>返回MySqlConnection对象</returns>
public static void GetMySqlCon(string ip, string port, string userId, string pwd, string dbName = "information_schema")
{
var mStrSqlcon = $"Database={dbName};Data Source={ip};User Id={userId};Password={pwd};pooling=false;CharSet=utf8;port={port}";
MySqlConnection myCon = new MySqlConnection(mStrSqlcon);
_mySqlCon?.Close();
_mySqlCon = myCon;
DbConStr = mStrSqlcon.Replace(dbName, "{0}");
}
public void Disconnect()
{
if (_disposed)
{
return;
}
_connection?.Close();
_connection?.Dispose();
_connection = null;
}
private void Close()
{
if (IsTransactionPending)
{
IsTransactionPending = false;
dbTransaction?.Rollback();
}
dbConnection?.Close();
}
public void InvalidCast()
{
MySqlConnection con = st.rootConn;
string sql = @"drop function if exists MyTwice; create function MyTwice( val int ) returns int begin return val * 2; end;";
MySqlCommand cmd = new MySqlCommand(sql, con);
cmd.ExecuteNonQuery();
cmd.CommandText = "drop procedure if exists spMyTwice; create procedure spMyTwice( out result int, val int ) begin set result = val * 2; end;";
cmd.ExecuteNonQuery();
try
{
cmd.CommandText = "drop user 'tester2'@'localhost'";
cmd.ExecuteNonQuery();
}
catch (Exception)
{
}
cmd.CommandText = "CREATE USER 'tester2'@'localhost' IDENTIFIED BY '123';";
cmd.ExecuteNonQuery();
cmd.CommandText = "grant execute on function `MyTwice` to 'tester2'@'localhost';";
cmd.ExecuteNonQuery();
cmd.CommandText = "grant execute on procedure `spMyTwice` to 'tester2'@'localhost'";
cmd.ExecuteNonQuery();
cmd.CommandText = "grant select on table mysql.proc to 'tester2'@'localhost'";
cmd.ExecuteNonQuery();
cmd.CommandText = "flush privileges";
cmd.ExecuteNonQuery();
MySqlConnection con2 = new MySqlConnection(st.rootConn.ConnectionString);
con2.Settings.UserID = "tester2";
con2.Settings.Password = "******";
// Invoke the function
cmd.Connection = con2;
con2.Open();
cmd.CommandText = "MyTwice";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new MySqlParameter("val", System.DBNull.Value));
cmd.Parameters.Add("@p", MySqlDbType.Int32);
cmd.Parameters[1].Direction = ParameterDirection.ReturnValue;
cmd.Parameters[0].Value = 20;
cmd.ExecuteNonQuery();
con2.Close();
Assert.Equal(cmd.Parameters[1].Value, 40);
con2.Open();
cmd.CommandText = "spMyTwice";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.Add(new MySqlParameter("result", System.DBNull.Value));
cmd.Parameters.Add("val", MySqlDbType.Int32);
cmd.Parameters[0].Direction = ParameterDirection.Output;
cmd.Parameters[1].Value = 20;
cmd.ExecuteNonQuery();
con2.Close();
Assert.Equal(cmd.Parameters[0].Value, 40);
}
internal static void fill(string queryString, DataTable toReturn)
{
using (var conn = new MySqlConnection(connString)) {
conn.Open();
conn.ChangeDatabase(Server.MySQLDatabaseName);
using (MySqlDataAdapter da = new MySqlDataAdapter(queryString, conn)) {
da.Fill(toReturn);
}
conn.Close();
}
}
public void CanOpenConnectionAfterAborting()
{
MySqlConnection connection = new MySqlConnection(GetConnectionString(true));
connection.Open();
Assert.AreEqual(ConnectionState.Open, connection.State);
connection.Open();
Assert.AreEqual(ConnectionState.Open, connection.State);
connection.Close();
}
private void CloseConnection()
{
try
{
connection?.Close();
}
catch (MySqlException exp)
{
throw new Exception("Could not close MySQL Connection connection!", exp);
}
}
public void Close()
{
try
{
_Reader?.Close();
}
catch { }
_Reader = null;
_SqlConnection?.Close();
_SqlConnection = null;
}
public void UseFunctions()
{
execSQL("CREATE TABLE Test (valid char, UserCode varchar(100), password varchar(100)) CHARSET latin1");
MySqlConnection c = new MySqlConnection(conn.ConnectionString + ";charset=latin1");
c.Open();
MySqlCommand cmd = new MySqlCommand("SELECT valid FROM Test WHERE Valid = 'Y' AND " +
"UserCode = 'username' AND Password = AES_ENCRYPT('Password','abc')", c);
cmd.ExecuteScalar();
c.Close();
}
//Close connection
private bool CloseConnection()
{
try
{
connection?.Close();
return(true);
}
catch (MySqlException ex)
{
return(false);
}
}
public void Close()
{
try
{
conn?.Close();
Logger.Info("Closed connection to MySQL @ " + Env.GetString("MYSQL_HOST"));
}
catch (MySqlException e)
{
Logger.Error(e.ToString());
}
}
internal static void execute(string queryString, bool createDB = false)
{
using (var conn = new MySqlConnection(connString)) {
conn.Open();
if (!createDB) {
conn.ChangeDatabase(Server.MySQLDatabaseName);
}
using (MySqlCommand cmd = new MySqlCommand(queryString, conn)) {
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
/// <summary>
/// Tests connection, throws on error.
/// </summary>
public void TestConnection()
{
MySqlConnection conn = null;
try
{
conn = Connection;
}
finally
{
conn?.Close();
}
}
// Exécute un scale sur la DB
public override object ExecuteScalar(string sqlcommand)
{
if (ConnectionType == ConnectionType.DATABASE_MYSQL)
{
Log.Debug("DataConnection", "SQL: " + sqlcommand);
object obj = null;
bool repeat = false;
MySqlConnection conn = null;
do
{
try
{
conn = (MySqlConnection)GetConnection();
long start = Environment.TickCount;
using (var cmd = new MySqlCommand(sqlcommand, conn))
{
obj = cmd.ExecuteScalar();
}
conn.Close();
Log.Debug("DataConnection", "SQL Select exec time " + (Environment.TickCount - start) + "ms");
if (Environment.TickCount - start > 500)
{
Log.Notice("DataConnection", "SQL Select took " + (Environment.TickCount - start) + "ms!\n" + sqlcommand);
}
repeat = false;
}
catch (Exception e)
{
conn?.Close();
if (!HandleException(e))
{
Log.Error("DataConnection", "ExecuteSelect: \"" + sqlcommand + "\"\n" + e);
throw;
}
repeat = true;
}
} while (repeat);
return(obj);
}
Log.Notice("DataConnection", "SQL Scalar not supported");
return(null);
}
public List <StationData> ReadTable()
{
try
{
// open connection
_conn.Open();
// read using an SQL select stateent from table with constant string variable
const string readDb = @"SELECT * FROM Station;";
var cmdRead = new MySqlCommand(readDb, _conn);
// execute command & assign to new variable
_reader = cmdRead.ExecuteReader();
// while reader is open
while (_reader.Read())
{
// create new stationData object using the object initializer
var stationData = new StationData
{
Longitude = (string)_reader["longitude"],
Latitude = (string)_reader["latitude"],
StationId = (string)_reader["stationId"],
Csv = (string)_reader["csv"],
Json = (string)_reader["json"]
};
// write out columns to confirm data
Console.WriteLine(_reader["longitude"] + " " + _reader["latitude"] + " " + _reader["stationID"]);
// adds stationData to list
_stationInfo.Add(stationData);
}
}
catch (Exception ex)
{
// outputs error message
Console.WriteLine(ex.Message);
}
finally
{
// close the reader with null propogation
_reader?.Close();
// close the connection with null propogation
_conn?.Close();
// used as an escape method when testing method function
//Console.ReadKey();
}
return(_stationInfo);
}
public void BulkCopyCTC(List<EmployeeDet> list)
{
DataTable dt =new DataTable();
dt.Columns.Add(new DataColumn("employee_id",typeof(System.String)));
dt.Columns.Add(new DataColumn("employee_name",typeof(System.String)));
dt.Columns.Add(new DataColumn("emp_ctc",typeof(System.Decimal)));
foreach(EmployeeDet item in list)
{
DataRow dr = dt.NewRow();
dr["employee_id"]= item.GetID();
dr["employee_name"]= item.GetName();
dr["emp_ctc"]= item.GetCTC();
dt.Rows.Add(dr);
}
MySqlConnection con =new MySqlConnection(newConnectionUtils().GetConnectionString());
if(con.State==ConnectionState.Open)
{
con.Close();
}
con.Open();
MySqlCommand cmd =new MySqlCommand("SP_InsertCTC", con);
cmd.CommandType=CommandType.StoredProcedure;
cmd.UpdatedRowSource=UpdateRowSource.None;
cmd.Parameters.Add("?e_id",MySqlDbType.String).SourceColumn="employee_id";
cmd.Parameters.Add("?e_name",MySqlDbType.String).SourceColumn= "employee_name";
cmd.Parameters.Add("?emp_ctc",MySqlDbType.Decimal).SourceColumn= "emp_ctc";
MySqlDataAdapter da =new MySqlDataAdapter();
da.InsertCommand= cmd;
da.UpdateBatchSize=100;
int records = da.Update(dt);
con.Close();
}
public void BeginTransactionOnPreviouslyOpenConnection()
{
string connStr = GetConnectionString(true);
MySqlConnection c = new MySqlConnection(connStr);
c.Open();
c.Close();
try
{
c.BeginTransaction();
}
catch (Exception ex)
{
Assert.AreEqual("The connection is not open.", ex.Message);
}
}
public void Latin1Connection()
{
if (Version < new Version(4, 1)) return;
execSQL("CREATE TABLE Test (id INT, name VARCHAR(200)) CHARSET latin1");
execSQL("INSERT INTO Test VALUES( 1, _latin1 'Test')");
MySqlConnection c = new MySqlConnection(conn.ConnectionString + ";charset=latin1");
c.Open();
MySqlCommand cmd = new MySqlCommand("SELECT id FROM Test WHERE name LIKE 'Test'", c);
object id = cmd.ExecuteScalar();
Assert.AreEqual(1, id);
c.Close();
}
public override bool TryConnect()
{
if (string.IsNullOrWhiteSpace(Server) || string.IsNullOrWhiteSpace(User))
{
throw new Exception("The connection parameters are not properly set.");
}
MySqlConnection conn = null;
try {
conn = GetConnection();
} finally {
conn?.Close();
}
return(true);
}
public static void Main(string[] args)
{
const string url = "127.0.0.1";
const string user = "******";
const string password = "******";
const string database = "bank";
string conn = $"Server={url};Database={database};port=3306;ussername={user};password={password}";
MySqlConnection connection = null;
try
{
connection = new MySqlConnection(conn);
connection.Open();
Console.WriteLine($"Conected {connection.ServerVersion}");
MySqlCommand cmd = new MySqlCommand("show tables", connection);
//Список начальников и кол-во подчиненных.
cmd.CommandText = @"SELECT chief.emp_id, chief.fname, chief.lname, COUNT(*) `count`
FROM employee chief
INNER JOIN employee e ON chief.emp_id = e.superior_emp_id
GROUP BY chief.emp_id
ORDER BY `count`;";
MySqlDataReader rdr = cmd.ExecuteReader();
Console.WriteLine(
$@"{rdr.GetName(0),6} {rdr.GetName(1),10} {rdr.GetName(2),10} {rdr.GetName(3),10}");
while (rdr.Read())
{
Console.WriteLine(
$@"{rdr.GetString(0),6} {rdr.GetString(1),10} {rdr.GetString(2),10} {rdr.GetString(3),10}");
}
}
catch (MySqlException e)
{
Console.WriteLine(e.SqlState);
Console.WriteLine(e);
Console.WriteLine(e.StackTrace);
}
finally
{
connection?.Close();
}
}
public void RollingBackOnClose()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (id INT) TYPE=InnoDB");
string connStr = GetConnectionString(true) + ";pooling=true;";
MySqlConnection c = new MySqlConnection(connStr);
c.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (1)", c);
c.BeginTransaction();
cmd.ExecuteNonQuery();
c.Close();
MySqlConnection c2 = new MySqlConnection(connStr);
c2.Open();
MySqlCommand cmd2 = new MySqlCommand("SELECT COUNT(*) from Test", c2);
c2.BeginTransaction();
object count = cmd2.ExecuteScalar();
c2.Close();
Assert.AreEqual(0, count);
}
public void ProblemCharsInSQLUTF8()
{
if (version < new Version(4, 1)) return;
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), mt MEDIUMTEXT, " +
"PRIMARY KEY(id)) CHAR SET utf8");
MySqlConnection c = new MySqlConnection(GetConnectionString(true) + ";charset=utf8");
c.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, ?text, ?mt)", c);
cmd.Parameters.AddWithValue("?id", 1);
cmd.Parameters.AddWithValue("?text", "This is my;test ? string–’‘’“”?");
cmd.Parameters.AddWithValue("?mt", "My MT string: ?");
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM Test";
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
Assert.IsTrue(reader.Read());
Assert.AreEqual(1, reader.GetInt32(0));
Assert.AreEqual("This is my;test ? string–’‘’“”?", reader.GetString(1));
Assert.AreEqual("My MT string: ?", reader.GetString(2));
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null) reader.Close();
if (c != null) c.Close();
}
}
public static void updatePagamenti(Pagamenti item)
{
using (MySqlConnection cnMySql = new MySqlConnection(connectionString))
{
cnMySql.Open();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("UPDATE antique.pagamenti ");
sb.Append("SET ");
sb.Append("`id_pagamenti` = ?id_pagamenti, ");
sb.Append("`data_ultimo_pagamento` = ?data_ultimo_pagamento, ");
sb.Append("`totale_pagamento` = ?totale_pagamento, ");
sb.Append("`numero_fattura` = ?numero_fattura, ");
sb.Append("`anno_fattura` = ?anno_fattura, ");
sb.Append("`tipo_fattura` = ?tipo_fattura, ");
sb.Append("`numregpn` = ?numregpn, ");
sb.Append("`annocom` = ?annocom, ");
sb.Append("WHERE id=?id;");
using (MySqlCommand cmd = new MySqlCommand(sb.ToString(), cnMySql))
{
//lista parametri comando
try
{
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
EventLogger.LogError("Errore durante l'aggiornamento dell'oggetto Pagamenti nel database.", ex);
}
}
cnMySql.Close();
}
}
public void LoadDataLocalInfile()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
string connString = conn.ConnectionString + ";pooling=false";
MySqlConnection c = new MySqlConnection(connString);
c.Open();
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 2000000; i++)
sw.WriteLine(i + ",'Test'");
sw.Flush();
sw.Close();
path = path.Replace(@"\", @"\\");
MySqlCommand cmd = new MySqlCommand(
"LOAD DATA LOCAL INFILE '" + path + "' INTO TABLE Test FIELDS TERMINATED BY ','", conn);
cmd.CommandTimeout = 0;
object cnt = 0;
cnt = cmd.ExecuteNonQuery();
Assert.AreEqual(2000000, cnt);
cmd.CommandText = "SELECT COUNT(*) FROM Test";
cnt = cmd.ExecuteScalar();
Assert.AreEqual(2000000, cnt);
c.Close();
}
public void UTF8()
{
if (version < new Version(4, 1)) return;
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (id int, name VARCHAR(200) CHAR SET utf8)");
MySqlConnection c = new MySqlConnection(conn.ConnectionString + ";charset=utf8");
c.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES(1, 'ЁЄЉҖҚ')", c); //russian
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Test VALUES(2, '兣冘凥凷冋')"; // simplified Chinese
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Test VALUES(3, '困巫忘否役')"; // traditional Chinese
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Test VALUES(4, '涯割晦叶角')"; // Japanese
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Test VALUES(5, 'ברחפע')"; // Hebrew
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Test VALUES(6, 'ψόβΩΞ')"; // Greek
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Test VALUES(7, 'þðüçöÝÞÐÜÇÖ')"; // Turkish
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Test VALUES(8, 'ฅๆษ')"; // Thai
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM Test";
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
reader.Read();
Assert.AreEqual("ЁЄЉҖҚ", reader.GetString(1));
reader.Read();
Assert.AreEqual("兣冘凥凷冋", reader.GetString(1));
reader.Read();
Assert.AreEqual("困巫忘否役", reader.GetString(1));
reader.Read();
Assert.AreEqual("涯割晦叶角", reader.GetString(1));
reader.Read();
Assert.AreEqual("ברחפע", reader.GetString(1));
reader.Read();
Assert.AreEqual("ψόβΩΞ", reader.GetString(1));
reader.Read();
Assert.AreEqual("þðüçöÝÞÐÜÇÖ", reader.GetString(1));
reader.Read();
Assert.AreEqual("ฅๆษ", reader.GetString(1));
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null) reader.Close();
c.Close();
}
}
public void CP932()
{
MySqlConnection c = new MySqlConnection(GetConnectionString(true) + ";charset=cp932");
c.Open();
try
{
MySqlCommand cmd = new MySqlCommand("SELECT '涯割晦叶角'", c);
string s = (string)cmd.ExecuteScalar();
Assert.AreEqual("涯割晦叶角", s);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (c != null)
c.Close();
}
}
public void Russian()
{
if (version < new Version(4, 1)) return;
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (id int, name VARCHAR(200) CHAR SET cp1251)");
MySqlConnection c = new MySqlConnection(conn.ConnectionString + ";charset=utf8");
c.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES(1, 'щьеи')", c);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM Test";
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
reader.Read();
Assert.AreEqual("щьеи", reader.GetString(1));
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null) reader.Close();
c.Close();
}
}
public void UTF8PreparedAndUsingParameters()
{
if (version < new Version(4, 1)) return;
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (name VARCHAR(200) CHAR SET utf8)");
MySqlConnection c = new MySqlConnection(conn.ConnectionString + ";charset=utf8");
c.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES(?val)", c);
cmd.Parameters.Add("?val", MySqlDbType.VarChar);
cmd.Prepare();
cmd.Parameters[0].Value = "ЁЄЉҖҚ"; // Russian
cmd.ExecuteNonQuery();
cmd.Parameters[0].Value = "兣冘凥凷冋"; // simplified Chinese
cmd.ExecuteNonQuery();
cmd.Parameters[0].Value = "困巫忘否役"; // traditional Chinese
cmd.ExecuteNonQuery();
cmd.Parameters[0].Value = "涯割晦叶角"; // Japanese
cmd.ExecuteNonQuery();
cmd.Parameters[0].Value = "ברחפע"; // Hebrew
cmd.ExecuteNonQuery();
cmd.Parameters[0].Value = "ψόβΩΞ"; // Greek
cmd.ExecuteNonQuery();
cmd.Parameters[0].Value = "þðüçöÝÞÐÜÇÖ"; // Turkish
cmd.ExecuteNonQuery();
cmd.Parameters[0].Value = "ฅๆษ"; // Thai
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM Test";
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
reader.Read();
Assert.AreEqual("ЁЄЉҖҚ", reader.GetString(0));
reader.Read();
Assert.AreEqual("兣冘凥凷冋", reader.GetString(0));
reader.Read();
Assert.AreEqual("困巫忘否役", reader.GetString(0));
reader.Read();
Assert.AreEqual("涯割晦叶角", reader.GetString(0));
reader.Read();
Assert.AreEqual("ברחפע", reader.GetString(0));
reader.Read();
Assert.AreEqual("ψόβΩΞ", reader.GetString(0));
reader.Read();
Assert.AreEqual("þðüçöÝÞÐÜÇÖ", reader.GetString(0));
reader.Read();
Assert.AreEqual("ฅๆษ", reader.GetString(0));
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null) reader.Close();
c.Close();
}
}
private void GetSip()
{
using (MySqlConnection con = new MySqlConnection(this.ConnectionString))
using (MySqlCommand cmd = new MySqlCommand("SELECT * FROM sip", con))
{
con.Open();
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
this.SipIP = rdr["ip"].ToString();
this.SipPort = rdr["port"].ToString();
this.SipUsername = rdr["username"].ToString();
this.SipPassword = rdr["password"].ToString();
this.SipExtraNumber = rdr["extra_number"].ToString();
this.SipEnabled = Convert.ToBoolean(rdr["enabled"]);
}
con.Close();
}
}
private void GetAdminPassword()
{
using (MySqlConnection con = new MySqlConnection(this.ConnectionString))
using (MySqlCommand cmd = new MySqlCommand("SELECT * FROM admin", con))
{
con.Open();
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
this.AdminPassword = rdr["password"].ToString();
}
con.Close();
}
}
public void RunWithoutSelectPrivsThrowException()
{
if (Version < new Version(5, 0)) return;
// we don't want this test to run in our all access fixture
string connInfo = GetConnectionInfo();
if (connInfo.IndexOf("use procedure bodies=false") == -1)
return;
suExecSQL(String.Format(
"GRANT ALL ON `{0}`.* to 'testuser'@'%' identified by 'testuser'",
database0));
suExecSQL(String.Format(
"GRANT ALL ON `{0}`.* to 'testuser'@'localhost' identified by 'testuser'",
database0));
execSQL("DROP PROCEDURE IF EXISTS spTest");
execSQL("CREATE PROCEDURE spTest(id int, OUT outid int, INOUT inoutid int) " +
"BEGIN SET outid=id+inoutid; SET inoutid=inoutid+id; END");
string s = GetConnectionString("testuser", "testuser", true);
MySqlConnection c = new MySqlConnection(s);
c.Open();
try
{
MySqlCommand cmd = new MySqlCommand("spTest", c);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?id", 2);
cmd.Parameters.AddWithValue("?outid", MySqlDbType.Int32);
cmd.Parameters[1].Direction = ParameterDirection.Output;
cmd.Parameters.AddWithValue("?inoutid", 4);
cmd.Parameters[2].Direction = ParameterDirection.InputOutput;
if (prepare) cmd.Prepare();
cmd.ExecuteNonQuery();
Assert.AreEqual(6, cmd.Parameters[1].Value);
Assert.AreEqual(6, cmd.Parameters[2].Value);
}
catch (InvalidOperationException iex)
{
Assert.IsTrue(iex.Message.StartsWith("Unable to retrieve"));
}
finally
{
if (c != null)
c.Close();
suExecSQL("DELETE FROM mysql.user WHERE user = '******'");
}
}
public void SpaceInDatabaseName()
{
string dbName = System.IO.Path.GetFileNameWithoutExtension(
System.IO.Path.GetTempFileName()) + " x";
try
{
suExecSQL(String.Format("CREATE DATABASE `{0}`", dbName));
suExecSQL(String.Format("GRANT ALL ON `{0}`.* to 'test'@'localhost' identified by 'test'",
dbName));
suExecSQL(String.Format("GRANT ALL ON `{0}`.* to 'test'@'%' identified by 'test'",
dbName));
suExecSQL("FLUSH PRIVILEGES");
string connStr = GetConnectionString(false) + ";database=" + dbName;
MySqlConnection c = new MySqlConnection(connStr);
c.Open();
c.Close();
}
finally
{
suExecSQL(String.Format("DROP DATABASE `{0}`", dbName));
}
}