public List <DefesaSiegeModels> ListarDefesas(int idGuilda) { SqlConnection conn = new SqlConnection(); SqlCommand sqlCom = new SqlCommand(); conn.ConnectionString = ConfigurationManager.ConnectionStrings["DB_SW"].ToString(); StringBuilder select = new StringBuilder(); select.AppendLine("select "); select.AppendLine("a.IdPlayer,b.nome, "); select.AppendLine("(select count(Vitoria) from dbo.SiegePlayerDefesa b where b.IdPlayer = a.IdPlayer and b.Vitoria = 1 and b.IdSiege in (select guild.IdSiege from dbo.SiegeGuilda guild where guild.IdGuilda = @idGuilda)) Vitoria, "); select.AppendLine("(select count(Vitoria) from dbo.SiegePlayerDefesa b where b.IdPlayer = a.IdPlayer and b.Vitoria = 2 and b.IdSiege in (select guild.IdSiege from dbo.SiegeGuilda guild where guild.IdGuilda = @idGuilda)) Derrota "); select.AppendLine("from dbo.SiegePlayers a "); select.AppendLine("inner join dbo.Player b on b.ID = a.IdPlayer "); select.AppendLine("where 1 = 1 "); select.AppendLine("and a.idSiege in (select guild.IdSiege from dbo.SiegeGuilda guild where guild.IdGuilda = @idGuilda) "); select.AppendLine("and b.Status = 'S'"); select.AppendLine("group by a.IdPlayer,b.nome "); select.AppendLine("order by 3 desc "); sqlCom.CommandText = select.ToString(); sqlCom.CommandType = System.Data.CommandType.Text; sqlCom.Parameters.Add(new SqlParameter("@idGuilda", System.Data.SqlDbType.Int)); sqlCom.Parameters["@idGuilda"].Value = idGuilda; try { DefesaSiegeModels objDefesa; List <Models.DefesaSiegeModels> objRetorno = new List <DefesaSiegeModels>(); conn.Open(); sqlCom.Connection = conn; SqlDataReader reader = sqlCom.ExecuteReader(); while (reader.Read()) { objDefesa = new DefesaSiegeModels(); objDefesa.Derrota = int.Parse(reader["Derrota"].ToString()); objDefesa.Player = new PlayerModels() { Id = int.Parse(reader["IdPlayer"].ToString()), Nome = reader["Nome"].ToString() }; objDefesa.Vitoria = int.Parse(reader["Vitoria"].ToString()); objRetorno.Add(objDefesa); } conn.Close(); conn.Dispose(); return(objRetorno); } catch (Exception ex) { throw ex; } }
public List <DefesaSiegeModels> ListarDefesasConsolidado(int idGuilda, long idSiege) { SqlConnection conn = new SqlConnection(); SqlCommand sqlCom = new SqlCommand(); conn.ConnectionString = ConfigurationManager.ConnectionStrings["DB_SW"].ToString(); StringBuilder select = new StringBuilder(); select.AppendLine("select "); select.AppendLine("a.IdSiege,a.IdPlayer, d.Nome "); select.AppendLine(", (select count(Vitoria) from dbo.SiegePlayerDefesa e where e.idSiege = a.idSiege and e.IdPlayer = a.IdPlayer and e.Vitoria = 1 ) Vitoria "); select.AppendLine(", (select count(Vitoria) from dbo.SiegePlayerDefesa e where e.idSiege = a.idSiege and e.IdPlayer = a.IdPlayer and e.Vitoria = 2 ) Derrota "); select.AppendLine("from dbo.SiegePlayerDefesa a "); select.AppendLine("inner join dbo.SiegePlayers c on c.IdPlayer = a.IdPlayer and c.IdSiege = a.IdSiege "); select.AppendLine("inner join dbo.Player d on d.ID = c.IdPlayer "); select.AppendLine("where "); select.AppendLine("a.IdSiege = @idSiege "); select.AppendLine("and a.idSiege in (select guild.IdSiege from dbo.SiegeGuilda guild where guild.IdGuilda = @idGuilda) "); select.AppendLine("group by a.IdSiege,a.IdPlayer, d.Nome "); select.AppendLine("order by 3 "); sqlCom.CommandText = select.ToString(); sqlCom.CommandType = System.Data.CommandType.Text; sqlCom.Parameters.Add(new SqlParameter("@idSiege", System.Data.SqlDbType.BigInt)); sqlCom.Parameters["@idSiege"].Value = idSiege; sqlCom.Parameters.Add(new SqlParameter("@idGuilda", System.Data.SqlDbType.Int)); sqlCom.Parameters["@idGuilda"].Value = idGuilda; SiegeModels objSiege = ObterSiege(idSiege); try { DefesaSiegeModels objDefesa; List <Models.DefesaSiegeModels> objRetorno = new List <DefesaSiegeModels>(); conn.Open(); sqlCom.Connection = conn; SqlDataReader reader = sqlCom.ExecuteReader(); while (reader.Read()) { objDefesa = new DefesaSiegeModels(); objDefesa.Derrota = int.Parse(reader["Derrota"].ToString()); objDefesa.Player = new PlayerModels() { Id = int.Parse(reader["IdPlayer"].ToString()), Nome = reader["Nome"].ToString() }; objDefesa.Siege = objSiege; objDefesa.Vitoria = int.Parse(reader["Vitoria"].ToString()); objRetorno.Add(objDefesa); } conn.Close(); conn.Dispose(); return(objRetorno); } catch (Exception ex) { throw ex; } }