Database Reference
In-Depth Information
15.
If you will click the Load Currency List again, you will see that the currency
code US is not listed, because it has been deleted.
How It Works
In this program, you use a stored procedure to delete the currency from the Sales.Currency table. As you
can see in the Design view of the CommandStoredProcedure form, you have a ListBox and two Button
controls. The value selected in the ListBox will be passed to a stored procedure, which has a delete
query. So, the preparation for passing the value to a stored procedure takes place through these
statements:
// Create command object with Stored Procedure name
SqlCommand cmd = new SqlCommand("sp_DeleteCurrency", conn);
//Set command object for Stored Procedure execution
cmd.CommandType = CommandType.StoredProcedure;
Once you have specified that the command object is going to use a stored procedure, it's time to
prepare the parameter(s) required by the stored procedure for execution.
cmd.Parameters.Add(new SqlParameter("currCode", SqlDbType.NVarChar, 3));
As you can see, we want the ListBox's selection to be deleted, so we are passing the data to the
stored procedure parameter as
ListBox.SelectedItem
.
cmd.Parameters["currCode"].Value = lstCurrency.SelectedItem.ToString();
Once the parameter is ready, we open the connection and execute the stored procedure by using
ExecuteNonQuery
.
// Open connection
conn.Open();
// Execute command associated with StoredProcedure
cmd.ExecuteNonQuery();
Summary
In this chapter, I covered what an ADO.NET command is and how to create a
Command
object. I also
discussed associating a command with a connection, setting command text, and using
ExecuteScalar()
,
ExecuteReader()
, and
ExecuteNonOuery()
statements. You also learned how to use stored procedures
with C# code to perform DML operations. In the next chapter, you'll look at data readers.
