sql - C# Populate GridView from database with update and delete function

Question

I have been hunting for a way to populate a GridView from a sql local db with edit and delete functionality without creating duplicates.

So far I have got as far as creating a SQL query, storing the results as a datatable and pumping that in to the GridView. I cannot seem to figure out how to go about managing edit and delete functionality and hoping someone here can help.

Much appreciated.

Here's how I am populating the GridView:

//Load data in to parameters gridview
        using (SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PowerShellManager.Properties.Settings.PSManagerConnectionString"].ConnectionString))
        {
            con.Open();

            SqlCommand comm3 = new SqlCommand("SELECT * FROM Parameters WHERE ScriptId = (SELECT Scripts.ScriptId FROM Scripts WHERE Scripts.ScriptName = @ScriptName) AND ProfileId = (SELECT Profiles.ProfileId FROM Profiles WHERE Profiles.ProfileName = @currentlySelectedProfile)", con);
            comm3.Parameters.AddWithValue("ScriptName", listView_Scripts.SelectedItems[0].Text);
            comm3.Parameters.AddWithValue("currentlySelectedProfile", currentlySelectedProfile);
            DataTable t3 = new DataTable();
            t3.Load(comm3.ExecuteReader());
            //variablesDataGridView.DataSource = null;
            parametersDataGridView.DataSource = t3;
            //variablesBindingSource.Filter = new 
            con.Close();
        }

Answer 1

I've created a simple application just for you and edited whole answer! This will be a very long answer. You can save new Person to database or delete a Person from database, aswell as from GridView. Here is code with some picture examples:

Person class:

public class Person
{

    #region Members

    private int _ID = -1;
    private string _FirstName = string.Empty;
    private string _LastName = string.Empty;
    private DateTime? _BirthDate = null;
    private string _PhoneNumber = string.Empty;
    private string _Email = string.Empty;
    private bool _Changed = false;

    #endregion

    public Person()
    {
        _Changed = false;
    }

    #region Methods

    public void Save()
    {
        if (!_Changed)
            return;

        using (SqlConnection con = new SqlConnection(@"server=.sqlexpress;database=People;integrated Security=True;"))
        {
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = "Person_Save";
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@ID", _ID);
            cmd.Parameters.AddWithValue("@FirstName", _FirstName);
            cmd.Parameters.AddWithValue("@LastName", _LastName);
            cmd.Parameters.AddWithValue("@BirthDate", _BirthDate);
            cmd.Parameters.AddWithValue("@PhoneNumber", _PhoneNumber);
            cmd.Parameters.AddWithValue("@Email", _Email);

            try
            {
                con.Open();

                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    if (dr.Read())
                    {
                        _ID = dr.GetInt32(0);
                        _Changed = false;
                    }
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "Error message");
            }
        }
    }

    public void Delete()
    {
        using (SqlConnection con = new SqlConnection(@"server=.sqlexpress;database=People;integrated Security=True;"))
        {
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = "Person_Delete";
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@ID", _ID);

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "Error message");
            }
        }
    }

    #endregion

    #region Properties

    public int ID
    {
        get { return _ID; }
        set
        {
            if (_ID != value)
            {
                _ID = value;
                _Changed = true;
            }
        }
    }

    public string FirstName
    {
        get { return _FirstName; }
        set
        {
            if (_FirstName != value)
            {
                _FirstName = value;
                _Changed = true;
            }
        }
    }

    public string LastName
    {
        get { return _LastName; }
        set
        {
            if (_LastName != value)
            {
                _LastName = value;
                _Changed = true;
            }
        }
    }

    public DateTime? BirthDate
    {
        get { return _BirthDate; }
        set
        {
            if (_BirthDate != value)
            {
                _BirthDate = value;
                _Changed = true;
            }
        }
    }

    public string PhoneNumber
    {
        get { return _PhoneNumber; }
        set
        {
            if (_PhoneNumber != value)
            {
                _PhoneNumber = value;
                _Changed = true;
            }
        }
    }

    public string Email
    {
        get { return _Email; }
        set
        {
            if (_Email != value)
            {
                _Email = value;
                _Changed = true;
            }
        }
    }

    public bool Changed
    {
        get { return _Changed; }
        set { _Changed = value; }
    }

    #endregion

}

---

PeopleForm class:

public partial class PeopleForm : Form
{
    public PeopleForm()
    {
        InitializeComponent();

        FillDataSource();
    }

    public void FillDataSource()
    {
        List<Person> list = new List<Person>();

        using (SqlConnection con = new SqlConnection(@"server=.sqlexpress;database=People;integrated Security=True;"))
        {
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = "People_Read";
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            try
            {
                con.Open();

                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        list.Add(new Person()
                        {
                            ID = (int)dr["ID"]
                           ,FirstName = dr["FirstName"] as string
                           ,LastName = dr["LastName"] as string
                           ,BirthDate = dr["BirthDate"] as DateTime?
                           ,PhoneNumber = dr["PhoneNumber"] as string
                           ,Email = dr["Email"] as string
                        });
                    }
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "Error message");
            }
        }

        personBindingSource.DataSource = list;
    }

    private void btnSave_Click(object sender, EventArgs e)
    {
        foreach (Person person in personBindingSource)
        {
            person.Save();
        }
    }

    private void btnDelete_Click(object sender, EventArgs e)
    {
        ((Person)grdPeople.CurrentRow.DataBoundItem).Delete();
        personBindingSource.RemoveAt(grdPeople.CurrentRow.Index);
    }
}

---

Stored procedures:

CREATE PROCEDURE [dbo].[Person_Save]
     @ID int
    ,@FirstName nvarchar(30)
    ,@LastName nvarchar(30)
    ,@BirthDate date
    ,@PhoneNumber nvarchar(30)
    ,@Email nvarchar(50)
AS
BEGIN
    IF(@ID = -1)
    BEGIN
        INSERT INTO People
        VALUES (@FirstName,
                @LastName,
                @BirthDate,
                @PhoneNumber,
                @Email)

        SET @ID = SCOPE_IDENTITY();
    END
    ELSE
    BEGIN
        UPDATE  People
        SET     FirstName = @FirstName,
                LastName = @LastName,
                BirthDate = @BirthDate,
                PhoneNumber = @PhoneNumber,
                Email = @Email

        WHERE   ID = @ID
    END

    SELECT @ID;     
END

---

CREATE PROCEDURE [dbo].[Person_Delete]
    @ID int
AS
BEGIN
    DELETE
    FROM People
    WHERE ID = @ID
END

---

CREATE PROCEDURE [dbo].[People_Read]
AS
BEGIN
    SELECT   ID
            ,FirstName
            ,LastName
            ,BirthDate
            ,PhoneNumber
            ,Email
    FROM People
    ORDER BY LastName, FirstName        
END

You will have to bind your Person class to your DataGridView as binding source like this:

In the end you get working application that looks like this:

Notice that I've changed colors just a bit, nothing special. I've added a few people as an example. You'll have to handle error for the wrong input in your birth date column, but have some fun exploring. I think I gave you more than enough to start your application.

Have fun!