Friday, March 28, 2014

Sending Commands to the Database

Sending Commands to the Database

  • After connecting the database, you can send the commands to the database.
  • ADO.NET supports to send the following types of commands.
            1.  Insertion Command
            2.  Deletion Command
            3.  Updation Command
            4.  Select Command / Query Command
            5.  Stored Procedure / Function Command

1)  Insertion Command:

To insert a new row into the table.
SQL statement: insert into tablename values(value1, value2,…)

2)  Deletion Command:

To delete one or more rows from the table.
SQL statement: delete from tablename where condition

3)  Updation Command:

To update (modify) the table data.
SQL  statement:  update  tablename  set  column1=value1,  column2=value2 where condition

4)  Select Command:

To retrieve the data from the database table, into the frontend application.
SQL statement: select * from tablename

5)  Stored Procedure / Function Command:

To  call  a  stored  procedure  /  function  from  the  frontend  application,  that  is already created at backend.
SQL statement: No SQL statement is needed

Understanding the “Command” class:

  • In order to send the commands, you need to use Command class.
  • Library: SqlCommand / OleDbCommand

Command

  • CommandText
  • Connection
  • CommandType
  • Parameters
  • ExecuteNonQuery()
  • ExecuteReader()

Connection

  • ConnectionString
  • Open()
  • Close() 

a)  CommandText:  

This property contains the SQL statement (insertion statement / deletion statement / updation statement / select statement)

b)  Connection:  

This  property  contains  the  reference  of  the  connection  object, based on which, the above given SQL statement is to be executed.

c)  CommandType: 

This property specifies the type of the command that you want to execute. It is of two types:
  • Text: (default) This is used for any SQL statement (insertion statement / deletion statement / updation statement / select statement)
  • StoredProcedure: This is used for Stored Procedure / Function only.

d)  Parameters:  

This  property  contains  the  list  of  parameters  (argument  values), that  are  to  be  sent  to  backend,  while  you  are  calling  a  stored  procedure  / function. It is of ―collection‖ type, so that you can add any no. of parameters.

e)  ExecuteNonQuery():  

This  method  is  used  to  execute  any  SQL  statement (insertion  statement  /  deletion  statement  /  updation  statement  /  select statement)  or  a  stored  procedure  /  function  also.  In  other  words,  this  method moves the execution flow to backend database, execute the command there and then  come  back  with  some  result.  This  method  returns  no.  of  rows  affected‖, which  represents  the  count  of  the  rows,  which  are  affected  by  executing  this command. Suppose, after executing a delete statement, 2 rows are deleted. So it returns the integer value "2"

f)  ExecuteReader():  

This  method  is  used  to  execute  ―select‖  statement  only.  In other  words,  it  is  used  to  retrieve  some  data  from  the  database,  based  on  the given select statement. In other words, this method moves the execution flow to backend database, execute the command there and then come back with some 
result  data.  This  method  returns  the  table  data,  based  on  the  given  select statement.

Implementation Code for SQL Server:

  • Import the Library (at the top):
            using System.Data.SqlClient;
  • Construct the “Connection” class object:
          SqlConnection cn = new SqlConnection();
  • Assign the Connection string:
        cn.ConnectionString  =  ―data  source=<name  of  the  server>;user  id=<user name>;password=<password>;initial catalog=<database name>‖;
  • Open the connection:
             cn.Open();
  • Construct the “Command” class object:
            SqlCommand cmd = new SqlCommand();
  • Assign the SQL statement, which is to be executed:
          cmd.CommandText = ―insert statement / delete statement / update statement‖;
  • Assign the reference of connection object, based which the  command is to be executed:
         cmd.Connection = cn;
  • Execute the command and receive the no. of rows affected:
          int n = cmd.ExecuteNonQuery();
  • Close the connection:
           cn.Close();

Implementation Code for Oracle (or any other database):

  • Import the Library (at the top):
             using System.Data.OleDb;
  • Construct the “Connection” class object:
          OleDbConnection cn = new OleDbConnection();
  • Assign the Connection string:
          cn.ConnectionString  =  ―provider=<provider  name>;data  source=<name  of  the server>;user id=           <user name>;password=<password>‖;
  • Open the connection:
           cn.Open();
  • Construct the “Command” class object:
          OleDbCommand cmd = new OleDbCommand();
  • Assign the SQL statement, which is to be executed:
         cmd.CommandText = ―insert statement / delete statement / update statement‖;
  • Assign the reference of connection object, based which the command is to be executed:
           cmd.Connection = cn;
  •  Execute the command and receive the no. of rows affected:
           int n = cmd.ExecuteNonQuery();
  • Close the connection:
          cn.Close(); 

No comments:

Post a Comment