In my last blog I will told you that how to execute select command in sql by using ADO.NET. Now in this interesting blog I will told you that how to execute Insert, Delete and Update command by using ADO.Net. This program will demonstrate how to perform this task using C# Ado.Net.

Program to execute insert, delete and update command

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;             //Namespace required for ado.net programing.
using System.Data.SqlClient;   //Namespace required for ado.net programing.
namespace ConnectionDemo
{
    class Program
    {
        /// <summary>
        /// This property will return connection string required to establish connection to desired server.
        /// </summary>
        public static string getConnectionString
        {
            get
            {
                return @"Data Source=AAA-PC3;User Id=sa;Password=aaaa;Initial Catalog=WorkBook";
            }
        }
        /// <summary>
        /// This method will return boolean type value. If it returns true it means connection successfully
        /// establish and if it returns false then it means some problem occurs while establishing connection to server.
        /// </summary>
        /// <returns></returns>
        public static bool establishConnection()
        {
            bool status = false;
            try
            {
                con = new SqlConnection();                      //Create an object of SqlConnection class. This class will reside in System.Data.SqlClient namespace.
                con.ConnectionString = getConnectionString;     //Pass connection string to SqlConnection class by calling ConnectionString property of SqlConnection class.
                con.Open();                                     //Open connection by calling Open() method of SqlConnection class.
                status = true;                                  //Make status to be true flag.
            }
            catch
            {
                status = false;                              //If any exception is generated then make status to be false. This means connection is not established.
            }
            return status;
        }
        private static SqlConnection con = null;       //Create a reference variable of SqlConnection class.
        private static SqlCommand cmd = null;          //Create a reference variable of SqlCommand class.
        
        /// <summary>
        /// This method will be used to insert new record in table by using insert
        /// command of sql in ado.net. We can use SqlCommand class to pass query 
        /// and use ExecuteNonQuery() method of SqlCommand class to execute query
        /// This method will returns an integer value which return number of rows
        /// affected and if it is greater than 0 then ur query executed successfully.
        /// </summary>
        /// <param name="query"></param>
        public static void insertRecordinDatabase(string query)
        {
            if (establishConnection())
            {
                if (con != null)
                {
                    try
                    {
                        cmd = con.CreateCommand();                 //Create an object of sqlcommand class.
                        cmd.CommandText = query;                  //Pass command text to SqlCommand object.
                        cmd.CommandType = CommandType.Text;      //Tell command type to text.
                        int rows = cmd.ExecuteNonQuery();       //Return an integer value after executing query which represents number of row affected by query.
                        if (rows > 0)
                            Console.WriteLine("Number of rows affected by query  :  {0}", rows);
                        else
                            Console.WriteLine("No rows affected by your query.");
                    }
                    catch
                    {
                        Console.WriteLine("Invalid query.");
                    }
                    finally
                    {
                        if (con != null)
                            con.Close();
                    }
                }
            }
        }
        static void Main(string[] args)
        {
            string query = String.Empty;            //Initilize empty value in string variable.
            Console.WriteLine("Please eneter sqlquery for insert,delete or update record..");     //Display a message to user.
            query = Console.ReadLine();        //Read input from user.
            while (!string.IsNullOrEmpty(query))
            {
                insertRecordinDatabase(query);
                Console.ReadLine();
                Console.Clear();
                Console.WriteLine("Please eneter sqlquery for insert,delete or update record..");     //Display a message to user.
                query = Console.ReadLine();        //Read input from user.
            }
        }
    }
}