3
\$\begingroup\$

I made a small application using C#, SQL Database, entity framework , and windows forms to manipulate users (Add_User , Update_User , Delete_User) and their activities (Add_Activity , Update_Activity , Delete_Activity). I created a database with 2 tables, the first one with users and a second one with the activities of each user. The relationship is one to many with a foreign key at the activities table which is the User_ID in the first table. The first Frame has the option to create a new user or to choose a user from a data grid loaded with all the users from the table and update or delete them. By double clicking at one of the users at the data grid a second Frame opens with a data grid of all the activities of that user. The code for that is:

void updateActions()
{
    using (I2SEntities1 db = new I2SEntities1())
        dgActions.DataSource = db.Actions.Where(x => x.Client_ID.Equals(Main.client.Client_ID)).ToList<Action>();
}

Where dgActions is the data grid in the second frame and Main.client.Client_ID is the ID of the User.

  • My first question: Is this a good way of doing it. In order to load all the activities of the user I have to iterate to all the members in the table activities which doesn't look so good. There is an ICollection<Actions> in the auto generated code from entity framework , how can i make use of it?.

  • My second question: If I want to delete a user, I can't do it if it has activities because of the foreign key which is normal. How would I go about doing this?

Is there another, better way, to check if a user still has activities and display a message? I can do it with a try/catch block or by checking if the count in the ICollection<Action> of the class Client is zero . But is this the right away?

private void btnDelete_Click(object sender, EventArgs e)
    {
        if(MessageBox.Show("Do you really want to delete this user ?" , "Delete User" , MessageBoxButtons.YesNo) == DialogResult.Yes)
        {
            using (I2SEntities1 db = new I2SEntities1())
            {
                var entry = db.Entry(client);
                if (entry.State == System.Data.Entity.EntityState.Detached)
                    db.Clients.Attach(client);

                if (client.Actions.Count == 0)
                {
                    try
                    {
                        db.Clients.Remove(client);
                        db.SaveChanges();
                        MessageBox.Show("User: " + client.Name + " deleted successfully");
                    }
                    catch(Exception ex)
                    {
                        MessageBox.Show(ex.Message.ToString());
                    }

                }
                else
                    MessageBox.Show("Can't delete User " + client.Name + ". User has activities");



            }
            Clear();
            update_Grid();


        }
    }

These are my main concerns, but I would also like to know if I have a good application design.

namespace I2S
{
    public partial class Main : Form
    {
       public static Client client = new  Client();


        public Main()
        {
            InitializeComponent();

        }


        private void btnCancel_Click(object sender, EventArgs e)
        {
            Clear();
        }
        void Clear()
        {
            txtName.Text = txtPhone.Text = txtAddress.Text = "";
            btnAdd.Text = "Add";
            btnDelete.Enabled = false;
            client.Client_ID = 0;
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            if(MessageBox.Show("Do you really want to delete this user ?" , "Delete User" , MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                using (I2SEntities1 db = new I2SEntities1())
                {
                    var entry = db.Entry(client);
                    if (entry.State == System.Data.Entity.EntityState.Detached)
                        db.Clients.Attach(client);
                    db.Clients.Remove(client);

                    db.SaveChanges();
                    MessageBox.Show("Deleted Successfully");

                }
                Clear();
                update_Grid();


            }
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            client.Name = txtName.Text.Trim();
            client.Address = txtAddress.Text.Trim();
            client.Telephone = txtPhone.Text.Trim();

            using (I2SEntities1 db = new I2SEntities1())
            {
                if (client.Client_ID == 0)
                {
                    db.Clients.Add(client);
                    db.SaveChanges();


                    MessageBox.Show("New user added");
                }
                else
                {
                    db.Entry(client).State = System.Data.Entity.EntityState.Modified;
                    db.SaveChanges();



                    MessageBox.Show("User Updated Successfully");
                }
                Clear();
                update_Grid();

            }


        }

        private void Main_Load(object sender, EventArgs e)
        {
            update_Grid();

        }


        void update_Grid()
        {
            dgClients.AutoGenerateColumns = false;
            using (I2SEntities1 db = new I2SEntities1())
            {
                dgClients.DataSource = db.Clients.ToList<Client>();


            }
        }
        private void dgClients_CellClick(object sender, DataGridViewCellEventArgs e)
        {

            if (dgClients.CurrentRow.Index != -1)
            {
                client.Client_ID = Convert.ToInt32(dgClients.CurrentRow.Cells["Client_ID"].Value);
            }
            using (I2SEntities1 db = new I2SEntities1())
            {
                client = db.Clients.Where(x => x.Client_ID == client.Client_ID).FirstOrDefault();
                txtName.Text = client.Name;
                txtAddress.Text = client.Address;
                txtPhone.Text = client.Telephone;

            }
            btnAdd.Text = "Update";
            btnDelete.Enabled = true;

        }
        private void dgClients_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            if (dgClients.CurrentRow.Index != -1)
            {
                client.Client_ID = Convert.ToInt32(dgClients.CurrentRow.Cells["Client_ID"].Value);
            }
            Form2 actionsForm = new Form2();



            actionsForm.Show();
        }




    }
}


namespace I2S
{
    public partial class Form2 : Form
    {
        Action action = new Action();

        public Form2()
        {
            InitializeComponent();
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            Clear();
            txtDateStart.Format = DateTimePickerFormat.Short;
            txtTimeStart.Format = DateTimePickerFormat.Time;
            txtDateEnd.Format = DateTimePickerFormat.Short;
            txtTimeEnd.Format = DateTimePickerFormat.Time;


            btDeleteAction.Enabled = false;
            updateActions();
        }

        private void dgActions_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if(dgActions.CurrentRow.Index != -1)
            {
                action.Action_Id = Convert.ToInt32(dgActions.CurrentRow.Cells["Action_ID"].Value);
            }
            using (I2SEntities1 db = new I2SEntities1())
            {
                action = db.Actions.Where(x => x.Action_Id == action.Action_Id).FirstOrDefault();
                txtActionDescription.Text = action.Action_Desc;
                txtDateStart.Text = action.Action_Beggin.Date.ToString();
                txtTimeStart.Text = action.Action_Beggin.TimeOfDay.ToString();
                txtDateEnd.Text = action.Action_End.Date.ToString();
                txtTimeEnd.Text = action.Action_End.TimeOfDay.ToString();
            }
            btAddAction.Text = "Update";
            btDeleteAction.Enabled = true;

        }

        private void btDeleteAction_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Do you really want to delete this action ?", "Delete Action", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                using (I2SEntities1 db = new I2SEntities1())
                {
                    var entry = db.Entry(action);
                    if (entry.State == System.Data.Entity.EntityState.Detached)
                        db.Actions.Attach(action);
                    db.Actions.Remove(action);
                    MessageBox.Show("Action Sucesfully Removed");

                    db.SaveChanges();

                }
                Clear();
                updateActions();
            }
        }

        private void btAddAction_Click(object sender, EventArgs e)
        {
            action.Action_Desc = txtActionDescription.Text.Trim();

            action.Action_Beggin = Convert.ToDateTime(txtDateStart.Value.Date + txtTimeStart.Value.TimeOfDay);
            action.Action_End = Convert.ToDateTime(txtDateEnd.Value.Date + txtTimeEnd.Value.TimeOfDay);
            action.Client_ID = Main.client.Client_ID;

            using (I2SEntities1 db = new I2SEntities1())
            {
                if (action.Action_Id == 0)
                {
                    db.Actions.Add(action);
                    db.SaveChanges();

                    MessageBox.Show("New action added");
                }
                else
                {
                    db.Entry(action).State = System.Data.Entity.EntityState.Modified;


                    db.SaveChanges();

                    MessageBox.Show("Action Succesfully Updated");
                }
            }
            Clear();
            updateActions();

        }
        void updateActions()
        {
            using (I2SEntities1 db = new I2SEntities1())
                dgActions.DataSource = db.Actions.Where(x => x.Client_ID.Equals(Main.client.Client_ID)).ToList<Action>();
        }



        private void btClear_Click(object sender, EventArgs e)
        {
            Clear();
        }
        void Clear()
        {
            txtActionDescription.Text = txtDateStart.Text =  txtTimeStart.Text = txtDateEnd.Text = txtTimeEnd.Text= "";
            btDeleteAction.Enabled = false;
            btAddAction.Text = "Add";
            action.Action_Id = 0;
        }
    }
}

The DTO's for Client and Actions :

    namespace I2S
{
    using System;
    using System.Collections.Generic;

    public partial class Client
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Client()
        {
            this.Actions = new HashSet<Action>();
        }

        public int Client_ID { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public string Telephone { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Action> Actions { get; set; }
    }
}


namespace I2S
{
    using System;
    using System.Collections.Generic;

    public partial class Action
    {
        public int Action_Id { get; set; }
        public string Action_Desc { get; set; }
        public System.DateTime Action_Beggin { get; set; }
        public System.DateTime Action_End { get; set; }
        public int Client_ID { get; set; }

        public virtual Client Client { get; set; }
    }
}

namespace I2S
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;

    public partial class I2SEntities1 : DbContext
    {
        public I2SEntities1()
            : base("name=I2SEntities1")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }

        public virtual DbSet<Action> Actions { get; set; }
        public virtual DbSet<Client> Clients { get; set; }
    }
}
\$\endgroup\$
  • \$\begingroup\$ It would be helpful to reviewers, if you showed the DTO's for Client and Action - and the database context. Are you using code first or db first when maintaining your database? \$\endgroup\$ – Henrik Hansen Jul 1 at 11:18
  • \$\begingroup\$ @Henrik Hansen For the database context shall I printScreen it? What do you mean by code first or db first? \$\endgroup\$ – Aris Koutsoukis Jul 1 at 11:32
  • \$\begingroup\$ No, I just mean the code for I2SEntities1 \$\endgroup\$ – Henrik Hansen Jul 1 at 11:43
  • 1
    \$\begingroup\$ Code first:you create the data model as classes in C# and use migrations to create the database. db first: You create your database - tables ect. - in SQL Server "by hand". \$\endgroup\$ – Henrik Hansen Jul 1 at 11:59
  • 1
    \$\begingroup\$ I create my database in SQL "by hand" and than i used entity framework for the classes. \$\endgroup\$ – Aris Koutsoukis Jul 1 at 12:01
1
\$\begingroup\$

So for anyone that might find it interesting: I found how to make it more efficient. I used as data source for my data grid this:

dgActions.DataSource = db.Actions.Where(
    x => x.Client_ID.Equals(Main.client.Client_ID)).ToList<Action>();

But in my opinion that is not so efficient. So I thought to use the navigation property of the class Client as data source. I pass a client_ID from the first form. Create a new Client object and copy the one from the data base that has the same Client_ID passed from the previous form and use the navigation property of that object as data source. I think this is a better implementation:

void updateActions()
{
    using (I2SEntities1 db = new I2SEntities1())
    {
        myClient = db.Clients.Where(x 
            => x.Client_ID == Main.client.Client_ID).FirstOrDefault();
        dgActions.DataSource = myClient.Actions.ToList<Action>();
    }
}
\$\endgroup\$
  • 1
    \$\begingroup\$ in my opinion that is not so efficient. - efficiency doesn't care about opinions :-P this needs to be proven with a benchmark ;-] \$\endgroup\$ – t3chb0t Jul 1 at 16:51
  • \$\begingroup\$ @t3chb0t You are right. But in this case it looks obvious. In the first case I had to iterate over the hole table to find the data that I needed and in the other case I just use the ones at the IColection. I am not familiar with this kind of programing and with c#. But if I would compare it with the complexity of an algorithm in the first case it would be O(n) where n number of members in an array and in the second case O(k) where k size of bucket of the unordered associative container. where 1 < k < n ; \$\endgroup\$ – Aris Koutsoukis Jul 1 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.