c#

johnnyc

i am wondering i have a stored procedure invoke from a method. This stored procedure simple returns a number of enteries of customers. I want to store the number into an array 1.how do you return the number from the stored procedure. And how would i invoke the method with the number.. I f it is clear give me a message i am new at this stuff sorry

bonkey

Depending on your database, you either need to use the OleDbCommand or SqlCommand objects, I'm guessing. Maybe the OdbcCommand object.

To determine which one, read up what the differences are - they're basically the same object model optimised for different database types.

Once you decide which one you need, read up on how to use parameters in a command, how to use ExecuteReader() method to get a DataReader, and then check up on the Read() method of that.

That should give you everything you need. If not, then I'd suggest you need to show where you've gotten and be more specific about the probem.

jc

johnnyc

i am using the sql server database. But my problem is i am invoking a method that call a stored procedure this procedure counts the no of members. I want to return the number of members and store the members in an array!!. This is my method i am wondering is this right i know it is'nt but if anybody has an solution or direction i should please send an reply

public int getAllMemberDetails(int[] number)
{
int number1=0;
// Create a connection to the database
SqlConnection cnMembers1 = new SqlConnection
("server=localhost;integrated security=true;" + "database=GolfSystemDB");

//use the stored procedure created in sql usp_GetNoMember
SqlCommand cmdMember = new SqlCommand("usp_GetNoMember", cnMembers1);
cmdMember.CommandType = CommandType.StoredProcedure;
// Executing stored procedure call
cnMembers1.Open();


SqlDataReader drMembers = cmdMember.ExecuteReader();
drMembers.Read();

number1=Convert.ToInt32(drMembers.GetValue(0));

drMembers.Close();
cnMembers1.Close();


//return name;
return number[number1];

}

bonkey

Well, what you have at the moment is returning the number of members, yes?

Or, get it to return the results instead.

(I.E. instead of "select count(results) from ..." use "select results from..."

Then, write a loop something like this :

int myCounter = 0;
While (dr.Read()) {
  // read the result from the current row, and add to an array/list/whatever
myCounter++;
}

Yeah? Now, you have myCounter, which contains the number of rows returned, and you've taken a value from each row and added it to an array, yes?

Is this what you want?

johnnyc

thanks very much greatly appreciated!!

Kernel32

A couple of other options is to add an OUT parameter on the stored procedure and return the count in that parameter. Then use the ExecuteNonQuery method of SqlCommand to execute the query. Another option is to use ExecuteScaler method of the SqlCommand. It returns the first column of the first row of the first resultset and is designed for exactly this problem, you can find it well documented in the help file.

bonkey

Originally posted by Kernel32
A couple of other options is to add an OUT parameter on the stored procedure and return the count in that parameter. Then use the ExecuteNonQuery method of SqlCommand to execute the query.

Another option is to use ExecuteScaler method of the SqlCommand. It returns the first column of the first row of the first resultset and is designed for exactly this problem, you can find it well documented in the help file.

Neither of these will return you the information to populate the array with.

You could use an out parameter alongside the SELECT option that I suggested, but to be honest, its only creating more work seeing as you have to loop through the resultset anyway to build the array of output vals.

jc

p.s. I remember in RDO you could assign a single-column resultsset directly to an array, but I don't think ADO has an equivalent functionality.

Kernel32

Maybe I read the question wrong but I didn't see anything in it about returning the list of customers, just the number of customers. The array is supposed to have one row and one col containing the count I thought?