public IOfferedServiceDTO GetLatestOfferedServiceByProviderId(int providerId) { var offeredServiceDTO = new OfferedServiceDTO(); string query = "SELECT * FROM offeredservices WHERE offeredservices.providerId = " + providerId + " ORDER BY offeredservices.id DESC LIMIT 1; "; using var connection = MySqlFactory.CreateConnection(); connection.Open(); MySqlCommand command = MySqlFactory.CreateCommand(query, connection); MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { offeredServiceDTO.Id = reader.GetInt32("id"); offeredServiceDTO.Name = reader.GetString("name"); offeredServiceDTO.CategoryId = reader.GetInt16("categoryId"); offeredServiceDTO.Price = reader.GetDecimal("price"); offeredServiceDTO.StatusId = reader.GetInt16("status"); offeredServiceDTO.DeliveryTimeDays = reader.GetInt32("deliveryTimeDays"); offeredServiceDTO.DeliveryTimeHours = reader.GetInt32("deliveryTimeHours"); offeredServiceDTO.DateOfPost = reader.GetDateTime("dateOfpost"); offeredServiceDTO.Description = reader.GetString("description"); offeredServiceDTO.ProviderId = reader.GetInt32("providerId"); offeredServiceDTO.IsDeleted = Convert.ToBoolean(reader.GetInt16("isDeleted")); } return(offeredServiceDTO); }
private IEnumerable <IOfferedServiceDTO> GetOfferedServicesWhere(string whereClause) { var offeredServiceDTOs = new List <IOfferedServiceDTO>(); string query = "SELECT * FROM offeredServices WHERE " + whereClause; using var connection = MySqlFactory.CreateConnection(); connection.Open(); var command = MySqlFactory.CreateCommand(query, connection); var reader = command.ExecuteReader(); while (reader.Read()) { offeredServiceDTOs.Add(new OfferedServiceDTO() { Id = reader.GetInt32("id"), Name = reader.GetString("name"), CategoryId = reader.GetInt16("categoryId"), Price = reader.GetDecimal("price"), StatusId = reader.GetInt16("status"), DeliveryTimeDays = reader.GetInt32("deliveryTimeDays"), DeliveryTimeHours = reader.GetInt32("deliveryTimeHours"), DateOfPost = reader.GetDateTime("dateOfpost"), Description = reader.GetString("description"), ProviderId = reader.GetInt32("providerId"), IsDeleted = Convert.ToBoolean(reader.GetInt16("isDeleted")) }); } return(offeredServiceDTOs); }
private IEnumerable <IReviewDTO> GetReviewsWhere(string whereClause) { var reviewDTOs = new List <IReviewDTO>(); string commandText = "SELECT * FROM reviews WHERE " + whereClause; using var connection = MySqlFactory.CreateConnection(); connection.Open(); var command = MySqlFactory.CreateCommand(commandText, connection); var reader = command.ExecuteReader(); while (reader.Read()) { reviewDTOs.Add(new ReviewDTO() { Id = reader.GetInt32("id"), Title = reader.GetString("title"), Text = reader.GetString("text"), Rating = reader.GetInt16("rating"), WriterId = reader.GetInt32("writerId"), UsedServiceId = reader.GetInt32("usedServiceId"), DateOfPost = reader.GetDateTime("dateOfPost") }); } return(reviewDTOs); }
private IUserDTO GetUserBy(string whereClause) { var userDTO = new UserDTO(); var commandText = "SELECT * FROM users WHERE " + whereClause; using var connection = MySqlFactory.CreateConnection(); connection.Open(); var command = MySqlFactory.CreateCommand(commandText, connection); var dataReader = command.ExecuteReader(); while (dataReader.Read()) { userDTO.Id = Convert.ToInt32(dataReader["id"]); userDTO.FirstName = dataReader["firstName"].ToString(); userDTO.LastName = dataReader["lastName"].ToString(); userDTO.Email = dataReader["email"].ToString(); userDTO.Password = dataReader["password"].ToString(); userDTO.Description = dataReader["description"].ToString(); userDTO.RoleId = Convert.ToInt16(dataReader["roleId"]); userDTO.StatusId = Convert.ToInt16(dataReader["statusId"]); userDTO.IsDeletedId = Convert.ToInt16(dataReader["isDeleted"]); return(userDTO); } return(null); }
/// <summary> /// 异步查询一条数据 /// /// /// </summary> /// <typeparam name="T">实体</typeparam> /// <param name="connection">连接字符串</param> /// <param name="cmd">sql语句</param> /// <param name="param">参数</param> /// <param name="flag">true存储过程,false sql语句</param> /// <returns>t</returns> public async Task <T> FindOneAsync <T>(string cmd, DynamicParameters param = null, bool flag = false) where T : class, new() { using (var con = new MySqlFactory().DataBaseConnection(_connectionStr)) { IDataReader dataReader = await con.ExecuteReaderAsync(cmd, param, null, null, flag?CommandType.StoredProcedure : CommandType.Text); Type type = typeof(T); T t = new T(); foreach (var item in type.GetProperties()) { for (int i = 0; i < dataReader.FieldCount; i++) { //属性名与查询出来的列名比较 if (item.Name.ToLower() != dataReader.GetName(i).ToLower()) { continue; } var kvalue = dataReader[item.Name]; if (kvalue == DBNull.Value) { continue; } item.SetValue(t, kvalue, null); break; } } return(t); } }
public IEnumerable <IUserDTO> GetAllUsers() { var userDTOs = new List <IUserDTO>(); const string commandText = "SELECT * FROM users"; using var connection = MySqlFactory.CreateConnection(); connection.Open(); var command = MySqlFactory.CreateCommand(commandText, connection); var dataReader = command.ExecuteReader(); while (dataReader.Read()) { userDTOs.Add(new UserDTO() { Id = Convert.ToInt32(dataReader["id"]), FirstName = dataReader["firstName"].ToString(), LastName = dataReader["lastName"].ToString(), Email = dataReader["email"].ToString(), Password = dataReader["password"].ToString(), Description = dataReader["description"].ToString(), RoleId = Convert.ToInt16(dataReader["roleId"]), StatusId = Convert.ToInt16(dataReader["statusId"]), IsDeletedId = Convert.ToInt16(dataReader["isDeleted"]) }); } return(userDTOs); }
static void Main(string[] args) { try { //abstract classes DbFactory db; DbCommand cmd; DbConnection con; //Create a "fake" connection with a sql server db = new SqlFactory(); con = db.CreateConnection(); con.Open(); cmd = db.CreateCommand(); cmd.Execute(); //Now the connection is with a MySql Server db = new MySqlFactory(); con = db.CreateConnection(); con.Open(); cmd = db.CreateCommand(); cmd.Execute(); Console.ReadLine(); } finally { Console.ReadKey(); } }
public void SetStatus(int statusId, int userId) { var query = $"UPDATE users SET statusId = {statusId} WHERE users.id = {userId}"; using var connection = MySqlFactory.CreateConnection(); connection.Open(); var command = MySqlFactory.CreateCommand(query, connection); command.ExecuteNonQuery(); }
public void UpdateUser(IUserDTO userDTO) { var query = $"UPDATE users SET firstname = '{userDTO.FirstName}', lastname = '{userDTO.LastName}', email = '{userDTO.Email}', password = '******', description = '{userDTO.Description}' WHERE id = {userDTO.Id}; "; using var connection = MySqlFactory.CreateConnection(); connection.Open(); var command = MySqlFactory.CreateCommand(query, connection); command.ExecuteNonQuery(); }
/// <summary> /// 增、删、改同步操作 /// /// /// </summary> /// <typeparam name="T">实体</typeparam> /// <param name="connection">链接字符串</param> /// <param name="cmd">sql语句</param> /// <param name="param">参数</param> /// <param name="flag">true存储过程,false sql语句</param> /// <returns>int</returns> public int ExcuteNonQuery <T>(string cmd, DynamicParameters param = null, bool flag = false) where T : class, new() { int result = 0; using (var con = new MySqlFactory().DataBaseConnection(_connectionStr)) { result = con.Execute(cmd, param, null, null, flag ? CommandType.StoredProcedure : CommandType.Text); } return(result); }
public void SetStatus(int offeredServiceId, int status) { var query = $"UPDATE offeredServices SET offeredservices.status = {status} WHERE id = {offeredServiceId};"; using var connection = MySqlFactory.CreateConnection(); connection.Open(); var command = MySqlFactory.CreateCommand(query, connection); command.ExecuteNonQuery(); }
/// <summary> /// 异步查询操作 /// /// /// </summary> /// <typeparam name="T">实体</typeparam> /// <param name="connection">连接字符串</param> /// <param name="cmd">sql语句</param> /// <param name="param">参数</param> /// <param name="flag">true存储过程,false sql语句</param> /// <returns>object</returns> public async Task <object> ExecuteScalarAsync <T>(string cmd, DynamicParameters param = null, bool flag = false) where T : class, new() { object result = null; using (var con = new MySqlFactory().DataBaseConnection(_connectionStr)) { result = con.ExecuteScalarAsync(cmd, param, null, null, flag ? CommandType.StoredProcedure : CommandType.Text); } return(result); }
public DataTable GetDataTable(string cmd, DynamicParameters param = null, bool flag = false) { var dt = new DataTable(); using (var con = new MySqlFactory().DataBaseConnection(_connectionStr)) { IDataReader dataReader = con.ExecuteReader(cmd.ToLower(), param, null, null, flag ? CommandType.StoredProcedure : CommandType.Text); dt.Load(dataReader); } return(dt); }
public void AddUser(IUserDTO user) { string query = "INSERT INTO users (firstName, lastName, email, password, roleId) VALUES('" + user.FirstName + "', '" + user.LastName + "', '" + user.Email + "', '" + user.Password + "', '" + user.RoleId + "'); "; using var connection = MySqlFactory.CreateConnection(); connection.Open(); var command = MySqlFactory.CreateCommand(query, connection); command.ExecuteNonQuery(); }
public int ExcuteNonQuery(string cmd, DynamicParameters param = null, bool flag = false) { WriterLog(cmd); int result = 0; using (var con = new MySqlFactory().DataBaseConnection(_connectionStr)) { result = con.Execute(cmd.ToLower(), param, null, null, flag ? CommandType.StoredProcedure : CommandType.Text); } return(result); }
public void AddImage(IImageDTO imageDTO) { using var connection = MySqlFactory.CreateConnection(); connection.Open(); var cmd = MySqlFactory.CreateCommand("AddImage", connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@offeredServiceId", imageDTO.OfferedServiceId); cmd.Parameters.AddWithValue("@image", imageDTO.Image); cmd.Parameters.AddWithValue("@isHeadImage", imageDTO.IsHeadImage); cmd.ExecuteNonQuery(); connection.Close(); }
public void UpdateRating(int offeredServiceId, decimal rating) { using var connection = MySqlFactory.CreateConnection(); connection.Open(); var cmd = MySqlFactory.CreateCommand("UpdateRating", connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@id", offeredServiceId); cmd.Parameters.AddWithValue("@rating", rating); cmd.ExecuteNonQuery(); connection.Close(); }
public void AddUsedService(IUsedServiceDTO usedServiceDTO) { using var connection = MySqlFactory.CreateConnection(); connection.Open(); var command = MySqlFactory.CreateCommand("AddUsedService", connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@consumerId", usedServiceDTO.ConsumerId); command.Parameters.AddWithValue("@offeredServiceId", usedServiceDTO.OfferedServiceId); command.Parameters.AddWithValue("@dateOfPurchase", usedServiceDTO.DateOfPurchase); command.ExecuteNonQuery(); connection.Close(); }
public int ExcuteNonQueryTransaction(string cmd, DynamicParameters param, bool flag = false) { int result = 0; using (var con = new MySqlFactory().DataBaseConnection(_connectionStr)) { IDbTransaction transaction = con.BeginTransaction(); result = con.Execute(cmd, param, null, null, flag ? CommandType.StoredProcedure : CommandType.Text); if (result != 0) { transaction.Commit(); } transaction.Rollback(); } return(result); }
public int ExcuteNonQueryTransaction(List <string> cmd, List <DynamicParameters> param, List <CommandType> commandType) { int result = 0; using (var con = new MySqlFactory().DataBaseConnection(_connectionStr)) { IDbTransaction transaction = con.BeginTransaction(); result += cmd.Select((t, i) => con.Execute(t, param[i], transaction, null, commandType[i])).Sum(); if (result == cmd.Count) { transaction.Commit(); } transaction.Rollback(); } return(result); }
private ConexaoFactory setConexao(string banco) { ConexaoFactory conexaoFactory = null; switch (banco) { case "Oracle": conexaoFactory = new OracleFactory(); break; case "MySQL": conexaoFactory = new MySqlFactory(); break; } return(conexaoFactory); }
public bool CheckEmailExistance(string email) { string query = "SELECT users.email FROM users WHERE users.email = '" + email + "'"; using var connection = MySqlFactory.CreateConnection(); connection.Open(); var command = MySqlFactory.CreateCommand(query, connection); command.ExecuteNonQuery(); var dataTable = new DataTable(); var adapter = MySqlFactory.CreateDataAdapter(command); adapter.Fill(dataTable); return(dataTable.Rows.Count == 1); }
public void AddReview(IReviewDTO reviewDTO) { using var connection = MySqlFactory.CreateConnection(); connection.Open(); var command = MySqlFactory.CreateCommand("AddReview", connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@title", reviewDTO.Title); command.Parameters.AddWithValue("@text", reviewDTO.Text); command.Parameters.AddWithValue("@rating", reviewDTO.Rating); command.Parameters.AddWithValue("@dateOfPost", reviewDTO.DateOfPost); command.Parameters.AddWithValue("@writerId", reviewDTO.WriterId); command.Parameters.AddWithValue("@usedServiceId", reviewDTO.UsedServiceId); command.ExecuteNonQuery(); connection.Close(); }
public void Update(IOfferedServiceDTO offeredServiceDTO) { using var connection = MySqlFactory.CreateConnection(); connection.Open(); var cmd = MySqlFactory.CreateCommand("UpdateOfferedService", connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@_id", offeredServiceDTO.Id); cmd.Parameters.AddWithValue("@_name", offeredServiceDTO.Name); cmd.Parameters.AddWithValue("@_categoryId", offeredServiceDTO.CategoryId); cmd.Parameters.AddWithValue("@_price", offeredServiceDTO.Price); cmd.Parameters.AddWithValue("@_description", offeredServiceDTO.Description); cmd.Parameters.AddWithValue("@_deliveryTimeDays", offeredServiceDTO.DeliveryTimeDays); cmd.Parameters.AddWithValue("@_deliveryTimeHours", offeredServiceDTO.DeliveryTimeHours); cmd.ExecuteNonQuery(); connection.Close(); }
public IUsedServiceDTO GetUsedServiceById(int usedServiceId) { var usedServiceDTO = new UsedServiceDTO(); string query = "SELECT * FROM usedservices WHERE usedservices.id = " + usedServiceId + ""; using var connection = MySqlFactory.CreateConnection(); connection.Open(); var command = MySqlFactory.CreateCommand(query, connection); var reader = command.ExecuteReader(); while (reader.Read()) { usedServiceDTO.Id = reader.GetInt32("id"); usedServiceDTO.ConsumerId = reader.GetInt32("consumerId"); usedServiceDTO.OfferedServiceId = reader.GetInt32("offeredServiceId"); usedServiceDTO.DateOfPurchase = reader.GetDateTime("dateOfPurchase"); } return(usedServiceDTO); }
public IEnumerable <ICategoryDTO> GetAllCategories() { var categoryDTOs = new List <ICategoryDTO>(); string query = "SELECT * FROM categories"; using var connection = MySqlFactory.CreateConnection(); connection.Open(); var command = MySqlFactory.CreateCommand(query, connection); var reader = command.ExecuteReader(); while (reader.Read()) { categoryDTOs.Add(new CategoryDTO() { Id = reader.GetInt16("id"), Name = reader.GetString("name") }); } return(categoryDTOs); }
public void Add(IOfferedServiceDTO offeredServiceDTO) { using var connection = MySqlFactory.CreateConnection(); connection.Open(); var cmd = MySqlFactory.CreateCommand("AddOfferedService", connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@name", offeredServiceDTO.Name); cmd.Parameters.AddWithValue("@categoryId", offeredServiceDTO.CategoryId); cmd.Parameters.AddWithValue("@price", offeredServiceDTO.Price); cmd.Parameters.AddWithValue("@providerId", offeredServiceDTO.ProviderId); cmd.Parameters.AddWithValue("@description", offeredServiceDTO.Description); cmd.Parameters.AddWithValue("@status", offeredServiceDTO.StatusId); cmd.Parameters.AddWithValue("@deliveryTimeDays", offeredServiceDTO.DeliveryTimeDays); cmd.Parameters.AddWithValue("@deliveryTimeHours", offeredServiceDTO.DeliveryTimeHours); cmd.Parameters.AddWithValue("@dateOfPost", offeredServiceDTO.DateOfPost); cmd.Parameters.AddWithValue("@isDeleted", Convert.ToInt16(offeredServiceDTO.IsDeleted)); cmd.ExecuteNonQuery(); connection.Close(); }
public ActionResult AbstractFactory(string factoryType) { IDAOFactory factory = null; if (factoryType == "mssql") { factory = new MsSqlFactory(); } else { factory = new MySqlFactory(); } factoryType = "DesignPatterns.Pattern.GangOfFour.Creational.MsSqlFactory"; ObjectHandle oh = Activator.CreateInstance(Assembly.GetExecutingAssembly().FullName, factoryType); factory = (IDAOFactory)oh.Unwrap(); DAOHelper o = new DAOHelper(factory); o.Save(); return(View()); }
public IEnumerable <IUsedServiceDTO> GetAllUsedServices() { var usedServiceDTOs = new List <IUsedServiceDTO>(); const string commandText = "SELECT * FROM usedServices"; using var connection = MySqlFactory.CreateConnection(); connection.Open(); var command = MySqlFactory.CreateCommand(commandText, connection); var reader = command.ExecuteReader(); while (reader.Read()) { usedServiceDTOs.Add(new UsedServiceDTO() { Id = reader.GetInt32("id"), ConsumerId = reader.GetInt32("consumerId"), OfferedServiceId = reader.GetInt32("offeredServiceId"), DateOfPurchase = reader.GetDateTime("dateOfPurchase") }); } return(usedServiceDTOs); }
private IEnumerable <IUsedServiceDTO> GetUsedServicesWhere(string whereClause) { var usedServiceDTOs = new List <IUsedServiceDTO>(); string query = "SELECT * FROM usedServices WHERE " + whereClause; using var connection = MySqlFactory.CreateConnection(); connection.Open(); var command = MySqlFactory.CreateCommand(query, connection); var reader = command.ExecuteReader(); while (reader.Read()) { usedServiceDTOs.Add(new UsedServiceDTO() { Id = reader.GetInt32("id"), ConsumerId = reader.GetInt32("consumerId"), OfferedServiceId = reader.GetInt32("offeredServiceId"), DateOfPurchase = reader.GetDateTime("dateOfPurchase") }); } return(usedServiceDTOs); }