using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
using ParticipantsDsTableAdapters;


/// <summary>
/// Summary description for ParticipantService
/// </summary>
[WebService(Namespace = "http://hasan.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class ParticipantService : System.Web.Services.WebService 
{

    public ParticipantService () 
    {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }

    #region Methodes Classiques
    [WebMethod]
    public void Ajouter(string nom, string prenom)
    {
        SqlConnection myConnection = new SqlConnection();
        myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["ParticipantsDB"].ToString();

        SqlCommand myCommand = new SqlCommand("INSERT INTO Participant (Nom, Prenom) VALUES (@nom, @prenom)", myConnection);
        myCommand.Parameters.Add("@nom", SqlDbType.NVarChar, 50).Value = nom;
        myCommand.Parameters.Add("@prenom", SqlDbType.NVarChar, 50).Value = prenom;

        myConnection.Open();
        myCommand.ExecuteNonQuery();
        myConnection.Close();
    }

    [WebMethod]
    public void AjouterParticipant(ParticipantBusiness p)
    {
        this.Ajouter(p.Nom,p.Prenom);
    }
    
    [WebMethod]
    public void Modifier(int id, string nom, string prenom)
    {
        SqlConnection myConnection = new SqlConnection();
        myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["ParticipantsDB"].ToString();

        SqlCommand myCommand = new SqlCommand("UPDATE Participant SET Nom = @nom, Prenom = @prenom WHERE id=@id", myConnection);
        myCommand.Parameters.Add("@id", SqlDbType.Int).Value = id;
        myCommand.Parameters.Add("@nom", SqlDbType.NVarChar, 50).Value = nom;
        myCommand.Parameters.Add("@prenom", SqlDbType.NVarChar, 50).Value = prenom;

        myConnection.Open();
        myCommand.ExecuteNonQuery();
        myConnection.Close();
        
    }

    [WebMethod]
    public void Supprimer(int id)
    {
        SqlConnection myConnection = new SqlConnection();
        myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["ParticipantsDB"].ToString();

        SqlCommand myCommand = new SqlCommand("DELETE FROM Participant WHERE id=@id", myConnection);
        myCommand.Parameters.Add("@id", SqlDbType.Int).Value = id;

        myConnection.Open();
        myCommand.ExecuteNonQuery();
        myConnection.Close();

    }

    [WebMethod]
    public List<ParticipantBusiness> Lister()
    {
        //Couche Business Complète
        List<ParticipantBusiness> myList = new List<ParticipantBusiness>();

        SqlConnection myConnection = new SqlConnection();
        myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["ParticipantsDB"].ToString();

        SqlCommand myCommand = new SqlCommand("SELECT Id, Nom, Prenom FROM Participant", myConnection);

        myConnection.Open();
        SqlDataReader myReader = myCommand.ExecuteReader();


        while (myReader.Read())
        {
            myList.Add(new ParticipantBusiness(myReader.GetInt32(0), myReader.GetString(1), myReader.GetString(2)));
        }
        myConnection.Close();
        return myList;        
    }

    [WebMethod]
    public DataSet Tabler()
    {
        // DataSet Manuel
        DataSet ds = new DataSet("MonSuperDataSet");
        
        DataTable res = new DataTable("Participants");
        DataColumn dc = new DataColumn("Id", typeof (System.Int32));
        
        res.Columns.Add(dc);
        res.Columns.Add(new DataColumn("Nom", typeof(System.String)));
        res.Columns.Add(new DataColumn("Prenom", typeof(System.String)));

        ds.Tables.Add(res);

        // Il faut remplir la table
        SqlConnection myConnection = new SqlConnection();
        myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["ParticipantsDB"].ToString();

        SqlCommand myCommand = new SqlCommand("SELECT Id, Nom, Prenom FROM Participant", myConnection);

        myConnection.Open();
        SqlDataReader myReader = myCommand.ExecuteReader();


        while (myReader.Read())
        {
            DataRow myRow = res.NewRow();

            myRow["Id"] = myReader.GetInt32(0);
            myRow["Nom"] = myReader.GetString(1);
            myRow["Prenom"] = myReader.GetString(2);

            res.Rows.Add(myRow);
        }
        myConnection.Close();
        return ds;
    }

    [WebMethod]
    public DataSet GetParticipants()
    {
        // DataSet Typé
        ParticipantsDs myDataSet = new ParticipantsDs();
        ParticipantTableAdapter myAdapter = new ParticipantTableAdapter();
        myAdapter.Fill(myDataSet.Participant);

        return myDataSet;
    }

    #endregion



}