When working with the SqlCommand you will no doubt have to supply parameters to your command whether it be a Stored Procedure or Text command. Although not as efficient as an actual Stored Procedure there are some powerful things you can achieve using the System.Data.ParameterDirection, or rather other than just Input.
The enumeration holds the following values:
- Input
- InputOutput
- Output
- ReturnValue
In this article I will give a working example of each, simple but you will see the idea. At the end I will make a custom object which I will populate using some of these directions.
For this example I am working with the AdventureWorks Database for Sql Server 2005.
Input
The most common direction.
using (System.Data.SqlClient.SqlConnection sc1 =
new System.Data.SqlClient.SqlConnection(@"Data Source=REA_ANDREW-PC\SQLEXPRESS;Initial Catalog=AdventureWorks;"+
"Integrated Security=True"))
{
sc1.Open();
using (System.Data.SqlClient.SqlCommand command1 = new System.Data.SqlClient.SqlCommand())
{
command1.CommandType = CommandType.Text;
command1.Connection = sc1;
// DIRECTION :: Input
command1.CommandText = "INSERT INTO [Purchasing].[ShipMethod] (Name,ShipBase,ShipRate)"+
"VALUES (@Name,@ShipBase,@ShipRate)";
//Method 1
command1.Parameters.AddWithValue("@Name", "MyShippingMethod");
//Method 2
System.Data.SqlClient.SqlParameter parameter2 =
new System.Data.SqlClient.SqlParameter("@ShipBase", 10.00M);
parameter2.Direction = ParameterDirection.Input;
command1.Parameters.Add(parameter2);
//Method 3 //Deprecated for AddWithValue
command1.Parameters.Add("@ShipRate", 10.00M);
command1.ExecuteNonQuery();
}
}
Here I have displayed three different syntactical ways of adding parameters to your SqlCommand. The third and final way is deprectaed and you will see this if you try inside Visual Studio. Because of this it will not appear inside intellisense.
InputOutput
For this example I will supply the parameter with a value and during the command I will modify its value and return the new value inside the parameter. For the purposes of this example I will give it an initial value of 1 and when it has completed its execution its value should be 1 + the number of records inside the [Purchasing].[ShipMethod] table i.e. 7.
using (System.Data.SqlClient.SqlConnection sc1 =
new System.Data.SqlClient.SqlConnection(@"Data Source=REA_ANDREW-PC\SQLEXPRESS;Initial Catalog=AdventureWorks;"+
"Integrated Security=True"))
{
sc1.Open();
using (System.Data.SqlClient.SqlCommand command1 = new System.Data.SqlClient.SqlCommand())
{
command1.CommandType = CommandType.Text;
command1.Connection = sc1;
// DIRECTION :: Input
command1.CommandText = "select @MyParameter = @MyParameter + Count(*) FROM [Purchasing].[ShipMethod]";
System.Data.SqlClient.SqlParameter paramter1 = command1.Parameters.Add("@MyParameter", SqlDbType.SmallInt);
paramter1.Value = 1;
paramter1.Direction = ParameterDirection.InputOutput;
command1.ExecuteNonQuery();
//The following value is now 7 after intially being 1
int newValue = (int)paramter1.Value;
}
}
Output
The output is going to look like the above example only I will not give it an intial value and simply assign the value inside the command text.
using (System.Data.SqlClient.SqlConnection sc1 =
new System.Data.SqlClient.SqlConnection(@"Data Source=REA_ANDREW-PC\SQLEXPRESS;Initial Catalog=AdventureWorks;"+
"Integrated Security=True"))
{
sc1.Open();
using (System.Data.SqlClient.SqlCommand command1 = new System.Data.SqlClient.SqlCommand())
{
command1.CommandType = CommandType.Text;
command1.Connection = sc1;
// DIRECTION :: Input
command1.CommandText = "select @MyParameter = Count(*) FROM [Purchasing].[ShipMethod]";
System.Data.SqlClient.SqlParameter paramter1 = command1.Parameters.Add("@MyParameter", SqlDbType.SmallInt);
paramter1.Direction = ParameterDirection.Output;
command1.ExecuteNonQuery();
//The following value is now 6, the number of records inside the table
int newValue = (int)paramter1.Value;
}
}
Return Value
The return value is something which you will return at the end of the statement/s. For this example I need to create a short stored procedure, again i will simply return the count of records from the table:
ALTER PROCEDURE CountRows
AS
Declare @CountR int
SELECT @CountR = Count(*) FROM [Purchasing].[ShipMethod]
RETURN @CountR
And so the function which will now work with this, and using the ReturnValue direction is as follows:
using (System.Data.SqlClient.SqlConnection sc1 =
new System.Data.SqlClient.SqlConnection(@"Data Source=REA_ANDREW-PC\SQLEXPRESS;Initial Catalog=AdventureWorks;"+
"Integrated Security=True"))
{
sc1.Open();
using (System.Data.SqlClient.SqlCommand command1 = new System.Data.SqlClient.SqlCommand())
{
command1.CommandType = CommandType.StoredProcedure;
command1.Connection = sc1;
// DIRECTION :: Input
command1.CommandText = "CountRows";
System.Data.SqlClient.SqlParameter paramter1 = command1.Parameters.Add("@CountR", SqlDbType.SmallInt);
paramter1.Direction = ParameterDirection.ReturnValue;
command1.ExecuteNonQuery();
//The following value is now 6, the number of records inside the table
int newValue = (int)paramter1.Value;
}
}
The simple example object
I now want to use these counting methods to construct a sample object which will contain simply.
- Total Record Count
- List<string> of Shipping Names which can be bound to
This is just for an example, I realise lol that I could simply use the List<string> Count property BUT think of it like this. What if you wanted to create a paged object so the list actual only contains say ten items because of a page size you set BUT you still have knowledge of the total number of records through the Total Record Count property, from which you could calculate the total number of pages.!! :-)
The object
namespace WindowsForm_Examples_NET_2
{ class SimpleObjectOne
{
private int _recordCount;
private List<string> _shippingNames;
public List<string> ShippingNames
{
get { return _shippingNames; }
set { _shippingNames = value; }
}
public int RecordCount
{
get { return _recordCount; }
set { _recordCount = value; }
}
public SimpleObjectOne()
{
_shippingNames = new List<string>();
}
[System.ComponentModel.DataObjectMethod(
System.ComponentModel.DataObjectMethodType.Select)]
public static SimpleObjectOne GetSimpleObjectOne()
{
SimpleObjectOne objectOne = new SimpleObjectOne();
objectOne.Execute();
return objectOne;
}
private void Execute()
{
using (System.Data.SqlClient.SqlConnection sc1 =
new System.Data.SqlClient.SqlConnection(@"Data Source=REA_ANDREW-PC\SQLEXPRESS;Initial Catalog=AdventureWorks;" +
"Integrated Security=True"))
{
sc1.Open();
using (System.Data.SqlClient.SqlCommand command1 = new System.Data.SqlClient.SqlCommand())
{
command1.CommandType = System.Data.CommandType.Text;
command1.Connection = sc1;
// DIRECTION :: Input
command1.CommandText = "SET @CountRows = (SELECT Count(*) as COUNTR FROM [Purchasing].[ShipMethod]);" +
"SELECT Name FROM [Purchasing].[ShipMethod];";
System.Data.SqlClient.SqlParameter parameter1 =
command1.Parameters.Add("@CountRows", System.Data.SqlDbType.Int);
parameter1.Direction = System.Data.ParameterDirection.Output;
using (System.Data.SqlClient.SqlDataReader reader = command1.ExecuteReader())
{
while (reader.Read())
{
_shippingNames.Add(reader["Name"] as string);
}
}
RecordCount = (int)parameter1.Value;
//The following value is now 6, the number of records inside the table
}
}
}
}
}
An important note to make here, is if you are using the ExecuteReader on a statement and pass in Output parameters, you must close the data reader before you can access these output parameter values. In the above you will see that I encase the data reader in a using statement block.
And finally to consume this example I use the following three lines:
SimpleObjectOne newObject = SimpleObjectOne.GetSimpleObjectOne();
int RecordCount = newObject.RecordCount;
List<string> names = newObject.ShippingNames;
I now have 6 as the record count and a list of shipping names.
Cheers Andrew