Categories
asp.net c# sql-server

Return Result from Select Query in stored procedure to a List

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.

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. 🙂