/// <summary>
/// Retrieves a List of the generic type object from the database that match the where conditional
/// </summary>
/// <param name="connection">Connection to the database to use when performing the request</param>
/// <param name="tableName">Name of the table to retrieve from</param>
/// <param name="where">Where conditional string that the generic objects must match</param>
/// <returns>A list of the generic objects that match the where conditional, or null if an error occurs</returns>
public List <T> RetrieveDataWhere(MySqlConnection connection, string tableName, string where)
{
string commandString = "select * from " + tableName + " where " + where + ";";
MySqlCommand cmd = connection.CreateCommand();
cmd.CommandText = commandString;
MySqlDataReader reader;
try
{
reader = cmd.ExecuteReader();
}
catch (MySqlException e)
{
LastException = e;
return(null);
}
List <T> ret = new List <T>();
while (reader.Read())
{
T toAdd = new T();
toAdd.Deserialize(reader);
ret.Add(toAdd);
}
reader.Close();
return(ret);
}
public void errorWriter(MySqlException oMySQLException)
{
TheLogger.Log("ExecSql Error in " + oMySQLException.TargetSite + " due to : " + oMySQLException.Message + "\n");
string errorText = oMySQLException.ToString();
errorWriter(errorText);
}
private MySqlConnection create_conn()
{
MySqlConnection conn = null;
retry:
string strConn = @"Server={0};Database={1};Uid={2};Pwd={3};";
try
{
conn = new MySqlConnection(string.Format(strConn, Env.dbServerAddress, Env.dbDatabase, Env.dbUid, Env.dbPassword));
conn.Open();
}catch (MySqlException ex)
{
MySqlException inner = ex.InnerException as MySqlException;
// no database!! create!!
if (inner?.Number == 1049)
{
string createConn = @"Server={0};Uid={1};Pwd={2};";
using (conn = new MySqlConnection(string.Format(createConn, Env.dbServerAddress, Env.dbUid, Env.dbPassword)))
using (var com = conn.CreateCommand())
{
conn.Open();
com.CommandText = string.Format(@"CREATE DATABASE IF NOT EXISTS `{0}`;", Env.dbDatabase);
com.ExecuteNonQuery();
}
goto retry;
}
throw;
}
return(conn);
}
private ValueTask <int> DoReadBytesSync(ArraySegment <byte> buffer)
{
try
{
if (RemainingTimeout == Constants.InfiniteTimeout)
{
return(new ValueTask <int>(m_socket.Receive(buffer.Array, buffer.Offset, buffer.Count, SocketFlags.None)));
}
while (RemainingTimeout > 0)
{
var startTime = Environment.TickCount;
if (m_socket.Poll(Math.Min(int.MaxValue / 1000, RemainingTimeout) * 1000, SelectMode.SelectRead))
{
var bytesRead = m_socket.Receive(buffer.Array, buffer.Offset, buffer.Count, SocketFlags.None);
RemainingTimeout -= unchecked (Environment.TickCount - startTime);
return(new ValueTask <int>(bytesRead));
}
RemainingTimeout -= unchecked (Environment.TickCount - startTime);
}
return(ValueTaskExtensions.FromException <int>(MySqlException.CreateForTimeout()));
}
catch (Exception ex)
{
return(ValueTaskExtensions.FromException <int>(ex));
}
}
private static void handleMySQLError(MySqlException e)
{
switch (e.Number)
{
case (int)MySqlErrorCode.None:
m_log.ErrorFormat("[{0}]: Cannot connect to database!", LOG_NAME);
break;
case (int)MySqlErrorCode.AccessDenied:
m_log.ErrorFormat("[{0}]: Access denied connecting to database.", LOG_NAME);
break;
case (int)MySqlErrorCode.NoSuchTable:
m_log.ErrorFormat("[{0}]: Table not found in database!", LOG_NAME);
break;
case (int)MySqlErrorCode.TableAccessDenied:
m_log.ErrorFormat("[{0}]: Access denied to database table.", LOG_NAME);
break;
case (int)MySqlErrorCode.BadFieldError:
m_log.ErrorFormat("[{0}]: Bad feild: Database table is incorrectly set up.", LOG_NAME);
break;
default:
m_log.ErrorFormat("[{0}]: MySqlErrorCode {1} while logging event.", LOG_NAME, e.Number);
break;
}
}
public bool executeNonQueryCommand(string sqlCommand, ref MySqlException returnEx)
{
bool retVal = true;
//myConnectionString = Properties.Settings.Default.connectionString;
int temp;
try
{
myTransCommand.CommandText = sqlCommand;
if (myTransCommand.Connection.State.ToString() != "Open")
{
myTransCommand.Connection.Open();
}
temp = myTransCommand.ExecuteNonQuery();
retVal = true;
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
retVal = false;
returnEx = ex;
}
return(retVal);
}
// Loggers
#region MysqlLogger
/// <summary>
/// </summary>
/// <param name="me">
/// </param>
/// <param name="SqlQuery">
/// </param>
private void MySqlLogger(MySqlException me, string SqlQuery)
{
FileInfo t = new FileInfo("SqlError.log");
if (t.Exists)
{
TextWriter tex = t.AppendText();
tex.WriteLine("Date/Time: " + DateTime.Now.ToString());
tex.WriteLine(" ");
tex.WriteLine("Sql String: " + SqlQuery);
tex.WriteLine(" ");
tex.WriteLine("Sql Error: ");
tex.WriteLine(me);
tex.Write(tex.NewLine);
tex.Flush();
tex.Close();
tex = null;
t = null;
}
else
{
StreamWriter sw = t.CreateText();
sw.WriteLine("Date/Time: " + DateTime.Now.ToString());
sw.WriteLine(" ");
sw.WriteLine("Sql String: " + SqlQuery);
sw.WriteLine(" ");
sw.WriteLine("Sql Error: ");
sw.WriteLine(me);
sw.Write(sw.NewLine);
sw.Flush();
sw.Close();
sw = null;
t = null;
}
}
MySqlErrorCode HandleMySQLException(MySqlException ex, string query = "")
{
MySqlErrorCode code = (MySqlErrorCode)ex.Number;
if (ex.InnerException != null)
{
if (ex.InnerException is MySqlException)
{
code = (MySqlErrorCode)((MySqlException)ex.InnerException).Number;
}
}
switch (code)
{
case MySqlErrorCode.BadFieldError:
case MySqlErrorCode.NoSuchTable:
Log.outError(LogFilter.Sql, "Your database structure is not up to date. Please make sure you've executed all queries in the sql/updates folders.");
break;
case MySqlErrorCode.ParseError:
Log.outError(LogFilter.Sql, "Error while parsing SQL. Core fix required.");
break;
}
Log.outError(LogFilter.Sql, $"SqlException: {ex.Message} SqlQuery: {query}");
return(code);
}
/// <summary>
/// 提交当前操作的结果
/// </summary>
public int Commit()
{
try
{
int returnValue = dbcontext.SaveChanges();
if (dbTransaction != null)
{
dbTransaction.Commit();
this.Close();
}
return(returnValue);
}
catch (Exception ex)
{
if (ex.InnerException != null && ex.InnerException.InnerException is MySqlException)
{
MySqlException sqlEx = ex.InnerException.InnerException as MySqlException;
throw ExceptionEx.ThrowDataAccessException(sqlEx, sqlEx.Message);
}
throw;
}
finally
{
if (dbTransaction == null)
{
this.Close();
}
}
}
public void updateSyncFlag(string tableName, string PKField = "", string PKFieldValue = "")
{
string sqlCommand = "";
MySqlException internalEX = null;
DS.beginTransaction();
try
{
sqlCommand = "UPDATE " + tableName + " SET SYNCHRONIZED = 1, EDITED = 0";
if (PKField.Length > 0)
{
sqlCommand = sqlCommand + " WHERE " + PKField + " = '" + PKFieldValue + "'";
}
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
DS.commit();
}
catch (Exception ex)
{
gUtil.saveSystemDebugLog(0, "FAILED TO SET SYNC FIELD [" + ex.Message + "]");
}
}
public static string Format(Exception ex)
{
Cursor.Current = Cursors.Default;
Type t = ex.GetType();
if (t.Name.ToLower() == "mysqlexception")
{
MySqlException e = ex as MySqlException;
switch (e.Number)
{
case -2:
return("查询结果集过大，网络传输超时失败。");
case 2:
case 53:
return("无法连接到服务器，请检查您的网络连接，也有可能是服务器地址填写错误。");
case 208:
return("表结构丢失。");
case 547:
return(string.Format("违反外键约束:{0}", e.Message));
case 201:
case 2812:
return("存储过程丢失。");
case 2627:
case 2601:
return(string.Format("违反主键约束:{0}", e.Message));
case 4060:
return("无效的数据库名称。");
case 8145:
return("存储过程参数配置不正确，请联系管理员解决该问题。");
case 8178:
return("缺少输入或输出参数");
case 18456:
return("登录数据库失败，无效的用户名或密码。");
case 11:
case 17142:
return("无法连接到数据库，请检查数据库是否已启动。");
default:
return(e.Message);
}
}
else if (t == typeof(System.Net.WebException))
{
return("无法连接到应用服务器。");
}
else
{
return(ex.Message);
}
}
public override bool Exists()
{
try
{
_connection.Open();
_connection.Close();
return(true);
}
catch (Exception ex)
{
MySqlException mex = ex as MySqlException;
if (mex == null)
{
throw;
}
if (mex.Number == 1049)
{
return(false);
}
if (mex.InnerException == null)
{
throw;
}
mex = mex.InnerException as MySqlException;
if (mex == null)
{
throw;
}
if (mex.Number == 1049)
{
return(false);
}
throw;
}
}
public int ExecuteNonQuery(string SQLSTR, out MySqlException ErrReport)
{
int functionReturnValue = 0;
MySqlCommand sqlco = new MySqlCommand();
ErrReport = null;
try
{
sqlco.Connection = objConn;
sqlco.CommandText = SQLSTR;
return(sqlco.ExecuteNonQuery());
}
catch (MySqlException sEX)
{
ErrReport = sEX;
functionReturnValue = 0;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQLClass - ExecuteSQL");
functionReturnValue = 0;
}
finally
{
sqlco.Dispose();
}
return(functionReturnValue);
}
private bool testConnection(bool skipMessage = false)
{
bool result = false;
MySqlException internalEX = null;
if (localhostRadioButton.Checked)
{
ipAddress = "localhost";
}
else
{
ipAddress = gUtil.allTrim(ip1Textbox.Text) + "." + gUtil.allTrim(ip2Textbox.Text) + "." + gUtil.allTrim(ip3Textbox.Text) + "." + gUtil.allTrim(ip4Textbox.Text);
}
DS.setConfigFileConnectionString(ipAddress);
result = DS.testConfigConnectionString(ref internalEX);
if (!skipMessage)
{
if (!result)
{
MessageBox.Show("CONNECTION ERROR [" + internalEX.Message + "]");
}
else
{
MessageBox.Show("CONNECTION SUCCESS");
}
}
return(result);
}
public void Data()
{
var exception = new MySqlException(MySqlErrorCode.No, "two", "three");
Assert.Equal(1002, exception.Data["Server Error Code"]);
Assert.Equal("two", exception.Data["SqlState"]);
}
/// <summary>
/// Gets a message from an exception raised by the connector.
/// Removes the initial #{number} and the ending dot.
/// </summary>
/// <param name="e">Exception.</param>
/// <returns>The message.</returns>
/// <exception cref="ArgumentNullException"><paramref name="e"/> is a <B>null</B> reference.</exception>
public override string GetExceptionMessage(Exception /*!*/ e)
{
if (e == null)
{
throw new ArgumentNullException("e");
}
MySqlException mye = e as MySqlException;
if (mye == null || mye.Message.Length == 0)
{
return(e.Message);
}
string msg = StripErrorNumber(mye.Message);
// skip last dot:
int j = msg.Length;
if (msg[j - 1] == '.')
{
j--;
}
return(String.Format("{0} (error {1})", msg.Substring(0, j), mye.Number));
}
public Boolean Eliminar()
{
if (conexion.IUD(string.Format("DELETE FROM DetalleDeVenta WHERE idVenta='{0}'", IdVenta)))
{
}
else
{
error = conexion.Error;
}
if (conexion.IUD(string.Format("DELETE FROM Venta WHERE idVenta='{0}'", IdVenta)))
{
}
else
{
error = conexion.Error;
}
if (conexion.IUD(string.Format("DELETE FROM factura WHERE IdFactura='{0}'", IdFactura)))
{
// MessageBox.Show("La venta ha sido cancelada", " ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(true);
}
else
{
error = conexion.Error;
return(false);
}
}
public Boolean Venta()
{
/*Inserta datos en la tabla de venta */
if (conexion.IUD(string.Format("insert into Venta(idCliente,idEmpleado) value('{0}','{1}')", idCliente, idEmpleado)))
{
IdVenta = Convert.ToInt32(conexion.consulta(string.Format("SELECT MAX(idVenta) from Venta")).Rows[0][0].ToString());
}
else
{
error = conexion.Error;
}
if (conexion.IUD(string.Format("insert into Factura(IdEmpleado,idCliente) value('{0}','{1}')", idEmpleado, idCliente)))
{
IdFactura = Convert.ToInt32(conexion.consulta(string.Format("SELECT MAX(IdFactura) from Factura")).Rows[0][0].ToString());
return(true);
}
else
{
//error = conexion.Error;
return(false);
}
/*Inserta datos en la tabla de factura */
}
private IQueryable <Sanpham> GetSanphams()
{
bool success = false;
int retryCount = 0;
MySqlException exception = null;
while (!success && retryCount <= 6)
{
try
{
var spLst = _context.Sanpham.Include(s => s.DanhMucNavigation);
success = true;
retryCount = 5;
}
catch (MySqlException e)
{
exception = e;
success = false;
retryCount++;
Thread.Sleep(800);
}
}
if (retryCount > 5)
{
throw exception;
}
return(_context.Sanpham.Include(s => s.DanhMucNavigation));
}
public void PasswordExpiration()
{
if ((Fixture.Version < new Version(5, 6, 6)) || (Fixture.Version >= new Version(8, 0, 17)))
{
return;
}
const string expireduser = "******";
const string expiredhost = "localhost";
string expiredfull = string.Format("'{0}'@'{1}'", expireduser, expiredhost);
using (MySqlConnection conn = Fixture.GetConnection(true))
{
MySqlCommand cmd = new MySqlCommand("", conn);
// creates expired user
cmd.CommandText = string.Format("SELECT COUNT(*) FROM mysql.user WHERE user='******' AND host='{1}'", expireduser, expiredhost);
long count = (long)cmd.ExecuteScalar();
if (count > 0)
{
MySqlHelper.ExecuteNonQuery(conn, String.Format("DROP USER " + expiredfull));
}
MySqlHelper.ExecuteNonQuery(conn, String.Format("CREATE USER {0} IDENTIFIED BY '{1}1'", expiredfull, expireduser));
MySqlHelper.ExecuteNonQuery(conn, String.Format("GRANT SELECT ON `{0}`.* TO {1}", conn.Database, expiredfull));
MySqlHelper.ExecuteNonQuery(conn, String.Format("ALTER USER {0} PASSWORD EXPIRE", expiredfull));
conn.Close();
// validates expired user
var cnstrBuilder = new MySqlConnectionStringBuilder(Root.ConnectionString);
cnstrBuilder.UserID = expireduser;
cnstrBuilder.Password = expireduser + "1";
conn.ConnectionString = cnstrBuilder.ConnectionString;
conn.Open();
cmd.CommandText = "SELECT 1";
MySqlException ex = Assert.Throws <MySqlException>(() => cmd.ExecuteScalar());
Assert.Equal(1820, ex.Number);
if (Fixture.Version >= new Version(5, 7, 6))
{
cmd.CommandText = string.Format("SET PASSWORD = '******'", expireduser);
}
else
{
cmd.CommandText = string.Format("SET PASSWORD = PASSWORD('{0}1')", expireduser);
}
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT 1";
cmd.ExecuteScalar();
conn.Close();
conn.ConnectionString = Root.ConnectionString;
conn.Open();
MySqlHelper.ExecuteNonQuery(conn, String.Format("DROP USER " + expiredfull));
conn.Close();
}
}
private bool saveDataTransaction()
{
bool result = false;
string sqlCommand = "";
MySqlException internalEX = null;
double unitConversion = getConvertValue();
DS.beginTransaction();
try
{
DS.mySqlConnect();
switch (currentMode)
{
case NEW_CONVERSION:
sqlCommand = "INSERT INTO UNIT_CONVERT (CONVERT_UNIT_ID_1, CONVERT_UNIT_ID_2, CONVERT_MULTIPLIER) VALUES (" + selectedUnit1_ID + ", " + selectedUnit2_ID + ", " + unitConversion + ")";
gUtil.saveSystemDebugLog(globalConstants.MENU_SATUAN, "ADD NEW UNIT CONVERT [" + selectedUnit1_ID + "/" + selectedUnit2_ID + "/" + unitConversion + "]");
break;
case EDIT_CONVERSION:
sqlCommand = "UPDATE UNIT_CONVERT SET CONVERT_MULTIPLIER = " + unitConversion + " WHERE CONVERT_UNIT_ID_1 = " + selectedUnit1_ID + " AND CONVERT_UNIT_ID_2 = " + selectedUnit2_ID;
gUtil.saveSystemDebugLog(globalConstants.MENU_SATUAN, "UPDATE UNIT CONVERT [" + selectedUnit1_ID + "/" + selectedUnit2_ID + "/" + unitConversion + "]"); break;
}
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
DS.commit();
result = true;
}
catch (Exception e)
{
gUtil.saveSystemDebugLog(globalConstants.MENU_SATUAN, "EXCEPTION THROWN [" + e.Message + "]");
try
{
DS.rollBack();
}
catch (MySqlException ex)
{
if (DS.getMyTransConnection() != null)
{
gUtil.showDBOPError(ex, "ROLLBACK");
}
}
gUtil.showDBOPError(e, "INSERT");
result = false;
}
finally
{
DS.mySqlClose();
}
return(result);
}
public static bool IsUniqueKeyException(this MySqlException ex)
{
return(ex.Number == 1060 ||
ex.Number == 1061 ||
ex.Number == 1062 ||
ex.Number == 1088 ||
ex.Number == 1092);
}
public static string ParseError(MySqlException mySqlException)
{
try
{
return(DefinedErrors[mySqlException.Number] != null ? DefinedErrors[mySqlException.Number] : mySqlException.Message);
}
catch { return(mySqlException.Message); }
}
//Everything that goes wrong with MySql will raise an MySqlException. Since this is also used in connectToDb() the query is optional.
public void HandleException(MySqlException exception, string query = "")
{
Console.WriteLine("!!EXCEPTION THROWN: \n" + exception.ToString());
if (query.Length != 0)
{
Console.WriteLine("Query executed: " + query);
}
}
public static bool IsFailoverException(this MySqlException mySqlException)
{
return(mySqlException.Number == (int)MySqlErrorCode.UnableToConnectToHost ||
mySqlException.Number == (int)MySqlErrorCode.OptionPreventsStatement ||
mySqlException.Number == 0 && mySqlException.HResult == -2147467259 ||
mySqlException.HResult == -532462766); //reading from stream failed during instance reboot
// Fatal error reading from the stream, usually Number being 0 means host connection issues
}
public override bool IsCredentialValid(ICredential credential, TimeSpan minDuration)
{
bool Success;
StartWatch(out Stopwatch Watch);
try
{
string ConnectionString = CredentialToConnectionString(credential, true);
using (MySqlConnection VerificationConnection = new MySqlConnection(ConnectionString))
{
VerificationConnection.Open();
VerificationConnection.Close();
}
Success = true;
}
#if TRACE
catch (ApplicationException)
{
throw;
}
#endif
catch (MySqlException e)
{
bool IsValidityError = false;
MySqlException InnerException = e;
while (InnerException != null && !IsValidityError)
{
if (InnerException.Number == ER_DBACCESS_DENIED_ERROR || InnerException.Number == ER_ACCESS_DENIED_ERROR || InnerException.Number == ER_BAD_DB_ERROR)
{
IsValidityError = true;
}
else
{
_LastErrorCode = InnerException.Number;
InnerException = InnerException.InnerException as MySqlException;
}
}
if (!IsValidityError)
{
TraceMySqlException(e);
}
Success = false;
}
catch (Exception e)
{
TraceException(e);
Success = false;
}
MinWait(Watch, minDuration);
return(Success);
}
private bool RetryOnExistsFailure(MySqlException e)
{
if (e.Number == 1049)
{
ClearPool();
return(true);
}
return(false);
}
public DatabaseException(DbConfig.DbType type, int code)
{
switch (type)
{
case DbConfig.DbType.MYSQL:
instance = MySqlException.GetInstance(code);
break;
}
}
private void ParseMySqlException(Exception ex)
{
MySqlException mysqlEx = (MySqlException)ex;
m_sErrorInfo = mysqlEx.Message;
m_sDBMSErrorInfo = mysqlEx.Message;
m_iErrorCode = (int)mysqlEx.Number;
m_sSqlState = mysqlEx.SqlState;
}
private void ClearConnectionPoolIfDatabaseFailingOver(
MySqlException ex)
{
if (ex.IsFailoverException())
{
MySqlConnection.ClearPool(
new MySqlConnection(GetConnectionString()));
}
}
