Click here to Skip to main content
15,867,594 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
//I have the below stored procedure that has a cursor to retrieve the employee details for each country in the country table

SQL
CREATE PROCEDURE EMPLOYEEdETAILS
AS 
  declare @COUNTRYID INT
  
  declare test cursor
  for
    select REC_ID FROM COUNTRIES
    
  OPEN TEST
  FETCH NEXT FROM TEST INTO @COUNTRYID
  WHILE @@FETCH_STATUS=0
  BEGIN
    SELECT * FROM EMPLOYEES WHERE COUNTRY=@COUNTRYID
    FETCH NEXT FROM TEST INTO @COUNTRYID
  END
  CLOSE TEST

  DEALLOCATE TEST

// I need to read the retrieved data into grid view in asp.net
C#
protected void Button1_Click(object sender, EventArgs e)
{
  DataTable dt = new DataTable();
  dt.Columns.Add("ID", typeof(string));
           
  SqlDataReader rdr = null;
  SqlCommand cmd = null;

  con.Open();

  // Set up a command with the given query and associate
  // this with the current connection.
  string CommandText = "employeeDetails";
  cmd = new SqlCommand(CommandText);
  cmd.Connection = con;

  // Execute the query
  rdr = cmd.ExecuteReader();

  // Fill the list box with the values retrieved
                 
  while(rdr.Read())
  {
    dt.Rows.Add(rdr["REC_ID"].ToString());
  }
  GridView1.DataSource = dt;
  GridView1.DataBind();
}

The result returns only the first row but when I execute it from SQL it returns all the rows, I need to return all the rows into the grid view any one can help .
Posted
Updated 29-Mar-15 20:22pm
v2

1 solution

You don't really need the cursor for this. Adjust your query as follows (in SP)

SQL
CREATE PROCEDURE EMPLOYEEdETAILS
AS 
  BEGIN
-- you should explicitely state what columns you're fetching, * invites ambigous column error when you change the join

SELECT e.* 
FROM EMPLOYEES e
INNER JOIN COUNTRIES c ON e.country = c.rec_id

END


You could also add the parameter @country_id to SP to be able to filter only employees from a given country.

I would also change the column name employee.country to employee.country_id because that is what it is, more precise then country (I would expect name or code of the country in such field)


In the code, remove the reader and simply fill the dataset (there are lots of examples for this)


NOTE: if you for some reason want to keep the reader then you have to go through all rows calling reader.read until there are no more and accumulate them into datatable.
 
Share this answer
 
Comments
Sherif Barakat 30-Mar-15 3:00am    
I know many ways to do that without cursor but in this case I just need to use the cursor as a good example for how to use cursor in asp.net, the code is work but only return one row in SQL return 5 rows, Could you please help on that.
Sinisa Hajnal 30-Mar-15 6:31am    
You confuse two things. Reader always returns ONE row. You have to go through it like with the cursor...when you use cursor in the procedure, you have to call FETCH NEXT...same with the reader, you have to call reader.Read FOR EACH ROW within.

I've commented on it at the end of my solution. Try with reader.GetString("REC_ID")
Sherif Barakat 30-Mar-15 22:03pm    
I have done it and it is work now
protected void Button1_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(string));
dt.Columns.Add("EMPLOYEE_FULL_NAME", typeof(string));
dt.Columns.Add("COUNTRY", typeof(string));
SqlDataReader rdr = null;
SqlCommand cmd = null;

con.Open();
string CommandText = " TEST ";
cmd = new SqlCommand(CommandText);
cmd.Connection = con;
rdr = cmd.ExecuteReader();
do
{
int count = rdr.FieldCount;

while(rdr.Read())
{
dt.Rows.Add
(
rdr["REC_ID"].ToString(),
rdr["EMPLOYEE_FULL_NAME"].ToString(),
rdr["COUNTRY"].ToString()
);
}
} while (rdr.NextResult());

GridView1.DataSource = dt;
GridView1.DataBind();

}
// Thank you so much for your concern Sinisa Hajnal
Sinisa Hajnal 31-Mar-15 2:28am    
I'm glad you've done it, good job!

You still should add finally and dispose of disposable objects. You should always dispose of objects that use system resources.
Sherif Barakat 31-Mar-15 22:11pm    
Thank you so much for your supporting the idea.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900