Overblog Suivre ce blog
Editer l'article Administration Créer mon blog

Présentation

  • : Romagny13 - Du .NET,du pur .NET
  • Romagny13 - Du .NET,du pur .NET
  • : Cycle de progression Faire (quelque chose qui marche) -> comprendre ce que l’on fait/comment cela marche -> pousser plus loin les notions
  • Contact

Recherche

Articles Récents

3 février 2014 1 03 /02 /février /2014 18:44

Dans le fichier de configuration

  <connectionStrings>

    <add name="dbConnectionString" providerName="System.Data.OleDb" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\romagny\Documents\metier\sources\DbDemo.mdb;" />

  </connectionStrings>

 

Pour bien faire il faudrait utiliser une BindingList(avec un projet windows forms) et implémenter INotifyPropertyChanged

BindingList<Client> bindingClients = new BindingList<Client>(clients);

 

GetAll

MainForm

        private ClientDAO clientDAO;

        private List<Client> clients;

 

        private void MainForm_Load(object sender, EventArgs e)

        {

            clientDAO = new ClientDAO();

            clients = clientDAO.GetClients();

            dataGridView1.DataSource = clients;

        }

 

ClientDAO

public List<Client> GetClients()

        {

            List<Client> clients = new List<Client>();

            using (OleDbConnection connection = Sql.Instance.GetConnection())

            {

                using (OleDbCommand command = connection.CreateCommand())

                {

                    command.CommandType = CommandType.Text;

                    command.CommandText = "SELECT [Id],[Name],[Email],[CategoryId] FROM [Client]";

                    using (OleDbDataReader reader = command.ExecuteReader())

                    {

                        while (reader.Read())

                        {

                            Client client = new Client();

                            client.Id = reader["Id"] == DBNull.Value ? default(int) : int.Parse(reader["Id"].ToString());

                            client.Name = reader["Name"] == DBNull.Value ? default(string) : reader["Name"].ToString();

                            client.Email = reader["Email"] == DBNull.Value ? default(string) : reader["Email"].ToString();

                            client.CategoryId = reader["CategoryId"] == DBNull.Value ? default(int) : int.Parse(reader["CategoryId"].ToString());

                            clients.Add(client);

                        }

                    }

                }

            }

            return clients;

        }

 

GetOne

MainForm

// récupération du client par la clé primaire

Client client = clientDAO.GetClient(12);

ClientDAO

public Client GetClient(int id)

        {

            Client client = new Client();

            using (OleDbConnection connection = Sql.Instance.GetConnection())

            {

                using (OleDbCommand command = connection.CreateCommand())

                {

                    command.CommandType = CommandType.Text;

                    command.CommandText = "SELECT [Id],[Name],[Email],[CategoryId] FROM [Client] WHERE [Id]=?";

                    command.Parameters.Add(new OleDbParameter("@id", id));

 

                    using (OleDbDataReader reader = command.ExecuteReader())

                    {

                        while (reader.Read())

                        {

                            client.Id = reader["Id"] == DBNull.Value ? default(int) : int.Parse(reader["Id"].ToString());

                            client.Name = reader["Name"] == DBNull.Value ? default(string) : reader["Name"].ToString();

                            client.Email = reader["Email"] == DBNull.Value ? default(string) : reader["Email"].ToString();

                            client.CategoryId = reader["CategoryId"] == DBNull.Value ? default(int) : int.Parse(reader["CategoryId"].ToString());

                        }

                    }

                }

            }

 

            return client;

        }

 

Add

MainForm

// création d'un client

Client client = new Client() { Name="Prenium Patrick",Email="preniumpat54@msn.com",CategoryId=2};

 

// ajout

clientDAO.AddClient(client);

ClientDAO

public int AddClient(Client client)

        {

            int result = 0;

            using (OleDbConnection connection = Sql.Instance.GetConnection())

            {

 

                using (OleDbCommand command = connection.CreateCommand())

                {

                    command.CommandType = CommandType.Text;

                    command.CommandText = "INSERT INTO [Client]([Name],[Email],[CategoryId]) VALUES(?,?,?);";

                    command.Parameters.Add(new OleDbParameter("@name", client.Name));

                    command.Parameters.Add(new OleDbParameter("@email", client.Email));

                    command.Parameters.Add(new OleDbParameter("@categoryId", client.CategoryId));

                    command.ExecuteNonQuery();

 

                    command.CommandText = "SELECT @@IDENTITY";

                    result = Convert.ToInt32(command.ExecuteScalar());

                }

            }

            return result;

        }

 

Update

MainForm

// récupérer le client à modifier

Client client = clients.Where(c=> c.Id ==14).First();

client.Email = "preniumpatrick54@voila.fr";

 

// mise à jour

clientDAO.UpdateClient(client);

ClientDAO

public int UpdateClient(Client client)

        {

            int result = 0;

            using (OleDbConnection connection = Sql.Instance.GetConnection())

            {

 

                using (OleDbCommand command = connection.CreateCommand())

                {

                    command.CommandType = CommandType.Text;

                    command.CommandText = "UPDATE [Client] SET [Name]=?,[Email]=?,[CategoryId]=? WHERE [Id]=?";

                    command.Parameters.Add(new OleDbParameter("@name", client.Name));

                    command.Parameters.Add(new OleDbParameter("@email", client.Email));

                    command.Parameters.Add(new OleDbParameter("@categoryId", client.CategoryId));

                    command.Parameters.Add(new OleDbParameter("@Id", client.Id));

                    result = command.ExecuteNonQuery();

                }

            }

            return result;

        }

 

Delete

MainForm

// récupérer le client à supprimer, exemple le client sélectionné dans le datagridView(propiétés du datagridView MultiSelect à false et SelectionMode à FullRowSelect)

Client client = (Client)dataGridView1.SelectedRows[0].DataBoundItem;

 

clientDAO.DeleteClient(client);

 

ClientDAO

public int DeleteClient(Client client)

             {

                    int result = 0;

            using (OleDbConnection connection = Sql.Instance.GetConnection())

            {

                using (OleDbCommand command = connection.CreateCommand())

                {

                    command.CommandType = CommandType.Text;

                    command.CommandText = "DELETE FROM [Client] WHERE [Id]=?";

                    command.Parameters.Add(new OleDbParameter("@id", client.Id));

                    result = command.ExecuteNonQuery();

                }

            }

 

                    return result;

             }

 

Partager cet article

Repost 0
Published by Romagny13 - dans ADO.NET
commenter cet article

commentaires