SQLCOMMAND OBJECT In ADO.NET

The SQLCOMMAND OBJECT

When we have a more complex query that retrieves records from different tables, or don't want the UPDATE command to check for changes in the database before updating it, we can specify our own SQL commands.The Command object enables  to execute queries against  data source. in order to retrieve data, you must know the schema of your database as well as how to build a valid SQL query. The Command objects allow developers to specify parameters dynamically at run time. When defining the SQL statement we use a placeholder instead of a particular value. Then we use the Parameters collection of the Command object to define the dynamic column value.The SqlCommand object must be used in conjunction with theSqlConnection object

Creating a SqlCommand Object

SqlCommand cmd = new SqlCommand("select CategoryName from Categories", new sqlconnection(parameter));

Querying Data

using a SQL select command
1)Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand("select EmployeeName from Emp", conn);
 2. Call Execute reader to get query resultsSqlDataReader dr = cmd.ExecuteReader();

Inserting Data

using a SQL insert command,To insert data into a database, use the ExecuteNonQuery method of the SqlCommand object.
1)Insert command string
 string insertString = @"
     insert into Emp
     (EmpName, Post)
     values ('Aditya', 'S\w Engg')";

 2.)Instantiate a new command with a query and connection
 SqlCommand cmd = new SqlCommand(insertString, conn);

 3) Call ExecuteNonQuery to send command
 cmd.ExecuteNonQuery();

Updating Data

using a SQL update command,To update data into a database, use the ExecuteNonQuery method of the SqlCommand object.

1)prepare command string
 string updateString = @"
     update Emp
     set EmpName = 'Ashish'
     where CategoryName = 'Raj'";
 
2) Instantiate a new command with command text only
 SqlCommand cmd = new SqlCommand(updateString,conn);
 

3) Call ExecuteNonQuery to send command
 cmd.ExecuteNonQuery();

Deleting Data

using a SQL delete command,To delete data into a database, use the ExecuteNonQuery method of the SqlCommand object.

1) delete command string
 string deleteString = @"
     delete from Emp
     where EmpName = 'Ashish'";
 
2) Instantiate a new command
 SqlCommand cmd = new SqlCommand();
 
3) Set the CommandText property
 cmd.CommandText = deleteString;
 
4) Set the Connection property
 cmd.Connection = conn;
 
5)Call ExecuteNonQuery to send command
 cmd.ExecuteNonQuery();

Getting Single Values

    For getting a single value we can use  count, sum, average, or other aggregated value from a data set.

1) Instantiate a new command
 SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);
 
2) Call ExecuteNonQuery to send command
 int count = (int)cmd.ExecuteScalar();