How can I get information back from my server

[Suzi167]

Hello Everyone,

I am facing an issue which is probably very common for people that have worked with ASP.net for a while but since I am new it is challenging to me:

So here it goes:

I have a web page and I need to generate a report based on a SQL script which is the following:


DECLARE @count int --this will hold up our row count

SELECT * FROM Employees

SELECT @count = @@ROWCOUNT


I create the query string and send it to the SQL serevr - NO problem with that.

The issue is :

How can I pass the value SQL returned in @count back to my application.
If it was a simple select statement then the returned vaule is stored in Dataset but when the value is something else how do I proceed?

Thanks very much in advance!

Suzi

[superuser]

Here is a method I created and reuse for executing a stored procedure and retrieving the returned data. The listbox is just for demo :)



/// <summary>
/// Retrieve data from a stored procedure
/// CodeType:C#
/// </summary>
private void spdata()
{
SqlConnection objConn = new SqlConnection();
objConn.ConnectionString = @"Data Source=server;Initial Catalog=database;Persist Security Info=True;User ID=userid;Password=password";
SqlCommand objcmd = new SqlCommand();
objcmd.CommandType = CommandType.StoredProcedure;
objcmd.CommandText = "getrows";//stored procedure name
objcmd.Connection = objConn;
objConn.Open();
System.Data.SqlClient.SqlDataReader objDatareader = objcmd.ExecuteReader();

while (objDatareader.Read())
{
this.listBox1.Items.Add(objDatareader.GetValue(0).ToString());//load listbox example
}

objConn.Close();
objDatareader.Close();
objcmd.Dispose();
objConn.Dispose();
objDatareader.Dispose();
}



EDIT*

As good practise you should set the sql statment up on the server as a stored procedure and call it with the above code.



create procedure
getrows
as
declare @count int
select * from t_prime
select @count = @@rowcount

[Suzi167]

Thank you so much for the detailed response.

I will try it out and let you know how it went.

Suzi

[Suzi167]

OK..I read through your code and now it is very clear.

I have another question.
How about if the stored procedure takes some input parameters.
How can I pass those input parameters form my C# code to the stored procedure.
For Example:


CREATE PROC spInsertOrder

@CustomerID varchar(5),
@EmployeeID int,
@OrderDate datetime = NULL,
@RequiredDate datetime = NULL


AS


....... //the rest of the procedure continues here



The parameters are enetered by the user in text boxes for example.

So in my C# code I have for ex:



string ID = "";
ID = txtCustomer_id.text;


How do I get that string to the stored procedure???

I was trying to figure it out and I came across the SQLParameter class.
Is that what I need to use?

Thanks!

Suzi

[hedge_fund]

I will modify superuser's code to show you how to use parameters.


/// <summary>
/// Retrieve data from a stored procedure
/// CodeType:C#
/// </summary>
private void spdata()
{
SqlConnection objConn = new SqlConnection();
objConn.ConnectionString = @"Data Source=server;Initial Catalog=database;Persist Security Info=True;User ID=userid;Password=password";
SqlCommand objcmd = new SqlCommand();
objcmd.CommandType = CommandType.StoredProcedure;
objcmd.CommandText = "getrows";//stored procedure name

//parameter addition
objcmd.Parameters.Add("@CustomerID", SqlDbType.VarChar, 5).Value = 'Whatever you want it to be';
objcmd.Connection = objConn;
objConn.Open();
System.Data.SqlClient.SqlDataReader objDatareader = objcmd.ExecuteReader();

while (objDatareader.Read())
{
this.listBox1.Items.Add(objDatareader.GetValue(0).ToString());//load listbox example
}

objConn.Close();
objDatareader.Close();
objcmd.Dispose();
objConn.Dispose();
objDatareader.Dispose();
}



Also Suzi,
regarding your stored procedure, why don't you simply change the 2 select calls into one:


DECLARE @count int --this will hold up our row count
SELECT @count = COUNT(*) FROM Employees

[Suzi167]

Thanks for the reply,

I am at work right now but I will try it on lunch break.
Also thank yo very much for the suggestion to shorten the Stored procedure.
I agree with you that in this case it is easier to use the stored procedure in the way you suggested since in that case COUNT and @@ROWCOUNT do the same thing.

I just wanted to exercise the use of the @@ROWCOUNT system function.

I will let you know how it went so future users can use that code as well.

Thanks Again!

Suzi