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.
}
}
}
}