Dealing With Database Using SqlCommand Methods

In this blog, you wil learn how to deal with Database using SqlCommand Methods.

If we want to deal with database two things will happen 

1. Modification.
2. Retrieving.

Modification

Under modification section we have insert, update and delete queries. We will use ExecuteNonQuery method of SqlCommand Class because we are not querying anything in the database we are doing modification of data in database.

ExecuteNonQuer()

This method does not return any records. It will return only the number of rows affected in the database.

In the following example, it will update all the records in the employee table active column value to 1.

int i= objsqlCommand.ExecuteNonQuery();

objsqlCommand.ExecuteNonQuery() will return the number of rows affected in the employee table and store it into 'i' variable. All the time ExecuteNonQuery returns integer value.

Ex

using(SqlConnection connection = new SqlConnection(dbsql)) {
    connection.Open();
    SqlCommand objsqlCommand = new SqlCommand("update set active=@active where employee", connection);
    objsqlCommand.Parameters.Add(new SqlParameter("@active", 1));
    int i = objsqlCommand.ExecuteNonQuery();
    connection.Close();
}

Retrieving

Under Retrieving section we are querying the database by using select statement.

For this we will use ExecuteReader() or ExecuteScalar() methods.

ExecuteReader()

If the select query returns more than one record we need to use ExecuteReader() method.

In the following example, it will return all the records from the employee table.

Ex

List < Employee > employee = new List < Employee > ();
using(SqlConnection connection = new SqlConnection(dbsql)) {
    connection.Open();
    SqlCommand objsqlCommand = new SqlCommand("select id,name from employee", connection);
    SqlDataReader _sqlDataReader = sqlCommand.ExecuteReader();
    if (_sqlDataReader.HasRows) {
        while (_sqlDataReader.Read()) {
            employee.add(new Employee() {
                id = _sqlDataReader["id"].ToString(),
                    name = _sqlDataReader["name"].ToString()
            });
        }
    }
    connection.Close();
}

ExecuteScalar()

If the select query returns only one record we need to use ExecuteScalar() method.

In the following example, it will return max employee id from the employee table.

Ex

string employeid = string.empty;
using(SqlConnection connection = new SqlConnection(dbsql)) {
    connection.Open();
    SqlCommand objsqlCommand = new SqlCommand("select max(id) from employee", connection);
    employeid = objsqlCommand.ExecuteScalar().ToString();
    connection.Close();
}

We have shown how sqlcommand methods deal with database queries.