muttley91 muttley91 - 1 month ago 12
ASP.NET (C#) Question

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.

Answer

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();
reader = command.ExecuteReader();

List<Test> TestList = new List<Test>();
Test test;

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. :)