public static void CloseProgram()
{
try
{
if (Conn != null)
{
if (Conn?.State == ConnectionState.Open)
{
Conn?.Close();
}
}
}
catch {}
//close splashscreen and splashscreen thread
CloseSplash();
//remove event handlers
try
{
Current.Dispatcher.UnhandledException -= Dispatcher_SystemException;
NetworkStatus.AvailabilityChanged -= new NetworkStatusChangedHandler(DoAvailabilityChanged);
}
catch { }
finally
{
Environment.Exit(0);
}
}
private void Disconnect()
{
if ((Conn?.IsConnected).GetValueOrDefault())
{
Conn?.Close();
}
}
//关闭
public void Close()
{
for (int i = 0; i < conns.Length; i++)
{
Conn conn = conns[i];
if (conn == null)
{
continue;
}
if (!conn.isUse)
{
continue;
}
lock (conn)
{
conn.Close();
}
}
}
public int ExecuteInsert(DbCommand dbCommand)
{
PrepareCommand(dbCommand);
int obj = -1;
try
{
obj = ExecuteScalar <int>(dbCommand.CommandText + ";select @@IDENTITY");
}
catch
{
throw;
}
finally
{
Conn.Close();
}
return(obj);
}
/// <summary>
/// 返回插入
/// </summary>
/// <param name="commandText"></param>
/// <param name="dbParameters"></param>
/// <returns></returns>
public int ExecuteInsert(string commandText, params DbParameter[] dbParameters)
{
PrepareCommand(commandText, dbParameters);
int obj = -1;
try
{
obj = ExecuteScalar <int>(commandText + ";select @@IDENTITY");
}
catch
{
throw;
}
finally
{
Conn.Close();
}
return(obj);
}
public static DataTable DataTableQuary(string SqlString)
{
// new com.jwsoft.common.EncryptDog.EncryptDog().IsAuthorization();
DataTable dataTable = new DataTable();
SqlConnection connection = new Conn().SqlConnectionSystem();
try
{
new SqlDataAdapter {
SelectCommand = new SqlCommand(SqlString, connection)
}.Fill(dataTable);
}
catch (SqlException exception)
{
connection.Close();
throw exception;
}
return(dataTable);
}
public static int ExecuteSQL(string sqlString)
{
int num = -1;
SqlConnection connection = new Conn().SqlConnectionSystem();
try
{
num = new SqlCommand(sqlString, connection).ExecuteNonQuery();
}
catch
{
num = -1;
}
finally
{
connection.Close();
}
return(num);
}
/*********************************************************************************************************************************************/
/*
* ado.net -- DbCommand.[Task<DbDataReader> ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)]
* select -- 所有行
*/
internal async Task <List <M> > ExecuteReaderMultiRowAsync <M>()
where M : class
{
var command = new CommandInfo(SqlCount == 1 ? SqlOne : SqlTwo, Parameter);
bool needClose = Conn.State == ConnectionState.Closed;
using (var cmd = TrySetupAsyncCommand(command, Conn, command.Parameter.ParamReader))
{
DbDataReader reader = null;
try
{
if (needClose)
{
await TryOpenAsync(Conn).ConfigureAwait(false);
}
reader = await ExecuteReaderWithFlagsFallbackAsync(cmd, needClose, XConfig.MultiRow).ConfigureAwait(false);
if (reader.FieldCount == 0)
{
return(new List <M>());
}
var func = DC.SC.GetHandle <M>(SqlCount == 1 ? SqlOne : SqlTwo, reader);
var result = new List <M>();
while (await reader.ReadAsync(default(CancellationToken)).ConfigureAwait(false))
{
result.Add(func(reader));
}
while (await reader.NextResultAsync(default(CancellationToken)).ConfigureAwait(false))
{
}
return(result);
}
finally
{
using (reader) { }
if (needClose)
{
Conn.Close();
}
}
}
}
public override void SaveTransaction()
{
try
{
Conn.Open();
//preparing statement
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = Conn;
string stmt = "INSERT INTO rentals(referenceno,customerid,date_of_rental,date_of_use,date_of_pickup,date_of_return,purpose,total_amount,downpayment,notes) ";
stmt += "VALUES(?referenceno,?customerid,?dateofrental,?dateofuse,?dateofpickup,?dateofreturn,?purpose,?amount,?downpayment,?notes);";
cmd.CommandText = stmt;
cmd.Prepare();
//binding parameters
cmd.Parameters.AddWithValue("?referenceno", ReferenceNo);
cmd.Parameters.AddWithValue("?customerid", CustomerId);
cmd.Parameters.AddWithValue("?dateofrental", _dateOfRental);
cmd.Parameters.AddWithValue("?dateofuse", _dateOfUse);
cmd.Parameters.AddWithValue("?dateofpickup", _dateOfPickUp);
cmd.Parameters.AddWithValue("?dateofreturn", _dateOfReturn);
cmd.Parameters.AddWithValue("?purpose", _purpose);
cmd.Parameters.AddWithValue("?amount", TotalAmount);
cmd.Parameters.AddWithValue("?downpayment", Downpayment);
cmd.Parameters.AddWithValue("?notes", Notes);
cmd.ExecuteNonQuery();
//MessageBox.Show("Rent Transaction Saved!");
}
catch (MySqlException ex)
{
Trace.WriteLine("Error: {0}", ex.ToString());
MessageBox.Show("Error occurred in saving the transaction.");
}
finally
{
Conn.Close();
}
}
public void AddTeacher(int id)
{
MySqlConnection cmd = Blog.AccessDatabase();
Conn.Open();
// I could not find correct query to insert into teachers table
// 'Insert into teacher (columns) values ();
cmd.CommandText = "Insert into teachers (teacherid, teacherfname, teacherlname, employeenumber) " +
"values (@id)";
cmd.Parameters.AddWithValue("@id", id);
cmd.Prepare();
cmd.ExecuteNonQuery();
Conn.Close();
}
public virtual bool BulkUpdate(IList <T> entities, IDbTransaction transaction = null,
int?commandTimeout = null)
{
if (entities == null)
{
throw new ArgumentNullException("entities", "Add to DB null entities");
}
if (transaction == null)
{
Conn.Open();
using (transaction = Conn.BeginTransaction())
{
var result = Conn.Update(entities, transaction, commandTimeout);
transaction.Commit();
Conn.Close();
return(result);
}
}
return(false);
}
public virtual async Task <bool> BulkRemoveAsync(IList <T> entities, IDbTransaction transaction = null, int?commandTimeout = null)
{
if (entities == null)
{
throw new ArgumentNullException("entities", "Add to DB null entities");
}
if (transaction == null)
{
Conn.Open();
using (transaction = Conn.BeginTransaction())
{
var result = await Conn.DeleteAsync(entities, transaction, commandTimeout);
transaction.Commit();
Conn.Close();
return(result);
}
}
return(await Task.FromResult(false));
}
public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlDataReader reader2;
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new Conn().SqlConnectionSystem();
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
reader2 = reader;
}
catch
{
conn.Close();
throw;
}
return(reader2);
}
public virtual long BulkAdd(IList <T> entities, IDbTransaction transaction = null,
int?commandTimeout = null)
{
if (entities == null)
{
throw new ArgumentNullException("entities", "Add to DB null entities");
}
if (transaction == null)
{
Conn.Open();
using (transaction = Conn.BeginTransaction())
{
var affectedRows = Conn.Insert(entities, transaction, commandTimeout);
transaction.Commit();
Conn.Close();
return(affectedRows);
}
}
return(0);
}
public void Dispose()
{
if (DT != null)
{
if (!Commited)
{
DT.Rollback();
}
DT.Dispose();
}
if (Conn != null && Conn.State != ConnectionState.Closed)
{
Conn.Close();
Conn.Dispose();
}
if (_CurrentSession != null)
{
_CurrentSession = null;
}
}
public void SaveInfo()
{
try
{
Conn.Open();
//preparing statement
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = Conn;
string stmt = "INSERT INTO products(name,clothtype,quantity,description,size_fit,rentprice,amount,category,status) ";
stmt += "VALUES(?name,?clothtype,?quantity,?description,?sizefit,?rentprice,?amount,?category,?status);";
cmd.CommandText = stmt;
cmd.Prepare();
//binding parameters
cmd.Parameters.AddWithValue("?name", _name);
cmd.Parameters.AddWithValue("?clothtype", _clothType);
cmd.Parameters.AddWithValue("?quantity", _quantity);
cmd.Parameters.AddWithValue("?description", _description);
cmd.Parameters.AddWithValue("?sizefit", _sizeFit);
cmd.Parameters.AddWithValue("?rentprice", _rentPrice);
cmd.Parameters.AddWithValue("?amount", _amount);
cmd.Parameters.AddWithValue("?category", _category);
cmd.Parameters.AddWithValue("?status", _status);
cmd.ExecuteNonQuery();
MessageBox.Show("Product Added!");
}
catch (MySqlException ex)
{
Trace.WriteLine("Error: {0}", ex.ToString());
MessageBox.Show("Error occurred in saving the product.");
}
finally
{
Conn.Close();
}
}
private nint GetRecordCount()
{
bool shouldClose = false;
nint count = 0;
// Has a Table, ID and display field been specified?
if (TableName != "" && IDField != "" && DisplayField != "")
{
// Is the database already open?
if (Conn.State != ConnectionState.Open)
{
shouldClose = true;
Conn.Open();
}
// Execute query
using (var command = Conn.CreateCommand()) {
// Create new command
command.CommandText = $"SELECT count({IDField}) FROM [{TableName}]";
// Get the results from the database
using (var reader = command.ExecuteReader()) {
while (reader.Read())
{
// Read count from query
var result = (long)reader [0];
count = (nint)result;
}
}
}
// Should we close the connection to the database
if (shouldClose)
{
Conn.Close();
}
}
// Return the number of records
return(count);
}
public override void UpdateTransaction()
{
try
{
Conn.Open();
//preparing statement
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = Conn;
string stmt = "UPDATE sales SET ";
stmt += "customerid=?customerid,";
//stmt += "date_of_purchase=?dateofpurchase,";
stmt += "amount=?amount,";
stmt += "notes=?notes ";
stmt += "WHERE salesid=?salesid;";
cmd.CommandText = stmt;
cmd.Prepare();
//binding parameters
cmd.Parameters.AddWithValue("?salesid", TransactionId);
cmd.Parameters.AddWithValue("?customerid", CustomerId);
//cmd.Parameters.AddWithValue("?dateofpurchase", _dateOfPurchase);
cmd.Parameters.AddWithValue("?amount", TotalAmount);
cmd.Parameters.AddWithValue("?notes", Notes);
cmd.ExecuteNonQuery();
//MessageBox.Show("Updated Successfully!");
}
catch (MySqlException ex)
{
Trace.WriteLine("Error: {0}", ex.ToString());
MessageBox.Show("Error occurred in updating the transaction.");
}
finally
{
Conn.Close();
}
}
/*********************************************************************************************************************************************/
/*
* ado.net -- DbCommand.[Task<DbDataReader> ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)]
* select -- 所有行
*/
internal async Task <List <M> > ExecuteReaderMultiRowAsync <M>()
{
var comm = new CommandInfo(SqlCount == 1 ? SqlOne : SqlTwo, Parameter);
bool needClose = Conn.State == ConnectionState.Closed;
using (var cmd = SettingCommand(comm, Conn, comm.Parameter.ParamReader))
{
DbDataReader reader = null;
try
{
if (needClose)
{
await OpenAsync(Conn);
}
reader = await ExecuteReaderWithFlagsFallbackAsync(cmd, needClose, XConfig.MultiRow);
if (reader.FieldCount == 0)
{
return(new List <M>());
}
var func = DC.XC.GetHandle <M>(SqlCount == 1 ? SqlOne : SqlTwo, reader);
var result = new List <M>();
while (await reader.ReadAsync())
{
result.Add(func(reader));
}
while (await reader.NextResultAsync())
{
}
return(result);
}
finally
{
using (reader) { }
if (needClose)
{
Conn.Close();
}
}
}
}
public void HeartBeat()
{
//Debug.Log("[主定时器执行]");
long timeNow = Utility.GetTimeStamp();
for (int i = 0; i < conns.Length; i++)
{
Conn conn = conns[i];
if (!conn.isUse)
{
continue;
}
if (conn.lastTickTime < timeNow - ServerConfig.HEART_BEAT_TIME)
{
Debug.Log("[心跳引起断开连接]" + conn.GetAdress());
lock (conn)
conn.Close();
}
}
}
public static void FillDataSet(DataSet data, string SqlString, string TableName)
{
//new com.jwsoft.common.EncryptDog.EncryptDog().IsAuthorization();
SqlDataAdapter adapter = new SqlDataAdapter();
SqlConnection connection = new Conn().SqlConnectionSystem();
try
{
adapter.SelectCommand = new SqlCommand(SqlString, connection);
adapter.Fill(data, TableName);
}
catch (SqlException exception)
{
throw exception;
}
finally
{
connection.Close();
adapter.Dispose();
}
}
public bool deleteAllNotification()
{
bool flag = false;
try
{
ConnectDB();
MySqlCommand query = new MySqlCommand("DELETE FROM notificaciones;", Conn);
Conn.Open();
query.ExecuteNonQuery();
flag = true;
}
catch (MySqlException e) {
Console.WriteLine(e.Message);
}
finally{
disconnectDB();
Conn.Close();
}
return(flag);
}
public static List <String> getCustomerIdsInOrdersProductsTable()
{
openConnectionIfClosed();
List <String> customerIdsList = new List <string>();
using (var command = Conn.CreateCommand())
{
command.CommandText = "SELECT CUSTOMER_ID FROM ORDERS";
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
customerIdsList.Add(reader[0].ToString());
}
}
}
Conn.Close();
return(customerIdsList);
}
public virtual DataTable GetAllRecords(string tblName)
{
DataTable dt = null;
if (Conn != null)
{
try
{
Conn.Open();
dt = new DataTable();
Cmd.CommandText = String.Format("Use {0} SELECT * FROM {1}", BaseName, tblName);
dt.Load(Cmd.ExecuteReader());
Conn.Close();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
return(dt);
}
/// <summary>
/// 登出消息处理，正常登出返回0
/// </summary>
/// <param name="conn"></param>
/// <param name="protoBase"></param>
public void MsgLogout(Conn conn, ProtocolBase protoBase)
{
ProtocolBytes protoBytes = new ProtocolBytes();
protoBytes.AddString("Logout");
protoBytes.AddInt(0);
//存在两种下线情况
if (conn.player == null)
{
conn.Send(protoBytes);
conn.Close();
}
else
{
//Logout方法调用Close，必须先Send
conn.Send(protoBytes);
conn.player.Logout();
}
}
public static DataTable ExecuteDataTable(CommandType cmdType, string spName, SqlParameter[] commandParameters)
{
// new com.jwsoft.common.EncryptDog.EncryptDog().IsAuthorization();
SqlConnection connection = new Conn().SqlConnectionSystem();
SqlCommand command = new SqlCommand();
SqlDataAdapter adapter = new SqlDataAdapter();
DataTable dataTable = new DataTable();
try
{
command.Connection = connection;
command.CommandText = spName;
command.CommandType = cmdType;
if (commandParameters != null)
{
foreach (SqlParameter parameter in commandParameters)
{
if ((parameter.Direction == ParameterDirection.InputOutput) && (parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
}
adapter.SelectCommand = command;
adapter.Fill(dataTable);
command.Parameters.Clear();
}
catch (SqlException exception)
{
throw exception;
}
finally
{
adapter.Dispose();
command.Dispose();
connection.Close();
}
return(dataTable);
}
public override NSObject ObjectValueForItem(NSComboBox comboBox, nint index)
{
NSString value = new NSString("");
bool shouldClose = false;
// Has a Table, ID and display field been specified?
if (TableName != "" && IDField != "" && DisplayField != "")
{
// Is the database already open?
if (Conn.State != ConnectionState.Open)
{
shouldClose = true;
Conn.Open();
}
// Execute query
using (var command = Conn.CreateCommand()) {
// Create new command
command.CommandText = $"SELECT {DisplayField} FROM [{TableName}] ORDER BY {DisplayField} ASC LIMIT 1 OFFSET {index}";
// Get the results from the database
using (var reader = command.ExecuteReader()) {
while (reader.Read())
{
// Read the display field from the query
value = new NSString((string)reader [0]);
}
}
}
// Should we close the connection to the database
if (shouldClose)
{
Conn.Close();
}
}
// Return results
return(value);
}
public override void Create()
{
MyDataSet.WriteXmlSchema(DirPath + "\\" + BaseName + "_sheme.xsd");// запись схемы данных
try
{
Conn.Open();
if (MyDataSet != null)
{
Cmd.CommandText = string.Format("if (db_id('{0}') is not null) DROP DATABASE [{0}] CREATE DATABASE [{0}] ", BaseName);
Cmd.ExecuteNonQuery();
foreach (DataTable tbl in MyDataSet.Tables)
{
Cmd.CommandText = string.Format("USE [{0}] CREATE TABLE {1} (", BaseName, tbl.TableName);
foreach (DataColumn col in tbl.Columns)
{
Cmd.CommandText += string.Format("[{0}] {1}, ", col.ColumnName, new SqlField(col).GenerateSql());
}
Cmd.CommandText = Cmd.CommandText.Remove(Cmd.CommandText.Length - 2);
Cmd.CommandText += string.Format(@")");
Cmd.ExecuteNonQuery();
}
}
MessageBox.Show("DataBase (sql): " + BaseName + " has created Successfully", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public int ExecuteNonQuerySproc(DbCommand command)
{
command.Connection = Conn;
command.CommandTimeout = 0;
try
{
if (ConnectionState.Closed == Conn.State)
{
Conn.Open();
}
return(command.ExecuteNonQuery());
}
finally
{
if (ConnectionState.Open == Conn.State)
{
Conn.Close();
}
}
}
/// <summary>
/// Executes a query with no return value.
/// </summary>
/// <param name="command">The raw command object.</param>
public void ExecuteNonQuery(System.Data.Common.DbCommand command)
{
command.Connection = Conn;
command.CommandTimeout = 0;
try
{
if (ConnectionState.Closed == Conn.State)
{
Conn.Open();
}
command.ExecuteNonQuery();
}
finally
{
if (ConnectionState.Open == Conn.State)
{
Conn.Close();
}
}
}
