public async Task <int> Add(Producto entity)
{
using (var connection = ConnectionFactory.Connection(_configuracion))
{
connection.Open();
var values = new
{
nombre = entity.nombre,
referencia = entity.referencia,
precioVenta = entity.precioVenta,
codigoBarra = entity.codigoBarra,
categoria = entity.categoria,
descripcion = entity.descripcion,
costoUnidad = entity.costoUnidad,
cantidadInicial = entity.cantidadInicial,
cantidadMinima = entity.cantidadMinima,
marca = entity.marca,
Estado = entity.Estado,
color = entity.color,
genero = entity.genero,
costoEnvio = entity.costoEnvio,
UserName = "******",
};
var result = await connection.QuerySingleAsync <int>(StoreProcedure_Add, values, commandType : CommandType.StoredProcedure);
return(result);
}
}
public void InsertAndRollback()
{
string wrongSql = "INSERT INTO DocumentCategorys (Name, Description) Values (@Name, @Description);";
using (var conn = ConnectionFactory.Connection())
{
conn.Open();
using (var transaction = conn.BeginTransaction())
{
try
{
conn.Execute(wrongSql
, new DocumentCategory()
{
Name = "", Description = "xxxxxxx", Id = 1
}
, commandType: CommandType.Text
, transaction: transaction);
transaction.Commit();
}
catch (System.Exception)
{
transaction.Rollback();
}
}
}
}
public IEnumerable <DocumentCategory> GetAllWithPaged(int page = 1, string condition = "", string orderby = "")
{
using (var conn = ConnectionFactory.Connection())
{
return(conn.GetListPaged <DocumentCategory>(page, DataAccessAppSetting.RecordPerPage, condition, orderby));
}
}
public IEnumerable <DocumentCategory> GetAllWithSqlScriptByLoadDetail()
{
using (var conn = ConnectionFactory.Connection())
{
var documentCategoryDictionary = new Dictionary <int, DocumentCategory>();
return(conn.Query <DocumentCategory, Document, DocumentCategory>(
SqlText.DocumentCategory_Select_WithDetail,
(documentCategory, document) =>
{
DocumentCategory documentCategoryEntry;
if (!documentCategoryDictionary.TryGetValue(documentCategory.Id, out documentCategoryEntry))
{
documentCategoryEntry = documentCategory;
documentCategoryEntry.Documents = new List <Document>();
documentCategoryDictionary.Add(documentCategoryEntry.Id, documentCategoryEntry);
}
if (document != null)
{
documentCategoryEntry.Documents.Add(document);
}
return documentCategoryEntry;
},
splitOn: "Id")
.Distinct()
.ToList());
}
}
public IEnumerable <DocumentCategory> GetAllWithSqlScript()
{
using (var conn = ConnectionFactory.Connection())
{
return(conn.Query <DocumentCategory>(SqlText.DocumentCategory_Select).ToList());
}
}
public bool Login(string user, string password)
{
bool result = false;
try
{
string sql = "select user_name, user_password from tbl_user where user_name= '" + user + "'and user_password = '******';";
con = ConnectionFactory.Connection();
MySqlCommand cmd = new MySqlCommand(sql, con);
con.Open();
MySqlDataReader dtreader = cmd.ExecuteReader();
if (dtreader.Read())//If there's any data.
{
result = true;
}
}
catch (MySqlException e)
{
MessageBox.Show("Algo errado aconteceu durante o login. Por favor, tente novamente mais tarde.", "Erro!", MessageBoxButton.OK, MessageBoxImage.Error, MessageBoxResult.OK);
throw new Exception(e.Message);
}
finally
{
con.Close();
}
return(result);
}
public IEnumerable <User> GetList()
{
using (var conn = ConnectionFactory.Connection())
{
//string sql = @"select * from user ";
return(conn.Query <User>("select username,password from user"));
}
}
public int Insert(User entity)
{
using (var conn = ConnectionFactory.Connection())
{
string sql = " insert into [dbo].[User](UserName,Password,Tel,QQ) values(@UserName,@Password,@Tel,@QQ)";
return(conn.Execute(sql, entity));
}
}
public DocumentCategory GetWithSqlScriptById(int id)
{
using (var conn = ConnectionFactory.Connection())
{
return(conn.QuerySingle <DocumentCategory>(SqlText.DocumentCategory_Select_ByID,
new { DocumentCategoryID = id }));
}
}
public int CheckMobileCode(string mobile, string code)
{
using (var conn = ConnectionFactory.Connection())
{
string sql = "select 1 from VerificationCode where mobile=@Mobile and code=@Code and DATEDIFF(MINUTE,intime,getdate())<300";
return(conn.Query <int>(sql, new { Mobile = mobile, Code = code }).FirstOrDefault());
}
}
public IList <GameAreaService> GetList(int gameId)
{
string sql = "select Id, AreaName from GameAreaService where Games_GameId=@GameId";
using (var conn = ConnectionFactory.Connection())
{
return(conn.Query <GameAreaService>(sql, new { GameId = gameId }).ToList());
}
}
public int UserInfoUpdate(User user)
{
using (var conn = ConnectionFactory.Connection())
{
string sql = " update [dbo].[User] set where UserId=@UserId";
int i = conn.Execute(sql, new { UserId = user.UserId });
return(i);
}
}
public R_Game_User_Play GetModel(int id = 1, string name = null)
{
string sql = "select top 1 State,type,Games_GameId,InTime from R_Game_User_Play where User_UserId=@UserId order by intime asc ";
using (var conn = ConnectionFactory.Connection())
{
return(conn.Query <R_Game_User_Play>(sql, new { UserId = id }).SingleOrDefault());
}
}
public int Insert(Log entity)
{
string sql = "insert into log(Title,Msg,IP)values(@Title,@Msg,@IP)";
using (var conn = ConnectionFactory.Connection())
{
return(conn.Execute(sql, entity));
}
}
/// <summary>
/// 大神申请>大神上分
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public int Insert(R_Game_User_Play entity)
{
string sql = "insert into R_Game_User_Play(GameRating_CutImg,Type,User_UserId,Games_GameId,GameRatingId,GameAreaId) values(@GameRating_CutImg,@Type,@User_UserId,@Games_GameId,@GameRatingId,@GameAreaId) ";
using (var conn = ConnectionFactory.Connection())
{
return(conn.Execute(sql, entity));
}
}
public bool Update(User entity)
{
using (var conn = ConnectionFactory.Connection())
{
string sql = "update [dbo].[User] set Last_Login_IP=@Last_Login_IP,Last_Login_Time=@Last_Login_Time where UserId=@Id";
int i = conn.Execute(sql, new { Id = entity.UserId, Last_Login_IP = entity.Last_Login_IP, Last_Login_Time = entity.Last_Login_Time });
return(i > 0 ? true : false);
}
}
public User GetModel(int id = 1, string name = null)
{
using (var conn = ConnectionFactory.Connection())
{
string sql = @"select UserId,UserName,Password,Tel ,State,CurrentCity,Last_Login_Time,Last_Login_IP ,Intro,
OnLine_State,HeadImg
from [dbo].[User] where UserName=@name or Tel=@name ";
return(conn.Query <User>(sql, new { name = name }).SingleOrDefault());
}
}
public void DeleteRequest(decimal id)
{
using (var conn = ConnectionFactory.Connection())
{
var parameters = new DynamicParameters();
parameters.Add("requestid", id);
conn.Execute(SqlText.Request_Delete, parameters);
}
}
public async Task <IEnumerable <Producto> > GetAll()
{
using (var connection = ConnectionFactory.Connection(_configuracion))
{
connection.Open();
var result = await connection.QueryAsync <Producto>(StoreProcedure_Get, commandType : CommandType.StoredProcedure);
return(result);
}
}
public Request UpdateRequest(decimal id)
{
using (var conn = ConnectionFactory.Connection())
{
var parameters = new DynamicParameters();
parameters.Add("studentid", "6031301045");
parameters.Add("requestid", id);
conn.Execute(SqlText.Request_Update, parameters);
return(GetRequestById(id));
}
}
public bool CadastroReceita(ModelReceita receita)
{
bool i = false;
try
{
string sql = @"insert into tbl_receita(receita_ingrediente1, receita_ingrediente2, receita_ingrediente3, receita_ingrediente4, receita_ingrediente5, receita_ingrediente6, receita_ingrediente7, receita_ingrediente8, receita_ingrediente9, receita_ingrediente10, receita_titulo, receita_preparo, receita_tipo, receita_rendimento, receita_tempoPreparo, receita_custoTotal)
values('" + receita.Ingrediente1 + "', '" + receita.Ingrediente2 + "', '" + receita.Ingrediente3 + "', '" + receita.Ingrediente4 + "', '" + receita.Ingrediente5
+ "', '" + receita.Ingrediente6 + "', '" + receita.Ingrediente7 + "', '" + receita.Ingrediente8 + "', '" + receita.Ingrediente9 + "', '" + receita.Ingrediente10
+ "', '" + receita.Titulo + "', '" + receita.Preparo + "', '" + receita.Tipo + "', '" + receita.Rendimento + "', '" + receita.TempoPreparo + "', '" + receita.CustoTotal + "');";
con = ConnectionFactory.Connection();
con.Open();
MySqlCommand cmd = new MySqlCommand(sql, con);
cmd.ExecuteNonQuery();
con.Close();
sql = "select receita_titulo from tbl_receita where receita_titulo = '" + receita.Titulo + "';";
con.Open();
cmd = new MySqlCommand(sql, con);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
i = true;
}
else
{
i = false;
}
}
catch (MySqlException e)
{
MessageBox.Show("Algo errado aconteceu durante o cadastro. Por favor, tente novamente mais tarde.", "Erro!", MessageBoxButton.OK, MessageBoxImage.Error, MessageBoxResult.OK);
throw new Exception(e.Message);
}
finally
{
con.Close();
}
return(i);
}
public string OutCode(string mobile, string vcode, string ip)
{
DynamicParameters dp = new DynamicParameters();
dp.Add("@mobile", mobile);
dp.Add("@code", vcode);
dp.Add("@ip", ip);
dp.Add("@outCode", "", DbType.String, ParameterDirection.Output);
using (var conn = ConnectionFactory.Connection())
{
conn.Execute("SendVCode", dp, null, null, CommandType.StoredProcedure);
string outCode = dp.Get <string>("@outCode");
return(outCode);
}
}
public IEnumerable <Request> GetAllRequest()
{
using (var conn = ConnectionFactory.Connection())
{
return(conn.Query <Request, RequestType, Request>(SqlText.Request_Select,
(request, reuestType) =>
{
request.RequestType = reuestType;
return request;
},
splitOn: "RequestTypeId"
).ToList());
}
return(new List <Request>());
}
public Province GetProvinceById(string id)
{
using (var conn = ConnectionFactory.Connection())
{
var parameters = new OracleDynamicParameters();
parameters.Add("vprovinceid", 170, dbType: OracleDbType.Int32);
parameters.Add("vlanguage", "T", dbType: OracleDbType.Varchar2);
parameters.Add("pdata", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
var affectedRows = conn.Query <Province>(
sql: "SP_NAME",
param: parameters,
commandType: CommandType.StoredProcedure).FirstOrDefault();
return(affectedRows);
}
}
public Request GetRequestById(decimal id)
{
using (var conn = ConnectionFactory.Connection())
{
return(conn.Query <Request, RequestType, Request>(SqlText.Request_Select_ByID.Replace("@REQUESTID", id.ToString()),
(request, reuestType) =>
{
request.RequestType = reuestType;
return request;
},
splitOn: "RequestTypeId"
).FirstOrDefault());
}
return(new Request());
}
public int UserMoney(int userId, bool state)
{
string sql = @"SELECT COALESCE(SUM(rmb),0) FROM Money where Money.User_UserId=@UserId and RMBState=@State";
using (var conn = ConnectionFactory.Connection())
{
if (state == false)
{
return(conn.Query <int>(sql, new { UserId = userId, State = 0 }).SingleOrDefault());
}
else
{
return(conn.Query <int>(sql, new { UserId = userId, State = 1 }).SingleOrDefault());
}
}
}
public IList <Focus> GetFocusList(int userId, bool sign)
{
string sql = "select User_UserId from Focus where User_UserId=@UserId";//关注的用户
using (var conn = ConnectionFactory.Connection())
{
if (sign)
{
return(conn.Query <Focus>(sql, new { UserId = userId }).ToList());
}
else
{
sql = "select Self_UserId from Focus where Self_UserId=@Self_UserId";//粉丝数
return(conn.Query <Focus>(sql, new { Self_UserId = userId }).ToList());
}
}
}
public ModelReceita Selection(string titulo)
{
ModelReceita receita = new ModelReceita();
string sql = @"select receita_ingrediente1, receita_ingrediente2, receita_ingrediente3, receita_ingrediente4, receita_ingrediente5, receita_ingrediente6,
receita_ingrediente7, receita_ingrediente8, receita_ingrediente9, receita_ingrediente10, receita_titulo, receita_preparo, receita_tipo, receita_rendimento,
receita_tempoPreparo, receita_custoTotal from tbl_receita where receita_titulo = '" + titulo + "';";
con = ConnectionFactory.Connection();
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(sql, con);
MySqlDataReader dt = cmd.ExecuteReader();
if (dt.Read())
{
receita.Ingrediente1 = dt.GetString("receita_ingrediente1");
receita.Ingrediente2 = dt.GetString("receita_ingrediente2");
receita.Ingrediente3 = dt.GetString("receita_ingrediente3");
receita.Ingrediente4 = dt.GetString("receita_ingrediente4");
receita.Ingrediente5 = dt.GetString("receita_ingrediente5");
receita.Ingrediente6 = dt.GetString("receita_ingrediente6");
receita.Ingrediente7 = dt.GetString("receita_ingrediente7");
receita.Ingrediente8 = dt.GetString("receita_ingrediente8");
receita.Ingrediente9 = dt.GetString("receita_ingrediente9");
receita.Ingrediente10 = dt.GetString("receita_ingrediente10");
receita.Titulo = dt.GetString("receita_titulo");
receita.Preparo = dt.GetString("receita_preparo");
receita.Tipo = dt.GetString("receita_tipo");
receita.Rendimento = dt.GetString("receita_rendimento");
receita.TempoPreparo = dt.GetString("receita_tempoPreparo");
receita.CustoTotal = dt.GetString("receita_custoTotal");
}
con.Close();
}
catch (MySqlException e)
{
MessageBox.Show("Algo errado aconteceu durante a seleção. Por favor, tente novamente mais tarde.", "Erro!", MessageBoxButton.OK, MessageBoxImage.Error, MessageBoxResult.OK);
throw new Exception(e.Message);
}
return(receita);
}
public Request AddRequest()
{
using (var conn = ConnectionFactory.Connection())
{
var parameters = new DynamicParameters();
parameters.Add("requesttypeid", 101);
parameters.Add("requeststatus", "D");
parameters.Add("acadyear", 2560);
parameters.Add("semester", 1);
parameters.Add("studentid", "4631301045");
parameters.Add("requestid", dbType: DbType.Decimal, direction: ParameterDirection.Output);
conn.Execute(SqlText.Request_Insert, parameters);
var Id = parameters.Get <decimal>("requestid");
return(GetRequestById(Id));
}
}
public int InsertFunPlay(R_Game_User_Play entity, User user)
{
using (var conn = ConnectionFactory.Connection())
{
const string sql = " insert into R_Game_User_Play(GameRating_CutImg,Type,User_UserId,Games_GameId,GameRatingId,GameAreaId,OnlinePrice,OfflinePrice,TagName,ServiceNote,GameScore_CutImg) values(@GameRating_CutImg, @Type, @User_UserId, @Games_GameId, @GameRatingId, @GameAreaId, @OnlinePrice, @OfflinePrice, @TagName, @ServiceNote, @GameScore_CutImg) ";
const string sql2 = " update [dbo].[User] set HomePage_Img=@HomePage_Img,Sex=@Sex,CurrentCity=@CurrentCity,PersonalPhoto=@PersonalPhoto,PersonalAudio=@PersonalAudio where UserId=@UserId";
IDbTransaction tran = conn.BeginTransaction();
try
{
int i = conn.Execute(sql, entity, tran);
i += conn.Execute(sql2, new { UserId = user.UserId, HomePage_Img = user.HomePage_Img, Sex = user.Sex, CurrentCity = user.CurrentCity, PersonalPhoto = user.PersonalPhoto, PersonalAudio = user.PersonalAudio }, tran);
tran.Commit();
return(i);
}
catch (Exception)
{
tran.Rollback();
throw;
}
}
}
