Using ADO.NET there are four options for returning information from an SQL query:
- Use a DataSet object to gather the returned rows and to work with these rows in addition to the return values and the return parameters.
- Use a DataReader object to gather the returned rows, to move through these rows, and to gather return values and return parameters.
- Use the ExecuteScalar method to return the value from the first column of the results' first row with the return values and the return parameters. This is most useful with aggregate functions.
- Use the ExecuteNonQuery method to return only the return parameters and the return values. Any returned rows are discarded. This is most useful for executing action queries.
These are all methods called from your command object.
There are lots of different ways to skin a cat, you can use output parameters, you can use ExecuteScalar, you can use return values or you can use dummy recordsets.
You should be able to something like the following in your C# to get the return value from a query
// add a new parameter, with any name we want - its for our own use only
SqlParameter sqlParam = com.Parameters.Add("@ReturnValue", SqlDbType.Int);
// set the direction flag so that it will be filled with the return value
myParm.Direction = ParameterDirection.ReturnValue;
The code above the captures the return value that you can set as you need, perhaps with a 0 for exists and a 1 for not exists.
If NOT Exists(SELECT * FROM ItemList WHERE ItemName='txtItemNama')
BEGIN
INSERT INTO ItemList (ItemName) VALUES('txtItemNamea')
Return 0;
END
ELSE
BEGIN
Return 1;
END
There is a bit caveat with the above advice however - I usually either use an ORM like Linq-to-SQL or NHibernate, or I use Stored Procedures. I find inline SQL quite cumbersome. So, while my answer should be sound in general, you will probably need to work through some details to get it working exactly as you need.