Windows Forms: Insert Update Delete View and Search data from SQL Server in C#

By FoxLearn 6/1/2017 9:40:28 PM   12.1K
How to Insert Update Delete View and Search data from SQL Server in C#

Step 1Click New Project, then select Visual C# on the left, then Windows and then select Windows Forms Application. Name your project "CRUDSqlServer" and then click OK

crud sql c#Step 2: Design your form as below

crud sql c#

Step 3: Create a Customer table, then create an EF Model by using Entity Framework as below

entity framework c#

Step 4: Add code to handle your form as below

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Windows.Forms;

namespace CRUDSqlServer
{
    public partial class Form1 : Form
    {
        TestEntities test;
        public Form1()
        {
            InitializeComponent();
        }

        private void txtSearch_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == (char)13)//Enter key
            {
                if (string.IsNullOrEmpty(txtSearch.Text))
                {
                    dataGridView.DataSource = customerBindingSource;
                }
                else
                {
                    //using linq to query data
                    var query = from o in customerBindingSource.DataSource as List<Customer>
                                where o.CustomerID == txtSearch.Text || o.Fullname.Contains(txtSearch.Text) || o.Email == txtSearch.Text || o.Address.Contains(txtSearch.Text)
                                select o;
                    dataGridView.DataSource = query.ToList();
                }
            }
        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            try
            {
                panel.Enabled = true;
                txtCustomerID.Focus();
                //Create a new customer, then add to binding source
                Customer c = new Customer();
                test.Customers.Add(c);
                customerBindingSource.Add(c);
                customerBindingSource.MoveLast();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            panel.Enabled = true;
            txtCustomerID.Focus();
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            panel.Enabled = false;
            customerBindingSource.ResetBindings(false);
            foreach (DbEntityEntry entry in test.ChangeTracker.Entries())
            {
                //Check your object state
                switch (entry.State)
                {
                    case EntityState.Added:
                        entry.State = EntityState.Detached;
                        break;
                    case EntityState.Modified:
                        entry.State = EntityState.Unchanged;
                        break;
                    case EntityState.Deleted:
                        entry.Reload();
                        break;
                }
            }
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                //End edit & save data to sql database
                customerBindingSource.EndEdit();
                test.SaveChangesAsync();
                panel.Enabled = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
                customerBindingSource.ResetBindings(false);
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            panel.Enabled = false;
            test = new TestEntities();
            //Load data to binding source
            customerBindingSource.DataSource = test.Customers.ToList();
        }

        private void dataGridView_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Delete)
            {
                if (MessageBox.Show("Are you sure want to delete this record ?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    test.Customers.Remove(customerBindingSource.Current as Customer);
                    customerBindingSource.RemoveCurrent();
                }
            }
        }
    }
}

VIDEO TUTORIALS