Return List from Database using C# and VB.Net

Hi bigbear,

Refer below sample code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Namespaces

C#

using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data.SqlClient
Imports System.Configuration

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    List<ReturnOrderList> orderDetails = GetReceiptProductList(10249);
}

NorthwindEntities entities = new NorthwindEntities();
public static List<ReturnOrderList> GetReceiptProductList(int id)
{
    List<ReturnOrderList> productList = new List<ReturnOrderList>();
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        string sqlList = "SELECT * FROM [Order Details] WHERE OrderID = @id";
        using (SqlCommand cmdList = new SqlCommand(sqlList, connection))
        {
            cmdList.Parameters.AddWithValue("@id", id);
            connection.Open();
            using (SqlDataReader reader = cmdList.ExecuteReader())
            {
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        Order_Detail details = new Order_Detail();
                        details.ProductID = Convert.ToInt32(reader["ProductID"]);
                        details.UnitPrice = Convert.ToDecimal(reader["UnitPrice"]);
                        details.Quantity = Convert.ToInt16(reader["Quantity"]);

                        Order order = new Order();
                        order.OrderID = Convert.ToInt32(reader["OrderID"]);

                        ReturnOrderList returnReceipt = new ReturnOrderList();
                        returnReceipt.Orders = order;
                        returnReceipt.OrderDetails = details;
                        productList.Add(returnReceipt);
                    }
                }
            }
        }
    }

    return productList;
}

public class ReturnOrderList
{
    public Order Orders { get; set; }
    public Order_Detail OrderDetails { get; set; }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim orderDetails As List(Of ReturnOrderList) = GetReceiptProductList(10249)
End Sub

Private entities As NorthwindEntities = New NorthwindEntities()
Public Shared Function GetReceiptProductList(ByVal id As Integer) As List(Of ReturnOrderList)
    Dim productList As List(Of ReturnOrderList) = New List(Of ReturnOrderList)()
    Using connection As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Dim sqlList As String = "SELECT * FROM [Order Details] WHERE OrderID = @id"
        Using cmdList As SqlCommand = New SqlCommand(sqlList, connection)
            cmdList.Parameters.AddWithValue("@id", id)
            connection.Open()
            Using reader As SqlDataReader = cmdList.ExecuteReader()
                If reader IsNot Nothing Then
                    While reader.Read()
                        Dim details As Order_Detail = New Order_Detail()
                        details.ProductID = Convert.ToInt32(reader("ProductID"))
                        details.UnitPrice = Convert.ToDecimal(reader("UnitPrice"))
                        details.Quantity = Convert.ToInt16(reader("Quantity"))
                        Dim order As Order = New Order()
                        order.OrderID = Convert.ToInt32(reader("OrderID"))
                        Dim returnReceipt As ReturnOrderList = New ReturnOrderList()
                        returnReceipt.Orders = order
                        returnReceipt.OrderDetails = details
                        productList.Add(returnReceipt)
                    End While
                End If
            End Using
        End Using
    End Using

    Return productList
End Function

Public Class ReturnOrderList
    Public Property Orders As Order
    Public Property OrderDetails As Order_Detail
End Class