ADO.NET

When we have a more complex query that retrieves records from different tables, or don't wado.net 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 the SqlConnection object

Creating a SqlCommand Object

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

Querying Data

using a SQL select command

1. Instado.netiate a new command with a query and connection

SqlCommand cmd = new SqlCommand("select EmployeeName 
                                    from Emp",conn);

2. Call Execute reader to get query results

SqlDataReader 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. Instado.netiate 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. Instado.netiate 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. Instado.netiate 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. Instado.netiate a new command

SqlCommand cmd = new SqlCommand("select count(*)
                        from Categories", conn);

2. Call ExecuteNonQuery to send command

 int count = (int)cmd.ExecuteScalar();