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