locked
two datareader relation between two tables RRS feed

  • Question

  • User932259438 posted

    Hi,

    I want to retrieve from second table commend under PostId.


    var connectionString = ConfigurationManager.ConnectionStrings
                    ["ConnectionString"].ConnectionString;
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = "SELECT * FROM [tbl_Posts] INNER JOIN tbl_UsersProfiles ON tbl_Posts.AuthorUserId = tbl_UsersProfiles.UserId WHERE DeletePost='false'";
    
                    using (var sqlDataReader = sqlCommand.ExecuteReader())
                    {
                        while (sqlDataReader.Read())
                        {
                            var ID = sqlDataReader.GetInt32(sqlDataReader.GetOrdinal("ID"));
                            var FullName = sqlDataReader.GetString(sqlDataReader.GetOrdinal("Firstname"))+" "+ sqlDataReader.GetString(sqlDataReader.GetOrdinal("surname"));
                            var post = sqlDataReader.GetString(sqlDataReader.GetOrdinal("Post"));
                            var DateByPost = sqlDataReader.GetDateTime(sqlDataReader.GetOrdinal("DateByPost"));
                            var imgcircle = (sqlDataReader.GetString(sqlDataReader.GetOrdinal("imgcircle")) == "/" ? "img/userimage.png" : sqlDataReader.GetString(sqlDataReader.GetOrdinal("imgcircle")));
                            var uplaodphoto = (sqlDataReader.GetString(sqlDataReader.GetOrdinal("uplaodphoto")) == "/" ? "none" : "img/"+sqlDataReader.GetString(sqlDataReader.GetOrdinal("uplaodphoto")));
    
    
                                using (var sqlConnection2 = new SqlConnection(connectionString))
                                {
                                    sqlConnection2.Open();
                                    using (var sqlCommand2 = sqlConnection2.CreateCommand())
                                    {
                                        sqlCommand2.CommandText = "SELECT * FROM [tbl_PostComments] WHERE PostID='"+ID+"'";
    
                                        using (var sqlDataReader2 = sqlCommand2.ExecuteReader())
                                        {
                                            while (sqlDataReader2.Read())
                                            {
                                                var Message = (sqlDataReader2.GetString(sqlDataReader.GetOrdinal("Message")));
    
                                            }
                                        }
                                    }
                                }
    
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    
    /// <summary>
    /// Summary description for Stock
    /// </summary>
    public class Posts
    {
        public int ID { get; set; }
    
        public string FullName { get; set; }
    
        public string Post { get; set; }
    
        public DateTime DateByPost { get; set; }
    
        public string replies { get; set; }
    
        public string imgcircle { get; set; }
    
        public string uplaodphoto { get; set; }
    
        public string Message { get; set; }
    
    }
    



    Message I need to get if I have from first table tbl_Posts ID=2

    and second table tbl_PostComments.PostId I have two comments under PostId=2

    Message, PostId
    test1, 2
    test5, 2
    test6, 5


    Then I need to get two comment in my case.

    How can I resolved this?



    Sunday, January 14, 2018 12:29 AM

Answers

  • User1120430333 posted

    Both reads should be separated into two methods MethodRead1() and MethodRead2().

    MethodRead1() will return a List<Posts> objects that were populated by the reader loop in MethodRead1(). Using the vars in the way you have it is not optimal programming when all you should be doing is creating a List of Posts and restrung it out  of MethodRead1().

    The List<Posts> can be passed into MethodRead2(List<Posts>) where you will loop through the List<Posts> objects get the ID to make the second read, get the data from the reader and populate the Posts object to complete each individual Posts object and return the List<Posts> out of the MethodRead2(List<Posts>) with completed Posts that have been populated completely.

    Secondly, you should be using parametrized inline T-SQL to prevent SQL Injection Attacks.

    https://www.mssqltips.com/sqlservertip/2981/using-parameters-for-sql-server-queries-and-stored-procedures/

    <copied>

    Parameterizing in SQL from other languages

    <end>

    You see the concept of a method that is going to return a List<T> of objects out of it with the List<T> of objects that will be used by other methods() that will have the List<T> of objects created by GetStudents() passed into them.

    And the Posts object should just be named Post, because there is nothing plural about the  object in its naming convention you have given it

    public List<DTOStudent> GetStudents()
            {
                var dtos = new List<DTOStudent>();
    
                using (var context = new CUDataEntities())
                {
                    
                    var students = context.Students.ToList();
    
                    foreach(var stud in students)
                    {
                        var dto = new DTOStudent
                        {
                            StudentID = stud.StudentID,
                            FirstName = stud.FirstName,
                            LastName = stud.LastName,
                            EnrollmentDate = stud.EnrollmentDate
                        };
    
                        dtos.Add(dto);
                    }
                }
    
                return dtos;
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 14, 2018 1:05 PM