public string GetMTFromPrintersWherePrinterID(int PrinterID)
{
string _mt = "";
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
MySql.Data.MySqlClient.MySqlDataReader _dataReader;
string _sql = "SELECT mt FROM printers WHERE printersid='" + PrinterID + "';";
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_dataReader = _mySqlCommand.ExecuteReader();
while (_dataReader.Read())
{
_mt = _dataReader.GetValue(0).ToString();
}
_dataReader.Close();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
return(_mt);
}
public int GetUserIDByLogin(string Login)
{
int _id = 0;
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
MySql.Data.MySqlClient.MySqlDataReader _dataReader;
string _sql = "SELECT usersid FROM users WHERE login='******';";
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_dataReader = _mySqlCommand.ExecuteReader();
while (_dataReader.Read())
{
Int32.TryParse(_dataReader.GetValue(0).ToString(), out _id);
}
_dataReader.Close();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
return(_id);
}
public void SetDismantled(int TestID)
{
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
string _sql = String.Format("UPDATE test SET dismantled='1' WHERE testid='{0}'", TestID);
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_mySqlCommand.ExecuteNonQuery();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
}
public string GetFIDFromComponents(string SN)
{
string _status = "";
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
MySql.Data.MySqlClient.MySqlDataReader _dataReader;
string _sql = "SELECT status FROM test WHERE sn='" + SN + "';";
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_dataReader = _mySqlCommand.ExecuteReader();
while (_dataReader.Read())
{
_status = _dataReader.GetValue(0).ToString();
}
_dataReader.Close();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
return(_status);
}
public AppVersion GetCurrentVersion()
{
AppVersion _version = null;
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
MySql.Data.MySqlClient.MySqlDataReader _dataReader;
string _sql = String.Format("SELECT * FROM versions WHERE current='1';");
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_dataReader = _mySqlCommand.ExecuteReader();
while (_dataReader.Read())
{
int _ver;
Int32.TryParse(_dataReader.GetValue(1).ToString(), out _ver);
_version = new AppVersion(_ver, _dataReader.GetValue(2).ToString(), _dataReader.GetValue(3).ToString(), _dataReader.GetValue(4).ToString(), 1);
}
_dataReader.Close();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
return(_version);
}
public int GetPageCountByTestID(int TestID)
{
int _pageCount = 0;
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
MySql.Data.MySqlClient.MySqlDataReader _dataReader;
string _sql = "SELECT pagecount FROM test WHERE testid='" + TestID + "';";
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_dataReader = _mySqlCommand.ExecuteReader();
while (_dataReader.Read())
{
Int32.TryParse(_dataReader.GetValue(0).ToString(), out _pageCount);
}
_dataReader.Close();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
return(_pageCount);
}
public void InsertComponentLog(string Login, int TestID, int ComponentID, string Status)
{
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
string _sql = ";";
_sql = String.Format("" +
"INSERT INTO dismantled (usersid, testid, componentid, status, date) VALUES " +
"(" +
"(SELECT usersid FROM users WHERE login='******'), " +
"'{1}', " +
"'{2}', " +
"'{3}', " +
"'{4}'" +
");", Login, TestID, ComponentID, Status, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_mySqlCommand.ExecuteNonQuery();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
}
public void UpdateComponentStock(int ComponentID)
{
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
string _sql = String.Format("UPDATE components SET stock = stock + 1 WHERE componentsid='{0}'", ComponentID);
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_mySqlCommand.ExecuteNonQuery();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
using (MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(strSQL, connection);
MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@fs", MySql.Data.MySqlClient.MySqlDbType.Binary);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT @@IDENTITY";
int rows = (int)cmd.ExecuteScalar();
return(rows);
}
catch (System.Data.OleDb.OleDbException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
public void InsertHDD(string HDDSN, string PrinterMT, string PrinterSN)
{
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
string _sql = String.Format("INSERT INTO hdd (sn, prtmt, prtsn, destroyed) VALUES ('{0}', '{1}', '{2}', '{3}');", HDDSN, PrinterMT, PrinterSN, 0);
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_mySqlCommand.ExecuteNonQuery();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
}
public override void SendClientMessage(int Type, string MessageValue)
{
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand();
command.CommandText = "INSERT INTO messages (SessionToken, Type, MessageValue, DateCreated) VALUES (@SessionToken, @Type, @MessageValue, @DateCreated);";
command.Parameters.AddWithValue("@SessionToken", Program.sessionToken);
command.Parameters.AddWithValue("@Type", Type);
command.Parameters.AddWithValue("@MessageValue", MessageValue);
command.Parameters.AddWithValue("@DateCreated", CommonCommunicator.UnixTime());
Database.Command(command);
command.Dispose();
}
public void InsertResets(int TestID, string MBSN, string OPSN, string ENGINESN)
{
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
string _sql = String.Format(
" INSERT INTO resets (testid, date, ");
if (MBSN != null)
{
_sql += String.Format("mbsn");
}
if (OPSN != null)
{
_sql += String.Format("opsn");
}
if (ENGINESN != null)
{
_sql += String.Format("enginesn");
}
_sql += String.Format(") VALUES('{0}', '{1}', ", TestID, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
if (MBSN != null)
{
_sql += String.Format("'{0}'", MBSN);
}
if (OPSN != null)
{
_sql += String.Format("'{0}'", OPSN);
}
if (ENGINESN != null)
{
_sql += String.Format("'{0}'", ENGINESN);
}
_sql += String.Format(");");
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_mySqlCommand.ExecuteNonQuery();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
}
public void InsertTestDataToMySQL(int PrinterID, string PrinterSN, string Status, string Login, bool Firmware, bool Defaults, bool Nvram, int PageCount)
{
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
string _sql = String.Format("INSERT INTO test (printersid, sn, hddid, status, userid, date, dismantled, firmware, defaults, nvram, pagecount) VALUES " +
"(" +
//printersid
"'{0}', " +
//printer sn
"'{1}', " +
//hddid
"(SELECT hddid FROM hdd WHERE prtsn='{1}'), " +
//status
"'{2}', " +
//userid
"(SELECT usersid FROM users WHERE login='******'), " +
//date
"'{4}', " +
//dismantled
"'{5}', " +
//firmware
"'{6}', " +
//defaults
"'{7}', " +
//nvram
"'{8}', " +
//pagecount
"'{9}'" +
");", PrinterID.ToString(), PrinterSN, Status, Login, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), 0, (Firmware == true ? 1 : 0), (Defaults == true ? 1 : 0), (Nvram == true ? 1 : 0), PageCount);
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_mySqlCommand.ExecuteNonQuery();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
}
// Dispose(bool disposing) executes in two distinct scenarios.
// If disposing equals true, the method has been called directly
// or indirectly by a user's code. Managed and unmanaged resources
// can be disposed.
// If disposing equals false, the method has been called by the
// runtime from inside the finalizer and you should not reference
// other objects. Only unmanaged resources can be disposed.
protected virtual void Dispose(bool disposing)
{
// Check to see if Dispose has already been called.
if (!this.disposed)
{
// If disposing equals true, dispose all managed
// and unmanaged resources.
if (disposing)
{
// Dispose managed resources.
//if (_MyConnection != null)
//{
// if (_MyConnection.State == ConnectionState.Open)
// _MyConnection.Close();
// _MyConnection.Dispose();
// _MyConnection = null;
//}
if (_MyCommand != null)
{
if (_MyCommand.Parameters.Count > 0)
{
_MyCommand.Parameters.Clear();
}
_MyCommand.Dispose();
}
if (_MyDataAdaptor != null)
{
_MyDataAdaptor.Dispose();
}
}
// Release unmanaged resources. If disposing is false,
// only the following code is executed.
CloseHandle(handle);
handle = System.IntPtr.Zero;
// Note that this is not thread safe.
// Another thread could start disposing the object
// after the managed resources are disposed,
// but before the disposed flag is set to true.
// If thread safety is necessary, it must be
// implemented by the client.
}
disposed = true;
}
public void UpsertResets(int TestID, string MBSN, string OPSN, string ENGINESN)
{
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
MySql.Data.MySqlClient.MySqlDataReader _dataReader;
_conn.Open();
string _sql = String.Format("SELECT resetsid FROM resets WHERE testid='{0}';", TestID);
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_dataReader = _mySqlCommand.ExecuteReader();
int _value = 0;
while (_dataReader.Read())
{
Int32.TryParse(_dataReader.GetValue(0).ToString(), out _value);
}
_dataReader.Close();
_mySqlCommand.Dispose();
_conn.Close();
if (_value == 0)
{
InsertResets(TestID, MBSN, OPSN, ENGINESN);
}
else
{
UpdateResets(TestID, MBSN, OPSN, ENGINESN);
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
}
public Component GetComponentByPN(string PN)
{
Component _component = null;
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
MySql.Data.MySqlClient.MySqlDataReader _dataReader;
string _sql = String.Format("SELECT * FROM components WHERE pn='{0}';", PN);
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_dataReader = _mySqlCommand.ExecuteReader();
while (_dataReader.Read())
{
int _id;
Int32.TryParse(_dataReader.GetValue(0).ToString(), out _id);
int _stock;
Int32.TryParse(_dataReader.GetValue(8).ToString(), out _stock);
int _yield;
Int32.TryParse(_dataReader.GetValue(9).ToString(), out _yield);
_component = new Component(_id, _dataReader.GetValue(1).ToString(), _dataReader.GetValue(2).ToString(), _dataReader.GetValue(3).ToString(), _dataReader.GetValue(4).ToString(), _dataReader.GetValue(5).ToString(), _dataReader.GetValue(6).ToString(), _dataReader.GetValue(7).ToString(), _stock, _yield, _dataReader.GetValue(10).ToString(), _dataReader.GetValue(11).ToString());
}
_dataReader.Close();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
return(_component);
}
public List <Component> GetComponents()
{
List <Component> _list = new List <Component>();
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
MySql.Data.MySqlClient.MySqlDataReader _dataReader;
string _sql = "SELECT * FROM components;";
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_dataReader = _mySqlCommand.ExecuteReader();
while (_dataReader.Read())
{
int _id;
Int32.TryParse(_dataReader.GetValue(0).ToString(), out _id);
int _stock;
Int32.TryParse(_dataReader.GetValue(8).ToString(), out _stock);
int _yield;
Int32.TryParse(_dataReader.GetValue(9).ToString(), out _yield);
_list.Add(new Component(_id, _dataReader.GetValue(1).ToString(), _dataReader.GetValue(2).ToString(), _dataReader.GetValue(3).ToString(), _dataReader.GetValue(4).ToString(), _dataReader.GetValue(5).ToString(), _dataReader.GetValue(6).ToString(), _dataReader.GetValue(7).ToString(), _stock, _yield, _dataReader.GetValue(10).ToString(), _dataReader.GetValue(11).ToString()));
}
_dataReader.Close();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
return(_list);
}
public int GetPrinterIDFromTestBySN(string SN)
{
int _id = 0;
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
MySql.Data.MySqlClient.MySqlDataReader _dataReader;
string _sql = "SELECT printersid FROM test WHERE sn='" + SN + "';";
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_dataReader = _mySqlCommand.ExecuteReader();
while (_dataReader.Read())
{
if (_dataReader.GetValue(0).Equals(null))
{
_id = 0;
}
else
{
Int32.TryParse(_dataReader.GetValue(0).ToString(), out _id);
}
}
_dataReader.Close();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
return(_id);
}
public void InsertPrinterDismantleTime(int UserID, int TestID, int PrintersID, DateTime StartDateTime, DateTime EndDateTime, TimeSpan Time)
{
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
//DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
string _sql = String.Format("INSERT INTO dismantledtimes (userid, testid, printersid, start, end, time) VALUES " +
"(" +
//userid
"'{0}', " +
//testid
"'{1}', " +
//printersid
"'{2}', " +
//start
"'{3}', " +
//end
"'{4}', " +
//time
"'{5}'" +
");",
UserID, TestID, PrintersID, StartDateTime.ToString("yyyy-MM-dd HH:mm:ss"), EndDateTime.ToString("yyyy-MM-dd HH:mm:ss"), Time.TotalMinutes);
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_mySqlCommand.ExecuteNonQuery();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
}
public void UpdateResets(int TestID, string MBSN, string OPSN, string ENGINESN)
{
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
string _sql = String.Format(
"UPDATE resets SET ", TestID);
if (MBSN != null)
{
_sql += String.Format("mbsn='{0}',", MBSN);
}
if (OPSN != null)
{
_sql += String.Format("opsn='{0}',", OPSN);
}
if (ENGINESN != null)
{
_sql += String.Format("enginesn='{0}',", ENGINESN);
}
_sql += String.Format(
" date='{0}'" +
" WHERE testid='{1}'", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), TestID);
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_mySqlCommand.ExecuteNonQuery();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
}
public List <SSubmodel> GetPrinterIDFromPrintersByModel(string MachineType)
{
List <SSubmodel> _list = new List <SSubmodel>();
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
MySql.Data.MySqlClient.MySqlDataReader _dataReader;
string _sql = String.Format(@"SELECT printersid, submodel, name FROM `printers` WHERE mt='{0}';", MachineType);
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_dataReader = _mySqlCommand.ExecuteReader();
while (_dataReader.Read())
{
int _id;
Int32.TryParse(_dataReader.GetValue(0).ToString(), out _id);
int _sub;
Int32.TryParse(_dataReader.GetValue(1).ToString(), out _sub);
_list.Add(new SSubmodel(_id, _sub, _dataReader.GetValue(2).ToString()));
}
_dataReader.Close();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
return(_list);
}
//public string AddField(ref DataTable dtTarget, string name, int index)
//{
// int columnIndex = dtTarget.Columns.IndexOf(name);
// if (columnIndex < 0)
// {
// return $"Column '{name}' was not found in the target table.";
// }
// else
// {
// FieldMapping.Add(index, new CsvFieldMapping(name, index, columnIndex));
// return "";
// }
//}
public string GetTargetTableStructure(string tableName, MySql.Data.MySqlClient.MySqlConnection connection, ref DataTable dtTarget)
{
string errorMessage = "";
System.Console.WriteLine($"Getting the table structure of: '{tableName}'");
try
{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand($"DELETE FROM {tableName}", connection);
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
cmd.Dispose();
using (MySql.Data.MySqlClient.MySqlDataAdapter adapter = new MySql.Data.MySqlClient.MySqlDataAdapter($"SELECT * FROM {tableName} WHERE 1=2", connection))
{
adapter.Fill(dtTarget);
}
}
catch (System.Exception e)
{
errorMessage = e.Message;
}
return errorMessage;
}
/// <summary>
/// 删除过时的AIS数据操作
/// </summary>
private static void DeleteAISdata(MySql.Data.MySqlClient.MySqlConnection connection, AisControl controlObject)
{
// 开始事务处理
MySql.Data.MySqlClient.MySqlTransaction transaction = connection.BeginTransaction();
MySql.Data.MySqlClient.MySqlCommand command = connection.CreateCommand();
DateTime lastDynamicTime = DateTime.Now.Subtract(new TimeSpan(0, 4, 0, 0));
DateTime lastStaticTime = DateTime.Now.Subtract(new TimeSpan(15, 0, 0, 0));
string sqlDeleteAisData = "delete from realtimeaisdynamicinfotable where AISTimeStamp < '" + lastDynamicTime + "';";
sqlDeleteAisData += "delete from realtimeaisstaticinfotable where Timestamp < '" + lastStaticTime + "';";
try
{
command.CommandText = sqlDeleteAisData;
command.ExecuteNonQuery();
// 提交数据入库
transaction.Commit();
if (controlObject != null)
{
// 提交错误日志
controlObject.AddStatusString("AIS过时数据删除操作成功。");
}
command.Dispose();
}
catch (Exception ex)
{
// 放弃当前的数据入库操作
transaction.Rollback();
if (controlObject != null)
{
// 提交错误日志
controlObject.AddStatusString("AIS过时数据删除操作失败。\r\n错误信息为：" + ex.ToString());
}
}
}
public List <Printer> GetPrinters(List <Component> ListOfComponents)
{
List <Printer> _list = new List <Printer>();
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
MySql.Data.MySqlClient.MySqlDataReader _dataReader;
string _sql = "SELECT * FROM printers;";
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_dataReader = _mySqlCommand.ExecuteReader();
while (_dataReader.Read())
{
int _id;
Int32.TryParse(_dataReader.GetValue(0).ToString(), out _id);
string _components = _dataReader.GetValue(3).ToString();
_list.Add(new Printer(_id, _dataReader.GetValue(1).ToString(), _dataReader.GetValue(2).ToString(), ParseComponents(_components, ListOfComponents), _dataReader.GetValue(4).ToString()));
}
_dataReader.Close();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
return(_list);
}
public User GetUserByNFC(string NFC)
{
User _user = null;
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
MySql.Data.MySqlClient.MySqlDataReader _dataReader;
string _sql = "SELECT * FROM users WHERE NFC='" + NFC + "' OR NFC2='" + NFC + "';";
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_dataReader = _mySqlCommand.ExecuteReader();
while (_dataReader.Read())
{
int _perm;
Int32.TryParse(_dataReader.GetValue(5).ToString(), out _perm);
_user = new User(_dataReader.GetValue(1).ToString(), _dataReader.GetValue(2).ToString(), _dataReader.GetValue(3).ToString(), _dataReader.GetValue(4).ToString(), _perm);
}
_dataReader.Close();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
return(_user);
}
public static void run()
{
try
{
// 手动连接
conn.ConnectionString = connStr;
conn.Open();
string tab = "tusers";
string sql3 = $"INSERT INTO {tab} VALUES(default, @str)";
var cmd3 = new MySql.Data.MySqlClient.MySqlCommand(sql3, conn);
cmd3.Parameters.AddWithValue("@str", "👏");
using (cmd3)
{
int rows = cmd3.ExecuteNonQuery();
System.Console.WriteLine(rows);
}
string sql1 = $"select * from {tab}";
var cmd1 = new MySql.Data.MySqlClient.MySqlCommand(sql1, conn);
MySql.Data.MySqlClient.MySqlDataReader reader = cmd1.ExecuteReader();
while (reader.Read())
{
// 此时 reader 为当前行
for (int col = 0; col < reader.FieldCount; col++)
{
var fieldName = reader.GetName(col);
var val = reader[col];
System.Console.Write("{0}: {1}\n", fieldName, val);
}
}
reader.Close();
cmd1.Dispose();
/*
* // 创建数据库执行对象
* string sql1 = "SELECT * FROM [TUser]";
* var cmd1 = new System.Data.SqlClient.SqlCommand(sql1, conn);
*
* // 执行查询命令
* // 写操作: ExecuteNonQuery
* // 读操作: ExecuteReader, ExecuteScalar
* System.Data.SqlClient.SqlDataReader reader = cmd1.ExecuteReader();
* System.Console.WriteLine("HasRows: {0}", reader.HasRows);
* System.Console.WriteLine("FieldCount: {0}", reader.FieldCount);
* System.Console.WriteLine("Depth: {0}", reader.Depth);
* System.Console.WriteLine("RecordsAffected: {0}", reader.RecordsAffected); // -1 for select
*
* // 手动去调用 Read() 方法之后, DataReader 对象才会移动到结果集的第一行
* // 同时此方法也返回一个 Bool 值, 表明下一行是否可用, True 则可用, False 则到达结果集末尾
* while (reader.Read())
* {
* // GetOrdinal 获取对应列的序号
* int index = reader.GetOrdinal("login");
* System.Console.WriteLine("\n[login] index: {0}", index);
*
* // 此时 reader 为当前行
* for (int col = 0; col < reader.FieldCount; col++)
* {
* var fieldName = reader.GetName(col);
* var val = reader[col];
* var dtype = reader.GetDataTypeName(col);
* if (dtype == "datetime")
* {
* // https://docs.microsoft.com/zh-cn/dotnet/standard/base-types/standard-date-and-time-format-strings?view=netcore-2.2
* var utc = System.DateTime.SpecifyKind(reader.GetDateTime(col), System.DateTimeKind.Utc);
* val = string.Format("\"{0:O}\"", utc);
* }
* System.Console.Write("{0}: {1} ", fieldName, val);
* System.Console.WriteLine(dtype);
* }
* }
* reader.Close();
* cmd1.Dispose();
*
* // ExecuteScalar: 获取一行一列值
* string sql2 = "SELECT COUNT([Id]) FROM [TUser]";
* var cmd2 = new System.Data.SqlClient.SqlCommand(sql2, conn);
* int count = (int) cmd2.ExecuteScalar();
* System.Console.WriteLine("users count: {0}", count);
* cmd2.Dispose();
*
* // 新增数据
* string sql3 = string.Format(@"INSERT INTO [TUser]
* VALUES('*****@*****.**', 'David', 'david123', '{0}', '{0}')", System.DateTime.UtcNow);
* var cmd3 = new System.Data.SqlClient.SqlCommand(sql3, conn);
* using(cmd3)
* {
* int rows = cmd3.ExecuteNonQuery();
* System.Console.WriteLine(rows);
* }
*
* // 修改
* var sql4 = @"UPDATE [TUser]
* SET [Password] = @Password, [UpdatedAt] = @UpdatedAt
* WHERE [Id] = @Id";
* using(var cmd4 = conn.CreateCommand())
* {
* cmd4.CommandText = sql4;
* cmd4.CommandType = System.Data.CommandType.Text;
* cmd4.Parameters.AddRange(new System.Data.SqlClient.SqlParameter[]
* {
* new System.Data.SqlClient.SqlParameter("@Password", "hello_world"),
* new System.Data.SqlClient.SqlParameter("@UpdatedAt", System.DateTime.UtcNow),
* new System.Data.SqlClient.SqlParameter("@Id", 5),
* });
*
* // SqlCommand.Prepare method requires all parameters to have an explicitly set type.
* // cmd4.Prepare();
*
* // var r = cmd1.ExecuteNonQuery();
* var r = cmd4.ExecuteReader();
* System.Console.WriteLine(r.RecordsAffected);
* }
*/
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
System.Console.WriteLine("MySqlException: {0}", ex.Message);
}
catch (System.Exception ex)
{
System.Console.WriteLine("Exception: {0}", ex.Message);
}
finally
{
System.Console.WriteLine("正在关闭连接...Done!");
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// Responsible for inserting a record to the mailrecord table
/// </summary>
/// <param name="Rloc">
/// record locator value
/// </param>
/// <param name="Created">
/// create timestamp
/// </param>
/// <param name="IPAdress">
/// user ip address
/// </param>
/// <param name="ProcessorName">
/// processor name value
/// </param>
/// <param name="FormatName">
/// format name value
/// </param>
/// <returns>
/// true if successful, false otherwise
/// </returns>
public bool addMailRecord(string Rloc,
System.DateTime Created,
string FromName,
string ProcessorName,
string FormName,
string Subject,
string Team,
string Company
)
{
bool ReturnValue = false;
MySql.Data.MySqlClient.MySqlCommand TmpInsertCommand =
new MySql.Data.MySqlClient.MySqlCommand("CompositionAdd", m_Connection);
string TmpInsertSql;
///////////////////////
// Init error description
ErrorDescription = "";
///////////////////////
// Init parameters
TmpInsertSql = "INSERT INTO `MAILRECORDS` (`RLOC`,`CREATED`,`FROMNAME`,`PROCESSORNAME`,`FORMNAME`,`SUBJECT`,`TEAM`,`COMPANY`) VALUE (?RLOC,?CREATED,?FROMNAME,?PROCESSORNAME,?FORMNAME,?SUBJECT,?TEAM,?COMPANY); ";
// RLOC / PNR
MySql.Data.MySqlClient.MySqlParameter pRLoc =
new MySql.Data.MySqlClient.MySqlParameter("?RLOC", MySql.Data.MySqlClient.MySqlDbType.VarChar, 7);
pRLoc.Value = (Rloc != null) ? (object)Rloc : (object)DBNull.Value;
TmpInsertCommand.Parameters.Add(pRLoc);
// Created
MySql.Data.MySqlClient.MySqlParameter pCreated =
new MySql.Data.MySqlClient.MySqlParameter("?CREATED", MySql.Data.MySqlClient.MySqlDbType.Datetime);
pCreated.Value = (Created != null) ? (object)Created : (object)DBNull.Value;
TmpInsertCommand.Parameters.Add(pCreated);
// FromName / To/From
MySql.Data.MySqlClient.MySqlParameter pFromName =
new MySql.Data.MySqlClient.MySqlParameter("?FROMNAME", MySql.Data.MySqlClient.MySqlDbType.VarChar, 150);
pFromName.Value = (FromName != null) ? (object)FromName : (object)DBNull.Value;
TmpInsertCommand.Parameters.Add(pFromName);
// ProcessorName
MySql.Data.MySqlClient.MySqlParameter pProcessorName =
new MySql.Data.MySqlClient.MySqlParameter("?PROCESSORNAME", MySql.Data.MySqlClient.MySqlDbType.VarChar, 30);
pProcessorName.Value = (ProcessorName != null) ? (object)ProcessorName : (object)DBNull.Value;
TmpInsertCommand.Parameters.Add(pProcessorName);
// FormName / Format Name
MySql.Data.MySqlClient.MySqlParameter pFormName =
new MySql.Data.MySqlClient.MySqlParameter("?FORMNAME", MySql.Data.MySqlClient.MySqlDbType.VarChar, 65);
pFormName.Value = (FormName != null) ? (object)FormName : (object)DBNull.Value;
TmpInsertCommand.Parameters.Add(pFormName);
// Subject
MySql.Data.MySqlClient.MySqlParameter pSubject =
new MySql.Data.MySqlClient.MySqlParameter("?SUBJECT", MySql.Data.MySqlClient.MySqlDbType.VarChar, 500);
pSubject.Value = (Subject != null) ? (object)Subject : (object)DBNull.Value;
TmpInsertCommand.Parameters.Add(pSubject);
// Team
MySql.Data.MySqlClient.MySqlParameter pTeam =
new MySql.Data.MySqlClient.MySqlParameter("?TEAM", MySql.Data.MySqlClient.MySqlDbType.VarChar, 65);
pTeam.Value = (Team != null) ? (object)Team : (object)DBNull.Value;
TmpInsertCommand.Parameters.Add(pTeam);
// Company
MySql.Data.MySqlClient.MySqlParameter pCompany =
new MySql.Data.MySqlClient.MySqlParameter("?COMPANY", MySql.Data.MySqlClient.MySqlDbType.VarChar, 65);
pCompany.Value = (Company != null) ? (object)Company : (object)DBNull.Value;
TmpInsertCommand.Parameters.Add(pCompany);
TmpInsertCommand.CommandType = CommandType.Text;
TmpInsertCommand.CommandText = TmpInsertSql;
m_Connection.Open();
try
{
TmpInsertCommand.ExecuteNonQuery();
ReturnValue = true;
}
catch (MySql.Data.MySqlClient.MySqlException exc)
{
// Log error
ErrorDescription = exc.Message;
}
finally
{
// Clean up
m_Connection.Close();
TmpInsertCommand.Dispose();
}
return(ReturnValue);
}
public void CreateTables()
{
try
{
if (ItIsMSSQL)
{
SqlConnection objConn = new SqlConnection(ConnectionString);
objConn.Open();
var command = new SqlCommand(@"IF NOT EXISTS (select * from sysobjects where id = object_id(N'Events'))
BEGIN
CREATE TABLE[dbo].[Events]([InfobaseCode] int Not NULL, [DateTime][datetime] Not NULL,
[TransactionStatus][varchar](1) NULL,
[TransactionStartTime][datetime] NULL,
[TransactionMark] bigint NULL,
[Transaction][varchar](100) NULL,
[UserName] int NULL,
[ComputerName] int NULL,
[AppName] Int NULL,
[EventID] int NULL,
[EventType][varchar](1) NULL,
[Comment][nvarchar](max) NULL,
[MetadataID] int NULL,
[DataStructure][nvarchar](max) NULL,
[DataString][nvarchar](max) NULL,
[ServerID] int NULL,
[MainPortID] int NULL,
[SecondPortID] int NULL,
[Seance] int NULL);
CREATE CLUSTERED INDEX[CIX_Events] ON[dbo].[Events]([InfobaseCode], [DateTime])
END", objConn);
command.ExecuteNonQuery();
command.CommandText = "IF NOT EXISTS (select * from sysobjects where id = object_id(N'Infobases'))" + Environment.NewLine +
" CREATE TABLE [dbo].[Infobases] ([Guid] [char](40) NOT NULL, [Code] int NOT NULL, [Name] [char](100))" + Environment.NewLine +
" IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'Infobases') AND Name = 'ClusteredIndex')" + Environment.NewLine +
" CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Infobases] ([Guid] ASC);";
command.ExecuteNonQuery();
command.CommandText =
"IF NOT EXISTS (select * from sysobjects where id = object_id(N'Users'))" + Environment.NewLine +
"CREATE TABLE [dbo].[Users]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](100), [Guid] [varchar](40));" + Environment.NewLine +
"IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'Users') AND Name = 'ClusteredIndex')" + Environment.NewLine +
"CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Users] ([InfobaseCode] ASC, [Code] ASC);" + Environment.NewLine +
"" +
"IF NOT EXISTS (select * from sysobjects where id = object_id(N'Metadata'))" + Environment.NewLine +
"CREATE TABLE [dbo].[Metadata]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](100), [Guid] [varchar](40));" + Environment.NewLine +
"IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'Metadata') AND Name = 'ClusteredIndex')" + Environment.NewLine +
"CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Metadata] ([InfobaseCode] ASC, [Code] ASC);" + Environment.NewLine +
"" +
"IF NOT EXISTS (select * from sysobjects where id = object_id(N'Computers'))" + Environment.NewLine +
"CREATE TABLE [dbo].[Computers]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](100));" + Environment.NewLine +
"IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'Computers') AND Name = 'ClusteredIndex')" + Environment.NewLine +
"CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Computers] ([InfobaseCode] ASC, [Code] ASC);" + Environment.NewLine +
"" +
"IF NOT EXISTS (select * from sysobjects where id = object_id(N'Applications'))" + Environment.NewLine +
"CREATE TABLE [dbo].[Applications]([InfobaseCode] int NOT NULL, [Code] int NOT NULL,[Name] [nvarchar](100));" + Environment.NewLine +
"IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'Applications') AND Name = 'ClusteredIndex')" + Environment.NewLine +
"CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Applications] ([InfobaseCode] ASC, [Code] ASC);" + Environment.NewLine +
"" +
"IF NOT EXISTS (select * from sysobjects where id = object_id(N'EventsType'))" + Environment.NewLine +
"CREATE TABLE [dbo].[EventsType]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](max));" + Environment.NewLine +
"IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'EventsType') AND Name = 'ClusteredIndex')" + Environment.NewLine +
"CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[EventsType] ([InfobaseCode] ASC, [Code] ASC);" + Environment.NewLine +
"" +
"IF NOT EXISTS (select * from sysobjects where id = object_id(N'Servers'))" + Environment.NewLine +
"CREATE TABLE [dbo].[Servers]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](100));" + Environment.NewLine +
"IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'Servers') AND Name = 'ClusteredIndex')" + Environment.NewLine +
"CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Servers] ([InfobaseCode] ASC, [Code] ASC);" + Environment.NewLine +
"" +
"IF NOT EXISTS (select * from sysobjects where id = object_id(N'MainPorts'))" + Environment.NewLine +
"CREATE TABLE [dbo].[MainPorts]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](100));" + Environment.NewLine +
"IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'MainPorts') AND Name = 'ClusteredIndex')" + Environment.NewLine +
"CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[MainPorts] ([InfobaseCode] ASC, [Code] ASC);" + Environment.NewLine +
"" +
"IF NOT EXISTS (select * from sysobjects where id = object_id(N'SecondPorts'))" + Environment.NewLine +
"CREATE TABLE [dbo].[SecondPorts]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](100));" + Environment.NewLine +
"IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'SecondPorts') AND Name = 'ClusteredIndex')" + Environment.NewLine +
"CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[SecondPorts] ([InfobaseCode] ASC, [Code] ASC);";
command.ExecuteNonQuery();
command.CommandText = "SELECT TOP 1 * FROM Events";
command.ExecuteReader();
command.Dispose();
objConn.Close();
objConn.Dispose();
}
else if (ItIsMySQL)
{
MySql.Data.MySqlClient.MySqlConnection objConn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString);
objConn.Open();
string DBName = objConn.Database;
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand
{
Connection = objConn,
CommandText = "CREATE TABLE IF NOT EXISTS `Events` (`InfobaseCode` int(11) NOT NULL, `DateTime` int(11) NOT NULL," +
"`TransactionStatus` varchar(1) NULL, `TransactionStartTime` datetime NULL, "+
"`TransactionMark` bigint NULL, `Transaction` varchar(100) NULL, `UserName` int(11) NULL, `ComputerName` int(11) NULL, "+
"`AppName` int(11) NULL, `EventID` int(11) NULL, `EventType` varchar(1) NULL, " +
"`Comment` text NULL, `MetadataID` int(11) NULL, `DataStructure` text NULL, `DataString` text NULL, "+
"`ServerID` int(11) NULL, `MainPortID` int(11) NULL, `SecondPortID` int(11) NULL, `Seance` int(11) NULL"+
") ENGINE=InnoDB DEFAULT CHARSET=utf8;"
};
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IF NOT EXISTS `Infobases` (`Guid` varchar(40) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100)," +
"PRIMARY KEY `Guid` (`Guid`));";
command.ExecuteNonQuery();
command.CommandText =
"CREATE TABLE IF NOT EXISTS `Users`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), `Guid` varchar(40), PRIMARY KEY (`InfobaseCode`, `Code`));" + Environment.NewLine +
"" +
"CREATE TABLE IF NOT EXISTS `Metadata`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), `Guid` varchar(40), PRIMARY KEY (`InfobaseCode`, `Code`));" + Environment.NewLine +
"" +
"CREATE TABLE IF NOT EXISTS `Computers`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), PRIMARY KEY (`InfobaseCode`, `Code`));" + Environment.NewLine +
"" +
"CREATE TABLE IF NOT EXISTS `Applications`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), PRIMARY KEY (`InfobaseCode`, `Code`));" + Environment.NewLine +
"" +
"CREATE TABLE IF NOT EXISTS `EventsType`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` text, PRIMARY KEY (`InfobaseCode`, `Code`));" + Environment.NewLine +
"" +
"CREATE TABLE IF NOT EXISTS `Servers`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), PRIMARY KEY (`InfobaseCode`, `Code`));" + Environment.NewLine +
"" +
"CREATE TABLE IF NOT EXISTS `MainPorts`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), PRIMARY KEY (`InfobaseCode`, `Code`));" + Environment.NewLine +
"" +
"CREATE TABLE IF NOT EXISTS `SecondPorts`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), PRIMARY KEY (`InfobaseCode`, `Code`));";
command.ExecuteNonQuery();
command.Dispose();
objConn.Close();
objConn.Dispose();
}
Log.Info("Target database tables have been verified!");
}
catch (Exception ex)
{
Log.Error(ex, "Error occurred while during target database tables verification");
}
}
public override Bitmap LoadImage(int image_id)
{
MySql.Data.MySqlClient.MySqlDataReader myData;
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
string SQL;
byte[] rawData;
MemoryStream ms;
UInt32 FileSize;
Bitmap outImage;
SQL = "SELECT image_name, image_size, image_data FROM images WHERE id =";
SQL += image_id.ToString();
try
{
cmd.Connection = Connection;
cmd.CommandText = SQL;
myData = cmd.ExecuteReader();
if (!myData.HasRows)
throw new Exception("There are no blobs to save");
myData.Read();
FileSize = myData.GetUInt32(myData.GetOrdinal("image_size"));
rawData = new byte[FileSize];
myData.GetBytes(myData.GetOrdinal("image_data"), 0, rawData, 0, (Int32)FileSize);
ms = new MemoryStream(rawData);
outImage = new Bitmap(ms);
ms.Close();
ms.Dispose();
myData.Close();
myData.Dispose();
cmd.Dispose();
return outImage;
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
return null;
}
}
public void Dispose()
{
Command.Dispose();
Connection.Dispose();
}
public void InsertTestDataToMySQL(string PrinterMT, string PrinterSN, string Status, string Login, bool Firmware, bool Defaults, bool Nvram, bool Fuser, bool Head, bool PSU, bool Scanner, bool ADF, bool OP, bool MB, bool ENG)
{
try
{
if (_conn.State == System.Data.ConnectionState.Open)
{
_conn.Close();
}
_conn.Open();
MySql.Data.MySqlClient.MySqlCommand _mySqlCommand;
string _sql = String.Format("INSERT INTO test (printersid, sn, hddid, status, userid, date, dismantled, firmware, defaults, nvram, F, H, PSU, S, A, OP, MB, ENG) VALUES " +
"(" +
//printersid
"(SELECT printersid FROM printers WHERE mt='{0}'), " +
//printer sn
"'{1}', " +
//hddid
"(SELECT hddid FROM hdd WHERE prtsn='{1}'), " +
//status
"'{2}', " +
//userid
"(SELECT usersid FROM users WHERE login='******'), " +
//date
"'{4}', " +
//dismantled
"'{5}', " +
//firmware
"'{6}', " +
//defaults
"'{7}', " +
//nvram
"'{8}', " +
//F
"'{9}', " +
//H
"'{10}', " +
//PSU
"'{11}', " +
//S
"'{12}', " +
//A
"'{13}', " +
//OP
"'{14}', " +
//MB
"'{15}', " +
//ENG
"'{16}'" +
");", PrinterMT, PrinterSN, Status, Login, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), 0, (Firmware == true?1:0), (Defaults == true?1:0), (Nvram == true?1:0),
(Fuser == true ? 1 : 0), (Head == true ? 1 : 0), (PSU == true ? 1 : 0), (Scanner == true ? 1 : 0), (ADF == true ? 1 : 0), (OP == true ? 1 : 0), (MB == true ? 1 : 0), (ENG == true ? 1 : 0));
_mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn);
_mySqlCommand.ExecuteNonQuery();
_mySqlCommand.Dispose();
_conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.Message);
}
}
