Insert, Update, Delete and Searching records of database from C# application

Here we will look at how manipulation to database records is made using C# programming. Before you start coding do not forget to make connection string because this string allows C# program to connect to Sql Server. Also you have to add a namespace System.Data.SqlClient in your application which contains functionality of different classes used in connectivity.
Different Classes in System.Data.SqlClient:
There are many classes in this namespace but commonly used are:
SqlConnection
SqlCommand
SqlDataReader
SqlException
SqlDataAdapter  
Example:
Here in this application we will insert, update, delete and search records from person table placed in database CIS. Suppose we have following columns in person table. Stored procedures are used in this application you can use simple Sql statements for inset, update, delete and search.
·         Per_No
·         Per_Name
·         Per_Address
·         Per_Phone
And here is Form for Person class:
Now let’s look at coding which will insert, update, delete and search records from person table.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace CISPROJECT
{
    public partial class Person : Form
    {
      
SqlConnection Mycon= new SqlConnection("Data Source=MINHAS-A65DA31B\\SQLEXPRESS;Initial Catalog=CIS;Integrated Security=True");

          SqlCommand Mycmd;
          SqlDataReader Myreader;
        public Person()
        {
            InitializeComponent();
        }

        private void Insertbutton_Click(object sender, EventArgs e)
        {
            try
            {
                   
                if (Per_NotextBox.Text != "" && Per_NametextBox.Text != "" && Per_AddtextBox.Text != "" && Per_PhonetextBox.Text != "")
                {
                   Mycmd = Mycon.CreateCommand();
                             Mycon.Open();

                    Mycmd.CommandText = "exec Insertperson'" + Per_NotextBox.Text + "','" + Per_NametextBox.Text + " ','" + Per_AddtextBox.Text + " ','" + Per_PhonetextBox.Text + "'";
                    Mycmd.ExecuteNonQuery();
                    Mycon.Close();
                    MessageBox.Show("Data Inserted");
                }
                else
                {
                    MessageBox.Show("Please provide values for all fields");
                    Per_NotextBox.Focus();
                }
            }
            catch (SqlException e1)
            {
                MessageBox.Show(e1.Message);
            }

        }

        private void Updatebutton_Click(object sender, EventArgs e)
        {
            string name, address, phone;
            if (Per_NotextBox.Text != "")
            {
                try
                {
                    Mycmd = Mycon.CreateCommand();
                             Mycon.Open();

                    Mycmd.CommandText = "select * from person where per_No='" + Per_NotextBox.Text + "'";
                    Myreader = Mycmd.ExecuteReader();
                    if Myreader.Read())
                    {
                        name = Myreader[1].ToString();
                        address = Myreader[2].ToString();
                        phone = Myreader[3].ToString();

                        Myreader.Close();


                        if (Per_NametextBox.Text != "")
                            name = Per_NametextBox.Text;
                        if (Per_AddtextBox.Text != "")
                            address = Per_AddtextBox.Text.ToString();
                        if (Per_PhonetextBox.Text != "")
                            phone = Per_PhonetextBox.Text.ToString();

                        Mycmd.CommandText = "exec  updateperson '" + name + "','" + address + "','" + phone + "','" + Per_NotextBox.Text + "'";
                        Mycmd.ExecuteNonQuery();

                        Mycon.Close();
                        MessageBox.Show("Record updated");
                    }
                    else
                    {
                        MessageBox.Show("Value do not exist in the database");
                        Per_NotextBox.Focus();
                    }

                }

                catch (SqlException ce)
                {
                    MessageBox.Show(ce.Message);
                }



            }
            else
            {
                MessageBox.Show("Please Enter a value to update the record");
                Per_NotextBox.Focus();
            }

        }

        private void Deletebutton_Click(object sender, EventArgs e)
        {
            try
            {
                if (Per_NotextBox.Text != "")
                {
                    DialogResult dlg = new DialogResult();
                    dlg = MessageBox.Show("Do you really want to delete the record", "Confirm Deletion", MessageBoxButtons.YesNo);
                    if (dlg == DialogResult.Yes)
                   
          Mycmd = Mycon.CreateCommand();
          Mycon.Open();
Mycmd.CommandText = "exec  Deleteperson '" + Per_NotextBox.Text + "'";
          Mycmd.ExecuteNonQuery();
          Mycon.Close();
          MessageBox.Show("Data Deleted");
                    }
                   
                   
                }
                else
                {
                    MessageBox.Show("Please provide Per_No to delete record");
                    Per_NotextBox.Focus();
                }
            }

            catch (SqlException e1)
            {
                MessageBox.Show(e1.Message);
            }
        }

        private void Searchbutton_Click(object sender, EventArgs e)
        {
            Mycon.Open();
SqlDataAdapter da = new SqlDataAdapter("select * from person where per_no='" + Per_NotextBox.Text + "'", Mycon);
            DataTable dt = new DataTable();
            da.Fill(dt);

            dataGridView1.DataSource = dt;


        }


     
    }

    }

Comments

  1. Anonymous11 May 2014 at 02:35

    sql insert , Update , delete using c#
    with code , easy explanation and demo app
    http://geeksprogrammings.blogspot.com/2014/05/sql-insert-update-delete-using-c.html

    ReplyDelete

Post a Comment

Popular posts from this blog

Check if ViewBag is null or doesn't exist

ASP.NET MVC uses  ViewBag to store some data from Controller and than accessed in View like you can save UserName in ViewBag who is logged in to your website and than display it in view. Sometimes you need to check if ViewBag exists or it is not null than execute your next piece of code. Let's take an example. Controller Code public ActionResult Index() { ViewBag . UserName = "John"; return View(); } View Code @if ( ViewBag . UserName ! = null ) { < label >@ViewBag.UserName</ label >;     } Here if ViewBag.UserName is not null than label will be rendered with username. If it is null the code will not execute. So you can easily check for a ViewBag Property and execute your code.
Read more

Using Progress Bar In C#

Image
Using Progress Bar in C#: Progress Bar is very commonly used control in many applications to display the progress of task being performed. Progress Bar is displayed when copying files, deleting files or installing some software. How to use Progress Bar: ·          Start New Windows Form Application. ·          Go to Toolbox. ·          Select Progress Bar and Drag it to your Form. To make Progress Bar working you need to know some properties of Progress Bar Progress Bar.Maximum: Defines how long it should run Progress Bar.Step: Defines how much it should increase current position of Progress Bar. Progress Bar.Style: How should it look like? There is also a very basic method which is used to run Progress Bar properly. Progress Bar.PerformStep() Let’s see an example. In this example we will copy files and folders from one location to another and show the progress bar while copying. using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using
Read more

Jquery serer side datatables in asp.net

In this post we will learn how we can implement jquery   server side datatables in asp.net mvc application. We need to add datatables library to our page, we can add references of libray from datatables <a href="https://datatables.net/" target="_blank">website</a>. We need to create an html table on which we will apply datatable. <table class="table table-striped table-bordered table-hover table-checkable order-column dataTable" id="tblInventoryItems">   <thead>     <tr>       <th>Item Name</th>       <th>Item Price</th>       <th>Item Quantity</th>       <th></th>     </tr>   </thead>   <tbody></tbody> </table> Then we need the code which will apply the datatable to above html table. Below is the code to apply datatable $("yourtableid").DataTable({         "proccessing": true,         "serverSide": true,  
Read more