Working with command object:

• Command object is used to perform various operations on database objects tables, views, stored procedures etc;
• We can perform the operations like inserting the records, deleting the records, updating the records etc; and also creating, dropping, altering tables views, stored procedures
• Command object with connection oriented architecture

Properties with command object

  1.     Command Text
  2.     Command type
  3.     Connection 
  4.     Command Timeout
  5.     Parameters

1 Command Text:

• This property is used to mention the required command text value
• Command text can be anyone from sql query (or) stored procedure name (or) table name

2 Command Type:

• Used to set or get the required command text value
• Command type value can be any one from text (or) stored procedure (or) table direct
• All these command type values are stored within the enumeration named as command type like

CommandType.Text (default)
CommandType.StoredProcedure
CommandType.TableDirect

• We will take command type as text if command text is sql query, stored procedure if command text is stored procedure name and table direct if command text required

3 Connection:

• Used to set (or) get the required connection object name using which we would like to execute the command object

4 Command Timeout:

• Used to set (or) get the required time period in seconds that frontend should wait to get response from backend after sending the command object request to backend
• The default command timeout period is 30sec within this time period if frontend doesn’t get any response from the backend then command timeout error will be raised

5 Parameters:

• This is the collection property used to send required parameters & these values that are required to be send to the stored procedures

        Steps to work with command object(Non-Parameterized Query )

1.     Declare The Command Object

          Sy: Classname Object Name;

          Ex: Sqlcommand Cmd;

2.     Define Command Object

         Sy: Object Name=New Classname(“Command Text”, Connection Object)

         Ex:Cmd=New Sqlcommand(“Delete Empdetails Where Empid=14”,Con);

3.     Mention Command Type

         Sy: Object Name Command Type=Command Type.Value;

         Ex: Cmd.Commandtype=Commandtype.Text;

4.     Open the Connection

          Sy:-Connection Object Name.Open( );

          Ex:-Con.Open( );

5.     Execute The Commend Object

        Sy: Object Name.Execution Method( );

        Ex: Cmd.ExecuteNonQuery( );

Execution methods in command object:

ExecuteNonQuery( )	ExecuteReader( )	ExecuteScalar( )
Will work with action queries only	Will work with Non Action queries only	Will work with Non Action queries that contains aggregate functions
Returns the count of Rows affected by the query	Returns the rows selected by the query	Returns the first row & first column value of the query result
Return type is int	Return type is Data Reader	Return type is object
Return value is optional & can be assigned to integer variable	Return value is compulsory & should be assigned to another object ie Data Reader	Return value is compulsory & should be assigned to the variable of required type

Advantages using Non-Parameterized Query:-

[1] Query is flexible i.e.,Based on Database Query is not require to be changed.
 

Disadvantages In Non-Parameterized Query Method:-

[1] Once query is run query execution plans are deleted when result is sent to Database. So query execution will be always slow. 

[2] possibility of SqlInjection attacks.

To avoid these disadvantages we prefer Parametrized Queries.