Return Result from Select Query in stored procedure to a List

Question

I'm writing a stored procedure that currently contains only a SELECT query. It will be expanded to do a number of other things, which is why it has to be a stored procedure, but for now, it is a simple query.

Something like this:

SELECT name, occupation, position  FROM jobs  WHERE ... 

I'm looking to return the results of this query to be used in C#. I want to add it to a list so that I can bind it to a GridView component.

I don't know how to go about this, though. If I have to insert it into a list after returning all selected data, then that's alright, I just need to know how to properly return the data so that I can do that.

If I can return it in a format that can be popped right into a list, though, that would be ideal.

Answer 1

In stored procedure, you just need to write the select query like the below:

CREATE PROCEDURE TestProcedure AS BEGIN     SELECT ID, Name      FROM Test END 

On C# side, you can access using Reader, datatable, adapter.

Using adapter has just explained by Susanna Floora.

Using Reader:

SqlConnection connection = new SqlConnection(ConnectionString);  command = new SqlCommand("TestProcedure", connection); command.CommandType = System.Data.CommandType.StoredProcedure; connection.Open(); SqlDataReader reader = command.ExecuteReader();  List<Test> TestList = new List<Test>(); Test test = null;  while (reader.Read()) {     test = new Test();     test.ID = int.Parse(reader["ID"].ToString());     test.Name = reader["Name"].ToString();     TestList.Add(test); }  gvGrid.DataSource = TestList; gvGrid.DataBind(); 

Using dataTable:

SqlConnection connection = new SqlConnection(ConnectionString);  command = new SqlCommand("TestProcedure", connection); command.CommandType = System.Data.CommandType.StoredProcedure; connection.Open();  DataTable dt = new DataTable();  dt.Load(command.ExecuteReader()); gvGrid.DataSource = dt; gvGrid.DataBind(); 

I hope it will help you. :)

Answer 2

 SqlConnection connection = new SqlConnection(ConnectionString);   command = new SqlCommand("TestProcedure", connection);  command.CommandType = System.Data.CommandType.StoredProcedure;   connection.Open();   DataTable dt = new DataTable();   dt.Load(command.ExecuteReader());   gvGrid.DataSource = dt;  gvGrid.DataBind();