//written in class file..........
 public DataSet selectvalue()
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
            con.Open();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand cmd = new SqlCommand("sp_selectshift", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter param = new SqlParameter();

            ds.Clear();
            da.SelectCommand = cmd;
            da.Fill(ds);
            con.Close();
            return ds;


        }

//...written in aspx.cs page......
void binddropdownlist()
{							
DataSet ds = new DataSet();
            ds.Clear();
            ds = ob.selectvalue();
            DropDownList1.DataSource = ds.Tables[0];
            DropDownList1.DataTextField = "shift_name";
            DropDownList1.DataValueField = "shift_id";
            DropDownList1.DataBind();
}

//stored procedure.
ALTER PROCEDURE dbo.sp_selectshift
	
AS
	select * from tbl_shift 
	RETURN
.................................