Home > DeveloperSection > Blogs > Executing insert, delete or update query in SqlServer using ADO.NET

Executing insert, delete or update query in SqlServer using ADO.NET


ADO.Net ADO.Net 
Ratings:
1 Comment(s)
 21181  View(s)
Rate this:

Executing insert, delete and update command in Sql Server by using ADO.NET

Hi.

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.

            }

        }

    }

}


Nice blog

By Jenry Hock on   5 years ago

Hi awadhendra,

Nice blog ...


Don't want to miss updates? Please click the below button!

Follow MindStick