// GET: TradePointsTypes
public JsonResult Index()
{
return(Json(
SQLConnectionHandler.GetInstance()
.Execute(SQLCommand.SelectAllTradePointsTypes()).GetResult(),
JsonRequestBehavior.AllowGet));
}
public static List <DrugStore> GetTopfive()
{
List <DrugStore> lstStore = new List <DrugStore>();
object[] value = null;
var connection = new SQLCommand(ConstValue.ConnectionString);
var result = connection.Select("topfive_Rating", value);
if (connection.errorCode == 0 && result.Rows.Count > 0)
{
foreach (DataRow dr in result.Rows)
{
DrugStore store = new DrugStore()
{
ID = int.Parse(dr["ID"].ToString()),
name = dr["name"].ToString(),
address = dr["address"].ToString(),
district = int.Parse(dr["district"].ToString()),
imgSrc = dr["imgSrc"].ToString(),
openTime = dr["openTime"].ToString(),
closedTime = dr["closedTime"].ToString(),
averageRating = int.Parse(dr["AverageRating"].ToString())
};
lstStore.Add(store);
}
}
return(lstStore);
}
/// <summary>
/// 注意，在调用该方法之前注意先调用Where或LikeWhere方法以更新需要更新的数据，不可首先调用。
/// </summary>
/// <returns></returns>
public int Update()
{
int effect = 0;
string sql = "update " + TableName + " set ";
string id = "";
string parameterName = "";
for (int i = 0; i < record.Columns.Count; i++)
{
if (record.Columns[i].Value != null)
{
id = DateTime.Now.ToFileTime().ToString();
parameterName = "@" + record.Columns[i].Name + "_" + id;
sql += record.Columns[i].Name + " = " + parameterName + ",";
SQLCommand.Parameters.AddWithValue(parameterName, record.Columns[i].Value);
}
}
sql = sql.Remove(sql.Length - 1, 1);
sql += " where 1=1 ";
SQLCommand.CommandText = sql + WhereString;
SQLCommand.Connection.Open();
effect = SQLCommand.ExecuteNonQuery();
SQLCommand.Connection.Close();
return(effect);
}
public static List <Comment> GetAllCmt()
{
List <Comment> lstCmt = new List <Comment>();
object[] value = null;
var connection = new SQLCommand(ConstValue.ConnectionString);
var result = connection.Select("Comments_Getall", value);
if (connection.errorCode == 0 && result.Rows.Count > 0)
{
foreach (DataRow dr in result.Rows)
{
Comment storeComment = new Comment()
{
cid = int.Parse(dr["cid"].ToString()),
name = dr["name"].ToString(),
email = dr["email"].ToString(),
comment = dr["comment"].ToString(),
datetime = Convert.ToDateTime(dr["datePosted"].ToString()),
rating = int.Parse(dr["rating"].ToString()),
storeId = int.Parse(dr["storeId"].ToString())
};
lstCmt.Add(storeComment);
}
}
return(lstCmt);
}
/// <summary>
/// 指用指定条件更新数据，该方法不需调用Where、LikeWhere等方法,也不需要单列指定更新的值，过滤条件由where指定，该Where带参数。
/// </summary>
/// <param name="columnNames">列名数组</param>
/// <param name="values">列对应的值数组</param>
/// <param name="where">过滤条件,最前边带连接关键字and、or，参数形式为：@0、@1</param>
/// <param name="whereValues">where语句中参数的值，要与where中出现的位置对应</param>
/// <returns></returns>
public int Update(string[] columnNames, object[] columnValues, string where, object[] whereValues)
{
int effect = 0;
string sql = "update " + TableName + " set ";
string id = "";
string parameterName = "";
for (int i = 0; i < columnNames.Length; i++)
{
id = DateTime.Now.ToFileTime().ToString();
parameterName = "@" + columnNames[i] + "_" + id;
sql += columnNames[i] + " = " + parameterName + ",";
SQLCommand.Parameters.AddWithValue(parameterName, columnValues[i]);
}
sql = sql.Remove(sql.Length - 1, 1);
sql += " where 1=1 ";
SQLCommand.CommandText = sql + where;
for (int i = 0; i < whereValues.Length; i++)
{
SQLCommand.Parameters.AddWithValue("@" + i.ToString(), whereValues[i]);
}
SQLCommand.Connection.Open();
effect = SQLCommand.ExecuteNonQuery();
SQLCommand.Connection.Close();
return(effect);
}
protected int ExecuteNonQuery(string sqlCommand, CommandType type, SqlTransaction transaction)
{
int affectedRows = 0;
try
{
InitSqlCommand(sqlCommand, SQLParameters.ToArray(), type, SQLConnection);
m_Logger.DebugFormat("__{0}__: {1}: Command Type = {2} , SQL Command = {3}, Parameters = {4}", this.GetType().Name, MethodInfo.GetCurrentMethod().Name, SQLCommand.CommandType, SQLCommand.CommandText, SQLCommand.Parameters.Count);
SQLCommand.Transaction = transaction;
affectedRows = SQLCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception)
{
try
{
transaction.Rollback();
}
catch (Exception)
{
throw;
}
throw;
}
finally
{
ClearSQLParameters();
SQLCommand.Parameters.Clear();
}
return(affectedRows);
}
public static List <Account> GetAll()
{
List <Account> lstStore = new List <Account>();
object[] value = null;
var connection = new SQLCommand(ConstValue.ConnectionString);
var result = connection.Select("Account_Getall", value);
if (connection.errorCode == 0 && result.Rows.Count > 0)
{
foreach (DataRow dr in result.Rows)
{
Account store = new Account()
{
id = int.Parse(dr["id"].ToString()),
username = dr["username"].ToString(),
password = dr["password"].ToString(),
groupId = int.Parse(dr["groupId"].ToString())
};
lstStore.Add(store);
}
}
return(lstStore);
}
public static List <FeedBack> GetAll()
{
List <FeedBack> lstStore = new List <FeedBack>();
object[] value = null;
var connection = new SQLCommand(ConstValue.ConnectionString);
var result = connection.Select("Feedback_Getall", value);
if (connection.errorCode == 0 && result.Rows.Count > 0)
{
foreach (DataRow dr in result.Rows)
{
FeedBack store = new FeedBack()
{
fbId = int.Parse(dr["fbId"].ToString()),
fbName = dr["fbName"].ToString(),
fbEmail = dr["fbEmail"].ToString(),
fbPhone = dr["fbPhone"].ToString(),
fbContent = dr["fbContent"].ToString()
};
lstStore.Add(store);
}
}
return(lstStore);
}
public static List <DrugStore> GetAllSearch(object[] value, ref string[] output, ref int errorCode,
ref string errorMessage)
{
List <DrugStore> lstStore = new List <DrugStore>();
var connection = new SQLCommand(ConstValue.ConnectionString);
var result = connection.Select("DrugStore_GetallSearch", value);
if (connection.errorCode == 0 && result.Rows.Count > 0)
{
foreach (DataRow dr in result.Rows)
{
DrugStore store = new DrugStore()
{
ID = int.Parse(dr["ID"].ToString()),
name = dr["name"].ToString(),
address = dr["address"].ToString(),
district = int.Parse(dr["district"].ToString()),
phone = dr["phone"].ToString(),
imgSrc = dr["imgSrc"].ToString(),
status = int.Parse(dr["status"].ToString()),
categoryId = int.Parse(dr["categoryId"].ToString()),
openTime = dr["openTime"].ToString(),
closedTime = dr["closedTime"].ToString(),
averageRating = int.Parse(dr["AverageRating"].ToString())
};
lstStore.Add(store);
}
}
return(lstStore);
}
protected override void InternalOpen()
{
// Connect to the Catalog Store
_connection = Session.Device.Store.GetSQLConnection();
_connection.BeginTransaction(SQLIsolationLevel.ReadUncommitted);
// Create a command using DevicePlanNode.Statement
_command = _connection.CreateCommand(true);
_command.CursorLocation = SQLCursorLocation.Server;
_command.CommandBehavior = SQLCommandBehavior.Default;
_command.CommandType = SQLCommandType.Statement;
_command.LockType = SQLLockType.ReadOnly;
_command.Statement = _devicePlanNode.Statement.ToString();
// Set the parameter values
foreach (CatalogPlanParameter planParameter in _devicePlanNode.PlanParameters)
{
_command.Parameters.Add(planParameter.SQLParameter);
planParameter.SQLParameter.Value = GetSQLValue(planParameter.PlanNode.DataType, planParameter.PlanNode.Execute(Program));
}
// Open a cursor from the command
_cursor = _command.Open(SQLCursorType.Dynamic, SQLIsolationLevel.ReadUncommitted);
_bOF = true;
_eOF = !_cursor.Next();
}
static void Main()
{
//Windows Authentication
//@"Data Source =(Machine Name)\(InstanceName)Initial Catalog=(DBName);Integrated Secuirty = true;"
//SQL Authentiication
//@"Data Source =(Machine Name)\(InstanceName)Initial Catalog=(DBName);ID=(username);Password=(Password);"
SqlConnection SqlConnection = new SqlConnection();
SqlConnection.open();
SqlCommand queryOne = new SQLCommand("Select * from products", SqlConnection);
SqlDataReader reader = queryOne.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
}
reader.Close();
SqlConnection.Close();
if (Debugger.isAttached)
{
Console.ReadLine();
}
}
public static List <Page> Page_GetAll()
{
List <Page> lstPage = new List <Page>();
object[] value = null;
var connection = new SQLCommand(ConstValue.ConnectionString);
var result = connection.Select("lp_page_GetAll", value);
if (connection.errorCode == 0 && result.Rows.Count > 0)
{
foreach (DataRow dr in result.Rows)
{
Page page = new Page()
{
id = int.Parse(dr["id"].ToString()),
name = dr["name"].ToString(),
alias = dr["alias"].ToString(),
note = dr["note"].ToString(),
permission = int.Parse(dr["permission"].ToString()),
};
lstPage.Add(page);
}
}
return(lstPage);
}
public static List <DrugDetails> GetAll()
{
List <DrugDetails> lstStore = new List <DrugDetails>();
object[] value = null;
var connection = new SQLCommand(ConstValue.ConnectionString);
var result = connection.Select("DrugDetails_Getall", value);
if (connection.errorCode == 0 && result.Rows.Count > 0)
{
foreach (DataRow dr in result.Rows)
{
DrugDetails store = new DrugDetails()
{
id = int.Parse(dr["id"].ToString()),
drugname = dr["drugname"].ToString(),
price = int.Parse(dr["price"].ToString()),
note = dr["note"].ToString(),
iddrugstore = int.Parse(dr["iddrugstore"].ToString())
};
lstStore.Add(store);
}
}
return(lstStore);
}
public List<Requirement> GetRequirements(string projectID)
{
List<Requirement> tempList = new List<Requirement>();
Requirement req = new Requirement();
using (var sc = new SQLCommand("RMsisDB"))
{
sc.CommandText.AppendFormat("SELECT * from {0} where {1} = {2}",
sc.WrapObjectName("Requirement"),
sc.WrapObjectName("project_id"),
projectID);
using (var dr = sc.ExecuteReader())
{
while (dr.Read())
{
req = new Requirement();
req.ID = dr.GetValue(RMSIS_REQ_ID_LOCATION).ToString();
req.Description = dr.GetValue(RMSIS_REQ_DESC_LOCATION).ToString();
tempList.Add(req);
}
}
}
return tempList;
}
public string Build(SQLCommand sqlCmd)
{
StringBuilder queryBuilder;
switch (sqlCmd)
{
case SQLCommand.SELECT:
queryBuilder = BuildSelect();
break;
case SQLCommand.CREATE:
queryBuilder = BuildCreate();
break;
default:
queryBuilder = new StringBuilder(Environment.NewLine);
break;
}
if (queryBuilder == null)
{
return(string.Empty);
}
// apply text replacements
foreach (var textReplacement in _replacements)
{
queryBuilder.Replace(textReplacement.Key, textReplacement.Value);
}
// remove first CR
queryBuilder.Remove(0, Environment.NewLine.Length);
return(queryBuilder.ToString());
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="callBack">Called when command has been executed</param>
/// <param name="callbackObject">Object</param>
/// <param name="header">
/// Determines if this command is a "header" and should be carried across batches.
/// </param>
/// <param name="sqlCommand">SQL Command</param>
/// <param name="commandType">Command type</param>
/// <param name="parameterStarter">Parameter starter</param>
/// <param name="parameters">Parameters</param>
public Command(Action <ICommand, List <dynamic>, TCallbackData> callBack, TCallbackData callbackObject, bool header, string sqlCommand, CommandType commandType, string parameterStarter, object[]?parameters)
{
SQLCommand = sqlCommand ?? string.Empty;
CommandType = commandType;
parameters ??= Array.Empty <object>();
Parameters = new IParameter[parameters.Length];
CallBack = callBack ?? DefaultAction;
CallbackData = callbackObject;
DetermineFinalizable(parameterStarter, SQLCommand.ToUpperInvariant());
Header = header;
for (int x = 0, parametersLength = parameters.Length; x < parametersLength; ++x)
{
var CurrentParameter = parameters[x];
if (CurrentParameter is IParameter parameter)
{
Parameters[x] = parameter;
}
else if (CurrentParameter is null)
{
Parameters[x] = new Parameter <object>(x.ToString(CultureInfo.InvariantCulture), default(DbType), null, ParameterDirection.Input, parameterStarter);
}
else if (CurrentParameter is string TempParameter)
{
Parameters[x] = new StringParameter(x.ToString(CultureInfo.InvariantCulture), TempParameter, ParameterDirection.Input, parameterStarter);
}
else
{
Parameters[x] = new Parameter <object>(x.ToString(CultureInfo.InvariantCulture), CurrentParameter, ParameterDirection.Input, parameterStarter);
}
}
}
public bool Insert(Employee employee)
{
SQLConnectionHandler.GetInstance()
.Execute(SQLCommand.InsertEmployee(employee), true);
SQLConnectionHandler.GetInstance()
.Execute(SQLCommand.InsertEmployeesList(employee), true);
return(true);
}
public void InitTable()
{
DepartureList = new List <Stop>();
ArriveList = new List <Stop>();
SQLCommand.Search(date, miejsce, ArriveList, DepartureList);
lstTrain.ItemsSource = ArriveList;
lstTrain_Copy.ItemsSource = DepartureList;
}
protected void DisposeExecuteCommand()
{
if (_executeCommand != null)
{
_executeCommand.Dispose();
_executeCommand = null;
}
}
// GET: ObjectRelations
public JsonResult Index()
{
return(Json(
SQLConnectionHandler.GetInstance()
.Execute(SQLCommand.SelectAllObjectsRelations()).GetResult(),
JsonRequestBehavior.AllowGet
));
}
public async static Task InitializeDatabasePlayer(rFrameworkPlayer rPlayer)
{
string SQLQuery = "SELECT * FROM `users` WHERE `identifier` = '" + rPlayer.SteamID + "'";
DebugWrite(SQLQuery);
MySqlConnection SQLConnection = GetDBConnection();
await SQLConnection.OpenAsync();
MySqlCommand SQLCommand = new MySqlCommand(SQLQuery, SQLConnection);
MySqlDataReader SQLDataReader = (MySqlDataReader)await SQLCommand.ExecuteReaderAsync();
if (!SQLDataReader.Read())
{
SQLQuery = "INSERT INTO `users` (identifier, name) VALUES ('" + rPlayer.SteamID + "', '" + rPlayer.CorePlayer.Name + "')";
DebugWrite(SQLQuery);
SQLConnection = GetDBConnection();
SQLCommand = new MySqlCommand(SQLQuery, SQLConnection);
await SQLConnection.OpenAsync();
await SQLCommand.ExecuteNonQueryAsync();
rPlayer.BankBalance = 0;
rPlayer.CashBalance = 0;
rPlayer.Vehicles = "";
}
else
{
rPlayer.BankBalance = SQLDataReader.GetInt64(3);
rPlayer.CashBalance = SQLDataReader.GetInt64(4);
rPlayer.Vehicles = SQLDataReader.GetString(5);
SQLCommand.Dispose();
SQLDataReader.Dispose();
//Get transactions
SQLQuery = "SELECT * FROM `transactions` WHERE `from_player` = '" + rPlayer.SteamID + "' OR 'to_player' = '" + rPlayer.SteamID + "'";
SQLCommand = new MySqlCommand(SQLQuery, SQLConnection);
SQLDataReader = (MySqlDataReader)await SQLCommand.ExecuteReaderAsync();
List <rBankTransfer> transactions = new List <rBankTransfer>();
while (SQLDataReader.Read())
{
rBankTransfer transaction = new rBankTransfer(SQLDataReader.GetString(1), SQLDataReader.GetString(0),
SQLDataReader.GetString(2), (SQLDataReader.GetString(0).Equals(SQLDataReader.GetString(1)) ? true : false),
(SQLDataReader.GetString(3).Equals("Cash Withdrawn")) ? true : false, (int)SQLDataReader.GetInt64(3), DateTime.Parse(SQLDataReader.GetString(4)));
transactions.Add(transaction);
}
rPlayer.Transfers = transactions;
}
SQLCommand.Dispose();
SQLDataReader.Dispose();
SQLConnection.Dispose();
return;
}
public bool Update(TradePointProduct product)
{
if (product.Id < 0)
{
return(false);
}
SQLConnectionHandler.GetInstance()
.Execute(SQLCommand.UpdateTradePointProduct(product), true);
return(true);
}
public bool Update(TradePointSale sale)
{
if (sale.Id < 0)
{
return(false);
}
SQLConnectionHandler.GetInstance()
.Execute(SQLCommand.UpdateTradePointSale(sale), true);
return(true);
}
/// <summary>
/// Returns the hash code for the command
/// </summary>
/// <returns>The hash code for the object</returns>
public override int GetHashCode()
{
int ParameterTotal = Parameters.Sum(x => x.GetHashCode());
if (ParameterTotal > 0)
{
return((SQLCommand.GetHashCode() * 23 + CommandType.GetHashCode()) * 23 + ParameterTotal);
}
return(SQLCommand.GetHashCode() * 23 + CommandType.GetHashCode());
}
public bool Update(TradePointRequest request)
{
if (request.Id < 0)
{
return(false);
}
SQLConnectionHandler.GetInstance()
.Execute(SQLCommand.UpdateTradePointRequest(request), true);
return(true);
}
public bool Delete(ObjectRelation relation)
{
if (relation.Id < 0)
{
return(false);
}
SQLConnectionHandler.GetInstance()
.Execute(SQLCommand.DeleteObjectsRelations(relation), true);
return(true);
}
public bool Update(TradePointCustomer customer)
{
if (customer.Id < 0)
{
return(false);
}
SQLConnectionHandler.GetInstance()
.Execute(SQLCommand.UpdateTradePointsCustomer(customer), true);
return(true);
}
public bool Delete(TradePointType type)
{
if (type.Id < 0)
{
return(false);
}
SQLConnectionHandler.GetInstance()
.Execute(SQLCommand.DeleteTradePointType(type), true);
return(true);
}
public bool Update(Supplier supplier)
{
if (supplier.Id < 0)
{
return(false);
}
SQLConnectionHandler.GetInstance()
.Execute(SQLCommand.UpdateSupplier(supplier), true);
return(true);
}
public bool Insert(TradePointPayment payment)
{
if (payment.Id < 0)
{
return(false);
}
SQLConnectionHandler.GetInstance()
.Execute(SQLCommand.InsertTradePointPayment(payment), true);
return(true);
}
public bool Delete(TradePoint point)
{
if (point.Id < 0)
{
return(false);
}
SQLConnectionHandler.GetInstance()
.Execute(SQLCommand.DeleteTradePoint(point), true);
return(true);
}
public List<Project> GetProjectList()
{
List<Project> tempList = new List<Project>();
Project proj = new Project();
using (var sc = new SQLCommand("RMsisDB"))
{
sc.CommandText.AppendFormat("SELECT * from {0}",
sc.WrapObjectName("Project"));
using (var dr = sc.ExecuteReader())
{
while (dr.Read())
{
Object[] val = new Object[dr.FieldCount];
dr.GetValues(val);
proj = new Project();
proj.ID = dr.GetValue(RMSIS_PROJECT_ID_LOCATION).ToString();
proj.Name = dr.GetValue(RMSIS_PROJECT_NAME_LOCATION).ToString();
tempList.Add(proj);
}
}
}
return tempList;
}
public string Build(SQLCommand sqlCmd)
{
StringBuilder queryBuilder;
switch (sqlCmd)
{
case SQLCommand.SELECT:
queryBuilder = BuildSelect();
break;
case SQLCommand.CREATE:
queryBuilder = BuildCreate();
break;
default:
queryBuilder = new StringBuilder(Environment.NewLine);
break;
}
if (queryBuilder == null)
{
return string.Empty;
}
// apply text replacements
foreach (var textReplacement in _replacements)
{
queryBuilder.Replace(textReplacement.Key, textReplacement.Value);
}
// remove first CR
queryBuilder.Remove(0, Environment.NewLine.Length);
return queryBuilder.ToString();
}
public object Any(SQLCommand request)
{
if (auth.AuthResult(this.Request.Headers.GetValues("Signature"), this.Request.RawUrl))
{
ArrayList ht = sqlCommandLogic.ShowResponse(request);
return new SQLCommandResponse { objResult = ht };
}
return new AuthFaildResponse { strResult = "No Authentication." };
}
private static string GetSQLCommand(SQLCommand pCommand, string pTable, List<string> mFields, List<string> mParameters)
{
StringBuilder sb = new StringBuilder();
switch (pCommand)
{
case SQLCommand.INSERT:
sb.Append("INSERT INTO ");
sb.Append(pTable);
sb.Append("(");
foreach (string s in mFields)
{
sb.Append("\"" + s + "\"");
if (s != mFields[mFields.Count - 1])
sb.Append(",");
}
sb.Append(") VALUES (");
foreach (string s in mParameters)
{
sb.Append("'" + s + "'");
if (s != mParameters[mParameters.Count - 1])
sb.Append(",");
}
sb.Append(");");
return sb.ToString();
case SQLCommand.DELETE:
throw new NotImplementedException();
case SQLCommand.SELECT:
throw new NotImplementedException();
case SQLCommand.UPDATE:
throw new NotImplementedException();
default:
throw new NotImplementedException();
}
}
