Stored Procedures from Microsof SQL Server 2005

I guess you want to display all records in a GridView, if yes then try this:

 string strcon = "Data Source=ABC-PC\\SQLEXPRESS;Initial Catalog=mydb;Integrated Security=True";
        SqlConnection conn;

protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                conn = new SqlConnection(strcon);
                conn.Open();
                SqlCommand cmd = new SqlCommand("usp_Displayallusers", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
                DataSet ds = new DataSet();

da.Fill(ds);
                this.GridView1.DataSource = ds;
                this.GridView1.DataBind();

            }
            catch(Exception ex)
            {
                Response.Clear();
                Response.Write("<h4>" + ex.Message.ToString() + "</h4>");
            }
        }

hope it helps :)

for further queries comment here!!

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace CallingStoredProcedures
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        DataTable table = new DataTable();
        int i=0;
        int MaxRows; 
        private void btnGet_Click(object sender, EventArgs e)
        {

            if (i < MaxRows)
            {
                textBox1.Text = table.Rows[i][0].ToString();
                textBox2.Text = table.Rows[i][1].ToString();
                textBox3.Text = table.Rows[i][2].ToString();
                i++;
            }           
     }
        public DataTable GetTable()
        {            
            table.Columns.Add("FirstName", typeof(string));
            table.Columns.Add("LastName", typeof(string));
            table.Columns.Add("JobTitle", typeof(string));
            return table;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            GetTable();
            SqlConnection con = new SqlConnection("Persist Security Info=False;database=TestDB;server=xyz;Connect Timeout=30;user=abc;pwd=****;");
            con.Open();
            SqlCommand command = new SqlCommand("usp_displayallusers", con);
            command.CommandType = CommandType.StoredProcedure;
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                table.Rows.Add(reader[1].ToString(), reader[2].ToString(), reader[3].ToString());
            }
            MaxRows = table.Rows.Count;
        }
   }
}

The result i am getting is navigation of my records that are already in my SQL Database table "Workers".
On every click, it shows next row on my Visual Form.

Not sure what your real problem here is, since you mentioned that you know it can be retrieved and put to DataTable, DataSets, DataGrids, etc. But anyway, to retrieve data from the database, you need to be familiar on the following objects(There are other objects that you need to learn, but just by learning these classes, you can already retrieve from your database).

SqlConnection[^] - This is your connection to your data source, obviously.
SqlCommand[^] - This is where you will set the SQL statement that you are going to execute on the database. Since you are using stored procedures, putting the name of the SP on the CommandText property of the command does the trick. For cases where your SP has parameters, you will need to add parameters on the Parameters property of the command.
DataAdapter[^] - This is the one that you need to fill your data source(DataTable,DataSet).

For some examples, check out the following articles
http://msdn.microsoft.com/en-us/library/dw70f090%28v=VS.90%29.aspx[^]
Using ADO.NET for beginners[^]