In this article, Michael Youssef shows us how to use the Command Object to execute SQL commands on the database server. He details commands we need to perform on the database like INSERT, UPDATE, DELETE and SELECT, represented in ADO.NET as Command Object. You should already know how to create a connection with the database, or you can refer to "A Closer Look at ADO.NET: The Connection Object" to learn how.
As with the connection object, there are provider-specific classes for the command object. For example, the SQL Server Provider has the class SqlCommand and the OLEDB .NET Data Provider has the class OleDbCommand. The Command Classes implement the IDbCommand Interface, which has methods like ExecuteReader(), ExecuteNonQuery(), ExecuteScalar(), CreateParameter(), Cancel() and Prepare(). The interface also has the following properties: Connection, CommandTimeout, CommandType, CommandText, Parameters and Transaction. We will be discussing all these properties and methods, so don't worry about that.
You can create a command object in many ways. You can create a connection object, then use the method CreateCommand() of the connection object to create a Command Object. Or you can create a connection object, then create a command object using the new operator (the standard way). After that, assign the connection object reference to the Connection property of the command object.
The command object has a property called CommandText, which contains a string value that represents the command that will be executed in the database server. So to use the command object, you must associate it with a connection object (which must be open before you use the command Object). Then assign the command string value to the CommandText property and use of the Execute methods.
The CommandType property takes a value of the System.Data.CommandType Enumeration (CommandType.Text, CommandType.TableDirect or CommandType.StoredProcedure). ADO.NET recognizes three types of commands. The first type is text commands, which are those text commands that are sent to the database server directly and written in specific SQL Dialect (for SQL Server, MSDE and Access it's T-SQL). The commandType.Text is the default for the property CommandType of the Command object.
The second type is the CommandType.StoredProcedure which is set to call a specific stored procedure in the database; of course when you set the Command.CommandType property to CommandType.StoredProcedure, the value of the Command.CommandText must be the name of the stored procedure that you are calling. Finally, the type CommandType.TableDirect is used to return a complete table from the database, and it's used only by the OLEDB .NET Data Provider. I think that it's time to create a command object and execute a T-SQL Command in the database.
In the following code example I have established a connection with the database pubs (located in the server MichaelServer) and I have used the method ExecuteScalar() of the SqlComm1 object to execute a command against the titles table, so let's take a look at the code:
using System; // reference to the namespace that contains most // of the classes that form the ADO.NET Architecture using System.Data; // referene to the namespace of the SQL Server .NET Data provider using System.Data.SqlClient;
namespace AdoApp { class Class1 { static void Main(string[] args) { SqlConnection SqlConn1 = new SqlConnection(); SqlConn1.ConnectionString = "Server=(local);Database=pubs;User ID=sa;Password=;"; // creating and initializing the SqlCommand Object SqlCommand SqlComm1 = new SqlCommand(); SqlComm1.Connection = SqlConn1; SqlComm1.CommandType = CommandType.Text; SqlComm1.CommandText = "SELECT COUNT(*) FROM titles"; try { SqlConn1.Open(); Console.WriteLine("There are " + SqlComm1.ExecuteScalar().ToString() + " records in titles"); } catch(Exception ex) { Console.WriteLine(ex.Message); } finally { // here we call the Close() method to close the Connection SqlConn1.Close(); Console.ReadLine(); } } } }
Here's the result of running this code example:
Let's walk through the code line by line. The first statement creates a SqlConnection instance called SqlConn1, then the following statement assigns a connection string to the property ConnectionString of the object SqlConn1. The third statement in the Main() method creates a SqlCommand instance called SqlComm1, then the next statement assigns the SqlConn1 object reference to the SqlComm1 object through the Connection property of the SqlComm1 object.
Because I wanted to execute a T-SQL statement against the titles table, I have assigned the enumeration value CommandType.Text to the property SqlComm1.CommandType. I have also assigned the T-SQL Statement as a string value to the SqlComm1.CommandText. In the Try block we open the connection to the database using the SqlConn1.Open(), then we use the SqlComm1.ExecuteScalar() method to print out the result of executing the text (the T-SQL Statement) on the server.
Actually, the ExecuteScalar() method returns a scalar value, or we can say that it returns the first value of the first column in the first record of the returned result set. So we use the ExecuteScalar() method when our T-SQL Statement returns only one value, a statement like SELECT COUNT(*) FROM titles. The ExecuteScalar() method returns the value as object so we have two solutions. The first is to use the ToString() method of the ExecuteScalar() return value, which is the object to return the value as a string. The second solution is to cast the return value to a specific data type and store it in a variable. I will talk more about that later when we discuss ADO.NET Data Types. There's nothing special about the rest of the code, so let's use the method ExecuteNonQuery().
In the following code example I use the ExecuteNonQuery() method to insert a new record into the jobs table. Let's take a look at the code.
using System; // reference to the namespace that contains most // of the classes that form the ADO.NET Architecture using System.Data; // referene to the namespace of the SQL Server .NET Data provider using System.Data.SqlClient;
namespace AdoApp { class Class1 { static void Main(string[] args) { SqlConnection SqlConn1 = new SqlConnection(); SqlConn1.ConnectionString = "Server=(local);Database=pubs;User ID=sa;Password=;"; // creating and initializing the SqlCommand Object SqlCommand SqlComm1 = new SqlCommand(); SqlComm1.Connection = SqlConn1; SqlComm1.CommandType = CommandType.Text; SqlComm1.CommandText = "INSERT INTO jobs(job_desc,min_lvl,max_lvl)" + "VALUES('Technical Writer',25,100)"; try { SqlConn1.Open(); Console.WriteLine("Rows Effected: " + SqlComm1.ExecuteNonQuery().ToString()); } catch(Exception ex) { Console.WriteLine(ex.Message); } finally { // here we call the Close() method to close the Connection SqlConn1.Close(); Console.ReadLine(); } } } }
Here's what you get when you run this code:
This time what we have changed the text of the command through the SqlComm1.CommandText property assignment, and we have used the method ExecuteNonQuery() to insert a new record into the jobs table. The ExecuteNonQuery() method, as its name implies, executes non query SQL Statements. This means that you can use it to execute INSERT, UPDATE, DELETE and CREATE statements, and the method returns the number of rows that have been affected by executing the text of the command. So in your example the CommandText property has the T-SQL Statement INSERT INTO jobs(job_desc,min_lvl,max_lvl) VALUES('Technical Writer',25,100), which inserts a record into the Jobs table. Let's delete our new entry.
In the following code example I use the ExecuteNonQuery() method to delete the newly entered record and then count how many records are in the table. Note that before we entered the above record, the table had only 14 records; after we delete the record, the table once again holds 14 records.
using System; // reference to the namespace that contains most // of the classes that form the ADO.NET Architecture using System.Data; // referene to the namespace of the SQL Server .NET Data provider using System.Data.SqlClient;
namespace AdoApp { class Class1 { static void Main(string[] args) { SqlConnection SqlConn1 = new SqlConnection(); SqlConn1.ConnectionString = "Server=(local);Database=pubs;User ID=sa;Password=;"; // creating and initializing the SqlCommand Object SqlCommand SqlComm1 = new SqlCommand(); SqlComm1.Connection = SqlConn1; SqlComm1.CommandType = CommandType.Text; SqlComm1.CommandText = "DELETE FROM jobs WHERE job_desc = 'Technical Writer'"; try { SqlConn1.Open(); Console.WriteLine("Rows Effected: " + SqlComm1.ExecuteNonQuery()); SqlComm1.CommandText = "SELECT COUNT(*) FROM Jobs"; Console.WriteLine("The jobs table contains " + SqlComm1.ExecuteScalar().ToString() + " Rows"); } catch(Exception ex) { Console.WriteLine(ex.Message); } finally { // here we call the Close() method to close the Connection SqlConn1.Close(); Console.ReadLine(); } } } }
This time we have assigned the property CommandText the string value (the T-SQL Statement) "DELETE FROM jobs WHERE job_desc = 'Technical Writer'" which deletes the record we just inserted into the table. After we called the ExecuteNonQuery() method, we assigned a new text to the CommandText property of the SqlComm1 object, which again counts how many records in the table. Now, let's learn how to use a stored procedure.
We will create a stored procedure in the pubs database to count the records of the jobs table (using the statement SELECT COUNT(*) FROM JOBS) and see how we can use it. To create a stored procedure, open SQL Server Enterprise Manager and navigate to the pubs database, then expand it and right click on Stored procedures. Choose New Stored procedure.
Our stored procedure is very simple, we just count how many records are in the table. In the next article we will talk about how to create a parameterized stored procedure and how to use the SqlParameter object. Click on OK after you type the stored procedure as in the above figure. Now Let's take a look at the code that calls this stored procedure:
using System; // reference to the namespace that contains most // of the classes that form the ADO.NET Architecture using System.Data; // referene to the namespace of the SQL Server .NET Data provider using System.Data.SqlClient;
namespace AdoApp { class Class1 { static void Main(string[] args) { SqlConnection SqlConn1 = new SqlConnection(); SqlConn1.ConnectionString = "Server=(local);Database=pubs;User ID=sa;Password=;"; // creating and initializing the SqlCommand Object SqlCommand SqlComm1 = new SqlCommand(); SqlComm1.Connection = SqlConn1; SqlComm1.CommandType = CommandType.StoredProcedure; SqlComm1.CommandText = "CountRecords"; try { SqlConn1.Open(); Console.WriteLine("Calling the database stored procedure returns :" + SqlComm1.ExecuteScalar()); } catch(Exception ex) { Console.WriteLine(ex.Message); } finally { // here we call the Close() method to close the Connection SqlConn1.Close(); Console.ReadLine(); } } } }
The only pieces of code that we have changed are the CommandType and CommandText properties of the SqlComm1 object. To execute a stored procedure you need to tell the command object about it through the assignment of the enumeration value CommandType.StoredProcedure to the CommandType property, and the assignment of the stored procedure name to the property CommandText. Because the stored procedure returns a scalar value, we must use the ExecuteScalar() method in order to get the value. If we used the ExecuteNonQuery() method, we will not get an error but we will get -1 because no records in the jobs table have been affected by executing the stored procedure. In the next article we are going to talk about SQL Server Stored Procedure and Parameterized stored procedure calls.
blog comments powered by Disqus
Database Code 
/ 60 
A Closer Look at ADO.NET: The Connection Object
