Receiving list of datas in .Net by executing Stored procedure

Question

Having a stored procedure which returns list of Students with each user having list of projects

Execute the Stored procedure and receive the Students with their Projects from .Net

List<Student> students = ...................

public class Student
{
public int StudentId { get; set; }
public string StudentName{ get; set; }
public List<Project> Projects { get; set;}
}

public class Project
{
public int ProjectId { get; set; }
public string ProjectName { get; set; }
}

Jithu Thomas · Answer

Using System.Data.SqlClient (Recommended): This approach is well-supported and offers a reliable way to interact with SQL Server databases in .NET. using System.Data.SqlClient; public List GetStudentsWithProjects() { List students = new List (); // Replace with your actual connection string string connectionString = YourConnectionStringHere; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); // Replace with your stored procedure name and parameter names (if any) string storedProcedure = GetStudentsWithProjects; using (SqlCommand command = new SqlCommand(storedProcedure connection)) { command.CommandType = CommandType.StoredProcedure; // Add parameters if needed (replace with actual parameter names and values) // command.Parameters.AddWithValue(@param1 value1); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Student student = new Student(); student.StudentId = reader.GetInt32(0); // assuming StudentId is the first column (index 0) student.StudentName = reader.GetString(1); // assuming StudentName is the second column (index 1) // Handle projects as a separate list within the student object student.Projects = new List (); // Check if there are additional result sets for projects (nested result sets) if (reader.HasRows) { reader.NextResult(); // Move to the next result set (if any) while (reader.Read()) { Project project = new Project(); project.ProjectId = reader.GetInt32(0); // assuming ProjectId is the first column project.ProjectName = reader.GetString(1); // assuming ProjectName is the second column student.Projects.Add(project); } } students.Add(student); } } } } return students; }