Return List<T> from Database using C# and VB.Net

Last Reply one month ago By dharmendr

Posted one month ago

Hello everyone, I have a database table to store my receipt info and on my receipt I have a list of products on the receipt. To store a list I read I need to make another table of that list data and to link the receipt and list of my products together I’m supposed to link with receiptid. To retrieve the productlist table I have receipt id in the table but in my product class I do not have receipt id do I have a receipt object and product object and properties off both of them classes in 1 new List<T> I wanted to return a new class of that list<T> do get my data.

I made a new class and have a receipt object and product object in it to return properties of both is this the correct way I should be doing this? Any info / help / tips would be greatly appreciated.

 

//I am still in the processing of finishing this method
        public static List<ReturnReceiptProductList> GetReceiptProductList(int rid)
        {
            List<ReturnReceiptProductList> productList = new List<ReturnReceiptProductList>();
            ReturnReceiptProductList returnReceipt = new ReturnReceiptProductList();
            using (SqlConnection connection = ConnectionString.GetSqlConnection())
            {
                string sqlList = "SELECT * FROM OrdersList WHERE ReceiptId = @rid";
                using (SqlCommand cmdList = new SqlCommand(sqlList, connection))
                {
                    cmdList.Parameters.AddWithValue("@rid", rid);
                    connection.Open();
                    using (SqlDataReader reader = cmdList.ExecuteReader())
                    {
                        if (reader != null)
                        {
                            while (reader.Read())
                            {
                                Product product = new Product();
                                Receipt receipt = new Receipt();
                                receipt.ReceiptID = Convert.ToInt32(reader["ReceiptId"].ToString());
                                receipt.UserId = Convert.ToInt32(reader["UserId"].ToString());
                                product.ProductId = Convert.ToInt32(reader["ProductId"].ToString());
                                product.ProductName = reader["ProductName"].ToString();
                                product.ProductQuantity = Convert.ToInt32(reader["ProductQuantity"].ToString());
                                product.ProductPrice = Convert.ToDecimal(reader["ProductPrice"].ToString());
                                product.ProductTax = Convert.ToDecimal(reader["ProductTax"].ToString());
                            }
                        }
                    }
                }
            }
        }
    public class ReturnReceiptProductList
    {
        //Returning the values to link Product & Receipt.

        public Product Product { get; set; }

        public Receipt Receipt { get; set; }

        public ReturnReceiptProductList() { }

        public ReturnReceiptProductList(Product p, Receipt r)
        {
            Product = p;
            Receipt = r;
        }
    }

 

You are viewing reply posted by: dharmendr one month ago.
Posted one month ago

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