C# and SQL Server (V1) Perform SMART CRUD Operations with SEARCH (Windows Forms Source Code)

on

Microsoft Visual Studio 2019 : C# and Microsoft SQL Server Database Tutorial (Version 1).
👨‍🏫 How to Perform SMART CRUD (Create Read Update & Delete) Operations with Search functionality.

🎓 Master Visual Basic .NET and Access Database By Building the Point Of Sale System (POS).
📲 Enroll Now: https://bit.ly/2WcbRhX

👨‍🏫 How to connect SQL Server database using VB.NET
In computer programming, create, read, update, and delete (CRUD) are the four basic functions of persistent storage. Alternate words are sometimes used when defining the four basic functions of CRUD, such as retrieve instead of read, modify instead of update, or destroy instead of delete.
Read more: Wikipedia

Screenshot

YouTube : (Original Version)
▶️ https://youtu.be/ZA4wr2DW88E

YouTube : Edited Version (Part 1/2)
▶️ https://youtu.be/mw4TmD2AMtA

YouTube : Edited Version (Part 2/2)
▶️ https://youtu.be/iqT0e09JCXQ

[-📍-] Recommended Videos [-📍-]

🚀 4K | How to Download and Install Visual Studio 2019, SQL Server 2017 and SQL Server Management Studio (SSMS).
📺 https://youtu.be/ZA19qSiPcOE

🚀 4K | How to Download and Install PostgreSQL on Windows 10 and NpgSQL - ADO.NET Data Provider for PostgreSQL.
📺 https://youtu.be/KCxWG2g22yo

🚀 4K | How to download and install XAMPP for Windows 10 and MySQL Connector/NET.
📺 https://youtu.be/yHYyMMlYRjk

[-📍-] Bonus Video Tutorials [-📍-]

-- Microsoft SQL Server --
Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network. Wikipedia

🚀 4K | VB.NET and SQL Server Tutorial | Perform SMART CRUD (Create Read Update & Delete) Operations with Search functionality.
📺 Version 1: https://youtu.be/V7zZ92qS7Bg
📺 Version 2: https://youtu.be/jIyGTcccM94

🚀 4K | C# and SQL Server Tutorial | Perform SMART CRUD (Create Read Update & Delete) Operations with Search functionality.
📺 Version 1: https://youtu.be/ZA4wr2DW88E
📺 Version 2: https://youtu.be/i_yQHRbFBAA

-- PostgreSQL --
PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and technical standards compliance. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users. Wikipedia

🚀 4K | VB.NET and PostgreSQL Tutorial | Perform SMART CRUD (Create Read Update & Delete) Operations with Search functionality.
📺 https://youtu.be/kqyeaTWol9k

🚀 4K | C# and PostgreSQL Tutorial | Perform SMART CRUD (Create Read Update & Delete) Operations with Search functionality.
📺 https://youtu.be/SnpW2hAuTmk

-- MySQL --
MySQL is an open-source relational database management system. Its name is a combination of "My", the name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language. Wikipedia

🚀 4K | VB.NET and MySQL Tutorial | Perform SMART CRUD (Create Read Update & Delete) Operations with Search functionality.
📺 https://youtu.be/J2e9q9CgxVI

🚀 4K | C# and MySQL Tutorial | Perform SMART CRUD (Create Read Update & Delete) Operations with Search functionality.
📺 https://youtu.be/tvd6tnQcDGA

 -- Microsoft Access --
Microsoft Access is a database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of applications, included in the Professional and higher editions or sold separately. Wikipedia

🚀 Full-HD | VB.NET and Access Database Tutorial | Perform SMART CRUD (Create Read Update & Delete) Operations with Search functionality.
📺 Part 1/2: https://youtu.be/LfHSZ7vLwyU
📺 Part 2/2: https://youtu.be/PMZXbvQ5X5Y

🚀 Full-HD | C# and Access Database Tutorial | Perform SMART CRUD (Create Read Update & Delete) Operations with Search functionality.
📺 Part 1/2: https://youtu.be/TNOdAwOo4Kk
📺 Part 2/2: https://youtu.be/wonsL3PpOY4

[FREE SOURCE CODE by iBASSKUNG]

🎯 Souce Code: http://bit.ly/2Q9nFht

#BEGIN

Class: CRUD.cs

using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace CS_SMART_CRUD_V1
{
    class CRUD
    {

        public static string ConString = "Data Source=TANIN-SANGNGAM\\SQLEXPRESS;Initial Catalog=CS_SMART_CRUD;Integrated Security=True";
        public static string sql = string.Empty;
        public static SqlConnection con = new SqlConnection(ConString);
        public static SqlCommand cmd = default(SqlCommand);

        public static DataTable PerformCRUD(SqlCommand com)
        {

            SqlDataAdapter da = default(SqlDataAdapter);
            DataTable dt = new DataTable();

            try
            {
                da = new SqlDataAdapter();
                da.SelectCommand = com;
                da.Fill(dt);

                return dt;

            }
            catch (System.Exception ex)
            {
                MessageBox.Show("An error occurred: " + ex.Message, "Failed to execute SQL Statement : iBasskung Tutorial",
                                MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return dt;

        }
    }
}


Form: CRUD_FORM.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace CS_SMART_CRUD_V1
{
    public partial class CRUD_FORM : Form
    {

        private string ID = "";

        public CRUD_FORM()
        {
            InitializeComponent();
            resetME();
        }

        private void resetME()
        {

            firstNameTextBox.Clear();
            lastNameTextBox.Clear();
            keywordTextBox.Clear();

            if(genderComboBox.Items.Count > 0)
            {
                genderComboBox.SelectedIndex = 0;
            }

            updateButton.Text = "Update ()";
            deleteButton.Text = "Delete ()";

        }

        private void CRUD_FORM_Load(object sender, EventArgs e)
        {
            loadData("");
        }

        private void loadData(string keyword)
        {

            CRUD.sql = "SELECT AUTO_ID, FIRST_NAME, LAST_NAME, [FIRST_NAME] + ' ' + [LAST_NAME] AS FULL_NAME, GENDER FROM TBL_SMART_CRUD " + 
                       "WHERE CAST([AUTO_ID] AS nvarchar) + ' ' + [FIRST_NAME] + ' ' + [LAST_NAME] LIKE '%' + @keyword + '%' OR [GENDER] = @keyword";

            CRUD.cmd = new SqlCommand(CRUD.sql, CRUD.con);
            CRUD.cmd.Parameters.Clear();
            CRUD.cmd.Parameters.AddWithValue("keyword", keyword);

            DataTable dt = CRUD.PerformCRUD(CRUD.cmd);

            if (dt.Rows.Count > 0)
            {
               // MessageBox.Show("Number of rows: " + dt.Rows.Count.ToString());
            }

            dgv1.MultiSelect = false;
            dgv1.AutoGenerateColumns = true;
            dgv1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;

            dgv1.DataSource = dt;

            dgv1.Columns[0].HeaderText = "ID";
            dgv1.Columns[1].HeaderText = "First Name";
            dgv1.Columns[2].HeaderText = "Last Name";
            dgv1.Columns[3].HeaderText = "Full Name";
            dgv1.Columns[4].HeaderText = "Gender";

            dgv1.Columns[0].Width = 65;
            dgv1.Columns[1].Width = 150;
            dgv1.Columns[2].Width = 150;
            dgv1.Columns[3].Width = 200;
            dgv1.Columns[4].Width = 85;

            if (dgv1.Rows.Count > 0)
            {
                // DataGridView : Auto select first row.
                dgv1.CurrentCell = dgv1.Rows[0].Cells[0];
            }

        }

        private void execute(string mySQL, string param)
        {
            CRUD.cmd = new SqlCommand(mySQL, CRUD.con);
            AddParameters(param);
            CRUD.PerformCRUD(CRUD.cmd);
        }

        private void AddParameters(string str)
        {
            CRUD.cmd.Parameters.Clear();
            CRUD.cmd.Parameters.AddWithValue("firstName", firstNameTextBox.Text.Trim());
            CRUD.cmd.Parameters.AddWithValue("lastName", lastNameTextBox.Text.Trim());
            CRUD.cmd.Parameters.AddWithValue("gender", genderComboBox.SelectedItem.ToString());

            if (str == "Update" || str == "Delete" && !string.IsNullOrEmpty(this.ID))
            {
                CRUD.cmd.Parameters.AddWithValue("id", this.ID);
            }

        }

        private void insertButton_Click(object sender, EventArgs e)
        {

            if (string.IsNullOrEmpty(firstNameTextBox.Text.Trim()) ||
                string.IsNullOrEmpty(lastNameTextBox.Text.Trim()))
            {
                MessageBox.Show("Please input first name and last name.",
                    "Insert Data : iBasskung Tutorial", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            CRUD.sql = "INSERT INTO TBL_SMART_CRUD(FIRST_NAME, LAST_NAME, GENDER) VALUES(@firstName, @lastName, @gender)";
            execute(CRUD.sql, "Insert");

            MessageBox.Show("The record has been saved.","Insert Data : iBasskung Tutorial", MessageBoxButtons.OK, MessageBoxIcon.Information);

            loadData("");

            resetME();

        }

        private void dgv1_CellClick(object sender, DataGridViewCellEventArgs e)
        {

            if (e.RowIndex != -1)
            {

                this.ID = Convert.ToString(dgv1.CurrentRow.Cells[0].Value);
                updateButton.Text = "Update (" + this.ID + ")";
                deleteButton.Text = "Delete (" + this.ID + ")";

                firstNameTextBox.Text = Convert.ToString(dgv1.CurrentRow.Cells[1].Value);
                lastNameTextBox.Text = Convert.ToString(dgv1.CurrentRow.Cells[2].Value);

                genderComboBox.SelectedItem = Convert.ToString(dgv1.CurrentRow.Cells[4].Value);

            }

        }

        private void updateButton_Click(object sender, EventArgs e)
        {

            if(dgv1.Rows.Count == 0)
            {
                MessageBox.Show(dgv1.Rows.Count.ToString() + " No rows found.", "Update Data : iBasskung Tutorial", 
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            if (updateButton.Text.Equals("Update ()"))
            {
                MessageBox.Show("Please select item from the list.", "Update Data : iBasskung Tutorial",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            if (string.IsNullOrEmpty(firstNameTextBox.Text.Trim()) ||
                string.IsNullOrEmpty(lastNameTextBox.Text.Trim()))
            {
                MessageBox.Show("Please input first name and last name.",
                    "Update Data : iBasskung Tutorial", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            CRUD.sql = "UPDATE TBL_SMART_CRUD SET FIRST_NAME = @firstName, LAST_NAME = @lastName, GENDER = @gender WHERE AUTO_ID = @id";

            execute(CRUD.sql, "Update");

            MessageBox.Show("The record has been updated!", "Update Data : iBasskung Tutorial", MessageBoxButtons.OK, MessageBoxIcon.Information);

            loadData("");

            resetME();

        }

        private void deleteButton_Click(object sender, EventArgs e)
        {

            if (dgv1.Rows.Count == 0)
            {
                MessageBox.Show(dgv1.Rows.Count.ToString() + " No rows found.", "Delete Data : iBasskung Tutorial",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            if (deleteButton.Text.Equals("Delete ()"))
            {
                MessageBox.Show("Please select item from the list.", "Delete Data : iBasskung Tutorial",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            if (MessageBox.Show("Do you want to permanently delete the selected record?", 
                "Delete Data : iBasskung Tutorial", MessageBoxButtons.YesNo, MessageBoxIcon.Question, 
                MessageBoxDefaultButton.Button1) == DialogResult.Yes)
            {

                CRUD.sql = "DELETE FROM TBL_SMART_CRUD WHERE AUTO_ID = @id";

                execute(CRUD.sql, "Delete");

                MessageBox.Show("The record has been deleted!", "Delete Data : iBasskung Tutorial", MessageBoxButtons.OK, MessageBoxIcon.Information);

                loadData("");

            }

            resetME();

        }
        private void searchButton_Click(object sender, EventArgs e)
        {

            if (string.IsNullOrEmpty(keywordTextBox.Text.Trim()))
            {
                loadData("");
            }
            else
            {
                loadData(keywordTextBox.Text.Trim());
            }

            resetME();

            if (keywordTextBox.CanSelect)
            {
                keywordTextBox.Select();
            }

        }
    }
}

#END

⛳ Follow me around ⛳
✔ Want to get updates on new courses or other cool free stuff? Just follow me on social media if that's your thing!

📺 Pages:
📍 https://www.facebook.com/CodeAMinute
📍 https://www.facebook.com/IbasskungTutorial
📍 https://www.facebook.com/codewithibasskung

📺 YouTube:
📍 https://www.youtube.com/c/iBasskung

📺 Udemy (Online Course):
📍 https://www.udemy.com/vbnet-crystal-reports

📺 Twitter:
📍 https://twitter.com/#!/IBasskung

📺 Blogger:
📍 https://codeaminute.blogspot.com
📍 http://bit.ly/2wjuwvs

💯 THANK YOU SO MUCH 💯

Tags: visual studio 2019, visual basic 2019, vb.net, vb.net tutorial for beginners visual studio 2019, visual basic, vb, visual studio 2019 visual basic tutorial, visual studio 2019 tutorial, how to use visual studio 2019, visual basic tutorial 2019, access, visual studio

#VisualCSharp #CSharp #SQLServer #CRUDOperation

Comments

Post a Comment