// 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(); } }